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)

API Script to update expiration date for Install Base

csi_item_instance_pub.update_item_instance

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    CURSOR cur_ib IS
    SELECT
        instance_id,
        inventory_item_id item_id,
        system_id,
        object_version_number,
        external_reference,
        serial_number,
        last_oe_order_line_id,
        attribute10
    FROM
        csi_item_instances csi
    WHERE 1=1
and csi.instance_id = 9710652--9710653
;
      --
    l_external_reference     VARCHAR2(50);
    l_instance_id            NUMBER;
    l_object_version_number  NUMBER;
    l_end_user               VARCHAR2(100);
    v_instance_rec           csi_datastructures_pub.instance_rec;
    v_ext_attrib_values_tbl  csi_datastructures_pub.extend_attrib_values_tbl;
    v_party_tbl              csi_datastructures_pub.party_tbl;
    v_account_tbl            csi_datastructures_pub.party_account_tbl;
    v_pricing_attrib_tbl     csi_datastructures_pub.pricing_attribs_tbl;
    v_org_assignments_tbl    csi_datastructures_pub.organization_units_tbl;
    v_asset_assignment_tbl   csi_datastructures_pub.instance_asset_tbl;
    v_txn_rec                csi_datastructures_pub.transaction_rec;
    v_instance_id_lst        csi_datastructures_pub.id_tbl;
    v_status_code            VARCHAR2(100);
    t_output                 VARCHAR2(1000);
    v_return_status          VARCHAR2(1000);
    v_msg_dummy              VARCHAR2(1000);
    v_msg_count              NUMBER;
    v_msg_data               VARCHAR2(1000);
    l_system_id              csi_item_instances.system_id%TYPE;
BEGIN
      --
    FOR rec_ib IN cur_ib LOOP
dbms_output.put_line('instance_id = ' || rec_ib.instance_id);
        BEGIN
            v_instance_rec.instance_id := rec_ib.instance_id;
v_instance_rec.active_end_date := '01-JUN-2023';
            v_instance_rec.object_version_number := rec_ib.object_version_number;
            v_txn_rec.transaction_id := NULL;
            v_txn_rec.transaction_date := sysdate;
            v_txn_rec.source_transaction_date := sysdate;
            v_txn_rec.transaction_type_id := 1;
            --v_txn_rec.txn_sub_type_id := NULL;
            csi_item_instance_pub.update_item_instance(1,
                                                      fnd_api.g_false,
                                                      fnd_api.g_false,
                                                      fnd_api.g_valid_level_full,
                                                      v_instance_rec,
                                                      v_ext_attrib_values_tbl,
                                                      v_party_tbl,
                                                      v_account_tbl,
                                                      v_pricing_attrib_tbl,
                                                      v_org_assignments_tbl,
                                                      v_asset_assignment_tbl,
                                                      v_txn_rec,
                                                      v_instance_id_lst,
                                                      v_return_status,
                                                      v_msg_count,
                                                      v_msg_data);
            DBMS_OUTPUT.put_line ('v_return_status = ' || v_return_status);
IF v_return_status != 'S' THEN
               --
                IF v_msg_count > 0 THEN
                    FOR j IN 1..v_msg_count LOOP
                        fnd_msg_pub.get(j, fnd_api.g_false,
                                       v_msg_data,
                                       v_msg_dummy);
                        t_output := ( 'Msg'
                                      || to_char(j)
                                      || ': '
                                      || v_msg_data );
                        dbms_output.put_line(substr(t_output, 1,
                                                   255));
                    END LOOP;
                END IF;
            -- DBMS_OUTPUT.put_line ('v_return_status = ' || v_return_status);
            END IF;
            --
            IF v_return_status = 'S' THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('exception v_return_status = ' || v_return_status);
        END;
    END LOOP;
END;
/

API script to end date responsibilities for a User

 SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    CURSOR emp_resp_det 
    IS
SELECT
usr.user_id,
usr.user_name,
papf.employee_number,
papf.full_name,
resp.responsibility_name,
resp.responsibility_id,
resp.application_id,
furg.security_group_id,
furg.start_date,
furg.end_date,
trunc(sysdate) responsibility_end_date
FROM
per_all_people_f          papf,
fnd_responsibility_tl     resp,
fnd_user                  usr,
fnd_user_resp_groups_all  furg
WHERE
1 = 1
AND trunc(sysdate) BETWEEN trunc(furg.start_date) AND trunc(nvl(furg.end_date,sysdate))
AND furg.responsibility_id = resp.responsibility_id
AND usr.user_id = furg.user_id
AND papf.person_id = usr.employee_id
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
AND resp.responsibility_name <> 'NUAN Access Request System'
--AND papf.employee_number = 1111111
ORDER BY usr.user_id;
    l_flag VARCHAR2(2) := NULL;
    l_count NUMBER := 0;
BEGIN
    dbms_output.put_line('Script To End Date Responsibilities For Given User');
    dbms_output.put_line('--------------------------------------------------------------------------------');
    dbms_output.put_line('USER_ID,' || 'USER_NAME,' || 'RESPONSIBILITY_NAME,' ||  'L_FLAG');
    l_count := 0;
    FOR rec_emp_resp_det IN emp_resp_det 
    LOOP
        BEGIN
            l_flag := NULL;
            
            fnd_user_resp_groups_api.update_assignment(
                                                        user_id => rec_emp_resp_det.user_id, 
                                                        responsibility_id => rec_emp_resp_det.responsibility_id,
                                                        responsibility_application_id => rec_emp_resp_det.application_id, 
                                                        security_group_id => rec_emp_resp_det.security_group_id,
                                                        start_date => rec_emp_resp_det.start_date,
                                                        end_date => rec_emp_resp_det.responsibility_end_date, 
                                                        description => NULL, 
                                                        update_who_columns => 'Y'
                                                      );
            l_flag := 'S';
        EXCEPTION
            WHEN OTHERS 
            THEN
                dbms_output.put_line('Error Updating End date for employee ' || rec_emp_resp_det.employee_number || ': ' || sqlerrm);
                l_flag := 'E';
        END;
        dbms_output.put_line(rec_emp_resp_det.user_id || ',' || rec_emp_resp_det.user_name|| ',' ||rec_emp_resp_det.responsibility_name ||  ',' || l_flag);
        l_count := l_count + 1;
    END LOOP;
    dbms_output.put_line('--------------------------------------------------------------------------------');
    dbms_output.put_line('Total Records Processed = ' ||l_count);
    dbms_output.put_line('--------------------------------------------------------------------------------');
    COMMIT;
EXCEPTION
    WHEN OTHERS 
    THEN
        dbms_output.put_line('Error :' || sqlerrm);
END;
/

link between fnd_user and per_all_people_f

 SELECT
    fur.user_id,
    fur.user_name,
    papf.employee_number,
    papf.full_name
FROM
    per_all_people_f  papf,
    fnd_user          fur
WHERE
        1 = 1
    AND papf.person_id = fur.employee_id
    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
    AND papf.employee_number = 704391
ORDER BY
    fur.user_id;

Query to find list of responsibilities assign to user

SELECT

    usr.user_id,

    usr.user_name,

    papf.employee_number,

    papf.full_name,

    resp.responsibility_name,

    resp.responsibility_id,

    resp.application_id,

    furg.security_group_id,

    furg.start_date,

    furg.end_date

FROM

    per_all_people_f          papf,

    fnd_responsibility_tl     resp,

    fnd_user                  usr,

    fnd_user_resp_groups_all  furg

WHERE

        1 = 1

    AND trunc(sysdate) BETWEEN trunc(furg.start_date) AND trunc(nvl(furg.end_date, sysdate))

    AND furg.responsibility_id = resp.responsibility_id

    AND usr.user_id = furg.user_id

    AND papf.person_id = usr.employee_id

    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)

    AND papf.employee_number = 1111111

ORDER BY

    usr.user_id;

API to get order line number, line amount and order amount

---------------------------------------------------------------------------------

oe_order_misc_pub.get_concat_line_number(oola.line_id) 

oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL')

oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL')

---------------------------------------------------------------------------------

SELECT
    ooha.org_id,
    ooha.header_id,
    ooha.order_number,
    oola.line_id,
    oe_order_misc_pub.get_concat_line_number(oola.line_id) line_number,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, oola.line_id, 'ALL'), 0)                 line_amount,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL'), 0)                         order_amount  
FROM
    oe_order_headers_all  ooha,
    oe_order_lines_all    oola
WHERE
        ooha.header_id = oola.header_id
    AND ooha.org_id = oola.org_id
    AND ooha.order_number = '71000004'
    order by oola.line_id;

query to find supplier address and email id in oracle apps

 SELECT DISTINCT
    aps.vendor_name,
    aps.segment1    supplier_number,
    assa.org_id,
    assa.vendor_site_code,
    (
        SELECT
            hcp.email_address
        FROM
            hz_party_sites     hps,
            hz_contact_points  hcp
        WHERE
                1 = 1
            AND hcp.owner_table_name = 'HZ_PARTY_SITES'
            AND hps.party_id = aps.party_id
            AND hcp.owner_table_id = hps.party_site_id
            AND hcp.contact_point_type = 'EMAIL'
            AND ROWNUM <= 1
    )               email_id,
    assa.address_line1,
    assa.address_line2,
    assa.address_line3,
    assa.city,
    assa.state,
    assa.zip,
    assa.country
FROM
    apps.ap_suppliers             aps,
    apps.ap_supplier_sites_all    assa
WHERE
        1 = 1
    AND aps.vendor_id = assa.vendor_id
    AND aps.segment1 = '28015';