How to develop Item Replenishment interface Scripts

How to develop Item Replenishment interface  ?

How to  Create Staging Tables :
create table STAG_REPLENISH_HEADERS
(
ORGANIZATION_ID VARCHAR2(5),
 PROCESS_MODE    VARCHAR2(4),
 PROCESS_STATUS     VARCHAR(4),
 REPLENISHMENT_HEADER_ID VARCHAR2(5),
 REPLENISHMENT_COUNT_NAME VARCHAR(15),
 SUBINVENTORY_CODE VARCHAR2(5)
 COUNT_DATE date,               
 LAST_UPDATE_DATE  date,         
 CREATION_DATE  date,           
 CREATED_BY number,               
 LAST_UPDATED_BY  number       
 )



create table STAG_REPLENISH_LINES
(
 ORGANIZATION_ID VARCHAR2(5),
 INVENTORY_ITEM_ID VARCHAR2(5), 
 COUNT_TYPE_CODE VARCHAR(4),
 COUNT_QUANTITY     VARCHAR(7),
 COUNT_UOM_CODE     VARCHAR(4),
REPLENISHMENT_LINE_ID  VARCHAR2(4),
 REPLENISHMENT_HEADER_ID VARCHAR2(5),
 LAST_UPDATE_DATE date,         
 LAST_UPDATED_BY  number,       
 CREATION_DATE   date,          
 CREATED_BY   number             
)




How to  Create Control File :

load data
INFILE 'A04_REPLENISH.dat'
REPLACE
INTO TABLE A05_PRE_REPLENISH_HEADERS_INT
(ORGANIZATION_ID         POSITION(2:4) CHAR,
 PROCESS_MODE            POSITION(8:8) CHAR,
 PROCESS_STATUS            POSITION(12:12) CHAR,
 REPLENISHMENT_HEADER_ID    POSITION(16:19) CHAR,
 REPLENISHMENT_COUNT_NAME    POSITION(22:31) CHAR,
 SUBINVENTORY_CODE          POSITION(34:40) CHAR,
 COUNT_DATE                 SYSDATE,
 LAST_UPDATE_DATE           SYSDATE,
 CREATION_DATE              SYSDATE,
 CREATED_BY                 CONSTANT 1068 ,
 LAST_UPDATED_BY          CONSTANT 1068 )
INTO TABLE A05_PRE_REPLENISH_LINES_INT
(INVENTORY_ITEM_ID        POSITION(44:47) CHAR,
 ORGANIZATION_ID        POSITION(2:4)   CHAR,
 COUNT_TYPE_CODE        POSITION(50:50) CHAR,
 COUNT_QUANTITY            POSITION(54:55) CHAR,
 COUNT_UOM_CODE            POSITION(58:59) CHAR,
 REPLENISHMENT_LINE_ID      CONSTANT 1002,
 REPLENISHMENT_HEADER_ID    POSITION(16:19) CHAR,
 LAST_UPDATE_DATE           SYSDATE,
 LAST_UPDATED_BY          CONSTANT 1068,
 CREATION_DATE              SYSDATE,
 CREATED_BY                 CONSTANT 1068 )

                            ****************************************

        HOW TO DEVELOP REPLENISMENT INTERFACE DOCUMENTATION
                              ****************************************
                            

COUNT_TYPE_CODE
****************
1 Onhand quantity
2 Order quantity
3 Order maximum


PROCESS MODE
************
1 On line processing
2 Concurrent processing
3 Background processing


PROCESS_STATUS
*****************
1 Hold
2 Pending
3 Processing
4 Error
5 Completed

TABLES
*************************
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_HEADERS
MTL_PARAMETERS
MTL_SECONDARY_INVENTORIES

HOW TO FIND max(REPLENISHMENT_HEADER_ID
************************************
select max(REPLENISHMENT_HEADER_ID) from MTL_REPLENISH_HEADERS
64

HOW TO FIND organization_code
*************************
select organization_code from MTL_PARAMETERS where organization_id=207

HOW TO FIND SUBINVENTORY_CODE
***************************
SELECT SECONDARY_INVENTORY_NAME FROM MTL_SECONDARY_INVENTORIES WHERE organization_id=207


INSERT INTO
MTL_REPLENISH_HEADERS_INT
(REPLENISHMENT_HEADER_ID,
COUNT_DATE,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
REPLENISHMENT_COUNT_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
SUBINVENTORY_CODE,
PROCESS_MODE,
PROCESS_STATUS)
values
(67,'15-SEP-05','15-SEP-05','15-SEP-05',1068,1068,'A01_count',207,'M1','A01_SUB_IN',2,2)



MTL_REPLENISH_LINES

MTL_REPLENISH_LINES_INT


TO FIND MAX(REPLENISHMENT_LINE_ID)
**********************************
SELECT MAX(REPLENISHMENT_LINE_ID) FROM MTL_REPLENISH_LINES_INT
1002

TO FIND UOM_CODE
*****************
SELECT UOM_CODE FROM MTL_UNITS_OF_MEASURE_TL

INSERT INTO MTL_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)
VALUES
(67,1003,'15-SEP-05',1068,'15-SEP-05',1068,2,1345,'Ea',9812)


ERRORS
*******
1)  WHEN U ARE NOT ENTERING THE ITEM ID
     7    No Replenishment Lines are associated with Header
2)  10    Line(s) associated with this header failed validation

       LINES ERROR
    17    Item is not specified in the subinventory or is not min-max planned in the subinventory

     
 
WHEN U ARE NOT ENTERING SUBINV CONTROLLED ITEM
U GET ERROR (10)   
***********************************************
     INSERT INTO
MTL_REPLENISH_HEADERS_INT
(REPLENISHMENT_HEADER_ID,
COUNT_DATE,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
REPLENISHMENT_COUNT_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
SUBINVENTORY_CODE,
PROCESS_MODE,
PROCESS_STATUS)
values
(68,'15-SEP-05','15-SEP-05','15-SEP-05',1068,1068,'A01_CNT_1',207,'M1','A01_SUB_IN',2,2)


INSERT INTO MTL_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)
VALUES
(68,1003,'15-SEP-05',1068,'15-SEP-05',1068,2,1345,'Ea',9782)


WHEN ORDER MAXIMUM
*******************

INSERT INTO MTL_REPLENISH_HEADERS_INT
(REPLENISHMENT_HEADER_ID,
COUNT_DATE,
LAST_UPDATE_DATE,
CREATION_DATE,
 CREATED_BY,
LAST_UPDATED_BY,
REPLENISHMENT_COUNT_NAME,
ORGANIZATION_ID,
ORGANIZATION_CODE,
SUBINVENTORY_CODE,
PROCESS_MODE,
PROCESS_STATUS)
values
(76,'15-SEP-05','15-SEP-05','15-SEP-05',1068,1068,'A01_CNT_6',207,'M1','A01_SUB_IN',2,2)

COMMIT;

INSERT INTO MTL_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)
VALUES
(76,1004,'15-SEP-05',1068,'15-SEP-05',1068,3,'','Ea',9812)

COMMIT;


PO_REQUISITIONS_INTERFACE_ALL