Query to find sales orders and invoices details from service contract

SELECT hou.name operating_unit, hou.organization_id org_id, ooha.order_number sales_order_number, ooha.cust_po_number po_number, ohdff.subscription_id subscription_id, trunc(ooha.ordered_date) sales_order_date, trunc(ooha.booked_date) booked_date, hca1.account_number bill_to_customer_number, hp1.party_name bill_to_customer_name, hca2.account_number ship_to_customer_number, hp2.party_name ship_to_customer_name, nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'LINES'), 0) order_amount, okhb.contract_number, okhb.contract_number_modifier, trunc(okhb.start_date) contract_start_date, trunc(okhb.end_date) contract_end_date, round(okhb.estimated_amount, 2) contract_amount, okhb.sts_code, okhb.id, rcta.trx_number invoice_number, trunc(rcta.trx_date) invoice_date, rcta.invoice_currency_code invoice_currency, SUM(rctla.extended_amount) invoice_amount, decode(apsa.status, 'OP', 'Open', 'CL', 'Close') invoice_status, rcta.customer_trx_id, ooha.header_id, ottt.name order_type, ooha.context FROM hr_operating_units hou, oe_order_headers_all ooha, oe_transaction_types_tl ottt, hz_cust_site_uses_all hcsua1, hz_cust_acct_sites_all hcasa1, hz_cust_accounts hca1, hz_parties hp1, hz_cust_site_uses_all hcsua2, hz_cust_acct_sites_all hcasa2, hz_cust_accounts hca2, hz_parties hp2, okc_k_rel_objs rel, okc_k_headers_all_b okhb, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, ra_customer_trx_lines_all rctla, oe_order_headers_all_dfv ohdff WHERE 1 = 1 AND hou.organization_id = ooha.org_id AND ooha.order_type_id = ottt.transaction_type_id AND ottt.language = userenv('LANG') AND hcsua1.site_use_id = ooha.invoice_to_org_id AND hcasa1.cust_acct_site_id = hcsua1.cust_acct_site_id AND hca1.cust_account_id = hcasa1.cust_account_id AND hp1.party_id = hca1.party_id AND hcsua2.site_use_id = ooha.ship_to_org_id AND hcasa2.cust_acct_site_id = hcsua2.cust_acct_site_id AND hca2.cust_account_id = hcasa2.cust_account_id AND hp2.party_id = hca2.party_id AND to_number(rel.chr_id) = okhb.id AND rel.object1_id1 = ooha.header_id AND ooha.org_id = okhb.org_id AND rel.jtot_object1_code = 'OKX_ORDERHEAD' AND okhb.contract_number = rctla.interface_line_attribute1 AND okhb.contract_number_modifier = rctla.interface_line_attribute2 AND rctla.interface_line_context = 'OKS CONTRACTS' AND rctla.line_type = 'LINE' AND rcta.customer_trx_id = rctla.customer_trx_id AND rcta.org_id = rctla.org_id AND apsa.customer_trx_id = rctla.customer_trx_id AND apsa.org_id = rctla.org_id AND ohdff.rowid = ooha.rowid AND ohdff.amp_order = 'Y' AND hou.organization_id IN ( 101 ) -- AND OKHB.CONTRACT_NUMBER in ('11413838','11397088','IR5134831') GROUP BY hou.name, hou.organization_id, ooha.order_number, ooha.cust_po_number, ohdff.subscription_id, trunc(ooha.ordered_date), trunc(ooha.booked_date), hca1.account_number, hp1.party_name, hca2.account_number, hp2.party_name, okhb.contract_number, okhb.contract_number_modifier, okhb.start_date, okhb.end_date, round(okhb.estimated_amount, 2), okhb.sts_code, okhb.id, rcta.trx_number, trunc(rcta.trx_date), rcta.invoice_currency_code, apsa.status, rcta.customer_trx_id, ooha.header_id, ottt.name, ooha.context

Script to create lookup values (Insert values into fnd_lookup_values)

SET SERVEROUTPUT ON SIZE 1000000;
SET DEFINE OFF;

DECLARE
    CURSOR cur_lookup_dtl IS
    SELECT
        lookup_type,
        security_group_id,
        view_application_id
    FROM
        fnd_lookup_types
    WHERE
        lookup_type = 'QUOTING_SALES_REP';
    CURSOR cur_salesrep IS
    SELECT
        person_id,
        resource_id,
        employee_number,
        full_name,
        email_address,
        phone_number
    FROM
        quoting_sales_rep_extbl
    WHERE
        employee_number in (111111) ;
    l_rowid   VARCHAR2(100) := 0;
    l_userid  NUMBER := -1;
    l_count   NUMBER := 0;
    l_flag    VARCHAR2(2) := NULL;
BEGIN
    dbms_output.enable(NULL);
    dbms_output.put_line('Script To insert sales rep to QUOTING_SALES_REP lookup');
    dbms_output.put_line('--------------------------------------------------------------------------------');
    dbms_output.put_line('PERSON_ID,'
                         || 'RESOURCE_ID,'
                         || 'EMPLOYEE_NUMBER,'
                         || 'FULL_NAME,'
                         --|| 'EMAIL_ADDRESS,'
                         --|| 'PHONE_NUMBER,'
                         || 'L_FLAG');
    l_count := 0;
    FOR rec_lookup_dtl IN cur_lookup_dtl LOOP
        FOR rec_salesrep IN cur_salesrep LOOP
            BEGIN
                l_rowid := NULL;
                l_flag := NULL;
                fnd_lookup_values_pkg.insert_row(x_rowid => l_rowid,
                                                x_lookup_type => rec_lookup_dtl.lookup_type,
                                                x_security_group_id => rec_lookup_dtl.security_group_id,
                                                x_view_application_id => rec_lookup_dtl.view_application_id,
                                                x_lookup_code => rec_salesrep.resource_id,
                                                x_tag => rec_salesrep.person_id,
                                                x_attribute_category =>rec_lookup_dtl.lookup_type,--NULL,
                                                x_attribute1 => rec_salesrep.email_address,
                                                x_attribute2 => rec_salesrep.phone_number,
                                                x_attribute3 => NULL,
                                                x_attribute4 => NULL,
                                                x_enabled_flag => 'Y',
                                                x_start_date_active => sysdate,
                                                x_end_date_active => NULL,
                                                x_territory_code => NULL,
                                                x_attribute5 => NULL,
                                                x_attribute6 => NULL,
                                                x_attribute7 => NULL,
                                                x_attribute8 => NULL,
                                                x_attribute9 => NULL,
                                                x_attribute10 => NULL,
                                                x_attribute11 => NULL,
                                                x_attribute12 => NULL,
                                                x_attribute13 => NULL,
                                                x_attribute14 => NULL,
                                                x_attribute15 => NULL,
                                                x_meaning => rec_salesrep.employee_number,
                                                x_description => rec_salesrep.full_name,
                                                x_creation_date => sysdate,
                                                x_created_by => l_userid,
                                                x_last_update_date => sysdate,
                                                x_last_updated_by => l_userid,
                                                x_last_update_login => l_userid);
                --COMMIT;
                --DBMS_OUTPUT.put_line (j.X_LOOKUP_CODE || ' loaded');
                --dbms_output.put_line('sss1: ' || l_flag);
                l_flag := 'S';
                  --dbms_output.put_line('insert completed: ' || l_flag);
            EXCEPTION
                WHEN OTHERS THEN
                    --dbms_output.put_line('Inner Exception: ' || sqlerrm);
                    dbms_output.put_line('Error occurred while inserting records into the lookup for the employee number: '
                                         || rec_salesrep.employee_number
                                         || ': '
                                         || sqlerrm);
                    l_flag := 'E';
            END;
            dbms_output.put_line(rec_salesrep.person_id
                                 || ','
                                 || rec_salesrep.resource_id
                                 || ','
                                 || rec_salesrep.employee_number
                                 || ','
                                 || rec_salesrep.full_name
                                 --|| ','
                                 --|| rec_salesrep.email_address
                                 --|| ','
                                 --|| l_flag
                                 --|| ','
                                 --|| trim(rec_salesrep.phone_number)
                                 || ','
                                 || l_flag
                                );
            l_count := l_count + 1;
        END LOOP;
    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;
/

Query to find Bill To, Ship To Customer and Amount for the sales orders

 SELECT 
    hou.name,
    ooha.org_id,
    ooha.order_number,
    (
        SELECT
            okhb.contract_number
            || '-'
            || okhb.contract_number_modifier
        FROM
            okc_k_headers_all_b  okhb,
            okc_k_rel_objs       rel
        WHERE
                1 = 1
            AND rel.chr_id = okhb.id
            AND rel.jtot_object1_code = 'OKX_ORDERHEAD'
            AND rel.object1_id1 = ooha.header_id
            and rownum <=1
    )                                                                                  service_contract_number,
    ooha.blanket_number,
    ott.name                                                                           order_type,
    bill_hp.party_name                                                                 customer_name,
    bill_hca.account_number                                                            customer_account_number,
    bill_hps.party_site_number                                                         bill_party_site_number,
    bill_hl.address1                                                                   bill_address1,
    bill_hl.address2                                                                   bill_address2,
    bill_hl.city                                                                       bill_city,
    bill_hl.state                                                                      bill_state,
    bill_hl.postal_code                                                                bill_postal_code,
    bill_hl.country                                                                    bill_country,
    ship_hps.party_site_number                                                         ship_party_site_number,
    ship_hl.address1                                                                   ship_address1,
    ship_hl.address2                                                                   ship_address2,
    ship_hl.city                                                                       ship_city,
    ship_hl.state                                                                      ship_state,
    ship_hl.postal_code                                                                ship_postal_code,
    ship_hl.country                                                                    ship_country,
    ooha.transactional_curr_code,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'LINES'), 0)               order_subtotal,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'TAXES'), 0)               order_tax,
    nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'ALL'), 0)                 order_total    
FROM
    oe_order_headers_all     ooha,
  --  oe_order_lines_all       oola,
    oe_transaction_types_tl  ott,
    hz_cust_site_uses_all    bill_hcsua,
    hz_cust_acct_sites_all   bill_hcasa,
    hz_cust_accounts         bill_hca,
    hz_party_sites           bill_hps,
    hz_parties               bill_hp,
    hz_locations             bill_hl,
    hz_cust_site_uses_all    ship_hcsua,
    hz_cust_acct_sites_all   ship_hcasa,
    hz_cust_accounts         ship_hca,
    hz_party_sites           ship_hps,
    hz_parties               ship_hp,
    hz_locations             ship_hl,
    hr_operating_units       hou
WHERE
        1 = 1
   -- AND ooha.header_id = oola.header_id
   -- AND ooha.org_id = oola.org_id
    AND ooha.order_type_id = ott.transaction_type_id
    AND ooha.org_id = hou.organization_id
    AND ooha.invoice_to_org_id = bill_hcsua.site_use_id
    AND bill_hcsua.cust_acct_site_id = bill_hcasa.cust_acct_site_id
    AND bill_hcasa.cust_account_id = bill_hca.cust_account_id
    AND bill_hcasa.party_site_id = bill_hps.party_site_id
    AND bill_hca.party_id = bill_hp.party_id
    AND bill_hps.location_id = bill_hl.location_id
    AND ooha.ship_to_org_id = ship_hcsua.site_use_id
    AND ship_hcsua.cust_acct_site_id = ship_hcasa.cust_acct_site_id
    AND ship_hcasa.cust_account_id = ship_hca.cust_account_id
    AND ship_hcasa.party_site_id = ship_hps.party_site_id
    AND ship_hca.party_id = ship_hp.party_id
    AND ship_hps.location_id = ship_hl.location_id
    --    AND oola.tax_value > 0
    --AND ooha.order_number = '111111111'
    --AND nvl(oe_totals_grp.get_order_total(ooha.header_id, NULL, 'TAXES'), 0) > 0
    AND trunc(ooha.booked_date) >= '01-JAN-2026';