#Tables Structure
CREATE TABLE LGM_SUPP_REG_HDR
(
OPERATING_UNIT VARCHAR2 (240),
PARTY_TYPE VARCHAR2 (240),
PARTY_NAME VARCHAR2 (240),
PARTY_NUMBER VARCHAR2 (240),
PARTY_SITE_NAME VARCHAR2 (240),
ITEM_CATEGORY VARCHAR2 (240),
TAX_CATEGORY_SA_INV VARCHAR2 (240),
STATUS VARCHAR2 (240),
ERROR_MSG VARCHAR2 (240)
)
/
CREATE OR REPLACE PUBLIC SYNONYM LGM_SUPP_REG_HDR FOR LGM_SUPP_REG_HDR
/
CREATE TABLE LGM_SUPP_REG_DTL
(
OPERATING_UNIT VARCHAR2 (240),
PARTY_TYPE VARCHAR2 (240),
PARTY_NAME VARCHAR2 (240),
PARTY_NUMBER VARCHAR2 (240),
PARTY_SITE_NAME VARCHAR2 (240),
REGIME_CODE VARCHAR2 (240),
PRIMARY_REG_NAME VARCHAR2 (240),
PRIMARY_REG_NUM VARCHAR2 (240),
SECONDARY_REG_NAME VARCHAR2 (240),
SECONDARY_REG_NUM VARCHAR2 (240),
ASSESSABLE_PRICE_LIST VARCHAR2 (240),
DEFAULT_SECTION VARCHAR2 (240),
START_DATE DATE,
END_DATE DATE,
STATUS VARCHAR2 (240),
ERROR_MSG VARCHAR2 (240)
)
/
CREATE OR REPLACE PUBLIC SYNONYM LGM_SUPP_REG_DTL FOR LGM_SUPP_REG_DTL
/
CREATE TABLE LGM_SUPP_REG_REP_CODE
(
OPERATING_UNIT VARCHAR2 (240),
PARTY_TYPE VARCHAR2 (240),
PARTY_NAME VARCHAR2 (240),
PARTY_NUMBER VARCHAR2 (240),
PARTY_SITE_NAME VARCHAR2 (240),
REGIME_CODE VARCHAR2 (240),
REPORTING_TYPE_NAME VARCHAR2 (240),
REPORTING_CODE VARCHAR2 (240),
REPORTED_CODE_DESC VARCHAR2 (240),
START_DATE DATE,
STATUS VARCHAR2 (240),
ERROR_MSG VARCHAR2 (240)
)
/
CREATE OR REPLACE PUBLIC SYNONYM LGM_SUPP_REG_REP_CODE FOR LGM_SUPP_REG_REP_CODE
/
#Procedure 1 (For TDS Details Entry - Child Program)
CREATE OR REPLACE PROCEDURE APPS.LGM_SUPP_3RD_PARTY_REG_TDS (
P_PARTY_NUM VARCHAR2)
IS
------------------------------TDS ONLY---------------------------------------
CURSOR HDR_TDS -- For inserting Data in Base Table
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
ITEM_CATEGORY,
TAX_CATEGORY_SA_INV
FROM LGM_SUPP_REG_HDR
WHERE 1 = 1
AND STATUS NOT LIKE '%E%'
AND PARTY_NUMBER = P_PARTY_NUM
AND ROWNUM = 1;
CURSOR DTL_TDS (P_PARTY_NUMBER VARCHAR2)
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
REGIME_CODE,
PRIMARY_REG_NAME,
PRIMARY_REG_NUM,
SECONDARY_REG_NAME,
SECONDARY_REG_NUM,
ASSESSABLE_PRICE_LIST,
DEFAULT_SECTION,
START_DATE,
END_DATE
FROM LGM_SUPP_REG_DTL
WHERE 1 = 1 AND PARTY_NUMBER = P_PARTY_NUMBER AND REGIME_CODE = 'TDS';
CURSOR REP_TDS (P_PARTY_NUMBER VARCHAR2)
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
REGIME_CODE,
REPORTING_TYPE_NAME,
REPORTING_CODE,
REPORTED_CODE_DESC,
START_DATE
FROM LGM_SUPP_REG_REP_CODE
WHERE 1 = 1 AND PARTY_NUMBER = P_PARTY_NUMBER AND REGIME_CODE = 'TDS';
V_COUNT NUMBER;
L_USER_ID NUMBER;
L_PARTY_ID NUMBER;
L_PARTY_SITE_ID NUMBER;
L_TAX_CATEGORY_ID NUMBER;
MAX_PARTY_REG_ID NUMBER;
L_REGIME_ID NUMBER;
L_PRIMARY_REG_NAME VARCHAR2 (240);
L_SECONDARY_REG_NAME VARCHAR2 (240);
L_REPORTING_TYPE_ID NUMBER;
L_REPORTING_CODE_ID NUMBER;
L_K_REGIME_ID NUMBER;
L_INSERT NUMBER;
------------------------------TDS ONLY---------------------------------------
BEGIN
V_COUNT := NULL;
L_USER_ID := NULL;
L_PARTY_ID := NULL;
L_PARTY_SITE_ID := NULL;
L_TAX_CATEGORY_ID := NULL;
MAX_PARTY_REG_ID := NULL;
L_REGIME_ID := NULL;
L_PRIMARY_REG_NAME := NULL;
L_SECONDARY_REG_NAME := NULL;
L_REPORTING_TYPE_ID := NULL;
L_REPORTING_CODE_ID := NULL;
L_K_REGIME_ID := NULL;
L_INSERT := NULL;
BEGIN
BEGIN
L_USER_ID := NULL;
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE 1 = 1 AND USER_NAME = 'BSLLGM';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_USER_ID := NULL;
DBMS_OUTPUT.PUT_LINE ('ERROR IN USER ID1');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR IN USER ID1');
RETURN;
WHEN OTHERS
THEN
L_USER_ID := NULL;
DBMS_OUTPUT.PUT_LINE ('ERROR IN USER ID2');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR IN USER ID2');
RETURN;
END;
BEGIN
FOR I IN HDR_TDS
LOOP
DBMS_OUTPUT.PUT_LINE (
'****************************TDS Record Starts Here****************************');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'****************************TDS Record Starts Here****************************');
DBMS_OUTPUT.PUT_LINE (
'Starting Loop For Party Number ' || I.PARTY_NUMBER);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Starting Loop For Party Number ' || I.PARTY_NUMBER);
--Supplier Party Number Validation
BEGIN
L_PARTY_ID := NULL;
SELECT VENDOR_ID
INTO L_PARTY_ID
FROM AP_SUPPLIERS
WHERE 1 = 1 AND SEGMENT1 = I.PARTY_NUMBER;
END;
BEGIN
V_COUNT := NULL;
SELECT COUNT (PARTY_ID)
INTO V_COUNT
FROM JAI_PARTY_REGS
WHERE 1 = 1
AND PARTY_ID = L_PARTY_ID
AND PARTY_SITE_ID IS NULL
AND ORG_ID IS NULL;
END;
IF V_COUNT > 0
THEN
DBMS_OUTPUT.PUT_LINE (
'Given Party Number '
|| I.PARTY_NUMBER
|| ' is already registered as TDS Only');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Given Party Number '
|| I.PARTY_NUMBER
|| ' is already registered as TDS Only');
END IF;
IF V_COUNT = 0
THEN
--Inserting Data in Header Table JAI_PARTY_REGS
DBMS_OUTPUT.PUT_LINE (
'Header Table JAI_PARTY_REGS Insertion is begin for TDS.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Header Table JAI_PARTY_REGS Insertion is begin for TDS.');
BEGIN
INSERT INTO JAI_PARTY_REGS (PARTY_REG_ID,
PARTY_TYPE_CODE,
SUPPLIER_FLAG,
CUSTOMER_FLAG,
SITE_FLAG,
PARTY_ID,
PARTY_SITE_ID,
ORG_CLASSIFICATION_CODE,
ITEM_CATEGORY_LIST,
INVOICE_TAX_CATEGORY_ID,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_PARTY_REGS_S.NEXTVAL,
'THIRD_PARTY_SITE',
'Y',
'N',
'N',
L_PARTY_ID,
NULL,
NULL,
NULL,
NULL,
1981, -- NULL,
SYSDATE,
L_USER_ID,
SYSDATE,
'252320177',
L_USER_ID,
'DEFINED');
END;
--STARTING DETAIL TABLES
FOR J IN DTL_TDS (I.PARTY_NUMBER)
LOOP
-- REGIME CODE VALIDATION
BEGIN
L_REGIME_ID := NULL;
SELECT REGIME_ID
INTO L_REGIME_ID
FROM JAI_REGIMES
WHERE 1 = 1
AND UPPER (REGIME_CODE) = UPPER (J.REGIME_CODE);
END;
IF J.PRIMARY_REG_NAME IS NOT NULL
THEN
BEGIN
L_PRIMARY_REG_NAME := NULL;
SELECT LOOKUP_CODE
INTO L_PRIMARY_REG_NAME
FROM FND_LOOKUP_VALUES
WHERE 1 = 1
AND LOOKUP_TYPE = 'JAI_REGISTRATION_TYPES'
AND UPPER (MEANING) =
UPPER (J.PRIMARY_REG_NAME);
END;
ELSE
L_PRIMARY_REG_NAME := NULL;
END IF;
IF J.SECONDARY_REG_NAME IS NOT NULL
THEN
BEGIN
L_SECONDARY_REG_NAME := NULL;
SELECT LOOKUP_CODE
INTO L_SECONDARY_REG_NAME
FROM FND_LOOKUP_VALUES
WHERE 1 = 1
AND LOOKUP_TYPE = 'JAI_REGISTRATION_TYPES'
AND UPPER (MEANING) =
UPPER (J.SECONDARY_REG_NAME);
END;
ELSE
L_SECONDARY_REG_NAME := NULL;
END IF;
DBMS_OUTPUT.PUT_LINE (
'Detail Table JAI_PARTY_REG_LINES Insertion is begin for TDS.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Detail Table JAI_PARTY_REG_LINES Insertion is begin for TDS.');
INSERT
INTO JAI_PARTY_REG_LINES (PARTY_REG_ID,
PARTY_REG_LINE_ID,
LINE_CONTEXT,
REGIME_ID,
REGISTRATION_TYPE_CODE,
REGISTRATION_NUMBER,
SEC_REGISTRATION_TYPE_CODE,
SECONDARY_REGISTRATION_NUMBER,
NUM_OF_RETURN_DAYS,
TAX_AUTHORITY_ID,
TAX_AUTHORITY_SITE_ID,
ASSESSABLE_PRICE_LIST_ID,
DEFAULT_SECTION_CODE,
EXEMPTION_TYPE,
EXEMPTION_NUM,
INTERCOMPANY_RECEIVABLE_CCID,
INTERCOMPANY_PAYABLE_CCID,
TRACKING_NUM,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_PARTY_REGS_S.CURRVAL,
JAI_PARTY_REG_LINES_S.NEXTVAL,
'REGISTRATIONS',
L_REGIME_ID,
L_PRIMARY_REG_NAME, -- J.PRIMARY_REG_NAME,
J.PRIMARY_REG_NUM,
L_SECONDARY_REG_NAME, -- J.SECONDARY_REG_NAME,
J.SECONDARY_REG_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'01-JUL-2017',
NULL,
SYSDATE,
L_USER_ID,
SYSDATE,
'252232702',
L_USER_ID,
'DEFINED');
END LOOP; -- ENDING LOOP J
FOR K IN REP_TDS (I.PARTY_NUMBER)
LOOP
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM JAI_REPORTING_TYPES
WHERE 1 = 1
AND UPPER (REPORTING_TYPE_NAME) =
UPPER (K.REPORTING_TYPE_NAME)
AND ROWNUM = 1;
END;
BEGIN
L_REPORTING_CODE_ID := NULL;
SELECT REPORTING_CODE_ID
INTO L_REPORTING_CODE_ID
FROM JAI_REPORTING_CODES
WHERE 1 = 1
AND UPPER (REPORTING_CODE) =
UPPER (K.REPORTING_CODE)
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID
AND ROWNUM = 1;
END;
BEGIN
L_K_REGIME_ID := NULL;
SELECT REGIME_ID
INTO L_K_REGIME_ID
FROM JAI_REGIMES
WHERE 1 = 1
AND UPPER (REGIME_CODE) = UPPER (K.REGIME_CODE);
END;
DBMS_OUTPUT.PUT_LINE (
'Reporting Associations Table JAI_REPORTING_ASSOCIATIONS Insertion is begin for TDS.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Reporting Associations Table JAI_REPORTING_ASSOCIATIONS Insertion is begin for TDS.');
INSERT
INTO JAI_REPORTING_ASSOCIATIONS (
REPORTING_ASSOCIATION_ID,
REPORTING_TYPE_ID,
REPORTING_CODE_ID,
REPORTING_TYPE_NAME,
REPORTING_USAGE,
REPORTING_CODE_DESCRIPTION,
REPORTING_CODE,
ENTITY_CODE,
ENTITY_ID,
ENTITY_SOURCE_TABLE,
REGIME_ID,
REGIME_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE,
STL_HDR_ID)
VALUES (JAI_REPORTING_ASSOCIATIONS_S.NEXTVAL,
L_REPORTING_TYPE_ID,
L_REPORTING_CODE_ID,
K.REPORTING_TYPE_NAME,
NULL,
K.REPORTED_CODE_DESC,
K.REPORTING_CODE,
'THIRD_PARTY',
JAI_PARTY_REGS_S.CURRVAL,
'JAI_PARTY_REGS',
L_K_REGIME_ID,
K.REGIME_CODE,
'01-JUL-2017',
NULL,
SYSDATE,
L_USER_ID,
SYSDATE,
'252320177',
L_USER_ID,
'DEFINED',
NULL);
END LOOP; -- ENDING LOOP K
END IF;
DBMS_OUTPUT.PUT_LINE (
'Ending Loop For Party Number ' || I.PARTY_NUMBER);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Ending Loop For Party Number ' || I.PARTY_NUMBER);
DBMS_OUTPUT.PUT_LINE (
'****************************TDS Record Ends Here****************************');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'****************************TDS Record Ends Here****************************');
END LOOP; -- ENDING LOOP I
END;
END;
END;
/
#Procedure 2 (For GST Details Entry) Parent Program
CREATE OR REPLACE PROCEDURE APPS.LGM_SUPP_3RD_PARTY_REG_D
IS
CURSOR HDR -- For Validations Only
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
ITEM_CATEGORY,
TAX_CATEGORY_SA_INV
FROM LGM_SUPP_REG_HDR where STATUS is null;
CURSOR HDR_1 -- For inserting Data in Base Table
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
ITEM_CATEGORY,
TAX_CATEGORY_SA_INV
FROM LGM_SUPP_REG_HDR
WHERE 1 = 1 AND STATUS is null;
CURSOR DTL (P_PARTY_NUMBER VARCHAR2,P_PARTY_SITE VARCHAR2)
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
REGIME_CODE,
PRIMARY_REG_NAME,
PRIMARY_REG_NUM,
SECONDARY_REG_NAME,
SECONDARY_REG_NUM,
ASSESSABLE_PRICE_LIST,
DEFAULT_SECTION,
START_DATE,
END_DATE
FROM LGM_SUPP_REG_DTL
WHERE 1 = 1 AND PARTY_NUMBER = P_PARTY_NUMBER and party_site_name=P_PARTY_SITE;
CURSOR REP (P_PARTY_NUMBER VARCHAR2,P_PARTY_SITE VARCHAR2)
IS
SELECT ROWID,
OPERATING_UNIT,
PARTY_TYPE,
PARTY_NAME,
PARTY_NUMBER,
PARTY_SITE_NAME,
REGIME_CODE,
REPORTING_TYPE_NAME,
REPORTING_CODE,
REPORTED_CODE_DESC,
START_DATE
FROM LGM_SUPP_REG_REP_CODE
WHERE 1 = 1 AND PARTY_NUMBER = P_PARTY_NUMBER and party_site_name=P_PARTY_SITE;
V_COUNT NUMBER;
L_USER_ID NUMBER;
L_PARTY_ID NUMBER;
L_PARTY_SITE_ID NUMBER;
L_TAX_CATEGORY_ID NUMBER;
MAX_PARTY_REG_ID NUMBER;
L_REGIME_ID NUMBER;
L_PRIMARY_REG_NAME VARCHAR2 (240);
L_SECONDARY_REG_NAME VARCHAR2 (240);
L_REPORTING_TYPE_ID NUMBER;
L_REPORTING_CODE_ID NUMBER;
L_K_REGIME_ID NUMBER;
L_INSERT NUMBER;
BEGIN
V_COUNT := NULL;
L_USER_ID := NULL;
L_PARTY_ID := NULL;
L_PARTY_SITE_ID := NULL;
L_TAX_CATEGORY_ID := NULL;
MAX_PARTY_REG_ID := NULL;
L_REGIME_ID := NULL;
L_PRIMARY_REG_NAME := NULL;
L_SECONDARY_REG_NAME := NULL;
L_REPORTING_TYPE_ID := NULL;
L_REPORTING_CODE_ID := NULL;
L_K_REGIME_ID := NULL;
L_INSERT := NULL;
BEGIN
L_USER_ID := NULL;
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE 1 = 1 AND USER_NAME = 'BSLLGM';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_USER_ID := NULL;
DBMS_OUTPUT.PUT_LINE ('ERROR IN USER ID1');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR IN USER ID1');
RETURN;
WHEN OTHERS
THEN
L_USER_ID := NULL;
DBMS_OUTPUT.PUT_LINE ('ERROR IN USER ID2');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR IN USER ID2');
RETURN;
END;
--Validation of Header Table Data Before inserting in Base Table
BEGIN
FOR I IN HDR
LOOP
--Supplier Party Number Validation
BEGIN
V_COUNT := NULL;
SELECT COUNT (VENDOR_ID)
INTO V_COUNT
FROM AP_SUPPLIERS
WHERE 1 = 1 AND SEGMENT1 = I.PARTY_NUMBER;
IF V_COUNT <> 1
THEN
DBMS_OUTPUT.PUT_LINE (
'Record found in error for given Party Number in AP_SUPPLIERS Table');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Record found in error for given Party Number in AP_SUPPLIERS Table');
DBMS_OUTPUT.PUT_LINE (
'Supplier Party Number Validation is fail.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Party Number Validation is fail.');
UPDATE LGM_SUPP_REG_HDR
SET STATUS = 'E',
ERROR_MSG = ERROR_MSG || ' - Invalid Party Number'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;
ELSIF V_COUNT = 1
THEN
/*UPDATE LGM_SUPP_REG_HDR
SET STATUS = STATUS || 'S'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;*/
null;
END IF;
END;
--Supplier Party Site Name Validation
BEGIN
V_COUNT := NULL;
SELECT COUNT (ASSA.VENDOR_SITE_ID)
INTO V_COUNT
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL ASSA
WHERE 1 = 1
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND APS.SEGMENT1 = I.PARTY_NUMBER
AND upper(ASSA.VENDOR_SITE_CODE) = upper(I.PARTY_SITE_NAME);
IF V_COUNT <> 1
THEN
DBMS_OUTPUT.PUT_LINE (
'Record found in error for given Party Number in AP_SUPPLIERS,AP_SUPPLIER_SITES_ALL Tables');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Record found in error for given Party Number in AP_SUPPLIERS,AP_SUPPLIER_SITES_ALL Tables');
DBMS_OUTPUT.PUT_LINE (
'Supplier Party Number Validation is fail.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Party Number Validation is fail.');
UPDATE LGM_SUPP_REG_HDR
SET STATUS = 'E',
ERROR_MSG = ERROR_MSG || ' - Invalid Party Site Name'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;
ELSIF V_COUNT = 1
THEN
/* UPDATE LGM_SUPP_REG_HDR
SET STATUS = STATUS || 'S'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;*/
null;
END IF;
END;
--Supplier Invoice Tax Category Standalone Validation
IF I.TAX_CATEGORY_SA_INV IS NOT NULL
THEN
BEGIN
V_COUNT := NULL;
SELECT COUNT (TAX_CATEGORY_ID)
INTO V_COUNT
FROM JAI_TAX_CATEGORIES
WHERE 1 = 1 AND upper(TAX_CATEGORY_NAME) = upper(I.TAX_CATEGORY_SA_INV);
IF V_COUNT <> 1
THEN
DBMS_OUTPUT.PUT_LINE (
'Record found in error for given Party Number in JAI_TAX_CATEGORIES Table');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Record found in error for given Party Number in JAI_TAX_CATEGORIES Table');
DBMS_OUTPUT.PUT_LINE (
'Supplier Invoice Tax Category Standalone Validation is fail.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Invoice Tax Category Standalone Validation is fail.');
UPDATE LGM_SUPP_REG_HDR
SET STATUS = 'E',
ERROR_MSG =
ERROR_MSG
|| ' - Invalid Invoice Tax Category Standalone'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;
ELSIF V_COUNT = 1
THEN
/* UPDATE LGM_SUPP_REG_HDR
SET STATUS = STATUS || 'S'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;*/
null;
END IF;
END;
END IF;
--Supplier Party Number Validation for existing in Base Table of JAI_PARTY_REGS
END LOOP; -- ENDING LOOP I
--Ending of Validation of Header Table Data Before inserting in Base Table
END;
BEGIN
FOR I IN HDR_1
LOOP
DBMS_OUTPUT.PUT_LINE (
'****************************New Record Starts Here****************************');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'****************************New Record Starts Here****************************');
DBMS_OUTPUT.PUT_LINE (
'Starting Loop For Party Number ' || I.PARTY_NUMBER);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Starting Loop For Party Number ' || I.PARTY_NUMBER);
BEGIN
V_COUNT := NULL;
SELECT COUNT (*)
INTO V_COUNT
FROM LGM_SUPP_REG_HDR HDR,
LGM_SUPP_REG_DTL DTL,
LGM_SUPP_REG_REP_CODE REP
WHERE 1 = 1
AND HDR.PARTY_NUMBER = DTL.PARTY_NUMBER
AND HDR.PARTY_NUMBER = REP.PARTY_NUMBER
AND HDR.PARTY_NUMBER = I.PARTY_NUMBER
AND DTL.REGIME_CODE = 'TDS';
END;
IF V_COUNT > 1 -- Checking for TDS registration required or not
THEN
DBMS_OUTPUT.PUT_LINE (
'Calling TDS Only Program For Party Number ' || I.PARTY_NUMBER);
LGM_SUPP_3RD_PARTY_REG_TDS (I.PARTY_NUMBER);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Calling TDS Only Program For Party Number ' || I.PARTY_NUMBER);
BEGIN
null;-- LGM_SUPP_3RD_PARTY_REG_TDS (I.PARTY_NUMBER); --Calling child procedure for TDS entry only
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (
'Child Program for TDS is failed to run.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Child Program for TDS is failed to run.');
END;
END IF;
DBMS_OUTPUT.PUT_LINE (
'****************************GST Record Starts Here****************************');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'****************************GST Record Starts Here****************************');
--Supplier Party Number Validation
BEGIN
L_PARTY_ID := NULL;
SELECT VENDOR_ID
INTO L_PARTY_ID
FROM AP_SUPPLIERS
WHERE 1 = 1 AND SEGMENT1 = I.PARTY_NUMBER;
DBMS_OUTPUT.PUT_LINE (
'Party ID for given party number '
|| I.PARTY_NUMBER
|| ' is '
|| L_PARTY_ID);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Party ID for given party number '
|| I.PARTY_NUMBER
|| ' is '
|| L_PARTY_ID);
DBMS_OUTPUT.PUT_LINE (
'Supplier Party Number Validation is success.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Party Number Validation is success.');
EXCEPTION
WHEN OTHERS
THEN
L_PARTY_ID := NULL;
END;
--Supplier Party Site Name Validation
BEGIN
L_PARTY_SITE_ID := NULL;
SELECT ASSA.VENDOR_SITE_ID
INTO L_PARTY_SITE_ID
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL ASSA
WHERE 1 = 1
AND APS.VENDOR_ID = ASSA.VENDOR_ID
AND APS.SEGMENT1 = I.PARTY_NUMBER
AND upper(ASSA.VENDOR_SITE_CODE) = upper(I.PARTY_SITE_NAME);
DBMS_OUTPUT.PUT_LINE (
'Party Site ID for given party number '
|| I.PARTY_NUMBER
|| ' is '
|| L_PARTY_SITE_ID);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Party Site ID for given party number '
|| I.PARTY_NUMBER
|| ' is '
|| L_PARTY_SITE_ID);
DBMS_OUTPUT.PUT_LINE (
'Supplier Party Site Name Validation is success.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Party Site Name Validation is success.');
EXCEPTION
WHEN OTHERS
THEN
L_PARTY_SITE_ID := NULL;
END;
--Supplier Invoice Tax Category Standalone Validation
BEGIN
L_TAX_CATEGORY_ID := NULL;
SELECT TAX_CATEGORY_ID
INTO L_TAX_CATEGORY_ID
FROM JAI_TAX_CATEGORIES
WHERE 1 = 1 AND upper(TAX_CATEGORY_NAME) = upper(I.TAX_CATEGORY_SA_INV);
DBMS_OUTPUT.PUT_LINE (
'Tax Invoice Category ID for given party number '
|| I.TAX_CATEGORY_SA_INV
|| ' is '
|| L_TAX_CATEGORY_ID);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Tax Invoice Category ID for given party number '
|| I.TAX_CATEGORY_SA_INV
|| ' is '
|| L_TAX_CATEGORY_ID);
DBMS_OUTPUT.PUT_LINE (
'Supplier Party Site Name Validation is success.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Supplier Party Site Name Validation is success.');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
L_TAX_CATEGORY_ID := NULL;
WHEN OTHERS
THEN
L_TAX_CATEGORY_ID := NULL;
END;
BEGIN
V_COUNT := NULL;
SELECT COUNT (PARTY_ID)
INTO V_COUNT
FROM JAI_PARTY_REGS
WHERE 1 = 1
AND PARTY_ID = L_PARTY_ID
AND PARTY_SITE_ID = L_PARTY_SITE_ID;
END;
IF V_COUNT > 0
THEN
DBMS_OUTPUT.PUT_LINE (
'Given Party Number with Party Site'
|| I.PARTY_NUMBER
|| ' is already registered as GST');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Given Party Number with Party Site'
|| I.PARTY_NUMBER
|| ' is already registered as GST');
UPDATE LGM_SUPP_REG_HDR
SET STATUS = STATUS || 'E',
ERROR_MSG = 'This Supplier and SIte already registered.'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;
END IF;
IF V_COUNT = 0
THEN
--Inserting Data in Header Table JAI_PARTY_REGS
DBMS_OUTPUT.PUT_LINE (
'Header Table JAI_PARTY_REGS Insertion is begin for GST.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Header Table JAI_PARTY_REGS Insertion is begin for GST.');
BEGIN
INSERT INTO JAI_PARTY_REGS (PARTY_REG_ID,
PARTY_TYPE_CODE,
SUPPLIER_FLAG,
CUSTOMER_FLAG,
SITE_FLAG,
PARTY_ID,
PARTY_SITE_ID,
ORG_CLASSIFICATION_CODE,
ITEM_CATEGORY_LIST,
INVOICE_TAX_CATEGORY_ID,
ORG_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_PARTY_REGS_S.NEXTVAL,
'THIRD_PARTY_SITE',
'Y',
'N',
'Y',
L_PARTY_ID,
L_PARTY_SITE_ID,
NULL,
I.ITEM_CATEGORY,
L_TAX_CATEGORY_ID,
1891,
SYSDATE,
L_USER_ID,
SYSDATE,
'252320177',
L_USER_ID,
'DEFINED');
END;
BEGIN
UPDATE LGM_SUPP_REG_HDR
SET STATUS = 'S'
WHERE 1 = 1 AND PARTY_NUMBER = I.PARTY_NUMBER;
END;
--STARTING DETAIL TABLES
FOR J IN DTL (I.PARTY_NUMBER,I.PARTY_SITE_NAME)
LOOP
-- REGIME CODE VALIDATION
BEGIN
L_REGIME_ID := NULL;
SELECT REGIME_ID
INTO L_REGIME_ID
FROM JAI_REGIMES
WHERE 1 = 1
AND UPPER (REGIME_CODE) = UPPER (J.REGIME_CODE);
END;
IF J.PRIMARY_REG_NAME IS NOT NULL
THEN
BEGIN
L_PRIMARY_REG_NAME := NULL;
SELECT LOOKUP_CODE
INTO L_PRIMARY_REG_NAME
FROM FND_LOOKUP_VALUES
WHERE 1 = 1
AND LOOKUP_TYPE = 'JAI_REGISTRATION_TYPES'
AND UPPER (MEANING) = UPPER (J.PRIMARY_REG_NAME);
END;
ELSE
L_PRIMARY_REG_NAME := NULL;
END IF;
IF J.SECONDARY_REG_NAME IS NOT NULL
THEN
BEGIN
L_SECONDARY_REG_NAME := NULL;
SELECT LOOKUP_CODE
INTO L_SECONDARY_REG_NAME
FROM FND_LOOKUP_VALUES
WHERE 1 = 1
AND LOOKUP_TYPE = 'JAI_REGISTRATION_TYPES'
AND UPPER (MEANING) =
UPPER (J.SECONDARY_REG_NAME);
END;
ELSE
L_SECONDARY_REG_NAME := NULL;
END IF;
DBMS_OUTPUT.PUT_LINE ('Insertion is begin in DTL.');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG,
'Insertion is begin in DTL.');
DBMS_OUTPUT.PUT_LINE (
'Detail Table JAI_PARTY_REG_LINES Insertion is begin for GST.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Detail Table JAI_PARTY_REG_LINES Insertion is begin for GST.');
INSERT
INTO JAI_PARTY_REG_LINES (PARTY_REG_ID,
PARTY_REG_LINE_ID,
LINE_CONTEXT,
REGIME_ID,
REGISTRATION_TYPE_CODE,
REGISTRATION_NUMBER,
SEC_REGISTRATION_TYPE_CODE,
SECONDARY_REGISTRATION_NUMBER,
NUM_OF_RETURN_DAYS,
TAX_AUTHORITY_ID,
TAX_AUTHORITY_SITE_ID,
ASSESSABLE_PRICE_LIST_ID,
DEFAULT_SECTION_CODE,
EXEMPTION_TYPE,
EXEMPTION_NUM,
INTERCOMPANY_RECEIVABLE_CCID,
INTERCOMPANY_PAYABLE_CCID,
TRACKING_NUM,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_PARTY_REGS_S.CURRVAL,
JAI_PARTY_REG_LINES_S.NEXTVAL,
'REGISTRATIONS',
L_REGIME_ID,
L_PRIMARY_REG_NAME, -- J.PRIMARY_REG_NAME,
J.PRIMARY_REG_NUM,
L_SECONDARY_REG_NAME, -- J.SECONDARY_REG_NAME,
J.SECONDARY_REG_NUM,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'01-JUL-2017',
NULL,
SYSDATE,
L_USER_ID,
SYSDATE,
'252232702',
L_USER_ID,
'DEFINED');
END LOOP; -- ENDING LOOP J
FOR K IN REP (I.PARTY_NUMBER,I.PARTY_SITE_NAME)
LOOP
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM JAI_REPORTING_TYPES
WHERE 1 = 1
AND UPPER (REPORTING_TYPE_NAME) =
UPPER (K.REPORTING_TYPE_NAME)
AND ROWNUM = 1;
END;
BEGIN
L_REPORTING_CODE_ID := NULL;
SELECT REPORTING_CODE_ID
INTO L_REPORTING_CODE_ID
FROM JAI_REPORTING_CODES
WHERE 1 = 1
AND UPPER (REPORTING_CODE) =
UPPER (K.REPORTING_CODE)
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID
AND ROWNUM = 1;
END;
BEGIN
L_K_REGIME_ID := NULL;
SELECT REGIME_ID
INTO L_K_REGIME_ID
FROM JAI_REGIMES
WHERE 1 = 1
AND UPPER (REGIME_CODE) = UPPER (K.REGIME_CODE);
END;
DBMS_OUTPUT.PUT_LINE ('Insertion is begin in REP.');
APPS.FND_FILE.PUT_LINE (FND_FILE.LOG,
'Insertion is begin in REP.');
DBMS_OUTPUT.PUT_LINE (
'Reporting Associations Table JAI_REPORTING_ASSOCIATIONS Insertion is begin for GST.');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Reporting Associations Table JAI_REPORTING_ASSOCIATIONS Insertion is begin for GST.');
INSERT
INTO JAI_REPORTING_ASSOCIATIONS (REPORTING_ASSOCIATION_ID,
REPORTING_TYPE_ID,
REPORTING_CODE_ID,
REPORTING_TYPE_NAME,
REPORTING_USAGE,
REPORTING_CODE_DESCRIPTION,
REPORTING_CODE,
ENTITY_CODE,
ENTITY_ID,
ENTITY_SOURCE_TABLE,
REGIME_ID,
REGIME_CODE,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE,
STL_HDR_ID)
VALUES (JAI_REPORTING_ASSOCIATIONS_S.NEXTVAL,
L_REPORTING_TYPE_ID,
L_REPORTING_CODE_ID,
K.REPORTING_TYPE_NAME,
NULL,
K.REPORTED_CODE_DESC,
K.REPORTING_CODE,
'THIRD_PARTY',
JAI_PARTY_REGS_S.CURRVAL,
'JAI_PARTY_REGS',
L_K_REGIME_ID,
K.REGIME_CODE,
'01-JUL-2017',
NULL,
SYSDATE,
L_USER_ID,
SYSDATE,
'252320177',
L_USER_ID,
'DEFINED',
NULL);
END LOOP; -- ENDING LOOP K
END IF;
DBMS_OUTPUT.PUT_LINE (
'Ending Loop For Party Number ' || I.PARTY_NUMBER);
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Ending Loop For Party Number ' || I.PARTY_NUMBER);
DBMS_OUTPUT.PUT_LINE (
'****************************GST Record Ends Here****************************');
APPS.FND_FILE.PUT_LINE (
FND_FILE.LOG,
'****************************GST Record Ends Here****************************');
END LOOP; -- ENDING LOOP I -- ENDING LOOP I
END;
COMMIT;
END;
/
-----------------------------------------------------------------------------------------------------------
Manish Kumar
