[Home] [Help]
572: AND asa.access_set_id in (g_pri_access_set_id, g_sec_access_set_id);
573:
574: CURSOR c_err IS
575: SELECT entity_id, event_id, ae_header_id
576: FROM xla_validation_lines_gt;
577:
578: l_err c_err%ROWTYPE;
579: l_ledger_id INTEGER;
580: l_log_module VARCHAR2(240);
688: IS
689: CURSOR c_full_bal IS
690: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
691: ,NULL segment_value
692: FROM xla_validation_lines_gt t
693: LEFT OUTER JOIN gl_access_set_assignments asa
694: on asa.ledger_id = g_ledger_id
695: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
696: AND asa.access_set_id = g_pri_access_set_id
704:
705: CURSOR c_full_mgt IS
706: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
707: ,NULL segment_value
708: FROM xla_validation_lines_gt t
709: LEFT OUTER JOIN gl_access_set_assignments asa
710: on asa.ledger_id = g_ledger_id
711: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
712: AND asa.access_set_id = g_pri_access_set_id
720:
721: CURSOR c_bal_none IS
722: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
723: ,t.bal_seg_value segment_value
724: FROM xla_validation_lines_gt t
725: LEFT OUTER JOIN gl_access_set_assignments asa
726: ON asa.segment_value = t.bal_seg_value
727: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
728: AND asa.ledger_id = g_ledger_id
731:
732: CURSOR c_bal_bal IS
733: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
734: ,t.bal_seg_value segment_value
735: FROM xla_validation_lines_gt t
736: LEFT OUTER JOIN gl_access_set_assignments asa
737: ON asa.segment_value = t.bal_seg_value
738: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
739: AND asa.ledger_id = g_ledger_id
742:
743: CURSOR c_bal_mgt IS
744: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
745: ,t.bal_seg_value segment_value
746: FROM xla_validation_lines_gt t
747: LEFT OUTER JOIN gl_access_set_assignments asa
748: on asa.segment_value = t.bal_seg_value
749: AND asa.ledger_id = g_ledger_id
750: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
759:
760: CURSOR c_mgt_none IS
761: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
762: ,t.mgt_seg_value segment_value
763: FROM xla_validation_lines_gt t
764: LEFT OUTER JOIN gl_access_set_assignments asa
765: ON asa.segment_value = t.mgt_seg_value
766: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
767: AND asa.ledger_id = g_ledger_id
770:
771: CURSOR c_mgt_mgt IS
772: SELECT t.entity_id, t.event_id, t.ae_header_id, t.ae_line_num
773: ,t.mgt_seg_value segment_value
774: FROM xla_validation_lines_gt t
775: LEFT OUTER JOIN gl_access_set_assignments asa
776: ON asa.segment_value = t.mgt_seg_value
777: AND asa.access_privilege_code = C_ACCESS_SET_FULL_PRIVILEGE
778: AND asa.ledger_id = g_ledger_id
1125: --
1126: -- If running in BC mode, do not use suspense account nor substitute accounts.
1127: --
1128: l_stmt := '
1129: INSERT INTO xla_validation_lines_gt
1130: (ae_header_id
1131: ,ae_line_num
1132: ,ledger_id
1133: ,displayed_line_number
1319: END IF;
1320:
1321: ELSIF(g_suspense_allowed_flag = 'Y') THEN
1322: l_stmt := '
1323: INSERT INTO xla_validation_lines_gt
1324: (ae_header_id
1325: ,ae_line_num
1326: ,ledger_id
1327: ,displayed_line_number
1807: p_level => C_LEVEL_STATEMENT);
1808: END IF;
1809:
1810: l_stmt:=
1811: 'UPDATE xla_validation_lines_gt l
1812: SET
1813: ( l.bal_seg_value
1814: ,l.mgt_seg_value
1815: ,l.cost_center_seg_value
1928: END IF;
1929:
1930: ELSE -- (g_sla_bal_by_ledger_curr_flag <> 'Y')
1931: l_stmt := '
1932: INSERT INTO xla_validation_lines_gt
1933: (ae_header_id
1934: ,ae_line_num
1935: ,ledger_id
1936: ,displayed_line_number
2303: SET zero_amount_flag = 'Y'
2304: WHERE application_id = g_application_id and
2305: ae_header_id in
2306: (select ae_header_id
2307: from xla_validation_lines_gt
2308: group by ae_header_id
2309: having sum(abs(accounted_cr)) = 0 and sum(abs(accounted_dr))=0);
2310:
2311: IF (p_budgetary_control_mode = 'NONE') THEN
2311: IF (p_budgetary_control_mode = 'NONE') THEN
2312: UPDATE /*+ index(XAL,XLA_AE_LINES_U1)*/ xla_ae_lines xal -- 4769388
2313: SET (code_combination_id, substituted_ccid)=
2314: (SELECT code_combination_id, substituted_ccid
2315: FROM xla_validation_lines_gt xvlg
2316: WHERE xvlg.ae_header_id = xal.ae_header_id
2317: AND xvlg.ae_line_num = xal.ae_line_num)
2318: WHERE xal.application_id = g_application_id
2319: AND (xal.ae_header_id, xal.ae_line_num) in
2318: WHERE xal.application_id = g_application_id
2319: AND (xal.ae_header_id, xal.ae_line_num) in
2320: (select /*+ unnest cardinality(GT,10)*/ -- 4769388
2321: ae_header_id, ae_line_num
2322: from xla_validation_lines_gt GT -- 4769388
2323: where substituted_ccid is not NULL);
2324:
2325: IF (C_LEVEL_STATEMENT>= g_log_level) THEN
2326: trace(p_msg => '# of rows updated to xla_ae_lines:'||to_char(SQL%ROWCOUNT),
2331:
2332:
2333: ELSE -- (g_caller <> mpa or acct program or third party merge)
2334:
2335: INSERT INTO xla_validation_lines_gt
2336: (ae_header_id
2337: ,ae_line_num
2338: ,ledger_id
2339: ,displayed_line_number
2827: ,h.event_id
2828: ,h.encumbrance_type_id
2829: ,e.encumbrance_type -- 5522973
2830: ,e.enabled_flag encum_type_enabled_flag
2831: FROM xla_validation_lines_gt h
2832: LEFT OUTER JOIN gl_encumbrance_types e
2833: ON e.encumbrance_type_id = h.encumbrance_type_id
2834: WHERE h.ledger_id = g_ledger_id
2835: AND h.balance_type_code = 'E'
3601: (p_seg_ledger_id INTEGER)
3602: IS
3603: CURSOR c_invalid_bal_segment IS
3604: SELECT t.*
3605: FROM xla_validation_lines_gt t
3606: LEFT OUTER JOIN gl_ledger_segment_values s
3607: ON s.segment_value = t.bal_seg_value
3608: AND s.segment_type_code = C_BAL_SEGMENT
3609: AND s.ledger_id = p_seg_ledger_id
3723: (p_seg_ledger_id INTEGER)
3724: IS
3725: CURSOR c_invalid_mgt_segment IS
3726: SELECT t.*
3727: FROM xla_validation_lines_gt t
3728: LEFT OUTER JOIN gl_ledger_segment_values s
3729: ON s.segment_value = t.mgt_seg_value
3730: AND s.segment_type_code = C_MGT_SEGMENT
3731: AND s.ledger_id = p_seg_ledger_id
3863: t.entity_id,
3864: t.party_type_code, t.party_id, t.party_site_id,
3865: c.cust_account_id customer_id, ps.site_use_id customer_site_id,
3866: s.vendor_id, ss.vendor_site_id
3867: FROM xla_validation_lines_gt t
3868: LEFT OUTER JOIN hz_cust_accounts_all c
3869: ON c.cust_account_id = t.party_id
3870: LEFT OUTER JOIN hz_cust_site_uses_all ps
3871: ON ps.site_use_id = t.party_site_id
4043: ,t.entered_currency_code
4044: ,curr.enabled_flag curr_enabled_flag
4045: ,curr.start_date_active curr_start_date_active
4046: ,curr.end_date_active curr_end_date_active
4047: FROM xla_validation_lines_gt t
4048: LEFT OUTER JOIN fnd_currencies curr
4049: ON curr.currency_code = t.entered_currency_code
4050: WHERE (curr.enabled_flag IS NULL) OR
4051: (curr.enabled_flag = 'N') OR
4186: ,t.displayed_line_number
4187: ,t.entity_id
4188: ,bud.budget_name
4189: ,fnd_flex_ext.get_segs('SQLGL', 'GL#', t.ccid_coa_id, t.code_combination_id) account
4190: FROM xla_validation_lines_gt t
4191: JOIN gl_budget_versions bud
4192: ON bud.budget_version_id = t.budget_version_id
4193: LEFT OUTER JOIN gl_budget_assignments b
4194: ON b.currency_code = t.entered_currency_code
4290: ,t.event_id
4291: ,t.displayed_line_number
4292: ,t.entity_id
4293: ,t.accounting_class_code
4294: FROM xla_validation_lines_gt t
4295: LEFT OUTER JOIN xla_lookups lk
4296: ON lk.lookup_type = 'XLA_ACCOUNTING_CLASS'
4297: AND lk.lookup_code = t.accounting_class_code
4298: WHERE lk.lookup_code IS NULL
4399: l_seg_ledger_id INTEGER;
4400:
4401: CURSOR c_line_error IS
4402: SELECT *
4403: FROM xla_validation_lines_gt
4404: WHERE error_flag = 'Y';
4405:
4406: CURSOR c_account(p_coa_id INTEGER, p_code_combination_id INTEGER) IS
4407: SELECT fnd_flex_ext.get_segs('SQLGL', 'GL#', p_coa_id, p_code_combination_id)
5296: Exhausted Undo Tablespace when a single header has many lines.
5297: The following sql updates # of errors * # of lines.
5298:
5299: FORALL i IN l_prev_err_count+1..g_err_count
5300: UPDATE xla_validation_lines_gt
5301: set balancing_line_type = C_LINE_TYPE_COMPLETE
5302: WHERE ae_header_id = g_err_hdr_ids(i); */
5303:
5304: --
5302: WHERE ae_header_id = g_err_hdr_ids(i); */
5303:
5304: --
5305: -- Bug 7128871
5306: -- Update xla_validation_lines_gt for distinct ae header ids.
5307: --
5308:
5309: FOR i IN g_prev_err_count+1..g_err_count LOOP
5310: IF NOT l_distinct_hdr_ids.EXISTS(g_err_hdr_ids(i)) THEN
5315: -- As indices of l_dinstinct_hdr_ids are not consecutive,
5316: -- need to use "INDICES OF".
5317: --
5318: FORALL i IN INDICES OF l_distinct_hdr_ids
5319: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
5320: */ XLA_VALIDATION_LINES_GT
5321: SET balancing_line_type = C_LINE_TYPE_COMPLETE
5322: WHERE ae_header_id = l_distinct_hdr_ids(i);
5323:
5316: -- need to use "INDICES OF".
5317: --
5318: FORALL i IN INDICES OF l_distinct_hdr_ids
5319: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
5320: */ XLA_VALIDATION_LINES_GT
5321: SET balancing_line_type = C_LINE_TYPE_COMPLETE
5322: WHERE ae_header_id = l_distinct_hdr_ids(i);
5323:
5324: END IF;
5364: , accounting_date accounting_date
5365: , party_type_code party_type_code
5366: , party_id party_id
5367: , party_site_id party_site_id
5368: FROM xla_validation_lines_gt
5369: WHERE ae_header_id = p_ae_header_id
5370: GROUP BY bal_seg_value
5371: , entered_currency_code
5372: , max_ae_line_num
5609: l_bal.entered_dr := l_bal.entered_cr - l_bal.entered_dr;
5610: l_bal.entered_cr := NULL;
5611: END IF;
5612:
5613: INSERT INTO xla_validation_lines_gt
5614: (balancing_line_type
5615: ,ledger_id
5616: ,ae_header_id
5617: ,ae_line_num
5721: CURSOR c_bal(p_rounding_offset NUMBER) is
5722: SELECT ae_header_id
5723: ,entity_id
5724: ,event_id
5725: FROM xla_validation_lines_gt
5726: WHERE balance_type_code = 'A' -- <> 'B' -- 4458381
5727: AND entered_currency_code <> 'STAT'
5728: AND balancing_line_type = C_LINE_TYPE_PROCESS
5729: GROUP BY ae_header_id, entity_id , event_id
5803: END IF;
5804:
5805: IF (l_comp_count>0) THEN
5806: FORALL j in 1..l_comp_count
5807: UPDATE xla_validation_lines_gt
5808: SET balancing_line_type = C_LINE_TYPE_COMPLETE
5809: WHERE ae_header_id = l_comp_hdr_ids(j)
5810: AND balancing_line_type = C_LINE_TYPE_PROCESS;
5811: END IF;
5850: ,p_rounding_offset NUMBER) IS
5851: SELECT ae_header_id
5852: ,entity_id
5853: ,event_id
5854: FROM xla_validation_lines_gt
5855: WHERE balance_type_code = 'A'
5856: AND entered_currency_code <> 'STAT'
5857: AND balancing_line_type = C_LINE_TYPE_PROCESS
5858: GROUP BY ae_header_id, entity_id, event_id
6024: END IF;
6025:
6026: IF (l_comp_count>0) THEN
6027: FORALL j in 1..l_comp_count
6028: UPDATE xla_validation_lines_gt
6029: SET balancing_line_type = C_LINE_TYPE_COMPLETE
6030: WHERE ae_header_id = l_comp_hdr_ids(j)
6031: AND balancing_line_type = C_LINE_TYPE_PROCESS;
6032: END IF;
6072: ,max_displayed_line_number max_disp_line_num
6073: ,accounting_date accounting_date
6074: ,entity_id
6075: ,event_id
6076: FROM XLA_VALIDATION_LINES_GT
6077: WHERE balancing_line_type in (C_LINE_TYPE_PROCESS
6078: ,C_LINE_TYPE_IC_BAL_INTER
6079: ,C_LINE_TYPE_IC_BAL_INTRA
6080: ,C_LINE_TYPE_XLA_BALANCING
6395: l_bal.entered_cr := NULL;
6396: END IF;
6397:
6398:
6399: INSERT INTO xla_validation_lines_gt
6400: (balancing_line_type
6401: ,ledger_id
6402: ,ae_header_id
6403: ,ae_line_num
6880: ,je_category_name
6881: ,accounting_date
6882: ,event_id
6883: ,entity_id
6884: FROM xla_validation_lines_gt l
6885: WHERE balance_type_code = 'A'
6886: AND entered_currency_code <> 'STAT'
6887: AND balancing_line_type = C_LINE_TYPE_PROCESS
6888: GROUP BY ae_header_id
7079: ,l.currency_conversion_type
7080: ,l.unrounded_accounted_dr
7081: ,l.unrounded_accounted_cr
7082: ,'N'
7083: FROM xla_validation_lines_gt l
7084: ,fun_bal_headers_gt h
7085: WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
7086: AND l.ae_header_id = h.group_id
7087: AND EXISTS (select 1 from xla_distribution_links xdl
7119: ,l.currency_conversion_type
7120: ,l.unrounded_accounted_cr
7121: ,l.unrounded_accounted_dr
7122: ,'N'
7123: FROM xla_validation_lines_gt l
7124: ,fun_bal_headers_gt h
7125: WHERE l.balancing_line_type = C_LINE_TYPE_PROCESS
7126: AND l.ae_header_id = h.group_id
7127: AND EXISTS (select 1 from xla_distribution_links xdl
7170: END IF;
7171: create_intercompany_errors(l_err_count, l_err_hdr_ids);
7172: END IF;
7173:
7174: INSERT INTO xla_validation_lines_gt(
7175: ae_header_id
7176: ,ae_line_num
7177: ,displayed_line_number
7178: ,max_ae_line_num
7242: ,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
7243: C_LINE_TYPE_IC_BAL_INTER)
7244: ,l.balance_type_code
7245: FROM fun_bal_results_gt res
7246: ,xla_validation_lines_gt l
7247: ,gl_code_combinations ccid
7248: ,fnd_currencies fcu
7249: WHERE l.ae_line_num = l.max_ae_line_num
7250: AND l.ae_header_id = res.group_id
7265: p_module => l_log_module,
7266: p_level => C_LEVEL_EVENT);
7267: END IF;
7268:
7269: INSERT INTO xla_validation_lines_gt(
7270: ae_header_id
7271: ,ae_line_num
7272: ,displayed_line_number
7273: ,max_ae_line_num
7341: ,decode(res.balancing_type, C_FUN_INTRA, C_LINE_TYPE_IC_BAL_INTRA,
7342: C_LINE_TYPE_IC_BAL_INTER)
7343: ,l.balance_type_code
7344: FROM fun_bal_results_gt res
7345: ,xla_validation_lines_gt l
7346: ,gl_code_combinations ccid
7347: ,fnd_currencies fcu
7348: WHERE l.ae_line_num = l.max_ae_line_num
7349: AND l.ae_header_id = res.group_id
7373: /* Commented out for bug 7128871
7374: Exhausted Undo Tablespace when a single header has many lines.
7375:
7376: FORALL j IN 1..l_err_count
7377: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7378: XLA_VALIDATION_LINES_GT
7379: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7380: WHERE ae_header_id = l_err_hdr_ids(j); */
7381:
7374: Exhausted Undo Tablespace when a single header has many lines.
7375:
7376: FORALL j IN 1..l_err_count
7377: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7378: XLA_VALIDATION_LINES_GT
7379: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7380: WHERE ae_header_id = l_err_hdr_ids(j); */
7381:
7382: --
7390: END IF;
7391: END LOOP;
7392: --
7393: -- Bug 7128871
7394: -- Update xla_validation_lines_gt for distinct ae header ids.
7395: -- As indices of l_dinstinct_hdr_ids are not consecutive,
7396: -- need to use "INDICES OF".
7397: --
7398: FORALL i IN INDICES OF l_distinct_hdr_ids
7395: -- As indices of l_dinstinct_hdr_ids are not consecutive,
7396: -- need to use "INDICES OF".
7397: --
7398: FORALL i IN INDICES OF l_distinct_hdr_ids
7399: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7400: */ XLA_VALIDATION_LINES_GT
7401: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7402: WHERE ae_header_id = l_distinct_hdr_ids(i);
7403:
7396: -- need to use "INDICES OF".
7397: --
7398: FORALL i IN INDICES OF l_distinct_hdr_ids
7399: UPDATE /*+ INDEX (XLA_VALIDATION_LINES_GT,XLA_VALIDATION_LINES_GT_N2)
7400: */ XLA_VALIDATION_LINES_GT
7401: SET balancing_line_type = C_LINE_TYPE_COMPLETE
7402: WHERE ae_header_id = l_distinct_hdr_ids(i);
7403:
7404: IF (C_LEVEL_EVENT >= g_log_level) THEN
7463: CURSOR c_ccid IS
7464: SELECT t.bal_seg_value
7465: ,t.balance_type_code -- 4458381
7466: ,min(t.accounting_date) accounting_date
7467: FROM xla_validation_lines_gt t
7468: WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
7469: ,C_LINE_TYPE_ENC_BALANCING)
7470: AND t.code_combination_id IS NULL
7471: AND ((g_res_encumb_ccid IS NOT NULL AND t.balance_type_code = 'E') OR
7475: CURSOR c_errors IS
7476: SELECT entity_id
7477: ,event_id
7478: ,ae_header_id
7479: FROM xla_validation_lines_gt
7480: WHERE balancing_line_type IN (C_LINE_TYPE_XLA_BALANCING
7481: ,C_LINE_TYPE_ENC_BALANCING)
7482: AND code_combination_id < 0
7483: GROUP BY entity_id, event_id, ae_header_id;
7653: END IF;
7654: END LOOP;
7655:
7656: FORALL i IN 1..j
7657: UPDATE xla_validation_lines_gt t
7658: SET code_combination_id = l_ccids(i)
7659: ,control_account_enabled_flag = l_reference3s(i)
7660: ,mgt_seg_value = l_mgt_seg_values(i)
7661: WHERE t.bal_seg_value = l_bal_seg_values(i)
7781:
7782:
7783: CURSOR c_no_sus_ccid IS
7784: SELECT entity_id, event_id, ae_header_id, balance_type_code
7785: FROM xla_validation_lines_gt
7786: WHERE balancing_line_type = C_LINE_TYPE_XLA_BALANCING
7787: AND balance_type_code = 'A'
7788: GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
7789:
7843: END IF;
7844:
7845: SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
7846: BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
7847: FROM xla_validation_lines_gt t
7848: WHERE balance_type_code not in('E','B')
7849: AND entered_currency_code <> 'STAT'
7850: AND balancing_line_type in (C_LINE_TYPE_PROCESS,
7851: C_LINE_TYPE_IC_BAL_INTER,
7874: -- currencies already balanced in entered amounts but not in accounted amounts.
7875:
7876: SELECT DISTINCT xvl.ae_header_id, xvl.entered_currency_code
7877: BULK COLLECT INTO l_already_bal_hdr_ids, l_already_bal_ent_currs
7878: FROM xla_validation_lines_gt xvl
7879: WHERE xvl.ae_header_id IN (
7880:
7881: SELECT t.ae_header_id
7882: FROM xla_validation_lines_gt t
7878: FROM xla_validation_lines_gt xvl
7879: WHERE xvl.ae_header_id IN (
7880:
7881: SELECT t.ae_header_id
7882: FROM xla_validation_lines_gt t
7883: WHERE t.balance_type_code not in('E','B')
7884: AND t.entered_currency_code <> 'STAT'
7885: AND t.balancing_line_type in (C_LINE_TYPE_PROCESS,
7886: C_LINE_TYPE_IC_BAL_INTER,
7926: -- Note: Debit and Credit line are created by using two insert statement
7927: -- to prevent the 0 amount lines from being created
7928: --
7929: FORALL i IN 1..l_bal_hdr_ids.COUNT
7930: INSERT INTO xla_validation_lines_gt
7931: (balancing_line_type
7932: ,ledger_id
7933: ,ae_header_id
7934: ,max_ae_line_num
8025: ,t.party_type_code
8026: ,t.party_id
8027: ,t.party_site_id
8028: ,NULL
8029: FROM xla_validation_lines_gt t
8030: WHERE ae_header_id = l_bal_hdr_ids(i)
8031: AND entered_currency_code = l_bal_ent_currs(i)
8032: AND bal_seg_value = l_bal_bal_segs(i)
8033: AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
8071:
8072: IF (l_already_bal_hdr_ids.COUNT > 0) THEN
8073:
8074: FORALL i IN 1..l_already_bal_hdr_ids.COUNT
8075: INSERT INTO xla_validation_lines_gt
8076: (balancing_line_type
8077: ,ledger_id
8078: ,ae_header_id
8079: ,max_ae_line_num
8126: ,t.party_type_code
8127: ,t.party_id
8128: ,t.party_site_id
8129: ,NULL
8130: FROM xla_validation_lines_gt t
8131: WHERE t.ae_header_id = l_already_bal_hdr_ids(i)
8132: AND t.entered_currency_code = l_already_bal_ent_currs(i)
8133: AND t.balancing_line_type IN (C_LINE_TYPE_PROCESS
8134: ,C_LINE_TYPE_IC_BAL_INTER
8204: END IF;
8205:
8206: END IF;
8207:
8208: UPDATE xla_validation_lines_gt t
8209: SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
8210: SELECT nc.code_combination_id
8211: ,nc.reference3
8212: ,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
8294: populate_missing_ccid(l_err_count, l_err_hdr_ids);
8295:
8296: IF (l_err_count>0) THEN
8297: FORALL j IN 1..l_err_count
8298: UPDATE xla_validation_lines_gt
8299: SET balancing_line_type = C_LINE_TYPE_COMPLETE
8300: WHERE ae_header_id = l_err_hdr_ids(j)
8301: AND balancing_line_type = C_LINE_TYPE_PROCESS;
8302: END IF;
8352: l_mau NUMBER;
8353:
8354: CURSOR c_no_sus_ccid IS
8355: SELECT entity_id, event_id, ae_header_id, balance_type_code
8356: FROM xla_validation_lines_gt
8357: WHERE balancing_line_type = C_LINE_TYPE_ENC_BALANCING
8358: GROUP BY entity_id, event_id, ae_header_id, balance_type_code;
8359:
8360: BEGIN
8413: END IF;
8414:
8415: SELECT ae_header_id, bal_seg_value, entered_currency_code, encumbrance_type_id
8416: BULK COLLECT INTO l_bal_hdr_ids, l_bal_bal_segs, l_bal_ent_currs, l_bal_enc_ids
8417: FROM xla_validation_lines_gt t
8418: WHERE balance_type_code = 'E'
8419: AND entered_currency_code <> 'STAT'
8420: AND balancing_line_type in (C_LINE_TYPE_PROCESS,
8421: C_LINE_TYPE_IC_BAL_INTER,
8454: -- Note: Debit and Credit line are created by using two insert statement
8455: -- to prevent the 0 amount lines from being created
8456: --
8457: FORALL i IN 1..l_bal_hdr_ids.COUNT
8458: INSERT INTO xla_validation_lines_gt
8459: (balancing_line_type
8460: ,ledger_id
8461: ,ae_header_id
8462: ,max_ae_line_num
8553: ,t.party_type_code
8554: ,t.party_id
8555: ,t.party_site_id
8556: ,NULL
8557: FROM xla_validation_lines_gt t
8558: WHERE ae_header_id = l_bal_hdr_ids(i)
8559: AND entered_currency_code = l_bal_ent_currs(i)
8560: AND bal_seg_value = l_bal_bal_segs(i)
8561: AND NVL(encumbrance_type_id,-99) = NVL(l_bal_enc_ids(i),-99)
8648: END IF;
8649:
8650: END IF;
8651:
8652: UPDATE xla_validation_lines_gt t
8653: SET (code_combination_id, control_account_enabled_flag, mgt_seg_value) = (
8654: SELECT nc.code_combination_id
8655: ,nc.reference3
8656: ,decode(g_mgt_seg_column_name, g_bal_seg_column_name, t.bal_seg_value,
8739: populate_missing_ccid(l_err_count, l_err_hdr_ids);
8740:
8741: IF (l_err_count>0) THEN
8742: FORALL j IN 1..l_err_count
8743: UPDATE xla_validation_lines_gt
8744: SET balancing_line_type = C_LINE_TYPE_COMPLETE
8745: WHERE ae_header_id = l_err_hdr_ids(j)
8746: AND balancing_line_type = C_LINE_TYPE_PROCESS;
8747: END IF;
8928: ,l.party_type_code
8929: ,l.party_id
8930: ,l.party_site_id
8931: ,'N' -- 4262811
8932: FROM xla_validation_lines_gt l
8933: ,gl_code_combinations ccid
8934: ,xla_lookups xl -- added line for bug 6902085
8935: WHERE l.balancing_line_type NOT IN (C_LINE_TYPE_PROCESS, C_LINE_TYPE_COMPLETE)
8936: AND ccid.code_combination_id(+) = l.code_combination_id
9008:
9009: INSERT INTO xla_ae_segment_values
9010: (ae_header_id, segment_type_code, segment_value, ae_lines_count)
9011: SELECT ae_header_id, C_BAL_SEGMENT, bal_seg_value, count(*)
9012: FROM xla_validation_lines_gt
9013: WHERE bal_seg_value IS NOT NULL
9014: GROUP BY ae_header_id, bal_seg_value
9015: UNION ALL
9016: SELECT ae_header_id, C_MGT_SEGMENT, mgt_seg_value, count(*)
9013: WHERE bal_seg_value IS NOT NULL
9014: GROUP BY ae_header_id, bal_seg_value
9015: UNION ALL
9016: SELECT ae_header_id, C_MGT_SEGMENT, mgt_seg_value, count(*)
9017: FROM xla_validation_lines_gt
9018: WHERE mgt_seg_value IS NOT NULL
9019: GROUP BY ae_header_id, mgt_seg_value
9020: UNION ALL
9021: SELECT ae_header_id, C_CC_SEGMENT, cost_center_seg_value, count(*)
9018: WHERE mgt_seg_value IS NOT NULL
9019: GROUP BY ae_header_id, mgt_seg_value
9020: UNION ALL
9021: SELECT ae_header_id, C_CC_SEGMENT, cost_center_seg_value, count(*)
9022: FROM xla_validation_lines_gt
9023: WHERE cost_center_seg_value IS NOT NULL
9024: GROUP BY ae_header_id, cost_center_seg_value
9025: UNION ALL
9026: SELECT ae_header_id, C_NA_SEGMENT, natural_account_seg_value, count(*)
9023: WHERE cost_center_seg_value IS NOT NULL
9024: GROUP BY ae_header_id, cost_center_seg_value
9025: UNION ALL
9026: SELECT ae_header_id, C_NA_SEGMENT, natural_account_seg_value, count(*)
9027: FROM xla_validation_lines_gt
9028: WHERE natural_account_seg_value IS NOT NULL
9029: GROUP BY ae_header_id, natural_account_seg_value;
9030:
9031: IF (C_LEVEL_EVENT >= g_log_level) THEN
9059: PROCEDURE post_validation
9060: IS
9061: CURSOR c_cont_acct IS
9062: SELECT *
9063: FROM xla_validation_lines_gt
9064: WHERE balancing_line_type IN (C_LINE_TYPE_LC_BALANCING
9065: ,C_LINE_TYPE_XLA_BALANCING
9066: ,C_LINE_TYPE_ENC_BALANCING)
9067: AND control_account_enabled_flag <> 'N'
9200: END IF;
9201:
9202: IF (g_err_count > 0) THEN
9203: FORALL i IN 1..g_err_count
9204: UPDATE xla_validation_lines_gt
9205: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
9206: WHERE ae_header_id = g_err_hdr_ids(i);
9207:
9208: FORALL i IN 1..g_err_count
9205: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_INVALID
9206: WHERE ae_header_id = g_err_hdr_ids(i);
9207:
9208: FORALL i IN 1..g_err_count
9209: UPDATE xla_validation_lines_gt
9210: SET accounting_entry_status_code = xla_ae_journal_entry_pkg.C_RELATED_INVALID
9211: WHERE event_id = g_err_event_ids(i);
9212: END IF;
9213:
9340: END IF;
9341: END LOOP;
9342:
9343: --
9344: -- Possible values returned to xla_validation_lines_gt.funds_status_codes
9345: -- can be found using
9346: -- select * from fnd_lookup_values where lookup_type like 'FUNDS_CHECK_RESULT_CODE'
9347: -- and language = 'US' order by lookup_code;
9348: -- In particular, any result code starts with 'F' (FXX) means the line is
9379: , l_array_unrounded_entered_cr
9380: , l_array_unrounded_entered_dr
9381: , l_array_unrounded_accounted_cr
9382: , l_array_unrounded_accounted_dr
9383: FROM xla_validation_lines_gt xvl
9384: , xla_ae_headers xah
9385: , fnd_lookup_values flv
9386: WHERE xvl.ae_header_id = xah.ae_header_id
9387: AND xvl.accounting_class_code <> 'RFE'
10053: l_array_ledgers := xla_accounting_cache_pkg.GetLedgers;
10054:
10055: IF l_array_ledgers.COUNT > 0 THEN
10056: FOR l_count IN 1 .. l_array_ledgers.COUNT LOOP
10057: delete from xla_validation_lines_gt;
10058: balance_by_ledger(l_array_ledgers(l_count)
10059: ,gl_mc_info.get_ledger_category
10060: (l_array_ledgers(l_count)) -- ledger category code
10061: ,p_budgetary_control_mode);
10067: p_primary_ledger_id => g_trx_ledger_id);
10068:
10069: IF l_array_ledgers.COUNT > 0 THEN
10070: FOR l_count IN 1 .. l_array_ledgers.COUNT LOOP
10071: delete from xla_validation_lines_gt;
10072: balance_by_ledger(l_array_ledgers(l_count)
10073: ,'ALC'
10074: ,p_budgetary_control_mode);
10075: END LOOP;
10160:
10161: IF p_ledger_array.COUNT > 0 THEN
10162: FOR l_count IN 1 .. p_ledger_array.COUNT loop
10163: IF(g_err_count=0) THEN
10164: delete from xla_validation_lines_gt;
10165: balance_by_ledger(p_ledger_array(l_count)
10166: , NULL -- Ledger category code
10167: ,'NONE');
10168: END IF;
10254: ,p_accounting_mode => p_accounting_mode);
10255:
10256: -- Process ALC ledgers
10257: FOR i IN 1 .. p_ledger_ids.COUNT LOOP
10258: delete from xla_validation_lines_gt;
10259:
10260: g_ae_header_id := p_ae_header_ids(i);
10261:
10262: IF (C_LEVEL_STATEMENT >= g_log_level) THEN