create or replace procedure xx_shipto_site_validation(P_CUSTOMER varchar2)
as
LV_ERR_MSG VARCHAR2 (4000);
LV_STATUS_FLAG VARCHAR2 (1);
LV_CUSTOMER_ID NUMBER;
LV_SHIP_SITE_USE_ID NUMBER;
begin
BEGIN
SELECT CUSTOMER_ID
INTO LV_CUSTOMER_ID
FROM AR_CUSTOMERS ARC
WHERE ARC.CUSTOMER_NAME = P_CUSTOMER;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_STATUS_FLAG := ‘E’;
LV_ERR_MSG := LV_ERR_MSG||’-‘||’Invalid Customer : ‘||P_CUSTOMER||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
WHEN OTHERS THEN
LV_STATUS_FLAG := ‘E’;
LV_ERR_MSG := LV_ERR_MSG||’-‘||’Other Exception for Customer : ‘||P_CUSTOMER||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
END;
BEGIN
SELECT HCAS.CUST_ACCT_SITE_ID — hcsu.site_use_id
INTO LV_SHIP_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
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 = ‘SHIP_TO’
AND HCAS.STATUS = ‘A’
AND HCSU.STATUS = ‘A’
AND SHIP_TO_FLAG = ‘P’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_STATUS_FLAG := ‘E’;
LV_ERR_MSG := LV_ERR_MSG||’-‘||’Invalid Ship To Customer id :’||LV_CUSTOMER_ID||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
WHEN OTHERS THEN
LV_STATUS_FLAG := ‘E’;
LV_ERR_MSG :=
LV_ERR_MSG||’-‘||’Ship TO Customer Address id :’||LV_CUSTOMER_ID||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
END;
–******************************************************
end;
