Introduction: We have a requirement when the new pricelist has been created that needs to be updated in the customer master account level and Bill To and Ship To level, blow code will help to achieve that process.
How do we solve:
DECLARE
p_curr_prc_list NUMBER;
p_update_prc_list NUMBER;
p_customer NUMBER;
lv_cur_pl VARCHAR2 (240);
ln_count NUMBER;
g_request_id NUMBER := fnd_profile.VALUE (‘CONC_REQUEST_ID’);
g_resp_id NUMBER := fnd_profile.VALUE (‘RESP_ID’);
g_appl_id NUMBER := fnd_profile.VALUE (‘RESP_APPL_ID’);
g_userid NUMBER := fnd_profile.VALUE (‘USER_ID’);
x_return_status VARCHAR2 (10);
x_msg_count NUMBER (10);
x_msg_data VARCHAR2 (1200);
v_object_version_number NUMBER (10);
lv_update_prc_list VARCHAR2 (1200);
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
BEGIN
fnd_global.apps_initialize (g_userid, g_resp_id, g_appl_id);
–mo_global.set_policy_context (‘S’, 3);
fnd_file.put_line (fnd_file.LOG,
‘Current Price List :’ || p_curr_prc_list);
fnd_file.put_line (fnd_file.LOG,
‘Update To Price List :’ || p_update_prc_list
);
fnd_file.put_line (fnd_file.LOG, ‘Customer Name :’ || p_customer);
–Fetch the Update Price List Name
BEGIN
SELECT NAME
INTO lv_update_prc_list
FROM qp_list_headers
WHERE list_header_id = p_update_prc_list AND list_type_code = ‘PRL’;
EXCEPTION
WHEN OTHERS
THEN
lv_update_prc_list := ‘NA’;
END;
IF p_customer IS NULL AND p_curr_prc_list IS NULL
THEN
— Either Customer anmd Current Price List Parameter is Mandatory -If the both is null then program will complete error
fnd_file.put_line (fnd_file.output,
‘Current Price List :’ || p_curr_prc_list
);
fnd_file.put_line (fnd_file.output,
‘Update To Price List :’ || p_update_prc_list
);
fnd_file.put_line (fnd_file.output, ‘Customer Name :’ || p_customer);
fnd_file.put_line
(fnd_file.LOG,
‘Any one of the Parameter Current Price List Name or Customer Name Should Have a Value’
);
fnd_file.put_line
(fnd_file.output,
‘Any one of the Parameter Current Price List Name or Customer Name Should Have a Value’
);
raise_application_error
(-20004,
‘Any one of the Parameter Current Price List Name or Customer Name Should Have a Value’
);
ELSE
BEGIN
fnd_file.put_line (fnd_file.output, ‘Parameter Validation Passed’);
IF p_curr_prc_list IS NOT NULL
AND p_curr_prc_list = p_update_prc_list
THEN
fnd_file.put_line (fnd_file.output,
‘Current Price List :’ || p_curr_prc_list
);
fnd_file.put_line (fnd_file.output,
‘Update To Price List :’ || p_update_prc_list
);
fnd_file.put_line (fnd_file.output,
‘Customer Name :’ || p_customer
);
fnd_file.put_line
(fnd_file.LOG,
‘Current Price List Name and Update To Price List Name Have a Same Value’
);
fnd_file.put_line
(fnd_file.output,
‘Current Price List Name and Update To Price List Name Have a Same Value’
);
raise_application_error
(-20004,
‘Current Price List Name and Update To Price List Name Have a Same Value’
);
END IF;
BEGIN
SELECT NAME
INTO lv_cur_pl
FROM qp_list_headers
WHERE list_header_id = p_curr_prc_list AND list_type_code = ‘PRL’;
EXCEPTION
WHEN OTHERS
THEN
lv_cur_pl := ‘NA’;
END;
fnd_file.put_line (fnd_file.LOG,
‘Current Price List Name :’ || lv_cur_pl
);
— Customer Parameter and Current Pricle List Parameter have a Value
IF p_curr_prc_list IS NOT NULL AND p_customer IS NOT NULL
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Current price list parameter have value and customer name have value,drive with current price list and customer name’
);
ln_count := 0;
fnd_file.put_line
(fnd_file.LOG,
‘********************************************************************************************************************************’
);
fnd_file.put_line (fnd_file.LOG, ‘Customer Account Level Update’);
ln_count := 0;
FOR cust_acc_rec IN (SELECT hz.party_id, hz.party_name,
hca.cust_account_id,
hca.account_number, hca.price_list_id,
qph.NAME, hca.object_version_number
FROM hz_parties hz,
hz_cust_accounts hca,
qp_list_headers qph
WHERE hz.party_id = hca.party_id
AND hca.account_number = p_customer
AND hca.price_list_id = p_curr_prc_list
AND hca.price_list_id = qph.list_header_id
AND hca.price_list_id IS NOT NULL)
LOOP
fnd_file.put_line
(fnd_file.LOG,
‘————————————————————‘
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Name :’ || cust_acc_rec.party_name
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Number :’
|| cust_acc_rec.account_number
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Current Price List :’
|| cust_acc_rec.NAME
);
fnd_file.put_line (fnd_file.LOG,
‘Update to Price List :’
|| lv_update_prc_list
);
ln_count := ln_count + 1;
p_cust_account_rec.cust_account_id :=
cust_acc_rec.cust_account_id;
p_cust_account_rec.price_list_id := p_update_prc_list;
BEGIN
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 => cust_acc_rec.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
fnd_file.put_line (fnd_file.LOG,
‘Customer is Updated Successful’
);
ELSE
fnd_file.put_line (fnd_file.LOG,
‘Customer Update 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’
);
fnd_file.put_line (fnd_file.LOG,
i || ‘) ‘ || x_msg_data
);
END LOOP;
END IF;
END;
fnd_file.put_line
(fnd_file.LOG,
‘————————————————————‘
);
END LOOP;
fnd_file.put_line
(fnd_file.LOG,
‘Total Records Updated in Customer Account Level ‘
|| ln_count
);
fnd_file.put_line
(fnd_file.output,
‘Total Records Updated in Customer Account Level ‘
|| ln_count
);
COMMIT;
fnd_file.put_line
(fnd_file.LOG,
‘********************************************************************************************************************************’
);
FOR cust_site_rec IN
(SELECT 1 sno, hp.party_name customer_name,
hcaa.account_number account_number,
hcaa.cust_account_id cust_account_id,
qph.list_header_id, qph.NAME price_list_name,
hcsua.site_use_code, hcsua.LOCATION, hcsua.site_use_id,
hcsua.cust_acct_site_id,
hcsua.attribute2 price_list_name_2
FROM qp_list_headers qph,
apps.hz_cust_accounts_all hcaa,
apps.hz_cust_acct_sites_all hcasa,
apps.hz_cust_site_uses_all hcsua,
apps.hz_parties hp
WHERE 1 = 1
AND hcaa.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.price_list_id = qph.list_header_id
AND hcaa.party_id = hp.party_id
AND hcaa.account_number = p_customer
AND ( hcsua.price_list_id = p_curr_prc_list
OR NVL (hcsua.attribute2, ‘NA’) = lv_update_prc_list
)
AND ( hcsua.price_list_id IS NOT NULL
OR hcsua.attribute2 IS NOT NULL
))
LOOP
ln_count := ln_count + 1;
fnd_file.put_line
(fnd_file.LOG,
‘————————————————————‘
);
fnd_file.put_line (fnd_file.LOG,
‘Customer Name :’
|| cust_site_rec.customer_name
);
fnd_file.put_line (fnd_file.LOG,
‘Account Number :’
|| cust_site_rec.account_number
);
fnd_file.put_line (fnd_file.LOG,
‘Cust Account ID :’
|| cust_site_rec.cust_account_id
);
fnd_file.put_line (fnd_file.LOG,
‘Current Price List ID :’
|| cust_site_rec.list_header_id
);
fnd_file.put_line (fnd_file.LOG,
‘Current Price List :’
|| cust_site_rec.price_list_name
);
fnd_file.put_line (fnd_file.LOG,
‘Current Price List 2 ‘
|| cust_site_rec.price_list_name_2
);
fnd_file.put_line (fnd_file.LOG,
‘Site Use Code :’
|| cust_site_rec.site_use_code
);
fnd_file.put_line (fnd_file.LOG,
‘Site Use ID :’ || cust_site_rec.site_use_id
);
fnd_file.put_line (fnd_file.LOG,
‘Cust Acct Site ID :’
|| cust_site_rec.cust_acct_site_id
);
fnd_file.put_line (fnd_file.LOG,
‘Update to Price List :’
|| lv_update_prc_list
);
BEGIN
SELECT object_version_number
INTO v_object_version_number
FROM hz_cust_site_uses_all
WHERE site_use_code = cust_site_rec.site_use_code
AND site_use_id = cust_site_rec.site_use_id;
END;
IF cust_site_rec.price_list_name_2 IS NOT NULL
THEN
p_cust_site_use_rec.attribute2 := lv_update_prc_list;
ELSE
p_cust_site_use_rec.attribute2 := NULL;
END IF;
p_cust_site_use_rec.site_use_id := cust_site_rec.site_use_id;
p_cust_site_use_rec.cust_acct_site_id :=
cust_site_rec.cust_acct_site_id;
p_cust_site_use_rec.price_list_id := p_update_prc_list;
BEGIN
hz_cust_account_site_v2pub.update_cust_site_use
(p_init_msg_list => ‘T’,
p_cust_site_use_rec => p_cust_site_use_rec,
p_object_version_number => v_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 = ‘S’
THEN
apps.fnd_file.put_line (fnd_file.LOG,
‘ Price List Updated Status’
|| x_return_status
);
COMMIT;
ELSE
IF NVL (x_msg_count, 0) >= 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_file.put_line (fnd_file.LOG,
‘ Error Status ‘
|| x_return_status
);
apps.fnd_file.put_line (fnd_file.LOG,
‘ Error message ‘
|| x_msg_data
);
END LOOP;
ELSE
apps.fnd_file.put_line (fnd_file.LOG,
‘ Error message ‘
|| x_msg_data
);
apps.fnd_file.put_line (fnd_file.LOG,
‘x_msg_count’ || x_msg_count
);
END IF;
END IF;
END;
fnd_file.put_line
(fnd_file.LOG,
‘————————————————————‘
);
END LOOP;
COMMIT;
fnd_file.put_line
(fnd_file.LOG,
‘Total Records Updated in BILL TO/SHIP TO Level ‘
|| ln_count
);
fnd_file.put_line
(fnd_file.output,
‘Total Records Updated in BILL TO/SHIP TO Level ‘
|| ln_count
);
fnd_file.put_line
(fnd_file.LOG,
‘********************************************************************************************************************************’
);
END IF;
END;
NULL;
END IF;
END;
Conclusion: This code will fix that Price list in customer master and show the values in DBMS Output
