INTRODUCTION
This blog explains how we validate Bill to customer in oracle apps
–******************************************************
— Bill To Customer Address Validation
–******************************************************
BEGIN
SELECT hcas.cust_acct_site_id –hcsu.site_use_id
INTO lv_site_use_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_cust_site_uses_all hcsu
WHERE hca.cust_account_id = lv_customer_id — 940301
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = ‘BILL_TO’
AND hcsu.LOCATION = lc_dsin_data.productgroup
AND hcas.status = ‘A’
AND hcsu.status = ‘A’
AND bill_to_flag = ‘P’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_status_flag := ‘E’;
lv_err_msg :=
lv_err_msg
|| ‘-‘
|| ‘Invalid Bill To Customer Address :’
|| lc_dsin_data.depot_name
|| SQLCODE
|| ‘-‘
|| SQLERRM;
apps.fnd_file.put_line (apps.fnd_file.LOG,
‘-‘ || lv_err_msg);
WHEN TOO_MANY_ROWS
THEN
BEGIN
SELECT MAX (hcas.cust_acct_site_id) –hcsu.site_use_id
INTO lv_site_use_id
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_party_sites hps,
hz_cust_site_uses_all hcsu
WHERE hca.cust_account_id = lv_customer_id — 940301
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.party_site_id = hps.party_site_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = ‘BILL_TO’
AND hcsu.LOCATION = lc_dsin_data.productgroup
AND hcas.status = ‘A’
AND hcsu.status = ‘A’
AND bill_to_flag = ‘P’;
EXCEPTION
WHEN OTHERS
THEN
lv_status_flag := ‘E’;
lv_err_msg :=
lv_err_msg
|| ‘-‘
|| ‘Invalid Bill To Customer Address :’
|| lc_dsin_data.depot_name
|| SQLCODE
|| ‘-‘
|| SQLERRM;
apps.fnd_file.put_line (apps.fnd_file.LOG,
‘-‘ || lv_err_msg
);
END;
WHEN OTHERS
THEN
lv_status_flag := ‘E’;
lv_err_msg :=
lv_err_msg
|| ‘-‘
|| ‘Bill TO Customer Address does not exists :’
|| lc_dsin_data.depot_name
|| SQLCODE
|| ‘-‘
|| SQLERRM;
apps.fnd_file.put_line (apps.fnd_file.LOG,
‘-‘ || lv_err_msg);
END;
