Query to find Customer Billto/Shipto Address and contact email id and Phone number and information

SELECT distinct

    hp.party_id,

    hca.cust_account_id,

    hcsua.site_use_id,

    hcasa.org_id,

    hca.account_number     customer_number,

    hp.party_name          customer_name,

    hca.attribute5,

    hcp.send_statements,

    hcp.dunning_letters    send_dunning_letters,

    hps.party_site_number  billto_shipto_location,

    hcsua.site_use_code,

    decode(hcsua.site_use_code,'BILL_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     bill_to_address,

    decode(hcsua.site_use_code,'SHIP_TO',hl.address1

    || ' '

    || hl.address2

    || ' '

    || hl.city

    || ' '

    || hl.state

    || ' '

    || hl.postal_code

    || ' '

    || hl.country)     ship_to_address,

    translate(TRIM(hcasa.attribute3), 'x'

                                           || CHR(13)

                                           || CHR(9)

                                           || '|',

              'x')         invoice_billto_email_address,

    decode(hcasa.attribute2, 'E', decode(hcasa.attribute3, NULL, 'Postal Mail', 'Email'), 'Postal Mail')   delivery_method,          

    (

        SELECT

            LISTAGG(hcp.email_address, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) emild_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            AND hcp.owner_table_name = 'HZ_PARTIES'

            AND hcar.cust_account_id = hca.cust_account_id

            AND hcar.party_id = rel.party_id

            AND hrr.cust_account_role_id = hcar.cust_account_role_id

            AND hrr.responsibility_type = 'DUN'

            AND hcp.contact_point_type = 'EMAIL'

    )                      dunning_email_address,

    (

        SELECT

            LISTAGG(hcp.raw_phone_number, ' , ') WITHIN GROUP(

                ORDER BY

                    hcar.cust_account_id

            ) phone_list

        FROM

            hz_relationships        rel,

            hz_contact_points       hcp,

            hz_cust_account_roles   hcar,

            hz_role_responsibility  hrr

        WHERE

                1 = 1

            AND rel.object_id = hp.party_id

            AND rel.party_id = hcp.owner_table_id

            AND rel.relationship_type = 'CONTACT'

            AND rel.directional_flag = 'F'

            AND hcp.owner_table_name = 'HZ_PARTIES'

            AND hcar.cust_account_id = hca.cust_account_id

            AND hcar.party_id = rel.party_id

            AND hrr.cust_account_role_id = hcar.cust_account_role_id

            AND hrr.responsibility_type = 'DUN'

            AND hcp.contact_point_type = 'PHONE'

    )                      dunning_phone

FROM

    hz_cust_accounts        hca,

    hz_parties              hp,

    hz_cust_acct_sites_all  hcasa,

    hz_cust_site_uses_all   hcsua,

    hz_party_sites          hps,

    hz_customer_profiles    hcp,

    hz_locations            hl

WHERE

        1 = 1

    AND hp.party_id = hca.party_id

    AND hca.status = 'A'

    AND hca.cust_account_id = hcasa.cust_account_id

    AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

    AND hcasa.org_id = hcsua.org_id

    AND hcasa.party_site_id = hps.party_site_id

    AND hp.party_id = hps.party_id

    AND hps.location_id = hl.location_id

    AND hca.cust_account_id = hcp.cust_account_id

    AND hcp.site_use_id IS NULL

    AND hcsua.site_use_code in ('BILL_TO','SHIP_TO')

    AND hcasa.status = 'A'

    AND hcsua.status = 'A'

    AND hps.status = 'A'

    AND hp.party_type = 'ORGANIZATION'

    AND hcasa.org_id IN ( 103 )

    --AND hca.account_number in ('1432072','1498470','1448442')--'2408'

    ;

Query to find the functional and usd amount for the ar invoice

 SELECT
    hca.account_number                                                       customer_number,
    hp.party_name                                                            customer_name,
    hca.attribute5                                                           customer_key,
    rcta.trx_number,
    rcta.trx_date,
    ar.amount_due_original,
    round(ar.amount_due_original * nvl(ar.exchange_rate, 1), 2)              functional_amount,
   --  ar.*
    round(ar.amount_due_original * decode(rcta.invoice_currency_code, 'USD', 1, nvl((
        SELECT
            gld.conversion_rate
        FROM
            gl_daily_rates gld
        WHERE
                gld.from_currency = ar.invoice_currency_code
            AND gld.to_currency = 'USD'
            AND gld.conversion_type = 'Corporate'
            AND trunc(rcta.trx_date) = trunc(gld.conversion_date)
            AND ROWNUM = 1
    ),
                                                                                    1)),
          2)                                                                 usd_amount
FROM
    hz_parties                hp,
    hz_cust_accounts          hca,
    ra_customer_trx_all       rcta,
    ar_payment_schedules_all  ar
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND rcta.bill_to_customer_id = hca.cust_account_id
    AND rcta.customer_trx_id = ar.customer_trx_id
    AND ar.class IN ( 'INV', 'CM', 'DM' )
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND rcta.trx_date BETWEEN '01-JUL-2025' AND '30-JUN-2026'
    --AND rcta.trx_number = '40047438'
    AND hca.account_number IN ( '13357' );-- '1416594' );

Query to find the organization location

SELECT

    hou.organization_id,

    hou.name operating_unit,

    hl.location_code,

    hl.description,

    hl.address_line_1,

    hl.town_or_city,

    hl.region_1,

    hl.postal_code,

    hl.country

FROM

    hr_operating_units         hou,

    hr_all_organization_units  haou,

    hr_locations_all           hl

WHERE

        hou.organization_id = haou.organization_id

    AND haou.location_id = hl.location_id

    AND hou.organization_id IN ( 101)

ORDER BY

    hou.name;

Query to display output in json format

 SELECT JSON_OBJECT(

    'PRODUCT_CLASS' VALUE flv.lookup_code,

    'PRODUCT_CLASS_DESCRIPTION' VALUE flv.description    

     ) AS json_output

FROM

    fnd_lookup_values flv

WHERE

        flv.lookup_type = 'PRODUCT_CLASS'

    AND flv.enabled_flag = 'Y'

    AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate);

Query to find AR invoice Amount

 SELECT
    rcta.trx_number,
    rcta.trx_date,
    rcta.creation_date,
    apsa.amount_due_original,
    apsa.amount_line_items_original,
    apsa.tax_original
FROM
    ra_customer_trx_all       rcta,
    ar_payment_schedules_all  apsa
WHERE
        1 = 1
    AND rcta.customer_trx_id = apsa.customer_trx_id
    AND rcta.trx_number = '70682380'
    ;

Script to update customer account (update hz_cust_accounts table) in oracle apps

 set serveroutput on

DECLARE

p_cust_account_rec      HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;

p_object_version_number NUMBER;

x_return_status         VARCHAR2(2000);

x_msg_count             NUMBER;

x_msg_data              VARCHAR2(2000);

BEGIN

-- Setting the Context --

mo_global.init('AR');

fnd_global.apps_initialize ( user_id      => 233158

                            ,resp_id      => 58900

                            ,resp_appl_id => 222);

mo_global.set_policy_context('S',111);

fnd_global.set_nls_context('AMERICAN');

-- Initializing the Mandatory API parameters

p_cust_account_rec.cust_account_id := 6525878;

p_cust_account_rec.ATTRIBUTE5   := '6317668';

p_cust_account_rec.ATTRIBUTE7   := 'Test1';

p_object_version_number            := 1; --object_version_number from hz_cust_accounts table

DBMS_OUTPUT.PUT_LINE('API Starts');

HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT

                  (

                    p_init_msg_list         => FND_API.G_TRUE,

                    p_cust_account_rec      => p_cust_account_rec,

                    p_object_version_number => p_object_version_number,

                    x_return_status         => x_return_status,

                    x_msg_count             => x_msg_count,

                    x_msg_data              => x_msg_data

                          );

IF  x_return_status = fnd_api.g_ret_sts_success THEN

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Customer Account updated Successful ');

 

ELSE

    DBMS_OUTPUT.put_line ('Customer Account update failed:'||x_msg_data);

    ROLLBACK;

    FOR i IN 1 .. x_msg_count

    LOOP

      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');

      dbms_output.put_line( i|| ') '|| x_msg_data);

    END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE('API completed');

END;

/

Script to update Service Contract Header

SET SERVEROUTPUT ON;
DECLARE
    l_api_version    CONSTANT NUMBER := 1.0;
    l_init_msg_list  CONSTANT VARCHAR2(1) := 'T';
    l_return_status  VARCHAR2(1);
    l_msg_count      NUMBER;
    l_msg_data       VARCHAR2(2000);
    l_chrv_tbl_in    okc_contract_pub.chrv_tbl_type;
    l_chrv_tbl_out   okc_contract_pub.chrv_tbl_type;
    l_clev_tbl_in    okc_contract_pub.clev_tbl_type;
    l_clev_tbl_out   okc_contract_pub.clev_tbl_type;
    l_cle_tbl_in     okc_contract_pub.clev_tbl_type;
    l_cle_tbl_out    okc_contract_pub.clev_tbl_type;
    i                NUMBER := 1;
    l_count          NUMBER := 0;
    l_id             okc_k_headers_all_b.id%TYPE;
    l_end_date       okc_k_headers_all_b.end_date%TYPE;
    

CURSOR header_cur IS
    SELECT
        id,
        contract_number,
        start_date,
        end_date
    FROM
        okc_k_headers_all_b
    WHERE
        id = 7569984;

BEGIN
    fnd_global.apps_initialize(user_id => 233132, resp_id => 598452, resp_appl_id => 515);
    mo_global.init('OKS');
    mo_global.set_policy_context('S', 103);
    

FOR header_rec IN header_cur LOOP
        l_chrv_tbl_in(1).id := header_rec.id;
        l_chrv_tbl_in(1).contract_number := '5144117-Test Agreement';
        l_chrv_tbl_in(1).short_description := 'Test Agreement';
        

        okc_contract_pub.update_contract_header(p_api_version => l_api_version,
                                               p_init_msg_list => fnd_api.g_true,
                                               x_return_status => l_return_status,
                                               x_msg_count => l_msg_count,
                                               x_msg_data => l_msg_data,
                                               p_restricted_update => 'N',
                                               p_chrv_tbl => l_chrv_tbl_in,
                                               x_chrv_tbl => l_chrv_tbl_out);
        

        dbms_output.put_line('l_return_status: ' || l_return_status);
        

        IF ( l_return_status <> 'S' ) THEN
            dbms_output.put_line('Error while updating Contract Header ' || header_rec.contract_number);
            dbms_output.put_line('Error: ' || l_msg_data);
        ELSE
            dbms_output.put_line('Sucess: ');
            COMMIT;
        END IF;

    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(' Error while update Contract back to Enter status  ' || sqlerrm);
END;
/