[Home] [Help]
PACKAGE: APPS.QP_BULK_LOADER_PUB
Source
1 PACKAGE QP_BULK_LOADER_PUB AUTHID CURRENT_USER AS
2 /* $Header: QPXVBLKS.pls 120.6.12010000.1 2008/07/28 11:58:13 appldev ship $ */
3
4 TYPE num_type IS TABLE OF Number INDEX BY BINARY_INTEGER;
5 TYPE char_type IS TABLE OF Varchar2(1) INDEX BY BINARY_INTEGER;
6 TYPE char30_type IS TABLE OF Varchar2(30) INDEX BY BINARY_INTEGER;
7 TYPE char2_type IS TABLE OF Varchar2(2) INDEX BY BINARY_INTEGER;
8 TYPE char240_type IS TABLE OF Varchar2(240) INDEX BY BINARY_INTEGER;
9 TYPE char4_type IS TABLE OF Varchar2(4) INDEX BY BINARY_INTEGER;
10 TYPE char3_type IS TABLE OF Varchar2(3) INDEX BY BINARY_INTEGER;
11 TYPE char50_type IS TABLE OF Varchar2(50) INDEX BY BINARY_INTEGER;
12 TYPE char2000_type IS TABLE OF Varchar2(2000) INDEX BY BINARY_INTEGER;
13 TYPE char10_type IS TABLE OF Varchar2(10) INDEX BY BINARY_INTEGER;
14 TYPE real_type IS TABLE OF Number(32,10) INDEX BY BINARY_INTEGER;
15 TYPE date_type IS TABLE OF Date INDEX BY BINARY_INTEGER;
16
17 TYPE HEADER_REC_TYPE IS RECORD
18 (
19 list_header_id num_type,
20 creation_date date_type,
21 created_by num_type,
22 last_update_date date_type,
23 last_updated_by num_type,
24 last_update_login num_type,
25 program_application_id num_type,
26 program_id num_type,
27 program_update_date date_type,
28 request_id num_type,
29 list_type_code char30_type,
30 start_date_active char30_type,
31 end_date_active char30_type,
32 source_lang char4_type,
33 automatic_flag char_type,
34 name char240_type,
35 description char2000_type,
36 currency_code char30_type,
37 version_no char30_type,
38 rounding_factor num_type,
39 ship_method_code char30_type,
40 freight_terms_code char30_type,
41 terms_id num_type,
42 comments char2000_type,
43 discount_lines_flag char_type,
44 gsa_indicator char_type,
45 prorate_flag char30_type,
46 source_system_code char30_type,
47 ask_for_flag char_type,
48 active_flag char_type,
49 parent_list_header_id num_type,
50 active_date_first_type char30_type,
51 start_date_active_first date_type,
52 end_date_active_first date_type,
53 active_date_second_type char30_type,
54 start_date_active_second date_type,
55 end_date_active_second date_type,
56 context char30_type,
57 attribute1 char240_type,
58 attribute2 char240_type,
59 attribute3 char240_type,
60 attribute4 char240_type,
61 attribute5 char240_type,
62 attribute6 char240_type,
63 attribute7 char240_type,
64 attribute8 char240_type,
65 attribute9 char240_type,
66 attribute10 char240_type,
67 attribute11 char240_type,
68 attribute12 char240_type,
69 attribute13 char240_type,
70 attribute14 char240_type,
71 attribute15 char240_type,
72 language char4_type,
73 process_id num_type,
74 process_type char30_type,
75 interface_action_code char30_type,
76 lock_flag char2_type,
77 process_flag char2_type,
78 delete_flag char2_type,
79 process_status_flag char2_type,
80 mobile_download char_type,
81 currency_header_id num_type,
82 pte_code char30_type,
83 list_source_code char30_type,
84 orig_sys_header_ref char50_type,
85 orig_org_id num_type,
86 global_flag char_type
87 );
88
89
90 TYPE LINE_REC_TYPE IS RECORD
91 (
92 list_line_id num_type
93 ,creation_date date_type
94 ,created_by num_type
95 ,last_update_date date_type
96 ,last_updated_by num_type
97 ,last_update_login num_type
98 ,program_application_id num_type
99 ,program_id num_type
100 ,program_update_date date_type
101 ,request_id num_type
102 ,list_header_id num_type
103 ,list_line_type_code char30_type
104 ,start_date_active date_type
105 ,end_date_active date_type
106 ,automatic_flag char_type
107 ,modifier_level_code char30_type
108 ,price_by_formula_id num_type
109 ,list_price num_type
110 ,list_price_uom_code char3_type
111 ,primary_uom_flag char_type
112 ,inventory_item_id num_type
113 ,organization_id num_type
114 ,related_item_id num_type
115 ,relationship_type_id num_type
116 ,substitution_context char30_type
117 ,substitution_attribute char30_type
118 ,substitution_value char240_type
119 ,revision char50_type
120 ,revision_date date_type
121 ,revision_reason_code char30_type
122 ,price_break_type_code char30_type
123 ,percent_price num_type
124 ,number_effective_periods num_type
125 ,effective_period_uom char3_type
126 ,arithmetic_operator char30_type
127 ,operand real_type
128 ,override_flag char_type
129 ,print_on_invoice_flag char_type
130 ,rebate_transaction_type_code char30_type
131 ,base_qty num_type
132 ,base_uom_code char3_type
133 ,accrual_qty num_type
134 ,accrual_uom_code char3_type
135 ,estim_accrual_rate num_type
136 ,process_id num_type
137 ,process_type char30_type
138 ,interface_action_code char30_type
139 ,lock_flag char2_type
140 ,process_flag char2_type
141 ,delete_flag char2_type
142 ,process_status_flag char2_type
143 ,comments char2000_type
144 ,generate_using_formula_id num_type
145 ,reprice_flag char_type
146 ,list_line_no char30_type
147 ,estim_gl_value num_type
148 ,benefit_price_list_line_id num_type
149 ,expiration_period_start_date date_type
150 ,number_expiration_periods num_type
151 ,expiration_period_uom char3_type
152 ,expiration_date date_type
153 ,accrual_flag char_type
154 ,pricing_phase_id num_type
155 ,pricing_group_sequence num_type
156 ,incompatibility_grp_code char30_type
157 ,product_precedence num_type
158 ,proration_type_code char30_type
159 ,accrual_conversion_rate num_type
160 ,benefit_qty num_type
161 ,benefit_uom_code char3_type
162 ,recurring_flag char_type
163 ,benefit_limit num_type
164 ,charge_type_code char30_type
165 ,charge_subtype_code char30_type
166 ,include_on_returns_flag char_type
167 ,qualification_ind num_type
168 ,context char30_type
169 ,attribute1 char240_type
170 ,attribute2 char240_type
171 ,attribute3 char240_type
172 ,attribute4 char240_type
173 ,attribute5 char240_type
174 ,attribute6 char240_type
175 ,attribute7 char240_type
176 ,attribute8 char240_type
177 ,attribute9 char240_type
178 ,attribute10 char240_type
179 ,attribute11 char240_type
180 ,attribute12 char240_type
181 ,attribute13 char240_type
182 ,attribute14 char240_type
183 ,attribute15 char240_type
184 ,rltd_modifier_grp_no num_type
185 ,rltd_modifier_grp_type char30_type
186 ,price_break_header_ref char50_type
187 ,pricing_phase_name char240_type
188 ,recurring_value num_type
189 ,net_amount_flag char_type
190 ,price_by_formula char50_type
191 ,generate_using_formula char50_type
192 ,attribute_status char50_type
193 ,orig_sys_line_ref char50_type
194 ,orig_sys_header_ref char50_type
195 --Bug#5359974 RAVI
196 ,continuous_price_break_flag char_type
197 );
198
199 TYPE PRICING_ATTR_REC_TYPE IS RECORD
200 (
201 pricing_attribute_id num_type
202 ,creation_date date_type
203 ,created_by num_type
204 ,last_update_date date_type
205 ,last_update_by num_type
206 ,last_update_login num_type
207 ,program_application_id num_type
208 ,program_id num_type
209 ,program_update_date date_type
210 ,request_id num_type
211 ,list_line_id num_type
212 ,excluder_flag char_type
213 ,accumulate_flag char_type
214 ,product_attribute_context char30_type
215 ,product_attribute char30_type
216 ,product_attr_value char240_type
217 ,product_uom_code char3_type
218 ,pricing_attribute_context char30_type
219 ,pricing_attribute char30_type
220 ,pricing_attr_value_from char240_type
221 ,pricing_attr_value_to char240_type
222 ,attribute_grouping_no num_type
223 ,product_attribute_datatype char30_type
224 ,pricing_attribute_datatype char30_type
225 ,comparison_operator_code char30_type
226 ,list_header_id num_type
227 ,pricing_phase_id num_type
228 ,qualification_ind num_type
229 ,pricing_attr_value_from_number num_type
230 ,pricing_attr_value_to_number num_type
231 ,context char30_type
232 ,attribute1 char240_type
233 ,attribute2 char240_type
234 ,attribute3 char240_type
235 ,attribute4 char240_type
236 ,attribute5 char240_type
237 ,attribute6 char240_type
238 ,attribute7 char240_type
239 ,attribute8 char240_type
240 ,attribute9 char240_type
241 ,attribute10 char240_type
242 ,attribute11 char240_type
243 ,attribute12 char240_type
244 ,attribute13 char240_type
245 ,attribute14 char240_type
246 ,attribute15 char240_type
247 ,process_id num_type
248 ,process_type char30_type
249 ,interface_action_code char30_type
250 ,lock_flag char2_type
251 ,process_flag char2_type
252 ,delete_flag char2_type
253 ,process_status_flag char2_type
254 ,price_list_line_index num_type
255 ,list_line_no char30_type
256 ,orig_sys_pricing_attr_ref char50_type
257 ,product_attr_code char50_type
258 ,product_attr_val_disp char50_type
259 ,pricing_attr_code char50_type
260 ,pricing_attr_value_from_disp char50_type
261 ,pricing_attr_value_to_disp char50_type
262 ,attribute_status char50_type
263 ,orig_sys_line_ref char50_type
264 ,orig_sys_header_ref char50_type
265 );
266
267 TYPE QUALIFIER_REC_TYPE IS RECORD
268 (
269 qualifier_id num_type
270 ,creation_date date_type
271 ,created_by num_type
272 ,last_update_date date_type
273 ,last_update_by num_type
274 ,request_id num_type
275 ,program_application_id num_type
276 ,program_id num_type
277 ,program_update_date date_type
278 ,last_update_login num_type
279 ,qualifier_grouping_no num_type
280 ,qualifier_context char30_type
281 ,qualifier_attribute char30_type
282 ,qualifier_attr_value char240_type
283 ,qualifier_attr_value_to char240_type
284 ,qualifier_datatype char10_type
285 ,qualifier_precedence num_type
286 ,comparison_operator_code char30_type
287 ,excluder_flag char_type
288 ,start_date_active date_type
289 ,end_date_active date_type
290 ,list_header_id num_type
291 ,list_line_id num_type
292 ,qualifier_rule_id num_type
293 ,created_from_rule_id num_type
294 ,active_flag char_type
295 ,list_type_code char30_type
296 ,qual_attr_value_from_number num_type
297 ,qual_attr_value_to_number num_type
298 ,qualifier_group_cnt num_type
299 ,header_quals_exist_flag char_type
300 ,context char30_type
301 ,attribute1 char240_type
302 ,attribute2 char240_type
306 ,attribute6 char240_type
303 ,attribute3 char240_type
304 ,attribute4 char240_type
305 ,attribute5 char240_type
307 ,attribute7 char240_type
308 ,attribute8 char240_type
309 ,attribute9 char240_type
310 ,attribute10 char240_type
311 ,attribute11 char240_type
312 ,attribute12 char240_type
313 ,attribute13 char240_type
314 ,attribute14 char240_type
315 ,attribute15 char240_type
316 ,process_id num_type
317 ,process_type char30_type
318 ,interface_action_code char30_type
319 ,lock_flag char2_type
320 ,process_flag char2_type
321 ,delete_flag char2_type
322 ,process_status_flag char2_type
323 ,list_line_no char30_type
324 ,ORIG_SYS_QUALIFIER_REF char50_type
325 ,CREATED_FROM_RULE char50_type
326 ,QUALIFIER_RULE char50_type
327 ,QUALIFIER_ATTRIBUTE_CODE char50_type
328 ,QUALIFIER_ATTR_VALUE_CODE char50_type
329 ,QUALIFIER_ATTR_VALUE_TO_CODE char50_type
330 ,ATTRIBUTE_STATUS char50_type
331 ,ORIG_SYS_HEADER_REF char50_type
332 ,orig_sys_line_ref char50_type
333 ,QUALIFY_HIER_DESCENDENTS_FLAG char_type
334 );
335
336 TYPE thread_info_rec_type IS RECORD
337 ( request_id NUMBER
338 ,total_lines NUMBER:=0
339 );
340
341 TYPE thread_info_tbl_type IS TABLE OF thread_info_rec_type INDEX BY BINARY_INTEGER;
342
343 G_THREAD_INFO_TABLE thread_info_tbl_type;
344
345 G_INS_HEADER_REC header_rec_type;
346 G_UDT_HEADER_REC header_rec_type;
347
348 G_INS_LINE_REC line_rec_type;
349 G_UDT_LINE_REC line_rec_type;
350 G_UDT_LINE_REC_OLD line_rec_type; -- 6028305
351 G_INS_QUALIFIER_REC qualifier_rec_type;
352 G_UDT_QUALIFIER_REC qualifier_rec_type;
353 G_INS_PRICING_ATTR_REC pricing_attr_rec_type;
354 G_UDT_PRICING_ATTR_REC pricing_attr_rec_type;
355
356 G_QP_STATUS VARCHAR2(1):=NULL;
357 G_QP_DEBUG VARCHAR2(1):='N';
358 G_QP_BATCH_SIZE NUMBER;
359 -- ENH duplicate line check flag RAVI
360 G_QP_ENABLE_DUP_LINE_CHECK VARCHAR2(1):='Y';
361
362
363 --ENH Update Functionality START RAVI
364 /**
365 During update the null columns in interface tables are populated from the
366 qp tables. If the column is to be updated with null value then the following
367 constants should be entered in the interface columns.
368 **/
369 G_NULL_DATE CONSTANT DATE := to_date('01/01/1001', 'MM/DD/YYYY');
370 G_NULL_CHAR CONSTANT VARCHAR2(1) := 'Z';
371 G_NULL_NUMBER CONSTANT NUMBER := 100010001000100010001000;
372
373 FUNCTION GET_NULL_DATE RETURN DATE;
374 FUNCTION GET_NULL_CHAR RETURN VARCHAR2;
375 FUNCTION GET_NULL_NUMBER RETURN NUMBER;
376
377 --ENH Update Functionality END RAVI
378
379
380 -- ENH duplicate line check flag RAVI
381 PROCEDURE LOAD_PRICING_DATA
382 (
383 err_buff OUT NOCOPY VARCHAR2
384 ,retcode OUT NOCOPY NUMBER
385 ,p_entity VARCHAR2
386 ,p_entity_name VARCHAR2
387 ,p_process_id NUMBER
388 ,p_process_type VARCHAR2
389 ,p_process_parent VARCHAR2 DEFAULT 'Y'
390 ,p_no_of_threads NUMBER DEFAULT 1
391 ,p_spawned_request VARCHAR2 DEFAULT 'N'
392 ,p_request_id NUMBER DEFAULT NULL
393 ,p_debug_on VARCHAR2
394 ,p_enable_dup_ln_check VARCHAR2
395 );
396
397 PROCEDURE LOAD_LISTS
398 (
399 err_buff OUT NOCOPY VARCHAR2
400 ,retcode OUT NOCOPY NUMBER
401 ,p_entity_name VARCHAR2
402 ,p_process_id NUMBER
403 ,p_process_type VARCHAR2
404 ,p_process_parent VARCHAR2
405 ,p_no_of_threads NUMBER
406 ,p_spawned_request VARCHAR2
407 ,p_request_id NUMBER );
408
409 PROCEDURE PROCESS_HEADER
410 (p_request_id NUMBER);
411
412 PROCEDURE PROCESS_QUALIFIER
413 (p_request_id NUMBER);
414
415 PROCEDURE PROCESS_LINE
416 (p_request_id NUMBER,
417 p_process_parent varchar2); -- 6028305
418
419 PROCEDURE PROCESS_PRICING_ATTR
420 (p_request_id NUMBER,
421 p_process_parent varchar2); -- 6028305
422
423 PROCEDURE POST_CLEANUP
424 (p_request_id NUMBER);
425
426 PROCEDURE POST_CLEANUP_LINE
427 (p_request_id NUMBER);
428
429 PROCEDURE VALIDATE_LINES
430 (p_request_id NUMBER);
431
432 PROCEDURE Delete_errored_records_parents
433 (p_request_id NUMBER);
434
435 PROCEDURE ERRORS_TO_OUTPUT
436 (p_request_id NUMBER);
437
438 PROCEDURE Purge
439 (p_request_id NUMBER);
440
441 FUNCTION Get_QP_Status RETURN VARCHAR2;
442
443 PROCEDURE write_log
444 (log_text VARCHAR2);
445
446 PROCEDURE CLEAN_UP_CODE
447 (l_request_id NUMBER);
448
449 END QP_BULK_LOADER_PUB;