Nov 12, 2010

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;
/

No comments:

OraApps Search

Custom Search

Search This Blog