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