Pakage of Item Replenishment Import Interface....

CREATE OR REPLACE PACKAGE BODY A02_REPLENISMENT_IMPORT
  AS
--***********************************************************************************
      --Name                     :A02_REPLENISMENT_IMPORT
      --Package Name           :A02_REPLENISMENT_IMPORT
      --Created On             :20-SEP-2005
      --Created By             :
      --purpose                :Package Body to hold validation and transfer
      --                         data from staging table to interface tables.
--*************************************************************************************
--variabe_declaration
          intl_err_messg           varchar2(500);
          col_name_v          varchar2(20);
          A02_null_exception   EXCEPTION;
--procedure to store error massage
PROCEDURE err_messg(msg IN VARCHAR2) IS
       BEGIN
            intl_err_messg := msg;
END err_messg;
--procedure to check the nullity of not null column
PROCEDURE A02_NOT_NULL (COL_NAME   IN  VARCHAR2 , COL_VAL  IN  VARCHAR2)
         IS
        BEGIN
            IF COL_VAL IS NULL THEN
               err_messg('ERROR:: '||COL_NAME|| ' IS NULL||ACTION:PROVIDE A NOT NULL VALUE TO '||COL_NAME);
               COL_NAME_V :=COL_NAME ;
               RAISE A02_null_exception;
            END IF;
        END A02_NOT_NULL;
 PROCEDURE A02_NOT_NULL (COL_NAME   IN  VARCHAR2 , COL_VAL  IN  NUMBER)
         IS
        BEGIN
            IF COL_VAL IS NULL THEN
               err_messg('ERROR::'||COL_NAME|| ' IS NULL || ACTION:PROVIDE A NOT NULL VALUE TO '||COL_NAME);
               COL_NAME_V :=COL_NAME ;
               RAISE A02_null_exception;
            END IF;
        END A02_NOT_NULL;
PROCEDURE A02_NOT_NULL (COL_NAME   IN  VARCHAR2 , COL_VAL  IN  DATE)
         IS
        BEGIN
            IF COL_VAL IS NULL THEN
               err_messg('ERROR::'||COL_NAME|| 'IS NULL'||'ACTION:PROVIDE A NOT NULL VALUE TO '||COL_NAME);
               COL_NAME_V :=COL_NAME ;
               RAISE A02_null_exception;
            END IF;
        END A02_NOT_NULL;
--function to check the uniqueness of header id
FUNCTION UNIQUE_HEADER_ID  (REP_HEADER_ID  IN NUMBER) RETURN BOOLEAN
        IS
      VALCK  NUMBER(10);
     BEGIN
      SELECT REPLENISHMENT_HEADER_ID INTO VALCK FROM MTL_REPLENISH_HEADERS WHERE
                         REPLENISHMENT_HEADER_ID=REP_HEADER_ID ;
        IF VALCK IS NOT NULL THEN
            err_messg('ERROR::'||'REPLENISHMENT_HEADER_ID' || 'IS ALLREDY PRESENT'||'ACTION:PROVIDE A UNIQUE HEADER ID '||'REPLENISH_HEADER_ID');
            RETURN (FALSE);
          ELSE
         RETURN (TRUE);
        END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
             RETURN (TRUE);
      END  UNIQUE_HEADER_ID;
--function to check the uniqueness of REPLENISH_COUNT_NAME
FUNCTION UNIQUE_REPLENISH_COUNT_NAME  (REP_COUNT_NAME  IN VARCHAR2) RETURN BOOLEAN
           IS
           VALCK  varchar2(30);
         BEGIN
             SELECT REPLENISHMENT_COUNT_NAME INTO VALCK FROM MTL_REPLENISH_HEADERS WHERE
                         REPLENISHMENT_COUNT_NAME=REP_COUNT_NAME ;
           IF VALCK IS NOT NULL THEN
               err_messg('ERROR::'||'REPLENISHMENT_COUNT_NAME' || 'IS ALLREDY PRESENT'||'ACTION:PROVIDE A UNIQUE COUNT_NAME '||'REPLENISH_COUNT_NAME');
               RETURN (FALSE);
             ELSE
               RETURN (TRUE);
           END IF;
         EXCEPTION
             WHEN NO_DATA_FOUND THEN
               RETURN (TRUE);
       END  UNIQUE_REPLENISH_COUNT_NAME;
--TO CHECK THE VALIDITY OF SUBINVENTORY CODE
FUNCTION VALID_SUBINVENTORY  (REP_SUBINVENTORY IN VARCHAR2, REP_ORG_ID  IN NUMBER) RETURN BOOLEAN
             IS
             VALCK  varchar2(50);
         BEGIN
              SELECT SECONDARY_INVENTORY_NAME INTO VALCK FROM MTL_SECONDARY_INVENTORIES
                   WHERE ORGANIZATION_ID=REP_ORG_ID AND SECONDARY_INVENTORY_NAME=REP_SUBINVENTORY;
             IF VALCK IS NOT NULL THEN
               RETURN (true);
              ELSE
               err_messg('ERROR::'||'SECONDARY_INVENTORY_NAME' || 'IS NOT VALID'||'ACTION:PROVIDE A VALID SECONDARY_INVENTORY_NAME '||'SECONDARY_INVENTORY_NAME');
               RETURN (FALSE);
            END IF;
         EXCEPTION
             WHEN NO_DATA_FOUND THEN
               RETURN (FALSE);
      END  VALID_SUBINVENTORY ;
--to check the transactability of entered item
FUNCTION CK_ITEM_ENABILITY(item_id_v  NUMBER,REP_ORG_ID  NUMBER) RETURN BOOLEAN
            IS
        V_ITEM_ID               NUMBER(10);
        VALCK                   NUMBER(1);
        TRANS_CHK               VARCHAR2(1);
        not_transactable_item   EXCEPTION;
        invalid_item            EXCEPTION;
  BEGIN
         V_ITEM_ID:=item_id_v;
            SELECT 1 INTO VALCK FROM DUAL WHERE V_ITEM_ID
                                   IN(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B
                                                                  WHERE ORGANIZATION_ID=REP_ORG_ID);
            IF  VALCK=1  THEN
                  SELECT MTL_TRANSACTIONS_ENABLED_FLAG INTO TRANS_CHK FROM  MTL_SYSTEM_ITEMS_B
                             WHERE  INVENTORY_ITEM_ID= item_id_v AND ORGANIZATION_ID=REP_ORG_ID;
                IF TRANS_CHK='Y'  THEN
                        RETURN (TRUE);
                  ELSE
                err_messg('ERROR:: '||'ITEM_IS NOT TRANSACTABLE  ||  ACTION: PROVIDE AN TRANSACTABLE ITEM'|| 'INVENTORY_ITEM_ID');
                       RAISE  not_transactable_item;
                        RETURN (FALSE);
                END IF;
             ELSE
             err_messg('ERROR:: '||'ITEM_IS NOT VALID IN THE ORGANIZATION ||  ACTION: PROVIDE AN VALID ITEM'|| 'INVENTORY_ITEM_ID');
                       RAISE  invalid_item;
                        RETURN (FALSE);
            END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
            THEN
             err_messg('ERROR:: '||'ITEM_IS NOT VALID IN THE ORGANIZATION ||  ACTION: PROVIDE AN VALID ITEM'|| 'INVENTORY_ITEM_ID');
             RETURN FALSE;
  END  CK_ITEM_ENABILITY;
--MAIN PROCEDURE
PROCEDURE A02_MAIN_PROCEDURE
         IS
--VARIABLE DECLARATION
         ITEM_E_V  BOOLEAN;
       HEADER_ID_V                  BOOLEAN;
       COUNT_NAME_V                 BOOLEAN;
       V_ERRBUF                     VARCHAR2(10);
          V_RETCODE                    VARCHAR2(50);
          SUBINVENTORY_V            BOOLEAN;
         V_COUNT_COMPL                NUMBER;
          V_COUNT_ERROR                NUMBER;
          V_COUNT_LINES_COMP           NUMBER;
--CURSOR DECLARATION
       CURSOR C_HDR_STAG IS SELECT * FROM A02_PRE_REPLENISH_HEADERS_INT for update;
         C_HDR       A02_PRE_REPLENISH_HEADERS_INT%ROWTYPE;
       CURSOR C_LINES_STAG  IS SELECT * FROM A02_PRE_REPLENISH_LINES_INT for update;
       C_LINES     A02_PRE_REPLENISH_LINES_INT%ROWTYPE;
--EXCEPTION DECLARATION
       NON_UNIQUE_HDR                   EXCEPTION;
       NON_UNIQUE_COUNT_NAME           EXCEPTION;
       NO_LINES                             EXCEPTION;
       INVALID_SUBINVENTORY         EXCEPTION;
       NO_LINES_PRESENT  EXCEPTION;
       FAILED_ITEM       EXCEPTION;
BEGIN
      OPEN C_HDR_STAG;
       LOOP
         FETCH C_HDR_STAG  INTO C_HDR ;
         EXIT WHEN  C_HDR_STAG%NOTFOUND;
--PROCESSING UNPROCESSED ROWS
        /* IF
           (C_HDR.PROCESS_FLAG IS NULL) OR ( C_HDR.PROCESS_FLAG IS NOT NULL AND  C_HDR.PROCESS_FLAG<>'COMPLETE')
                THEN
           UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                     SET  PROCESS_FLAG='UNPROCESSED'
               WHERE CURRENT OF C_HDR_STAG; 
       END IF;   */
-- CHECKING NULLITY OF COLUMNS
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('REPLENISHMENT_HEADER_ID',C_HDR.REPLENISHMENT_HEADER_ID);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('REPLENISHMENT_COUNT_NAME',C_HDR.REPLENISHMENT_COUNT_NAME);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('COUNT_DATE',C_HDR.COUNT_DATE);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('LAST_UPDATE_DATE',C_HDR.LAST_UPDATE_DATE);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('CREATION_DATE',C_HDR.CREATION_DATE);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('CREATED_BY',C_HDR.CREATED_BY);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('LAST_UPDATED_BY',C_HDR.LAST_UPDATED_BY) ;
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('ORGANIZATION_ID',C_HDR.ORGANIZATION_ID);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('SUBINVENTORY_CODE',C_HDR.SUBINVENTORY_CODE);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('PROCESS_STATUS',C_HDR.PROCESS_STATUS);
       A02_REPLENISMENT_IMPORT.A02_NOT_NULL('PROCESS_MODE',C_HDR.PROCESS_MODE);
--CHECKING UNIQUENESS OF HEADER ID
     BEGIN
             HEADER_ID_V :=  A02_REPLENISMENT_IMPORT.UNIQUE_HEADER_ID(  C_HDR.REPLENISHMENT_HEADER_ID);
        IF
           HEADER_ID_V =FALSE  THEN
           RAISE NON_UNIQUE_HDR;
        END IF;
    END;
--CHECKING UNIQUENESS OF COUNT NAME
    BEGIN
         COUNT_NAME_V :=  A02_REPLENISMENT_IMPORT.UNIQUE_REPLENISH_COUNT_NAME ( C_HDR.REPLENISHMENT_COUNT_NAME);
        IF
           COUNT_NAME_V  =FALSE  THEN
           RAISE NON_UNIQUE_COUNT_NAME;
        END IF;
    END;
-- CHECKING VALIDITY OF  SUBINVENTORY
     BEGIN
         SUBINVENTORY_V :=  A02_REPLENISMENT_IMPORT.VALID_SUBINVENTORY  ( C_HDR.SUBINVENTORY_CODE,C_HDR.ORGANIZATION_ID);
        IF
        SUBINVENTORY_V =FALSE  THEN
           RAISE INVALID_SUBINVENTORY;
        END IF;
    END;
      INSERT INTO  MTL_REPLENISH_HEADERS_INT (
                                            REPLENISHMENT_HEADER_ID,
                                            REPLENISHMENT_COUNT_NAME,
                                       COUNT_DATE,
                                       LAST_UPDATE_DATE,      
                                     CREATION_DATE,
                                     CREATED_BY,
                                       LAST_UPDATED_BY,
                                       ORGANIZATION_ID,
                                       SUBINVENTORY_CODE,
                                       PROCESS_STATUS,
                                   PROCESS_MODE)
                                 VALUES(
                                        C_HDR.REPLENISHMENT_HEADER_ID,
                                       C_HDR.REPLENISHMENT_COUNT_NAME,
                                       C_HDR.COUNT_DATE,
                                       C_HDR.LAST_UPDATE_DATE,
                                      C_HDR.CREATION_DATE,
                                       C_HDR.CREATED_BY,
                                       C_HDR.LAST_UPDATED_BY,
                                       C_HDR.ORGANIZATION_ID,
                                       C_HDR.SUBINVENTORY_CODE,
                                       C_HDR.PROCESS_STATUS,
                                       C_HDR.PROCESS_MODE);
    END LOOP;
    CLOSE  C_HDR_STAG;
   
--PROCESSING FOR LINES
                 
     BEGIN
            OPEN C_LINES_STAG;
            LOOP
            FETCH C_LINES_STAG  INTO  C_LINES;
            EXIT WHEN C_LINES_STAG%NOTFOUND;
    -- CHECKING NULLITY OF COLUMNS
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('REPLENISHMENT_HEADER_ID',C_LINES.REPLENISHMENT_HEADER_ID);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('REPLENISHMENT_LINE_ID',C_LINES.REPLENISHMENT_LINE_ID);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('LAST_UPDATE_DATE',C_LINES.LAST_UPDATE_DATE);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('CREATION_DATE',C_LINES.CREATION_DATE);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('CREATED_BY',C_LINES.CREATED_BY);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('LAST_UPDATED_BY',C_LINES.LAST_UPDATED_BY);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('INVENTORY_ITEM_ID',C_LINES.INVENTORY_ITEM_ID);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('COUNT_TYPE_CODE',C_LINES.COUNT_TYPE_CODE);
           A02_REPLENISMENT_IMPORT.A02_NOT_NULL('COUNT_UOM_CODE',C_LINES.COUNT_UOM_CODE);
     --CHECKING_ITEM_ENABILITY AND ITEM_VALDLITY
            BEGIN
       ITEM_E_V :=  A02_REPLENISMENT_IMPORT.CK_ITEM_ENABILITY( C_LINES.INVENTORY_ITEM_ID,C_LINES.ORGANIZATION_ID);
                  IF  ITEM_E_V=FALSE THEN
                      RAISE FAILED_ITEM ;
                  END IF;
              END;
           INSERT INTO  MTL_REPLENISH_LINES_INT(
                            REPLENISHMENT_HEADER_ID,
                            REPLENISHMENT_LINE_ID,
                            ORGANIZATION_ID,
                            LAST_UPDATE_DATE,
                            CREATION_DATE,
                            CREATED_BY,
                            LAST_UPDATED_BY,
                            INVENTORY_ITEM_ID,
                            COUNT_TYPE_CODE,
                            COUNT_QUANTITY,
                            COUNT_UOM_CODE)
              VALUES(
                            C_LINES.REPLENISHMENT_HEADER_ID,
                            C_LINES.REPLENISHMENT_LINE_ID,
                            C_LINES.ORGANIZATION_ID,
                            C_LINES.LAST_UPDATE_DATE,
                            C_LINES.CREATION_DATE,
                            C_LINES.CREATED_BY,
                            C_LINES.LAST_UPDATED_BY,
                            C_LINES.INVENTORY_ITEM_ID,                                    
                            C_LINES.COUNT_TYPE_CODE,
                            C_LINES.COUNT_QUANTITY,
                            C_LINES.COUNT_UOM_CODE);
       
       
                  UPDATE  A02_PRE_REPLENISH_LINES_INT
                         SET  PROCESS_FLAG='COMPLETED';
                         -- WHERE CURRENT OF C_LINES_STAG;
                          V_COUNT_LINES_COMP:=V_COUNT_LINES_COMP  +1;
       END LOOP;
       CLOSE C_LINES_STAG;
       EXCEPTION
                       WHEN  FAILED_ITEM  THEN
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                            VALUES(SYSDATE,'INVENTORY_ITEM_ID','err_messg','A02_PRE_REPLENISH_LINES_INT');
                       WHEN NO_LINES_PRESENT THEN
                             INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                          VALUES(SYSDATE,'REPLENISHMENT_LINE_ID','err_messg','A02_PRE_REPLENISH_LINES_INT');
      END;
      --VALIDATING THE PRESENCE OF ATLEAST A LINE PER HEADER
             IF
                   V_COUNT_LINES_COMP=0 THEN
                    RAISE NO_LINES  ;   
             END IF;
    --TAKING COUNT OF PROCESSED ROWS
                 UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                 SET  PROCESS_FLAG='COMPLETED';
                 -- WHERE CURRENT OF C_HDR_STAG;
                 V_COUNT_COMPL:=  V_COUNT_COMPL+1;
    EXCEPTION
            WHEN A02_null_exception THEN
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                            VALUES(SYSDATE,COL_NAME_V,'err_messg','A02_PRE_REPLENISH_HEADERS_INT');
                                            UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                                            SET  PROCESS_FLAG='ERROR';
          --                                  WHERE CURRENT OF C_HDR_STAG;
                                            V_COUNT_ERROR:=V_COUNT_ERROR    +1;
                       WHEN  NON_UNIQUE_HDR THEN
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                            VALUES(SYSDATE,'REPLENISHMENT_HEADER_ID','err_messg','A02_PRE_REPLENISH_HEADERS_INT');
                                             UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                                             SET PROCESS_FLAG='ERROR';
             --                               WHERE CURRENT OF C_HDR_STAG;
                                            V_COUNT_ERROR:=V_COUNT_ERROR    +1;
                      WHEN NON_UNIQUE_COUNT_NAME THEN
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                            VALUES(SYSDATE,'REPLENISHMENT_COUNT_NAME','err_messg','A02_PRE_REPLENISH_HEADERS_INT');
                                            UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                                            SET PROCESS_FLAG='ERROR';
                --                            WHERE CURRENT OF C_HDR_STAG;
                                            V_COUNT_ERROR:=V_COUNT_ERROR    +1;
                      WHEN INVALID_SUBINVENTORY THEN
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                            VALUES(SYSDATE,'SUBINVENTORY_CODE','err_messg','A02_PRE_REPLENISH_HEADERS_INT');
                                            UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                                            SET  PROCESS_FLAG='ERROR';
                   --                         WHERE CURRENT OF C_HDR_STAG;
                                            V_COUNT_ERROR:=V_COUNT_ERROR    +1;
  /*                  WHEN OTHERS THEN
                       V_ERRBUF:=SQLCODE;
                       V_RETCODE:=SUBSTR(SQLERRM,1,200);
                          INSERT INTO A02_REPLENISHMENT_ERRORS_INT(PROCESSING_DATE,COLUMN_NAME,ERROR_MESSG, TABLE_NAME)
                                VALUES (SYSDATE,'ERROR_CODE','err_messg','A02_PRE_REPLENISH_HEADERS_INT');
                                UPDATE  A02_PRE_REPLENISH_HEADERS_INT
                                            SET PROCESS_FLAG='ERROR';
                     --                       WHERE CURRENT OF C_HDR_STAG;
                                            V_COUNT_ERROR:=V_COUNT_ERROR +1; */
END A02_MAIN_PROCEDURE;
END A02_REPLENISMENT_IMPORT;
/

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,

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 develop Open Replenishment Interface

 From Oracle Manufacturing APIs and Open Interfaces Manual

How to develop Open Replenishment Interface

            Oracle Inventory provides an open interface for you to easily load replenishment requests from external systems such as a bar-code application. Such requests may be in the form of stock-take counts or requisition requests for subinventories in which you do not track quantities.

You may also use the Replenishment Interface to process requisition requests generated by external applications for tracked subinventories.

Functional Overview

            You must write the load program that inserts a single row for each replenishment count/request into the MTL_REPLENISH_HEADERS_INT table. A record for each item included in the count header must be inserted into the MTL_REPLENISH_LINES_INT table. There are two modes you can use to send your replenishment counts through the interface. These are Concurrent and Background modes.

            Under Concurrent mode processing, you populate the interface tables for a specific replenishment count and then call the replenishment validator from the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). The validator processes the replenishment count specified as a parameter at process submission, validating rows in both the MTL_REPLENISH_HEADER_INT and MTL_REPLENISH_LINES_INT tables. The validator derives any additional columns and updates the error flag if an error is detected.

            For Background mode processing, you populate the interface tables and then let the Replenishment Validator asynchronously poll the tables for replenishment counts to process.

            If the replenishment count, both header and lines, passes all required validation, the records are inserted into the MTL_REPLENISH_HEADERS and MTL_REPLENISH_LINES tables and are deleted from the interface tables. If an error is detected during the validation process, the header and corresponding replenishment lines will be left in the interface table.

            Once the lines are in the internal replenishment tables, you use the Replenishment Processor as described in the Oracle Inventory User’s Guide to process the counts and create requisitions. See: Entering and Processing Replenishment Counts, Oracle Inventory User’s Guide.

Setting Up the Replenishment Interface

            Access the Replenishment Interface through the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). Select the type of request by choosing Single Request. In the Request Name field, select Validate Replenishment Interface. In the Parameters window, select Concurrent or Background as the Processing Mode and select the Count Name for processing. Select Submit Request to begin processing. You can also use the Schedule button to specify resubmission parameters that will control how frequently the Replenishment Validator polls for records in the interface tables.

Inserting into the Replenishment Interface Tables

            This section provides a chart for each interface table that lists all columns, followed by a section giving a brief description of a subset of columns requiring further explanation. The chart identifies each column’s datatype and whether it is Required, Derived, or Optional.

            Several of the attributes in the interface tables can be populated using either the user-friendly values or the internal identifiers. For example, you have the choice of specifying either the flexfield segment representation or the internal identifier (e.g. INVENTORY_ITEM_ID) for the required value. When specifying the organization, you may either use the organization code or the internal identifier (e.g. ORGANIZATION_ID).

            If you populate the user friendly values, the Replenishment Validator will validate them and will derive the internal identifiers. If the translation is available to the external system, it may be advantageous to use the internal identifiers to improve performance.

Replenishment Headers Interface Tables

The following graphic describes the MTL_REPLENISH_HEADERS_INT table:

Table 7–8 Oracle Inventory Replenishment Headers Interface

Column Name Type Required Derived Optional

REPLENISHMENT_HEADER_ID Number 3

REPLENISHMENT_COUNT_NAME Varchar2(10) 3

COUNT_DATE Date 3

LAST_UPDATE_DATE Date 3

ERROR_FLAG

If a validation error occurs, the replenishment validator populates this column with an error code. The error flag for a replenishment header will be set if either the validation of the header fails or if the validation of any of the lines of the header fails.

ORGANIZATION_ID

This column identifies the internal identifier of the organization from which the

replenishment count originated. You must enter either the internal organization identifier or the user friendly organization code.

CREATION_DATE Date 3

CREATED_BY Number 3

LAST_UPDATE_LOGIN Number 3

LAST_UPDATED_BY Number 3

ORGANIZATION_ID Number 3

ORGANIZATION_CODE Varchar2(3) 3

SUBINVENTORY_CODE Varchar2(10) 3

SUPPLY_CUTOFF_DATE Date 3

PROCESS_STATUS Number 3

PROCESS_MODE Number 3

ERROR_FLAG Number 3

REQUEST_ID Number 3

PROGRAM_APPLICATION_ID Number 3

PROGRAM_ID Number 3

PROGRAM_UPDATE_DATE Date 3

DELIVERY_LOCATION_ID Number 3

DELIVERY_LOCATION_CODE Varchar2(20) 3

Table 7–8 Oracle Inventory Replenishment Headers Interface

Column Name Type Required Derived Optional

ORGANIZATION_CODE

This column is the user friendly code for the organization that is the source of the replenishment count. It may be used instead of the internal identifier, in which case the internal identifier will be derived.

PROCESS_MODE

This column determines how the interfaced replenishment count will be processed.

The valid options are:

2 - Concurrent

3 - Background

Interface replenishment counts marked for Background processing will be picked up by the replenishment validator polling process. The validator will pick up and process all replenishment counts with a process mode of Background each time it runs.

You use Concurrent processing mode if you want to launch a dedicated

replenishment validator process to explicitly process a single replenishment count, identified as a parameter to the program, from the interface table.

PROCESS_STATUS

This column is used to identify the current processing status of the replenishment count. You should insert rows that you intend to be processed with a value of 2 (Pending). The valid values for this column are:

1. Hold

2. Pending

3. Processing

4. Error

5. Completed

If you want to insert records into the interface tables but temporarily prevent them from being processed, you can use this column by setting the value to 1 (Hold). After the validator has run, it will set the value of this column to 5 (Completed). This status is used whenever the process completes, whether validation errors were detected or not.

A status of 4 (Error) indicates an internal error occurred. This error indicates an exceptional condition and should not occur.

REPLENISH_HEADER_ID

Enter a unique identifier for the replenishment count. This column is used to group the lines of a replenishment count with the header. You may use the sequence MTL_REPLENISH_HEADERS_S to obtain a unique identifier.

REPLENISH_COUNT_NAME

Enter a unique name for the replenishment count.

SUBINVENTORY_CODE

This column identifies the subinventory that is the source of the replenishment

count.

SUPPLY_CUTOFF_DATE

Enter the date after which planned supply will not be considered in available

quantity calculations. A null value here indicates that you do not want to consider planned supply when performing replenishment calculations.

DELIVERY_LOCATION_ID

Enter the internal identifier for the location to which the replenishment should be delivered. You may enter the delivery location identifier, the user friendly delivery location code or neither. If neither is specified, the default delivery location for the organization from which the replenishment originated is defaulted.

DELIVERY_LOCATION_CODE

Enter the user friendly code for the delivery location of the replenishment. You may enter this code instead of the internal identifier, in which case the internal identifier will be derived. You may specify neither the code or the identifier, in which case the default delivery location of the organization originating the replenishment will be used.

The following graphic describes the MTL_REPLENISH_LINES_INT table:

Table 7–9 Oracle Inventory Replenishment Lines Interface

Column Name Type Required Derived Optional

REPLENISHMENT_HEADER_ID Number 3

REPLENISHMENT_LINE_ID Number 3

ORGANIZATION_ID Number 3

Open Replenishment Interface

Oracle Inventory Open Interfaces and APIs 7-33

REPLENISHMENT_HEADER_ID

Enter the unique identifier of the replenishment count. The identifier entered here is the foreign key reference which links the header table with the lines table to associate a group of lines with a single header.

REPLENISHMENT_LINE_ID

Enter the identifier for the line within the replenishment count. You may use the sequence MTL_REPLENISH_LINES_S to obtain a unique identifier for the line.

INVENTORY_ITEM_ID

Enter the internal identifier for the item to be replenished.

LAST_UPDATE_DATE Date 3

CREATION_DATE Date 3

CREATED_BY Number 3

LAST_UPDATE_LOGIN Number 3

LAST_UPDATED_BY Number 3

INVENTORY_ITEM_ID Number 3

SEGMENT {1-20} Varchar2(40) 3

COUNT_TYPE_CODE Number 3

COUNT_QUANTITY Number 3

REFERENCE Varchar2(240) 3

ERROR_FLAG Number 3

REQUEST_ID Number 3

PROGRAM_APPLICATION_ID Number 3

PROGRAM_ID Number 3

PROGRAM_UPDATE_DATE Date 3

COUNT_UNIT_OF_MEASURE Varchar2(25) 3

COUNT_UOM_CODE Varchar2(3) 3

Table 7–9 Oracle Inventory Replenishment Lines Interface

Column Name Type Required Derived Optional

Open Replenishment Interface

7-34 Oracle Manufacturing APIs and Open Interfaces Manual

SEGMENT{1-20}

You may use these flexfield columns instead of INVENTORY_ITEM_ID to enter the item identifier in a more user-friendly form.

ORGANIZATION_ID

This column identifies the internal identifier of the organization from which the

replenishment count originated. If you do not enter a value here, the organization dentifier will be derived from the replenishment header.

COUNT_TYPE_CODE

Enter the type of the replenishment count entry. The valid count types are:

1. On-hand Quantity

2. Order Quantity

3. Order Maximum

Use On-hand Quantity to identify counts that are the result of stock-takes of

subinventories in which you do not track on-hand quantities.

Use Order Quantity when you want to specify the quantity to be ordered. This

count type may be used with either tracked or non-tracked subinventories.

Use Order Maximum when you want to place an order for the min-max maximum

quantity specified for item in the subinventory specified. This count type may be

used with either tracked or non-tracked subinventories.

COUNT_QUANTITY

This column is used to specify the count quantity that corresponds to the count type

entered for the line. When the count type is On-hand Quantity, the count quantity

is the on-hand balance determined during the stock-take. When the count type is

Order Quantity, the count quantity represents the quantity to be ordered. This

column is not used when the count type is Order Maximum.

REFERENCE

Use this column to enter any replenishment count reference information.

Open Replenishment Interface

Oracle Inventory Open Interfaces and APIs 7-35

COUNT_UNIT_OF_MEASURE

Enter the count unit of measure identifier. This column may be used to specify the

full name for the unit of measure. This column is meaningful only when a value is

entered in the COUNT_QUANTITY columns.

COUNT_UOM_CODE

This column is represents the unit of measure code used for the count. You may

specify the code when populating this table or you may use the full name for the

unit of measure, in which case this column will be derived. This column is

meaningful only when a value is entered in the COUNT_QUANTITY columns.

ERROR_FLAG

This flag indicates the error status of the validation of a replenishment line. The

replenishment validator populates this column with a line corresponding to the

error detected during validation.

Validation

Oracle Inventory validates the following conditions:

¦ The value of REPLENISH_HEADER_ID must be unique among existing

replenishment counts

¦ The value of REPLENISH_COUNT_NAME must be unique among existing

count headers

¦ The value of LAST_UPDATED_BY must be a valid user name

¦ ORGANIZATION_ID must be a valid identifier of an organization

¦ SUBINVENTORY_CODE must refer to an existing subinventory

¦ DELIVERY_LOCATION_ID must be a valid identifier of a location associated

with the organization generating the replenishment

¦ There must be at least one line per header

¦ The ORGANIZATION_ID at the header level must be the same as that at the line level

¦ COUNT_TYPE_CODE must be either 1, 2, or 3 and must be consistent with

whether the subinventory is tracked or non-tracked

¦ The value of COUNT_QUANTITY must be consistent with COUNT_TYPE_

CODE and must be greater than zero

Open Replenishment Interface

7-36 Oracle Manufacturing APIs and Open Interfaces Manual

¦ INVENTORY_ITEM_ID must refer to a transactable item in the organization

specified

¦ The item must exist in the subinventory and must be min-max planned in that

subinventory

¦ The COUNT_UOM_CODE must be valid and conversions to primary UOM

must exist

¦ Each line must correspond to a header

Viewing Failed Transactions

Replenishment counts that fail the validation process will remain in the MTL_

REPLENISH_HEADERS_INT and MTL_REPLENISH_LINES_INT tables. You may

use SQL*PLUS to identify the headers that have failed by selecting those rows with

a process_status of 5 (Complete). The reason for the failure will be reflected in the

ERROR_FLAG column.

Possible values for the ERROR_FLAG column in the MTL_REPLENISH_

HEADERS_INT table are:

1 - Non-unique replenishment header id

2 - Non-unique replenishment count name

3 - Invalid user name

4 - Invalid organization identifier

5 - Invalid subinventory

7 - Header with no corresponding replenishment lines

10 - Header failed because line failed

18 - Delivery location is not valid

Possible values for the ERROR_FLAG column in the MTL_REPLENISH_LINES_

INT table are:

1 - No corresponding header id

3 - Invalid user name

8 - Invalid item identifier or item isn’t transactable

9 - Invalid unit of measure or no conversion to primary unit of measure exists

11 - No item specified in either identifier or segments

Open Replenishment Interface

12 - Invalid count type

13 - On-hand count type used for tracked subinventory

14 - Invalid count quantity

15 - Lines organization header does not match header organization identifier

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

subinventory

Fixing Failed Transactions

Frequently, errors in the interface are caused by problems external to the

replenishment count itself. For example, there may be validation that failed becausean entity that was being validated had the wrong status (i.e. disabled), or the failure could even be the result of a system error, such as running out of space. In these cases, the resolution is simple; once you have made the necessary changes, you simply need to resubmit the replenishment validator process.

If, however, you need to make changes to the data in the interface table, you need to either delete the failed records, correct them in the external feeder system and resubmit them, or update the interface record in the interface table using SQL*PLUS. When you resubmit updated transactions for processing, all validation will be performed again.

HR EMPLOYEE API to CREATE EMPLOYEE in HRMS....

DECLARE
   l_emp_num varchar2(20);
   l_person_id number;
   l_assignment_id number;
   l_asg_object_version_number number;
   l_effective_start_date date;
   l_effective_end_date date;
   l_full_name varchar2(240);
   l_per_comment_id number;
   l_assignment_sequence number;
   l_assignment_number per_all_assignments_f.assignment_number%TYPE;
   l_name_combination_warning boolean;
   l_assign_payroll_warning boolean;
   l_orig_hire_warning boolean;
   l_per_object_version_number number;
   l_asgobject_version_number number;
   l_comment_id number(5);
  CURSOR CUR_TP IS
 SELECT     tp.hire_date
    ,tp.business_group_id
    ,tp.last_name
      ,tp.sex
 FROM TEMP_PERSON tp ;
BEGIN
   for ap in CUR_TP loop
   l_emp_num:=null;
   begin
   HR_EMPLOYEE_API.CREATE_EMPLOYEE
   (
   p_hire_date                     =>    ap.hire_date
  ,p_business_group_id             =>    ap.business_group_id
  ,p_last_name                     =>    ap.last_name
  ,p_sex                           =>    ap.sex
  ,p_employee_number     =>  l_emp_num
  ,p_person_id       =>  l_person_id
  ,p_assignment_id       =>   l_assignment_id
  ,p_per_object_version_number     =>    l_per_object_version_number
  ,p_asg_object_version_number     =>    l_asgobject_version_number
  ,p_per_effective_start_date      =>    l_effective_start_date
  ,p_per_effective_end_date        =>    l_effective_end_date
  ,p_full_name                     =>    l_full_name
  ,p_per_comment_id            =>  l_comment_id
  ,p_assignment_sequence     =>  l_assignment_sequence
  ,p_assignment_number     =>  l_assignment_number
  ,p_name_combination_warning      =>  l_name_combination_warning
  ,p_assign_payroll_warning    =>  l_assign_payroll_warning
  ,p_orig_hire_warning     =>  l_orig_hire_warning
   );
end;
 END LOOP;
  COMMIT;
 END;
/


   hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
   hr_utility.raise_error;

How to Handle the Projects ?

How to Handle the Projects ?

It is commonly understood in the IT industry that "most projects fail." This prediction of doom and gloom is overheard in corporate corridors and has been consistently written about in the technology trade media. Large-scale information technology projects are traditionally prone to greater failure rates than other complex projects of similar size and scope -- in engineering or construction, for example. According to a University of Maryland study, projects are often designated as "failures" if they are over schedule by more than 30 percent, over budget by more than 30 percent, and the end product does not meet user requirements.

Industry research reports that most projects "seem" to fail in the last third of the project; yet in reality, they fail in the first third of the project. For those IT executives not experienced or well-trained to manage complex IT projects, a project may look like it is running along smoothly until it is too late to do something about it.

Fortunately, IT executives can learn from those who have gone before them. Given the complexity of IT projects and their high rates of failure, sponsoring an IT project can be a daunting and monumental task. After years of experience working with IT executives from multi-national to regional corporations in various industries, I have witnessed how those who practice a set of seven distinct habits are infinitely more successful than those who do not.

Though some are more visceral than measurable, the following practices should allow every IT executive to recognize failure before it occurs -- or better yet, avoid signs of failure all together.

1. Understand The Challenge There are three things inherent to IT projects that make them more apt to fail than other projects. First, there is no governing body in the IT industry that dictates certain standards for IT implementation. Second, technology is evolving at such a rapid pace, it is difficult to build a competency in any one particular area before it changes. Last, it has become standard practice to deliver IT projects with at least some flaws. Conversely, a margin of error is slim to none in the automobile, aviation or medical industries.



2. Exhibit Self-Awareness Successful IT executives need to fully embrace their role as the project sponsor. They need to obtain all necessary C-level buy-in and secure adequate funding. Project sponsors must clearly articulate the objectives and strategies to the project team, offer their time and lend their support.

3. Know Your Team Players The "human factor" is absolutely critical to the success or failure of an IT project. IT executives need to hand pick each member of the project team based on a detailed assessment of the skills and experience necessary to get the job done. The most critical player on the team is the project manager. In addition to having a specific set of skills, training and experience, the project manager must also have the ability to motivate and drive peers, exhibit attention to detail, and understand how to use project management tools properly.

4. Outsource Experienced IT executives often hire an outsourced IT consulting firm to assure success. When choosing an IT consulting firm, the savvy IT executive looks for a firm with literally hundreds of man years addressing challenges similar to what their company is facing; that has a proven, scalable and flexible methodology; that is vendor independent; that staffs every complex project with senior-level strategists that they can trust and respect; and that is able to provide in-person service on-the-fly.

5. Start with Discovery Believe it or not, many IT projects begin without a "discovery" phase. During the discovery phase, requirements and expectations are defined, the infrastructure is assessed and project recommendations are made. Ideally, neither a project team nor a project budget should be finalized until the discovery phase is complete. In fact, in some instances, a discovery phase may reveal a project that is destined for failure before any time or resources are consumed. A discovery phase can be a painful process, but one that yields valuable results. Following the discovery phase, there should also be a clearly defined planning, design, testing, implementation and monitoring phase.

6. Require Documentation IT executives should request a number of weekly documents from the project manager. First, a "run rate analysis" for both the budget and the schedule -- where the project is in relation to where it is supposed to be. Second, there must be a pre-determined "serial path" as well as a "critical path." A serial path assumes that one item is dependent upon the other and will not start until the item that precedes it is complete. A "critical path" maps out how to accomplish the goal in the least amount of time. Tasks that can be accomplished in parallel are clearly defined on a "critical path." IT executives who are trained to recognize potential project failure stay off the critical path at all costs, as there is no room for error once a project hits the critical path stage.

7. Communicate Frequently and Openly Project sponsors need to be seen and heard and stay front and center. They need to listen carefully to their project managers and ask the right questions at the right time. They need to be ready to address problems along the way. A "no news is good news" mentality will leave a project sponsor with a failed project in the end. At the onset of a project, successful IT executives ask their project managers to create an "issues and concerns" template. They direct their managers to complete this form as challenges or roadblocks arise during the project.


IT success does happen. IT executives who practice the aforementioned seven habits will find themselves going from the hot seat to Easy Street. Good luck, I hope it works for you the same way I've seen it work for so many of your peers.