[Home] [Help]
PACKAGE BODY: APPS.PA_BILL_REV_XLA_UPGRADE
Source
1 PACKAGE BODY PA_BILL_REV_XLA_UPGRADE AS
2 /* $Header: PAXBRU1B.pls 120.37 2011/08/24 07:38:50 vdharman ship $ */
3
4 PROCEDURE GL_IMP_UPG_AD_PAR( p_table_owner IN VARCHAR2,
5 p_table_name IN VARCHAR2,
6 p_script_name IN VARCHAR2,
7 p_num_workers IN NUMBER,
8 p_worker_id IN NUMBER,
9 p_batch_size IN NUMBER,
10 p_min_header_id IN NUMBER,
11 p_max_header_id IN NUMBER,
12 p_batch_id IN NUMBER)
13 IS
14
15 l_start_jeid NUMBER(15);
16 l_end_jeid NUMBER(15);
17
18 l_any_rows_to_process BOOLEAN;
19 l_rows_processed NUMBER;
20 l_sql_stmt VARCHAR2(2000);
21 BEGIN
22
23 l_sql_stmt := 'select je_header_id id_value ' ||
24 ' FROM gl_je_headers hd, ' ||
25 ' PA_PRIM_REP_LEGER_tmp per ' ||
26 ' where hd.LEDGER_ID = per.denorm_ledger_id ' ||
27 ' and hd.PERIOD_NAME = per.PERIOD_NAME ' ||
28 ' and hd.je_source = ''Project Accounting'' ' ||
29 ' and per.batch_id = ' || p_batch_id ;
30
31 ad_parallel_updates_pkg.initialize_id_range(
32 ad_parallel_updates_pkg.ID_RANGE_SCAN_EQUI_ROWSETS,
33 p_table_owner,
34 p_table_name,
35 p_script_name,
36 'JE_HEADER_ID',
37 p_worker_id,
38 p_num_workers,
39 p_batch_size,
40 0,
41 l_sql_stmt,
42 null,
43 null);
44
45 ------ Get rowid ranges ------
46 ad_parallel_updates_pkg.get_id_range(
47 l_start_jeid,
48 l_end_jeid,
49 l_any_rows_to_process,
50 p_batch_size,
51 TRUE);
52
53 -------------------------------------------------------------------
54 -- Run the transaction transformation for unposted items.
55 -- Relies on AD rerunnability
56 -------------------------------------------------------------------
57
58
59 WHILE ( l_any_rows_to_process = TRUE )
60 LOOP
61
62 /*Bug 4943551 Changed the logic by using a new table PA_PRIM_REP_LEGER_tmp */
63
64 UPDATE GL_IMPORT_REFERENCES gl
65 SET gl.gl_sl_link_id = xla_gl_sl_link_id_s.nextval,
66 gl.gl_sl_link_table = 'XLAJEL'
67 WHERE gl.gl_sl_link_id is NULL
68 AND gl.je_header_id >= l_start_jeid
69 AND gl.je_header_id <= l_end_jeid
70 and EXISTS ( select 'X'
71 from gl_je_headers hd,
72 PA_PRIM_REP_LEGER_tmp per
73 where hd.je_header_id >= l_start_jeid
74 and hd.je_header_id <= l_end_jeid
75 and hd.LEDGER_ID = per.denorm_ledger_id
76 and hd.PERIOD_NAME = per.PERIOD_NAME
77 and per.batch_id = p_batch_id
78 and hd.je_source = 'Project Accounting'
79 and hd.JE_HEADER_ID = gl.JE_HEADER_ID);
80
81 l_rows_processed := SQL%ROWCOUNT;
82
83 ad_parallel_updates_pkg.processed_id_range(
84 l_rows_processed,
85 l_end_jeid);
86
87 COMMIT;
88
89 ad_parallel_updates_pkg.get_id_range(
90 l_start_jeid,
91 l_end_jeid,
92 l_any_rows_to_process,
93 p_batch_size,
94 FALSE);
95
96 END LOOP ; /* end of WHILE loop */
97
98 COMMIT;
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 RAISE;
103 END;
104
105 PROCEDURE REV_UPG_AD_PAR( p_table_owner IN VARCHAR2,
106 p_table_name IN VARCHAR2,
107 p_script_name IN VARCHAR2,
108 p_num_workers IN NUMBER,
109 p_worker_id IN NUMBER,
110 p_batch_size IN NUMBER,
111 p_batch_id IN NUMBER)
112 IS
113
114 l_start_rowid ROWID;
115 l_end_rowid ROWID;
116
117 l_any_rows_to_process BOOLEAN;
118 l_rows_processed NUMBER;
119 BEGIN
120
121 ad_parallel_updates_pkg.initialize_rowid_range(
122 ad_parallel_updates_pkg.ROWID_RANGE,
123 p_table_owner,
124 p_table_name,
125 p_script_name,
126 p_worker_id,
127 p_num_workers,
128 p_batch_size, 0);
129
130 ------ Get rowid ranges ------
131 ad_parallel_updates_pkg.get_rowid_range(
132 l_start_rowid,
133 l_end_rowid,
134 l_any_rows_to_process,
135 p_batch_size,
136 TRUE);
137
138 WHILE ( l_any_rows_to_process = TRUE )
139 LOOP
140
141 PA_BILL_REV_XLA_UPGRADE.UPGRADE_TRANSACTIONS(
142 p_start_rowid => l_start_rowid,
143 p_end_rowid => l_end_rowid,
144 p_batch_id => p_batch_id,
145 p_rows_process => l_rows_processed);
146
147 ad_parallel_updates_pkg.processed_rowid_range(
148 l_rows_processed,
149 l_end_rowid);
150
151 COMMIT;
152
153 ad_parallel_updates_pkg.get_rowid_range(
154 l_start_rowid,
155 l_end_rowid,
156 l_any_rows_to_process,
157 p_batch_size,
158 FALSE);
159
160 END LOOP ; /* end of WHILE loop */
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 RAISE;
165 END;
166
167 PROCEDURE UPGRADE_TRANSACTIONS(p_start_rowid IN ROWID,
168 p_end_rowid IN ROWID,
169 p_batch_id IN NUMBER,
170 p_rows_process OUT NOCOPY NUMBER)
171 IS
172
173
174 l_creation_date date :=sysdate;
175 l_created_by number(15) := 2; --Bug 6319424: Commented '-2005'
176 l_last_update_date date := sysdate;
177 l_last_updated_by number(15) := 2; --Bug 6319424: Commented '-2005'
178 l_last_update_login number(15):= 2; --Bug 6319424: Commented '-2005'
179
180
181 BEGIN
182
183 p_rows_process := 0;
184
185 INSERT ALL /*+ rowid(dr) leading(dr) */
186 WHEN (/*unbilled_receivable_dr <> 0 Commented for Bug 10302541
187 and */unbilled_code_combination_id is not null
188 and currency_code is not null)THEN /*5455002*/
189 INTO PA_XLA_LINES_TMP
190 ( project_id,
191 draft_revenue_num,
192 ae_header_id,
193 xla_event_id,
194 code_combination_id,
195 amount_cr,
196 amount_dr,
197 currency_code,
198 gl_batch_name,
199 gl_category,
200 ledger_id,
201 accounting_date,
202 position,
203 adjusted_flag)
204 values
205 ( project_id,
206 draft_revenue_num,
207 xla_ae_headers_s.nextval,
208 xla_events_s.nextval,
209 unbilled_code_combination_id,
210 decode(sign(unbilled_receivable_dr),
211 -1, abs(unbilled_receivable_dr), ''),
212 decode(sign(unbilled_receivable_dr),
213 1, abs(unbilled_receivable_dr), ''),
214 currency_code,
215 unbilled_batch_name,
216 'Revenue - UBR',
217 set_of_books_id,
218 gl_date,
219 3,
220 adjusted_flag
221 )
222 WHEN (/*unearned_revenue_cr <> 0 Commented for Bug 10302541
223 and */ unearned_code_combination_id is not null
224 and currency_code is not null) THEN /*5455002*/
225 INTO PA_XLA_LINES_TMP
226 ( project_id,
227 draft_revenue_num,
228 ae_header_id,
229 xla_event_id,
230 code_combination_id,
231 amount_cr,
232 amount_dr ,
233 currency_code,
234 gl_batch_name,
235 gl_category,
236 ledger_id,
237 accounting_date,
238 position,
239 adjusted_flag)
240 values
241 ( project_id,
242 draft_revenue_num,
243 xla_ae_headers_s.nextval,
244 xla_events_s.nextval,
245 unearned_code_combination_id,
246 decode(sign(unearned_revenue_cr),
247 1, abs(unearned_revenue_cr), ''),
248 decode(sign(unearned_revenue_cr),
249 -1, abs(unearned_revenue_cr), ''),
250 currency_code,
251 unearned_batch_name,
252 'Revenue - UER',
253 set_of_books_id,
254 gl_date,
255 4,
256 adjusted_flag
257 )
258 WHEN (realized_gains_amount <> 0
259 and realized_gains_ccid is not null
260 and currency_code is not null) THEN /*5455002*/
261 INTO PA_XLA_LINES_TMP
262 ( project_id,
263 draft_revenue_num,
264 ae_header_id,
265 xla_event_id,
266 code_combination_id,
267 amount_cr,
268 amount_dr ,
269 currency_code,
270 gl_batch_name,
271 gl_category,
272 ledger_id,
273 accounting_date,
274 position,
275 adjusted_flag)
276 values
277 ( project_id,
278 draft_revenue_num,
279 xla_ae_headers_s.nextval,
280 xla_events_s.nextval,
281 realized_gains_ccid,
282 decode(sign(realized_gains_amount),
283 1, abs(realized_gains_amount),''),
284 decode(sign(realized_gains_amount),
285 -1, abs(realized_gains_amount), ''),
286 currency_code,
287 realized_gains_batch_name,
288 'Revenue - Realized Gains',
289 set_of_books_id,
290 gl_date,
291 5,
292 adjusted_flag
293 )
294 WHEN (realized_losses_amount <> 0
295 and realized_losses_ccid is not null
296 and currency_code is not null) THEN/*5455002*/
297 INTO PA_XLA_LINES_TMP
298 ( project_id,
299 draft_revenue_num,
300 ae_header_id,
301 xla_event_id,
302 code_combination_id,
303 amount_cr,
304 amount_dr ,
305 currency_code,
306 gl_batch_name,
307 gl_category,
308 ledger_id,
309 accounting_date,
310 position,
311 adjusted_Flag)
312 values
313 ( project_id,
314 draft_revenue_num,
315 xla_ae_headers_s.nextval,
316 xla_events_s.nextval,
317 realized_losses_ccid,
318 decode(sign(realized_losses_amount),
319 1, abs(realized_losses_amount), ''),
320 decode(sign(realized_losses_amount),
321 -1, abs(realized_losses_amount),''),
322 currency_code,
323 realized_losses_batch_name,
324 'Revenue - Realized Losses',
325 set_of_books_id,
326 gl_date,
327 6,
328 adjusted_flag
329 )
330 WHEN 1= 1 THEN
331 INTO PA_XLA_DRAFT_REV_TMP
332 (REV_ROWID,
333 PROJECT_ID,
334 DRAFT_REVENUE_NUM,
335 AE_HEADER_ID,
336 XLA_EVENT_ID,
337 LEDGER_ID,
338 ACCOUNTING_DATE,
339 CURRENCY_CODE,
340 ADJUSTED_FLAG)
341 values
342 (REV_ROWID,
343 PROJECT_ID,
344 DRAFT_REVENUE_NUM,
345 xla_ae_headers_s.nextval,
346 xla_events_s.nextval,
347 set_of_books_id,
348 gl_date,
349 currency_code,
350 adjusted_flag)
351 INTO XLA_TRANSACTION_ENTITIES_UPG
352 (upg_batch_id,
353 upg_source_application_id,
354 application_id,
355 ledger_id,
356 legal_entity_id,
357 entity_code,
358 source_id_int_1,
359 source_id_int_2,
360 security_id_int_1,
361 security_id_char_1,
362 source_application_id,
363 creation_date,
364 created_by,
365 last_update_date,
366 last_updated_by,
367 last_update_login,
368 entity_id,
369 upg_valid_flag,
370 transaction_number)
371 VALUES
372 ( batch_id ,
373 upg_source_app_id,
374 app_id,
375 set_of_books_id,
376 legal_entity_id,
377 'REVENUE',
378 project_id,
379 draft_revenue_num,
380 org_id ,
381 null,
382 '275',
383 l_creation_date,
384 l_created_by,
385 l_last_update_date,
386 l_last_updated_by,
387 l_last_update_login,
388 XLA_TRANSACTION_ENTITIES_S.nextval,
389 '' ,
390 transaction_number)
391 INTO XLA_EVENTS
392 (upg_batch_id,
393 upg_source_application_id,
394 application_id,
395 event_type_code,
396 event_number,
397 event_status_code,
398 process_status_code,
399 on_hold_flag,
400 event_date,
401 creation_date,
402 created_by,
403 last_update_date,
404 last_updated_by,
405 last_update_login,
406 program_update_date,
407 program_id,
408 program_application_id,
409 request_id,
410 entity_id,
411 event_id,
412 upg_valid_flag,
413 transaction_date)
414 VALUES
415 (batch_id,
416 upg_source_app_id,
417 app_id,
418 decode(adjusted_flag,'Y','REVENUE_ADJ','REVENUE'),
419 xla_events_s.nextval,
420 'P',
421 'P',
422 'N',
423 gl_date,
424 l_creation_date,
425 l_created_by,
426 l_last_update_date,
427 l_last_updated_by,
428 l_last_update_login,
429 l_creation_date,
430 -2005,
431 275,
432 '',
433 XLA_TRANSACTION_ENTITIES_S.nextval,
434 xla_events_s.nextval,
435 'Y',
436 gl_date
437 )
438 INTO XLA_AE_HEADERS
439 (upg_batch_id,
440 upg_source_application_id,
441 application_id,
442 amb_context_code,
443 entity_id,
444 event_id,
445 event_type_code,
446 ae_header_id,
447 ledger_id,
448 accounting_date,
449 period_name,
450 reference_date,
454 gl_transfer_date,
451 balance_type_code,
452 je_category_name,
453 gl_transfer_status_code,
455 accounting_entry_status_code,
456 accounting_entry_type_code,
457 creation_date,
458 created_by,
459 last_update_date,
460 last_updated_by,
461 last_update_login,
462 program_update_date,
463 program_id,
464 program_application_id,
465 request_id,
466 upg_valid_flag)
467 VALUES
468 (batch_id,
469 upg_source_app_id,
470 app_id,
471 'DEFAULT',
472 XLA_TRANSACTION_ENTITIES_S.nextval,
473 xla_events_s.nextval,
474 decode(adjusted_flag,'Y','REVENUE_ADJ','REVENUE'),
475 xla_ae_headers_s.nextval,
476 set_of_books_id,
477 gl_date,
478 gl_period_name,
479 '',
480 'A',
481 'Revenue',
482 'Y',
483 trans_date,
484 'F',
485 'STANDARD',
486 l_creation_date,
487 l_created_by,
488 l_last_update_date,
489 l_last_updated_by,
490 l_last_update_login,
491 l_creation_date,
492 -2005,
493 275,
494 '',
495 'Y'
496 ) select /*+ rowid(dr) leading(dr) */
497 dr.rowid rev_rowid,
498 p_batch_id batch_id,
499 275 upg_source_app_id,
500 275 app_id,
501 imp.set_of_books_id set_of_books_id,
502 hr.org_information2 legal_entity_id,
503 dr.project_id project_id, --src id int start
504 dr.draft_revenue_num draft_revenue_num,
505 dr.org_id org_id,
506 dr.gl_date gl_date,
507 dr.gl_period_name gl_period_name,
508 NULL trx_number,
509 dr.transferred_date trans_date,
510 dr.unbilled_receivable_dr unbilled_receivable_dr,
511 dr.unearned_revenue_cr unearned_revenue_cr,
512 dr.unbilled_code_combination_id unbilled_code_combination_id,
513 dr.unearned_code_combination_id unearned_code_combination_id,
514 dr.unbilled_batch_name unbilled_batch_name,
515 dr.unearned_batch_name unearned_batch_name,
516 dr.realized_gains_amount realized_gains_amount,
517 dr.realized_losses_amount realized_losses_amount,
518 dr.realized_gains_ccid realized_gains_ccid,
519 dr.realized_losses_ccid realized_losses_ccid,
520 dr.realized_gains_batch_name realized_gains_batch_name,
521 dr.realized_losses_batch_name realized_losses_batch_name,
522 pa.projfunc_currency_code currency_code,
523 decode(dr.draft_revenue_num_credited,null,'N','Y') adjusted_flag,
524 pa.segment1||'-'||to_char(dr.draft_revenue_num) transaction_number
525 from pa_draft_revenues_all dr,
526 pa_implementations_all imp,
527 pa_xla_upg_ctrl gl,
528 pa_projects_all pa,
529 hr_organization_information hr/*Added for 4920063 */
530 where dr.rowid >= p_start_rowid
531 and dr.rowid <= p_end_rowid
532 and dr.TRANSFER_STATUS_CODE ='A'
533 and dr.event_id is null
534 and dr.org_id = imp.org_id
535 and gl.status ='P'
536 and gl.reference = 'GL_PERIOD_STATUSES'
537 and gl.batch_id = p_batch_id
538 and gl.ledger_id = imp.set_of_books_id
539 and dr.gl_date between to_date(gl.min_value,'J') and to_date(gl.max_value,'J')
540 and pa.project_id = dr.project_id
541 and hr.organization_id = imp.org_id
542 and hr.org_information_context = 'Operating Unit Information';
543
544 INSERT ALL
545 WHEN 1 = 1 THEN
546 INTO PA_REV_AE_LINES_TMP
547 (ae_header_id,
548 ae_line_num,
549 gl_batch_name,
550 code_combination_id,
551 dist_type)
552 values
553 (
554 ae_header_id,
555 line_num,
556 gl_batch_name,
557 code_combination_id,
558 gl_category
559 )
560 INTO XLA_AE_LINES
561 (upg_batch_id,
562 ae_header_id,
563 ae_line_num,
564 application_id,
565 code_combination_id,
566 gl_transfer_mode_code,
567 accounted_dr,
568 unrounded_accounted_dr,
569 accounted_cr,
570 unrounded_accounted_cr,
571 currency_code,
572 entered_dr,
573 unrounded_entered_dr,
574 entered_cr,
575 unrounded_entered_cr,
576 description,
577 accounting_class_code,
578 gl_sl_link_id,
579 gl_sl_link_table,
580 creation_date,
581 created_by,
582 last_update_date,
583 last_updated_by,
584 last_update_login,
585 program_update_date,
586 program_id,
587 program_application_id,
588 request_id,
589 gain_or_loss_flag,
590 accounting_date,
591 ledger_id,
592 business_class_code
593 )
594 VALUES
595 ( batch_id,
596 ae_header_id,
597 line_num,
598 275,
599 code_combination_id,
600 'S',
601 amount_dr,
602 amount_dr,
606 amount_dr,
603 amount_cr,
604 amount_cr,
605 currency_code,
607 amount_dr,
608 amount_cr,
609 amount_cr,
610 'Project Revenue',
611 account_class,
612 gl_sl_link_id,
613 'XLAJEL',
614 l_creation_date,
615 l_created_by,
616 l_last_update_date,
617 l_last_updated_by,
618 l_last_update_login,
619 l_creation_date,
620 -2005,
621 275,
622 '',
623 gain_or_loss_flag,
624 accounting_date,
625 ledger_id,
626 'PA_REV_ADJ')
627 INTO XLA_DISTRIBUTION_LINKS
628 (APPLICATION_ID,
629 EVENT_ID,
630 AE_HEADER_ID,
631 AE_LINE_NUM,
632 SOURCE_DISTRIBUTION_TYPE,
633 SOURCE_DISTRIBUTION_ID_NUM_1,
634 SOURCE_DISTRIBUTION_ID_NUM_2,
635 MERGE_DUPLICATE_CODE,
636 EVENT_TYPE_CODE,
637 EVENT_CLASS_CODE,
638 UPG_BATCH_ID,
639 REF_AE_HEADER_ID,
640 LINE_DEFINITION_CODE,
641 temp_line_num,
642 unrounded_accounted_dr,
643 unrounded_accounted_cr,
644 unrounded_entered_dr,
645 unrounded_entered_cr,
646 rounding_class_code)
647 VALUES
648 (275,
649 event_id,
650 ae_header_id,
651 line_num,
652 gl_category,
653 source_num1,
654 source_num2,
655 'N',
656 event_type_code,
657 event_class_code,
658 batch_id,
659 ae_header_id,
660 'PA_ACCRUAL_ACCOUNTING',
661 line_num,
662 amount_dr,
663 amount_cr,
664 amount_dr,
665 amount_cr,
666 account_class
667 )
668 select
669 p_batch_id AS batch_id,
670 ae_header_id AS ae_header_id,
671 event_id AS event_id,
672 account_class AS account_class,
673 code_combination_id AS code_combination_id,
674 amount_dr AS amount_dr,
675 amount_cr AS amount_cr,
676 currency_code AS currency_code,
677 gain_or_loss_flag AS gain_or_loss_flag,
678 event_type_code AS event_type_code,
679 event_class_code AS event_class_code,
680 source_num1,
681 source_num2,
682 gl_sl_link_id,
683 gl_batch_name,
684 gl_category,
685 accounting_date,
686 ledger_id,
687 RANK() OVER (PARTITION BY ae_header_id
688 ORDER BY position,row_num) AS line_num
689 FROM
690 ( SELECT 1 position,
691 dr.ae_header_id ae_header_id,
692 decode(dr.adjusted_flag,'N','REVENUE','REVENUE_ADJ') account_class,
693 crdl.code_combination_id code_combination_id,
694 decode(sign(crdl.amount),
695 1, abs(crdl.amount),
696 '') amount_cr,
697 decode(sign(crdl.amount),
698 -1, abs(crdl.amount),
699 '') amount_dr,
700 nvl(crdl.revproc_currency_code,crdl.projfunc_currency_code) currency_code,
701 dr.xla_event_id event_id,
702 'REVENUE_ALL' event_type_code,
703 decode(dr.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
704 'N' gain_or_loss_flag,
705 dr.project_id project_id,
706 dr.draft_revenue_num dr_num,
707 crdl.DRAFT_REVENUE_ITEM_LINE_NUM dr_line_num,
708 crdl.expenditure_item_id source_num1,
709 crdl.line_num source_num2,
710 null gl_sl_link_id,
711 crdl.BATCH_NAME gl_batch_name,
712 'Revenue - Normal Revenue' gl_category,
713 rownum row_num,
714 dr.accounting_date accounting_date,
715 dr.ledger_id ledger_id
716 FROM PA_CUST_REV_DIST_LINES_ALL CRDL,
717 PA_XLA_DRAFT_REV_TMP DR
718 WHERE DR.DRAFT_REVENUE_NUM = CRDL.DRAFT_REVENUE_NUM
719 AND DR.PROJECT_ID = CRDL.PROJECT_ID
720 /*AND NVL(CRDL.AMOUNT, 0) <> 0 Commented for Bug 10302541 */
721 AND crdl.code_combination_id is not null /*Bug 5455002*/
722 AND nvl(crdl.revproc_currency_code,crdl.projfunc_currency_code) is not null /*5441521*/
723 UNION ALL
724 SELECT /*+ USE_NL(DR,ERDL,EV)*/
725 2 position,
726 dr.ae_header_id ae_header_id,
727 DECODE(et.event_type_classification,
728 'WRITE OFF','EVENT_WO_REVENUE',
729 decode(dr.adjusted_flag,'N','REVENUE',
730 'REVENUE_ADJ'))account_class,
731 erdl.code_combination_id code_combination_id,
732 decode(sign(erdl.amount),
733 1, abs(erdl.amount),
734 '') amount_cr,
735 decode(sign(erdl.amount),
736 -1, abs(erdl.amount),
737 '') amount_dr,
738 nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) currency_code,
739 dr.xla_event_id event_id,
743 dr.project_id project_id,
740 'REVENUE_ALL' event_type_code,
741 decode(dr.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
742 'N' gain_or_loss_flag,
744 erdl.draft_revenue_num dr_num,
745 erdl.draft_revenue_item_line_num dr_line_num,
746 ev.event_id source_num1,
747 erdl.line_num source_num2, /*Modified for 12899417*/
748 null gl_sl_link_id,
749 erdl.BATCH_NAME gl_batch_name,
750 'Revenue - Event Revenue' gl_category,
751 rownum row_num,
752 dr.accounting_date accounting_date,
753 dr.ledger_id ledger_id
754 FROM PA_CUST_EVENT_RDL_ALL ERDL,
755 PA_XLA_DRAFT_REV_TMP DR,
756 PA_EVENTS ev,
757 PA_EVENT_TYPES et
758 WHERE DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
759 AND DR.PROJECT_ID = ERDL.PROJECT_ID
760 AND NVL(ERDL.AMOUNT, 0) <> 0
761 AND erdl.project_id = ev.project_id
762 AND nvl(erdl.task_id,-99) = nvl(ev.task_id,-99)
763 AND erdl.event_num = ev.event_num
764 AND ev.event_type = et.event_type
765 AND erdl.code_combination_id is not null /*Bug 5455002*/
766 AND nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) is not null /*5441521*/
767 UNION ALL
768 select position,
769 dr.ae_header_id ae_header_id,
770 Decode(position,3,'UNBILL',
771 4,'UNEARNED_REVENUE',
772 5,'REALIZED_GAINS',
773 6,'REALIZED_LOSS') account_class,
774 code_combination_id,
775 amount_cr,
776 amount_dr,
777 dr.currency_code currency_code,
778 dr.xla_event_id event_id,
779 'REVENUE_ALL' event_type_code,
780 decode(dr.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
781 'N' gain_or_loss_flag,
782 dr.project_id project_id,
783 dr.draft_revenue_num dr_num,
784 1 dr_line_num,
785 dr.project_id source_num1,
786 dr.draft_revenue_num source_num2,
787 null gl_sl_link_id,
788 gl_batch_name,
789 gl_category,
790 rownum row_num,
791 accounting_date,
792 ledger_id
793 FROM PA_XLA_LINES_TMP DR);
794
795
796 UPDATE PA_DRAFT_REVENUES_ALL dr
797 SET dr.event_id = ( select tmp.xla_event_id
798 from PA_XLA_DRAFT_REV_TMP tmp
799 WHERE dr.rowid = tmp.rev_rowid
800 and rownum = 1), /*bug 9972513*/
801 dr.created_by =-99999
802 WHERE dr.rowid >= p_start_rowid
803 AND dr.rowid <= p_end_rowid
804 AND dr.event_id is null
805 AND dr.transfer_Status_code='A'
806 AND EXISTS (SELECT 'X'
807 FROM PA_XLA_DRAFT_REV_TMP tmp
808 WHERE dr.rowid = tmp.rev_rowid);
809
810 p_rows_process := p_rows_process + SQL%ROWCOUNT;
811
812 UPDATE XLA_AE_LINES lin
813 SET lin.gl_sl_link_id = (SELECT gl_sl_link_id
814 FROM GL_IMPORT_REFERENCES imp,
815 PA_REV_AE_LINES_TMP tmp
816 WHERE imp.reference_6 = tmp.gl_batch_name
817 AND imp.reference_2 = tmp.code_combination_id
818 AND imp.reference_3 = tmp.dist_type
819 AND lin.ae_header_id = tmp.ae_header_id
820 AND lin.ae_line_num = tmp.ae_line_num
821 AND imp.gl_sl_link_id is not null /*Bug 5168431*/
822 AND rownum =1)
823 WHERE EXISTS ( SELECT 1
824 FROM PA_REV_AE_LINES_TMP tmp1
825 WHERE lin.ae_header_id = tmp1.ae_header_id
826 AND lin.ae_line_num = tmp1.ae_line_num)
827 AND lin.application_id = 275
828 AND lin.gl_sl_link_id is null
829 AND lin.upg_batch_id = p_batch_id;
830
831
832
833 EXCEPTION
834 WHEN NO_DATA_FOUND THEN
835 p_rows_process :=0;
836 RAISE;
837
838 WHEN OTHERS THEN
839 p_rows_process :=0;
840 RAISE;
841
842 END UPGRADE_TRANSACTIONS;
843
844
845 PROCEDURE REV_UPG_MC_AD_PAR( p_table_owner IN VARCHAR2,
846 p_table_name IN VARCHAR2,
847 p_script_name IN VARCHAR2,
848 p_num_workers IN NUMBER,
849 p_worker_id IN NUMBER,
850 p_batch_size IN NUMBER,
851 p_batch_id IN NUMBER)
852 IS
853
854 l_start_rowid ROWID;
855 l_end_rowid ROWID;
856
857 l_any_rows_to_process BOOLEAN;
858 l_rows_processed NUMBER;
859 BEGIN
860
861 ad_parallel_updates_pkg.initialize_rowid_range(
862 ad_parallel_updates_pkg.ROWID_RANGE,
863 p_table_owner,
864 p_table_name,
865 p_script_name,
866 p_worker_id,
867 p_num_workers,
868 p_batch_size, 0);
869
870 ------ Get rowid ranges ------
874 l_any_rows_to_process,
871 ad_parallel_updates_pkg.get_rowid_range(
872 l_start_rowid,
873 l_end_rowid,
875 p_batch_size,
876 TRUE);
877
878 WHILE ( l_any_rows_to_process = TRUE )
879 LOOP
880
881 PA_BILL_REV_XLA_UPGRADE.UPGRADE_MC_TRANSACTIONS(
882 p_start_rowid => l_start_rowid,
883 p_end_rowid => l_end_rowid,
884 p_batch_id => p_batch_id,
885 p_rows_process => l_rows_processed);
886
887 ad_parallel_updates_pkg.processed_rowid_range(
888 l_rows_processed,
889 l_end_rowid);
890
891 COMMIT;
892
893 ad_parallel_updates_pkg.get_rowid_range(
894 l_start_rowid,
895 l_end_rowid,
896 l_any_rows_to_process,
897 p_batch_size,
898 FALSE);
899
900 END LOOP ; /* end of WHILE loop */
901
902 EXCEPTION
903 WHEN OTHERS THEN
904 RAISE;
905 END;
906
907
908
909 PROCEDURE UPGRADE_MC_TRANSACTIONS( p_start_rowid IN ROWID,
910 p_end_rowid IN ROWID,
911 p_batch_id IN NUMBER,
912 p_rows_process OUT NOCOPY NUMBER) IS
913
914 l_creation_date date :=sysdate;
915 l_created_by number(15) := 2; --Bug 6319424: Commented '-2005'
916 l_last_update_date date := sysdate;
917 l_last_updated_by number(15) := 2; --Bug 6319424: Commented '-2005'
918 l_last_update_login number(15):= 2; --Bug 6319424: Commented '-2005'
919 l_rows_processed NUMBER :=0;
920 BEGIN
921
922 l_rows_processed := 0;
923
924 INSERT ALL /*+ rowid(mc) leading(mc) */
925 WHEN (mc_unbilled_receivable_dr <> 0
926 and unbilled_code_combination_id is not null
927 and currency_code is not null)THEN /*5455002*/
928 INTO PA_XLA_LINES_TMP
929 ( project_id,
930 draft_revenue_num,
931 ae_header_id,
932 xla_event_id,
933 code_combination_id,
934 amount_cr,
935 amount_dr ,
936 currency_code,
937 entered_cr,
938 entered_dr,
939 gl_batch_name,
940 gl_category,
941 position,
942 accounting_date,
943 ledger_id,
944 conversion_date,
945 adjusted_flag)
946 values
947 ( project_id,
948 draft_revenue_num,
949 xla_ae_headers_s.nextval,
950 xla_events_s.nextval,
951 unbilled_code_combination_id,
952 decode(sign(mc_unbilled_receivable_dr),
953 -1, abs(mc_unbilled_receivable_dr), ''),
954 decode(sign(mc_unbilled_receivable_dr),
955 1, abs(mc_unbilled_receivable_dr), ''),
956 currency_code,
957 decode(sign(unbilled_receivable_dr),
958 -1, abs(unbilled_receivable_dr), ''),
959 decode(sign(unbilled_receivable_dr),
960 1, abs(unbilled_receivable_dr), ''),
961 unbilled_batch_name,
962 'Revenue - UBR',
963 3,
964 gl_date,
965 rep_set_of_books_id,
966 gl_date,
967 adjusted_flag
968 )
969 WHEN (mc_unearned_revenue_cr <> 0
970 and unearned_code_combination_id is not null
971 and currency_code is not null) THEN /*5455002*/
972 INTO PA_XLA_LINES_TMP
973 ( project_id,
974 draft_revenue_num,
975 ae_header_id,
976 xla_event_id,
977 code_combination_id,
978 amount_cr,
979 amount_dr ,
980 currency_code,
981 entered_cr,
982 entered_dr,
983 gl_batch_name,
984 gl_category,
985 position,
986 accounting_date,
987 ledger_id,
988 conversion_date,
989 adjusted_flag)
990 values
991 ( project_id,
992 draft_revenue_num,
993 xla_ae_headers_s.nextval,
994 xla_events_s.nextval,
995 unearned_code_combination_id,
996 decode(sign(mc_unearned_revenue_cr),
997 1, abs(mc_unearned_revenue_cr), ''),
998 decode(sign(mc_unearned_revenue_cr),
999 -1, abs(mc_unearned_revenue_cr), ''),
1000 currency_code,
1001 decode(sign(unearned_revenue_cr),
1002 1, abs(unearned_revenue_cr), ''),
1003 decode(sign(unearned_revenue_cr),
1004 -1, abs(unearned_revenue_cr), ''),
1005 unearned_batch_name,
1006 'Revenue - UER',
1007 4,
1008 gl_date,
1009 rep_set_of_books_id,
1010 gl_date,
1011 adjusted_flag
1012 )
1013 WHEN (mc_realized_gains_amount <> 0
1014 and realized_gains_ccid is not null
1015 and currency_code is not null) THEN /*5455002*/
1016 INTO PA_XLA_LINES_TMP
1017 ( project_id,
1018 draft_revenue_num,
1019 ae_header_id,
1020 xla_event_id,
1021 code_combination_id,
1022 amount_cr,
1023 amount_dr ,
1024 currency_code,
1025 entered_cr,
1026 entered_dr,
1027 gl_batch_name,
1028 gl_category,
1029 position,
1033 adjusted_flag)
1030 accounting_date,
1031 ledger_id,
1032 conversion_date,
1034 values
1035 ( project_id,
1036 draft_revenue_num,
1037 xla_ae_headers_s.nextval,
1038 xla_events_s.nextval,
1039 realized_gains_ccid,
1040 decode(sign(mc_realized_gains_amount),
1041 1, abs(mc_realized_gains_amount),''),
1042 decode(sign(mc_realized_gains_amount),
1043 -1, abs(mc_realized_gains_amount), ''),
1044 currency_code,
1045 decode(sign(realized_gains_amount),
1046 1, abs(realized_gains_amount),''),
1047 decode(sign(realized_gains_amount),
1048 -1, abs(realized_gains_amount), ''),
1049 realized_gains_batch_name,
1050 'Revenue - Realized Gains',
1051 5,
1052 gl_date,
1053 rep_set_of_books_id,
1054 gl_date,
1055 adjusted_Flag
1056 )
1057 WHEN (mc_realized_losses_amount <> 0
1058 and realized_losses_ccid is not null
1059 and currency_code is not null)THEN /*5455002*/
1060 INTO PA_XLA_LINES_TMP
1061 ( project_id,
1062 draft_revenue_num,
1063 ae_header_id,
1064 xla_event_id,
1065 code_combination_id,
1066 amount_cr,
1067 amount_dr ,
1068 currency_code,
1069 entered_cr,
1070 entered_dr,
1071 gl_batch_name,
1072 gl_category,
1073 position,
1074 accounting_date,
1075 ledger_id,
1076 conversion_date,
1077 adjusted_flag)
1078 values
1079 ( project_id,
1080 draft_revenue_num,
1081 xla_ae_headers_s.nextval,
1082 xla_events_s.nextval,
1083 realized_losses_ccid,
1084 decode(sign(mc_realized_losses_amount),
1085 1, abs(mc_realized_losses_amount), ''),
1086 decode(sign(mc_realized_losses_amount),
1087 -1, abs(mc_realized_losses_amount),''),
1088 currency_code,
1089 decode(sign(realized_losses_amount),
1090 1, abs(realized_losses_amount), ''),
1091 decode(sign(realized_losses_amount),
1092 -1, abs(realized_losses_amount),''),
1093 realized_losses_batch_name,
1094 'Revenue - Realized Losses',
1095 6,
1096 gl_date,
1097 rep_set_of_books_id,
1098 gl_date,
1099 adjusted_flag
1100 )
1101 WHEN 1 = 1 THEN
1102 INTO PA_XLA_DRAFT_REV_TMP
1103 (REV_ROWID,
1104 PROJECT_ID,
1105 DRAFT_REVENUE_NUM,
1106 AE_HEADER_ID,
1107 XLA_EVENT_ID,
1108 CURRENCY_CODE,
1109 ACCOUNTING_DATE,
1110 LEDGER_ID,
1111 REP_SET_OF_BOOKS_ID,
1112 adjusted_flag)
1113 values
1114 (rev_rowid,
1115 PROJECT_ID,
1116 DRAFT_REVENUE_NUM,
1117 xla_ae_headers_s.nextval,
1118 event_id,
1119 currency_code,
1120 gl_date,
1121 ledger_id,
1122 rep_set_of_books_id,
1123 adjusted_flag)
1124
1125 INTO XLA_AE_HEADERS
1126 (upg_batch_id,
1127 upg_source_application_id,
1128 application_id,
1129 amb_context_code,
1130 entity_id,
1131 event_id,
1132 event_type_code,
1133 ae_header_id,
1134 ledger_id,
1135 accounting_date,
1136 period_name,
1137 balance_type_code,
1138 je_category_name,
1139 gl_transfer_status_code,
1140 gl_transfer_date,
1141 accounting_entry_status_code,
1142 accounting_entry_type_code,
1143 creation_date,
1144 created_by,
1145 last_update_date,
1146 last_updated_by,
1147 last_update_login,
1148 program_update_date,
1149 program_id,
1150 program_application_id,
1151 upg_valid_flag)
1152 VALUES
1153 (batch_id,
1154 upg_source_app_id,
1155 app_id,
1156 'DEFAULT',
1157 entity_id,
1158 event_id,
1159 decode(adjusted_flag,'Y','REVENUE_ADJ','REVENUE'),
1160 xla_ae_headers_s.nextval,
1161 rep_set_of_books_id,
1162 gl_date,
1163 gl_period_name,
1164 'A',
1165 'Revenue',
1166 decode(trans_status_code,'A','Y','N'),
1167 decode(trans_status_code,'A',trans_date,NULL),
1168 'F',
1169 'STANDARD',
1170 l_creation_date,
1171 l_created_by,
1172 l_last_update_date,
1173 l_last_updated_by,
1174 l_last_update_login,
1175 l_creation_date,
1176 -2005,
1177 275,
1178 'Y'
1179 )select /*+ rowid(mc) leading(mc) */
1180 mc.rowid rev_rowid,
1181 p_batch_id batch_id,
1182 275 upg_source_app_id,
1183 275 app_id,
1184 mc.set_of_books_id rep_set_of_books_id,
1185 hr.org_information2 legal_entity_id,
1186 dr.gl_date gl_date,
1187 dr.gl_period_name gl_period_name,
1188 evt.event_id event_id,
1189 evt.entity_id entity_id,
1190 mc.project_id project_id,
1191 mc.draft_revenue_num draft_revenue_num,
1192 mc.transferred_date trans_date,
1196 mc.unearned_revenue_cr mc_unearned_revenue_cr,
1193 dr.unbilled_receivable_dr unbilled_receivable_dr,
1194 dr.unearned_revenue_cr unearned_revenue_cr,
1195 mc.unbilled_receivable_dr mc_unbilled_receivable_dr,
1197 dr.unbilled_code_combination_id unbilled_code_combination_id,
1198 dr.unearned_code_combination_id unearned_code_combination_id,
1199 mc.unbilled_batch_name unbilled_batch_name,
1200 mc.unearned_batch_name unearned_batch_name,
1201 dr.realized_gains_amount realized_gains_amount,
1202 dr.realized_losses_amount realized_losses_amount,
1203 mc.realized_gains_amount mc_realized_gains_amount,
1204 mc.realized_losses_amount mc_realized_losses_amount,
1205 dr.realized_gains_ccid realized_gains_ccid,
1206 dr.realized_losses_ccid realized_losses_ccid,
1207 mc.realized_gains_batch_name realized_gains_batch_name,
1208 mc.realized_losses_batch_name realized_losses_batch_name,
1209 pa.projfunc_currency_code currency_code,
1210 mc.transfer_status_code trans_status_code,
1211 imp.set_of_books_id ledger_id,
1212 decode(dr.draft_revenue_num_credited,null,'N','Y') adjusted_flag
1213 from pa_draft_revenues_all dr,
1214 pa_implementations_all imp,
1215 pa_mc_draft_revs_all mc,
1216 XLA_EVENTS evt,
1217 pa_xla_upg_ctrl gl,
1218 pa_projects_all pa,
1219 hr_organization_information hr
1220 where mc.rowid >= p_start_rowid
1221 and mc.rowid <= p_end_rowid
1222 and dr.project_id = mc.project_id
1223 and dr.draft_revenue_num = mc.draft_revenue_num
1224 and nvl(mc.xla_migrated_flag,'N') ='N'
1225 -- and mc.transfer_status_code = 'A'
1226 and gl.status = 'P'
1227 and gl.reference = 'GL_PERIOD_STATUSES'
1228 and gl.batch_id = p_batch_id
1229 and gl.ledger_id = imp.set_of_books_id
1230 and dr.gl_date between to_date(gl.min_value,'J') and to_date(gl.max_value,'J')
1231 and dr.org_id = imp.org_id
1232 and dr.event_id = evt.event_id
1233 and pa.project_id = mc.project_id
1234 and hr.organization_id = imp.org_id
1235 and hr.org_information_context = 'Operating Unit Information';
1236
1237 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1238
1239 INSERT ALL
1240 WHEN 1 = 1 THEN
1241 INTO PA_REV_AE_LINES_TMP
1242 (ae_header_id,
1243 ae_line_num,
1244 gl_batch_name,
1245 code_combination_id,
1246 dist_type)
1247 values
1248 (
1249 ae_header_id,
1250 line_num,
1251 gl_batch_name,
1252 code_combination_id,
1253 gl_category
1254 )
1255 INTO XLA_AE_LINES
1256 (upg_batch_id,
1257 ae_header_id,
1258 ae_line_num,
1259 application_id,
1260 code_combination_id,
1261 gl_transfer_mode_code,
1262 accounted_dr,
1263 unrounded_accounted_dr,
1264 accounted_cr,
1265 unrounded_accounted_cr,
1266 currency_code,
1267 entered_dr,
1268 unrounded_entered_dr,
1269 entered_cr,
1270 unrounded_entered_cr,
1271 currency_conversion_date,
1272 currency_conversion_rate,
1273 currency_conversion_type,
1274 description,
1275 accounting_class_code,
1276 gl_sl_link_id,
1277 gl_sl_link_table,
1278 creation_date,
1279 created_by,
1280 last_update_date,
1281 last_updated_by,
1282 last_update_login,
1283 program_update_date,
1284 program_id,
1285 program_application_id,
1286 request_id,
1287 gain_or_loss_flag,
1288 accounting_date,
1289 ledger_id,
1290 business_class_code
1291 )
1292 VALUES
1293 ( batch_id,
1294 ae_header_id,
1295 line_num,
1296 275,
1297 code_combination_id,
1298 'S',
1299 amount_dr,
1300 amount_dr,
1301 amount_cr,
1302 amount_cr,
1303 currency_code,
1304 entered_dr,
1305 entered_dr,
1306 entered_cr,
1307 entered_cr,
1308 conversion_date,
1309 exchange_rate,
1310 rate_type,
1311 'Project Revenue',
1312 account_class,
1313 gl_sl_link_id,
1314 'XLAJEL',
1315 l_creation_date,
1316 l_created_by,
1317 l_last_update_date,
1318 l_last_updated_by,
1319 l_last_update_login,
1320 l_creation_date,
1321 -2005,
1322 275,
1323 '',
1324 gain_or_loss_flag,
1325 accounting_date,
1326 ledger_id,
1327 'PA_REV_ADJ')
1328 INTO XLA_DISTRIBUTION_LINKS
1329 (APPLICATION_ID,
1330 EVENT_ID,
1331 AE_HEADER_ID,
1332 AE_LINE_NUM,
1333 SOURCE_DISTRIBUTION_TYPE,
1334 SOURCE_DISTRIBUTION_ID_NUM_1,
1335 SOURCE_DISTRIBUTION_ID_NUM_2,
1336 MERGE_DUPLICATE_CODE,
1337 EVENT_TYPE_CODE,
1338 EVENT_CLASS_CODE,
1339 UPG_BATCH_ID,
1340 REF_AE_HEADER_ID,
1344 unrounded_accounted_cr,
1341 LINE_DEFINITION_CODE,
1342 temp_line_num,
1343 unrounded_accounted_dr,
1345 unrounded_entered_dr,
1346 unrounded_entered_cr,
1347 rounding_class_code)
1348 VALUES
1349 (275,
1350 event_id,
1351 ae_header_id,
1352 line_num,
1353 gl_category,
1354 source_num1,
1355 source_num2,
1356 'N',
1357 event_type_code,
1358 event_class_code,
1359 batch_id,
1360 ae_header_id,
1361 'PA_ACCRUAL_ACCOUNTING',
1362 line_num,
1363 amount_dr,
1364 amount_cr,
1365 entered_dr,
1366 entered_cr,
1367 account_class
1368 )
1369 select
1370 p_batch_id AS batch_id,
1371 ae_header_id AS ae_header_id,
1372 event_id AS event_id,
1373 account_class AS account_class,
1374 code_combination_id AS code_combination_id,
1375 amount_dr AS amount_dr,
1376 amount_cr AS amount_cr,
1377 entered_dr AS entered_dr,
1378 entered_cr AS entered_cr,
1379 currency_code AS currency_code,
1380 conversion_date AS conversion_date,
1381 exchange_rate AS exchange_rate,
1382 rate_type AS rate_type,
1383 gain_or_loss_flag AS gain_or_loss_flag,
1384 event_type_code AS event_type_code,
1385 event_class_code AS event_class_code,
1386 source_num1,
1387 source_num2,
1388 gl_sl_link_id,
1389 gl_batch_name,
1390 gl_category,
1391 accounting_date,
1392 ledger_id,
1393 RANK() OVER (PARTITION BY ae_header_id
1394 ORDER BY position,row_num) AS line_num
1395 FROM
1396 ( SELECT 1 position,
1397 mc.ae_header_id ae_header_id,
1398 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') account_class,
1399 crdl.code_combination_id code_combination_id,
1400 decode(sign(mcrdl.amount),
1401 1, abs(mcrdl.amount),
1402 '') amount_cr,
1403 decode(sign(mcrdl.amount),
1404 -1, abs(mcrdl.amount),
1405 '') amount_dr,
1406 decode(sign(crdl.amount),
1407 1, abs(crdl.amount),
1408 '') entered_cr,
1409 decode(sign(crdl.amount),
1410 -1, abs(crdl.amount),
1411 '') entered_dr,
1412 nvL(crdl.revproc_currency_code,crdl.projfunc_currency_code) currency_code,
1413 mcrdl.conversion_date AS conversion_date,
1414 mcrdl.exchange_rate AS exchange_rate,
1415 mcrdl.rate_type AS rate_type,
1416 mc.xla_event_id event_id,
1417 'REVENUE_ALL' event_type_code,
1418 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1419 'N' gain_or_loss_flag,
1420 mc.project_id project_id,
1421 mc.draft_revenue_num dr_num,
1422 mcrdl.DRAFT_REVENUE_ITEM_LINE_NUM dr_line_num,
1423 mcrdl.expenditure_item_id source_num1,
1424 mcrdl.line_num source_num2,
1425 null gl_sl_link_id,
1426 mcrdl.BATCH_NAME gl_batch_name,
1427 'Revenue - Normal Revenue' gl_category,
1428 rownum row_num,
1429 mc.accounting_date accounting_date,
1430 mc.REP_SET_OF_BOOKS_ID ledger_id
1431 FROM PA_CUST_REV_DIST_LINES_ALL CRDL,
1432 PA_MC_CUST_RDL_ALL mcrdl,
1433 PA_XLA_DRAFT_REV_TMP MC
1434 WHERE mc.project_id = mcrdl.project_id
1435 AND mc.draft_revenue_num = mcrdl.draft_revenue_num
1436 AND mc.rep_set_of_books_id = mcrdl.set_of_books_id
1437 AND mcrdl.expenditure_item_id = crdl.expenditure_item_id
1438 AND mcrdl.line_num = crdl.line_num
1439 AND NVL(mcrdl.amount, 0) <> 0
1440 AND crdl.code_combination_id is not null /*Bug 5455002*/
1441 AND nvl(crdl.revproc_currency_code,crdl.projfunc_currency_code) is not null /*5441521*/
1442 UNION ALL
1443 SELECT /*+ /*+ USE_NL(MC,MCERDL,EV)*/
1444 2 position,
1445 mc.ae_header_id ae_header_id,
1446 DECODE(et.event_type_classification,
1447 'WRITE OFF','EVENT_WO_REVENUE',
1448 decode(mc.adjusted_flag,'N','REVENUE',
1449 'REVENUE_ADJ')) account_class,
1450 erdl.code_combination_id code_combination_id,
1451 decode(sign(mcerdl.amount),
1452 1, abs(mcerdl.amount),
1453 '') amount_cr,
1454 decode(sign(mcerdl.amount),
1455 -1, abs(mcerdl.amount),
1456 '') amount_dr,
1457 decode(sign(erdl.amount),
1458 1, abs(erdl.amount),
1459 '') entered_cr,
1460 decode(sign(erdl.amount),
1461 -1, abs(erdl.amount),
1462 '') entered_dr,
1463 nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) currency_code,
1467 mc.xla_event_id event_id,
1464 mcerdl.conversion_date AS conversion_date,
1465 mcerdl.exchange_rate AS exchange_rate,
1466 mcerdl.rate_type AS rate_type,
1468 'REVENUE_ALL' event_type_code,
1469 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1470 'N' gain_or_loss_flag,
1471 mc.project_id project_id,
1472 erdl.draft_revenue_num dr_num,
1473 erdl.draft_revenue_item_line_num dr_line_num,
1474 ev.event_id source_num1,
1475 1 source_num2,
1476 null gl_sl_link_id,
1477 mcerdl.BATCH_NAME gl_batch_name,
1478 'Revenue - Event Revenue' gl_category,
1479 rownum row_num,
1480 mc.accounting_date accounting_date,
1481 mc.REP_SET_OF_BOOKS_ID ledger_id
1482 FROM PA_CUST_EVENT_RDL_ALL erdl,
1483 PA_MC_CUST_EVENT_RDL_ALL mcerdl,
1484 PA_EVENTS ev,
1485 PA_XLA_DRAFT_REV_TMP MC,
1486 PA_EVENT_TYPES et
1487 WHERE mc.project_id = mcerdl.project_id
1488 AND mc.draft_revenue_num = mcerdl.draft_revenue_num
1489 AND mc.rep_set_of_books_id = mcerdl.set_of_books_id
1490 AND NVL(mcerdl.amount, 0) <> 0
1491 AND mcerdl.project_id = erdl.project_id
1492 AND nvl(mcerdl.task_id,-99) = nvl(erdl.task_id,-99)
1493 AND mcerdl.event_num = erdl.event_num
1494 AND mcerdl.project_id = ev.project_id
1495 AND nvl(mcerdl.task_id,-99) = nvl(ev.task_id,-99)
1496 AND mcerdl.event_num = ev.event_num
1497 AND ev.event_type = et.event_type
1498 AND erdl.code_combination_id is not null /*Bug 5455002*/
1499 AND nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) is not null /*5441521*/
1500 UNION ALL
1501 select position,
1502 mc.ae_header_id ae_header_id,
1503 Decode(position,3,'UNBILL',
1504 4,'UNEARNED_REVENUE',
1505 5,'REALIZED_GAINS',
1506 6,'REALIZED_LOSS') account_class,
1507 code_combination_id,
1508 amount_cr,
1509 amount_dr,
1510 entered_cr,
1511 entered_dr,
1512 mc.currency_code currency_code,
1513 mc.conversion_date AS conversion_date,
1514 amount_cr/entered_cr AS exchange_rate,
1515 'User' AS rate_type,
1516 mc.xla_event_id event_id,
1517 'REVENUE_ALL' event_type_code,
1518 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1519 'N' gain_or_loss_flag,
1520 mc.project_id project_id,
1521 mc.draft_revenue_num dr_num,
1522 1 dr_line_num,
1523 mc.project_id source_num1,
1524 mc.draft_revenue_num source_num2,
1525 null gl_sl_link_id,
1526 gl_batch_name,
1527 gl_category,
1528 rownum row_num,
1529 accounting_date,
1530 ledger_id
1531 FROM PA_XLA_LINES_TMP MC );
1532
1533 UPDATE PA_MC_DRAFT_REVS_ALL mc
1534 SET mc.xla_migrated_flag = 'Y'
1535 WHERE mc.rowid >= p_start_rowid
1536 AND mc.rowid <= p_end_rowid
1537 AND nvl(xla_migrated_flag,'N') = 'N'
1538 AND EXISTS (SELECT 'X'
1539 FROM PA_XLA_DRAFT_REV_TMP tmp
1540 WHERE mc.rowid = tmp.rev_rowid);
1541
1542 p_rows_process := p_rows_process + SQL%ROWCOUNT;
1543
1544 UPDATE XLA_AE_LINES lin
1545 SET lin.gl_sl_link_id = (select gl_sl_link_id
1546 FROM GL_IMPORT_REFERENCES imp,
1547 PA_REV_AE_LINES_TMP tmp
1548 WHERE imp.reference_6 = tmp.gl_batch_name
1549 AND imp.reference_2 = tmp.code_combination_id
1550 AND imp.reference_3 = tmp.dist_type
1551 AND lin.ae_header_id = tmp.ae_header_id
1552 AND lin.ae_line_num = tmp.ae_line_num
1553 AND imp.gl_sl_link_id is not null /*Bug 5168431*/
1554 AND rownum=1)
1555 WHERE EXISTS ( SELECT 1
1556 FROM PA_REV_AE_LINES_TMP tmp1
1557 WHERE lin.ae_header_id = tmp1.ae_header_id
1558 AND lin.ae_line_num = tmp1.ae_line_num)
1559 AND lin.application_id = 275
1560 AND lin.gl_sl_link_id is null
1561 AND lin.upg_batch_id = p_batch_id;
1562
1563 EXCEPTION
1564 WHEN NO_DATA_FOUND THEN
1565 RAISE;
1566
1567 WHEN OTHERS THEN
1568 RAISE;
1569
1570 END UPGRADE_MC_TRANSACTIONS;
1571
1572
1573 /* Called from concurrent program*/
1574 PROCEDURE CON_UPGRADE_TRANSACTIONS
1575 IS
1576
1577 l_batch_id number;
1578 l_start_rowid rowid;
1579 l_end_rowid rowid;
1580 l_rows_processed number :=0;
1581 BEGIN
1582
1583 SELECT XLA_UPG_BATCHES_S.nextval
1584 INTO l_batch_id
1585 FROM DUAL;
1586
1587 SELECT MIN(ROWID), MAX(ROWID)
1588 INTO l_start_rowid, l_end_rowid
1589 FROM PA_DRAFT_REVENUES_ALL;
1590
1591
1592 UPGRADE_TRANSACTIONS(p_batch_id => l_batch_id,
1593 p_start_rowid => l_start_rowid,
1594 p_end_rowid => l_end_rowid,
1595 p_rows_process => l_rows_processed);
1596
1597 COMMIT;
1598
1599
1600 SELECT MIN(ROWID), MAX(ROWID)
1601 INTO l_start_rowid, l_end_rowid
1602 FROM PA_MC_DRAFT_REVS_ALL;
1603
1604
1605 UPGRADE_MC_TRANSACTIONS(p_batch_id => l_batch_id,
1606 p_start_rowid => l_start_rowid,
1607 p_end_rowid => l_end_rowid,
1608 p_rows_process => l_rows_processed);
1609
1610 COMMIT;
1611
1612 EXCEPTION
1613 WHEN NO_DATA_FOUND THEN
1614 RAISE;
1615
1616 WHEN OTHERS THEN
1617 RAISE;
1618
1619 END CON_UPGRADE_TRANSACTIONS;
1620
1621 END PA_BILL_REV_XLA_UPGRADE;