[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.33.12000000.2 2007/09/18 07:08:46 pvishnoi 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
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
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,
451 balance_type_code,
452 je_category_name,
453 gl_transfer_status_code,
454 gl_transfer_date,
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,
603 amount_cr,
604 amount_cr,
605 currency_code,
606 amount_dr,
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
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,
740 'REVENUE_ALL' event_type_code,
741 decode(dr.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
742 'N' gain_or_loss_flag,
743 dr.project_id project_id,
744 erdl.draft_revenue_num dr_num,
745 erdl.draft_revenue_item_line_num dr_line_num,
746 ev.event_id source_num1,
747 1 source_num2,
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 dr.created_by =-99999
801 WHERE dr.rowid >= p_start_rowid
802 AND dr.rowid <= p_end_rowid
803 AND dr.event_id is null
804 AND dr.transfer_Status_code='A'
805 AND EXISTS (SELECT 'X'
806 FROM PA_XLA_DRAFT_REV_TMP tmp
807 WHERE dr.rowid = tmp.rev_rowid);
808
809 p_rows_process := p_rows_process + SQL%ROWCOUNT;
810
811 UPDATE XLA_AE_LINES lin
812 SET lin.gl_sl_link_id = (SELECT gl_sl_link_id
813 FROM GL_IMPORT_REFERENCES imp,
814 PA_REV_AE_LINES_TMP tmp
815 WHERE imp.reference_6 = tmp.gl_batch_name
816 AND imp.reference_2 = tmp.code_combination_id
817 AND imp.reference_3 = tmp.dist_type
818 AND lin.ae_header_id = tmp.ae_header_id
819 AND lin.ae_line_num = tmp.ae_line_num
820 AND imp.gl_sl_link_id is not null /*Bug 5168431*/
821 AND rownum =1)
822 WHERE EXISTS ( SELECT 1
823 FROM PA_REV_AE_LINES_TMP tmp1
824 WHERE lin.ae_header_id = tmp1.ae_header_id
825 AND lin.ae_line_num = tmp1.ae_line_num)
826 AND lin.application_id = 275
827 AND lin.gl_sl_link_id is null
828 AND lin.upg_batch_id = p_batch_id;
829
830
831
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 p_rows_process :=0;
835 RAISE;
836
837 WHEN OTHERS THEN
838 p_rows_process :=0;
839 RAISE;
840
841 END UPGRADE_TRANSACTIONS;
842
843
844 PROCEDURE REV_UPG_MC_AD_PAR( p_table_owner IN VARCHAR2,
845 p_table_name IN VARCHAR2,
846 p_script_name IN VARCHAR2,
847 p_num_workers IN NUMBER,
848 p_worker_id IN NUMBER,
849 p_batch_size IN NUMBER,
850 p_batch_id IN NUMBER)
851 IS
852
853 l_start_rowid ROWID;
854 l_end_rowid ROWID;
855
856 l_any_rows_to_process BOOLEAN;
857 l_rows_processed NUMBER;
858 BEGIN
859
860 ad_parallel_updates_pkg.initialize_rowid_range(
861 ad_parallel_updates_pkg.ROWID_RANGE,
862 p_table_owner,
863 p_table_name,
864 p_script_name,
865 p_worker_id,
866 p_num_workers,
867 p_batch_size, 0);
868
869 ------ Get rowid ranges ------
870 ad_parallel_updates_pkg.get_rowid_range(
871 l_start_rowid,
872 l_end_rowid,
873 l_any_rows_to_process,
874 p_batch_size,
875 TRUE);
876
877 WHILE ( l_any_rows_to_process = TRUE )
878 LOOP
879
880 PA_BILL_REV_XLA_UPGRADE.UPGRADE_MC_TRANSACTIONS(
881 p_start_rowid => l_start_rowid,
882 p_end_rowid => l_end_rowid,
883 p_batch_id => p_batch_id,
884 p_rows_process => l_rows_processed);
885
886 ad_parallel_updates_pkg.processed_rowid_range(
887 l_rows_processed,
888 l_end_rowid);
889
890 COMMIT;
891
892 ad_parallel_updates_pkg.get_rowid_range(
893 l_start_rowid,
894 l_end_rowid,
895 l_any_rows_to_process,
896 p_batch_size,
897 FALSE);
898
899 END LOOP ; /* end of WHILE loop */
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 RAISE;
904 END;
905
906
907
908 PROCEDURE UPGRADE_MC_TRANSACTIONS( p_start_rowid IN ROWID,
909 p_end_rowid IN ROWID,
910 p_batch_id IN NUMBER,
911 p_rows_process OUT NOCOPY NUMBER) IS
912
913 l_creation_date date :=sysdate;
914 l_created_by number(15) := 2; --Bug 6319424: Commented '-2005'
915 l_last_update_date date := sysdate;
916 l_last_updated_by number(15) := 2; --Bug 6319424: Commented '-2005'
917 l_last_update_login number(15):= 2; --Bug 6319424: Commented '-2005'
918 l_rows_processed NUMBER :=0;
919 BEGIN
920
921 l_rows_processed := 0;
922
923 INSERT ALL /*+ rowid(mc) leading(mc) */
924 WHEN (mc_unbilled_receivable_dr <> 0
925 and unbilled_code_combination_id is not null
926 and currency_code is not null)THEN /*5455002*/
927 INTO PA_XLA_LINES_TMP
928 ( project_id,
929 draft_revenue_num,
930 ae_header_id,
931 xla_event_id,
932 code_combination_id,
933 amount_cr,
934 amount_dr ,
935 currency_code,
936 entered_cr,
937 entered_dr,
938 gl_batch_name,
939 gl_category,
940 position,
941 accounting_date,
942 ledger_id,
943 conversion_date,
944 adjusted_flag)
945 values
946 ( project_id,
947 draft_revenue_num,
948 xla_ae_headers_s.nextval,
949 xla_events_s.nextval,
950 unbilled_code_combination_id,
951 decode(sign(mc_unbilled_receivable_dr),
952 -1, abs(mc_unbilled_receivable_dr), ''),
953 decode(sign(mc_unbilled_receivable_dr),
954 1, abs(mc_unbilled_receivable_dr), ''),
955 currency_code,
956 decode(sign(unbilled_receivable_dr),
957 -1, abs(unbilled_receivable_dr), ''),
958 decode(sign(unbilled_receivable_dr),
959 1, abs(unbilled_receivable_dr), ''),
960 unbilled_batch_name,
961 'Revenue - UBR',
962 3,
963 gl_date,
964 rep_set_of_books_id,
965 gl_date,
966 adjusted_flag
967 )
968 WHEN (mc_unearned_revenue_cr <> 0
969 and unearned_code_combination_id is not null
970 and currency_code is not null) THEN /*5455002*/
971 INTO PA_XLA_LINES_TMP
972 ( project_id,
973 draft_revenue_num,
974 ae_header_id,
975 xla_event_id,
976 code_combination_id,
977 amount_cr,
978 amount_dr ,
979 currency_code,
980 entered_cr,
981 entered_dr,
982 gl_batch_name,
983 gl_category,
984 position,
985 accounting_date,
986 ledger_id,
987 conversion_date,
988 adjusted_flag)
989 values
990 ( project_id,
991 draft_revenue_num,
992 xla_ae_headers_s.nextval,
993 xla_events_s.nextval,
994 unearned_code_combination_id,
995 decode(sign(mc_unearned_revenue_cr),
996 1, abs(mc_unearned_revenue_cr), ''),
997 decode(sign(mc_unearned_revenue_cr),
998 -1, abs(mc_unearned_revenue_cr), ''),
999 currency_code,
1000 decode(sign(unearned_revenue_cr),
1001 1, abs(unearned_revenue_cr), ''),
1002 decode(sign(unearned_revenue_cr),
1003 -1, abs(unearned_revenue_cr), ''),
1004 unearned_batch_name,
1005 'Revenue - UER',
1006 4,
1007 gl_date,
1008 rep_set_of_books_id,
1009 gl_date,
1010 adjusted_flag
1011 )
1012 WHEN (mc_realized_gains_amount <> 0
1013 and realized_gains_ccid is not null
1014 and currency_code is not null) THEN /*5455002*/
1015 INTO PA_XLA_LINES_TMP
1016 ( project_id,
1017 draft_revenue_num,
1018 ae_header_id,
1019 xla_event_id,
1020 code_combination_id,
1021 amount_cr,
1022 amount_dr ,
1023 currency_code,
1024 entered_cr,
1025 entered_dr,
1026 gl_batch_name,
1027 gl_category,
1028 position,
1029 accounting_date,
1030 ledger_id,
1031 conversion_date,
1032 adjusted_flag)
1033 values
1034 ( project_id,
1035 draft_revenue_num,
1036 xla_ae_headers_s.nextval,
1037 xla_events_s.nextval,
1038 realized_gains_ccid,
1039 decode(sign(mc_realized_gains_amount),
1040 1, abs(mc_realized_gains_amount),''),
1041 decode(sign(mc_realized_gains_amount),
1042 -1, abs(mc_realized_gains_amount), ''),
1043 currency_code,
1044 decode(sign(realized_gains_amount),
1045 1, abs(realized_gains_amount),''),
1046 decode(sign(realized_gains_amount),
1047 -1, abs(realized_gains_amount), ''),
1048 realized_gains_batch_name,
1049 'Revenue - Realized Gains',
1050 5,
1051 gl_date,
1052 rep_set_of_books_id,
1053 gl_date,
1054 adjusted_Flag
1055 )
1056 WHEN (mc_realized_losses_amount <> 0
1057 and realized_losses_ccid is not null
1058 and currency_code is not null)THEN /*5455002*/
1059 INTO PA_XLA_LINES_TMP
1060 ( project_id,
1061 draft_revenue_num,
1062 ae_header_id,
1063 xla_event_id,
1064 code_combination_id,
1065 amount_cr,
1066 amount_dr ,
1067 currency_code,
1068 entered_cr,
1069 entered_dr,
1070 gl_batch_name,
1071 gl_category,
1072 position,
1073 accounting_date,
1074 ledger_id,
1075 conversion_date,
1076 adjusted_flag)
1077 values
1078 ( project_id,
1079 draft_revenue_num,
1080 xla_ae_headers_s.nextval,
1081 xla_events_s.nextval,
1082 realized_losses_ccid,
1083 decode(sign(mc_realized_losses_amount),
1084 1, abs(mc_realized_losses_amount), ''),
1085 decode(sign(mc_realized_losses_amount),
1086 -1, abs(mc_realized_losses_amount),''),
1087 currency_code,
1088 decode(sign(realized_losses_amount),
1089 1, abs(realized_losses_amount), ''),
1090 decode(sign(realized_losses_amount),
1091 -1, abs(realized_losses_amount),''),
1092 realized_losses_batch_name,
1093 'Revenue - Realized Losses',
1094 6,
1095 gl_date,
1096 rep_set_of_books_id,
1097 gl_date,
1098 adjusted_flag
1099 )
1100 WHEN 1 = 1 THEN
1101 INTO PA_XLA_DRAFT_REV_TMP
1102 (REV_ROWID,
1103 PROJECT_ID,
1104 DRAFT_REVENUE_NUM,
1105 AE_HEADER_ID,
1106 XLA_EVENT_ID,
1107 CURRENCY_CODE,
1108 ACCOUNTING_DATE,
1109 LEDGER_ID,
1110 REP_SET_OF_BOOKS_ID,
1111 adjusted_flag)
1112 values
1113 (rev_rowid,
1114 PROJECT_ID,
1115 DRAFT_REVENUE_NUM,
1116 xla_ae_headers_s.nextval,
1117 event_id,
1118 currency_code,
1119 gl_date,
1120 ledger_id,
1121 rep_set_of_books_id,
1122 adjusted_flag)
1123
1124 INTO XLA_AE_HEADERS
1125 (upg_batch_id,
1126 upg_source_application_id,
1127 application_id,
1128 amb_context_code,
1129 entity_id,
1130 event_id,
1131 event_type_code,
1132 ae_header_id,
1133 ledger_id,
1134 accounting_date,
1135 period_name,
1136 balance_type_code,
1137 je_category_name,
1138 gl_transfer_status_code,
1139 gl_transfer_date,
1140 accounting_entry_status_code,
1141 accounting_entry_type_code,
1142 creation_date,
1143 created_by,
1144 last_update_date,
1145 last_updated_by,
1146 last_update_login,
1147 program_update_date,
1148 program_id,
1149 program_application_id,
1150 upg_valid_flag)
1151 VALUES
1152 (batch_id,
1153 upg_source_app_id,
1154 app_id,
1155 'DEFAULT',
1156 entity_id,
1157 event_id,
1158 decode(adjusted_flag,'Y','REVENUE_ADJ','REVENUE'),
1159 xla_ae_headers_s.nextval,
1160 rep_set_of_books_id,
1161 gl_date,
1162 gl_period_name,
1163 'A',
1164 'Revenue',
1165 decode(trans_status_code,'A','Y','N'),
1166 decode(trans_status_code,'A',trans_date,NULL),
1167 'F',
1168 'STANDARD',
1169 l_creation_date,
1170 l_created_by,
1171 l_last_update_date,
1172 l_last_updated_by,
1173 l_last_update_login,
1174 l_creation_date,
1175 -2005,
1176 275,
1177 'Y'
1178 )select /*+ rowid(mc) leading(mc) */
1179 mc.rowid rev_rowid,
1180 p_batch_id batch_id,
1181 275 upg_source_app_id,
1182 275 app_id,
1183 mc.set_of_books_id rep_set_of_books_id,
1184 hr.org_information2 legal_entity_id,
1185 dr.gl_date gl_date,
1186 dr.gl_period_name gl_period_name,
1187 evt.event_id event_id,
1188 evt.entity_id entity_id,
1189 mc.project_id project_id,
1190 mc.draft_revenue_num draft_revenue_num,
1191 mc.transferred_date trans_date,
1192 dr.unbilled_receivable_dr unbilled_receivable_dr,
1193 dr.unearned_revenue_cr unearned_revenue_cr,
1194 mc.unbilled_receivable_dr mc_unbilled_receivable_dr,
1195 mc.unearned_revenue_cr mc_unearned_revenue_cr,
1196 dr.unbilled_code_combination_id unbilled_code_combination_id,
1197 dr.unearned_code_combination_id unearned_code_combination_id,
1198 mc.unbilled_batch_name unbilled_batch_name,
1199 mc.unearned_batch_name unearned_batch_name,
1200 dr.realized_gains_amount realized_gains_amount,
1201 dr.realized_losses_amount realized_losses_amount,
1202 mc.realized_gains_amount mc_realized_gains_amount,
1203 mc.realized_losses_amount mc_realized_losses_amount,
1204 dr.realized_gains_ccid realized_gains_ccid,
1205 dr.realized_losses_ccid realized_losses_ccid,
1206 mc.realized_gains_batch_name realized_gains_batch_name,
1207 mc.realized_losses_batch_name realized_losses_batch_name,
1208 pa.projfunc_currency_code currency_code,
1209 mc.transfer_status_code trans_status_code,
1210 imp.set_of_books_id ledger_id,
1211 decode(dr.draft_revenue_num_credited,null,'N','Y') adjusted_flag
1212 from pa_draft_revenues_all dr,
1213 pa_implementations_all imp,
1214 pa_mc_draft_revs_all mc,
1215 XLA_EVENTS evt,
1216 pa_xla_upg_ctrl gl,
1217 pa_projects_all pa,
1218 hr_organization_information hr
1219 where mc.rowid >= p_start_rowid
1220 and mc.rowid <= p_end_rowid
1221 and dr.project_id = mc.project_id
1222 and dr.draft_revenue_num = mc.draft_revenue_num
1223 and nvl(mc.xla_migrated_flag,'N') ='N'
1224 -- and mc.transfer_status_code = 'A'
1225 and gl.status = 'P'
1226 and gl.reference = 'GL_PERIOD_STATUSES'
1227 and gl.batch_id = p_batch_id
1228 and gl.ledger_id = imp.set_of_books_id
1229 and dr.gl_date between to_date(gl.min_value,'J') and to_date(gl.max_value,'J')
1230 and dr.org_id = imp.org_id
1231 and dr.event_id = evt.event_id
1232 and pa.project_id = mc.project_id
1233 and hr.organization_id = imp.org_id
1234 and hr.org_information_context = 'Operating Unit Information';
1235
1236 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1237
1238 INSERT ALL
1239 WHEN 1 = 1 THEN
1240 INTO PA_REV_AE_LINES_TMP
1241 (ae_header_id,
1242 ae_line_num,
1243 gl_batch_name,
1244 code_combination_id,
1245 dist_type)
1246 values
1247 (
1248 ae_header_id,
1249 line_num,
1250 gl_batch_name,
1251 code_combination_id,
1252 gl_category
1253 )
1254 INTO XLA_AE_LINES
1255 (upg_batch_id,
1256 ae_header_id,
1257 ae_line_num,
1258 application_id,
1259 code_combination_id,
1260 gl_transfer_mode_code,
1261 accounted_dr,
1262 unrounded_accounted_dr,
1263 accounted_cr,
1264 unrounded_accounted_cr,
1265 currency_code,
1266 entered_dr,
1267 unrounded_entered_dr,
1268 entered_cr,
1269 unrounded_entered_cr,
1270 currency_conversion_date,
1271 currency_conversion_rate,
1272 currency_conversion_type,
1273 description,
1274 accounting_class_code,
1275 gl_sl_link_id,
1276 gl_sl_link_table,
1277 creation_date,
1278 created_by,
1279 last_update_date,
1280 last_updated_by,
1281 last_update_login,
1282 program_update_date,
1283 program_id,
1284 program_application_id,
1285 request_id,
1286 gain_or_loss_flag,
1287 accounting_date,
1288 ledger_id,
1289 business_class_code
1290 )
1291 VALUES
1292 ( batch_id,
1293 ae_header_id,
1294 line_num,
1295 275,
1296 code_combination_id,
1297 'S',
1298 amount_dr,
1299 amount_dr,
1300 amount_cr,
1301 amount_cr,
1302 currency_code,
1303 entered_dr,
1304 entered_dr,
1305 entered_cr,
1306 entered_cr,
1307 conversion_date,
1308 exchange_rate,
1309 rate_type,
1310 'Project Revenue',
1311 account_class,
1312 gl_sl_link_id,
1313 'XLAJEL',
1314 l_creation_date,
1315 l_created_by,
1316 l_last_update_date,
1317 l_last_updated_by,
1318 l_last_update_login,
1319 l_creation_date,
1320 -2005,
1321 275,
1322 '',
1323 gain_or_loss_flag,
1324 accounting_date,
1325 ledger_id,
1326 'PA_REV_ADJ')
1327 INTO XLA_DISTRIBUTION_LINKS
1328 (APPLICATION_ID,
1329 EVENT_ID,
1330 AE_HEADER_ID,
1331 AE_LINE_NUM,
1332 SOURCE_DISTRIBUTION_TYPE,
1333 SOURCE_DISTRIBUTION_ID_NUM_1,
1334 SOURCE_DISTRIBUTION_ID_NUM_2,
1335 MERGE_DUPLICATE_CODE,
1336 EVENT_TYPE_CODE,
1337 EVENT_CLASS_CODE,
1338 UPG_BATCH_ID,
1339 REF_AE_HEADER_ID,
1340 LINE_DEFINITION_CODE,
1341 temp_line_num,
1342 unrounded_accounted_dr,
1343 unrounded_accounted_cr,
1344 unrounded_entered_dr,
1345 unrounded_entered_cr,
1346 rounding_class_code)
1347 VALUES
1348 (275,
1349 event_id,
1350 ae_header_id,
1351 line_num,
1352 gl_category,
1353 source_num1,
1354 source_num2,
1355 'N',
1356 event_type_code,
1357 event_class_code,
1358 batch_id,
1359 ae_header_id,
1360 'PA_ACCRUAL_ACCOUNTING',
1361 line_num,
1362 amount_dr,
1363 amount_cr,
1364 entered_dr,
1365 entered_cr,
1366 account_class
1367 )
1368 select
1369 p_batch_id AS batch_id,
1370 ae_header_id AS ae_header_id,
1371 event_id AS event_id,
1372 account_class AS account_class,
1373 code_combination_id AS code_combination_id,
1374 amount_dr AS amount_dr,
1375 amount_cr AS amount_cr,
1376 entered_dr AS entered_dr,
1377 entered_cr AS entered_cr,
1378 currency_code AS currency_code,
1379 conversion_date AS conversion_date,
1380 exchange_rate AS exchange_rate,
1381 rate_type AS rate_type,
1382 gain_or_loss_flag AS gain_or_loss_flag,
1383 event_type_code AS event_type_code,
1384 event_class_code AS event_class_code,
1385 source_num1,
1386 source_num2,
1387 gl_sl_link_id,
1388 gl_batch_name,
1389 gl_category,
1390 accounting_date,
1391 ledger_id,
1392 RANK() OVER (PARTITION BY ae_header_id
1393 ORDER BY position,row_num) AS line_num
1394 FROM
1395 ( SELECT 1 position,
1396 mc.ae_header_id ae_header_id,
1397 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') account_class,
1398 crdl.code_combination_id code_combination_id,
1399 decode(sign(mcrdl.amount),
1400 1, abs(mcrdl.amount),
1401 '') amount_cr,
1402 decode(sign(mcrdl.amount),
1403 -1, abs(mcrdl.amount),
1404 '') amount_dr,
1405 decode(sign(crdl.amount),
1406 1, abs(crdl.amount),
1407 '') entered_cr,
1408 decode(sign(crdl.amount),
1409 -1, abs(crdl.amount),
1410 '') entered_dr,
1411 nvL(crdl.revproc_currency_code,crdl.projfunc_currency_code) currency_code,
1412 mcrdl.conversion_date AS conversion_date,
1413 mcrdl.exchange_rate AS exchange_rate,
1414 mcrdl.rate_type AS rate_type,
1415 mc.xla_event_id event_id,
1416 'REVENUE_ALL' event_type_code,
1417 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1418 'N' gain_or_loss_flag,
1419 mc.project_id project_id,
1420 mc.draft_revenue_num dr_num,
1421 mcrdl.DRAFT_REVENUE_ITEM_LINE_NUM dr_line_num,
1422 mcrdl.expenditure_item_id source_num1,
1423 mcrdl.line_num source_num2,
1424 null gl_sl_link_id,
1425 mcrdl.BATCH_NAME gl_batch_name,
1426 'Revenue - Normal Revenue' gl_category,
1427 rownum row_num,
1428 mc.accounting_date accounting_date,
1429 mc.REP_SET_OF_BOOKS_ID ledger_id
1430 FROM PA_CUST_REV_DIST_LINES_ALL CRDL,
1431 PA_MC_CUST_RDL_ALL mcrdl,
1432 PA_XLA_DRAFT_REV_TMP MC
1433 WHERE mc.project_id = mcrdl.project_id
1434 AND mc.draft_revenue_num = mcrdl.draft_revenue_num
1435 AND mc.rep_set_of_books_id = mcrdl.set_of_books_id
1436 AND mcrdl.expenditure_item_id = crdl.expenditure_item_id
1437 AND mcrdl.line_num = crdl.line_num
1438 AND NVL(mcrdl.amount, 0) <> 0
1439 AND crdl.code_combination_id is not null /*Bug 5455002*/
1440 AND nvl(crdl.revproc_currency_code,crdl.projfunc_currency_code) is not null /*5441521*/
1441 UNION ALL
1442 SELECT /*+ /*+ USE_NL(MC,MCERDL,EV)*/
1443 2 position,
1444 mc.ae_header_id ae_header_id,
1445 DECODE(et.event_type_classification,
1446 'WRITE OFF','EVENT_WO_REVENUE',
1447 decode(mc.adjusted_flag,'N','REVENUE',
1448 'REVENUE_ADJ')) account_class,
1449 erdl.code_combination_id code_combination_id,
1450 decode(sign(mcerdl.amount),
1451 1, abs(mcerdl.amount),
1452 '') amount_cr,
1453 decode(sign(mcerdl.amount),
1454 -1, abs(mcerdl.amount),
1455 '') amount_dr,
1456 decode(sign(erdl.amount),
1457 1, abs(erdl.amount),
1458 '') entered_cr,
1459 decode(sign(erdl.amount),
1460 -1, abs(erdl.amount),
1461 '') entered_dr,
1462 nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) currency_code,
1463 mcerdl.conversion_date AS conversion_date,
1464 mcerdl.exchange_rate AS exchange_rate,
1465 mcerdl.rate_type AS rate_type,
1466 mc.xla_event_id event_id,
1467 'REVENUE_ALL' event_type_code,
1468 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1469 'N' gain_or_loss_flag,
1470 mc.project_id project_id,
1471 erdl.draft_revenue_num dr_num,
1472 erdl.draft_revenue_item_line_num dr_line_num,
1473 ev.event_id source_num1,
1474 1 source_num2,
1475 null gl_sl_link_id,
1476 mcerdl.BATCH_NAME gl_batch_name,
1477 'Revenue - Event Revenue' gl_category,
1478 rownum row_num,
1479 mc.accounting_date accounting_date,
1480 mc.REP_SET_OF_BOOKS_ID ledger_id
1481 FROM PA_CUST_EVENT_RDL_ALL erdl,
1482 PA_MC_CUST_EVENT_RDL_ALL mcerdl,
1483 PA_EVENTS ev,
1484 PA_XLA_DRAFT_REV_TMP MC,
1485 PA_EVENT_TYPES et
1486 WHERE mc.project_id = mcerdl.project_id
1487 AND mc.draft_revenue_num = mcerdl.draft_revenue_num
1488 AND mc.rep_set_of_books_id = mcerdl.set_of_books_id
1489 AND NVL(mcerdl.amount, 0) <> 0
1490 AND mcerdl.project_id = erdl.project_id
1491 AND nvl(mcerdl.task_id,-99) = nvl(erdl.task_id,-99)
1492 AND mcerdl.event_num = erdl.event_num
1493 AND mcerdl.project_id = ev.project_id
1494 AND nvl(mcerdl.task_id,-99) = nvl(ev.task_id,-99)
1495 AND mcerdl.event_num = ev.event_num
1496 AND ev.event_type = et.event_type
1497 AND erdl.code_combination_id is not null /*Bug 5455002*/
1498 AND nvl(erdl.revproc_currency_code,erdl.projfunc_currency_code) is not null /*5441521*/
1499 UNION ALL
1500 select position,
1501 mc.ae_header_id ae_header_id,
1502 Decode(position,3,'UNBILL',
1503 4,'UNEARNED_REVENUE',
1504 5,'REALIZED_GAINS',
1505 6,'REALIZED_LOSS') account_class,
1506 code_combination_id,
1507 amount_cr,
1508 amount_dr,
1509 entered_cr,
1510 entered_dr,
1511 mc.currency_code currency_code,
1512 mc.conversion_date AS conversion_date,
1513 amount_cr/entered_cr AS exchange_rate,
1514 'User' AS rate_type,
1515 mc.xla_event_id event_id,
1516 'REVENUE_ALL' event_type_code,
1517 decode(mc.adjusted_flag,'N','REVENUE','REVENUE_ADJ') event_class_code,
1518 'N' gain_or_loss_flag,
1519 mc.project_id project_id,
1520 mc.draft_revenue_num dr_num,
1521 1 dr_line_num,
1522 mc.project_id source_num1,
1523 mc.draft_revenue_num source_num2,
1524 null gl_sl_link_id,
1525 gl_batch_name,
1526 gl_category,
1527 rownum row_num,
1528 accounting_date,
1529 ledger_id
1530 FROM PA_XLA_LINES_TMP MC );
1531
1532 UPDATE PA_MC_DRAFT_REVS_ALL mc
1533 SET mc.xla_migrated_flag = 'Y'
1534 WHERE mc.rowid >= p_start_rowid
1535 AND mc.rowid <= p_end_rowid
1536 AND nvl(xla_migrated_flag,'N') = 'N'
1537 AND EXISTS (SELECT 'X'
1538 FROM PA_XLA_DRAFT_REV_TMP tmp
1539 WHERE mc.rowid = tmp.rev_rowid);
1540
1541 p_rows_process := p_rows_process + SQL%ROWCOUNT;
1542
1543 UPDATE XLA_AE_LINES lin
1544 SET lin.gl_sl_link_id = (select gl_sl_link_id
1545 FROM GL_IMPORT_REFERENCES imp,
1546 PA_REV_AE_LINES_TMP tmp
1547 WHERE imp.reference_6 = tmp.gl_batch_name
1548 AND imp.reference_2 = tmp.code_combination_id
1549 AND imp.reference_3 = tmp.dist_type
1550 AND lin.ae_header_id = tmp.ae_header_id
1551 AND lin.ae_line_num = tmp.ae_line_num
1552 AND imp.gl_sl_link_id is not null /*Bug 5168431*/
1553 AND rownum=1)
1554 WHERE EXISTS ( SELECT 1
1555 FROM PA_REV_AE_LINES_TMP tmp1
1556 WHERE lin.ae_header_id = tmp1.ae_header_id
1557 AND lin.ae_line_num = tmp1.ae_line_num)
1558 AND lin.application_id = 275
1559 AND lin.gl_sl_link_id is null
1560 AND lin.upg_batch_id = p_batch_id;
1561
1562 EXCEPTION
1563 WHEN NO_DATA_FOUND THEN
1564 RAISE;
1565
1566 WHEN OTHERS THEN
1567 RAISE;
1568
1569 END UPGRADE_MC_TRANSACTIONS;
1570
1571
1572 /* Called from concurrent program*/
1573 PROCEDURE CON_UPGRADE_TRANSACTIONS
1574 IS
1575
1576 l_batch_id number;
1577 l_start_rowid rowid;
1578 l_end_rowid rowid;
1579 l_rows_processed number :=0;
1580 BEGIN
1581
1582 SELECT XLA_UPG_BATCHES_S.nextval
1583 INTO l_batch_id
1584 FROM DUAL;
1585
1586 SELECT MIN(ROWID), MAX(ROWID)
1587 INTO l_start_rowid, l_end_rowid
1588 FROM PA_DRAFT_REVENUES_ALL;
1589
1590
1591 UPGRADE_TRANSACTIONS(p_batch_id => l_batch_id,
1592 p_start_rowid => l_start_rowid,
1593 p_end_rowid => l_end_rowid,
1594 p_rows_process => l_rows_processed);
1595
1596 COMMIT;
1597
1598
1599 SELECT MIN(ROWID), MAX(ROWID)
1600 INTO l_start_rowid, l_end_rowid
1601 FROM PA_MC_DRAFT_REVS_ALL;
1602
1603
1604 UPGRADE_MC_TRANSACTIONS(p_batch_id => l_batch_id,
1605 p_start_rowid => l_start_rowid,
1606 p_end_rowid => l_end_rowid,
1607 p_rows_process => l_rows_processed);
1608
1609 COMMIT;
1610
1611 EXCEPTION
1612 WHEN NO_DATA_FOUND THEN
1613 RAISE;
1614
1615 WHEN OTHERS THEN
1616 RAISE;
1617
1618 END CON_UPGRADE_TRANSACTIONS;
1619
1620 END PA_BILL_REV_XLA_UPGRADE;