[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_CACHE
Source
1 PACKAGE BODY OE_BULK_CACHE AS
2 /* $Header: OEBUCCHB.pls 120.5.12010000.4 2008/11/30 21:45:55 smusanna ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_CACHE';
5
6 PROCEDURE Get_Address(
7 p_address_type_in IN VARCHAR2,
8 p_org_id_in IN NUMBER,
9 p_address_id_in IN NUMBER,
10 p_tp_location_code_in IN VARCHAR2,
11 p_tp_translator_code_in IN VARCHAR2,
12 l_addr1 OUT NOCOPY VARCHAR2,
13
14 l_addr2 OUT NOCOPY VARCHAR2,
15
16 l_addr3 OUT NOCOPY VARCHAR2,
17
18 l_addr4 OUT NOCOPY VARCHAR2,
19
20 l_addr_alt OUT NOCOPY VARCHAR2,
21
22 l_city OUT NOCOPY VARCHAR2,
23
24 l_county OUT NOCOPY VARCHAR2,
25
26 l_state OUT NOCOPY VARCHAR2,
27
28 l_zip OUT NOCOPY VARCHAR2,
29
30 l_province OUT NOCOPY VARCHAR2,
31
32 l_country OUT NOCOPY VARCHAR2,
33
34 l_region1 OUT NOCOPY VARCHAR2,
35
36 l_region2 OUT NOCOPY VARCHAR2,
37
38 l_region3 OUT NOCOPY VARCHAR2,
39
40 x_return_status OUT NOCOPY VARCHAR2)
41
42 IS
43
44 l_entity_id NUMBER;
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(80);
47 l_status_code NUMBER;
48 l_return_status VARCHAR2(20);
49 l_address_type NUMBER;
50 l_org_id NUMBER;
51 l_tp_location_code VARCHAR2(3200);
52 l_tp_translator_code VARCHAR2(3200);
53 l_tp_location_name VARCHAR2(3200);
54 l_addr_id VARCHAR2(3200);
55
56 --
57 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
58 --
59 BEGIN
60 IF l_debug_level > 0 THEN
61 oe_debug_pub.add( 'ADDRESS TYPE = '||P_ADDRESS_TYPE_IN ) ;
62 END IF;
63 IF l_debug_level > 0 THEN
64 oe_debug_pub.add( 'ORG = '||P_ORG_ID_IN ) ;
65 END IF;
66 IF l_debug_level > 0 THEN
67 oe_debug_pub.add( 'ADDRESS ID = '||P_ADDRESS_ID_IN ) ;
68 END IF;
69
70 IF p_address_type_in = 'CUSTOMER' THEN
71 l_address_type := 1;
72 ELSIF p_address_type_in = 'HR_LOCATION' THEN
73 l_address_type := 2;
74 END IF;
75
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'BEFORE CALLING EC ADDRESS DERIVATION API' ) ;
78 END IF;
79 ece_trading_partners_pub.ece_Get_Address_wrapper(
80 p_api_version_number => 1.0,
81 x_return_status => l_return_status,
82 x_msg_count => l_msg_count,
83 x_msg_data => l_msg_data,
84 x_status_code => l_status_code,
85 p_address_type => l_address_type,
86 p_transaction_type => 'POAO',
87 p_org_id_in => p_org_id_in,
88 p_address_id_in => p_address_id_in,
89 p_tp_location_code_in => p_tp_location_code_in,
90 p_translator_code_in => p_tp_translator_code_in,
91 p_tp_location_name_in => l_tp_location_name,
92 p_address_line1_in => l_addr1,
93 p_address_line2_in => l_addr2,
94 p_address_line3_in => l_addr3,
95 p_address_line4_in => l_addr4,
96 p_address_line_alt_in => l_addr_alt,
97 p_city_in => l_city,
98 p_county_in => l_county,
99 p_state_in => l_state,
100 p_zip_in => l_zip,
101 p_province_in => l_province,
102 p_country_in => l_country,
103 p_region_1_in => l_region1,
104 p_region_2_in => l_region2,
105 p_region_3_in => l_region3,
106 x_entity_id_out => l_entity_id,
107 x_org_id_out => l_org_id,
108 x_address_id_out => l_addr_id,
109 x_tp_location_code_out => l_tp_location_code,
110 x_translator_code_out => l_tp_translator_code,
111 x_tp_location_name_out => l_tp_location_name,
112 x_address_line1_out => l_addr1,
113 x_address_line2_out => l_addr2,
114 x_address_line3_out => l_addr3,
115 x_address_line4_out => l_addr4,
116 x_address_line_alt_out => l_addr_alt,
117 x_city_out => l_city,
118 x_county_out => l_county,
119 x_state_out => l_state,
120 x_zip_out => l_zip,
121 x_province_out => l_province,
122 x_country_out => l_country,
123 x_region_1_out => l_region1,
124 x_region_2_out => l_region2,
125 x_region_3_out => l_region3);
126
127 IF l_debug_level > 0 THEN
128 oe_debug_pub.add( 'ADDR1 = '||SUBSTR ( L_ADDR1 , 0 , 240 ) ) ;
129 END IF;
130 IF l_debug_level > 0 THEN
131 oe_debug_pub.add( 'CITY = '||L_CITY ) ;
132 END IF;
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add( 'ZIP = '||L_ZIP ) ;
135 END IF;
136 IF l_debug_level > 0 THEN
137 oe_debug_pub.add( 'COUNTRY = '||L_COUNTRY ) ;
138 END IF;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
144 THEN
145 OE_BULK_MSG_PUB.Add_Exc_Msg
146 (G_PKG_NAME, 'Get_Address');
147 END IF;
148 END Get_Address;
149
150
151 FUNCTION Load_Order_Type
152 ( p_key IN NUMBER
153 , p_default_attributes IN VARCHAR2
154 )
155 RETURN NUMBER
156 IS
157 --
158 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
159 --
160 BEGIN
161
162 IF G_ORDER_TYPE_TBL.EXISTS(p_key)
163 AND ( p_default_attributes = 'N'
164 OR (p_default_attributes = 'Y'
165 AND G_ORDER_TYPE_TBL(p_key).default_attributes = 'Y'))
166 THEN
167
168 RETURN p_key;
169
170 END IF;
171
172
173 IF p_default_attributes = 'Y' THEN
174
175 SELECT o.transaction_type_id
176 ,otl.name
177 ,o.order_category_code
178 ,o.warehouse_id
179 ,o.agreement_required_flag
180 ,o.po_required_flag
181 ,o.entry_credit_check_rule_id
182 ,o.start_date_active
183 ,o.end_date_active
184 ,i.rule_id
185 ,a.rule_id
186 ,pl.list_header_id
187 ,sp.lookup_code
188 ,sm.lookup_code
189 ,fp.lookup_code
190 ,ft.lookup_code
191 ,dc.lookup_code
192 ,lt.transaction_type_id
193 ,o.conversion_type_code
194 ,o.tax_calculation_event_code
195 ,o.auto_scheduling_flag
196 ,o.scheduling_level_code
197 ,'Y'
198 ,rl.QUICK_CR_CHECK_FLAG
199 ,rtrx.tax_calculation_flag
200 ,o.cust_trx_type_id
201 INTO G_ORDER_TYPE_TBL(p_key).order_type_id
202 ,G_ORDER_TYPE_TBL(p_key).name
203 ,G_ORDER_TYPE_TBL(p_key).order_category_code
204 ,G_ORDER_TYPE_TBL(p_key).ship_from_org_id
205 ,G_ORDER_TYPE_TBL(p_key).agreement_required_flag
206 ,G_ORDER_TYPE_TBL(p_key).require_po_flag
207 ,G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id
208 ,G_ORDER_TYPE_TBL(p_key).start_date_active
209 ,G_ORDER_TYPE_TBL(p_key).end_date_active
210 ,G_ORDER_TYPE_TBL(p_key).invoicing_rule_id
211 ,G_ORDER_TYPE_TBL(p_key).accounting_rule_id
212 ,G_ORDER_TYPE_TBL(p_key).price_list_id
213 ,G_ORDER_TYPE_TBL(p_key).shipment_priority_code
214 ,G_ORDER_TYPE_TBL(p_key).shipping_method_code
215 ,G_ORDER_TYPE_TBL(p_key).fob_point_code
216 ,G_ORDER_TYPE_TBL(p_key).freight_terms_code
217 ,G_ORDER_TYPE_TBL(p_key).demand_class_code
218 ,G_ORDER_TYPE_TBL(p_key).default_outbound_line_type_id
219 ,G_ORDER_TYPE_TBL(p_key).conversion_type_code
220 ,G_ORDER_TYPE_TBL(p_key).tax_calculation_event
221 ,G_ORDER_TYPE_TBL(p_key).auto_scheduling_flag
222 ,G_ORDER_TYPE_TBL(p_key).scheduling_level_code
223 ,G_ORDER_TYPE_TBL(p_key).default_attributes
224 ,G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag
225 ,G_ORDER_TYPE_TBL(p_key).tax_calculation_flag
226 ,G_ORDER_TYPE_TBL(p_key).cust_trx_type_id
227 FROM oe_transaction_types_all o
228 ,oe_transaction_types_tl otl
229 ,oe_ra_rules_v i
230 ,oe_ra_rules_v a
231 ,qp_list_headers_vl pl
232 ,oe_lookups sp
233 ,oe_ship_methods_v sm
234 ,oe_ar_lookups_v fp
235 ,oe_lookups ft
236 ,oe_fnd_common_lookups_v dc
237 ,oe_transaction_types_all lt
238 ,oe_credit_check_rules rl
239 ,ra_cust_trx_types rtrx
240 WHERE o.transaction_type_id = p_key
241 AND o.invoicing_rule_id = i.rule_id(+)
242 AND i.status(+) = 'A'
243 AND i.type(+) = 'I'
244 AND o.accounting_rule_id = a.rule_id(+)
245 AND a.status(+) = 'A'
246 AND a.type(+) = 'A'
247 AND o.price_list_id = pl.list_header_id(+)
248 AND nvl(pl.active_flag(+),'Y') = 'Y'
249 AND o.shipment_priority_code = sp.lookup_code(+)
250 AND sp.lookup_type(+) = 'SHIPMENT_PRIORITY'
251 AND sp.enabled_flag(+) = 'Y'
252 AND sysdate between nvl(sp.start_date_active(+),sysdate)
253 and nvl(sp.end_date_active(+),sysdate)
254 AND o.shipping_method_code = sm.lookup_code(+)
255 AND sm.lookup_type(+) = 'SHIP_METHOD'
256 AND sm.enabled_flag(+) = 'Y'
257 AND sysdate between nvl(sm.start_date_active(+),sysdate)
258 and nvl(sm.end_date_active(+),sysdate)
259 AND o.fob_point_code = fp.lookup_code(+)
260 AND fp.lookup_type(+) = 'FOB'
261 AND fp.enabled_flag(+) = 'Y'
262 AND sysdate between nvl(fp.start_date_active(+),sysdate)
263 and nvl(fp.end_date_active(+),sysdate)
264 AND o.freight_terms_code = ft.lookup_code(+)
265 AND ft.lookup_type(+) = 'FREIGHT_TERMS'
266 AND ft.enabled_flag(+) = 'Y'
267 AND sysdate between nvl(ft.start_date_active(+),sysdate)
268 and nvl(ft.end_date_active(+),sysdate)
269 AND o.demand_class_code = dc.lookup_code(+)
270 AND dc.lookup_type(+) = 'DEMAND_CLASS'
271 AND dc.enabled_flag(+) = 'Y'
272 AND sysdate between nvl(dc.start_date_active(+),sysdate)
273 and nvl(dc.end_date_active(+),sysdate)
274 AND lt.transaction_type_id(+) = o.default_outbound_line_type_id
275 AND sysdate between nvl(lt.start_date_active(+),sysdate)
276 and nvl(lt.end_date_active(+),sysdate)
277 AND otl.transaction_type_id = o.transaction_type_id
278 AND otl.language = userenv('LANG')
279 AND o.entry_credit_check_rule_id = rl.credit_check_rule_id(+)
280 AND o.cust_trx_type_id = rtrx.cust_trx_type_id(+)
281 AND sysdate between nvl(rl.start_date_active(+),sysdate)
282 and nvl(rl.end_date_active(+),sysdate);
283
284
285 ELSE
286
287 SELECT o.transaction_type_id
288 ,otl.name
289 ,o.order_category_code
290 ,o.warehouse_id
291 ,o.agreement_required_flag
292 ,o.po_required_flag
293 ,o.entry_credit_check_rule_id
294 ,o.start_date_active
295 ,o.end_date_active
296 ,o.tax_calculation_event_code
297 ,o.auto_scheduling_flag
298 ,o.scheduling_level_code
299 ,rl.quick_cr_check_flag
300 ,rtrx.tax_calculation_flag
301 ,o.cust_trx_type_id
302 INTO G_ORDER_TYPE_TBL(p_key).order_type_id
303 ,G_ORDER_TYPE_TBL(p_key).name
304 ,G_ORDER_TYPE_TBL(p_key).order_category_code
305 ,G_ORDER_TYPE_TBL(p_key).ship_from_org_id
306 ,G_ORDER_TYPE_TBL(p_key).agreement_required_flag
307 ,G_ORDER_TYPE_TBL(p_key).require_po_flag
308 ,G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id
309 ,G_ORDER_TYPE_TBL(p_key).start_date_active
310 ,G_ORDER_TYPE_TBL(p_key).end_date_active
311 ,G_ORDER_TYPE_TBL(p_key).tax_calculation_event
312 ,G_ORDER_TYPE_TBL(p_key).auto_scheduling_flag
313 ,G_ORDER_TYPE_TBL(p_key).scheduling_level_code
314 ,G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag
315 ,G_ORDER_TYPE_TBL(p_key).tax_calculation_flag
316 ,G_ORDER_TYPE_TBL(p_key).cust_trx_type_id
317 FROM OE_TRANSACTION_TYPES_ALL o
318 ,oe_transaction_types_tl otl
319 ,oe_credit_check_rules rl
320 ,ra_cust_trx_types rtrx
321 WHERE o.transaction_type_id = p_key
322 AND otl.transaction_type_id = o.transaction_type_id
323 AND otl.language = userenv('LANG')
324 AND o.entry_credit_check_rule_id = rl.credit_check_rule_id(+)
325 AND o.cust_trx_type_id = rtrx.cust_trx_type_id(+)
326 AND sysdate between nvl(rl.start_date_active(+),sysdate)
327 and nvl(rl.end_date_active(+),sysdate);
328
329 END IF;
330
331 -- Set the Global OE_BULK_ORDER_PVT.G_CC_REQUIRED if the cc rule exists
332 -- for one of the Order Types in a batch
333
334 IF G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id IS NOT NULL
335 THEN
336 IF OE_BULK_ORDER_PVT.G_CC_REQUIRED = 'N' THEN
337 OE_BULK_ORDER_PVT.G_CC_REQUIRED := 'Y';
338 END IF;
339
340 -- Set the Global OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED if any one
341 -- order in a batch requires real time credit checking. If this flag
342 -- is set then orders will get inserted with booked_flag = 'N' to allow
343 -- Real Time CC to happen order by order.
344
345 IF ( G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag IS NULL OR
346 G_ORDER_TYPE_TBL(p_key).quick_cr_check_flag = 'N')
347 THEN
348 IF OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED = 'N' THEN
349 OE_BULK_ORDER_PVT.G_REALTIME_CC_REQUIRED := 'Y';
350 END IF;
351 END IF;
352 END IF;
353
354
355 RETURN p_key;
356
357 EXCEPTION
358 WHEN NO_DATA_FOUND THEN
359 IF G_ORDER_TYPE_TBL.EXISTS(p_key) THEN
360 IF l_debug_level > 0 THEN
361 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
362 END IF;
363 G_ORDER_TYPE_TBL.DELETE(p_key);
364 END IF;
365 RAISE NO_DATA_FOUND;
366 WHEN OTHERS THEN
367 IF G_ORDER_TYPE_TBL.EXISTS(p_key) THEN
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
370 END IF;
371 G_ORDER_TYPE_TBL.DELETE(p_key);
372 END IF;
373 OE_BULK_MSG_PUB.Add_Exc_Msg
374 ( G_PKG_NAME
375 ,'Load_Order_Type'
376 );
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END Load_Order_Type;
379
380 FUNCTION Load_Line_Type
381 ( p_key IN NUMBER
382 , p_default_attributes IN VARCHAR2
383 )
384 RETURN NUMBER
385 IS
386 --
387 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
388 --
389 BEGIN
390
391 IF G_LINE_TYPE_TBL.EXISTS(p_key) THEN
392
393 RETURN p_key;
394
395 END IF;
396
397 SELECT /*+ PUSH_PRED(ct) */ o.transaction_type_id
398 ,o.order_category_code
399 ,o.start_date_active
400 ,o.end_date_active
401 ,o.cust_trx_type_id
402 ,ct.tax_calculation_flag
403 ,o.scheduling_level_code
404 INTO G_LINE_TYPE_TBL(p_key).line_type_id
405 ,G_LINE_TYPE_TBL(p_key).order_category_code
406 ,G_LINE_TYPE_TBL(p_key).start_date_active
407 ,G_LINE_TYPE_TBL(p_key).end_date_active
408 ,G_LINE_TYPE_TBL(p_key).cust_trx_type_id
409 ,G_LINE_TYPE_TBL(p_key).tax_calculation_flag
410 ,G_LINE_TYPE_TBL(p_key).scheduling_level_code
411 FROM oe_transaction_types_all o
412 ,ra_cust_trx_types ct
413 WHERE o.transaction_type_id = p_key
414 AND o.cust_trx_type_id = ct.cust_trx_type_id(+);
415
416 RETURN p_key;
417
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 IF G_LINE_TYPE_TBL.EXISTS(p_key) THEN
421 IF l_debug_level > 0 THEN
422 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
423 END IF;
424 G_LINE_TYPE_TBL.DELETE(p_key);
425 END IF;
426 RAISE NO_DATA_FOUND;
427 WHEN OTHERS THEN
428 IF G_LINE_TYPE_TBL.EXISTS(p_key) THEN
429 IF l_debug_level > 0 THEN
430 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
431 END IF;
432 G_LINE_TYPE_TBL.DELETE(p_key);
433 END IF;
434 OE_BULK_MSG_PUB.Add_Exc_Msg
435 ( G_PKG_NAME
436 ,'Load_Line_Type'
437 );
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 END Load_Line_Type;
440
441 FUNCTION Load_Agreement
442 ( p_key IN NUMBER
443 , p_default_attributes IN VARCHAR2
444 )
445 RETURN NUMBER
446 IS
447 --
448 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
449 --
450 BEGIN
451
452 IF G_AGREEMENT_TBL.EXISTS(p_key)
453 AND ( p_default_attributes = 'N'
454 OR (p_default_attributes = 'Y'
455 AND G_AGREEMENT_TBL(p_key).default_attributes = 'Y'))
456 THEN
457
458 RETURN p_key;
459
460 END IF;
461
462 IF p_default_attributes = 'Y' THEN
463
464 SELECT a.agreement_id
465 ,a.name
466 ,a.start_date_active
467 ,a.end_date_active
468 ,a.revision
469 ,a.sold_to_org_id
470 ,a.price_list_id
471 ,i.rule_id
472 ,ac.rule_id
473 ,term.term_id
474 ,s.salesrep_id
475 ,a.purchase_order_num
476 ,a.invoice_contact_id
477 ,a.invoice_to_org_id
478 ,'Y'
479 INTO G_AGREEMENT_TBL(p_key).agreement_id
480 ,G_AGREEMENT_TBL(p_key).name
481 ,G_AGREEMENT_TBL(p_key).start_date_active
482 ,G_AGREEMENT_TBL(p_key).end_date_active
483 ,G_AGREEMENT_TBL(p_key).revision
484 ,G_AGREEMENT_TBL(p_key).sold_to_org_id
485 ,G_AGREEMENT_TBL(p_key).price_list_id
486 ,G_AGREEMENT_TBL(p_key).invoicing_rule_id
487 ,G_AGREEMENT_TBL(p_key).accounting_rule_id
488 ,G_AGREEMENT_TBL(p_key).payment_term_id
489 ,G_AGREEMENT_TBL(p_key).salesrep_id
490 ,G_AGREEMENT_TBL(p_key).cust_po_number
491 ,G_AGREEMENT_TBL(p_key).invoice_to_contact_id
492 ,G_AGREEMENT_TBL(p_key).invoice_to_org_id
493 ,G_AGREEMENT_TBL(p_key).default_attributes
494 FROM oe_agreements_vl a
495 ,oe_ra_rules_v i
496 ,oe_ra_rules_v ac
497 ,oe_ra_terms_v term
498 ,ra_salesreps s
499 WHERE a.agreement_id = p_key
503 AND a.accounting_rule_id = ac.rule_id(+)
500 AND a.invoicing_rule_id = i.rule_id(+)
501 AND i.status(+) = 'A'
502 AND i.type(+) = 'I'
504 AND ac.status(+) = 'A'
505 AND ac.type(+) = 'A'
506 AND a.term_id = term.term_id(+)
507 AND sysdate between nvl(term.start_date_active(+),sysdate)
508 and nvl(term.end_date_active(+),sysdate)
509 AND a.salesrep_id = s.salesrep_id(+)
510 AND sysdate between nvl(s.start_date_active(+),sysdate)
511 and nvl(s.end_date_active(+),sysdate)
512 ;
513
514 ELSE
515
516 SELECT a.agreement_id
517 ,a.name
518 ,a.start_date_active
519 ,a.end_date_active
520 ,a.revision
521 ,a.sold_to_org_id
522 ,a.price_list_id
523 INTO G_AGREEMENT_TBL(p_key).agreement_id
524 ,G_AGREEMENT_TBL(p_key).name
525 ,G_AGREEMENT_TBL(p_key).start_date_active
526 ,G_AGREEMENT_TBL(p_key).end_date_active
527 ,G_AGREEMENT_TBL(p_key).revision
528 ,G_AGREEMENT_TBL(p_key).sold_to_org_id
529 ,G_AGREEMENT_TBL(p_key).price_list_id
530 FROM oe_agreements_vl a
531 WHERE a.agreement_id = p_key;
532
533 END IF;
534
535 RETURN p_key;
536
537 EXCEPTION
538 WHEN NO_DATA_FOUND THEN
539 IF G_AGREEMENT_TBL.EXISTS(p_key) THEN
540 IF l_debug_level > 0 THEN
541 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
542 END IF;
543 G_AGREEMENT_TBL.DELETE(p_key);
544 END IF;
545 RAISE NO_DATA_FOUND;
546 WHEN OTHERS THEN
547 IF G_AGREEMENT_TBL.EXISTS(p_key) THEN
548 IF l_debug_level > 0 THEN
549 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
550 END IF;
551 G_AGREEMENT_TBL.DELETE(p_key);
552 END IF;
553 OE_BULK_MSG_PUB.Add_Exc_Msg
554 ( G_PKG_NAME
555 ,'Load_Agreement'
556 );
557 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
558 END Load_Agreement;
559
560 FUNCTION Load_Item
561 ( p_key1 IN NUMBER
562 , p_key2 IN NUMBER
563 , p_default_attributes IN VARCHAR2
564 )
565 RETURN NUMBER
566 IS
567 l_key2 NUMBER;
568 -- --INVCONV start OPM 02/JUN/00 BEGIN
569 --===================
570 /* CURSOR c_opm_item ( discrete_org_id IN NUMBER
571 , discrete_item_id IN NUMBER) IS
572 SELECT dualum_ind
573 , item_id
574 , item_um
575 , item_um2
576 , grade_ctl -- OPM HVOP
577 FROM ic_item_mst
578 WHERE delete_mark = 0
579 AND item_no in (SELECT segment1
580 FROM mtl_system_items
581 WHERE organization_id = discrete_org_id
582 AND inventory_item_id = discrete_item_id);
583 --OPM 02/JUN/00 END
584 --=================
585 */
586 -- INVCONV
587
588 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
589 --
590 BEGIN
591
592 IF G_ITEM_TBL.EXISTS(p_key1)
593 AND ( p_default_attributes = 'N'
594 OR (p_default_attributes = 'Y'
595 AND G_ITEM_TBL(p_key1).default_attributes = 'Y')) AND
596 G_ITEM_TBL(p_key1).organization_id
597 = nvl(p_key2, G_ITEM_TBL(p_key1).organization_id)
598 THEN
599
600 RETURN p_key1;
601
602 END IF;
603
604 l_key2 := OE_BULK_ORDER_PVT.G_ITEM_ORG;
605
606 IF p_key1 IS NOT NULL AND
607 NOT G_ITEM_TBL.EXISTS(p_key1) THEN
608
609 /* Always load values based on the validation org
610 for the below attributes. In future please add here for the columns
611 which need to get loaded based on validation org */
612 /* Shippable_item_flag will be loaded into cache here and
613 later the same will be reloaded based on the ship_from_org_id.
614 This is because shippable_flag_item need to be loaded based on
615 the validation_org if ship_from_org is null*/
616
617 SELECT msi.INVENTORY_ITEM_ID
618 ,msi.ORGANIZATION_ID
619 ,msi.CUSTOMER_ORDER_ENABLED_FLAG
620 ,msi.INTERNAL_ORDER_ENABLED_FLAG
621 ,msi.INVOICING_RULE_ID
622 ,msi.ACCOUNTING_RULE_ID
623 ,msi.DEFAULT_SHIPPING_ORG
624 ,msi.SHIP_MODEL_COMPLETE_FLAG
625 ,msi.BUILD_IN_WIP_FLAG
626 ,msi.BOM_ITEM_TYPE
627 ,msi.REPLENISH_TO_ORDER_FLAG
628 ,msi.PRIMARY_UOM_CODE
629 ,msi.PICK_COMPONENTS_FLAG
630 ,msi.SHIPPABLE_ITEM_FLAG
631 ,msi.SERVICE_ITEM_FLAG
632 ,msi.OVER_SHIPMENT_TOLERANCE
633 ,msi.UNDER_SHIPMENT_TOLERANCE
634 ,msi.description
635 ,msi.hazard_class_id
636 ,msi.weight_uom_code
637 ,msi.volume_uom_code
638 ,msi.unit_volume
639 ,msi.unit_weight
640 ,DECODE(msi.mtl_transactions_enabled_flag, 'Y', 'Y', 'N')
644 ,msi.ONT_PRICING_QTY_SOURCE -- INVCONV
641 pickable_flag
642 --bug 3798477
643 --,DECODE(msi.ONT_PRICING_QTY_SOURCE, 'P', 0, 'S',1,NULL) -- INVCONV
645 ,msi.TRACKING_QUANTITY_IND
646 --bug 3798477
647 ,msi.SECONDARY_UOM_CODE
648 -- INVCONV start
649 ,msi.SECONDARY_DEFAULT_IND
650 ,msi.LOT_DIVISIBLE_FLAG
651 ,msi.GRADE_CONTROL_FLAG
652 ,msi.LOT_CONTROL_CODE
653 ,msi.CONFIG_MODEL_TYPE -- added for supporting configurations
654 ,msi.PLANNING_MAKE_BUY_CODE
655 ,kfv.concatenated_segments
656 ,msi.full_lead_time
657 ,msi.fixed_lead_time
658 ,msi.variable_lead_time
659
660 INTO G_ITEM_TBL(p_key1).inventory_item_id
661 ,G_ITEM_TBL(p_key1).organization_id
662 ,G_ITEM_TBL(p_key1).customer_order_enabled_flag
663 ,G_ITEM_TBL(p_key1).internal_order_enabled_flag
664 ,G_ITEM_TBL(p_key1).invoicing_rule_id
665 ,G_ITEM_TBL(p_key1).accounting_rule_id
666 ,G_ITEM_TBL(p_key1).default_shipping_org
667 ,G_ITEM_TBL(p_key1).ship_model_complete_flag
668 ,G_ITEM_TBL(p_key1).build_in_wip_flag
669 ,G_ITEM_TBL(p_key1).bom_item_type
670 ,G_ITEM_TBL(p_key1).replenish_to_order_flag
671 ,G_ITEM_TBL(p_key1).primary_uom_code
672 ,G_ITEM_TBL(p_key1).pick_components_flag
673 ,G_ITEM_TBL(p_key1).shippable_item_flag
674 ,G_ITEM_TBL(p_key1).service_item_flag
675 ,G_ITEM_TBL(p_key1).ship_tolerance_above
676 ,G_ITEM_TBL(p_key1).ship_tolerance_below
677 ,G_ITEM_TBL(p_key1).item_description
678 ,G_ITEM_TBL(p_key1).hazard_class_id
679 ,G_ITEM_TBL(p_key1).weight_uom_code
680 ,G_ITEM_TBL(p_key1).volume_uom_code
681 ,G_ITEM_TBL(p_key1).unit_volume
682 ,G_ITEM_TBL(p_key1).unit_weight
683 ,G_ITEM_TBL(p_key1).pickable_flag
684 --bug 3798477
685 ,G_ITEM_TBL(p_key1).ont_pricing_qty_source
686 ,G_ITEM_TBL(p_key1).tracking_quantity_ind
687 --bug 3798477
688 -- INCONV
689 ,G_ITEM_TBL(p_key1).secondary_uom_code
690 ,G_ITEM_TBL(p_key1).secondary_default_ind
691 ,G_ITEM_TBL(p_key1).lot_divisible_flag
692 ,G_ITEM_TBL(p_key1).grade_control_flag
693 ,G_ITEM_TBL(p_key1).lot_control_code
694 ,G_ITEM_TBL(p_key1).config_model_type --- added for supporting configurations
695 ,G_ITEM_TBL(p_key1).planning_make_buy_code
696 ,G_ITEM_TBL(p_key1).ordered_item
697 ,G_ITEM_TBL(p_key1).full_lead_time
698 ,G_ITEM_TBL(p_key1).fixed_lead_time
699 ,G_ITEM_TBL(p_key1).variable_lead_time
700 FROM MTL_SYSTEM_ITEMS msi,
701 MTL_SYSTEM_ITEMS_KFV kfv
702 WHERE msi.INVENTORY_ITEM_ID = p_key1
703 AND msi.ORGANIZATION_ID = l_key2
704 AND kfv.INVENTORY_ITEM_ID = p_key1
705 AND kfv.ORGANIZATION_ID = l_key2;
706
707 -- INVCONV start remove opm
708
709 /* IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' THEN
710
711 IF l_debug_level > 0 THEN
712 oe_debug_pub.add( 'PROCESS INSTALLED' ) ;
713 END IF;
714
715 IF INV_GMI_RSV_BRANCH.G_PROCESS_INV_INSTALLED = 'I' THEN
716 OPEN c_opm_item( l_key2
717 , p_key1);
718 FETCH c_opm_item INTO
719 G_ITEM_TBL(p_key1).dualum_ind
720 , G_ITEM_TBL(p_key1).opm_item_id
721 , G_ITEM_TBL(p_key1).opm_item_um
722 , G_ITEM_TBL(p_key1).opm_item_um2
723 , G_ITEM_TBL(p_key1).opm_grade_ctl; -- OPM HVOP
724
725 /*OPM HVOP need this in case of process warehouse and discrete item - Fully clear the process cache
726 IF c_opm_item%NOTFOUND THEN
727
728 IF l_debug_level > 0 THEN
729 oe_debug_pub.add( 'OPM item not found ', 1 ) ;
730 END IF;
731 G_ITEM_TBL(p_key1).opm_item_id := NULL;
732 G_ITEM_TBL(p_key1).opm_item_um := NULL;
733 G_ITEM_TBL(p_key1).opm_item_um2 := NULL;
734 G_ITEM_TBL(p_key1).dualum_ind := NULL;
735 G_ITEM_TBL(p_key1).opm_grade_ctl := NULL;
736 END IF;
737 CLOSE c_opm_item;
738 END IF;
739
740 END IF;
741
742 */
743 -- INVCONV end
744
745 /* When p_key2 is not null ie. ship_from_org_id is not null then
746 load the shippable_item_flag based on the ship_from_org. In future
747 please add the attributes here that needs to be loaded based on the
748 ship_from_org_id */
749
750 IF (p_key2 IS NOT NULL) THEN
751
752 IF (G_ITEM_TBL(p_key1).organization_id <> p_key2) THEN
753
754 IF l_debug_level > 0 THEN
755 oe_debug_pub.add( 'QUERYING BASED ON SHIP_FROM_ORG' , 3 ) ;
756 END IF;
757
758 -- invconv IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG <> 'Y' THEN -- OPM HVOP added for error to test
759
760 SELECT shippable_item_flag
761 ,organization_id
762 ,primary_uom_code
766 ,volume_uom_code
763 ,description
764 ,hazard_class_id
765 ,weight_uom_code
767 ,unit_volume
768 ,unit_weight
769 ,DECODE(mtl_transactions_enabled_flag, 'Y', 'Y', 'N')
770 pickable_flag
771 -- INVCONV start
772 ,ONT_PRICING_QTY_SOURCE
773 ,TRACKING_QUANTITY_IND
774 ,SECONDARY_UOM_CODE
775 ,SECONDARY_DEFAULT_IND
776 ,LOT_DIVISIBLE_FLAG
777 ,GRADE_CONTROL_FLAG
778 ,LOT_CONTROL_CODE
779
780 INTO G_ITEM_TBL(p_key1).shippable_item_flag
781 ,G_ITEM_TBL(p_key1).organization_id
782 ,G_ITEM_TBL(p_key1).primary_uom_code
783 ,G_ITEM_TBL(p_key1).item_description
784 ,G_ITEM_TBL(p_key1).hazard_class_id
785 ,G_ITEM_TBL(p_key1).weight_uom_code
786 ,G_ITEM_TBL(p_key1).volume_uom_code
787 ,G_ITEM_TBL(p_key1).unit_volume
788 ,G_ITEM_TBL(p_key1).unit_weight
789 ,G_ITEM_TBL(p_key1).pickable_flag
790 -- INVCONV start
791 ,G_ITEM_TBL(p_key1).ont_pricing_qty_source
792 ,G_ITEM_TBL(p_key1).tracking_quantity_ind
793 ,G_ITEM_TBL(p_key1).secondary_uom_code
794 ,G_ITEM_TBL(p_key1).secondary_default_ind
795 ,G_ITEM_TBL(p_key1).lot_divisible_flag
796 ,G_ITEM_TBL(p_key1).grade_control_flag
797 ,G_ITEM_TBL(p_key1).lot_control_code
798 FROM MTL_SYSTEM_ITEMS
799 WHERE INVENTORY_ITEM_ID = p_key1
800 AND ORGANIZATION_ID = p_key2; -- ship from org
801
802 IF l_debug_level > 0 THEN
803 oe_debug_pub.add( 'pal1 ' , 3 ) ;
804 END IF;
805 -- INVCONV end if ;
806
807
808 --bug 3798477
809 SELECT wms_enabled_flag
810 INTO G_ITEM_TBL(p_key1).wms_enabled_flag
811 FROM mtl_parameters
812 WHERE organization_id = p_key2;
813 --bug 3798477
814 IF l_debug_level > 0 THEN
815 oe_debug_pub.add( 'pal2 ' , 3 ) ;
816 END IF;
817
818 IF INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN
819 G_ITEM_TBL(p_key1).process_warehouse_flag := 'Y';
820 ELSE
821 G_ITEM_TBL(p_key1).process_warehouse_flag := NULL;
822 END IF;
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add( 'OPM IN OE_ORDER_CACHE.LOAD_ITEM PROCESS WAREHOUSE FLAG IS ' || G_ITEM_TBL ( P_KEY1 ) .PROCESS_WAREHOUSE_FLAG ) ;
825 END IF;
826 IF l_debug_level > 0 THEN
827 oe_debug_pub.add( 'pal3 ' , 3 ) ;
828 END IF;
829
830
831 END IF; -- if item tbl.org_id <> p_key2
832
833 END IF; -- End if p_key2 is not null
834
835 END IF; -- End if p_key1 is not null
836
837 RETURN p_key1;
838
839 EXCEPTION
840 WHEN NO_DATA_FOUND THEN
841 IF l_debug_level > 0 THEN
842 oe_debug_pub.add( 'NO DATA FOUND IN LOAD ITEM' ) ;
843 END IF;
844 IF G_ITEM_TBL.EXISTS(p_key1) THEN
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
847 END IF;
848 G_ITEM_TBL.DELETE(p_key1);
849 END IF;
850
851 /*IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' -- INVCONV take out
852 AND INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN
853 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
854 OE_BULK_MSG_PUB.add('Y','ERROR');
855 RAISE NO_DATA_FOUND;
856 END IF; */
857
858 WHEN OTHERS THEN
859
860 IF G_ITEM_TBL.EXISTS(p_key1) THEN
861 IF l_debug_level > 0 THEN
862 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
863 END IF;
864 G_ITEM_TBL.DELETE(p_key1);
865 END IF;
866 OE_BULK_MSG_PUB.Add_Exc_Msg
867 ( G_PKG_NAME
868 , 'Load_Item'
869 );
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871
872 IF l_debug_level > 0 THEN
873 oe_debug_pub.add( 'pal4 ' , 3 ) ;
874 END IF;
875 END Load_Item;
876
877
878 FUNCTION Load_Ship_To
879 ( p_key IN NUMBER
880 , p_default_attributes IN VARCHAR2
881 , p_edi_attributes IN VARCHAR2
882 )
883 RETURN NUMBER
884 IS
885 l_addr_alt VARCHAR2(3200) := NULL;
886 l_region1 VARCHAR2(3200) := NULL;
887 l_region2 VARCHAR2(3200) := NULL;
888 l_region3 VARCHAR2(3200) := NULL;
889 l_return_status VARCHAR2(30);
890 --
891 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
892 --
893 BEGIN
894
895 IF G_SHIP_TO_TBL.EXISTS(p_key)
896 AND ( p_default_attributes = 'N'
897 OR (p_default_attributes = 'Y'
898 AND G_SHIP_TO_TBL(p_key).default_attributes = 'Y'))
899 AND ( p_edi_attributes = 'N'
900 OR (p_edi_attributes = 'Y'
904 RETURN p_key;
901 AND G_SHIP_TO_TBL(p_key).address_id IS NOT NULL))
902 THEN
903
905
906 END IF;
907
908
909 IF p_default_attributes = 'N' THEN
910
911 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
912 ,a.cust_account_id
913 INTO G_SHIP_TO_TBL(p_key).ship_to_org_id
914 ,G_SHIP_TO_TBL(p_key).customer_id
915 FROM hz_cust_site_uses_all s
916 ,hz_cust_acct_sites a
917 WHERE s.site_use_id = p_key
918 AND s.site_use_code = 'SHIP_TO'
919 AND s.cust_acct_site_id = a.cust_acct_site_id
920 AND s.status = 'A'
921 AND a.status ='A'; --bug 2752321
922
923 ELSIF p_default_attributes = 'Y' THEN
924
925 IF l_debug_level > 0 THEN
926 oe_debug_pub.add( 'SHIP TO :'||P_KEY ) ;
927 END IF;
928 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
929 ,a.cust_account_id
930 ,s.warehouse_id
931 ,s.OVER_SHIPMENT_TOLERANCE
932 ,s.UNDER_SHIPMENT_TOLERANCE
933 ,s.ITEM_CROSS_REF_PREF
934 ,s.dates_positive_tolerance
935 ,s.date_type_preference
936 ,o.transaction_type_id
937 ,sm.lookup_code
938 ,fp.lookup_code
939 ,ft.lookup_code
940 ,dc.lookup_code
941 ,'Y'
942 INTO G_SHIP_TO_TBL(p_key).ship_to_org_id
943 ,G_SHIP_TO_TBL(p_key).customer_id
944 ,G_SHIP_TO_TBL(p_key).ship_from_org_id
945 ,G_SHIP_TO_TBL(p_key).ship_tolerance_above
946 ,G_SHIP_TO_TBL(p_key).ship_tolerance_below
947 ,G_SHIP_TO_TBL(p_key).item_identifier_type
948 ,G_SHIP_TO_TBL(p_key).latest_schedule_limit
949 ,G_SHIP_TO_TBL(p_key).order_date_type_code
950 ,G_SHIP_TO_TBL(p_key).order_type_id
951 ,G_SHIP_TO_TBL(p_key).shipping_method_code
952 ,G_SHIP_TO_TBL(p_key).fob_point_code
953 ,G_SHIP_TO_TBL(p_key).freight_terms_code
954 ,G_SHIP_TO_TBL(p_key).demand_class_code
955 ,G_SHIP_TO_TBL(p_key).default_attributes
956 FROM hz_cust_site_uses_all s
957 ,hz_cust_acct_sites_all a -- changed to _all since we know site_use_id and to perform better.
958 ,oe_transaction_types_all o
959 ,oe_ship_methods_v sm
960 ,oe_ar_lookups_v fp
961 ,oe_lookups ft
962 ,oe_fnd_common_lookups_v dc
963 WHERE s.site_use_id = p_key
964 AND a.cust_acct_site_id = s.cust_acct_site_id
965 AND s.site_use_code = 'SHIP_TO'
966 AND s.status = 'A'
967 AND a.status ='A' --bug 2752321
968 AND s.order_type_id = o.transaction_type_id(+)
969 AND sysdate between nvl(o.start_date_active(+),sysdate)
970 and nvl(o.end_date_active(+),sysdate)
971 AND s.ship_via = sm.lookup_code(+)
972 AND sm.lookup_type(+) = 'SHIP_METHOD'
973 AND sm.enabled_flag(+) = 'Y'
974 AND sysdate between nvl(sm.start_date_active(+),sysdate)
975 and nvl(sm.end_date_active(+),sysdate)
976 AND s.fob_point = fp.lookup_code(+)
977 AND fp.lookup_type(+) = 'FOB'
978 AND fp.enabled_flag(+) = 'Y'
979 AND sysdate between nvl(fp.start_date_active(+),sysdate)
980 and nvl(fp.end_date_active(+),sysdate)
981 AND s.freight_term = ft.lookup_code(+)
982 AND ft.lookup_type(+) = 'FREIGHT_TERMS'
983 AND ft.enabled_flag(+) = 'Y'
984 AND sysdate between nvl(ft.start_date_active(+),sysdate)
985 and nvl(ft.end_date_active(+),sysdate)
986 AND s.demand_class_code = dc.lookup_code(+)
987 AND dc.lookup_type(+) = 'DEMAND_CLASS'
988 AND dc.enabled_flag(+) = 'Y'
989 AND sysdate between nvl(dc.start_date_active(+),sysdate)
990 and nvl(dc.end_date_active(+),sysdate);
991
992 END IF;
993
994 IF p_edi_attributes = 'Y' THEN
995
996 SELECT b.cust_acct_site_id
997 ,a.ece_tp_location_code
998 ,b.location
999 INTO G_SHIP_TO_TBL(p_key).address_id
1000 ,G_SHIP_TO_TBL(p_key).edi_location_code
1001 ,G_SHIP_TO_TBL(p_key).location
1002 FROM hz_cust_acct_sites_all a
1003 , hz_cust_site_uses_all b
1004 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1005 AND b.site_use_id = p_key
1006 AND b.site_use_code='SHIP_TO';
1007
1008 Get_Address
1009 (p_address_type_in => 'CUSTOMER',
1010 p_org_id_in => OE_BULK_ORDER_PVT.G_ITEM_ORG,
1011 p_address_id_in => G_SHIP_TO_TBL(p_key).address_id,
1012 p_tp_location_code_in => NULL,
1013 p_tp_translator_code_in => NULL,
1014 l_addr1 => G_SHIP_TO_TBL(p_key).address1,
1015 l_addr2 => G_SHIP_TO_TBL(p_key).address2,
1016 l_addr3 => G_SHIP_TO_TBL(p_key).address3,
1017 l_addr4 => G_SHIP_TO_TBL(p_key).address4,
1018 l_addr_alt => l_addr_alt,
1019 l_city => G_SHIP_TO_TBL(p_key).city,
1020 l_county => G_SHIP_TO_TBL(p_key).county,
1021 l_state => G_SHIP_TO_TBL(p_key).state,
1022 l_zip => G_SHIP_TO_TBL(p_key).zip,
1026 l_region2 => l_region2,
1023 l_province => G_SHIP_TO_TBL(p_key).province,
1024 l_country => G_SHIP_TO_TBL(p_key).country,
1025 l_region1 => l_region1,
1027 l_region3 => l_region3,
1028 x_return_status => l_return_status);
1029
1030 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1032 END IF;
1033 END IF;
1034
1035 RETURN p_key;
1036
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 IF G_SHIP_TO_TBL.EXISTS(p_key) THEN
1040 IF l_debug_level > 0 THEN
1041 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1042 END IF;
1043 G_SHIP_TO_TBL.DELETE(p_key);
1044 END IF;
1045 IF l_debug_level > 0 THEN
1046 oe_debug_pub.add( 'NO DATA FOUND IN LOAD SHIP TO' ) ;
1047 END IF;
1048 RAISE NO_DATA_FOUND;
1049 WHEN OTHERS THEN
1050 IF G_SHIP_TO_TBL.EXISTS(p_key) THEN
1051 IF l_debug_level > 0 THEN
1052 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1053 END IF;
1054 G_SHIP_TO_TBL.DELETE(p_key);
1055 END IF;
1056 OE_BULK_MSG_PUB.Add_Exc_Msg
1057 ( G_PKG_NAME
1058 , 'Load_Ship_To'
1059 );
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061 END Load_Ship_To;
1062
1063 FUNCTION Load_Sold_To
1064 ( p_key IN NUMBER
1065 , p_default_attributes IN VARCHAR2
1066 , p_edi_attributes IN VARCHAR2
1067 )
1068 RETURN NUMBER
1069 IS
1070 l_tp_ret BOOLEAN;
1071 l_tp_ret_status VARCHAR2(30);
1072 l_msg_count NUMBER;
1073 l_msg_data VARCHAR2(2000);
1074 l_org_id NUMBER;
1075 --
1076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1077 --
1078 BEGIN
1079
1080 IF G_SOLD_TO_TBL.EXISTS(p_key)
1081 AND ( p_default_attributes = 'N'
1082 OR (p_default_attributes = 'Y'
1083 AND G_SOLD_TO_TBL(p_key).default_attributes = 'Y'))
1084 AND ( p_edi_attributes = 'N'
1085 OR (p_edi_attributes = 'Y'
1086 AND G_SOLD_TO_TBL(p_key).address_id IS NOT NULL))
1087 THEN
1088
1089 RETURN p_key;
1090
1091 END IF;
1092
1093 l_org_id := MO_GLOBAL.Get_Current_Org_Id;
1094
1095 IF p_edi_attributes = 'Y' THEN
1096
1097 BEGIN
1098
1099 SELECT /* MOAC_SQL_CHANGE */ a.cust_acct_site_id
1100 INTO G_SOLD_TO_TBL(p_key).address_id
1101 FROM hz_cust_site_uses_all b, hz_cust_acct_sites_all a
1102 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1103 AND a.cust_account_id = p_key
1104 /* AND NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
1105 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
1106 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
1107 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
1108 And a.org_id = l_org_id
1109 AND b.site_use_code = 'SOLD_TO'
1110 AND b.primary_flag = 'Y'
1111 AND b.status = 'A'
1112 AND a.status = 'A';--bug 2752321
1113
1114 l_tp_ret := EC_TRADING_PARTNER_PVT.Is_Entity_Enabled (
1115 p_api_version_number => 1.0
1116 ,p_init_msg_list => null
1117 ,p_simulate => null
1118 ,p_commit => null
1119 ,p_validation_level => null
1120 ,p_transaction_type => 'POAO'
1121 ,p_transaction_subtype => null
1122 ,p_entity_type => EC_TRADING_PARTNER_PVT.G_CUSTOMER
1123 ,p_entity_id => G_SOLD_TO_TBL(p_key).address_id
1124 ,p_return_status => l_tp_ret_status
1125 ,p_msg_count => l_msg_count
1126 ,p_msg_data => l_msg_data);
1127
1128 IF l_debug_level > 0 THEN
1129 oe_debug_pub.add( 'AFTER CALL TO THE EDI API , RET STATUS: ' ||L_TP_RET_STATUS ) ;
1130 END IF;
1131
1132 IF l_tp_ret = FALSE then
1133 IF l_debug_level > 0 THEN
1134 oe_debug_pub.add( 'TP SETUP FALSE FOR :'||P_KEY ) ;
1135 END IF;
1136 G_SOLD_TO_TBL(p_key).tp_setup := FALSE;
1137 ELSE
1138 IF l_debug_level > 0 THEN
1139 oe_debug_pub.add( 'TP SETUP TRUE FOR :'||P_KEY ) ;
1140 END IF;
1141 G_SOLD_TO_TBL(p_key).tp_setup := TRUE;
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN NO_DATA_FOUND THEN
1146 G_SOLD_TO_TBL(p_key).address_id := -1;
1147 G_SOLD_TO_TBL(p_key).tp_setup := FALSE;
1148 END;
1149
1150 END IF;
1151
1152 RETURN p_key;
1153
1154 EXCEPTION
1155 WHEN NO_DATA_FOUND THEN
1156 IF G_SOLD_TO_TBL.EXISTS(p_key) THEN
1157 IF l_debug_level > 0 THEN
1158 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1159 END IF;
1160 G_SOLD_TO_TBL.DELETE(p_key);
1161 END IF;
1162 RAISE NO_DATA_FOUND;
1163 WHEN OTHERS THEN
1164 IF G_SOLD_TO_TBL.EXISTS(p_key) THEN
1165 IF l_debug_level > 0 THEN
1169 END IF;
1166 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1167 END IF;
1168 G_SOLD_TO_TBL.DELETE(p_key);
1170 OE_BULK_MSG_PUB.Add_Exc_Msg
1171 ( G_PKG_NAME
1172 , 'Load_Sold_To'
1173 );
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END Load_Sold_To;
1176
1177 --{Bug 5054618
1178 FUNCTION Load_End_Customer
1179 ( p_key IN NUMBER
1180 , p_default_attributes IN VARCHAR2
1181 , p_edi_attributes IN VARCHAR2
1182 )
1183 RETURN NUMBER
1184 IS
1185 l_tp_ret BOOLEAN;
1186 l_tp_ret_status VARCHAR2(30);
1187 l_msg_count NUMBER;
1188 l_msg_data VARCHAR2(2000);
1189 --
1190 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1191 --
1192 Cursor End_Customer_site(p_site_use_code VARCHAR,p_key NUMBER) IS
1193 SELECT a.cust_acct_site_id
1194 FROM hz_cust_site_uses_all b, hz_cust_acct_sites_all a
1195 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1196 AND a.cust_account_id = p_key
1197 AND NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
1198 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
1199 AND b.site_use_code = p_site_use_code
1200 AND b.primary_flag = 'Y'
1201 AND b.status = 'A'
1202 AND a.status = 'A';
1203
1204 BEGIN
1205 IF G_END_CUSTOMER_TBL.EXISTS(p_key)
1206 AND ( p_default_attributes = 'N'
1207 OR (p_default_attributes = 'Y'
1208 AND G_END_CUSTOMER_TBL(p_key).default_attributes = 'Y'))
1209 AND ( p_edi_attributes = 'N'
1210 OR (p_edi_attributes = 'Y'
1211 AND G_END_CUSTOMER_TBL(p_key).address_id IS NOT NULL))
1212 THEN
1213 RETURN p_key;
1214
1215 END IF ;
1216
1217 IF p_edi_attributes = 'Y' THEN
1218
1219 OPEN End_customer_site('SOLD_TO',p_key);
1220 FETCH End_customer_site
1221 INTO G_END_CUSTOMER_TBL(p_key).address_id;
1222 IF End_customer_site%FOUND then
1223 CLOSE End_customer_site;
1224 oe_debug_pub.add('found sold to site use id');
1225 goto site_found;
1226 ELSE
1227 CLOSE End_customer_site;
1228 END IF;
1229
1230 OPEN End_customer_site('SHIP_TO',p_key);
1231 FETCH End_customer_site
1232 INTO G_END_CUSTOMER_TBL(p_key).address_id;
1233 IF End_customer_site%FOUND then
1234 CLOSE End_customer_site;
1235 oe_debug_pub.add('found sold to site use id');
1236 goto site_found;
1237 ELSE
1238 CLOSE End_customer_site;
1239 END IF;
1240
1241 OPEN End_customer_site('BILL_TO',p_key);
1242 FETCH End_customer_site
1243 INTO G_END_CUSTOMER_TBL(p_key).address_id;
1244 IF End_customer_site%FOUND then
1245 CLOSE End_customer_site;
1246 oe_debug_pub.add('found sold to site use id');
1247 goto site_found;
1248 ELSE
1249 CLOSE End_customer_site;
1250 END IF;
1251
1252 OPEN End_customer_site('DELIVER_TO',p_key);
1253 FETCH End_customer_site
1254 INTO G_END_CUSTOMER_TBL(p_key).address_id;
1255 IF End_customer_site%FOUND then
1256 CLOSE End_customer_site;
1257 oe_debug_pub.add('found sold to site use id');
1258 goto site_found;
1259 ELSE
1260 CLOSE End_customer_site;
1261 END IF;
1262
1263 <<site_found>>
1264
1265 l_tp_ret := EC_TRADING_PARTNER_PVT.Is_Entity_Enabled (
1266 p_api_version_number => 1.0
1267 ,p_init_msg_list => null
1268 ,p_simulate => null
1269 ,p_commit => null
1270 ,p_validation_level => null
1271 ,p_transaction_type => 'POAO'
1272 ,p_transaction_subtype => null
1273 ,p_entity_type => EC_TRADING_PARTNER_PVT.G_CUSTOMER
1274 ,p_entity_id => G_SOLD_TO_TBL(p_key).address_id
1275 ,p_return_status => l_tp_ret_status
1276 ,p_msg_count => l_msg_count
1277 ,p_msg_data => l_msg_data);
1278
1279 IF l_debug_level > 0 THEN
1280 oe_debug_pub.add( 'AFTER CALL TO THE EDI API , RET STATUS: ' ||L_TP_RET_STATUS ) ;
1281 END IF;
1282
1283 IF l_tp_ret = FALSE then
1284 IF l_debug_level > 0 THEN
1285 oe_debug_pub.add( 'TP SETUP FALSE FOR :'||P_KEY ) ;
1286 END IF;
1287 G_END_CUSTOMER_TBL(p_key).tp_setup := FALSE;
1288 ELSE
1289 IF l_debug_level > 0 THEN
1290 oe_debug_pub.add( 'TP SETUP TRUE FOR :'||P_KEY ) ;
1291 END IF;
1292 G_END_CUSTOMER_TBL(p_key).tp_setup := TRUE;
1293 END IF;
1294
1295
1296 END IF;
1297 RETURN p_key;
1298
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 IF G_END_CUSTOMER_TBL.EXISTS(p_key) THEN
1302 IF l_debug_level > 0 THEN
1303 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1304 END IF;
1305 G_END_CUSTOMER_TBL.DELETE(p_key);
1306 END IF;
1307 RAISE NO_DATA_FOUND;
1308 WHEN OTHERS THEN
1312 END IF;
1309 IF G_END_CUSTOMER_TBL.EXISTS(p_key) THEN
1310 IF l_debug_level > 0 THEN
1311 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1313 G_END_CUSTOMER_TBL.DELETE(p_key);
1314 END IF;
1315 OE_BULK_MSG_PUB.Add_Exc_Msg
1316 ( G_PKG_NAME
1317 , 'Load_End_Customer_To'
1318 );
1319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1320 END Load_End_customer;
1321
1322 /*end customer changes */
1323
1324 FUNCTION Load_End_Customer_Site
1325 ( p_key IN NUMBER
1326 , p_default_attributes IN VARCHAR2
1327 , p_edi_attributes IN VARCHAR2
1328 )
1329 RETURN NUMBER
1330 IS
1331 l_addr_alt VARCHAR2(3200) := NULL;
1332 l_region1 VARCHAR2(3200) := NULL;
1333 l_region2 VARCHAR2(3200) := NULL;
1334 l_region3 VARCHAR2(3200) := NULL;
1335 l_return_status VARCHAR2(30);
1336 --
1337 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1338 --
1339 Cursor End_customer_site_use (p_site_use_code varchar2) IS
1340 SELECT s.site_use_id
1341 ,a.cust_account_id
1342 from hz_cust_site_uses s
1343 ,hz_cust_acct_sites a
1344 WHERE s.site_use_id = p_key
1345 AND s.site_use_code =p_site_use_code
1346 AND s.cust_acct_site_id = a.cust_acct_site_id
1347 AND s.status = 'A'
1348 AND a.status ='A';
1349
1350 Cursor End_customer_address(p_site_use_code varchar2,p_key number) IS
1351 SELECT b.cust_acct_site_id
1352 ,a.ece_tp_location_code
1353 ,b.location
1354 FROM hz_cust_acct_sites_all a
1355 , hz_cust_site_uses_all b
1356 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1357 AND b.site_use_id = p_key
1358 AND b.site_use_code=p_site_use_code;
1359 BEGIN
1360 oe_debug_pub.add('Entering Load_End_Customer_Site');
1361 IF G_END_CUSTOMER_SITE_TBL.EXISTS(p_key)
1362 AND ( p_default_attributes = 'N'
1363 OR (p_default_attributes = 'Y'
1364 AND G_END_CUSTOMER_SITE_TBL(p_key).default_attributes = 'Y'))
1365 THEN
1366
1367 RETURN p_key;
1368
1369 END IF;
1370
1371 IF p_default_attributes = 'N' THEN
1372
1373 OPEN End_customer_site_use('SOLD_TO');
1374 FETCH End_customer_site_use
1375 INTO G_END_CUSTOMER_SITE_TBL(p_key).sold_to_site_use_id,
1376 G_END_CUSTOMER_SITE_TBL(p_key).customer_id;
1377 IF End_customer_site_use%FOUND then
1378 CLOSE End_customer_site_use ;
1379 oe_debug_pub.add('found sold to site use id');
1380 goto site_found;
1381 ELSE
1382 CLOSE End_customer_site_use;
1383 END IF;
1384 OPEN End_customer_site_use('SHIP_TO');
1385 FETCH End_customer_site_use
1386 INTO G_END_CUSTOMER_SITE_TBL(p_key).sold_to_site_use_id,
1387 G_END_CUSTOMER_SITE_TBL(p_key).customer_id;
1388 IF End_customer_site_use%FOUND then
1389 CLOSE End_customer_site_use ;
1390 oe_debug_pub.add('found ship to site use id');
1391 goto site_found;
1392 ELSE
1393 CLOSE End_customer_site_use;
1394 END IF;
1395 OPEN End_customer_site_use('BILL_TO');
1396 FETCH End_customer_site_use
1397 INTO G_END_CUSTOMER_SITE_TBL(p_key).sold_to_site_use_id,
1398 G_END_CUSTOMER_SITE_TBL(p_key).customer_id;
1399 IF End_customer_site_use%FOUND then
1400 CLOSE End_customer_site_use ;
1401 oe_debug_pub.add('found bill to site use id');
1402 goto site_found;
1403 ELSE
1404 CLOSE End_customer_site_use;
1405 END IF;
1406 OPEN End_customer_site_use('DELIVER_TO');
1407 FETCH End_customer_site_use
1408 INTO G_END_CUSTOMER_SITE_TBL(p_key).sold_to_site_use_id,
1409 G_END_CUSTOMER_SITE_TBL(p_key).customer_id;
1410 IF End_customer_site_use%FOUND then
1411 CLOSE End_customer_site_use ;
1412 oe_debug_pub.add('found deliver to site use id');
1413 goto site_found;
1414 ELSE
1415 CLOSE End_customer_site_use;
1416 END IF;
1417
1418 END IF; -- if default attribute is N
1419
1420 <<site_found>>
1421
1422 IF p_edi_attributes = 'Y' THEN
1423
1424 OPEN End_customer_address('SOLD_TO',p_key);
1425 FETCH End_customer_address
1426 INTO G_SOLD_TO_SITE_TBL(p_key).address_id
1427 ,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
1428 ,G_SOLD_TO_SITE_TBL(p_key).location;
1429
1430 IF End_customer_address%FOUND then
1431 CLOSE End_customer_address;
1432 oe_debug_pub.add('found sold to site use id');
1433 goto address_found;
1434 ELSE
1435 CLOSE End_customer_address;
1436 END IF;
1437
1438 OPEN End_customer_address('SHIP_TO',p_key);
1439 FETCH End_customer_address
1440 INTO G_SOLD_TO_SITE_TBL(p_key).address_id
1441 ,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
1442 ,G_SOLD_TO_SITE_TBL(p_key).location;
1443
1444 IF End_customer_address%FOUND then
1445 CLOSE End_customer_address;
1446 oe_debug_pub.add('found ship to site use id');
1447 goto address_found;
1448 ELSE
1449 CLOSE End_customer_address;
1450 END IF;
1451
1452 OPEN End_customer_address('BILL_TO',p_key);
1453 FETCH End_customer_address
1457
1454 INTO G_SOLD_TO_SITE_TBL(p_key).address_id
1455 ,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
1456 ,G_SOLD_TO_SITE_TBL(p_key).location;
1458 IF End_customer_address%FOUND then
1459 CLOSE End_customer_address;
1460 oe_debug_pub.add('found bill to site use id');
1461 goto address_found;
1462 ELSE
1463 CLOSE End_customer_address;
1464 END IF;
1465
1466 OPEN End_customer_address('DELIVER_TO',p_key);
1467 FETCH End_customer_address
1468 INTO G_SOLD_TO_SITE_TBL(p_key).address_id
1469 ,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
1470 ,G_SOLD_TO_SITE_TBL(p_key).location;
1471
1472 IF End_customer_address%FOUND then
1473 CLOSE End_customer_address;
1474 oe_debug_pub.add('found deliver to site use id');
1475 goto address_found;
1476 ELSE
1477 CLOSE End_customer_address;
1478 END IF;
1479
1480
1481
1482 <<address_found>>
1483
1484 Get_Address
1485 (p_address_type_in => 'CUSTOMER',
1486 p_org_id_in => OE_BULK_ORDER_PVT.G_ITEM_ORG,
1487 p_address_id_in => G_SOLD_TO_SITE_TBL(p_key).address_id,
1488 p_tp_location_code_in => NULL,
1489 p_tp_translator_code_in => NULL,
1490 l_addr1 => G_SOLD_TO_SITE_TBL(p_key).address1,
1491 l_addr2 => G_SOLD_TO_SITE_TBL(p_key).address2,
1492 l_addr3 => G_SOLD_TO_SITE_TBL(p_key).address3,
1493 l_addr4 => G_SOLD_TO_SITE_TBL(p_key).address4,
1494 l_addr_alt => l_addr_alt,
1495 l_city => G_SOLD_TO_SITE_TBL(p_key).city,
1496 l_county => G_SOLD_TO_SITE_TBL(p_key).county,
1497 l_state => G_SOLD_TO_SITE_TBL(p_key).state,
1498 l_zip => G_SOLD_TO_SITE_TBL(p_key).zip,
1499 l_province => G_SOLD_TO_SITE_TBL(p_key).province,
1500 l_country => G_SOLD_TO_SITE_TBL(p_key).country,
1501 l_region1 => l_region1,
1502 l_region2 => l_region2,
1503 l_region3 => l_region3,
1504 x_return_status => l_return_status);
1505
1506 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1508 END IF;
1509
1510
1511
1512 END IF;
1513
1514
1515 RETURN p_key;
1516
1517 EXCEPTION
1518 WHEN NO_DATA_FOUND THEN
1519 IF l_debug_level > 0 THEN
1520 oe_debug_pub.add( 'NO DATA FOUND IN LOAD SOLD_TO_SITE:'||TO_CHAR ( P_KEY ) ) ;
1521 END IF;
1522 IF G_SOLD_TO_SITE_TBL.EXISTS(p_key) THEN
1523 IF l_debug_level > 0 THEN
1524 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1525 END IF;
1526 G_SOLD_TO_SITE_TBL.DELETE(p_key);
1527 END IF;
1528 RAISE NO_DATA_FOUND;
1529 WHEN OTHERS THEN
1530 IF G_SOLD_TO_SITE_TBL.EXISTS(p_key) THEN
1531 IF l_debug_level > 0 THEN
1532 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1533 END IF;
1534 G_SOLD_TO_SITE_TBL.DELETE(p_key);
1535 END IF;
1536 OE_BULK_MSG_PUB.Add_Exc_Msg
1537 ( G_PKG_NAME
1538 , 'Load_Sold_To_Site'
1539 );
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 END Load_End_Customer_Site;
1542
1543 --Bug 5054618}
1544
1545 FUNCTION Load_Invoice_To
1546 ( p_key IN NUMBER
1547 , p_default_attributes IN VARCHAR2
1548 , p_edi_attributes IN VARCHAR2
1549 )
1550 RETURN NUMBER
1551 IS
1552 l_addr_alt VARCHAR2(3200) := NULL;
1553 l_region1 VARCHAR2(3200) := NULL;
1554 l_region2 VARCHAR2(3200) := NULL;
1555 l_region3 VARCHAR2(3200) := NULL;
1556 l_return_status VARCHAR2(30);
1557 --
1558 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1559 --
1560 BEGIN
1561
1562 IF G_INVOICE_TO_TBL.EXISTS(p_key)
1563 AND ( p_default_attributes = 'N'
1564 OR (p_default_attributes = 'Y'
1565 AND G_INVOICE_TO_TBL(p_key).default_attributes = 'Y'))
1566 THEN
1567
1568 RETURN p_key;
1569
1570 END IF;
1571
1572
1573 IF p_default_attributes = 'N' THEN
1574
1575 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
1576 ,a.cust_account_id
1577 INTO G_INVOICE_TO_TBL(p_key).invoice_to_org_id
1578 ,G_INVOICE_TO_TBL(p_key).customer_id
1579 FROM hz_cust_site_uses_all s
1580 ,hz_cust_acct_sites a
1581 WHERE s.site_use_id = p_key
1582 AND s.site_use_code = 'BILL_TO'
1583 AND s.cust_acct_site_id = a.cust_acct_site_id
1584 AND s.status = 'A'
1585 AND a.status ='A'; --bug 2752321
1586
1587 ELSIF p_default_attributes = 'Y' THEN
1588
1589 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
1590 ,a.cust_account_id
1591 ,o.transaction_type_id
1592 ,term.term_id
1593 ,pl.list_header_id
1594 ,'Y'
1598 ,G_INVOICE_TO_TBL(p_key).payment_term_id
1595 INTO G_INVOICE_TO_TBL(p_key).invoice_to_org_id
1596 ,G_INVOICE_TO_TBL(p_key).customer_id
1597 ,G_INVOICE_TO_TBL(p_key).order_type_id
1599 ,G_INVOICE_TO_TBL(p_key).price_list_id
1600 ,G_INVOICE_TO_TBL(p_key).default_attributes
1601 FROM hz_cust_site_uses_all s
1602 ,hz_cust_acct_sites a
1603 ,oe_transaction_types_all o
1604 ,ra_terms_b term
1605 ,qp_list_headers_b pl
1606 WHERE s.site_use_id = p_key
1607 AND s.site_use_code = 'BILL_TO'
1608 AND s.cust_acct_site_id = a.cust_acct_site_id
1609 AND s.status = 'A'
1610 AND a.status ='A'--bug 2752321
1611 AND s.order_type_id = o.transaction_type_id(+)
1612 AND sysdate between nvl(o.start_date_active(+),sysdate)
1613 and nvl(o.end_date_active(+),sysdate)
1614 AND s.payment_term_id = term.term_id(+)
1615 AND sysdate between nvl(term.start_date_active(+),sysdate)
1616 and nvl(term.end_date_active(+),sysdate)
1617 AND s.price_list_id = pl.list_header_id(+)
1618 AND nvl(pl.active_flag(+),'Y') = 'Y'
1619 ;
1620
1621 END IF;
1622
1623 IF p_edi_attributes = 'Y' THEN
1624
1625 SELECT b.cust_acct_site_id
1626 ,a.ece_tp_location_code
1627 ,b.location
1628 INTO G_INVOICE_TO_TBL(p_key).address_id
1629 ,G_INVOICE_TO_TBL(p_key).edi_location_code
1630 ,G_INVOICE_TO_TBL(p_key).location
1631 FROM hz_cust_acct_sites_all a
1632 , hz_cust_site_uses_all b
1633 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1634 AND b.site_use_id = p_key
1635 AND b.site_use_code='BILL_TO';
1636
1637 Get_Address
1638 (p_address_type_in => 'CUSTOMER',
1639 p_org_id_in => OE_BULK_ORDER_PVT.G_ITEM_ORG,
1640 p_address_id_in => G_INVOICE_TO_TBL(p_key).address_id,
1641 p_tp_location_code_in => NULL,
1642 p_tp_translator_code_in => NULL,
1643 l_addr1 => G_INVOICE_TO_TBL(p_key).address1,
1644 l_addr2 => G_INVOICE_TO_TBL(p_key).address2,
1645 l_addr3 => G_INVOICE_TO_TBL(p_key).address3,
1646 l_addr4 => G_INVOICE_TO_TBL(p_key).address4,
1647 l_addr_alt => l_addr_alt,
1648 l_city => G_INVOICE_TO_TBL(p_key).city,
1649 l_county => G_INVOICE_TO_TBL(p_key).county,
1650 l_state => G_INVOICE_TO_TBL(p_key).state,
1651 l_zip => G_INVOICE_TO_TBL(p_key).zip,
1652 l_province => G_INVOICE_TO_TBL(p_key).province,
1653 l_country => G_INVOICE_TO_TBL(p_key).country,
1654 l_region1 => l_region1,
1655 l_region2 => l_region2,
1656 l_region3 => l_region3,
1657 x_return_status => l_return_status);
1658
1659 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661 END IF;
1662 END IF;
1663
1664
1665 RETURN p_key;
1666
1667 EXCEPTION
1668 WHEN NO_DATA_FOUND THEN
1669 IF l_debug_level > 0 THEN
1670 oe_debug_pub.add( 'NO DATA FOUND IN LOAD INVOICE TO :'||TO_CHAR ( P_KEY ) ) ;
1671 END IF;
1672 IF G_INVOICE_TO_TBL.EXISTS(p_key) THEN
1673 IF l_debug_level > 0 THEN
1674 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1675 END IF;
1676 G_INVOICE_TO_TBL.DELETE(p_key);
1677 END IF;
1678 RAISE NO_DATA_FOUND;
1679 WHEN OTHERS THEN
1680 IF G_INVOICE_TO_TBL.EXISTS(p_key) THEN
1681 IF l_debug_level > 0 THEN
1682 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1683 END IF;
1684 G_INVOICE_TO_TBL.DELETE(p_key);
1685 END IF;
1686 OE_BULK_MSG_PUB.Add_Exc_Msg
1687 ( G_PKG_NAME
1688 , 'Load_Invoice_To'
1689 );
1690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1691 END Load_Invoice_To;
1692 /*sdatti*/
1693 FUNCTION Load_Sold_To_Site
1694 ( p_key IN NUMBER
1695 , p_default_attributes IN VARCHAR2
1696 , p_edi_attributes IN VARCHAR2
1697 )
1698 RETURN NUMBER
1699 IS
1700 l_addr_alt VARCHAR2(3200) := NULL;
1701 l_region1 VARCHAR2(3200) := NULL;
1702 l_region2 VARCHAR2(3200) := NULL;
1703 l_region3 VARCHAR2(3200) := NULL;
1704 l_return_status VARCHAR2(30);
1705 --
1706 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1707 --
1708 BEGIN
1709
1710 IF G_SOLD_TO_SITE_TBL.EXISTS(p_key)
1711 AND ( p_default_attributes = 'N'
1712 OR (p_default_attributes = 'Y'
1713 AND G_SOLD_TO_SITE_TBL(p_key).default_attributes = 'Y'))
1714 THEN
1715
1716 RETURN p_key;
1717
1718 END IF;
1719
1720
1721 IF p_default_attributes = 'N' THEN
1722
1723 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
1724 ,a.cust_account_id
1725 INTO G_SOLD_TO_SITE_TBL(p_key).sold_to_site_use_id
1729 WHERE s.site_use_id = p_key
1726 ,G_SOLD_TO_SITE_TBL(p_key).customer_id
1727 FROM hz_cust_site_uses_all s
1728 ,hz_cust_acct_sites a
1730 AND s.site_use_code = 'SOLD_TO'
1731 AND s.cust_acct_site_id = a.cust_acct_site_id
1732 AND s.status = 'A'
1733 AND a.status ='A';
1734
1735 ELSIF p_default_attributes = 'Y' THEN
1736
1737 SELECT /* MOAC_SQL_CHANGE */ s.site_use_id
1738 ,a.cust_account_id
1739 ,o.transaction_type_id
1740 ,term.term_id
1741 ,pl.list_header_id
1742 ,'Y'
1743 INTO G_SOLD_TO_SITE_TBL(p_key).sold_to_site_use_id
1744 ,G_SOLD_TO_SITE_TBL(p_key).customer_id
1745 ,G_SOLD_TO_SITE_TBL(p_key).order_type_id
1746 ,G_SOLD_TO_SITE_TBL(p_key).payment_term_id
1747 ,G_SOLD_TO_SITE_TBL(p_key).price_list_id
1748 ,G_SOLD_TO_SITE_TBL(p_key).default_attributes
1749 FROM hz_cust_site_uses_all s
1750 ,hz_cust_acct_sites a
1751 ,oe_transaction_types_all o
1752 ,ra_terms_b term
1753 ,qp_list_headers_b pl
1754 WHERE s.site_use_id = p_key
1755 AND s.site_use_code = 'SOLD_TO'
1756 AND s.cust_acct_site_id = a.cust_acct_site_id
1757 AND s.status = 'A'
1758 AND a.status ='A'--bug 2752321
1759 AND s.order_type_id = o.transaction_type_id(+)
1760 AND sysdate between nvl(o.start_date_active(+),sysdate)
1761 and nvl(o.end_date_active(+),sysdate)
1762 AND s.payment_term_id = term.term_id(+)
1763 AND sysdate between nvl(term.start_date_active(+),sysdate)
1764 and nvl(term.end_date_active(+),sysdate)
1765 AND s.price_list_id = pl.list_header_id(+)
1766 AND nvl(pl.active_flag(+),'Y') = 'Y'
1767 ;
1768
1769 END IF;
1770
1771 IF p_edi_attributes = 'Y' THEN
1772
1773 SELECT b.cust_acct_site_id
1774 ,a.ece_tp_location_code
1775 ,b.location
1776 INTO G_SOLD_TO_SITE_TBL(p_key).address_id
1777 ,G_SOLD_TO_SITE_TBL(p_key).edi_location_code
1778 ,G_SOLD_TO_SITE_TBL(p_key).location
1779 FROM hz_cust_acct_sites_all a
1780 , hz_cust_site_uses_all b
1781 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1782 AND b.site_use_id = p_key
1783 AND b.site_use_code='SOLD_TO';
1784
1785 Get_Address
1786 (p_address_type_in => 'CUSTOMER',
1787 p_org_id_in => OE_BULK_ORDER_PVT.G_ITEM_ORG,
1788 p_address_id_in => G_SOLD_TO_SITE_TBL(p_key).address_id,
1789 p_tp_location_code_in => NULL,
1790 p_tp_translator_code_in => NULL,
1791 l_addr1 => G_SOLD_TO_SITE_TBL(p_key).address1,
1792 l_addr2 => G_SOLD_TO_SITE_TBL(p_key).address2,
1793 l_addr3 => G_SOLD_TO_SITE_TBL(p_key).address3,
1794 l_addr4 => G_SOLD_TO_SITE_TBL(p_key).address4,
1795 l_addr_alt => l_addr_alt,
1796 l_city => G_SOLD_TO_SITE_TBL(p_key).city,
1797 l_county => G_SOLD_TO_SITE_TBL(p_key).county,
1798 l_state => G_SOLD_TO_SITE_TBL(p_key).state,
1799 l_zip => G_SOLD_TO_SITE_TBL(p_key).zip,
1800 l_province => G_SOLD_TO_SITE_TBL(p_key).province,
1801 l_country => G_SOLD_TO_SITE_TBL(p_key).country,
1802 l_region1 => l_region1,
1803 l_region2 => l_region2,
1804 l_region3 => l_region3,
1805 x_return_status => l_return_status);
1806
1807 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809 END IF;
1810 END IF;
1811
1812
1813 RETURN p_key;
1814
1815 EXCEPTION
1816 WHEN NO_DATA_FOUND THEN
1817 IF l_debug_level > 0 THEN
1818 oe_debug_pub.add( 'NO DATA FOUND IN LOAD SOLD_TO_SITE:'||TO_CHAR ( P_KEY ) ) ;
1819 END IF;
1820 IF G_SOLD_TO_SITE_TBL.EXISTS(p_key) THEN
1821 IF l_debug_level > 0 THEN
1822 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1823 END IF;
1824 G_SOLD_TO_SITE_TBL.DELETE(p_key);
1825 END IF;
1826 RAISE NO_DATA_FOUND;
1827 WHEN OTHERS THEN
1828 IF G_SOLD_TO_SITE_TBL.EXISTS(p_key) THEN
1829 IF l_debug_level > 0 THEN
1830 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1831 END IF;
1832 G_SOLD_TO_SITE_TBL.DELETE(p_key);
1833 END IF;
1834 OE_BULK_MSG_PUB.Add_Exc_Msg
1835 ( G_PKG_NAME
1836 , 'Load_Sold_To_Site'
1837 );
1838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1839 END Load_Sold_To_Site;
1840 /*sdatti*/
1841 FUNCTION Load_Salesrep
1842 ( p_key IN NUMBER
1843 )
1844 RETURN NUMBER
1845 IS
1846 --
1847 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1848 --
1849 BEGIN
1850
1851 IF G_SALESREP_TBL.EXISTS(p_key)
1852 THEN
1853
1854 RETURN p_key;
1855
1856 END IF;
1857
1858 SELECT salesrep_id
1859 ,sales_credit_type_id
1860 ,person_id
1864 ,G_SALESREP_TBL(p_key).sales_credit_type_id
1861 ,sales_tax_geocode
1862 ,sales_tax_inside_city_limits
1863 INTO G_SALESREP_TBL(p_key).salesrep_id
1865 ,G_SALESREP_TBL(p_key).person_id
1866 ,G_SALESREP_TBL(p_key).sales_tax_geocode
1867 ,G_SALESREP_TBL(p_key).sales_tax_inside_city_limits
1868 FROM RA_SALESREPS s
1869 WHERE SALESREP_ID = p_key;
1870
1871
1872 RETURN p_key;
1873
1874 EXCEPTION
1875 WHEN NO_DATA_FOUND THEN
1876 IF l_debug_level > 0 THEN
1877 oe_debug_pub.add( 'NO DATA FOUND IN LOAD SALESREP :'||TO_CHAR ( P_KEY ) ) ;
1878 END IF;
1879 IF G_SALESREP_TBL.EXISTS(p_key) THEN
1880 IF l_debug_level > 0 THEN
1881 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1882 END IF;
1883 G_SALESREP_TBL.DELETE(p_key);
1884 END IF;
1885 RAISE NO_DATA_FOUND;
1886 WHEN OTHERS THEN
1887 IF G_SALESREP_TBL.EXISTS(p_key) THEN
1888 IF l_debug_level > 0 THEN
1889 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
1890 END IF;
1891 G_SALESREP_TBL.DELETE(p_key);
1892 END IF;
1893 OE_BULK_MSG_PUB.Add_Exc_Msg
1894 ( G_PKG_NAME
1895 , 'Load_Salesrep'
1896 );
1897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1898 END Load_Salesrep;
1899
1900 FUNCTION Load_Ship_From
1901 ( p_key IN NUMBER
1902 )
1903 RETURN NUMBER
1904 IS
1905 l_addr_id NUMBER;
1906 l_location_code VARCHAR2(40);
1907 l_addr_code VARCHAR2(40);
1908 l_addr1 VARCHAR2(3200) := NULL;
1909 l_addr2 VARCHAR2(3200) := NULL;
1910 l_addr3 VARCHAR2(3200) := NULL;
1911 l_addr4 VARCHAR2(3200) := NULL;
1912 l_addr_alt VARCHAR2(3200) := NULL;
1913 l_city VARCHAR2(3200) := NULL;
1914 l_county VARCHAR2(3200) := NULL;
1915 l_state VARCHAR2(3200) := NULL;
1916 l_zip VARCHAR2(3200) := NULL;
1917 l_province VARCHAR2(3200) := NULL;
1918 l_country VARCHAR2(3200) := NULL;
1919 l_region1 VARCHAR2(3200) := NULL;
1920 l_region2 VARCHAR2(3200) := NULL;
1921 l_region3 VARCHAR2(3200) := NULL;
1922 l_return_status VARCHAR2(30);
1923 --
1924 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1925 --
1926 BEGIN
1927
1928 -- Initially, ship from cache is only for EDI attributes therefore
1929 -- no need to have separate in parameters to indicate what kind of
1930 -- attributes need to be cached.
1931 IF G_SHIP_FROM_TBL.EXISTS(p_key)
1932 THEN
1933
1934 RETURN p_key;
1935
1936 END IF;
1937
1938
1939 BEGIN
1940
1941 SELECT hu.location_id,hl.ece_tp_location_code, hl.location_code
1942 INTO l_addr_id, l_location_code,l_addr_code
1943 FROM hr_all_organization_units hu,
1944 hr_locations hl
1945 WHERE hl.location_id = hu.location_id
1946 AND hu.organization_id = p_key;
1947
1948 EXCEPTION
1949 WHEN OTHERS THEN
1950 NULL;
1951 IF l_debug_level > 0 THEN
1952 oe_debug_pub.add( 'UNABLE TO DERIVE SHIP FROM ADDR' ||' KEY :'||P_KEY ) ;
1953 END IF;
1954 END;
1955
1956 Get_Address(
1957 p_address_type_in => 'HR_LOCATION',
1958 p_org_id_in => OE_BULK_ORDER_PVT.G_ITEM_ORG,
1959 p_address_id_in => l_addr_id,
1960 p_tp_location_code_in => NULL,
1961 p_tp_translator_code_in => NULL,
1962 l_addr1 => l_addr1,
1963 l_addr2 => l_addr2,
1964 l_addr3 => l_addr3,
1965 l_addr4 => l_addr4,
1966 l_addr_alt => l_addr_alt,
1967 l_city => l_city,
1968 l_county => l_county,
1969 l_state => l_state,
1970 l_zip => l_zip,
1971 l_province => l_province,
1972 l_country => l_country,
1973 l_region1 => l_region1,
1974 l_region2 => l_region2,
1975 l_region3 => l_region3,
1976 x_return_status => l_return_status);
1977
1978 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1980 END IF;
1981
1982 G_SHIP_FROM_TBL(p_key).address1 := SUBSTR(l_addr1,0,30);
1983 G_SHIP_FROM_TBL(p_key).address2 := SUBSTR(l_addr2,0,30);
1984 G_SHIP_FROM_TBL(p_key).address3 := SUBSTR(l_addr3,0,30);
1985 G_SHIP_FROM_TBL(p_key).address4 := SUBSTR(l_addr4,0,30);
1986 G_SHIP_FROM_TBL(p_key).state := SUBSTR(l_state,0,30);
1987 G_SHIP_FROM_TBL(p_key).city := SUBSTR(l_city,0,30);
1988 G_SHIP_FROM_TBL(p_key).zip := SUBSTR(l_zip,0,30);
1989 G_SHIP_FROM_TBL(p_key).country := SUBSTR(l_country,0,30);
1990 G_SHIP_FROM_TBL(p_key).county := SUBSTR(l_county,0,30);
1991 G_SHIP_FROM_TBL(p_key).province := SUBSTR(l_province,0,240);
1992 G_SHIP_FROM_TBL(p_key).location := l_addr_code;
1993 G_SHIP_FROM_TBL(p_key).edi_location_code := l_location_code;
1994
1995 RETURN p_key;
1996
1997 EXCEPTION
1998 WHEN NO_DATA_FOUND THEN
1999 IF l_debug_level > 0 THEN
2000 oe_debug_pub.add( 'NO DATA FOUND IN LOAD SHIP FROM :'||TO_CHAR ( P_KEY ) ) ;
2001 END IF;
2002 IF G_SHIP_FROM_TBL.EXISTS(p_key) THEN
2003 IF l_debug_level > 0 THEN
2004 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2005 END IF;
2006 G_SHIP_FROM_TBL.DELETE(p_key);
2007 END IF;
2008 RAISE NO_DATA_FOUND;
2009 WHEN OTHERS THEN
2010 IF G_SHIP_FROM_TBL.EXISTS(p_key) THEN
2011 IF l_debug_level > 0 THEN
2012 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2013 END IF;
2014 G_SHIP_FROM_TBL.DELETE(p_key);
2015 END IF;
2016 OE_BULK_MSG_PUB.Add_Exc_Msg
2017 ( G_PKG_NAME
2018 , 'Load_Ship_From'
2019 );
2020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2021 END Load_Ship_From;
2022
2023 FUNCTION Load_Price_List
2024 ( p_key IN NUMBER
2025 )
2026 RETURN NUMBER
2027 IS
2028 --
2029 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2030 --
2031 BEGIN
2032
2033 IF G_PRICE_LIST_TBL.EXISTS(p_key)
2034 THEN
2035
2036 RETURN p_key;
2037
2038 END IF;
2039
2040
2041 SELECT list_header_id
2042 ,name
2043 ,list_type_code
2044 ,start_date_active
2045 ,end_date_active
2046 ,currency_code
2047 INTO G_PRICE_LIST_TBL(p_key).price_list_id
2048 ,G_PRICE_LIST_TBL(p_key).name
2049 ,G_PRICE_LIST_TBL(p_key).list_type_code
2050 ,G_PRICE_LIST_TBL(p_key).start_date_active
2051 ,G_PRICE_LIST_TBL(p_key).end_date_active
2052 ,G_PRICE_LIST_TBL(p_key).currency_code
2053 FROM qp_list_headers_vl
2054 WHERE list_header_id = p_key;
2055
2056 RETURN p_key;
2057
2058 EXCEPTION
2059 WHEN NO_DATA_FOUND THEN
2060 IF l_debug_level > 0 THEN
2061 oe_debug_pub.add( 'NO DATA FOUND IN LOAD PRICE LIST :'||TO_CHAR ( P_KEY ) ) ;
2062 END IF;
2063 IF G_PRICE_LIST_TBL.EXISTS(p_key) THEN
2064 IF l_debug_level > 0 THEN
2065 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2066 END IF;
2067 G_PRICE_LIST_TBL.DELETE(p_key);
2068 END IF;
2069 RAISE NO_DATA_FOUND;
2070 WHEN OTHERS THEN
2071 IF G_PRICE_LIST_TBL.EXISTS(p_key) THEN
2072 IF l_debug_level > 0 THEN
2073 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2074 END IF;
2075 G_PRICE_LIST_TBL.DELETE(p_key);
2076 END IF;
2077 OE_BULK_MSG_PUB.Add_Exc_Msg
2078 ( G_PKG_NAME
2082 END Load_Price_List;
2079 , 'Load_Price_List'
2080 );
2081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2083
2084 FUNCTION IS_CC_REQUIRED
2085 ( p_key IN NUMBER
2086 )
2087 RETURN BOOLEAN
2088 IS
2089 BEGIN
2090
2091 IF G_ORDER_TYPE_TBL(p_key).entry_credit_check_rule_id IS NOT NULL
2092 THEN
2093 RETURN TRUE;
2094 ELSE
2095 RETURN FALSE;
2096 END IF;
2097
2098 END IS_CC_REQUIRED;
2099
2100 FUNCTION Load_Loc_Info
2101 ( p_key IN NUMBER
2102 )
2103 RETURN NUMBER
2104 IS
2105 --
2106 l_org_id NUMBER;
2107 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2108 --
2109 BEGIN
2110
2111 IF G_LOC_INFO_TBL.EXISTS(p_key)
2112 THEN
2113 RETURN p_key;
2114
2115 END IF;
2116
2117 l_org_id := MO_GLOBAL.Get_Current_Org_Id;
2118
2119 SELECT SU.SITE_USE_ID,
2120 SU.CUST_ACCT_SITE_ID,
2121 ACCT_SITE.CUST_ACCOUNT_ID,
2122 LOC.POSTAL_CODE,
2123 LOC.LOCATION_ID,
2124 PARTY.PARTY_ID ,
2125 PARTY.PARTY_NAME,
2126 PARTY_SITE.PARTY_SITE_ID,
2127 CUST_ACCT.ACCOUNT_NUMBER,
2128 CUST_ACCT.TAX_HEADER_LEVEL_FLAG ACCT_TAX_HEADER_LEVEL_FLAG,
2129 CUST_ACCT.TAX_ROUNDING_RULE ACCT_TAX_ROUNDING_RULE,
2130 LOC.STATE,
2131 SU.TAX_HEADER_LEVEL_FLAG SU_TAX_HEADER_LEVEL_FLAG,
2132 SU.TAX_ROUNDING_RULE SU_TAX_ROUNDING_RULE
2133 INTO
2134 G_LOC_INFO_TBL(p_key).site_use_id,
2135 G_LOC_INFO_TBL(p_key).cust_acct_site_id,
2136 G_LOC_INFO_TBL(p_key).cust_account_id,
2137 G_LOC_INFO_TBL(p_key).postal_code,
2138 G_LOC_INFO_TBL(p_key).loc_id,
2139 G_LOC_INFO_TBL(p_key).party_id,
2140 G_LOC_INFO_TBL(p_key).party_name,
2141 G_LOC_INFO_TBL(p_key).party_site_id,
2142 G_LOC_INFO_TBL(p_key).account_number,
2143 G_LOC_INFO_TBL(p_key).acct_tax_header_level_flag,
2144 G_LOC_INFO_TBL(p_key).acct_tax_rounding_rule,
2145 G_LOC_INFO_TBL(p_key).state,
2146 G_LOC_INFO_TBL(p_key).tax_header_level_flag,
2147 G_LOC_INFO_TBL(p_key).tax_rounding_rule
2148 FROM
2149 HZ_CUST_SITE_USES_ALL SU ,
2150 HZ_CUST_ACCT_SITES ACCT_SITE,
2151 HZ_PARTY_SITES PARTY_SITE,
2152 HZ_LOCATIONS LOC,
2153 HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
2154 HZ_PARTIES PARTY,
2155 HZ_CUST_ACCOUNTS CUST_ACCT
2156 WHERE SU.SITE_USE_ID = p_key
2157 AND SU.CUST_ACCT_SITE_ID = acct_site.cust_acct_site_id
2158 and acct_site.cust_account_id = cust_acct.cust_account_id
2159 and cust_acct.party_id = party.party_id
2160 and acct_site.party_site_id = party_site.party_site_id
2161 and party_site.location_id = loc.location_id
2162 and loc.location_id = loc_assign.location_id
2163 /*AND NVL(acct_site.org_id,
2164 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1), ' ',NULL,
2165 SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),-99)) =
2166 NVL(loc_assign.org_id,
2167 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,
2168 SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99)) */
2169 and NVL(acct_site.org_id, l_org_id) = NVL (loc_assign.org_id, l_org_id);
2170
2171 RETURN p_key;
2172
2173 EXCEPTION
2174 WHEN NO_DATA_FOUND THEN
2175 IF G_LOC_INFO_TBL.EXISTS(p_key) THEN
2176 IF l_debug_level > 0 THEN
2177 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2178 END IF;
2179 G_LOC_INFO_TBL.DELETE(p_key);
2180 END IF;
2181 RAISE NO_DATA_FOUND;
2182 WHEN OTHERS THEN
2183 IF G_LOC_INFO_TBL.EXISTS(p_key) THEN
2184 IF l_debug_level > 0 THEN
2185 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2186 END IF;
2187 G_LOC_INFO_TBL.DELETE(p_key);
2188 END IF;
2189 OE_BULK_MSG_PUB.Add_Exc_Msg
2190 ( G_PKG_NAME
2191 ,'Load_Loc_Info'
2192 );
2193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2194 END Load_Loc_Info;
2195
2196 FUNCTION Load_Tax_Attributes
2197 ( p_key IN VARCHAR2,
2198 p_tax_date IN DATE
2199 )
2200 RETURN NUMBER
2201 IS
2202 --
2203 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2204 --
2205 BEGIN
2206
2207 SELECT V.AMOUNT_INCLUDES_TAX_FLAG,
2208 V.TAXABLE_BASIS TAXABLE_BASIS,
2209 V.TAX_CALCULATION_PLSQL_BLOCK,
2210 V.VAT_TAX_ID
2211 INTO G_TAX_ATTRIBUTES_TBL(1).AMOUNT_INCLUDES_TAX_FLAG,
2212 G_TAX_ATTRIBUTES_TBL(1).TAXABLE_BASIS,
2213 G_TAX_ATTRIBUTES_TBL(1).TAX_CALCULATION_PLSQL_BLOCK,
2214 G_TAX_ATTRIBUTES_TBL(1).VAT_TAX_ID
2215 FROM AR_VAT_TAX V
2216 WHERE V.TAX_CODE = p_key
2217 AND trunc(p_tax_date)
2218 BETWEEN trunc(V.START_DATE)
2219 AND NVL(trunc(V.END_DATE),trunc(p_tax_date))
2220 AND V.TAX_CLASS = 'O'
2221 AND NVL(V.ENABLED_FLAG,'Y') = 'Y'
2222 AND V.SET_OF_BOOKS_ID = OE_BULK_ORDER_PVT.G_SOB_ID;
2223
2224 RETURN 1;
2225
2226 END Load_Tax_Attributes;
2227
2228 FUNCTION Load_Person
2229 ( p_key IN NUMBER,
2230 p_tax_date IN DATE
2231 )
2232 RETURN NUMBER
2233 IS
2234 --
2235 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2236 --
2237 BEGIN
2238
2239 IF G_PERSON_TBL.EXISTS(p_key)
2240 AND p_tax_date between G_PERSON_TBL(p_key).start_date and
2241 G_PERSON_TBL(p_key).end_date
2242 THEN
2243
2244 RETURN p_key;
2245
2246 END IF;
2247
2248 SELECT ASGN.ORGANIZATION_ID,
2249 HOU.LOCATION_ID,
2250 nvl(ASGN.EFFECTIVE_START_DATE,TO_DATE( '01011900',
2251 'DDMMYYYY')),
2252 nvl(ASGN.EFFECTIVE_END_DATE,TO_DATE( '31122199', 'DDMMYYYY'))
2253 INTO G_PERSON_TBL(p_key).organization_id,
2254 G_PERSON_TBL(p_key).location_id,
2255 G_PERSON_TBL(p_key).start_date,
2256 G_PERSON_TBL(p_key).end_date
2257 FROM PER_ALL_ASSIGNMENTS_F ASGN,
2258 hr_organization_units hou
2259 WHERE ASGN.PERSON_ID = p_key
2260 AND NVL(ASGN.PRIMARY_FLAG, 'Y') = 'Y'
2261 AND hou.organization_id = ASGN.ORGANIZATION_ID
2262 AND p_tax_date
2263 BETWEEN nvl(ASGN.EFFECTIVE_START_DATE,TO_DATE( '01011900'
2264 , 'DDMMYYYY'))
2265 AND nvl(ASGN.EFFECTIVE_END_DATE,TO_DATE( '31122199', 'DDMMYYYY'))
2266 AND ASSIGNMENT_TYPE = 'E';
2267
2268 RETURN p_key;
2269
2270 EXCEPTION
2271 WHEN NO_DATA_FOUND THEN
2272 IF G_PERSON_TBL.EXISTS(p_key) THEN
2273 IF l_debug_level > 0 THEN
2274 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2275 END IF;
2276 G_PERSON_TBL.DELETE(p_key);
2277 END IF;
2278 RAISE NO_DATA_FOUND;
2279 WHEN OTHERS THEN
2280 IF G_PERSON_TBL.EXISTS(p_key) THEN
2281 IF l_debug_level > 0 THEN
2282 oe_debug_pub.add( 'DELETE INVALID RECORD' ) ;
2283 END IF;
2284 G_PERSON_TBL.DELETE(p_key);
2285 END IF;
2286 OE_BULK_MSG_PUB.Add_Exc_Msg
2287 ( G_PKG_NAME
2288 ,'Load_Person'
2289 );
2290 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2291 END Load_Person;
2292
2293
2294
2295
2296 END OE_BULK_CACHE;