create or replace procedure xx_Thirdpart_reg_validation(P_CUSTOMER varchar2)
as
LV_ERR_MSG VARCHAR2 (4000);
LV_STATUS_FLAG VARCHAR2 (1);
LV_CUSTOMER_ID NUMBER;
LV_SITE_USE_ID NUMBER;
LV_GST_NUM VARCHAR2 (20);
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_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 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 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||’-‘||’Bill TO Customer id :’|| LV_CUSTOMER_ID ||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
END;
BEGIN
SELECT DISTINCT TRIM (TO_CHAR (B.REGISTRATION_NUMBER)) GST_NUM
INTO LV_GST_NUM
FROM JAI_PARTY_REGS A
,JAI_PARTY_REG_LINES B
WHERE 1 = 1
AND A.PARTY_REG_ID = B.PARTY_REG_ID
AND A.PARTY_TYPE_CODE = ‘THIRD_PARTY_SITE’
AND B.REGIME_ID = 10000
AND B.REGISTRATION_TYPE_CODE = ‘GST’
AND A.PARTY_ID = LV_CUSTOMER_ID
AND A.PARTY_SITE_ID = LV_SITE_USE_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
LV_STATUS_FLAG := ‘E’;
LV_ERR_MSG := LV_ERR_MSG||’-‘||’Invalid GST Customer : ‘||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||’-‘||’Customer Is Not Registered :’||LV_CUSTOMER_ID||SQLCODE||’-‘||SQLERRM;
APPS.FND_FILE.PUT_LINE (APPS.FND_FILE.LOG, ‘-‘||LV_ERR_MSG);
END;
end;
