Query to find the last access date for a Responsibility in R12

SELECT
    frt.responsibility_name,
    fu.user_name,
    MAX(flr.start_time) "Last Connect"
FROM
    applsys.fnd_login_responsibilities    flr,
    applsys.fnd_user                      fu,
    applsys.fnd_logins                    fl,
    applsys.fnd_responsibility_tl         frt
WHERE
        fl.login_id = flr.login_id
    AND fl.user_id = fu.user_id
    AND fu.user_name = 'SANDEEP_SHARMA' -- Username
    AND frt.responsibility_id = flr.responsibility_id
       --and frt.responsibility_name = '&ResponsibilityName'
GROUP BY
    frt.responsibility_name,
    fu.user_name;

Query to find oracle patch is applied or not

 SELECT DISTINCT
    e.patch_name,
    a.bug_number,
    ad_patch.is_patch_applied('R12', - 1, a.bug_number)            patch_applied,
    to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS')           end_date,
    e.patch_type,
    d.patch_abstract
FROM
    ad_bugs             a,
    ad_patch_run_bugs   b,
    ad_patch_runs       c,
    ad_patch_drivers    d,
    ad_applied_patches  e
WHERE
        a.bug_id = b.bug_id
    AND b.patch_run_id = c.patch_run_id
    AND c.patch_driver_id = d.patch_driver_id
    AND d.applied_patch_id = e.applied_patch_id
   AND e.patch_name in ('33515489')--patch_number
ORDER BY
    1,
    3 ASC;

Query to get Account description of GL Code Combination in Oracle APPS

SELECT

    gcc.concatenated_segments                                                                              account,

    gl_flexfields_pkg.get_concat_description(gcc.chart_of_accounts_id, gcc.code_combination_id) account_description

FROM

    gl_code_combinations_kfv gcc

WHERE

    gcc.code_combination_id = 111111

Query to find AP Invoice status in Oracle apps

 SELECT
    aia.org_id,
    aia.invoice_num,
    aia.invoice_date,
    decode(ap_invoices_utility_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount,
                                                       aia.payment_status_flag,
                                                       aia.invoice_type_lookup_code),
           'FULL', 'Fully Applied',
           'NEVER APPROVED', 'Never Validated',
           'NEEDS REAPPROVAL','Needs Revalidation',
           'CANCELLED', 'Cancelled',
           'UNPAID', 'Unpaid',
           'AVAILABLE', 'Available',
           'UNAPPROVED', 'Unvalidated',
           'APPROVED', 'Validated',
           'PERMANENT', 'Permanent Prepayment',
           NULL) invoice_status
FROM
    ap_invoices_all aia
 WHERE aia.invoice_num  = '111111111';

How to compare fmb files

 Open each in forms builder and run File/Administration/Object List Report. This creates a <formname>.txt
 compare the text file and find difference.

API Script to Terminate Service Contract/Line/Subline in Oracle Apps

oks_terminate_contract_pub.Terminate_Contract

set serveroutput on size 100000;

DECLARE
   l_return_status   VARCHAR2 (100);
   l_date_term       DATE;
   L_MSG_DATA        VARCHAR2 (1000);
   l_msg_count       NUMBER;
   l_msg_index_out   NUMBER;
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
BEGIN
/*Note:-
P_contract_id            --> Contract Header Id
Line Id                     --> Contract Line Id
P_subline_id              --> Contract Subline Id
P_termination_date    --> Termination Date
p_fullcredit_yn          --> Indicates if full credit has to be given during termination and default value is ‘N’
p_suppress_credit_yn --> Indicates if credit has to be suppressed during termination and default value is ‘N’
p_term_reason_code  --> Termination Reason
p_override_amount    --> Override Amount*/
   BEGIN
    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = 'TEST_USER';    
   END;
  
   BEGIN
    SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_tl
     WHERE responsibility_name = 'NUAN NDI Ireland Service Contracts Manager'
       and application_id = 515 ;  --Service Contracts
   END;
  
   fnd_global.apps_initialize (user_id        => l_user_id,
                               resp_id        => l_resp_id,
                               resp_appl_id   => l_resp_appl_id
                              );
                             
   okc_context.set_okc_org_context (p_chr_id => 1953781);
  
   oks_terminate_contract_pub.Terminate_Contract(
      p_contract_id          => 1953781,
      p_line_id              => NULL,
      p_subline_id           => NULL,
      P_Termination_Date     => TO_DATE ('17-OCT-2022'),
      p_fullcredit_yn        => 'N', --Indicates if full credit has to be given during termination and default value is ‘N’
      p_suppress_credit_yn   => 'N', --Indicates if credit has to be suppressed during termination and default value is ‘N’
      p_term_reason_code     => 'RMA',
      p_override_amount      => NULL,
      x_return_status        => l_return_status);
  
   DBMS_OUTPUT.put_line ('l_return_status - ' || l_return_status);
   IF l_return_status = 'S'
   THEN
      COMMIT;
   END IF;
  
   IF l_return_status <> 'S'
   THEN
      FOR i IN 1 .. fnd_msg_pub.count_msg
      LOOP
         fnd_msg_pub.get (p_msg_index       => i,
                          p_encoded         => 'T',
                          p_data            => l_msg_data,
                          p_msg_index_out   => l_msg_index_out);
         fnd_message.set_encoded (l_msg_data);
         l_msg_data := fnd_message.get;
         DBMS_OUTPUT.put_line (l_msg_data);
      END LOOP;
   END IF;
END;
/

query to find service contract terminate reason

SELECT LOOKUP_CODE,MEANING,DESCRIPTION
FROM   fnd_lookups fndlup
where  fndlup.lookup_type = 'OKC_TERMINATION_REASON'
and trunc(nvl(END_DATE_ACTIVE,sysdate)) >= trunc(sysdate)