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