--HSN And SAC Code Migration
CREATE TABLE XXJG.JG_HSN_REPORTING_CODES
(
REPORTING_CODE VARCHAR2(30 BYTE),
REPORTING_CODE_DESCRIPTION VARCHAR2(1500 BYTE),
EFFECTIVE_FROM DATE,
STATUS VARCHAR2(30 BYTE),
ERROR_MSG VARCHAR2(240 BYTE)
)
/
CREATE OR REPLACE PUBLIC SYNONYM JG_HSN_REPORTING_CODES FOR XXJG.JG_HSN_REPORTING_CODES
/
CREATE TABLE XXJG.JG_SAC_REPORTING_CODES
(
REPORTING_CODE VARCHAR2(30 BYTE),
REPORTING_CODE_DESCRIPTION VARCHAR2(1500 BYTE),
EFFECTIVE_FROM DATE,
STATUS VARCHAR2(30 BYTE),
ERROR_MSG VARCHAR2(240 BYTE)
)
/
CREATE OR REPLACE PUBLIC SYNONYM JG_SAC_REPORTING_CODES FOR XXJG.JG_SAC_REPORTING_CODES
/
CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_HSN (ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) AS
L_REPORTING_TYPE_ID NUMBER;
L_EFFECTIVE_FROM DATE := '01-JUL-2017';
L_CREATED_BY NUMBER;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER := 01072017;
V_COUNT NUMBER;
CURSOR CUR_HSN
IS
SELECT ROWID,
TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
REPORTING_CODE_DESCRIPTION
FROM JG_HSN_REPORTING_CODES HSN
WHERE 1 = 1 ; -- AND REPORTING_CODE = '12131400';
BEGIN
BEGIN
L_CREATED_BY := NULL;
L_LAST_UPDATED_BY := NULL;
SELECT USER_ID, USER_ID
INTO L_CREATED_BY, L_LAST_UPDATED_BY
FROM APPS.FND_USER
WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
END;
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM APPS.JAI_REPORTING_TYPES
WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_HSN_CODE';
END;
FOR REC IN CUR_HSN
LOOP
V_COUNT := NULL;
BEGIN
SELECT COUNT (REPORTING_CODE)
INTO V_COUNT
FROM APPS.JAI_REPORTING_CODES
WHERE REPORTING_CODE = REC.REPORTING_CODE
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
END;
IF V_COUNT = 0
THEN
INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
REPORTING_TYPE_ID,
REPORTING_CODE,
REPORTING_CODE_DESCRIPTION,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
L_REPORTING_TYPE_ID,
REC.REPORTING_CODE,
REC.REPORTING_CODE_DESCRIPTION,
L_EFFECTIVE_FROM,
NULL,
SYSDATE,
L_CREATED_BY,
SYSDATE,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATED_BY,
NULL);
UPDATE JG_HSN_REPORTING_CODES
SET STATUS = 'COMPLETE', ERROR_MSG = NULL
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('SUCCESS');
ELSE
UPDATE JG_HSN_REPORTING_CODES
SET STATUS = 'ERROR',
ERROR_MSG =
'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('FAILED');
END IF;
COMMIT;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_SAC (ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) AS
L_REPORTING_TYPE_ID NUMBER;
L_EFFECTIVE_FROM DATE := '01-JUL-2017';
L_CREATED_BY NUMBER;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER := 01072017;
V_COUNT NUMBER;
CURSOR CUR_SAC
IS
SELECT ROWID,
TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
REPORTING_CODE_DESCRIPTION
FROM JG_SAC_REPORTING_CODES SAC
WHERE 1 = 1 ; -- AND REPORTING_CODE = '996633';
BEGIN
BEGIN
L_CREATED_BY := NULL;
L_LAST_UPDATED_BY := NULL;
SELECT USER_ID, USER_ID
INTO L_CREATED_BY, L_LAST_UPDATED_BY
FROM APPS.FND_USER
WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
END;
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM APPS.JAI_REPORTING_TYPES
WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_SAC_CODE_ITEM';
END;
FOR REC IN CUR_SAC
LOOP
V_COUNT := NULL;
BEGIN
SELECT COUNT (REPORTING_CODE)
INTO V_COUNT
FROM APPS.JAI_REPORTING_CODES
WHERE REPORTING_CODE = REC.REPORTING_CODE
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
END;
IF V_COUNT = 0
THEN
INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
REPORTING_TYPE_ID,
REPORTING_CODE,
REPORTING_CODE_DESCRIPTION,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
L_REPORTING_TYPE_ID,
REC.REPORTING_CODE,
REC.REPORTING_CODE_DESCRIPTION,
L_EFFECTIVE_FROM,
NULL,
SYSDATE,
L_CREATED_BY,
SYSDATE,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATED_BY,
NULL);
UPDATE JG_SAC_REPORTING_CODES
SET STATUS = 'COMPLETE', ERROR_MSG = NULL
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('SUCCESS');
ELSE
UPDATE JG_SAC_REPORTING_CODES
SET STATUS = 'ERROR',
ERROR_MSG =
'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('FAILED');
END IF;
COMMIT;
END LOOP;
END;
/
-----------------------------------------------------------------------------------------------------------
Manish Kumar T
(
REPORTING_CODE VARCHAR2(30 BYTE),
REPORTING_CODE_DESCRIPTION VARCHAR2(1500 BYTE),
EFFECTIVE_FROM DATE,
STATUS VARCHAR2(30 BYTE),
ERROR_MSG VARCHAR2(240 BYTE)
)
/
CREATE OR REPLACE PUBLIC SYNONYM JG_HSN_REPORTING_CODES FOR XXJG.JG_HSN_REPORTING_CODES
/
CREATE TABLE XXJG.JG_SAC_REPORTING_CODES
(
REPORTING_CODE VARCHAR2(30 BYTE),
REPORTING_CODE_DESCRIPTION VARCHAR2(1500 BYTE),
EFFECTIVE_FROM DATE,
STATUS VARCHAR2(30 BYTE),
ERROR_MSG VARCHAR2(240 BYTE)
)
/
CREATE OR REPLACE PUBLIC SYNONYM JG_SAC_REPORTING_CODES FOR XXJG.JG_SAC_REPORTING_CODES
/
CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_HSN (ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) AS
L_REPORTING_TYPE_ID NUMBER;
L_EFFECTIVE_FROM DATE := '01-JUL-2017';
L_CREATED_BY NUMBER;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER := 01072017;
V_COUNT NUMBER;
CURSOR CUR_HSN
IS
SELECT ROWID,
TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
REPORTING_CODE_DESCRIPTION
FROM JG_HSN_REPORTING_CODES HSN
WHERE 1 = 1 ; -- AND REPORTING_CODE = '12131400';
BEGIN
BEGIN
L_CREATED_BY := NULL;
L_LAST_UPDATED_BY := NULL;
SELECT USER_ID, USER_ID
INTO L_CREATED_BY, L_LAST_UPDATED_BY
FROM APPS.FND_USER
WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
END;
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM APPS.JAI_REPORTING_TYPES
WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_HSN_CODE';
END;
FOR REC IN CUR_HSN
LOOP
V_COUNT := NULL;
BEGIN
SELECT COUNT (REPORTING_CODE)
INTO V_COUNT
FROM APPS.JAI_REPORTING_CODES
WHERE REPORTING_CODE = REC.REPORTING_CODE
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
END;
IF V_COUNT = 0
THEN
INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
REPORTING_TYPE_ID,
REPORTING_CODE,
REPORTING_CODE_DESCRIPTION,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
L_REPORTING_TYPE_ID,
REC.REPORTING_CODE,
REC.REPORTING_CODE_DESCRIPTION,
L_EFFECTIVE_FROM,
NULL,
SYSDATE,
L_CREATED_BY,
SYSDATE,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATED_BY,
NULL);
UPDATE JG_HSN_REPORTING_CODES
SET STATUS = 'COMPLETE', ERROR_MSG = NULL
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('SUCCESS');
ELSE
UPDATE JG_HSN_REPORTING_CODES
SET STATUS = 'ERROR',
ERROR_MSG =
'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('FAILED');
END IF;
COMMIT;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE APPS.XXJG_REP_TYPE_SAC (ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER) AS
L_REPORTING_TYPE_ID NUMBER;
L_EFFECTIVE_FROM DATE := '01-JUL-2017';
L_CREATED_BY NUMBER;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_LOGIN NUMBER := 01072017;
V_COUNT NUMBER;
CURSOR CUR_SAC
IS
SELECT ROWID,
TRIM (REPLACE (REPORTING_CODE, CHR (13), NULL)) REPORTING_CODE,
TRIM (REPLACE (REPORTING_CODE_DESCRIPTION, CHR (13), NULL))
REPORTING_CODE_DESCRIPTION
FROM JG_SAC_REPORTING_CODES SAC
WHERE 1 = 1 ; -- AND REPORTING_CODE = '996633';
BEGIN
BEGIN
L_CREATED_BY := NULL;
L_LAST_UPDATED_BY := NULL;
SELECT USER_ID, USER_ID
INTO L_CREATED_BY, L_LAST_UPDATED_BY
FROM APPS.FND_USER
WHERE 1 = 1 AND UPPER (USER_NAME) = 'XYZAGM';
END;
BEGIN
L_REPORTING_TYPE_ID := NULL;
SELECT REPORTING_TYPE_ID
INTO L_REPORTING_TYPE_ID
FROM APPS.JAI_REPORTING_TYPES
WHERE 1 = 1 AND REPORTING_TYPE_CODE = 'GST_SAC_CODE_ITEM';
END;
FOR REC IN CUR_SAC
LOOP
V_COUNT := NULL;
BEGIN
SELECT COUNT (REPORTING_CODE)
INTO V_COUNT
FROM APPS.JAI_REPORTING_CODES
WHERE REPORTING_CODE = REC.REPORTING_CODE
AND REPORTING_TYPE_ID = L_REPORTING_TYPE_ID;
END;
IF V_COUNT = 0
THEN
INSERT INTO APPS.JAI_REPORTING_CODES (REPORTING_CODE_ID,
REPORTING_TYPE_ID,
REPORTING_CODE,
REPORTING_CODE_DESCRIPTION,
EFFECTIVE_FROM,
EFFECTIVE_TO,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LAST_UPDATED_BY,
RECORD_TYPE_CODE)
VALUES (JAI_REPORTING_CODES_S.NEXTVAL,
L_REPORTING_TYPE_ID,
REC.REPORTING_CODE,
REC.REPORTING_CODE_DESCRIPTION,
L_EFFECTIVE_FROM,
NULL,
SYSDATE,
L_CREATED_BY,
SYSDATE,
L_LAST_UPDATE_LOGIN,
L_LAST_UPDATED_BY,
NULL);
UPDATE JG_SAC_REPORTING_CODES
SET STATUS = 'COMPLETE', ERROR_MSG = NULL
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('SUCCESS');
ELSE
UPDATE JG_SAC_REPORTING_CODES
SET STATUS = 'ERROR',
ERROR_MSG =
'REPORTING CODE IS ALREADY EXISTS IN BASE TABLE APPS.JAI_REPORTING_CODES'
WHERE ROWID = REC.ROWID;
DBMS_OUTPUT.PUT_LINE ('FAILED');
END IF;
COMMIT;
END LOOP;
END;
/
-----------------------------------------------------------------------------------------------------------Manish Kumar T