[Home] [Help]
PACKAGE BODY: APPS.WMS_RULES_WORKBENCH_PVT
Source
1 Package BODY WMS_RULES_WORKBENCH_PVT AS
2 /* $Header: WMSRLWBB.pls 120.24.12020000.2 2012/07/24 10:00:41 azjain ship $ */
3
4 -- File : WMSRLWBS.pls
5 -- Content : WMS_RULES_WORKBENCH_PVT package spec
6 -- Description : This API is created to handle all the procedures, function variables to be used by Rules WorkBench
7
8 -- Notes :
9 -- List of Pl/SQL Tables,Functions and Procedures --------------------------
10 -- procedure send_msg_to_pipe(p_debug_mode in Boolean, p_message in VARCHAR2)
11 -- function get_return_type_name(p_org_id number ,p_rule_type_code in number, p_return_type_code in varchar2, p_return_type_id in number) return varchar2;
12 -- function get_customer_name(p_customer_id in number) return varchar2;
13 -- function get_organization_code(p_organization_id in number) return varchar2;
14 -- function get_freight_code_name(p_org_id in number ,p_freight_code in varchar2 ) return varchar2;
15 -- function get_item(p_org_id in number , p_inventory_item_id in number) return varchar2;
16 -- function get_abc_group_class_name(p_org_id in number , p_assignment_group_id in number, p_abc_class_id in number) return varchar2;
17 -- function get_category_set_name(p_org_id in number , p_category_set_id in number, p_category_id in number) return varchar2;
18 -- function get_order_type_name(p_transaction_type_id in number) return varchar2;
19 -- function get_project_name(p_project_id in number) return varchar2;
20 -- function get_task_name(p_project_id in number , p_task_id in number) return varchar2;
21 -- function get_vendor_name(p_org_id in number, p_vendor_id in number) return varchar2;
22 -- function get_vendor_site(p_org_id in number, p_vendor_id in number, p_vendor_site_id in number) return varchar2;
23 -- function get_user_name(p_user_id in number) return varchar2;
24 -- function get_transaction_action_name(p_transaction_action_id in number) return varchar2;
25 -- function get_reason_name(p_reason_id in number) return varchar2;
26 -- function get_transaction_source_name(p_transaction_source_type_id in number) return varchar2;
27 -- function get_transaction_type_name(p_transaction_type_id in number ) return varchar2;
28 -- function get_unit_of_measure(p_uom_code in varchar2) return varchar2;
29 -- function get_uom_class_name(p_uom_class in varchar2) return varchar2;
30 -- function get_item_type_name(p_item_type_code in varchar2) return varchar2;
31 -- procedure Search()
32 -- Function get_item_type( p_org_id IN NUMBER,p_inventory_item_id IN NUMBER ) return VARCHAR2;
33 -- Function Item_Category
34 -- Function get_uom_class( p_uom_code IN VARCHAR2) return VARCHAR2;
35 -- Function get_vendor_id( p_reference IN VARCHAR2, p_reference_id IN NUMBER) return NUMBER;
36 -- Function get_order_type_id( p_move_order_line_id IN NUMBER) return NUMBER;
37 -- procedure send_msg_to_pipe(p_debug_mode in Boolean, p_message in VARCHAR2);
38 -- Procedure get_customer_freight_details()
39 -- Function get_order_type_id( p_move_order_line_id IN NUMBER, p_transaction_source_type_id IN NUMBER, p_reference_id IN NUMBER ) return NUMBER;
40 -----------------------------------------------------------------------------
41
42 g_pkg_name constant varchar2(30) := 'WMS_Rules_workbench_pvt';
43 l_debug NUMBER;
44
45 --
46 --Procedures for logging messages
47 PROCEDURE log_event(
48 p_api_name VARCHAR2,
49 p_label VARCHAR2,
50 p_message VARCHAR2) IS
51
52 l_module VARCHAR2(255);
53
54 BEGIN
55 l_module := g_pkg_name || p_label;
56 inv_log_util.trace(p_message, l_module, 9);
57 END log_event;
58
59 PROCEDURE log_error(
60 p_api_name VARCHAR2,
61 p_label VARCHAR2,
62 p_message VARCHAR2) IS
63
64 l_module VARCHAR2(255);
65
66 BEGIN
67 l_module := g_pkg_name || p_label;
68 inv_log_util.trace(p_message, l_module, 9);
69
70 END log_error;
71
72 PROCEDURE log_error_msg(
73 p_api_name VARCHAR2,
74 p_label VARCHAR2) IS
75
76 l_module VARCHAR2(255);
77
78 BEGIN
79 l_module := g_pkg_name || p_label;
80 inv_log_util.trace('', l_module, 9);
81
82 END log_error_msg;
83
84 PROCEDURE log_procedure(
85 p_api_name VARCHAR2,
86 p_label VARCHAR2,
87 p_message VARCHAR2) IS
88
89 l_module VARCHAR2(255);
90
91 BEGIN
92 l_module := g_pkg_name || p_label;
93 inv_log_util.trace(p_message, l_module, 9);
94
95 END log_procedure;
96
97 ---- Function to get Return type name ( Strategy / Rule name )
98 ---- to be used in the view wms_selection_criteria_txn_v
99
100 Function get_return_type_name(p_org_id IN NUMBER,
101 p_rule_type_code IN NUMBER,
102 p_return_type_code IN VARCHAR2,
103 p_return_type_id IN NUMBER)
104 Return VARCHAR2 is
105
106 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
107 l_api_name constant VARCHAR2(30) := 'GET_RETURN_TYPE_NAME';
108
109 l_return_type_name VARCHAR2(80) := NULL;
110
111
112 BEGIN
113 IF (p_return_type_code = 'V' ) then
114 IF(p_rule_type_code = 10) THEN
115 SELECT criterion_name
116 INTO l_return_type_name
117 FROM wms_crossdock_criteria_vl
118 WHERE criterion_id=p_return_type_id
119 AND criterion_type = 1 ;
120 return l_return_type_name;
121 ELSIF (p_rule_type_code = 11) THEN
122 SELECT criterion_name
123 INTO l_return_type_name
124 FROM wms_crossdock_criteria_vl
125 WHERE criterion_id=p_return_type_id
126 AND criterion_type = 2;
127 return l_return_type_name;
128 ELSIF (p_rule_type_code = 5) THEN
129 SELECT cost_group
130 INTO l_return_type_name
131 FROM CST_COST_GROUPS
132 WHERE cost_group_id=p_return_type_id
133 AND organization_id = p_org_id;
134 return l_return_type_name;
135 ELSIF (p_rule_type_code = 12) THEN -- Bug : 6682436
136 SELECT meaning
137 INTO l_return_type_name
138 FROM mfg_lookups_v
139 WHERE lookup_type = 'WMS_CARTONIZATION_ALGORITHMS'
140 AND lookup_code = p_return_type_id;
141 return l_return_type_name;
142 END IF;
143
144 ELSIF (p_return_type_code = 'S' ) then
145
146 select distinct name into l_return_type_name
147 from wms_strategies_vl
148 where organization_id in (p_org_id, -1)
149 and type_code = p_rule_type_code
150 and strategy_id = p_return_type_id;
151 return l_return_type_name;
152 ElSIF (p_return_type_code = 'R') THEN
153 select name into l_return_type_name
154 from wms_rules_vl
155 where organization_id in (p_org_id, -1)
156 and rule_id = p_return_type_id;
157 return l_return_type_name;
158 ELSE
159 l_return_type_name := '';
160 End If;
161 return l_return_type_name;
162 Exception
163 When others then
164 Return null;
165 End get_return_type_name;
166
167 ----- Function to get customer name, used in sthe view
168 -----
169
170 Function get_customer_name(p_customer_id IN NUMBER)
171 Return VARCHAR2 is
172 /* TCA changes : replaced ra_customer by
173 hz_parties party,
174 hz_cust_accounts cust_acct
175 */
176 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
177 l_api_name constant VARCHAR2(30) := 'GET_CUSTOMER_NAME';
178
179 l_customer_name HZ_PARTIES.PARTY_NAME%TYPE := NULL;
180 Begin
181
182 /*select rc.customer_name
183 into l_customer_name
184 from ra_customers rc
185 where rc.customer_id = p_customer_id;*/
186
187 /* SELECT distinct substrb ( PARTY.PARTY_NAME, 1, 50 )
188 INTO l_customer_name
189 FROM HZ_PARTIES PARTY,
190 HZ_CUST_ACCOUNTS CUST_ACCT
191 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
192 AND PARTY.PARTY_ID = p_customer_id; */
193
194
195 SELECT distinct substrb ( PARTY.PARTY_NAME, 1, 50 )
196 INTO l_customer_name
197 FROM HZ_PARTIES PARTY,
198 HZ_CUST_ACCOUNTS CUST_ACCT
199 WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
200 AND CUST_ACCT.CUST_ACCOUNT_ID = p_customer_id;
201
202 Return l_customer_name;
203 Exception
204 When others then
205 Return null;
206 End get_customer_name;
207
208 ----------
209
210 --- function to get the organization_code based on the organization_id
211 ---
212 Function get_organization_code(p_organization_id IN NUMBER)
213 Return VARCHAR2 is
214
215 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
216 l_api_name constant VARCHAR2(30) := 'GET_ORGANIZATION_CODE';
217
218 l_organization_code VARCHAR2(30) := NULL;
219
220 Begin
221
222 select ood.organization_code
223 into l_organization_code
224 from org_organization_definitions ood
225 where sysdate < nvl(ood.disable_date,sysdate+1)
226 and ood.organization_id = p_organization_id ;
227
228 Return l_organization_code;
229 exception
230 when others then
231 return null;
232
233 End get_organization_code;
234 ----------------------
235 -----
236 Function get_freight_code_name(p_org_id IN NUMBER,
237 p_freight_code IN VARCHAR2 )
238 Return VARCHAR2 is
239
240 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
241 l_api_name constant VARCHAR2(30) := 'GET_FREIGHT_CODE_NAME';
242
243 l_freight_code_name VARCHAR2(30);
244
245 Begin
246 select ofv.freight_code_tl
247 into l_freight_code_name
248 from org_freight ofv
249 where ofv.organization_id = p_org_id
250 and ofv.freight_code = p_freight_code
251 and sysdate < nvl(ofv.disable_date,sysdate+1);
252
253 Return l_freight_code_name;
254
255 Exception
256 When others then
257 Return null;
258 End get_freight_code_name;
259
260 ---------
261 ---
262 Function get_item(p_org_id IN NUMBER,
263 p_inventory_item_id IN NUMBER)
264 Return VARCHAR2 is
265
266 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
267 l_api_name constant VARCHAR2(30) := 'GET_ITEM';
268
269 l_item VARCHAR2(80);
270
271 Begin
272
273 select msik.concatenated_segments into l_item
274 from mtl_system_items_kfv msik
275 where msik.organization_id = p_org_id
276 and msik.inventory_item_id = p_inventory_item_id;
277
278 Return l_item;
279 Exception
280 When others then
281 Return null;
282 End get_item;
283 ---
284 ---
285
286 Function get_abc_group_class(p_org_id IN NUMBER,
287 p_assignment_group_id IN NUMBER,
288 p_class_id IN NUMBER )
289 Return VARCHAR2 is
290
291 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
292 l_api_name constant VARCHAR2(30) := ' GET_ASSIGNMENT_GROUP_CLASS';
293
294 l_abc_group_class VARCHAR2(500) := '';
295
296 Begin
297
298 select maag.assignment_group_name||' / '|| mac.abc_class_name
299 into l_abc_group_class
300 from mtl_abc_classes mac,
301 mtl_abc_assignment_groups maag ,
302 MTL_ABC_ASSGN_GROUP_CLASSES magc
303 where maag.organization_id = mac.organization_id
304 and magc.assignment_group_id = maag.assignment_group_id
305 and magc.abc_class_id = mac.abc_class_id
306 and mac.organization_id = p_org_id
307 and maag.assignment_group_id = p_assignment_group_id
308 and mac.abc_class_id = p_class_id ;
309
310 Return l_abc_group_class;
311
312 Exception
313 When others then
314 Return null;
315 End get_abc_group_class;
316 ---
317 ---
318 Function get_category_set_name(p_org_id IN NUMBER,
319 p_category_set_id IN NUMBER,
320 p_category_id IN NUMBER)
321 Return VARCHAR2 is
322
323 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
324 l_api_name constant VARCHAR2(30) := 'GET_CATEGORY_SET_NAME';
325
326 l_category_set_name VARCHAR2(500) := NULL;
327
328 Begin
329
330 /*
331 Select mcs.category_set_name||' / '||mck.concatenated_segments into l_category_set_name
332 From mtl_categories_kfv mck
333 ,mtl_category_sets_vl mcs
334 ,mtl_category_set_valid_cats mcsvc
335 Where mcs.category_set_id = mcsvc.category_set_id
336 and mck.category_id = mcsvc.category_id
337 and mcsvc.category_set_id = p_category_set_id
338 and mcsvc.category_id = p_category_id; */
339
340 -- Bug # 3271041
341
342 SELECT mcs.CATEGORY_SET_NAME||' / '|| mck.CONCATENATED_SEGMENTS into l_category_set_name
343 FROM MTL_CATEGORIES_KFV mck
344 ,MTL_CATEGORIES_VL mc
345 ,MTL_CATEGORY_SETS_VL mcs
346 ,( SELECT mic.ORGANIZATION_ID
347 ,mic.CATEGORY_SET_ID
348 ,mic.CATEGORY_ID
349 FROM MTL_ITEM_CATEGORIES mic
350 WHERE mic.ORGANIZATION_ID = p_org_id
351 GROUP BY mic.ORGANIZATION_ID
352 ,mic.CATEGORY_SET_ID
353 ,mic.CATEGORY_ID ) x
354 WHERE mcs.CATEGORY_SET_ID = x.CATEGORY_SET_ID
355 AND mc.CATEGORY_ID = mck.CATEGORY_ID
356 AND mck.CATEGORY_ID = x.CATEGORY_ID
357 AND x.CATEGORY_SET_ID = p_category_set_id
358 AND x.CATEGORY_ID = p_category_id;
359
360 Return l_category_set_name;
361
362 Exception
363 When others then
364 Return null;
365 End get_category_set_name;
366 ---------------------
367 ---------------------
368 Function get_order_type_name(p_transaction_type_id in number)
369 Return VARCHAR2 is
370
371 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
372 l_api_name constant VARCHAR2(30) := 'GET_ORDER_TYPE_NAME';
373
374 l_order_type_name VARCHAR2(80) := NULL;
375
376 Begin
377
378 select ottv.name into l_order_type_name
379 from oe_transaction_types_vl ottv
380 where ottv.transaction_type_id = p_transaction_type_id;
381
382 Return l_order_type_name;
383 Exception
384 When others then
385 Return null;
386 End get_order_type_name;
387 ----
388 ----
389 Function get_project_name(p_project_id IN NUMBER)
390 Return VARCHAR2 is
391
392 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
393 l_api_name constant VARCHAR2(30) := 'GET_PROJECT_NAME';
394
395 l_project_name VARCHAR2(80) := NULL;
396
397 Begin
398
399 select distinct ppov.project_name into l_project_name
400 from pjm_projects_mtll_v ppov
401 where ppov.project_id = p_project_id;
402
403 Return l_project_name;
404 Exception
405 When others then
406 Return null;
407 End get_project_name;
408 ----
409 ---
410 Function get_task_name(p_project_id IN NUMBER ,
411 p_task_id IN NUMBER)
412 Return VARCHAR2 is
413
414 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
415 l_api_name constant VARCHAR2(30) := 'GET_TASK_NAME';
416
417 l_task_name VARCHAR2(80) := NULL;
418
419 Begin
420
421 -------------------
422 SELECT nvl(p.project_number, '') || ' / ' || nvl(ptev.TASK_NUMBER, '') into l_task_name
423 FROM pjm_tasks_mtll_v ptev, pjm_projects_mtll_v p
424 where p.project_id = ptev.project_id
425 and ptev.project_id = p_project_id
426 and ptev.task_id = p_task_id;
427 -------------
428 /* select ppev.project_name||' / '||ptev.indented_task_name
429 into l_task_name
430 from pa_tasks_expend_v ptev
431 ,pa_projects_expend_v ppev
432 where ptev.project_id = ppev.project_id
433 and ppev.project_id = p_project_id
434 and ptev.task_id = p_task_id; */
435
436 Return l_task_name;
437 Exception
438 When others then
439 Return null;
440 End get_task_name;
441 ---------
442 --------
443 Function get_vendor_name(p_org_id IN NUMBER,
444 p_vendor_id IN NUMBER)
445 Return VARCHAR2 is
446
447 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
448 l_api_name constant VARCHAR2(30) := 'GET_VENDOR_NAME';
449
450 l_vendor_name VARCHAR2(80) := NULL;
451
452 Begin
453
454 SELECT pv.vendor_name
455 INTO l_vendor_name
456 FROM PO_VENDORS pv
457 ,PO_VENDOR_SITES_ALL pvsa
458 ,ORG_ORGANIZATION_DEFINITIONS ood
459 WHERE ood.ORGANIZATION_ID = p_org_id
460 AND nvl(pvsa.ORG_ID,-99) = nvl(ood.OPERATING_UNIT,-99)
461 AND pvsa.PURCHASING_SITE_FLAG = 'Y'
462 AND sysdate < nvl(pvsa.INACTIVE_DATE, sysdate + 1)
463 AND pv.VENDOR_ID = pvsa.VENDOR_ID
464 AND pvsa.vendor_id = p_vendor_id
465 GROUP BY pv.VENDOR_NAME;
466
467 Return l_vendor_name ;
468 Exception
469 When others then
470 Return null;
471 End get_vendor_name;
472 -------
473
474 ---
475 Function get_user_name(p_user_id IN NUMBER)
476 Return VARCHAR2 is
477
478 g_pkg_name constant VARCHAR2(50) := 'wms_selection_criteria_pvt';
479 l_api_name constant VARCHAR2(30) := 'get_user_name';
480
481 l_user_name VARCHAR2(80) := NULL;
482
483 Begin
484
485 select fu.user_name
486 into l_user_name
487 from fnd_user fu
488 where sysdate < nvl(fu.end_date,sysdate+1)
489 and fu.user_id = p_user_id;
490
491 Return l_user_name;
492 Exception
493 When others then
494 Return null;
495 End get_user_name;
496 ---
497 ---
498 Function get_transaction_action_name(p_transaction_action_id IN NUMBER)
499 Return VARCHAR2 is
500
501 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
502 l_api_name constant VARCHAR2(30) := 'GET_TRANSACTION_ACTION_NAME';
503
504 l_transaction_action_name VARCHAR2(80) := NULL;
505
506 Begin
507
508 select ml.meaning
509 into l_transaction_action_name
510 from mfg_lookups ml
511 where ml.lookup_type = 'MTL_TRANSACTION_ACTION'
512 and ml.lookup_code = p_transaction_action_id ;
513
514 Return l_transaction_action_name;
515
516 Exception
517 when others then
518 Return null;
519 END get_transaction_action_name;
520 ---
521 ---
522 Function get_reason_name(p_reason_id IN NUMBER)
523 Return VARCHAR2 is
524
525 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
526 l_api_name constant VARCHAR2(30) := 'GET_REASON_NAME';
527
528 l_reason_name VARCHAR2(80) := NULL;
529
530 Begin
531
532 select mtr.reason_name
533 into l_reason_name
534 from mtl_transaction_reasons mtr
535 where sysdate < nvl(mtr.disable_date,sysdate+1)
536 and mtr.reason_id = p_reason_id;
537
538 Return l_reason_name;
539 Exception
540 When others then
541 Return null;
542 End get_reason_name;
543 ---
544 ---
545 Function get_transaction_source_name(p_transaction_source_type_id IN NUMBER)
546 Return VARCHAR2 is
547
548 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
549 l_api_name constant VARCHAR2(30) := 'GET_TRANSACTION_SOURCE_NAME';
550
551 l_transaction_source_name VARCHAR2(80) := NULL;
552
553 Begin
554
555 select mtst.transaction_source_type_name
556 into l_transaction_source_name
557 from mtl_txn_source_types mtst
558 where sysdate < nvl(mtst.disable_date,sysdate+1)
559 and mtst.transaction_source_type_id = p_transaction_source_type_id;
560
561 Return l_transaction_source_name ;
562 Exception
563 when others then
564 Return null;
565 End get_transaction_source_name;
566 ---
567 ---
568 Function get_transaction_type_name(p_transaction_type_id IN NUMBER )
569 Return VARCHAR2 is
570
571 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
572 l_api_name constant VARCHAR2(30) := 'GET_TRANSACTION_TYPE_NAME';
573
574 l_transaction_type_name VARCHAR2(80) := NULL;
575
576 Begin
577
578 select mtt.transaction_type_name
579 into l_transaction_type_name
580 from mtl_transaction_types mtt
581 where sysdate < nvl(mtt.disable_date,sysdate+1)
582 and mtt.transaction_type_id = p_transaction_type_id;
583
584 Return l_transaction_type_name;
585
586 Exception
587 When others then
588 return null;
589 End get_transaction_type_name;
590 ---
591 ---
592 Function get_unit_of_measure(p_uom_code IN VARCHAR2)
593 Return VARCHAR2 is
594
595 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
596 l_api_name constant VARCHAR2(30) := 'GET_UNIT_OF_MEASURE';
597
598 l_unit_of_measure VARCHAR2(80) := NULL;
599
600 Begin
601
602 select muom.unit_of_measure_tl
603 into l_unit_of_measure
604 from mtl_units_of_measure muom
605 where sysdate < nvl(muom.disable_date,sysdate+1)
606 and muom.uom_code = p_uom_code;
607
608 Return l_unit_of_measure ;
609 Exception
610 When others then
611 Return null;
612 End get_unit_of_measure;
613 ----
614 ----
615 Function get_uom_class_name(p_uom_class IN VARCHAR2)
616 Return VARCHAR2 is
617
618 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
619 l_api_name constant VARCHAR2(30) := 'GET_UOM_CLASS_NAME';
620
621 l_UOM_class_name VARCHAR2(80) := NULL;
622
623 Begin
624
625 select muc.uom_class_tl
626 into l_uom_class_name
627 from mtl_uom_classes muc
628 where muc.uom_class = p_uom_class;
629
630 Return l_uom_class_name ;
631 Exception
632 When others then
633 Return null;
634 End get_uom_class_name;
635 ---
636 ---
637
638 Function get_item_type_name(p_item_type_code IN VARCHAR2)
639 Return VARCHAR2 is
640
641 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
642 l_api_name constant VARCHAR2(30) := 'GET_ITEM_TYPE';
643
644 l_item_type VARCHAR2(80) := NULL;
645
646 Begin
647 select ml.meaning into l_item_type
648 from fnd_common_lookups ml
649 where ml.lookup_type = 'ITEM_TYPE'
650 and ml.lookup_code = p_item_type_code;
651
652 Return l_item_type;
653 Exception
654 When others then
655 Return null;
656 End get_item_type_name;
657 ----------
658 ----------
659 procedure Search
660 ( p_api_version IN NUMBER
661 ,p_init_msg_list IN VARCHAR2
662 ,p_validation_level IN NUMBER
663 ,x_return_status OUT NOCOPY VARCHAR2
664 ,x_msg_count OUT NOCOPY NUMBER
665 ,x_msg_data OUT NOCOPY VARCHAR2
666 ,p_transaction_temp_id IN NUMBER
667 ,p_type_code IN NUMBER
668 ,x_return_type OUT NOCOPY VARCHAR2
669 ,x_return_type_id OUT NOCOPY NUMBER
670 ,p_organization_id IN NUMBER
671 ,x_sequence_number OUT NOCOPY NUMBER
672 ) is
673
674 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
675 l_api_version CONSTANT NUMBER := 1.0;
676 l_api_name constant VARCHAR2(30) := 'SEARCH';
677
678 l_debug_mode BOOLEAN := inv_pp_debug.is_debug_mode;
679
680
681 l_rec_wsct WMS_SELECTION_CRITERIA_TXN%ROWTYPE;
682 l_rec_mtrl MTL_TXN_REQUEST_LINES%ROWTYPE;
683
684
685 l_sequence_number wms_selection_criteria_txn.sequence_number%type;
686 l_return_type_code wms_selection_criteria_txn.return_type_code%type;
687 l_return_type_id wms_selection_criteria_txn.return_type_id%type;
688 l_from_organization_id wms_selection_criteria_txn.from_organization_id%type;
689 l_from_subinventory_name wms_selection_criteria_txn.from_subinventory_name%type default null;
690 l_to_organization_id wms_selection_criteria_txn.to_organization_id%type;
691 l_to_subinventory_name wms_selection_criteria_txn.to_subinventory_name%type;
692 l_customer_id wms_selection_criteria_txn.customer_id%type;
693 l_freight_code wms_selection_criteria_txn.freight_code%type;
694 l_inventory_item_id wms_selection_criteria_txn.inventory_item_id%type;
695 l_item_type wms_selection_criteria_txn.item_type%type;
696 l_order_type_id wms_selection_criteria_txn.order_type_id%type;
697 l_vendor_id wms_selection_criteria_txn.vendor_id%type;
698 l_project_id wms_selection_criteria_txn.project_id%type;
699 l_task_id wms_selection_criteria_txn.task_id%type;
700 l_user_id wms_selection_criteria_txn.user_id%type;
701 l_transaction_action_id wms_selection_criteria_txn.transaction_action_id%type;
702 l_reason_id wms_selection_criteria_txn.reason_id%type;
703 l_transaction_source_type_id wms_selection_criteria_txn.transaction_source_type_id%type;
704 l_transaction_type_id wms_selection_criteria_txn.transaction_type_id%type;
705 l_uom_code wms_selection_criteria_txn.uom_code%type;
706 l_uom_class wms_selection_criteria_txn.uom_class%type default null;
707
708 l_return_value BOOLEAN;
709
710 --8809951 start
711 l_category_id wms_selection_criteria_txn.category_id%type;
712 l_category_set_id wms_selection_criteria_txn.category_set_id%type;
713 l_assignment_group_id wms_selection_criteria_txn.assignment_group_id%type;
714 l_abc_class_id wms_selection_criteria_txn.abc_class_id%type;
715 TYPE mtrl_line_tabtype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
716 mtrl_line_tab mtrl_line_tabtype;
717
718 /*The sub query is to filter out the eligible records with the constant values that we have so that the
719 PL/SQL function calls like get_order_type(), get_item_type() etc will have to be executed only for the eligible records ) */
720 --Bug#10218502, for perf improvement, changed the cursor to call the pl/sql fucntions only if Rules WB has it restricted based on the
721 -- corresponding attributes. For ex , only if order type if restricted, call the corresponding pl/sql function.
722 CURSOR SIMILAR_MTRL_LINES IS
723 SELECT line_id FROM (
724 select mtrl.line_id,
725 mtrl.organization_id,
726 mtrl.inventory_item_id,
727 mtrl.transaction_source_type_id,
728 mtrl.reference_id,
729 mtrl.reference,
730 mtrl.uom_code
731 from mtl_txn_request_lines mtrl
732 where mtrl.header_id = l_rec_mtrl.header_id
733 AND mtrl.organization_id = l_from_organization_id
734 AND mtrl.line_status <> 5
735 AND NVL(mtrl.from_subinventory_code,'###') = NVL(l_from_subinventory_name,NVL(mtrl.from_subinventory_code,'###'))
736 AND NVL(mtrl.to_organization_id ,-999) = NVL(l_to_organization_id, NVL(mtrl.to_organization_id,-999))
737 AND NVL(mtrl.to_subinventory_code, '###') = NVL(l_to_subinventory_name, NVL(mtrl.to_subinventory_code,'###'))
738 AND mtrl.inventory_item_id = NVL(l_inventory_item_id , mtrl.inventory_item_id )
739 AND NVL(mtrl.project_id ,-999) = NVL(l_project_id , NVL(mtrl.project_id ,-999) )
740 AND NVL(mtrl.task_id ,-999) = NVL(l_task_id , NVL(mtrl.task_id ,-999) )
741 AND NVL(mtrl.reason_id ,-999) = NVL(l_reason_id ,NVL(mtrl.reason_id ,-999) )
742 AND mtrl.transaction_source_type_id = NVL(l_transaction_source_type_id,mtrl.transaction_source_type_id)
743 AND mtrl.transaction_type_id = NVL(l_transaction_type_id,mtrl.transaction_type_id)
744 AND NVL(mtrl.uom_code,'##') = NVL(l_uom_code ,nvl(mtrl.uom_code,'##'))
745 AND mtrl.last_updated_by = NVL(l_user_id, mtrl.last_updated_by)
746 AND ( (p_type_code=2 AND mtrl.pick_strategy_id IS NULL) OR
747 (p_type_code=1 AND mtrl.put_away_strategy_id IS NULL) ) /*10371674*/
748 ) mtrl1
749 WHERE ( NVL(l_freight_code,'AAA') = 'AAA' OR
750 EXISTS (SELECT 1
751 FROM wsh_delivery_details wdd,
752 wsh_carriers wc,
753 wsh_carrier_services wcs
754 WHERE wdd.move_order_line_id = mtrl1.line_id
755 AND wdd.ship_method_code = wcs.ship_method_code (+)
756 AND wcs.carrier_id = wc.carrier_id (+)
757 AND wc.freight_code = l_freight_code
758 )
759 )
760 AND ( Nvl(l_customer_id, -999) = -999 OR
761 EXISTS (SELECT 1 FROM wsh_delivery_details wdd
762 WHERE wdd.move_order_line_id = mtrl1.line_id
763 AND wdd.customer_id = l_customer_id
764 )
765 )
766 AND ( NVL(l_item_type,'1') = '1' OR
767 WMS_RULES_WORKBENCH_PVT.get_item_type(mtrl1.organization_id,mtrl1.inventory_item_id) = l_item_type
768 )
769 AND ( NVL(l_uom_class,'aaa') = 'aaa' OR
770 WMS_RULES_WORKBENCH_PVT.get_uom_class(mtrl1.uom_code) = l_uom_class
771 )
772 AND ( NVL(l_order_type_id,-999)= -999 OR
773 WMS_RULES_WORKBENCH_PVT.get_order_type_id(mtrl1.line_id,mtrl1.transaction_source_type_id,mtrl1.reference_id) = l_order_type_id
774 )
775 AND ( mtrl1.transaction_source_type_id <> 1 OR
776 NVL(l_vendor_id,-999) = -999 OR
777 WMS_RULES_WORKBENCH_PVT.get_vendor_id(mtrl1.reference,mtrl1.reference_id) = l_vendor_id
778 )
779 AND (l_category_id IS NULL OR WMS_RULES_WORKBENCH_PVT.get_Item_Cat(mtrl1.organization_id,
780 mtrl1.inventory_item_id,
781 l_category_set_id,
782 l_category_id )='Y'
783 )
784 AND (l_abc_class_id IS NULL OR WMS_RULES_WORKBENCH_PVT.get_group_class(mtrl1.inventory_item_id,
785 l_assignment_group_id,
786 l_abc_class_id)='Y'
787 );
788
789 --8809951 end
790
791 --- Cursor for Strategy /Rule /Value Selection based on the current move order line
792 -- 8809951 Added columns for High Volume Project Phase-2
793 cursor cur_stg_selection is
794 select return_type_code
795 ,return_type_id
796 ,sequence_number
797 ,from_subinventory_name
798 , to_organization_id
799 , to_subinventory_name
800 , Nvl(customer_id,l_customer_id) --8809951
801 , Nvl(freight_code,l_freight_code) --8809951
802 , inventory_item_id
803 , Nvl(item_type,l_item_type) --8809951
804 , Nvl(order_type_id,l_order_type_id) --8809951
805 , Nvl(vendor_id, l_vendor_id) --8809951
806 , project_id
807 , task_id
808 , user_id
809 , transaction_action_id
810 , reason_id
811 , transaction_source_type_id
812 , transaction_type_id
813 , uom_code
814 , Nvl(uom_class, l_uom_class) --8809951
815 , category_id
816 , category_set_id
817 , assignment_group_id
818 , abc_class_id
819 from wms_selection_criteria_txn
820 where from_organization_id = l_from_organization_id
821 and rule_type_code = p_type_code
822 and enabled_flag = 1
823 and nvl(from_subinventory_name, l_from_subinventory_name) = l_from_subinventory_name
824 and nvl(to_organization_id, l_to_organization_id) = l_to_organization_id
825 and nvl(to_subinventory_name, l_to_subinventory_name ) = l_to_subinventory_name
826 and nvl(customer_id, l_customer_id) = l_customer_id
827 and nvl(freight_code, l_freight_code) = l_freight_code
828 and nvl(inventory_item_id, l_inventory_item_id) = l_inventory_item_id
829 and nvl(item_type, l_item_type) = l_item_type
830 and nvl(order_type_id, l_order_type_id) = l_order_type_id
831 and nvl(vendor_id, l_vendor_id) = l_vendor_id
832 and nvl(project_id, l_project_id) = l_project_id
833 and nvl(task_id, l_task_id ) = l_task_id
834 and nvl(user_id, l_user_id ) = l_user_id
835 and nvl(transaction_action_id, l_transaction_action_id ) = l_transaction_action_id
836 and nvl(reason_id , l_reason_id ) = l_reason_id
837 and nvl(transaction_source_type_id, l_transaction_source_type_id) = l_transaction_source_type_id
838 and nvl(transaction_type_id, l_transaction_type_id) = l_transaction_type_id
839 and nvl(uom_code, l_uom_code) = l_uom_code
840 and nvl(uom_class, l_uom_class) = l_uom_class
841 and nvl(effective_from,to_date('01011900','ddmmyyyy')) <= trunc(sysdate)
842 and nvl(effective_to,to_date('31124000','ddmmyyyy')) >= trunc(sysdate)
843 and wms_datecheck_pvt.date_valid(l_from_organization_id,date_type_code,date_type_from,date_type_to,effective_from,effective_to) = 'Y' --Added bug 4081657
844 and decode(category_id,null,'N', 'Y') = decode(category_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_Item_Cat(l_rec_mtrl.organization_id,
845 l_rec_mtrl.inventory_item_id,
846 category_set_id,
847 category_id )
848 )
849 and decode(abc_class_id,null,'N', 'Y') = decode(abc_class_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_group_class(l_rec_mtrl.inventory_item_id,
850 assignment_group_id,
851 abc_class_id)
852 )
853
854 order by sequence_number;
855
856
857 begin
858 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
859 -- Bug # 2935052 -----
860 -- Call custom-specific strategy search stub procedure
861 wms_re_Custom_PUB.SearchForStrategy (
862 p_init_msg_list
863 ,x_return_status
864 ,x_msg_count
865 ,x_msg_data
866 ,p_transaction_temp_id
867 ,p_type_code
868 ,l_return_type_code
869 ,l_return_type_id
870 );
871 -- leave the actual procedure, if stub procedure already found a strategy
872 if x_return_status = fnd_api.g_ret_sts_success then
873 if l_debug = 1 then
874 log_event(l_api_name, 'Custom Type is found : ',l_return_type_code);
875 log_event(l_api_name, 'Custom Strategy/Rule/value found : ',l_return_type_id);
876 end if;
877
878 --- reassign the l_strategy_id to the new variables
879 x_return_type := l_return_type_code;
880 x_return_type_id := l_return_type_id;
881 x_sequence_number := 0;
882
883 return;
884 -- leave the actual procedure, if stub procedure got an unexpected error
885 elsif x_return_status = fnd_api.g_ret_sts_unexp_error then
886 raise fnd_api.g_exc_unexpected_error;
887 -- continue strategy search, if stub procedure didn't find strategy already
888 elsif x_return_status = fnd_api.g_ret_sts_error then
889 -- Re-Initialize API return status to success
890 x_return_status := fnd_api.g_ret_sts_success;
891 -- every other return status seems to be unexpected: leave
892 else
893 fnd_message.set_name('WMS','WMS_INVALID_RETURN_STATUS');
894 -- WMS_re_Custom_PUB.SearchForStrategy returned wrong status
895 fnd_msg_pub.add;
896 log_error_msg(l_api_name, 'bad_return_status');
897 raise fnd_api.g_exc_unexpected_error;
898 end if;
899 --- end of Bug # 2935052 -----
900
901 if l_debug = 1 then
902 log_procedure(l_api_name,'','Start selection Criteria ');
903 log_event(l_api_name,'Move order line: ', p_transaction_temp_id);
904 end if;
905 -- Initialize message list if p_init_msg_list is set to TRUE
906 if fnd_api.to_boolean( p_init_msg_list ) then
907 fnd_msg_pub.initialize;
908 end if;
909
910 -- Initialize API return status to success
911 x_return_status := fnd_api.g_ret_sts_success;
912
913 -- Bug 5264987 - Fetching the MO_lines from the Cache instead of querying ,
914
915 l_return_value := INV_CACHE.set_mol_rec(p_transaction_temp_id);
916 IF NOT l_return_value THEN
917 If l_debug = 1 THEN
918 log_event(l_api_name,'','Move order line cursor not found ');
919 End If;
920 raise fnd_api.g_exc_unexpected_error;
921 END IF;
922 log_event(l_api_name,'','test:Move order line fetched from cache');
923 l_rec_mtrl := INV_CACHE.mol_rec;
924
925
926 if l_debug = 1 then
927 log_event(l_api_name,'','fetching move order line');
928 log_event(l_api_name,'from org ', to_char(l_rec_mtrl.organization_id));
929 log_event(l_api_name,'dest org ', to_char(l_rec_mtrl.organization_id));
930 end if;
931
932 If (l_return_value) Then
933 --- For all the null values below, Functions need to be written
934 ---
935 if l_debug = 1then
936 log_event(l_api_name,'','Setting all variables');
937 end if;
938 -- Bug #3178127
939 -- modified the l_form_organization_id
940
941 l_from_organization_id := nvl(p_organization_id, l_rec_mtrl.organization_id);
942 l_from_subinventory_name := nvl(l_rec_mtrl.from_subinventory_code, 'aaa');
943 l_to_organization_id := nvl(l_rec_mtrl.organization_id, -999);
944 l_to_subinventory_name := nvl(l_rec_mtrl.to_subinventory_code, 'aaa');
945 l_customer_id := l_customer_id;
946 l_freight_code := l_freight_code;
947 l_inventory_item_id := nvl(l_rec_mtrl.inventory_item_id, -999);
948 l_item_type := nvl(get_item_type( l_rec_mtrl.organization_id ,l_rec_mtrl.inventory_item_id), '1');
949 l_order_type_id := nvl(get_order_type_id( l_rec_mtrl.line_id,l_rec_mtrl.transaction_source_type_id,
950 l_rec_mtrl.reference_id), -999);
951 l_project_id := nvl(l_rec_mtrl.project_id, -999);
952 l_task_id := nvl(l_rec_mtrl.task_id, -999);
953 l_user_id := nvl(l_rec_mtrl.last_updated_by, -999);
954 l_transaction_action_id := -999;
955 l_reason_id := nvl(l_rec_mtrl.reason_id, -999);
956 l_transaction_source_type_id := nvl(l_rec_mtrl.transaction_source_type_id, -999);
957 l_transaction_type_id := nvl(l_rec_mtrl.transaction_type_id, -999);
958 l_uom_code := nvl(l_rec_mtrl.uom_code, 'aaa');
959 l_uom_class := nvl(get_uom_class(l_rec_mtrl.uom_code), 'aaa');
960
961 if l_debug = 1 then
962 log_event(l_api_name,'','Setting of the variables is done');
963 log_event(l_api_name,'','copy Vendor Id');
964 end if;
965
966 if ( l_rec_mtrl.transaction_source_type_id = 1) then
967 l_VENDOR_ID := get_vendor_id(l_rec_mtrl.reference, l_rec_mtrl.reference_id);
968 end if;
969
970 l_vendor_id := nvl(l_vendor_id, -999);
971
972 ---- Setting customer_id and freigth details
973 if l_debug = 1 then
974 log_event(l_api_name,'','Setting Customer_id and freight details ');
975 end if;
976 get_customer_freight_details(p_transaction_temp_id, l_customer_id, l_freight_code);
977
978 l_customer_id := nvl(l_customer_id, -999);
979 l_freight_code := nvl(l_freight_code, 'AAA');
980
981 ------------ Setting Variables ---------------------------------------------------------
982 if l_debug = 1 then
983 log_event(l_api_name,'l_from_organization_id ',l_from_organization_id);
984 log_event(l_api_name,'l_from_subinventory_name ',l_from_subinventory_name);
985 log_event(l_api_name,'l_to_organization_id ',l_to_organization_id);
986 log_event(l_api_name,'l_to_subinventory_name ',l_to_subinventory_name);
987 log_event(l_api_name,'l_customer_id ',l_customer_id );
988 log_event(l_api_name,'l_freight_code ',l_freight_code );
989 log_event(l_api_name,'l_inventory_item_id ',l_inventory_item_id );
990 log_event(l_api_name,'l_item_type ',l_item_type );
991 log_event(l_api_name,'l_order_type_id ',l_order_type_id);
992 log_event(l_api_name,'l_project_id ',l_project_id);
993 log_event(l_api_name,'l_task_id ',l_task_id);
994 log_event(l_api_name,'l_user_id ',l_user_id);
995 log_event(l_api_name,'l_transaction_action_id ',l_transaction_action_id);
996 log_event(l_api_name,'l_reason_id ',l_reason_id );
997 log_event(l_api_name,'l_reason_id ',l_reason_id );
998 log_event(l_api_name,'l_transaction_source_type_id ',l_transaction_source_type_id );
999 log_event(l_api_name,'l_transaction_type_id ',l_transaction_type_id );
1000 log_event(l_api_name,'l_uom_code ',l_uom_code);
1001 log_event(l_api_name,'l_uom_class ',l_uom_class);
1002 log_event(l_api_name,'l_VENDOR_ID ',l_VENDOR_ID);
1003
1004 end if;
1005 ------------------------------------------------------------------------------------------
1006 -- 8809951 Added columns for High Volume Project Phase-2
1007 OPEN cur_stg_selection;
1008 FETCH cur_stg_selection
1009 INTO
1010 l_return_type_code,
1011 l_return_type_id,
1012 l_sequence_number,
1013 l_from_subinventory_name,
1014 l_to_organization_id,
1015 l_to_subinventory_name,
1016 l_customer_id,
1017 l_freight_code,
1018 l_inventory_item_id,
1019 l_item_type,
1020 l_order_type_id,
1021 l_vendor_id,
1022 l_project_id,
1023 l_task_id,
1024 l_user_id,
1025 l_transaction_action_id,
1026 l_reason_id,
1027 l_transaction_source_type_id,
1028 l_transaction_type_id,
1029 l_uom_code,
1030 l_uom_class,
1031 l_category_id,
1032 l_category_set_id,
1033 l_assignment_group_id,
1034 l_abc_class_id;
1035
1036 If (cur_stg_selection%NOTFOUND) Then
1037 --3224420close cur_stg_selection;
1038 if l_debug = 1 then
1039 log_event(l_api_name,'','stg_selection cursor not found ');
1040 end if;
1041 l_return_type_code := NULL;
1042 l_return_type_id := NULL;
1043 l_sequence_number := NULL;
1044 x_return_status := fnd_api.g_ret_sts_success;
1045
1046 --- setting global variables used by Rules simulator and trace execution forms
1047 IF p_type_code = 1 THEN
1048 wms_search_order_globals_pvt.g_putaway_strategy_id := -999;
1049 wms_search_order_globals_pvt.g_putaway_seq_num := -999;
1050 ELSIF p_type_code = 2 THEN
1051 wms_search_order_globals_pvt.g_pick_strategy_id := -999;
1052 wms_search_order_globals_pvt.g_pick_seq_num := -999;
1053 ELSIF p_type_code = 5 THEN
1054 wms_search_order_globals_pvt.g_costgroup_strategy_id := -999;
1055 wms_search_order_globals_pvt.g_costgroup_seq_num := -999;
1056 END IF; --- end of globol variables section
1057 End if;
1058
1059 If l_debug = 1 then
1060 log_event(l_api_name,' ' , 'After getting Rules WB cursor to fetch strategy/rule.... ');
1061 log_event(l_api_name,'l_from_organization_id ',l_from_organization_id);
1062 log_event(l_api_name,'l_from_subinventory_name ',l_from_subinventory_name);
1063 log_event(l_api_name,'l_to_organization_id ',l_to_organization_id);
1064 log_event(l_api_name,'l_to_subinventory_name ',l_to_subinventory_name);
1065 log_event(l_api_name,'l_customer_id ',l_customer_id );
1066 log_event(l_api_name,'l_freight_code ',l_freight_code );
1067 log_event(l_api_name,'l_inventory_item_id ',l_inventory_item_id );
1068 log_event(l_api_name,'l_item_type ',l_item_type );
1069 log_event(l_api_name,'l_order_type_id ',l_order_type_id);
1070 log_event(l_api_name,'l_project_id ',l_project_id);
1071 log_event(l_api_name,'l_task_id ',l_task_id);
1072 log_event(l_api_name,'l_user_id ',l_user_id);
1073 log_event(l_api_name,'l_transaction_action_id ',l_transaction_action_id);
1074 log_event(l_api_name,'l_reason_id ',l_reason_id );
1075 log_event(l_api_name,'l_transaction_source_type_id ',l_transaction_source_type_id );
1076 log_event(l_api_name,'l_transaction_type_id ',l_transaction_type_id );
1077 log_event(l_api_name,'l_uom_code ',l_uom_code);
1078 log_event(l_api_name,'l_uom_class ',l_uom_class);
1079 log_event(l_api_name,'l_VENDOR_ID ',l_VENDOR_ID);
1080 log_event(l_api_name,'l_return_type_code ',l_return_type_code);
1081 log_event(l_api_name,'l_return_type_id ',l_return_type_id);
1082 log_event(l_api_name,'l_sequence_number ',l_sequence_number);
1083 End if;
1084
1085
1086 If (cur_stg_selection%FOUND) Then
1087
1088 if l_debug =1 then
1089 log_event(l_api_name,'',' Open/fetching stg_selection cursor');
1090 end if;
1091
1092 x_return_type := l_return_type_code;
1093 x_return_type_id := l_return_type_id;
1094 x_sequence_number := l_sequence_number;
1095 x_return_status := fnd_api.g_ret_sts_success;
1096
1097 --- setting global variables used by Rules simulator and trace execution forms
1098
1099 IF l_return_type_code = 'S' THEN
1100 --clean up the mtrl table
1101 mtrl_line_tab.delete;
1102 IF p_type_code = 1 THEN
1103 wms_search_order_globals_pvt.g_putaway_strategy_id := l_return_type_id;
1104 wms_search_order_globals_pvt.g_putaway_seq_num := l_sequence_number;
1105
1106 -- 8809951 start
1107 OPEN SIMILAR_MTRL_LINES;
1108 FETCH SIMILAR_MTRL_LINES BULK COLLECT INTO mtrl_line_tab;
1109 CLOSE SIMILAR_MTRL_LINES;
1110
1111 FORALL i IN mtrl_line_tab.first..mtrl_line_tab.last
1112 UPDATE mtl_txn_request_lines
1113 SET put_away_strategy_id = l_return_type_id
1114 WHERE line_id = mtrl_line_tab(i);
1115
1116 if l_debug = 1 then
1117 log_event(l_api_name,'msg:','p_type_code => ' ||p_type_code);
1118 log_event(l_api_name,'msg:','Updated '||mtrl_line_tab.count||' mtrl lines with strategy_id ' ||l_return_type_id);
1119 end if;
1120
1121 -- 8809951 end
1122
1123
1124 ELSIF p_type_code = 2 THEN
1125 wms_search_order_globals_pvt.g_pick_strategy_id := l_return_type_id;
1126 wms_search_order_globals_pvt.g_pick_seq_num := l_sequence_number;
1127
1128 -- 8809951 start
1129 OPEN SIMILAR_MTRL_LINES;
1130 FETCH SIMILAR_MTRL_LINES BULK COLLECT INTO mtrl_line_tab;
1131 CLOSE SIMILAR_MTRL_LINES;
1132
1133 FORALL i IN mtrl_line_tab.first..mtrl_line_tab.last
1134 UPDATE mtl_txn_request_lines
1135 SET pick_strategy_id = l_return_type_id
1136 WHERE line_id = mtrl_line_tab(i);
1137 if l_debug = 1 then
1138 log_event(l_api_name,'msg:','p_type_code => '||p_type_code);
1139 log_event(l_api_name,'msg:','Updated '||mtrl_line_tab.count||' mtrl lines with strategy_id ' ||l_return_type_id);
1140 end if;
1141
1142 -- 8809951 end
1143
1144 ELSIF p_type_code = 5 THEN
1145 wms_search_order_globals_pvt.g_costgroup_strategy_id := l_return_type_id;
1146 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1147 ELSIF p_type_code = 10 THEN
1148 NULL;
1149 ELSIF p_type_code = 11 THEN
1150 NULL;
1151 END IF;
1152
1153 ELSIF l_return_type_code = 'R' THEN
1154 IF p_type_code = 1 THEN
1155 wms_search_order_globals_pvt.g_putaway_rule_id := l_return_type_id;
1156 wms_search_order_globals_pvt.g_putaway_seq_num := l_sequence_number;
1157
1158 ELSIF p_type_code = 2 THEN
1159 wms_search_order_globals_pvt.g_pick_rule_id := l_return_type_id;
1160 wms_search_order_globals_pvt.g_pick_seq_num := l_sequence_number;
1161
1162 ELSIF p_type_code = 5 THEN
1163 wms_search_order_globals_pvt.g_costgroup_rule_id := l_return_type_id;
1164 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1165 ELSIF p_type_code = 10 THEN
1166 NULL;
1167 ELSIF p_type_code = 11 THEN
1168 NULL;
1169 END IF;
1170 ELSIF l_return_type_code = 'V' THEN
1171
1172 IF p_type_code = 5 THEN
1173 wms_search_order_globals_pvt.g_costgroup_id := l_return_type_id;
1174 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1175 ELSIF p_type_code = 10 THEN
1176 NULL;
1177 ELSIF p_type_code = 11 THEN
1178 NULL;
1179 END IF;
1180
1181 END IF;
1182
1183 --- end of globol variables section
1184
1185 if l_debug = 1 then
1186 log_event(l_api_name,'Strategy / Rule =>',l_return_type_code);
1187 log_event(l_api_name,'Strategy Id / Rule Id => ' ,l_return_type_id);
1188 end if;
1189
1190 If cur_stg_selection%ISOPEN then
1191 CLOSE cur_stg_selection;
1192 End if;
1193 END If;
1194 If cur_stg_selection%ISOPEN then
1195 CLOSE cur_stg_selection;
1196 End if; --added for 3224420
1197
1198 End if;
1199 Exception
1200
1201 WHEN fnd_api.g_exc_error THEN
1202 x_return_status := fnd_api.g_ret_sts_error;
1203 If cur_stg_selection%ISOPEN then
1204 CLOSE cur_stg_selection;
1205 End if;
1206
1207
1208 fnd_msg_pub.Count_And_Get
1209 ( p_count => x_msg_count
1210 ,p_data => x_msg_data);
1211 log_error(l_api_name, 'error', 'Error in selection Criteria - ' ||
1212 x_msg_data);
1213 --
1214 WHEN fnd_api.g_exc_unexpected_error THEN
1215 x_return_status := fnd_api.g_ret_sts_unexp_error;
1216
1217 If cur_stg_selection%ISOPEN then
1218 CLOSE cur_stg_selection;
1219 End if;
1220
1221 fnd_msg_pub.Count_And_Get
1222 ( p_count => x_msg_count
1223 ,p_data => x_msg_data);
1224 log_error(l_api_name, 'unexp_error', 'Unexpected error ' ||
1225 'in selection Criteria - ' || x_msg_data);
1226 WHEN OTHERS THEN
1227 if l_debug = 1 then
1228 log_event(l_api_name,'',' Exception in selection Criteria');
1229 end if;
1230 x_return_status := fnd_api.g_ret_sts_unexp_error;
1231
1232 If cur_stg_selection%ISOPEN then
1233 CLOSE cur_stg_selection;
1234 End if;
1235
1236 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
1237 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1238 end if;
1239 End Search;
1240 ----------
1241 ----------
1242 procedure cg_mmtt_search
1243 ( p_api_version IN NUMBER
1244 ,p_init_msg_list IN VARCHAR2
1245 ,p_validation_level IN NUMBER
1246 ,x_return_status OUT NOCOPY VARCHAR2
1247 ,x_msg_count OUT NOCOPY NUMBER
1248 ,x_msg_data OUT NOCOPY VARCHAR2
1249 ,p_transaction_temp_id IN NUMBER
1250 ,p_type_code IN NUMBER
1251 ,x_return_type OUT NOCOPY VARCHAR2
1252 ,x_return_type_id OUT NOCOPY NUMBER
1253 ,p_organization_id IN NUMBER
1254 ,x_sequence_number OUT NOCOPY NUMBER
1255 ) is
1256
1257 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1258 l_api_version CONSTANT NUMBER := 1.0;
1259 l_api_name constant VARCHAR2(30) := 'CG_MMTT_SEARCH';
1260
1261 l_debug_mode BOOLEAN := inv_pp_debug.is_debug_mode;
1262
1263 l_rec_wsct WMS_SELECTION_CRITERIA_TXN%ROWTYPE;
1264 -- l_rec_mtrl MTL_TXN_REQUEST_LINES%ROWTYPE;
1265
1266 TYPE rec_mmtt is RECORD (
1267 line_id mtl_material_transactions_temp.transaction_temp_id%TYPE,
1268 organization_id mtl_material_transactions_temp.organization_id%TYPE,
1269 inventory_item_id mtl_material_transactions_temp.inventory_item_id%TYPE,
1270 revision mtl_material_transactions_temp.revision%TYPE,
1271 from_subinventory_code mtl_material_transactions_temp.subinventory_code%TYPE,
1272 to_subinventory_code mtl_material_transactions_temp.subinventory_code%TYPE,
1273 uom_code mtl_material_transactions_temp.transaction_uom%TYPE,
1274 reason_id mtl_material_transactions_temp.reason_id%TYPE,
1275 project_id mtl_material_transactions_temp.project_id%TYPE,
1276 task_id mtl_material_transactions_temp.task_id%type,
1277 transaction_type_id mtl_material_transactions_temp.transaction_type_id%TYPE,
1278 transaction_source_type_id mtl_material_transactions_temp.transaction_source_type_id%TYPE,
1279 to_organization_id mtl_material_transactions_temp.organization_id%TYPE,
1280 reference mtl_material_transactions_temp.transaction_reference%TYPE,
1281 reference_id mtl_material_transactions_temp.rcv_transaction_id%TYPE,
1282 transaction_action_id mtl_material_transactions_temp.transaction_action_id%TYPE,
1283 last_updated_by mtl_material_transactions_temp.last_updated_by%TYPE
1284 );
1285
1286 l_rec_mtrl rec_mmtt;
1287
1288 l_sequence_number wms_selection_criteria_txn.sequence_number%type;
1289 l_return_type_code wms_selection_criteria_txn.return_type_code%type;
1290 l_return_type_id wms_selection_criteria_txn.return_type_id%type;
1291 l_from_organization_id wms_selection_criteria_txn.from_organization_id%type;
1292 l_from_subinventory_name wms_selection_criteria_txn.from_subinventory_name%type default null;
1293 l_to_organization_id wms_selection_criteria_txn.to_organization_id%type;
1294 l_to_subinventory_name wms_selection_criteria_txn.to_subinventory_name%type;
1295 l_customer_id wms_selection_criteria_txn.customer_id%type;
1296 l_freight_code wms_selection_criteria_txn.freight_code%type;
1297 l_inventory_item_id wms_selection_criteria_txn.inventory_item_id%type;
1298 l_item_type wms_selection_criteria_txn.item_type%type;
1299 l_order_type_id wms_selection_criteria_txn.order_type_id%type;
1300 l_vendor_id wms_selection_criteria_txn.vendor_id%type;
1301 l_project_id wms_selection_criteria_txn.project_id%type;
1302 l_task_id wms_selection_criteria_txn.task_id%type;
1303 l_user_id wms_selection_criteria_txn.user_id%type;
1304 l_transaction_action_id wms_selection_criteria_txn.transaction_action_id%type;
1305 l_reason_id wms_selection_criteria_txn.reason_id%type;
1306 l_transaction_source_type_id wms_selection_criteria_txn.transaction_source_type_id%type;
1307 l_transaction_type_id wms_selection_criteria_txn.transaction_type_id%type;
1308 l_uom_code wms_selection_criteria_txn.uom_code%type;
1309 l_uom_class wms_selection_criteria_txn.uom_class%type default null;
1310
1311 --- Cursor for Strategy /Rule /Value Selection based on the current move order line
1312 --
1313 cursor cur_stg_selection is
1314 select return_type_code, return_type_id, sequence_number
1315 from wms_selection_criteria_txn
1316 where from_organization_id = l_from_organization_id
1317 and rule_type_code = p_type_code
1318 and enabled_flag = 1
1319 and nvl(from_subinventory_name, l_from_subinventory_name) = l_from_subinventory_name
1320 and nvl(to_organization_id, l_to_organization_id) = l_to_organization_id
1321 and nvl(to_subinventory_name, l_to_subinventory_name ) = l_to_subinventory_name
1322 and nvl(customer_id, l_customer_id) = l_customer_id
1323 and nvl(freight_code, l_freight_code) = l_freight_code
1324 and nvl(inventory_item_id, l_inventory_item_id) = l_inventory_item_id
1325 and nvl(item_type, l_item_type) = l_item_type
1326 and nvl(order_type_id, l_order_type_id) = l_order_type_id
1327 and nvl(vendor_id, l_vendor_id) = l_vendor_id
1328 and nvl(project_id, l_project_id) = l_project_id
1329 and nvl(task_id, l_task_id ) = l_task_id
1330 and nvl(user_id, l_user_id ) = l_user_id
1331 and nvl(transaction_action_id, l_transaction_action_id ) = l_transaction_action_id
1332 and nvl(reason_id , l_reason_id ) = l_reason_id
1333 and nvl(transaction_source_type_id, l_transaction_source_type_id) = l_transaction_source_type_id
1334 and nvl(transaction_type_id, l_transaction_type_id) = l_transaction_type_id
1335 and nvl(uom_code, l_uom_code) = l_uom_code
1336 and nvl(uom_class, l_uom_class) = l_uom_class
1337 and nvl(effective_from,to_date('01011900','ddmmyyyy')) <= trunc(sysdate)
1338 and nvl(effective_to,to_date('31124000','ddmmyyyy')) >= trunc(sysdate)
1339 and wms_datecheck_pvt.date_valid(l_from_organization_id,date_type_code,date_type_from,date_type_to,effective_from,effective_to) = 'Y' --Added bug 4081657
1340 and decode(category_id,null, 'N', 'Y') = decode(category_id, null, 'N', WMS_RULES_WORKBENCH_PVT.get_Item_Cat(l_from_organization_id, -- 14071820
1341 l_rec_mtrl.inventory_item_id ,
1342 category_set_id,
1343 category_id ))
1344 and decode(abc_class_id,null,'N', 'Y') = decode(abc_class_id,null,'N', WMS_RULES_WORKBENCH_PVT.get_group_class(l_rec_mtrl.inventory_item_id,
1345 assignment_group_id,
1346 abc_class_id))
1347 order by sequence_number;
1348
1349 --- Cursor to fetch all the values for the current MMTT record
1350 cursor cur_mmt is
1351 SELECT
1352 mmtt.transaction_temp_id ,
1353 mmtt.organization_id,
1354 mmtt.inventory_item_id,
1355 mmtt.revision,
1356 decode( mmtt.transaction_action_id,
1357 1,mmtt.subinventory_code,
1358 2,mmtt.subinventory_code,
1359 3,mmtt.subinventory_code,
1360 21,mmtt.subinventory_code,
1361 28,mmtt.subinventory_code,
1362 29,mmtt.subinventory_code,
1363 32,mmtt.subinventory_code,
1364 34,mmtt.subinventory_code,
1365 NULL),
1366 decode(transaction_action_id, 1,
1367 NULL, 2,
1368 transfer_subinventory, 3,
1369 transfer_subinventory, 21,
1370 NULL, 28,
1371 transfer_subinventory,
1372 29, NULL, 32, NULL,34, NULL, subinventory_code),
1373 mmtt.transaction_uom,
1374 mmtt.reason_id,
1375 mmtt.project_id,
1376 mmtt.task_id,
1377 mmtt.transaction_type_id,
1378 mmtt.transaction_source_type_id,
1379 decode(mmtt.transaction_action_id, 3, mmtt.transfer_organization, 21, mmtt.transfer_organization, mmtt.organization_id),
1380 mmtt.transaction_reference,
1381 decode(mmtt.source_code,'RCV', mmtt.rcv_transaction_id,to_number(NULL)),
1382 mmtt.transaction_action_id,
1383 mmtt.last_updated_by
1384 from mtl_material_transactions_temp mmtt
1385 where mmtt.transaction_temp_id = p_transaction_temp_id;
1386
1387 begin
1388 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1389 -- Bug # 2935052 -----
1390 -- Call custom-specific strategy search stub procedure
1391 wms_re_Custom_PUB.SearchForStrategy (
1392 p_init_msg_list
1393 ,x_return_status
1394 ,x_msg_count
1395 ,x_msg_data
1396 ,p_transaction_temp_id
1397 ,p_type_code
1398 ,l_return_type_code
1399 ,l_return_type_id
1400 );
1401 -- leave the actual procedure, if stub procedure already found a strategy
1402 if x_return_status = fnd_api.g_ret_sts_success then
1403 log_event(l_api_name,'custom Type is d', l_return_type_code);
1404 log_event(l_api_name,'custom strategyRule/Value found', l_return_type_id);
1405 --- reassign the l_strategy_id to the new variables
1406 x_return_type := l_return_type_code;
1407 x_return_type_id := l_return_type_id;
1408 x_sequence_number := 0;
1409 return;
1410 -- leave the actual procedure, if stub procedure got an unexpected error
1411 elsif x_return_status = fnd_api.g_ret_sts_unexp_error then
1412 raise fnd_api.g_exc_unexpected_error;
1413 -- continue strategy search, if stub procedure didn't find strategy already
1414 elsif x_return_status = fnd_api.g_ret_sts_error then
1415 -- Re-Initialize API return status to success
1416 x_return_status := fnd_api.g_ret_sts_success;
1417 -- every other return status seems to be unexpected: leave
1418 else
1419 fnd_message.set_name('WMS','WMS_INVALID_RETURN_STATUS');
1420 -- WMS_re_Custom_PUB.SearchForStrategy returned wrong status
1421 fnd_msg_pub.add;
1422 log_error_msg(l_api_name, 'bad_return_status');
1423 raise fnd_api.g_exc_unexpected_error;
1424 end if;
1425 ---- end of Bug # 2935052 -----
1426 if l_debug = 1 then
1427 log_event(l_api_name,'','Start');
1428 log_event(l_api_name,'MMTT line id: ', to_char(p_transaction_temp_id));
1429 end if;
1430
1431 -- Initialize message list if p_init_msg_list is set to TRUE
1432 if fnd_api.to_boolean( p_init_msg_list ) then
1433 fnd_msg_pub.initialize;
1434 end if;
1435 -- Initialize API return status to success
1436 x_return_status := fnd_api.g_ret_sts_success;
1437
1438 open cur_mmt;
1439 fetch cur_mmt into l_rec_mtrl;
1440
1441 if l_debug = 1 then
1442 log_event(l_api_name,'','fetching MMTT cursor ');
1443 log_event(l_api_name,'from org' , to_char(l_rec_mtrl.organization_id));
1444 log_event(l_api_name,'dest org' ,to_char(l_rec_mtrl.to_organization_id));
1445 end if;
1446
1447 If (cur_mmt%NOTFOUND) Then
1448 close cur_mmt;
1449 End if;
1450
1451 If (cur_mmt%FOUND) Then
1452 --- For all the null values below, Functions need to be written
1453 ---
1454 if l_debug = 1 then
1455 log_event(l_api_name,'','Setting all variables');
1456 end if;
1457
1458 --l_from_organization_id := nvl(l_rec_mtrl.organization_id, -999);
1459 l_from_organization_id := nvl(p_organization_id, l_rec_mtrl.organization_id);
1460 l_from_subinventory_name := nvl(l_rec_mtrl.from_subinventory_code, 'aaa');
1461 l_to_organization_id := nvl(l_rec_mtrl.to_organization_id, -999);
1462 l_to_subinventory_name := nvl(l_rec_mtrl.to_subinventory_code, 'aaa');
1463 l_customer_id := -999;
1464 l_freight_code := 'XXX';
1465 l_inventory_item_id := nvl(l_rec_mtrl.inventory_item_id, -999);
1466 l_item_type := nvl(get_item_type( l_rec_mtrl.organization_id ,l_rec_mtrl.inventory_item_id), '1');
1467 l_order_type_id := nvl(get_order_type_id( l_rec_mtrl.line_id), -999);
1468 l_project_id := nvl(l_rec_mtrl.project_id, -999);
1469 l_task_id := nvl(l_rec_mtrl.task_id, -999);
1470 l_user_id := nvl(l_rec_mtrl.last_updated_by, -999);
1471 l_transaction_action_id := nvl(l_rec_mtrl.transaction_action_id, -999);
1472 l_reason_id := nvl(l_rec_mtrl.reason_id, -999);
1473 l_transaction_source_type_id := nvl(l_rec_mtrl.transaction_source_type_id, -999);
1474 l_transaction_type_id := nvl(l_rec_mtrl.transaction_type_id, -999);
1475 l_uom_code := nvl(l_rec_mtrl.uom_code, 'aaa');
1476 l_uom_class := nvl(get_uom_class(l_rec_mtrl.uom_code), 'aaaaaaaaaa');
1477 --
1478 if ( l_rec_mtrl.transaction_source_type_id = 1) then
1479 l_VENDOR_ID := get_vendor_id(l_rec_mtrl.reference, l_rec_mtrl.reference_id);
1480 end if;
1481 l_vendor_id := nvl(l_vendor_id, -999);
1482
1483 --- Setting customer_id and freigth details
1484
1485 get_customer_freight_details(p_transaction_temp_id,
1486 l_customer_id ,
1487 l_freight_code);
1488
1489 l_customer_id := nvl(l_customer_id, -999);
1490 l_freight_code := nvl(l_freight_code, 'AAA');
1491 ------------- Setting Variables ---------------------------------------------------------
1492 if l_debug = 1 then
1493 log_event(l_api_name,'l_from_organization_id ' ,l_from_organization_id);
1494 log_event(l_api_name,'l_from_subinventory_name ' ,l_from_subinventory_name);
1495 log_event(l_api_name,'l_to_organization_id ' ,l_to_organization_id);
1496 log_event(l_api_name,'l_to_subinventory_name ' ,l_to_subinventory_name);
1497 log_event(l_api_name,'l_customer_id ' ,l_customer_id );
1498 log_event(l_api_name,'l_freight_code ' ,l_freight_code );
1499 log_event(l_api_name,'l_inventory_item_id ' ,l_inventory_item_id );
1500 log_event(l_api_name,'l_item_type ' ,l_item_type );
1501 log_event(l_api_name,'l_order_type_id ' ,l_order_type_id);
1502 log_event(l_api_name,'l_project_id ' ,l_project_id);
1503 log_event(l_api_name,'l_task_id ' ,l_task_id);
1504 log_event(l_api_name,'l_user_id ' ,l_user_id);
1505 log_event(l_api_name,'l_transaction_action_id ' ,l_transaction_action_id);
1506 log_event(l_api_name,'l_reason_id ' ,l_reason_id );
1507 log_event(l_api_name,'l_reason_id ' ,l_reason_id );
1508 log_event(l_api_name,'l_transaction_source_type_id ' ,l_transaction_source_type_id );
1509 log_event(l_api_name,'l_transaction_type_id ' ,l_transaction_type_id );
1510 log_event(l_api_name,'l_uom_code ' ,l_uom_code);
1511 log_event(l_api_name,'l_uom_class ' ,l_uom_class);
1512 log_event(l_api_name,'l_vendor_id ' ,l_vendor_id);
1513 end if;
1514 ------------------------------------------------------------------------------------------
1515
1516 OPEN cur_stg_selection;
1517 FETCH cur_stg_selection INTO l_return_type_code, l_return_type_id, l_sequence_number;
1518
1519 If (cur_stg_selection%NOTFOUND) Then
1520 --commenting out for 3224420 close cur_stg_selection;
1521 if l_debug = 1 then
1522 log_event(l_api_name,'','stg_selection cursor not found ');
1523 end if;
1524 l_return_type_code := NULL;
1525 l_return_type_id := NULL;
1526 l_sequence_number := NULL;
1527
1528 IF p_type_code = 5 THEN
1529 wms_search_order_globals_pvt.g_costgroup_strategy_id := -999;
1530 wms_search_order_globals_pvt.g_costgroup_seq_num := -999;
1531 END IF;
1532 x_return_status := fnd_api.g_ret_sts_success;
1533 End if;
1534
1535 If (cur_stg_selection%FOUND) Then
1536 x_return_type := l_return_type_code;
1537 x_return_type_id := l_return_type_id;
1538 x_sequence_number := l_sequence_number;
1539 x_return_status := fnd_api.g_ret_sts_success;
1540
1541 if l_debug = 1 then
1542 log_event(l_api_name, '',' Open/fetching stg_selection cursor');
1543 end if;
1544 --- setting global variables used by Rules simulator and trace execution forms
1545 IF p_type_code = 5 THEN
1546 IF l_return_type_code = 'S' THEN
1547 wms_search_order_globals_pvt.g_costgroup_strategy_id := l_return_type_id;
1548 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1549 ELSIF( l_return_type_code = 'R' ) THEN
1550 wms_search_order_globals_pvt.G_COSTGROUP_RULE_ID := l_return_type_id;
1551 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1552 ELSIF( l_return_type_code = 'V' ) THEN
1553 wms_search_order_globals_pvt.G_COSTGROUP_ID := l_return_type_id;
1554 wms_search_order_globals_pvt.g_costgroup_seq_num := l_sequence_number;
1555 END IF;
1556 END IF;
1557 --- end of globol variables section
1558 if l_debug = 1 then
1559 log_event(l_api_name,'Strategy / Rule =>' ,l_return_type_code);
1560 log_event(l_api_name,'Strategy Id / Rule Id => ' ,l_return_type_id);
1561 end if;
1562 IF cur_stg_selection%ISOPEN then
1563 CLOSE cur_stg_selection;
1564 End if;
1565 End IF;
1566 IF cur_mmt%ISOPEN then
1567 CLOSE cur_mmt;
1568 End if;
1569 IF (cur_stg_selection%ISOPEN) then
1570 CLOSE cur_stg_selection;
1571 END IF; --Added ofr 3224420
1572 End if;
1573 Exception
1574 WHEN fnd_api.g_exc_error THEN
1575 x_return_status := fnd_api.g_ret_sts_error;
1576 IF cur_mmt%ISOPEN then
1577 CLOSE cur_mmt;
1578 End if;
1579
1580 IF cur_stg_selection%ISOPEN then
1581 CLOSE cur_stg_selection;
1582 End if;
1583 fnd_msg_pub.Count_And_Get
1584 ( p_count => x_msg_count
1585 ,p_data => x_msg_data);
1586 log_error(l_api_name, 'error', 'Error in selection Criteria - ' ||
1587 x_msg_data);
1588 WHEN fnd_api.g_exc_unexpected_error THEN
1589 x_return_status := fnd_api.g_ret_sts_unexp_error;
1590 IF cur_mmt%ISOPEN then
1591 CLOSE cur_mmt;
1592 End if;
1593
1594 IF cur_stg_selection%ISOPEN then
1595 CLOSE cur_stg_selection;
1596 End if;
1597 fnd_msg_pub.Count_And_Get
1598 ( p_count => x_msg_count
1599 ,p_data => x_msg_data);
1600 log_error(l_api_name, 'unexp_error', x_msg_data);
1601 WHEN OTHERS THEN
1602 IF cur_mmt%ISOPEN then
1603 CLOSE cur_mmt;
1604 End if;
1605 IF cur_stg_selection%ISOPEN then
1606 CLOSE cur_stg_selection;
1607 End if;
1608
1609 if l_debug = 1 then
1610 log_event(l_api_name,'',' Exception in selection Criteria');
1611 end if;
1612 x_return_status := fnd_api.g_ret_sts_unexp_error;
1613
1614 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
1615 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1616 end if;
1617 End cg_mmtt_search;
1618 ---
1619 ---
1620 Function get_item_type( p_org_id IN NUMBER,
1621 p_inventory_item_id IN NUMBER )
1622 Return VARCHAR2 is
1623 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1624 l_api_name constant VARCHAR2(30) := 'GET_ITEM_TYPE';
1625 l_item_type VARCHAR2(30) := NULL; --Bug#8367746 Changed size from 10 to 30
1626 l_return_value BOOLEAN; -- 8809951
1627
1628 begin
1629 if l_debug = 1 then
1630 log_event(l_api_name,' Enter' ,l_api_name);
1631 end if;
1632
1633 -- 8809951 start
1634 l_return_value := inv_cache.set_item_rec(p_org_id,p_inventory_item_id);
1635 IF NOT l_return_value THEN
1636 If l_debug = 1 THEN
1637 log_event(l_api_name,'','Item not found ');
1638 END IF;
1639 RAISE fnd_api.g_exc_unexpected_error;
1640 END IF;
1641
1642 l_item_type:=inv_cache.item_rec.item_type;
1643 -- 8809951 end
1644
1645 if l_debug = 1 then
1646 log_event(l_api_name,'End','get_item_type');
1647 end if;
1648 return l_item_type;
1649 Exception
1650 When others then
1651 Return NULL;
1652 end get_item_type;
1653
1654 ---
1655 ---
1656
1657 Function get_item_cat( p_org_id IN NUMBER,
1658 p_inventory_item_id IN NUMBER,
1659 p_category_set_id IN NUMBER,
1660 p_category_id IN NUMBER)
1661 return VARCHAR2 is
1662 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1663 l_api_name constant VARCHAR2(30) := 'GET_ITEM_CATEGORY';
1664 l_category_exist VARCHAR2(10) := 'N';
1665 --8809951 start
1666 l_hash_value NUMBER;
1667 l_hash_string VARCHAR2(200);
1668 -- 8809951 end
1669 Begin
1670
1671 if l_debug = 1 then
1672 log_event(l_api_name,'Enter ', l_api_name);
1673 end if;
1674
1675 if ( p_org_id IS NOT NULL AND
1676 p_inventory_item_id IS NOT NULL AND
1677 p_category_set_id IS NOT NULL AND
1678 p_category_id IS NOT NULL ) then
1679
1680 -- 8809951 start
1681 l_hash_string := p_org_id || '-' || p_inventory_item_id || '-' || p_category_set_id || '-' || p_category_id ;
1682 l_hash_value := DBMS_UTILITY.get_hash_value
1683 (name => l_hash_string
1684 , base => g_hash_base
1685 , hash_size => g_hash_size );
1686
1687 IF g_item_cat_table.EXISTS(l_hash_value) THEN
1688 l_category_exist :=g_item_cat_table(l_hash_value);
1689 ELSE
1690 select 'Y' INTO l_category_exist
1691 from mtl_item_categories mic
1692 where mic.organization_id = p_org_id
1693 and mic.inventory_item_id = p_inventory_item_id
1694 and mic.category_set_id = p_category_set_id
1695 and mic.category_id = p_category_id
1696 and rownum<2;
1697 g_item_cat_table(l_hash_value) := l_category_exist ;
1698 END IF;
1699 end if;
1700 -- 8809951 end
1701 Return l_category_exist;
1702
1703 Exception
1704 When others then
1705 return l_category_exist;
1706 End get_item_cat;
1707 ---
1708 ---
1709 Function get_group_class( p_inventory_item_id IN NUMBER,
1710 p_assignment_group_id IN NUMBER,
1711 p_class_id IN NUMBER ) return VARCHAR2 is
1712 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1713 l_api_name constant VARCHAR2(30) := 'GET_GROUP_CLASS';
1714 l_group_class_exist VARCHAR2(10) := 'N';
1715
1716 Begin
1717 if l_debug = 1 then
1718 log_event(l_api_name,'Enter ',l_api_name);
1719 end if;
1720
1721 if ( p_inventory_item_id IS NOT NULL AND
1722 p_assignment_group_id IS NOT NULL AND
1723 p_class_id IS NOT NULL ) then
1724
1725
1726 -- 8809951 Modified the query
1727 select 'Y' INTO l_group_class_exist FROM mtl_abc_assignments
1728 where inventory_item_id = p_inventory_item_id
1729 and assignment_group_id = p_assignment_group_id
1730 and abc_class_id = p_class_id;
1731
1732 end if;
1733
1734 return l_group_class_exist ;
1735
1736 Exception
1737 When others then
1738 return l_group_class_exist ;
1739 End get_group_class;
1740 ---
1741 ----
1742 Function get_uom_class( p_uom_code IN VARCHAR2)
1743 Return VARCHAR2 is
1744
1745 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1746 l_api_name constant VARCHAR2(30) := 'GET_UOM_CLASS';
1747 l_uom_class VARCHAR2(10) := NULL;
1748 -- 8809951 start
1749 l_hash_value NUMBER;
1750 l_hash_string VARCHAR2(200);
1751 -- 8809951 end
1752 Begin
1753 if l_debug = 1 then
1754 log_event(l_api_name,' Enter',l_api_name);
1755 end if;
1756
1757 -- 8809951 start
1758 l_hash_string := p_uom_code;
1759 l_hash_value := DBMS_UTILITY.get_hash_value
1760 (name => l_hash_string
1761 , base => g_hash_base
1762 , hash_size => g_hash_size );
1763
1764 IF g_uom_class_tbl.EXISTS(l_hash_value) THEN
1765 l_uom_class :=g_uom_class_tbl(l_hash_value);
1766 ELSE
1767 select muom.uom_class into l_uom_class
1768 from MTL_UNITS_OF_MEASURE muom
1769 where muom.uom_code = p_uom_code;
1770 g_uom_class_tbl(l_hash_value) := l_uom_class;
1771 END IF;
1772 -- 8809951 end
1773 Return l_uom_class;
1774 Exception
1775 When others then
1776 Return null;
1777 End get_uom_class;
1778
1779 ----
1780 ----
1781 Function get_vendor_id( p_reference IN VARCHAR2,
1782 p_reference_id IN NUMBER)
1783 Return NUMBER is
1784
1785 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1786 l_api_name constant VARCHAR2(30) := 'GET_VENDOR_ID';
1787
1788 l_vendor_id NUMBER := NULL;
1789
1790 Begin
1791 if l_debug = 1 then
1792 log_event(l_api_name,' Enter' ,l_api_name);
1793 end if;
1794 if (p_reference = 'PO_LINE_LOCATION_ID' ) then
1795
1796 -- MOAC: _ALL tables replace views in next three select statements
1797 -- existing where clauses sufficient to stripe by OU
1798
1799 select poh.vendor_id into l_vendor_id
1800 from po_headers_all poh,
1801 po_lines_all pol,
1802 po_line_locations_all pll
1803 where poh.po_header_id = pol.po_header_id
1804 and pll.po_header_id = pol.po_header_id
1805 and pll.po_line_id = pol.po_line_id
1806 and pll.shipment_type = 'STANDARD'
1807 and pll.line_location_id = p_reference_id ;
1808
1809 elsif (p_reference = 'PO_DISTRIBUTION_ID' ) then
1810 select poh.vendor_id into l_vendor_id
1811 from po_headers_all poh,
1812 po_lines_all pol,
1813 po_distributions_all pod,
1814 po_line_locations_all pll
1815 where poh.po_header_id = pol.po_header_id
1816 and pll.po_header_id = pol.po_header_id
1817 and pll.po_line_id = pol.po_line_id
1818 and pod.po_header_id = pll.po_header_id
1819 and pod.po_line_id = pll.po_line_id
1820 and pod.line_location_id = pll.line_location_id
1821 and pll.shipment_type = 'STANDARD'
1822 and pod.po_distribution_id = p_reference_id;
1823 Else
1824 select poh.vendor_id into l_vendor_id
1825 from po_headers_all poh,
1826 po_lines_all pol,
1827 rcv_transactions rct,
1828 po_line_locations_all pll
1829 where poh.po_header_id = pol.po_header_id
1830 and pll.po_header_id = pol.po_header_id
1831 and pll.po_line_id = pol.po_line_id
1832 and rct.po_header_id = pll.po_header_id
1833 and rct.po_line_id = pll.po_line_id
1834 and rct.po_line_location_id = pll.line_location_id
1835 and pll.shipment_type = 'STANDARD'
1836 and rct.transaction_id = p_reference_id;
1837 End if;
1838 Return l_vendor_id;
1839 Exception
1840 When others then
1841 Return null;
1842 End get_vendor_id;
1843 ---
1844 ---
1845 Function get_order_type_id( p_move_order_line_id IN NUMBER)
1846 Return NUMBER is
1847
1848 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1849 l_api_name constant VARCHAR2(30) := 'GET_ORDER_TYPE_ID';
1850
1851 l_order_type_id VARCHAR2(10) := NULL;
1852 l_transaction_source_type_id NUMBER ;
1853 l_reference_id NUMBER;
1854 l_return_value BOOLEAN; --8809951
1855
1856 Begin
1857 if l_debug = 1 then
1858 log_event(l_api_name,' Enter' ,l_api_name);
1859 log_event(l_api_name,' Move order Line :' , p_move_order_line_id);
1860 end if;
1861
1862 --8809951 start
1863 l_return_value := inv_cache.set_mol_rec(p_move_order_line_id);
1864 IF NOT l_return_value THEN
1865 IF l_debug = 1 THEN
1866 log_event(l_api_name,'','Move order line not found ');
1867 END IF;
1868 RAISE fnd_api.g_exc_unexpected_error;
1869 END IF;
1870
1871 /* -- Bug #3387877
1872 select nvl(mtrl.transaction_source_type_id,0) , nvl(mtrl.reference_id, 0)
1873 into l_transaction_source_type_id, l_reference_id
1874 from mtl_txn_request_lines mtrl
1875 where mtrl.line_id = p_move_order_line_id; */
1876
1877 l_transaction_source_type_id := inv_cache.mol_rec.transaction_source_type_id;
1878 l_reference_id := inv_cache.mol_rec.reference_id;
1879
1880 --8809951 end
1881 if l_debug = 1 then
1882 log_event(l_api_name,' Txn_source_type_id ' ,l_transaction_source_type_id);
1883 log_event(l_api_name,' l_reference_id :' , l_reference_id);
1884 end if;
1885
1886 if l_transaction_source_type_id in (2,8) then
1887
1888 -- MOAC : changed oe_order_headers to oe_order_headers_all
1889
1890 /* added the index hint with the suggestion of apps performance team */
1891 select /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/ oh.order_type_id into l_order_type_id
1892 from oe_order_headers_all oh,
1893 wsh_delivery_details wdd
1894 where oh.header_id = wdd.source_header_id
1895 and wdd.released_status = 'S'
1896 and wdd.source_code = 'OE'
1897 and wdd.move_order_line_id = p_move_order_line_id;
1898
1899 -- Bug #3387877
1900 -- to get the sales order type at header level
1901
1902 -- MOAC : changed oe_order_headers to oe_order_headers_all
1903 -- MOAC : and oe_order_lines to oe_order_lines_all
1904
1905 elsif l_transaction_source_type_id = 12 then
1906 select oh.order_type_id into l_order_type_id
1907 from oe_order_headers_all oh ,
1908 oe_order_lines_all ol
1909 where oh.header_id = ol.header_id
1910 and ol.line_id = l_reference_id ;
1911
1912 end if;
1913 return l_order_type_id;
1914
1915 Exception
1916 When others then
1917 Return null;
1918 End get_order_type_id;
1919 --
1920 --Bug#10218502- Overloading the function get_order_type_id for perf reasons.
1921 --
1922 Function get_order_type_id( p_move_order_line_id IN NUMBER,
1923 p_transaction_source_type_id IN NUMBER,
1924 p_reference_id IN NUMBER
1925 )
1926 Return NUMBER is
1927
1928 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1929 l_api_name constant VARCHAR2(30) := 'GET_ORDER_TYPE_ID';
1930 l_order_type_id VARCHAR2(10) := NULL;
1931
1932 Begin
1933 if l_debug = 1 then
1934 log_event(l_api_name,' Enter' ,l_api_name);
1935 log_event(l_api_name,' Move order Line :' , p_move_order_line_id);
1936 log_event(l_api_name,' Txn_source_type_id ' ,p_transaction_source_type_id);
1937 log_event(l_api_name,' l_reference_id :' , p_reference_id);
1938 end if;
1939
1940 if p_transaction_source_type_id in (2,8) then
1941
1942 -- MOAC : changed oe_order_headers to oe_order_headers_all
1943
1944 /* added the index hint with the suggestion of apps performance team */
1945 select /*+index (WDD WSH_DELIVERY_DETAILS_N7)*/ oh.order_type_id into l_order_type_id
1946 from oe_order_headers_all oh,
1947 wsh_delivery_details wdd
1948 where oh.header_id = wdd.source_header_id
1949 and wdd.released_status = 'S'
1950 and wdd.source_code = 'OE'
1951 and wdd.move_order_line_id = p_move_order_line_id;
1952
1953 -- Bug #3387877
1954 -- to get the sales order type at header level
1955
1956 -- MOAC : changed oe_order_headers to oe_order_headers_all
1957 -- MOAC : and oe_order_lines to oe_order_lines_all
1958
1959 elsif p_transaction_source_type_id = 12 then
1960 select oh.order_type_id into l_order_type_id
1961 from oe_order_headers_all oh ,
1962 oe_order_lines_all ol
1963 where oh.header_id = ol.header_id
1964 and ol.line_id = p_reference_id ;
1965
1966 end if;
1967 return l_order_type_id;
1968
1969 Exception
1970 When others then
1971 Return null;
1972 End get_order_type_id;
1973 ---
1974 ---
1975 Procedure get_customer_freight_details(p_transaction_temp_id IN NUMBER,
1976 x_customer_id OUT NOCOPY NUMBER,
1977 x_freight_code OUT NOCOPY VARCHAR2) is
1978
1979 g_pkg_name constant VARCHAR2(50) := 'WMS_SELECTION_CRITERIA_PVT';
1980 l_api_name constant VARCHAR2(30) := 'GET_CUSTOMER_FREIGHT_DETAILS';
1981 l_customer_id NUMBER;
1982 l_freight_code VARCHAR2(30);
1983 l_trx_source_line_id NUMBER;
1984 begin
1985 if l_debug = 1 then
1986 log_event(l_api_name,' Enter',l_api_name);
1987 end if;
1988 if l_debug = 1 then
1989 log_event(l_api_name,'p_transaction_temp_id:'|| p_transaction_temp_id,l_api_name);
1990 end if;
1991
1992 BEGIN
1993 SELECT nvl(trx_source_line_id, -999)
1994 INTO l_trx_source_line_id
1995 FROM mtl_material_Transactions_temp
1996 WHERE transaction_temp_id = p_transaction_temp_id
1997 AND transaction_source_type_id = INV_GLOBALS.G_SOURCETYPE_RMA
1998 AND transaction_action_id = INV_GLOBALS.G_ACTION_RECEIPT;
1999
2000 if l_debug = 1 then
2001 log_event(l_api_name,'l_trx_source_line_id:'|| l_trx_source_line_id,l_api_name);
2002 end if;
2003
2004 EXCEPTION
2005 WHEN NO_DATA_FOUND THEN
2006 l_trx_source_line_id := -999 ;
2007 END;
2008 BEGIN
2009 select customer_id, freight_carrier_code into l_customer_id, l_freight_code
2010 from WMS_TXN_CONTEXT_TEMP
2011 where line_id = p_transaction_temp_id;
2012
2013 if l_debug = 1 then
2014 log_event(l_api_name,' Values from wms_txn_context_temp:l_customer_id:'||l_customer_id||
2015 'l_freight_code' || l_freight_code,l_api_name);
2016 end if;
2017 EXCEPTION
2018 WHEN NO_DATA_FOUND THEN
2019 if l_debug = 1 then
2020 log_event(l_api_name,' In the no_data_found exception for wms_txn_context_temp',l_api_name);
2021 end if;
2022
2023 IF l_trx_source_line_id <>-999 THEN
2024 if l_debug = 1 then
2025 log_event(l_api_name,' In no_data_found_exception with trx_source_line_id having value',l_api_name);
2026 end if;
2027
2028 SELECT
2029 hz.PARTY_ID,
2030 oola.freight_carrier_code
2031 INTO l_customer_id,
2032 l_freight_code
2033 FROM oe_order_lines_all oola,
2034 HZ_PARTIES hz
2035 WHERE oola.line_id = l_trx_source_line_id
2036 AND hz.party_id = oola.sold_to_org_id;
2037
2038 if l_debug = 1 then
2039 log_event(l_api_name,' Values from the mmtt query: l_customer_id:'||l_customer_id||
2040 ' l_freight_code' || l_freight_code,l_api_name);
2041 end if;
2042
2043 END IF;
2044
2045 END;
2046 x_customer_id := l_customer_id;
2047 x_freight_code := l_freight_code;
2048
2049 Exception
2050 When others then
2051 x_customer_id := NULL;
2052 x_freight_code := NULL;
2053 End get_customer_freight_details;
2054
2055
2056
2057 --*******************************************************************************
2058 /**
2059 This function will returns the location name for a specified locationID
2060 */
2061 Function get_location_name(p_location_id IN NUMBER)
2062 Return VARCHAR2 is
2063
2064 l_location_code VARCHAR2(50);
2065 g_pkg_name constant VARCHAR2(50) := 'WMS_RULES_WORKBENCH_PVT';
2066 l_api_name constant VARCHAR2(30) := 'GET_LOCATION_NAME';
2067 begin
2068 if l_debug = 1 then
2069 log_event(l_api_name,' Enter',l_api_name);
2070 end if;
2071 select location_code into l_location_code
2072 from hr_locations
2073 where location_id=p_location_id;
2074 RETURN l_location_code;
2075 Exception
2076 When others then
2077 l_location_code := NULL;
2078 RETURN l_location_code;
2079 End GET_LOCATION_NAME;
2080
2081
2082 /*
2083 This procedure will search for an appropriate Cross Dock rule(value)
2084 for the input businees object.
2085 Following are the codes for the Crossdock rule Types
2086 For Supply-Initiated Crossdock rule_type_code= 10
2087 For Demand -Initiated Crossdock rule_type_code= 11
2088 */
2089
2090 Procedure cross_dock_search(
2091 p_rule_type_code IN NUMBER,
2092 p_organization_id IN NUMBER,
2093 p_customer_id IN NUMBER,
2094 p_inventory_item_id IN NUMBER,
2095 p_item_type IN VARCHAR,
2096 p_vendor_id IN NUMBER,
2097 p_location_id IN NUMBER,
2098 p_project_id IN NUMBER,
2099 p_task_id IN NUMBER,
2100 p_user_id IN NUMBER,
2101 p_uom_code IN VARCHAR,
2102 p_uom_class IN VARCHAR,
2103 x_return_type OUT NOCOPY VARCHAR2,
2104 x_return_type_id OUT NOCOPY NUMBER, --criterion_id
2105 x_sequence_number OUT NOCOPY NUMBER,
2106 x_return_status OUT NOCOPY VARCHAR2)
2107 is
2108
2109 g_pkg_name constant VARCHAR2(50) := 'WMS_RULES_WORKBENCH_PVT';
2110 l_api_version CONSTANT NUMBER := 1.0;
2111 l_api_name constant VARCHAR2(30) := 'cross_dock_search';
2112 l_debug_mode BOOLEAN := inv_pp_debug.is_debug_mode;
2113
2114 l_sequence_number wms_selection_criteria_txn.sequence_number%type;
2115 l_return_type_code wms_selection_criteria_txn.return_type_code%type;
2116 l_return_type_id wms_selection_criteria_txn.return_type_id%type;
2117 l_msg_count NUMBER;
2118 l_msg_data VARCHAR2(2000);
2119
2120 CURSOR cur_crossdock_value_selection IS
2121 SELECT return_type_code, return_type_id, sequence_number
2122 FROM wms_selection_criteria_txn
2123 WHERE from_organization_id = p_organization_id
2124 AND rule_type_code = p_rule_type_code
2125 AND enabled_flag = 1
2126 AND NVL(customer_id, NVL(p_customer_id, -9)) = NVL(p_customer_id, -9)
2127 AND NVL(inventory_item_id, p_inventory_item_id) = p_inventory_item_id
2128 AND NVL(item_type, NVL(p_item_type, '#')) = NVL(p_item_type, '#')
2129 AND NVL(vendor_id, NVL(p_vendor_id, -9)) = NVL(p_vendor_id, -9)
2130 AND NVL(location_id, NVL(p_location_id, -9)) = NVL(p_location_id, -9)
2131 AND NVL(project_id, NVL(p_project_id, -9)) = NVL(p_project_id, -9)
2132 AND NVL(task_id, NVL(p_task_id, -9)) = NVL(p_task_id, -9)
2133 AND NVL(user_id, NVL(p_user_id, -9)) = NVL(p_user_id, -9)
2134 AND NVL(uom_code, NVL(p_uom_code, '#')) = NVL(p_uom_code, '#')
2135 AND NVL(uom_class, NVL(p_uom_class, '#')) = NVL(p_uom_class, '#')
2136 AND DECODE(abc_class_id, NULL, 'N', 'Y') = DECODE(abc_class_id, NULL,'N',
2137 WMS_RULES_WORKBENCH_PVT.get_group_class(p_inventory_item_id,
2138 assignment_group_id,
2139 abc_class_id))
2140 ORDER BY sequence_number;
2141
2142 BEGIN
2143 -- Initialize the return status
2144 x_return_status := fnd_api.g_ret_sts_success;
2145
2146 OPEN cur_crossdock_value_selection;
2147 FETCH cur_crossdock_value_selection INTO l_return_type_code, l_return_type_id, l_sequence_number;
2148
2149 IF (cur_crossdock_value_selection%NOTFOUND) THEN
2150 l_return_type_code := NULL;
2151 l_return_type_id := NULL;
2152 l_sequence_number := NULL;
2153 ELSE
2154 x_return_type := l_return_type_code;
2155 x_return_type_id := l_return_type_id;
2156 x_sequence_number := l_sequence_number;
2157 END IF;
2158
2159 IF cur_crossdock_value_selection%ISOPEN THEN
2160 CLOSE cur_crossdock_value_selection;
2161 END IF;
2162
2163 IF l_debug = 1 THEN
2164 log_event(l_api_name, 'Cross Dock Type: =====> ', l_return_type_code);
2165 log_event(l_api_name, 'Cross Dock value ID: => ', l_return_type_id);
2166 END IF;
2167
2168 EXCEPTION
2169
2170 WHEN fnd_api.g_exc_error THEN
2171 x_return_status := fnd_api.g_ret_sts_error;
2172 If cur_crossdock_value_selection%ISOPEN then
2173 CLOSE cur_crossdock_value_selection;
2174 End if;
2175
2176 fnd_msg_pub.Count_And_Get
2177 ( p_count => l_msg_count
2178 ,p_data => l_msg_data);
2179 log_error(l_api_name, 'error', 'Error in Cross selection Criteria - ' ||l_msg_data);
2180
2181 WHEN fnd_api.g_exc_unexpected_error THEN
2182 x_return_status := fnd_api.g_ret_sts_unexp_error;
2183 If cur_crossdock_value_selection%ISOPEN then
2184 CLOSE cur_crossdock_value_selection;
2185 End if;
2186 fnd_msg_pub.Count_And_Get
2187 ( p_count => l_msg_count
2188 ,p_data => l_msg_data);
2189 log_error(l_api_name, 'unexp_error', 'Unexpected error ' ||' in selection Criteria - ' || l_msg_data);
2190
2191 WHEN OTHERS THEN
2192 if l_debug = 1 then
2193 log_event(l_api_name,'',' Exception in cross selection Criteria');
2194 end if;
2195 x_return_status := fnd_api.g_ret_sts_unexp_error;
2196
2197 If cur_crossdock_value_selection%ISOPEN then
2198 CLOSE cur_crossdock_value_selection;
2199 End if;
2200 if (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) then
2201 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2202 end if;
2203 End CROSS_DOCK_SEARCH;
2204
2205 --*******************************************************************************
2206
2207
2208 END WMS_RULES_WORKBENCH_PVT;