[Home] [Help]
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)
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 */
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,
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
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
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,
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
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)