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