[Home] [Help]
224: CURSOR c_ccid IS
225: SELECT t.bal_seg_value
226: ,t.code_combination_id
227: ,min(t.accounting_Date) accounting_date
228: FROM xla_validation_lines_gt t
229: WHERE substituted_by_suspense_flag = 'Y'
230: GROUP BY t.bal_seg_value
231: ,t.code_combination_id
232: ;
240: ,event_id
241: ,ae_header_id
242: ,bal_seg_value
243: ,SUSPENSE_CODE_COMBINATION_ID
244: FROM xla_validation_lines_gt
245: WHERE substituted_by_suspense_flag = 'Y'
246: AND code_combination_id < 0
247: GROUP BY entity_id, event_id, ae_header_id ,bal_seg_value,SUSPENSE_CODE_COMBINATION_ID ;
248:
357:
358: k := i-1 ;
359:
360: FORALL j IN 1..k
361: UPDATE xla_validation_lines_gt t
362: SET code_combination_id = l_new_ccids(j)
363: WHERE t.substituted_by_suspense_flag = 'Y'
364: AND t.code_combination_id = l_old_ccids(j)
365: AND t.bal_seg_value = l_bal_seg_values(j);
837: AND asa.access_set_id in (g_pri_access_set_id, g_sec_access_set_id);
838:
839: CURSOR c_err IS
840: SELECT entity_id, event_id, ae_header_id
841: FROM xla_validation_lines_gt;
842:
843: l_err c_err%ROWTYPE;
844: l_ledger_id INTEGER;
845: l_log_module VARCHAR2(240);
953: IS
954: CURSOR c_full_bal IS
955: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
956: ,NULL segment_value
957: FROM xla_validation_lines_gt t
958: LEFT OUTER JOIN gl_access_set_assignments asa
959: on asa.ledger_id = g_ledger_id
960: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
961: AND asa.access_set_id = g_pri_access_set_id
969:
970: CURSOR c_full_mgt IS
971: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
972: ,NULL segment_value
973: FROM xla_validation_lines_gt t
974: LEFT OUTER JOIN gl_access_set_assignments asa
975: on asa.ledger_id = g_ledger_id
976: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
977: AND asa.access_set_id = g_pri_access_set_id
985:
986: CURSOR c_bal_none IS
987: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
988: ,t.bal_seg_value segment_value
989: FROM xla_validation_lines_gt t
990: LEFT OUTER JOIN gl_access_set_assignments asa
991: ON asa.segment_value = t.bal_seg_value
992: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
993: AND asa.ledger_id = g_ledger_id
996:
997: CURSOR c_bal_bal IS
998: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
999: ,t.bal_seg_value segment_value
1000: FROM xla_validation_lines_gt t
1001: LEFT OUTER JOIN gl_access_set_assignments asa
1002: ON asa.segment_value = t.bal_seg_value
1003: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
1004: AND asa.ledger_id = g_ledger_id
1007:
1008: CURSOR c_bal_mgt IS
1009: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
1010: ,t.bal_seg_value segment_value
1011: FROM xla_validation_lines_gt t
1012: LEFT OUTER JOIN gl_access_set_assignments asa
1013: on asa.segment_value = t.bal_seg_value
1014: AND asa.ledger_id = g_ledger_id
1015: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
1024:
1025: CURSOR c_mgt_none IS
1026: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
1027: ,t.mgt_seg_value segment_value
1028: FROM xla_validation_lines_gt t
1029: LEFT OUTER JOIN gl_access_set_assignments asa
1030: ON asa.segment_value = t.mgt_seg_value
1031: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
1032: AND asa.ledger_id = g_ledger_id
1035:
1036: CURSOR c_mgt_mgt IS
1037: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
1038: ,t.mgt_seg_value segment_value
1039: FROM xla_validation_lines_gt t
1040: LEFT OUTER JOIN gl_access_set_assignments asa
1041: ON asa.segment_value = t.mgt_seg_value
1042: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
1043: AND asa.ledger_id = g_ledger_id
1390: --
1391: -- If running in BC mode, do not use suspense account nor substitute accounts.
1392: --
1393: l_stmt := '
1394: INSERT INTO xla_validation_lines_gt
1395: (ae_header_id
1396: ,ae_line_num
1397: ,ledger_id
1398: ,displayed_line_number
1587: END IF;
1588:
1589: ELSIF(g_suspense_allowed_flag = 'Y') THEN
1590: l_stmt := '
1591: INSERT INTO xla_validation_lines_gt
1592: (ae_header_id
1593: ,ae_line_num
1594: ,ledger_id
1595: ,displayed_line_number
2079:
2080: --bug#10047740 Suspense BSV should be the Original CCID BSV
2081: -- hence commented out the update of BAL_SEG_VALUE
2082: l_stmt:=
2083: 'UPDATE xla_validation_lines_gt l
2084: SET
2085: ( -- l.bal_seg_value,
2086: l.mgt_seg_value
2087: ,l.cost_center_seg_value
2202: END IF;
2203:
2204: ELSE -- (g_sla_bal_by_ledger_curr_flag <> 'Y')
2205: l_stmt := '
2206: INSERT INTO xla_validation_lines_gt
2207: (ae_header_id
2208: ,ae_line_num
2209: ,ledger_id
2210: ,displayed_line_number
2576: UPDATE xla_ae_headers
2577: SET zero_amount_flag = 'Y'
2578: WHERE application_id = g_application_id and
2579: ae_header_id in
2580: (select /*+ cardinality(XLA_VALIDATION_LINES_GT, 1) */ ae_header_id --bug9174950
2581: from xla_validation_lines_gt
2582: group by ae_header_id
2583: having sum(abs(accounted_cr)) = 0 and sum(abs(accounted_dr))=0);
2584:
2577: SET zero_amount_flag = 'Y'
2578: WHERE application_id = g_application_id and
2579: ae_header_id in
2580: (select /*+ cardinality(XLA_VALIDATION_LINES_GT, 1) */ ae_header_id --bug9174950
2581: from xla_validation_lines_gt
2582: group by ae_header_id
2583: having sum(abs(accounted_cr)) = 0 and sum(abs(accounted_dr))=0);
2584:
2585: IF (p_budgetary_control_mode = 'NONE') THEN
2585: IF (p_budgetary_control_mode = 'NONE') THEN
2586: UPDATE /*+ index(XAL,XLA_AE_LINES_U1)*/ xla_ae_lines xal -- 4769388
2587: SET (code_combination_id, substituted_ccid)=
2588: (SELECT code_combination_id, substituted_ccid
2589: FROM xla_validation_lines_gt xvlg
2590: WHERE xvlg.ae_header_id = xal.ae_header_id
2591: AND xvlg.ae_line_num = xal.ae_line_num)
2592: WHERE xal.application_id = g_application_id
2593: AND (xal.ae_header_id, xal.ae_line_num) in
2592: WHERE xal.application_id = g_application_id
2593: AND (xal.ae_header_id, xal.ae_line_num) in
2594: (select /*+ unnest cardinality(GT,10)*/ -- 4769388
2595: ae_header_id, ae_line_num
2596: from xla_validation_lines_gt GT -- 4769388
2597: where substituted_ccid is not NULL);
2598:
2599: IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2600: trace(p_msg => '# of rows updated to xla_ae_lines:'||to_char(SQL%ROWCOUNT),
2605:
2606:
2607: ELSE -- (g_caller <> mpa or acct program or third party merge)
2608:
2609: INSERT INTO xla_validation_lines_gt
2610: (ae_header_id
2611: ,ae_line_num
2612: ,ledger_id
2613: ,displayed_line_number
3102: ,h.event_id
3103: ,h.encumbrance_type_id
3104: ,e.encumbrance_type -- 5522973
3105: ,e.enabled_flag encum_type_enabled_flag
3106: FROM xla_validation_lines_gt h
3107: LEFT OUTER JOIN gl_encumbrance_types e
3108: ON e.encumbrance_type_id = h.encumbrance_type_id
3109: WHERE h.ledger_id = g_ledger_id
3110: AND h.balance_type_code = 'E'
3451: AND gtd.transaction_calendar_id = g_transaction_calendar_id
3452: AND gtd.business_day_flag = 'N'
3453: --added for bug 9839027
3454: and xah.ae_header_id in
3455: (select lgt.ae_header_id from xla_validation_lines_gt lgt)
3456: and xah.application_id = g_application_id;
3457:
3458: l_eff_date_rule_code VARCHAR2(1);
3459: l_log_module VARCHAR2(240);
3882: (p_seg_ledger_id INTEGER)
3883: IS
3884: CURSOR c_invalid_bal_segment IS
3885: SELECT t.*
3886: FROM xla_validation_lines_gt t
3887: LEFT OUTER JOIN gl_ledger_segment_values s
3888: ON s.segment_value = t.bal_seg_value
3889: AND s.segment_type_code = C_BAL_SEGMENT
3890: AND s.ledger_id = p_seg_ledger_id
4004: (p_seg_ledger_id INTEGER)
4005: IS
4006: CURSOR c_invalid_mgt_segment IS
4007: SELECT t.*
4008: FROM xla_validation_lines_gt t
4009: LEFT OUTER JOIN gl_ledger_segment_values s
4010: ON s.segment_value = t.mgt_seg_value
4011: AND s.segment_type_code = C_MGT_SEGMENT
4012: AND s.ledger_id = p_seg_ledger_id
4144: t.entity_id,
4145: t.party_type_code, t.party_id, t.party_site_id,
4146: c.cust_account_id customer_id, ps.site_use_id customer_site_id,
4147: s.vendor_id, ss.vendor_site_id
4148: FROM xla_validation_lines_gt t
4149: LEFT OUTER JOIN hz_cust_accounts_all c
4150: ON c.cust_account_id = t.party_id
4151: LEFT OUTER JOIN hz_cust_site_uses_all ps
4152: ON ps.site_use_id = t.party_site_id
4328: ,t.entered_currency_code
4329: ,curr.enabled_flag curr_enabled_flag
4330: ,curr.start_date_active curr_start_date_active
4331: ,curr.end_date_active curr_end_date_active
4332: FROM xla_validation_lines_gt t
4333: LEFT OUTER JOIN fnd_currencies curr
4334: ON curr.currency_code = t.entered_currency_code
4335: WHERE (curr.enabled_flag IS NULL) OR
4336: (curr.enabled_flag = 'N') OR
4473: ,t.displayed_line_number
4474: ,t.entity_id
4475: ,bud.budget_name
4476: ,fnd_flex_ext.get_segs('SQLGL', 'GL#', t.ccid_coa_id, t.code_combination_id) account
4477: FROM xla_validation_lines_gt t
4478: JOIN gl_budget_versions bud
4479: ON bud.budget_version_id = t.budget_version_id
4480: LEFT OUTER JOIN gl_budget_assignments b
4481: ON b.currency_code = t.entered_currency_code
4577: ,t.event_id
4578: ,t.displayed_line_number
4579: ,t.entity_id
4580: ,t.accounting_class_code
4581: FROM xla_validation_lines_gt t
4582: LEFT OUTER JOIN xla_lookups lk
4583: ON lk.lookup_type = 'XLA_ACCOUNTING_CLASS'
4584: AND lk.lookup_code = t.accounting_class_code
4585: WHERE lk.lookup_code IS NULL
4688: l_ccid INTEGER;
4689:
4690: CURSOR c_line_error IS
4691: SELECT *
4692: FROM xla_validation_lines_gt
4693: WHERE error_flag = 'Y';
4694:
4695: CURSOR c_account(p_coa_id INTEGER, p_code_combination_id INTEGER) IS
4696: SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', p_coa_id, p_code_combination_id)
5599: Exhausted Undo Tablespace when a single header has many lines.
5600: The following sql updates # of errors * # of lines.
5601:
5602: FORALL i IN l_prev_err_count+1..g_err_count
5603: UPDATE xla_validation_lines_gt
5604: set balancing_line_type = C_LINE_TYPE_COMPLETE
5605: WHERE ae_header_id = g_err_hdr_ids(i); */
5606:
5607: --
5605: WHERE ae_header_id = g_err_hdr_ids(i); */
5606:
5607: --
5608: -- Bug 7128871
5609: -- Update xla_validation_lines_gt for distinct ae header ids.
5610: --
5611:
5612: FOR i IN g_prev_err_count+1..g_err_count LOOP
5613: IF NOT l_temp_distinct_hdr_ids.EXISTS(g_err_hdr_ids(i)) THEN
5619: -- As indices of l_dinstinct_hdr_ids are not consecutive,
5620: -- need to use "INDICES OF".
5621: --
5622: FORALL i IN INDICES OF l_distinct_hdr_ids
5623: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
5624: */ XLA_VALIDATION_LINES_GT
5625: SET balancing_line_type = C_LINE_TYPE_COMPLETE
5626: WHERE ae_header_id = l_distinct_hdr_ids(i);
5627: END IF;
5620: -- need to use "INDICES OF".
5621: --
5622: FORALL i IN INDICES OF l_distinct_hdr_ids
5623: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
5624: */ XLA_VALIDATION_LINES_GT
5625: SET balancing_line_type = C_LINE_TYPE_COMPLETE
5626: WHERE ae_header_id = l_distinct_hdr_ids(i);
5627: END IF;
5628:
5667: , accounting_date accounting_date
5668: , party_type_code party_type_code
5669: , party_id party_id
5670: , party_site_id party_site_id
5671: FROM xla_validation_lines_gt
5672: WHERE ae_header_id = p_ae_header_id
5673: AND entered_currency_code <> 'STAT' -- added for bug#10166812
5674: GROUP BY bal_seg_value
5675: , entered_currency_code
5913: l_bal.entered_dr := l_bal.entered_cr - l_bal.entered_dr;
5914: l_bal.entered_cr := NULL;
5915: END IF;
5916:
5917: INSERT INTO xla_validation_lines_gt
5918: (balancing_line_type
5919: ,ledger_id
5920: ,ae_header_id
5921: ,ae_line_num
6033: CURSOR c_bal(p_rounding_offset NUMBER) is
6034: SELECT ae_header_id
6035: ,entity_id
6036: ,event_id
6037: FROM xla_validation_lines_gt
6038: WHERE balance_type_code = 'A' -- <> 'B' -- 4458381
6039: AND entered_currency_code <> 'STAT'
6040: AND balancing_line_type = C_LINE_TYPE_PROCESS
6041: GROUP BY ae_header_id, entity_id , event_id
6115: END IF;
6116:
6117: IF (l_comp_count>0) THEN
6118: FORALL j in 1..l_comp_count
6119: UPDATE xla_validation_lines_gt
6120: SET balancing_line_type = C_LINE_TYPE_COMPLETE
6121: WHERE ae_header_id = l_comp_hdr_ids(j)
6122: AND balancing_line_type = C_LINE_TYPE_PROCESS;
6123: END IF;
6162: ,p_rounding_offset NUMBER) IS
6163: SELECT ae_header_id
6164: ,entity_id
6165: ,event_id
6166: FROM xla_validation_lines_gt
6167: WHERE balance_type_code = 'A'
6168: AND entered_currency_code <> 'STAT'
6169: AND balancing_line_type = C_LINE_TYPE_PROCESS
6170: GROUP BY ae_header_id, entity_id, event_id
6336: END IF;
6337:
6338: IF (l_comp_count>0) THEN
6339: FORALL j in 1..l_comp_count
6340: UPDATE xla_validation_lines_gt
6341: SET balancing_line_type = C_LINE_TYPE_COMPLETE
6342: WHERE ae_header_id = l_comp_hdr_ids(j)
6343: AND balancing_line_type = C_LINE_TYPE_PROCESS;
6344: END IF;
6385: ,max_displayed_line_number max_disp_line_num
6386: ,accounting_date accounting_date
6387: ,entity_id
6388: ,event_id
6389: FROM XLA_VALIDATION_LINES_GT
6390: WHERE balancing_line_type in (C_LINE_TYPE_PROCESS
6391: ,C_LINE_TYPE_IC_BAL_INTER
6392: ,C_LINE_TYPE_IC_BAL_INTRA
6393: ,C_LINE_TYPE_XLA_BALANCING
6709: l_bal.entered_cr := NULL;
6710: END IF;
6711:
6712:
6713: INSERT INTO xla_validation_lines_gt
6714: (balancing_line_type
6715: ,ledger_id
6716: ,ae_header_id
6717: ,ae_line_num
6795: END LOOP;
6796:
6797: -- Bug 7529475
6798: /*
6799: update xla_validation_lines_gt xgt
6800: set xgt.encumbrance_type_id = (SELECT xll.encumbrance_type_id
6801: FROM xla_validation_lines_gt xll
6802: WHERE xll.ae_header_id = xgt.ae_header_id
6803: and xll.balancing_line_type=C_LINE_TYPE_ENC_BALANCING
6797: -- Bug 7529475
6798: /*
6799: update xla_validation_lines_gt xgt
6800: set xgt.encumbrance_type_id = (SELECT xll.encumbrance_type_id
6801: FROM xla_validation_lines_gt xll
6802: WHERE xll.ae_header_id = xgt.ae_header_id
6803: and xll.balancing_line_type=C_LINE_TYPE_ENC_BALANCING
6804: and xll.balance_type_code <> 'B'
6805: AND xll.entered_currency_code <> 'STAT'
7207:
7208: -- 10180336 begin
7209: IF (p_err_count>0) THEN
7210: FORALL j IN 1..p_err_count
7211: UPDATE xla_validation_lines_gt
7212: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7213: WHERE ae_header_id = p_err_ae_header_ids(j)
7214: AND balancing_line_type = C_LINE_TYPE_PROCESS;
7215: END IF;
7263: ,je_category_name
7264: ,accounting_date
7265: ,event_id
7266: ,entity_id
7267: FROM xla_validation_lines_gt l
7268: WHERE balance_type_code = 'A'
7269: AND entered_currency_code <> 'STAT'
7270: AND balancing_line_type = C_LINE_TYPE_PROCESS
7271: GROUP BY ae_header_id
7518: ,l.currency_conversion_type
7519: ,l.unrounded_accounted_dr
7520: ,l.unrounded_accounted_cr
7521: ,'N'
7522: FROM xla_validation_lines_gt l
7523: ,fun_bal_headers_gt h
7524: WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
7525: AND l.ae_header_id = h.group_id
7526: AND l.entered_currency_code <> 'STAT' -- added for bug#10166812
7562: ,l.currency_conversion_type
7563: ,l.unrounded_accounted_cr
7564: ,l.unrounded_accounted_dr
7565: ,'N'
7566: FROM xla_validation_lines_gt l
7567: ,fun_bal_headers_gt h
7568: WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
7569: AND l.ae_header_id = h.group_id * -1
7570: AND l.entered_currency_code <> 'STAT' -- added for bug#10166812
7621: END IF;
7622: create_intercompany_errors(l_err_count, l_err_hdr_ids);
7623: END IF;
7624:
7625: INSERT INTO xla_validation_lines_gt(
7626: ae_header_id
7627: ,ae_line_num
7628: ,displayed_line_number
7629: ,max_ae_line_num
7693: ,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
7694: C_LINE_TYPE_IC_BAL_INTER)
7695: ,l.balance_type_code
7696: FROM fun_bal_results_gt res
7697: ,xla_validation_lines_gt l
7698: ,gl_code_combinations ccid
7699: ,fnd_currencies fcu
7700: WHERE l.ae_line_num = l.max_ae_line_num
7701: AND l.ae_header_id = res.group_id
7715: END IF;
7716:
7717:
7718:
7719: INSERT INTO xla_validation_lines_gt(
7720: ae_header_id
7721: ,ae_line_num
7722: ,displayed_line_number
7723: ,max_ae_line_num
7791: ,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
7792: C_LINE_TYPE_IC_BAL_INTER)
7793: ,l.balance_type_code
7794: FROM fun_bal_results_gt res
7795: ,xla_validation_lines_gt l
7796: ,gl_code_combinations ccid
7797: ,fnd_currencies fcu
7798: WHERE l.ae_line_num = l.max_ae_line_num
7799: AND l.balancing_line_type NOT IN (C_LINE_TYPE_IC_BAL_INTRA, C_LINE_TYPE_IC_BAL_INTER)
7823: /* Commented out for bug 7128871
7824: Exhausted Undo Tablespace when a single header has many lines.
7825:
7826: FORALL j IN 1..l_err_count
7827: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7828: XLA_VALIDATION_LINES_GT
7829: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7830: WHERE ae_header_id = l_err_hdr_ids(j); */
7831:
7824: Exhausted Undo Tablespace when a single header has many lines.
7825:
7826: FORALL j IN 1..l_err_count
7827: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7828: XLA_VALIDATION_LINES_GT
7829: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7830: WHERE ae_header_id = l_err_hdr_ids(j); */
7831:
7832: --
7840: END IF;
7841: END LOOP;
7842: --
7843: -- Bug 7128871
7844: -- Update xla_validation_lines_gt for distinct ae header ids.
7845: -- As indices of l_dinstinct_hdr_ids are not consecutive,
7846: -- need to use "INDICES OF".
7847: --
7848: FORALL i IN INDICES OF l_distinct_hdr_ids
7845: -- As indices of l_dinstinct_hdr_ids are not consecutive,
7846: -- need to use "INDICES OF".
7847: --
7848: FORALL i IN INDICES OF l_distinct_hdr_ids
7849: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7850: */ XLA_VALIDATION_LINES_GT
7851: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7852: WHERE ae_header_id = l_distinct_hdr_ids(i);
7853:
7846: -- need to use "INDICES OF".
7847: --
7848: FORALL i IN INDICES OF l_distinct_hdr_ids
7849: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7850: */ XLA_VALIDATION_LINES_GT
7851: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7852: WHERE ae_header_id = l_distinct_hdr_ids(i);
7853:
7854: IF (C_LEVEL_EVENT >= g_log_level) THEN
7913: CURSOR c_ccid IS
7914: SELECT t.bal_seg_value
7915: ,t.balance_type_code -- 4458381
7916: ,min(t.accounting_date) accounting_date
7917: FROM xla_validation_lines_gt t
7918: WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
7919: ,C_LINE_TYPE_ENC_BALANCING)
7920: AND t.code_combination_id IS NULL
7921: AND ((g_res_encumb_ccid IS NOT NULL AND t.balance_type_code = 'E') OR
7925: CURSOR c_errors IS
7926: SELECT entity_id
7927: ,event_id
7928: ,ae_header_id
7929: FROM xla_validation_lines_gt
7930: WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
7931: ,C_LINE_TYPE_ENC_BALANCING)
7932: AND code_combination_id < 0
7933: GROUP BY entity_id, event_id, ae_header_id;
8103: END IF;
8104: END LOOP;
8105:
8106: FORALL i IN 1..j
8107: UPDATE xla_validation_lines_gt t
8108: SET code_combination_id = l_ccids(i)
8109: ,control_account_enabled_flag = l_reference3s(i)
8110: ,mgt_seg_value = l_mgt_seg_values(i)
8111: WHERE t.bal_seg_value = l_bal_seg_values(i)
8233:
8234:
8235: CURSOR c_no_sus_ccid IS
8236: SELECT entity_id, event_id, ae_header_id, balance_type_code
8237: FROM xla_validation_lines_gt
8238: WHERE balancing_line_type = C_LINE_TYPE_XLA_BALANCING
8239: AND balance_type_code = 'A'
8240: GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
8241:
8295: END IF;
8296:
8297: SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
8298: BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
8299: FROM xla_validation_lines_gt t
8300: WHERE balance_type_code not in('E','B')
8301: AND entered_currency_code <> 'STAT'
8302: AND balancing_line_type in (C_LINE_TYPE_PROCESS,
8303: C_LINE_TYPE_IC_BAL_INTER,
8328: -- Added balancing segment value in select clause and group by clause for bug 12710754
8329:
8330: SELECT DISTINCT xvl.ae_header_id, xvl.bal_seg_value, xvl.entered_currency_code
8331: BULK COLLECT INTO l_already_bal_hdr_ids, l_bal_seg_value, l_already_bal_ent_currs
8332: FROM xla_validation_lines_gt xvl
8333: WHERE xvl.ae_header_id IN (
8334:
8335: SELECT t.ae_header_id
8336: FROM xla_validation_lines_gt t
8332: FROM xla_validation_lines_gt xvl
8333: WHERE xvl.ae_header_id IN (
8334:
8335: SELECT t.ae_header_id
8336: FROM xla_validation_lines_gt t
8337: WHERE t.balance_type_code not in('E','B')
8338: AND t.entered_currency_code <> 'STAT'
8339: AND t.balancing_line_type in (C_LINE_TYPE_PROCESS,
8340: C_LINE_TYPE_IC_BAL_INTER,
8383: --
8384: /* bug 9127520 start, for all commented out then replaced below
8385:
8386: FORALL i IN 1..l_bal_hdr_ids.COUNT
8387: INSERT INTO xla_validation_lines_gt
8388: (balancing_line_type
8389: ,ledger_id
8390: ,ae_header_id
8391: ,max_ae_line_num
8482: ,t.party_type_code
8483: ,t.party_id
8484: ,t.party_site_id
8485: ,NULL
8486: FROM xla_validation_lines_gt t
8487: WHERE ae_header_id = l_bal_hdr_ids(i)
8488: AND entered_currency_code = l_bal_ent_currs(i)
8489: AND bal_seg_value = l_bal_bal_segs(i)
8490: AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
8515: OR sum(nvl(unrounded_accounted_dr,0)) <> 0; */
8516:
8517:
8518: FORALL i IN 1..l_bal_hdr_ids.COUNT
8519: INSERT INTO xla_validation_lines_gt
8520: (balancing_line_type
8521: ,ledger_id
8522: ,ae_header_id
8523: ,max_ae_line_num
8614: ,t.party_type_code
8615: ,t.party_id
8616: ,t.party_site_id
8617: ,NULL
8618: FROM xla_validation_lines_gt t
8619: WHERE ae_header_id = l_bal_hdr_ids(i)
8620: AND entered_currency_code = l_bal_ent_currs(i)
8621: AND bal_seg_value = l_bal_bal_segs(i)
8622: AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
8666:
8667: IF (l_already_bal_hdr_ids.COUNT > 0) THEN
8668:
8669: FORALL i IN 1..l_already_bal_hdr_ids.COUNT
8670: INSERT INTO xla_validation_lines_gt
8671: (balancing_line_type
8672: ,ledger_id
8673: ,ae_header_id
8674: ,max_ae_line_num
8721: ,t.party_type_code
8722: ,t.party_id
8723: ,t.party_site_id
8724: ,NULL
8725: FROM xla_validation_lines_gt t
8726: WHERE t.ae_header_id = l_already_bal_hdr_ids(i)
8727: AND t.entered_currency_code = l_already_bal_ent_currs(i)
8728: --Added for 12710754 start
8729: AND t.bal_seg_value = l_bal_seg_value(i)
8802: END IF;
8803:
8804: END IF;
8805:
8806: UPDATE xla_validation_lines_gt t
8807: SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
8808: SELECT nc.code_combination_id
8809: ,nc.reference3
8810: ,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
8892: populate_missing_ccid(l_err_count, l_err_hdr_ids);
8893:
8894: IF (l_err_count>0) THEN
8895: FORALL j IN 1..l_err_count
8896: UPDATE xla_validation_lines_gt
8897: SET balancing_line_type = C_LINE_TYPE_COMPLETE
8898: WHERE ae_header_id = l_err_hdr_ids(j)
8899: AND balancing_line_type = C_LINE_TYPE_PROCESS;
8900: END IF;
8954:
8955:
8956: CURSOR c_no_sus_ccid IS
8957: SELECT entity_id, event_id, ae_header_id, balance_type_code
8958: FROM xla_validation_lines_gt
8959: WHERE balancing_line_type = C_LINE_TYPE_ENC_BALANCING
8960: GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
8961:
8962: BEGIN
9015: END IF;
9016:
9017: SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
9018: BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
9019: FROM xla_validation_lines_gt t
9020: WHERE balance_type_code = 'E'
9021: AND entered_currency_code <> 'STAT'
9022: AND balancing_line_type in (C_LINE_TYPE_PROCESS,
9023: C_LINE_TYPE_IC_BAL_INTER,
9056: -- Note: Debit and Credit line are created by using two insert statement
9057: -- to prevent the 0 amount lines from being created
9058: --
9059: FORALL i IN 1..l_bal_hdr_ids.COUNT
9060: INSERT INTO xla_validation_lines_gt
9061: (balancing_line_type
9062: ,ledger_id
9063: ,ae_header_id
9064: ,max_ae_line_num
9155: ,t.party_type_code
9156: ,t.party_id
9157: ,t.party_site_id
9158: ,NULL
9159: FROM xla_validation_lines_gt t
9160: WHERE ae_header_id = l_bal_hdr_ids(i)
9161: AND entered_currency_code = l_bal_ent_currs(i)
9162: AND bal_seg_value = l_bal_bal_segs(i)
9163: AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
9250: END IF;
9251:
9252: END IF;
9253:
9254: /*UPDATE xla_validation_lines_gt t
9255: SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
9256: SELECT nc.code_combination_id
9257: ,nc.reference3
9258: ,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
9342:
9343: --8531035 performance changes start, old query commented above
9344:
9345: l_stmt := '
9346: UPDATE xla_validation_lines_gt t
9347: SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
9348: SELECT nc.code_combination_id
9349: ,nc.reference3
9350: ,decode(:1, :2, t.bal_seg_value,
9420: populate_missing_ccid(l_err_count, l_err_hdr_ids);
9421:
9422: IF (l_err_count>0) THEN
9423: FORALL j IN 1..l_err_count
9424: UPDATE xla_validation_lines_gt
9425: SET balancing_line_type = C_LINE_TYPE_COMPLETE
9426: WHERE ae_header_id = l_err_hdr_ids(j)
9427: AND balancing_line_type = C_LINE_TYPE_PROCESS;
9428: END IF;
9609: ,l.party_type_code
9610: ,l.party_id
9611: ,l.party_site_id
9612: ,'N' -- 4262811
9613: FROM xla_validation_lines_gt l
9614: ,gl_code_combinations ccid
9615: ,xla_lookups xl -- added line for bug 6902085
9616: WHERE l.balancing_line_type NOT IN (C_LINE_TYPE_PROCESS, C_LINE_TYPE_COMPLETE)
9617: AND ccid.code_combination_id(+) = l.code_combination_id
9689:
9690: INSERT INTO xla_ae_segment_values
9691: (ae_header_id, segment_type_code, segment_value, ae_lines_count)
9692: SELECT ae_header_id, C_BAL_SEGMENT, bal_seg_value, count(*)
9693: FROM xla_validation_lines_gt
9694: WHERE bal_seg_value IS NOT NULL
9695: GROUP BY ae_header_id, bal_seg_value
9696: UNION ALL
9697: SELECT ae_header_id, C_MGT_SEGMENT, mgt_seg_value, count(*)
9694: WHERE bal_seg_value IS NOT NULL
9695: GROUP BY ae_header_id, bal_seg_value
9696: UNION ALL
9697: SELECT ae_header_id, C_MGT_SEGMENT, mgt_seg_value, count(*)
9698: FROM xla_validation_lines_gt
9699: WHERE mgt_seg_value IS NOT NULL
9700: GROUP BY ae_header_id, mgt_seg_value
9701: UNION ALL
9702: SELECT ae_header_id, C_CC_SEGMENT, cost_center_seg_value, count(*)
9699: WHERE mgt_seg_value IS NOT NULL
9700: GROUP BY ae_header_id, mgt_seg_value
9701: UNION ALL
9702: SELECT ae_header_id, C_CC_SEGMENT, cost_center_seg_value, count(*)
9703: FROM xla_validation_lines_gt
9704: WHERE cost_center_seg_value IS NOT NULL
9705: GROUP BY ae_header_id, cost_center_seg_value
9706: UNION ALL
9707: SELECT ae_header_id, C_NA_SEGMENT, natural_account_seg_value, count(*)
9704: WHERE cost_center_seg_value IS NOT NULL
9705: GROUP BY ae_header_id, cost_center_seg_value
9706: UNION ALL
9707: SELECT ae_header_id, C_NA_SEGMENT, natural_account_seg_value, count(*)
9708: FROM xla_validation_lines_gt
9709: WHERE natural_account_seg_value IS NOT NULL
9710: GROUP BY ae_header_id, natural_account_seg_value;
9711:
9712: IF (C_LEVEL_EVENT >= g_log_level) THEN
9740: PROCEDURE post_validation
9741: IS
9742: CURSOR c_cont_acct IS
9743: SELECT *
9744: FROM xla_validation_lines_gt
9745: WHERE balancing_line_type IN (C_LINE_TYPE_LC_BALANCING
9746: ,C_LINE_TYPE_XLA_BALANCING
9747: ,C_LINE_TYPE_ENC_BALANCING)
9748: AND control_account_enabled_flag <> 'N'
9881: END IF;
9882:
9883: IF (g_err_count > 0) THEN
9884: FORALL i IN 1..g_err_count
9885: UPDATE xla_validation_lines_gt
9886: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
9887: WHERE ae_header_id = g_err_hdr_ids(i);
9888:
9889: FORALL i IN 1..g_err_count
9886: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
9887: WHERE ae_header_id = g_err_hdr_ids(i);
9888:
9889: FORALL i IN 1..g_err_count
9890: UPDATE xla_validation_lines_gt
9891: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_RELATED_INVALID
9892: WHERE event_id = g_err_event_ids(i);
9893: END IF;
9894:
10021: END IF;
10022: END LOOP;
10023:
10024: --
10025: -- Possible values returned to xla_validation_lines_gt.funds_status_codes
10026: -- can be found using
10027: -- select * from fnd_lookup_values where lookup_type like 'FUNDS_CHECK_RESULT_CODE'
10028: -- and language = 'US' order by lookup_code;
10029: -- In particular, any result code starts with 'F' (FXX) means the line is
10060: , l_array_unrounded_entered_cr
10061: , l_array_unrounded_entered_dr
10062: , l_array_unrounded_accounted_cr
10063: , l_array_unrounded_accounted_dr
10064: FROM xla_validation_lines_gt xvl
10065: , xla_ae_headers xah
10066: , fnd_lookup_values flv
10067: WHERE xvl.ae_header_id = xah.ae_header_id
10068: AND xvl.accounting_class_code <> 'RFE'
10739:
10740: IF l_array_ledgers.COUNT > 0 THEN
10741: FOR l_count IN 1 .. l_array_ledgers.COUNT LOOP
10742:
10743: DELETE FROM xla_validation_lines_gt;
10744: DELETE FROM fun_bal_headers_gt; --bug9526716 added fun table deletes in multiple place in xlajebal.pkb
10745: DELETE FROM fun_bal_lines_gt;
10746: DELETE FROM fun_bal_results_gt;
10747: DELETE FROM fun_bal_errors_gt;
10760:
10761: IF l_array_ledgers.COUNT > 0 THEN
10762: FOR l_count IN 1 .. l_array_ledgers.COUNT LOOP
10763:
10764: DELETE FROM xla_validation_lines_gt;
10765: DELETE FROM fun_bal_headers_gt;
10766: DELETE FROM fun_bal_lines_gt;
10767: DELETE FROM fun_bal_results_gt;
10768: DELETE FROM fun_bal_errors_gt;
10859: IF p_ledger_array.COUNT > 0 THEN
10860: FOR l_count IN 1 .. p_ledger_array.COUNT loop
10861: IF(g_err_count=0) THEN
10862:
10863: DELETE FROM xla_validation_lines_gt;
10864: DELETE FROM fun_bal_headers_gt;
10865: DELETE FROM fun_bal_lines_gt;
10866: DELETE FROM fun_bal_results_gt;
10867: DELETE FROM fun_bal_errors_gt;
10959:
10960: -- Process ALC ledgers
10961: FOR i IN 1 .. p_ledger_ids.COUNT LOOP
10962:
10963: DELETE FROM xla_validation_lines_gt;
10964: DELETE FROM fun_bal_headers_gt;
10965: DELETE FROM fun_bal_lines_gt;
10966: DELETE FROM fun_bal_results_gt;
10967: DELETE FROM fun_bal_errors_gt;