DECLARE
vOrgId NUMBER := 00;
xReturnStatus VARCHAR2(1);
xMsgCount NUMBER;
xMsgData VARCHAR2(2000);
pMagIdx NUMBER(15);
xNewBatchId NUMBER;
vBatchInfoRec wsh_picking_batches_pub.batch_info_rec;
vRuleId NUMBER;
vRuleName VARCHAR2(30);
vBatchPrefix VARCHAR2(200);
xRequestId NUMBER;
BEGIN
fnd_global.apps_initialize(user_id => 00, resp_id => 00,resp_appl_id => 00);
mo_global.set_policy_context('S', vOrgId);
mo_global.init('ONT');
vBatchInfoRec.Delivery_Id := 00; --wsh_new_deliveries.delivery_id
vBatchInfoRec.Backorders_Only_Flag := 'I'; --E:Unrelease / O:Backordered / I:All / M:Replenishment Completed
vBatchInfoRec.Existing_Rsvs_Only_Flag := 'N';
vBatchInfoRec.Customer_Id := 00; --wsh_new_deliveries.customer_id
--vBatchInfoRec.Order_Header_Id := 00;
vBatchInfoRec.Default_Stage_Subinventory := 'XX'; ----mtl_secondary_inventories.secondary_inventory_name
vBatchInfoRec.Pick_From_Subinventory := 'XX'; --mtl_secondary_inventories.secondary_inventory_name
vBatchInfoRec.Organization_Id := 00;
vBatchInfoRec.Include_Planned_Lines := 'N';
vBatchInfoRec.Autocreate_Delivery_Flag := 'Y';
vBatchInfoRec.Autodetail_Pr_Flag := 'Y';
vBatchInfoRec.allocation_method := 'I';
--vBatchInfoRec.Pick_From_locator_Id := NULL;
vBatchInfoRec.Auto_Pick_Confirm_Flag := 'Y';
vBatchInfoRec.Autopack_Flag := 'Y';
vBatchInfoRec.actual_departure_date := fnd_date.canonical_to_date('2020/01/01');
--vBatchInfoRec.Ship_Confirm_Rule_Id := 00;
--vBatchInfoRec.Ship_Confirm_Rule_Name := 'XX';
--vBatchInfoRec.Document_Set_Id := 00;
--vBatchInfoRec.Pick_Grouping_Rule_Id := 00;
vRuleId := 00; --wsh_picking_rules_v.picking_rule_id
vRuleName := 'XX'; --wsh_picking_rules_v.picking_rule_name
vBatchPrefix := NULL;
wsh_picking_batches_pub.create_batch(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => xReturnStatus,
x_msg_count => xMsgCount,
x_msg_data => xMsgData,
p_rule_id => vRuleId,
p_rule_name => vRuleName,
p_batch_rec => vBatchInfoRec,
p_batch_prefix => vBatchPrefix,
x_batch_id => xNewBatchId);
IF xReturnStatus = fnd_api.g_ret_sts_success THEN
DBMS_OUTPUT.put_line('Pick Release Batch Got Created Sucessfully ' ||
xNewBatchId);
ELSE
DBMS_OUTPUT.put_line('Message count ' || xMsgCount);
IF xMsgCount = 1 THEN
DBMS_OUTPUT.put_line('Message data ' || xMsgData);
ELSIF xMsgCount > 1 THEN
LOOP
pMagIdx := pMagIdx + 1;
xMsgData := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
IF xMsgData IS NULL THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line('Message' || pMagIdx || '---' || xMsgData);
END LOOP;
END IF;
END IF;
wsh_picking_batches_pub.release_batch(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => xReturnStatus,
x_msg_count => xMsgCount,
x_msg_data => xMsgData,
p_batch_id => xNewBatchId,
p_batch_name => NULL,
p_log_level => 1,
--p_release_mode => 'CONCURRENT',
p_release_mode => 'ONLINE',
-- (online or concurrent)
x_request_id => xRequestId);
IF xReturnStatus = fnd_api.g_ret_sts_success THEN
dbms_output.put_line('Pick Selection List Generation ' || xRequestId);
ELSE
DBMS_OUTPUT.put_line('Message count ' || xMsgCount);
IF xMsgCount = 1 THEN
DBMS_OUTPUT.put_line('Message data ' || xMsgData);
ELSIF xMsgCount > 1 THEN
LOOP
pMagIdx := pMagIdx + 1;
xMsgData := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE);
IF xMsgData IS NULL THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line('Message' || pMagIdx || '---' || xMsgData);
END LOOP;
END IF;
END IF;
END;
2020/11/17
2020/09/14
[Performance] cursor loop vs collection forall
--建立暫存表格
CREATE TABLE cux_objects AS SELECT owner, object_name name, object_type type FROM all_objects WHERE 1 = 0
--測試 1 (Cursor & Loop) => about 40 sec
DECLARE
vOwner all_objects.owner%TYPE;
vObjectName all_objects.object_name%TYPE;
vObjectType all_objects.object_type%TYPE;
vStart DATE;
vEnd DATE;
CURSOR curObject IS
SELECT owner,
object_name,
object_type
FROM all_objects;
BEGIN
vStart := SYSTIMESTAMP;
OPEN curObject;
LOOP
FETCH curObject
INTO vOwner,
vObjectName,
vObjectType;
EXIT WHEN curObject%NOTFOUND;
INSERT INTO cux_objects
VALUES
(vOwner,
vObjectName,
vObjectType);
END LOOP;
CLOSE curObject;
COMMIT;
vEnd := SYSTIMESTAMP;
dbms_output.put_line((vEnd - vStart) * 86400);
END;
--測試 2 (Collection & forall) => about 6 sec
DECLARE
TYPE tOwner IS TABLE OF all_objects.owner%TYPE;
TYPE tObjectName IS TABLE OF all_objects.object_name%TYPE;
TYPE tObjectType IS TABLE OF all_objects.object_type%TYPE;
vOwner tOwner;
vObjectName tObjectName;
vObjectType tObjectType;
vStart DATE;
vEnd DATE;
BEGIN
vStart := SYSTIMESTAMP;
SELECT owner,
object_name,
object_type
BULK COLLECT
INTO vOwner,
vObjectName,
vObjectType
FROM all_objects;
FORALL idx IN vObjectName.first .. vObjectName.last
INSERT INTO cux_objects
VALUES
(vOwner(idx),
vObjectName(idx),
vObjectType(idx));
COMMIT;
vEnd := SYSTIMESTAMP;
dbms_output.put_line ((vEnd - vStart) * 86400);
END;
2020/09/01
How to [Purge Designator ]
Sample :
--MDS [SCHEDULE_DESIG]
--Forecast [FORECAST_DESIG]
SET serveroutput ON;
DECLARE
xRequestId NUMBER;
vOrganizationId NUMBER := 00;
vScheduleDesignator VARCHAR2(10) := 'MDS';
BEGIN
fnd_global.apps_initialize(user_id => 00, resp_id => 00,
resp_appl_id => 00);
xRequestId := Fnd_Request.Submit_Request('MRP', 'MRCPRG', '', '', FALSE,
vOrganizationId,
vScheduleDesignator,
'SCHEDULE_DESIG', chr(0));
IF (xRequestId > 0) THEN
COMMIT;
dbms_output.put_line('Request Id : ' || xRequestId);
ELSE
dbms_output.put_line('Failed');
END IF;
END;
-- Others
SET serveroutput ON;
DECLARE
xRequestId NUMBER;
xRequestReturnStatus BOOLEAN;
xPhase VARCHAR2(40);
xStatus VARCHAR2(40);
xDevPhase VARCHAR2(40);
xDevStatus VARCHAR2(40);
xMessage VARCHAR2(2000);
vOrganizationId NUMBER := 00;
vScheduleDesignator VARCHAR2(10) := 'MDS';
BEGIN
fnd_global.apps_initialize(user_id => 00, resp_id => 00,
resp_appl_id => 00);
xRequestId := Fnd_Request.Submit_Request('MRP', 'MRCPRG', '', '', FALSE,
vOrganizationId,
vScheduleDesignator,
'SCHEDULE_DESIG', chr(0));
IF (xRequestId > 0) THEN
COMMIT;
dbms_output.put_line('Request Id : ' || xRequestId);
LOOP
xRequestReturnStatus :=fnd_concurrent.wait_for_request(request_id => xRequestId,
INTERVAL => 60, max_wait => 0,
phase => xPhase, status => xStatus,
dev_phase => xDevPhase,
dev_status => xDevStatus,
message => xMessage);
EXIT WHEN upper(xPhase) = 'COMPLETED' OR upper(xStatus) IN('CANCELLED',
'ERROR',
'TERMINATED');
END LOOP;
ELSE
dbms_output.put_line(' Failed ');
END IF;
END;
訂閱：
文章 (Atom)
發表文章
