Tuesday, 22 July 2025

Script to update the customer profile (hz_customer_profile_v2pub.update_customer_profile)

SET SERVEROUTPUT ON;
DECLARE
    p_customer_profile_rec_type  hz_customer_profile_v2pub.customer_profile_rec_type;
    p_cust_account_profile_id    NUMBER;
    p_object_version_number      NUMBER;
    x_return_status              VARCHAR2(2000);
    x_msg_count                  NUMBER;
    x_msg_data                   VARCHAR2(2000);
BEGIN
    dbms_output.put_line('Start of API');
    mo_global.init('AR');
    fnd_global.apps_initialize(user_id => 233158,     --fnd_user.user_id
                               resp_id => 58900,         --US Receivables Manager
                               resp_appl_id => 222);
    mo_global.set_policy_context('S', 101);
    fnd_global.set_nls_context('AMERICAN');
    
-- Initializing the Mandatory API parameters
    p_customer_profile_rec_type.cust_account_profile_id := 2097166;             --HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID
    p_customer_profile_rec_type.statement_cycle_id := fnd_api.g_miss_num;       --AR_STATEMENT_CYCLES.STATEMENT_CYCLE_ID
    p_customer_profile_rec_type.send_statements := 'N';
    p_customer_profile_rec_type.dunning_letter_set_id := fnd_api.g_miss_num;    --AR_DUNNING_LETTER_SETS.DUNNING_LETTER_SET_ID   
    p_customer_profile_rec_type.dunning_letters := 'N';
    p_customer_profile_rec_type.credit_balance_statements := 'N';
    p_customer_profile_rec_type.credit_checking := 'N';
    p_customer_profile_rec_type.credit_hold := 'N';
    SELECT
        object_version_number
    INTO p_object_version_number
    FROM
        hz_customer_profiles
    WHERE
        cust_account_profile_id = 2097166;
    dbms_output.put_line('Calling the API hz_customer_profile_v2pub.update_customer_profile');
    hz_customer_profile_v2pub.update_customer_profile(p_init_msg_list => fnd_api.g_true,
                                                     p_customer_profile_rec => p_customer_profile_rec_type,
                                                     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('Updation of Customer Profile is Successful ');
    ELSE
        dbms_output.put_line('Updation of Customer Profile got 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('End of API');
END;
/

Query to find customer number and name from project

 SELECT
    ppa.project_id,
    ppa.segment1,
    ppa.name,
    hp.party_name,
    hca.account_number,
    hcp.credit_checking,
    hcp.credit_hold
FROM
    pa_projects_all         ppa,
    pa_project_customers    ppc,
    hz_cust_accounts        hca,
    hz_customer_profiles    hcp,
    hz_cust_acct_sites_all  hcasa,
    hz_parties              hp
WHERE
        1 = 1
    AND ppa.project_id = ppc.project_id
    AND ppc.customer_id = hca.cust_account_id
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND ppc.bill_to_address_id = hcasa.cust_acct_site_id
    AND hcp.site_use_id IS NULL
    AND ppa.segment1 = '1111111';

Query to find customer information from PA Project

  SELECT
    ppa.project_id,
    ppa.segment1,
    ppa.name,
    hp.party_name,
    hca.account_number,
    hcp.credit_checking,
    hcp.credit_hold,
    hl_ship.address1               ship_address1,
    hl_ship.address2               ship_address2,
    hl_ship.address3               ship_address3,
    hl_ship.address4               ship_address4,
    hl_ship.postal_code          ship_postal_code,
    hl_ship.city                       ship_city,
    hl_ship.state                      ship_state,
    hl_ship.province               ship_province,
    hl_ship.county                  ship_county,
    hl_bill.address1                bill_address1,
    hl_bill.address2                bill_address2,
    hl_bill.address3                bill_address3,
    hl_bill.address4                bill_address4,
    hl_bill.postal_code           bill_postal_code,
    hl_bill.city                        bill_city,
    hl_bill.state                       bill_state,
    hl_bill.province                bill_province,
    hl_bill.county                   bill_county
FROM
    pa_projects_all                ppa,
    pa_project_customers     ppc,
    hz_cust_accounts            hca,
    hz_customer_profiles      hcp,
    hz_cust_acct_sites_all     hcasa_bill,
    hz_party_sites                  hps_bill,
    hz_locations                     hl_bill,
    hz_cust_acct_sites_all     hcasa_ship,
    hz_party_sites                  hps_ship,
    hz_locations                     hl_ship,
    hz_parties                         hp
WHERE
        1 = 1
    AND ppa.project_id = ppc.project_id
    AND ppc.customer_id = hca.cust_account_id
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND ppc.bill_to_address_id = hcasa_bill.cust_acct_site_id
    AND hcasa_bill.party_site_id = hps_bill.party_site_id
    AND hps_bill.location_id = hl_bill.location_id
    AND ppc.ship_to_address_id = hcasa_ship.cust_acct_site_id
    AND hcasa_ship.party_site_id = hps_ship.party_site_id
    AND hps_ship.location_id = hl_ship.location_id
    AND hcp.site_use_id IS NULL
    AND ppa.segment1 = '1111111';

Monday, 21 July 2025

How to add last character (,) in notepad ++

1. Open file in Notepad++
2. Ctrl + H to open the Find and Replace dialog.
3. select Regular expression in Search Mode at the bottom.
4. Find what: $ 
5. Replace with: ,
6. Click "Replace All".

Monday, 30 June 2025

Query to find invoice number from credit memo

 SELECT
    rcta.customer_trx_id,
    rcta.trx_number,
    rcta.trx_date,
    rctta.name          transaction_type_name,
    rctta.type          transaction_type,
    rcta.previous_customer_trx_id,
    apsa.payment_schedule_id,
    apsa.class,
    araa.applied_customer_trx_id,
    araa.applied_payment_schedule_id,
    rctai.trx_number    invoice_number
FROM
    ra_customer_trx_all             rcta,
    ra_cust_trx_types_all           rctta,
    ar_payment_schedules_all        apsa,
    ar_receivable_applications_all  araa,
    ar_payment_schedules_all        apsai,
    ra_customer_trx_all             rctai
WHERE
        1 = 1
    AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
    AND rcta.org_id = rctta.org_id
    AND rcta.customer_trx_id = apsa.customer_trx_id
    AND rcta.org_id = apsa.org_id
    AND rcta.customer_trx_id = araa.customer_trx_id
    AND rcta.org_id = araa.org_id
    AND apsa.payment_schedule_id = araa.payment_schedule_id
    AND apsa.org_id = araa.org_id
    AND araa.applied_customer_trx_id = apsai.customer_trx_id
    AND araa.applied_payment_schedule_id = apsai.payment_schedule_id
    AND araa.org_id = apsai.org_id
    AND araa.applied_customer_trx_id = rctai.customer_trx_id
    AND araa.org_id = rctai.org_id
    AND rcta.trx_number = '111111111'
    AND apsa.class = 'CM';

Query to find order line number from sales order

 SELECT
    ooha.org_id,
    ooha.order_number,
    rtrim(oola.line_number|| '.'|| oola.shipment_number|| '.'|| oola.option_number|| '.'|| oola.component_number|| '.'|| oola.service_number,'.') line_number
FROM
    oe_order_headers_all  ooha,
    oe_order_lines_all    oola
WHERE
        1 = 1
    AND ooha.header_id = oola.header_id
    AND ooha.org_id = oola.org_id
    AND ooha.order_number = '11111111';

Thursday, 26 June 2025

Query to find receipt number from Invoice number (link between ra_customer_trx_all and r_cash_receipts_all)

 SELECT
    acra.receipt_number,
    rcta.trx_number
FROM
    ar_receivable_applications_all  araa,
    ar_cash_receipts_all            acra,
    ra_customer_trx_all             rcta
WHERE
        araa.status = 'APP'
    AND araa.cash_receipt_id = acra.cash_receipt_id
    AND araa.applied_customer_trx_id = rcta.customer_trx_id
    --AND rct.customer_trx_id IN (1111111 )
    AND rcta.trx_number = '1111111'
;