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:
Post a Comment