[Home] [Help]
PACKAGE BODY: APPS.OE_ITORD_UTIL
Source
1 PACKAGE BODY OE_ITORD_UTIL AS
2 /* $Header: OEITORDB.pls 120.9.12020000.2 2012/12/04 04:30:22 kadiraju ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ITORD_UTIL';
6
7 --------------------------------------------------------------------------------------
8 --Function get_item_category_id will return the item category_id for the passed inventory_item_id
9 --------------------------------------------------------------------------------------
10 Function get_item_category_id ( p_inventory_item_id IN Number )
11 Return Number IS
12 l_item_category_id Number;
13 begin
14
15 IF NOT OE_GLOBALS.Equal( p_inventory_item_id , G_INVENTORY_ITEM_ID ) then
16 select category_id
17 into l_item_category_id
18 from mtl_item_categories ic,
19 MTL_DEFAULT_CATEGORY_SETS CS
20 where ic.category_set_id=cs.category_set_id
21 AND CS.functional_area_id = 7
22 AND ic.organization_id=oe_sys_parameters.Value('MASTER_ORGANIZATION_ID')
23 AND IC.INVENTORY_ITEM_ID = p_inventory_item_id ;
24
25 G_INVENTORY_ITEM_ID := p_inventory_item_id;
26 G_ITEM_CATEGORY_ID := l_item_category_id;
27
28 return l_item_category_id;
29 ELSE
30 Return G_ITEM_CATEGORY_ID;
31 END IF;
32 Exception
33 when others then
34 return null;
35
36 End get_item_category_id;
37
38 --------------------------------------------------------------------------------------
39 --Function get_customer_class_id will return the customer_class_id for the passed customer_id
40 --------------------------------------------------------------------------------------
41 Function get_customer_class_id ( p_customer_id IN Number )
42 Return Number IS
43 l_customer_class_id Number;
44 begin
45
46 IF NOT OE_GLOBALS.Equal( p_customer_id , G_SOLD_TO_ORG_ID ) then
47
48 SELECT cp.profile_class_id
49 into l_customer_class_id
50 FROM
51 HZ_CUSTOMER_PROFILES cp ,
52 hz_cust_profile_classes cpc
53 WHERE cpc.profile_class_id=cp.PROFILE_CLASS_ID
54 AND cp.site_use_id IS NULL
55 AND cp.cust_account_id= p_customer_id;
56
57 G_SOLD_TO_ORG_ID := p_customer_id;
58 G_CUSTOMER_PROFILE_CLASS_ID := l_customer_class_id;
59
60 return l_customer_class_id;
61 ELSE
62 Return G_CUSTOMER_PROFILE_CLASS_ID;
63 END IF;
64
65 Exception
66 when others then
67 return null;
68 End;
69
70 --------------------------------------------------------------------------------------
71 --Function get_region_id will return the region_id for the passed ship_to_org_id
72 --bug7294798 using WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches to get all matching region_ids
73 --Get all the matching region_id for the current location_id and comma seperate it.This
74 -- comma seperated list of region_ids is used in validation routines and OE_ITEMS_ORD_MV to support rules based on regions
75 --------------------------------------------------------------------------------------
76 Function get_region_ids ( p_ship_to_org_id IN NUMBER)
77 Return varchar2
78 IS
79 l_location_id NUMBER;
80 l_region_id_list varchar2(32000) := ',';
81 l_return_status VARCHAR2(1);
82 l_region_id_tbl WSH_UTIL_CORE.ID_TAB_TYPE;
83 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
84 --15885432 Start
85 Region_Loc_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
86 --15885432 End
87 begin
88
89 IF NOT OE_GLOBALS.Equal( p_ship_to_org_id , G_SHIP_TO_ORGANIZATION_ID ) then
90
91 IF p_ship_to_org_id IS NULL then
92 RETURN NULL;
93 END IF;
94
95 l_location_id := OE_ITORD_UTIL.Get_Shipto_Location_Id(p_ship_to_org_id);
96
97 WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches
98 ( p_location_id => l_location_id,
99 p_use_cache => TRUE,
100 p_lang_code => USERENV('LANG'),
101 x_region_tab => l_region_id_tbl,
102 x_return_status => l_return_status );
103
104 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
105 IF l_debug_level > 0 then
106 oe_debug_pub.add('Unexpected error in WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
107 END IF;
108
109 RETURN NULL;
110 END IF;
111 --15885432 Start
112 -- if there are any regions returned then check following for zone
113 IF l_region_id_tbl.count >0 THEN
114 FOR i IN l_region_id_tbl.FIRST..l_region_id_tbl.LAST LOOP
115
116 WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches(
117 p_region_id => l_region_id_tbl(i),
118 x_zone_tab => Region_Loc_zone_tab,
119 x_return_status => l_return_status);
120
121 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
122 IF l_debug_level > 0 then
123 oe_debug_pub.add('Unexpected error in WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
124 END IF;
125
126 RETURN NULL;
127 END IF;
128 -- get the all values in list with comma seperator.
129 FOR j IN 1..Region_Loc_zone_tab.COUNT LOOP
130 l_region_id_list := l_region_id_list||to_char(Region_Loc_zone_tab(j))||',';
131 END LOOP;
132 END LOOP;
133
134
135 END IF;
136 --15885432 End
137
138
139
140 for i in 1..l_region_id_tbl.count loop
141 l_region_id_list := l_region_id_list||to_char(l_region_id_tbl(i))||',';
142 End loop;
143
144 G_SHIP_TO_ORGANIZATION_ID := p_ship_to_org_id;
145 G_SHIP_TO_REGION_ID_LIST := l_region_id_list;
146
147 return l_region_id_list;
148 ELSE
149 Return G_SHIP_TO_REGION_ID_LIST;
150 END IF;
151 Exception
152 when others then
153 IF l_debug_level > 0 then
154 oe_debug_pub.add('Unexpected error in getting the region_ids :'||SQLERRM);
155 END IF;
156
157 return NULL;
158
159 End get_region_ids;
160
161 ---------------------------------------------------------------------------------------------------------
162 --Function get_customer_category_code will return the Customer Category Code for the given sold_to_org_id
163 ---------------------------------------------------------------------------------------------------------
164 Function get_customer_category_code ( p_customer_id IN NUMBER )
165 Return Varchar2
166 IS
167 l_customer_category_code Varchar2(30);
168 begin
169 IF NOT OE_GLOBALS.Equal( p_customer_id , G_CUST_ID ) then
170 SELECT party.CATEGORY_CODE
171 into l_customer_category_code
172 FROM
173 HZ_CUST_ACCOUNTS cust,
174 HZ_PARTIES party
175 WHERE cust.party_id = party.party_id
176 and cust.cust_account_id = p_customer_id;
177
178 G_CUST_ID := p_customer_id;
179 G_CUST_CATEGORY_CODE := l_customer_category_code;
180
181 return l_customer_category_code;
182 ELSE
183 return G_CUST_CATEGORY_CODE;
184 END IF;
185 Exception
186 when others then
187 return null;
188 END get_customer_category_code;
189
190
191 -----------------------------------------------------------------------------------------------------------
192 --Function get_item_name returns the concatenated_segments for the passed item_id
193 -----------------------------------------------------------------------------------------------------------
194
195 Function get_item_name ( p_inventory_item_id IN NUMBER )
196 RETURN VARCHAR2
197 IS
198 l_item Varchar2 (30000);
199 begin
200 select concatenated_segments
201 into l_item
202 from mtl_system_items_kfv
203 where inventory_item_id = p_inventory_item_id
204 and organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID');
205
206 return l_item;
207
208 Exception
209 when others then
210 return null;
211 End get_item_name;
212
213 -----------------------------------------------------------------------------------------------------------
214 --Function get_item_category_name returns the category concatenated_segments for the passed item_id
215 -----------------------------------------------------------------------------------------------------------
216
217 Function get_item_category_name(p_inventory_item_id IN NUMBER )
218 RETURN VARCHAR2
219 IS
220 l_category Varchar2(30000);
221 begin
222 select concatenated_segments
223 into l_category
224 from mtl_categories_kfv
225 where category_id = OE_ITORD_UTIL.get_item_category_id (p_inventory_item_id);
226
227 return l_category;
228 Exception
229 when others then
230 return null;
231 End get_item_category_name;
232 -----------------------------------------------------------------------------------------------------------
233 --Procedure set_globals will set the global vartiables which will be referenced in the view oe_items_ord_mv
234 -----------------------------------------------------------------------------------------------------------
235 Procedure set_globals (
236 P_CUSTOMER_ID IN NUMBER,
237 P_CUSTOMER_CLASS_ID IN NUMBER,
238 P_CUSTOMER_CATEGORY_CODE IN VARCHAR2,
239 P_REGION_ID_LIST IN VARCHAR2,
240 P_ORDER_TYPE_ID IN NUMBER,
241 P_SHIP_TO_ORG_ID IN NUMBER,
242 P_SALES_CHANNEL_CODE IN VARCHAR2,
243 P_SALESREP_ID IN NUMBER,
244 P_END_CUSTOMER_ID IN NUMBER,
245 P_INVOICE_TO_ORG_ID IN NUMBER,
246 P_DELIVER_TO_ORG_ID IN NUMBER
247 ) IS
248
249 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
250 begin
251
252 IF l_debug_level > 0 then
253 oe_debug_pub.add(' Set Globals - P_CUSTOMER_ID :'||P_CUSTOMER_ID );
254 oe_debug_pub.add(' Set Globals - P_CUSTOMER_CLASS_ID :'||P_CUSTOMER_CLASS_ID );
255 oe_debug_pub.add(' Set Globals - P_CUSTOMER_CATEGORY_CODE :'||P_CUSTOMER_CATEGORY_CODE );
256 oe_debug_pub.add(' Set Globals - P_REGION_ID_LIST :'|| P_REGION_ID_LIST );
257 oe_debug_pub.add(' Set Globals - P_ORDER_TYPE_ID :'||P_ORDER_TYPE_ID );
258 oe_debug_pub.add(' Set Globals - P_SHIP_TO_ORG_ID :'||P_SHIP_TO_ORG_ID );
259 oe_debug_pub.add(' Set Globals - P_SALES_CHANNEL_CODE :'||P_SALES_CHANNEL_CODE);
260 oe_debug_pub.add(' Set Globals - P_SALESREP_ID :'||P_SALESREP_ID );
261 oe_debug_pub.add(' Set Globals - P_END_CUSTOMER_ID :'||P_END_CUSTOMER_ID );
262 oe_debug_pub.add(' Set Globals - P_INVOICE_TO_ORG_ID :'||P_INVOICE_TO_ORG_ID);
263 oe_debug_pub.add(' Set Globals - P_DELIVER_TO_ORG_ID :'||P_DELIVER_TO_ORG_ID);
264 oe_debug_pub.add(' Set Globals - OPERATING_UNIT_ID :'|| mo_global.get_current_org_id);
265 oe_debug_pub.add(' Set Globals - ITEM_VALIDATION_ORG_ID :'|| OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'));
266 END IF;
267
268 G_CUSTOMER_ID := P_CUSTOMER_ID;
269 G_CUSTOMER_CLASS_ID := P_CUSTOMER_CLASS_ID;
270 G_CUSTOMER_CATEGORY_CODE := P_CUSTOMER_CATEGORY_CODE;
271 G_REGION_ID_LIST := P_REGION_ID_LIST; --bug7294798
272 G_ORDER_TYPE_ID := P_ORDER_TYPE_ID;
273 G_SHIP_TO_ORG_ID := P_SHIP_TO_ORG_ID;
274 G_SALES_CHANNEL_CODE := P_SALES_CHANNEL_CODE;
275 G_SALESREP_ID := P_SALESREP_ID;
276 G_END_CUSTOMER_ID := P_END_CUSTOMER_ID;
277 G_INVOICE_TO_ORG_ID := P_INVOICE_TO_ORG_ID;
278 G_DELIVER_TO_ORG_ID := P_DELIVER_TO_ORG_ID;
279
280 G_OPERATING_UNIT_ID := mo_global.get_current_org_id;
281 G_ITEM_VALIDATION_ORG_ID := OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID');
282 Exception
283 when others then
284 null;
285 End set_globals;
286
287
288 Function get_customer_id
289 Return Number
290 IS
291 BEGIN
292 return G_CUSTOMER_ID;
293 END;
294
295
296 Function get_customer_class_id
297 Return number
298 IS
299 BEGIN
300 return G_CUSTOMER_CLASS_ID;
301 END;
302
303 Function get_customer_category_code
304 Return varchar2
305 IS
306 BEGIN
307 return G_CUSTOMER_CATEGORY_CODE;
308 END;
309
310 Function get_region_ids
311 Return VARCHAR2
312 IS
313 BEGIN
314 return G_REGION_ID_LIST;
315 END;
316
317
318 Function get_order_type_id
319 Return Number
320 IS
321 BEGIN
322 return G_ORDER_TYPE_ID;
323 END;
324
325
326 Function get_ship_to_org_id
327 Return Number
328 IS
329 BEGIN
330 return G_SHIP_TO_ORG_ID;
331 END;
332
333 Function get_sales_channel_code
334 Return Varchar2
335 IS
336 BEGIN
337 return G_SALES_CHANNEL_CODE;
338 END;
339
340
341 Function get_salesrep_id
342 Return Number
343 IS
344 BEGIN
345 return G_SALESREP_ID;
346 END;
347
348
349
350 Function get_end_customer_id
351 Return Number
352 IS
353 BEGIN
354 return G_END_CUSTOMER_ID;
355 END;
356
357
358 Function get_invoice_to_org_id
359 Return Number
360 IS
361 BEGIN
362 return G_INVOICE_TO_ORG_ID;
363 END;
364
365
366 Function get_deliver_to_org_id
367 Return Number
368 IS
369 BEGIN
370 return G_DELIVER_TO_ORG_ID;
371 END;
372
373
374
375 Function get_operating_unit_id
376 Return Number
377 IS
378 BEGIN
379 return G_OPERATING_UNIT_ID;
380 END;
381
382
383 Function get_item_validation_org_id
384 Return Number
385 IS
386 BEGIN
387 return G_ITEM_VALIDATION_ORG_ID;
388 END;
389
390
391 --This function is called from process_order (Entity level validation OEXLLINB.pls)
392 --Returns true/false indicating orderable or not
393
394 Function Validate_item_orderability ( p_line_rec in OE_Order_PUB.Line_Rec_Type )
395 Return BOOLEAN IS
396 l_operating_unit_id NUMBER;
397 l_exists varchar2(1);
398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
399
400
401 l_x_item_category_id NUMBER;
402 l_x_customer_class_id NUMBER;
403 l_x_sales_channel_code VARCHAR2(30);
404 l_x_order_type_id NUMBER;
405 l_x_customer_category_code Varchar2(30);
406 l_x_region_id_list VARCHAR2(32000);
407 l_exclusion_rule_exists Varchar2(1);
408
409 begin
410
411 l_operating_unit_id := p_line_rec.org_id;
412 l_x_item_category_id := OE_ITORD_UTIL.get_item_category_id (p_line_rec.inventory_item_id);
413 l_x_customer_class_id := OE_ITORD_UTIL.get_customer_class_id(p_line_rec.sold_to_org_id);
414 l_x_region_id_list := OE_ITORD_UTIL.get_region_ids(p_line_rec.ship_to_org_id);
415 l_x_customer_category_code := OE_ITORD_UTIL.get_customer_category_code (p_line_rec.sold_to_org_id);
416 l_x_order_type_id := OE_ITORD_UTIL.get_order_type_id (p_line_rec.header_id);
417 l_x_sales_channel_code := OE_ITORD_UTIL.get_sales_channel_code(p_line_rec.header_id);
418
419 IF l_debug_level >0 then
420 oe_debug_pub.add('Entering OE_ITORD_UTIL.Validate_item_orderability',1);
421 oe_debug_pub.add('Inventory_item_id :'||p_line_rec.inventory_item_id);
422 oe_debug_pub.add('Item Category Id :'||l_x_item_category_id);
423 oe_debug_pub.add('Customer ID :'|| p_line_rec.sold_to_org_id);
424 oe_debug_pub.add('Customer Class ID :'||l_x_customer_class_id);
425 oe_debug_pub.add('region_id_list :'||l_x_region_id_list);
426 oe_debug_pub.add('order_type_id :'||l_x_order_type_id);
427 oe_debug_pub.add('sales_channel_code :'||l_x_sales_channel_code);
428
429 END IF;
430
431
432 --Validation for Exclusion Rules
433
434 begin
435
436 select 'Y' into l_exists
437 From oe_item_orderability hdr,
438 oe_item_orderability_rules rules
439 Where hdr.orderability_id = rules.orderability_id
440 and hdr.generally_available='Y'
441 and hdr.org_id = l_operating_unit_id
442 and hdr.enable_flag = 'Y'
443 and rules.enable_flag = 'Y'
444 and ( hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
445 and ( rules.customer_id = p_line_rec.sold_to_org_id
446 or rules.customer_class_id = l_x_customer_class_id
447 or rules.customer_category_code = l_x_customer_category_code
448 or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
449 or rules.order_type_id = l_x_order_type_id
450 or rules.ship_to_location_id = p_line_rec.ship_to_org_id
451 or rules.sales_channel_code = l_x_sales_channel_code
452 or rules.sales_person_id = p_line_rec.salesrep_id
453 or rules.end_customer_id = p_line_rec.end_customer_id
454 or rules.bill_to_location_id = p_line_rec.invoice_to_org_id
455 or rules.deliver_to_location_id = p_line_rec.deliver_to_org_id
456 )
457 and rownum = 1;
458
459 l_exclusion_rule_exists := 'Y';
460 return false;
461 Exception
462 when others then
463 l_exclusion_rule_exists := 'N';
464 End;
465
466 -- Validate Inclusion Rules
467 IF l_exclusion_rule_exists = 'N' then
468
469 begin
470
471 select 'Y' into l_exists
472 From oe_item_orderability hdr,
473 oe_item_orderability_rules rules
474 Where hdr.orderability_id = rules.orderability_id
475 and hdr.generally_available='N'
476 and hdr.org_id = l_operating_unit_id
477 and hdr.enable_flag = 'Y'
478 and rules.enable_flag = 'Y'
479 and ( hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
480 and ( rules.customer_id = p_line_rec.sold_to_org_id
481 or rules.customer_class_id = l_x_customer_class_id
482 or rules.customer_category_code = l_x_customer_category_code
483 or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
484 or rules.order_type_id = l_x_order_type_id
485 or rules.ship_to_location_id = p_line_rec.ship_to_org_id
486 or rules.sales_channel_code = l_x_sales_channel_code
487 or rules.sales_person_id = p_line_rec.salesrep_id
488 or rules.end_customer_id = p_line_rec.end_customer_id
489 or rules.bill_to_location_id = p_line_rec.invoice_to_org_id
490 or rules.deliver_to_location_id = p_line_rec.deliver_to_org_id
491 )
492 and rownum = 1;
493
494 return true;
495 Exception
496 when no_data_found then
497 begin
498 --This is to handle the inclusion rule case where only item orderablility header is defined but no rules are defined
499 --Item is generally not available in all cases(since no rule is defeined in detail block)
500
501 select 'Y' into l_exists
502 From oe_item_orderability hdr
503 where hdr.generally_available='N'
504 and hdr.org_id = l_operating_unit_id
505 and hdr.enable_flag = 'Y'
506 and (hdr.inventory_item_id = p_line_rec.inventory_item_id or hdr.item_category_id = l_x_item_category_id )
507 and rownum = 1;
508
509 return false;
510 Exception
511 when no_data_found then
512 return true;
513 END;
514 End;
515
516 END IF;
517
518
519 oe_debug_pub.add('Exiting OE_ITORD_UTIL.Validate_item_orderability',1);
520
521 Exception
522 when others then
523 OE_MSG_PUB.Add_Exc_Msg
524 (
525 G_PKG_NAME
526 ,'Validate_item_orderability'
527 );
528 End Validate_item_orderability;
529
530
531 -- Overloaded Validate_item_orderability
532 -- This will be called from HVOI OE_BULK_PROCESS_LINE.VALIDATE_ITEM_FIELDS (OEBLLINB.pls)
533
534 Function Validate_item_orderability ( p_org_id IN NUMBER,
535 p_line_id IN NUMBER,
536 p_header_id IN NUMBER,
537 p_inventory_item_id IN NUMBER,
538 p_sold_to_org_id IN NUMBER,
539 p_ship_to_org_id IN NUMBER,
540 p_salesrep_id IN NUMBER,
541 p_end_customer_id IN NUMBER,
542 p_invoice_to_org_id IN NUMBER,
543 p_deliver_to_org_id IN NUMBER )
544 Return BOOLEAN IS
545
546 l_x_item_category_id NUMBER;
547 l_x_customer_class_id NUMBER;
548 l_x_sales_channel_code VARCHAR2(30);
549 l_x_order_type_id NUMBER;
550 l_x_region_id_list VARCHAR2(32000);
551 l_x_customer_category_code Varchar2(30);
552
553 l_exists varchar2(1);
554 l_exclusion_rule_exists Varchar2(1);
555
556 begin
557
558
559 l_x_item_category_id := OE_ITORD_UTIL.get_item_category_id (p_inventory_item_id);
560 l_x_customer_class_id := OE_ITORD_UTIL.get_customer_class_id(p_sold_to_org_id);
561 l_x_region_id_list := OE_ITORD_UTIL.get_region_ids(p_ship_to_org_id);
562 l_x_customer_category_code := OE_ITORD_UTIL.get_customer_category_code (p_sold_to_org_id);
563 l_x_order_type_id := OE_ITORD_UTIL.get_order_type_id(p_header_id);
564 l_x_sales_channel_code := OE_ITORD_UTIL.get_sales_channel_code(p_header_id);
565
566
567 --Validation for Exclusion Rules
568
569 begin
570
571 select 'Y' into l_exists
572 From oe_item_orderability hdr,
573 oe_item_orderability_rules rules
574 Where hdr.orderability_id = rules.orderability_id
575 and hdr.generally_available='Y'
576 and hdr.org_id = p_org_id
577 and hdr.enable_flag = 'Y'
578 and rules.enable_flag = 'Y'
579 and ( hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
580 and ( rules.customer_id = p_sold_to_org_id
581 or rules.customer_class_id = l_x_customer_class_id
582 or rules.customer_category_code = l_x_customer_category_code
583 or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
584 or rules.order_type_id = l_x_order_type_id
585 or rules.ship_to_location_id = p_ship_to_org_id
586 or rules.sales_channel_code = l_x_sales_channel_code
587 or rules.sales_person_id = p_salesrep_id
588 or rules.end_customer_id = p_end_customer_id
589 or rules.bill_to_location_id = p_invoice_to_org_id
590 or rules.deliver_to_location_id = p_deliver_to_org_id
591 )
592 and rownum = 1;
593
594 l_exclusion_rule_exists := 'Y';
595 return false;
596 Exception
597 when others then
598 l_exclusion_rule_exists := 'N';
599 End;
600
601 -- Validate Inclusion Rules
602 IF l_exclusion_rule_exists = 'N' then
603
604 begin
605
606 select 'Y' into l_exists
607 From oe_item_orderability hdr,
608 oe_item_orderability_rules rules
609 Where hdr.orderability_id = rules.orderability_id
610 and hdr.generally_available='N'
611 and hdr.org_id = p_org_id
612 and hdr.enable_flag = 'Y'
613 and rules.enable_flag = 'Y'
614 and ( hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
615 and ( rules.customer_id = p_sold_to_org_id
616 or rules.customer_class_id = l_x_customer_class_id
617 or rules.customer_category_code = l_x_customer_category_code
618 or INSTR( l_x_region_id_list ,(','||to_char(rules.region_id)||',') ) <> 0
619 or rules.order_type_id = l_x_order_type_id
620 or rules.ship_to_location_id = p_ship_to_org_id
621 or rules.sales_channel_code = l_x_sales_channel_code
622 or rules.sales_person_id = p_salesrep_id
623 or rules.end_customer_id = p_end_customer_id
624 or rules.bill_to_location_id = p_invoice_to_org_id
625 or rules.deliver_to_location_id = p_deliver_to_org_id
626 )
627 and rownum = 1;
628
629 return true;
630 Exception
631 when no_data_found then
632 begin
633 select 'Y' into l_exists
634 From oe_item_orderability hdr
635 where hdr.generally_available='N'
636 and hdr.org_id = p_org_id
637 and hdr.enable_flag = 'Y'
638 and (hdr.inventory_item_id = p_inventory_item_id or hdr.item_category_id = l_x_item_category_id )
639 and rownum=1;
640
641 return false;
642 Exception
643 when no_data_found then
644 return true;
645 END;
646
647
648 End;
649
650 END IF;
651
652
653 return true;
654
655
656 Exception
657 when others then
658 OE_MSG_PUB.Add_Exc_Msg
659 (
660 G_PKG_NAME
661 ,'Validate_item_orderability'
662 );
663 End Validate_item_orderability;
664
665
666 PROCEDURE Insert_Row
667 ( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
668 , x_return_status OUT NOCOPY VARCHAR2
669 )
670 IS
671 BEGIN
672
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674
675 INSERT INTO OE_ITEM_ORDERABILITY
676 (
677 orderability_id,
678 org_id,
679 item_level,
680 item_category_id,
681 inventory_item_id,
682 generally_available,
683 enable_flag,
684 created_by,
685 creation_date,
686 last_updated_by,
687 last_update_date
688 )
689 VALUES (
690 p_item_orderability_rec.orderability_id,
691 p_item_orderability_rec.org_id,
692 p_item_orderability_rec.item_level,
693 p_item_orderability_rec.item_category_id,
694 p_item_orderability_rec.inventory_item_id,
695 p_item_orderability_rec.generally_available,
696 p_item_orderability_rec.enable_flag,
697 p_item_orderability_rec.created_by,
698 p_item_orderability_rec.creation_date,
699 --to_date(to_char(p_item_orderability_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
700 p_item_orderability_rec.last_updated_by,
701 p_item_orderability_rec.last_update_date
702 --to_date(to_char(p_item_orderability_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
703 );
704
705 EXCEPTION
706 WHEN OTHERS
707 THEN
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709
710 OE_MSG_PUB.Add_Exc_Msg
711 (
712 G_PKG_NAME
713 ,'Insert_Row - OE_ITEM_ORDERABILITY'
714 );
715
716 END;
717
718 PROCEDURE Update_Row
719 ( p_item_orderability_rec IN OE_ITORD_UTIL.Item_Orderability_Rec
720 , x_return_status OUT NOCOPY VARCHAR2
721 )
722 IS
723 BEGIN
724
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726
727 UPDATE OE_ITEM_ORDERABILITY
728 SET item_level = p_item_orderability_rec.item_level,
729 item_category_id = p_item_orderability_rec.item_category_id,
730 inventory_item_id = p_item_orderability_rec.inventory_item_id,
731 generally_available = p_item_orderability_rec.generally_available,
732 enable_flag = p_item_orderability_rec.enable_flag,
733 created_by = p_item_orderability_rec.created_by,
734 creation_date = p_item_orderability_rec.creation_date ,
735 --creation_date = to_date(to_char(p_item_orderability_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
736 last_updated_by = p_item_orderability_rec.last_updated_by,
737 last_update_date = p_item_orderability_rec.last_update_date
738 --last_update_date = to_date(to_char(p_item_orderability_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS')
739 WHERE orderability_id = p_item_orderability_rec.orderability_id;
740
741 EXCEPTION
742 WHEN OTHERS
743 THEN
744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
745
746 OE_MSG_PUB.Add_Exc_Msg
747 (
748 G_PKG_NAME
749 ,'Update_Row - OE_ITEM_ORDERABILITY'
750 );
751
752 END;
753
754
755 PROCEDURE Insert_Row
756 ( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
757 , x_return_status OUT NOCOPY VARCHAR2
758 , x_rowid OUT NOCOPY ROWID
759 )
760 IS
761 BEGIN
762
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764
765 INSERT INTO OE_ITEM_ORDERABILITY_RULES
766 (
767 ORDERABILITY_ID,
768 RULE_LEVEL,
769 CUSTOMER_ID,
770 CUSTOMER_CLASS_ID,
771 CUSTOMER_CATEGORY_CODE,
772 REGION_ID,
773 ORDER_TYPE_ID,
774 SHIP_TO_LOCATION_ID,
775 SALES_CHANNEL_CODE,
776 SALES_PERSON_ID,
777 END_CUSTOMER_ID,
778 BILL_TO_LOCATION_ID,
779 DELIVER_TO_LOCATION_ID,
780 ENABLE_FLAG,
781 CREATED_BY,
782 CREATION_DATE,
783 LAST_UPDATED_BY,
784 LAST_UPDATE_DATE,
785 CONTEXT,
786 ATTRIBUTE1,
787 ATTRIBUTE2,
788 ATTRIBUTE3,
789 ATTRIBUTE4,
790 ATTRIBUTE5,
791 ATTRIBUTE6,
792 ATTRIBUTE7,
793 ATTRIBUTE8,
794 ATTRIBUTE9,
795 ATTRIBUTE10,
796 ATTRIBUTE11,
797 ATTRIBUTE12,
798 ATTRIBUTE13,
799 ATTRIBUTE14,
800 ATTRIBUTE15,
801 ATTRIBUTE16,
802 ATTRIBUTE17,
803 ATTRIBUTE18,
804 ATTRIBUTE19,
805 ATTRIBUTE20
806 )
807 VALUES (
808 p_item_orderability_rules_rec.ORDERABILITY_ID,
809 p_item_orderability_rules_rec.RULE_LEVEL,
810 p_item_orderability_rules_rec.CUSTOMER_ID,
811 p_item_orderability_rules_rec.CUSTOMER_CLASS_ID,
812 p_item_orderability_rules_rec.CUSTOMER_CATEGORY_CODE,
813 p_item_orderability_rules_rec.REGION_ID,
814 p_item_orderability_rules_rec.ORDER_TYPE_ID,
815 p_item_orderability_rules_rec.SHIP_TO_LOCATION_ID,
816 p_item_orderability_rules_rec.SALES_CHANNEL_CODE,
817 p_item_orderability_rules_rec.SALES_PERSON_ID,
818 p_item_orderability_rules_rec.END_CUSTOMER_ID,
819 p_item_orderability_rules_rec.BILL_TO_LOCATION_ID,
820 p_item_orderability_rules_rec.DELIVER_TO_LOCATION_ID,
821 p_item_orderability_rules_rec.ENABLE_FLAG,
822 p_item_orderability_rules_rec.CREATED_BY,
823 p_item_orderability_rules_rec.creation_date,
824 --to_date(to_char(p_item_orderability_rules_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
825 p_item_orderability_rules_rec.last_updated_by,
826 p_item_orderability_rules_rec.last_update_date,
827 --to_date(to_char(p_item_orderability_rules_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
828 p_item_orderability_rules_rec.CONTEXT,
829 p_item_orderability_rules_rec.ATTRIBUTE1,
830 p_item_orderability_rules_rec.ATTRIBUTE2,
831 p_item_orderability_rules_rec.ATTRIBUTE3,
832 p_item_orderability_rules_rec.ATTRIBUTE4,
833 p_item_orderability_rules_rec.ATTRIBUTE5,
834 p_item_orderability_rules_rec.ATTRIBUTE6,
835 p_item_orderability_rules_rec.ATTRIBUTE7,
836 p_item_orderability_rules_rec.ATTRIBUTE8,
837 p_item_orderability_rules_rec.ATTRIBUTE9,
838 p_item_orderability_rules_rec.ATTRIBUTE10,
839 p_item_orderability_rules_rec.ATTRIBUTE11,
840 p_item_orderability_rules_rec.ATTRIBUTE12,
841 p_item_orderability_rules_rec.ATTRIBUTE13,
842 p_item_orderability_rules_rec.ATTRIBUTE14,
843 p_item_orderability_rules_rec.ATTRIBUTE15,
844 p_item_orderability_rules_rec.ATTRIBUTE16,
845 p_item_orderability_rules_rec.ATTRIBUTE17,
846 p_item_orderability_rules_rec.ATTRIBUTE18,
847 p_item_orderability_rules_rec.ATTRIBUTE19,
848 p_item_orderability_rules_rec.ATTRIBUTE20
849 ) returning rowid into x_rowid ;
850 EXCEPTION
851 WHEN OTHERS
852 THEN
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
854
855 OE_MSG_PUB.Add_Exc_Msg
856 (
857 G_PKG_NAME
858 ,'Insert_Row - OE_ITEM_ORDERABILITY_RULES'
859 );
860
861 END;
862
863 PROCEDURE Update_Row
864 ( p_item_orderability_rules_rec IN OE_ITORD_UTIL.Item_Orderability_Rules_Rec
865 , p_row_id IN ROWID
866 , x_return_status OUT NOCOPY VARCHAR2
867 )
868 IS
869 BEGIN
870
871 x_return_status := FND_API.G_RET_STS_SUCCESS;
872
873 UPDATE OE_ITEM_ORDERABILITY_RULES
874 SET RULE_LEVEL = p_item_orderability_rules_rec.RULE_LEVEL,
875 CUSTOMER_ID = p_item_orderability_rules_rec.CUSTOMER_ID,
876 CUSTOMER_CLASS_ID = p_item_orderability_rules_rec.CUSTOMER_CLASS_ID,
877 CUSTOMER_CATEGORY_CODE = p_item_orderability_rules_rec.CUSTOMER_CATEGORY_CODE,
878 REGION_ID = p_item_orderability_rules_rec.REGION_ID,
879 ORDER_TYPE_ID = p_item_orderability_rules_rec.ORDER_TYPE_ID,
880 SHIP_TO_LOCATION_ID = p_item_orderability_rules_rec.SHIP_TO_LOCATION_ID,
881 SALES_CHANNEL_CODE = p_item_orderability_rules_rec.SALES_CHANNEL_CODE,
882 SALES_PERSON_ID = p_item_orderability_rules_rec.SALES_PERSON_ID,
883 END_CUSTOMER_ID = p_item_orderability_rules_rec.END_CUSTOMER_ID,
884 BILL_TO_LOCATION_ID = p_item_orderability_rules_rec.BILL_TO_LOCATION_ID,
885 DELIVER_TO_LOCATION_ID = p_item_orderability_rules_rec.DELIVER_TO_LOCATION_ID,
886 ENABLE_FLAG = p_item_orderability_rules_rec.ENABLE_FLAG,
887 CREATED_BY = p_item_orderability_rules_rec.CREATED_BY,
888 CREATION_DATE = p_item_orderability_rules_rec.creation_date,
889 --CREATION_DATE = to_date(to_char(p_item_orderability_rules_rec.creation_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
890 LAST_UPDATED_BY = p_item_orderability_rules_rec.last_updated_by,
891 LAST_UPDATE_DATE = p_item_orderability_rules_rec.last_update_date,
892 --LAST_UPDATE_DATE = to_date(to_char(p_item_orderability_rules_rec.last_update_date,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS'),
893 CONTEXT = p_item_orderability_rules_rec.CONTEXT,
894 ATTRIBUTE1 = p_item_orderability_rules_rec.ATTRIBUTE1,
895 ATTRIBUTE2 = p_item_orderability_rules_rec.ATTRIBUTE2,
896 ATTRIBUTE3 = p_item_orderability_rules_rec.ATTRIBUTE3,
897 ATTRIBUTE4 = p_item_orderability_rules_rec.ATTRIBUTE4,
898 ATTRIBUTE5 = p_item_orderability_rules_rec.ATTRIBUTE5,
899 ATTRIBUTE6 = p_item_orderability_rules_rec.ATTRIBUTE6,
900 ATTRIBUTE7 = p_item_orderability_rules_rec.ATTRIBUTE7,
901 ATTRIBUTE8 = p_item_orderability_rules_rec.ATTRIBUTE8,
902 ATTRIBUTE9 = p_item_orderability_rules_rec.ATTRIBUTE9,
903 ATTRIBUTE10 = p_item_orderability_rules_rec.ATTRIBUTE10,
904 ATTRIBUTE11 = p_item_orderability_rules_rec.ATTRIBUTE11,
905 ATTRIBUTE12 = p_item_orderability_rules_rec.ATTRIBUTE12,
906 ATTRIBUTE13 = p_item_orderability_rules_rec.ATTRIBUTE13,
907 ATTRIBUTE14 = p_item_orderability_rules_rec.ATTRIBUTE14,
908 ATTRIBUTE15 = p_item_orderability_rules_rec.ATTRIBUTE15,
909 ATTRIBUTE16 = p_item_orderability_rules_rec.ATTRIBUTE16,
910 ATTRIBUTE17 = p_item_orderability_rules_rec.ATTRIBUTE17,
911 ATTRIBUTE18 = p_item_orderability_rules_rec.ATTRIBUTE18,
912 ATTRIBUTE19 = p_item_orderability_rules_rec.ATTRIBUTE19,
913 ATTRIBUTE20 = p_item_orderability_rules_rec.ATTRIBUTE20
914 WHERE ROWID = p_row_id;
915
916 EXCEPTION
917 WHEN OTHERS
918 THEN
919 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
920
921 OE_MSG_PUB.Add_Exc_Msg
922 (
923 G_PKG_NAME
924 ,'Update_Row - OE_ITEM_ORDERABILITY_RULES'
925 );
926
927 END;
928
929
930 FUNCTION Check_Duplicate_Rules (l_sql_stmt varchar2)
931 RETURN BOOLEAN
932 IS
933 l_dup_count number(1);
934 BEGIN
935 EXECUTE IMMEDIATE l_sql_stmt INTO l_dup_count;
936
937 IF l_dup_count > 0
938 THEN
939 RETURN FALSE;
940 END IF;
941
942 RETURN TRUE;
943 END;
944
945
946 Procedure REFRESH_MATERIALIZED_VIEW
947 (
948 ERRBUF OUT NOCOPY VARCHAR2,
949 RETCODE OUT NOCOPY VARCHAR2
950 )
951 IS
952 BEGIN
953 DBMS_MVIEW.REFRESH(list => 'OE_ITEMS_MV');
954
955 RETCODE := 0;
956 END refresh_materialized_view;
957
958 FUNCTION GET_RULE_LEVEL_VALUE ( P_RULE_LEVEL varchar2
959 , P_RULE_LEVEL_VALUE varchar2
960 )
961 RETURN VARCHAR2
962 IS
963 l_rule_level_id NUMBER;
964 l_rule_level_value varchar2(200);
965 BEGIN
966 oe_debug_pub.add('Rule Level: '||P_RULE_LEVEL);
967 oe_debug_pub.add('Rule Level Value: '||P_RULE_LEVEL_VALUE);
968
969 IF p_rule_level IN('CUSTOMER','END_CUST')
970 THEN
971
972 l_rule_level_id := TO_NUMBER(p_rule_level_value);
973
974 SELECT party.party_name
975 INTO l_rule_level_value
976 FROM hz_parties party,
977 hz_cust_accounts acct
978 WHERE acct.party_id = party.party_id
979 AND acct.cust_account_id = l_rule_level_id;
980
981 ELSIF p_rule_level = 'CUST_CLASS'
982 THEN
983 l_rule_level_id := TO_NUMBER(p_rule_level_value);
984
985 SELECT cpc.name
986 INTO l_rule_level_value
987 FROM hz_cust_profile_classes cpc
988 WHERE profile_class_id = l_rule_level_id;
989
990 ELSIF p_rule_level = 'CUST_CATEGORY'
991 THEN
992
993 SELECT meaning
994 INTO l_rule_level_value
995 FROM ar_lookups
996 WHERE lookup_type = 'CUSTOMER_CATEGORY'
997 AND lookup_code = p_rule_level_value;
998
999 ELSIF p_rule_level = 'REGIONS'
1000 THEN
1001 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1002
1003 SELECT country || ', '||state||', '||city||', '||ZONE|| ', '||postal_code_from || ' - '||postal_code_to region
1004 INTO l_rule_level_value
1005 FROM wsh_regions_v
1006 WHERE region_id = l_rule_level_id;
1007
1008 ELSIF p_rule_level = 'ORDER_TYPE'
1009 THEN
1010 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1011
1012 SELECT name
1013 INTO l_rule_level_value
1014 FROM oe_order_types_v
1015 WHERE order_type_id = l_rule_level_id;
1016
1017 ELSIF p_rule_level = 'SALES_CHANNEL'
1018 THEN
1019
1020 SELECT meaning
1021 INTO l_rule_level_value
1022 FROM oe_lookups
1023 WHERE lookup_type = 'SALES_CHANNEL'
1024 AND lookup_code = p_rule_level_value;
1025
1026 ELSIF p_rule_level = 'SALES_REP'
1027 THEN
1028
1029 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1030
1031 SELECT name
1032 INTO l_rule_level_value
1033 FROM ra_salesreps
1034 WHERE salesrep_id = l_rule_level_id;
1035
1036 ELSIF p_rule_level = 'SHIP_TO_LOC'
1037 THEN
1038
1039 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1040
1041 SELECT site.location
1042 INTO l_rule_level_value
1043 FROM hz_cust_site_uses_all site
1044 WHERE site.site_use_code = 'SHIP_TO'
1045 AND site.site_use_id= l_rule_level_id;
1046
1047 ELSIF p_rule_level = 'BILL_TO_LOC'
1048 THEN
1049
1050 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1051
1052 SELECT site.location
1053 INTO l_rule_level_value
1054 FROM hz_cust_site_uses_all site
1055 WHERE site.site_use_code = 'BILL_TO'
1056 AND site.site_use_id= l_rule_level_id;
1057
1058 ELSIF p_rule_level = 'DELIVER_TO_LOC'
1059 THEN
1060
1061 l_rule_level_id := TO_NUMBER(p_rule_level_value);
1062
1063 SELECT site.location
1064 INTO l_rule_level_value
1065 FROM hz_cust_site_uses_all site
1066 WHERE site.site_use_code = 'DELIVER_TO'
1067 AND site.site_use_id= l_rule_level_id;
1068 ELSE
1069 l_rule_level_value := 'Invalid rule passed';
1070 END IF;
1071
1072 RETURN l_rule_level_value;
1073
1074 END;
1075
1076 --Returns hz_locations.location_id for the given site_use_id
1077 FUNCTION Get_Shipto_Location_Id
1078 (p_site_use_id IN NUMBER
1079 )
1080 RETURN NUMBER
1081 IS
1082 l_ship_to_location_id NUMBER;
1083 BEGIN
1084
1085 SELECT loc.location_id
1086 INTO l_ship_to_location_id
1087 FROM hz_cust_site_uses_all site_uses,
1088 hz_cust_acct_sites_all acct_site,
1089 hz_party_sites party_site,
1090 hz_locations loc
1091 WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
1092 AND acct_site.party_site_id = party_site.party_site_id
1093 AND loc.location_id = party_site.location_id
1094 AND site_uses.site_use_code = 'SHIP_TO'
1095 AND site_uses.site_use_id = p_site_use_id;
1096
1097 RETURN l_ship_to_location_id;
1098
1099 EXCEPTION
1100 WHEN NO_DATA_FOUND THEN
1101 RETURN NULL;
1102 END Get_Shipto_Location_Id;
1103
1104
1105 Function get_order_type_id (p_header_id IN NUMBER)
1106 Return Number
1107 is
1108 l_order_type_id NUMBER;
1109 begin
1110 IF NOT OE_GLOBALS.Equal( p_header_id , G_HEADER_ID ) then
1111 select order_type_id
1112 into l_order_type_id
1113 from oe_order_headers_all
1114 where header_id = p_header_id;
1115
1116 G_HEADER_ID := p_header_id;
1117 G_TRX_TYPE_ID := l_order_type_id;
1118
1119 RETURN l_order_type_id;
1120 ELSE
1121 RETURN G_TRX_TYPE_ID;
1122
1123 END IF;
1124
1125
1126 Exception
1127 when others then
1128 RETURN NULL;
1129 End get_order_type_id;
1130
1131
1132 Function get_sales_channel_code (p_header_id IN NUMBER)
1133 Return VARCHAR2
1134 is
1135 l_sales_channel_code VARCHAR2(30);
1136 begin
1137
1138 IF NOT OE_GLOBALS.Equal( p_header_id , G_HDR_ID ) then
1139 select sales_channel_code
1140 into l_sales_channel_code
1141 from oe_order_headers_all
1142 where header_id = p_header_id;
1143
1144 G_HDR_ID := p_header_id;
1145 G_SC_CODE := l_sales_channel_code;
1146
1147 RETURN l_sales_channel_code;
1148 ELSE
1149 RETURN G_SC_CODE;
1150 END IF;
1151
1152 Exception
1153 when others then
1154 RETURN NULL;
1155 End get_sales_channel_code;
1156
1157
1158
1159 END OE_ITORD_UTIL;