[Home] [Help]
178: cs BINARY_INTEGER;
179:
180: BEGIN
181:
182: WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_ROWS.Init');
183:
184: IF initialized = TRUE THEN
185: WSH_UTIL.Write_Log('Package already initialized for session');
186: RETURN SUCCESS;
181:
182: WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_ROWS.Init');
183:
184: IF initialized = TRUE THEN
185: WSH_UTIL.Write_Log('Package already initialized for session');
186: RETURN SUCCESS;
187: END IF;
188:
189: -- Store the backorder mode for the session
188:
189: -- Store the backorder mode for the session
190: backorder_mode := WSH_PR_PICKING_SESSION.backorders_flag;
191: IF backorder_mode NOT IN ('E','I','O') THEN
192: WSH_UTIL.Write_Log('Invalid backorder mode.');
193: RETURN FAILURE;
194: END IF;
195:
196: -- Get the primary release sequence rule attribute
197: primary_rsr := WSH_PR_PICKING_SESSION.primary_rsr;
198:
199: -- If backorders are excluded or included, must process unreleased lines
200: IF backorder_mode in ('E', 'I') THEN
201: WSH_UTIL.Write_Log('Calling Open_Unreleased_SQL_Cursor...');
202: cs := Open_Unreleased_SQL_Cursor;
203: IF cs = FAILURE THEN
204: WSH_UTIL.Write_Log('Error in Open_Unreleased_SQL_Cursor');
205: RETURN FAILURE;
200: IF backorder_mode in ('E', 'I') THEN
201: WSH_UTIL.Write_Log('Calling Open_Unreleased_SQL_Cursor...');
202: cs := Open_Unreleased_SQL_Cursor;
203: IF cs = FAILURE THEN
204: WSH_UTIL.Write_Log('Error in Open_Unreleased_SQL_Cursor');
205: RETURN FAILURE;
206: END IF;
207: END IF;
208:
207: END IF;
208:
209: -- If backorders are included or only, must process backordered lines
210: IF backorder_mode in ('I', 'O') THEN
211: WSH_UTIL.Write_Log('Calling Open_Backordered_SQL_Cursor...');
212: cs := Open_Backordered_SQL_Cursor;
213: IF cs = FAILURE THEN
214: WSH_UTIL.Write_Log('Error in Open_Backordered_SQL_Cursor');
215: RETURN FAILURE;
210: IF backorder_mode in ('I', 'O') THEN
211: WSH_UTIL.Write_Log('Calling Open_Backordered_SQL_Cursor...');
212: cs := Open_Backordered_SQL_Cursor;
213: IF cs = FAILURE THEN
214: WSH_UTIL.Write_Log('Error in Open_Backordered_SQL_Cursor');
215: RETURN FAILURE;
216: END IF;
217: END IF;
218:
279:
280: cs BINARY_INTEGER;
281:
282: BEGIN
283: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Unreleased_SQL_Cursor');
284:
285: -- Call to dynamically create SQL for unreleased lines
286: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Unreleased_Line_Details...');
287: cs := WSH_PR_PICKING_SESSION.Unreleased_Line_Details;
282: BEGIN
283: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Unreleased_SQL_Cursor');
284:
285: -- Call to dynamically create SQL for unreleased lines
286: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Unreleased_Line_Details...');
287: cs := WSH_PR_PICKING_SESSION.Unreleased_Line_Details;
288: IF cs = FAILURE THEN
289: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Unreleased_Line_Details');
290: RETURN FAILURE;
285: -- Call to dynamically create SQL for unreleased lines
286: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Unreleased_Line_Details...');
287: cs := WSH_PR_PICKING_SESSION.Unreleased_Line_Details;
288: IF cs = FAILURE THEN
289: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Unreleased_Line_Details');
290: RETURN FAILURE;
291: END IF;
292:
293: -- Fetch the unreleased_SQL string
290: RETURN FAILURE;
291: END IF;
292:
293: -- Fetch the unreleased_SQL string
294: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for unreleased SQL');
295: unreleased_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('UNRELEASED_SQL');
296: IF unreleased_SQL = '-1' THEN
297: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
298: RETURN FAILURE;
293: -- Fetch the unreleased_SQL string
294: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for unreleased SQL');
295: unreleased_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('UNRELEASED_SQL');
296: IF unreleased_SQL = '-1' THEN
297: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
298: RETURN FAILURE;
299: END IF;
300:
301: -- Call package to process buffer into cursor for parsing and executing
300:
301: -- Call package to process buffer into cursor for parsing and executing
302: cs := Open_Execute_Cursor('UNRELEASED');
303: IF cs = FAILURE THEN
304: WSH_UTIL.Write_Log('Error in Open_Execute_Cursor');
305: RETURN FAILURE;
306: END IF;
307:
308: RETURN SUCCESS;
332: cs BINARY_INTEGER;
333:
334: BEGIN
335: -- Call to dynamically create SQL for backordered lines
336: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Backordered_SQL_Cursor');
337: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Backordered_Line_Details');
338: cs := WSH_PR_PICKING_SESSION.Backordered_Line_Details;
339: IF cs = FAILURE THEN
340: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Backordered_Line_Details');
333:
334: BEGIN
335: -- Call to dynamically create SQL for backordered lines
336: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Backordered_SQL_Cursor');
337: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Backordered_Line_Details');
338: cs := WSH_PR_PICKING_SESSION.Backordered_Line_Details;
339: IF cs = FAILURE THEN
340: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Backordered_Line_Details');
341: RETURN FAILURE;
336: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Backordered_SQL_Cursor');
337: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Backordered_Line_Details');
338: cs := WSH_PR_PICKING_SESSION.Backordered_Line_Details;
339: IF cs = FAILURE THEN
340: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Backordered_Line_Details');
341: RETURN FAILURE;
342: END IF;
343:
344: -- Fetch the backordered_SQL string
341: RETURN FAILURE;
342: END IF;
343:
344: -- Fetch the backordered_SQL string
345: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for backordered SQL');
346: backordered_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('BACKORDERED_SQL');
347: IF backordered_SQL = '-1' THEN
348: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
349: RETURN FAILURE;
344: -- Fetch the backordered_SQL string
345: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for backordered SQL');
346: backordered_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('BACKORDERED_SQL');
347: IF backordered_SQL = '-1' THEN
348: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
349: RETURN FAILURE;
350: END IF;
351:
352: -- Call package to process buffer into cursor for parsing and executing
351:
352: -- Call package to process buffer into cursor for parsing and executing
353: cs := Open_Execute_Cursor('BACKORDERED');
354: IF cs = FAILURE THEN
355: WSH_UTIL.Write_Log('Error in Open_Execute_Cursor');
356: RETURN FAILURE;
357: END IF;
358:
359: RETURN SUCCESS;
384:
385: BEGIN
386: -- Call to dynamically create non_ship_SQL statement based on session
387: -- variables
388: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Non_Shippable_SQL_Cursor');
389: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Non_Shippable_Line_Details');
390: cs := WSH_PR_PICKING_SESSION.Non_Shippable_Lines;
391: IF cs = FAILURE THEN
392: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Non_Shippable_Lines');
385: BEGIN
386: -- Call to dynamically create non_ship_SQL statement based on session
387: -- variables
388: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Non_Shippable_SQL_Cursor');
389: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Non_Shippable_Line_Details');
390: cs := WSH_PR_PICKING_SESSION.Non_Shippable_Lines;
391: IF cs = FAILURE THEN
392: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Non_Shippable_Lines');
393: RETURN FAILURE;
388: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Open_Non_Shippable_SQL_Cursor');
389: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Non_Shippable_Line_Details');
390: cs := WSH_PR_PICKING_SESSION.Non_Shippable_Lines;
391: IF cs = FAILURE THEN
392: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Non_Shippable_Lines');
393: RETURN FAILURE;
394: END IF;
395:
396: -- Fetch the non_ship_SQL string
393: RETURN FAILURE;
394: END IF;
395:
396: -- Fetch the non_ship_SQL string
397: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for non-ship SQL');
398: non_ship_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('NON_SHIP_SQL');
399: IF non_ship_SQL = '-1' THEN
400: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
401: RETURN FAILURE;
396: -- Fetch the non_ship_SQL string
397: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for non-ship SQL');
398: non_ship_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('NON_SHIP_SQL');
399: IF non_ship_SQL = '-1' THEN
400: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
401: RETURN FAILURE;
402: END IF;
403:
404: -- Call package to process buffer into cursor for parsing and executing
403:
404: -- Call package to process buffer into cursor for parsing and executing
405: cs := Open_Execute_Cursor('NON_SHIPPABLE');
406: IF cs = FAILURE THEN
407: WSH_UTIL.Write_Log('Error in Open_Execute_Cursor');
408: RETURN FAILURE;
409: END IF;
410:
411: RETURN SUCCESS;
440:
441: cs BINARY_INTEGER;
442:
443: BEGIN
444: WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_ROWS.Open_Execute_Cursor');
445: IF p_mode = 'UNRELEASED' THEN
446: -- Open a cursor for unreleased_SQL
447: WSH_UTIL.Write_Log('Processing unreleased cursor');
448: u_cursor := DBMS_SQL.Open_Cursor;
443: BEGIN
444: WSH_UTIL.Write_Log('Starting WSH_PR_PICKING_ROWS.Open_Execute_Cursor');
445: IF p_mode = 'UNRELEASED' THEN
446: -- Open a cursor for unreleased_SQL
447: WSH_UTIL.Write_Log('Processing unreleased cursor');
448: u_cursor := DBMS_SQL.Open_Cursor;
449:
450: -- Parse the cursor
451: WSH_UTIL.Write_Log('Parsing unreleased cursor');
447: WSH_UTIL.Write_Log('Processing unreleased cursor');
448: u_cursor := DBMS_SQL.Open_Cursor;
449:
450: -- Parse the cursor
451: WSH_UTIL.Write_Log('Parsing unreleased cursor');
452: DBMS_SQL.Parse(u_cursor, unreleased_SQL, DBMS_SQL.v7);
453:
454: -- Identify the column numbers with variable
455: WSH_UTIL.Write_Log('Column definition for unreleased cursor');
451: WSH_UTIL.Write_Log('Parsing unreleased cursor');
452: DBMS_SQL.Parse(u_cursor, unreleased_SQL, DBMS_SQL.v7);
453:
454: -- Identify the column numbers with variable
455: WSH_UTIL.Write_Log('Column definition for unreleased cursor');
456: DBMS_SQL.Define_Column(u_cursor, 1, u_line_id);
457: DBMS_SQL.Define_Column(u_cursor, 2, u_header_id);
458: DBMS_SQL.Define_Column(u_cursor, 3, u_org_id);
459: DBMS_SQL.Define_Column(u_cursor, 4, u_ato_flag, 1);
493: DBMS_SQL.Define_Column(u_cursor, 38, u_invoice_value);
494:
495:
496: -- Bind release criteria values
497: WSH_UTIL.Write_Log('Binding unreleased cursor');
498:
499: IF (wsh_pr_picking_session.header_id <> 0) THEN
500: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
501: DBMS_SQL.Bind_Variable(u_cursor, ':X_header_id',
496: -- Bind release criteria values
497: WSH_UTIL.Write_Log('Binding unreleased cursor');
498:
499: IF (wsh_pr_picking_session.header_id <> 0) THEN
500: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
501: DBMS_SQL.Bind_Variable(u_cursor, ':X_header_id',
502: wsh_pr_picking_session.header_id);
503: END IF;
504: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
500: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
501: DBMS_SQL.Bind_Variable(u_cursor, ':X_header_id',
502: wsh_pr_picking_session.header_id);
503: END IF;
504: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
505: DBMS_SQL.Bind_Variable(u_cursor, ':X_order_type_id',
506: wsh_pr_picking_session.order_type_id);
507: IF (wsh_pr_picking_session.customer_id <> 0) THEN
508: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
504: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
505: DBMS_SQL.Bind_Variable(u_cursor, ':X_order_type_id',
506: wsh_pr_picking_session.order_type_id);
507: IF (wsh_pr_picking_session.customer_id <> 0) THEN
508: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
509: DBMS_SQL.Bind_Variable(u_cursor, ':X_customer_id',
510: wsh_pr_picking_session.customer_id);
511: END IF;
512:
510: wsh_pr_picking_session.customer_id);
511: END IF;
512:
513: IF (wsh_pr_picking_session.order_line_id <> 0) THEN
514: WSH_UTIL.Write_Log('X_order_line_id = ' || to_char(wsh_pr_picking_session.order_line_id));
515: DBMS_SQL.Bind_Variable(u_cursor, ':X_order_line_id',
516: wsh_pr_picking_session.order_line_id);
517: END IF;
518:
515: DBMS_SQL.Bind_Variable(u_cursor, ':X_order_line_id',
516: wsh_pr_picking_session.order_line_id);
517: END IF;
518:
519: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
520: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_set_number',
521: wsh_pr_picking_session.ship_set_number);
522: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
523: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_site_use_id',
518:
519: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
520: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_set_number',
521: wsh_pr_picking_session.ship_set_number);
522: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
523: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_site_use_id',
524: wsh_pr_picking_session.ship_site_use_id);
525: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
526: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_method_code',
521: wsh_pr_picking_session.ship_set_number);
522: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
523: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_site_use_id',
524: wsh_pr_picking_session.ship_site_use_id);
525: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
526: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_method_code',
527: wsh_pr_picking_session.ship_method_code);
528: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
529: DBMS_SQL.Bind_Variable(u_cursor, ':X_warehouse_id',
524: wsh_pr_picking_session.ship_site_use_id);
525: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
526: DBMS_SQL.Bind_Variable(u_cursor, ':X_ship_method_code',
527: wsh_pr_picking_session.ship_method_code);
528: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
529: DBMS_SQL.Bind_Variable(u_cursor, ':X_warehouse_id',
530: wsh_pr_picking_session.warehouse_id);
531: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
532: DBMS_SQL.Bind_Variable(u_cursor, ':X_subinventory',
527: wsh_pr_picking_session.ship_method_code);
528: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
529: DBMS_SQL.Bind_Variable(u_cursor, ':X_warehouse_id',
530: wsh_pr_picking_session.warehouse_id);
531: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
532: DBMS_SQL.Bind_Variable(u_cursor, ':X_subinventory',
533: wsh_pr_picking_session.subinventory);
534: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
535: DBMS_SQL.Bind_Variable(u_cursor, ':X_shipment_priority',
530: wsh_pr_picking_session.warehouse_id);
531: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
532: DBMS_SQL.Bind_Variable(u_cursor, ':X_subinventory',
533: wsh_pr_picking_session.subinventory);
534: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
535: DBMS_SQL.Bind_Variable(u_cursor, ':X_shipment_priority',
536: wsh_pr_picking_session.shipment_priority);
537: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
538: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_request_date',
533: wsh_pr_picking_session.subinventory);
534: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
535: DBMS_SQL.Bind_Variable(u_cursor, ':X_shipment_priority',
536: wsh_pr_picking_session.shipment_priority);
537: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
538: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_request_date',
539: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
540: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
541: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_request_date',
536: wsh_pr_picking_session.shipment_priority);
537: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
538: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_request_date',
539: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
540: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
541: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_request_date',
542: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
543: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
544: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_sched_ship_date',
539: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
540: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
541: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_request_date',
542: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
543: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
544: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_sched_ship_date',
545: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
546: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
547: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_sched_ship_date',
542: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
543: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
544: DBMS_SQL.Bind_Variable(u_cursor, ':X_from_sched_ship_date',
545: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
546: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
547: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_sched_ship_date',
548: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
549: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
550: DBMS_SQL.Bind_Variable(u_cursor, ':X_existing_rsvs_only_flag',
545: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
546: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
547: DBMS_SQL.Bind_Variable(u_cursor, ':X_to_sched_ship_date',
548: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
549: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
550: DBMS_SQL.Bind_Variable(u_cursor, ':X_existing_rsvs_only_flag',
551: wsh_pr_picking_session.existing_rsvs_only_flag);
552: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
553: DBMS_SQL.Bind_Variable(u_cursor, ':X_inventory_item_id',
548: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
549: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
550: DBMS_SQL.Bind_Variable(u_cursor, ':X_existing_rsvs_only_flag',
551: wsh_pr_picking_session.existing_rsvs_only_flag);
552: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
553: DBMS_SQL.Bind_Variable(u_cursor, ':X_inventory_item_id',
554: wsh_pr_picking_session.inventory_item_id);
555: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
556: DBMS_SQL.Bind_Variable(u_cursor, ':X_reservations',
551: wsh_pr_picking_session.existing_rsvs_only_flag);
552: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
553: DBMS_SQL.Bind_Variable(u_cursor, ':X_inventory_item_id',
554: wsh_pr_picking_session.inventory_item_id);
555: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
556: DBMS_SQL.Bind_Variable(u_cursor, ':X_reservations',
557: wsh_pr_picking_session.reservations);
558: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
559: DBMS_SQL.Bind_Variable(u_cursor, ':X_departure_id',
554: wsh_pr_picking_session.inventory_item_id);
555: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
556: DBMS_SQL.Bind_Variable(u_cursor, ':X_reservations',
557: wsh_pr_picking_session.reservations);
558: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
559: DBMS_SQL.Bind_Variable(u_cursor, ':X_departure_id',
560: wsh_pr_picking_session.departure_id);
561: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
562: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
558: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
559: DBMS_SQL.Bind_Variable(u_cursor, ':X_departure_id',
560: wsh_pr_picking_session.departure_id);
561: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
562: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
563: DBMS_SQL.Bind_Variable(u_cursor, ':X_delivery_id',
564: wsh_pr_picking_session.delivery_id);
565: END IF;
566: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
562: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
563: DBMS_SQL.Bind_Variable(u_cursor, ':X_delivery_id',
564: wsh_pr_picking_session.delivery_id);
565: END IF;
566: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
567: DBMS_SQL.Bind_Variable(u_cursor, ':X_include_planned_lines',
568: wsh_pr_picking_session.include_planned_lines);
569:
570: -- Execute the cursor
567: DBMS_SQL.Bind_Variable(u_cursor, ':X_include_planned_lines',
568: wsh_pr_picking_session.include_planned_lines);
569:
570: -- Execute the cursor
571: WSH_UTIL.Write_Log('Executing unreleased cursor');
572: v_ignore := DBMS_SQL.Execute(u_cursor);
573:
574: ELSIF p_mode= 'BACKORDERED' THEN
575: -- Open a cursor for backordered_SQL
572: v_ignore := DBMS_SQL.Execute(u_cursor);
573:
574: ELSIF p_mode= 'BACKORDERED' THEN
575: -- Open a cursor for backordered_SQL
576: WSH_UTIL.Write_Log('Processing backordered cursor');
577: b_cursor := DBMS_SQL.Open_Cursor;
578:
579: -- Parse the cursor
580: WSH_UTIL.Write_Log('Parsing backordered cursor');
576: WSH_UTIL.Write_Log('Processing backordered cursor');
577: b_cursor := DBMS_SQL.Open_Cursor;
578:
579: -- Parse the cursor
580: WSH_UTIL.Write_Log('Parsing backordered cursor');
581: DBMS_SQL.Parse(b_cursor, backordered_SQL, DBMS_SQL.v7);
582:
583: -- Identify the column numbers with variable
584: WSH_UTIL.Write_Log('Column definition for backordered cursor');
580: WSH_UTIL.Write_Log('Parsing backordered cursor');
581: DBMS_SQL.Parse(b_cursor, backordered_SQL, DBMS_SQL.v7);
582:
583: -- Identify the column numbers with variable
584: WSH_UTIL.Write_Log('Column definition for backordered cursor');
585: DBMS_SQL.Define_Column(b_cursor, 1, b_line_id);
586: DBMS_SQL.Define_Column(b_cursor, 2, b_header_id);
587: DBMS_SQL.Define_Column(b_cursor, 3, b_org_id);
588: DBMS_SQL.Define_Column(b_cursor, 4, b_ato_flag, 1);
621: DBMS_SQL.Define_Column(b_cursor, 37, b_load_seq_number);
622: DBMS_SQL.Define_Column(b_cursor, 38, b_invoice_value);
623:
624: -- Bind release criteria values
625: WSH_UTIL.Write_Log('Binding backordered cursor');
626:
627: IF (wsh_pr_picking_session.header_id <> 0) THEN
628: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
629: DBMS_SQL.Bind_Variable(b_cursor, ':X_header_id',
624: -- Bind release criteria values
625: WSH_UTIL.Write_Log('Binding backordered cursor');
626:
627: IF (wsh_pr_picking_session.header_id <> 0) THEN
628: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
629: DBMS_SQL.Bind_Variable(b_cursor, ':X_header_id',
630: wsh_pr_picking_session.header_id);
631: END IF;
632: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
628: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
629: DBMS_SQL.Bind_Variable(b_cursor, ':X_header_id',
630: wsh_pr_picking_session.header_id);
631: END IF;
632: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
633: DBMS_SQL.Bind_Variable(b_cursor, ':X_order_type_id',
634: wsh_pr_picking_session.order_type_id);
635: IF (wsh_pr_picking_session.customer_id <> 0) THEN
636: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
632: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
633: DBMS_SQL.Bind_Variable(b_cursor, ':X_order_type_id',
634: wsh_pr_picking_session.order_type_id);
635: IF (wsh_pr_picking_session.customer_id <> 0) THEN
636: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
637: DBMS_SQL.Bind_Variable(b_cursor, ':X_customer_id',
638: wsh_pr_picking_session.customer_id);
639: END IF;
640:
638: wsh_pr_picking_session.customer_id);
639: END IF;
640:
641: IF (wsh_pr_picking_session.order_line_id <> 0) THEN
642: WSH_UTIL.Write_Log('X_order_line_id = ' || to_char(wsh_pr_picking_session.order_line_id));
643: DBMS_SQL.Bind_Variable(b_cursor, ':X_order_line_id',
644: wsh_pr_picking_session.order_line_id);
645: END IF;
646:
643: DBMS_SQL.Bind_Variable(b_cursor, ':X_order_line_id',
644: wsh_pr_picking_session.order_line_id);
645: END IF;
646:
647: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
648: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_set_number',
649: wsh_pr_picking_session.ship_set_number);
650: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
651: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_site_use_id',
646:
647: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
648: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_set_number',
649: wsh_pr_picking_session.ship_set_number);
650: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
651: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_site_use_id',
652: wsh_pr_picking_session.ship_site_use_id);
653: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
654: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_method_code',
649: wsh_pr_picking_session.ship_set_number);
650: WSH_UTIL.Write_Log('X_ship_site_use_id = ' || to_char(wsh_pr_picking_session.ship_site_use_id));
651: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_site_use_id',
652: wsh_pr_picking_session.ship_site_use_id);
653: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
654: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_method_code',
655: wsh_pr_picking_session.ship_method_code);
656: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
657: DBMS_SQL.Bind_Variable(b_cursor, ':X_warehouse_id',
652: wsh_pr_picking_session.ship_site_use_id);
653: WSH_UTIL.Write_Log('X_ship_method_code = ' || wsh_pr_picking_session.ship_method_code);
654: DBMS_SQL.Bind_Variable(b_cursor, ':X_ship_method_code',
655: wsh_pr_picking_session.ship_method_code);
656: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
657: DBMS_SQL.Bind_Variable(b_cursor, ':X_warehouse_id',
658: wsh_pr_picking_session.warehouse_id);
659: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
660: DBMS_SQL.Bind_Variable(b_cursor, ':X_subinventory',
655: wsh_pr_picking_session.ship_method_code);
656: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
657: DBMS_SQL.Bind_Variable(b_cursor, ':X_warehouse_id',
658: wsh_pr_picking_session.warehouse_id);
659: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
660: DBMS_SQL.Bind_Variable(b_cursor, ':X_subinventory',
661: wsh_pr_picking_session.subinventory);
662: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
663: DBMS_SQL.Bind_Variable(b_cursor, ':X_shipment_priority',
658: wsh_pr_picking_session.warehouse_id);
659: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
660: DBMS_SQL.Bind_Variable(b_cursor, ':X_subinventory',
661: wsh_pr_picking_session.subinventory);
662: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
663: DBMS_SQL.Bind_Variable(b_cursor, ':X_shipment_priority',
664: wsh_pr_picking_session.shipment_priority);
665: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
666: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_request_date',
661: wsh_pr_picking_session.subinventory);
662: WSH_UTIL.Write_Log('X_shipment_priority = ' || wsh_pr_picking_session.shipment_priority);
663: DBMS_SQL.Bind_Variable(b_cursor, ':X_shipment_priority',
664: wsh_pr_picking_session.shipment_priority);
665: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
666: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_request_date',
667: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
668: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
669: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_request_date',
664: wsh_pr_picking_session.shipment_priority);
665: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
666: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_request_date',
667: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
668: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
669: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_request_date',
670: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
671: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
672: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_sched_ship_date',
667: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
668: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
669: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_request_date',
670: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
671: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
672: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_sched_ship_date',
673: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
674: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
675: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_sched_ship_date',
670: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
671: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
672: DBMS_SQL.Bind_Variable(b_cursor, ':X_from_sched_ship_date',
673: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
674: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
675: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_sched_ship_date',
676: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
677: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
678: DBMS_SQL.Bind_Variable(b_cursor, ':X_existing_rsvs_only_flag',
673: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
674: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
675: DBMS_SQL.Bind_Variable(b_cursor, ':X_to_sched_ship_date',
676: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
677: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
678: DBMS_SQL.Bind_Variable(b_cursor, ':X_existing_rsvs_only_flag',
679: wsh_pr_picking_session.existing_rsvs_only_flag);
680: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
681: DBMS_SQL.Bind_Variable(b_cursor, ':X_inventory_item_id',
676: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
677: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
678: DBMS_SQL.Bind_Variable(b_cursor, ':X_existing_rsvs_only_flag',
679: wsh_pr_picking_session.existing_rsvs_only_flag);
680: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
681: DBMS_SQL.Bind_Variable(b_cursor, ':X_inventory_item_id',
682: wsh_pr_picking_session.inventory_item_id);
683: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
684: DBMS_SQL.Bind_Variable(b_cursor, ':X_reservations',
679: wsh_pr_picking_session.existing_rsvs_only_flag);
680: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
681: DBMS_SQL.Bind_Variable(b_cursor, ':X_inventory_item_id',
682: wsh_pr_picking_session.inventory_item_id);
683: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
684: DBMS_SQL.Bind_Variable(b_cursor, ':X_reservations',
685: wsh_pr_picking_session.reservations);
686: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
687: DBMS_SQL.Bind_Variable(b_cursor, ':X_departure_id',
682: wsh_pr_picking_session.inventory_item_id);
683: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
684: DBMS_SQL.Bind_Variable(b_cursor, ':X_reservations',
685: wsh_pr_picking_session.reservations);
686: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
687: DBMS_SQL.Bind_Variable(b_cursor, ':X_departure_id',
688: wsh_pr_picking_session.departure_id);
689: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
690: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
686: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
687: DBMS_SQL.Bind_Variable(b_cursor, ':X_departure_id',
688: wsh_pr_picking_session.departure_id);
689: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
690: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
691: DBMS_SQL.Bind_Variable(b_cursor, ':X_delivery_id',
692: wsh_pr_picking_session.delivery_id);
693: END IF;
694: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
690: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
691: DBMS_SQL.Bind_Variable(b_cursor, ':X_delivery_id',
692: wsh_pr_picking_session.delivery_id);
693: END IF;
694: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
695: DBMS_SQL.Bind_Variable(b_cursor, ':X_include_planned_lines',
696: wsh_pr_picking_session.include_planned_lines);
697:
698: -- Execute the cursor
695: DBMS_SQL.Bind_Variable(b_cursor, ':X_include_planned_lines',
696: wsh_pr_picking_session.include_planned_lines);
697:
698: -- Execute the cursor
699: WSH_UTIL.Write_Log('Executing backordered cursor');
700: v_ignore := DBMS_SQL.Execute(b_cursor);
701:
702: ELSIF p_mode = 'SYNC' THEN
703:
701:
702: ELSIF p_mode = 'SYNC' THEN
703:
704: -- Open a cursor for sync_SQL
705: WSH_UTIL.Write_Log('Processing sync cursor');
706: s_cursor := DBMS_SQL.Open_Cursor;
707:
708: -- Parse the cursor
709: WSH_UTIL.Write_Log('Parsing sync cursor');
705: WSH_UTIL.Write_Log('Processing sync cursor');
706: s_cursor := DBMS_SQL.Open_Cursor;
707:
708: -- Parse the cursor
709: WSH_UTIL.Write_Log('Parsing sync cursor');
710: DBMS_SQL.Parse(s_cursor, sync_SQL, DBMS_SQL.v7);
711:
712: -- Identify the column numbers with variable
713: WSH_UTIL.Write_Log('Column definition for sync cursor');
709: WSH_UTIL.Write_Log('Parsing sync cursor');
710: DBMS_SQL.Parse(s_cursor, sync_SQL, DBMS_SQL.v7);
711:
712: -- Identify the column numbers with variable
713: WSH_UTIL.Write_Log('Column definition for sync cursor');
714: DBMS_SQL.Define_Column(s_cursor, 1, s_line_id);
715: DBMS_SQL.Define_Column(s_cursor, 2, s_header_id);
716: DBMS_SQL.Define_Column(s_cursor, 3, s_org_id);
717: DBMS_SQL.Define_Column(s_cursor, 4, s_ato_flag, 1);
737: DBMS_SQL.Define_Column(s_cursor, 24, s_detail_container_id);
738: DBMS_SQL.Define_Column(s_cursor, 25, s_load_seq_number);
739:
740: -- Bind release criteria values
741: WSH_UTIL.Write_Log('Binding sync cursor');
742:
743: WSH_UTIL.Write_Log('X_p_param_1 = ' || to_char(wsh_pr_picking_session.sync_line_id));
744: DBMS_SQL.Bind_Variable(s_cursor, ':X_p_param_1',
745: wsh_pr_picking_session.sync_line_id);
739:
740: -- Bind release criteria values
741: WSH_UTIL.Write_Log('Binding sync cursor');
742:
743: WSH_UTIL.Write_Log('X_p_param_1 = ' || to_char(wsh_pr_picking_session.sync_line_id));
744: DBMS_SQL.Bind_Variable(s_cursor, ':X_p_param_1',
745: wsh_pr_picking_session.sync_line_id);
746: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
747: DBMS_SQL.Bind_Variable(s_cursor, ':X_warehouse_id',
742:
743: WSH_UTIL.Write_Log('X_p_param_1 = ' || to_char(wsh_pr_picking_session.sync_line_id));
744: DBMS_SQL.Bind_Variable(s_cursor, ':X_p_param_1',
745: wsh_pr_picking_session.sync_line_id);
746: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
747: DBMS_SQL.Bind_Variable(s_cursor, ':X_warehouse_id',
748: wsh_pr_picking_session.warehouse_id);
749: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
750: DBMS_SQL.Bind_Variable(s_cursor, ':X_subinventory',
745: wsh_pr_picking_session.sync_line_id);
746: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
747: DBMS_SQL.Bind_Variable(s_cursor, ':X_warehouse_id',
748: wsh_pr_picking_session.warehouse_id);
749: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
750: DBMS_SQL.Bind_Variable(s_cursor, ':X_subinventory',
751: wsh_pr_picking_session.subinventory);
752: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
753: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_request_date',
748: wsh_pr_picking_session.warehouse_id);
749: WSH_UTIL.Write_Log('X_subinventory = ' || wsh_pr_picking_session.subinventory);
750: DBMS_SQL.Bind_Variable(s_cursor, ':X_subinventory',
751: wsh_pr_picking_session.subinventory);
752: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
753: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_request_date',
754: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
755: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
756: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_request_date',
751: wsh_pr_picking_session.subinventory);
752: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
753: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_request_date',
754: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
755: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
756: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_request_date',
757: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
758: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
759: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_sched_ship_date',
754: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
755: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
756: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_request_date',
757: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
758: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
759: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_sched_ship_date',
760: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
761: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
762: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_sched_ship_date',
757: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
758: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
759: DBMS_SQL.Bind_Variable(s_cursor, ':X_from_sched_ship_date',
760: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
761: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
762: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_sched_ship_date',
763: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
764: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
765: DBMS_SQL.Bind_Variable(s_cursor, ':X_existing_rsvs_only_flag',
760: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
761: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
762: DBMS_SQL.Bind_Variable(s_cursor, ':X_to_sched_ship_date',
763: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
764: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
765: DBMS_SQL.Bind_Variable(s_cursor, ':X_existing_rsvs_only_flag',
766: wsh_pr_picking_session.existing_rsvs_only_flag);
767: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
768: DBMS_SQL.Bind_Variable(s_cursor, ':X_inventory_item_id',
763: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
764: WSH_UTIL.Write_Log('X_existing_rsvs_only_flag = ' || wsh_pr_picking_session.existing_rsvs_only_flag);
765: DBMS_SQL.Bind_Variable(s_cursor, ':X_existing_rsvs_only_flag',
766: wsh_pr_picking_session.existing_rsvs_only_flag);
767: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
768: DBMS_SQL.Bind_Variable(s_cursor, ':X_inventory_item_id',
769: wsh_pr_picking_session.inventory_item_id);
770: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
771: DBMS_SQL.Bind_Variable(s_cursor, ':X_reservations',
766: wsh_pr_picking_session.existing_rsvs_only_flag);
767: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
768: DBMS_SQL.Bind_Variable(s_cursor, ':X_inventory_item_id',
769: wsh_pr_picking_session.inventory_item_id);
770: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
771: DBMS_SQL.Bind_Variable(s_cursor, ':X_reservations',
772: wsh_pr_picking_session.reservations);
773: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
774: DBMS_SQL.Bind_Variable(s_cursor, ':X_departure_id',
769: wsh_pr_picking_session.inventory_item_id);
770: WSH_UTIL.Write_Log('X_reservations = ' || wsh_pr_picking_session.reservations);
771: DBMS_SQL.Bind_Variable(s_cursor, ':X_reservations',
772: wsh_pr_picking_session.reservations);
773: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
774: DBMS_SQL.Bind_Variable(s_cursor, ':X_departure_id',
775: wsh_pr_picking_session.departure_id);
776: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
777: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
773: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
774: DBMS_SQL.Bind_Variable(s_cursor, ':X_departure_id',
775: wsh_pr_picking_session.departure_id);
776: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
777: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
778: DBMS_SQL.Bind_Variable(s_cursor, ':X_delivery_id',
779: wsh_pr_picking_session.delivery_id);
780: END IF;
781: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
777: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
778: DBMS_SQL.Bind_Variable(s_cursor, ':X_delivery_id',
779: wsh_pr_picking_session.delivery_id);
780: END IF;
781: WSH_UTIL.Write_Log('X_include_planned_lines = ' || wsh_pr_picking_session.include_planned_lines);
782: DBMS_SQL.Bind_Variable(s_cursor, ':X_include_planned_lines',
783: wsh_pr_picking_session.include_planned_lines);
784:
785: -- Execute the cursor
782: DBMS_SQL.Bind_Variable(s_cursor, ':X_include_planned_lines',
783: wsh_pr_picking_session.include_planned_lines);
784:
785: -- Execute the cursor
786: WSH_UTIL.Write_Log('Executing sync cursor');
787: v_ignore := DBMS_SQL.Execute(s_cursor);
788:
789: ELSIF p_mode = 'NON_SHIPPABLE' THEN
790: -- Open a cursor for non_ship_SQL
787: v_ignore := DBMS_SQL.Execute(s_cursor);
788:
789: ELSIF p_mode = 'NON_SHIPPABLE' THEN
790: -- Open a cursor for non_ship_SQL
791: WSH_UTIL.Write_Log('Processing non-ship cursor');
792: ns_cursor := DBMS_SQL.Open_Cursor;
793:
794: -- Parse the cursor
795: WSH_UTIL.Write_Log('Parsing non-ship cursor');
791: WSH_UTIL.Write_Log('Processing non-ship cursor');
792: ns_cursor := DBMS_SQL.Open_Cursor;
793:
794: -- Parse the cursor
795: WSH_UTIL.Write_Log('Parsing non-ship cursor');
796: DBMS_SQL.Parse(ns_cursor, non_ship_SQL, DBMS_SQL.v7);
797:
798: WSH_UTIL.Write_Log('Column definition for non-ship cursor');
799: DBMS_SQL.Define_Column(ns_cursor, 1, n_line_id);
794: -- Parse the cursor
795: WSH_UTIL.Write_Log('Parsing non-ship cursor');
796: DBMS_SQL.Parse(ns_cursor, non_ship_SQL, DBMS_SQL.v7);
797:
798: WSH_UTIL.Write_Log('Column definition for non-ship cursor');
799: DBMS_SQL.Define_Column(ns_cursor, 1, n_line_id);
800: DBMS_SQL.Define_Column(ns_cursor, 2, n_header_id);
801: DBMS_SQL.Define_Column(ns_cursor, 3, n_line_detail_id);
802: DBMS_SQL.Define_Column(ns_cursor, 4, n_org_id);
801: DBMS_SQL.Define_Column(ns_cursor, 3, n_line_detail_id);
802: DBMS_SQL.Define_Column(ns_cursor, 4, n_org_id);
803:
804: -- Bind release criteria values
805: WSH_UTIL.Write_Log('Binding non-ship cursor');
806:
807: WSH_UTIL.Write_Log('X_request_id = ' || to_char(wsh_pr_picking_session.request_id));
808: DBMS_SQL.Bind_Variable(ns_cursor, ':X_request_id',
809: wsh_pr_picking_session.request_id);
803:
804: -- Bind release criteria values
805: WSH_UTIL.Write_Log('Binding non-ship cursor');
806:
807: WSH_UTIL.Write_Log('X_request_id = ' || to_char(wsh_pr_picking_session.request_id));
808: DBMS_SQL.Bind_Variable(ns_cursor, ':X_request_id',
809: wsh_pr_picking_session.request_id);
810:
811: IF (wsh_pr_picking_session.header_id <> 0) THEN
808: DBMS_SQL.Bind_Variable(ns_cursor, ':X_request_id',
809: wsh_pr_picking_session.request_id);
810:
811: IF (wsh_pr_picking_session.header_id <> 0) THEN
812: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
813: DBMS_SQL.Bind_Variable(ns_cursor, ':X_header_id',
814: wsh_pr_picking_session.header_id);
815: END IF;
816: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
812: WSH_UTIL.Write_Log('X_header_id = ' || to_char(wsh_pr_picking_session.header_id));
813: DBMS_SQL.Bind_Variable(ns_cursor, ':X_header_id',
814: wsh_pr_picking_session.header_id);
815: END IF;
816: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
817: DBMS_SQL.Bind_Variable(ns_cursor, ':X_order_type_id',
818: wsh_pr_picking_session.order_type_id);
819: IF (wsh_pr_picking_session.customer_id <> 0) THEN
820: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
816: WSH_UTIL.Write_Log('X_order_type_id = ' || to_char(wsh_pr_picking_session.order_type_id));
817: DBMS_SQL.Bind_Variable(ns_cursor, ':X_order_type_id',
818: wsh_pr_picking_session.order_type_id);
819: IF (wsh_pr_picking_session.customer_id <> 0) THEN
820: WSH_UTIL.Write_Log('X_customer_id = ' || to_char(wsh_pr_picking_session.customer_id));
821: DBMS_SQL.Bind_Variable(ns_cursor, ':X_customer_id',
822: wsh_pr_picking_session.customer_id);
823: END IF;
824:
822: wsh_pr_picking_session.customer_id);
823: END IF;
824:
825: IF (wsh_pr_picking_session.order_line_id <> 0) THEN
826: WSH_UTIL.Write_Log('X_order_line_id = ' || to_char(wsh_pr_picking_session.order_line_id));
827: DBMS_SQL.Bind_Variable(ns_cursor, ':X_order_line_id',
828: wsh_pr_picking_session.order_line_id);
829: END IF;
830:
827: DBMS_SQL.Bind_Variable(ns_cursor, ':X_order_line_id',
828: wsh_pr_picking_session.order_line_id);
829: END IF;
830:
831: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
832: DBMS_SQL.Bind_Variable(ns_cursor, ':X_ship_set_number',
833: wsh_pr_picking_session.ship_set_number);
834: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
835: DBMS_SQL.Bind_Variable(ns_cursor, ':X_warehouse_id',
830:
831: WSH_UTIL.Write_Log('X_ship_set_number = ' || to_char(wsh_pr_picking_session.ship_set_number));
832: DBMS_SQL.Bind_Variable(ns_cursor, ':X_ship_set_number',
833: wsh_pr_picking_session.ship_set_number);
834: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
835: DBMS_SQL.Bind_Variable(ns_cursor, ':X_warehouse_id',
836: wsh_pr_picking_session.warehouse_id);
837: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
838: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_request_date',
833: wsh_pr_picking_session.ship_set_number);
834: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
835: DBMS_SQL.Bind_Variable(ns_cursor, ':X_warehouse_id',
836: wsh_pr_picking_session.warehouse_id);
837: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
838: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_request_date',
839: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
840: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
841: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_request_date',
836: wsh_pr_picking_session.warehouse_id);
837: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
838: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_request_date',
839: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
840: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
841: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_request_date',
842: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
843: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
844: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_sched_ship_date',
839: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
840: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
841: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_request_date',
842: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
843: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
844: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_sched_ship_date',
845: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
846: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
847: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_sched_ship_date',
842: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
843: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
844: DBMS_SQL.Bind_Variable(ns_cursor, ':X_from_sched_ship_date',
845: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
846: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
847: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_sched_ship_date',
848: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
849: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
850: DBMS_SQL.Bind_Variable(ns_cursor, ':X_inventory_item_id',
845: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
846: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
847: DBMS_SQL.Bind_Variable(ns_cursor, ':X_to_sched_ship_date',
848: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
849: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
850: DBMS_SQL.Bind_Variable(ns_cursor, ':X_inventory_item_id',
851: wsh_pr_picking_session.inventory_item_id);
852: IF (wsh_pr_picking_session.departure_id <> 0) THEN
853: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
849: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
850: DBMS_SQL.Bind_Variable(ns_cursor, ':X_inventory_item_id',
851: wsh_pr_picking_session.inventory_item_id);
852: IF (wsh_pr_picking_session.departure_id <> 0) THEN
853: WSH_UTIL.Write_Log('X_departure_id = ' || to_char(wsh_pr_picking_session.departure_id));
854: DBMS_SQL.Bind_Variable(ns_cursor, ':X_departure_id',
855: wsh_pr_picking_session.departure_id);
856: END IF;
857: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
854: DBMS_SQL.Bind_Variable(ns_cursor, ':X_departure_id',
855: wsh_pr_picking_session.departure_id);
856: END IF;
857: IF (wsh_pr_picking_session.delivery_id <> 0) THEN
858: WSH_UTIL.Write_Log('X_delivery_id = ' || to_char(wsh_pr_picking_session.delivery_id));
859: DBMS_SQL.Bind_Variable(ns_cursor, ':X_delivery_id',
860: wsh_pr_picking_session.delivery_id);
861: END IF;
862:
860: wsh_pr_picking_session.delivery_id);
861: END IF;
862:
863: -- Execute the cursor
864: WSH_UTIL.Write_Log('Executing non-ship cursor');
865: v_ignore := DBMS_SQL.Execute(ns_cursor);
866:
867: ELSE
868: WSH_UTIL.Write_Log('Invalid mode for Open_Execute_Cursor');
864: WSH_UTIL.Write_Log('Executing non-ship cursor');
865: v_ignore := DBMS_SQL.Execute(ns_cursor);
866:
867: ELSE
868: WSH_UTIL.Write_Log('Invalid mode for Open_Execute_Cursor');
869: RETURN FAILURE;
870: END IF;
871:
872: RETURN SUCCESS;
873:
874: EXCEPTION
875: WHEN OTHERS THEN
876: Close_Cursors;
877: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Open_Execute_Cursor');
878: RETURN FAILURE;
879:
880: END Open_Execute_Cursor;
881:
949: cs BINARY_INTEGER;
950: custom_line BINARY_INTEGER;
951:
952: BEGIN
953: WSH_UTIL.Write_Log('In Get_Line_Details');
954: -- handle uninitialized package errors here
955: IF initialized = FALSE THEN
956: WSH_UTIL.Write_Log('The package must be initialized before use');
957: call_parameters(2) := FAILURE;
952: BEGIN
953: WSH_UTIL.Write_Log('In Get_Line_Details');
954: -- handle uninitialized package errors here
955: IF initialized = FALSE THEN
956: WSH_UTIL.Write_Log('The package must be initialized before use');
957: call_parameters(2) := FAILURE;
958: RETURN;
959: END IF;
960:
971: release_table(current_line) := first_line;
972: current_line := current_line + 1;
973: END IF;
974:
975: WSH_UTIL.Write_Log('Fetching Customer specified lines');
976:
977: custom_line := WSH_PR_CUSTOM.PROCESS_LINES;
978:
979: IF ( custom_line >1 ) THEN
978:
979: IF ( custom_line >1 ) THEN
980: MAX_LINES := custom_line + 2 ;
981: END IF ;
982: WSH_UTIL.Write_Log('Custom Lines set to ' || to_char(custom_line));
983: WSH_UTIL.Write_Log('Setting MAX_LINES to ' || to_char(MAX_LINES));
984:
985: IF ss_model_fetch_mode = FALSE THEN
986: LOOP
979: IF ( custom_line >1 ) THEN
980: MAX_LINES := custom_line + 2 ;
981: END IF ;
982: WSH_UTIL.Write_Log('Custom Lines set to ' || to_char(custom_line));
983: WSH_UTIL.Write_Log('Setting MAX_LINES to ' || to_char(MAX_LINES));
984:
985: IF ss_model_fetch_mode = FALSE THEN
986: LOOP
987: IF current_line < MAX_LINES THEN
987: IF current_line < MAX_LINES THEN
988: -- Inserts the next line detail into release_table
989: cs := Get_Line_Details_Pvt;
990: IF cs = FAILURE THEN
991: WSH_UTIL.Write_Log('Failed in Get_Line_Details_Pvt');
992: call_parameters(2) := FAILURE;
993: RETURN;
994: ELSIF cs = DONE THEN
995: WSH_UTIL.Write_Log('Fetched all lines');
991: WSH_UTIL.Write_Log('Failed in Get_Line_Details_Pvt');
992: call_parameters(2) := FAILURE;
993: RETURN;
994: ELSIF cs = DONE THEN
995: WSH_UTIL.Write_Log('Fetched all lines');
996: EXIT;
997: END IF;
998: ELSE
999: WSH_UTIL.Write_Log('Testing for incomplete model');
995: WSH_UTIL.Write_Log('Fetched all lines');
996: EXIT;
997: END IF;
998: ELSE
999: WSH_UTIL.Write_Log('Testing for incomplete model');
1000: model_sync_line := 0;
1001: IF (release_table(current_line - 1).item_type_code = 'MODEL' or
1002: release_table(current_line - 1).item_type_code = 'KIT' or
1003: release_table(current_line - 1).parent_line_id > 0) THEN
1006: ELSE
1007: model_sync_line := release_table(current_line - 1).line_id;
1008: END IF;
1009: END IF;
1010: WSH_UTIL.Write_Log('Parent model line_id is ' || to_char(model_sync_line));
1011: IF (((release_table(current_line - 1).ship_set_number > 0) and
1012: (release_table(current_line - 1).ship_set_number =
1013: release_table(current_line - 2).ship_set_number) and
1014: (release_table(current_line - 1).header_id =
1015: release_table(current_line - 2).header_id)) or
1016: (model_sync_line > 0 and
1017: (release_table(current_line - 2).line_id = model_sync_line or
1018: release_table(current_line - 2).parent_line_id = model_sync_line))) THEN
1019: WSH_UTIL.Write_Log('Large model or ss, entering MODEL FETCH MODE');
1020: ss_model_fetch_mode := TRUE;
1021: EXIT;
1022: ELSE
1023: first_line := release_table(current_line - 1);
1030: END IF;
1031:
1032: IF ss_model_fetch_mode = TRUE THEN
1033: LOOP
1034: WSH_UTIL.Write_Log('In model fetch mode for model ' ||
1035: to_char(model_sync_line));
1036: IF current_line <= 101 THEN
1037: -- We are still in the midst of a ship set or a large model,
1038: -- so continue to fetch lines
1038: -- so continue to fetch lines
1039: -- Inserts the next line detail into release_table
1040: cs := Get_Line_Details_Pvt;
1041: IF cs = FAILURE THEN
1042: WSH_UTIL.Write_Log('Failed in Get_Line_Details_Pvt');
1043: ss_model_fetch_mode := FALSE;
1044: model_sync_line := 0;
1045: call_parameters(2) := FAILURE;
1046: RETURN;
1044: model_sync_line := 0;
1045: call_parameters(2) := FAILURE;
1046: RETURN;
1047: ELSIF cs = DONE THEN
1048: WSH_UTIL.Write_Log('Fetched all lines');
1049: ss_model_fetch_mode := FALSE;
1050: model_sync_line := 0;
1051: EXIT;
1052: END IF;
1061: release_table(current_line - 1).parent_line_id = model_sync_line))) THEN
1062: null;
1063: ELSE
1064: -- fetched an entire model, so return to wshpgld
1065: WSH_UTIL.Write_Log('Entire large model fetched, exit MODEL FETCH MODE');
1066: ss_model_fetch_mode := FALSE;
1067: model_sync_line := 0;
1068: first_line := release_table(current_line - 1);
1069: release_table.delete(current_line - 1);
1072: END IF;
1073: ELSE
1074: -- Reached the end of buffer, but not model, must
1075: -- return rows and indicate to C program to refetch
1076: WSH_UTIL.Write_Log('Setting to MODEL RE-FETCH MODE for large model');
1077: first_line := release_table(current_line - 1);
1078: release_table.delete(current_line - 1);
1079: current_line := current_line - 1;
1080: call_parameters(3) := SS_MODEL_REFETCH;
1141:
1142: EXCEPTION
1143: WHEN OTHERS THEN
1144: Close_Cursors;
1145: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Get_Line_Details');
1146: call_parameters(2) := FAILURE;
1147:
1148: END Get_Line_Details;
1149:
1212: IF DBMS_SQL.Fetch_Rows(u_cursor) > 0 THEN
1213: line := 41;
1214: Map_Col_Value('u');
1215: ELSE
1216: WSH_UTIL.Write_Log('Closing cursor u');
1217: DBMS_SQL.Close_Cursor(u_cursor);
1218: END IF;
1219:
1220: line := 5;
1220: line := 5;
1221: IF DBMS_SQL.Fetch_Rows(b_cursor) > 0 THEN
1222: Map_Col_Value('b');
1223: ELSE
1224: WSH_UTIL.Write_Log('Closing cursor b');
1225: DBMS_SQL.Close_Cursor(b_cursor);
1226: END IF;
1227:
1228: line := 6;
1316:
1317: EXCEPTION
1318: WHEN OTHERS THEN
1319: Close_Cursors;
1320: WSH_UTIL.Write_Log(to_char(line));
1321: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Get_Line_Details_Pvt');
1322: RETURN FAILURE;
1323:
1324: END Get_Line_Details_Pvt;
1317: EXCEPTION
1318: WHEN OTHERS THEN
1319: Close_Cursors;
1320: WSH_UTIL.Write_Log(to_char(line));
1321: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Get_Line_Details_Pvt');
1322: RETURN FAILURE;
1323:
1324: END Get_Line_Details_Pvt;
1325:
1488:
1489: cs BINARY_INTEGER;
1490:
1491: BEGIN
1492: WSH_UTIL.Write_Log('--------------------');
1493: IF p_source = 's' THEN
1494: WSH_UTIL.Write_Log('Current line is ' || to_char(current_sync_line));
1495: ELSE
1496: WSH_UTIL.Write_Log('Current line is ' || to_char(current_line));
1490:
1491: BEGIN
1492: WSH_UTIL.Write_Log('--------------------');
1493: IF p_source = 's' THEN
1494: WSH_UTIL.Write_Log('Current line is ' || to_char(current_sync_line));
1495: ELSE
1496: WSH_UTIL.Write_Log('Current line is ' || to_char(current_line));
1497: END IF;
1498: IF p_source = 'u' THEN
1492: WSH_UTIL.Write_Log('--------------------');
1493: IF p_source = 's' THEN
1494: WSH_UTIL.Write_Log('Current line is ' || to_char(current_sync_line));
1495: ELSE
1496: WSH_UTIL.Write_Log('Current line is ' || to_char(current_line));
1497: END IF;
1498: IF p_source = 'u' THEN
1499: release_table(current_line).line_id := u_line_id;
1500: release_table(current_line).header_id := u_header_id;
1535: release_table(current_line).load_seq_number := u_load_seq_number;
1536: release_table(current_line).invoice_value := u_invoice_value;
1537: release_table(current_line).backorder_line := 0;
1538:
1539: WSH_UTIL.Write_Log('Row Description:');
1540: WSH_UTIL.Write_Log('header_id = ' || to_char(u_header_id) || ' line_id = ' || to_char(u_line_id) ||
1541: ' line_detail_id = ' || to_char(u_line_detail_id));
1542: ELSIF p_source = 'b' THEN
1543: release_table(current_line).line_id := b_line_id;
1536: release_table(current_line).invoice_value := u_invoice_value;
1537: release_table(current_line).backorder_line := 0;
1538:
1539: WSH_UTIL.Write_Log('Row Description:');
1540: WSH_UTIL.Write_Log('header_id = ' || to_char(u_header_id) || ' line_id = ' || to_char(u_line_id) ||
1541: ' line_detail_id = ' || to_char(u_line_detail_id));
1542: ELSIF p_source = 'b' THEN
1543: release_table(current_line).line_id := b_line_id;
1544: release_table(current_line).header_id := b_header_id;
1579: release_table(current_line).load_seq_number := b_load_seq_number;
1580: release_table(current_line).invoice_value := b_invoice_value;
1581: release_table(current_line).backorder_line := 1;
1582:
1583: WSH_UTIL.Write_Log('Row Description');
1584: WSH_UTIL.Write_Log('header_id = ' || to_char(b_header_id) || ' line_id = ' || to_char(b_line_id) ||
1585: ' pick_line_detail_id = ' || to_char(b_line_detail_id));
1586:
1587: ELSIF p_source = 's' THEN
1580: release_table(current_line).invoice_value := b_invoice_value;
1581: release_table(current_line).backorder_line := 1;
1582:
1583: WSH_UTIL.Write_Log('Row Description');
1584: WSH_UTIL.Write_Log('header_id = ' || to_char(b_header_id) || ' line_id = ' || to_char(b_line_id) ||
1585: ' pick_line_detail_id = ' || to_char(b_line_detail_id));
1586:
1587: ELSIF p_source = 's' THEN
1588: sync_table(current_sync_line).line_id := s_line_id;
1610: sync_table(current_sync_line).master_container_item_id := s_master_container_id;
1611: sync_table(current_sync_line).detail_container_item_id := s_detail_container_id;
1612: sync_table(current_sync_line).load_seq_number := s_load_seq_number;
1613:
1614: WSH_UTIL.Write_Log('Sync Line Row Description:');
1615: WSH_UTIL.Write_Log('header_id = ' || to_char(s_header_id) || ' line_id = ' || to_char(s_line_id) ||
1616: ' line_detail_id = ' || to_char(s_line_detail_id));
1617:
1618:
1611: sync_table(current_sync_line).detail_container_item_id := s_detail_container_id;
1612: sync_table(current_sync_line).load_seq_number := s_load_seq_number;
1613:
1614: WSH_UTIL.Write_Log('Sync Line Row Description:');
1615: WSH_UTIL.Write_Log('header_id = ' || to_char(s_header_id) || ' line_id = ' || to_char(s_line_id) ||
1616: ' line_detail_id = ' || to_char(s_line_detail_id));
1617:
1618:
1619: current_sync_line := current_sync_line + 1;
1623: release_table(current_line).header_id := n_header_id;
1624: release_table(current_line).line_detail_id := n_line_detail_id;
1625: release_table(current_line).org_id := n_org_id;
1626:
1627: WSH_UTIL.Write_Log('Non-Ship Line Row Description:');
1628: WSH_UTIL.Write_Log('header_id = ' || to_char(n_header_id) || ' line_id = ' || to_char(n_line_id) ||
1629: ' line_detail_id = ' || to_char(n_line_detail_id));
1630:
1631: current_line := current_line + 1;
1624: release_table(current_line).line_detail_id := n_line_detail_id;
1625: release_table(current_line).org_id := n_org_id;
1626:
1627: WSH_UTIL.Write_Log('Non-Ship Line Row Description:');
1628: WSH_UTIL.Write_Log('header_id = ' || to_char(n_header_id) || ' line_id = ' || to_char(n_line_id) ||
1629: ' line_detail_id = ' || to_char(n_line_detail_id));
1630:
1631: current_line := current_line + 1;
1632: RETURN;
1647: release_table(current_line - 1).planned_departure_date_t))) OR
1648: ((primary_rsr = 'SHIPMENT_PRIORITY')
1649: AND (release_table(current_line).shipment_priority <>
1650: release_table(current_line - 1).shipment_priority))) THEN
1651: WSH_UTIL.Write_Log('--RSR Switch--');
1652: release_table(current_line - 1).primary_rsr_switch := 1;
1653: ELSE
1654: release_table(current_line - 1).primary_rsr_switch := 0;
1655: END IF;
1680: cs BINARY_INTEGER;
1681:
1682: BEGIN
1683:
1684: WSH_UTIL.Write_Log('Setting sync_line_id to ' || to_char(p_sync_line_id));
1685: wsh_pr_picking_session.sync_line_id := p_sync_line_id;
1686:
1687: -- Call to dynamically create SQL for Sync Lines
1688: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Sync_Details...');
1684: WSH_UTIL.Write_Log('Setting sync_line_id to ' || to_char(p_sync_line_id));
1685: wsh_pr_picking_session.sync_line_id := p_sync_line_id;
1686:
1687: -- Call to dynamically create SQL for Sync Lines
1688: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Sync_Details...');
1689: cs := WSH_PR_PICKING_SESSION.Sync_Details;
1690: IF cs = FAILURE THEN
1691: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Sync_Details');
1692: RETURN FAILURE;
1687: -- Call to dynamically create SQL for Sync Lines
1688: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Sync_Details...');
1689: cs := WSH_PR_PICKING_SESSION.Sync_Details;
1690: IF cs = FAILURE THEN
1691: WSH_UTIL.Write_Log('Error in WSH_PR_PICKING_SESSION.Sync_Details');
1692: RETURN FAILURE;
1693: END IF;
1694:
1695: -- Fetch the Sync Lines SQL
1692: RETURN FAILURE;
1693: END IF;
1694:
1695: -- Fetch the Sync Lines SQL
1696: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for sync SQL');
1697: sync_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('SYNC_SQL');
1698: IF sync_SQL = '-1' THEN
1699: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
1700: RETURN FAILURE;
1695: -- Fetch the Sync Lines SQL
1696: WSH_UTIL.Write_Log('Calling WSH_PR_PICKING_SESSION.Get_Session_Value for sync SQL');
1697: sync_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('SYNC_SQL');
1698: IF sync_SQL = '-1' THEN
1699: WSH_UTIL.Write_Log('Error in call to WSH_PR_PICKING_SESSION.Get_Session_Value');
1700: RETURN FAILURE;
1701: END IF;
1702:
1703: -- Call package to process buffer into cursor for parsing and executing
1702:
1703: -- Call package to process buffer into cursor for parsing and executing
1704: cs := Open_Execute_Cursor('SYNC');
1705: IF cs = FAILURE THEN
1706: WSH_UTIL.Write_Log('Error in Open_Execute_Cursor');
1707: RETURN FAILURE;
1708: END IF;
1709:
1710: -- Initialize first_sync_line for each call to set_sync_line
1740: BEGIN
1741:
1742: cs := WSH_PR_PICKING_SESSION.Construct_SQL('SET REQUEST');
1743: IF cs = FAILURE THEN
1744: WSH_UTIL.Write_Log('Error in Construct SQL');
1745: RETURN FAILURE;
1746: END IF;
1747: sreq_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('SET_REQUEST_SQL');
1748: sreq_cursor := DBMS_SQL.Open_Cursor;
1747: sreq_SQL := WSH_PR_PICKING_SESSION.Get_Session_Value('SET_REQUEST_SQL');
1748: sreq_cursor := DBMS_SQL.Open_Cursor;
1749: DBMS_SQL.Parse(sreq_cursor, sreq_SQL, DBMS_SQL.v7);
1750:
1751: WSH_UTIL.Write_Log('Binding Set Request ID cursor');
1752:
1753: WSH_UTIL.Write_Log('X_request_id = ' || to_char(wsh_pr_picking_session.request_id));
1754: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_request_id',
1755: wsh_pr_picking_session.request_id);
1749: DBMS_SQL.Parse(sreq_cursor, sreq_SQL, DBMS_SQL.v7);
1750:
1751: WSH_UTIL.Write_Log('Binding Set Request ID cursor');
1752:
1753: WSH_UTIL.Write_Log('X_request_id = ' || to_char(wsh_pr_picking_session.request_id));
1754: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_request_id',
1755: wsh_pr_picking_session.request_id);
1756: WSH_UTIL.Write_Log('X_sync_line_id = ' || to_char(p_sync_line_id));
1757: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_sync_line_id',
1752:
1753: WSH_UTIL.Write_Log('X_request_id = ' || to_char(wsh_pr_picking_session.request_id));
1754: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_request_id',
1755: wsh_pr_picking_session.request_id);
1756: WSH_UTIL.Write_Log('X_sync_line_id = ' || to_char(p_sync_line_id));
1757: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_sync_line_id',
1758: p_sync_line_id);
1759: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1760: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_request_date',
1755: wsh_pr_picking_session.request_id);
1756: WSH_UTIL.Write_Log('X_sync_line_id = ' || to_char(p_sync_line_id));
1757: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_sync_line_id',
1758: p_sync_line_id);
1759: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1760: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_request_date',
1761: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1762: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1763: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_request_date',
1758: p_sync_line_id);
1759: WSH_UTIL.Write_Log('X_from_request_date = ' || to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1760: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_request_date',
1761: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1762: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1763: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_request_date',
1764: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1765: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
1766: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_inventory_item_id',
1761: to_char(wsh_pr_picking_session.from_request_date,'YYYY/MM/DD HH24:MI:SS'));
1762: WSH_UTIL.Write_Log('X_to_request_date = ' || to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1763: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_request_date',
1764: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1765: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
1766: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_inventory_item_id',
1767: wsh_pr_picking_session.inventory_item_id);
1768: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
1769: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_warehouse_id',
1764: to_char(wsh_pr_picking_session.to_request_date,'YYYY/MM/DD HH24:MI:SS'));
1765: WSH_UTIL.Write_Log('X_inventory_item_id = ' || to_char(wsh_pr_picking_session.inventory_item_id));
1766: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_inventory_item_id',
1767: wsh_pr_picking_session.inventory_item_id);
1768: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
1769: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_warehouse_id',
1770: wsh_pr_picking_session.warehouse_id);
1771: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1772: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_sched_ship_date',
1767: wsh_pr_picking_session.inventory_item_id);
1768: WSH_UTIL.Write_Log('X_warehouse_id = ' || to_char(wsh_pr_picking_session.warehouse_id));
1769: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_warehouse_id',
1770: wsh_pr_picking_session.warehouse_id);
1771: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1772: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_sched_ship_date',
1773: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1774: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1775: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_sched_ship_date',
1770: wsh_pr_picking_session.warehouse_id);
1771: WSH_UTIL.Write_Log('X_from_sched_ship_date = ' || to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1772: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_from_sched_ship_date',
1773: to_char(wsh_pr_picking_session.from_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1774: WSH_UTIL.Write_Log('X_to_sched_ship_date = ' || to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1775: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_sched_ship_date',
1776: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1777:
1778: v_ignore := DBMS_SQL.Execute(sreq_cursor);
1775: DBMS_SQL.Bind_Variable(sreq_cursor, ':X_to_sched_ship_date',
1776: to_char(wsh_pr_picking_session.to_sched_ship_date,'YYYY/MM/DD HH24:MI:SS'));
1777:
1778: v_ignore := DBMS_SQL.Execute(sreq_cursor);
1779: WSH_UTIL.Write_Log('Updated ' || to_char(v_ignore) || ' lines in model');
1780: DBMS_SQL.Close_Cursor(sreq_cursor);
1781: RETURN SUCCESS;
1782:
1783: EXCEPTION
1782:
1783: EXCEPTION
1784: WHEN OTHERS THEN
1785: Close_Cursors;
1786: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Open_Execute_Cursor');
1787: RETURN FAILURE;
1788:
1789: END Set_Request_Lines;
1790:
1839: i BINARY_INTEGER;
1840: cs BINARY_INTEGER;
1841:
1842: BEGIN
1843: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Get_Sync_Line_Details');
1844:
1845: -- handle uninitialized package errors here
1846: IF initialized = FALSE THEN
1847: WSH_UTIL.Write_Log('The package must be initialized before use');
1843: WSH_UTIL.Write_Log('Starting WSH_PICKING_ROWS.Get_Sync_Line_Details');
1844:
1845: -- handle uninitialized package errors here
1846: IF initialized = FALSE THEN
1847: WSH_UTIL.Write_Log('The package must be initialized before use');
1848: call_parameters(2) := FAILURE;
1849: RETURN;
1850: END IF;
1851:
1876: cs := DONE;
1877: END IF;
1878:
1879: IF cs = DONE THEN
1880: WSH_UTIL.Write_Log('Fetched all lines');
1881: EXIT;
1882: END IF;
1883: ELSE
1884: first_sync_line := sync_table(current_sync_line - 1);
1926:
1927: EXCEPTION
1928: WHEN OTHERS THEN
1929: Close_Cursors;
1930: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Get_Sync_Line_Details');
1931: call_parameters(2) := FAILURE;
1932:
1933: END Get_Sync_Line_Details;
1934:
1963: i BINARY_INTEGER;
1964: cs BINARY_INTEGER;
1965:
1966: BEGIN
1967: WSH_UTIL.Write_Log('In Get_Non_Ship_lines');
1968: -- handle uninitialized package errors here
1969: IF initialized = FALSE THEN
1970: WSH_UTIL.Write_Log('The package must be initialized before use');
1971: call_parameters(2) := FAILURE;
1966: BEGIN
1967: WSH_UTIL.Write_Log('In Get_Non_Ship_lines');
1968: -- handle uninitialized package errors here
1969: IF initialized = FALSE THEN
1970: WSH_UTIL.Write_Log('The package must be initialized before use');
1971: call_parameters(2) := FAILURE;
1972: RETURN;
1973: END IF;
1974:
1973: END IF;
1974:
1975: IF ns_lines_refetch_mode = FALSE THEN
1976: -- Process non-shippable lines
1977: WSH_UTIL.Write_Log('Calling Open_non_Shippable_SQL_Cursor...');
1978: cs := Open_Non_Shippable_SQL_Cursor;
1979: IF cs = FAILURE THEN
1980: WSH_UTIL.Write_Log('Error in Open_Non_Shippable_SQL_Cursor');
1981: call_parameters(2) := FAILURE;
1976: -- Process non-shippable lines
1977: WSH_UTIL.Write_Log('Calling Open_non_Shippable_SQL_Cursor...');
1978: cs := Open_Non_Shippable_SQL_Cursor;
1979: IF cs = FAILURE THEN
1980: WSH_UTIL.Write_Log('Error in Open_Non_Shippable_SQL_Cursor');
1981: call_parameters(2) := FAILURE;
1982: RETURN;
1983: END IF;
1984: END IF;
2010: cs := DONE;
2011: END IF;
2012:
2013: IF cs = FAILURE THEN
2014: WSH_UTIL.Write_Log('Failed in Get_Non_Ship_Lines');
2015: call_parameters(2) := FAILURE;
2016: RETURN;
2017: ELSIF cs = DONE THEN
2018: WSH_UTIL.Write_Log('Fetched all lines');
2014: WSH_UTIL.Write_Log('Failed in Get_Non_Ship_Lines');
2015: call_parameters(2) := FAILURE;
2016: RETURN;
2017: ELSIF cs = DONE THEN
2018: WSH_UTIL.Write_Log('Fetched all lines');
2019: EXIT;
2020: END IF;
2021: ELSE
2022: first_line := release_table(current_line - 1);
2043:
2044: EXCEPTION
2045: WHEN OTHERS THEN
2046: Close_Cursors;
2047: WSH_UTIL.Default_Handler('WSH_PR_PICKING_ROWS.Get_Non_Ship_Lines');
2048: call_parameters(2) := FAILURE;
2049:
2050: END Get_Non_Ship_Lines;
2051: