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