/***************************************************************************************
* TYPE : Script *
* NAME : PICK_RELESE.sql *
* PURPOSE : The script will pick release the orders which are in B, R status *
* *
* Author Date Ver Description *
* -------- ----------- ---- -------------------------- ---------------------------*
* XXXXXXX 20-Feb-2020 1.00 Created *
***************************************************************************************/
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE 100000;
ALTER SESSION SET current_schema=apps;
DECLARE
CURSOR CUR_GET_DATA IS
SELECT WDD.SOURCE_LINE_ID P_LINE_ID,
WDD.INVENTORY_ITEM_ID P_ITEM_ID,
WDD.DELIVERY_DETAIL_ID P_DELIVERY_DETAIL_ID,
WDD.BATCH_ID P_BATCH_ID,
WDD.SOURCE_HEADER_ID P_HEADER_ID,
WDD.MOVE_ORDER_LINE_ID P_MOVE_ORDER_LINE_ID
FROM APPS.WSH_DELIVERY_DETAILS WDD,
OE_ORDER_HEADERS_ALL OOH
WHERE WDD.SOURCE_HEADER_ID = OOH.HEADER_ID
AND ooh.org_id = 82
and wdd.organization_id = 331
--AND ooh.header_id in (137508997)
and ooh.header_id in (135503888)
AND WDD.RELEASED_STATUS IN ('B','R');
CURSOR CUR_OM_GET_ORDER_DATA(P_OE_HEADER_ID NUMBER) IS
SELECT OEOHA.HEADER_ID,
OEOHA.ORDER_NUMBER,
OEOHA.ATTRIBUTE2 PB_ID,
OEOHA.SHIPPING_METHOD_CODE,
OEOHA.ORDER_TYPE_ID,
OEOHA.SOLD_TO_ORG_ID,
OEOHA.SHIP_FROM_ORG_ID,
OEOHA.SALES_CHANNEL_CODE,
OEOHA.ORDERED_DATE,
OEOHA.REQUEST_DATE,
OEOHA.ATTRIBUTE17 OPMAIN_WHSE_ID,
OTTA.ATTRIBUTE13 AUTOSHIP_CONFIRM
FROM OE_ORDER_HEADERS_ALL OEOHA, OE_TRANSACTION_TYPES_ALL OTTA
WHERE OEOHA.ORDER_TYPE_ID = OTTA.TRANSACTION_TYPE_ID
AND OEOHA.ATTRIBUTE7 IS NOT NULL
AND OEOHA.BOOKED_FLAG = 'Y'
AND OEOHA.CANCELLED_FLAG != 'Y'
AND OEOHA.HEADER_ID = P_OE_HEADER_ID;
CURSOR CUR_GET_DELIVERIES(P_NUM_ORDER_HEADER_ID NUMBER, P_ORDER_LINE_ID NUMBER, P_INV_ITEM_ID NUMBER, P_DELIVERY_DET_ID NUMBER) IS
SELECT WSHDV.SOURCE_LINE_NUMBER,
WSHDV.ITEM_DESCRIPTION,
WSHDV.DELIVERY_DETAIL_ID,
WSHDV.INVENTORY_ITEM_ID,
WSHDV.SOURCE_LINE_ID,
WSHDV.DELIVERY_ID,
WSHDV.REQUESTED_QUANTITY_UOM,
WSHDV.BATCH_ID,
WSHDV.ORIGINAL_SUBINVENTORY,
WSHDV.SRC_REQUESTED_QUANTITY ORDERED_QUANTITY,
WSHDV.REQUESTED_QUANTITY,
WSHDV.RELEASED_STATUS_NAME,
WSHDV.RELEASED_STATUS
FROM WSH_DELIVERABLES_V WSHDV, OE_ORDER_LINES_ALL OEOLA
WHERE OEOLA.LINE_ID = WSHDV.SOURCE_LINE_ID
AND WSHDV.SOURCE_HEADER_ID = OEOLA.HEADER_ID
AND OEOLA.HEADER_ID = P_NUM_ORDER_HEADER_ID
AND OEOLA.LINE_ID = P_ORDER_LINE_ID
AND WSHDV.RELEASED_STATUS IN ('B','R')
AND WSHDV.INVENTORY_ITEM_ID = P_INV_ITEM_ID
AND WSHDV.DELIVERY_DETAIL_ID = P_DELIVERY_DET_ID
ORDER BY WSHDV.REQUESTED_QUANTITY;
-- AND NVL(WSHDV.BATCH_ID, P_NUM_BATCH_ID) = P_NUM_BATCH_ID;
CURSOR DELETE_RESERVATION_CUR(P_NUM_LINE_ID NUMBER) IS
SELECT RESERVATION_ID
FROM MTL_RESERVATIONS
WHERE DEMAND_SOURCE_LINE_ID = P_NUM_LINE_ID;
L_NUM_BATCH_ID NUMBER;
L_CHR_PL_RESULT VARCHAR2(100);
L_CHR_PL_PHASE VARCHAR2(30);
L_CHR_PL_SKIP VARCHAR2(1) := 'N';
L_CHR_DISTRIBUTOR_ID VARCHAR2(1000);
L_CHR_BATCH_NAME WSH_PICKING_BATCHES.NAME%TYPE;
L_NUM_HEADER_ID NUMBER;
L_NUM_SHIP_CONF_RULE_ID WSH_SHIP_CONFIRM_RULES.SHIP_CONFIRM_RULE_ID%TYPE;
L_NUM_USER_ID NUMBER;
L_NUM_RESP_ID NUMBER;
L_NUM_RESP_APPL_ID NUMBER;
L_CHR_TRACKING VARCHAR2(500);
L_CHR_BOX VARCHAR2(500);
L_DT_SHIP DATE;
L_CHR_SHIPWT VARCHAR2(150);
L_ORIGINAL_RSV_REC INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
L_TBL_SERIAL_NUM1 INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
L_NUM_REQUEST_ID NUMBER;
L_RETURN_STATUS VARCHAR2(2000);
L_MSG_DATA VARCHAR2(2000);
L_MSG_COUNT NUMBER;
LN_PICK_FROM_LOCATOR_ID NUMBER;
P_PICK_LOC_ACT VARCHAR2(500);
LC_SUBINV_CODE VARCHAR2(500);
BEGIN
L_NUM_USER_ID := 9009;
L_NUM_RESP_ID := 61563;
L_NUM_RESP_APPL_ID := 20044;
FND_GLOBAL.APPS_INITIALIZE(USER_ID => L_NUM_USER_ID,
RESP_ID => L_NUM_RESP_ID,
RESP_APPL_ID => L_NUM_RESP_APPL_ID);
FOR A IN CUR_GET_DATA LOOP
FOR REC_OM_GET_ORDER_DATA IN CUR_OM_GET_ORDER_DATA(A.P_HEADER_ID) LOOP
L_NUM_HEADER_ID := REC_OM_GET_ORDER_DATA.HEADER_ID;
---CURSOR CUR_GET_DELIVERIES(P_NUM_ORDER_HEADER_ID NUMBER, P_ORDER_LINE_ID NUMBER,P_INV_ITEM_ID NUMBER,P_DELIVERY_DET_ID NUMBER) IS
FOR J IN CUR_GET_DELIVERIES(REC_OM_GET_ORDER_DATA.HEADER_ID,
A.P_LINE_ID,
A.P_ITEM_ID,
A.P_DELIVERY_DETAIL_ID) LOOP
SELECT MTL_TXN_REQUEST_HEADERS_S.NEXTVAL
INTO L_NUM_BATCH_ID
FROM DUAL;
L_CHR_DISTRIBUTOR_ID := REC_OM_GET_ORDER_DATA.PB_ID;
L_CHR_BATCH_NAME := 'XX_PICK_' || TO_CHAR(L_NUM_BATCH_ID);
L_NUM_SHIP_CONF_RULE_ID := 0;
BEGIN
INSERT INTO WSH_PICKING_BATCHES
(BATCH_ID,
NAME,
BACKORDERS_ONLY_FLAG,
EXISTING_RSVS_ONLY_FLAG,
CUSTOMER_ID,
ORDER_HEADER_ID,
ORDER_LINE_ID,
ORDER_TYPE_ID,
INCLUDE_PLANNED_LINES,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
AUTO_PICK_CONFIRM_FLAG,
AUTOPACK_FLAG,
AUTOPACK_LEVEL,
AUTODETAIL_PR_FLAG,
AUTOCREATE_DELIVERY_FLAG,
SHIPMENT_PRIORITY_CODE,
SHIP_METHOD_CODE,
CARRIER_ID,
SHIP_TO_LOCATION_ID,
SHIP_SET_NUMBER,
SUBINVENTORY,
SHIP_FROM_LOCATION_ID,
DEFAULT_STAGE_SUBINVENTORY,
DEFAULT_STAGE_LOCATOR_ID,
PICK_FROM_SUBINVENTORY,
PICK_FROM_LOCATOR_ID,
PICK_GROUPING_RULE_ID,
PICK_SEQUENCE_RULE_ID,
PARTIAL_ALLOWED_FLAG,
SHIP_CONFIRM_RULE_ID)
VALUES
(L_NUM_BATCH_ID,
L_CHR_BATCH_NAME,
'O',
'N',
REC_OM_GET_ORDER_DATA.SOLD_TO_ORG_ID,
REC_OM_GET_ORDER_DATA.HEADER_ID,
J.SOURCE_LINE_ID,
REC_OM_GET_ORDER_DATA.ORDER_TYPE_ID,
'N',
SYSDATE,
-1,
SYSDATE,
-1,
-1,
-1,
-1,
SYSDATE,
-1,
'Y', -- AUTO PICK CONFIRM FLAG
'N',
0,
'Y',
'Y',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'XXXX69', --default_stage_subinventory,
56564, --default_stage_locator_id,
'XXXX21', -- pick_from_subinventory,
NULL,
-- pick_from_locator_id,
1006, -- pick_grouping_rule_id,
1006, --pick_sequence_rule_id,
NULL, -- partial_allowed_flag,
L_NUM_SHIP_CONF_RULE_ID);
dbms_output.put_line('l_num_batch_id ' || l_num_batch_id);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('INSERT FAILED' || SQLERRM);
--p_batch_id := 0;
END;
COMMIT;
begin
WSH_PICK_LIST.ONLINE_RELEASE(L_NUM_BATCH_ID,
L_CHR_PL_RESULT,
L_CHR_PL_PHASE,
L_CHR_PL_SKIP);
--p_batch_id := l_num_batch_id;
exception when others then
null;
end;
COMMIT;
END LOOP;
END LOOP;
END LOOP;
END;
/
