[Home] [Help]
PACKAGE: APPS.OE_BULK_CACHE
Source
1 PACKAGE OE_BULK_CACHE AS
2 /* $Header: OEBUCCHS.pls 120.1.12010000.4 2008/11/26 01:50:32 smusanna ship $ */
3
4
5 ---------------------------------------------------------------------
6 -- CACHE RECORD/TABLE TYPES
7 ---------------------------------------------------------------------
8
9 TYPE Order_Type_Rec_Type IS RECORD
10 ( order_type_id NUMBER
11 , name VARCHAR2(30)
12 , order_category_code VARCHAR2(30)
13 , agreement_type_code VARCHAR2(30)
14 , order_number_source_id NUMBER
15 , agreement_required_flag VARCHAR2(1)
16 , require_po_flag VARCHAR2(1)
17 , enforce_line_prices_flag VARCHAR2(1)
18 , entry_credit_check_rule_id NUMBER
19 , start_date_active DATE
20 , end_date_active DATE
21 , invoicing_rule_id NUMBER
22 , accounting_rule_id NUMBER
23 , price_list_id NUMBER
24 , shipment_priority_code VARCHAR2(30)
25 , shipping_method_code VARCHAR2(30)
26 , fob_point_code VARCHAR2(30)
27 , freight_terms_code VARCHAR2(30)
28 , demand_class_code VARCHAR2(30)
29 , ship_from_org_id NUMBER
30 , default_outbound_line_type_id NUMBER
31 , conversion_type_code VARCHAR2(30)
32 , tax_calculation_event VARCHAR2(30)
33 , auto_scheduling_flag VARCHAR2(1)
34 , scheduling_level_code VARCHAR2(30)
35 , default_attributes VARCHAR2(1) DEFAULT 'N'
36 , quick_cr_check_flag VARCHAR2(1) DEFAULT 'N'
37 , tax_calculation_flag VARCHAR2(1)
38 , cust_trx_type_id NUMBER
39 );
40
41 TYPE Line_Type_Rec_Type IS RECORD
42 ( line_type_id NUMBER
43 , order_category_code VARCHAR2(30)
44 , start_date_active DATE
45 , end_date_active DATE
46 , cust_trx_type_id NUMBER
47 , tax_calculation_flag VARCHAR2(1)
48 , scheduling_level_code VARCHAR2(30)
49 );
50
51 TYPE Agreement_Rec_Type IS RECORD
52 ( agreement_id NUMBER
53 , name VARCHAR2(240)
54 , start_date_active DATE
55 , end_date_active DATE
56 , revision VARCHAR2(50)
57 , sold_to_org_id NUMBER
58 , price_list_id NUMBER
59 , invoicing_rule_id NUMBER
60 , accounting_rule_id NUMBER
61 , payment_term_id NUMBER
62 , salesrep_id NUMBER
63 , cust_po_number VARCHAR2(50)
64 , invoice_to_contact_id NUMBER
65 , invoice_to_org_id NUMBER
66 , default_attributes VARCHAR2(1) DEFAULT 'N'
67 );
68
69 TYPE Ship_To_Rec_Type IS RECORD
70 ( ship_to_org_id NUMBER
71 , customer_id NUMBER
72 , order_type_id NUMBER
73 , ship_tolerance_above NUMBER
74 , ship_tolerance_below NUMBER
75 , latest_schedule_limit NUMBER
76 , order_date_type_code VARCHAR2(30)
77 , fob_point_code VARCHAR2(30)
78 , freight_terms_code VARCHAR2(30)
79 , demand_class_code VARCHAR2(30)
80 , ship_from_org_id NUMBER
81 , payment_term_id NUMBER
82 , shipping_method_code VARCHAR2(30)
83 , item_identifier_type VARCHAR2(30)
84 , default_attributes VARCHAR2(1) DEFAULT 'N'
85 -- EDI attributes
86 , address_id NUMBER
87 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
88 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
89 , address1 VARCHAR2(30)
90 , address2 VARCHAR2(30)
91 , address3 VARCHAR2(30)
92 , address4 VARCHAR2(30)
93 , state VARCHAR2(30)
94 , city VARCHAR2(30)
95 , zip VARCHAR2(30)
96 , country VARCHAR2(30)
97 , county VARCHAR2(30)
98 , province VARCHAR2(240)
99 );
100
101 TYPE Invoice_To_Rec_Type IS RECORD
102 ( invoice_to_org_id NUMBER
103 , customer_id NUMBER
104 , order_type_id NUMBER
105 , price_list_id NUMBER
106 , payment_term_id NUMBER
107 , default_attributes VARCHAR2(1) DEFAULT 'N'
108 -- EDI attributes
109 , address_id NUMBER
110 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
111 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
112 , address1 VARCHAR2(30)
113 , address2 VARCHAR2(30)
114 , address3 VARCHAR2(30)
115 , address4 VARCHAR2(30)
116 , state VARCHAR2(30)
117 , city VARCHAR2(30)
118 , zip VARCHAR2(30)
119 , country VARCHAR2(30)
120 , county VARCHAR2(30)
121 , province VARCHAR2(240)
122 );
123
124 TYPE Sold_To_Rec_Type IS RECORD
125 ( org_id NUMBER
126 , price_list_id NUMBER
127 , fob_point_code VARCHAR2(30)
128 , freight_terms_code VARCHAR2(30)
129 , payment_term_id NUMBER
130 , ship_to_org_id NUMBER
131 , deliver_to_org_id NUMBER
132 , invoice_to_org_id NUMBER
133 , contact_id NUMBER
134 , ship_partial_allowed VARCHAR2(1)
135 , shipping_method_code VARCHAR2(30)
136 , order_type_id NUMBER
137 , default_attributes VARCHAR2(1) DEFAULT 'N'
138 -- EDI attributes
139 , tp_setup BOOLEAN
140 , address_id NUMBER
141 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
142 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
143 , address1 VARCHAR2(30)
144 , address2 VARCHAR2(30)
145 , address3 VARCHAR2(30)
146 , address4 VARCHAR2(30)
147 , state VARCHAR2(30)
148 , city VARCHAR2(30)
149 , zip VARCHAR2(30)
150 , country VARCHAR2(30)
151 , county VARCHAR2(30)
152 , province VARCHAR2(240)
153 );
154 /*sdatti*/
155 TYPE Sold_To_Site_Rec_Type IS RECORD
156 ( sold_to_site_use_id NUMBER
157 , customer_id NUMBER
158 , order_type_id NUMBER
159 , price_list_id NUMBER
160 , payment_term_id NUMBER
161 , default_attributes VARCHAR2(1) DEFAULT 'N'
162 -- EDI attributes
163 , address_id NUMBER
164 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
165 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
166 , address1 VARCHAR2(30)
167 , address2 VARCHAR2(30)
168 , address3 VARCHAR2(30)
169 , address4 VARCHAR2(30)
170 , state VARCHAR2(30)
171 , city VARCHAR2(30)
172 , zip VARCHAR2(30)
173 , country VARCHAR2(30)
174 , county VARCHAR2(30)
175 , province VARCHAR2(240)
176 );
177 /*sdatti*/
178 --{ Bug 5054618
179 /* End customer changes for HVOP */
180 TYPE End_Customer_Rec_Type IS RECORD
181 ( org_id NUMBER
182 , price_list_id NUMBER
183 , fob_point_code VARCHAR2(30)
184 , freight_terms_code VARCHAR2(30)
185 , payment_term_id NUMBER
186 , ship_to_org_id NUMBER
187 , deliver_to_org_id NUMBER
188 , invoice_to_org_id NUMBER
189 , contact_id NUMBER
190 , ship_partial_allowed VARCHAR2(1)
191 , shipping_method_code VARCHAR2(30)
192 , order_type_id NUMBER
193 , default_attributes VARCHAR2(1) DEFAULT 'N'
194 -- EDI attributes
195 , tp_setup BOOLEAN
196 , address_id NUMBER
197 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
198 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
199 , address1 VARCHAR2(30)
200 , address2 VARCHAR2(30)
201 , address3 VARCHAR2(30)
202 , address4 VARCHAR2(30)
203 , state VARCHAR2(30)
204 , city VARCHAR2(30)
205 , zip VARCHAR2(30)
206 , country VARCHAR2(30)
207 , county VARCHAR2(30)
208 , province VARCHAR2(240)
209 );
210
211 TYPE End_Cust_Site_Rec_Type IS RECORD
212 ( sold_to_site_use_id NUMBER
213 , customer_id NUMBER
214 , order_type_id NUMBER
215 , price_list_id NUMBER
216 , payment_term_id NUMBER
217 , default_attributes VARCHAR2(1) DEFAULT 'N'
218 -- EDI attributes
219 , address_id NUMBER
220 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
221 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
222 , address1 VARCHAR2(30)
223 , address2 VARCHAR2(30)
224 , address3 VARCHAR2(30)
225 , address4 VARCHAR2(30)
226 , state VARCHAR2(30)
227 , city VARCHAR2(30)
228 , zip VARCHAR2(30)
229 , country VARCHAR2(30)
230 , county VARCHAR2(30)
231 , province VARCHAR2(240)
232 );
233
234 -- Bug 5054618}
235 TYPE Price_List_Rec_Type IS RECORD
236 ( price_list_id NUMBER
237 , name VARCHAR2(240)
238 , list_type_code VARCHAR2(30)
239 , start_date_active DATE
240 , end_date_active DATE
241 , currency_code VARCHAR2(15)
242 );
243
244 TYPE Set_Of_Books_Rec_Type IS RECORD
245 ( set_of_books_id NUMBER
246 , currency_code VARCHAR2(15)
247 , default_attributes VARCHAR2(1) DEFAULT 'N'
248 );
249
250 TYPE Item_Rec_Type IS RECORD
251 ( inventory_item_id NUMBER
252 , organization_id NUMBER
253 , customer_order_enabled_flag VARCHAR2(1)
254 , internal_order_enabled_flag VARCHAR2(1)
255 , ship_model_complete_flag VARCHAR2(1)
256 , build_in_wip_flag VARCHAR2(1)
257 , replenish_to_order_flag VARCHAR2(1)
258 , pick_components_flag VARCHAR2(1)
259 , shippable_item_flag VARCHAR2(1)
260 , service_item_flag VARCHAR2(1)
261 , bom_item_type NUMBER
262 , process_warehouse_flag VARCHAR2(1)
263 --, dualum_ind NUMBER -- INVCONV
264 --, opm_item_id NUMBER -- INVCONV
265 --, opm_item_um VARCHAR2(4) -- INVCONV
266 --, opm_item_um2 VARCHAR2(4) -- INVCONV
267 --, opm_grade_ctl NUMBER -- INVCONV
268 , invoicing_rule_id NUMBER
269 , accounting_rule_id NUMBER
270 , default_shipping_org NUMBER
271 , primary_uom_code VARCHAR2(30)
272 , ship_tolerance_above NUMBER
273 , ship_tolerance_below NUMBER
274 , item_description VARCHAR2(240)
275 , hazard_class_id NUMBER
276 , weight_uom_code VARCHAR2(3)
277 , volume_uom_code VARCHAR2(3)
278 , unit_volume NUMBER
279 , unit_weight NUMBER
280 , pickable_flag VARCHAR2(1)
281 , master_container_item_id NUMBER
282 , detail_container_item_id NUMBER
283 , default_attributes VARCHAR2(1) DEFAULT 'N'
284 --bug 3798477
285 , ont_pricing_qty_source VARCHAR2(30) --INVCONV
286 , tracking_quantity_ind VARCHAR2(30)
287 , wms_enabled_flag VARCHAR2(1)
288 --bug 3798477
289 , config_model_type VARCHAR2(30)
290 , planning_make_buy_code NUMBER
291 , ordered_item VARCHAR2(2000)
292 -- INVCONV start
293 , secondary_default_ind VARCHAR2(30)
294 , lot_divisible_flag VARCHAR2(1)
295 , grade_control_flag VARCHAR2(1)
296 , lot_control_code NUMBER
297 , secondary_uom_code VARCHAR2(3)
298 -- INVCONV end
299 , full_lead_time NUMBER
300 , fixed_lead_time NUMBER
301 , variable_lead_time NUMBER
302 );
303
304
305 TYPE Salesrep_Rec_Type IS RECORD
306 ( salesrep_id NUMBER
307 , sales_credit_type_id NUMBER
308 , person_id NUMBER
309 , sales_tax_geocode VARCHAR2(30)
310 , sales_tax_inside_city_limits VARCHAR2(1)
311 );
312
313 TYPE Ship_From_Rec_Type IS RECORD
314 ( org_id NUMBER
315 -- EDI attributes
316 , address_id NUMBER
317 , edi_location_code VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
318 , location VARCHAR2(40) --Bug 7025494, changed width from 30 to 40
319 , address1 VARCHAR2(30)
320 , address2 VARCHAR2(30)
321 , address3 VARCHAR2(30)
322 , address4 VARCHAR2(30)
323 , state VARCHAR2(30)
324 , city VARCHAR2(30)
325 , zip VARCHAR2(30)
326 , country VARCHAR2(30)
327 , county VARCHAR2(30)
328 , province VARCHAR2(240)
329 );
330
331 TYPE Loc_Info_Rec_Type IS RECORD
332 ( site_use_id NUMBER
333 , cust_acct_site_id NUMBER
334 , cust_account_id NUMBER
335 , postal_code VARCHAR2(60)
336 , loc_id NUMBER
337 , party_id NUMBER
338 , party_name VARCHAR2(360)
339 , party_site_id NUMBER
340 , account_number VARCHAR2(30)
341 , acct_tax_header_level_flag VARCHAR2(1)
342 , acct_tax_rounding_rule VARCHAR2(30)
343 , state VARCHAR2(60)
344 , tax_header_level_flag VARCHAR2(1)
345 , tax_rounding_rule VARCHAR2(30)
346 );
347
348 TYPE Tax_Attributes_Rec_Type IS RECORD
349 ( amount_includes_tax_flag VARCHAR2(1)
350 , taxable_basis VARCHAR2(30)
351 , tax_calculation_plsql_block VARCHAR2(2000)
352 , vat_tax_id NUMBER
353 , start_date DATE
354 , end_date DATE
355 );
356
357 TYPE Person_Rec_Type IS RECORD
358 ( organization_id NUMBER
359 , location_id NUMBER
360 , start_date DATE
361 , end_date DATE
362 );
363
364
365 TYPE Order_Type_Tbl_Type IS TABLE OF Order_Type_Rec_Type
366 INDEX BY BINARY_INTEGER;
367
368 TYPE Line_Type_Tbl_Type IS TABLE OF Line_Type_Rec_Type
369 INDEX BY BINARY_INTEGER;
370
371 TYPE Agreement_Tbl_Type IS TABLE OF Agreement_Rec_Type
372 INDEX BY BINARY_INTEGER;
373
377 TYPE Invoice_To_Tbl_Type IS TABLE OF Invoice_To_Rec_Type
374 TYPE Ship_To_Tbl_Type IS TABLE OF Ship_To_Rec_Type
375 INDEX BY BINARY_INTEGER;
376
378 INDEX BY BINARY_INTEGER;
379 /*sdatti*/
380 TYPE Sold_To_Site_Tbl_Type IS TABLE OF Sold_To_Site_Rec_Type
381 INDEX BY BINARY_INTEGER;
382 /*sdatti*/
383
384 TYPE End_Customer_Site_Tbl_Type IS TABLE OF End_Cust_Site_Rec_Type
385 INDEX BY BINARY_INTEGER; -- end customer changes (Bug 5054618)
386
387 TYPE End_Customer_Tbl_Type IS TABLE OF End_customer_Rec_Type
388 INDEX BY BINARY_INTEGER; -- end customer changes (Bug 5054618)
389
390 TYPE Item_Tbl_Type IS TABLE OF Item_Rec_Type
391 INDEX BY BINARY_INTEGER;
392
393 TYPE Sold_To_Tbl_Type IS TABLE OF Sold_To_Rec_Type
394 INDEX BY BINARY_INTEGER;
395
396 TYPE Salesrep_Tbl_Type IS TABLE OF Salesrep_Rec_Type
397 INDEX BY BINARY_INTEGER;
398
399 TYPE Ship_From_Tbl_Type IS TABLE OF Ship_From_Rec_Type
400 INDEX BY BINARY_INTEGER;
401
402 TYPE Price_List_Tbl_Type IS TABLE OF Price_List_Rec_Type
403 INDEX BY BINARY_INTEGER;
404
405 TYPE Loc_Info_Tbl_Type IS TABLE OF Loc_Info_Rec_Type
406 INDEX BY BINARY_INTEGER;
407
408 TYPE Tax_Attributes_Tbl_Type IS TABLE OF Tax_Attributes_Rec_Type
409 INDEX BY BINARY_INTEGER;
410
411 TYPE Person_Tbl_Type IS TABLE OF Person_Rec_Type
412 INDEX BY BINARY_INTEGER;
413 ---------------------------------------------------------------------
414 -- GLOBAL CACHE TABLES
415 ---------------------------------------------------------------------
416
417 G_ORDER_TYPE_TBL Order_Type_Tbl_Type;
418 G_LINE_TYPE_TBL Line_Type_Tbl_Type;
419 G_AGREEMENT_TBL Agreement_Tbl_Type;
420 G_SHIP_TO_TBL Ship_To_Tbl_Type;
421 G_INVOICE_TO_TBL Invoice_To_Tbl_Type;
422 G_SOLD_TO_SITE_TBL Sold_To_Site_Tbl_Type;
423 G_ITEM_TBL Item_Tbl_Type;
424 G_SOLD_TO_TBL Sold_To_Tbl_Type;
425 G_SALESREP_TBL Salesrep_Tbl_Type;
426 G_SHIP_FROM_TBL Ship_From_Tbl_Type;
427 G_PRICE_LIST_TBL Price_List_Tbl_Type;
428 G_END_CUSTOMER_SITE_TBL End_Customer_Site_Tbl_Type; --Bug 5054618
429 G_END_CUSTOMER_TBL End_Customer_Tbl_Type; --Bug 5054618
430 G_LOC_INFO_TBL Loc_Info_Tbl_Type;
431 G_TAX_ATTRIBUTES_TBL Tax_Attributes_Tbl_Type;
432 G_PERSON_TBL Person_Tbl_Type;
433
434 ---------------------------------------------------------------------
435 -- PROCEDURES/FUNCTIONS
436 ---------------------------------------------------------------------
437
438 ---------------------------------------------------------------------
439 -- Functions to load cache for each entity
440 --
441 -- IN parameters:
442 -- p_key: Primary key of record that is to be cached e.g.order_type_id
443 -- for Load_Order_Type
444 --
445 -- p_default_attributes: pass 'Y' if you are using the cache to
446 -- retrieve attributes that can default from this entity e.g.
447 -- accounting rule for order type entity. These defaultable attributes
448 -- will also be validated when populating the cache and if invalid,
449 -- cache will store null.
450 --
451 -- p_edi_attributes: pass 'Y' if there are EDI specific attributes
452 -- that need to be cached for this entity. Used by acknowledgment
453 -- APIs.
454 ---------------------------------------------------------------------
455
456 FUNCTION Load_Order_Type
457 ( p_key IN NUMBER
458 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
459 )
460 RETURN NUMBER;
461
462 FUNCTION Load_Line_Type
463 ( p_key IN NUMBER
464 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
465 )
466 RETURN NUMBER;
467
468 FUNCTION Load_Agreement
469 ( p_key IN NUMBER
470 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
471 )
472 RETURN NUMBER;
473
474 FUNCTION Load_Item
475 ( p_key1 IN NUMBER
476 , p_key2 IN NUMBER
477 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
478 )
479 RETURN NUMBER;
480
481
482 FUNCTION Load_Ship_To
483 ( p_key IN NUMBER
484 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
485 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
486 )
487 RETURN NUMBER;
488
489 --{Bug 5054618
490 FUNCTION Load_End_customer
491 ( p_key IN NUMBER
492 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
493 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
494 )
495 RETURN NUMBER;
496
497 FUNCTION Load_End_Customer_Site
498 ( p_key IN NUMBER
499 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
500 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
501 )
502 RETURN NUMBER;
503 --Bug 5054618}
504
505 FUNCTION Load_Sold_To
506 ( p_key IN NUMBER
507 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
508 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
509 )
510 RETURN NUMBER;
511
512 FUNCTION Load_Invoice_To
513 ( p_key IN NUMBER
514 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
515 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
516 )
517 RETURN NUMBER;
518 /*sdatti*/
519 FUNCTION Load_Sold_To_Site
520 ( p_key IN NUMBER
521 , p_default_attributes IN VARCHAR2 DEFAULT 'N'
522 , p_edi_attributes IN VARCHAR2 DEFAULT 'N'
523 )
524 RETURN NUMBER;
525
526 /*sdatti*/
527 FUNCTION Load_Salesrep
528 ( p_key IN NUMBER
529 )
530 RETURN NUMBER;
531
532 FUNCTION Load_Ship_From
533 ( p_key IN NUMBER
534 )
535 RETURN NUMBER;
536
537 FUNCTION Load_Price_List
538 ( p_key IN NUMBER
539 )
540 RETURN NUMBER;
541
542 FUNCTION IS_CC_REQUIRED
543 ( p_key IN NUMBER
544 )
545 RETURN BOOLEAN;
546
547 FUNCTION Load_Loc_Info
548 ( p_key IN NUMBER
549 )
550 RETURN NUMBER;
551
552 FUNCTION Load_Tax_Attributes
553 ( p_key IN VARCHAR2,
554 p_tax_date IN DATE
555 )
556 RETURN NUMBER;
557
558 FUNCTION Load_Person
559 ( p_key IN NUMBER,
560 p_tax_date IN DATE
561 )
562 RETURN NUMBER;
563
564 END OE_BULK_CACHE;