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