[Home] [Help]
PACKAGE BODY: APPS.QP_BULK_EXPORT_TMP_PVT
Source
1 PACKAGE BODY qp_bulk_export_tmp_pvt AS
2 /* $Header: QPXBTEXB.pls 120.2 2005/11/07 01:45:05 srashmi noship $ */
3 PROCEDURE EXPORT_TMP_PRICING_DATA
4 (
5 err_buff OUT NOCOPY VARCHAR2
6 ,retcode OUT NOCOPY NUMBER
7 ,list_from NUMBER
8 ,list_to NUMBER
9 ,p_entity_name VARCHAR2
10 ,interface_action VARCHAR2
11 )
12 IS
13 CURSOR C_PRIC_HEADER IS
14 select unique list_header_id
15 from qp_list_headers_b
16 where list_header_id between list_from and list_to;
17
18 l_list_header_id number;
19
20 BEGIN
21
22 fnd_file.put_line(fnd_file.log, 'Start the export of Pricelists to the Interface table');
23 fnd_file.put_line(fnd_file.log, 'Price List ID From: '||to_char(list_from));
24 fnd_file.put_line(fnd_file.log, 'Price List ID TO : '||to_char(list_to));
25
26 fnd_file.put_line(fnd_file.log, 'Interface Action Code: '||interface_action);
27 if interface_action is not NULL and
28 (interface_action = 'INSERT' or
29 interface_action = 'UPDATE' or
30 interface_action = 'DELETE') then
31 g_interface_action := interface_action;
32 else
33 g_interface_action := 'INSERT';
34 end if;
35 fnd_file.put_line(fnd_file.log, 'G Interface Action Code: '||g_interface_action);
36 OPEN C_PRIC_HEADER;
37 LOOP
38 FETCH C_PRIC_HEADER into l_list_header_id;
39
40 EXIT WHEN C_PRIC_HEADER%NOTFOUND;
41
42 fnd_file.put_line(fnd_file.log, 'Processing Price List : '||to_char(l_list_header_id));
43 export_tmp_lists(
44 list_from => l_list_header_id,
45 p_entity_name => p_entity_name);
46 commit;
47 fnd_file.put_line(fnd_file.log, 'END Processing Price List : '||to_char(l_list_header_id));
48 END LOOP;
49 fnd_file.put_line(fnd_file.log, 'END Processing ');
50 END;
51
52 PROCEDURE EXPORT_TMP_LISTS
53 (
54 list_from NUMBER
55 ,p_entity_name VARCHAR2
56 )
57 IS
58 l_PRICE_LIST_rec QP_Price_List_PUB.Price_List_Rec_Type;
59
60 CURSOR C_PRIC_QUALIFIERS IS
61 SELECT QUALIFIER_ID
62 FROM QP_QUALIFIERS
63 WHERE LIST_HEADER_ID=LIST_FROM;
64
65 CURSOR C_PRIC_LINES IS
66 SELECT LIST_LINE_ID
67 FROM QP_LIST_LINES
68 WHERE LIST_HEADER_ID=LIST_FROM;
69
70 CURSOR C_PRIC_ATTRIBS IS
71 SELECT PRICING_ATTRIBUTE_ID
72 FROM QP_PRICING_ATTRIBUTES
73 WHERE LIST_HEADER_ID = LIST_FROM;
74
75 l_list_line_id number;
76 l_list_qualifier_id number;
77 l_list_attrib_id number;
78 l_orig_sys_header_ref varchar2(50);
79
80 BEGIN
81 fnd_file.put_line(fnd_file.log, 'In EXPORT_TMP_LISTS ');
82 l_PRICE_LIST_rec := QP_Price_List_Util.Query_Row
83 ( p_list_header_id => list_from
84 );
85 fnd_file.put_line(fnd_file.log, 'Inserting Pricelist '||l_PRICE_LIST_rec.name);
86 if g_interface_action = 'INSERT' then
87 l_orig_sys_header_ref := p_entity_name || l_PRICE_LIST_rec.list_header_id;
88 else
89 begin
90 select orig_system_header_ref
91 into l_orig_sys_header_ref
92 from qp_list_headers_b
93 where list_header_id = l_PRICE_LIST_rec.list_header_id;
94 exception
95 when others then
96 null;
97 end;
98 end if;
99 INSERT INTO qp_interface_list_headers
100 (list_header_id
101 ,creation_date
102 ,created_by
103 ,last_update_date
104 ,last_updated_by
105 ,last_update_login
106 ,program_application_id
107 ,program_id
108 ,program_update_date
109 ,request_id
110 ,list_type_code
111 ,start_date_active
112 ,end_date_active
113 ,source_lang
114 ,automatic_flag
115 ,name
116 ,description
117 ,currency_code
118 ,version_no
119 ,rounding_factor
120 ,ship_method_code
121 ,freight_terms_code
122 ,terms_id
123 ,comments
124 ,discount_lines_flag
125 ,gsa_indicator
126 ,prorate_flag
127 ,source_system_code
128 ,ask_for_flag
129 ,active_flag
130 ,parent_list_header_id
131 ,active_date_first_type
132 ,start_date_active_first
133 ,end_date_active_first
134 ,active_date_second_type
135 ,start_date_active_second
136 ,end_date_active_second
137 ,context
138 ,attribute1
139 ,attribute2
140 ,attribute3
141 ,attribute4
142 ,attribute5
143 ,attribute6
144 ,attribute7
145 ,attribute8
146 ,attribute9
147 ,attribute10
148 ,attribute11
149 ,attribute12
150 ,attribute13
151 ,attribute14
152 ,attribute15
153 ,language
154 ,process_id
155 ,process_type
156 ,interface_action_code
157 ,lock_flag
158 ,process_flag
159 ,delete_flag
160 ,process_status_flag
161 ,mobile_download
162 ,currency_header_id
163 ,pte_code
164 ,list_source_code
165 ,orig_sys_header_ref
166 ,orig_org_id
167 ,global_flag)
168 values
169 (l_PRICE_LIST_rec.list_header_id
170 ,l_PRICE_LIST_rec.creation_date
171 ,l_PRICE_LIST_rec.created_by
172 ,l_PRICE_LIST_rec.last_update_date
173 ,l_PRICE_LIST_rec.last_updated_by
174 ,l_PRICE_LIST_rec.last_update_login
175 ,l_PRICE_LIST_rec.program_application_id
176 ,l_PRICE_LIST_rec.program_id
177 ,l_PRICE_LIST_rec.program_update_date
178 ,NULL
179 ,l_PRICE_LIST_rec.list_type_code
180 ,fnd_date.date_to_canonical(l_PRICE_LIST_rec.start_date_active)
181 ,fnd_date.date_to_canonical(l_PRICE_LIST_rec.end_date_active)
182 ,'US'
183 ,l_PRICE_LIST_rec.automatic_flag
184 ,(p_entity_name || l_PRICE_LIST_rec.name)
185 ,l_PRICE_LIST_rec.description
186 ,l_PRICE_LIST_rec.currency_code
187 ,l_PRICE_LIST_rec.version_no
188 ,l_PRICE_LIST_rec.rounding_factor
189 ,l_PRICE_LIST_rec.ship_method_code
190 ,l_PRICE_LIST_rec.freight_terms_code
191 ,l_PRICE_LIST_rec.terms_id
192 ,l_PRICE_LIST_rec.comments
193 ,l_PRICE_LIST_rec.discount_lines_flag
194 ,l_PRICE_LIST_rec.gsa_indicator
195 ,l_PRICE_LIST_rec.prorate_flag
196 ,l_PRICE_LIST_rec.source_system_code
197 ,NULL --l_PRICE_LIST_rec.ask_for_flag
198 ,l_PRICE_LIST_rec.active_flag
199 ,NULL --l_PRICE_LIST_rec.parent_list_header_id
200 ,NULL --l_PRICE_LIST_rec.active_date_first_type
201 ,NULL --l_PRICE_LIST_rec.start_date_active_first
202 ,NULL --l_PRICE_LIST_rec.end_date_active_first
203 ,NULL --l_PRICE_LIST_rec.active_date_second_type
204 ,NULL --l_PRICE_LIST_rec.start_date_active_second
205 ,NULL --l_PRICE_LIST_rec.end_date_active_second
206 ,l_PRICE_LIST_rec.context
207 ,l_PRICE_LIST_rec.attribute1
208 ,l_PRICE_LIST_rec.attribute2
209 ,l_PRICE_LIST_rec.attribute3
210 ,l_PRICE_LIST_rec.attribute4
211 ,l_PRICE_LIST_rec.attribute5
212 ,l_PRICE_LIST_rec.attribute6
213 ,l_PRICE_LIST_rec.attribute7
214 ,l_PRICE_LIST_rec.attribute8
215 ,l_PRICE_LIST_rec.attribute9
216 ,l_PRICE_LIST_rec.attribute10
217 ,l_PRICE_LIST_rec.attribute11
218 ,l_PRICE_LIST_rec.attribute12
219 ,l_PRICE_LIST_rec.attribute13
220 ,l_PRICE_LIST_rec.attribute14
221 ,l_PRICE_LIST_rec.attribute15
222 ,'US'
223 ,NULL
224 ,NULL
225 ,DECODE( g_interface_action, 'DELETE', 'UPDATE', g_interface_action) --,'INSERT'
226 ,NULL
227 ,'Y'
228 ,NULL
229 ,'P' --NULL
230 ,l_PRICE_LIST_rec.mobile_download
231 ,l_PRICE_LIST_rec.currency_header_id
232 ,l_PRICE_LIST_rec.pte_code
233 ,l_PRICE_LIST_rec.list_source_code
234 ,l_orig_sys_header_ref
235 --added for MOAC - uncommented below for MOAC
236 ,l_PRICE_LIST_rec.org_id
237 ,l_PRICE_LIST_rec.global_flag);
238
239 fnd_file.put_line(fnd_file.log, 'Inserted Pricelist ORIG_SYS_HEADER_REF'|| p_entity_name || l_PRICE_LIST_rec.list_header_id);
240
241 OPEN C_PRIC_QUALIFIERS;
242 LOOP
243 FETCH C_PRIC_QUALIFIERS into l_list_qualifier_id;
244 EXIT WHEN C_PRIC_QUALIFIERS%NOTFOUND;
245 fnd_file.put_line(fnd_file.log, 'Processing Qualifier : '||to_char(l_list_qualifier_id));
246 export_tmp_qualifiers(
247 list_from => l_list_qualifier_id,
248 p_entity_name => p_entity_name);
249
250 END LOOP;
251 fnd_file.put_line(fnd_file.log, 'END Processing Qualifier : '||to_char(l_list_qualifier_id));
252
253 OPEN C_PRIC_LINES;
254 LOOP
255 FETCH C_PRIC_LINES into l_list_line_id;
256 EXIT WHEN C_PRIC_LINES%NOTFOUND;
257 fnd_file.put_line(fnd_file.log, 'Processing Price List line : '||to_char(l_list_line_id));
258 export_tmp_lines(
259 list_from => l_list_line_id,
260 p_entity_name => p_entity_name);
261
262 END LOOP;
263 fnd_file.put_line(fnd_file.log, 'END Processing Price List Line: '||to_char(l_list_line_id));
264
265 OPEN C_PRIC_ATTRIBS;
266 LOOP
267 FETCH C_PRIC_ATTRIBS into l_list_attrib_id;
268 EXIT WHEN C_PRIC_ATTRIBS%NOTFOUND;
269 fnd_file.put_line(fnd_file.log, 'Processing Product/Price Attribs : '||to_char(l_list_attrib_id));
270 export_tmp_attribs(
271 list_from => l_list_attrib_id,
272 p_entity_name => p_entity_name);
273
274 END LOOP;
275 fnd_file.put_line(fnd_file.log, 'END Processing Product/Price Attribs : '||to_char(l_list_attrib_id));
276 END ;
277
278 PROCEDURE EXPORT_TMP_QUALIFIERS
279 (
280 list_from NUMBER
281 ,p_entity_name VARCHAR2
282 )
283 IS
284 l_QUALIFIER_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
285 l_orig_sys_header_ref varchar2(50);
286 l_orig_sys_line_ref varchar2(50);
287 l_orig_sys_qualifier_ref varchar2(50);
288
289 Begin
290 fnd_file.put_line(fnd_file.log, 'In EXPORT_TMP_QUALIFIERS ');
291 l_QUALIFIER_rec := QP_QUALIFIERS_UTIL.Query_Row
292 ( p_qualifier_id => list_from
293 );
294 fnd_file.put_line(fnd_file.log, 'Inserting Qualifier '||to_char(l_QUALIFIER_rec.qualifier_id));
295 if g_interface_action = 'INSERT' then
296 l_orig_sys_qualifier_ref := p_entity_name || l_QUALIFIER_rec.qualifier_id;
297 l_orig_sys_header_ref := p_entity_name || l_QUALIFIER_rec.list_header_id;
298 else
299 begin
300 select orig_sys_qualifier_ref, orig_sys_header_ref
301 into l_orig_sys_qualifier_ref, l_orig_sys_header_ref
302 from qp_qualifiers
303 where qualifier_id = l_QUALIFIER_rec.qualifier_id;
304 exception
305 when others then
306 null;
307 end;
308 end if;
309 INSERT INTO QP_INTERFACE_QUALIFIERS
310 (QUALIFIER_ID
311 ,REQUEST_ID
312 ,QUALIFIER_GROUPING_NO
313 ,QUALIFIER_CONTEXT
314 ,QUALIFIER_ATTRIBUTE
315 ,QUALIFIER_ATTR_VALUE
316 ,QUALIFIER_ATTR_VALUE_TO
317 ,QUALIFIER_DATATYPE
318 ,QUALIFIER_PRECEDENCE
319 ,COMPARISON_OPERATOR_CODE
320 ,EXCLUDER_FLAG
321 ,START_DATE_ACTIVE
322 ,END_DATE_ACTIVE
323 ,LIST_HEADER_ID
324 ,LIST_LINE_ID
325 ,QUALIFIER_RULE_ID
326 ,CREATED_FROM_RULE_ID
327 ,ACTIVE_FLAG
328 ,LIST_TYPE_CODE
329 ,QUAL_ATTR_VALUE_FROM_NUMBER
330 ,QUAL_ATTR_VALUE_TO_NUMBER
331 ,QUALIFIER_GROUP_CNT
332 ,HEADER_QUALS_EXIST_FLAG
333 ,CONTEXT
334 ,ATTRIBUTE1
335 ,ATTRIBUTE2
336 ,ATTRIBUTE3
337 ,ATTRIBUTE4
338 ,ATTRIBUTE5
339 ,ATTRIBUTE6
340 ,ATTRIBUTE7
341 ,ATTRIBUTE8
342 ,ATTRIBUTE9
343 ,ATTRIBUTE10
344 ,ATTRIBUTE11
345 ,ATTRIBUTE12
346 ,ATTRIBUTE13
347 ,ATTRIBUTE14
348 ,ATTRIBUTE15
349 ,PROCESS_ID
350 ,PROCESS_TYPE
351 ,INTERFACE_ACTION_CODE
352 ,LOCK_FLAG
353 ,PROCESS_FLAG
354 ,DELETE_FLAG
355 ,PROCESS_STATUS_FLAG
356 ,LIST_LINE_NO
357 ,CREATED_FROM_RULE
358 ,QUALIFIER_RULE
359 ,QUALIFIER_ATTRIBUTE_CODE
360 ,QUALIFIER_ATTR_VALUE_CODE
361 ,QUALIFIER_ATTR_VALUE_TO_CODE
362 ,ATTRIBUTE_STATUS
363 ,ORIG_SYS_HEADER_REF
364 ,ORIG_SYS_QUALIFIER_REF
365 ,ORIG_SYS_LINE_REF
366 ,QUALIFY_HIER_DESCENDENTS_FLAG)
367 values
368 (l_Qualifier_Rec.QUALIFIER_ID
369 ,l_Qualifier_Rec.REQUEST_ID
370 ,l_Qualifier_Rec.QUALIFIER_GROUPING_NO
371 ,l_Qualifier_Rec.QUALIFIER_CONTEXT
372 ,l_Qualifier_Rec.QUALIFIER_ATTRIBUTE
373 ,l_Qualifier_Rec.QUALIFIER_ATTR_VALUE
374 ,l_Qualifier_Rec.QUALIFIER_ATTR_VALUE_TO
375 ,l_Qualifier_Rec.QUALIFIER_DATATYPE
376 ,l_Qualifier_Rec.QUALIFIER_PRECEDENCE
377 ,l_Qualifier_Rec.COMPARISON_OPERATOR_CODE
378 ,l_Qualifier_Rec.EXCLUDER_FLAG
379 ,l_Qualifier_Rec.START_DATE_ACTIVE
380 ,l_Qualifier_Rec.END_DATE_ACTIVE
381 ,l_Qualifier_Rec.LIST_HEADER_ID
382 ,l_Qualifier_Rec.LIST_LINE_ID
383 ,l_Qualifier_Rec.QUALIFIER_RULE_ID
384 ,l_Qualifier_Rec.CREATED_FROM_RULE_ID
385 ,l_Qualifier_Rec.ACTIVE_FLAG
386 ,l_Qualifier_Rec.LIST_TYPE_CODE
387 ,l_Qualifier_Rec.QUAL_ATTR_VALUE_FROM_NUMBER
388 ,l_Qualifier_Rec.QUAL_ATTR_VALUE_TO_NUMBER
389 ,l_Qualifier_Rec.QUALIFIER_GROUP_CNT
390 ,l_Qualifier_Rec.HEADER_QUALS_EXIST_FLAG
391 ,l_Qualifier_Rec.CONTEXT
392 ,l_Qualifier_Rec.ATTRIBUTE1
393 ,l_Qualifier_Rec.ATTRIBUTE2
394 ,l_Qualifier_Rec.ATTRIBUTE3
395 ,l_Qualifier_Rec.ATTRIBUTE4
396 ,l_Qualifier_Rec.ATTRIBUTE5
397 ,l_Qualifier_Rec.ATTRIBUTE6
398 ,l_Qualifier_Rec.ATTRIBUTE7
399 ,l_Qualifier_Rec.ATTRIBUTE8
400 ,l_Qualifier_Rec.ATTRIBUTE9
401 ,l_Qualifier_Rec.ATTRIBUTE10
402 ,l_Qualifier_Rec.ATTRIBUTE11
403 ,l_Qualifier_Rec.ATTRIBUTE12
404 ,l_Qualifier_Rec.ATTRIBUTE13
405 ,l_Qualifier_Rec.ATTRIBUTE14
406 ,l_Qualifier_Rec.ATTRIBUTE15
407 ,NULL
408 ,NULL
409 ,g_interface_action --,'INSERT'
410 ,NULL
411 ,'Y'
412 ,NULL
413 ,'P' --NULL
414 ,NULL --l_Qualifier_Rec.LIST_LINE_NO
415 ,NULL
416 ,NULL
417 ,NULL
418 ,NULL
419 ,NULL
420 ,NULL
421 ,l_orig_sys_header_ref --(p_entity_name || to_char(l_Qualifier_Rec.list_header_id))
422 ,l_orig_sys_qualifier_ref --(p_entity_name || to_char(l_Qualifier_Rec.qualifier_id))
423 ,NULL
424 ,l_Qualifier_Rec.QUALIFY_HIER_DESCENDENT_FLAG);
425 fnd_file.put_line(fnd_file.log, 'Inserted Qualifier '|| (p_entity_name || to_char(l_Qualifier_Rec.qualifier_id)));
426 end;
427
428 PROCEDURE EXPORT_TMP_LINES
429 (
430 list_from NUMBER
431 ,p_entity_name VARCHAR2
432 )
433 IS
434 l_LINE_rec QP_Price_List_PUB.Price_List_Line_Rec_Type;
435 l_orig_sys_header_ref varchar2(50);
436 l_orig_sys_line_ref varchar2(50);
437 l_orig_sys_pricing_attr_ref varchar2(50);
438 l_price_break_header_ref varchar2(50);
439 Begin
440 fnd_file.put_line(fnd_file.log, 'In EXPORT_TMP_LINES ');
441 l_LINE_rec := QP_Price_List_Line_Util.Query_Row
442 ( p_list_line_id => list_from
443 );
444 fnd_file.put_line(fnd_file.log, 'Inserting Line '||to_char(l_LINE_rec.list_line_id));
445 if g_interface_action = 'INSERT' then
446 l_orig_sys_line_ref := p_entity_name || l_LINE_rec.list_line_id;
447 l_orig_sys_header_ref := p_entity_name || l_LINE_rec.list_header_id;
448 if l_LINE_Rec.from_rltd_modifier_id is not NULL then
449 l_price_break_header_ref := p_entity_name || l_LINE_Rec.from_rltd_modifier_id;
450 end if;
451 else
452 begin
453 select orig_sys_line_ref, orig_sys_header_ref
454 into l_orig_sys_line_ref, l_orig_sys_header_ref
455 from qp_list_lines
456 where list_line_id = l_LINE_rec.list_line_id;
457 exception
458 when others then
459 null;
460 end;
461 if l_LINE_Rec.from_rltd_modifier_id is not NULL then
462 begin
463 select orig_sys_line_ref
464 into l_price_break_header_ref
465 from qp_list_lines
466 where list_line_id = l_LINE_rec.from_rltd_modifier_id;
467 exception
468 when others then
469 null;
470 end;
471 end if;
472 end if;
473 INSERT into qp_interface_list_lines
474 (LIST_LINE_ID
475 ,PROGRAM_APPLICATION_ID
476 ,PROGRAM_ID
477 ,PROGRAM_UPDATE_DATE
478 ,REQUEST_ID
479 ,LIST_HEADER_ID
480 ,LIST_LINE_TYPE_CODE
481 ,START_DATE_ACTIVE
482 ,END_DATE_ACTIVE
483 ,AUTOMATIC_FLAG
484 ,MODIFIER_LEVEL_CODE
485 ,PRICE_BY_FORMULA_ID
486 ,LIST_PRICE
487 ,LIST_PRICE_UOM_CODE
488 ,PRIMARY_UOM_FLAG
489 ,INVENTORY_ITEM_ID
490 ,ORGANIZATION_ID
491 ,RELATED_ITEM_ID
492 ,RELATIONSHIP_TYPE_ID
493 ,SUBSTITUTION_CONTEXT
494 ,SUBSTITUTION_ATTRIBUTE
495 ,SUBSTITUTION_VALUE
496 ,REVISION
497 ,REVISION_DATE
498 ,REVISION_REASON_CODE
499 ,PRICE_BREAK_TYPE_CODE
500 ,PERCENT_PRICE
501 ,NUMBER_EFFECTIVE_PERIODS
502 ,EFFECTIVE_PERIOD_UOM
503 ,ARITHMETIC_OPERATOR
504 ,OPERAND
505 ,OVERRIDE_FLAG
506 ,PRINT_ON_INVOICE_FLAG
507 ,REBATE_TRANSACTION_TYPE_CODE
508 ,BASE_QTY
509 ,BASE_UOM_CODE
510 ,ACCRUAL_QTY
511 ,ACCRUAL_UOM_CODE
512 ,ESTIM_ACCRUAL_RATE
513 ,PROCESS_ID
514 ,PROCESS_TYPE
515 ,INTERFACE_ACTION_CODE
516 ,LOCK_FLAG
517 ,PROCESS_FLAG
518 ,DELETE_FLAG
519 ,PROCESS_STATUS_FLAG
520 ,COMMENTS
521 ,GENERATE_USING_FORMULA_ID
522 ,REPRICE_FLAG
523 ,LIST_LINE_NO
524 ,ESTIM_GL_VALUE
525 ,BENEFIT_PRICE_LIST_LINE_ID
526 ,EXPIRATION_PERIOD_START_DATE
527 ,NUMBER_EXPIRATION_PERIODS
528 ,EXPIRATION_PERIOD_UOM
529 ,EXPIRATION_DATE
530 ,ACCRUAL_FLAG
531 ,PRICING_PHASE_ID
532 ,PRICING_GROUP_SEQUENCE
533 ,INCOMPATIBILITY_GRP_CODE
534 ,PRODUCT_PRECEDENCE
535 ,PRORATION_TYPE_CODE
536 ,ACCRUAL_CONVERSION_RATE
537 ,BENEFIT_QTY
538 ,BENEFIT_UOM_CODE
539 ,RECURRING_FLAG
540 ,BENEFIT_LIMIT
541 ,CHARGE_TYPE_CODE
542 ,CHARGE_SUBTYPE_CODE
543 ,INCLUDE_ON_RETURNS_FLAG
544 ,QUALIFICATION_IND
545 ,CONTEXT
546 ,ATTRIBUTE1
547 ,ATTRIBUTE2
548 ,ATTRIBUTE3
549 ,ATTRIBUTE4
550 ,ATTRIBUTE5
551 ,ATTRIBUTE6
552 ,ATTRIBUTE7
553 ,ATTRIBUTE8
554 ,ATTRIBUTE9
555 ,ATTRIBUTE10
556 ,ATTRIBUTE11
557 ,ATTRIBUTE12
558 ,ATTRIBUTE13
559 ,ATTRIBUTE14
560 ,ATTRIBUTE15
561 ,RLTD_MODIFIER_GRP_NO
562 ,RLTD_MODIFIER_GRP_TYPE
563 ,PRICE_BREAK_HEADER_REF
564 ,PRICING_PHASE_NAME
565 ,PRICE_BY_FORMULA
566 ,GENERATE_USING_FORMULA
567 ,ATTRIBUTE_STATUS
568 ,ORIG_SYS_LINE_REF
569 ,ORIG_SYS_HEADER_REF
570 ,RECURRING_VALUE
571 ,NET_AMOUNT_FLAG)
572 values
573 (l_LINE_Rec.LIST_LINE_ID
574 , l_LINE_Rec.PROGRAM_APPLICATION_ID
575 , l_LINE_Rec.PROGRAM_ID
576 , l_LINE_Rec.PROGRAM_UPDATE_DATE
577 , NULL
578 , l_LINE_Rec.LIST_HEADER_ID
579 , l_LINE_Rec.LIST_LINE_TYPE_CODE
580 , l_LINE_Rec.START_DATE_ACTIVE
581 , l_LINE_Rec.END_DATE_ACTIVE
582 , l_LINE_Rec.AUTOMATIC_FLAG
583 , l_LINE_Rec.MODIFIER_LEVEL_CODE
584 , l_LINE_Rec.PRICE_BY_FORMULA_ID
585 , l_LINE_Rec.LIST_PRICE
586 , NULL --l_LINE_Rec.LIST_PRICE_UOM_CODE
587 , l_LINE_Rec.PRIMARY_UOM_FLAG
588 , l_LINE_Rec.INVENTORY_ITEM_ID
589 , l_LINE_Rec.ORGANIZATION_ID
590 , l_LINE_Rec.RELATED_ITEM_ID
591 , l_LINE_Rec.RELATIONSHIP_TYPE_ID
592 , l_LINE_Rec.SUBSTITUTION_CONTEXT
593 , l_LINE_Rec.SUBSTITUTION_ATTRIBUTE
594 , l_LINE_Rec.SUBSTITUTION_VALUE
595 , l_LINE_Rec.REVISION
596 , l_LINE_Rec.REVISION_DATE
597 , l_LINE_Rec.REVISION_REASON_CODE
598 , l_LINE_Rec.PRICE_BREAK_TYPE_CODE
599 , l_LINE_Rec.PERCENT_PRICE
600 , l_LINE_Rec.NUMBER_EFFECTIVE_PERIODS
601 , l_LINE_Rec.EFFECTIVE_PERIOD_UOM
602 , l_LINE_Rec.ARITHMETIC_OPERATOR
603 , l_LINE_Rec.OPERAND
604 , l_LINE_Rec.OVERRIDE_FLAG
605 , l_LINE_Rec.PRINT_ON_INVOICE_FLAG
606 , NULL --l_LINE_Rec.REBATE_TRANSACTION_TYPE_CODE
607 , l_LINE_Rec.BASE_QTY
608 , l_LINE_Rec.BASE_UOM_CODE
609 , l_LINE_Rec.ACCRUAL_QTY
610 , l_LINE_Rec.ACCRUAL_UOM_CODE
611 , l_LINE_Rec.ESTIM_ACCRUAL_RATE
612 , NULL
613 , NULL
614 , g_interface_action --, 'INSERT'
615 , NULL
616 ,'Y'
617 , NULL
618 ,'P' -- NULL
619 , l_LINE_Rec.COMMENTS
620 , l_LINE_Rec.GENERATE_USING_FORMULA_ID
621 , l_LINE_Rec.REPRICE_FLAG
622 , l_LINE_Rec.LIST_LINE_NO
623 , NULL --l_LINE_Rec.ESTIM_GL_VALUE
624 , NULL --l_LINE_Rec.BENEFIT_PRICE_LIST_LINE_ID
625 , NULL --l_LINE_Rec.EXPIRATION_PERIOD_START_DATE
626 , NULL --l_LINE_Rec.NUMBER_EXPIRATION_PERIODS
627 , NULL --l_LINE_Rec.EXPIRATION_PERIOD_UOM
628 , NULL --l_LINE_Rec.EXPIRATION_DATE
629 , NULL --l_LINE_Rec.ACCRUAL_FLAG
630 , NULL --l_LINE_Rec.PRICING_PHASE_ID
631 , NULL --l_LINE_Rec.PRICING_GROUP_SEQUENCE
632 , NULL --l_LINE_Rec.INCOMPATIBILITY_GRP_CODE
633 , l_LINE_Rec.PRODUCT_PRECEDENCE
634 , NULL --l_LINE_Rec.PRORATION_TYPE_CODE
635 , NULL --l_LINE_Rec.ACCRUAL_CONVERSION_RATE
636 , NULL --l_LINE_Rec.BENEFIT_QTY
637 , NULL --l_LINE_Rec.BENEFIT_UOM_CODE
638 , NULL --l_LINE_Rec.RECURRING_FLAG
639 , NULL --l_LINE_Rec.BENEFIT_LIMIT
640 , NULL -- l_LINE_Rec.CHARGE_TYPE_CODE
641 , NULL --l_LINE_Rec.CHARGE_SUBTYPE_CODE
642 , NULL --l_LINE_Rec.INCLUDE_ON_RETURNS_FLAG
643 , l_LINE_Rec.QUALIFICATION_IND
644 , l_LINE_Rec.CONTEXT
645 , l_LINE_Rec.ATTRIBUTE1
646 , l_LINE_Rec.ATTRIBUTE2
647 , l_LINE_Rec.ATTRIBUTE3
648 , l_LINE_Rec.ATTRIBUTE4
649 , l_LINE_Rec.ATTRIBUTE5
650 , l_LINE_Rec.ATTRIBUTE6
651 , l_LINE_Rec.ATTRIBUTE7
652 , l_LINE_Rec.ATTRIBUTE8
653 , l_LINE_Rec.ATTRIBUTE9
654 , l_LINE_Rec.ATTRIBUTE10
655 , l_LINE_Rec.ATTRIBUTE11
656 , l_LINE_Rec.ATTRIBUTE12
657 , l_LINE_Rec.ATTRIBUTE13
658 , l_LINE_Rec.ATTRIBUTE14
659 , l_LINE_Rec.ATTRIBUTE15
660 , l_LINE_Rec.RLTD_MODIFIER_GROUP_NO
661 , l_LINE_Rec.RLTD_MODIFIER_GRP_TYPE
662 , l_price_break_header_ref --PRICE_BREAK_HEADER_REF
663 , NULL --l_LINE_Rec.PRICING_PHASE_NAME
664 , NULL --l_LINE_Rec.PRICE_BY_FORMULA
665 , NULL --l_LINE_Rec.GENERATE_USING_FORMULA
666 , NULL
667 ,l_ORIG_SYS_LINE_REF
668 ,l_ORIG_SYS_HEADER_REF
669 --, p_entity_name || to_char(l_LINE_Rec.list_line_id)
670 --, p_entity_name || to_char(l_LINE_Rec.list_header_id)
671 , l_LINE_Rec.RECURRING_VALUE
672 , NULL); --l_LINE_Rec.NET_AMOUNT_FLAG);
673
674 fnd_file.put_line(fnd_file.log, 'Inserted Line '|| p_entity_name || to_char(l_LINE_Rec.list_line_id));
675
676 end;
677
678 PROCEDURE EXPORT_TMP_ATTRIBS
679 (
680 list_from NUMBER
681 ,p_entity_name VARCHAR2
682 )
683 IS
684 l_attribs_rec QP_Price_List_PUB.Pricing_Attr_Rec_Type;
685 l_orig_sys_header_ref varchar2(50);
686 l_orig_sys_line_ref varchar2(50);
687 l_orig_sys_pricing_attr_ref varchar2(50);
688 begin
689 fnd_file.put_line(fnd_file.log, 'In EXPORT_TMP_ATTRIBS ');
690 l_attribs_rec := Qp_pll_pricing_attr_Util.Query_Row
691 ( p_pricing_attribute_id => list_from
692 );
693 fnd_file.put_line(fnd_file.log, 'Inserting Attribs '||to_char(l_attribs_rec.pricing_attribute_id));
694 if g_interface_action = 'INSERT' then
695 l_orig_sys_pricing_attr_ref := p_entity_name || l_attribs_rec.pricing_attribute_id;
696 l_orig_sys_line_ref := p_entity_name || l_attribs_rec.list_line_id;
697 l_orig_sys_header_ref := p_entity_name || l_attribs_rec.list_header_id;
698 else
699 begin
700 select orig_sys_line_ref, orig_sys_header_ref, orig_sys_pricing_attr_ref
701 into l_orig_sys_line_ref, l_orig_sys_header_ref, l_orig_sys_pricing_attr_ref
702 from qp_pricing_attributes
703 where pricing_attribute_id = l_attribs_rec.pricing_attribute_id;
704 exception
705 when others then
706 null;
707 end;
708 end if;
709 insert into qp_interface_pricing_attribs
710 ( PRICING_ATTRIBUTE_ID
711 ,PROGRAM_APPLICATION_ID
712 ,PROGRAM_ID
713 ,PROGRAM_UPDATE_DATE
714 ,REQUEST_ID
715 ,LIST_LINE_ID
716 ,EXCLUDER_FLAG
717 ,ACCUMULATE_FLAG
718 ,PRODUCT_ATTRIBUTE_CONTEXT
719 ,PRODUCT_ATTRIBUTE
720 ,PRODUCT_ATTR_VALUE
721 ,PRODUCT_UOM_CODE
722 ,PRICING_ATTRIBUTE_CONTEXT
723 ,PRICING_ATTRIBUTE
724 ,PRICING_ATTR_VALUE_FROM
725 ,PRICING_ATTR_VALUE_TO
726 ,ATTRIBUTE_GROUPING_NO
727 ,PRODUCT_ATTRIBUTE_DATATYPE
728 ,PRICING_ATTRIBUTE_DATATYPE
729 ,COMPARISON_OPERATOR_CODE
730 ,LIST_HEADER_ID
731 ,PRICING_PHASE_ID
732 ,QUALIFICATION_IND
733 ,PRICING_ATTR_VALUE_FROM_NUMBER
734 ,PRICING_ATTR_VALUE_TO_NUMBER
735 ,CONTEXT
736 ,ATTRIBUTE1
737 ,ATTRIBUTE2
738 ,ATTRIBUTE3
739 ,ATTRIBUTE4
740 ,ATTRIBUTE5
741 ,ATTRIBUTE6
742 ,ATTRIBUTE7
743 ,ATTRIBUTE8
744 ,ATTRIBUTE9
745 ,ATTRIBUTE10
746 ,ATTRIBUTE11
747 ,ATTRIBUTE12
748 ,ATTRIBUTE13
749 ,ATTRIBUTE14
750 ,ATTRIBUTE15
751 ,PROCESS_ID
752 ,PROCESS_TYPE
753 ,INTERFACE_ACTION_CODE
754 ,LOCK_FLAG
755 ,PROCESS_FLAG
756 ,DELETE_FLAG
757 ,PROCESS_STATUS_FLAG
758 ,PRICE_LIST_LINE_INDEX
759 ,LIST_LINE_NO
760 ,ORIG_SYS_PRICING_ATTR_REF
761 ,PRODUCT_ATTR_CODE
762 ,PRODUCT_ATTR_VAL_DISP
763 ,PRICING_ATTR_CODE
764 ,PRICING_ATTR_VALUE_FROM_DISP
765 ,PRICING_ATTR_VALUE_TO_DISP
766 ,ATTRIBUTE_STATUS
767 ,ORIG_SYS_LINE_REF
768 ,ORIG_SYS_HEADER_REF)
769 values
770 ( l_attribs_rec.PRICING_ATTRIBUTE_ID
771 ,l_attribs_rec.PROGRAM_APPLICATION_ID
772 ,l_attribs_rec.PROGRAM_ID
773 ,l_attribs_rec.PROGRAM_UPDATE_DATE
774 , NULL --l_attribs_rec.REQUEST_ID
775 ,l_attribs_rec.LIST_LINE_ID
776 ,l_attribs_rec.EXCLUDER_FLAG
777 ,l_attribs_rec.ACCUMULATE_FLAG
778 ,l_attribs_rec.PRODUCT_ATTRIBUTE_CONTEXT
779 ,l_attribs_rec.PRODUCT_ATTRIBUTE
780 ,l_attribs_rec.PRODUCT_ATTR_VALUE
781 ,l_attribs_rec.PRODUCT_UOM_CODE
782 ,l_attribs_rec.PRICING_ATTRIBUTE_CONTEXT
783 ,l_attribs_rec.PRICING_ATTRIBUTE
784 ,l_attribs_rec.PRICING_ATTR_VALUE_FROM
785 ,l_attribs_rec.PRICING_ATTR_VALUE_TO
786 ,l_attribs_rec.ATTRIBUTE_GROUPING_NO
787 ,l_attribs_rec.PRODUCT_ATTRIBUTE_DATATYPE
788 ,l_attribs_rec.PRICING_ATTRIBUTE_DATATYPE
789 ,l_attribs_rec.COMPARISON_OPERATOR_CODE
790 ,l_attribs_rec.LIST_HEADER_ID
791 ,l_attribs_rec.PRICING_PHASE_ID
792 ,l_attribs_rec.QUALIFICATION_IND
793 ,l_attribs_rec.PRICING_ATTR_VALUE_FROM_NUMBER
794 ,l_attribs_rec.PRICING_ATTR_VALUE_TO_NUMBER
795 ,l_attribs_rec.CONTEXT
796 ,l_attribs_rec.ATTRIBUTE1
797 ,l_attribs_rec.ATTRIBUTE2
798 ,l_attribs_rec.ATTRIBUTE3
799 ,l_attribs_rec.ATTRIBUTE4
800 ,l_attribs_rec.ATTRIBUTE5
801 ,l_attribs_rec.ATTRIBUTE6
802 ,l_attribs_rec.ATTRIBUTE7
803 ,l_attribs_rec.ATTRIBUTE8
804 ,l_attribs_rec.ATTRIBUTE9
805 ,l_attribs_rec.ATTRIBUTE10
806 ,l_attribs_rec.ATTRIBUTE11
807 ,l_attribs_rec.ATTRIBUTE12
808 ,l_attribs_rec.ATTRIBUTE13
809 ,l_attribs_rec.ATTRIBUTE14
810 ,l_attribs_rec.ATTRIBUTE15
811 , NULL --_attribs_rec.PROCESS_ID
812 , NULL --_attribs_rec.PROCESS_TYPE
813 , g_interface_action --,'INSERT' --l_attribs_rec.INTERFACE_ACTION_CODE
814 , NULL --_attribs_rec.LOCK_FLAG
815 ,'Y' --_attribs_rec.PROCESS_FLAG
816 , NULL --_attribs_rec.DELETE_FLAG
817 ,'P' -- NULL --_attribs_rec.PROCESS_STATUS_FLAG
818 ,l_attribs_rec.PRICE_LIST_LINE_INDEX
819 , NULL --l_attribs_rec.LIST_LINE_NO
820 --,(p_entity_name || l_attribs_rec.pricing_attribute_id)
821 ,l_ORIG_SYS_PRICING_ATTR_REF
822 , NULL --_attribs_rec.PRODUCT_ATTR_CODE
823 , NULL --_attribs_rec.PRODUCT_ATTR_VAL_DISP
824 , NULL --_attribs_rec.PRICING_ATTR_CODE
825 , NULL --_attribs_rec.PRICING_ATTR_VALUE_FROM_DISP
826 , NULL --_attribs_rec.PRICING_ATTR_VALUE_TO_DISP
827 , NULL --_attribs_rec.ATTRIBUTE_STATUS
828 ,l_ORIG_SYS_LINE_REF
829 ,l_ORIG_SYS_HEADER_REF);
830 --,(p_entity_name || to_char(l_attribs_rec.list_line_id)) --ORIG_SYS_LINE_REF
831 --,(p_entity_name || to_char(l_attribs_rec.list_header_id))); --ORIG_SYS_HEADER_REF);
832 fnd_file.put_line(fnd_file.log, 'Inserting Attribs '||(p_entity_name || to_char(l_attribs_rec.pricing_attribute_id)));
833
834 end;
835
836 END QP_BULK_EXPORT_TMP_PVT;