[Home] [Help]
4152: , coa_mapping_id
4153: )
4154: SELECT code_combination_id
4155: , sl_coa_mapping_id
4156: FROM xla_transaction_accts_gt
4157: WHERE code_combination_id IS NOT NULL
4158: AND processing_status_code IN (C_MAP_CCID , C_MAP_SEGMENT)
4159: AND sl_coa_mapping_id = p_gl_coa_mapping_id
4160: GROUP BY code_combination_id, sl_coa_mapping_id
4229: ,temp.segment29
4230: ,temp.segment30
4231: ,temp.code_combination_status_code) =
4232: (
4233: SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
4234: DISTINCT
4235: DECODE(gami.error_code, NULL, gami.to_ccid, -1)
4236: , nvl(temp.segment1 , gami.to_segment1)
4237: , nvl(temp.segment2 , gami.to_segment2)
4271: END
4272: ELSE C_INVALID
4273: END
4274: FROM gl_accts_map_int_gt gami
4275: , xla_transaction_accts_gt xta
4276: WHERE xta.ae_header_id = temp.ae_header_id
4277: AND xta.temp_line_num = temp.temp_line_num
4278: AND xta.ledger_id = temp.ledger_id
4279: AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
4286: )
4287: WHERE temp.code_combination_id IS NULL
4288: AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4289: AND temp.balance_type_code <> 'X'
4290: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
4291: FROM xla_transaction_accts_gt t
4292: WHERE t.ae_header_id = temp.ae_header_id
4293: AND t.temp_line_num = temp.temp_line_num
4294: AND t.ledger_id = temp.ledger_id
4287: WHERE temp.code_combination_id IS NULL
4288: AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4289: AND temp.balance_type_code <> 'X'
4290: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
4291: FROM xla_transaction_accts_gt t
4292: WHERE t.ae_header_id = temp.ae_header_id
4293: AND t.temp_line_num = temp.temp_line_num
4294: AND t.ledger_id = temp.ledger_id
4295: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4343: ,temp.alt_segment29
4344: ,temp.alt_segment30
4345: ,temp.alt_ccid_status_code) =
4346: (
4347: SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
4348: DISTINCT
4349: DECODE(gami.error_code, NULL, gami.to_ccid, -1)
4350: , nvl(temp.alt_segment1 , gami.to_segment1)
4351: , nvl(temp.alt_segment2 , gami.to_segment2)
4385: END
4386: ELSE C_INVALID
4387: END
4388: FROM gl_accts_map_int_gt gami
4389: , xla_transaction_accts_gt xta
4390: WHERE xta.ae_header_id = temp.ae_header_id
4391: AND xta.temp_line_num = temp.temp_line_num
4392: AND xta.ledger_id = temp.ledger_id
4393: AND xta.sl_coa_mapping_id = temp.sl_coa_mapping_id
4400: )
4401: WHERE temp.alt_code_combination_id IS NULL
4402: AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4403: AND temp.balance_type_code <> 'X'
4404: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
4405: FROM xla_transaction_accts_gt t
4406: WHERE t.ae_header_id = temp.ae_header_id
4407: AND t.temp_line_num = temp.temp_line_num
4408: AND t.ledger_id = temp.ledger_id
4401: WHERE temp.alt_code_combination_id IS NULL
4402: AND temp.sl_coa_mapping_id = p_gl_coa_mapping_id
4403: AND temp.balance_type_code <> 'X'
4404: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --added bug7673701
4405: FROM xla_transaction_accts_gt t
4406: WHERE t.ae_header_id = temp.ae_header_id
4407: AND t.temp_line_num = temp.temp_line_num
4408: AND t.ledger_id = temp.ledger_id
4409: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4509: , l_array_to_segment_code
4510: , l_array_ccid
4511: , l_array_segment_value
4512: FROM gl_code_combinations gami
4513: , xla_transaction_accts_gt xta
4514: WHERE gami.code_combination_id = xta.code_combination_id
4515: AND gami.chart_of_accounts_id = l_from_coa_id
4516: AND xta.code_combination_id IS NOT NULL
4517: AND xta.from_segment_code IS NOT NULL
4573: , l_array_to_segment_code
4574: , l_array_ccid
4575: , l_array_segment_value
4576: FROM gl_accts_map_int_gt gami
4577: , xla_transaction_accts_gt xta
4578: WHERE gami.from_ccid = xta.code_combination_id
4579: AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4580: AND xta.code_combination_id IS NOT NULL
4581: AND xta.from_segment_code IS NOT NULL
4624: ,temp.segment29
4625: ,temp.segment30
4626: ,temp.code_combination_status_code) =
4627: (
4628: SELECT /*+ INDEX(SEG XLA_TRANSACTION_ACCTS_GT_N1) */ DISTINCT --bug7673701
4629: DECODE(l_array_gl_map_status(i), C_INVALID, -1, temp.code_combination_id)
4630: , DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_segment_value(i), temp.segment1)
4631: , DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_segment_value(i), temp.segment2)
4632: , DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_segment_value(i), temp.segment3)
4664: WHEN C_CREATED THEN C_NOT_PROCESSED
4665: ELSE temp.code_combination_status_code
4666: END
4667: END
4668: FROM xla_transaction_accts_gt seg
4669: WHERE seg.ae_header_id = temp.ae_header_id
4670: AND seg.temp_line_num = temp.temp_line_num
4671: AND seg.ledger_id = temp.ledger_id
4672: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
4677: AND seg.to_segment_code = l_array_to_segment_code(i) --added 6660472 suggested by Kaouther
4678: AND seg.processing_status_code = l_array_processing_status_code(i)--added for bug6314762 to avoid single row subquery returns more than one row error
4679: )
4680: WHERE temp.balance_type_code <> 'X'
4681: AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
4682: FROM xla_transaction_accts_gt t
4683: WHERE t.ae_header_id = temp.ae_header_id
4684: AND t.temp_line_num = temp.temp_line_num
4685: AND t.ledger_id = temp.ledger_id
4678: AND seg.processing_status_code = l_array_processing_status_code(i)--added for bug6314762 to avoid single row subquery returns more than one row error
4679: )
4680: WHERE temp.balance_type_code <> 'X'
4681: AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
4682: FROM xla_transaction_accts_gt t
4683: WHERE t.ae_header_id = temp.ae_header_id
4684: AND t.temp_line_num = temp.temp_line_num
4685: AND t.ledger_id = temp.ledger_id
4686: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4760: , l_array_alt_to_segment_code
4761: , l_array_alt_ccid
4762: , l_array_alt_segment_value
4763: FROM gl_accts_map_int_gt gami
4764: , xla_transaction_accts_gt xta
4765: WHERE gami.from_ccid = xta.code_combination_id
4766: AND gami.coa_mapping_id = xta.sl_coa_mapping_id
4767: AND xta.code_combination_id IS NOT NULL
4768: AND xta.from_segment_code IS NOT NULL
4808: ,temp.alt_segment29
4809: ,temp.alt_segment30
4810: ,temp.alt_ccid_status_code) =
4811: (
4812: SELECT /*+ INDEX(SEG XLA_TRANSACTION_ACCTS_GT_N1) */ DISTINCT --bug7673701
4813: DECODE(l_array_alt_gl_map_status(i), C_INVALID, -1, temp.alt_code_combination_id)
4814: , DECODE(seg.to_segment_code,'SEGMENT1' ,l_array_alt_segment_value(i), temp.alt_segment1)
4815: , DECODE(seg.to_segment_code,'SEGMENT2' ,l_array_alt_segment_value(i), temp.alt_segment2)
4816: , DECODE(seg.to_segment_code,'SEGMENT3' ,l_array_alt_segment_value(i), temp.alt_segment3)
4848: WHEN C_CREATED THEN C_NOT_PROCESSED
4849: ELSE temp.alt_ccid_status_code
4850: END
4851: END
4852: FROM xla_transaction_accts_gt seg
4853: WHERE seg.ae_header_id = temp.ae_header_id
4854: AND seg.temp_line_num = temp.temp_line_num
4855: AND seg.ledger_id = temp.ledger_id
4856: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
4862: --AND seg.processing_status_code = l_array_processing_status_code(i) --added by for bug6314762 to avoid single row subquery returns more than one row error
4863: AND seg.processing_status_code = l_array_alt_proc_status_code(i) --corrected bug 8757043
4864: )
4865: WHERE temp.balance_type_code <> 'X'
4866: AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
4867: FROM xla_transaction_accts_gt t
4868: WHERE t.ae_header_id = temp.ae_header_id
4869: AND t.temp_line_num = temp.temp_line_num
4870: AND t.ledger_id = temp.ledger_id
4863: AND seg.processing_status_code = l_array_alt_proc_status_code(i) --corrected bug 8757043
4864: )
4865: WHERE temp.balance_type_code <> 'X'
4866: AND EXISTS (SELECT /*+ INDEX(t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
4867: FROM xla_transaction_accts_gt t
4868: WHERE t.ae_header_id = temp.ae_header_id
4869: AND t.temp_line_num = temp.temp_line_num
4870: AND t.ledger_id = temp.ledger_id
4871: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
4905: ,ledger_id
4906: ,entity_id
4907: ,from_ccid
4908: ,ccid_coa_id
4909: FROM (SELECT /*+ dynamic_sampling(1) INDEX (XTAG XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTAG,GAMI) */ DISTINCT
4910: gami.error_code error_code
4911: ,xjlg.event_id event_id
4912: ,xjlg.ledger_id ledger_id
4913: ,xjlg.entity_id entity_id
4913: ,xjlg.entity_id entity_id
4914: ,gami.from_ccid from_ccid
4915: ,xjlg.ccid_coa_id ccid_coa_id
4916: FROM gl_accts_map_int_gt gami
4917: ,xla_transaction_accts_gt xtag
4918: ,xla_ae_lines_gt xjlg
4919: WHERE xjlg.ae_header_id = xtag.ae_header_id
4920: AND xjlg.temp_line_num = xtag.temp_line_num
4921: AND xjlg.ledger_id = xtag.ledger_id
4928: AND gami.error_code IS NOT NULL
4929: AND xjlg.code_combination_id = -1
4930: AND xjlg.balance_type_code <> 'X'
4931: UNION
4932: SELECT /*+ dynamic_sampling(1) INDEX (XTAG XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTAG,GAMI) */ DISTINCT
4933: gami.error_code error_code
4934: ,xjlg.event_id event_id
4935: ,xjlg.ledger_id ledger_id
4936: ,xjlg.entity_id entity_id
4936: ,xjlg.entity_id entity_id
4937: ,gami.from_ccid from_ccid
4938: , xjlg.ccid_coa_id coa_id
4939: FROM gl_accts_map_int_gt gami
4940: ,xla_transaction_accts_gt xtag
4941: ,xla_ae_lines_gt xjlg
4942: WHERE xjlg.ae_header_id = xtag.ae_header_id
4943: AND xjlg.temp_line_num = xtag.temp_line_num
4944: AND xjlg.ledger_id = xtag.ledger_id
5075:
5076: l_count := 0;
5077:
5078: FOR qualifier_rec IN (SELECT xtag.from_segment_code qualifier
5079: FROM xla_transaction_accts_gt xtag
5080: WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
5081: AND xtag.processing_status_code = 'MAP_QUALIFIER'
5082: GROUP BY xtag.from_segment_code )
5083: LOOP
5089:
5090: INSERT INTO gl_accts_map_bsv_gt
5091: ( SOURCE_BSV )
5092: SELECT segment
5093: FROM xla_transaction_accts_gt xtag
5094: WHERE xtag.sl_coa_mapping_id = p_gl_coa_mapping_id
5095: AND xtag.processing_status_code = 'MAP_QUALIFIER'
5096: AND xtag.from_segment_code = qualifier_rec.qualifier
5097: GROUP BY segment
5148: UPDATE xla_ae_lines_gt temp
5149: SET temp.code_combination_id = -1
5150: ,temp.code_combination_status_code = C_INVALID
5151: WHERE temp.balance_type_code <> 'X'
5152: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5153: FROM xla_transaction_accts_gt t
5154: WHERE t.ae_header_id = temp.ae_header_id
5155: AND t.temp_line_num = temp.temp_line_num
5156: AND t.ledger_id = temp.ledger_id
5149: SET temp.code_combination_id = -1
5150: ,temp.code_combination_status_code = C_INVALID
5151: WHERE temp.balance_type_code <> 'X'
5152: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5153: FROM xla_transaction_accts_gt t
5154: WHERE t.ae_header_id = temp.ae_header_id
5155: AND t.temp_line_num = temp.temp_line_num
5156: AND t.ledger_id = temp.ledger_id
5157: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5213: UPDATE xla_ae_lines_gt temp
5214: SET temp.alt_code_combination_id = -1
5215: ,temp.alt_ccid_status_code = C_INVALID
5216: WHERE temp.balance_type_code <> 'X'
5217: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5218: FROM xla_transaction_accts_gt t
5219: WHERE t.ae_header_id = temp.ae_header_id
5220: AND t.temp_line_num = temp.temp_line_num
5221: AND t.ledger_id = temp.ledger_id
5214: SET temp.alt_code_combination_id = -1
5215: ,temp.alt_ccid_status_code = C_INVALID
5216: WHERE temp.balance_type_code <> 'X'
5217: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5218: FROM xla_transaction_accts_gt t
5219: WHERE t.ae_header_id = temp.ae_header_id
5220: AND t.temp_line_num = temp.temp_line_num
5221: AND t.ledger_id = temp.ledger_id
5222: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5337: WHEN C_CREATED THEN C_NOT_PROCESSED
5338: ELSE temp.code_combination_status_code
5339: END
5340: FROM (
5341: SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */ DISTINCT --bug7673701
5342: xta.ae_header_id ae_header_id
5343: , xta.temp_line_num temp_line_num
5344: , xta.ledger_id ledger_id
5345: , xta.sl_coa_mapping_id sl_coa_mapping_id
5345: , xta.sl_coa_mapping_id sl_coa_mapping_id
5346: , xta.to_segment_code to_segment_code
5347: , gami.target_bsv target_value
5348: FROM gl_accts_map_bsv_gt gami
5349: , xla_transaction_accts_gt xta
5350: WHERE gami.source_bsv = xta.segment
5351: AND xta.from_segment_code = qualifier_rec.qualifier
5352: AND xta.processing_status_code = 'MAP_QUALIFIER'
5353: AND xta.side_code IN ('ALL','CREDIT','NA')
5358: AND seg.ledger_id = temp.ledger_id
5359: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5360: )
5361: WHERE temp.balance_type_code <> 'X'
5362: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5363: FROM xla_transaction_accts_gt t
5364: WHERE t.ae_header_id = temp.ae_header_id
5365: AND t.temp_line_num = temp.temp_line_num
5366: AND t.ledger_id = temp.ledger_id
5359: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5360: )
5361: WHERE temp.balance_type_code <> 'X'
5362: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5363: FROM xla_transaction_accts_gt t
5364: WHERE t.ae_header_id = temp.ae_header_id
5365: AND t.temp_line_num = temp.temp_line_num
5366: AND t.ledger_id = temp.ledger_id
5367: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id
5451: WHEN C_CREATED THEN C_NOT_PROCESSED
5452: ELSE temp.alt_ccid_status_code
5453: END
5454: FROM (
5455: SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */ DISTINCT --bug7673701
5456: xta.ae_header_id ae_header_id
5457: , xta.temp_line_num temp_line_num
5458: , xta.ledger_id ledger_id
5459: , xta.sl_coa_mapping_id sl_coa_mapping_id
5459: , xta.sl_coa_mapping_id sl_coa_mapping_id
5460: , xta.to_segment_code to_segment_code
5461: , gami.target_bsv target_value
5462: FROM gl_accts_map_bsv_gt gami
5463: , xla_transaction_accts_gt xta
5464: WHERE gami.source_bsv = xta.segment
5465: AND xta.from_segment_code = qualifier_rec.qualifier
5466: AND xta.processing_status_code = 'MAP_QUALIFIER'
5467: AND xta.side_code IN ('ALL','DEBIT')
5472: AND seg.ledger_id = temp.ledger_id
5473: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5474: )
5475: WHERE temp.balance_type_code <> 'X'
5476: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5477: FROM xla_transaction_accts_gt t
5478: WHERE t.ae_header_id = temp.ae_header_id
5479: AND t.temp_line_num = temp.temp_line_num
5480: AND t.ledger_id = temp.ledger_id
5473: AND seg.sl_coa_mapping_id = temp.sl_coa_mapping_id
5474: )
5475: WHERE temp.balance_type_code <> 'X'
5476: AND EXISTS (SELECT /*+ INDEX (t XLA_TRANSACTION_ACCTS_GT_N1) */ 'x' --bug7673701
5477: FROM xla_transaction_accts_gt t
5478: WHERE t.ae_header_id = temp.ae_header_id
5479: AND t.temp_line_num = temp.temp_line_num
5480: AND t.ledger_id = temp.ledger_id
5481: AND t.sl_coa_mapping_id = temp.sl_coa_mapping_id