*******************************************************************************************************
How to Create STAGING TABLES SCRIPTS
*******************************************************************************************************
connect custom/custom@applin.world
DROP TABLE A02_PRE_REPLENISH_HEADERS_INT CASCADE CONSTRAINTS ;
CREATE TABLE A02_PRE_REPLENISH_HEADERS_INT (
ORGANIZATION_ID NUMBER (12),
PROCESS_MODE NUMBER (12),
PROCESS_STATUS NUMBER (12),
REPLENISHMENT_HEADER_ID NUMBER (12),
REPLENISHMENT_COUNT_NAME VARCHAR2 (12),
SUBINVENTORY_CODE VARCHAR2 (12),
COUNT_DATE DATE,
LAST_UPDATE_DATE DATE,
CREATION_DATE DATE,
CREATED_BY NUMBER (12),
LAST_UPDATED_BY NUMBER (12))
GRANT ALL ON A02_PRE_REPLENISH_HEADERS_INT TO APPS;
CONNECT APPS/APPS@APPLIN.WORLD
CREATE SYNONYM A02_PRE_REPLENISH_HEADERS_INT FOR CUSTOM.A02_PRE_REPLENISH_HEADERS_INT
COMMIT;
connect custom/custom@applin.world
DROP TABLE A02_PRE_REPLENISH_LINES_INT;
CREATE TABLE A02_PRE_REPLENISH_LINES_INT (
INVENTORY_ITEM_ID NUMBER (12),
ORGANIZATION_ID NUMBER (12),
COUNT_TYPE_CODE NUMBER (11),
COUNT_QUANTITY NUMBER (12),
COUNT_UOM_CODE VARCHAR2 (12),
REPLENISHMENT_LINE_ID NUMBER (12),
REPLENISHMENT_HEADER_ID NUMBER (12),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER (12),
CREATION_DATE DATE,
CREATED_BY NUMBER (12))
GRANT ALL ON A02_PRE_REPLENISH_LINES_INT TO APPS;
CONNECT APPS/APPS@APPLIN.WORLD
CREATE SYNONYM A02_PRE_REPLENISH_LINES_INT FOR CUSTOM.A02_PRE_REPLENISH_LINES_INT
COMMIT;
INSERT INTO A02_PRE_REPLENISH_HEADERS_INT
(REPLENISHMENT_HEADER_ID,
COUNT_DATE,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
REPLENISHMENT_COUNT_NAME,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
PROCESS_MODE,
PROCESS_STATUS)
values
(79,'16-SEP-05','16-SEP-05','16-SEP-05',1068,1068,'A02_REPLEN',204,'A01_REPLEN',2,2)
COMMIT;
INSERT INTO A02_PRE_REPLENISH_LINES_INT
(REPLENISHMENT_HEADER_ID,
REPLENISHMENT_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
COUNT_TYPE_CODE,
COUNT_QUANTITY,
COUNT_UOM_CODE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID)
VALUES
(79,1999,'16-SEP-05',1068,'16-SEP-05',1068,2,'555','Ea',9814,204)
COMMIT;
CREATE TABLE A02_REPLENISHMENT_ERRORS_INT (
PROCESSING_DATE DATE,
COLUMN_NAME VARCHAR2 (25),
ERROR_MESSG VARCHAR2 (200),
TABLE_NAME VARCHAR2 (25))
GRANT ALL ON A02_REPLENISHMENT_ERRORS_INT TO APPS;
CONNECT APPS/APPS@APPLIN.WORLD
CREATE SYNONYM A02_REPLENISHMENT_ERRORS_INT FOR CUSTOM.A02_REPLENISHMENT_ERRORS_INT
COMMIT;
ALTER TABLE A02_PRE_REPLENISH_LINES_INT ADD (PROCESS_FLAG VARCHAR2(10));
Control Files :
LOAD DATA
INFILE '/apps/visappl/cust/11.5.0/bin/A02_HEADER.dat'
APPEND INTO TABLE A02_PRE_REPLENISH_HEADERS_INT
FIELDS TERMINATED BY ','
(ORGANIZATION_ID,
PROCESS_MODE,
PROCESS_STATUS,
REPLENISHMENT_HEADER_ID,
REPLENISHMENT_COUNT_NAME,
SUBINVENTORY_CODE,
COUNT_DATE,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY)
LOAD DATA
INFILE '/apps/visappl/cust/11.5.0/bin/A02_LINES.dat'
APPEND INTO TABLE A02_PRE_REPLENISH_LINES_INT
FIELDS TERMINATED BY ','
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
COUNT_TYPE_CODE,
COUNT_QUANTITY,
COUNT_UOM_CODE,
REPLENISHMENT_LINE_ID,
REPLENISHMENT_HEADER_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
Header Data File :
207,2,2,791,A02_RN,A01_REPLEN,21-AUG-2005,21-AUG-2005,21-AUG-2005,1068,1068,
207,2,2,801,A02_RN1,A01_REPLEN,21-AUG-2005,21-AUG-2005,21-AUG-2005,1068,1068,
207,2,2,811,A02_RN2,A01_REPLEN,21-AUG-2005,21-AUG-2005,21-AUG-2005,1068,1068,
Lines data File:
9814,207,2,555,Ea,1999,791,21-AUG-2005,1068,21-AUG-2005,1068,
9814,207,2,555,Ea,2000,801,21-AUG-2005,1068,21-AUG-2005,1068,
9814,207,2,555,Ea,2001,811,21-AUG-2005,1068,21-AUG-2005,1068,
Tables thats used in Replenishment
A02_PRE_REPLENISH_HEADERS_INT
A02_PRE_REPLENISH_LINES_INT
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
A02_REPLENISHMENT_ERRORS_INT
MTL_REPLENISH_HEADERS
MTL_REPLENISH_LINES
commit;
exec A02_REPLENISMENT_IMPORT.A02_MAIN_PROCEDURE;
INV.MTL_REPLENISH_LINES_INT
No comments:
Post a Comment