[Home] [Help]
PACKAGE BODY: APPS.WSH_PR_PICKING_ROWS
Source
1 PACKAGE BODY WSH_PR_PICKING_ROWS AS
2 /* $Header: WSHPRPRB.pls 115.14 99/07/16 08:20:06 porting ship $ */
3
4 --
5 -- Package
6 -- WSH_PR_PICKING_ROWS
7 --
8 -- Purpose
9 -- This package does the following:
10 -- - Open and fetch unreleased line details cursor
11 -- - Open and fetch backorderd line details cursor
12 -- - Open and fetch non-shippable lines cursor
13 -- - Merges unreleased and backordered lines
14 -- - Provides a table of the eligible release rows
15 -- to the client Pro*C program
16 --
17
18 --
19 -- PACKAGE CONSTANTS
20 --
21
22 SUCCESS CONSTANT BINARY_INTEGER := 0;
23 FAILURE CONSTANT BINARY_INTEGER := -1;
24 DONE CONSTANT BINARY_INTEGER := 2;
25 SS_MODEL_REFETCH CONSTANT BINARY_INTEGER := 5;
26
27 --
28 -- PACKAGE VARIABLES
29 --
30
31 initialized BOOLEAN := FALSE;
32
33 -- Track local PL/SQL table information
34 current_line BINARY_INTEGER := 1;
35 current_sync_line BINARY_INTEGER := 1;
36 first_line relRecTyp;
37 first_sync_line relRecTyp;
38 sync_line_id BINARY_INTEGER;
39 MAX_LINES BINARY_INTEGER := 52;
40
41 -- Track SQL buffers and cursors
42 unreleased_SQL VARCHAR2(10000);
43 u_cursor INTEGER;
44 backordered_SQL VARCHAR2(10000);
45 b_cursor INTEGER;
46 sync_SQL VARCHAR2(10000);
47 s_cursor INTEGER;
48 non_ship_SQL VARCHAR2(10000);
49 ns_cursor INTEGER;
50 sreq_SQL VARCHAR2(10000);
51 sreq_cursor INTEGER;
52
53 backorder_mode VARCHAR2(1);
54 primary_rsr VARCHAR2(30);
55 v_ignore INTEGER;
56
57 -- Determine if this is the first time Get_Line_Details_Pvt is called
58 first_call_gld VARCHAR2(1) := 'Y';
59
60 -- When re-fetching due to large model/ship set sizes, to make sure
61 -- only enough to lines to make up the model are fetched when it is
62 -- larger than 50
63 ss_model_fetch_mode BOOLEAN;
64 model_sync_line BINARY_INTEGER;
65
66 -- To keep track of non_ship lines refetch mode
67 ns_lines_refetch_mode BOOLEAN;
68
69 -- Column variables for DBMS_SQL package for mapping to selected values from cursors
70
71 -- Unreleased lines -- Backordered lines
72 u_line_id BINARY_INTEGER; b_line_id BINARY_INTEGER;
73 u_header_id BINARY_INTEGER; b_header_id BINARY_INTEGER;
74 u_org_id BINARY_INTEGER; b_org_id BINARY_INTEGER;
75 u_ato_flag VARCHAR2(1); b_ato_flag VARCHAR2(1);
76 u_line_detail_id BINARY_INTEGER; b_line_detail_id BINARY_INTEGER;
77 u_ship_model_complete VARCHAR2(1); b_ship_model_complete VARCHAR2(1);
78 u_ship_set_number BINARY_INTEGER; b_ship_set_number BINARY_INTEGER;
79 u_parent_line_id BINARY_INTEGER; b_parent_line_id BINARY_INTEGER;
80 u_ld_warehouse_id BINARY_INTEGER; b_ld_warehouse_id BINARY_INTEGER;
81 u_ship_to_site_use_id BINARY_INTEGER; b_ship_to_site_use_id BINARY_INTEGER;
82 u_ship_to_contact_id BINARY_INTEGER; b_ship_to_contact_id BINARY_INTEGER;
83 u_ship_method_code VARCHAR2(30); b_ship_method_code VARCHAR2(30);
84 u_shipment_priority VARCHAR2(30); b_shipment_priority VARCHAR2(30);
85 u_departure_id BINARY_INTEGER; b_departure_id BINARY_INTEGER;
86 u_delivery_id BINARY_INTEGER; b_delivery_id BINARY_INTEGER;
87 u_item_type_code VARCHAR2(30); b_item_type_code VARCHAR2(30);
88 u_schedule_date BINARY_INTEGER; b_schedule_date BINARY_INTEGER;
89 u_ordered_quantity BINARY_INTEGER; b_ordered_quantity BINARY_INTEGER;
90 u_cancelled_quantity BINARY_INTEGER; b_cancelled_quantity BINARY_INTEGER;
91 u_l_inventory_item_id BINARY_INTEGER; b_l_inventory_item_id BINARY_INTEGER;
92 u_ld_inventory_item_id BINARY_INTEGER; b_ld_inventory_item_id BINARY_INTEGER;
93 u_customer_item_id BINARY_INTEGER; b_customer_item_id BINARY_INTEGER;
94 u_dep_plan_required_flag VARCHAR2(1); b_dep_plan_required_flag VARCHAR2(1);
95 u_shipment_schedule_line_id BINARY_INTEGER; b_shipment_schedule_line_id BINARY_INTEGER;
96 u_unit_code VARCHAR2(3); b_unit_code VARCHAR2(3);
97 u_line_type_code VARCHAR2(30); b_line_type_code VARCHAR2(30);
98 u_component_code VARCHAR2(1000); b_component_code VARCHAR2(1000);
99 u_standard_comp_freeze_date VARCHAR2(15); b_standard_comp_freeze_date VARCHAR2(15);
100 u_order_number BINARY_INTEGER; b_order_number BINARY_INTEGER;
101 u_order_type_id BINARY_INTEGER; b_order_type_id BINARY_INTEGER;
102 u_customer_id BINARY_INTEGER; b_customer_id BINARY_INTEGER;
103 u_invoice_to_site_use_id BINARY_INTEGER; b_invoice_to_site_use_id BINARY_INTEGER;
104 u_planned_departure_date_d BINARY_INTEGER; b_planned_departure_date_d BINARY_INTEGER;
105 u_planned_departure_date_t BINARY_INTEGER; b_planned_departure_date_t BINARY_INTEGER;
106 u_master_container_id BINARY_INTEGER; b_master_container_id BINARY_INTEGER;
107 u_detail_container_id BINARY_INTEGER; b_detail_container_id BINARY_INTEGER;
108 u_load_seq_number BINARY_INTEGER; b_load_seq_number BINARY_INTEGER;
109 u_invoice_value NUMBER; b_invoice_value NUMBER;
110
111 -- Demand synchronized
112 s_line_id BINARY_INTEGER;
113 s_header_id BINARY_INTEGER;
114 s_org_id BINARY_INTEGER;
115 s_ato_flag VARCHAR2(1);
116 s_line_detail_id BINARY_INTEGER;
117 s_ship_model_complete VARCHAR2(1);
118 s_ship_set_number BINARY_INTEGER;
119 s_parent_line_id BINARY_INTEGER;
120 s_ld_warehouse_id BINARY_INTEGER;
121 s_ship_to_site_use_id BINARY_INTEGER;
122 s_ship_to_contact_id BINARY_INTEGER;
123 s_ship_method_code VARCHAR2(30);
124 s_shipment_priority VARCHAR2(30);
125 s_departure_id BINARY_INTEGER;
126 s_delivery_id BINARY_INTEGER;
127 s_schedule_date BINARY_INTEGER;
128 s_customer_item_id BINARY_INTEGER;
129 s_dep_plan_required_flag VARCHAR2(1);
130 s_order_number BINARY_INTEGER;
131 s_order_type_id BINARY_INTEGER;
132 s_customer_id BINARY_INTEGER;
133 s_invoice_to_site_use_id BINARY_INTEGER;
134 s_master_container_id BINARY_INTEGER;
135 s_detail_container_id BINARY_INTEGER;
136 s_load_seq_number BINARY_INTEGER;
137
138 -- Non-ship lines
139 n_line_id BINARY_INTEGER;
140 n_header_id BINARY_INTEGER;
141 n_line_detail_id BINARY_INTEGER;
142 n_org_id BINARY_INTEGER;
143
144 --
145 -- PUBLIC FUNCTIONS/PROCEDURES
146 --
147
148 --
149 -- FORWARD DECLERATIONS
150 --
151 FUNCTION Open_Unreleased_SQL_Cursor RETURN BINARY_INTEGER;
152 FUNCTION Open_Backordered_SQL_Cursor RETURN BINARY_INTEGER;
153 FUNCTION Open_Non_Shippable_SQL_cursor RETURN BINARY_INTEGER;
154 FUNCTION Open_Execute_Cursor (
155 p_mode IN VARCHAR2
156 ) RETURN BINARY_INTEGER;
157 FUNCTION Get_Line_Details_Pvt RETURN BINARY_INTEGER;
158 PROCEDURE Map_Col_Value(p_source IN VARCHAR2);
159 PROCEDURE Insert_RL_Row(p_source IN VARCHAR2);
160
161 --
162 -- Name
163 -- FUNCTION Init
164 --
165 -- Purpose
166 -- This function, based on the mode opens appropriate cursors.
167 --
168 -- Return Values
169 -- -1 => Failure
170 -- 0 => Success
171 --
172 -- Notes
173 --
174
175 FUNCTION Init
176 RETURN BINARY_INTEGER IS
177
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;
187 END IF;
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;
206 END IF;
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;
216 END IF;
217 END IF;
218
219 ss_model_fetch_mode := FALSE;
220 ns_lines_refetch_mode := FALSE;
221
222 --
223 -- Make sure the first_line record has header_id set to -1
224 -- This is used to determine whether the Get_Line_Details
225 -- is called for the first time or not. The first_line is set
226 -- as a dummy line
227 --
228 first_line.header_id := -1;
229 first_sync_line.header_id := -1;
230
231 initialized := TRUE;
232 RETURN SUCCESS;
233
234 END Init;
235
236
237 --
238 -- Name
239 -- FUNCTION Get_Row_Count
240 --
241 -- Purpose
242 -- This function returns the number of rows returned in the
243 -- previous call to Get_Line_Details
244 --
245 -- Return Values
246 -- Number of rows fetched in previous call
247 --
248 -- Notes
249 --
250
251 FUNCTION Get_Size
252 RETURN BINARY_INTEGER IS
253
254 BEGIN
255
256 RETURN release_table.count;
257
258 END Get_Size;
259
260
261 --
262 -- Name
263 -- FUNCTION Open_Unreleased_SQL_Cursor
264 --
265 -- Purpose
266 -- This function calls WSH_PR_PICKING_SESSION to create the dynamic SQL
267 -- statement for unreleased lines, calls open_execute_cursor to open,
268 -- parse and execute the cursor for unreleased lines.
269 --
270 -- Return Values
271 -- -1 => Failure
272 -- 0 => Success
273 --
274 -- Notes
275 --
276
277 FUNCTION Open_Unreleased_SQL_Cursor
278 RETURN BINARY_INTEGER IS
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;
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
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
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;
309
310 END Open_Unreleased_SQL_Cursor;
311
312
313 --
314 -- Name
315 -- FUNCTION Open_Backordered_SQL_Cursor
316 --
317 -- Purpose
318 -- This function calls WSH_PR_PICKING_SESSION to create the dynamic SQL
319 -- statement for backordered lines, calls open_execute_cursor to open,
320 -- parse and execute the cursor for backordered lines.
321 --
322 -- Return Values
323 -- -1 => Failure
324 -- 0 => Success
325 --
326 -- Notes
327 --
328
329 FUNCTION Open_Backordered_SQL_Cursor
330 RETURN BINARY_INTEGER IS
331
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');
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;
350 END IF;
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;
360
361 END Open_Backordered_SQL_Cursor;
362
363
364 --
365 -- Name
366 -- FUNCTION Open_Non_Shippable_SQL_Cursor
367 --
368 -- Purpose
369 -- This function calls WSH_PR_PICKING_SESSION to create the dynamic SQL
370 -- statement for non-shippable lines, calls open_execute_cursor to open,
371 -- parse and execute the cursor for non-shippable lines.
372 --
373 -- Return Values
374 -- -1 => Failure
375 -- 0 => Success
376 --
377 -- Notes
378 --
379
380 FUNCTION Open_Non_Shippable_SQL_Cursor
381 RETURN BINARY_INTEGER IS
382
383 cs BINARY_INTEGER;
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');
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;
402 END IF;
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;
412
413 END Open_Non_Shippable_SQL_Cursor;
414
415
416 --
417 -- Name
418 -- FUNCTION Open_Execute_cursor
419 --
420 -- Purpose
421 -- This routine opens the appropriate cursor, parses and executes it
422 -- based on the parameter passed to it. It does this for unreleased,
423 -- backordered and non-ship lines.
424 -- It also binds the variables in the cursor to the release criteria values
425 --
426 -- Arguments
427 -- p_mode specifies what cursor to use.
428 --
429 -- Return Values
430 -- -1 => Failure
431 -- 0 => Success
432 --
433 -- Notes
434 --
435
436 FUNCTION Open_Execute_Cursor (
437 p_mode IN VARCHAR2
438 )
439 RETURN BINARY_INTEGER IS
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;
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');
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);
460 DBMS_SQL.Define_Column(u_cursor, 5, u_line_detail_id);
461 DBMS_SQL.Define_Column(u_cursor, 6, u_ship_model_complete, 1);
462 DBMS_SQL.Define_Column(u_cursor, 7, u_ship_set_number);
463 DBMS_SQL.Define_Column(u_cursor, 8, u_parent_line_id);
464 DBMS_SQL.Define_Column(u_cursor, 9, u_ld_warehouse_id);
465 DBMS_SQL.Define_Column(u_cursor, 10, u_ship_to_site_use_id);
466 DBMS_SQL.Define_Column(u_cursor, 11, u_ship_to_contact_id);
467 DBMS_SQL.Define_Column(u_cursor, 12, u_ship_method_code, 30);
468 DBMS_SQL.Define_Column(u_cursor, 13, u_shipment_priority, 30);
469 DBMS_SQL.Define_Column(u_cursor, 14, u_departure_id);
470 DBMS_SQL.Define_Column(u_cursor, 15, u_delivery_id);
471 DBMS_SQL.Define_Column(u_cursor, 16, u_item_type_code, 30);
472 DBMS_SQL.Define_Column(u_cursor, 17, u_schedule_date);
473 DBMS_SQL.Define_Column(u_cursor, 18, u_ordered_quantity);
474 DBMS_SQL.Define_Column(u_cursor, 19, u_cancelled_quantity);
475 DBMS_SQL.Define_Column(u_cursor, 20, u_l_inventory_item_id);
476 DBMS_SQL.Define_Column(u_cursor, 21, u_ld_inventory_item_id);
477 DBMS_SQL.Define_Column(u_cursor, 22, u_customer_item_id);
478 DBMS_SQL.Define_Column(u_cursor, 23, u_dep_plan_required_flag, 1);
479 DBMS_SQL.Define_Column(u_cursor, 24, u_shipment_schedule_line_id);
480 DBMS_SQL.Define_Column(u_cursor, 25, u_unit_code, 3);
481 DBMS_SQL.Define_Column(u_cursor, 26, u_line_type_code, 30);
482 DBMS_SQL.Define_Column(u_cursor, 27, u_component_code, 1000);
483 DBMS_SQL.Define_Column(u_cursor, 28, u_standard_comp_freeze_date, 15);
484 DBMS_SQL.Define_Column(u_cursor, 29, u_order_number);
485 DBMS_SQL.Define_Column(u_cursor, 30, u_order_type_id);
486 DBMS_SQL.Define_Column(u_cursor, 31, u_customer_id);
487 DBMS_SQL.Define_Column(u_cursor, 32, u_invoice_to_site_use_id);
488 DBMS_SQL.Define_Column(u_cursor, 33, u_planned_departure_date_d);
489 DBMS_SQL.Define_Column(u_cursor, 34, u_planned_departure_date_t);
490 DBMS_SQL.Define_Column(u_cursor, 35, u_master_container_id);
491 DBMS_SQL.Define_Column(u_cursor, 36, u_detail_container_id);
492 DBMS_SQL.Define_Column(u_cursor, 37, u_load_seq_number);
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',
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));
509 DBMS_SQL.Bind_Variable(u_cursor, ':X_customer_id',
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
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',
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',
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',
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',
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));
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
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
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');
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);
589 DBMS_SQL.Define_Column(b_cursor, 5, b_line_detail_id);
590 DBMS_SQL.Define_Column(b_cursor, 6, b_ship_model_complete, 1);
591 DBMS_SQL.Define_Column(b_cursor, 7, b_ship_set_number);
592 DBMS_SQL.Define_Column(b_cursor, 8, b_parent_line_id);
593 DBMS_SQL.Define_Column(b_cursor, 9, b_ld_warehouse_id);
594 DBMS_SQL.Define_Column(b_cursor, 10, b_ship_to_site_use_id);
595 DBMS_SQL.Define_Column(b_cursor, 11, b_ship_to_contact_id);
596 DBMS_SQL.Define_Column(b_cursor, 12, b_ship_method_code, 30);
597 DBMS_SQL.Define_Column(b_cursor, 13, b_shipment_priority, 30);
598 DBMS_SQL.Define_Column(b_cursor, 14, b_departure_id);
599 DBMS_SQL.Define_Column(b_cursor, 15, b_delivery_id);
600 DBMS_SQL.Define_Column(b_cursor, 16, b_item_type_code, 30);
601 DBMS_SQL.Define_Column(b_cursor, 17, b_schedule_date);
602 DBMS_SQL.Define_Column(b_cursor, 18, b_ordered_quantity);
603 DBMS_SQL.Define_Column(b_cursor, 19, b_cancelled_quantity);
604 DBMS_SQL.Define_Column(b_cursor, 20, b_l_inventory_item_id);
605 DBMS_SQL.Define_Column(b_cursor, 21, b_ld_inventory_item_id);
606 DBMS_SQL.Define_Column(b_cursor, 22, b_customer_item_id);
607 DBMS_SQL.Define_Column(b_cursor, 23, b_dep_plan_required_flag, 1);
608 DBMS_SQL.Define_Column(b_cursor, 24, b_shipment_schedule_line_id);
609 DBMS_SQL.Define_Column(b_cursor, 25, b_unit_code, 3);
610 DBMS_SQL.Define_Column(b_cursor, 26, b_line_type_code, 30);
611 DBMS_SQL.Define_Column(b_cursor, 27, b_component_code, 1000);
612 DBMS_SQL.Define_Column(b_cursor, 28, b_standard_comp_freeze_date, 15);
613 DBMS_SQL.Define_Column(b_cursor, 29, b_order_number);
614 DBMS_SQL.Define_Column(b_cursor, 30, b_order_type_id);
615 DBMS_SQL.Define_Column(b_cursor, 31, b_customer_id);
616 DBMS_SQL.Define_Column(b_cursor, 32, b_invoice_to_site_use_id);
617 DBMS_SQL.Define_Column(b_cursor, 33, b_planned_departure_date_d);
618 DBMS_SQL.Define_Column(b_cursor, 34, b_planned_departure_date_t);
619 DBMS_SQL.Define_Column(b_cursor, 35, b_master_container_id);
620 DBMS_SQL.Define_Column(b_cursor, 36, b_detail_container_id);
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',
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));
637 DBMS_SQL.Bind_Variable(b_cursor, ':X_customer_id',
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
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',
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',
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',
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',
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));
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
699 WSH_UTIL.Write_Log('Executing backordered cursor');
700 v_ignore := DBMS_SQL.Execute(b_cursor);
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');
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);
718 DBMS_SQL.Define_Column(s_cursor, 5, s_line_detail_id);
719 DBMS_SQL.Define_Column(s_cursor, 6, s_ship_model_complete, 1);
720 DBMS_SQL.Define_Column(s_cursor, 7, s_ship_set_number);
721 DBMS_SQL.Define_Column(s_cursor, 8, s_parent_line_id);
722 DBMS_SQL.Define_Column(s_cursor, 9, s_ld_warehouse_id);
723 DBMS_SQL.Define_Column(s_cursor, 10, s_ship_to_site_use_id);
724 DBMS_SQL.Define_Column(s_cursor, 11, s_ship_to_contact_id);
725 DBMS_SQL.Define_Column(s_cursor, 12, s_ship_method_code, 30);
726 DBMS_SQL.Define_Column(s_cursor, 13, s_shipment_priority, 30);
727 DBMS_SQL.Define_Column(s_cursor, 14, s_departure_id);
728 DBMS_SQL.Define_Column(s_cursor, 15, s_delivery_id);
729 DBMS_SQL.Define_Column(s_cursor, 16, s_schedule_date);
730 DBMS_SQL.Define_Column(s_cursor, 17, s_customer_item_id);
731 DBMS_SQL.Define_Column(s_cursor, 18, s_dep_plan_required_flag, 1);
732 DBMS_SQL.Define_Column(s_cursor, 19, s_order_number);
733 DBMS_SQL.Define_Column(s_cursor, 20, s_order_type_id);
734 DBMS_SQL.Define_Column(s_cursor, 21, s_customer_id);
735 DBMS_SQL.Define_Column(s_cursor, 22, s_invoice_to_site_use_id);
736 DBMS_SQL.Define_Column(s_cursor, 23, s_master_container_id);
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);
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',
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',
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',
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));
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
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
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);
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);
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
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));
821 DBMS_SQL.Bind_Variable(ns_cursor, ':X_customer_id',
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
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',
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',
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));
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
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');
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
882
883 --
884 -- Name
885 -- PROCEDURE Get_Line_Details
886 --
887 -- Purpose
888 -- This routine returns information about the lines that
889 -- are eligible for release. They are placed in a table.
890 -- It controls the number of lines to be fetched and
891 -- provided to the client. The default value is set at 50.
892 -- This may be modified by the first parameter if it is not 0.
893 -- It also indicates whether there are any more lines to be
894 -- retrieved.
895 --
896 -- Arguments
897 -- All the column values and call_parameters
898 -- Call_parameters(2) transalates as
899 -- -1 => Failure
900 -- 0 => Success, but there are more rows to be fetched
901 -- 2 => Success, no more rows to fetch
902 -- Call_parameters(1) is the size
903 --
904 -- Notes
905 --
906
907 PROCEDURE Get_Line_Details(
908 line_id OUT intTabTyp,
909 header_id OUT intTabTyp,
910 org_id OUT intTabTyp,
911 ato_flag OUT cflagTabTyp,
912 line_detail_id OUT intTabTyp,
913 ship_model_complete OUT cflagTabTyp,
914 ship_set_number OUT intTabTyp,
915 parent_line_id OUT intTabTyp,
916 ld_warehouse_id OUT intTabTyp,
917 ship_to_site_use_id OUT intTabTyp,
918 ship_to_contact_id OUT intTabTyp,
919 ship_method_code OUT cnameTabTyp,
920 shipment_priority OUT cnameTabTyp,
921 departure_id OUT intTabTyp,
922 delivery_id OUT intTabTyp,
923 item_type_code OUT cnameTabTyp,
924 schedule_date OUT intTabTyp,
925 ordered_quantity OUT intTabTyp,
926 cancelled_quantity OUT intTabTyp,
927 l_inventory_item_id OUT intTabTyp,
928 ld_inventory_item_id OUT intTabTyp,
929 customer_item_id OUT intTabTyp,
930 dep_plan_required_flag OUT cflagTabTyp,
931 shipment_schedule_line_id OUT intTabTyp,
932 unit_code OUT ccodeTabTyp,
933 line_type_code OUT cnameTabTyp,
934 component_code OUT cbufTabTyp,
935 standard_comp_freeze_date OUT cdateTabTyp,
936 order_number OUT intTabTyp,
937 order_type_id OUT intTabTyp,
938 customer_id OUT intTabTyp,
939 invoice_to_site_use_id OUT intTabTyp,
940 master_container_item_id OUT intTabTyp,
941 detail_container_item_id OUT intTabTyp,
942 load_seq_number OUT intTabTyp,
943 backorder_line OUT intTabTyp,
944 primary_rsr_switch OUT intTabTyp,
945 call_parameters IN OUT intTabTyp)
946 IS
947
948 i BINARY_INTEGER;
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;
958 RETURN;
959 END IF;
960
961 -- Clear the table and initialize table index
962 IF release_table.count <> 0 THEN
963 call_parameters.delete;
964 release_table.delete;
965 current_line := 1;
966 END IF;
967
968 -- If called after the first time, place the last row fetched in previous
969 -- call as the first row, since it was not returned in the previous call
970 IF first_line.header_id <> -1 THEN
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
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
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');
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
1004 IF release_table(current_line - 1).parent_line_id > 0 THEN
1005 model_sync_line := release_table(current_line - 1).parent_line_id;
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);
1024 release_table.delete(current_line - 1);
1025 current_line := current_line - 1;
1026 EXIT;
1027 END IF;
1028 END IF;
1029 END LOOP;
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
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;
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;
1053
1054 IF (((release_table(current_line - 1).ship_set_number > 0) and
1055 (release_table(current_line - 1).ship_set_number =
1056 release_table(current_line - 2).ship_set_number) and
1057 (release_table(current_line - 1).header_id =
1058 release_table(current_line - 2).header_id)) or
1059 (model_sync_line > 0 and
1060 (release_table(current_line - 1).line_id = model_sync_line or
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);
1070 current_line := current_line - 1;
1071 EXIT;
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;
1081 EXIT;
1082 END IF;
1083 END LOOP;
1084 END IF;
1085
1086
1087 -- Setup return values
1088 FOR i IN 1..release_table.count LOOP
1089 line_id(i) := release_table(i).line_id;
1090 header_id(i) := release_table(i).header_id;
1091 org_id(i) := release_table(i).org_id;
1092 ato_flag(i) := release_table(i).ato_flag;
1093 line_detail_id(i) := release_table(i).line_detail_id;
1094 ship_model_complete(i) := release_table(i).ship_model_complete;
1095 ship_set_number(i) := release_table(i).ship_set_number;
1096 parent_line_id(i) := release_table(i).parent_line_id;
1097 ld_warehouse_id(i) := release_table(i).ld_warehouse_id;
1098 ship_to_site_use_id(i) := release_table(i).ship_to_site_use_id;
1099 ship_to_contact_id(i) := release_table(i).ship_to_contact_id;
1100 ship_method_code(i) := release_table(i).ship_method_code;
1101 shipment_priority(i) := release_table(i).shipment_priority;
1102 departure_id(i) := release_table(i).departure_id;
1103 delivery_id(i) := release_table(i).delivery_id;
1104 item_type_code(i) := release_table(i).item_type_code;
1105 schedule_date(i) := release_table(i).schedule_date;
1106 ordered_quantity(i) := release_table(i).ordered_quantity;
1107 cancelled_quantity(i) := release_table(i).cancelled_quantity;
1108 l_inventory_item_id(i) := release_table(i).l_inventory_item_id;
1109 ld_inventory_item_id(i) := release_table(i).ld_inventory_item_id;
1110 customer_item_id(i) := release_table(i).customer_item_id;
1111 dep_plan_required_flag(i) := release_table(i).dep_plan_required_flag;
1112 shipment_schedule_line_id(i) := release_table(i).shipment_schedule_line_id;
1113 unit_code(i) := release_table(i).unit_code;
1114 line_type_code(i) := release_table(i).line_type_code;
1115 component_code(i) := release_table(i).component_code;
1116 standard_comp_freeze_date(i) := release_table(i).standard_comp_freeze_date;
1117 order_number(i) := release_table(i).order_number;
1118 order_type_id(i) := release_table(i).order_type_id;
1119 customer_id(i) := release_table(i).customer_id;
1120 invoice_to_site_use_id(i) := release_table(i).invoice_to_site_use_id;
1121 master_container_item_id(i) := release_table(i).master_container_item_id;
1122 detail_container_item_id(i) := release_table(i).detail_container_item_id;
1123 load_seq_number(i) := release_table(i).load_seq_number;
1124 backorder_line(i) := release_table(i).backorder_line;
1125 primary_rsr_switch(i) := release_table(i).primary_rsr_switch;
1126 END LOOP;
1127 call_parameters(1) := release_table.count;
1128 IF cs = DONE THEN
1129 call_parameters(2) := DONE;
1130 -- Reinitialize the first line marker since we have fetched all rows for
1131 -- reuse later with non-shippable lines
1132 first_line.header_id := -1;
1133 IF release_table.count > 0 THEN
1134 IF ss_model_fetch_mode = FALSE THEN
1135 primary_rsr_switch(release_table.count) := 1;
1136 END IF;
1137 END IF;
1138 ELSE
1139 call_parameters(2) := SUCCESS;
1140 END IF;
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
1150
1151 --
1152 -- Name
1153 -- FUNCTION Get_Line_Details_Pvt
1154 --
1155 -- Purpose
1156 -- This function fetches rows from each of the cursors for unreleased and
1157 -- backordered lines, performs a merge sort and inserts the next row in
1158 -- the release_table table based on the release sequence rule. It also
1159 -- indiactes in a column if there is a change in the most significant
1160 -- criteria in the release sequence rule.
1161 --
1162 -- Notes
1163 --
1164
1165 FUNCTION Get_Line_Details_Pvt
1166 RETURN BINARY_INTEGER IS
1167
1168 next_line VARCHAR2(1);
1169 empty_cursor VARCHAR2(1) := '';
1170 cs BINARY_INTEGER;
1171 line BINARY_INTEGER;
1172
1173 BEGIN
1174
1175 --
1176 -- Fetch unreleased lines only
1177 --
1178 line := 1;
1179 IF backorder_mode = 'E' THEN
1180 IF DBMS_SQL.Fetch_Rows(u_cursor) > 0 THEN
1181 Map_Col_Value('u');
1182 Insert_RL_Row('u');
1183 RETURN SUCCESS;
1184 ELSE
1185 DBMS_SQL.Close_Cursor(u_cursor);
1186 RETURN DONE;
1187 END IF;
1188
1189 --
1190 -- Fetch backordered lines only
1191 --
1192 ELSIF backorder_mode = 'O' THEN
1193
1194 line := 2;
1195 IF DBMS_SQL.Fetch_Rows(b_cursor) > 0 THEN
1196 Map_Col_Value('b');
1197 Insert_RL_Row('b');
1198 RETURN SUCCESS;
1199 ELSE
1200 DBMS_SQL.Close_Cursor(b_cursor);
1201 RETURN DONE;
1202 END IF;
1203 --
1204 -- Fetch both unreleased and backordered lines, and compare
1205 --
1206 ELSIF backorder_mode = 'I' THEN
1207
1208 -- Fetch row from unreleased lines cursor
1209 line := 3;
1210 IF first_call_gld = 'Y' THEN
1211 line := 4;
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;
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;
1229 IF NOT DBMS_SQL.Is_Open(u_cursor) AND NOT DBMS_SQL.Is_Open(b_cursor) THEN
1230 first_call_gld := 'N';
1231 RETURN DONE;
1232 END IF;
1233
1234 line := 7;
1235 IF NOT DBMS_SQL.Is_Open(u_cursor) THEN
1236 Insert_RL_Row('b');
1237 first_call_gld := 'N';
1238 RETURN SUCCESS;
1239 END IF;
1240
1241 line := 8;
1242 IF NOT DBMS_SQL.Is_Open(b_cursor) THEN
1243 line := 81;
1244 Insert_RL_Row('u');
1245 first_call_gld := 'N';
1246 RETURN SUCCESS;
1247 END IF;
1248 first_call_gld := 'N';
1249 END IF;
1250
1251 line := 9;
1252 IF NOT DBMS_SQL.Is_Open(b_cursor) THEN
1253 IF DBMS_SQL.Fetch_Rows(u_cursor) > 0 THEN
1254 Map_Col_Value('u');
1255 Insert_RL_Row('u');
1256 RETURN SUCCESS;
1257 ELSE
1258 DBMS_SQL.Close_Cursor(u_cursor);
1259 RETURN DONE;
1260 END IF;
1261 ELSIF NOT DBMS_SQL.Is_Open(u_cursor) THEN
1262 line := 10;
1263 IF DBMS_SQL.Fetch_Rows(b_cursor) > 0 THEN
1264 Map_Col_Value('b');
1265 Insert_RL_Row('b');
1266 RETURN SUCCESS;
1267 ELSE
1268 DBMS_SQL.Close_Cursor(b_cursor);
1269 RETURN DONE;
1270 END IF;
1271 ELSE
1272 line := 11;
1273 next_line := WSH_PR_PICKING_SESSION.Get_Next_Line_Detail(u_invoice_value,
1274 b_invoice_value,
1275 u_order_number,
1276 b_order_number,
1277 u_schedule_date,
1278 b_schedule_date,
1279 u_planned_departure_date_d,
1280 u_planned_departure_date_t,
1281 b_planned_departure_date_d,
1282 b_planned_departure_date_t,
1283 u_shipment_priority,
1284 b_shipment_priority);
1285
1286 IF next_line = 'u' THEN
1287 Insert_RL_Row('u');
1288 IF DBMS_SQL.Fetch_Rows(u_cursor) > 0 THEN
1289 Map_Col_Value('u');
1290 RETURN SUCCESS;
1291 ELSE
1292 DBMS_SQL.Close_Cursor(u_cursor);
1293 Insert_RL_Row('b');
1294 RETURN SUCCESS;
1295 END IF;
1296 ELSIF next_line = 'b' THEN
1297 Insert_RL_Row('b');
1298 IF DBMS_SQL.Fetch_Rows(b_cursor) > 0 THEN
1299 Map_Col_Value('b');
1300 RETURN SUCCESS;
1301 ELSE
1302 DBMS_SQL.Close_Cursor(b_cursor);
1303 Insert_RL_Row('u');
1304 RETURN SUCCESS;
1305 END IF;
1306 END IF;
1307
1308 line := 12;
1309 IF NOT DBMS_SQL.Is_Open(u_cursor) AND NOT DBMS_SQL.Is_Open(b_cursor) THEN
1310 RETURN DONE;
1311 END IF;
1312
1313 END IF;
1314
1315 END IF;
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;
1325
1326
1327 --
1328 -- Name
1329 -- PROCEDURE Map_Col_Value
1330 --
1331 -- Purpose
1332 -- This function maps the column value from the cursor
1333 -- to the variable (based on the source) after each fetch
1334 -- row call to DBMS_SQL. Utility to go with DBMS_SQL.
1335 --
1336 -- Arguments
1337 -- p_source - indicates whether to map the value for
1338 -- backordered lines ('b') or unreleased
1339 -- lines ('u')
1340 --
1341 -- Notes
1342 --
1343
1344 PROCEDURE Map_Col_Value(
1345 p_source IN VARCHAR2
1346 ) IS
1347
1348 cs BINARY_INTEGER;
1349
1350 BEGIN
1351 IF p_source = 'u' THEN
1352 DBMS_SQL.Column_Value(u_cursor, 1, u_line_id);
1353 DBMS_SQL.Column_Value(u_cursor, 2, u_header_id);
1354 DBMS_SQL.Column_Value(u_cursor, 3, u_org_id);
1355 DBMS_SQL.Column_Value(u_cursor, 4, u_ato_flag);
1356 DBMS_SQL.Column_Value(u_cursor, 5, u_line_detail_id);
1357 DBMS_SQL.Column_Value(u_cursor, 6, u_ship_model_complete);
1358 DBMS_SQL.Column_Value(u_cursor, 7, u_ship_set_number);
1359 DBMS_SQL.Column_Value(u_cursor, 8, u_parent_line_id);
1360 DBMS_SQL.Column_Value(u_cursor, 9, u_ld_warehouse_id);
1361 DBMS_SQL.Column_Value(u_cursor, 10, u_ship_to_site_use_id);
1362 DBMS_SQL.Column_Value(u_cursor, 11, u_ship_to_contact_id);
1363 DBMS_SQL.Column_Value(u_cursor, 12, u_ship_method_code);
1364 DBMS_SQL.Column_Value(u_cursor, 13, u_shipment_priority);
1365 DBMS_SQL.Column_Value(u_cursor, 14, u_departure_id);
1366 DBMS_SQL.Column_Value(u_cursor, 15, u_delivery_id);
1367 DBMS_SQL.Column_Value(u_cursor, 16, u_item_type_code);
1368 DBMS_SQL.Column_Value(u_cursor, 17, u_schedule_date);
1369 DBMS_SQL.Column_Value(u_cursor, 18, u_ordered_quantity);
1370 DBMS_SQL.Column_Value(u_cursor, 19, u_cancelled_quantity);
1371 DBMS_SQL.Column_Value(u_cursor, 20, u_l_inventory_item_id);
1372 DBMS_SQL.Column_Value(u_cursor, 21, u_ld_inventory_item_id);
1373 DBMS_SQL.Column_Value(u_cursor, 22, u_customer_item_id);
1374 DBMS_SQL.Column_Value(u_cursor, 23, u_dep_plan_required_flag);
1375 DBMS_SQL.Column_Value(u_cursor, 24, u_shipment_schedule_line_id);
1376 DBMS_SQL.Column_Value(u_cursor, 25, u_unit_code);
1377 DBMS_SQL.Column_Value(u_cursor, 26, u_line_type_code);
1378 DBMS_SQL.Column_Value(u_cursor, 27, u_component_code);
1379 DBMS_SQL.Column_Value(u_cursor, 28, u_standard_comp_freeze_date);
1380 DBMS_SQL.Column_Value(u_cursor, 29, u_order_number);
1381 DBMS_SQL.Column_Value(u_cursor, 30, u_order_type_id);
1382 DBMS_SQL.Column_Value(u_cursor, 31, u_customer_id);
1383 DBMS_SQL.Column_Value(u_cursor, 32, u_invoice_to_site_use_id);
1384 DBMS_SQL.Column_Value(u_cursor, 33, u_planned_departure_date_d);
1385 DBMS_SQL.Column_Value(u_cursor, 34, u_planned_departure_date_t);
1386 DBMS_SQL.Column_Value(u_cursor, 35, u_master_container_id);
1387 DBMS_SQL.Column_Value(u_cursor, 36, u_detail_container_id);
1388 DBMS_SQL.Column_Value(u_cursor, 37, u_load_seq_number);
1389 DBMS_SQL.Column_Value(u_cursor, 38, u_invoice_value);
1390 ELSIF p_source = 'b' THEN
1391 DBMS_SQL.Column_Value(b_cursor, 1, b_line_id);
1392 DBMS_SQL.Column_Value(b_cursor, 2, b_header_id);
1393 DBMS_SQL.Column_Value(b_cursor, 3, b_org_id);
1394 DBMS_SQL.Column_Value(b_cursor, 4, b_ato_flag);
1395 DBMS_SQL.Column_Value(b_cursor, 5, b_line_detail_id);
1396 DBMS_SQL.Column_Value(b_cursor, 6, b_ship_model_complete);
1397 DBMS_SQL.Column_Value(b_cursor, 7, b_ship_set_number);
1398 DBMS_SQL.Column_Value(b_cursor, 8, b_parent_line_id);
1399 DBMS_SQL.Column_Value(b_cursor, 9, b_ld_warehouse_id);
1400 DBMS_SQL.Column_Value(b_cursor, 10, b_ship_to_site_use_id);
1401 DBMS_SQL.Column_Value(b_cursor, 11, b_ship_to_contact_id);
1402 DBMS_SQL.Column_Value(b_cursor, 12, b_ship_method_code);
1403 DBMS_SQL.Column_Value(b_cursor, 13, b_shipment_priority);
1404 DBMS_SQL.Column_Value(b_cursor, 14, b_departure_id);
1405 DBMS_SQL.Column_Value(b_cursor, 15, b_delivery_id);
1406 DBMS_SQL.Column_Value(b_cursor, 16, b_item_type_code);
1407 DBMS_SQL.Column_Value(b_cursor, 17, b_schedule_date);
1408 DBMS_SQL.Column_Value(b_cursor, 18, b_ordered_quantity);
1409 DBMS_SQL.Column_Value(b_cursor, 19, b_cancelled_quantity);
1410 DBMS_SQL.Column_Value(b_cursor, 20, b_l_inventory_item_id);
1411 DBMS_SQL.Column_Value(b_cursor, 21, b_ld_inventory_item_id);
1412 DBMS_SQL.Column_Value(b_cursor, 22, b_customer_item_id);
1413 DBMS_SQL.Column_Value(b_cursor, 23, b_dep_plan_required_flag);
1414 DBMS_SQL.Column_Value(b_cursor, 24, b_shipment_schedule_line_id);
1415 DBMS_SQL.Column_Value(b_cursor, 25, b_unit_code);
1416 DBMS_SQL.Column_Value(b_cursor, 26, b_line_type_code);
1417 DBMS_SQL.Column_Value(b_cursor, 27, b_component_code);
1418 DBMS_SQL.Column_Value(b_cursor, 28, b_standard_comp_freeze_date);
1419 DBMS_SQL.Column_Value(b_cursor, 29, b_order_number);
1420 DBMS_SQL.Column_Value(b_cursor, 30, b_order_type_id);
1421 DBMS_SQL.Column_Value(b_cursor, 31, b_customer_id);
1422 DBMS_SQL.Column_Value(b_cursor, 32, b_invoice_to_site_use_id);
1423 DBMS_SQL.Column_Value(b_cursor, 33, b_planned_departure_date_d);
1424 DBMS_SQL.Column_Value(b_cursor, 34, b_planned_departure_date_t);
1425 DBMS_SQL.Column_Value(b_cursor, 35, b_master_container_id);
1426 DBMS_SQL.Column_Value(b_cursor, 36, b_detail_container_id);
1427 DBMS_SQL.Column_Value(b_cursor, 37, b_load_seq_number);
1428 DBMS_SQL.Column_Value(b_cursor, 38, b_invoice_value);
1429 ELSIF p_source = 's' THEN
1430 DBMS_SQL.Column_Value(s_cursor, 1, s_line_id);
1431 DBMS_SQL.Column_Value(s_cursor, 2, s_header_id);
1432 DBMS_SQL.Column_Value(s_cursor, 3, s_org_id);
1433 DBMS_SQL.Column_Value(s_cursor, 4, s_ato_flag);
1434 DBMS_SQL.Column_Value(s_cursor, 5, s_line_detail_id);
1435 DBMS_SQL.Column_Value(s_cursor, 6, s_ship_model_complete);
1436 DBMS_SQL.Column_Value(s_cursor, 7, s_ship_set_number);
1437 DBMS_SQL.Column_Value(s_cursor, 8, s_parent_line_id);
1438 DBMS_SQL.Column_Value(s_cursor, 9, s_ld_warehouse_id);
1439 DBMS_SQL.Column_Value(s_cursor, 10, s_ship_to_site_use_id);
1440 DBMS_SQL.Column_Value(s_cursor, 11, s_ship_to_contact_id);
1441 DBMS_SQL.Column_Value(s_cursor, 12, s_ship_method_code);
1442 DBMS_SQL.Column_Value(s_cursor, 13, s_shipment_priority);
1443 DBMS_SQL.Column_Value(s_cursor, 14, s_departure_id);
1444 DBMS_SQL.Column_Value(s_cursor, 15, s_delivery_id);
1445 DBMS_SQL.Column_Value(s_cursor, 16, s_schedule_date);
1446 DBMS_SQL.Column_Value(s_cursor, 17, s_customer_item_id);
1447 DBMS_SQL.Column_Value(s_cursor, 18, s_dep_plan_required_flag);
1448 DBMS_SQL.Column_Value(s_cursor, 19, s_order_number);
1449 DBMS_SQL.Column_Value(s_cursor, 20, s_order_type_id);
1450 DBMS_SQL.Column_Value(s_cursor, 21, s_customer_id);
1451 DBMS_SQL.Column_Value(s_cursor, 22, s_invoice_to_site_use_id);
1452 DBMS_SQL.Column_Value(s_cursor, 23, s_master_container_id);
1453 DBMS_SQL.Column_Value(s_cursor, 24, s_detail_container_id);
1454 DBMS_SQL.Column_Value(s_cursor, 25, s_load_seq_number);
1455 ELSIF p_source = 'n' THEN
1456 DBMS_SQL.Column_Value(ns_cursor, 1, n_line_id);
1457 DBMS_SQL.Column_Value(ns_cursor, 2, n_header_id);
1458 DBMS_SQL.Column_Value(ns_cursor, 3, n_line_detail_id);
1459 DBMS_SQL.Column_Value(ns_cursor, 4, n_org_id);
1460 END IF;
1461
1462 RETURN;
1463
1464 END Map_Col_Value;
1465
1466
1467 --
1468 -- Name
1469 -- PROCEDURE Insert_RL_Row
1470 --
1471 -- Purpose
1472 -- This function inserts a row from the source variables
1473 -- specified by p_source into release_table table. It also
1474 -- determines whether there is a switch in the most
1475 -- significant criteria of the release sequence rule.
1476 --
1477 -- Arguments
1478 -- p_source - indicates whether to insert a row for
1479 -- backordered lines ('b') or unreleased
1480 -- lines ('u')
1481 --
1482 -- Notes
1483 --
1484
1485 PROCEDURE Insert_RL_Row(
1486 p_source IN VARCHAR2
1487 ) IS
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));
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;
1501 release_table(current_line).org_id := u_org_id;
1502 release_table(current_line).ato_flag := u_ato_flag;
1503 release_table(current_line).line_detail_id := u_line_detail_id;
1504 release_table(current_line).ship_model_complete := u_ship_model_complete;
1505 release_table(current_line).ship_set_number := u_ship_set_number;
1506 release_table(current_line).parent_line_id := u_parent_line_id;
1507 release_table(current_line).ld_warehouse_id := u_ld_warehouse_id;
1508 release_table(current_line).ship_to_site_use_id := u_ship_to_site_use_id;
1509 release_table(current_line).ship_to_contact_id := u_ship_to_contact_id;
1510 release_table(current_line).ship_method_code := u_ship_method_code;
1511 release_table(current_line).shipment_priority := u_shipment_priority;
1512 release_table(current_line).departure_id := u_departure_id;
1513 release_table(current_line).delivery_id := u_delivery_id;
1514 release_table(current_line).item_type_code := u_item_type_code;
1515 release_table(current_line).schedule_date := u_schedule_date;
1516 release_table(current_line).ordered_quantity := u_ordered_quantity;
1517 release_table(current_line).cancelled_quantity := u_cancelled_quantity;
1518 release_table(current_line).l_inventory_item_id := u_l_inventory_item_id;
1519 release_table(current_line).ld_inventory_item_id := u_ld_inventory_item_id;
1520 release_table(current_line).customer_item_id := u_customer_item_id;
1521 release_table(current_line).dep_plan_required_flag := u_dep_plan_required_flag;
1522 release_table(current_line).shipment_schedule_line_id := u_shipment_schedule_line_id;
1523 release_table(current_line).unit_code := u_unit_code;
1524 release_table(current_line).line_type_code := u_line_type_code;
1525 release_table(current_line).component_code := u_component_code;
1526 release_table(current_line).standard_comp_freeze_date := u_standard_comp_freeze_date;
1527 release_table(current_line).order_number := u_order_number;
1528 release_table(current_line).order_type_id := u_order_type_id;
1529 release_table(current_line).customer_id := u_customer_id;
1530 release_table(current_line).invoice_to_site_use_id := u_invoice_to_site_use_id;
1531 release_table(current_line).planned_departure_date_d := u_planned_departure_date_d;
1532 release_table(current_line).planned_departure_date_t := u_planned_departure_date_t;
1533 release_table(current_line).master_container_item_id := u_master_container_id;
1534 release_table(current_line).detail_container_item_id := u_detail_container_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;
1544 release_table(current_line).header_id := b_header_id;
1545 release_table(current_line).org_id := b_org_id;
1546 release_table(current_line).ato_flag := b_ato_flag;
1547 release_table(current_line).line_detail_id := b_line_detail_id;
1548 release_table(current_line).ship_model_complete := b_ship_model_complete;
1549 release_table(current_line).ship_set_number := b_ship_set_number;
1550 release_table(current_line).parent_line_id := b_parent_line_id;
1551 release_table(current_line).ld_warehouse_id := b_ld_warehouse_id;
1552 release_table(current_line).ship_to_site_use_id := b_ship_to_site_use_id;
1553 release_table(current_line).ship_to_contact_id := b_ship_to_contact_id;
1554 release_table(current_line).ship_method_code := b_ship_method_code;
1555 release_table(current_line).shipment_priority := b_shipment_priority;
1556 release_table(current_line).departure_id := b_departure_id;
1557 release_table(current_line).delivery_id := b_delivery_id;
1558 release_table(current_line).item_type_code := b_item_type_code;
1559 release_table(current_line).schedule_date := b_schedule_date;
1560 release_table(current_line).ordered_quantity := b_ordered_quantity;
1561 release_table(current_line).cancelled_quantity := b_cancelled_quantity;
1562 release_table(current_line).l_inventory_item_id := b_l_inventory_item_id;
1563 release_table(current_line).ld_inventory_item_id := b_ld_inventory_item_id;
1564 release_table(current_line).customer_item_id := b_customer_item_id;
1565 release_table(current_line).dep_plan_required_flag := b_dep_plan_required_flag;
1566 release_table(current_line).shipment_schedule_line_id := b_shipment_schedule_line_id;
1567 release_table(current_line).unit_code := b_unit_code;
1568 release_table(current_line).line_type_code := b_line_type_code;
1569 release_table(current_line).component_code := b_component_code;
1570 release_table(current_line).standard_comp_freeze_date := b_standard_comp_freeze_date;
1571 release_table(current_line).order_number := b_order_number;
1572 release_table(current_line).order_type_id := b_order_type_id;
1573 release_table(current_line).customer_id := b_customer_id;
1574 release_table(current_line).invoice_to_site_use_id := b_invoice_to_site_use_id;
1575 release_table(current_line).planned_departure_date_d := b_planned_departure_date_d;
1576 release_table(current_line).planned_departure_date_t := b_planned_departure_date_t;
1577 release_table(current_line).master_container_item_id := b_master_container_id;
1578 release_table(current_line).detail_container_item_id := b_detail_container_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
1588 sync_table(current_sync_line).line_id := s_line_id;
1589 sync_table(current_sync_line).header_id := s_header_id;
1590 sync_table(current_sync_line).org_id := s_org_id;
1591 sync_table(current_sync_line).ato_flag := s_ato_flag;
1592 sync_table(current_sync_line).line_detail_id := s_line_detail_id;
1593 sync_table(current_sync_line).ship_model_complete := s_ship_model_complete;
1594 sync_table(current_sync_line).ship_set_number := s_ship_set_number;
1595 sync_table(current_sync_line).parent_line_id := s_parent_line_id;
1596 sync_table(current_sync_line).ld_warehouse_id := s_ld_warehouse_id;
1597 sync_table(current_sync_line).ship_to_site_use_id := s_ship_to_site_use_id;
1598 sync_table(current_sync_line).ship_to_contact_id := s_ship_to_contact_id;
1599 sync_table(current_sync_line).ship_method_code := s_ship_method_code;
1600 sync_table(current_sync_line).shipment_priority := s_shipment_priority;
1601 sync_table(current_sync_line).departure_id := s_departure_id;
1602 sync_table(current_sync_line).delivery_id := s_delivery_id;
1603 sync_table(current_sync_line).schedule_date := s_schedule_date;
1604 sync_table(current_sync_line).customer_item_id := s_customer_item_id;
1605 sync_table(current_sync_line).dep_plan_required_flag := s_dep_plan_required_flag;
1606 sync_table(current_sync_line).order_number := s_order_number;
1607 sync_table(current_sync_line).order_type_id := s_order_type_id;
1608 sync_table(current_sync_line).customer_id := s_customer_id;
1609 sync_table(current_sync_line).invoice_to_site_use_id := s_invoice_to_site_use_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
1619 current_sync_line := current_sync_line + 1;
1620 RETURN;
1621 ELSIF p_source = 'n' THEN
1622 release_table(current_line).line_id := n_line_id;
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;
1632 RETURN;
1633
1634 END IF;
1635
1636 IF current_line <> 1 THEN
1637 IF (((primary_rsr = 'INVOICE_VALUE')
1638 AND (release_table(current_line).invoice_value <> release_table(current_line - 1).invoice_value)) OR
1639 ((primary_rsr = 'ORDER_NUMBER')
1640 AND (release_table(current_line).order_number <> release_table(current_line - 1).order_number)) OR
1641 ((primary_rsr = 'SCHEDULE_DATE')
1642 AND (release_table(current_line).schedule_date <> release_table(current_line - 1).schedule_date)) OR
1643 ((primary_rsr = 'DEPARTURE')
1644 AND ((release_table(current_line).planned_departure_date_d <>
1645 release_table(current_line - 1).planned_departure_date_d) OR
1646 (release_table(current_line).planned_departure_date_t <>
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;
1656 END IF;
1657
1658 current_line := current_line + 1;
1659
1660 END Insert_RL_Row;
1661
1662
1663 --
1664 -- Name
1665 -- FUNCTION Set_Sync_Line
1666 --
1667 -- Purpose
1668 -- This routine sets up the sync_line_id to fetch the
1669 -- records that have been demand synchronized.
1670 --
1671 -- Arguments
1672 --
1673 -- Notes
1674 --
1675
1676 FUNCTION Set_Sync_Line(
1677 p_sync_line_id IN BINARY_INTEGER
1678 ) RETURN BINARY_INTEGER IS
1679
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...');
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
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
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
1711 -- since we are processing a different line for sync-ing
1712 first_sync_line.header_id := -1;
1713
1714 RETURN SUCCESS;
1715
1716 END Set_Sync_Line;
1717
1718
1719 --
1720 -- Name
1721 -- Function Set_Request_lines
1722 --
1723 -- Purpose
1724 -- Set request_id for non-ship lines that belong to the
1725 -- same warehouse and match the same other release criteria
1726 -- to be marked as Not Applicable later
1727 --
1728 -- Arguments
1729 -- p_sync_line_id => Sync line for the mode
1730 --
1731 -- Return Values
1732 -- 0 => Success
1733 -- -1 => Failure
1734 --
1735
1736 FUNCTION Set_Request_Lines(
1737 p_sync_line_id IN BINARY_INTEGER
1738 ) RETURN BINARY_INTEGER IS
1739 cs BINARY_INTEGER;
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;
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',
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',
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',
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
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
1791 --
1792 -- Name
1793 -- PROCEDURE Get_Sync_Line_Details
1794 --
1795 -- Purpose
1796 -- This routine returns lines that heve been BOM exploded and/or
1797 -- Demand synchronized for a particular line.
1798 --
1799 -- Arguments
1800 -- All the column values and call_parameters
1801 -- Call_parameters(2) transalates as
1802 -- -1 => Failure
1803 -- 0 => Success, but there are more rows to be fetched
1804 -- 2 => Success, no more rows to fetch
1805 -- Call_parameters(1) is the size
1806 --
1807 -- Notes
1808 --
1809
1810 PROCEDURE Get_Sync_Line_Details(
1811 line_id OUT intTabTyp,
1812 header_id OUT intTabTyp,
1813 org_id OUT intTabTyp,
1814 ato_flag OUT cflagTabTyp,
1815 line_detail_id OUT intTabTyp,
1816 ship_model_complete OUT cflagTabTyp,
1817 ship_set_number OUT intTabTyp,
1818 parent_line_id OUT intTabTyp,
1819 ld_warehouse_id OUT intTabTyp,
1820 ship_to_site_use_id OUT intTabTyp,
1821 ship_to_contact_id OUT intTabTyp,
1822 ship_method_code OUT cnameTabTyp,
1823 shipment_priority OUT cnameTabTyp,
1824 departure_id OUT intTabTyp,
1825 delivery_id OUT intTabTyp,
1826 schedule_date OUT intTabTyp,
1827 customer_item_id OUT intTabTyp,
1828 dep_plan_required_flag OUT cflagTabTyp,
1829 order_number OUT intTabTyp,
1830 order_type_id OUT intTabTyp,
1831 customer_id OUT intTabTyp,
1832 invoice_to_site_use_id OUT intTabTyp,
1833 master_container_item_id OUT intTabTyp,
1834 detail_container_item_id OUT intTabTyp,
1835 load_seq_number OUT intTabTyp,
1836 call_parameters IN OUT intTabTyp
1837 ) IS
1838
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');
1848 call_parameters(2) := FAILURE;
1849 RETURN;
1850 END IF;
1851
1852 -- Clear the table and initialize table index
1853 IF sync_table.count <> 0 THEN
1854 call_parameters.delete;
1855 sync_table.delete;
1856 current_sync_line := 1;
1857 END IF;
1858
1859 -- If called after the first time, place the last row fetched in previous
1860 -- call as the first row, since it was not returned in the previous call
1861 IF first_sync_line.header_id <> -1 THEN
1862 sync_table(current_sync_line) := first_sync_line;
1863 current_sync_line := current_sync_line + 1;
1864 END IF;
1865
1866 LOOP
1867 IF current_sync_line < MAX_LINES THEN
1868 -- Inserts the next line detail into sync_table
1869
1870 IF DBMS_SQL.Fetch_Rows(s_cursor) > 0 THEN
1871 Map_Col_Value('s');
1872 Insert_RL_Row('s');
1873 cs := SUCCESS;
1874 ELSE
1875 DBMS_SQL.Close_Cursor(s_cursor);
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);
1885 sync_table.delete(current_sync_line - 1);
1886 current_sync_line := current_sync_line - 1;
1887 EXIT;
1888 END IF;
1889 END LOOP;
1890
1891 -- Setup return values
1892 FOR i IN 1..sync_table.count LOOP
1893 line_id(i) := sync_table(i).line_id;
1894 header_id(i) := sync_table(i).header_id;
1895 org_id(i) := sync_table(i).org_id;
1896 ato_flag(i) := sync_table(i).ato_flag;
1897 line_detail_id(i) := sync_table(i).line_detail_id;
1898 ship_model_complete(i) := sync_table(i).ship_model_complete;
1899 ship_set_number(i) := sync_table(i).ship_set_number;
1900 parent_line_id(i) := sync_table(i).parent_line_id;
1901 ld_warehouse_id(i) := sync_table(i).ld_warehouse_id;
1902 ship_to_site_use_id(i) := sync_table(i).ship_to_site_use_id;
1903 ship_to_contact_id(i) := sync_table(i).ship_to_contact_id;
1904 ship_method_code(i) := sync_table(i).ship_method_code;
1905 shipment_priority(i) := sync_table(i).shipment_priority;
1906 departure_id(i) := sync_table(i).departure_id;
1907 delivery_id(i) := sync_table(i).delivery_id;
1908 schedule_date(i) := sync_table(i).schedule_date;
1909 customer_item_id(i) := sync_table(i).customer_item_id;
1910 dep_plan_required_flag(i) := sync_table(i).dep_plan_required_flag;
1911 order_number(i) := sync_table(i).order_number;
1912 order_type_id(i) := sync_table(i).order_type_id;
1913 customer_id(i) := sync_table(i).customer_id;
1914 invoice_to_site_use_id(i) := sync_table(i).invoice_to_site_use_id;
1915 master_container_item_id(i) := sync_table(i).master_container_item_id;
1916 detail_container_item_id(i) := sync_table(i).detail_container_item_id;
1917 load_seq_number(i) := sync_table(i).load_seq_number;
1918 END LOOP;
1919 call_parameters(1) := sync_table.count;
1920
1921 IF cs = DONE THEN
1922 call_parameters(2) := DONE;
1923 ELSE
1924 call_parameters(2) := SUCCESS;
1925 END IF;
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
1935
1936 --
1937 -- Name
1938 -- PROCEDURE Get_Non_Ship_Lines
1939 --
1940 -- Purpose
1941 -- This procedure fetches all the non-shippable lines that need to
1942 -- be passed through Pick Release.
1943 --
1944 -- Arguments
1945 -- All the column values needed for non ship lines and call_parameters
1946 -- Call_parameters(2) transalates as
1947 -- -1 => Failure
1948 -- 0 => Success, but there are more rows to be fetched
1949 -- 2 => Success, no more rows to fetch
1950 -- Call_parameters(1) is the size
1951 --
1952 -- Notes
1953 --
1954
1955 PROCEDURE Get_Non_Ship_Lines(
1956 line_id OUT intTabTyp,
1957 header_id OUT intTabTyp,
1958 line_detail_id OUT intTabTyp,
1959 org_id OUT intTabTyp,
1960 call_parameters IN OUT intTabTyp
1961 ) IS
1962
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;
1972 RETURN;
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;
1982 RETURN;
1983 END IF;
1984 END IF;
1985
1986 -- Clear the table and initialize table index
1987 IF release_table.count <> 0 THEN
1988 call_parameters.delete;
1989 release_table.delete;
1990 current_line := 1;
1991 END IF;
1992
1993 -- If called after the first time, place the last row fetched in previous
1994 -- call as the first row, since it was not returned in the previous call
1995 IF first_line.header_id <> -1 THEN
1996 release_table(current_line) := first_line;
1997 current_line := current_line + 1;
1998 END IF;
1999
2000 LOOP
2001 IF current_line < MAX_LINES THEN
2002 -- Fetch lines from the non-shippable cursor
2003
2004 IF DBMS_SQL.Fetch_Rows(ns_cursor) > 0 THEN
2005 Map_Col_Value('n');
2006 Insert_RL_Row('n');
2007 cs := SUCCESS;
2008 ELSE
2009 DBMS_SQL.Close_Cursor(ns_cursor);
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');
2019 EXIT;
2020 END IF;
2021 ELSE
2022 first_line := release_table(current_line - 1);
2023 release_table.delete(current_line - 1);
2024 current_line := current_line - 1;
2025 ns_lines_refetch_mode := TRUE;
2026 EXIT;
2027 END IF;
2028 END LOOP;
2029
2030 -- Setup return values
2031 FOR i IN 1..release_table.count LOOP
2032 line_id(i) := release_table(i).line_id;
2033 header_id(i) := release_table(i).header_id;
2034 line_detail_id(i) := release_table(i).line_detail_id;
2035 org_id(i) := release_table(i).org_id;
2036 END LOOP;
2037 call_parameters(1) := release_table.count;
2038 IF cs = DONE THEN
2039 call_parameters(2) := DONE;
2040 ELSE
2041 call_parameters(2) := SUCCESS;
2042 END IF;
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
2052
2053 --
2054 -- Name
2055 -- PROCEDURE Close_Cursors
2056 --
2057 -- Purpose
2058 -- This procedure closes all the cursors that may have been
2059 -- opened to process pick release eligible lines.
2060 --
2061 -- Arguments
2062 --
2063 -- Notes
2064 --
2065
2066 PROCEDURE Close_Cursors IS
2067
2068 BEGIN
2069
2070 IF DBMS_SQL.Is_Open(u_cursor) THEN
2071 DBMS_SQL.Close_Cursor(u_cursor);
2072 END IF;
2073 IF DBMS_SQL.Is_Open(b_cursor) THEN
2074 DBMS_SQL.Close_Cursor(b_cursor);
2075 END IF;
2076 IF DBMS_SQL.Is_Open(s_cursor) THEN
2077 DBMS_SQL.Close_Cursor(s_cursor);
2078 END IF;
2079 IF DBMS_SQL.Is_Open(ns_cursor) THEN
2080 DBMS_SQL.Close_Cursor(ns_cursor);
2081 END IF;
2082 IF DBMS_SQL.Is_Open(sreq_cursor) THEN
2083 DBMS_SQL.Close_Cursor(sreq_cursor);
2084 END IF;
2085
2086 END Close_Cursors;
2087
2088 END WSH_PR_PICKING_ROWS;