Nov 12, 2010

How to Create Item Replenishment Scripts

*******************************************************************************************************
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:

OraApps Search

Custom Search

Search This Blog