[Home] [Help]
PACKAGE BODY: APPS.QP_BULK_UTIL
Source
1 PACKAGE BODY QP_BULK_UTIL AS
2 /* $Header: QPXBUTLB.pls 120.12.12010000.3 2009/05/07 07:11:30 dnema ship $ */
3
4 PROCEDURE LOAD_INS_HEADER
5 ( P_REQUEST_ID IN NUMBER
6 ,X_HEADER_REC OUT NOCOPY QP_BULK_LOADER_PUB.HEADER_REC_TYPE
7 )
8 IS
9
10 CURSOR C_PL_HEADER IS
11 SELECT list_header_id
12 ,creation_date
13 ,created_by
14 ,last_update_date
15 ,last_updated_by
16 ,last_update_login
17 ,program_application_id
18 ,program_id
19 ,program_update_date
20 ,request_id
21 ,list_type_code
22 --,fnd_date.date_to_canonical(start_date_active) -- rnayani 5138015 -- gtippire 4440794
23 --,fnd_date.date_to_canonical(end_date_active) -- rnayani 5138015 -- gtippire 4440794
24 ,start_date_active -- rnayani 5138015
25 ,end_date_active -- rnayani 5138015
26 ,source_lang
27 ,automatic_flag
28 ,name
29 ,description
30 ,currency_code
31 ,version_no
32 ,rounding_factor
33 ,ship_method_code
34 ,freight_terms_code
35 ,terms_id
36 ,comments
37 ,discount_lines_flag
38 ,gsa_indicator
39 ,prorate_flag
40 ,source_system_code
41 ,ask_for_flag
42 ,active_flag
43 ,parent_list_header_id
44 ,active_date_first_type
45 ,start_date_active_first
46 ,end_date_active_first
47 ,active_date_second_type
48 ,start_date_active_second
49 ,end_date_active_second
50 ,context
51 ,attribute1
52 ,attribute2
53 ,attribute3
54 ,attribute4
55 ,attribute5
56 ,attribute6
57 ,attribute7
58 ,attribute8
59 ,attribute9
60 ,attribute10
61 ,attribute11
62 ,attribute12
63 ,attribute13
64 ,attribute14
65 ,attribute15
66 ,language
67 ,process_id
68 ,process_type
69 ,interface_action_code
70 ,lock_flag
71 ,process_flag
72 ,delete_flag
73 ,process_status_flag
74 ,mobile_download
75 ,currency_header_id
76 ,pte_code
77 ,list_source_code
78 ,orig_sys_header_ref
79 ,orig_org_id
80 ,global_flag
81 FROM qp_interface_list_headers
82 WHERE request_id = p_request_id
83 AND process_status_flag = 'P' --IS NULL
84 AND interface_action_code='INSERT';
85
86 BEGIN
87 qp_bulk_loader_pub.write_log('Entering Loading Ins Header');
88 OPEN C_PL_HEADER;
89 FETCH C_PL_HEADER BULK COLLECT
90 INTO x_header_rec.list_header_id
91 ,x_header_rec.creation_date
92 ,x_header_rec.created_by
93 ,x_header_rec.last_update_date
94 ,x_header_rec.last_updated_by
95 ,x_header_rec.last_update_login
96 ,x_header_rec.program_application_id
97 ,x_header_rec.program_id
98 ,x_header_rec.program_update_date
99 ,x_header_rec.request_id
100 ,x_header_rec.list_type_code
101 ,x_header_rec.start_date_active
102 ,x_header_rec.end_date_active
103 ,x_header_rec.source_lang
104 ,x_header_rec.automatic_flag
105 ,x_header_rec.name
106 ,x_header_rec.description
107 ,x_header_rec.currency_code
108 ,x_header_rec.version_no
109 ,x_header_rec.rounding_factor
110 ,x_header_rec.ship_method_code
111 ,x_header_rec.freight_terms_code
112 ,x_header_rec.terms_id
113 ,x_header_rec.comments
114 ,x_header_rec.discount_lines_flag
115 ,x_header_rec.gsa_indicator
116 ,x_header_rec.prorate_flag
117 ,x_header_rec.source_system_code
118 ,x_header_rec.ask_for_flag
119 ,x_header_rec.active_flag
120 ,x_header_rec.parent_list_header_id
121 ,x_header_rec.active_date_first_type
122 ,x_header_rec.start_date_active_first
123 ,x_header_rec.end_date_active_first
124 ,x_header_rec.active_date_second_type
125 ,x_header_rec.start_date_active_second
126 ,x_header_rec.end_date_active_second
127 ,x_header_rec.context
128 ,x_header_rec.attribute1
129 ,x_header_rec.attribute2
130 ,x_header_rec.attribute3
131 ,x_header_rec.attribute4
132 ,x_header_rec.attribute5
133 ,x_header_rec.attribute6
134 ,x_header_rec.attribute7
135 ,x_header_rec.attribute8
136 ,x_header_rec.attribute9
137 ,x_header_rec.attribute10
138 ,x_header_rec.attribute11
139 ,x_header_rec.attribute12
140 ,x_header_rec.attribute13
141 ,x_header_rec.attribute14
142 ,x_header_rec.attribute15
143 ,x_header_rec.language
144 ,x_header_rec.process_id
145 ,x_header_rec.process_type
146 ,x_header_rec.interface_action_code
147 ,x_header_rec.lock_flag
148 ,x_header_rec.process_flag
149 ,x_header_rec.delete_flag
150 ,x_header_rec.process_status_flag
151 ,x_header_rec.mobile_download
152 ,x_header_rec.currency_header_id
153 ,x_header_rec.pte_code
154 ,x_header_rec.list_source_code
155 ,x_header_rec.orig_sys_header_ref
156 ,x_header_rec.orig_org_id
157 ,x_header_rec.global_flag;
158
159 CLOSE C_PL_HEADER;
160
161 qp_bulk_loader_pub.write_log('Leaving Loading Ins Header');
162
163 EXCEPTION
164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
165 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.LOAD_INS_HEADER:'||sqlerrm);
166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
167 WHEN OTHERS THEN
168 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.LOAD_INS_HEADER:'||sqlerrm);
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170
171 END LOAD_INS_HEADER;
172
173 PROCEDURE LOAD_UDT_HEADER
174 ( P_REQUEST_ID IN NUMBER
175 ,X_HEADER_REC OUT NOCOPY QP_BULK_LOADER_PUB.HEADER_REC_TYPE
176 )
177 IS
178
179
180 CURSOR C_PL_HEADER IS
181 SELECT /*+ LEADING(a) */ --bug8359604
182 a.list_header_id,
183 a.creation_date,
184 a.created_by,
185 a.last_update_date,
186 a.last_updated_by,
187 a.last_update_login,
188 a.program_application_id,
189 a.program_id,
190 a.program_update_date,
191 a.request_id,
192 a.list_type_code,
193 a.start_date_active,
194 a.end_date_active,
195 a.source_lang,
196 a.automatic_flag,
197 a.name,
198 a.description,
199 a.currency_code,
200 a.version_no,
201 a.rounding_factor,
202 a.ship_method_code,
203 a.freight_terms_code,
204 a.terms_id,
205 a.comments,
206 a.discount_lines_flag,
207 a.gsa_indicator,
208 a.prorate_flag,
209 a.source_system_code,
210 a.ask_for_flag,
211 a.active_flag,
212 a.parent_list_header_id,
213 a.active_date_first_type,
214 a.start_date_active_first,
215 a.end_date_active_first,
216 a.active_date_second_type,
217 a.start_date_active_second,
218 a.end_date_active_second,
219 a.context,
220 a.attribute1,
221 a.attribute2,
222 a.attribute3,
223 a.attribute4,
224 a.attribute5,
225 a.attribute6,
226 a.attribute7,
227 a.attribute8,
228 a.attribute9,
229 a.attribute10,
230 a.attribute11,
231 a.attribute12,
232 a.attribute13,
233 a.attribute14,
234 a.attribute15,
235 a.language,
236 process_id,
237 process_type,
238 interface_action_code,
239 lock_flag,
240 process_flag,
241 delete_flag,
242 process_status_flag,
243 a.mobile_download,
244 a.currency_header_id,
245 a.pte_code,
246 a.list_source_code,
247 a.orig_sys_header_ref,
248 a.orig_org_id,
249 a.global_flag
250 FROM qp_interface_list_headers a, qp_list_headers_vl b
251 -- ENH undo alcoa changes RAVI
252 /**
253 The key between interface and qp tables is only orig_sys_hdr_ref
254 (not list_header_id)
255 **/
256 WHERE a.orig_sys_header_ref = b.orig_system_header_ref
257 AND a.request_id = p_request_id
258 AND a.process_status_flag = 'P' --IS NULL
259 AND a.interface_action_code='UPDATE';
260
261 BEGIN
262 qp_bulk_loader_pub.write_log('Entering Loading Udt Header');
263
264 OPEN C_PL_HEADER;
265 FETCH C_PL_HEADER BULK COLLECT
266 INTO x_header_rec.list_header_id
267 ,x_header_rec.creation_date
268 ,x_header_rec.created_by
269 ,x_header_rec.last_update_date
270 ,x_header_rec.last_updated_by
271 ,x_header_rec.last_update_login
272 ,x_header_rec.program_application_id
273 ,x_header_rec.program_id
274 ,x_header_rec.program_update_date
275 ,x_header_rec.request_id
276 ,x_header_rec.list_type_code
277 ,x_header_rec.start_date_active
278 ,x_header_rec.end_date_active
279 ,x_header_rec.source_lang
280 ,x_header_rec.automatic_flag
281 ,x_header_rec.name
282 ,x_header_rec.description
283 ,x_header_rec.currency_code
284 ,x_header_rec.version_no
285 ,x_header_rec.rounding_factor
286 ,x_header_rec.ship_method_code
287 ,x_header_rec.freight_terms_code
288 ,x_header_rec.terms_id
289 ,x_header_rec.comments
290 ,x_header_rec.discount_lines_flag
291 ,x_header_rec.gsa_indicator
292 ,x_header_rec.prorate_flag
293 ,x_header_rec.source_system_code
294 ,x_header_rec.ask_for_flag
295 ,x_header_rec.active_flag
296 ,x_header_rec.parent_list_header_id
297 ,x_header_rec.active_date_first_type
298 ,x_header_rec.start_date_active_first
299 ,x_header_rec.end_date_active_first
300 ,x_header_rec.active_date_second_type
301 ,x_header_rec.start_date_active_second
302 ,x_header_rec.end_date_active_second
303 ,x_header_rec.context
304 ,x_header_rec.attribute1
305 ,x_header_rec.attribute2
306 ,x_header_rec.attribute3
307 ,x_header_rec.attribute4
308 ,x_header_rec.attribute5
309 ,x_header_rec.attribute6
310 ,x_header_rec.attribute7
311 ,x_header_rec.attribute8
312 ,x_header_rec.attribute9
313 ,x_header_rec.attribute10
314 ,x_header_rec.attribute11
315 ,x_header_rec.attribute12
316 ,x_header_rec.attribute13
317 ,x_header_rec.attribute14
318 ,x_header_rec.attribute15
319 ,x_header_rec.language
320 ,x_header_rec.process_id
321 ,x_header_rec.process_type
322 ,x_header_rec.interface_action_code
323 ,x_header_rec.lock_flag
324 ,x_header_rec.process_flag
325 ,x_header_rec.delete_flag
326 ,x_header_rec.process_status_flag
327 ,x_header_rec.mobile_download
328 ,x_header_rec.currency_header_id
329 ,x_header_rec.pte_code
330 ,x_header_rec.list_source_code
331 ,x_header_rec.orig_sys_header_ref
332 ,x_header_rec.orig_org_id
333 ,x_header_rec.global_flag;
334
335 CLOSE C_PL_HEADER;
336
337 qp_bulk_loader_pub.write_log('Leaving Loading Udt Header');
338
339 EXCEPTION
340 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
341 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.LOAD_UDT_HEADER:'||sqlerrm);
342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
343 WHEN OTHERS THEN
344 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.LOAD_UDT_HEADER:'||sqlerrm);
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346
347
348 END LOAD_UDT_HEADER;
349
350
351 PROCEDURE INSERT_HEADER
352 (p_header_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.HEADER_REC_TYPE)
353 IS
354 x_result varchar2(1);
355 BEGIN
356 qp_bulk_loader_pub.write_log('Entering Insert Header');
357 FORALL I IN
358 P_HEADER_REC.list_header_id.FIRST..P_HEADER_REC.list_header_id.LAST
359
360 INSERT INTO qp_list_headers_b
361 ( LIST_HEADER_ID
362 ,CREATION_DATE
363 ,CREATED_BY
364 ,LAST_UPDATE_DATE
365 ,LAST_UPDATED_BY
366 ,LAST_UPDATE_LOGIN
367 ,PROGRAM_APPLICATION_ID
368 ,PROGRAM_ID
369 ,PROGRAM_UPDATE_DATE
370 ,REQUEST_ID
371 ,LIST_TYPE_CODE
372 ,START_DATE_ACTIVE
373 ,END_DATE_ACTIVE
374 ,AUTOMATIC_FLAG
375 ,CURRENCY_CODE
376 ,ROUNDING_FACTOR
377 ,SHIP_METHOD_CODE
378 ,FREIGHT_TERMS_CODE
379 ,TERMS_ID
380 ,COMMENTS
381 ,DISCOUNT_LINES_FLAG
382 ,GSA_INDICATOR
383 ,PRORATE_FLAG
384 ,SOURCE_SYSTEM_CODE
385 ,ASK_FOR_FLAG
386 ,ACTIVE_FLAG
387 ,PARENT_LIST_HEADER_ID
388 ,START_DATE_ACTIVE_FIRST
389 ,END_DATE_ACTIVE_FIRST
390 ,ACTIVE_DATE_FIRST_TYPE
391 ,START_DATE_ACTIVE_SECOND
392 ,END_DATE_ACTIVE_SECOND
393 ,ACTIVE_DATE_SECOND_TYPE
394 ,CONTEXT
395 ,ATTRIBUTE1
396 ,ATTRIBUTE2
397 ,ATTRIBUTE3
398 ,ATTRIBUTE4
399 ,ATTRIBUTE5
400 ,ATTRIBUTE6
401 ,ATTRIBUTE7
402 ,ATTRIBUTE8
403 ,ATTRIBUTE9
404 ,ATTRIBUTE10
405 ,ATTRIBUTE11
406 ,ATTRIBUTE12
407 ,ATTRIBUTE13
408 ,ATTRIBUTE14
409 ,ATTRIBUTE15
410 ,MOBILE_DOWNLOAD
411 ,CURRENCY_HEADER_ID
412 ,PTE_CODE
413 ,LIST_SOURCE_CODE
414 ,ORIG_SYSTEM_HEADER_REF
415 ,ORIG_ORG_ID
416 ,GLOBAL_FLAG
417 )
418 -- Bug 4615792 RAVI
419 /**
420 Do not insert if process status flag for the record is null.
421 **/
422 SELECT
423 P_HEADER_REC.list_header_id(I)
424 ,SYSDATE
425 ,FND_GLOBAL.USER_ID
426 ,SYSDATE
427 ,FND_GLOBAL.USER_ID
428 ,FND_GLOBAL.CONC_LOGIN_ID
429 ,660
430 ,NUll
431 ,NULL
432 ,P_HEADER_REC.Request_Id(I)
433 ,P_HEADER_REC.LIST_TYPE_CODE(I)
434 ,fnd_date.canonical_to_date(p_header_rec.start_date_active(I))
435 ,fnd_date.canonical_to_date(p_header_rec.end_date_active(I))
436 -- ,P_HEADER_REC.START_DATE_ACTIVE(I)
437 -- ,P_HEADER_REC.END_DATE_ACTIVE(I)
438 ,P_HEADER_REC.AUTOMATIC_FLAG(I)
439 ,P_HEADER_REC.CURRENCY_CODE(I)
440 ,P_HEADER_REC.ROUNDING_FACTOR(I)
441 ,P_HEADER_REC.SHIP_METHOD_CODE(I)
442 ,P_HEADER_REC.FREIGHT_TERMS_CODE(I)
443 ,P_HEADER_REC.TERMS_ID(I)
444 ,P_HEADER_REC.COMMENTS(I)
445 ,P_HEADER_REC.DISCOUNT_LINES_FLAG(I)
446 ,P_HEADER_REC.GSA_INDICATOR(I)
447 ,P_HEADER_REC.PRORATE_FLAG(I)
448 ,P_HEADER_REC.SOURCE_SYSTEM_CODE(I)
449 ,P_HEADER_REC.ASK_FOR_FLAG(I)
450 ,P_HEADER_REC.ACTIVE_FLAG(I)
451 ,P_HEADER_REC.PARENT_LIST_HEADER_ID(I)
452 ,P_HEADER_REC.START_DATE_ACTIVE_FIRST(I)
453 ,P_HEADER_REC.END_DATE_ACTIVE_FIRST(I)
454 ,P_HEADER_REC.ACTIVE_DATE_FIRST_TYPE(I)
455 ,P_HEADER_REC.START_DATE_ACTIVE_SECOND(I)
456 ,P_HEADER_REC.END_DATE_ACTIVE_SECOND(I)
457 ,P_HEADER_REC.ACTIVE_DATE_SECOND_TYPE(I)
458 ,P_HEADER_REC.CONTEXT(I)
459 ,P_HEADER_REC.ATTRIBUTE1(I)
460 ,P_HEADER_REC.ATTRIBUTE2(I)
461 ,P_HEADER_REC.ATTRIBUTE3(I)
462 ,P_HEADER_REC.ATTRIBUTE4(I)
463 ,P_HEADER_REC.ATTRIBUTE5(I)
464 ,P_HEADER_REC.ATTRIBUTE6(I)
465 ,P_HEADER_REC.ATTRIBUTE7(I)
466 ,P_HEADER_REC.ATTRIBUTE8(I)
467 ,P_HEADER_REC.ATTRIBUTE9(I)
468 ,P_HEADER_REC.ATTRIBUTE10(I)
469 ,P_HEADER_REC.ATTRIBUTE11(I)
470 ,P_HEADER_REC.ATTRIBUTE12(I)
471 ,P_HEADER_REC.ATTRIBUTE13(I)
472 ,P_HEADER_REC.ATTRIBUTE14(I)
473 ,P_HEADER_REC.ATTRIBUTE15(I)
474 ,P_HEADER_REC.MOBILE_DOWNLOAD(I)
475 ,P_HEADER_REC.CURRENCY_HEADER_ID(I)
476 ,P_HEADER_REC.PTE_CODE(I)
477 ,P_HEADER_REC.LIST_SOURCE_CODE(I)
478 ,P_HEADER_REC.ORIG_SYS_HEADER_REF(I)
479 --added for MOAC
480 ,P_HEADER_REC.ORIG_ORG_ID(I)
481 -- ,P_HEADER_REC.ORIG_ORG_ID(I)
482 ,P_HEADER_REC.GLOBAL_FLAG(I)
483 FROM DUAL
484 WHERE P_HEADER_REC.process_status_flag(I) IS NOT NULL;
485
486
487 qp_bulk_loader_pub.write_log('Inserted Header records: '|| SQL%ROWCOUNT);
488
489 FORALL I IN
490 P_HEADER_REC.list_header_id.FIRST..P_HEADER_REC.list_header_id.LAST
491
492 INSERT INTO QP_LIST_HEADERS_TL
493 ( LIST_HEADER_ID
494 ,CREATION_DATE
495 ,CREATED_BY
496 ,LAST_UPDATE_DATE
497 ,LAST_UPDATED_BY
498 ,LAST_UPDATE_LOGIN
499 ,LANGUAGE
500 ,SOURCE_LANG
501 ,NAME
502 ,DESCRIPTION
503 ,VERSION_NO)
504 select P_HEADER_REC.LIST_HEADER_ID(I)
505 ,SYSDATE
506 ,FND_GLOBAL.USER_ID
507 ,SYSDATE
508 ,FND_GLOBAL.USER_ID
509 ,FND_GLOBAL.CONC_LOGIN_ID
510 ,L.LANGUAGE_CODE
511 ,nvl(P_HEADER_REC.SOURCE_LANG(I),userenv('LANG'))
512 ,P_HEADER_REC.NAME(I)
513 ,P_HEADER_REC.DESCRIPTION(I)
514 ,P_HEADER_REC.VERSION_NO(I)
515 from FND_LANGUAGES L
516 where L.INSTALLED_FLAG in ('I', 'B')
517 -- Bug 4615792 RAVI
518 /**
522 and not exists
519 Do not insert if process status flag for the record is null.
520 **/
521 AND P_HEADER_REC.process_status_flag(I) IS NOT NULL
523 (select NULL
524 from QP_LIST_HEADERS_TL T
525 where T.LIST_HEADER_ID = P_HEADER_REC.LIST_HEADER_ID(I)
526 and T.LANGUAGE = L.LANGUAGE_CODE);
527
528 IF QP_SECURITY.SECURITY_ON = 'Y' THEN
529 FOR I IN 1..p_header_rec.orig_sys_header_ref.COUNT
530 LOOP
531 QP_security.create_default_grants( p_instance_type => QP_security.G_PRICELIST_OBJECT,
532 p_instance_pk1 => p_header_rec.list_header_id(I),
533 x_return_status => x_result);
534 END LOOP;
535 END IF;
536
537 qp_bulk_loader_pub.write_log('Leaving Insert Header');
538
539 COMMIT;
540
541 EXCEPTION
542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
543 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_HEADER:'||sqlerrm);
544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
545 WHEN OTHERS THEN
546 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_HEADER:'||sqlerrm);
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
548
549
550 END INSERT_HEADER;
551
552 PROCEDURE INSERT_LINE
553 (p_line_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.LINE_REC_TYPE)
554 IS
555 BEGIN
556
557 qp_bulk_loader_pub.write_log('Entering Insert Line');
558 FORALL I IN
559 P_LINE_REC.orig_sys_line_ref.FIRST..P_LINE_REC.orig_sys_line_ref.LAST
560
561 INSERT INTO qp_list_lines
562 ( LIST_LINE_ID
563 ,CREATION_DATE
564 ,CREATED_BY
565 ,LAST_UPDATE_DATE
566 ,LAST_UPDATED_BY
567 ,LAST_UPDATE_LOGIN
568 ,PROGRAM_APPLICATION_ID
569 ,PROGRAM_ID
570 ,PROGRAM_UPDATE_DATE
571 ,REQUEST_ID
572 ,LIST_HEADER_ID
573 ,LIST_LINE_TYPE_CODE
574 ,START_DATE_ACTIVE
575 ,END_DATE_ACTIVE
576 ,AUTOMATIC_FLAG
577 ,MODIFIER_LEVEL_CODE
578 ,PRICE_BY_FORMULA_ID
579 ,LIST_PRICE
580 ,LIST_PRICE_UOM_CODE
581 ,PRIMARY_UOM_FLAG
582 ,INVENTORY_ITEM_ID
583 ,ORGANIZATION_ID
584 ,RELATED_ITEM_ID
585 ,RELATIONSHIP_TYPE_ID
586 ,SUBSTITUTION_CONTEXT
587 ,SUBSTITUTION_ATTRIBUTE
588 ,SUBSTITUTION_VALUE
589 ,REVISION
590 ,REVISION_DATE
591 ,REVISION_REASON_CODE
592 ,PRICE_BREAK_TYPE_CODE
593 ,PERCENT_PRICE
594 ,NUMBER_EFFECTIVE_PERIODS
595 ,EFFECTIVE_PERIOD_UOM
596 ,ARITHMETIC_OPERATOR
597 ,OPERAND
598 ,OVERRIDE_FLAG
599 ,PRINT_ON_INVOICE_FLAG
600 ,REBATE_TRANSACTION_TYPE_CODE
601 ,BASE_QTY
602 ,BASE_UOM_CODE
603 ,ACCRUAL_QTY
604 ,ACCRUAL_UOM_CODE
605 ,ESTIM_ACCRUAL_RATE
606 ,COMMENTS
607 ,GENERATE_USING_FORMULA_ID
608 ,REPRICE_FLAG
609 ,LIST_LINE_NO
610 ,ESTIM_GL_VALUE
611 ,BENEFIT_PRICE_LIST_LINE_ID
612 ,EXPIRATION_PERIOD_START_DATE
613 ,NUMBER_EXPIRATION_PERIODS
614 ,EXPIRATION_PERIOD_UOM
615 ,EXPIRATION_DATE
616 ,ACCRUAL_FLAG
617 ,PRICING_PHASE_ID
618 ,PRICING_GROUP_SEQUENCE
619 ,INCOMPATIBILITY_GRP_CODE
620 ,PRODUCT_PRECEDENCE
621 ,PRORATION_TYPE_CODE
622 ,ACCRUAL_CONVERSION_RATE
623 ,BENEFIT_QTY
624 ,BENEFIT_UOM_CODE
625 ,RECURRING_FLAG
626 ,BENEFIT_LIMIT
627 ,CHARGE_TYPE_CODE
628 ,CHARGE_SUBTYPE_CODE
629 ,CONTEXT
630 ,ATTRIBUTE1
631 ,ATTRIBUTE2
632 ,ATTRIBUTE3
633 ,ATTRIBUTE4
634 ,ATTRIBUTE5
635 ,ATTRIBUTE6
636 ,ATTRIBUTE7
637 ,ATTRIBUTE8
638 ,ATTRIBUTE9
639 ,ATTRIBUTE10
640 ,ATTRIBUTE11
641 ,ATTRIBUTE12
642 ,ATTRIBUTE13
643 ,ATTRIBUTE14
644 ,ATTRIBUTE15
645 ,INCLUDE_ON_RETURNS_FLAG
646 ,QUALIFICATION_IND
647 ,RECURRING_VALUE
648 ,NET_AMOUNT_FLAG
649 ,ORIG_SYS_LINE_REF
650 ,ORIG_SYS_HEADER_REF
651 --Bug#5359974 RAVI
652 ,CONTINUOUS_PRICE_BREAK_FLAG
653 )
654 --VALUES
655 -- (
656 --changes made for bug no 6028305
657 SELECT
658 P_LINE_REC.LIST_LINE_ID(I)
659 ,sysdate
660 ,FND_GLOBAL.USER_ID
661 ,sysdate
662 ,FND_GLOBAL.USER_ID
663 ,FND_GLOBAL.CONC_LOGIN_ID
664 ,NULL
665 ,NULL
666 ,NULL
667 ,P_LINE_REC.REQUEST_ID(I)
668 ,P_LINE_REC.LIST_HEADER_ID(I)
669 ,P_LINE_REC.LIST_LINE_TYPE_CODE(I)
670 ,P_LINE_REC.START_DATE_ACTIVE(I)
671 ,P_LINE_REC.END_DATE_ACTIVE(I)
672 ,P_LINE_REC.AUTOMATIC_FLAG(I)
673 ,P_LINE_REC.MODIFIER_LEVEL_CODE(I)
674 ,P_LINE_REC.PRICE_BY_FORMULA_ID(I)
675 ,P_LINE_REC.LIST_PRICE(I)
676 ,P_LINE_REC.LIST_PRICE_UOM_CODE(I)
677 ,P_LINE_REC.PRIMARY_UOM_FLAG(I)
678 ,P_LINE_REC.INVENTORY_ITEM_ID(I)
679 ,P_LINE_REC.ORGANIZATION_ID(I)
680 ,P_LINE_REC.RELATED_ITEM_ID(I)
681 ,P_LINE_REC.RELATIONSHIP_TYPE_ID(I)
682 ,P_LINE_REC.SUBSTITUTION_CONTEXT(I)
683 ,P_LINE_REC.SUBSTITUTION_ATTRIBUTE(I)
684 ,P_LINE_REC.SUBSTITUTION_VALUE(I)
685 ,P_LINE_REC.REVISION(I)
686 ,P_LINE_REC.REVISION_DATE(I)
687 ,P_LINE_REC.REVISION_REASON_CODE(I)
688 ,P_LINE_REC.PRICE_BREAK_TYPE_CODE(I)
689 ,P_LINE_REC.PERCENT_PRICE(I)
693 ,P_LINE_REC.OPERAND(I)
690 ,P_LINE_REC.NUMBER_EFFECTIVE_PERIODS(I)
691 ,P_LINE_REC.EFFECTIVE_PERIOD_UOM(I)
692 ,P_LINE_REC.ARITHMETIC_OPERATOR(I)
694 ,P_LINE_REC.OVERRIDE_FLAG(I)
695 ,P_LINE_REC.PRINT_ON_INVOICE_FLAG(I)
696 ,P_LINE_REC.REBATE_TRANSACTION_TYPE_CODE(I)
697 ,P_LINE_REC.BASE_QTY(I)
698 ,P_LINE_REC.BASE_UOM_CODE(I)
699 ,P_LINE_REC.ACCRUAL_QTY(I)
700 ,P_LINE_REC.ACCRUAL_UOM_CODE(I)
701 ,P_LINE_REC.ESTIM_ACCRUAL_RATE(I)
702 ,P_LINE_REC.COMMENTS(I)
703 ,P_LINE_REC.GENERATE_USING_FORMULA_ID(I)
704 ,P_LINE_REC.REPRICE_FLAG(I)
705 ,P_LINE_REC.LIST_LINE_NO(I)
706 ,P_LINE_REC.ESTIM_GL_VALUE(I)
707 ,P_LINE_REC.BENEFIT_PRICE_LIST_LINE_ID(I)
708 ,P_LINE_REC.EXPIRATION_PERIOD_START_DATE(I)
709 ,P_LINE_REC.NUMBER_EXPIRATION_PERIODS(I)
710 ,P_LINE_REC.EXPIRATION_PERIOD_UOM(I)
711 ,P_LINE_REC.EXPIRATION_DATE(I)
712 ,P_LINE_REC.ACCRUAL_FLAG(I)
713 ,P_LINE_REC.PRICING_PHASE_ID(I)
714 ,P_LINE_REC.PRICING_GROUP_SEQUENCE(I)
715 ,P_LINE_REC.INCOMPATIBILITY_GRP_CODE(I)
716 ,P_LINE_REC.PRODUCT_PRECEDENCE(I)
717 ,P_LINE_REC.PRORATION_TYPE_CODE(I)
718 ,P_LINE_REC.ACCRUAL_CONVERSION_RATE(I)
719 ,P_LINE_REC.BENEFIT_QTY(I)
720 ,P_LINE_REC.BENEFIT_UOM_CODE(I)
721 ,P_LINE_REC.RECURRING_FLAG(I)
722 ,P_LINE_REC.BENEFIT_LIMIT(I)
723 ,P_LINE_REC.CHARGE_TYPE_CODE(I)
724 ,P_LINE_REC.CHARGE_SUBTYPE_CODE(I)
725 ,P_LINE_REC.CONTEXT(I)
726 ,P_LINE_REC.ATTRIBUTE1(I)
727 ,P_LINE_REC.ATTRIBUTE2(I)
728 ,P_LINE_REC.ATTRIBUTE3(I)
729 ,P_LINE_REC.ATTRIBUTE4(I)
730 ,P_LINE_REC.ATTRIBUTE5(I)
731 ,P_LINE_REC.ATTRIBUTE6(I)
732 ,P_LINE_REC.ATTRIBUTE7(I)
733 ,P_LINE_REC.ATTRIBUTE8(I)
734 ,P_LINE_REC.ATTRIBUTE9(I)
735 ,P_LINE_REC.ATTRIBUTE10(I)
736 ,P_LINE_REC.ATTRIBUTE11(I)
737 ,P_LINE_REC.ATTRIBUTE12(I)
738 ,P_LINE_REC.ATTRIBUTE13(I)
739 ,P_LINE_REC.ATTRIBUTE14(I)
740 ,P_LINE_REC.ATTRIBUTE15(I)
741 ,P_LINE_REC.INCLUDE_ON_RETURNS_FLAG(I)
742 ,P_LINE_REC.QUALIFICATION_IND(I)
743 ,P_LINE_REC.RECURRING_VALUE(I)
744 ,P_LINE_REC.NET_AMOUNT_FLAG(I)
745 ,P_LINE_REC.ORIG_SYS_LINE_REF(I)
746 ,P_LINE_REC.ORIG_SYS_HEADER_REF(I)
747 --Bug#5359974 RAVI
748 ,P_LINE_REC.CONTINUOUS_PRICE_BREAK_FLAG(I)
749 --);
750 --6028305
751 FROM DUAL
752 WHERE exists
753 (Select 'Y' from qp_interface_list_lines
754 WHERE orig_sys_line_ref=P_LINE_REC.ORIG_SYS_LINE_REF(I)
755 AND request_id=P_LINE_REC.REQUEST_ID(I)
756 AND process_status_flag is not null);
757
758 qp_bulk_loader_pub.write_log('Line Insert Count: '|| sql%rowcount);
759 qp_bulk_loader_pub.write_log('Leaving Insert Line');
760
761 EXCEPTION
762 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
763 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_LINE:'||sqlerrm);
764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
765 WHEN OTHERS THEN
766 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_LINE:'||sqlerrm);
767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
768
769
770 END INSERT_LINE;
771
772
773 -- New procedure 6028305
774 PROCEDURE UPDATE_LINE_TO_OLD
775 (p_line_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.LINE_REC_TYPE)
776 IS
777 BEGIN
778
779 qp_bulk_loader_pub.write_log('Entering Update Line'||'entered'|| p_line_rec.orig_sys_line_ref.count);
780 FOR I IN
781 P_LINE_REC.orig_sys_line_ref.FIRST..P_LINE_REC.orig_sys_line_ref.LAST
782 loop
783 --qp_bulk_loader_pub.write_log('count'|| i);
784 UPDATE qp_list_lines
785 SET LAST_UPDATE_DATE =sysdate
786 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
787 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
788 ,PROGRAM_APPLICATION_ID =NULL
789 ,PROGRAM_ID =NULL
790 ,PROGRAM_UPDATE_DATE =NULL
791 ,REQUEST_ID =P_LINE_REC.REQUEST_ID(I)
792 ,LIST_HEADER_ID =P_LINE_REC.LIST_HEADER_ID(I)
793 ,LIST_LINE_TYPE_CODE =P_LINE_REC.LIST_LINE_TYPE_CODE(I)
794 ,START_DATE_ACTIVE =P_LINE_REC.START_DATE_ACTIVE(I)
795 ,END_DATE_ACTIVE =P_LINE_REC.END_DATE_ACTIVE(I)
796 ,AUTOMATIC_FLAG =P_LINE_REC.AUTOMATIC_FLAG(I)
797 ,MODIFIER_LEVEL_CODE =P_LINE_REC.MODIFIER_LEVEL_CODE(I)
798 ,PRICE_BY_FORMULA_ID =P_LINE_REC.PRICE_BY_FORMULA_ID(I)
799 ,LIST_PRICE =P_LINE_REC.LIST_PRICE(I)
800 ,LIST_PRICE_UOM_CODE =P_LINE_REC.LIST_PRICE_UOM_CODE(I)
801 ,PRIMARY_UOM_FLAG =P_LINE_REC.PRIMARY_UOM_FLAG(I)
802 ,INVENTORY_ITEM_ID =P_LINE_REC.INVENTORY_ITEM_ID(I)
803 ,ORGANIZATION_ID =P_LINE_REC.ORGANIZATION_ID(I)
804 ,RELATED_ITEM_ID =P_LINE_REC.RELATED_ITEM_ID(I)
805 ,RELATIONSHIP_TYPE_ID =P_LINE_REC.RELATIONSHIP_TYPE_ID(I)
806 ,SUBSTITUTION_CONTEXT =P_LINE_REC.SUBSTITUTION_CONTEXT(I)
807 ,SUBSTITUTION_ATTRIBUTE =P_LINE_REC.SUBSTITUTION_ATTRIBUTE(I)
808 ,SUBSTITUTION_VALUE =P_LINE_REC.SUBSTITUTION_VALUE(I)
809 ,REVISION =P_LINE_REC.REVISION(I)
810 ,REVISION_DATE =P_LINE_REC.REVISION_DATE(I)
811 ,REVISION_REASON_CODE =P_LINE_REC.REVISION_REASON_CODE(I)
812 ,PRICE_BREAK_TYPE_CODE =P_LINE_REC.PRICE_BREAK_TYPE_CODE(I)
813 ,PERCENT_PRICE =P_LINE_REC.PERCENT_PRICE(I)
814 ,NUMBER_EFFECTIVE_PERIODS =P_LINE_REC.NUMBER_EFFECTIVE_PERIODS(I)
818 ,OVERRIDE_FLAG =P_LINE_REC.OVERRIDE_FLAG(I)
815 ,EFFECTIVE_PERIOD_UOM =P_LINE_REC.EFFECTIVE_PERIOD_UOM(I)
816 ,ARITHMETIC_OPERATOR =P_LINE_REC.ARITHMETIC_OPERATOR(I)
817 ,OPERAND =P_LINE_REC.OPERAND(I)
819 ,PRINT_ON_INVOICE_FLAG =P_LINE_REC.PRINT_ON_INVOICE_FLAG(I)
820 ,REBATE_TRANSACTION_TYPE_CODE =P_LINE_REC.REBATE_TRANSACTION_TYPE_CODE(I)
821 ,BASE_QTY =P_LINE_REC.BASE_QTY(I)
822 ,BASE_UOM_CODE =P_LINE_REC.BASE_UOM_CODE(I)
823 ,ACCRUAL_QTY =P_LINE_REC.ACCRUAL_QTY(I)
824 ,ACCRUAL_UOM_CODE =P_LINE_REC.ACCRUAL_UOM_CODE(I)
825 ,ESTIM_ACCRUAL_RATE =P_LINE_REC.ESTIM_ACCRUAL_RATE(I)
826 ,COMMENTS =P_LINE_REC.COMMENTS(I)
827 ,GENERATE_USING_FORMULA_ID =P_LINE_REC.GENERATE_USING_FORMULA_ID(I)
828 ,REPRICE_FLAG =P_LINE_REC.REPRICE_FLAG(I)
829 ,LIST_LINE_NO =P_LINE_REC.LIST_LINE_NO(I)
830 ,ESTIM_GL_VALUE =P_LINE_REC.ESTIM_GL_VALUE(I)
831 ,BENEFIT_PRICE_LIST_LINE_ID =P_LINE_REC.BENEFIT_PRICE_LIST_LINE_ID(I)
832 ,EXPIRATION_PERIOD_START_DATE =P_LINE_REC.EXPIRATION_PERIOD_START_DATE(I)
833 ,NUMBER_EXPIRATION_PERIODS =P_LINE_REC.NUMBER_EXPIRATION_PERIODS(I)
834 ,EXPIRATION_PERIOD_UOM =P_LINE_REC.EXPIRATION_PERIOD_UOM(I)
835 ,EXPIRATION_DATE =P_LINE_REC.EXPIRATION_DATE(I)
836 ,ACCRUAL_FLAG =P_LINE_REC.ACCRUAL_FLAG(I)
837 ,PRICING_PHASE_ID =P_LINE_REC.PRICING_PHASE_ID(I)
838 ,PRICING_GROUP_SEQUENCE =P_LINE_REC.PRICING_GROUP_SEQUENCE(I)
839 ,INCOMPATIBILITY_GRP_CODE =P_LINE_REC.INCOMPATIBILITY_GRP_CODE(I)
840 ,PRODUCT_PRECEDENCE =P_LINE_REC.PRODUCT_PRECEDENCE(I)
841 ,PRORATION_TYPE_CODE =P_LINE_REC.PRORATION_TYPE_CODE(I)
842 ,ACCRUAL_CONVERSION_RATE =P_LINE_REC.ACCRUAL_CONVERSION_RATE(I)
843 ,BENEFIT_QTY =P_LINE_REC.BENEFIT_QTY(I)
844 ,BENEFIT_UOM_CODE =P_LINE_REC.BENEFIT_UOM_CODE(I)
845 ,RECURRING_FLAG =P_LINE_REC.RECURRING_FLAG(I)
846 ,BENEFIT_LIMIT =P_LINE_REC.BENEFIT_LIMIT(I)
847 ,CHARGE_TYPE_CODE =P_LINE_REC.CHARGE_TYPE_CODE(I)
848 ,CHARGE_SUBTYPE_CODE =P_LINE_REC.CHARGE_SUBTYPE_CODE(I)
849 ,CONTEXT =P_LINE_REC.CONTEXT(I)
850 ,ATTRIBUTE1 =P_LINE_REC.ATTRIBUTE1(I)
851 ,ATTRIBUTE2 =P_LINE_REC.ATTRIBUTE2(I)
852 ,ATTRIBUTE3 =P_LINE_REC.ATTRIBUTE3(I)
853 ,ATTRIBUTE4 =P_LINE_REC.ATTRIBUTE4(I)
854 ,ATTRIBUTE5 =P_LINE_REC.ATTRIBUTE5(I)
855 ,ATTRIBUTE6 =P_LINE_REC.ATTRIBUTE6(I)
856 ,ATTRIBUTE7 =P_LINE_REC.ATTRIBUTE7(I)
857 ,ATTRIBUTE8 =P_LINE_REC.ATTRIBUTE8(I)
858 ,ATTRIBUTE9 =P_LINE_REC.ATTRIBUTE9(I)
859 ,ATTRIBUTE10 =P_LINE_REC.ATTRIBUTE10(I)
860 ,ATTRIBUTE11 =P_LINE_REC.ATTRIBUTE11(I)
861 ,ATTRIBUTE12 =P_LINE_REC.ATTRIBUTE12(I)
862 ,ATTRIBUTE13 =P_LINE_REC.ATTRIBUTE13(I)
863 ,ATTRIBUTE14 =P_LINE_REC.ATTRIBUTE14(I)
864 ,ATTRIBUTE15 =P_LINE_REC.ATTRIBUTE15(I)
865 ,INCLUDE_ON_RETURNS_FLAG =P_LINE_REC.INCLUDE_ON_RETURNS_FLAG(I)
866 ,QUALIFICATION_IND =P_LINE_REC.QUALIFICATION_IND(I)
867 ,RECURRING_VALUE =P_LINE_REC.RECURRING_VALUE(I)
868 ,NET_AMOUNT_FLAG =P_LINE_REC.NET_AMOUNT_FLAG(I)
869 ,ORIG_SYS_LINE_REF =P_LINE_REC.ORIG_SYS_LINE_REF(I)
870 ,ORIG_SYS_HEADER_REF =P_LINE_REC.ORIG_SYS_HEADER_REF(I)
871 WHERE ORIG_SYS_LINE_REF = P_LINE_REC.ORIG_SYS_LINE_REF(I)
872 AND ORIG_SYS_HEADER_REF = P_LINE_REC.ORIG_SYS_HEADER_REF(I)
873 AND P_LINE_REC.PROCESS_STATUS_FLAG(I) = 'P'
874 AND P_LINE_REC.interface_Action_code(I) = 'UPDATE'
875 AND request_id=P_LINE_REC.REQUEST_ID(I)
876 AND (orig_sys_header_Ref,orig_Sys_line_ref) IN
877 (select orig_sys_header_Ref,orig_Sys_line_ref
878 from qp_interface_pricing_Attribs
879 where process_Status_flag is NULL
880 AND request_id=P_LINE_REC.REQUEST_ID(I)); -- updating for erred records
881 end loop;
882 qp_bulk_loader_pub.write_log('Lines Records Updated: '|| sql%rowcount);
883 qp_bulk_loader_pub.write_log('Leaving Update Line old');
884
885 EXCEPTION
886 when no_data_found then
887 qp_bulk_loader_pub.write_log( 'NO data found QP_BULK_UTIL.UPDATE_LINE_TO_OLD');
888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
889 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
890 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_LINE_TO_OLD:'||sqlerrm);
891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
892 WHEN OTHERS THEN
893 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_LINE_TO_OLD:'||sqlerrm);
894 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
895
896
897 END UPDATE_LINE_TO_OLD;
898 -- End New procedure 6028305
899
900
901 PROCEDURE INSERT_QUALIFIER
902 (P_QUALIFIER_REC IN OUT NOCOPY QP_BULK_LOADER_PUB.Qualifier_Rec_Type)
903 IS
904 BEGIN
905
906 qp_bulk_loader_pub.write_log('Entering Insert Qualifier');
907 FORALL I IN
908 P_QUALIFIER_REC.orig_sys_qualifier_ref.FIRST..P_QUALIFIER_REC.orig_sys_qualifier_ref.LAST
909
910 INSERT INTO qp_qualifiers
911 ( QUALIFIER_ID
912 ,CREATION_DATE
913 ,CREATED_BY
914 ,LAST_UPDATE_DATE
915 ,LAST_UPDATED_BY
916 ,REQUEST_ID
917 ,PROGRAM_APPLICATION_ID
918 ,PROGRAM_ID
919 ,PROGRAM_UPDATE_DATE
920 ,LAST_UPDATE_LOGIN
924 ,QUALIFIER_ATTR_VALUE
921 ,QUALIFIER_GROUPING_NO
922 ,QUALIFIER_CONTEXT
923 ,QUALIFIER_ATTRIBUTE
925 ,COMPARISON_OPERATOR_CODE
926 ,EXCLUDER_FLAG
927 ,QUALIFIER_RULE_ID
928 ,START_DATE_ACTIVE
929 ,END_DATE_ACTIVE
930 ,CREATED_FROM_RULE_ID
931 ,QUALIFIER_PRECEDENCE
932 ,LIST_HEADER_ID
933 ,LIST_LINE_ID
934 ,QUALIFIER_DATATYPE
935 ,QUALIFIER_ATTR_VALUE_TO
936 ,CONTEXT
937 ,ATTRIBUTE1
938 ,ATTRIBUTE2
939 ,ATTRIBUTE3
940 ,ATTRIBUTE4
941 ,ATTRIBUTE5
942 ,ATTRIBUTE6
943 ,ATTRIBUTE7
944 ,ATTRIBUTE8
945 ,ATTRIBUTE9
946 ,ATTRIBUTE10
947 ,ATTRIBUTE11
948 ,ATTRIBUTE12
949 ,ATTRIBUTE13
950 ,ATTRIBUTE14
951 ,ATTRIBUTE15
952 ,ACTIVE_FLAG
953 ,LIST_TYPE_CODE
954 ,QUAL_ATTR_VALUE_FROM_NUMBER
955 ,QUAL_ATTR_VALUE_TO_NUMBER
956 ,QUALIFIER_GROUP_CNT
957 ,HEADER_QUALS_EXIST_FLAG
958 ,ORIG_SYS_QUALIFIER_REF
959 ,ORIG_SYS_HEADER_REF
960 ,ORIG_SYS_LINE_REF
961 ,QUALIFY_HIER_DESCENDENTS_FLAG
962 )
963 VALUES
964 (P_QUALIFIER_REC.QUALIFIER_ID(I)
965 ,SYSDATE
966 ,FND_GLOBAL.USER_ID
967 ,SYSDATE
968 ,FND_GLOBAL.USER_ID
969 ,P_QUALIFIER_REC.request_id(I)
970 ,660
971 ,NULL
972 ,NULL
973 ,FND_GLOBAL.CONC_LOGIN_ID
974 ,P_QUALIFIER_REC.QUALIFIER_GROUPING_NO(I)
975 ,P_QUALIFIER_REC.QUALIFIER_CONTEXT(I)
976 ,P_QUALIFIER_REC.QUALIFIER_ATTRIBUTE(I)
977 ,P_QUALIFIER_REC.QUALIFIER_ATTR_VALUE(I)
978 ,P_QUALIFIER_REC.COMPARISON_OPERATOR_CODE(I)
979 ,P_QUALIFIER_REC.EXCLUDER_FLAG(I)
980 ,P_QUALIFIER_REC.QUALIFIER_RULE_ID(I)
981 ,P_QUALIFIER_REC.START_DATE_ACTIVE(I)
982 ,P_QUALIFIER_REC.END_DATE_ACTIVE(I)
983 ,P_QUALIFIER_REC.CREATED_FROM_RULE_ID(I)
984 ,P_QUALIFIER_REC.QUALIFIER_PRECEDENCE(I)
985 ,P_QUALIFIER_REC.LIST_HEADER_ID(I)
986 ,nvl(P_QUALIFIER_REC.LIST_LINE_ID(I), -1)
987 ,P_QUALIFIER_REC.QUALIFIER_DATATYPE(I)
988 ,P_QUALIFIER_REC.QUALIFIER_ATTR_VALUE_TO(I)
989 ,P_QUALIFIER_REC.CONTEXT(I)
990 ,P_QUALIFIER_REC.ATTRIBUTE1(I)
991 ,P_QUALIFIER_REC.ATTRIBUTE2(I)
992 ,P_QUALIFIER_REC.ATTRIBUTE3(I)
993 ,P_QUALIFIER_REC.ATTRIBUTE4(I)
994 ,P_QUALIFIER_REC.ATTRIBUTE5(I)
995 ,P_QUALIFIER_REC.ATTRIBUTE6(I)
996 ,P_QUALIFIER_REC.ATTRIBUTE7(I)
997 ,P_QUALIFIER_REC.ATTRIBUTE8(I)
998 ,P_QUALIFIER_REC.ATTRIBUTE9(I)
999 ,P_QUALIFIER_REC.ATTRIBUTE10(I)
1000 ,P_QUALIFIER_REC.ATTRIBUTE11(I)
1001 ,P_QUALIFIER_REC.ATTRIBUTE12(I)
1002 ,P_QUALIFIER_REC.ATTRIBUTE13(I)
1003 ,P_QUALIFIER_REC.ATTRIBUTE14(I)
1004 ,P_QUALIFIER_REC.ATTRIBUTE15(I)
1005 ,P_QUALIFIER_REC.ACTIVE_FLAG (I)
1006 ,P_QUALIFIER_REC.LIST_TYPE_CODE(I)
1007 ,P_QUALIFIER_REC.QUAL_ATTR_VALUE_FROM_NUMBER(I)
1008 ,P_QUALIFIER_REC.QUAL_ATTR_VALUE_TO_NUMBER(I)
1009 ,P_QUALIFIER_REC.QUALIFIER_GROUP_CNT(I)
1010 ,P_QUALIFIER_REC.HEADER_QUALS_EXIST_FLAG(I)
1011 ,P_QUALIFIER_REC.ORIG_SYS_QUALIFIER_REF(I)
1012 ,P_QUALIFIER_REC.ORIG_SYS_HEADER_REF(I)
1013 ,P_QUALIFIER_REC.ORIG_SYS_LINE_REF(I)
1014 ,P_QUALIFIER_REC.QUALIFY_HIER_DESCENDENTS_FLAG(I)
1015 );
1016 qp_bulk_loader_pub.write_log('Inserted Qualifier Count: '|| sql%rowcount);
1017 qp_bulk_loader_pub.write_log('Leaving Insert Qualifier');
1018
1019 EXCEPTION
1020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1021 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_QUALIFIER:'||sqlerrm);
1022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023 WHEN OTHERS THEN
1024 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_QUALIFIER:'||sqlerrm);
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026
1027 END INSERT_QUALIFIER;
1028
1029 PROCEDURE INSERT_PRICING_ATTR
1030 (P_PRICING_ATTR_REC IN OUT NOCOPY QP_BULK_LOADER_PUB.Pricing_Attr_Rec_Type)
1031 IS
1032 BEGIN
1033
1034 qp_bulk_loader_pub.write_log('Entering Insert Pricing Attribute');
1035 FORALL I IN
1036 P_PRICING_ATTR_REC.orig_sys_pricing_attr_ref.FIRST
1037 ..P_PRICING_ATTR_REC.orig_sys_pricing_attr_ref.LAST
1038
1039 INSERT INTO qp_pricing_attributes
1040 ( PRICING_ATTRIBUTE_ID
1041 ,CREATION_DATE
1042 ,CREATED_BY
1043 ,LAST_UPDATE_DATE
1044 ,LAST_UPDATED_BY
1045 ,LAST_UPDATE_LOGIN
1046 ,PROGRAM_APPLICATION_ID
1047 ,PROGRAM_ID
1048 ,PROGRAM_UPDATE_DATE
1049 ,REQUEST_ID
1050 ,LIST_LINE_ID
1051 ,EXCLUDER_FLAG
1052 ,ACCUMULATE_FLAG
1053 ,PRODUCT_ATTRIBUTE_CONTEXT
1054 ,PRODUCT_ATTRIBUTE
1055 ,PRODUCT_ATTR_VALUE
1056 ,PRODUCT_UOM_CODE
1057 ,PRICING_ATTRIBUTE_CONTEXT
1058 ,PRICING_ATTRIBUTE
1059 ,PRICING_ATTR_VALUE_FROM
1060 ,PRICING_ATTR_VALUE_TO
1061 ,ATTRIBUTE_GROUPING_NO
1062 ,PRODUCT_ATTRIBUTE_DATATYPE
1063 ,PRICING_ATTRIBUTE_DATATYPE
1064 ,COMPARISON_OPERATOR_CODE
1065 ,CONTEXT
1066 ,ATTRIBUTE1
1067 ,ATTRIBUTE2
1068 ,ATTRIBUTE3
1069 ,ATTRIBUTE4
1070 ,ATTRIBUTE5
1071 ,ATTRIBUTE6
1072 ,ATTRIBUTE7
1073 ,ATTRIBUTE8
1074 ,ATTRIBUTE9
1075 ,ATTRIBUTE10
1076 ,ATTRIBUTE11
1077 ,ATTRIBUTE12
1078 ,ATTRIBUTE13
1079 ,ATTRIBUTE14
1080 ,ATTRIBUTE15
1081 ,LIST_HEADER_ID
1085 ,PRICING_ATTR_VALUE_TO_NUMBER
1082 ,PRICING_PHASE_ID
1083 ,QUALIFICATION_IND
1084 ,PRICING_ATTR_VALUE_FROM_NUMBER
1086 ,ORIG_SYS_LINE_REF
1087 ,ORIG_SYS_HEADER_REF
1088 ,ORIG_SYS_PRICING_ATTR_REF
1089 )
1090 --VALUES
1091 --(
1092 -- Bug No 6028305
1093 SELECT
1094 QP_PRICING_ATTRIBUTES_S.nextval
1095 ,sysdate
1096 ,FND_GLOBAL.USER_ID
1097 ,sysdate
1098 ,FND_GLOBAL.USER_ID
1099 ,FND_GLOBAL.CONC_LOGIN_ID
1100 ,null
1101 ,null
1102 ,null
1103 ,P_PRICING_ATTR_REC.REQUEST_ID(I)
1104 ,P_PRICING_ATTR_REC.LIST_LINE_ID(I)
1105 ,P_PRICING_ATTR_REC.EXCLUDER_FLAG(I)
1106 ,P_PRICING_ATTR_REC.ACCUMULATE_FLAG(I)
1107 ,P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE_CONTEXT(I)
1108 ,P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE(I)
1109 ,P_PRICING_ATTR_REC.PRODUCT_ATTR_VALUE(I)
1110 ,P_PRICING_ATTR_REC.PRODUCT_UOM_CODE(I)
1111 ,P_PRICING_ATTR_REC.PRICING_ATTRIBUTE_CONTEXT(I)
1112 ,P_PRICING_ATTR_REC.PRICING_ATTRIBUTE(I)
1113 ,P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_FROM(I)
1114 ,P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_TO(I)
1115 ,P_PRICING_ATTR_REC.ATTRIBUTE_GROUPING_NO(I)
1116 ,P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE_DATATYPE(I)
1117 ,P_PRICING_ATTR_REC.PRICING_ATTRIBUTE_DATATYPE(I)
1118 ,P_PRICING_ATTR_REC.COMPARISON_OPERATOR_CODE(I)
1119 ,P_PRICING_ATTR_REC.CONTEXT(I)
1120 ,P_PRICING_ATTR_REC.ATTRIBUTE1(I)
1121 ,P_PRICING_ATTR_REC.ATTRIBUTE2(I)
1122 ,P_PRICING_ATTR_REC.ATTRIBUTE3(I)
1123 ,P_PRICING_ATTR_REC.ATTRIBUTE4(I)
1124 ,P_PRICING_ATTR_REC.ATTRIBUTE5(I)
1125 ,P_PRICING_ATTR_REC.ATTRIBUTE6(I)
1126 ,P_PRICING_ATTR_REC.ATTRIBUTE7(I)
1127 ,P_PRICING_ATTR_REC.ATTRIBUTE8(I)
1128 ,P_PRICING_ATTR_REC.ATTRIBUTE9(I)
1129 ,P_PRICING_ATTR_REC.ATTRIBUTE10(I)
1130 ,P_PRICING_ATTR_REC.ATTRIBUTE11(I)
1131 ,P_PRICING_ATTR_REC.ATTRIBUTE12(I)
1132 ,P_PRICING_ATTR_REC.ATTRIBUTE13(I)
1133 ,P_PRICING_ATTR_REC.ATTRIBUTE14(I)
1134 ,P_PRICING_ATTR_REC.ATTRIBUTE15(I)
1135 ,P_PRICING_ATTR_REC.LIST_HEADER_ID(I)
1136 ,P_PRICING_ATTR_REC.PRICING_PHASE_ID(I)
1137 ,P_PRICING_ATTR_REC.QUALIFICATION_IND(I)
1138 ,P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_FROM_NUMBER(I)
1139 ,P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_TO_NUMBER(I)
1140 ,P_PRICING_ATTR_REC.ORIG_SYS_LINE_REF(I)
1141 ,P_PRICING_ATTR_REC.ORIG_SYS_HEADER_REF(I)
1142 ,P_PRICING_ATTR_REC.ORIG_SYS_PRICING_ATTR_REF(I)
1143 --);
1144 --6028305
1145 FROM DUAL
1146 WHERE exists (SELECT 'Y' from qp_interface_pricing_Attribs
1147 WHERE ORIG_SYS_PRICING_ATTR_REF=P_PRICING_ATTR_REC.ORIG_SYS_PRICING_ATTR_REF(I)
1148 AND request_id=P_PRICING_ATTR_REC.REQUEST_ID(I)
1149 AND process_status_flag is not null);
1150
1151 qp_bulk_loader_pub.write_log('Pricing Attr Insertcount: '|| sql%rowcount);
1152 qp_bulk_loader_pub.write_log('Leaving Insert Pricing Attribute');
1153
1154 EXCEPTION
1155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156 qp_bulk_loader_pub.write_log(
1157 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_PRICING_ATTR:'||sqlerrm);
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 WHEN OTHERS THEN
1160 qp_bulk_loader_pub.write_log(
1161 'UNEXPECTED ERROR IN QP_BULK_UTIL.INSERT_PRICING_ATTR:'||sqlerrm);
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163
1164 END INSERT_PRICING_ATTR;
1165
1166 PROCEDURE UPDATE_HEADER
1167 (p_header_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.HEADER_REC_TYPE)
1168 IS
1169 BEGIN
1170
1171 qp_bulk_loader_pub.write_log('Entering Update Header');
1172 FORALL I IN
1173 P_HEADER_REC.list_header_id.FIRST..P_HEADER_REC.list_header_id.LAST
1174
1175 UPDATE qp_list_headers_b
1176 SET LAST_UPDATE_DATE =SYSDATE
1177 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
1178 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
1179 ,PROGRAM_APPLICATION_ID =661
1180 ,PROGRAM_ID =NUll
1181 ,PROGRAM_UPDATE_DATE =NULL
1182 ,REQUEST_ID =P_HEADER_REC.Request_Id(I)
1183 ,LIST_TYPE_CODE =P_HEADER_REC.LIST_TYPE_CODE(I)
1184 ,START_DATE_ACTIVE = fnd_date.canonical_to_date(p_header_rec.start_date_active(I))
1185 ,END_DATE_ACTIVE = fnd_date.canonical_to_date(p_header_rec.end_date_active(I))
1186 ,AUTOMATIC_FLAG =P_HEADER_REC.AUTOMATIC_FLAG(I)
1187 ,CURRENCY_CODE =P_HEADER_REC.CURRENCY_CODE(I)
1188 ,ROUNDING_FACTOR =P_HEADER_REC.ROUNDING_FACTOR(I)
1189 ,SHIP_METHOD_CODE =P_HEADER_REC.SHIP_METHOD_CODE(I)
1190 ,FREIGHT_TERMS_CODE =P_HEADER_REC.FREIGHT_TERMS_CODE(I)
1191 ,TERMS_ID =P_HEADER_REC.TERMS_ID(I)
1192 ,COMMENTS =P_HEADER_REC.COMMENTS(I)
1193 ,DISCOUNT_LINES_FLAG =P_HEADER_REC.DISCOUNT_LINES_FLAG(I)
1194 ,GSA_INDICATOR =P_HEADER_REC.GSA_INDICATOR(I)
1195 ,PRORATE_FLAG =P_HEADER_REC.PRORATE_FLAG(I)
1196 ,SOURCE_SYSTEM_CODE =P_HEADER_REC.SOURCE_SYSTEM_CODE(I)
1197 ,ASK_FOR_FLAG =P_HEADER_REC.ASK_FOR_FLAG(I)
1198 ,ACTIVE_FLAG =P_HEADER_REC.ACTIVE_FLAG(I)
1199 ,PARENT_LIST_HEADER_ID =P_HEADER_REC.PARENT_LIST_HEADER_ID(I)
1200 ,START_DATE_ACTIVE_FIRST =P_HEADER_REC.START_DATE_ACTIVE_FIRST(I)
1201 ,END_DATE_ACTIVE_FIRST =P_HEADER_REC.END_DATE_ACTIVE_FIRST(I)
1202 ,ACTIVE_DATE_FIRST_TYPE =P_HEADER_REC.ACTIVE_DATE_FIRST_TYPE(I)
1203 ,START_DATE_ACTIVE_SECOND =P_HEADER_REC.START_DATE_ACTIVE_SECOND(I)
1204 ,END_DATE_ACTIVE_SECOND =P_HEADER_REC.END_DATE_ACTIVE_SECOND(I)
1205 ,ACTIVE_DATE_SECOND_TYPE =P_HEADER_REC.ACTIVE_DATE_SECOND_TYPE(I)
1206 ,CONTEXT =P_HEADER_REC.CONTEXT(I)
1207 ,ATTRIBUTE1 =P_HEADER_REC.ATTRIBUTE1(I)
1211 ,ATTRIBUTE5 =P_HEADER_REC.ATTRIBUTE5(I)
1208 ,ATTRIBUTE2 =P_HEADER_REC.ATTRIBUTE2(I)
1209 ,ATTRIBUTE3 =P_HEADER_REC.ATTRIBUTE3(I)
1210 ,ATTRIBUTE4 =P_HEADER_REC.ATTRIBUTE4(I)
1212 ,ATTRIBUTE6 =P_HEADER_REC.ATTRIBUTE6(I)
1213 ,ATTRIBUTE7 =P_HEADER_REC.ATTRIBUTE7(I)
1214 ,ATTRIBUTE8 =P_HEADER_REC.ATTRIBUTE8(I)
1215 ,ATTRIBUTE9 =P_HEADER_REC.ATTRIBUTE9(I)
1216 ,ATTRIBUTE10 =P_HEADER_REC.ATTRIBUTE10(I)
1217 ,ATTRIBUTE11 =P_HEADER_REC.ATTRIBUTE11(I)
1218 ,ATTRIBUTE12 =P_HEADER_REC.ATTRIBUTE12(I)
1219 ,ATTRIBUTE13 =P_HEADER_REC.ATTRIBUTE13(I)
1220 ,ATTRIBUTE14 =P_HEADER_REC.ATTRIBUTE14(I)
1221 ,ATTRIBUTE15 =P_HEADER_REC.ATTRIBUTE15(I)
1222 ,MOBILE_DOWNLOAD =P_HEADER_REC.MOBILE_DOWNLOAD(I)
1223 ,CURRENCY_HEADER_ID =P_HEADER_REC.CURRENCY_HEADER_ID(I)
1224 ,PTE_CODE =P_HEADER_REC.PTE_CODE(I)
1225 ,LIST_SOURCE_CODE =P_HEADER_REC.LIST_SOURCE_CODE(I)
1226 ,ORIG_SYSTEM_HEADER_REF =P_HEADER_REC.ORIG_SYS_HEADER_REF(I)
1227 ,ORIG_ORG_ID =P_HEADER_REC.ORIG_ORG_ID(I)
1228 ,GLOBAL_FLAG =P_HEADER_REC.GLOBAL_FLAG(I)
1229 -- ENH undo alcoa changes RAVI
1230 /**
1231 The key between interface and qp tables is only orig_sys_hdr_ref
1232 (not list_header_id)
1233 **/
1234 WHERE ORIG_SYSTEM_HEADER_REF = P_HEADER_REC.ORIG_SYS_HEADER_REF(I)
1235 AND P_HEADER_REC.process_status_flag(I) = 'P'; --IS NULL;
1236
1237 FORALL I IN
1238 P_HEADER_REC.list_header_id.FIRST..P_HEADER_REC.list_header_id.LAST
1239
1240 UPDATE QP_LIST_HEADERS_TL
1241 SET LAST_UPDATE_DATE =SYSDATE
1242 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
1243 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
1244 ,LANGUAGE =nvl(P_HEADER_REC.LANGUAGE(I),LANGUAGE)
1245 ,SOURCE_LANG =nvl(P_HEADER_REC.SOURCE_LANG(I),SOURCE_LANG)
1246 ,NAME =P_HEADER_REC.NAME(I)
1247 ,DESCRIPTION =P_HEADER_REC.DESCRIPTION(I)
1248 ,VERSION_NO =P_HEADER_REC.VERSION_NO(I)
1249 WHERE list_header_id = (SELECT list_header_id FROM QP_LIST_HEADERS_B
1250 -- ENH undo alcoa changes RAVI
1251 /**
1252 The key between interface and qp tables is only orig_sys_hdr_ref
1253 (not list_header_id)
1254 **/
1255 WHERE ORIG_SYSTEM_HEADER_REF = P_HEADER_REC.ORIG_SYS_HEADER_REF(I)
1256 )
1257 AND LANGUAGE = P_HEADER_REC.LANGUAGE(I)
1258 AND SOURCE_LANG = P_HEADER_REC.SOURCE_LANG(I)
1259 AND P_HEADER_REC.process_status_flag(I) = 'P'; --IS NULL;
1260
1261 qp_bulk_loader_pub.write_log('Header Records Updated: '|| sql%rowcount);
1262 qp_bulk_loader_pub.write_log('Leaving Update Header');
1263
1264 EXCEPTION
1265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1266 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_HEADER:'||sqlerrm);
1267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1268 WHEN OTHERS THEN
1269 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_HEADER:'||sqlerrm);
1270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1271
1272 END UPDATE_HEADER;
1273
1274 PROCEDURE UPDATE_LINE
1275 (p_line_rec IN OUT NOCOPY QP_BULK_LOADER_PUB.LINE_REC_TYPE)
1276 IS
1277 BEGIN
1278
1279 qp_bulk_loader_pub.write_log('Entering Update Line');
1280 FORALL I IN
1281 P_LINE_REC.orig_sys_line_ref.FIRST..P_LINE_REC.orig_sys_line_ref.LAST
1282
1283 UPDATE qp_list_lines
1284 SET LAST_UPDATE_DATE =sysdate
1285 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
1286 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
1287 ,PROGRAM_APPLICATION_ID =NULL
1288 ,PROGRAM_ID =NULL
1289 ,PROGRAM_UPDATE_DATE =NULL
1290 ,REQUEST_ID =P_LINE_REC.REQUEST_ID(I)
1291 ,LIST_HEADER_ID =P_LINE_REC.LIST_HEADER_ID(I)
1292 ,LIST_LINE_TYPE_CODE =P_LINE_REC.LIST_LINE_TYPE_CODE(I)
1293 ,START_DATE_ACTIVE =P_LINE_REC.START_DATE_ACTIVE(I)
1294 ,END_DATE_ACTIVE =P_LINE_REC.END_DATE_ACTIVE(I)
1295 ,AUTOMATIC_FLAG =P_LINE_REC.AUTOMATIC_FLAG(I)
1296 ,MODIFIER_LEVEL_CODE =P_LINE_REC.MODIFIER_LEVEL_CODE(I)
1297 ,PRICE_BY_FORMULA_ID =P_LINE_REC.PRICE_BY_FORMULA_ID(I)
1298 ,LIST_PRICE =P_LINE_REC.LIST_PRICE(I)
1299 ,LIST_PRICE_UOM_CODE =P_LINE_REC.LIST_PRICE_UOM_CODE(I)
1300 ,PRIMARY_UOM_FLAG =P_LINE_REC.PRIMARY_UOM_FLAG(I)
1301 ,INVENTORY_ITEM_ID =P_LINE_REC.INVENTORY_ITEM_ID(I)
1302 ,ORGANIZATION_ID =P_LINE_REC.ORGANIZATION_ID(I)
1303 ,RELATED_ITEM_ID =P_LINE_REC.RELATED_ITEM_ID(I)
1304 ,RELATIONSHIP_TYPE_ID =P_LINE_REC.RELATIONSHIP_TYPE_ID(I)
1305 ,SUBSTITUTION_CONTEXT =P_LINE_REC.SUBSTITUTION_CONTEXT(I)
1306 ,SUBSTITUTION_ATTRIBUTE =P_LINE_REC.SUBSTITUTION_ATTRIBUTE(I)
1307 ,SUBSTITUTION_VALUE =P_LINE_REC.SUBSTITUTION_VALUE(I)
1308 ,REVISION =P_LINE_REC.REVISION(I)
1309 ,REVISION_DATE =P_LINE_REC.REVISION_DATE(I)
1310 ,REVISION_REASON_CODE =P_LINE_REC.REVISION_REASON_CODE(I)
1311 ,PRICE_BREAK_TYPE_CODE =P_LINE_REC.PRICE_BREAK_TYPE_CODE(I)
1312 ,PERCENT_PRICE =P_LINE_REC.PERCENT_PRICE(I)
1313 ,NUMBER_EFFECTIVE_PERIODS =P_LINE_REC.NUMBER_EFFECTIVE_PERIODS(I)
1314 ,EFFECTIVE_PERIOD_UOM =P_LINE_REC.EFFECTIVE_PERIOD_UOM(I)
1315 ,ARITHMETIC_OPERATOR =P_LINE_REC.ARITHMETIC_OPERATOR(I)
1316 ,OPERAND =P_LINE_REC.OPERAND(I)
1320 ,BASE_QTY =P_LINE_REC.BASE_QTY(I)
1317 ,OVERRIDE_FLAG =P_LINE_REC.OVERRIDE_FLAG(I)
1318 ,PRINT_ON_INVOICE_FLAG =P_LINE_REC.PRINT_ON_INVOICE_FLAG(I)
1319 ,REBATE_TRANSACTION_TYPE_CODE =P_LINE_REC.REBATE_TRANSACTION_TYPE_CODE(I)
1321 ,BASE_UOM_CODE =P_LINE_REC.BASE_UOM_CODE(I)
1322 ,ACCRUAL_QTY =P_LINE_REC.ACCRUAL_QTY(I)
1323 ,ACCRUAL_UOM_CODE =P_LINE_REC.ACCRUAL_UOM_CODE(I)
1324 ,ESTIM_ACCRUAL_RATE =P_LINE_REC.ESTIM_ACCRUAL_RATE(I)
1325 ,COMMENTS =P_LINE_REC.COMMENTS(I)
1326 ,GENERATE_USING_FORMULA_ID =P_LINE_REC.GENERATE_USING_FORMULA_ID(I)
1327 ,REPRICE_FLAG =P_LINE_REC.REPRICE_FLAG(I)
1328 ,LIST_LINE_NO =P_LINE_REC.LIST_LINE_NO(I)
1329 ,ESTIM_GL_VALUE =P_LINE_REC.ESTIM_GL_VALUE(I)
1330 ,BENEFIT_PRICE_LIST_LINE_ID =P_LINE_REC.BENEFIT_PRICE_LIST_LINE_ID(I)
1331 ,EXPIRATION_PERIOD_START_DATE =P_LINE_REC.EXPIRATION_PERIOD_START_DATE(I)
1332 ,NUMBER_EXPIRATION_PERIODS =P_LINE_REC.NUMBER_EXPIRATION_PERIODS(I)
1333 ,EXPIRATION_PERIOD_UOM =P_LINE_REC.EXPIRATION_PERIOD_UOM(I)
1334 ,EXPIRATION_DATE =P_LINE_REC.EXPIRATION_DATE(I)
1335 ,ACCRUAL_FLAG =P_LINE_REC.ACCRUAL_FLAG(I)
1336 ,PRICING_PHASE_ID =P_LINE_REC.PRICING_PHASE_ID(I)
1337 ,PRICING_GROUP_SEQUENCE =P_LINE_REC.PRICING_GROUP_SEQUENCE(I)
1338 ,INCOMPATIBILITY_GRP_CODE =P_LINE_REC.INCOMPATIBILITY_GRP_CODE(I)
1339 ,PRODUCT_PRECEDENCE =P_LINE_REC.PRODUCT_PRECEDENCE(I)
1340 ,PRORATION_TYPE_CODE =P_LINE_REC.PRORATION_TYPE_CODE(I)
1341 ,ACCRUAL_CONVERSION_RATE =P_LINE_REC.ACCRUAL_CONVERSION_RATE(I)
1342 ,BENEFIT_QTY =P_LINE_REC.BENEFIT_QTY(I)
1343 ,BENEFIT_UOM_CODE =P_LINE_REC.BENEFIT_UOM_CODE(I)
1344 ,RECURRING_FLAG =P_LINE_REC.RECURRING_FLAG(I)
1345 ,BENEFIT_LIMIT =P_LINE_REC.BENEFIT_LIMIT(I)
1346 ,CHARGE_TYPE_CODE =P_LINE_REC.CHARGE_TYPE_CODE(I)
1347 ,CHARGE_SUBTYPE_CODE =P_LINE_REC.CHARGE_SUBTYPE_CODE(I)
1348 ,CONTEXT =P_LINE_REC.CONTEXT(I)
1349 ,ATTRIBUTE1 =P_LINE_REC.ATTRIBUTE1(I)
1350 ,ATTRIBUTE2 =P_LINE_REC.ATTRIBUTE2(I)
1351 ,ATTRIBUTE3 =P_LINE_REC.ATTRIBUTE3(I)
1352 ,ATTRIBUTE4 =P_LINE_REC.ATTRIBUTE4(I)
1353 ,ATTRIBUTE5 =P_LINE_REC.ATTRIBUTE5(I)
1354 ,ATTRIBUTE6 =P_LINE_REC.ATTRIBUTE6(I)
1355 ,ATTRIBUTE7 =P_LINE_REC.ATTRIBUTE7(I)
1356 ,ATTRIBUTE8 =P_LINE_REC.ATTRIBUTE8(I)
1357 ,ATTRIBUTE9 =P_LINE_REC.ATTRIBUTE9(I)
1358 ,ATTRIBUTE10 =P_LINE_REC.ATTRIBUTE10(I)
1359 ,ATTRIBUTE11 =P_LINE_REC.ATTRIBUTE11(I)
1360 ,ATTRIBUTE12 =P_LINE_REC.ATTRIBUTE12(I)
1361 ,ATTRIBUTE13 =P_LINE_REC.ATTRIBUTE13(I)
1362 ,ATTRIBUTE14 =P_LINE_REC.ATTRIBUTE14(I)
1363 ,ATTRIBUTE15 =P_LINE_REC.ATTRIBUTE15(I)
1364 ,INCLUDE_ON_RETURNS_FLAG =P_LINE_REC.INCLUDE_ON_RETURNS_FLAG(I)
1365 ,QUALIFICATION_IND =P_LINE_REC.QUALIFICATION_IND(I)
1366 ,RECURRING_VALUE =P_LINE_REC.RECURRING_VALUE(I)
1367 ,NET_AMOUNT_FLAG =P_LINE_REC.NET_AMOUNT_FLAG(I)
1368 ,ORIG_SYS_LINE_REF =P_LINE_REC.ORIG_SYS_LINE_REF(I)
1369 ,ORIG_SYS_HEADER_REF =P_LINE_REC.ORIG_SYS_HEADER_REF(I)
1370 --Bug#5359974 RAVI
1371 ,CONTINUOUS_PRICE_BREAK_FLAG =P_LINE_REC.CONTINUOUS_PRICE_BREAK_FLAG(I)
1372 WHERE ORIG_SYS_LINE_REF = P_LINE_REC.ORIG_SYS_LINE_REF(I)
1373 AND ORIG_SYS_HEADER_REF = P_LINE_REC.ORIG_SYS_HEADER_REF(I)
1374 AND P_LINE_REC.PROCESS_STATUS_FLAG(I) = 'P' --IS NULL;
1375 -- 6028305
1376 AND EXISTS (Select ORIG_SYS_LINE_REF
1377 from qp_interface_list_lines
1378 where ORIG_SYS_LINE_REF = P_LINE_REC.ORIG_SYS_LINE_REF(I)
1379 AND ORIG_SYS_HEADER_REF = P_LINE_REC.ORIG_SYS_HEADER_REF(I)
1380 AND PROCESS_STATUS_FLAG is not null
1381 AND REQUEST_ID=P_LINE_REC.REQUEST_ID(I)) ;
1382
1383 qp_bulk_loader_pub.write_log('Lines Records Updated: '|| sql%rowcount);
1384 qp_bulk_loader_pub.write_log('Leaving Update Line');
1385
1386 EXCEPTION
1387 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1388 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_LINE:'||sqlerrm);
1389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1390 WHEN OTHERS THEN
1391 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_LINE:'||sqlerrm);
1392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1393
1394
1395 END UPDATE_LINE;
1396
1397 PROCEDURE UPDATE_QUALIFIER
1398 (P_QUALIFIER_REC IN OUT NOCOPY QP_BULK_LOADER_PUB.Qualifier_Rec_Type)
1399
1400 IS
1401 BEGIN
1402
1403 qp_bulk_loader_pub.write_log('Entering Update Qualifier');
1404 FORALL I IN
1405 P_QUALIFIER_REC.orig_sys_qualifier_ref.FIRST..P_QUALIFIER_REC.orig_sys_qualifier_ref.LAST
1406
1407 UPDATE qp_qualifiers
1408 SET LAST_UPDATE_DATE =SYSDATE
1409 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
1410 ,REQUEST_ID =P_QUALIFIER_REC.request_id(I)
1411 ,PROGRAM_APPLICATION_ID =660
1412 ,PROGRAM_ID =NULL
1413 ,PROGRAM_UPDATE_DATE =NULL
1414 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
1415 ,QUALIFIER_GROUPING_NO =P_QUALIFIER_REC.QUALIFIER_GROUPING_NO(I)
1416 ,QUALIFIER_CONTEXT =P_QUALIFIER_REC.QUALIFIER_CONTEXT(I)
1417 ,QUALIFIER_ATTRIBUTE =P_QUALIFIER_REC.QUALIFIER_ATTRIBUTE(I)
1418 ,QUALIFIER_ATTR_VALUE =P_QUALIFIER_REC.QUALIFIER_ATTR_VALUE(I)
1422 ,START_DATE_ACTIVE =P_QUALIFIER_REC.START_DATE_ACTIVE(I)
1419 ,COMPARISON_OPERATOR_CODE =P_QUALIFIER_REC.COMPARISON_OPERATOR_CODE(I)
1420 ,EXCLUDER_FLAG =P_QUALIFIER_REC.EXCLUDER_FLAG(I)
1421 ,QUALIFIER_RULE_ID =P_QUALIFIER_REC.QUALIFIER_RULE_ID(I)
1423 ,END_DATE_ACTIVE =P_QUALIFIER_REC.END_DATE_ACTIVE(I)
1424 ,CREATED_FROM_RULE_ID =P_QUALIFIER_REC.CREATED_FROM_RULE_ID(I)
1425 ,QUALIFIER_PRECEDENCE =P_QUALIFIER_REC.QUALIFIER_PRECEDENCE(I)
1426 ,LIST_HEADER_ID =P_QUALIFIER_REC.LIST_HEADER_ID(I)
1427 ,LIST_LINE_ID =P_QUALIFIER_REC.LIST_LINE_ID(I)
1428 ,QUALIFIER_DATATYPE =P_QUALIFIER_REC.QUALIFIER_DATATYPE(I)
1429 ,QUALIFIER_ATTR_VALUE_TO =P_QUALIFIER_REC.QUALIFIER_ATTR_VALUE_TO(I)
1430 ,CONTEXT =P_QUALIFIER_REC.CONTEXT(I)
1431 ,ATTRIBUTE1 =P_QUALIFIER_REC.ATTRIBUTE1(I)
1432 ,ATTRIBUTE2 =P_QUALIFIER_REC.ATTRIBUTE2(I)
1433 ,ATTRIBUTE3 =P_QUALIFIER_REC.ATTRIBUTE3(I)
1434 ,ATTRIBUTE4 =P_QUALIFIER_REC.ATTRIBUTE4(I)
1435 ,ATTRIBUTE5 =P_QUALIFIER_REC.ATTRIBUTE5(I)
1436 ,ATTRIBUTE6 =P_QUALIFIER_REC.ATTRIBUTE6(I)
1437 ,ATTRIBUTE7 =P_QUALIFIER_REC.ATTRIBUTE7(I)
1438 ,ATTRIBUTE8 =P_QUALIFIER_REC.ATTRIBUTE8(I)
1439 ,ATTRIBUTE9 =P_QUALIFIER_REC.ATTRIBUTE9(I)
1440 ,ATTRIBUTE10 =P_QUALIFIER_REC.ATTRIBUTE10(I)
1441 ,ATTRIBUTE11 =P_QUALIFIER_REC.ATTRIBUTE11(I)
1442 ,ATTRIBUTE12 =P_QUALIFIER_REC.ATTRIBUTE12(I)
1443 ,ATTRIBUTE13 =P_QUALIFIER_REC.ATTRIBUTE13(I)
1444 ,ATTRIBUTE14 =P_QUALIFIER_REC.ATTRIBUTE14(I)
1445 ,ATTRIBUTE15 =P_QUALIFIER_REC.ATTRIBUTE15(I)
1446 ,ACTIVE_FLAG =P_QUALIFIER_REC.ACTIVE_FLAG (I)
1447 ,LIST_TYPE_CODE =P_QUALIFIER_REC.LIST_TYPE_CODE(I)
1448 ,QUAL_ATTR_VALUE_FROM_NUMBER =P_QUALIFIER_REC.QUAL_ATTR_VALUE_FROM_NUMBER(I)
1449 ,QUAL_ATTR_VALUE_TO_NUMBER =P_QUALIFIER_REC.QUAL_ATTR_VALUE_TO_NUMBER(I)
1450 ,QUALIFIER_GROUP_CNT =P_QUALIFIER_REC.QUALIFIER_GROUP_CNT(I)
1451 ,HEADER_QUALS_EXIST_FLAG =P_QUALIFIER_REC.HEADER_QUALS_EXIST_FLAG(I)
1452 ,ORIG_SYS_QUALIFIER_REF =P_QUALIFIER_REC.ORIG_SYS_QUALIFIER_REF(I)
1453 ,ORIG_SYS_HEADER_REF =P_QUALIFIER_REC.ORIG_SYS_HEADER_REF(I)
1454 ,ORIG_SYS_LINE_REF =P_QUALIFIER_REC.ORIG_SYS_LINE_REF(I)
1455 ,QUALIFY_HIER_DESCENDENTS_FLAG=P_QUALIFIER_REC.QUALIFY_HIER_DESCENDENTS_FLAG(I)
1456 WHERE orig_sys_qualifier_ref = P_QUALIFIER_REC.orig_sys_qualifier_ref(I)
1457 AND orig_sys_header_ref = P_QUALIFIER_REC.orig_sys_header_ref(I)
1458 AND P_QUALIFIER_REC.process_status_flag(I) = 'P'; --IS NULL;
1459
1460 qp_bulk_loader_pub.write_log('Qualifier Records Updated: '|| sql%rowcount);
1461 qp_bulk_loader_pub.write_log('Leaving Update Qualifier');
1462
1463 EXCEPTION
1464 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1465 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_QUALIFIER:'||sqlerrm);
1466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1467 WHEN OTHERS THEN
1468 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_QUALIFIER:'||sqlerrm);
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470
1471 END UPDATE_QUALIFIER;
1472
1473 PROCEDURE UPDATE_PRICING_ATTR
1474 (P_PRICING_ATTR_REC IN OUT NOCOPY QP_BULK_LOADER_PUB.Pricing_Attr_Rec_Type)
1475 IS
1476 BEGIN
1477 qp_bulk_loader_pub.write_log('Entering Update Pricing Attribute');
1478 FORALL I IN
1479 P_PRICING_ATTR_REC.orig_sys_pricing_attr_ref.FIRST
1480 ..P_PRICING_ATTR_REC.orig_sys_pricing_attr_ref.LAST
1481
1482 UPDATE qp_pricing_attributes
1483 SET LAST_UPDATE_DATE =sysdate
1484 ,LAST_UPDATED_BY =FND_GLOBAL.USER_ID
1485 ,LAST_UPDATE_LOGIN =FND_GLOBAL.CONC_LOGIN_ID
1486 ,PROGRAM_APPLICATION_ID =null
1487 ,PROGRAM_ID =null
1488 ,PROGRAM_UPDATE_DATE =null
1489 ,REQUEST_ID =P_PRICING_ATTR_REC.REQUEST_ID(I)
1490 ,LIST_LINE_ID =P_PRICING_ATTR_REC.LIST_LINE_ID(I)
1491 ,EXCLUDER_FLAG =P_PRICING_ATTR_REC.EXCLUDER_FLAG(I)
1492 ,ACCUMULATE_FLAG =P_PRICING_ATTR_REC.ACCUMULATE_FLAG(I)
1493 ,PRODUCT_ATTRIBUTE_CONTEXT =P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE_CONTEXT(I)
1494 ,PRODUCT_ATTRIBUTE =P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE(I)
1495 ,PRODUCT_ATTR_VALUE =P_PRICING_ATTR_REC.PRODUCT_ATTR_VALUE(I)
1496 ,PRODUCT_UOM_CODE =P_PRICING_ATTR_REC.PRODUCT_UOM_CODE(I)
1497 ,PRICING_ATTRIBUTE_CONTEXT =P_PRICING_ATTR_REC.PRICING_ATTRIBUTE_CONTEXT(I)
1498 ,PRICING_ATTRIBUTE =P_PRICING_ATTR_REC.PRICING_ATTRIBUTE(I)
1499 ,PRICING_ATTR_VALUE_FROM =P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_FROM(I)
1500 ,PRICING_ATTR_VALUE_TO =P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_TO(I)
1501 -- ,ATTRIBUTE_GROUPING_NO =P_PRICING_ATTR_REC.ATTRIBUTE_GROUPING_NO(I)
1502 ,PRODUCT_ATTRIBUTE_DATATYPE =P_PRICING_ATTR_REC.PRODUCT_ATTRIBUTE_DATATYPE(I)
1503 ,PRICING_ATTRIBUTE_DATATYPE =P_PRICING_ATTR_REC.PRICING_ATTRIBUTE_DATATYPE(I)
1504 ,COMPARISON_OPERATOR_CODE =P_PRICING_ATTR_REC.COMPARISON_OPERATOR_CODE(I)
1505 ,CONTEXT =P_PRICING_ATTR_REC.CONTEXT(I)
1506 ,ATTRIBUTE1 =P_PRICING_ATTR_REC.ATTRIBUTE1(I)
1507 ,ATTRIBUTE2 =P_PRICING_ATTR_REC.ATTRIBUTE2(I)
1508 ,ATTRIBUTE3 =P_PRICING_ATTR_REC.ATTRIBUTE3(I)
1509 ,ATTRIBUTE4 =P_PRICING_ATTR_REC.ATTRIBUTE4(I)
1510 ,ATTRIBUTE5 =P_PRICING_ATTR_REC.ATTRIBUTE5(I)
1511 ,ATTRIBUTE6 =P_PRICING_ATTR_REC.ATTRIBUTE6(I)
1512 ,ATTRIBUTE7 =P_PRICING_ATTR_REC.ATTRIBUTE7(I)
1513 ,ATTRIBUTE8 =P_PRICING_ATTR_REC.ATTRIBUTE8(I)
1514 ,ATTRIBUTE9 =P_PRICING_ATTR_REC.ATTRIBUTE9(I)
1515 ,ATTRIBUTE10 =P_PRICING_ATTR_REC.ATTRIBUTE10(I)
1516 ,ATTRIBUTE11 =P_PRICING_ATTR_REC.ATTRIBUTE11(I)
1517 ,ATTRIBUTE12 =P_PRICING_ATTR_REC.ATTRIBUTE12(I)
1518 ,ATTRIBUTE13 =P_PRICING_ATTR_REC.ATTRIBUTE13(I)
1519 ,ATTRIBUTE14 =P_PRICING_ATTR_REC.ATTRIBUTE14(I)
1520 ,ATTRIBUTE15 =P_PRICING_ATTR_REC.ATTRIBUTE15(I)
1521 ,LIST_HEADER_ID =P_PRICING_ATTR_REC.LIST_HEADER_ID(I)
1522 ,PRICING_PHASE_ID =P_PRICING_ATTR_REC.PRICING_PHASE_ID(I)
1523 ,QUALIFICATION_IND =P_PRICING_ATTR_REC.QUALIFICATION_IND(I)
1524 ,PRICING_ATTR_VALUE_FROM_NUMBER =P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_FROM_NUMBER(I)
1525 ,PRICING_ATTR_VALUE_TO_NUMBER =P_PRICING_ATTR_REC.PRICING_ATTR_VALUE_TO_NUMBER(I)
1526 ,ORIG_SYS_LINE_REF =P_PRICING_ATTR_REC.ORIG_SYS_LINE_REF(I)
1527 ,ORIG_SYS_HEADER_REF =P_PRICING_ATTR_REC.ORIG_SYS_HEADER_REF(I)
1528 ,ORIG_SYS_PRICING_ATTR_REF =P_PRICING_ATTR_REC.ORIG_SYS_PRICING_ATTR_REF(I)
1529 WHERE ORIG_SYS_PRICING_ATTR_REF = P_PRICING_ATTR_REC.ORIG_SYS_PRICING_ATTR_REF(I)
1530 AND ORIG_SYS_LINE_REF = p_pricing_attr_rec.orig_sys_line_ref(I)
1531 AND ORIG_SYS_HEADER_REF = p_pricing_attr_rec.orig_sys_header_ref(I)
1532 AND P_PRICING_ATTR_REC.PROCESS_STATUS_FLAG(I) = 'P'
1533 -- 6028305
1534 AND EXISTS (Select ORIG_SYS_PRICING_ATTR_REF
1535 from qp_interface_pricing_Attribs
1536 where ORIG_SYS_PRICING_ATTR_REF = P_PRICING_ATTR_REC.ORIG_SYS_PRICING_ATTR_REF(I)
1537 AND ORIG_SYS_LINE_REF = P_PRICING_ATTR_REC.ORIG_SYS_LINE_REF(I)
1538 AND ORIG_SYS_HEADER_REF =P_PRICING_ATTR_REC.ORIG_SYS_HEADER_REF(I)
1539 AND REQUEST_ID= P_PRICING_ATTR_REC.REQUEST_ID(I)
1540 AND PROCESS_STATUS_FLAG is not null) ;
1541
1542 qp_bulk_loader_pub.write_log('Pricing Attr Records Updated: '|| sql%rowcount);
1543 qp_bulk_loader_pub.write_log('Leaving Update Pricing Attribute');
1544
1545 EXCEPTION
1546 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1547 qp_bulk_loader_pub.write_log(
1548 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_PRICING_ATTR:'||sqlerrm);
1549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1550 WHEN OTHERS THEN
1551 qp_bulk_loader_pub.write_log(
1552 'UNEXPECTED ERROR IN QP_BULK_UTIL.UPDATE_PRICING_ATTR:'||sqlerrm);
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554
1555 END UPDATE_PRICING_ATTR;
1556
1557 PROCEDURE DELETE_HEADER
1558 (p_request_id NUMBER)
1559 IS
1560 l_msg_txt VARCHAR2(2000);
1561
1562 BEGIN
1563
1564 qp_bulk_loader_pub.write_log('Entering Delete Header');
1565
1566 FND_MESSAGE.SET_NAME('QP', 'HDR_NOT_ALLOWED_TO_DLT');
1567 l_msg_txt := FND_MESSAGE.GET;
1568
1569 INSERT INTO QP_INTERFACE_ERRORS
1570 (error_id,last_update_date, last_updated_by, creation_date,
1571 created_by, last_update_login, request_id, program_application_id,
1572 program_id, program_update_date, entity_type, table_name, column_name,
1573 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1574 orig_sys_pricing_attr_ref,error_message)
1575 SELECT
1576 qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1577 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1578 NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_HEADERS', NULL,
1579 orig_sys_header_ref,null,null, null,l_msg_txt
1580 FROM QP_INTERFACE_LIST_HEADERS
1581 WHERE request_id = p_request_id
1582 AND interface_action_code = 'DELETE';
1583
1584 UPDATE qp_interface_list_headers
1585 SET process_status_flag = NULL --'E'
1586 WHERE request_id = p_request_id
1587 AND interface_action_code = 'DELETE';
1588
1589 qp_bulk_loader_pub.write_log('Leaving Delete Header');
1590
1591 EXCEPTION
1592 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1593 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_HEADER:'||sqlerrm);
1594 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595 WHEN OTHERS THEN
1596 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_HEADER:'||sqlerrm);
1597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1598
1599 END DELETE_HEADER;
1600
1601 PROCEDURE DELETE_QUALIFIER
1602 (P_REQUEST_ID NUMBER)
1603 IS
1604 l_msg_txt VARCHAR2(2000);
1605
1606 BEGIN
1607
1608 qp_bulk_loader_pub.write_log('Entering Delete Qualifier');
1609
1610 FND_MESSAGE.SET_NAME('QP', 'NO_RECORD');
1611 FND_MESSAGE.SET_TOKEN('RECORD' , 'QUALIFIER');
1612 l_msg_txt := FND_MESSAGE.GET;
1613
1614 --check for existance of the record
1615 INSERT INTO QP_INTERFACE_ERRORS
1616 (error_id,last_update_date, last_updated_by, creation_date,
1617 created_by, last_update_login, request_id, program_application_id,
1618 program_id, program_update_date, entity_type, table_name, column_name,
1619 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1620 orig_sys_pricing_attr_ref, error_message)
1621 SELECT
1622 qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1623 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1624 NULL,NULL, 'PRL', 'QP_INTERFACE_QUALIFIERS', NULL,
1625 orig_sys_header_ref,null,orig_sys_qualifier_ref,null, l_msg_txt
1626 FROM QP_INTERFACE_QUALIFIERS qpiq
1627 WHERE request_id = p_request_id
1628 AND interface_action_code = 'DELETE'
1629 AND NOT EXISTS
1630 (SELECT 'Y' FROM QP_QUALIFIERS qpq
1631 WHERE qpq.orig_sys_qualifier_ref = qpiq.orig_sys_qualifier_ref
1632 AND qpq.orig_sys_header_ref = qpiq.orig_sys_header_ref) ;
1633
1634 --set process status flag
1635
1636 QP_BULK_VALIDATE.MARK_ERRORED_INTERFACE_RECORD
1637 ('QUALIFIER',
1638 p_request_id);
1639
1640 --delete the records
1641 DELETE FROM QP_QUALIFIERS
1642 WHERE rowid IN
1643 (SELECT q.rowid
1644 FROM QP_INTERFACE_QUALIFIERS iq, QP_QUALIFIERS q
1645 WHERE iq.request_id = p_request_id
1646 AND iq.interface_action_code = 'DELETE'
1647 AND iq.process_status_flag = 'P' --IS NULL
1648 AND iq.orig_sys_qualifier_ref = q.orig_sys_qualifier_ref
1649 AND iq.orig_sys_header_ref = q.orig_sys_header_ref);
1650
1651 qp_bulk_loader_pub.write_log('Qualifier Records Deleted: '|| sql%rowcount);
1652
1653 --Set process_status_flag of sucessfully deleted records
1654 UPDATE qp_interface_qualifiers
1655 SET process_status_flag ='I'
1656 WHERE process_status_flag = 'P' --IS NULL
1657 AND request_id = p_request_id
1658 AND interface_action_code = 'DELETE';
1659
1660 qp_bulk_loader_pub.write_log('Leaving Delete Qualifier');
1661
1662 EXCEPTION
1663 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1664 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_QUALIFIER:'||sqlerrm);
1665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1666 WHEN OTHERS THEN
1667 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_QUALIFIER:'||sqlerrm);
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669
1670 END DELETE_QUALIFIER;
1671
1672 PROCEDURE DELETE_LINE
1673 (p_request_id NUMBER)
1674 IS
1675
1676 l_msg_txt VARCHAR2(2000);
1677
1678 BEGIN
1679
1680 qp_bulk_loader_pub.write_log('Entering Delete Line');
1681
1682 FND_MESSAGE.SET_NAME('QP', 'NO_RECORD');
1683 FND_MESSAGE.SET_TOKEN('RECORD' , 'LINE');
1684 l_msg_txt := FND_MESSAGE.GET;
1685
1686 --check for existance of the record
1687 INSERT INTO QP_INTERFACE_ERRORS
1688 (error_id,last_update_date, last_updated_by, creation_date,
1689 created_by, last_update_login, request_id, program_application_id,
1690 program_id, program_update_date, entity_type, table_name, column_name,
1691 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1692 orig_sys_pricing_attr_ref,error_message)
1693 SELECT
1694 qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1695 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1696 NULL,NULL, 'PRL', 'QP_INTERFACE_LIST_LINES', NULL,
1697 orig_sys_header_ref,orig_sys_line_ref,null,null, l_msg_txt
1698 FROM QP_INTERFACE_LIST_LINES qpil
1699 WHERE request_id = p_request_id
1700 AND interface_action_code = 'DELETE'
1701 AND NOT EXISTS
1702 (SELECT 'Y' FROM QP_LIST_LINES qpl
1703 WHERE qpl.orig_sys_line_ref = qpil.orig_sys_line_ref
1704 AND qpl.orig_sys_header_ref = qpil.orig_sys_header_ref ) ;
1705
1706 --set process status flag
1707 QP_BULK_VALIDATE.MARK_ERRORED_INTERFACE_RECORD
1708 ('LINE',
1709 p_request_id);
1710
1711
1712 --delete if any PBH child lines
1713 DELETE FROM QP_PRICING_ATTRIBUTES
1714 WHERE list_line_id IN
1715 (SELECT ll.list_line_id
1716 FROM QP_INTERFACE_LIST_LINES il, QP_RLTD_MODIFIERS r,
1717 QP_LIST_LINES l, QP_LIST_LINES ll
1718 WHERE il.request_id = p_request_id
1719 AND il.process_status_flag = 'P' --IS NULL
1720 AND il.interface_action_code = 'DELETE'
1721 AND l.orig_sys_line_ref = il.orig_sys_line_ref
1722 AND l.orig_sys_header_ref = il.orig_sys_header_ref
1723 AND r.from_rltd_modifier_id = l.list_line_id
1724 AND r.to_rltd_modifier_id = ll.list_line_id );
1725
1726 DELETE FROM QP_LIST_LINES
1727 WHERE list_line_id IN
1728 (SELECT ll.list_line_id
1729 FROM QP_INTERFACE_LIST_LINES il, QP_RLTD_MODIFIERS r,
1730 QP_LIST_LINES l, QP_LIST_LINES ll
1731 WHERE il.request_id = p_request_id
1732 AND il.process_status_flag = 'P' --IS NULL
1733 AND il.interface_action_code = 'DELETE'
1734 AND l.orig_sys_line_ref = il.orig_sys_line_ref
1735 AND l.orig_sys_header_ref = il.orig_sys_header_ref
1736 AND r.from_rltd_modifier_id = l.list_line_id
1737 AND r.to_rltd_modifier_id = ll.list_line_id );
1738
1739 DELETE FROM QP_RLTD_MODIFIERS
1740 WHERE from_rltd_modifier_id IN
1741 (SELECT l.list_line_id FROM QP_LIST_LINES l, QP_INTERFACE_LIST_LINES il
1742 WHERE il.request_id = p_request_id
1743 AND il.process_status_flag = 'P' --IS NULL
1744 AND il.interface_action_code = 'DELETE'
1745 AND il.orig_sys_line_ref = l.orig_sys_line_ref
1746 AND l.orig_sys_header_ref = il.orig_sys_header_ref);
1747
1748 --end
1749
1750 DELETE FROM QP_PRICING_ATTRIBUTES
1751 WHERE list_line_id IN
1752 (SELECT l.list_line_id
1753 FROM QP_INTERFACE_LIST_LINES il,QP_LIST_LINES l
1754 WHERE il.request_id = p_request_id
1755 AND il.process_status_flag = 'P' --IS NULL
1756 AND il.interface_action_code = 'DELETE'
1757 AND il.orig_sys_line_ref = l.orig_sys_line_ref
1758 AND il.orig_sys_header_ref = l.orig_sys_header_ref);
1759
1760
1761 DELETE FROM QP_LIST_LINES
1762 WHERE list_line_id IN
1763 (SELECT l.list_line_id
1764 FROM QP_INTERFACE_LIST_LINES il,QP_LIST_LINES l
1765 WHERE il.request_id = p_request_id
1766 AND il.process_status_flag = 'P' --IS NULL
1767 AND il.interface_action_code = 'DELETE'
1768 AND il.orig_sys_line_ref = l.orig_sys_line_ref
1769 AND il.orig_sys_header_ref = l.orig_sys_header_ref);
1770
1771 --Set process_status_flag of sucessfully deleted records
1772 UPDATE qp_interface_list_lines
1773 SET process_status_flag ='I'
1774 WHERE process_status_flag = 'P' --IS NULL
1775 AND request_id = p_request_id
1776 AND interface_action_code = 'DELETE';
1777
1778 qp_bulk_loader_pub.write_log('Leaving Delete Line');
1779
1780 EXCEPTION
1781 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1782 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_LINE:'||sqlerrm);
1783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1784 WHEN OTHERS THEN
1788 END DELETE_LINE;
1785 qp_bulk_loader_pub.write_log( 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_LINE:'||sqlerrm);
1786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1787
1789
1790 PROCEDURE DELETE_PRICING_ATTR
1791 (p_request_id NUMBER)
1792 IS
1793 l_msg_txt VARCHAR2(2000);
1794
1795 BEGIN
1796
1797 qp_bulk_loader_pub.write_log('Entering Delete Pricing Attribute');
1798 FND_MESSAGE.SET_NAME('QP', 'NO_RECORD');
1799 FND_MESSAGE.SET_TOKEN('RECORD' , 'PRICING ATTRIBUTE');
1800 l_msg_txt := FND_MESSAGE.GET;
1801
1802 --check for existance of the record
1803 INSERT INTO QP_INTERFACE_ERRORS
1804 (error_id,last_update_date, last_updated_by, creation_date,
1805 created_by, last_update_login, request_id, program_application_id,
1806 program_id, program_update_date, entity_type, table_name, column_name,
1807 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1808 orig_sys_pricing_attr_ref,error_message)
1809 SELECT
1810 qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1811 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1812 NULL,NULL, 'PRL', 'QP_INTERFACE_PRICING_ATTRIBS', NULL,
1813 orig_sys_header_ref,orig_sys_line_ref,null,orig_sys_pricing_attr_ref, l_msg_txt
1814 FROM QP_INTERFACE_PRICING_ATTRIBS qpip
1815 WHERE request_id = p_request_id
1816 AND process_status_flag = 'P' --is null
1817 AND interface_action_code = 'DELETE'
1818 AND NOT EXISTS
1819 (SELECT 'Y' FROM QP_PRICING_ATTRIBUTES qpp
1820 WHERE qpp.orig_sys_pricing_attr_ref = qpip.orig_sys_pricing_attr_ref
1821 AND qpp.pricing_attribute_context IS NOT NULL
1822 AND qpp.request_id = p_request_id
1823 AND qpp.orig_sys_line_ref = qpip.orig_sys_line_ref
1824 AND qpp.orig_sys_header_ref = qpip.orig_sys_header_ref)
1825 -- Bug# 5236656
1826 -- If the Line is being deleted then the pricing record is automatically deleted.
1827 -- An other attempt to delete the Pricing attribute is not necessary.
1828 -- Check if the Line is also being deleted in this request.
1829 -- If so do not thrown an error that the pricing record is not found as it has already been deleted.
1830 AND NOT EXISTS (SELECT 'Y' FROM QP_INTERFACE_LIST_LINES qpil
1831 WHERE qpil.orig_sys_line_ref = qpip.orig_sys_line_ref
1832 AND qpil.orig_sys_header_ref = qpip.orig_sys_header_ref
1833 AND qpil.request_id = p_request_id
1834 AND qpil.interface_action_code = 'DELETE');
1835
1836 --Bug# 5253114 RAVI START
1837
1838 qp_bulk_loader_pub.write_log('Cannot delete a Price Break child line if it is not the highest break.');
1839 FND_MESSAGE.SET_NAME('QP', 'QP_NO_DELETE_PB_CHILD_LINE');
1840 l_msg_txt := FND_MESSAGE.GET;
1841
1842 INSERT INTO QP_INTERFACE_ERRORS
1843 (error_id,last_update_date, last_updated_by, creation_date,
1844 created_by, last_update_login, request_id, program_application_id,
1845 program_id, program_update_date, entity_type, table_name, column_name,
1846 orig_sys_header_ref,orig_sys_line_ref,orig_sys_qualifier_ref,
1847 orig_sys_pricing_attr_ref,error_message)
1848 SELECT
1849 qp_interface_errors_s.nextval, sysdate ,FND_GLOBAL.USER_ID, sysdate,
1850 FND_GLOBAL.USER_ID, FND_GLOBAL.CONC_LOGIN_ID, p_request_id, 660,
1851 NULL,NULL, 'PRL', 'QP_INTERFACE_PRICING_ATTRIBS', NULL,
1852 orig_sys_header_ref,orig_sys_line_ref,null,orig_sys_pricing_attr_ref, l_msg_txt
1853 FROM QP_INTERFACE_PRICING_ATTRIBS qpip
1854 WHERE request_id = p_request_id
1855 AND process_status_flag = 'P' --is null
1856 AND interface_action_code = 'DELETE'
1857 AND EXISTS (SELECT 'PRICE BREAK CHILD LINE'
1858 FROM qp_list_lines la, qp_rltd_modifiers ra
1859 WHERE la.orig_sys_line_ref = qpip.orig_sys_line_ref
1860 AND ra.to_rltd_modifier_id = la.list_line_id
1861 AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
1862 )
1863 AND pricing_attr_value_to <>
1864 (SELECT max(to_number(pb.pricing_attr_value_to))
1865 FROM qp_list_lines la, qp_rltd_modifiers ra,
1866 qp_rltd_modifiers rb, qp_pricing_attributes pb
1867 WHERE la.orig_sys_line_ref = qpip.orig_sys_line_ref
1868 AND ra.to_rltd_modifier_id = la.list_line_id
1869 AND ra.rltd_modifier_grp_type = 'PRICE BREAK'
1870 AND ra.from_rltd_modifier_id = rb.from_rltd_modifier_id
1871 AND rb.to_rltd_modifier_id = pb.list_line_id) ;
1872 --Bug# 5253114 RAVI END
1873
1874
1875 --set process status flag
1876 QP_BULK_VALIDATE.MARK_ERRORED_INTERFACE_RECORD
1877 ('PRICING_ATTRIBS',
1878 p_request_id);
1879
1880
1881 --delete the records
1882 DELETE FROM QP_PRICING_ATTRIBUTES
1883 WHERE pricing_attribute_id IN
1884 (SELECT pa.pricing_attribute_id
1885 FROM QP_INTERFACE_PRICING_ATTRIBS ipa, QP_PRICING_ATTRIBUTES pa
1886 WHERE ipa.request_id = p_request_id
1887 AND ipa.interface_action_code = 'DELETE'
1888 AND ipa.process_status_flag = 'P' --IS NULL
1889 AND ipa.orig_sys_line_ref = pa.orig_sys_line_ref
1890 AND ipa.orig_sys_header_ref = pa.orig_sys_header_ref
1891 AND ipa.orig_sys_pricing_attr_ref = pa.orig_sys_pricing_attr_ref);
1892
1893 qp_bulk_loader_pub.write_log('Number of PA deleted: '||to_char(SQL%ROWCOUNT));
1894 --Set process_status_flag of sucessfully deleted records
1895 UPDATE qp_interface_pricing_attribs
1896 SET process_status_flag ='I'
1897 WHERE process_status_flag = 'P' --IS NULL
1898 AND request_id = p_request_id
1899 AND interface_action_code = 'DELETE';
1900
1901 EXCEPTION
1902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1903 qp_bulk_loader_pub.write_log(
1904 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_PRICING_ATTR:'||sqlerrm);
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906 WHEN OTHERS THEN
1907 qp_bulk_loader_pub.write_log(
1908 'UNEXPECTED ERROR IN QP_BULK_UTIL.DELETE_PRICING_ATTR:'||sqlerrm);
1909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1910
1911 qp_bulk_loader_pub.write_log('Leaving Delete Pricing Attribute');
1912
1913 END DELETE_PRICING_ATTR;
1914
1915
1916 END QP_BULK_UTIL;