CREATE OR REPLACE PROCEDURE APPS.XX_XX_BATCH_CREATION (
ERRBUFF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_BATCH_NUMBER IN VARCHAR2,
P_WHSE_CODE IN VARCHAR2,
P_SHIFT_CODE IN NUMBER,
P_DATE IN VARCHAR2
)
AS
P_API_VERSION NUMBER DEFAULT 1 ;
P_VALIDATION_LEVEL NUMBER DEFAULT GME_API_PUB.MAX_ERRORS ;
P_INIT_MSG_LIST BOOLEAN DEFAULT FALSE ;
P_BATCH_TYPE NUMBER DEFAULT 0 ;
X_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
X_MESSAGE_COUNT NUMBER;
X_MESSAGE_LIST VARCHAR2 (1000);
X_RETURN_STATUS VARCHAR2 (1);
L_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
L_MSG_INDEX_OUT NUMBER;
XX_EXCEPTION_MATERIAL_TBL GME_API_PUB.UNALLOCATED_MATERIALS_TAB;
-- DECLARING TMS VARIABLES
V_ROUTE_CODE VARCHAR2 (100);
V_START_SHIFT_CODE NUMBER;
V_TM_ITEM_CODE VARCHAR2 (30);
V_TRIP_START_DATE DATE;
V_TM_ITEM_ID NUMBER;
V_UNLOAD_QUANTITY NUMBER;
V_ORGN_CODE VARCHAR2 (30);
V_WHSE_CODE VARCHAR2 (30);
V_ITEM_CODE VARCHAR2 (100);
V_LOT_NO NUMBER;
-- TMS VARIABLES DECLARATION ENDED
-- DECLARING VARIABLE FOR UPDATE WITH THE QUANTITY
L_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
X_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
L_VALUE_TAB GME_API_PUB.FIELD_VALUES_TAB;
L_INDEX NUMBER;
L_BATCH_ID GME_BATCH_HEADER.BATCH_ID%TYPE;
L_MATERIAL_DETAIL_ID GME_MATERIAL_DETAILS.MATERIAL_DETAIL_ID%TYPE;
V_PLANT_CODE VARCHAR2 (10);
V_ITEM_ID NUMBER;
V_NUM NUMBER := 2;
V_SCALE_TYPE NUMBER;
V_COST_ALLOC NUMBER;
V_CONTRIBUTE_YIELD_IND VARCHAR2 (10) := NULL;
V_ITEM_UOMS VARCHAR2 (10);
V_MATERIAL_DETAIL_ID NUMBER;
L_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
LX_DEF_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
L_TRANSACTIONS GME_API_PUB.INV_TRANS_REC_TAB;
L_TRANSACTION IC_TRAN_PND%ROWTYPE;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2 (1000);
V_CO_CODE VARCHAR2 (100);
V_WHSE_NAME VARCHAR2 (1000);
V_ACTUAL_DATE DATE;
P_RECEIPE_NO VARCHAR2 (100);
P_RECEIPE_ID NUMBER;
P_RECEIPE_RULE NUMBER;
P_RECEIPE_VERSION NUMBER;
REJECTION_FLAG NUMBER := 0;
-- ENDED
--- DECLARE CURSOR TO GET THE ITEM AND THE QUANTITY DETAILS FOR WHICH THE PRODUCTION BATCH NEEDS TO BE CREATED
CURSOR C_GET_TOTAL_QUANTITY
IS
SELECT --ROUTE_CODE, ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TTH.ATTRIBUTE5 RL_SECTION, -- TTH.ATTRIBUTE9 REASON_CD,
TRUNC (TRIP_START_DATE) TRIP_START_DATE,
TM_ITEM_ID,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY)
TOTAL_QUANTITY,
ORGN_CODE,
WHSE_CODE,
(SELECT ITEM_ID
FROM IC_ITEM_MST
WHERE ITEM_NO = TM_ITEM_CODE)
ITEM_ID
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
GROUP BY --ROUTE_CODE,
--ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRUNC (TRIP_START_DATE),
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
-- TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5;
-- CURSOR TO SUM THE ITEM QUANTITY FOR THE BATCH ALLOCATION
CURSOR C_INSERT_LINE_ALLOCATION (
P_ITEM_CODE VARCHAR2
)
IS
SELECT SUM (QTY) QTY, WHSE_1, REASON_CD
FROM ( SELECT NVL ( (SELECT WHSE_CODE
FROM XX_OPM_TM DOT
WHERE LOC_CODE = DEST_SUB_INVENTORY),
WHSE_CODE)
WHSE_1,
TTH.ATTRIBUTE9 REASON_CD,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY) QTY
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') =
P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
AND TM_ITEM_CODE = P_ITEM_CODE
GROUP BY ROUTE_CODE,
ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRIP_START_DATE,
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
SOURCE_SUB_INVENTORY,
TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5,
DEST_SUB_INVENTORY)
GROUP BY WHSE_1, REASON_CD;
-- END OF DECLARATION
BEGIN
-- GET THE DETAILS OF PLANT CODE,WAREHOUSE CODE AND ACTUAL DATE FROM TRUCK M SYSTEM
BEGIN
SELECT DISTINCT
DOT.ORGN_CODE,
DOT.WHSE_CODE,
TO_DATE (
TRUNC (TRIP_START_DATE)
|| ' '
|| TO_CHAR (SHIFT_START / 3600),
'dd-mon-yy hh24:mi'
)
TRIP_START_DATE
INTO V_PLANT_CODE, V_WHSE_CODE, V_ACTUAL_DATE
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT,
IC_WHSE_MST WHS,
MR_SHDY_DTL C
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND DOT.WHSE_CODE = WHS.WHSE_CODE
AND WHS.ATTRIBUTE1 = C.SHOPDAY_NO
AND START_SHIFT_CODE = P_SHIFT_CODE
AND C.SHIFT_NO = P_SHIFT_CODE
AND TO_CHAR ( (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND DOT.WHSE_CODE = P_WHSE_CODE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from TMS =' || SQLERRM
);
END;
-- TO GET THE DETAIL OF THE COMPANY CODE BASED ON THE PLANT CODE FETCHED ABOVE
BEGIN
SELECT CO_CODE
INTO V_CO_CODE
FROM SY_ORGN_MST
WHERE ORGN_CODE = V_PLANT_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from CO_CODE' || SQLERRM
);
END;
-- TO GET THE NAME OF THE WAREHOUSE FROM THE WAREHOUSE CODE
BEGIN
SELECT WHSE_NAME
INTO V_WHSE_NAME
FROM IC_WHSE_MST
WHERE WHSE_CODE = V_WHSE_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from WHSE_NAME' || SQLERRM
);
END;
---------------------FETCH RECEIPE INFORMATION ----------------------------
BEGIN
SELECT RECIPE_ID, RECIPE_NO, RECIPE_VERSION
INTO P_RECEIPE_ID, P_RECEIPE_NO, P_RECEIPE_VERSION
FROM GMD_RECIPES
WHERE RECIPE_VERSION = (SELECT MAX (RECIPE_VERSION)
FROM GMD_RECIPES
WHERE ATTRIBUTE1 = P_WHSE_CODE)
AND ATTRIBUTE1 = P_WHSE_CODE;
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no ' || P_RECEIPE_ID || ' ' || P_RECEIPE_NO
);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no not fetched' || SQLERRM
);
END;
---------------------------------------------------------------------
------------------FETCHING RECIPE VALIDATION RULE --------------------------------
BEGIN
SELECT RECIPE_VALIDITY_RULE_ID
INTO P_RECEIPE_RULE
FROM GMD_RECIPE_VALIDITY_RULES
WHERE RECIPE_ID = P_RECEIPE_ID;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'receipe validity rule ' || P_RECEIPE_RULE);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe validity rule not fetched due to ' || SQLERRM
);
END;
-- CREATION OF BATCH
----------------------------------------------------------------------------------
L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE;
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
FND_GLOBAL.APPS_INITIALIZE (
USER_ID => FND_PROFILE.VALUE ('USER_ID'),
RESP_ID => FND_PROFILE.VALUE ('RESP_ID'),
RESP_APPL_ID => FND_PROFILE.VALUE ('RESP_APPL_ID')
);
GME_API_PUB.CREATE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_BATCH_SIZE => NULL,
P_BATCH_SIZE_UOM => NULL,
P_CREATION_MODE => 'RECIPE',
P_RECIPE_ID => NULL,
P_RECIPE_NO => P_RECEIPE_NO,
P_RECIPE_VERSION => P_RECEIPE_VERSION,
P_PRODUCT_NO => NULL,
P_PRODUCT_ID => NULL,
P_IGNORE_QTY_BELOW_CAP => TRUE,
P_IGNORE_SHORTAGES => TRUE,
P_USE_SHOP_CAL => 0,
P_CONTIGUITY_OVERRIDE => 0,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
----------------WRITE THE DETAILS OF THE BATCH IN THE LOG FILE ---------------------------
FND_FILE.PUT_LINE (FND_FILE.LOG, 'create_batch =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'create_batch x_batch_header.batch_id= '
|| TO_CHAR (X_BATCH_HEADER.BATCH_ID)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.plant_code =' || X_BATCH_HEADER.PLANT_CODE,
1,
255)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.batch_no =' || X_BATCH_HEADER.BATCH_NO, 1, 255)
);
COMMIT;
-- THE BELOW CODE WILL STORE THE ITEM ID OF REJECTION ITEM IN V_ITEM_ID
SELECT ITEM_ID
INTO V_ITEM_ID
FROM FM_MATL_DTL
WHERE FORMULA_ID IN (SELECT FORMULA_ID
FROM GMD_RECIPES
WHERE RECIPE_NO LIKE 'M-H-2 TOP%')
AND LINE_TYPE = 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
-- THE BELOW CODE WILL FIND THE SCALE TYPE ,COST ALLOCATION,CONTRIBUTE TOWARDS YIELD INDICATOR AND UOM ASSOCIATED
-- WITH THE REJECTION ITEM CODE.SAME SET OF VALUES WILL BE USED FOR ANY NEW MATERIAL LINE INSERTION
BEGIN
SELECT SCALE_TYPE,
COST_ALLOC,
CONTRIBUTE_YIELD_IND,
ITEM_UM
INTO V_SCALE_TYPE,
V_COST_ALLOC,
V_CONTRIBUTE_YIELD_IND,
V_ITEM_UOMS
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'ERRROR WHILE FETCHING MATERIAL DETAILS');
END;
-- START THE UPDATE OF THE BATCH MATERIAL LINES BASED ON THE ITEM AND QUANTITY RETURNED FROM THE TMS QUERY
FOR I IN C_GET_TOTAL_QUANTITY
LOOP
IF I.ITEM_ID = V_ITEM_ID -- I.E. IF REJECTION ITEM IS ALSO A PART OF THE TMS QUERY
THEN
REJECTION_FLAG := 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
-- IF REJECTION ITEM IS A PART OF THE TMS QUERY THEN FETCH THE MATERIAL DETAIL ID OF THE REJECTION ITEM FROM THE
-- NEWLY CREATED BATCH TO UPDATE ITS QUANTITY
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF THE ABOVE LINE UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-------------------------INSERT THE ATTRIBUTE3(RL SECTION) VALUE -----------------------------------
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'ATTRIBUTE3';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF RL SECTION UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;
---------------INSERT INTO LINE ALLOCATION FOR THE REJECTION ITEM
FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
L_TRAN_ROW.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////
GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
);
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
COMMIT;
-----------------INSERT END
-- IF THE ITEMS RETURNED FROM THE TRUCKING MODULE QUERY ARE NOT REJECTION ITEM INSERT THEM IN THE NEWLY CREATED BATCH
ELSE
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
BEGIN
SELECT MAX (MATERIAL_DETAIL_ID)
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID AND LINE_TYPE = 1;
END;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'mateiral detail id' || L_MATERIAL_DETAIL_ID);
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
GME_API_PUB.INSERT_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_BATCHSTEP_NO => NULL,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_message_list =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_return_status =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
V_MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_TRANSACTIONS (1).ITEM_ID := I.ITEM_ID;
L_TRANSACTIONS (1).LINE_ID := V_MATERIAL_DETAIL_ID;
L_TRANSACTIONS (1).CO_CODE := V_CO_CODE;
L_TRANSACTIONS (1).WHSE_CODE := V_WHSE_CODE;
L_TRANSACTIONS (1).LOT_ID := 0;
L_TRANSACTIONS (1).LOCATION := 'NONE';
L_TRANSACTIONS (1).DOC_ID := L_BATCH_ID;
L_TRANSACTIONS (1).DOC_TYPE := 'PROD';
L_TRANSACTIONS (1).LINE_TYPE := 1;
L_TRANSACTIONS (1).TRANS_QTY := 0;
L_TRANSACTIONS (1).TRANS_UM := V_ITEM_UOMS;
L_TRANSACTIONS (1).TRANS_DATE := V_ACTUAL_DATE;
L_TRANSACTIONS (1).ORGN_CODE := V_PLANT_CODE;
GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION (
P_API_VERSION => 1,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_TRAN_REC => L_TRANSACTIONS (1),
X_TRAN_ROW => L_TRANSACTION,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
COMMIT;
DBMS_OUTPUT.PUT_LINE (X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Return status of create pending transaction'
|| X_RETURN_STATUS
|| ' '
|| X_MSG_COUNT
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Create pending transaction else'
|| SUBSTR (X_MSG_DATA, LENGTH (X_MSG_DATA) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'ASASDASDASDAS' || X_MATERIAL_DETAIL.MATERIAL_DETAIL_ID
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;
-- TO DO LINE ALLOCATION OF THE ITEMS SELECTED ABOVE
---------------INSERT INTO LINE ALLOCATION
FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
L_TRAN_ROW.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////
-- /////////////////////////////////////////////////////////////
-- CALL API METHOD
-- /////////////////////////////////////////////////////////////
GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
);
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
-----------------------------END OF LINE ALLOCATION
-- END OF CREATE
V_NUM := V_NUM + 1;
END IF;
END LOOP;
V_NUM := 0;
--END IF ;
--------------------------------------------RELEASE THE BATCH -------------------------------------------------------------
L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE; --'DBP';
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
---------------------------------CALL THE API----------------------------------------------------
GME_API_PUB.RELEASE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_IGNORE_SHORTAGES => TRUE,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);
COMMIT;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RELEASE BATCH' || X_RETURN_STATUS);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'RELEASE BATCH =' || TO_CHAR (X_MESSAGE_COUNT));
------------------------------------------------------------------------------------------------
-- IF THE REJECTION ITEM IS NOT A PART OF THE TMS QUERY THEN MAKE IT 0
IF REJECTION_FLAG = 0
THEN
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.WIP_PLAN_QTY := 0;
L_MATERIAL_DETAIL.ITEM_ID := V_ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'WIP_PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= ' || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
END IF;
END;
/
ERRBUFF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_BATCH_NUMBER IN VARCHAR2,
P_WHSE_CODE IN VARCHAR2,
P_SHIFT_CODE IN NUMBER,
P_DATE IN VARCHAR2
)
AS
P_API_VERSION NUMBER DEFAULT 1 ;
P_VALIDATION_LEVEL NUMBER DEFAULT GME_API_PUB.MAX_ERRORS ;
P_INIT_MSG_LIST BOOLEAN DEFAULT FALSE ;
P_BATCH_TYPE NUMBER DEFAULT 0 ;
X_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
X_MESSAGE_COUNT NUMBER;
X_MESSAGE_LIST VARCHAR2 (1000);
X_RETURN_STATUS VARCHAR2 (1);
L_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
L_MSG_INDEX_OUT NUMBER;
XX_EXCEPTION_MATERIAL_TBL GME_API_PUB.UNALLOCATED_MATERIALS_TAB;
-- DECLARING TMS VARIABLES
V_ROUTE_CODE VARCHAR2 (100);
V_START_SHIFT_CODE NUMBER;
V_TM_ITEM_CODE VARCHAR2 (30);
V_TRIP_START_DATE DATE;
V_TM_ITEM_ID NUMBER;
V_UNLOAD_QUANTITY NUMBER;
V_ORGN_CODE VARCHAR2 (30);
V_WHSE_CODE VARCHAR2 (30);
V_ITEM_CODE VARCHAR2 (100);
V_LOT_NO NUMBER;
-- TMS VARIABLES DECLARATION ENDED
-- DECLARING VARIABLE FOR UPDATE WITH THE QUANTITY
L_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
X_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
L_VALUE_TAB GME_API_PUB.FIELD_VALUES_TAB;
L_INDEX NUMBER;
L_BATCH_ID GME_BATCH_HEADER.BATCH_ID%TYPE;
L_MATERIAL_DETAIL_ID GME_MATERIAL_DETAILS.MATERIAL_DETAIL_ID%TYPE;
V_PLANT_CODE VARCHAR2 (10);
V_ITEM_ID NUMBER;
V_NUM NUMBER := 2;
V_SCALE_TYPE NUMBER;
V_COST_ALLOC NUMBER;
V_CONTRIBUTE_YIELD_IND VARCHAR2 (10) := NULL;
V_ITEM_UOMS VARCHAR2 (10);
V_MATERIAL_DETAIL_ID NUMBER;
L_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
LX_DEF_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
L_TRANSACTIONS GME_API_PUB.INV_TRANS_REC_TAB;
L_TRANSACTION IC_TRAN_PND%ROWTYPE;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2 (1000);
V_CO_CODE VARCHAR2 (100);
V_WHSE_NAME VARCHAR2 (1000);
V_ACTUAL_DATE DATE;
P_RECEIPE_NO VARCHAR2 (100);
P_RECEIPE_ID NUMBER;
P_RECEIPE_RULE NUMBER;
P_RECEIPE_VERSION NUMBER;
REJECTION_FLAG NUMBER := 0;
-- ENDED
--- DECLARE CURSOR TO GET THE ITEM AND THE QUANTITY DETAILS FOR WHICH THE PRODUCTION BATCH NEEDS TO BE CREATED
CURSOR C_GET_TOTAL_QUANTITY
IS
SELECT --ROUTE_CODE, ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TTH.ATTRIBUTE5 RL_SECTION, -- TTH.ATTRIBUTE9 REASON_CD,
TRUNC (TRIP_START_DATE) TRIP_START_DATE,
TM_ITEM_ID,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY)
TOTAL_QUANTITY,
ORGN_CODE,
WHSE_CODE,
(SELECT ITEM_ID
FROM IC_ITEM_MST
WHERE ITEM_NO = TM_ITEM_CODE)
ITEM_ID
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
GROUP BY --ROUTE_CODE,
--ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRUNC (TRIP_START_DATE),
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
-- TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5;
-- CURSOR TO SUM THE ITEM QUANTITY FOR THE BATCH ALLOCATION
CURSOR C_INSERT_LINE_ALLOCATION (
P_ITEM_CODE VARCHAR2
)
IS
SELECT SUM (QTY) QTY, WHSE_1, REASON_CD
FROM ( SELECT NVL ( (SELECT WHSE_CODE
FROM XX_OPM_TM DOT
WHERE LOC_CODE = DEST_SUB_INVENTORY),
WHSE_CODE)
WHSE_1,
TTH.ATTRIBUTE9 REASON_CD,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY) QTY
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') =
P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
AND TM_ITEM_CODE = P_ITEM_CODE
GROUP BY ROUTE_CODE,
ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRIP_START_DATE,
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
SOURCE_SUB_INVENTORY,
TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5,
DEST_SUB_INVENTORY)
GROUP BY WHSE_1, REASON_CD;
-- END OF DECLARATION
BEGIN
-- GET THE DETAILS OF PLANT CODE,WAREHOUSE CODE AND ACTUAL DATE FROM TRUCK M SYSTEM
BEGIN
SELECT DISTINCT
DOT.ORGN_CODE,
DOT.WHSE_CODE,
TO_DATE (
TRUNC (TRIP_START_DATE)
|| ' '
|| TO_CHAR (SHIFT_START / 3600),
'dd-mon-yy hh24:mi'
)
TRIP_START_DATE
INTO V_PLANT_CODE, V_WHSE_CODE, V_ACTUAL_DATE
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT,
IC_WHSE_MST WHS,
MR_SHDY_DTL C
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND DOT.WHSE_CODE = WHS.WHSE_CODE
AND WHS.ATTRIBUTE1 = C.SHOPDAY_NO
AND START_SHIFT_CODE = P_SHIFT_CODE
AND C.SHIFT_NO = P_SHIFT_CODE
AND TO_CHAR ( (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND DOT.WHSE_CODE = P_WHSE_CODE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from TMS =' || SQLERRM
);
END;
-- TO GET THE DETAIL OF THE COMPANY CODE BASED ON THE PLANT CODE FETCHED ABOVE
BEGIN
SELECT CO_CODE
INTO V_CO_CODE
FROM SY_ORGN_MST
WHERE ORGN_CODE = V_PLANT_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from CO_CODE' || SQLERRM
);
END;
-- TO GET THE NAME OF THE WAREHOUSE FROM THE WAREHOUSE CODE
BEGIN
SELECT WHSE_NAME
INTO V_WHSE_NAME
FROM IC_WHSE_MST
WHERE WHSE_CODE = V_WHSE_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from WHSE_NAME' || SQLERRM
);
END;
---------------------FETCH RECEIPE INFORMATION ----------------------------
BEGIN
SELECT RECIPE_ID, RECIPE_NO, RECIPE_VERSION
INTO P_RECEIPE_ID, P_RECEIPE_NO, P_RECEIPE_VERSION
FROM GMD_RECIPES
WHERE RECIPE_VERSION = (SELECT MAX (RECIPE_VERSION)
FROM GMD_RECIPES
WHERE ATTRIBUTE1 = P_WHSE_CODE)
AND ATTRIBUTE1 = P_WHSE_CODE;
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no ' || P_RECEIPE_ID || ' ' || P_RECEIPE_NO
);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no not fetched' || SQLERRM
);
END;
---------------------------------------------------------------------
------------------FETCHING RECIPE VALIDATION RULE --------------------------------
BEGIN
SELECT RECIPE_VALIDITY_RULE_ID
INTO P_RECEIPE_RULE
FROM GMD_RECIPE_VALIDITY_RULES
WHERE RECIPE_ID = P_RECEIPE_ID;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'receipe validity rule ' || P_RECEIPE_RULE);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe validity rule not fetched due to ' || SQLERRM
);
END;
-- CREATION OF BATCH
----------------------------------------------------------------------------------
L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE;
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
FND_GLOBAL.APPS_INITIALIZE (
USER_ID => FND_PROFILE.VALUE ('USER_ID'),
RESP_ID => FND_PROFILE.VALUE ('RESP_ID'),
RESP_APPL_ID => FND_PROFILE.VALUE ('RESP_APPL_ID')
);
GME_API_PUB.CREATE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_BATCH_SIZE => NULL,
P_BATCH_SIZE_UOM => NULL,
P_CREATION_MODE => 'RECIPE',
P_RECIPE_ID => NULL,
P_RECIPE_NO => P_RECEIPE_NO,
P_RECIPE_VERSION => P_RECEIPE_VERSION,
P_PRODUCT_NO => NULL,
P_PRODUCT_ID => NULL,
P_IGNORE_QTY_BELOW_CAP => TRUE,
P_IGNORE_SHORTAGES => TRUE,
P_USE_SHOP_CAL => 0,
P_CONTIGUITY_OVERRIDE => 0,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
----------------WRITE THE DETAILS OF THE BATCH IN THE LOG FILE ---------------------------
FND_FILE.PUT_LINE (FND_FILE.LOG, 'create_batch =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'create_batch x_batch_header.batch_id= '
|| TO_CHAR (X_BATCH_HEADER.BATCH_ID)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.plant_code =' || X_BATCH_HEADER.PLANT_CODE,
1,
255)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.batch_no =' || X_BATCH_HEADER.BATCH_NO, 1, 255)
);
COMMIT;
-- THE BELOW CODE WILL STORE THE ITEM ID OF REJECTION ITEM IN V_ITEM_ID
SELECT ITEM_ID
INTO V_ITEM_ID
FROM FM_MATL_DTL
WHERE FORMULA_ID IN (SELECT FORMULA_ID
FROM GMD_RECIPES
WHERE RECIPE_NO LIKE 'M-H-2 TOP%')
AND LINE_TYPE = 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
-- THE BELOW CODE WILL FIND THE SCALE TYPE ,COST ALLOCATION,CONTRIBUTE TOWARDS YIELD INDICATOR AND UOM ASSOCIATED
-- WITH THE REJECTION ITEM CODE.SAME SET OF VALUES WILL BE USED FOR ANY NEW MATERIAL LINE INSERTION
BEGIN
SELECT SCALE_TYPE,
COST_ALLOC,
CONTRIBUTE_YIELD_IND,
ITEM_UM
INTO V_SCALE_TYPE,
V_COST_ALLOC,
V_CONTRIBUTE_YIELD_IND,
V_ITEM_UOMS
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'ERRROR WHILE FETCHING MATERIAL DETAILS');
END;
-- START THE UPDATE OF THE BATCH MATERIAL LINES BASED ON THE ITEM AND QUANTITY RETURNED FROM THE TMS QUERY
FOR I IN C_GET_TOTAL_QUANTITY
LOOP
IF I.ITEM_ID = V_ITEM_ID -- I.E. IF REJECTION ITEM IS ALSO A PART OF THE TMS QUERY
THEN
REJECTION_FLAG := 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
-- IF REJECTION ITEM IS A PART OF THE TMS QUERY THEN FETCH THE MATERIAL DETAIL ID OF THE REJECTION ITEM FROM THE
-- NEWLY CREATED BATCH TO UPDATE ITS QUANTITY
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF THE ABOVE LINE UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-------------------------INSERT THE ATTRIBUTE3(RL SECTION) VALUE -----------------------------------
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'ATTRIBUTE3';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF RL SECTION UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;
---------------INSERT INTO LINE ALLOCATION FOR THE REJECTION ITEM
FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
L_TRAN_ROW.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////
GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
);
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
COMMIT;
-----------------INSERT END
-- IF THE ITEMS RETURNED FROM THE TRUCKING MODULE QUERY ARE NOT REJECTION ITEM INSERT THEM IN THE NEWLY CREATED BATCH
ELSE
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
BEGIN
SELECT MAX (MATERIAL_DETAIL_ID)
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID AND LINE_TYPE = 1;
END;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'mateiral detail id' || L_MATERIAL_DETAIL_ID);
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
GME_API_PUB.INSERT_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_BATCHSTEP_NO => NULL,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_message_list =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_return_status =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
V_MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_TRANSACTIONS (1).ITEM_ID := I.ITEM_ID;
L_TRANSACTIONS (1).LINE_ID := V_MATERIAL_DETAIL_ID;
L_TRANSACTIONS (1).CO_CODE := V_CO_CODE;
L_TRANSACTIONS (1).WHSE_CODE := V_WHSE_CODE;
L_TRANSACTIONS (1).LOT_ID := 0;
L_TRANSACTIONS (1).LOCATION := 'NONE';
L_TRANSACTIONS (1).DOC_ID := L_BATCH_ID;
L_TRANSACTIONS (1).DOC_TYPE := 'PROD';
L_TRANSACTIONS (1).LINE_TYPE := 1;
L_TRANSACTIONS (1).TRANS_QTY := 0;
L_TRANSACTIONS (1).TRANS_UM := V_ITEM_UOMS;
L_TRANSACTIONS (1).TRANS_DATE := V_ACTUAL_DATE;
L_TRANSACTIONS (1).ORGN_CODE := V_PLANT_CODE;
GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION (
P_API_VERSION => 1,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_TRAN_REC => L_TRANSACTIONS (1),
X_TRAN_ROW => L_TRANSACTION,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
COMMIT;
DBMS_OUTPUT.PUT_LINE (X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Return status of create pending transaction'
|| X_RETURN_STATUS
|| ' '
|| X_MSG_COUNT
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Create pending transaction else'
|| SUBSTR (X_MSG_DATA, LENGTH (X_MSG_DATA) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'ASASDASDASDAS' || X_MATERIAL_DETAIL.MATERIAL_DETAIL_ID
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;
-- TO DO LINE ALLOCATION OF THE ITEMS SELECTED ABOVE
---------------INSERT INTO LINE ALLOCATION
FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
L_TRAN_ROW.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////
-- /////////////////////////////////////////////////////////////
-- CALL API METHOD
-- /////////////////////////////////////////////////////////////
GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
);
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
-----------------------------END OF LINE ALLOCATION
-- END OF CREATE
V_NUM := V_NUM + 1;
END IF;
END LOOP;
V_NUM := 0;
--END IF ;
--------------------------------------------RELEASE THE BATCH -------------------------------------------------------------
L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE; --'DBP';
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
---------------------------------CALL THE API----------------------------------------------------
GME_API_PUB.RELEASE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_IGNORE_SHORTAGES => TRUE,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);
COMMIT;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RELEASE BATCH' || X_RETURN_STATUS);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'RELEASE BATCH =' || TO_CHAR (X_MESSAGE_COUNT));
------------------------------------------------------------------------------------------------
-- IF THE REJECTION ITEM IS NOT A PART OF THE TMS QUERY THEN MAKE IT 0
IF REJECTION_FLAG = 0
THEN
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.WIP_PLAN_QTY := 0;
L_MATERIAL_DETAIL.ITEM_ID := V_ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'WIP_PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= ' || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
END IF;
END;
/
When i call API GME_API_PUB.UPDATE_MATERIAL_LINE to update actual quantity for an item after batch release , i got this message Cannot update actual quantity for Item.
ReplyDeletewhat the API i can used to update the actual quantity for finish product after batch release ?
Thanks,
Mohamed Ibrahim
Thanks for the statistics. I discovered your blog very interesting; our java online training supplier won the high commonplace name through international for its coaching for more information please check the site
ReplyDeleteOracle Fusion HCM Technical Coaching Center
Hi,
ReplyDeleteThis is very informative and interesting.
oracle fusion SCM online training