[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_LINE_FACT_PVT
Source
1 Package Body ASO_BI_LINE_FACT_PVT AS
2 /* $Header: asovbiqlinb.pls 120.1 2005/07/06 00:45:34 kedukull noship $ */
3
4 g_schema VARCHAR2(30):= NULL;
5
6 -- This deletes quote lines that have got updated in date range
7 -- This is done to remove quote lines that belonged to older versions
8 -- of the quote
9 PROCEDURE Cleanup_Line_Data
10 AS
11 BEGIN
12
13 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
14 BIS_COLLECTION_UTILITIES.Debug('Start deleting the updated rows from ' ||
15 'ASO_BI_QUOTE_LINES_ALL table');
16 END IF;
17
18 DELETE FROM ASO_BI_QUOTE_LINES_ALL qlin
19 WHERE qlin.quote_number IN (
20 SELECT quote_number
21 FROM ASO_BI_QUOTE_IDS
22 );
23
24 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
25 BIS_COLLECTION_UTILITIES.Debug('Deleted the updated rows from '||
26 'ASO_BI_QUOTE_LINES_ALL table');
27 END IF;
28
29 COMMIT;
30 END Cleanup_Line_Data;
31
32
33 -- Inserts the quote lines id into ASO_BI_LINE_IDS table
34 -- corresponding the quotes that got changed in the given window
35 -- of dates
36 PROCEDURE initLoad_Quote_Line_ids
37 AS
38
39 BEGIN
40
41 INSERT/*+ APPEND PARALLEL(ASO_LINE_IDS)*/ INTO ASO_BI_LINE_IDS ASO_LINE_IDS
42 ( QUOTE_HEADER_ID,
43 QUOTE_NUMBER,
44 MAX_QUOTE_VERSION,
45 QUOTE_CREATION_DATE,
46 QUOTE_LINE_ID,
47 BATCH_ID
48 )
49 SELECT /*+ PARALLEL(qlin) PARALLEL(qid)*/
50 qid.quote_header_id,
51 qid.quote_number,
52 qid.max_quote_version,
53 qid.quote_creation_date,
54 qlin.quote_line_id,
55 NULL
56 FROM ASO_QUOTE_LINES_ALL qlin,
57 ASO_BI_QUOTE_IDS qid
58 WHERE qid.quote_header_id = qlin.quote_header_id;
59
60 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
61 BIS_COLLECTION_UTILITIES.Debug('Inserted '||SQL%ROWCOUNT||' rows in ' ||
62 'ASO_BI_LINE_IDS.');
63 END IF;
64
65 COMMIT;
66 ASO_BI_UTIL_PVT.Analyze_Table('ASO_BI_LINE_IDS');
67
68 END initLoad_Quote_Line_ids;
69
70
71 -- Inserts the updated quote line ids into ASO_BI_LINE_IDS table
72 PROCEDURE Populate_Quote_Line_ids
73 AS
74 l_batch_size Number := 1000;
75 BEGIN
76
77 /* For Transactions with Average Complexity */
78 l_batch_size:= bis_common_parameters.get_batch_size(
79 BIS_COMMON_PARAMETERS.MEDIUM);
80
81 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
82 BIS_COLLECTION_UTILITIES.Debug('Started populating ASO_BI_LINE_IDS table');
83 END IF;
84
85 INSERT INTO ASO_BI_LINE_IDS
86 ( QUOTE_HEADER_ID,
87 QUOTE_NUMBER,
88 MAX_QUOTE_VERSION,
89 QUOTE_CREATION_DATE,
90 QUOTE_LINE_ID,
91 BATCH_ID
92 )
93 SELECT qid.quote_header_id,
94 qid.quote_number,
95 qid.max_quote_version,
96 qid.quote_creation_date,
97 qlin.quote_line_id,
98 CEIL(ROWNUM/l_batch_size)
99 FROM ASO_QUOTE_LINES_ALL qlin,
100 ASO_BI_QUOTE_IDS qid
101 WHERE qid.quote_header_id = qlin.quote_header_id;
102
103 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
104 BIS_COLLECTION_UTILITIES.Debug('Inserted '||SQL%ROWCOUNT||' rows in ' ||
105 'ASO_BI_QUOTE_IDS.');
106 END IF;
107
108 COMMIT;
109 END Populate_Quote_Line_ids;
110
111
112 -- Inserts records into ASO_BI_QUOTE_FACT_JOBS as many as the batches
113 PROCEDURE Register_Line_jobs
114 AS
115 BEGIN
116
117 INSERT INTO ASO_BI_QUOTE_FACT_JOBS
118 ( batch_id,
119 worker_number,
120 status
121 )
122 SELECT DISTINCT batch_id, 0, 'UNASSIGNED'
123 FROM ASO_BI_LINE_IDS ;
124 COMMIT;
125
126 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
127 BIS_COLLECTION_UTILITIES.Debug('Inserted '||SQL%ROWCOUNT||' jobs into '||
128 'ASO_BI_QUOTE_FACT_JOBS');
129 END IF;
130
131 END Register_Line_jobs;
132
133 Procedure InitiLoad_QotLineStg
134 As
135 l_rate_type varchar2(50);
136 l_rpt_curr varchar2(50);
137 l_sec_currency varchar2(50);
138 Begin
139
140 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
141 BIS_COLLECTION_UTILITIES.Debug('populating the Quote Lines Staging');
142 END IF;
143
144 l_rate_type := bis_common_parameters.get_rate_type;
145 l_rpt_curr := bis_common_parameters.get_currency_code;
146 l_sec_currency := bis_common_parameters.get_secondary_currency_code;
147
148
149 INSERT/*+ APPEND PARALLEL(STG) */ INTO ASO_BI_QUOTE_LINES_STG STG
150 ( quote_header_id,
151 quote_number,
152 quote_version,
153 quote_creation_date,
154 quote_last_update_date,
155 quote_expiration_date,
156 resource_id,
157 resource_grp_id,
158 quote_source_code,
159 cust_account_id,
160 invoice_to_cust_account_id_hdr,
161 cust_party_id,
162 minisite_id_hdr,
163 quote_status_id,
164 sales_channel_code,
165 org_id,
166 order_id,
167 currency_code,
168 reporting_currency,
169 functional_currency,
170 reporting_conversion_rate,
171 functional_conversion_rate,
172 sec_conversion_rate,
173 shipment_id,
174 line_value,
175 line_quote_value,
176 quantity,
177 uom_code,
178 minisite_id,
179 marketing_source_code_id,
180 inventory_item_id,
181 organization_id,
182 invoice_to_cust_account_id,
183 agreement_id,
184 item_type_code,
185 config_header_id,
186 quote_line_id,
187 line_category_code,
188 marketing_source_code_id_hdr,
189 order_creation_date,
190 Config_Item_Id,
191 Charge_periodicity_code
192 )
193 SELECT /*+
194 USE_HASH(qhd) USE_HASH(linid) USE_HASH(ord) USE_HASH(hzcst)
195 USE_HASH(qlin) USE_HASH(qdtl) USE_HASH(qshp)
196 PARALLEL(qlin) PARALLEL(qhd) PARALLEL(qshp) PARALLEL(qdtl)
197 PARALLEL(linid) PARALLEL(hzcst) PARALLEL(ord) PARALLEL(RATE)*/
198 qhd.quote_header_id ,
199 qhd.quote_number,
200 linid.max_quote_version,
201 linid.QUOTE_CREATION_DATE,
202 qhd.last_update_date,
203 TRUNC(qhd.quote_expiration_date) + 1,
204 qhd.resource_id,
205 qhd.resource_grp_id,
206 qhd.quote_source_code,
207 qhd.cust_account_id,
208 qhd.invoice_to_cust_account_id,
209 hzcst.party_id cust_party_id,
210 qhd.minisite_id,
211 qhd.quote_status_id,
212 qhd.sales_channel_code,
213 qhd.org_id,
214 qhd.order_id,
215 qhd.currency_code,
216 l_rpt_curr,
217 rate.func_currency_code functional_currency,
218 rate.prim_conversion_rate, -- primary conv rate
219 rate.func_conversion_rate, -- functional currency conversion rate
220 rate.sec_conversion_rate, -- secondary currency conversion rate
221 qshp.shipment_id,
222 qlin.line_list_price*qlin.quantity,
223 qlin.line_quote_price*qlin.quantity,
224 qlin.quantity,
225 qlin.uom_code,
226 qlin.minisite_id,
227 qlin.marketing_source_code_id,
228 qlin.inventory_item_id,
229 qlin.organization_id,
230 qlin.invoice_to_cust_account_id,
231 qlin.agreement_id,
232 qlin.item_type_code,
233 qdtl.config_header_id,
234 qlin.quote_line_id,
235 qlin.line_category_code,
236 qhd.marketing_source_code_id,
237 TRUNC(ord.creation_date),
238 qdtl.config_item_id,
239 qlin.Charge_periodicity_code
240 FROM aso_quote_lines_all qlin,
241 aso_quote_headers_all qhd,
242 aso_shipments qshp,
243 aso_quote_line_details qdtl,
244 aso_bi_line_ids linid,
245 aso_bi_currency_rates rate,
246 hz_cust_accounts hzcst,
247 OE_ORDER_HEADERS_ALL ORD
248 WHERE qlin.quote_header_id = qhd.quote_header_id
249 AND qlin.quote_line_id = qshp.quote_line_id
250 AND qhd.quote_header_id = linid.quote_header_id
251 AND qhd.org_id = rate.org_id
252 AND qhd.currency_code = rate.txn_currency
253 AND rate.exchange_date = trunc(qhd.last_update_date)
254 AND qlin.quote_line_id = qdtl.quote_line_id(+)
255 AND qlin.quote_line_id = linid.quote_line_id
256 AND qhd.cust_account_id = hzcst.CUST_ACCOUNT_ID(+)
257 AND QHD.Order_id = ord.header_id(+);
258
259 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
260 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote Line Staging:'||
261 'Rowcount:'|| SQL%ROWCOUNT);
262 END IF;
263
264 COMMIT;
265
266 ASO_BI_UTIL_PVT.Analyze_Table('ASO_BI_QUOTE_LINES_STG');
267
268 End InitiLoad_QotLineStg ;
269
270
271 -- Populates the Staging table. Will be called as a part of
272 -- incremental load of the quote lines
273 PROCEDURE Populate_Qot_Line_Fact_Stg(p_batch_id IN NUMBER)
274 AS
275 l_rate_type varchar2(50);
276 l_rpt_curr varchar2(50);
277 l_sec_currency varchar2(50);
278 BEGIN
279
280 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
281 BIS_COLLECTION_UTILITIES.Debug('Stared populating the Quote line fact Staging');
282 END IF;
283
284 l_rate_type := bis_common_parameters.get_rate_type;
285 l_rpt_curr := bis_common_parameters.get_currency_code;
286 l_sec_currency := bis_common_parameters.get_secondary_currency_code;
287 -- Check for reporting currency null
288 INSERT/*+ append */ INTO ASO_BI_QUOTE_LINES_STG
289 ( quote_header_id,
290 quote_number,
291 quote_version,
292 quote_creation_date,
293 quote_last_update_date,
294 quote_expiration_date,
295 resource_id,
296 resource_grp_id,
297 quote_source_code,
298 cust_account_id,
299 invoice_to_cust_account_id_hdr,
300 cust_party_id,
301 minisite_id_hdr,
302 quote_status_id,
303 sales_channel_code,
304 org_id,
305 order_id,
306 currency_code,
307 reporting_currency,
308 functional_currency,
309 reporting_conversion_rate,
310 functional_conversion_rate,
311 sec_conversion_rate,
312 shipment_id,
313 line_value,
314 line_quote_value,
315 quantity,
316 uom_code,
317 minisite_id,
318 marketing_source_code_id,
319 inventory_item_id,
320 organization_id,
321 invoice_to_cust_account_id,
322 agreement_id,
323 item_type_code,
324 config_header_id,
325 quote_line_id,
326 line_category_code,
327 marketing_source_code_id_hdr,
328 order_creation_date,
329 config_item_id,
330 Charge_periodicity_code
331 )
332 SELECT qhd.quote_header_id ,
333 qhd.quote_number,
334 linid.max_quote_version,
335 linid.QUOTE_CREATION_DATE,
336 qhd.last_update_date,
337 TRUNC(qhd.quote_expiration_date)+1,
338 qhd.resource_id,
339 qhd.resource_grp_id,
340 qhd.quote_source_code,
341 qhd.cust_account_id,
342 qhd.invoice_to_cust_account_id,
343 hzcst.party_id cust_party_id,
344 qhd.minisite_id,
345 qhd.quote_status_id,
346 qhd.sales_channel_code,
347 qhd.org_id,
348 qhd.order_id,
349 qhd.currency_code,
350 l_rpt_curr,
351 rate.func_currency_code functional_currency,
352 rate.prim_conversion_rate, -- Primary Currency Conv. rate
353 rate.func_conversion_rate, -- Functional Currency conv. Rate
354 rate.sec_conversion_rate, -- Seondary Currency Conv. rate
355 qshp.shipment_id,
356 qlin.line_list_price*qlin.quantity,
357 qlin.line_quote_price*qlin.quantity,
358 qlin.quantity,
359 qlin.uom_code,
360 qlin.minisite_id,
361 qlin.marketing_source_code_id,
362 qlin.inventory_item_id,
363 qlin.organization_id,
364 qlin.invoice_to_cust_account_id,
365 qlin.agreement_id,
366 qlin.item_type_code,
367 qdtl.config_header_id,
368 qlin.quote_line_id,
369 qlin.line_category_code,
370 qhd.marketing_source_code_id,
371 TRUNC(ord.creation_date),
372 qdtl.config_item_id,
373 qlin.Charge_periodicity_code
374 FROM aso_quote_lines_all qlin,
375 aso_quote_headers_all qhd,
376 aso_shipments qshp,
377 aso_quote_line_details qdtl,
378 aso_bi_line_ids linid,
379 aso_bi_currency_rates rate,
380 hz_cust_accounts hzcst,
381 OE_ORDER_HEADERS_ALL ORD
382 WHERE qlin.quote_header_id = qhd.quote_header_id
383 AND qlin.quote_line_id = qshp.quote_line_id
384 AND qhd.quote_header_id = linid.quote_header_id
385 AND qhd.org_id = rate.org_id
386 AND qhd.currency_code = rate.txn_currency
387 AND rate.exchange_date = trunc(qhd.last_update_date)
388 AND qlin.quote_line_id = qdtl.quote_line_id(+)
389 AND qlin.quote_line_id = linid.quote_line_id
390 AND qhd.cust_account_id = hzcst.CUST_ACCOUNT_ID(+)
391 AND qhd.Order_id = ord.header_id(+)
392 AND linid.batch_id = p_batch_id;
393
397 END IF;
394 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
395 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote line fact '||
396 'Staging:Rowcount: '||SQL%ROWCOUNT);
398
399 COMMIT;
400
401 ASO_BI_UTIL_PVT.Analyze_Table('ASO_BI_QUOTE_LINES_STG');
402
403 END Populate_Qot_Line_Fact_Stg;
404
405 -- Initial Load of ASO_BI_QUOTE_LINES_ALL
406 -- Called as a part of initial load of quote lines
407 Procedure InitiLoad_QotLine
408 As
409 l_user_id number;
410 l_login_id number;
411 l_sysdate date;
412 Begin
413
414 l_user_id := FND_GLOBAL.user_id;
415 l_login_id := FND_GLOBAL.login_id;
416 l_sysdate := sysdate;
417
418 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
419 BIS_COLLECTION_UTILITIES.Debug('Start populating the Quote Line fact');
420 END IF;
421
422 EXECUTE IMMEDIATE
423 'INSERT/*+ APPEND PARALLEL(QOT_LINES_ALL)*/
424 INTO ASO_BI_QUOTE_LINES_ALL QOT_LINES_ALL
425 ( quote_header_id,
426 quote_number,
427 quote_version,
428 quote_creation_date,
429 quote_last_update_date,
430 quote_expiration_date,
431 resource_id,
432 resource_grp_id,
433 quote_source_code,
434 cust_account_id,
435 invoice_to_cust_account_id_hdr,
436 minisite_id_hdr,
437 cust_party_id,
438 quote_status_id,
439 sales_channel_code,
440 org_id,
441 order_id,
442 currency_code,
443 reporting_currency,
444 functional_currency,
445 reporting_conversion_rate,
446 functional_conversion_rate,
447 sec_conversion_rate,
448 shipment_id,
449 line_value,
450 line_quote_value,
451 quantity,
452 uom_code,
453 minisite_id,
454 marketing_source_code_id,
455 inventory_item_id,
456 organization_id,
457 invoice_to_cust_account_id,
458 agreement_id,
459 quote_line_id,
460 line_category_code,
461 marketing_source_code_id_hdr,
462 publish_flag,
463 creation_date,
464 created_by,
465 last_update_date,
466 last_updated_by,
467 last_update_login,
468 order_creation_date,
469 Top_Inventory_Item_Id,
470 Top_Organization_Id,
471 Charge_periodicity_code
472 )
473 SELECT /*+ PARALLEL(qlinstg)*/
474 qlinstg.quote_header_id,
475 qlinstg.quote_number,
476 qlinstg.quote_version,
477 qlinstg.quote_creation_date,
478 qlinstg.quote_last_update_date,
479 qlinstg.quote_expiration_date,
480 qlinstg.resource_id,
481 qlinstg.resource_grp_id,
482 qlinstg.quote_source_code,
483 qlinstg.cust_account_id,
484 qlinstg.invoice_to_cust_account_id_hdr,
485 qlinstg.minisite_id_hdr,
486 qlinstg.cust_party_id,
487 qlinstg.quote_status_id,
488 qlinstg.sales_channel_code,
489 qlinstg.org_id,
490 qlinstg.order_id,
491 qlinstg.currency_code,
492 qlinstg.reporting_currency,
493 qlinstg.functional_currency,
494 qlinstg.reporting_conversion_rate,
495 qlinstg.functional_conversion_rate,
496 qlinstg.sec_conversion_rate,
497 qlinstg.shipment_id,
498 qlinstg.line_value,
499 qlinstg.line_quote_value,
500 qlinstg.quantity,
501 qlinstg.uom_code,
502 qlinstg.minisite_id,
503 qlinstg.marketing_source_code_id,
504 qlinstg.inventory_item_id,
505 qlinstg.organization_id,
506 qlinstg.invoice_to_cust_account_id,
507 qlinstg.agreement_id,
508 qlinstg.quote_line_id,
509 qlinstg.line_category_code,
510 qlinstg.marketing_source_code_id_hdr,
511 null publish_flag,
512 :l_sysdate,
513 :l_user_id,
514 :l_sysdate,
515 :l_user_id,
516 :l_login_id,
517 qlinstg.order_creation_date,
518 FIRST_VALUE(inventory_item_id)OVER(
519 PARTITION BY config_header_id
520 ORDER BY config_item_id ASC
521 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
522 Top_Inventory_Item_Id,
523 FIRST_VALUE(organization_id)OVER(
524 PARTITION BY config_header_id
525 ORDER BY config_item_id ASC
526 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
527 Top_Organization_Id,
528 qlinstg.Charge_periodicity_code
529 FROM ASO_BI_QUOTE_LINES_STG qlinstg
530 WHERE Config_Header_Id IS NOT NULL'
531 USING l_sysdate,
532 l_user_id,
533 l_sysdate,
534 l_user_id,
535 l_login_id;
536
537 COMMIT;
538
539 EXECUTE IMMEDIATE
543 quote_number,
540 'INSERT/*+ APPEND PARALLEL(QOT_LINES_ALL)*/
541 INTO ASO_BI_QUOTE_LINES_ALL QOT_LINES_ALL
542 ( quote_header_id,
544 quote_version,
545 quote_creation_date,
546 quote_last_update_date,
547 quote_expiration_date,
548 resource_id,
549 resource_grp_id,
550 quote_source_code,
551 cust_account_id,
552 invoice_to_cust_account_id_hdr,
553 minisite_id_hdr,
554 cust_party_id,
555 quote_status_id,
556 sales_channel_code,
557 org_id,
558 order_id,
559 currency_code,
560 reporting_currency,
561 functional_currency,
562 reporting_conversion_rate,
563 functional_conversion_rate,
564 sec_conversion_rate,
565 shipment_id,
566 line_value,
567 line_quote_value,
568 quantity,
569 uom_code,
570 minisite_id,
571 marketing_source_code_id,
572 inventory_item_id,
573 organization_id,
574 invoice_to_cust_account_id,
575 agreement_id,
576 quote_line_id,
577 line_category_code,
578 marketing_source_code_id_hdr,
579 publish_flag,
580 creation_date,
581 created_by,
582 last_update_date,
583 last_updated_by,
584 last_update_login,
585 order_creation_date,
586 Top_Inventory_Item_Id,
587 Top_Organization_Id,
588 Charge_periodicity_code
589 )
590 SELECT /*+ PARALLEL(qlinstg)*/
591 qlinstg.quote_header_id,
592 qlinstg.quote_number,
593 qlinstg.quote_version,
594 qlinstg.quote_creation_date,
595 qlinstg.quote_last_update_date,
596 qlinstg.quote_expiration_date,
597 qlinstg.resource_id,
598 qlinstg.resource_grp_id,
599 qlinstg.quote_source_code,
600 qlinstg.cust_account_id,
601 qlinstg.invoice_to_cust_account_id_hdr,
602 qlinstg.minisite_id_hdr,
603 qlinstg.cust_party_id,
604 qlinstg.quote_status_id,
605 qlinstg.sales_channel_code,
606 qlinstg.org_id,
607 qlinstg.order_id,
608 qlinstg.currency_code,
609 qlinstg.reporting_currency,
610 qlinstg.functional_currency,
611 qlinstg.reporting_conversion_rate,
612 qlinstg.functional_conversion_rate,
613 qlinstg.sec_conversion_rate,
614 qlinstg.shipment_id,
615 qlinstg.line_value,
616 qlinstg.line_quote_value,
617 qlinstg.quantity,
618 qlinstg.uom_code,
619 qlinstg.minisite_id,
620 qlinstg.marketing_source_code_id,
621 qlinstg.inventory_item_id,
622 qlinstg.organization_id,
623 qlinstg.invoice_to_cust_account_id,
624 qlinstg.agreement_id,
625 qlinstg.quote_line_id,
626 qlinstg.line_category_code,
627 qlinstg.marketing_source_code_id_hdr,
628 null publish_flag,
629 :l_sysdate,
630 :l_user_id,
631 :l_sysdate,
632 :l_user_id,
633 :l_login_id,
634 qlinstg.order_creation_date,
635 Inventory_item_id Top_Inventory_Item_Id,
636 Organization_id Top_Organization_Id,
637 qlinstg.Charge_periodicity_code
638 FROM ASO_BI_QUOTE_LINES_STG qlinstg
639 WHERE Config_Header_Id IS NULL'
640 USING l_sysdate,
641 l_user_id,
642 l_sysdate,
643 l_user_id,
644 l_login_id;
645
646 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
647 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote Line fact'||
648 ':Rowcount:'||SQL%ROWCOUNT);
649 END IF;
650
651 COMMIT;
652
653 End InitiLoad_QotLine ;
654
655
656 -- Inserts Records into ASO_BI_QUOTE_LINES_ALL reading from
657 -- ASO_BI_QUOTE_LINES_STG table.
658 PROCEDURE Populate_Line_data AS
659 l_user_id number;
660 l_login_id number;
661 l_sysdate date;
662 BEGIN
663
664 l_user_id := FND_GLOBAL.user_id;
665 l_login_id := FND_GLOBAL.login_id;
666 l_sysdate := sysdate;
667
668 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
669 BIS_COLLECTION_UTILITIES.Debug('Start populating the Quote Line fact');
670 END IF;
671
672 EXECUTE IMMEDIATE
673 'INSERT/*+ append */ INTO ASO_BI_QUOTE_LINES_ALL
674 ( quote_header_id,
675 quote_number,
676 quote_version,
677 quote_creation_date,
678 quote_last_update_date,
679 quote_expiration_date,
680 resource_id,
681 resource_grp_id,
682 quote_source_code,
683 cust_account_id,
684 invoice_to_cust_account_id_hdr,
685 minisite_id_hdr,
686 cust_party_id,
687 quote_status_id,
688 sales_channel_code,
689 org_id,
690 order_id,
694 reporting_conversion_rate,
691 currency_code,
692 reporting_currency,
693 functional_currency,
695 functional_conversion_rate,
696 sec_conversion_rate,
697 shipment_id,
698 line_value,
699 line_quote_value,
700 quantity,
701 uom_code,
702 minisite_id,
703 marketing_source_code_id,
704 inventory_item_id,
705 organization_id,
706 invoice_to_cust_account_id,
707 agreement_id,
708 quote_line_id,
709 line_category_code,
710 marketing_source_code_id_hdr,
711 publish_flag,
712 creation_date,
713 created_by,
714 last_update_date,
715 last_updated_by,
716 last_update_login,
717 order_creation_date,
718 Top_Inventory_Item_Id,
719 Top_Organization_Id,
720 Charge_periodicity_code
721 )
722 SELECT
723 qlinstg.quote_header_id,
724 qlinstg.quote_number,
725 qlinstg.quote_version,
726 qlinstg.quote_creation_date,
727 qlinstg.quote_last_update_date,
728 qlinstg.quote_expiration_date,
729 qlinstg.resource_id,
730 qlinstg.resource_grp_id,
731 qlinstg.quote_source_code,
732 qlinstg.cust_account_id,
733 qlinstg.invoice_to_cust_account_id_hdr,
734 qlinstg.minisite_id_hdr,
735 qlinstg.cust_party_id,
736 qlinstg.quote_status_id,
737 qlinstg.sales_channel_code,
738 qlinstg.org_id,
739 qlinstg.order_id,
740 qlinstg.currency_code,
741 qlinstg.reporting_currency,
742 qlinstg.functional_currency,
743 qlinstg.reporting_conversion_rate,
744 qlinstg.functional_conversion_rate,
745 qlinstg.sec_conversion_rate,
746 qlinstg.shipment_id,
747 qlinstg.line_value,
748 qlinstg.line_quote_value,
749 qlinstg.quantity,
750 qlinstg.uom_code,
751 qlinstg.minisite_id,
752 qlinstg.marketing_source_code_id,
753 qlinstg.inventory_item_id,
754 qlinstg.organization_id,
755 qlinstg.invoice_to_cust_account_id,
756 qlinstg.agreement_id,
757 qlinstg.quote_line_id,
758 qlinstg.line_category_code,
759 qlinstg.marketing_source_code_id_hdr,
760 null publish_flag,
761 :l_sysdate,
762 :l_user_id,
763 :l_sysdate,
764 :l_user_id,
765 :l_login_id,
766 qlinstg.order_creation_date,
767 FIRST_VALUE(inventory_item_id)OVER(
768 PARTITION BY config_header_id
769 ORDER BY config_item_id ASC
770 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
771 Top_Inventory_Item_Id,
772 FIRST_VALUE(organization_id)OVER(
773 PARTITION BY config_header_id
774 ORDER BY config_item_id ASC
775 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
776 Top_Organization_Id,
777 qlinstg.Charge_periodicity_code
778 FROM
779 ASO_BI_QUOTE_LINES_STG qlinstg
780 WHERE Config_Header_Id IS NOT NULL'
781 USING l_sysdate,
782 l_user_id,
783 l_sysdate,
784 l_user_id,
785 l_login_id;
786
787 COMMIT;
788
789 EXECUTE IMMEDIATE
790 'INSERT/*+ append */ INTO ASO_BI_QUOTE_LINES_ALL
791 ( quote_header_id,
792 quote_number,
793 quote_version,
794 quote_creation_date,
795 quote_last_update_date,
796 quote_expiration_date,
797 resource_id,
798 resource_grp_id,
799 quote_source_code,
800 cust_account_id,
801 invoice_to_cust_account_id_hdr,
802 minisite_id_hdr,
803 cust_party_id,
804 quote_status_id,
805 sales_channel_code,
806 org_id,
807 order_id,
808 currency_code,
809 reporting_currency,
810 functional_currency,
811 reporting_conversion_rate,
812 functional_conversion_rate,
813 sec_conversion_rate,
814 shipment_id,
815 line_value,
816 line_quote_value,
817 quantity,
818 uom_code,
819 minisite_id,
820 marketing_source_code_id,
821 inventory_item_id,
822 organization_id,
823 invoice_to_cust_account_id,
824 agreement_id,
825 quote_line_id,
826 line_category_code,
827 marketing_source_code_id_hdr,
828 publish_flag,
829 creation_date,
830 created_by,
831 last_update_date,
832 last_updated_by,
833 last_update_login,
834 order_creation_date,
835 Top_Inventory_Item_Id,
836 Top_Organization_Id,
837 Charge_periodicity_code
838 )
839 SELECT
840 qlinstg.quote_header_id,
844 qlinstg.quote_last_update_date,
841 qlinstg.quote_number,
842 qlinstg.quote_version,
843 qlinstg.quote_creation_date,
845 qlinstg.quote_expiration_date,
846 qlinstg.resource_id,
847 qlinstg.resource_grp_id,
848 qlinstg.quote_source_code,
849 qlinstg.cust_account_id,
850 qlinstg.invoice_to_cust_account_id_hdr,
851 qlinstg.minisite_id_hdr,
852 qlinstg.cust_party_id,
853 qlinstg.quote_status_id,
854 qlinstg.sales_channel_code,
855 qlinstg.org_id,
856 qlinstg.order_id,
857 qlinstg.currency_code,
858 qlinstg.reporting_currency,
859 qlinstg.functional_currency,
860 qlinstg.reporting_conversion_rate,
861 qlinstg.functional_conversion_rate,
862 qlinstg.sec_conversion_rate,
863 qlinstg.shipment_id,
864 qlinstg.line_value,
865 qlinstg.line_quote_value,
866 qlinstg.quantity,
867 qlinstg.uom_code,
868 qlinstg.minisite_id,
869 qlinstg.marketing_source_code_id,
870 qlinstg.inventory_item_id,
871 qlinstg.organization_id,
872 qlinstg.invoice_to_cust_account_id,
873 qlinstg.agreement_id,
874 qlinstg.quote_line_id,
875 qlinstg.line_category_code,
876 qlinstg.marketing_source_code_id_hdr,
877 null publish_flag,
878 :l_sysdate,
879 :l_user_id,
880 :l_sysdate,
881 :l_user_id,
882 :l_login_id,
883 qlinstg.order_creation_date,
884 Inventory_item_id Top_Inventory_Item_Id,
885 Organization_id Top_Organization_Id,
886 qlinstg.Charge_periodicity_code
887 FROM
888 ASO_BI_QUOTE_LINES_STG qlinstg
889 WHERE Config_Header_Id IS NULL'
890 USING l_sysdate,
891 l_user_id,
892 l_sysdate,
893 l_user_id,
894 l_login_id;
895
896 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
897 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote line fact '||
898 'Staging:Rowcount: '||SQL%ROWCOUNT);
899 END IF;
900
901 COMMIT;
902
903 END Populate_Line_data;
904
905
906
907 -- This procedure is called as a part of incremental load of quote lines.
908 -- Populates ASO_BI_QUOTE_LINES_STG table
909 PROCEDURE Line_Worker(
910 Errbuf IN OUT NOCOPY VARCHAR2,
911 Retcode IN OUT NOCOPY NUMBER,
912 p_worker_no IN NUMBER)
913 AS
914 l_unassigned_cnt NUMBER := 0;
915 l_failed_cnt NUMBER := 0;
916 l_comp_cnt NUMBER := 0;
917 l_total_cnt NUMBER := 0;
918 l_count NUMBER := 0;
919 l_batch_id NUMBER := 0;
920 BEGIN
921 errbuf := NULL;
922 retcode := 0;
923
924 IF(BIS_COLLECTION_UTILITIES.Setup(
925 p_object_name => 'ASO_BI_QOT_LINE_SUBWORKER'||p_worker_no)) = false
926 THEN
927 errbuf := FND_MESSAGE.Get;
928 RAISE_APPLICATION_ERROR(-20000,errbuf);
929 END IF;
930
931 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
932 BIS_COLLECTION_UTILITIES.Debug('ASO_BI_QOT_LINE_SUBWORKER'||p_worker_no||
933 ' starting.');
934 END IF;
935
936 -- This call is to populate functional currency again
937 -- as each of these subworkers are started in a new session
938 -- and we are using a global session temporary table
939 -- for storing functional currency
940 -- ASO_BI_LINE_FACT_PVT.Get_Functional_Currency;
941
942 LOOP
943 SELECT NVL(SUM(DECODE(status,'UNASSIGNED',1,0)),0),
944 NVL(SUM(DECODE(status,'FAILED',1,0)),0),
945 NVL(SUM(DECODE(status,'COMPLETED',1,0)),0),
946 COUNT(*)
947 INTO l_unassigned_cnt,
948 l_failed_cnt,
949 l_comp_cnt,
950 l_total_cnt
951 FROM ASO_BI_QUOTE_FACT_JOBS;
952
953 IF(l_failed_cnt > 0) Then
954 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
955 BIS_COLLECTION_UTILITIES.Debug('Another worker have errored out.'||
956 ' Stop Processing.');
957 END IF;
958 Exit;
959 Elsif (l_unassigned_cnt = 0) Then
960 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
961 BIS_COLLECTION_UTILITIES.Debug('No More jobs left.Terminating.');
962 END IF;
963 EXIT;
964 Elsif( l_comp_cnt = l_total_cnt) Then
965 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
966 BIS_COLLECTION_UTILITIES.Debug('All Jobs completed.Terminating.');
967 END IF;
968 EXIT;
969 Elsif(l_unassigned_cnt > 0) Then
970 UPDATE ASO_BI_QUOTE_FACT_JOBS
971 SET status = 'IN_PROCESS',
972 worker_number = p_worker_no
973 WHERE status = 'UNASSIGNED'
974 AND rownum < 2;
975 l_count := SQL%ROWCOUNT;
976 END IF;
977 COMMIT;
978
979 If (l_count > 0) Then
980 BEGIN
981 SELECT batch_id
982 INTO l_batch_id
983 FROM ASO_BI_QUOTE_FACT_JOBS
987 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
984 WHERE worker_number = p_worker_no
985 AND status = 'IN_PROCESS';
986
988 BIS_COLLECTION_UTILITIES.Debug('Start populate line Staging:'||
989 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
990 END IF;
991
992 POPULATE_QOT_LINE_FACT_STG(p_batch_id => l_batch_id);
993
994 IF (BIS_COLLECTION_UTILITIES.g_debug) THEN
995 BIS_COLLECTION_UTILITIES.Debug('End populate line Staging:'||
996 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
997 END IF;
998
1002 AND worker_number = p_worker_no;
999 UPDATE ASO_BI_QUOTE_FACT_JOBS
1000 SET status = 'COMPLETED'
1001 WHERE status = 'IN_PROCESS'
1003
1004 COMMIT;
1005 EXCEPTION
1006 WHEN OTHERS Then
1007 retcode := -1;
1008 UPDATE ASO_BI_QUOTE_FACT_JOBS
1009 SET status = 'FAILED'
1010 WHERE worker_number = p_worker_no
1011 AND status = 'IN_PROCESS';
1012 COMMIT;
1013 RAISE;
1014 END;
1015 END IF; --if l_count > 0
1016 END LOOP;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 retcode := -1;
1020 errbuf := FND_MESSAGE.Get;
1021
1022 UPDATE ASO_BI_QUOTE_FACT_JOBS
1023 SET status = 'FAILED'
1024 WHERE worker_number = p_worker_no
1025 AND status = 'IN_PROCESS';
1026 COMMIT;
1027 END Line_Worker;
1028
1029 END ASO_BI_LINE_FACT_PVT;