[Home] [Help]
PACKAGE BODY: APPS.PA_XLA_UPGRADE
Source
1 PACKAGE BODY PA_XLA_UPGRADE AS
2 /* $Header: PACOXLUB.pls 120.25.12000000.2 2007/09/18 07:08:02 pvishnoi ship $ */
3
4 PROCEDURE UPGRADE_COST_XCHARGE (p_table_owner IN VARCHAR2,
5 p_script_name IN VARCHAR2,
6 p_worker_id IN NUMBER,
7 p_num_workers IN NUMBER,
8 p_batch_size IN NUMBER,
9 p_min_eiid IN NUMBER,
10 p_max_eiid IN NUMBER,
11 p_upg_batch_id IN NUMBER,
12 p_mode IN VARCHAR2,
13 p_cost_cross IN VARCHAR2)
14 IS
15
16 -----------------------------------------------------
17 -- Ad parallelization variables
18 -----------------------------------------------------
19 l_table_name varchar2(30) := 'PA_EXPENDITURE_ITEMS_ALL';
20 l_rows_processed number;
21 l_start_eiid number;
22 l_end_eiid number;
23 l_any_rows_to_process boolean;
24 -----------------------------------------------------
25
26 l_pa_app_id number := 275;
27 l_user number := 2; --Bug 6319424: Commented '-2005'
28 l_request_id number := null;
29 l_date date := sysdate;
30
31 BEGIN
32
33 ad_parallel_updates_pkg.initialize_id_range(
34 ad_parallel_updates_pkg.ID_RANGE_SUB_RANGE,
35 p_table_owner,
36 l_table_name,
37 p_script_name,
38 'EXPENDITURE_ITEM_ID',
39 p_worker_id,
40 p_num_workers,
41 p_batch_size,
42 0,
43 null,
44 p_min_eiid,
45 p_max_eiid);
46
47
48 ad_parallel_updates_pkg.get_id_range(
49 l_start_eiid,
50 l_end_eiid,
51 l_any_rows_to_process,
52 p_batch_size,
53 TRUE);
54
55
56 WHILE ( l_any_rows_to_process = TRUE ) LOOP
57
58 l_rows_processed := 0;
59
60 /* First fetch all the CDLs and CCDLs to be upgraded into a global temp table.
61
62 First insert is for Debit records and Second insert is for Credit records.
63
64 Position is used to derive the ae_line_num later and maintain its
65 integrity within a (ae header + expenditure_item_id + cdl_line_num) combination.
66 It is also used to identify the Dr and Cr record.
67
68 Order_Line_Num is used to ensure creation of one entity per expenditure item id.
69
70 The check for system_linkage_function is required since cdl batch_name not null is not a
71 sufficient criteria to identify all the valid CDLs as ER will also have not null batch name.
72
73 We need to use the ROW_NUMBER only since we are getting data from both CDL and
74 CCDL table which can result into the same cdl_line_num for a given expenditure_item_id */
75
76
77 INSERT WHEN cdl_line_type in ('R', 'D', 'BL', 'PC')
78 THEN INTO PA_XLA_UPG_LINES_GT
79 (legal_entity_id,
80 ledger_id,
81 org_id,
82 expenditure_item_id,
83 cdl_line_num,
84 cdl_line_type,
85 grouped_line_type,
86 gl_date,
87 gl_period_name,
88 batch_name,
89 code_combination_id,
90 acct_raw_cost,
91 denom_raw_cost,
92 denom_currency_code,
93 tp_amt_type_code,
94 je_category,
95 event_type_code,
96 event_class_code,
97 order_line_num,
98 position,
99 REFERENCE_2,
100 REFERENCE_3
101 )
102 VALUES
103 (legal_entity_id,
104 ledger_id,
105 org_id,
106 expenditure_item_id,
107 cdl_line_num,
108 cdl_line_type,
109 grouped_line_type,
110 gl_date,
111 gl_period_name,
112 batch_name,
113 dr_code_combination_id,
114 acct_raw_cost,
115 denom_raw_cost,
116 denom_currency_code,
117 tp_amt_type_code,
118 je_category,
119 event_type_code,
120 event_class_code,
121 order_line_num,
122 1,
123 DR_REFERENCE_2,
124 DR_REFERENCE_3
125 )
126 WHEN cdl_line_type in ('R', 'C', 'BL', 'PC')
127 THEN INTO PA_XLA_UPG_LINES_GT
128 (legal_entity_id,
129 ledger_id,
130 org_id,
131 expenditure_item_id,
132 cdl_line_num,
133 cdl_line_type,
134 grouped_line_type,
135 gl_date,
136 gl_period_name,
137 batch_name,
138 code_combination_id,
139 acct_raw_cost,
140 denom_raw_cost,
141 denom_currency_code,
142 tp_amt_type_code,
143 je_category,
144 event_type_code,
145 event_class_code,
146 order_line_num,
147 position,
148 REFERENCE_2,
149 REFERENCE_3
150 )
151 VALUES
152 (legal_entity_id,
153 ledger_id,
154 org_id,
155 expenditure_item_id,
156 cdl_line_num,
157 cdl_line_type,
158 grouped_line_type,
159 gl_date,
160 gl_period_name,
161 batch_name,
162 cr_code_combination_id,
163 acct_raw_cost,
164 denom_raw_cost,
165 denom_currency_code,
166 tp_amt_type_code,
167 je_category,
168 event_type_code,
169 event_class_code,
170 NULL,
171 2 ,
172 CR_REFERENCE_2,
173 CR_REFERENCE_3
174 )
175 SELECT
176 legal_entity_id AS legal_entity_id,
177 ledger_id AS ledger_id,
178 org_id AS org_id,
179 expenditure_item_id AS expenditure_item_id,
180 cdl_line_num AS cdl_line_num,
181 cdl_line_type AS cdl_line_type,
182 grouped_line_type AS grouped_line_type,
183 gl_date AS gl_date,
184 gl_period_name AS gl_period_name,
185 batch_name AS batch_name,
186 dr_code_combination_id AS dr_code_combination_id,
187 cr_code_combination_id AS cr_code_combination_id,
188 acct_raw_cost AS acct_raw_cost,
189 denom_raw_cost AS denom_raw_cost,
190 denom_currency_code AS denom_currency_code,
191 tp_amt_type_code AS tp_amt_type_code,
192 je_category AS je_category,
193 event_type_code AS event_type_code,
194 event_class_code AS event_class_code,
195 ROW_NUMBER() over (partition by expenditure_item_id
196 order by cdl_line_num) AS order_line_num,
197 DR_REFERENCE_2 As DR_REFERENCE_2,
198 CR_REFERENCE_2 As CR_REFERENCE_2,
199 DR_REFERENCE_3 As DR_REFERENCE_3,
200 CR_REFERENCE_3 As CR_REFERENCE_3
201 FROM
202 (
203 select /*+ NO_EXPAND LEADING(CDL,IMP,CTRL) USE_NL(ei,hoi) swap_join_inputs(IMP) swap_join_inputs(CTRL) */
204 to_number(hoi.org_information2) legal_entity_id,
205 imp.set_of_books_id ledger_id,
206 imp.org_id,
207 cdl.expenditure_item_id,
208 cdl.line_num cdl_line_num,
209 cdl.line_type cdl_line_type,
210 decode(cdl.line_type, 'R','R','B') grouped_line_type,
211 cdl.gl_date,
212 cdl.gl_period_name,
213 cdl.batch_name,
214 cdl.dr_code_combination_id,
215 cdl.cr_code_combination_id,
216 decode(cdl.line_type, 'C', -1 * cdl.acct_raw_cost,
217 'D', cdl.acct_raw_cost,
218 'R', decode(ei.system_linkage_function, 'BTC', cdl.acct_burdened_cost,
219 cdl.acct_raw_cost)) acct_raw_cost,
220 decode(cdl.line_type, 'C', -1 * cdl.denom_raw_cost,
221 'D', cdl.denom_raw_cost,
222 'R', decode(ei.system_linkage_function, 'BTC', cdl.denom_burdened_cost,
223 cdl.denom_raw_cost)) denom_raw_cost,
224 cdl.denom_currency_code,
225 null tp_amt_type_code,
226 decode(cdl.line_type, 'C', 'Total Burdened Cost',
227 'D', 'Total Burdened Cost',
228 'R', decode(ei.system_linkage_function, 'BTC','Burden Cost',
229 'INV','Inventory',
230 'ST','Labor Cost',
231 'OT','Labor Cost',
232 'PJ','Miscellaneous Transaction',
233 'USG','Usage Cost',
234 'WIP','WIP')) je_category,
235 decode(cdl.line_type, 'C', 'TOT_BURDENED_COST_DIST',
236 'D', 'TOT_BURDENED_COST_DIST',
237 'R', decode(ei.system_linkage_function, 'BTC','BURDEN_COST_DIST',
238 'INV','INVENTORY_COST_DIST',
239 'ST','LABOR_COST_DIST',
240 'OT','LABOR_COST_DIST',
241 'PJ','MISC_COST_DIST',
242 'USG','USG_COST_DIST',
243 'WIP','WIP_COST_DIST')) || DECODE (ei.adjusted_expenditure_item_id,
244 NULL , '' ,
245 '_ADJ'
246 ) event_type_code,
247 decode(cdl.line_type, 'C', 'TOT_BURDENED_COST',
248 'D', 'TOT_BURDENED_COST',
249 'R', decode(ei.system_linkage_function, 'BTC','BURDEN_COST',
250 'INV','INVENTORY_COST',
251 'ST','LABOR_COST',
252 'OT','LABOR_COST',
253 'PJ','MISC_COST',
254 'USG','USG_COST',
255 'WIP','WIP_COST')) || DECODE (ei.adjusted_expenditure_item_id,
256 NULL , '' ,
257 '_ADJ'
258 ) event_class_code,
259 'Cost' DR_REFERENCE_3,
260 'Liability' CR_REFERENCE_3,
261 to_char(cdl.dr_code_combination_id) DR_REFERENCE_2,
262 to_char('-99') CR_REFERENCE_2
263 FROM PA_COST_DISTRIBUTION_LINES_ALL cdl,
264 PA_EXPENDITURE_ITEMS_ALL ei,
265 PA_IMPLEMENTATIONS_ALL imp,
266 PA_XLA_UPG_CTRL ctrl,
267 HR_ORGANIZATION_INFORMATION hoi
268 WHERE cdl.expenditure_item_id between l_start_eiid and l_end_eiid
269 AND cdl.transfer_status_code = 'A'
270 AND cdl.batch_name is not null
271 AND cdl.acct_event_id is null
272 AND cdl.expenditure_item_id = ei.expenditure_item_id
273 AND (cdl.line_type in ('C','D') OR ei.system_linkage_function in ('BTC','INV','ST','PJ','USG','WIP','OT'))
274 AND cdl.org_id = imp.org_id
275 AND cdl.gl_date between to_date(ctrl.min_value,'J') and to_date (ctrl.max_value,'J')
276 AND ctrl.ledger_id = imp.set_of_books_id
277 AND ctrl.reference = 'GL_PERIOD_STATUSES'
278 AND ctrl.status = 'P'
279 AND ctrl.batch_id = p_upg_batch_id
280 AND hoi.organization_id = imp.org_id
281 AND hoi.org_information_context = 'Operating Unit Information'
282 AND NVL(p_cost_cross,'C') = 'C' /* Bug 5408944 */
283 AND (
284 (
285 cdl.line_type = 'R'
286 and cdl.dr_code_combination_id > 0
287 and cdl.cr_code_combination_id > 0
288 )
289 OR
290 (
291 cdl.line_type = 'D'
292 and cdl.dr_code_combination_id > 0
293 )
294 OR
295 (
296 cdl.line_type = 'C'
297 and cdl.cr_code_combination_id > 0
298 )
299 )
300 UNION ALL
301 SELECT /*+ LEADING(CDL,IMP,CTRL) USE_NL(ei,hoi) swap_join_inputs(IMP) swap_join_inputs(CTRL) */
302 to_number(hoi.org_information2) legal_entity_id,
303 imp.set_of_books_id ledger_id,
304 imp.org_id,
305 cdl.expenditure_item_id,
306 cdl.line_num cdl_line_num,
307 cdl.line_type cdl_line_type,
308 cdl.line_type grouped_line_type,
309 cdl.gl_date,
310 cdl.gl_period_name,
311 cdl.gl_batch_name batch_name,
312 cdl.dr_code_combination_id,
313 cdl.cr_code_combination_id ,
314 cdl.amount acct_raw_cost,
315 cdl.denom_transfer_price denom_raw_cost,
316 cdl.denom_tp_currency_code denom_currency_code,
317 ei.tp_amt_type_code tp_amt_type_code,
318 decode(cdl.line_type, 'BL', 'Borrowed and Lent',
319 'PC', 'Prov Cost Reclass') je_category,
320 decode(cdl.line_type, 'BL', 'BL_DISTRIBUTION',
321 'PC', 'PRVDR_RECVR_RECLASS') || DECODE (ei.adjusted_expenditure_item_id,
322 NULL , '' ,
323 '_ADJ'
324 ) event_type_code,
325 decode(cdl.line_type, 'BL', 'BORROWED_AND_LENT',
326 'PC', 'PRVDR_RECVR_RECLASS') || DECODE (ei.adjusted_expenditure_item_id,
327 NULL , '' ,
328 '_ADJ'
329 ) event_class_code,
330 'Cross Charge Debit' DR_REFERENCE_3,
331 'Cross Charge Credit' CR_REFERENCE_3,
332 to_char(cdl.dr_code_combination_id) DR_REFERENCE_2,
333 to_char(cdl.cr_code_combination_id) CR_REFERENCE_2
334 FROM PA_CC_DIST_LINES_ALL cdl,
335 PA_EXPENDITURE_ITEMS_ALL ei,
336 PA_IMPLEMENTATIONS_ALL imp,
337 PA_XLA_UPG_CTRL ctrl,
338 HR_ORGANIZATION_INFORMATION hoi
339 WHERE cdl.expenditure_item_id between l_start_eiid and l_end_eiid
340 AND cdl.transfer_status_code = 'A'
341 AND cdl.gl_batch_name is not null
342 AND cdl.acct_event_id is null
343 AND cdl.expenditure_item_id = ei.expenditure_item_id
344 AND cdl.org_id = imp.org_id
345 AND cdl.gl_date between to_date(ctrl.min_value,'J') and to_date (ctrl.max_value,'J')
346 AND ctrl.ledger_id = imp.set_of_books_id
347 AND ctrl.reference = 'GL_PERIOD_STATUSES'
348 AND ctrl.status = 'P'
349 AND ctrl.batch_id = p_upg_batch_id
350 AND hoi.organization_id = imp.org_id
351 AND hoi.org_information_context = 'Operating Unit Information'
352 AND NVL(p_cost_cross,'X') = 'X' /* Bug 5408944 */
353 AND cdl.dr_code_combination_id > 0
354 AND cdl.cr_code_combination_id > 0
355 );
356
357 l_rows_processed := SQL%ROWCOUNT;
358
359
360 IF nvl(l_rows_processed,0) > 0 THEN
361
362
363 INSERT ALL INTO XLA_TRANSACTION_ENTITIES_UPG
364 (upg_batch_id,
365 upg_source_application_id,
366 application_id,
367 ledger_id,
368 legal_entity_id,
369 entity_code,
370 source_id_int_1,
371 security_id_int_1,
372 source_application_id,
373 creation_date,
374 created_by,
375 last_update_date,
376 last_updated_by,
377 last_update_login,
378 entity_id,
379 transaction_number)
380 VALUES
381 (p_upg_batch_id ,
382 l_pa_app_id,
383 l_pa_app_id,
384 ledger_id,
385 legal_entity_id,
386 'EXPENDITURES',
387 expenditure_item_id,
388 org_id ,
389 l_pa_app_id,
390 l_date,
391 l_user,
392 l_date,
393 l_user,
394 l_user,
395 XLA_TRANSACTION_ENTITIES_S.nextval,
396 expenditure_item_id)
397 INTO PA_XLA_UPG_ENTITIES_GT
398 (
399 EXPENDITURE_ITEM_ID,
400 ENTITY_ID
401 )
402 VALUES
403 (
404 expenditure_item_id ,
405 XLA_TRANSACTION_ENTITIES_S.nextval
406 )
407 SELECT
408 ledger_id,
409 legal_entity_id,
410 expenditure_item_id,
411 org_id
412 FROM PA_XLA_UPG_LINES_GT lines_gt
413 WHERE order_line_num = 1
414 AND ((p_mode = 'D') OR
415 (p_mode = 'R' AND
416 NOT EXISTS (SELECT null
417 FROM XLA_TRANSACTION_ENTITIES_UPG xla_ent
418 WHERE xla_ent.application_id = l_pa_app_id
419 AND xla_ent.entity_code = 'EXPENDITURES'
420 AND NVL(xla_ent.source_id_int_1,-99) = lines_gt.expenditure_item_id
421 AND xla_ent.ledger_id = lines_gt.ledger_id
422 AND rownum = 1)
423 ));
424
425 l_rows_processed := SQL%ROWCOUNT/2;
426
427
428 /* One event and one header needs to be created per pair of C and D lines.
429 But this same event and header needs to be used while creating ae lines using both C and D lines.
430
431 Since there is no database link available between a C and corresponding D line,
432 its better to group by to ensure the data model consistency.
433
434 Since we have to group, gl_date is also included in the grouping criteria to be consistent with the
435 regular flow where one event is raised per expenditrue_item_id/line_type(R/C+D/BL/PC)/gl_date combination
436
440 (upg_batch_id,
437 Xla_transaction_entities has a non unique index N1 on columns(application_id,ledger_id,entity_code,source_id..)*/
438
439 INSERT ALL INTO XLA_EVENTS
441 upg_source_application_id,
442 application_id,
443 event_type_code,
444 event_number,
445 event_status_code,
446 process_status_code,
447 on_hold_flag,
448 event_date,
449 creation_date,
450 created_by,
451 last_update_date,
452 last_updated_by,
453 last_update_login,
454 program_update_date,
455 program_id,
456 program_application_id,
457 request_id,
458 entity_id,
459 event_id,
460 transaction_date)
461 VALUES
462 (p_upg_batch_id,
463 l_pa_app_id,
464 l_pa_app_id,
465 event_type_code,
466 xla_events_s.nextval,
467 'P', --event status
468 'P', --process status
469 'N',
470 gl_date, --event date
471 l_date,
472 l_user,
473 l_date,
474 l_user,
475 l_user,
476 l_date,
477 l_user,
478 l_pa_app_id,
479 l_request_id,
480 entity_id,
481 xla_events_s.nextval,
482 gl_date
483 )
484 INTO XLA_AE_HEADERS
485 (upg_batch_id,
486 upg_source_application_id,
487 application_id,
488 amb_context_code,
489 entity_id,
490 event_id,
491 event_type_code,
492 ae_header_id,
493 ledger_id,
494 accounting_date,
495 period_name,
496 balance_type_code,
497 je_category_name,
498 gl_transfer_status_code,
499 accounting_entry_status_code,
500 accounting_entry_type_code,
501 creation_date,
502 created_by,
503 last_update_date,
504 last_updated_by,
505 last_update_login,
506 program_update_date,
507 program_id,
508 program_application_id,
509 request_id)
510 VALUES
511 (p_upg_batch_id,
512 l_pa_app_id,
513 l_pa_app_id,
514 'DEFAULT',
515 entity_id,
516 xla_events_s.nextval,
517 event_type_code,
518 xla_ae_headers_s.nextval,
519 ledger_id,
520 gl_date,
521 gl_period_name,
522 'A', --balance type Actual
523 je_category,
524 'Y', --gl transfer status
525 'F', --acct entry status code final
526 'STANDARD',
527 l_date,
528 l_user,
529 l_date,
530 l_user,
531 l_user,
532 l_date,
533 l_user,
534 l_pa_app_id,
535 l_request_id
536 )
537 INTO PA_XLA_UPG_EVENTS_GT
538 (expenditure_item_id,
539 event_id,
540 grouped_line_type,
541 event_date,
542 header_id
543 )
544 VALUES
545 (expenditure_item_id,
546 xla_events_s.nextval,
547 grouped_line_type,
548 gl_date,
549 xla_ae_headers_s.nextval
550 )
551 SELECT /*+ USE_NL(ent_gt lines_gt) INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) */
552 lines_gt.expenditure_item_id,
553 ent_gt.entity_id,
554 lines_gt.grouped_line_type,
555 lines_gt.gl_date,
556 lines_gt.gl_period_name ,
557 lines_gt.ledger_id,
558 lines_gt.legal_entity_id,
559 lines_gt.je_category,
560 lines_gt.event_type_code
561 FROM PA_XLA_UPG_LINES_GT lines_gt,
562 PA_XLA_UPG_ENTITIES_GT ent_gt
563 WHERE lines_gt.expenditure_item_id = ent_gt.expenditure_item_id
564 GROUP BY lines_gt.expenditure_item_id,
565 ent_gt.entity_id,
566 lines_gt.grouped_line_type,
567 lines_gt.gl_date,
568 lines_gt.gl_period_name ,
569 lines_gt.ledger_id,
570 lines_gt.legal_entity_id,
571 lines_gt.je_category,
572 lines_gt.event_type_code ;
573
574
575 /* For the join with gl_import_references, cr_code_combination_id on R or C lines is not used since for these
576 lines credit side we do not populate ccid on gl interface table (since we group by cr ccid to create batch
577 name) while on debit side there can be multiple records with same batch names but different debit ccids.
578
579 gl_import_references has a non-unique index N3 on columns (reference_6, reference_2, reference_3)
580
581 It would be ok to use RANK here instead of ROW_NUMBER since separate headers are created for CDLs and CCDLs
582
583 The Cr and Dr amounts are altered assuming Switch Dr/CR to be 'Yes' on the JLT */
584
585 INSERT ALL INTO XLA_AE_LINES
589 application_id,
586 (upg_batch_id,
587 ae_header_id,
588 ae_line_num,
590 code_combination_id,
591 gl_transfer_mode_code,
592 accounted_dr,
593 accounted_cr,
594 currency_code,
595 entered_dr,
596 entered_cr,
597 description,
598 accounting_class_code,
599 creation_date,
600 created_by,
601 last_update_date,
602 last_updated_by,
603 last_update_login,
604 program_update_date,
605 program_id,
606 program_application_id,
607 request_id,
608 gain_or_loss_flag,
609 accounting_date,
610 ledger_id
611 )
612 VALUES
613 ( p_upg_batch_id,
614 header_id,
615 ae_line_num,
616 l_pa_app_id,
617 code_combination_id,
618 'D', --gl transfer mode Summary or detail
619 acct_dr,
620 acct_cr,
621 currency_code,
622 entered_dr,
623 entered_cr,
624 '', --description TBD
625 acct_class,
626 l_date,
627 l_user,
628 l_date,
629 l_user,
630 l_user,
631 l_date,
632 l_user,
633 l_pa_app_id,
634 l_request_id,
635 'N',
636 gl_date,
637 ledger_id)
638 INTO XLA_DISTRIBUTION_LINKS
639 (application_id,
640 event_id,
641 ae_header_id,
642 ae_line_num,
643 source_distribution_type,
644 source_distribution_id_num_1,
645 source_distribution_id_num_2,
646 merge_duplicate_code,
647 event_type_code,
648 event_class_code,
649 upg_batch_id,
650 ref_ae_header_id,
651 temp_line_num,
652 unrounded_accounted_dr,
653 unrounded_accounted_cr,
654 unrounded_entered_dr,
655 unrounded_entered_cr)
656 VALUES
657 (l_pa_app_id,
658 event_id,
659 header_id,
660 ae_line_num,
661 cdl_line_type,
662 expenditure_item_id,
663 cdl_line_num,
664 'N',
665 event_type_code,
666 event_class_code,
667 p_upg_batch_id,
668 header_id,
669 ae_line_num,
670 acct_dr,
671 acct_cr,
672 entered_dr,
673 entered_cr)
674 INTO PA_REV_AE_LINES_TMP
675 (ae_header_id,
676 ae_line_num,
677 gl_batch_name,
678 code_combination_id,
679 dist_type)
680 VALUES
681 (header_id,
682 ae_line_num,
683 batch_name,
684 reference_2,
685 reference_3)
686 SELECT
687 header_id AS header_id,
688 event_id AS event_id,
689 code_combination_id AS code_combination_id,
690 acct_class AS acct_class,
691 acct_dr AS acct_dr,
692 acct_cr AS acct_cr,
693 entered_dr AS entered_dr,
694 entered_cr AS entered_cr,
695 currency_code AS currency_code,
696 event_type_code AS event_type_code,
697 event_class_code AS event_class_code,
698 expenditure_item_id AS expenditure_item_id,
699 cdl_line_num AS cdl_line_num,
700 cdl_line_type AS cdl_line_type,
701 batch_name AS batch_name,
702 reference_2 AS reference_2,
703 reference_3 AS reference_3,
704 ledger_id AS ledger_id,
705 gl_date AS gl_date,
706 RANK() OVER (PARTITION BY header_id
707 ORDER BY expenditure_item_id, cdl_line_num, position) AS ae_line_num
708 FROM
709 (select /*+ USE_NL (event_gt lines_gt imp) INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1)
710 INDEX(imp, GL_IMPORT_REFERENCES_N3) */
711 lines_gt.position,
712 event_gt.header_id header_id,
713 event_gt.event_id event_id,
714 lines_gt.code_combination_id,
715 decode(lines_gt.position,
716 1, decode(lines_gt.cdl_line_type,
717 'PC', 'RECLASS_DESTINATION',
718 'BL', decode(lines_gt.tp_amt_type_code,
719 'REVENUE_TRANSFER', 'RECEIVER_REVENUE',
720 'RECEIVER_COST'),
721 'COST'),
722 2, decode(lines_gt.cdl_line_type,
723 'PC', 'RECLASS_SOURCE',
724 'BL', decode(lines_gt.tp_amt_type_code,
725 'REVENUE_TRANSFER', 'PROVIDER_REVENUE',
726 'PROVIDER_COST'),
727 'COST_CLEARING')
728 ) acct_class,
729 decode(position, 1, decode(sign(lines_gt.acct_raw_cost), 1, lines_gt.acct_raw_cost, 0, 0, NULL),
730 2, decode(sign(lines_gt.acct_raw_cost), -1, -1*lines_gt.acct_raw_cost, 0, NULL, NULL)) acct_dr,
731 decode(position, 1, decode(sign(lines_gt.acct_raw_cost), -1, -1*lines_gt.acct_raw_cost, 0, NULL, NULL),
732 2, decode(sign(lines_gt.acct_raw_cost), 1, 1*lines_gt.acct_raw_cost, 0, 0 , NULL)) acct_cr,
733 decode(position, 1, decode(sign(lines_gt.denom_raw_cost), 1, lines_gt.denom_raw_cost, 0, 0 , NULL),
737 lines_gt.denom_currency_code currency_code,
734 2,decode(sign(lines_gt.denom_raw_cost),-1,-1*lines_gt.denom_raw_cost,0, NULL ,NULL)) entered_dr,
735 decode(position, 1, decode(sign(lines_gt.denom_raw_cost),-1, -1*lines_gt.denom_raw_cost, 0, NULL ,NULL),
736 2,decode(sign(lines_gt.denom_raw_cost), 1,1*lines_gt.denom_raw_cost,0, 0 ,NULL)) entered_cr,
738 lines_gt.event_type_code event_type_code,
739 lines_gt.event_class_code,
740 lines_gt.expenditure_item_id expenditure_item_id,
741 lines_gt.cdl_line_num,
742 lines_gt.cdl_line_type,
743 lines_gt.batch_name,
744 lines_gt.reference_2,
745 lines_gt.reference_3,
746 lines_gt.ledger_id,
747 lines_gt.gl_date
748 from PA_XLA_UPG_LINES_GT lines_gt,
749 PA_XLA_UPG_EVENTS_GT event_gt
750 where event_gt.expenditure_item_id = lines_gt.expenditure_item_id
751 and event_gt.event_date = lines_gt.gl_date
752 and event_gt.grouped_line_type = lines_gt.grouped_line_type
753 );
754
755
756 /* Now stamp back the event id on the cdl and ccdl tables. */
757
758
759 UPDATE /*+ INDEX(cdl, PA_COST_DISTRIBUTION_LINES_U1) */
760 pa_cost_distribution_lines_all cdl
761 SET cdl.acct_event_id = (select /*+ INDEX(event_gt, PA_XLA_UPG_EVENTS_GT_N1) */
762 event_gt.event_id from PA_XLA_UPG_EVENTS_GT event_gt
763 where cdl.expenditure_item_id = event_gt.expenditure_item_id
764 and cdl.gl_date = event_gt.event_date
765 and decode(cdl.line_type, 'R', 'R', 'B' ) = event_gt.grouped_line_type
766 and rownum = 1)
767 WHERE cdl.expenditure_item_id between l_start_eiid and l_end_eiid
768 and cdl.line_type in ( 'R','C','D')
769 and cdl.acct_event_id is null
770 and exists ( select /*+ INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) */
771 1
772 from PA_XLA_UPG_LINES_GT lines_gt
773 where lines_gt.expenditure_item_id = cdl.expenditure_item_id
774 and lines_gt.cdl_line_num = cdl.line_num);
775
776
777
778 UPDATE /*+ INDEX(cdl, PA_CC_DIST_LINES_U2) */
779 PA_CC_DIST_LINES_ALL cdl
780 SET cdl.acct_event_id = (select /*+ INDEX(event_gt, PA_XLA_UPG_EVENTS_GT_N1) */
781 event_gt.event_id from PA_XLA_UPG_EVENTS_GT event_gt
782 where cdl.expenditure_item_id = event_gt.expenditure_item_id
783 and cdl.gl_date = event_gt.event_date
784 and cdl.line_type = event_gt.grouped_line_type
785 and rownum = 1)
786 WHERE cdl.expenditure_item_id between l_start_eiid and l_end_eiid
787 and cdl.line_type in ('BL','PC')
788 and cdl.acct_event_id is null
789 and exists ( select /*+ INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) */
790 1
791 from PA_XLA_UPG_LINES_GT lines_gt
792 where lines_gt.expenditure_item_id = cdl.expenditure_item_id
793 and lines_gt.cdl_line_num = cdl.line_num);
794
795
796 /* Stamp the gl_sl_link_id on xla_ae_lines to establish the link between GL and SLA
797
798 This is done in a separate update (and not combined with the insert on xla_ae_lines
799 since there are known issues of data corruption in the link with gl_import_references
800 using the gl_batch_name and this corruption should not prevent the data upgrade to SLA.
801
802 Even if any data corrutpion is encountered, the upgrade is continued as the only loss
803 is the lack of link between GL and SLA, which is simlar to the link not being there in
804 11i between GL and PA due to the above data corruption */
805
806
807 BEGIN
808
809 UPDATE XLA_AE_LINES xal
810 SET gl_sl_link_id = (SELECT /*+ INDEX(tmp, PA_REV_AE_LINES_TMP_U1) */
811 gl_sl_link_id
812 FROM GL_IMPORT_REFERENCES imp,
813 PA_REV_AE_LINES_TMP tmp
814 WHERE xal.ae_header_id = tmp.ae_header_id
815 AND xal.ae_line_num = tmp.ae_line_num
816 AND tmp.gl_batch_name = imp.reference_6
817 AND tmp.code_combination_id = nvl(imp.reference_2,-99)
818 AND tmp.dist_type = imp.reference_3
819 AND ROWNUM = 1)
820 WHERE application_id = l_pa_app_id
821 AND upg_batch_id = p_upg_batch_id
822 AND gl_sl_link_id is null
823 AND EXISTS ( SELECT /*+ INDEX(tmp1, PA_REV_AE_LINES_TMP_U1) */ 1
824 FROM PA_REV_AE_LINES_TMP tmp1
825 WHERE xal.ae_header_id = tmp1.ae_header_id
826 AND xal.ae_line_num = tmp1.ae_line_num);
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 null;
831 END;
832
833
834
835 END IF; /* l_rows_processed */
836
837
838 ad_parallel_updates_pkg.processed_id_range(
839 l_rows_processed,
840 l_end_eiid);
841
842 COMMIT;
843
844 ad_parallel_updates_pkg.get_id_range(
845 l_start_eiid,
846 l_end_eiid,
847 l_any_rows_to_process,
848 p_batch_size,
849 FALSE);
850
851 END LOOP ;
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 RAISE;
856
857 END UPGRADE_COST_XCHARGE;
858
859 ------------------------------------------------------------------------------------------------------
860
861 PROCEDURE UPGRADE_MC_COST_XCHARGE( p_table_owner IN VARCHAR2,
862 p_script_name IN VARCHAR2,
863 p_worker_id IN NUMBER,
864 p_num_workers IN NUMBER,
865 p_batch_size IN NUMBER,
866 p_min_eiid IN NUMBER,
867 p_max_eiid IN NUMBER,
868 p_upg_batch_id IN NUMBER,
869 p_mode IN VARCHAR2,
870 p_cost_cross IN VARCHAR2)
871
872 IS
873
874 -----------------------------------------------------
875 -- Ad parallelization variables
876 -----------------------------------------------------
877 l_table_name varchar2(30) := 'PA_EXPENDITURE_ITEMS_ALL';
878 l_rows_processed number;
879 l_start_eiid number;
880 l_end_eiid number;
881 l_any_rows_to_process boolean;
882 -----------------------------------------------------
883
884 l_pa_app_id number := 275;
885 l_user number := 2; --Bug 6319424: Commented '-2005'
886 l_request_id number := null;
887 l_date date := sysdate;
888
889 BEGIN
890
891 ad_parallel_updates_pkg.initialize_id_range(
892 ad_parallel_updates_pkg.ID_RANGE_SUB_RANGE,
893 p_table_owner,
894 l_table_name,
895 p_script_name,
896 'EXPENDITURE_ITEM_ID',
897 p_worker_id,
898 p_num_workers,
899 p_batch_size,
900 0,
901 null,
902 p_min_eiid,
903 p_max_eiid);
904
905
906 ad_parallel_updates_pkg.get_id_range(
907 l_start_eiid,
908 l_end_eiid,
909 l_any_rows_to_process,
910 p_batch_size,
911 TRUE);
912
913
914 WHILE ( l_any_rows_to_process = TRUE ) LOOP
915
916 l_rows_processed := 0;
917
918
919 INSERT WHEN cdl_line_type in ('R', 'D', 'BL', 'PC')
920 THEN INTO PA_XLA_UPG_LINES_GT
921 (legal_entity_id,
922 ledger_id,
923 org_id,
924 expenditure_item_id,
925 cdl_line_num,
926 cdl_line_type,
927 grouped_line_type,
928 gl_date,
929 gl_period_name,
930 batch_name,
931 code_combination_id,
932 acct_raw_cost,
933 denom_raw_cost,
934 denom_currency_code,
935 conversion_date,
936 conversion_rate,
937 conversion_type,
938 tp_amt_type_code,
939 je_category,
940 event_type_code,
941 event_class_code,
942 entity_id,
943 event_id,
944 position,
945 REFERENCE_2,
946 REFERENCE_3
947 )
948 VALUES
949 (legal_entity_id,
950 ledger_id,
951 org_id,
952 expenditure_item_id,
953 cdl_line_num,
954 cdl_line_type,
955 grouped_line_type,
956 gl_date,
957 gl_period_name,
958 batch_name,
959 dr_code_combination_id,
960 acct_raw_cost,
961 denom_raw_cost,
962 denom_currency_code,
963 conversion_date,
964 conversion_rate,
965 conversion_type,
966 tp_amt_type_code,
967 je_category,
968 event_type_code,
969 event_class_code,
970 entity_id,
971 event_id,
975 )
972 1,
973 DR_REFERENCE_2,
974 DR_REFERENCE_3
976 WHEN cdl_line_type in ('R', 'C', 'BL', 'PC')
977 THEN INTO PA_XLA_UPG_LINES_GT
978 (legal_entity_id,
979 ledger_id,
980 org_id,
981 expenditure_item_id,
982 cdl_line_num,
983 cdl_line_type,
984 grouped_line_type,
985 gl_date,
986 gl_period_name,
987 batch_name,
988 code_combination_id,
989 acct_raw_cost,
990 denom_raw_cost,
991 denom_currency_code,
992 conversion_date,
993 conversion_rate,
994 conversion_type,
995 tp_amt_type_code,
996 je_category,
997 event_type_code,
998 event_class_code,
999 entity_id,
1000 event_id,
1001 position,
1002 REFERENCE_2,
1003 REFERENCE_3
1004 )
1005 VALUES
1006 (legal_entity_id,
1007 ledger_id,
1008 org_id,
1009 expenditure_item_id,
1010 cdl_line_num,
1011 cdl_line_type,
1012 grouped_line_type,
1013 gl_date,
1014 gl_period_name,
1015 batch_name,
1016 cr_code_combination_id,
1017 acct_raw_cost,
1018 denom_raw_cost,
1019 denom_currency_code,
1020 conversion_date,
1021 conversion_rate,
1022 conversion_type,
1023 tp_amt_type_code,
1024 je_category,
1025 event_type_code,
1026 event_class_code,
1027 entity_id,
1028 event_id,
1029 2,
1030 CR_REFERENCE_2,
1031 CR_REFERENCE_3
1032 )
1033 SELECT /*+ USE_NL (cdl mc ei) INDEX(cdl, PA_COST_DISTRIBUTION_LINES_U1)
1034 INDEX( mc, PA_MC_COST_DIST_LINES_ALL_U1) INDEX(ei, PA_EXPENDITURE_ITEMS_PK) */
1035 to_number(hoi.org_information2) legal_entity_id,
1036 mc.set_of_books_id ledger_id,
1037 imp.org_id,
1038 mc.expenditure_item_id,
1039 mc.line_num cdl_line_num,
1040 mc.line_type cdl_line_type,
1041 decode(mc.line_type, 'R','R','B') grouped_line_type,
1042 cdl.gl_date,
1043 cdl.gl_period_name,
1044 mc.batch_name,
1045 cdl.dr_code_combination_id dr_code_combination_id,
1046 cdl.cr_code_combination_id cr_code_combination_id,
1047 decode(mc.line_type, 'C', -1 * mc.amount,
1048 'D', mc.amount,
1049 'R', decode(ei.system_linkage_function, 'BTC', mc.burdened_cost,
1050 mc.amount)) acct_raw_cost,
1051 decode(cdl.line_type, 'C', -1 * cdl.denom_raw_cost,
1052 'D', cdl.denom_raw_cost,
1053 'R', decode(ei.system_linkage_function, 'BTC', cdl.denom_burdened_cost,
1054 cdl.denom_raw_cost)) denom_raw_cost,
1055 cdl.denom_currency_code,
1056 mc.conversion_date conversion_date,
1057 mc.exchange_rate conversion_rate,
1058 mc.rate_type conversion_type,
1059 null tp_amt_type_code,
1060 decode(mc.line_type, 'C', 'Total Burdened Cost',
1061 'D', 'Total Burdened Cost',
1062 'R', decode(evt.event_type_code, 'BURDEN_COST_DIST','Burden Cost',
1063 'BURDEN_COST_DIST_ADJ','Burden Cost',
1064 'INVENTORY_COST_DIST','Inventory',
1065 'INVENTORY_COST_DIST_ADJ','Inventory',
1066 'LABOR_COST_DIST','Labor Cost',
1067 'LABOR_COST_DIST_ADJ','Labor Cost',
1068 'MISC_COST_DIST','Miscellaneous Transaction',
1069 'MISC_COST_DIST_ADJ','Miscellaneous Transaction',
1070 'USG_COST_DIST','Usage Cost',
1071 'USG_COST_DIST_ADJ','Usage Cost',
1072 'WIP_COST_DIST','WIP',
1073 'WIP_COST_DIST_ADJ','WIP'
1074 )
1075 ) je_category,
1076 evt.event_type_code,
1077 decode(cdl.line_type, 'C', 'TOT_BURDENED_COST',
1078 'D', 'TOT_BURDENED_COST',
1079 'R', decode(evt.event_type_code, 'BURDEN_COST_DIST','BURDEN_COST',
1080 'BURDEN_COST_DIST_ADJ','BURDEN_COST_ADJ',
1081 'INVENTORY_COST_DIST','INVENTORY_COST',
1082 'INVENTORY_COST_DIST_ADJ','INVENTORY_COST_ADJ',
1083 'LABOR_COST_DIST','LABOR_COST',
1084 'LABOR_COST_DIST_ADJ','LABOR_COST_ADJ',
1085 'MISC_COST_DIST','MISC_COST',
1086 'MISC_COST_DIST_ADJ','MISC_COST_ADJ',
1087 'USG_COST_DIST','USG_COST',
1088 'USG_COST_DIST_ADJ','USG_COST_ADJ',
1089 'WIP_COST_DIST','WIP_COST',
1090 'WIP_COST_DIST_ADJ','WIP_COST_ADJ'
1091 )) event_class_code,
1092 evt.entity_id,
1093 evt.event_id,
1094 'Cost' DR_REFERENCE_3,
1095 'Liability' CR_REFERENCE_3,
1096 to_char(cdl.dr_code_combination_id) DR_REFERENCE_2,
1097 to_char('-99') CR_REFERENCE_2
1098 from PA_MC_COST_DIST_LINES_ALL mc,
1099 PA_COST_DISTRIBUTION_LINES_ALL cdl,
1100 PA_EXPENDITURE_ITEMS_ALL ei,
1101 PA_IMPLEMENTATIONS_ALL imp,
1102 XLA_EVENTS evt,
1103 HR_ORGANIZATION_INFORMATION hoi
1104 where mc.expenditure_item_id = cdl.expenditure_item_id
1105 and mc.line_num = cdl.line_num
1106 and mc.xla_migrated_flag is null
1107 and mc.batch_name is not null
1108 and mc.transfer_status_code = 'A'
1109 and mc.expenditure_item_id = ei.expenditure_item_id
1110 and cdl.acct_event_id = evt.event_id
1111 and cdl.org_id = imp.org_id
1112 and hoi.organization_id = imp.org_id
1113 and hoi.org_information_context = 'Operating Unit Information'
1114 and cdl.expenditure_item_id between l_start_eiid and l_end_eiid
1118 INDEX( mc, PA_MC_CC_DIST_LINES_U2) INDEX(ei, PA_EXPENDITURE_ITEMS_PK) */
1115 AND NVL(p_cost_cross,'C') = 'C' /* Bug 5408944 */
1116 UNION ALL
1117 SELECT /*+ USE_NL (cdl mc ei) INDEX(cdl, PA_CC_DIST_LINES_U2)
1119 to_number(hoi.org_information2) legal_entity_id,
1120 mc.set_of_books_id ledger_id,
1121 imp.org_id,
1122 mc.expenditure_item_id,
1123 mc.line_num cdl_line_num,
1124 mc.line_type cdl_line_type,
1125 mc.line_type grouped_line_type,
1126 cdl.gl_date,
1127 cdl.gl_period_name,
1128 mc.gl_batch_name,
1129 cdl.dr_code_combination_id dr_code_combination_id,
1130 cdl.cr_code_combination_id cr_code_combination_id,
1131 mc.amount acct_raw_cost,
1132 cdl.denom_transfer_price denom_raw_cost,
1133 cdl.denom_tp_currency_code denom_currency_code,
1134 mc.acct_tp_rate_date conversion_date,
1135 mc.acct_tp_exchange_rate conversion_rate,
1136 mc.acct_tp_rate_type conversion_type,
1137 ei.tp_amt_type_code,
1138 decode(mc.line_type, 'BL', 'Borrowed and Lent',
1139 'PC', 'Prov Cost Reclass') je_category,
1140 evt.event_type_code,
1141 decode(mc.line_type, 'BL', 'BORROWED_AND_LENT',
1142 'PC', 'PRVDR_RECVR_RECLASS') event_class_code,
1143 evt.entity_id,
1144 evt.event_id,
1145 'Cross Charge Debit' DR_REFERENCE_3,
1146 'Cross Charge Credit' CR_REFERENCE_3,
1147 to_char(cdl.dr_code_combination_id) DR_REFERENCE_2,
1148 to_char(cdl.cr_code_combination_id) CR_REFERENCE_2
1149 from PA_MC_CC_DIST_LINES_ALL mc,
1150 PA_CC_DIST_LINES_ALL cdl,
1151 PA_EXPENDITURE_ITEMS_ALL ei,
1152 PA_IMPLEMENTATIONS_ALL imp,
1153 XLA_EVENTS evt,
1154 HR_ORGANIZATION_INFORMATION hoi
1155 where mc.expenditure_item_id = cdl.expenditure_item_id
1156 and mc.line_num = cdl.line_num
1157 and mc.xla_migrated_flag is null
1158 and mc.transfer_status_code = 'A'
1159 and mc.gl_batch_name is not null
1160 and mc.expenditure_item_id = ei.expenditure_item_id
1161 and cdl.acct_event_id = evt.event_id
1162 and cdl.org_id = imp.org_id
1163 and hoi.organization_id = imp.org_id
1164 and hoi.org_information_context = 'Operating Unit Information'
1165 and cdl.expenditure_item_id between l_start_eiid and l_end_eiid
1166 AND NVL(p_cost_cross,'X') = 'X' /* Bug 5408944 */;
1167
1168 l_rows_processed := SQL%ROWCOUNT;
1169
1170 IF nvl(l_rows_processed,0) > 0 THEN
1171
1172
1173 INSERT ALL INTO XLA_AE_HEADERS
1174 (upg_batch_id,
1175 upg_source_application_id,
1176 application_id,
1177 amb_context_code,
1178 entity_id,
1179 event_id,
1180 event_type_code,
1181 ae_header_id,
1182 ledger_id,
1183 accounting_date,
1184 period_name,
1185 balance_type_code,
1186 je_category_name,
1187 gl_transfer_status_code,
1188 accounting_entry_status_code,
1189 accounting_entry_type_code,
1190 creation_date,
1191 created_by,
1192 last_update_date,
1193 last_updated_by,
1194 last_update_login,
1195 program_update_date,
1196 program_id,
1197 program_application_id,
1198 request_id)
1199 VALUES
1200 (p_upg_batch_id,
1201 l_pa_app_id,
1202 l_pa_app_id,
1203 'DEFAULT',
1204 entity_id,
1205 event_id,
1206 event_type_code,
1207 xla_ae_headers_s.nextval,
1208 ledger_id,
1209 gl_date,
1210 gl_period_name,
1211 'A', --balance type Actual
1212 je_category,
1213 'Y', --gl transfer status
1214 'F', --acct entry status code final
1215 'STANDARD',
1216 l_date,
1217 l_user,
1218 l_date,
1219 l_user,
1220 l_user,
1221 l_date,
1222 l_user,
1223 l_pa_app_id,
1224 l_request_id
1225 )
1226 INTO PA_XLA_UPG_EVENTS_GT
1227 (expenditure_item_id,
1228 event_id,
1229 grouped_line_type,
1230 event_date,
1231 header_id,
1232 entity_id,
1233 ledger_id
1234 )
1235 VALUES
1236 (expenditure_item_id,
1237 event_id,
1238 grouped_line_type,
1239 gl_date,
1240 xla_ae_headers_s.nextval,
1241 entity_id,
1242 ledger_id
1243 )
1244 select lines_gt.event_id,
1245 lines_gt.ledger_id,
1246 lines_gt.expenditure_item_id,
1247 lines_gt.entity_id,
1248 lines_gt.grouped_line_type,
1249 lines_gt.gl_date,
1250 lines_gt.gl_period_name ,
1251 lines_gt.legal_entity_id,
1252 lines_gt.je_category,
1253 lines_gt.event_type_code
1254 from PA_XLA_UPG_LINES_GT lines_gt
1255 group by lines_gt.event_id,
1256 lines_gt.ledger_id,
1257 lines_gt.expenditure_item_id,
1258 lines_gt.entity_id,
1259 lines_gt.grouped_line_type,
1260 lines_gt.gl_date,
1261 lines_gt.gl_period_name ,
1265
1262 lines_gt.legal_entity_id,
1263 lines_gt.je_category,
1264 lines_gt.event_type_code;
1266
1267 INSERT ALL INTO XLA_AE_LINES
1268 (upg_batch_id,
1269 ae_header_id,
1270 ae_line_num,
1271 application_id,
1272 code_combination_id,
1273 gl_transfer_mode_code,
1274 accounted_dr,
1275 accounted_cr,
1276 currency_code,
1277 currency_conversion_date,
1278 currency_conversion_rate,
1279 currency_conversion_type,
1280 gl_sl_link_table,
1281 entered_dr,
1282 entered_cr,
1283 description,
1284 accounting_class_code,
1285 creation_date,
1286 created_by,
1287 last_update_date,
1288 last_updated_by,
1289 last_update_login,
1290 program_update_date,
1291 program_id,
1292 program_application_id,
1293 request_id,
1294 gain_or_loss_flag,
1295 accounting_date,
1296 ledger_id
1297 )
1298 VALUES
1299 ( p_upg_batch_id,
1300 header_id,
1301 ae_line_num,
1302 l_pa_app_id,
1303 code_combination_id,
1304 'D',
1305 acct_dr,
1306 acct_cr,
1307 currency_code,
1308 conversion_date,
1309 conversion_rate,
1310 conversion_type,
1311 'XLAJEL',
1312 entered_dr,
1313 entered_cr,
1314 '', --description TBD
1315 acct_class,
1316 l_date,
1317 l_user,
1318 l_date,
1319 l_user,
1320 l_user,
1321 l_date,
1322 l_user,
1323 l_pa_app_id,
1324 l_request_id,
1325 'N',
1326 gl_date,
1327 ledger_id)
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,
1341 temp_line_num,
1342 unrounded_accounted_dr,
1343 unrounded_accounted_cr,
1344 unrounded_entered_dr,
1345 unrounded_entered_cr)
1346 VALUES
1347 (l_pa_app_id,
1348 event_id,
1349 header_id,
1350 ae_line_num,
1351 cdl_line_type,
1352 expenditure_item_id,
1353 cdl_line_num,
1354 'N',
1355 event_type_code,
1356 event_class_code,
1357 p_upg_batch_id,
1358 header_id,
1359 ae_line_num,
1360 acct_dr,
1361 acct_cr,
1362 entered_dr,
1363 entered_cr)
1364 INTO PA_REV_AE_LINES_TMP
1365 (ae_header_id,
1366 ae_line_num,
1367 gl_batch_name,
1368 code_combination_id,
1369 dist_type)
1370 VALUES
1371 (header_id,
1372 ae_line_num,
1373 batch_name,
1374 reference_2,
1375 reference_3)
1376 SELECT
1377 header_id AS header_id,
1378 event_id AS event_id,
1379 code_combination_id AS code_combination_id,
1380 acct_class AS acct_class,
1381 acct_dr AS acct_dr,
1382 acct_cr AS acct_cr,
1383 entered_dr AS entered_dr,
1384 entered_cr AS entered_cr,
1385 currency_code AS currency_code,
1386 conversion_date AS conversion_date,
1387 conversion_rate AS conversion_rate,
1388 conversion_type AS conversion_type,
1389 event_type_code AS event_type_code,
1390 event_class_code AS event_class_code,
1391 expenditure_item_id AS expenditure_item_id,
1392 cdl_line_num AS cdl_line_num,
1393 cdl_line_type AS cdl_line_type,
1394 batch_name AS batch_name,
1395 reference_2 AS reference_2,
1396 reference_3 AS reference_3,
1397 ledger_id AS ledger_id,
1398 gl_date AS gl_date,
1399 RANK() OVER (PARTITION BY header_id
1400 ORDER BY expenditure_item_id, cdl_line_num, position) AS ae_line_num
1401 FROM
1402 (select /*+ USE_NL (event_gt lines_gt imp)
1403 INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) INDEX(imp, GL_IMPORT_REFERENCES_N3) */
1404 lines_gt.position,
1405 event_gt.header_id,
1406 event_gt.event_id,
1407 lines_gt.code_combination_id,
1408 decode(lines_gt.position,
1409 1, decode(lines_gt.cdl_line_type,
1410 'PC', 'RECLASS_DESTINATION',
1411 'BL', decode(lines_gt.tp_amt_type_code,
1412 'REVENUE_TRANSFER', 'RECEIVER_REVENUE',
1413 'RECEIVER_COST'),
1414 'COST'),
1415 2, decode(lines_gt.cdl_line_type,
1416 'PC', 'RECLASS_SOURCE',
1420 'COST_CLEARING')
1417 'BL', decode(lines_gt.tp_amt_type_code,
1418 'REVENUE_TRANSFER', 'PROVIDER_REVENUE',
1419 'PROVIDER_COST'),
1421 ) acct_class,
1422 decode(position, 1, decode(sign(lines_gt.acct_raw_cost), 1, lines_gt.acct_raw_cost, 0 , 0 , NULL),
1423 2, decode(sign(lines_gt.acct_raw_cost), -1, -1*lines_gt.acct_raw_cost, 0, NULL , NULL)) acct_dr,
1424 decode(position, 1, decode(sign(lines_gt.acct_raw_cost), -1, -1*lines_gt.acct_raw_cost, 0, NULL, NULL),
1425 2, decode(sign(lines_gt.acct_raw_cost), 1, 1*lines_gt.acct_raw_cost, 0, 0 , NULL)) acct_cr,
1426 decode(position, 1, decode(sign(lines_gt.denom_raw_cost), 1, lines_gt.denom_raw_cost,0, 0, NULL),
1427 2,decode(sign(lines_gt.denom_raw_cost),-1,-1*lines_gt.denom_raw_cost, 0, NULL, NULL)) entered_dr,
1428 decode(position, 1, decode(sign(lines_gt.denom_raw_cost), -1, -1*lines_gt.denom_raw_cost, 0, NULL, NULL),
1429 2,decode(sign(lines_gt.denom_raw_cost),1,1*lines_gt.denom_raw_cost,0, 0, NULL)) entered_cr,
1430 lines_gt.denom_currency_code currency_code,
1431 lines_gt.conversion_date,
1432 lines_gt.conversion_rate,
1433 lines_gt.conversion_type,
1434 lines_gt.event_type_code event_type_code,
1435 lines_gt.event_class_code,
1436 lines_gt.expenditure_item_id expenditure_item_id,
1437 lines_gt.cdl_line_num,
1438 lines_gt.cdl_line_type,
1439 lines_gt.batch_name,
1440 lines_gt.reference_2,
1441 lines_gt.reference_3,
1442 lines_gt.ledger_id,
1443 lines_gt.gl_date
1444 from PA_XLA_UPG_LINES_GT lines_gt,
1445 PA_XLA_UPG_EVENTS_GT event_gt
1446 where event_gt.expenditure_item_id = lines_gt.expenditure_item_id
1447 and event_gt.event_date = lines_gt.gl_date
1448 and event_gt.ledger_id = lines_gt.ledger_id /* Added for bug 4919145 */
1449 and event_gt.grouped_line_type = lines_gt.grouped_line_type
1450 );
1451
1452
1453 UPDATE /*+ INDEX( mc, PA_MC_COST_DIST_LINES_ALL_U1) */
1454 pa_mc_cost_dist_lines_all mc
1455 SET mc.xla_migrated_flag = 'Y'
1456 WHERE mc.expenditure_item_id between l_start_eiid and l_end_eiid
1457 and mc.line_type in ('R','C','D')
1458 and exists ( select /*+ INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) */
1459 1
1460 from PA_XLA_UPG_LINES_GT lines_gt
1461 where lines_gt.expenditure_item_id = mc.expenditure_item_id
1462 and lines_gt.cdl_line_num = mc.line_num);
1463
1464
1465 UPDATE /*+ INDEX( mc, PA_MC_CC_DIST_LINES_U2) */
1466 pa_mc_cc_dist_lines_all mc
1467 SET mc.xla_migrated_flag = 'Y'
1468 WHERE mc.expenditure_item_id between l_start_eiid and l_end_eiid
1469 and mc.line_type in ('BL','PC')
1470 and exists ( select /*+ INDEX(lines_gt, PA_XLA_UPG_LINES_GT_N1) */
1471 1
1472 from PA_XLA_UPG_LINES_GT lines_gt
1473 where lines_gt.expenditure_item_id = mc.expenditure_item_id
1474 and lines_gt.cdl_line_num = mc.line_num);
1475
1476
1477 BEGIN
1478
1479 UPDATE XLA_AE_LINES xal
1480 SET gl_sl_link_id = (SELECT /*+ INDEX(tmp, PA_REV_AE_LINES_TMP_U1) */
1481 gl_sl_link_id
1482 FROM GL_IMPORT_REFERENCES imp,
1483 PA_REV_AE_LINES_TMP tmp
1484 WHERE xal.ae_header_id = tmp.ae_header_id
1485 AND xal.ae_line_num = tmp.ae_line_num
1486 AND tmp.gl_batch_name = imp.reference_6
1487 AND tmp.code_combination_id = nvl(imp.reference_2,-99)
1488 AND tmp.dist_type = imp.reference_3
1489 AND ROWNUM = 1)
1490 WHERE application_id = l_pa_app_id
1491 AND upg_batch_id = p_upg_batch_id
1492 AND gl_sl_link_id is null
1493 AND EXISTS ( SELECT /*+ INDEX(tmp1, PA_REV_AE_LINES_TMP_U1) */ 1
1494 FROM PA_REV_AE_LINES_TMP tmp1
1495 WHERE xal.ae_header_id = tmp1.ae_header_id
1496 AND xal.ae_line_num = tmp1.ae_line_num);
1497
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 null;
1501 END;
1502
1503 END IF; /* If any rows to be processed */
1504
1505
1506 ad_parallel_updates_pkg.processed_id_range(
1507 l_rows_processed,
1508 l_end_eiid);
1509
1510 COMMIT;
1511
1512 ad_parallel_updates_pkg.get_id_range(
1513 l_start_eiid,
1514 l_end_eiid,
1515 l_any_rows_to_process,
1516 p_batch_size,
1517 FALSE);
1518
1519 END LOOP;
1520
1521
1522
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 RAISE;
1526
1527 END UPGRADE_MC_COST_XCHARGE;
1528
1529 ------------------------------------------------------------------------------------------------------
1530
1531 PROCEDURE POPULATE_CTRL_TABLE (p_batch_id IN NUMBER,
1532 x_mrc_enabled OUT NOCOPY VARCHAR2)
1533 IS
1534
1535 CURSOR c_date_range is
1536 SELECT ledger_id, to_char(min(start_date),'J') min_date, to_char(max(end_date),'J') max_date
1537 FROM gl_period_statuses
1538 WHERE application_id = 275
1539 AND migration_status_code = 'P'
1540 GROUP BY ledger_id;
1541
1542 l_start_date date DEFAULT NULL;
1543 l_end_date date DEFAULT NULL;
1544 l_jeh_min_id number DEFAULT NULL;
1545 l_jeh_max_id number DEFAULT NULL;
1546 l_cdl_min_eiid number DEFAULT NULL;
1547 l_cdl_max_eiid number DEFAULT NULL;
1548 l_ccd_min_eiid number DEFAULT NULL;
1549 l_ccd_max_eiid number DEFAULT NULL;
1550 l_min_eiid number DEFAULT NULL;
1551 l_max_eiid number DEFAULT NULL;
1552 l_cnt number DEFAULT NULL;
1553
1554 l_cost_cross NUMBER := 0;
1555
1556 BEGIN
1557
1558 --------------------------------------------------------------------------------------------------
1559 -- For each ledger min and max gl dates are stored in the control table so as to avoid joining
1560 -- with gl_period_statuses table in the main upgrade
1561 ---------------------------------------------------------------------------------------------------
1562
1563 INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE,
1564 LEDGER_ID, BATCH_ID, STATUS)
1565 SELECT 'GL_PERIOD_STATUSES', to_char(min(start_date),'J') , to_char(max(end_date),'J') , ledger_id, p_batch_id, 'P'
1566 FROM gl_period_statuses
1567 WHERE application_id = 275
1568 AND migration_status_code = 'P'
1569 GROUP BY ledger_id;
1570
1571 select min(to_date(min_value,'J') ) , max(to_date(max_value,'J') )
1572 into l_start_date, l_end_date
1573 from pa_xla_upg_ctrl
1574 where batch_id = p_batch_id
1575 and reference = 'GL_PERIOD_STATUSES';
1576
1577
1578
1579
1580 /*Bug 49435531 */
1581 INSERT INTO PA_PRIM_REP_LEGER_tmp (prim_ledger_id,
1582 denorm_ledger_id,
1583 period_name,
1584 batch_id)
1585 SELECT distinct per1.ledger_id,
1586 per1.ledger_id,
1587 per1.period_name,
1588 p_batch_id
1589 FROM gl_period_statuses per,
1590 gl_period_statuses per1
1591 WHERE per.application_id =275
1592 AND per.migration_status_code ='P'
1593 AND per1.application_id =101
1594 AND per1.ledger_id = per.ledger_id
1595 AND (per1.start_date between per.start_date and per.end_date OR
1596 per1.end_date between per.start_date and per.end_date)
1597 AND EXISTS ( Select null
1598 from pa_implementations_all
1599 where set_of_books_id = per.ledger_id);
1600
1601
1602 INSERT INTO PA_PRIM_REP_LEGER_tmp (prim_ledger_id,
1606 SELECT distinct per.prim_ledger_id,
1603 denorm_ledger_id,
1604 period_name,
1605 batch_id)
1607 mc.reporting_set_of_books_id,
1608 per.period_name,
1609 p_batch_id
1610 FROM PA_PRIM_REP_LEGER_tmp per,
1611 gl_mc_reporting_options_11i mc,
1612 pa_implementations_all imp
1613 WHERE per.prim_ledger_id = mc.primary_set_of_books_id
1614 AND mc.application_id = 275
1615 AND imp.org_id = mc.org_id;
1616
1617
1618
1619 ---------------------------------------------------------------------------------------------------
1620 -- Min and max header ids are stored in the control table to drive the gl_import_references upgrade
1621 ---------------------------------------------------------------------------------------------------
1622 /* Removed as currently we are using ID_RANGE_SCAN_EQUI_ROWSETS instead of rowid
1623 BEGIN
1624 Select min(je_header_id), max(je_header_id)
1625 into l_jeh_min_id, l_jeh_max_id
1626 from gl_je_headers hd,
1627 PA_PRIM_REP_LEGER_tmp per
1628 where hd.LEDGER_ID = per.denorm_ledger_id
1629 and hd.PERIOD_NAME = per.PERIOD_NAME
1630 and hd.je_source = 'Project Accounting'
1631 and per.batch_id = p_batch_id;
1632
1633 EXCEPTION
1634 WHEN NO_DATA_FOUND THEN
1635 l_jeh_min_id := 0;
1636 l_jeh_max_id := 0;
1637 END;*/
1638
1639 l_jeh_min_id := null;
1640 l_jeh_max_id := null;
1641
1642 INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE, LEDGER_ID, BATCH_ID, STATUS)
1643 VALUES ('GL_JE_HEADERS', l_jeh_min_id, l_jeh_max_id, '', p_batch_id, 'P');
1644
1645 ---------------------------------------------------------------------------------------------------
1646 -- Min and max expenditure item ids are stored in the control table to drive the cost and cross
1647 -- charge upgrade
1648 ---------------------------------------------------------------------------------------------------
1649
1650 l_cost_cross := 0;
1651
1652 SELECT /*+ parallel(cdl) */ nvl(min(expenditure_item_id),0), nvl(max(expenditure_item_id),0)
1653 INTO l_cdl_min_eiid, l_cdl_max_eiid
1654 FROM pa_cost_distribution_lines_all cdl
1655 WHERE gl_date between l_start_date and l_end_date
1656 AND transfer_status_code = 'A'
1657 AND acct_event_id is null
1658 AND batch_name is not null;
1659
1660 If l_cdl_min_eiid = 0 and l_cdl_max_eiid = 0 then
1661 l_cost_cross := 3; -- Only Cross Charge Is there
1662 End If;
1663
1664 SELECT /*+ parallel(ccdl) */ nvl(min(expenditure_item_id),0), nvl(max(expenditure_item_id),0)
1665 INTO l_ccd_min_eiid, l_ccd_max_eiid
1666 FROM pa_cc_dist_lines_all ccdl
1667 WHERE gl_date between l_start_date and l_end_date
1668 AND transfer_status_code = 'A'
1669 AND acct_event_id is null
1670 AND gl_batch_name is not null;
1671
1672 If l_ccd_min_eiid = 0 and l_ccd_max_eiid = 0 then
1673 if l_cost_cross = 3 then
1674 l_cost_cross := 1; -- Neither Cost nor Cross Charge is there
1675 Else
1676 l_cost_cross := 2; -- Only cost is there
1677 End If;
1678 End If;
1679
1680 /* bug 5408944
1681 l_cost_cross Meaning
1682 ------------------ ---------
1683 0 Both Cost and Cross charge
1684 1 Neither Cost nor Cross Charge is there
1685 2 Only Cost is there
1686 3 Only Cross Charge is there
1687
1688 */
1689
1690 INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE, LEDGER_ID, BATCH_ID, STATUS)
1691 VALUES ('COST_CROSS_FLAG', l_cost_cross, l_cost_cross, '', p_batch_id, 'P');
1692
1693
1694 l_min_eiid := 0;
1695
1696 if l_cdl_min_eiid > 0 then
1697 l_min_eiid := l_cdl_min_eiid;
1698 end if;
1699
1700 if l_ccd_min_eiid > 0 then
1701 if l_min_eiid = 0 then
1702 l_min_eiid := l_ccd_min_eiid;
1703 else
1704 if l_ccd_min_eiid < l_min_eiid then
1705 l_min_eiid := l_ccd_min_eiid;
1706 end if;
1707 end if;
1708 end if;
1709
1710
1711 l_max_eiid := 0;
1712
1713 if l_cdl_max_eiid > 0 then
1714 l_max_eiid := l_cdl_max_eiid;
1715 end if;
1716
1717 if l_ccd_max_eiid > 0 then
1718 if l_ccd_max_eiid > l_max_eiid then
1719 l_max_eiid := l_ccd_max_eiid;
1720 end if;
1721 end if;
1722
1723
1724 INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE, LEDGER_ID, BATCH_ID, STATUS)
1725 VALUES ('PA_EXPENDITURE_ITEMS_ALL', l_min_eiid, l_max_eiid, '', p_batch_id, 'P');
1726
1727
1728
1729 ---------------------------------------------------------------------------------------------------
1730 -- If mrc is enabled, a record is inserted in the control table to drive mrc upgrade
1731 ---------------------------------------------------------------------------------------------------
1732
1733 BEGIN
1734 SELECT count(*)
1735 INTO l_cnt
1736 FROM gl_mc_reporting_options_11i
1737 WHERE application_id = 275
1738 AND enabled_flag = 'Y';
1739 EXCEPTION
1740 WHEN NO_DATA_FOUND THEN
1741 l_cnt := 0;
1742 END;
1743
1744 IF l_cnt > 0
1745 THEN
1746 INSERT INTO pa_xla_upg_ctrl (REFERENCE, MIN_VALUE, MAX_VALUE, LEDGER_ID, BATCH_ID, STATUS)
1747 VALUES ('MRC', l_min_eiid, l_max_eiid, '', p_batch_id, 'P');
1748
1749 x_mrc_enabled := 'Y';
1750 ELSE
1751 x_mrc_enabled := 'N';
1752 END IF;
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 RAISE;
1757
1758 END POPULATE_CTRL_TABLE;
1759
1760 END PA_XLA_UPGRADE;