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