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';

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".