CREATE OR REPLACE PACKAGE BODY APPS.XXWEBADI_MTL_ONHANDQTY_PKG
AS
PROCEDURE XXWEBADI_MTL_ONHAND_QTY_INS (
errbuff OUT VARCHAR2,
retcode OUT NUMBER
)
IS
LV_BATCH_MODE VARCHAR2(3);
LN_BATCH_ID NUMBER;
LV_ERROR VARCHAR2(240) := ‘Unable to load reocords in MTL_TRANSACTIONS_INTERFACE TABLE..’;
CURSOR CUR_SEL IS SELECT BATCH_MODE,TRANSACTION_BATCH_ID
FROM XXINV_MTL_TRX_ONHAND_QTY_INT
WHERE 1=1 AND PROCESS_FLAG=0;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,’Before Validate Procedure..’);
–FND_FILE.PUT_LINE(FND_FILE.LOG,’Batch details..’||LV_BATCH_MODE ||’ ‘||LN_BATCH_ID);
For ONHAND_SEL in CUR_SEL Loop
IF LV_BATCH_MODE=’Y’ THEN
XXWEBADI_TRANSFORMATION_PROC(ONHAND_SEL.BATCH_MODE,ONHAND_SEL.TRANSACTION_BATCH_ID);
XXWEBADI_MTL_ONHAND_QTY_PROC;
ELSE
–FND_FILE.PUT_LINE(FND_FILE.LOG,’Before Validate Procedure..’);
XXWEBADI_MTL_ONHAND_QTY_PROC;
–FND_FILE.PUT_LINE(FND_FILE.LOG,’After Validate Procedure..’);
END IF;
END loop;
FND_FILE.PUT_LINE(FND_FILE.LOG,’After Validate Procedure..’);
BEGIN
INSERT into mtl_transactions_interface(
Transaction_interface_id
,source_code
,source_line_id
,source_header_id
,process_flag
,transaction_mode
,lock_flag
,locator_id
,inventory_item_id
,organization_id
,transaction_quantity
,primary_quantity
,transaction_uom
,transaction_date
,subinventory_code
,transaction_source_name
,transaction_source_id
,transaction_source_type_id
,transaction_type_id
,dst_segment1
,dst_segment2
,dst_segment3
,dst_segment4
,dst_segment5
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
select
Transaction_interface_id
,source_code
,source_line_id
,source_header_id
,process_flag
,transaction_mode
,lock_flag
,locator_id
,inventory_item_id
,organization_id
,transaction_quantity
,primary_quantity
,transaction_uom
,transaction_date
,subinventory_code
,transaction_source_name
,transaction_source_id
,transaction_source_type_id
,transaction_type_id
,dst_segment1
,dst_segment2
,dst_segment3
,dst_segment4
,dst_segment5
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
from XXINV_MTL_TRX_ONHAND_QTY_INT
where Process_flag_c=’S’
and error_code is null
and transaction_interface_id is not null;
Exception when Others Then
FND_MESSAGE.SET_NAME (‘BNE’, ‘WEBADI_ERROR’);
FND_MESSAGE.SET_TOKEN (‘MSG’, LV_ERROR);
FND_MESSAGE.RAISE_ERROR;
END;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.log,’Error in first Proc..’);
END XXWEBADI_MTL_ONHAND_QTY_INS;
PROCEDURE XXWEBADI_MTL_ONHAND_QTY_PROC
IS
X_ORGANIZATION_ID NUMBER;
X_INV_ITEM_ID NUMBER;
X_PRIMARY_UOM_CODE VARCHAR2(3);
X_TXN VARCHAR2(1);
X_ERR_MSG VARCHAR2(1000);
X_SUBINVENTORY VARCHAR2(100);
X_TRANSACTION_TYPE_ID NUMBER;
X_TXN_SOURCE_TYPE_ID NUMBER;
X_COUNT NUMBER;
X_err_code VARCHAR2(100);
X_PROCESS_FLAG VARCHAR2(1);
x_TRANSACTION_INTERFACE_ID NUMBER;
X_CODE_COMBINATION_ID NUMBER;
X_disposition_id NUMBER;
X_description VARCHAR2(240);
X_trx_source VARCHAR2(240);
X_account_id NUMBER;
X_DST_SEGMENT1 VARCHAR2(10);
X_DST_SEGMENT2 VARCHAR2(10);
X_DST_SEGMENT3 VARCHAR2(10);
X_DST_SEGMENT4 VARCHAR2(10);
X_DST_SEGMENT5 VARCHAR2(10);
X_LOCATOR_ID NUMBER;
CURSOR C1 IS SELECT
RECORD_ID,
ORGANIZATION_CODE,
ITEM_NUMBER,
SUBINVENTORY,
TRANSACTION_DATE,
TRANSACTION_TYPE,
UOM_CODE,
PRIMARY_QUANTITY_C,
TRANSACTION_QUANTITY,
DISTRIBUTION_CODE_COMBINATION
FROM
XXINV_MTL_TRX_ONHAND_QTY_INT
WHERE PROCESS_FLAG = 0;
CURSOR C2 IS SELECT
RECORD_ID,
ORGANIZATION_CODE,
ITEM_NUMBER,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
SUBINVENTORY,
TRANSACTION_DATE,
TRANSACTION_TYPE,
UOM_CODE,
PRIMARY_QUANTITY,
TRANSACTION_QUANTITY,
DISTRIBUTION_CODE_COMBINATION
FROM
XXINV_MTL_TRX_ONHAND_QTY_INT
WHERE PROCESS_FLAG = 1
AND ERROR_CODE_C is null
AND PROCESS_FLAG_C=’V’;
BEGIN
X_err_code := NULL;
X_COUNT :=0;
FOR ONHAND_LOOP IN C1
LOOP
X_COUNT := X_COUNT+1;
—Validations:
—————————————————
–ORGANIZATION VALIDATION
—————————————————-
BEGIN
SELECT MP.ORGANIZATION_ID,’V’
INTO X_ORGANIZATION_ID,
X_PROCESS_FLAG
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = ONHAND_LOOP.ORGANIZATION_CODE;
FND_FILE.PUT_LINE(FND_FILE.LOG,’ORGANIZATION IS VALID’);
EXCEPTION
WHEN OTHERS THEN
— X_err_code := SQLCODE;
X_ERR_MSG := SUBSTR(SQLERRM, 1, 200)||’:’||’ORGANIZATION IS NOT VALID’ ;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET ERROR_CODE_C=’E’,ERROR_MESSAGE_C = X_ERR_MSG
where record_id=ONHAND_LOOP.record_id;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’ORGANIZATION IS NOT VALID’);
END;
———————————————————–
—ITEM AND UOM VALIDATION
———————————————————–
BEGIN
SELECT MSI.INVENTORY_ITEM_ID
,MSI.PRIMARY_UOM_CODE
,’V’
,MSI.MTL_TRANSACTIONS_ENABLED_FLAG
INTO X_INV_ITEM_ID
,X_PRIMARY_UOM_CODE
,X_PROCESS_FLAG
,X_TXN
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = X_ORGANIZATION_ID
AND MSI.SEGMENT1 = ONHAND_LOOP.ITEM_NUMBER;
FND_FILE.PUT_LINE(FND_FILE.LOG,’ITEM IS VALID’);
EXCEPTION
WHEN OTHERS THEN
— X_err_code := SQLCODE;
X_ERR_MSG := SUBSTR(SQLERRM, 1, 200)||’:’||’ITEM IS NOT VALID’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET ERROR_CODE_C=’E’,ERROR_MESSAGE_C = X_ERR_MSG where record_id=ONHAND_LOOP.record_id;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’ITEM IS NOT VALID’);
END;
——————————————————
—SUB INVENTORY VALIDATION
——————————————————
BEGIN
SELECT MSI.SECONDARY_INVENTORY_NAME,’V’
INTO X_SUBINVENTORY,X_PROCESS_FLAG
FROM MTL_SECONDARY_INVENTORIES MSI
WHERE MSI.ORGANIZATION_ID = X_ORGANIZATION_ID–(GET FROM ORG_CODE)
AND MSI.SECONDARY_INVENTORY_NAME = ONHAND_LOOP.SUBINVENTORY;
FND_FILE.PUT_LINE(FND_FILE.LOG,’SUBINVENTORY IS VALID’);
EXCEPTION
WHEN OTHERS THEN
— X_err_code := SQLCODE;
X_ERR_MSG := SUBSTR(SQLERRM, 1, 200)||’:’||’SUBINVENTORY IS NOT VALID’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET ERROR_CODE_C=’E’,ERROR_MESSAGE_C = X_ERR_MSG where record_id=ONHAND_LOOP.record_id;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’SUBINVENTORY IS NOT VALID’);
END;
——————————————————–
—TRANSACTION TYPE VALIDATION
——————————————————–
BEGIN
SELECT MTT.TRANSACTION_TYPE_ID
,MTT.TRANSACTION_SOURCE_TYPE_ID
, ‘V’
INTO X_TRANSACTION_TYPE_ID
, X_TXN_SOURCE_TYPE_ID
, X_PROCESS_FLAG
FROM MTL_TRANSACTION_TYPES MTT
WHERE UPPER ( MTT.TRANSACTION_TYPE_NAME ) = UPPER (ONHAND_LOOP.TRANSACTION_TYPE )
AND NVL ( MTT.DISABLE_DATE, SYSDATE +1 ) > SYSDATE ;
FND_FILE.PUT_LINE(FND_FILE.LOG,’TRANSACTION TYPE IS VALID’);
EXCEPTION
WHEN OTHERS THEN
—X_err_code := SQLCODE;
X_ERR_MSG := SUBSTR(SQLERRM, 1, 200)||’:’||’TRANSACTION TYPE IS NOT VALID’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET ERROR_CODE_C=’E’,ERROR_MESSAGE_C = X_ERR_MSG where record_id=ONHAND_LOOP.record_id;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’TRANSACTION TYPE IS NOT VALID’);
END;
END LOOP;
BEGIN
IF X_PROCESS_FLAG=’V’ THEN
X_err_code := NULL;
X_ERR_MSG := NULL;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT
SET PROCESS_FLAG = 1,ERROR_CODE_C = X_err_code,ERROR_MESSAGE_C =X_ERR_MSG,PROCESS_FLAG_C=X_PROCESS_FLAG;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’NUMBER OF RECORDS VALIDATED : ‘||X_COUNT);
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,’NUMBER OF RECORDS VALIDATED : ‘||X_COUNT);
END IF;
END;
——————————————————————————————-
—FOR INSERTING RECORDS INTO INTERFACE TABLE
——————————————————————————————–
BEGIN
FND_GLOBAL.Apps_Initialize(FND_GLOBAL.USER_ID,
FND_GLOBAL.RESP_ID,
FND_GLOBAL.RESP_APPL_ID);
IF X_PROCESS_FLAG=’V’ THEN
X_ORGANIZATION_ID :=NULL;
X_INV_ITEM_ID :=NULL;
X_PRIMARY_UOM_CODE :=NULL;
X_TXN :=NULL;
X_ERR_MSG :=NULL;
X_SUBINVENTORY :=NULL;
X_TRANSACTION_TYPE_ID :=NULL;
X_TXN_SOURCE_TYPE_ID :=NULL;
X_COUNT :=NULL;
X_err_code :=NULL;
X_PROCESS_FLAG :=’S’;
X_LOCATOR_ID :=1381;
FOR X IN C2 LOOP
X_COUNT := X_COUNT+1;
IF X_PROCESS_FLAG=’S’ THEN
BEGIN
SELECT MP.ORGANIZATION_ID,’S’
INTO X_ORGANIZATION_ID,
X_PROCESS_FLAG
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = X.ORGANIZATION_CODE;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 1, PROCESS_FLAG_C=X_PROCESS_FLAG where record_id=x.record_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
X_PROCESS_FLAG:=’E’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 0,PROCESS_FLAG_C=’E’ where record_id=x.record_id ;
COMMIT;
END;
END IF;
———————————————
–INVENTORY–
———————————————
IF X_PROCESS_FLAG=’S’ THEN
BEGIN
SELECT MSI.INVENTORY_ITEM_ID
,MSI.PRIMARY_UOM_CODE
,’S’
,MSI.MTL_TRANSACTIONS_ENABLED_FLAG
INTO X_INV_ITEM_ID
,X_PRIMARY_UOM_CODE
,X_PROCESS_FLAG
,X_TXN
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = X_ORGANIZATION_ID
AND MSI.SEGMENT1 = X.ITEM_NUMBER;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 1 , PROCESS_FLAG_C=X_PROCESS_FLAG where record_id=x.record_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
X_PROCESS_FLAG:=’E’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 0,PROCESS_FLAG_C=’S’ where record_id=x.record_id ;
COMMIT;
END;
END IF;
———————————————————————–
–TRANSACTION TYPE
———————————————————————–
IF X_PROCESS_FLAG=’S’ THEN
BEGIN
SELECT MTT.TRANSACTION_TYPE_ID
,MTT.TRANSACTION_SOURCE_TYPE_ID
, ‘S’
INTO X_TRANSACTION_TYPE_ID
, X_TXN_SOURCE_TYPE_ID
, X_PROCESS_FLAG
FROM MTL_TRANSACTION_TYPES MTT
WHERE UPPER ( MTT.TRANSACTION_TYPE_NAME ) = UPPER (X.TRANSACTION_TYPE )
AND NVL ( MTT.DISABLE_DATE, SYSDATE +1 ) > SYSDATE;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 1, PROCESS_FLAG_C=X_PROCESS_FLAG where record_id=x.record_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
X_PROCESS_FLAG:=’E’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 0,PROCESS_FLAG_C=’E’ where record_id=x.record_id ;
COMMIT;
END;
END IF;
BEGIN
select mgd.disposition_id, mgd.description,mgd.segment1, mgd.distribution_account
into X_disposition_id,X_description,X_trx_source,X_account_id
from mtl_generic_dispositions mgd,
fnd_lookup_values_vl flv
where mgd.segment1=flv.lookup_code
AND flv.lookup_type=’XXWEBADI_INV_DISPOSITIONS’
AND flv.tag=mgd.organization_id
AND mgd.organization_id=207;
–where organization_id = 207;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 1, PROCESS_FLAG_C=X_PROCESS_FLAG where record_id=x.record_id;
EXCEPTION WHEN OTHERS THEN
X_PROCESS_FLAG:=’E’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 0,PROCESS_FLAG_C=’E’ where record_id=x.record_id ;
COMMIT;
END;
IF X_PROCESS_FLAG=’S’ THEN
IF x.distribution_code_combination is null THEN
BEGIN
SELECT segment1,segment2,segment3,segment4,segment5
INTO X_DST_SEGMENT1,X_DST_SEGMENT2,X_DST_SEGMENT3,X_DST_SEGMENT4,X_DST_SEGMENT5
FROM
GL_CODE_COMBINATIONS_KFV GCC
WHERE CODE_COMBINATION_ID=X_account_id;
EXCEPTION WHEN OTHERS THEN
X_PROCESS_FLAG:=’E’;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG = 0,PROCESS_FLAG_C=’E’ where record_id=x.record_id ;
COMMIT;
END;
END IF;
END IF;
IF X_PROCESS_FLAG=’S’ THEN
SELECT mtl_material_transactions_s.nextval
INTO X_TRANSACTION_INTERFACE_ID
FROM DUAL;
————————————————————–
—————————————————————-
BEGIN
update XXINV_MTL_TRX_ONHAND_QTY_INT
set
SOURCE_HEADER_ID=x.SOURCE_HEADER_ID
, SOURCE_LINE_ID=x.SOURCE_LINE_ID
, TRANSACTION_MODE=3
, SOURCE_CODE=’Alias Issue’
, PROCESS_FLAG=1
, LOCK_FLAG=2
, LOCATOR_ID=X_LOCATOR_ID
, TRANSACTION_INTERFACE_ID=X_TRANSACTION_INTERFACE_ID
, TRANSACTION_DATE=x.TRANSACTION_DATE
, INVENTORY_ITEM_ID=X_INV_ITEM_ID
, TRANSACTION_UOM=X_PRIMARY_UOM_CODE
, ORGANIZATION_ID=X_ORGANIZATION_ID
, SUBINVENTORY_CODE=x.SUBINVENTORY
, TRANSACTION_QUANTITY=x.TRANSACTION_QUANTITY
, TRANSACTION_TYPE_ID=X_TRANSACTION_TYPE_ID
, TRANSACTION_SOURCE_TYPE_ID=X_TXN_SOURCE_TYPE_ID
, DISTRIBUTION_ACCOUNT_ID=X_CODE_COMBINATION_ID
, TRANSACTION_SOURCE_NAME=X_trx_source
, TRANSACTION_SOURCE_ID=X_disposition_id
, CREATED_BY=NVL(FND_GLOBAL.USER_ID,-1)
, CREATION_DATE=SYSDATE
, LAST_UPDATED_BY=NVL(FND_GLOBAL.USER_ID,-1)
, LAST_UPDATE_DATE=SYSDATE
, LAST_UPDATE_LOGIN=NVL(FND_GLOBAL.LOGIN_ID,-1)
, dst_segment1=X_DST_SEGMENT1
, dst_segment2=X_DST_SEGMENT2
, dst_segment3=X_DST_SEGMENT3
, dst_segment4=X_DST_SEGMENT4
, dst_segment5=X_DST_SEGMENT5
where RECORD_ID=x.RECORD_ID;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,’NUMBER OF RECORDS INSERTED : ‘||X_COUNT);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,’RECORDS Insertion into Staging MTL_TRANSACTION_INTERFACE failed’);
DELETE
FROM XXINV_MTL_TRX_ONHAND_QTY_INT
WHERE RECORD_ID = x.RECORD_ID;
UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT SET PROCESS_FLAG=0,PROCESS_FLAG_C=’E’;
COMMIT;
END;
END IF;
END LOOP;
END IF;
END;
EXCEPTION WHEN OTHERS THEN
raise_application_error (-20000, SQLCODE || ‘:’ || SQLERRM);
END XXWEBADI_MTL_ONHAND_QTY_PROC;
PROCEDURE XXWEBADI_TRANSFORMATION_PROC(P_BATCH_MODE VARCHAR2,P_TRX_BATCH_ID NUMBER)
IS
CURSOR C1 IS
SELECT LOOKUP_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE = ‘XXDS_WEBADI_PO_REQUISITION_LP’
AND ENABLED_FLAG = ‘Y’
AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL (END_DATE_ACTIVE, SYSDATE);
LN_COUNT NUMBER := 0;
LV_STRING VARCHAR2 (32000);
BEGIN
IF P_BATCH_MODE = ‘Y’ THEN
BEGIN
LV_STRING := ‘UPDATE XXINV_MTL_TRX_ONHAND_QTY_INT’;
FOR I IN C1 LOOP
IF LN_COUNT 0 THEN
LV_STRING := LV_STRING || ‘,’;
END IF;
IF I.ATTRIBUTE_CATEGORY = ‘SUBSTR’ THEN
LV_STRING :=
LV_STRING || I.LOOKUP_CODE || ‘ = ‘ || I.ATTRIBUTE_CATEGORY || ‘(‘ || I.ATTRIBUTE1 || ‘,’ || I.ATTRIBUTE2
|| ‘,’ || I.ATTRIBUTE3|| ‘)’;
ELSIF I.ATTRIBUTE_CATEGORY = ‘CONCATENATE’ THEN
LV_STRING := LV_STRING || I.LOOKUP_CODE || ‘ = ‘ || I.ATTRIBUTE1;
ELSIF I.ATTRIBUTE_CATEGORY = ‘PREFIX’ THEN
LV_STRING := LV_STRING || I.LOOKUP_CODE || ‘ = ‘ || I.ATTRIBUTE2 || I.ATTRIBUTE1;
ELSIF I.ATTRIBUTE_CATEGORY = ‘SUFFIX’ THEN
LV_STRING := LV_STRING || I.LOOKUP_CODE || ‘ = ‘ || I.ATTRIBUTE1 || I.ATTRIBUTE2;
END IF;
LN_COUNT := LN_COUNT + 1;
END LOOP;
LV_STRING := LV_STRING || ‘ WHERE batch_mode = ‘ || P_BATCH_MODE;
END;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG, LV_STRING);
EXECUTE IMMEDIATE LV_STRING;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
END IF;
–DELETE XXDS_PO_REQUISITIONS_INTERFACE WHERE BATCH_ID = IP_BATCH_ID;
END XXWEBADI_TRANSFORMATION_PROC;
END;
/
