[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_GEN_PKGS
Source
1 PACKAGE BODY wms_rule_gen_pkgs AS
2 /* $Header: WMSGNPKB.pls 120.1 2006/10/24 23:56:36 grao noship $ */
3 --
4 g_build_package_row number;
5 g_build_package_tbl DBMS_SQL.VARCHAR2S;
6
7 g_tbl_pkg_body tbl_long_type;
8 g_tbl_pkg_body_f tbl_long_type;
9 g_tbl_pkg_body_c tbl_long_type;
10 g_tbl_pkg_body_f_avail tbl_long_type;
11
12 g_owner VARCHAR2(30);
13 -- ============================================================
14 -- InitBuildPackage
15 -- Called from GenerateRulePackage. Initializes
16 -- the global variables needed to dynamically build the
17 -- rule package.
18 -- ============================================================
19 PROCEDURE InitBuildPackage IS
20
21 BEGIN
22 g_build_package_row := 0;
23 g_build_package_tbl.delete;
24 END InitBuildPackage;
25 -- ================================
26 -- Bug # 2729877 / grao
27 -- This function is to be used for getting the number of rules for the given type
28 -- -- bug # 3407019 -- Added enable_flag = 'Y'
29
30 Function get_rule_count( p_type_code in NUMBER ) return number is
31 l_rule_count number := 0;
32 begin
33 select count(rule_id) into l_rule_count
34 from wms_rules_b
35 where type_code = p_type_code
36 and enabled_flag = 'Y';
37
38 return l_rule_count;
39 end get_rule_count;
40
41
42
43 -- ==========================================================
44 -- Build Package
45 -- This API takes a VARCHAR of undetermined length
46 -- and breaks it up into varchars of length 255. These
47 -- smaller strings are stored in the g_build_package_tbl,
48 -- and compose the sql statement to create the
49 -- Rules package.
50 -- ==========================================================
51 PROCEDURE BuildPackage(
52 p_package_string IN LONG)
53
54 IS
55 l_cur_start NUMBER;
56 l_package_length NUMBER;
57 l_num_chars NUMBER := 255;
58 l_row NUMBER;
59
60 BEGIN
61 --debug
62 if inv_pp_debug.is_debug_mode then
63 inv_pp_debug.send_long_to_pipe('Build_Package(). '|| p_package_string);
64 end if;
65
66 l_cur_start := 1;
67 -- get last filled row of table
68 l_row := g_build_package_row;
69 l_package_length := length(p_package_string);
70 -- return if string is null
71 IF l_package_length IS NULL or l_package_length = 0 THEN
72 return;
73 END IF;
74
75 --Loop through string, reading off l_num_chars bytes at a time
76 LOOP
77 --When at end of varchar, exit loop;
78 EXIT WHEN l_cur_start > l_package_length;
79 l_row := l_row + 1;
80
81 --Get substring from package_string
82 g_build_package_tbl(l_row) := substr(p_package_string,
83 l_cur_start,
84 l_num_chars);
85 --Call buil package to add row
86 -- We may need to call this API for AOL standards.
87 --ad_ddl.build_package(l_cur_string, l_row);
88
89 --increment pointers
90 l_cur_start := l_cur_start + l_num_chars;
91 IF l_cur_start + l_num_chars > l_package_length THEN
92 l_num_chars := l_package_length - l_cur_start + 1;
93 END IF;
94 END LOOP;
95
96 g_build_package_row := l_row;
97
98 END BuildPackage;
99 --=================================================================
100 --CreatePackage
101 -- This API calls dynamic SQL to build the package
102 -- currently sitting in the g_build_package_tbl.
103 -- p_package_body = TRUE if the package to be created is a body
104 --=================================================================
105 PROCEDURE CreatePackage(
106 x_return_status OUT NOCOPY VARCHAR2
107 ,p_package_name IN VARCHAR2
108 ,p_package_body IN BOOLEAN
109 ) IS
110 l_schema VARCHAR2(30);
111 l_status VARCHAR2(1);
112 l_industry VARCHAR2(1);
113 l_comp_error VARCHAR2(40);
114 l_return BOOLEAN;
115 l_cursor INTEGER;
116 l_error NUMBER;
117 l_dummy NUMBER;
118 CURSOR c_package_status IS
119 SELECT 1
120 FROM dual
121 WHERE exists(
122 SELECT status
123 FROM all_objects
124 WHERE object_name = p_package_name
125 AND object_type = 'PACKAGE'
126 AND status <> 'VALID'
127 AND owner = g_owner);
128
129 CURSOR c_package_body_status IS
130 SELECT 1
131 FROM dual
132 WHERE exists(
133 SELECT status
134 FROM all_objects
135 WHERE object_name = p_package_name
136 AND object_type = 'PACKAGE BODY'
137 AND status <> 'VALID'
138 AND owner = g_owner);
139 BEGIN
140
141 x_return_status := fnd_api.g_ret_sts_unexp_error;
142
143 --open cursor
144 l_cursor := dbms_sql.open_cursor;
145 --parse cursor
146 dbms_sql.parse(l_cursor,
147 g_build_package_tbl,
148 1,
149 g_build_package_row,
150 FALSE,
151 dbms_sql.native);
152
153 --l_dummy := dbms_sql.execute(l_cursor);
154 --close cursor
155 dbms_sql.close_cursor(l_cursor);
156
157 --Check status, return error if package that was created
158 -- is invalid
159
160
161
162 IF p_package_body THEN
163 OPEN c_package_body_status;
164 FETCH c_package_body_status INTO l_error;
165 IF c_package_body_status%FOUND THEN
166 x_return_status := fnd_api.g_ret_sts_unexp_error;
167 ELSE
168 x_return_status := fnd_api.g_ret_sts_success;
169 END IF;
170 CLOSE c_package_body_status;
171 ELSE
172 OPEN c_package_status;
173 FETCH c_package_status INTO l_error;
174 IF c_package_status%FOUND THEN
175 x_return_status := fnd_api.g_ret_sts_unexp_error;
176 ELSE
177 x_return_status := fnd_api.g_ret_sts_success;
178 END IF;
179 CLOSE c_package_status;
180 END IF;
181 EXCEPTION
182 WHEN OTHERS THEN
183 x_return_status := fnd_api.g_ret_sts_unexp_error;
184 END CreatePackage;
185
186
187 PROCEDURE GenerateSQL
188 (p_api_version IN NUMBER ,
189 p_init_msg_list IN VARCHAR2 ,
190 p_validation_level IN NUMBER ,
191 x_return_status OUT NOCOPY VARCHAR2 ,
192 x_msg_count OUT NOCOPY NUMBER ,
193 x_msg_data OUT NOCOPY VARCHAR2 ,
194 p_type_code IN NUMBER ,
195 p_type_name IN VARCHAR2 ,
196 p_counter IN NUMBER ,
197 p_counter_str IN VARCHAR2 ,
198 p_pkg_type IN VARCHAR2
199 ) IS
200
201 PRAGMA AUTONOMOUS_TRANSACTION;
202
203 -- API standard variables
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_api_name CONSTANT VARCHAR2(30) := 'GenerateRuleExecPkgs';
206 --
207 -- Static value
208 PUT_TYPE NUMBER := 1;
209 PICK_TYPE NUMBER := 2;
210 TASK_TYPE NUMBER := 3;
211 LABEL_TYPE NUMBER := 4;
212 CG_TYPE NUMBER := 5;
213 OP_TYPE NUMBER := 7;
214 PICK_PKG_NAME VARCHAR2(255) := 'WMS_RULE_PICK_PKG';
215 PUT_PKG_NAME VARCHAR2(255) := 'WMS_RULE_PUT_PKG';
216 TASK_PKG_NAME VARCHAR2(255) := 'WMS_RULE_TASK_PKG';
217 LABEL_PKG_NAME VARCHAR2(255) := 'WMS_RULE_LABEL_PKG';
218 CG_PKG_NAME VARCHAR2(255) := 'WMS_RULE_CG_PKG';
219 OP_PKG_NAME VARCHAR2(255) := 'WMS_RULE_OP_PKG';
220 -- variables needed for dynamic SQL
221 l_cursor INTEGER;
222 l_rows INTEGER;
223 l_rule_id wms_rules_b.rule_id%TYPE := NULL;
224 l_type_code wms_rules_b.TYPE_CODE%TYPE := p_type_code;
225 l_package_name VARCHAR2(255);
226 l_if_cl VARCHAR2(10);
227 l_endif_cl VARCHAR2(255);
228 l_cur_ocl VARCHAR2(10) := '''' ||'OPEN' || '''';
229 l_cur_fcl VARCHAR2(10) := '''' ||'FETCH' || '''';
230 l_cur_ccl VARCHAR2(10) := '''' ||'CLOSE' || '''';
231 l_pkg_stmt_o long;
232 l_pkg_stmt_f long;
233 l_pkg_stmt_c long;
234 l_pkg_stmt_f_avail long;
235 l_pkg_body_o long;
236 l_pkg_body_f long;
237 l_pkg_body_f_avail long;
238 l_pkg_body_c long;
239 l_pkg_hdr_B long;
240 l_pkg_hdr_S long;
241 l_pkg_body long;
242 l_pkg_end long;
243 l_pkg_fetch_hdr long;
244 l_pkg_close_hdr long;
245 l_pkg_open_end long;
246 l_pkg_fetch_end long;
247 l_pkg_close_end long;
248 l_pkg_fetch_avail_hdr long;
249 l_pkg_fetch_avail_end long;
250 l_counter NUMBER := p_counter;
251 l_type_name VARCHAR2(40) := p_type_name;
252 l_counter_str VARCHAR2(40) := p_counter_str;
253
254 l_new_ctr NUMBER;
255
256 TYPE Rule_TabTyp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
257 i NUMBER := 0;
258 j NUMBER := 0;
259 rule_cnt NUMBER := 0;
260
261 RuleTab Rule_TabTyp;
262 -- bug # 3407019 not required
263 -- l_rule_count Number;
264
265 -- cursor for validation of input parameters and pre-requisites
266 CURSOR rule_curs IS
267 /*SELECT rule_id
268 FROM WMS_RULES_B
269 WHERE enabled_flag = 'Y'
270 AND type_code = p_type_code
271 ORDER BY rule_weight DESC, creation_date;*/
272 -- modified by grao to exclude the invalid rules but status enabled
273 SELECT rl.rule_id
274 FROM WMS_RULES_B rl , all_objects obj
275 WHERE rl.enabled_flag = 'Y'
276 AND rl.type_code = p_type_code
277 AND (obj.object_name = 'WMS_RULE_'|| to_char(rl.rule_id)
278 AND obj.object_type = 'PACKAGE BODY'
279 AND obj.owner = g_owner AND obj.status = 'VALID' )
280 ORDER BY rl.rule_weight DESC, rl.creation_date;
281 -- -----------------------------
282 -- Defined SQL text
283 -- -----------------------------
284 --
285 -- ------------------------------
286 -- LABEL section
287 -- ------------------------------
288 l_label_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_LABEL_PKG'||l_counter_str||' AS
289
290 ---- For Opening the Label CURSOR ----
291 ----
292 PROCEDURE EXECUTE_LABEL_RULE(
293 p_rule_id IN NUMBER,
294 p_label_request_id IN NUMBER,
295 x_return_status OUT NOCOPY NUMBER);
296
297
298 END WMS_RULE_LABEL_PKG'||l_counter_str||';
299 --COMMIT;
300 --EXIT;
301
302
303 ';
304
305 l_label_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_LABEL_PKG'||l_counter_str||' AS
306
307 ---- For Opening the Label CURSOR ----
308 ----
309 PROCEDURE EXECUTE_LABEL_RULE(
310 p_rule_id IN NUMBER,
311 p_label_request_id IN NUMBER,
312 x_return_status OUT NOCOPY NUMBER) is
313
314 BEGIN
315 ';
316
317 l_label_open_end long :='
318 END EXECUTE_LABEL_RULE;';
319
320 l_label_fetch_hdr long := '';
321
322 l_label_fetch_end long :='';
323
324
325 l_label_close_hdr long :='';
326
327 l_label_close_end long :='
328 END WMS_RULE_LABEL_PKG'||l_counter_str||';
329 --COMMIT;
330 --EXIT;
331 ';
332
333 -- ----------------------
334 -- Task Section
335 -- ----------------------
336 l_task_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_TASK_PKG'||l_counter_str||' AS
337
338
339 ---- For Opening the Task CURSOR ----
340 ----
341 PROCEDURE EXECUTE_TASK_RULE(
342 p_rule_id IN NUMBER,
343 p_transaction_type_id IN NUMBER,
344 x_return_status OUT NOCOPY NUMBER);
345
346 END WMS_RULE_TASK_PKG'||l_counter_str||';
347 --COMMIT;
348 --EXIT;
349
350
351 ';
352
353 l_task_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_TASK_PKG'||l_counter_str||' AS
354
355 ---- For Opening the Task CURSOR ----
356 ----
357 PROCEDURE EXECUTE_TASK_RULE(
358 p_rule_id IN NUMBER,
359 p_transaction_type_id IN NUMBER,
360 x_return_status OUT NOCOPY NUMBER) is
361
362 BEGIN
363 ';
364
365 l_task_open_end long :='
366 END EXECUTE_TASK_RULE;';
367
368 l_task_fetch_hdr long := '';
369
370 l_task_fetch_end long :='';
371
372 l_task_close_hdr long :='';
373
374 l_task_close_end long :='
375 END WMS_RULE_TASK_PKG'||l_counter_str||';
376 --COMMIT;
377 --EXIT;
378 ';
379
380
381 -- ----------------------
382 -- CG Section
383 -- ----------------------
384
385 l_CG_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_CG_PKG'||l_counter_str||' AS
386
387
388 ---- For Calling the Cost Group rule ----
389 ----
390
391 PROCEDURE EXECUTE_CG_RULE(
392 p_rule_id IN NUMBER,
393 p_line_id IN NUMBER,
394 x_result OUT NOCOPY NUMBER);
395
396 END WMS_RULE_CG_PKG'||l_counter_str||';
397 --COMMIT;
398 --EXIT;
399
400
401 ';
402
403
404 l_CG_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_CG_PKG'||l_counter_str||' AS
405
406 ---- Calling the CostGroup rule----
407 ----
408
409 PROCEDURE EXECUTE_CG_RULE(
410 p_rule_id IN NUMBER,
411 p_line_id IN NUMBER,
412 x_result OUT NOCOPY NUMBER) is
413
414 BEGIN
415 ';
416 l_CG_open_end long :='
417 END EXECUTE_CG_RULE;';
418
419 l_CG_fetch_hdr long := '';
420
421 l_CG_fetch_end long :='';
422
423 l_CG_close_hdr long :='';
424
425 l_CG_close_end long :='
426 END WMS_RULE_CG_PKG'||l_counter_str||';
427 --COMMIT;
428 --EXIT;
429 ';
430
431 -- ----------------------
432 -- Put Section
433 -- ----------------------
434
435 l_put_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_PUT_PKG'||l_counter_str||' AS
436
437
438 ---- For Opening the Putaway CURSOR ----
439 ----
440 PROCEDURE EXECUTE_OPEN_RULE(
441 p_cursor IN OUT NOCOPY WMS_RULE_PVT.cv_put_type,
442 p_rule_id IN NUMBER,
443 p_organization_id IN NUMBER,
444 p_inventory_item_id IN NUMBER,
445 p_transaction_type_id IN NUMBER,
446 p_subinventory_code IN VARCHAR2,
447 p_locator_id IN NUMBER,
448 p_pp_transaction_temp_id IN NUMBER,
452 p_task_id IN NUMBER,
449 p_restrict_subs_code IN NUMBER,
450 p_restrict_locs_code IN NUMBER,
451 p_project_id IN NUMBER,
453 x_result OUT NOCOPY NUMBER);
454
455 PROCEDURE EXECUTE_FETCH_RULE (
456 p_cursor IN WMS_RULE_PVT.cv_put_type,
457 p_rule_id IN NUMBER,
458 x_subinventory_code OUT NOCOPY VARCHAR2,
459 x_locator_id OUT NOCOPY NUMBER,
460 x_project_id OUT NOCOPY NUMBER,
461 x_task_id OUT NOCOPY NUMBER,
462 x_return_status OUT NOCOPY NUMBER);
463
464 PROCEDURE EXECUTE_CLOSE_RULE (p_rule_id IN NUMBER,
465 p_cursor IN WMS_RULE_PVT.cv_put_type) ;
466
467 END WMS_RULE_PUT_PKG'||l_counter_str||';
468 --COMMIT;
469 --EXIT;
470
471
472 ';
473
474 l_put_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_PUT_PKG'||l_counter_str||' AS
475
476 ---- For Opening the Putaway CURSOR ----
477 ----
478 PROCEDURE EXECUTE_OPEN_RULE(
479 p_cursor IN OUT NOCOPY WMS_RULE_PVT.cv_put_type,
480 p_rule_id IN NUMBER,
481 p_organization_id IN NUMBER,
482 p_inventory_item_id IN NUMBER,
483 p_transaction_type_id IN NUMBER,
484 p_subinventory_code IN VARCHAR2,
485 p_locator_id IN NUMBER,
486 p_pp_transaction_temp_id IN NUMBER,
487 p_restrict_subs_code IN NUMBER,
488 p_restrict_locs_code IN NUMBER,
489 p_project_id IN NUMBER,
490 p_task_id IN NUMBER,
491 x_result OUT NOCOPY NUMBER) is
492
493 BEGIN
494 ';
495
496 l_put_open_end long :='
497 END EXECUTE_OPEN_RULE;';
498
499 l_put_fetch_hdr long := '
500
501 PROCEDURE EXECUTE_FETCH_RULE (
502 p_cursor IN WMS_RULE_PVT.cv_put_type,
503 p_rule_id IN NUMBER,
504 x_subinventory_code OUT NOCOPY VARCHAR2,
505 x_locator_id OUT NOCOPY NUMBER,
506 x_project_id OUT NOCOPY NUMBER,
507 x_task_id OUT NOCOPY NUMBER,
508 x_return_status OUT NOCOPY NUMBER) is
509
510 BEGIN
511 ';
512
513 l_put_fetch_end long :='
514
515 END EXECUTE_FETCH_RULE;';
516
517
518 l_put_close_hdr long :='
519
520 PROCEDURE EXECUTE_CLOSE_RULE (p_rule_id IN NUMBER,
521 p_cursor IN WMS_RULE_PVT.cv_put_type) is
522 BEGIN
523 ';
524
525 l_put_close_end long :='
526 END EXECUTE_CLOSE_RULE;
527 END WMS_RULE_PUT_PKG'||l_counter_str||';
528 --COMMIT;
529 --EXIT;
530 ';
531
532 -- ----------------------
533 -- Pick Section
534 -- ----------------------
535 l_pick_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_PICK_PKG'||l_counter_str||' AS
536
537
538 ---- For Opening the PICK CURSOR ----
539 ----
540 PROCEDURE EXECUTE_OPEN_RULE(
541 p_cursor IN OUT NOCOPY WMS_RULE_PVT.Cv_pick_type,
542 p_rule_id IN NUMBER,
543 p_organization_id IN NUMBER,
544 p_inventory_item_id IN NUMBER,
545 p_transaction_type_id IN NUMBER,
546 p_revision IN VARCHAR2,
547 p_lot_number IN VARCHAR2,
548 p_subinventory_code IN VARCHAR2,
549 p_locator_id IN NUMBER,
550 p_cost_group_id IN NUMBER,
551 p_pp_transaction_temp_id IN NUMBER,
552 p_serial_controlled IN NUMBER,
553 p_detail_serial IN NUMBER,
554 p_detail_any_serial IN NUMBER,
555 p_from_serial_number IN VARCHAR2,
556 p_to_serial_number IN VARCHAR2,
557 p_unit_number IN VARCHAR2,
558 p_lpn_id IN NUMBER,
559 p_project_id IN NUMBER,
560 p_task_id IN NUMBER,
561 x_result OUT NOCOPY NUMBER);
562
563 PROCEDURE EXECUTE_FETCH_RULE (
564 p_cursor IN WMS_RULE_PVT.Cv_pick_type,
565 p_rule_id IN NUMBER,
566 x_revision OUT NOCOPY VARCHAR2,
567 x_lot_number OUT NOCOPY VARCHAR2,
568 x_lot_expiration_date OUT NOCOPY DATE,
569 x_subinventory_code OUT NOCOPY VARCHAR2,
570 x_locator_id OUT NOCOPY NUMBER,
571 x_cost_group_id OUT NOCOPY NUMBER,
572 x_uom_code OUT NOCOPY VARCHAR2,
573 x_lpn_id OUT NOCOPY NUMBER,
574 x_serial_number OUT NOCOPY VARCHAR2,
575 x_possible_quantity OUT NOCOPY NUMBER,
576 x_sec_possible_quantity OUT NOCOPY NUMBER,
577 x_grade_code OUT NOCOPY VARCHAR2,
578 x_consist_string OUT NOCOPY VARCHAR2,
582 PROCEDURE EXECUTE_FETCH_AVAILABLE_INV (
579 x_order_by_string OUT NOCOPY VARCHAR2,
580 x_return_status OUT NOCOPY NUMBER);
581
583 p_cursor IN WMS_RULE_PVT.Cv_pick_type,
584 p_rule_id IN NUMBER,
585 x_return_status OUT NOCOPY NUMBER
586 );
587
588 PROCEDURE EXECUTE_CLOSE_RULE (p_rule_id IN NUMBER ,
589 p_cursor IN WMS_RULE_PVT.Cv_pick_type) ;
590
591 END WMS_RULE_PICK_PKG'||l_counter_str||';
592 --COMMIT;
593 --EXIT;
594
595
596 ';
597
598 l_pick_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_PICK_PKG'||l_counter_str||' AS
599
600 ---- For Opening the Pick CURSOR ----
601 ----
602 PROCEDURE EXECUTE_OPEN_RULE(
603 p_cursor IN OUT NOCOPY WMS_RULE_PVT.Cv_pick_type,
604 p_rule_id IN NUMBER,
605 p_organization_id IN NUMBER,
606 p_inventory_item_id IN NUMBER,
607 p_transaction_type_id IN NUMBER,
608 p_revision IN VARCHAR2,
609 p_lot_number IN VARCHAR2,
610 p_subinventory_code IN VARCHAR2,
611 p_locator_id IN NUMBER,
612 p_cost_group_id IN NUMBER,
613 p_pp_transaction_temp_id IN NUMBER,
614 p_serial_controlled IN NUMBER,
615 p_detail_serial IN NUMBER,
616 p_detail_any_serial IN NUMBER,
617 p_from_serial_number IN VARCHAR2,
618 p_to_serial_number IN VARCHAR2,
619 p_unit_number IN VARCHAR2,
620 p_lpn_id IN NUMBER,
621 p_project_id IN NUMBER,
622 p_task_id IN NUMBER,
623 x_result OUT NOCOPY NUMBER) is
624
625 BEGIN
626 ';
627
628 l_pick_open_end long := '
629 END EXECUTE_OPEN_RULE;';
630
631 l_pick_fetch_hdr long := '
632
633 PROCEDURE EXECUTE_FETCH_RULE (
634 p_cursor IN WMS_RULE_PVT.Cv_pick_type,
635 p_rule_id IN NUMBER,
636 x_revision OUT NOCOPY VARCHAR2,
637 x_lot_number OUT NOCOPY VARCHAR2,
638 x_lot_expiration_date OUT NOCOPY DATE,
639 x_subinventory_code OUT NOCOPY VARCHAR2,
640 x_locator_id OUT NOCOPY NUMBER,
641 x_cost_group_id OUT NOCOPY NUMBER,
642 x_uom_code OUT NOCOPY VARCHAR2,
643 x_lpn_id OUT NOCOPY NUMBER,
644 x_serial_number OUT NOCOPY VARCHAR2,
645 x_possible_quantity OUT NOCOPY NUMBER,
646 x_sec_possible_quantity OUT NOCOPY NUMBER,
647 x_grade_code OUT NOCOPY VARCHAR2,
648 x_consist_string OUT NOCOPY VARCHAR2,
649 x_order_by_string OUT NOCOPY VARCHAR2,
650 x_return_status OUT NOCOPY NUMBER) is
651
652 BEGIN
653 ';
654
655
656 l_pick_fetch_end long :='
657
658 END EXECUTE_FETCH_RULE;';
659
660 l_pick_fetch_avail_hdr long := '
661
662 PROCEDURE EXECUTE_FETCH_AVAILABLE_INV (
663 p_cursor IN WMS_RULE_PVT.Cv_pick_type,
664 p_rule_id IN NUMBER,
665 x_return_status OUT NOCOPY NUMBER) is
666
667 BEGIN
668 ';
669
670
671 l_pick_fetch_avail_end long :='
672
673 END EXECUTE_FETCH_AVAILABLE_INV;';
674
675
676 l_pick_close_hdr long :='
677
678 PROCEDURE EXECUTE_CLOSE_RULE (p_rule_id IN NUMBER,
679 p_cursor IN WMS_RULE_PVT.Cv_pick_type) is
680 BEGIN
681 ';
682
683 l_pick_close_end long :='
684 END EXECUTE_CLOSE_RULE;
685 END WMS_RULE_PICK_PKG'||l_counter_str||';
686 --COMMIT;
687 --EXIT;
688 ';
689
690 -- ----------------------
691 -- Operation Plan Section
692 -- ----------------------
693 l_op_hdr_S long := 'CREATE OR REPLACE PACKAGE WMS_RULE_OP_PKG'||l_counter_str||' AS
694
695
696 ---- For Opening the Operartion Plan CURSOR ----
697 ----
698 PROCEDURE EXECUTE_OP_RULE(
699 p_rule_id IN NUMBER,
700 p_transaction_type_id IN NUMBER,
701 x_return_status OUT NOCOPY NUMBER);
702
703 END WMS_RULE_OP_PKG'||l_counter_str||';
704 --COMMIT;
705 --EXIT;
706
707
708 ';
709
710 l_OP_hdr_B long := 'CREATE OR REPLACE PACKAGE BODY WMS_RULE_OP_PKG'||l_counter_str||' AS
711
712 ---- For Opening the Operartion Plan CURSOR ----
713 ----
714 PROCEDURE EXECUTE_OP_RULE(
715 p_rule_id IN NUMBER,
716 p_transaction_type_id IN NUMBER,
717 x_return_status OUT NOCOPY NUMBER) is
718
719 BEGIN
720 ';
721
722 l_op_open_end long :='
723 END EXECUTE_OP_RULE;';
724
725 l_op_fetch_hdr long := '';
726
730
727 l_op_fetch_end long :='';
728
729 l_op_close_hdr long :='';
731 l_op_close_end long :='
732 END WMS_RULE_op_PKG'||l_counter_str||';
733 --COMMIT;
734 --EXIT;
735 ';
736 ---------------------------------
737 BEGIN
738
739 --debug
740 if inv_pp_debug.is_debug_mode then
741 inv_pp_debug.send_long_to_pipe('Begin GenerateSQL()...p_type_code: ' ||
742 p_type_code);
743 inv_pp_debug.send_long_to_pipe('Begin GenerateSQL : Package Code Type ' || p_pkg_type);
744 end if;
745
746 l_pkg_stmt_o := NULL;
747 l_pkg_stmt_f := NULL;
748 l_pkg_stmt_c := NULL;
749 l_pkg_body_o :=' ';
750 l_pkg_body_f :=' ';
751 l_pkg_body_c :=' ';
752 l_pkg_stmt_f_avail := NULL;
753 l_pkg_body_f_avail :=' ';
754 -- bug #3407019
755 --l_rule_count := 0;
756
757
758 OPEN rule_curs;
759 FETCH rule_curs BULK COLLECT INTO ruletab;
760 CLOSE rule_curs;
761
762 gmi_reservation_util.println('GENPK , l_counter_str '||l_counter_str);
763 -- LG convergence add my package here
764 IF (l_type_code = PICK_TYPE) THEN
765 gmi_reservation_util.println('GENPK , picking gen');
766 l_package_name := 'WMS_RULE_PICK_PKG';
767 l_pkg_hdr_S := l_pick_hdr_S;
768 l_pkg_hdr_B := l_pick_hdr_B;
769 l_pkg_open_end := l_pick_open_end;
770 l_pkg_fetch_hdr := l_pick_fetch_hdr;
771 l_pkg_close_hdr := l_pick_close_hdr;
772 l_pkg_fetch_end := l_pick_fetch_end;
773 l_pkg_close_end := l_pick_close_end;
774
775
776 l_package_name := l_package_name || l_counter_str;
777
778 if inv_pp_debug.is_debug_mode then
779 inv_pp_debug.send_long_to_pipe(' Package Name + counter ' ||
780 l_package_name);
781 end if;
782
783
784 FOR i in 1..ruletab.count loop
785 IF (i > 1) THEN
786 l_if_cl := ' ELSIF ';
787 ELSE l_if_cl := ' IF ';
788 END IF;
789 l_rule_id := ruletab(i) ;
790
791 IF ( rule_cnt > 48) THEN
792 j := j + 1;
793 g_tbl_pkg_body(j) := l_pkg_body_o;
794 g_tbl_pkg_body_f(j) := l_pkg_body_f;
795 g_tbl_pkg_body_f_avail(j) := l_pkg_body_f_avail;
796 g_tbl_pkg_body_c(j) := l_pkg_body_c;
797 l_pkg_body_o := ' ';
798 l_pkg_body_f := ' ';
799 l_pkg_body_f_avail := ' ';
800 l_pkg_body_c := ' ';
801 rule_cnt := 0;
802
803 END IF;
804
805 rule_cnt := rule_cnt + 1;
806 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
807 ' p_rule_id = ' || l_rule_id || ' THEN
808 WMS_RULE_' || l_rule_id || '.open_curs(
809 p_cursor,
810 p_organization_id,
811 p_inventory_item_id,
812 p_transaction_type_id,
813 p_revision,
814 p_lot_number,
815 p_subinventory_code,
816 p_locator_id,
817 p_cost_group_id,
818 p_pp_transaction_temp_id,
819 p_serial_controlled,
820 p_detail_serial,
821 p_detail_any_serial,
822 p_from_serial_number,
823 p_to_serial_number,
824 p_unit_number,
825 p_lpn_id,
826 p_project_id,
827 p_task_id,
828 x_result );
829 ';
830
831 l_pkg_body_f := l_pkg_body_f || l_if_cl ||
832 ' p_rule_id = ' || l_rule_id || ' THEN
833 WMS_RULE_' || l_rule_id || '.fetch_one_row(
834 p_cursor,
835 x_revision,
836 x_lot_number,
837 x_lot_expiration_date,
838 x_subinventory_code,
839 x_locator_id,
840 x_cost_group_id,
841 x_uom_code,
842 x_lpn_id,
843 x_serial_number,
844 x_possible_quantity,
845 x_sec_possible_quantity,
846 x_grade_code,
847 x_consist_string,
848 x_order_by_string,
849 x_return_status );
850 ';
851 gmi_reservation_util.println('GENPK , f_avail gen '||l_rule_id);
852 l_pkg_body_f_avail := l_pkg_body_f_avail || l_if_cl ||
853 ' p_rule_id = ' || l_rule_id || ' THEN
854 WMS_RULE_' || l_rule_id || '.fetch_available_rows(
855 p_cursor,
856 x_return_status );
857 ';
858 -- end if;
859 -- gmi_reservation_util.println('GENPK , l_body_f_avail '||l_pkg_body_f_avail);
860
861
862 l_pkg_body_c := l_pkg_body_c || l_if_cl ||
863 ' p_rule_id = ' || l_rule_id || ' THEN
864 WMS_RULE_' || l_rule_id || '.close_curs(p_cursor);
865 ';
866
867 END LOOP;
868
869 -- END of LG convergence
870
871 ELSIF (l_type_code = PUT_TYPE ) THEN
872 l_package_name := 'WMS_RULE_PUT_PKG';
873 l_pkg_hdr_S := l_put_hdr_S;
874 l_pkg_hdr_B := l_put_hdr_B;
875 l_pkg_open_end := l_put_open_end;
879 l_pkg_close_end := l_put_close_end;
876 l_pkg_fetch_hdr := l_put_fetch_hdr;
877 l_pkg_close_hdr := l_put_close_hdr;
878 l_pkg_fetch_end := l_put_fetch_end;
880
881 l_package_name := l_package_name || l_counter_str;
882
883 FOR i in 1..ruletab.count loop
884 IF (i > 1) THEN
885 l_if_cl := ' ELSIF ';
886 ELSE l_if_cl := ' IF ';
887 END IF;
888 l_rule_id := ruletab(i) ;
889
890 IF ( rule_cnt > 50) THEN
891 j := j + 1;
892 g_tbl_pkg_body(j) := l_pkg_body_o;
893 g_tbl_pkg_body_f(j) := l_pkg_body_f;
894 g_tbl_pkg_body_c(j) := l_pkg_body_c;
895 l_pkg_body_o := NULL;
896 l_pkg_body_f := NULL;
897 l_pkg_body_c := NULL;
898 rule_cnt := 0;
899
900 END IF;
901
902 rule_cnt := rule_cnt + 1;
903
904 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
905 ' p_rule_id = ' || l_rule_id || ' THEN
906 WMS_RULE_' || l_rule_id || '.open_curs(
907 p_cursor,
908 p_organization_id,
909 p_inventory_item_id,
910 p_transaction_type_id,
911 p_subinventory_code,
912 p_locator_id,
913 p_pp_transaction_temp_id,
914 p_restrict_subs_code,
915 p_restrict_locs_code,
916 p_project_id,
917 p_task_id,
918 x_result );
919 ';
920
921 l_pkg_body_f := l_pkg_body_f || l_if_cl ||
922 ' p_rule_id = ' || l_rule_id || ' THEN
923 WMS_RULE_' || l_rule_id || '.fetch_one_row(
924 p_cursor,
925 x_subinventory_code,
926 x_locator_id,
927 x_project_id,
928 x_task_id,
929 x_return_status );
930 ';
931
932 l_pkg_body_c := l_pkg_body_c || l_if_cl ||
933 ' p_rule_id = ' || l_rule_id || ' THEN
934 WMS_RULE_' || l_rule_id || '.close_curs(p_cursor);
935 ';
936
937 END LOOP;
938
939 ELSIF (l_type_code = TASK_TYPE ) THEN
940 l_package_name := 'WMS_RULE_TASK_PKG';
941 l_pkg_hdr_S := l_task_hdr_S;
942 l_pkg_hdr_B := l_task_hdr_B;
943 l_pkg_open_end := l_task_open_end;
944 l_pkg_fetch_hdr := l_task_fetch_hdr;
945 l_pkg_close_hdr := l_task_close_hdr;
946 l_pkg_fetch_end := l_task_fetch_end;
947 l_pkg_close_end := l_task_close_end;
948
949 l_package_name := l_package_name || l_counter_str;
950
951
952 FOR i in 1..ruletab.count loop
953 IF (i > 1) THEN
954 l_if_cl := ' ELSIF ';
955 ELSE l_if_cl := ' IF ';
956 END IF;
957 l_rule_id := ruletab(i) ;
958
959 IF ( rule_cnt > 100) THEN
960 j := j + 1;
961 g_tbl_pkg_body(j) := l_pkg_body_o;
962 l_pkg_body_o := NULL;
963 rule_cnt := 0;
964
965 END IF;
966
967 rule_cnt := rule_cnt + 1;
968
969 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
970 ' p_rule_id = ' || l_rule_id || ' THEN
971 WMS_RULE_' || l_rule_id || '.Get_Task(
972 p_transaction_type_id,
973 x_return_status );
974 ';
975
976 END LOOP;
977 ELSIF (l_type_code = LABEL_TYPE ) THEN
978 l_package_name := 'WMS_RULE_LABEL_PKG';
979 l_pkg_hdr_S := l_label_hdr_S;
980 l_pkg_hdr_B := l_label_hdr_B;
981 l_pkg_open_end := l_label_open_end;
982 l_pkg_fetch_hdr := l_label_fetch_hdr;
983 l_pkg_close_hdr := l_label_close_hdr;
984 l_pkg_fetch_end := l_label_fetch_end;
985 l_pkg_close_end := l_label_close_end;
986
987 l_package_name := l_package_name || l_counter_str;
988
989
990
991 FOR i in 1..ruletab.count loop
992 IF (i > 1) THEN
993 l_if_cl := ' ELSIF ';
994 ELSE l_if_cl := ' IF ';
995 END IF;
996 l_rule_id := ruletab(i) ;
997
998 IF ( rule_cnt > 100) THEN
999 j := j + 1;
1000 g_tbl_pkg_body(j) := l_pkg_body_o;
1001 l_pkg_body_o := NULL;
1002 rule_cnt := 0;
1003
1004 END IF;
1005 rule_cnt := rule_cnt + 1;
1006
1007 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
1008 ' p_rule_id = ' || l_rule_id || ' THEN
1009 WMS_RULE_' || l_rule_id || '.Get_Label_Format(
1010 p_label_request_id,
1011 x_return_status );
1012 ';
1013
1014 END LOOP;
1015
1016 ELSIF (l_type_code = CG_TYPE ) THEN
1017 l_package_name := 'WMS_RULE_CG_PKG';
1018 l_pkg_hdr_S := l_CG_hdr_S;
1019 l_pkg_hdr_B := l_CG_hdr_B;
1020 l_pkg_open_end := l_CG_open_end;
1021 l_pkg_fetch_hdr := l_CG_fetch_hdr;
1022 l_pkg_close_hdr := l_CG_close_hdr;
1023 l_pkg_fetch_end := l_CG_fetch_end;
1024 l_pkg_close_end := l_CG_close_end;
1025
1026
1027 l_package_name := l_package_name || l_counter_str;
1031 l_new_ctr := 3000;
1028
1029 --- Bug # 3812503
1030 if ruletab.count > 3000 then
1032 else
1033 l_new_ctr := ruletab.count;
1034 end if;
1035
1036 FOR i in 1..l_new_ctr loop
1037 IF (i > 1) THEN
1038 l_if_cl := ' ELSIF ';
1039 ELSE
1040 l_if_cl := ' IF ';
1041 END IF;
1042 l_rule_id := ruletab(i) ;
1043
1044 IF ( rule_cnt > 100) THEN
1045 j := j + 1;
1046 g_tbl_pkg_body(j) := l_pkg_body_o;
1047 l_pkg_body_o := NULL;
1048 rule_cnt := 0;
1049
1050 END IF;
1051
1052 rule_cnt := rule_cnt + 1;
1053
1054 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
1055 ' p_rule_id = ' || l_rule_id || ' THEN
1056 WMS_RULE_' || l_rule_id || '.Get_CostGroup(
1057 p_line_id,
1058 x_result );
1059 ';
1060
1061 END LOOP;
1062
1063 ELSIF (l_type_code = OP_TYPE ) THEN
1064 l_package_name := 'WMS_RULE_OP_PKG';
1065 l_pkg_hdr_S := l_op_hdr_S;
1066 l_pkg_hdr_B := l_op_hdr_B;
1067 l_pkg_open_end := l_op_open_end;
1068 l_pkg_fetch_hdr := l_op_fetch_hdr;
1069 l_pkg_close_hdr := l_op_close_hdr;
1070 l_pkg_fetch_end := l_op_fetch_end;
1071 l_pkg_close_end := l_op_close_end;
1072
1073 l_package_name := l_package_name || l_counter_str;
1074
1075
1076 FOR i in 1..ruletab.count loop
1077 IF (i > 1) THEN
1078 l_if_cl := ' ELSIF ';
1079 ELSE l_if_cl := ' IF ';
1080 END IF;
1081 l_rule_id := ruletab(i) ;
1082
1083
1084 IF ( rule_cnt > 100) THEN
1085 j := j + 1;
1086 g_tbl_pkg_body(j) := l_pkg_body_o;
1087 l_pkg_body_o := NULL;
1088 rule_cnt := 0;
1089
1090 END IF;
1091
1092 rule_cnt := rule_cnt + 1;
1093
1094 l_pkg_body_o := l_pkg_body_o || l_if_cl ||
1095 ' p_rule_id = ' || l_rule_id || ' THEN
1096 WMS_RULE_' || l_rule_id || '.Get_OP(
1097 p_transaction_type_id,
1098 x_return_status );
1099 ';
1100
1101 END LOOP;
1102 END IF ;
1103
1104
1105 if inv_pp_debug.is_debug_mode then
1106 inv_pp_debug.send_long_to_pipe('l_rule_id: ' ||
1107 l_rule_id);
1108 end if;
1109
1110 IF (l_rule_id IS NOT NULL) THEN
1111 --inv_pp_debug.send_long_to_pipe('l_rule_id NOT NULL ');
1112 l_endif_cl := '
1113 END IF;';
1114 --- Bug #3812503 ----
1115 IF (l_type_code = CG_TYPE and l_new_ctr = 3000 ) THEN
1116 l_endif_cl := '
1117 ELSE
1118 EXECUTE IMMEDIATE ''BEGIN WMS_RULE_''||to_char(p_rule_id)||''.Get_CostGroup( :p_line_id, :x_result ); END;'' using p_line_id , out x_result;
1119
1120 END IF;' ;
1121
1122 END IF;
1123 ELSE
1124 l_endif_cl := ' NULL;';
1125 END IF;
1126
1127 l_pkg_body_o := l_pkg_body_o || l_endif_cl || l_pkg_open_end ;
1128 --inv_pp_debug.send_long_to_pipe('l_pkg_body_o' || l_pkg_body_o);
1129
1130 --gmi_reservation_util.println('GENPK , rule_cnt '||rule_cnt);
1131 IF ( rule_cnt >= 1) THEN
1132 j := j + 1;
1133 g_tbl_pkg_body(j) := l_pkg_body_o;
1134
1135
1136 IF ( l_type_code = PICK_TYPE or l_type_code = PUT_TYPE ) then
1137 g_tbl_pkg_body_f(j) := l_pkg_body_f;
1138 g_tbl_pkg_body_c(j) := l_pkg_body_c;
1139 g_tbl_pkg_body_f_avail(j) := l_pkg_body_f_avail;
1140 ELSE
1141 g_tbl_pkg_body(j) := g_tbl_pkg_body(j) || l_pkg_close_end;
1142 END IF;
1143 END IF;
1144
1145 --inv_pp_debug.send_long_to_pipe('l_pkg_stmt_c' || l_pkg_stmt_c);
1146 if inv_pp_debug.is_debug_mode then
1147 inv_pp_debug.send_long_to_pipe('BEFORE :Begin GenerateSQL : Package Code Type ' || p_pkg_type);
1148 end if;
1149 --------------------------
1150
1151 if inv_pp_debug.is_debug_mode then
1152 inv_pp_debug.send_long_to_pipe('Begin GenerateSQL()...p_type_code: ' ||
1153 p_type_code);
1154 inv_pp_debug.send_long_to_pipe('Begin GenerateSQL : Package Code Type ' || p_pkg_type);
1155 end if;
1156
1157 IF (p_pkg_type is null or p_pkg_type = 'S') then
1158 gmi_reservation_util.println('GENPK , building spec');
1159
1160 if inv_pp_debug.is_debug_mode then
1161 inv_pp_debug.send_long_to_pipe('Inside ... Spec for creating specs');
1162 inv_pp_debug.send_long_to_pipe('INSIDE' || p_pkg_type);
1163
1164 end if;
1165
1166 -- Initialize the global variables needed to build package
1167 InitBuildPackage;
1168
1169 -- Calls buildpackage
1170 BuildPackage(l_pkg_hdr_S);
1171 --debug
1172 if inv_pp_debug.is_debug_mode then
1173 inv_pp_debug.send_long_to_pipe('Calls CreatePackage() ...');
1174 end if;
1175 --create the package spec
1176 CreatePackage(x_return_status, l_package_name, FALSE);
1177 if inv_pp_debug.is_debug_mode then
1178 inv_pp_debug.send_long_to_pipe('x_return_status : '|| x_return_status);
1182 RAISE fnd_api.g_exc_unexpected_error;
1179 end if;
1180
1181 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1183 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1184 RAISE fnd_api.g_exc_error;
1185 END IF;
1186 END IF ;
1187 --------
1188 ---------
1189
1190 if inv_pp_debug.is_debug_mode then
1191 inv_pp_debug.send_long_to_pipe('BEFORE ... Body for creating Body');
1192 inv_pp_debug.send_long_to_pipe('BEFORE' || p_pkg_type);
1193 end if;
1194
1195 IF (p_pkg_type is null or p_pkg_type = 'B') then
1196
1197 gmi_reservation_util.println('GENPK , building body');
1198
1199 if inv_pp_debug.is_debug_mode then
1200 inv_pp_debug.send_long_to_pipe('Inside ... Body for creating Body');
1201 inv_pp_debug.send_long_to_pipe('INSIDE' || p_pkg_type);
1202 end if;
1203
1204 -- ---------------------------------
1205 -- Create Package Body
1206 -- ---------------------------------
1207 --re initialize global variables
1208 InitBuildPackage;
1209
1210 if inv_pp_debug.is_debug_mode then
1211 inv_pp_debug.send_long_to_pipe('Call Build_Package() : ' ||
1212 l_pkg_hdr_B);
1213 inv_pp_debug.send_long_to_pipe('l_pkg_stmt_o : ' || l_pkg_stmt_o);
1214 inv_pp_debug.send_long_to_pipe('l_pkg_stmt_f : ' || l_pkg_stmt_f);
1215 inv_pp_debug.send_long_to_pipe('l_pkg_stmt_c : ' || l_pkg_stmt_c);
1216 end if;
1217 BuildPackage(l_pkg_hdr_B);
1218 FOR i in 1..g_tbl_pkg_body.count loop
1219 IF (g_tbl_pkg_body.EXISTS(i)) THEN
1220 BuildPackage(g_tbl_pkg_body(i));
1221 g_tbl_pkg_body(i) := NULL;
1222 END IF;
1223 END LOOP;
1224
1225 gmi_reservation_util.println('GENPK , f_count '||g_tbl_pkg_body_f.count);
1226 IF (g_tbl_pkg_body_f.count >=1 ) THEN
1227 BuildPackage(l_pkg_fetch_hdr);
1228 FOR i in 1..g_tbl_pkg_body_f.count loop
1229 IF (g_tbl_pkg_body_f.EXISTS(i)) THEN
1230 BuildPackage(g_tbl_pkg_body_f(i));
1231 g_tbl_pkg_body_f(i) := NULL;
1232 END IF;
1233
1234 END LOOP;
1235 BuildPackage(l_endif_cl || l_pkg_fetch_end);
1236 END IF;
1237
1238 -- LG convergence ADD
1239 IF (l_type_code = PICK_TYPE) THEN
1240 gmi_reservation_util.println('GENPK , f_count '||g_tbl_pkg_body_f_avail.count);
1241 IF (g_tbl_pkg_body_f_avail.count >=1 ) THEN
1242 BuildPackage(l_pick_fetch_avail_hdr);
1243 FOR i in 1..g_tbl_pkg_body_f_avail.count loop
1244 IF (g_tbl_pkg_body_f_avail.EXISTS(i)) THEN
1245 BuildPackage(g_tbl_pkg_body_f_avail(i));
1246 g_tbl_pkg_body_f_avail(i) := NULL;
1247 END IF;
1248
1249 END LOOP;
1250 BuildPackage(l_endif_cl || l_pick_fetch_avail_end);
1251 END IF;
1252 END IF;
1253 -- END LG convergence
1254
1255 IF (g_tbl_pkg_body_c.count >=1 ) THEN
1256 BuildPackage(l_pkg_close_hdr);
1257 FOR i in 1..g_tbl_pkg_body_c.count loop
1258 IF (g_tbl_pkg_body_c.EXISTS(i)) THEN
1259 BuildPackage(g_tbl_pkg_body_c(i));
1260 g_tbl_pkg_body_c(i) := NULL;
1261 END IF;
1262 END LOOP;
1263 BuildPackage(l_endif_cl || l_pkg_close_end);
1264 END IF;
1265 /*====================================================================
1266 * Deallocate the table space memory by deleting all rows from tables.
1267 *====================================================================
1268 */
1269
1270 IF (g_tbl_pkg_body.count >= 1) THEN
1271 g_tbl_pkg_body.DELETE;
1272 END IF;
1273 IF (g_tbl_pkg_body_f.count >= 1) THEN
1274 g_tbl_pkg_body_f.DELETE;
1275 END IF;
1276 IF (g_tbl_pkg_body_f_avail.count >= 1) THEN
1277 g_tbl_pkg_body_f_avail.DELETE;
1278 END IF;
1279 IF (g_tbl_pkg_body_c.count >= 1) THEN
1280 g_tbl_pkg_body_c.DELETE;
1281 END IF;
1282 /*
1283 BuildPackage(l_pkg_hdr_B);
1284 BuildPackage(l_pkg_stmt_o);
1285 BuildPackage(l_pkg_stmt_f);
1286 BuildPackage(l_pkg_stmt_c);
1287 */
1288 --debug
1289 if inv_pp_debug.is_debug_mode then
1290 inv_pp_debug.send_long_to_pipe('Call CreatePackage() for BODY ..');
1291 inv_pp_debug.send_long_to_pipe(l_package_name);
1292 end if;
1293 --create the package body
1294 CreatePackage(x_return_status, l_package_name, FALSE);
1295
1296 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1297 RAISE fnd_api.g_exc_unexpected_error;
1298 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1299 RAISE fnd_api.g_exc_error;
1300 END IF;
1301
1302 END IF;
1303 COMMIT;
1304
1305 END GenerateSQL;
1306
1307
1308 PROCEDURE GenerateRuleExecPkgs
1309 (p_api_version IN NUMBER ,
1310 p_init_msg_list IN VARCHAR2 ,
1314 x_msg_data OUT NOCOPY VARCHAR2 ,
1311 p_validation_level IN NUMBER ,
1312 x_return_status OUT NOCOPY VARCHAR2 ,
1313 x_msg_count OUT NOCOPY NUMBER ,
1315 p_pick_code IN NUMBER ,
1316 p_put_code IN NUMBER ,
1317 p_task_code IN NUMBER ,
1318 p_label_code IN NUMBER ,
1319 p_CG_code IN NUMBER ,
1320 p_OP_code IN NUMBER ,
1321 p_pkg_type IN VARCHAR2
1322 ) IS
1323
1324 -- API standard variables
1325 l_api_version CONSTANT NUMBER := 1.0;
1326 l_api_name CONSTANT VARCHAR2(30) := 'GenerateRuleExecPkgs';
1327
1328 --
1329 -- Static value
1330 PUT_TYPE NUMBER := 1;
1331 PICK_TYPE NUMBER := 2;
1332 TASK_TYPE NUMBER := 3;
1333 LABEL_TYPE NUMBER := 4;
1334 CG_TYPE NUMBER := 5;
1335 op_type NUMBER := 7;
1336 -- variables needed for dynamic SQL
1337 l_cursor INTEGER;
1338 l_rows INTEGER;
1339 l_rule_id wms_rules_b.rule_id%TYPE;
1340 l_type_code wms_rules_b.TYPE_CODE%TYPE;
1341 l_package_name VARCHAR2(255);
1342 l_pkg_stmt long;
1343 l_pkg_hdr long;
1344 l_type_name VARCHAR2(40);
1345 l_counter NUMBER := 1;
1346 l_counter_str VARCHAR2(40) :='';
1347
1348 ------
1349 l_no_pick_rules NUMBER := 0;
1350 l_no_put_rules NUMBER := 0;
1351 l_no_cg_rules NUMBER := 0;
1352 l_no_task_rules NUMBER := 0;
1353 l_no_label_rules NUMBER := 0;
1354 l_no_op_rules NUMBER := 0;
1355
1356
1357 -- cursor for validation of input parameters and pre-requisites
1358 BEGIN
1359 -- Bug #3432157
1360 select oracle_username into g_owner
1361 from fnd_oracle_userid
1362 where read_only_flag = 'U';
1363
1364 --- get number of records for each rule_type Bug # 2729877
1365 if (p_pkg_type = 'B' ) then
1366 l_no_pick_rules := get_rule_count(PICK_TYPE);
1367 l_no_put_rules := get_rule_count(PUT_TYPE);
1368 l_no_cg_rules := get_rule_count(CG_TYPE);
1369 l_no_task_rules := get_rule_count(TASK_TYPE);
1370 l_no_label_rules := get_rule_count(LABEL_TYPE);
1371 l_no_op_rules := get_rule_count(OP_TYPE);
1372 end if;
1373
1374 if inv_pp_debug.is_debug_mode then
1375 inv_pp_debug.send_long_to_pipe('p_pick_code : ' || p_pick_code);
1376 inv_pp_debug.send_long_to_pipe('p_put_code : ' || p_put_code);
1377 inv_pp_debug.send_long_to_pipe('p_task_code : ' || p_task_code);
1378 inv_pp_debug.send_long_to_pipe('p_label_code : ' || p_label_code);
1379 inv_pp_debug.send_long_to_pipe('p_CG_code : ' || p_CG_code);
1380 inv_pp_debug.send_long_to_pipe('p_OP_code : ' || p_OP_code);
1381 inv_pp_debug.send_long_to_pipe('Package Code Type ' || p_pkg_type);
1382
1383 end if;
1384 --validate P parameters
1385 IF (p_pick_code IS NULL ) AND
1386 (p_put_code IS NULL ) AND
1387 (p_task_code IS NULL ) AND
1388 (p_label_code IS NULL ) AND
1389 (p_CG_code IS NULL ) AND
1390 (p_op_code IS NULL ) THEN
1391 fnd_message.set_name('WMS', 'WMS_INVALID_TYPE');
1392 FND_MSG_PUB.ADD;
1393 RAISE fnd_api.g_exc_error;
1394
1395 END IF;
1396
1397 --- Pick
1398 ---
1399
1400 IF (p_pick_code IS NOT NULL) THEN
1401
1402 -- Set the counter and counter strings
1403 --
1404 l_type_name := 'PICK' ;
1405 l_counter := get_count_with_lock(l_type_name);
1406
1407 if l_counter = 3 then
1408 l_counter := 0;
1409 end if;
1410 l_counter := l_counter + 1;
1411 l_counter_str := to_char(l_counter);
1412
1413
1414
1415
1416 if (l_counter <> -1 ) then
1417
1418 if (l_no_pick_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1419 GenerateSQL(p_api_version => 1.0,
1420 p_init_msg_list => fnd_api.g_false ,
1421 p_validation_level => fnd_api.g_valid_level_full ,
1422 x_return_status => x_return_status,
1423 x_msg_count => x_msg_count,
1424 x_msg_data => x_msg_data ,
1425 p_type_code => p_pick_code,
1426 p_type_name => l_type_name,
1427 p_counter => l_counter ,
1428 p_counter_str => l_counter_str,
1429 p_pkg_type => p_pkg_type);
1430
1431 if (x_return_status = 'S') then
1432 update_count(l_type_name, l_counter );
1433 end if;
1434 end if;
1435
1436 end if;
1437
1438
1439 END IF;
1440
1441 --- Putaway
1442 ---
1443
1444 IF (p_put_code IS NOT NULL) THEN
1445
1446 -- Set the counter and counter strings
1447 --
1451 if l_counter = 3 then
1448 l_type_name := 'PUTAWAY' ;
1449 l_counter := get_count_with_lock(l_type_name);
1450
1452 l_counter := 0;
1453 end if;
1454 l_counter := l_counter + 1;
1455 l_counter_str := to_char(l_counter);
1456
1457 if (l_counter <> -1 ) then
1458
1459 if (l_no_put_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1460
1461 GenerateSQL(p_api_version => 1.0,
1462 p_init_msg_list => fnd_api.g_false ,
1463 p_validation_level => fnd_api.g_valid_level_full ,
1464 x_return_status => x_return_status,
1465 x_msg_count => x_msg_count,
1466 x_msg_data => x_msg_data,
1467 p_type_code => p_put_code,
1468 p_type_name => l_type_name,
1469 p_counter => l_counter ,
1470 p_counter_str => l_counter_str,
1471 p_pkg_type => p_pkg_type);
1472
1473 if (x_return_status = 'S') then
1474 update_count(l_type_name, l_counter );
1475 end if;
1476 end if;
1477 end if;
1478
1479 END IF;
1480 --- Tasks
1481 ---
1482 IF (p_task_code IS NOT NULL) THEN
1483
1484 -- Set the counter and counter strings
1485 --
1486 l_type_name := 'TASK' ;
1487 l_counter := get_count_with_lock(l_type_name);
1488
1489
1490 if l_counter = 3 then
1491 l_counter := 0;
1492 end if;
1493 l_counter := l_counter + 1;
1494 l_counter_str := to_char(l_counter);
1495
1496 if (l_counter <> -1 ) then
1497
1498 if (l_no_task_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1499
1500 GenerateSQL(p_api_version => 1.0,
1501 p_init_msg_list => fnd_api.g_false ,
1502 p_validation_level => fnd_api.g_valid_level_full ,
1503 x_return_status => x_return_status,
1504 x_msg_count => x_msg_count,
1505 x_msg_data => x_msg_data,
1506 p_type_code => p_task_code,
1507 p_type_name => l_type_name,
1508 p_counter => l_counter,
1509 p_counter_str => l_counter_str,
1510 p_pkg_type => p_pkg_type);
1511
1512 if (x_return_status = 'S') then
1513 update_count(l_type_name, l_counter );
1514 end if;
1515
1516 end if;
1517 end if;
1518 END IF;
1519
1520 --- Labels
1521 ---
1522 IF (p_label_code IS NOT NULL) THEN
1523
1524 -- Set the counter and counter strings
1525 --
1526 l_type_name := 'LABEL' ;
1527 l_counter := get_count_with_lock(l_type_name);
1528
1529
1530 if l_counter = 3 then
1531 l_counter := 0;
1532 end if;
1533
1534 l_counter := l_counter + 1;
1535 l_counter_str := to_char(l_counter);
1536
1537
1538 if (l_counter <> -1 ) then
1539
1540 if (l_no_label_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1541
1542 GenerateSQL(p_api_version => 1.0,
1543 p_init_msg_list => fnd_api.g_false ,
1544 p_validation_level => fnd_api.g_valid_level_full ,
1545 x_return_status => x_return_status,
1546 x_msg_count => x_msg_count,
1547 x_msg_data => x_msg_data,
1548 p_type_code => p_label_code,
1549 p_type_name => l_type_name,
1550 p_counter => l_counter,
1551 p_counter_str => l_counter_str,
1552 p_pkg_type => p_pkg_type);
1553
1554 if (x_return_status = 'S') then
1555 update_count(l_type_name, l_counter );
1556 end if;
1557
1558 end if;
1559 end if;
1560 END IF;
1561 -- Cost Group
1562 ---
1563
1564 IF (p_CG_code IS NOT NULL) THEN
1565
1566 -- Set the counter and counter strings
1567 --
1568 l_type_name := 'COST_GROUP' ;
1569 l_counter := get_count_with_lock(l_type_name);
1570
1571
1572
1573 if l_counter = 3 then
1574 l_counter := 0;
1575 end if;
1576 l_counter := l_counter + 1;
1577 l_counter_str := to_char(l_counter);
1578
1579 if (l_counter <> -1 ) then
1580
1581 if (l_no_cg_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1582
1583 GenerateSQL(p_api_version => 1.0,
1584 p_init_msg_list => fnd_api.g_false ,
1585 p_validation_level => fnd_api.g_valid_level_full ,
1586 x_return_status => x_return_status,
1590 p_type_name => l_type_name,
1587 x_msg_count => x_msg_count,
1588 x_msg_data => x_msg_data,
1589 p_type_code => p_CG_code,
1591 p_counter => l_counter,
1592 p_counter_str => l_counter_str,
1593 p_pkg_type => p_pkg_type);
1594
1595 if (x_return_status = 'S') then
1596 update_count(l_type_name, l_counter );
1597 end if;
1598
1599 end if;
1600 end if;
1601 END IF;
1602
1603 --- Operation plans
1604 ---
1605 IF (p_OP_code IS NOT NULL) THEN
1606 -- Set the counter and counter strings
1607 --
1608 l_type_name := 'OPERATION_PLAN' ;
1609 l_counter := get_count_with_lock(l_type_name);
1610
1611
1612 if l_counter = 3 then
1613 l_counter := 0;
1614 end if;
1615 l_counter := l_counter + 1;
1616 l_counter_str := to_char(l_counter);
1617
1618 if (l_counter <> -1 ) then
1619
1620 if (l_no_op_rules > 0 and p_pkg_type = 'B' ) or (p_pkg_type = 'S' ) then
1621
1622 GenerateSQL(p_api_version => 1.0,
1623 p_init_msg_list => fnd_api.g_false ,
1624 p_validation_level => fnd_api.g_valid_level_full ,
1625 x_return_status => x_return_status,
1626 x_msg_count => x_msg_count,
1627 x_msg_data => x_msg_data,
1628 p_type_code => p_OP_code,
1629 p_type_name => l_type_name,
1630 p_counter => l_counter,
1631 p_counter_str => l_counter_str,
1632 p_pkg_type => p_pkg_type);
1633
1634 if (x_return_status = 'S') then
1635 update_count(l_type_name, l_counter );
1636 end if;
1637
1638 end if;
1639
1640 end if;
1641
1642 END IF;
1643 COMMIT;
1644
1645
1646 EXCEPTION
1647
1648 WHEN FND_API.G_EXC_ERROR THEN
1649 x_return_status := FND_API.G_RET_STS_ERROR;
1650 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
1651
1652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1654 fnd_msg_pub.count_and_get( p_count => x_msg_count, p_data => x_msg_data );
1655 END GenerateRuleExecPkgs;
1656 ----------------
1657 FUNCTION get_count_with_lock(P_RULE_TYPE IN VARCHAR2 )
1658 RETURN NUMBER IS
1659
1660 l_package_name_count NUMBER := NULL;
1661
1662 cursor c1 is
1663 SELECT package_name_count
1664 FROM WMS_RULE_LIST_PACKAGE
1665 WHERE rule_type = p_rule_type for update;
1666
1667 BEGIN
1668
1669 IF c1%ISOPEN THEN
1670 close c1;
1671 END IF;
1672
1673 OPEN c1;
1674 fetch c1 into l_package_name_count;
1675 IF c1%NOTFOUND THEN
1676
1677 if inv_pp_debug.is_debug_mode then
1678 inv_pp_debug.send_long_to_pipe('Get count not found' || P_RULE_TYPE);
1679 end if;
1680 -- return error;
1681 return(-1);
1682 ELSIF c1%FOUND THEN
1683 if inv_pp_debug.is_debug_mode then
1684 inv_pp_debug.send_long_to_pipe('Get count '|| l_package_name_count);
1685 end if;
1686 RETURN (l_package_name_count);
1687 END IF;
1688 CLOSE c1;
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 IF c1%isopen THEN
1692 close c1;
1693 END IF;
1694
1695 if inv_pp_debug.is_debug_mode then
1696 inv_pp_debug.send_long_to_pipe('Get count Excepttion '|| SQLERRM);
1697 end if;
1698 RETURN(-1);
1699 END get_count_with_lock;
1700 ----
1701 ---
1702 FUNCTION get_count_no_lock(p_rule_type IN VARCHAR2)
1703 RETURN NUMBER IS
1704 l_package_name_count NUMBER := NULL;
1705
1706 cursor c1 is
1707 SELECT package_name_count
1708 FROM WMS_RULE_LIST_PACKAGE
1709 WHERE rule_type = p_rule_type;
1710
1711 BEGIN
1712
1713 IF c1%ISOPEN THEN
1714 close c1;
1715 END IF;
1716
1717 OPEN c1;
1718 fetch c1 into l_package_name_count;
1719 IF c1%NOTFOUND THEN
1720 -- return error;
1721 return(-1);
1722 ELSIF c1%FOUND THEN
1723 RETURN (l_package_name_count);
1724 END IF;
1725 CLOSE c1;
1726 EXCEPTION
1727 WHEN OTHERS THEN
1728 IF c1%isopen THEN
1729 close c1;
1730 END IF;
1731 RETURN(-1);
1732 END get_count_no_lock;
1733
1734 ---
1735 ---
1736 PROCEDURE update_count(
1737 p_rule_type IN VARCHAR2
1738 , p_count IN NUMBER ) IS
1739 BEGIN
1740 UPDATE WMS_RULE_LIST_PACKAGE
1741 SET PACKAGE_NAME_COUNT = p_count
1742 WHERE RULE_TYPE = p_rule_type;
1743 EXCEPTION
1744 WHEN OTHERS THEN
1745 null;
1746 END update_count;
1747
1748 ---------
1749
1750 END wms_rule_gen_pkgs;