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