[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_QUOTE_FACT_PVT
Source
1 Package Body ASO_BI_QUOTE_FACT_PVT AS
2 /* $Header: asovbiqhdb.pls 120.1 2005/07/06 00:44:56 kedukull noship $ */
3 Procedure InitLoad_Quote_Ids (p_from_date IN Date,
4 p_to_date IN Date)
5 As
6 Begin
7 INSERT/*+ APPEND PARALLEL(ASO_QOT_IDS) */ INTO ASO_BI_QUOTE_IDS ASO_QOT_IDS
8 (Quote_header_id,
9 Quote_number,
10 Max_quote_version,
11 Quote_creation_date,
12 quote_amount_first,
13 batch_id)
14 SELECT /*+ PARALLEL(QV)*/
15 QV.quote_header_id,
16 QV.quote_number,
17 QV.max_quote_version,
18 QV.quote_creation_date,
19 (NVL(qv.total_list_price_first,0)+NVL(qv.total_adjusted_amount_first,0)) quote_amount_first,
20 NULL
21 FROM
22 (SELECT /*+ full(QHD) PARALLEL(QHD)*/ QHD.quote_version,
23 QHD.quote_number,
24 QHD.quote_header_id quote_header_id,
25 LAST_VALUE(QHD.quote_version) OVER(
26 PARTITION BY QHD.quote_number
27 ORDER BY Quote_version ASC
28 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
29 AS MAX_QUOTE_VERSION,
30 first_value(trunc(qhd.creation_date))
31 over(partition by qhd.quote_number
32 order by quote_version asc rows unbounded preceding)
33 as quote_creation_date,
34 first_value(qhd.TOTAL_list_price)
35 over(partition by qhd.quote_number
36 order by quote_version asc rows unbounded preceding)
37 as total_list_price_first,
38 first_value(qhd.total_adjusted_amount)
39 over(partition by qhd.quote_number
40 order by quote_version asc rows unbounded preceding)
41 as total_adjusted_amount_first
42 from aso_quote_headers_all qhd
43 where qhd.last_update_date
44 between p_from_date and p_to_date
45 and nvl(qhd.quote_type,'q')<>'t') qv
46 where qv.quote_version = qv.max_quote_version ;
47
48 if (bis_collection_utilities.g_debug) then
49 bis_collection_utilities.debug('inserted '||sql%rowcount||' rows in ' ||'aso_bi_quote_ids.');
50 end if;
51
52 commit;
53
54 aso_bi_util_pvt.analyze_table('aso_bi_quote_ids');
55
56 end initload_quote_ids ;
57
58 procedure populate_quote_ids(p_from_date date,
59 p_to_date date)
60 as
61 l_batch_size number := 1000;
62 begin
63
64 /* for transactions with average complexity */
65 l_batch_size:= bis_common_parameters.get_batch_size(bis_common_parameters.medium);
66
67 -- NVL(QHD.TOTAL_list_price,0)+NVL(QHD.total_adjusted_amount,0)
68
69 insert/*+ append */ into aso_bi_quote_ids
70 (quote_header_id,
71 quote_number,
72 max_quote_version,
73 quote_creation_date,
74 quote_amount_first,
75 batch_id)
76 select qv.quote_header_id,
77 qv.quote_number,
78 qv.max_quote_version,
79 qv.quote_creation_date,
80 (NVL(qv.total_list_price_first,0)+NVL(qv.total_adjusted_amount_first,0)) quote_amount_first,
81 ceil(rownum/l_batch_size)
82 from
83 (select qhd.quote_version,
84 qhd.quote_number,
85 qhd.quote_header_id quote_header_id,
86 last_value(qhd.quote_version) over(
87 partition by qhd.quote_number
88 order by quote_version asc
89 rows between unbounded preceding and unbounded following)
90 as max_quote_version,
91 first_value(trunc(qhd.creation_date))
92 over(partition by qhd.quote_number
93 order by quote_version asc rows unbounded preceding)
94 as quote_creation_date,
95 first_value(qhd.TOTAL_list_price)
96 over(partition by qhd.quote_number
97 order by quote_version asc rows unbounded preceding)
98 as total_list_price_first,
99 first_value(qhd.total_adjusted_amount)
100 over(partition by qhd.quote_number
101 order by quote_version asc rows unbounded preceding)
102 as total_adjusted_amount_first
103 from aso_quote_headers_all qhd
104 WHERE QHD.last_update_date
105 BETWEEN p_from_date AND p_to_date
106 AND NVL(QHD.QUOTE_TYPE,'Q')<>'T') QV
107 WHERE QV.quote_version = QV.max_quote_version ;
108 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
109 BIS_COLLECTION_UTILITIES.Debug('Inserted '||SQL%ROWCOUNT||' rows in ' ||'ASO_BI_QUOTE_IDS.');
110 END IF;
111 COMMIT;
112 End Populate_Quote_ids;
113
114 Procedure Register_jobs
115 As
116 Begin
117 Insert Into ASO_BI_QUOTE_FACT_JOBS(batch_id,worker_number,status)
118 SELECT DISTINCT batch_id,0,'UNASSIGNED'
119 FROM ASO_BI_QUOTE_IDS ;
120 COMMIT;
121
122 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
123 BIS_COLLECTION_UTILITIES.Debug('Inserted '||SQL%ROWCOUNT||' jobs into '||'ASO_BI_QUOTE_FACT_JOBS');
124 END IF;
125
126 End Register_jobs;
127
128 -- This procedure is called as a part of incremental load of quote headers.
129 Procedure Populate_Qot_Fact_Stg(p_batch_id IN NUMBER)
130 As
131 l_global_prim_currency Varchar2(10);
132 l_global_sec_currency Varchar2(10);
133 Begin
134
135 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
136 BIS_COLLECTION_UTILITIES.Debug('populating the Quote fact Staging');
137 END IF;
138
139 l_global_prim_currency := BIS_COMMON_PARAMETERS.Get_Currency_code ;
140 l_global_sec_currency := BIS_COMMON_PARAMETERS.Get_Secondary_Currency_code ;
141
142 COMMIT;
143
144 MERGE INTO ASO_BI_QUOTE_HDRS_STG STG
145 USING ( SELECT * FROM
146 (SELECT QHD.quote_number quote_number,
147 QHD.quote_version,
148 QHD.quote_header_id,
149 QHD.quote_name,
150 QID.quote_creation_date,
151 QHD.last_update_date quote_last_update_date,
152 NVL(QHD.TOTAL_list_price,0)+NVL(QHD.total_adjusted_amount,0) Quote_value,
153 QID.quote_amount_first,
154 QHD.total_adjusted_amount,
155 TRUNC(QHD.Quote_expiration_date) + 1 quote_expiration_date,
156 ORD.Header_id order_id,
157 QHD.currency_code,
158 RATE.prim_conversion_rate,
159 RATE.sec_conversion_rate,
160 QHD.resource_grp_id,
161 QHD.resource_id,
162 TRUNC(ORD.creation_date) order_creation_date,
163 QROBJ.object_id lead_id,
164 QHD.cust_party_id party_id,
165 QHD.marketing_source_code_id,
166 NVL(QHD.Total_adjusted_percent,0) Total_adjusted_percent,
167 (SELECT CASE WHEN count(LIN.charge_periodicity_code) = (CASE WHEN count(LIN.quote_line_id) = 0
168 THEN -1 ELSE
169 count(LIN.quote_line_id) END)
170 THEN 'Y' ELSE 'N' END FROM ASO_QUOTE_LINES_ALL LIN
171 WHERE LIN.quote_header_id = QHD.quote_header_id) recurring_charge_flag
172 FROM ASO_QUOTE_HEADERS_ALL QHD,
173 OE_ORDER_HEADERS_ALL ORD,
174 ASO_BI_QUOTE_IDS QID,
175 ASO_QUOTE_RELATED_OBJECTS QROBJ,
176 ASO_BI_CURRENCY_RATES RATE
177 WHERE QHD.order_id = ord.header_id(+)
178 AND QHD.quote_header_id = QID.quote_header_id
179 AND QID.batch_id = p_batch_id
180 AND QHD.quote_header_id = QROBJ.quote_object_id(+)
181 AND QROBJ.quote_object_type_code(+) = 'HEADER'
182 AND QROBJ.object_type_code(+) = 'LDID'
183 AND QROBJ.relationship_type_code(+) = 'OPP_QUOTE'
184 AND QHD.resource_grp_id IS NOT NULL
185 AND RATE.org_id(+) = QHD.org_id
186 AND RATE.exchange_date(+) = trunc(QHD.last_update_date)
187 AND RATE.txn_currency(+) = QHD.currency_code
188 ) )S
189 ON ( STG.quote_number = S.quote_number)
190 WHEN MATCHED THEN
191 UPDATE SET STG.quote_version = S.quote_version,
192 STG.quote_header_id = S.quote_header_id,
193 STG.quote_value = S.quote_value ,
194 STG.quote_name = S.quote_name,
195 STG.total_adjusted_amount = S.total_adjusted_amount,
196 STG.quote_amount_first = S.quote_amount_first,
197 STG.conversion_rate = S.prim_conversion_rate,
198 STG.sec_conversion_rate = S.sec_conversion_rate,
199 STG.quote_expiration_date = S.quote_expiration_date,
200 STG.order_id = S.order_id,
201 STG.currency_code = S.currency_code,
202 STG.resource_grp_id = S.resource_grp_id,
203 STG.resource_id = S.resource_id,
204 STG.order_creation_date = S.order_creation_date,
205 STG.lead_id = S.lead_id,
206 STG.party_id = S.party_id ,
207 STG.marketing_source_code_id = S.marketing_source_code_id,
208 STG.Total_adjusted_percent = S.Total_adjusted_percent,
209 STG.recurring_charge_flag = S.recurring_charge_flag
210 WHEN NOT MATCHED THEN
211 INSERT(quote_number,
212 quote_version,
213 quote_header_id,
214 quote_name,
215 quote_creation_date,
216 quote_last_update_date,
217 resource_id,
218 resource_grp_id,
219 quote_expiration_date,
220 currency_code,
221 reporting_currency,
222 conversion_rate,
223 sec_conversion_rate,
224 quote_value,
225 quote_amount_first,
226 total_adjusted_amount,
227 order_id,
228 order_creation_date,
229 lead_id,
230 party_id,
231 marketing_source_code_id,
232 Total_adjusted_percent,
233 recurring_charge_flag)
234 VALUES( S.quote_number,
235 S.quote_version,
236 S.quote_header_id,
237 S.quote_name,
238 S.quote_creation_date,
239 S.quote_last_update_date,
240 S.resource_id ,
241 S.resource_grp_id,
242 S.quote_expiration_date,
243 S.currency_code,
244 l_global_prim_currency ,
245 S.prim_conversion_rate,
246 S.sec_conversion_rate,
247 S.Quote_value,
248 S.Quote_amount_first,
249 S.total_adjusted_amount,
250 S.order_id,
251 S.order_creation_date,
252 S.lead_id,
253 S.party_id,
254 S.marketing_source_code_id,
255 S.Total_adjusted_percent,
256 S.recurring_charge_flag) ;
257
258 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
259 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote fact Staging:'||'Rowcount:'|| SQL%ROWCOUNT);
260 END IF;
261
262 COMMIT;
263
264 ASO_BI_UTIL_PVT.Analyze_Table('ASO_BI_QUOTE_HDRS_STG');
265
266 End Populate_Qot_Fact_Stg;
267
268 -- Initial Load of ASO_BI_QUOTE_HDRS_ALL
269 Procedure InitiLoad_QotHdr
270 As
271 l_user_id number;
272 l_login_id number;
273 l_global_prim_currency VARCHAR2(20);
274 Begin
275
276 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
277 BIS_COLLECTION_UTILITIES.Debug('Start populating the Quote fact Table');
278 END IF;
279
280 l_user_id := FND_GLOBAL.user_id;
281 l_login_id := FND_GLOBAL.login_id;
282 l_global_prim_currency := BIS_COMMON_PARAMETERS.Get_Currency_code ;
283
284 INSERT/*+ APPEND PARALLEL(QOTHDR)*/ INTO ASO_BI_QUOTE_HDRS_ALL QOTHDR
285 (quote_number,
286 quote_version,
287 quote_header_id,
288 quote_name,
289 quote_creation_date,
290 quote_last_update_date,
291 resource_id,
292 resource_grp_id,
293 quote_expiration_date,
294 currency_code,
295 reporting_currency,
296 conversion_rate,
297 sec_conversion_rate,
298 quote_value,
299 quote_amount_first,
300 total_adjusted_amount,
301 order_id,
302 order_creation_date,
303 lead_id,
304 creation_date,
305 created_by,
306 last_update_date,
307 last_updated_by,
308 LAST_UPDATE_LOGIN,
309 party_id,
310 marketing_source_code_id,
311 Total_adjusted_percent,
312 recurring_charge_flag
313 )
314 SELECT
315 /*+ USE_HASH(ORD) PARALLEL(QHD) PARALLEL(QROBJ) PARALLEL(ORD) PARALLEL(QID) PARALLEL(RATE) */
316 QHD.quote_number quote_number,
317 QHD.quote_version,
318 QHD.quote_header_id,
319 QHD.quote_name,
320 QID.quote_creation_date,
321 QHD.last_update_date quote_last_update_date,
322 QHD.resource_id,
323 QHD.resource_grp_id,
324 TRUNC(QHD.Quote_expiration_date) + 1 quote_expiration_date,
325 QHD.currency_code,
326 l_global_prim_currency ,
327 RATE.prim_conversion_rate,
328 RATE.sec_conversion_rate,
329 NVL(QHD.TOTAL_list_price,0)+NVL(QHD.total_adjusted_amount,0) Quote_value,
330 QID.quote_amount_first,
331 QHD.total_adjusted_amount,
332 ORD.Header_id order_id,
333 TRUNC(ORD.creation_date) order_creation_date,
334 QROBJ.object_id lead_id,
335 SYSDATE,
336 l_user_id,
337 SYSDATE,
338 l_user_id,
339 l_login_id,
340 QHD.cust_party_id party_id,
341 QHD.marketing_source_code_id,
345 count(LIN.quote_line_id) END)
342 NVL(QHD.Total_adjusted_percent, 0),
343 (SELECT CASE WHEN count(LIN.charge_periodicity_code) = (CASE WHEN count(LIN.quote_line_id) = 0
344 THEN -1 ELSE
346 THEN 'Y' ELSE 'N' END FROM ASO_QUOTE_LINES_ALL LIN
347 WHERE LIN.quote_header_id = QHD.quote_header_id) recurring_charge_flag
348 FROM
349 ASO_QUOTE_HEADERS_ALL QHD,
350 OE_ORDER_HEADERS_ALL ORD,
351 ASO_BI_QUOTE_IDS QID,
352 ASO_QUOTE_RELATED_OBJECTS QROBJ,
353 ASO_BI_CURRENCY_RATES RATE
354 WHERE
355 QHD.order_id = ord.header_id(+) AND
356 QHD.quote_header_id = QID.quote_header_id AND
357 QHD.quote_header_id = QROBJ.quote_object_id(+) AND
358 QROBJ.quote_object_type_code(+) = 'HEADER' AND
359 QROBJ.object_type_code(+) = 'LDID' AND
360 QROBJ.relationship_type_code(+) = 'OPP_QUOTE' AND
361 QHD.resource_grp_id IS NOT NULL AND
362 RATE.org_id(+) = QHD.org_id AND
363 RATE.txn_currency(+) = QHD.currency_code AND
364 RATE.exchange_date(+) = trunc(QHD.last_update_date);
365
366 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
367 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
368 END IF;
369 COMMIT;
370 End InitiLoad_QotHdr ;
371
372 -- This procedure is called as a part of incremental load of quote headers fact table.
373 Procedure Populate_data
374 As
375 l_user_id NUMBER;
376 l_login_id NUMBER;
377 Begin
378
379 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
380 BIS_COLLECTION_UTILITIES.Debug('Start populating the Quote fact Table');
381 END IF;
382
383 l_user_id := FND_GLOBAL.user_id;
384 l_login_id := FND_GLOBAL.login_id;
385
386 EXECUTE IMMEDIATE
387 'MERGE INTO ASO_BI_QUOTE_HDRS_ALL QOTHDR
388 USING(SELECT quote_number,
389 quote_version,
390 quote_header_id,
391 quote_name,
392 quote_creation_date,
393 quote_last_update_date,
394 resource_id,
395 resource_grp_id,
396 quote_expiration_date,
397 currency_code,
398 reporting_currency,
399 conversion_rate,
400 sec_conversion_rate,
401 quote_value,
402 quote_amount_first,
403 total_adjusted_amount,
404 order_id,
405 order_creation_date,
406 lead_id,
407 party_id,
408 marketing_source_code_id,
409 total_adjusted_percent,
410 recurring_charge_flag
411 FROM ASO_BI_QUOTE_HDRS_STG
412 WHERE (conversion_rate > 0)) STG
413 ON (QOTHDR.quote_number = STG.quote_number)
414 WHEN MATCHED THEN UPDATE
415 SET QOTHDR.quote_version = STG.quote_version,
416 QOTHDR.quote_header_id = STG.quote_header_id,
417 QOTHDR.quote_name = STG.quote_name,
418 QOTHDR.quote_value = STG.quote_value ,
419 QOTHDR.quote_amount_first = STG.quote_amount_first ,
420 QOTHDR.total_adjusted_amount = STG.total_adjusted_amount,
421 QOTHDR.conversion_rate = STG.conversion_rate,
422 QOTHDR.sec_conversion_rate = STG.sec_conversion_rate,
423 QOTHDR.quote_expiration_date = STG.quote_expiration_date,
424 QOTHDR.order_id = STG.order_id,
425 QOTHDR.currency_code = STG.currency_code,
426 QOTHDR.resource_grp_id = STG.resource_grp_id,
427 QOTHDR.resource_id = STG.resource_id,
428 QOTHDR.order_creation_date = STG.order_creation_date,
429 QOTHDR.lead_id = STG.lead_id,
430 QOTHDR.last_update_date = :sys_date,
431 QOTHDR.last_updated_by = :l_user_id,
432 QOTHDR.LAST_UPDATE_LOGIN = :l_login_id,
433 QOTHDR.party_id = STG.party_id,
434 QOTHDR.marketing_source_code_id = STG.marketing_source_code_id,
435 QOTHDR.total_adjusted_percent = STG.total_adjusted_percent,
436 QOTHDR.recurring_charge_flag = STG.recurring_charge_flag
437 WHEN NOT MATCHED THEN
438 INSERT(quote_number,
439 quote_version,
440 quote_header_id,
441 quote_name,
442 quote_creation_date,
443 quote_last_update_date,
444 resource_id,
445 resource_grp_id,
446 quote_expiration_date,
447 currency_code,
448 reporting_currency,
449 conversion_rate,
450 sec_conversion_rate,
451 quote_value,
452 quote_amount_first,
453 total_adjusted_amount,
454 order_id,
455 order_creation_date,
456 lead_id,
457 creation_date,
458 created_by,
459 last_update_date,
460 last_updated_by,
461 LAST_UPDATE_LOGIN,
462 party_id,
463 marketing_source_code_id,
464 Total_adjusted_percent,
465 recurring_charge_flag )
466 VALUES( STG.quote_number,
467 STG.quote_version,
468 STG.quote_header_id,
469 STG.quote_name,
470 STG.quote_creation_date,
471 STG.quote_last_update_date,
472 STG.resource_id ,
473 STG.resource_grp_id,
474 STG.quote_expiration_date,
475 STG.currency_code,
476 STG.reporting_currency ,
477 STG.conversion_rate,
478 STG.sec_conversion_rate,
482 STG.order_id,
479 STG.Quote_value,
480 STG.quote_amount_first,
481 STG.total_adjusted_amount,
483 STG.order_creation_date,
484 STG.lead_id,
485 :sys_date,
486 :l_user_id,
487 :sys_date,
488 :l_user_id,
489 :l_login_id,
490 STG.party_id,
491 STG.marketing_source_code_id,
492 STG.total_adjusted_percent,
493 STG.recurring_charge_flag)' USING SYSDATE, l_user_id,l_login_id,SYSDATE,l_user_id,SYSDATE,l_user_id,l_login_id;
494
495 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
496 BIS_COLLECTION_UTILITIES.Debug('Done populating the Quote fact Table:'||'Rowcount:'|| SQL%ROWCOUNT);
497 END IF;
498
499 COMMIT;
500
501 End Populate_data;
502
503 Procedure Worker(
504 Errbuf IN OUT NOCOPY VARCHAR2,
505 Retcode IN OUT NOCOPY NUMBER,
506 p_worker_no IN NUMBER)
507 As
508 l_unassigned_cnt NUMBER := 0;
509 l_failed_cnt NUMBER := 0;
510 l_comp_cnt NUMBER := 0;
511 l_total_cnt NUMBER := 0;
512 l_count NUMBER := 0;
513 l_batch_id NUMBER := 0;
514 Begin
515 errbuf := NULL;
516 retcode := 0;
517
518 IF(BIS_COLLECTION_UTILITIES.Setup(
519 p_object_name => 'ASO_BI_QOT_HDR_SUBWORKER'||p_worker_no)) = false
520 Then
521 errbuf := FND_MESSAGE.Get;
522 RAISE_APPLICATION_ERROR(-20000,errbuf);
523 End if;
524
525 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
526 BIS_COLLECTION_UTILITIES.Debug('ASO_BI_QOT_HDR_SUBWORKER'||p_worker_no||' starting.');
527 END IF;
528
529 LOOP
530 SELECT NVL(SUM(DECODE(status,'UNASSIGNED',1,0)),0),
531 NVL(SUM(DECODE(status,'FAILED',1,0)),0),
532 NVL(SUM(DECODE(status,'COMPLETED',1,0)),0),
533 COUNT(*)
534 INTO l_unassigned_cnt,
535 l_failed_cnt,
536 l_comp_cnt,
537 l_total_cnt
538 FROM ASO_BI_QUOTE_FACT_JOBS;
539
540 IF(l_failed_cnt > 0) Then
541 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
542 BIS_COLLECTION_UTILITIES.Debug('Another worker have errored out.Stop Processing.');
543 END IF;
544 Exit;
545 Elsif (l_unassigned_cnt = 0) Then
546 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
547 BIS_COLLECTION_UTILITIES.Debug('No More jobs left.Terminating.');
548 END IF;
549 EXIT;
550 Elsif( l_comp_cnt = l_total_cnt) Then
551 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
552 BIS_COLLECTION_UTILITIES.Debug('All Jobs completed.Terminating.');
553 END IF;
554 EXIT;
555 Elsif(l_unassigned_cnt > 0) Then
556 UPDATE ASO_BI_QUOTE_FACT_JOBS
557 SET status = 'IN_PROCESS',
558 worker_number = p_worker_no
559 WHERE status = 'UNASSIGNED'
560 AND rownum < 2;
561 l_count := SQL%ROWCOUNT;
562 End If;
563 COMMIT;
564
565 If (l_count > 0) Then
566 BEGIN
567 SELECT batch_id
568 INTO l_batch_id
569 FROM ASO_BI_QUOTE_FACT_JOBS
570 WHERE worker_number = p_worker_no
571 AND status = 'IN_PROCESS';
572
573 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
574 BIS_COLLECTION_UTILITIES.Debug('Start populate Staging:'||TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
575 END IF;
576
577 POPULATE_QOT_FACT_STG(p_batch_id => l_batch_id);
578
579 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
580 BIS_COLLECTION_UTILITIES.Debug('End populate Staging:'||TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
581 END IF;
582
583 UPDATE ASO_BI_QUOTE_FACT_JOBS
584 SET status = 'COMPLETED'
585 WHERE status = 'IN_PROCESS'
586 AND worker_number = p_worker_no;
587
588 COMMIT;
589 EXCEPTION
590 WHEN OTHERS Then
591 retcode := -1;
592 UPDATE ASO_BI_QUOTE_FACT_JOBS
593 SET status = 'FAILED'
594 WHERE worker_number = p_worker_no
595 AND status = 'IN_PROCESS';
596 COMMIT;
597 RAISE;
598 END;
599 End If; --if l_count > 0
600 END LOOP;
601 EXCEPTION
602 WHEN OTHERS THEN
603 retcode := -1;
604 errbuf := FND_MESSAGE.Get;
605
606 UPDATE ASO_BI_QUOTE_FACT_JOBS
607 SET status = 'FAILED'
608 WHERE worker_number = p_worker_no
609 AND status = 'IN_PROCESS';
610 COMMIT;
611 End Worker;
612
613 END ASO_BI_QUOTE_FACT_PVT ;