[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| to be appended, created or deleted |
| g_summarized_flag : Whether there are Summary Transactions in the |
| Packet |
| g_arrival_seq : Arrival Sequence Number of the Packet in process |
| g_no_msg_tokens : Number of messages tokens |
| g_reverse_tc_flag : Profile GL_REVERSE_TC_OPTION |
| g_enable_efc_flag : Profile PSA_ENABLE_EFC |
| g_fv_prepay_prof : FV profile option |
| g_debug : Global Variable used for debugging purpose |
| g_xla_debug : Global Variable used for SLA debugging purpose |
| g_overlapping_budget : Check if there are multiple overlapping budgets |
| for the account |
| g_session_id : Current Session Identifier |
| g_serial_id : Current Session Serial# Identifier |
+=============================================================================*/
TYPE SegNamArray IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
select 'x'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.result_code between 'F00' and 'F19'
and bp.ussgl_link_to_parent_id is null
and bp.template_id is null
and nvl(bp.override_amount, -1) >=
abs(nvl(bp.accounted_dr, 0) - nvl(bp.accounted_cr, 0))
and not exists
(
select 'If Partial Resv disallowed then all non-generated ' ||
'detail lines that failed with any validation errors ' ||
'or because of Funds Availability'
from gl_bc_packets pk
where pk.packet_id = g_packet_id
and pk.template_id is null
and pk.result_code like 'F%'
and ((g_partial_resv_flag = 'N'
and pk.ussgl_link_to_parent_id is null
and (pk.result_code between 'F20' and 'F29'
or nvl(pk.override_amount, -1) <
abs(nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0))))
or (pk.ussgl_link_to_parent_id = bp.ussgl_parent_id
and pk.result_code between 'F20' and 'F29'))
);
select 'x'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.result_code between 'F00' and 'F19'
and bp.ussgl_link_to_parent_id is not null
and exists
(
select 'Corresp Original Transaction which was Overridden'
from gl_bc_packets pk
where pk.packet_id = g_packet_id
and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
and pk.result_code = 'P21'
);
SELECT chart_of_accounts_id,
currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = g_ledger_id;
l_prepare_stmt := 'SELECT cc_bc_enable_flag FROM igc_cc_bc_enable WHERE set_of_books_id = :1';
SELECT distinct je_source_name, je_category_name
FROM gl_bc_packets
WHERE packet_id = decode(g_fcmode, 'U', g_packet_id_ursvd, g_packet_id);
l_option_selected NUMBER;
SELECT MIN(CASE) INTO l_option_selected
FROM (
SELECT CASE
WHEN (bc.je_source_name = indx.je_source_name) AND (bc.je_category_name = indx.je_category_name) THEN
1
WHEN (bc.je_source_name = indx.je_source_name) AND (bc.je_category_name = 'Other') THEN
2
WHEN (bc.je_category_name = indx.je_category_name) AND (bc.je_source_name = 'Other') THEN
3
WHEN (bc.je_source_name = 'Other' AND bc.je_category_name = 'Other') THEN
4
END CASE
FROM gl_bc_option_details bc
WHERE bc_option_id = g_bc_option_id);
psa_utils.debug_other_string(g_state_level,l_full_path, ' l_option_selected -> '||l_option_selected);
INSERT INTO psa_option_details_gt
( packet_id,
je_source_name,
je_category_name,
gl_bc_option_source,
gl_bc_option_category,
funds_check_level_code,
override_amount,
tolerance_percentage,
tolerance_amount
)
SELECT decode(g_fcmode, 'U', g_packet_id_ursvd, g_packet_id)
,indx.je_source_name
,indx.je_category_name
,decode(l_option_selected,
1, indx.je_source_name,
2, indx.je_source_name,
'Other')
,decode(l_option_selected,
1, indx.je_category_name,
3, indx.je_category_name,
'Other')
,funds_check_level_code
,override_amount
,tolerance_percentage
,tolerance_amount
FROM gl_bc_option_details
WHERE bc_option_id = g_bc_option_id
AND je_source_name = decode(l_option_selected,
1, indx.je_source_name,
2, indx.je_source_name,
'Other')
AND je_category_name = decode(l_option_selected,
1, indx.je_category_name,
3, indx.je_category_name,
'Other');
psa_utils.debug_other_string(g_state_level,l_full_path, sql%rowcount||' Rows Inserted into psa_option_details_gt ');
select 'USSGL Rows need to be created'
from dual
where exists
(
select 'Transaction with USSGL Code'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.ussgl_transaction_code is not null
);
select 'Associated Generated JEs to be appended or inserted'
from dual
where exists
(
select 'Associated Generated Row from existing GL Batch'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id is not null
and bp.je_batch_id >= 0
and bp.ussgl_transaction_code is not null
);
UPDATE GL_BC_PACKETS BP
SET BP.ussgl_parent_id = GL_USSGL_PARENT_S.NEXTVAL
WHERE
BP.packet_id = g_packet_id
AND BP.ussgl_transaction_code IS NOT NULL;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
UPDATE GL_BC_PACKETS BP
SET
( BP.reference1,
BP.reference2,
BP.reference3,
BP.reference4,
BP.reference5,
BP.reference6,
BP.reference7,
BP.reference8,
BP.reference9,
BP.reference10) =
(SELECT GI.reference_1,
GI.reference_2,
GI.reference_3,
GI.reference_4,
GI.reference_5,
GI.reference_6,
GI.reference_7,
GI.reference_8,
GI.reference_9,
GI.reference_10
FROM gl_import_references GI
WHERE GI.je_line_num = BP.je_line_num
AND GI.je_header_id= BP.je_header_id
AND GI.je_batch_id = BP.je_batch_id)
WHERE
BP.packet_id = g_packet_id
AND BP.ussgl_transaction_code IS NOT NULL;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets -> ' || SQL%ROWCOUNT );
sql_ussgl := 'insert into gl_bc_packets (packet_id, ' ||
'ledger_id, ' ||
'je_source_name, ' ||
'je_category_name, ' ||
'code_combination_id, ' ||
'actual_flag, ' ||
'period_name, ' ||
'period_year, ' ||
'period_num, ' ||
'quarter_num, ' ||
'currency_code, ' ||
'status_code, ' ||
'last_update_date, ' ||
'last_updated_by, ' ||
'entered_dr, ' ||
'entered_cr, ' ||
'accounted_dr, ' ||
'accounted_cr, ' ||
'originating_rowid, ' ||
'account_segment_value, ' ||
'je_batch_name, ' ||
'je_batch_id, ' ||
'je_header_id, ' ||
'je_line_num, '||
'reference1, ' ||
'reference2, ' ||
'reference3, ' ||
'reference4, ' ||
'reference5, ' ||
'reference6, ' ||
'reference7, ' ||
'reference8, ' ||
'reference9, ' ||
'reference10, '||
'ussgl_link_to_parent_id, '||
'session_id, '||
'serial_id, ' ||
'application_id) ';
'select bp.packet_id, ' ||
'bp.ledger_id, ' ||
'bp.je_source_name, ' ||
'bp.je_category_name, ' ||
'decode(ccg.code_combination_id, ' ||
'cco.code_combination_id, ' ||
'-1 * cco.code_combination_id, ' ||
'ccg.code_combination_id), ' ||
'''A'', ' ||
'bp.period_name, ' ||
'bp.period_year, ' ||
'bp.period_num, ' ||
'bp.quarter_num, ' ||
'bp.currency_code, ' ||
'bp.status_code, ' ||
'bp.last_update_date, ' ||
'bp.last_updated_by, ';
'select nvl(min(ccg1.code_combination_id), ' ||
'cco.code_combination_id) ' ||
'from gl_code_combinations ccg1 ' ||
'where ccg1.chart_of_accounts_id = ' ||
g_coa_id;
'select nvl(min(ccg1.code_combination_id), ' ||
'cco.code_combination_id) ' ||
'from gl_code_combinations ccg1 ' ||
'where ccg1.chart_of_accounts_id = ' ||
g_coa_id;
psa_utils.debug_other_string(g_state_level,l_full_path, 'USSGL Rows Inserted: '||sql%rowcount);
sql_ussgl := 'select DISTINCT ';
update gl_bc_packets bp
set bp.code_combination_id = l_ccid_out
where bp.code_combination_id = l_ccid
and bp.account_segment_value=seg_val(g_acct_seg_index);
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets -> ' || SQL%ROWCOUNT);
select application_column_name
from fnd_id_flex_segments
where application_id = appl_id
and id_flex_code = flex_code
and id_flex_num = flex_num
and enabled_flag = 'Y'
order by segment_num;
select gl_bc_packets_s.nextval
from dual;
insert into gl_bc_packets
(packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
encumbrance_type_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
ussgl_transaction_code,
originating_rowid,
automatic_encumbrance_flag,
funding_budget_version_id,
funds_check_level_code,
amount_type,
boundary_code,
tolerance_percentage,
tolerance_amount,
override_amount,
account_type,
dr_cr_code,
account_category_code,
effect_on_funds_code,
je_batch_id,
je_header_id,
je_line_num,
ussgl_parent_id,
ussgl_link_to_parent_id,
session_id,
serial_id,
application_id)
select g_packet_id,
bp.ledger_id,
bp.je_source_name,
bp.je_category_name,
bp.code_combination_id,
bp.actual_flag,
bp.period_name,
bp.period_year,
bp.period_num,
bp.quarter_num,
bp.currency_code,
'P',
sysdate,
g_user_id,
bp.budget_version_id,
bp.encumbrance_type_id,
bp.entered_cr,
bp.entered_dr,
bp.accounted_cr,
bp.accounted_dr,
bp.ussgl_transaction_code,
nvl(bp.originating_rowid,
decode(bp.ussgl_transaction_code, NULL, NULL, bp.rowid)),
-- decode(bp.account_type, 'C', g_budgetary_enc_flag,
-- 'D', g_budgetary_enc_flag,
-- nvl(ba.automatic_encumbrance_flag,
-- bp.automatic_encumbrance_flag)),
'Y',
nvl(bo.funding_budget_version_id, bp.funding_budget_version_id),
decode(bo.funds_check_level_code, null,
bp.funds_check_level_code,
'D', nvl(od.funds_check_level_code, 'D'),
bo.funds_check_level_code),
nvl(bo.amount_type, bp.amount_type),
nvl(bo.boundary_code, bp.boundary_code),
od.tolerance_percentage,
od.tolerance_amount,
od.override_amount,
bp.account_type,
bp.dr_cr_code,
bp.account_category_code,
decode(
decode(bp.actual_flag || bp.dr_cr_code ||
bp.account_category_code, 'BCP', 'dec', 'ADP', 'dec',
'EDP', 'dec', 'ACB', 'dec', 'BCB', 'n/a', 'BDB', 'n/a',
'ECB', 'n/a', 'EDB', 'n/a',
'PDP', 'dec',
'PCB', 'dec', 'FDP', 'dec','FCB', 'n/a', 'FDB','n/a','inc'
),
'dec', decode(sign(nvl(bp.accounted_cr, 0) -
nvl(bp.accounted_dr, 0)), 1, 'D', 'I'),
'inc', decode(sign(nvl(bp.accounted_cr, 0) -
nvl(bp.accounted_dr, 0)), -1, 'D', 'I'),
'n/a', 'I'),
bp.je_batch_id,
bp.je_header_id,
bp.je_line_num,
bp.ussgl_parent_id,
bp.ussgl_link_to_parent_id,
g_session_id,
g_serial_id,
g_resp_appl_id
from psa_option_details_gt od,
gl_budget_assignments ba,
gl_bc_packets bp,
gl_budorg_bc_options bo
where (od.je_source_name || ';' || od.je_category_name =
in (select BV1.budget_version_id
from gl_budget_versions bv1, gl_budgets b,
gl_period_statuses ps
where ba.ledger_id = g_ledger_id
and ba.currency_code = bp.currency_code
and ba.code_combination_id = bp.code_combination_id
and b.budget_name = bv1.budget_name
and ((b.budget_type = 'payment'
and bp.actual_flag in ('P', 'F'))
or
(b.budget_type = 'standard'
and bp.actual_flag not in ('P', 'F')))
and ps.application_id = 101
and ps.ledger_id = g_ledger_id
and ps.period_name = bp.period_name
and ps.start_date
>= (select p1.start_date
from gl_period_statuses p1
where p1.period_name = b.first_valid_period_name
and p1.application_id = ps.application_id
and p1.ledger_id = ps.ledger_id)
and ps.end_date
<= (select p2.end_date
from gl_period_statuses p2
where p2.period_name = b.last_valid_period_name
and p2.application_id = ps.application_id
and p2.ledger_id = ps.ledger_id))
and bp.packet_id = g_packet_id_ursvd
and bp.template_id is null
and bp.status_code = 'A';
psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert gl_bc_packets -> ' || SQL%ROWCOUNT );
SELECT
'There are USSGL rows in the packet'
INTO
l_dummy
FROM
DUAL
WHERE EXISTS
(
SELECT
'Record with non-null USSGL transaction code'
FROM
GL_BC_PACKETS BP
WHERE
BP.packet_id = g_packet_id_ursvd
AND BP.ussgl_transaction_code IS NOT NULL
);
select gl_bc_packet_arrival_order_s.nextval
from dual;
select 'Obtain Row Share Lock on the corresponding record for this Set of Books'
from gl_concurrency_control ct
where ct.concurrency_class = 'INSERT_PACKET_ARRIVAL'
and ct.concurrency_entity_name = 'SET_OF_BOOKS'
and ct.concurrency_entity_id = to_char(g_ledger_id)
FOR UPDATE;
update gl_bc_packets bp
set bp.funding_budget_version_id =
(select decode(pk.actual_flag, 'B', pk.budget_version_id,
bo.funding_budget_version_id)
from gl_budget_assignments ba,
gl_budgets b,
gl_budget_versions bv,
gl_period_statuses ps,
gl_bc_packets pk,
gl_budorg_bc_options bo
where
ba.ledger_id(+) = g_ledger_id
and ba.currency_code(+) = decode(PK.currency_code,
'STAT', 'STAT',
g_func_curr_code)
and ba.code_combination_id (+) = PK.code_combination_id
and bo.range_id(+) = ba.range_id
and bo.funding_budget_version_id = bv.budget_version_id
and bv.budget_name = b.budget_name
and ((b.budget_type = 'payment' and
pk.actual_flag IN ('P', 'F'))
or
(b.budget_type = 'standard' and
pk.actual_flag not in ('P', 'F')))
and ps.application_id = 101
and ps.ledger_id = g_ledger_id
and ps.period_name = pk.period_name
and ps.start_date >= (select p1.start_date
from gl_period_statuses p1
where p1.period_name = b.first_valid_period_name
and p1.application_id = ps.application_id
and p1.ledger_id = ps.ledger_id)
and ps.end_date <= (select p2.end_date
from gl_period_statuses p2
where p2.period_name = b.last_valid_period_name
and p2.application_id = ps.application_id
and p2.ledger_id = ps.ledger_id)
and pk.rowid = bp.rowid
)
where bp.packet_id = g_packet_id
and bp.template_id is null
and bp.funding_budget_version_id is null;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated failed');
update gl_bc_packets bp
set STATUS_CODE = DECODE(g_fcmode,'C','F','R'),
RESULT_CODE=
( select DECODE(count(bo.FUNDING_BUDGET_VERSION_ID),1,'F77','F80')
from
gl_bc_packets pk,
gl_budget_assignments ba,
gl_budorg_bc_options bo
where
pk.rowid=bp.rowid
and pk.code_combination_id=ba.code_combination_id
and pk.ledger_id = ba.ledger_id
and pk.currency_code = ba.currency_code
and ba.range_id = bo.range_id
)
where
bp.packet_id = g_packet_id;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set (bp.automatic_encumbrance_flag,
bp.funds_check_level_code,
bp.tolerance_percentage,
bp.tolerance_amount,
bp.override_amount,
bp.account_type,
bp.dr_cr_code,
bp.account_category_code,
bp.effect_on_funds_code,
bp.result_code) =
(
select 'Y',
decode(pk.funding_budget_version_id, NULL, 'N', NULL),
od.tolerance_percentage,
od.tolerance_amount,
od.override_amount,
cc.account_type,
decode(cc.account_type, 'A', 'D', 'E', 'D', 'D', 'D', 'C'),
decode(cc.account_type, 'D', 'B', 'C', 'B', 'P'),
decode(
decode(pk.actual_flag || cc.account_type,
'BL', 'dec',
'BO', 'dec',
'BR', 'dec',
'AA', 'dec',
'AE', 'dec',
'EA', 'dec',
'EE', 'dec',
'AC', 'dec',
'BC', 'n/a',
'BD', 'n/a',
'EC', 'n/a',
'ED', 'n/a',
'inc'),
'dec',
decode(sign(nvl(pk.accounted_dr, 0)-
nvl(pk.accounted_cr, 0)), 1, 'D', 'I'),
'inc',
decode(sign(nvl(pk.accounted_dr, 0)-
nvl(pk.accounted_cr, 0)), -1, 'D', 'I'),
'n/a', 'I'),
decode(cc.code_combination_id, null, 'F20',
decode(cc.enabled_flag, 'N', 'F21',
decode(sign(sysdate - nvl(cc.start_date_active,sysdate)),-1,'F21',
decode(sign(nvl(cc.end_date_active,sysdate)-sysdate),-1,'F21',
decode(pk.actual_flag ||
cc.detail_posting_allowed_flag,
'AN', 'F22', 'EN', 'F22',
decode(pk.actual_flag ||
cc.detail_budgeting_allowed_flag,
'BN', 'F23',
decode(ps.period_name, null, 'F24',
decode(pk.actual_flag || ps.closing_status,
'AN', 'F25', 'AC', 'F25', 'AP', 'F25',
decode(pk.actual_flag ||
nvl(br.open_flag, 'N'), 'BN', 'F26',
decode(pk.actual_flag || bv.status,
'BF', 'F27',
decode(sign(nvl(pk.bc_date, sysdate) -
nvl(uc.start_date_active, nvl(pk.bc_date, sysdate))),
-1, 'F28',
decode(sign(nvl(uc.end_date_active,
nvl(pk.bc_date, sysdate)) - nvl(pk.bc_date, sysdate)), -1, 'F28',
decode(substr(pk.result_code,1,1),
'X', 'F' || substr(pk.result_code,2),
null)))))))))))))
from gl_ussgl_transaction_codes uc,
gl_budget_versions bv,
gl_budget_period_ranges br,
gl_period_statuses ps,
gl_code_combinations cc,
psa_option_details_gt od,
gl_bc_packets pk
where uc.chart_of_accounts_id (+) = g_coa_id
and uc.ussgl_transaction_code (+) =
nvl(pk.ussgl_transaction_code, -1)
and bv.budget_version_id (+) = nvl(pk.budget_version_id, -1)
and br.budget_version_id (+) = nvl(pk.budget_version_id, -1)
and br.period_year (+) = pk.period_year
and pk.period_num between br.start_period_num (+)
and br.end_period_num (+)
and ps.application_id (+) = 101
and ps.ledger_id (+) = g_ledger_id
and ps.period_name (+) = pk.period_name
and cc.code_combination_id (+) = pk.code_combination_id
and (od.je_source_name || ';' || od.je_category_name =
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set (funds_check_level_code,
amount_type,
boundary_code) = (select
nvl(min(decode(bo.funds_check_level_code, 'D',
nvl(od.funds_check_level_code, 'D'),
nvl(bo.funds_check_level_code, 'N'))), 'N'),
min(bo.amount_type),
min(bo.boundary_code)
from gl_bc_packets pk,
psa_option_details_gt od,
gl_budget_assignments ba,
gl_budorg_bc_options bo
where pk.rowid = bp.rowid
and (od.je_source_name || ';' || od.je_category_name =
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.funds_check_level_code = 'B'
where bp.packet_id = g_packet_id
and bp.template_id is null
and bp.funds_check_level_code = 'N'
and bp.funding_budget_version_id IS NULL
and exists
(select null
from gl_budget_assignments ba
where ba.code_combination_id = bp.code_combination_id
and ba.ledger_id = bp.ledger_id
and ba.currency_code = bp.currency_code
);
insert into gl_bc_packets (packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
encumbrance_type_id,
template_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
funding_budget_version_id,
funds_check_level_code,
amount_type,
boundary_code,
tolerance_percentage,
tolerance_amount,
override_amount,
dr_cr_code,
account_category_code,
effect_on_funds_code,
session_id,
serial_id,
application_id)
select
min(bp.packet_id),
min(bp.ledger_id),
min(bp.je_source_name),
min(bp.je_category_name),
min(ah.summary_code_combination_id),
min(bp.actual_flag),
min(bp.period_name),
min(bp.period_year),
min(bp.period_num),
min(bp.quarter_num),
min(bp.currency_code),
min(bp.status_code),
min(bp.last_update_date),
min(bp.last_updated_by),
min(decode(bp.actual_flag, 'B', bp.budget_version_id, null)),
min(decode(bp.actual_flag, 'E', bp.encumbrance_type_id,null)),
min(st.template_id),
sum(nvl(bp.entered_dr, 0)),
sum(nvl(bp.entered_cr, 0)),
sum(nvl(bp.accounted_dr, 0)),
sum(nvl(bp.accounted_cr, 0)),
min(sb.funding_budget_version_id),
min(decode(sb.funds_check_level_code, 'D',
nvl(od.funds_check_level_code, 'D'),
sb.funds_check_level_code)),
min(sb.amount_type),
min(sb.boundary_code),
min(od.tolerance_percentage),
min(od.tolerance_amount),
min(od.override_amount),
min(sb.dr_cr_code),
min(st.account_category_code),
decode(
decode(min(bp.actual_flag) || min(sb.dr_cr_code) ||
min(st.account_category_code),
'BCP', 'dec', 'ADP', 'dec',
'EDP', 'dec', 'ACB', 'dec', 'BCB',
'n/a', 'BDB', 'n/a',
'ECB', 'n/a', 'EDB', 'n/a', 'inc'),
'dec',
decode(sign(sum(nvl(bp.accounted_dr, 0) -
nvl(bp.accounted_cr, 0))), 1, 'D', 'I'),
'inc',
decode(sign(sum(nvl(bp.accounted_dr, 0) -
nvl(bp.accounted_cr, 0))), -1, 'D', 'I'),
'n/a', 'I'),
min(bp.session_id),
min(bp.serial_id),
min(bp.application_id)
from psa_option_details_gt od,
gl_period_statuses ps,
gl_summary_templates st,
gl_account_hierarchies ah,
gl_bc_packets bp,
gl_summary_bc_options sb,
gl_budgets b,
gl_budget_versions bv,
gl_period_statuses ps2
where st.status = 'F'
and sb.funds_check_level_code || od.funds_check_level_code <> 'DN'
and st.template_id = ah.template_id
and sb.funding_budget_version_id = decode(bp.actual_flag,
'B', bp.budget_version_id,
sb.funding_budget_version_id)
and st.account_category_code = bp.account_category_code
and ps.ledger_id = g_ledger_id
and ps.application_id = 101
and ps.period_name = st.start_actuals_period_name
and (ps.period_year * 10000 + ps.period_num) <=
(bp.period_year * 10000 + bp.period_num)
AND SB.template_id = ST.template_id
AND SB.funding_budget_version_id = BV.budget_version_id
AND BV.budget_name = B.budget_name
AND ((BV.budget_type = 'payment' AND BP.actual_flag in ('P', 'F'))
OR (BV.budget_type = 'standard' AND BP.actual_flag in ('A', 'E'))
OR (BP.actual_flag = 'B'))
and ps2.ledger_id = g_ledger_id
and ps2.application_id = 101
AND PS2.period_name = BP.period_name
AND PS2.start_date >= (select P1.start_date
from GL_PERIOD_STATUSES P1
where P1.application_id = ps2.application_id
and P1.ledger_id = ps2.ledger_id
and P1.period_name = B.first_valid_period_name)
AND PS2.end_date <= (select P2.end_date
from GL_PERIOD_STATUSES P2
where P2.application_id = ps2.application_id
and P2.ledger_id = ps2.ledger_id
and P2.period_name = B.last_valid_period_name)
and ah.ledger_id = g_ledger_id
and ah.detail_code_combination_id = bp.code_combination_id
and od.packet_id = bp.packet_id
and od.je_source_name || ';' || od.je_category_name =
' Insert gl_bc_packets - summ trans ' || SQL%ROWCOUNT );
insert into gl_bc_packet_arrival_order
(packet_id,
ledger_id,
arrival_seq,
affect_funds_flag,
last_update_date,
last_updated_by)
values (g_packet_id,
g_ledger_id,
g_arrival_seq,
decode(g_fcmode, 'C', 'N', 'Y'),
sysdate,
g_user_id);
' insert gl_bc_packet_arrival_order -> ' || SQL%ROWCOUNT);
message_token('EVENT', 'Table Locked by Add/Delete Summary Accounts Process');
update
gl_bc_packets bp
set (bp.budget_approved_balance,
bp.actual_approved_balance,
bp.encumbrance_approved_balance,
bp.budget_pending_balance,
bp.actual_pending_balance,
bp.encumbrance_pending_balance) =
(
select
sum(decode(pk.status_code || pk.actual_flag,
'AB', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
),
sum(decode(pk.status_code || pk.actual_flag,
'AA', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
),
sum(decode(pk.status_code || pk.actual_flag,
'AE', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
),
sum(decode(pk.status_code || pk.actual_flag,
'PB', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
'CB', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
),
sum(decode(pk.status_code || pk.actual_flag,
'PA', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
'CA', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
),
sum(decode(pk.status_code || pk.actual_flag,
'PE', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
'CE', nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0),
0)
)
from gl_period_statuses ps,
gl_budgets bd,
gl_budget_versions bv,
gl_bc_packets pk,
gl_bc_packet_arrival_order ao
where ps.application_id = 101
and ps.ledger_id = g_ledger_id
and ps.period_name = bd.last_valid_period_name
and bd.budget_name = bv.budget_name
and bd.budget_type = bv.budget_type
and bv.budget_version_id = bp.funding_budget_version_id
and pk.funding_budget_version_id = bp.funding_budget_version_id
and pk.ledger_id = g_ledger_id
and pk.code_combination_id = bp.code_combination_id
and (pk.budget_version_id is null
or pk.budget_version_id = bp.funding_budget_version_id)
and pk.period_year = decode(bp.amount_type, 'PJTD',
pk.period_year, bp.period_year)
and pk.period_num = decode(bp.amount_type, 'PTD',
bp.period_num, pk.period_num)
and pk.quarter_num = decode(bp.amount_type, 'QTD',
bp.quarter_num, pk.quarter_num)
and ((pk.period_year = decode(bp.boundary_code,
'J', bd.latest_opened_year,
bp.period_year)
and pk.period_num <= decode(bp.boundary_code, 'P',
bp.period_num, 'J',
decode(ps.period_year,
bd.latest_opened_year,
ps.period_num,
pk.period_num), pk.period_num)
and pk.quarter_num <= decode(bp.boundary_code, 'Q',
bp.quarter_num, pk.quarter_num))
or pk.period_year < decode(bp.boundary_code, 'J',
bd.latest_opened_year,
bp.period_year))
and pk.currency_code = decode(pk.actual_flag, 'B',
g_func_curr_code, pk.currency_code)
and pk.packet_id = ao.packet_id
and ((pk.packet_id = g_packet_id -- Bug 3574935
and (decode(pk.funds_check_level_code, 'N', '0', 'D', '1',
'B', '2') || pk.rowid <
decode(bp.funds_check_level_code, 'N', '0', 'D', '1',
'B', '2') || bp.rowid
or pk.effect_on_funds_code = 'I'))
--Bug 6823089.. Start
-- or (pk.packet_id <= l_max_packet_id -- Bug 3574935, Bug 4119217
or(pk.packet_id >=0 and pk.status_code = 'A') --Bug 7476309
or (pk.packet_id >= 0
--Bug 6823089.. End
and ao.arrival_seq < g_arrival_seq
-- and ao.affect_funds_flag = 'Y'
and ao.ledger_id = g_ledger_id
and nvl(pk.result_code, 'X') like
decode(pk.status_code, 'A', 'P%', 'P', 'P%', 'C', 'P%', 'X') -- Bug 4630687
-- Bug 5046369 start
and (
(pk.status_code IN ('P', 'C')
and exists (select 'Packet is valid for the current session'
from v$session s
WHERE s.audsid = pk.session_id
AND s.serial# = pk.serial_id)
)
OR
pk.status_code = 'A'
)))
-- rgopalan Bug 2799257
and EXISTS
(SELECT 'x' FROM fnd_currencies
WHERE currency_code = PK.currency_code
AND currency_flag = 'Y')
)
where bp.packet_id = g_packet_id
and bp.result_code is null
and bp.effect_on_funds_code = 'D'
and bp.funds_check_level_code <> 'N'
and bp.currency_code = decode(bp.actual_flag, 'B', g_func_curr_code,
bp.currency_code)
and bp.funding_budget_version_id = decode(bp.actual_flag, 'B',
bp.budget_version_id,
bp.funding_budget_version_id)
-- rgopalan Bug 27992557
and exists
(SELECT 'x' FROM fnd_currencies
WHERE currency_code = BP.currency_code
AND currency_flag = 'Y');
' Update approved and pending balance in gl_bc_packets ' || SQL%ROWCOUNT );
SELECT nvl(effective_period_num,0), period_name, NVL(quarter_num,0), NVL(period_year,0)
INTO l_effective_period_num, l_period_name, l_quarter_num, l_period_year
FROM gl_period_statuses
WHERE ledger_id = g_ledger_id
AND application_id = 101
AND closing_status = 'O'
AND effective_period_num =
(SELECT max(effective_period_num)
FROM gl_period_statuses
WHERE ledger_id = g_ledger_id
AND application_id = 101
AND closing_status = 'O');
update
gl_bc_packets bp
set (bp.budget_posted_balance,
bp.actual_posted_balance,
bp.encumbrance_posted_balance) =
(
select
sum(decode(gb.actual_flag || bp.amount_type, 'BPTD',
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0),
'BQTD', nvl(gb.quarter_to_date_dr, 0) -
nvl(gb.quarter_to_date_cr, 0) +
nvl(gb.period_net_dr, 0)- nvl(gb.period_net_cr, 0),
'BYTD', nvl(gb.begin_balance_dr, 0) -
nvl(gb.begin_balance_cr, 0) + nvl(gb.period_net_dr, 0) -
nvl(gb.period_net_cr, 0),
'BPJTD', nvl(gb.project_to_date_dr, 0) -
nvl(gb.project_to_date_cr, 0) +
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0), 0)),
sum(decode(gb.actual_flag || bp.amount_type, 'APTD',
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0),
'AQTD', nvl(gb.quarter_to_date_dr, 0) -
nvl(gb.quarter_to_date_cr, 0) +
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0),
'AYTD', decode(pm.boundary_code, 'S',
nvl(gb.begin_balance_cr, 0) -
nvl(gb.begin_balance_dr, 0),
nvl(gb.begin_balance_dr, 0) -
nvl(gb.begin_balance_cr, 0) + nvl(gb.period_net_dr, 0) -
nvl(gb.period_net_cr, 0)),
'APJTD', nvl(gb.project_to_date_dr, 0) -
nvl(gb.project_to_date_cr, 0) +
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0), 0)),
sum(decode(gb.actual_flag || bp.amount_type, 'EPTD',
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0),
'EQTD', nvl(gb.quarter_to_date_dr, 0) -
nvl(gb.quarter_to_date_cr, 0) +
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0),
'EYTD', nvl(gb.begin_balance_dr, 0) -
nvl(gb.begin_balance_cr, 0) + nvl(gb.period_net_dr, 0) -
nvl(gb.period_net_cr, 0),
'EPJTD', nvl(gb.project_to_date_dr, 0) -
nvl(gb.project_to_date_cr, 0) +
nvl(gb.period_net_dr, 0) - nvl(gb.period_net_cr, 0), 0))
from gl_bc_period_map pm,
gl_balances gb
-- ## selecting the latest open period
-- ## Changes made For fix in Bug 3243216
/* (select effective_period_num e, period_name n,quarter_num q,
period_year y, ledger_id s, application_id a from gl_period_statuses
where
ledger_id= g_ledger_id and application_id =101 and closing_status='O'
and effective_period_num =
(select max(effective_period_num) from
gl_period_statuses where ledger_id= g_ledger_id and application_id =101 and
closing_status='O'))X*/ --Bug 5644702
WHERE
--- X.s = gb.ledger_id and x.a =101 and
-- ## changes for the bug 3243216
gb.ledger_id = g_ledger_id
and gb.code_combination_id = bp.code_combination_id
and gb.currency_code = g_func_curr_code
and gb.actual_flag = pm.actual_flag
and (gb.budget_version_id is null
or gb.budget_version_id = pm.budget_version_id)
-- ## Bug 3243216 replacement below
AND GB.period_name = PM.query_period_name
-- commented out below part as now we are selecting transaction period
-- based on latest open period and accordingly joining with gl_balances on query_period
/* AND GB.period_name = decode (PM.boundary_code, 'S', PM.query_period_name,
decode(GB.actual_flag,
'B', PM.query_period_name,
'A', decode(GREATEST(BP.period_year*10000+BP.period_num, l_effective_period_num),
BP.period_year*10000+BP.period_num,
decode(BP.amount_type,
'PTD', PM.query_period_Name,
'QTD', decode(BP.period_year,
l_period_year, decode(BP.quarter_num,
l_quarter_num, l_period_name,
pm.query_period_name),
PM.query_period_name),
'YTD', decode(BP.period_year,
l_period_year, l_period_name,
PM.query_Period_name),
'PJTD',decode(l_period_name,
NULL, PM.query_Period_name, l_period_name),
PM.query_period_name),
PM.query_period_name),
'E', PM.query_period_name)
) */
and pm.ledger_id = g_ledger_id
-- and pm.transaction_period_name = bp.period_name
and pm.transaction_period_name = decode(pm.actual_flag,
'B', bp.period_name,
'A', decode(GREATEST(BP.period_year*10000+BP.period_num, l_effective_period_num),
BP.period_year*10000+BP.period_num,
decode(BP.amount_type,
'PTD', bp.period_name,
'QTD', decode(BP.period_year,
l_period_year, decode(BP.quarter_num,
l_quarter_num, l_period_name,
bp.period_name),
bp.period_name),
'YTD', decode(BP.period_year,
l_period_year, l_period_name,
bp.period_name),
'PJTD',decode(l_period_name,
NULL, bp.period_name, l_period_name),
bp.period_name),
bp.period_name),
'E', bp.period_name)
and pm.boundary_code between 'A' AND 'Z'
and pm.boundary_code || '' in
(bp.boundary_code, decode(bp.amount_type, 'YTD',
decode(bp.template_id, null, decode(bp.account_type,
'A', 'S', 'L', 'S', 'O', 'S'), 'S')))
and (pm.budget_version_id is null
or pm.budget_version_id = bp.funding_budget_version_id)
)
where bp.packet_id = g_packet_id
and bp.result_code is null
and bp.effect_on_funds_code = 'D'
and bp.funds_check_level_code <> 'N'
and bp.currency_code = decode(bp.actual_flag, 'B', g_func_curr_code,
bp.currency_code)
and bp.funding_budget_version_id = decode(bp.actual_flag, 'B',
bp.budget_version_id,
bp.funding_budget_version_id);
' Update posted balance in gl_bc_packets ' || SQL%ROWCOUNT );
select decode(count(*),
count(decode(substr(bp.result_code, 1, 1), 'P', 1)),
decode(sign(count(decode(bp.result_code, 'P20', 1,
'P22', 1,
'P25', 1,
'P27', 1,
'P31', 1,
'P35', 1,
'P36', 1,
'P37', 1,
'P38', 1,
'P39', 1))), 0, 'S', 1, 'A'),
count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.template_id is null;
SELECT distinct bc.source_distribution_id_num_1
FROM gl_bc_packets bc
WHERE bc.packet_id = p_packet_id
AND bc.result_code like 'F%';
update gl_bc_packets bp
set bp.result_code =
decode(bp.actual_flag || decode(bp.currency_code, g_func_curr_code,
null, '1'), 'B1', 'P03',
decode(bp.actual_flag || decode(bp.budget_version_id,
bp.funding_budget_version_id, null, '1'), 'B1', 'P02',
decode(bp.funds_check_level_code, 'N', 'P01',
decode(bp.effect_on_funds_code, 'I', 'P00',
decode(g_fcmode, 'F', 'P05', decode(bp.account_category_code,
'P',
decode(sign(
((nvl(bp.budget_posted_balance, 0) -
nvl(bp.actual_posted_balance, 0) -
nvl(bp.encumbrance_posted_balance, 0) +
nvl(bp.budget_approved_balance, 0) -
nvl(bp.actual_approved_balance, 0) -
nvl(bp.encumbrance_approved_balance, 0) +
nvl(bp.budget_pending_balance, 0) -
nvl(bp.actual_pending_balance, 0) -
nvl(bp.encumbrance_pending_balance, 0)) -
((nvl(bp.accounted_dr, 0) -
nvl(bp.accounted_cr, 0)) *
decode(bp.actual_flag, 'B', -1, 1)) +
decode(sign(
(nvl(bp.budget_posted_balance, 0) +
nvl(bp.budget_approved_balance, 0) +
nvl(bp.budget_pending_balance, 0)) *
decode(bp.dr_cr_code, 'D', 1, -1)),
-1, nvl(bp.tolerance_amount, 0),
decode(bp.tolerance_percentage ||
';' || bp.tolerance_amount, ';', 0,
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update Result Code gl_bc_packets 1 updated -> ' || SQL%ROWCOUNT || ' rows');
UPDATE gl_bc_packets bp
set result_code='F78'
WHERE bp.packet_id = g_packet_id
AND bp.result_code like 'F%'
AND bp.funding_budget_version_id IS NULL
AND bp.funds_check_level_code = 'B';
update gl_bc_packets bp
set bp.result_code =
decode(bp.account_category_code || substr(bp.result_code, 1, 1),
'PP', 'F01', 'PF', 'F04', 'BP', 'F11', 'BF', 'F14')
where bp.packet_id = g_packet_id
and bp.template_id is null
and (bp.result_code like 'P%'
or bp.result_code in ('F00', 'F03', 'F10', 'F13'))
and exists
(
select
'Summary Row exists and fails Funds Check; Absolute'
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 2 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.result_code =
decode(bp.account_category_code, 'P', 'P22', 'B', 'P27')
where bp.packet_id = g_packet_id
and bp.template_id is null
and bp.result_code like 'P%'
and exists
(
select
'Summary Row exists and fails Funds Check; Advisory'
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 3 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.result_code =
decode(bp.ussgl_transaction_code, null,
decode(bp.account_category_code, 'P', 'F06', 'B', 'F15'),
'F05')
where bp.packet_id = g_packet_id
and bp.template_id is null
and bp.result_code like 'P%'
and (bp.ussgl_transaction_code is not null
or bp.ussgl_link_to_parent_id is not null)
and exists
(
select 'One or more Proprietary/Budgetary counterparts of ' ||
'this transaction exists and fails Funds Check'
from gl_bc_packets pk
where pk.packet_id = g_packet_id
and pk.template_id is null
and pk.result_code like 'F%'
and (pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
or pk.ussgl_link_to_parent_id in (bp.ussgl_link_to_parent_id, bp.ussgl_parent_id))
);
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 4 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.result_code = 'P23'
where bp.packet_id = g_packet_id
and bp.result_code like 'F%'
and bp.template_id is not null;
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 5 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.status_code = decode(bp.status_code || l_ret_code,
'PF', 'R',
'CF', 'F',
decode(bp.status_code || substr(bp.result_code, 1, 1),
'PF', 'R',
'CF', 'F',
bp.status_code)
),
bp.last_update_date = sysdate
where bp.packet_id = g_packet_id;
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 6 updated -> ' || SQL%ROWCOUNT || ' rows');
UPDATE gl_bc_packets pk
SET result_code ='F77'
WHERE pk.packet_id = g_packet_id
AND pk.source_distribution_id_num_1 = l_source_dist_id_num_1_tbl(I)
AND pk.result_code like 'P%';
' update gl_bc_packets 6.1, result_code to F77 for same packet and same distribution updated -> ' || SQL%ROWCOUNT || ' rows');
UPDATE gl_bc_packets pk
SET result_code = 'P12'
WHERE pk.packet_id = g_packet_id
AND result_code = 'P10'
AND exists (SELECT 'x'
FROM gl_bc_packets bc
WHERE bc.packet_id = pk.packet_id
AND bc.result_code = 'P20');
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 7 updated -> ' || SQL%ROWCOUNT || ' rows');
UPDATE gl_bc_packets pk
SET result_code = 'P17'
WHERE pk.packet_id = g_packet_id
AND result_code = 'P15'
AND exists (SELECT 'x'
FROM gl_bc_packets bc
WHERE bc.packet_id = pk.packet_id
AND bc.result_code = 'P25');
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets 8 updated -> ' || SQL%ROWCOUNT || ' rows');
update gl_bc_packets bp
set bp.result_code = 'P21'
where bp.packet_id = g_packet_id
and bp.result_code between 'F00' and 'F19'
and bp.ussgl_link_to_parent_id is null
and bp.template_id is null
and nvl(bp.override_amount, -1) >=
abs(nvl(bp.accounted_dr, 0) - nvl(bp.accounted_cr, 0))
and not exists
(
select 'If Partial Resv disallowed then all non-generated ' ||
'detail lines that failed with any validation errors ' ||
'or because of Funds Availability'
from gl_bc_packets pk
where pk.packet_id = g_packet_id
and pk.template_id is null
and pk.result_code like 'F%'
and ((g_partial_resv_flag = 'N'
and pk.ussgl_link_to_parent_id is null
and (pk.result_code between 'F20' and 'F29'
or nvl(pk.override_amount, -1) <
abs(nvl(pk.accounted_dr, 0) - nvl(pk.accounted_cr, 0))))
or (pk.ussgl_link_to_parent_id = bp.ussgl_parent_id
and pk.result_code between 'F20' and 'F29'))
);
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets1 -> ' || SQL%ROWCOUNT);
update gl_bc_packets bp
set bp.result_code = 'P26'
where bp.packet_id = g_packet_id
and bp.result_code between 'F00' and 'F19'
and bp.ussgl_link_to_parent_id is not null
and exists
(
select 'Corresp Original Transaction which was Overridden'
from gl_bc_packets pk
where pk.packet_id = g_packet_id
and pk.ussgl_parent_id = bp.ussgl_link_to_parent_id
and pk.result_code = 'P21'
);
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update gl_bc_packets2 -> ' || SQL%ROWCOUNT);
select decode(count(*),
count(decode(substr(bp.result_code, 1, 1), 'P', 1)),
decode(sign(count(decode(bp.result_code,
'P20', 1,
'P22', 1,
'P25', 1,
'P27', 1,
'P31', 1,
'P35', 1,
'P36', 1,
'P37', 1,
'P38', 1,
'P39', 1))), 0, 'S', 1, 'A'),
count(decode(substr(bp.result_code, 1, 1), 'F', 1)),
'F', decode(g_partial_resv_flag, 'Y', 'P', 'F'))
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.template_id is null;
update gl_bc_packets bp
set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
'CF', 'F',
decode(bp.status_code ||
substr(bp.result_code, 1, 1), 'PP', 'A',
'PF', 'R', 'CP', 'S', 'CF', 'F', 'T')),
bp.last_update_date = sysdate
where bp.packet_id = g_packet_id;
update gl_bc_packets bp
set bp.status_code = decode(bp.status_code || g_return_code, 'PS', 'A',
'PA', 'A', 'PF', 'R', 'CS', 'S', 'CA', 'S',
'CF', 'F',
decode(bp.status_code ||
substr(bp.result_code, 1, 1), 'PP', 'A',
'PF', 'R', 'CP', 'S', 'CF', 'F',
decode(bp.status_code, 'F', 'F', 'R', 'R', 'T'))),
bp.last_update_date = sysdate
where bp.packet_id = g_packet_id;
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
update gl_bc_packets bp
set bp.result_code = 'P23'
where bp.packet_id = g_packet_id
and bp.result_code like 'F%'
and bp.template_id is not null;
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
update gl_bc_packets bp
set (bp.entered_dr,
bp.entered_cr,
bp.accounted_dr,
bp.accounted_cr,
bp.status_code,
bp.result_code) =
(
select nvl(sum(nvl(pk.entered_dr, 0)), bp.entered_dr),
nvl(sum(nvl(pk.entered_cr, 0)), bp.entered_cr),
nvl(sum(nvl(pk.accounted_dr, 0)), bp.accounted_dr),
nvl(sum(nvl(pk.accounted_cr, 0)), bp.accounted_cr),
nvl(max(pk.status_code), 'R'),
decode(max(pk.status_code), null, bp.result_code,
decode(bp.status_code, 'A', bp.result_code, 'P23'))
from gl_account_hierarchies ah,
gl_bc_packets pk
where ah.ledger_id = g_ledger_id
and ah.template_id = bp.template_id
and ah.summary_code_combination_id = bp.code_combination_id
and ah.detail_code_combination_id = pk.code_combination_id
and pk.packet_id = g_packet_id
and pk.status_code = 'A'
and pk.template_id is null
and pk.actual_flag = bp.actual_flag
and pk.period_name = bp.period_name
and pk.currency_code = bp.currency_code
and pk.je_source_name = bp.je_source_name
and pk.je_category_name = bp.je_category_name
and (pk.budget_version_id is null
or pk.budget_version_id = bp.budget_version_id)
and pk.account_category_code = bp.account_category_code
)
where bp.packet_id = g_packet_id
and bp.template_id is not null;
psa_utils.debug_other_string(g_state_level,l_full_path, ' update gl_bc_packets --> ' || SQL%ROWCOUNT);
select max(bp.je_batch_id),
max(bp.actual_flag),
max(bp.je_line_num) -- bug 5139224
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.template_id is null
and bp.ussgl_link_to_parent_id is null;
select distinct bp.je_batch_id
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null;
select gl_je_batches_s.nextval
from dual;
SELECT enable_je_approval_flag
FROM gl_ledgers_public_v
WHERE ledger_id = g_ledger_id;
SELECT JH.je_source je_source
FROM GL_JE_HEADERS JH
WHERE JH.je_header_id =
(SELECT min(JH1.je_header_id)
FROM GL_JE_HEADERS JH1
WHERE JH1.je_batch_id = c_orig_batch_id);
SELECT journal_approval_flag
FROM GL_JE_SOURCES
WHERE je_source_name = c_je_source;
SELECT 'Y'
FROM FND_DESCRIPTIVE_FLEXS FD
WHERE application_id = 101
and descriptive_flexfield_name = 'GL_JE_LINES'
and context_user_override_flag = 'N'
and (UPPER(default_context_field_name) IN ('CONTEXT3', 'ACCOUNT_NUM'));
psa_utils.debug_other_string(g_state_level,l_full_path, ' goto delete_separate_batch label ');
goto delete_separate_batch;
insert into gl_je_lines
(je_header_id,
je_line_num,
last_update_date,
last_updated_by,
ledger_id,
code_combination_id,
period_name,
effective_date,
status,
creation_date,
created_by,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
tax_code,
invoice_identifier,
no1,
ignore_rate_flag,
reference_1,
reference_10)
select bp.je_header_id,
l_max_je_line_num + 10 * rownum, -- bug 5139224
sysdate,
g_user_id,
g_ledger_id,
bp.code_combination_id,
bp.period_name,
jh.default_effective_date,
'U',
sysdate,
g_user_id,
bp.entered_dr,
bp.entered_cr,
bp.accounted_dr,
bp.accounted_cr,
' ',
' ',
' ',
'Y',
BP.ussgl_link_to_parent_id,
'glxfje() generated: ' || g_packet_id
from gl_period_statuses ps,
gl_je_headers jh,
gl_bc_packets bp
where ps.application_id = 101
and ps.ledger_id = g_ledger_id
and ps.period_name = bp.period_name
and jh.je_header_id = bp.je_header_id
and bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null;
INSERT INTO GL_JE_LINES
(je_header_id,
je_line_num,
last_update_date,
last_updated_by,
ledger_id,
code_combination_id,
period_name,
effective_date,
status,
creation_date,
created_by,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
tax_code,
invoice_identifier,
no1,
ignore_rate_flag,
reference_1,
reference_2,
reference_3,
reference_4,
reference_5,
reference_6,
reference_7,
reference_8,
reference_9,
reference_10,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
SELECT
BP.je_header_id,
JL.je_line_num + 10*rownum,
SYSDATE,
g_user_id,
g_ledger_id,
BP.code_combination_id,
BP.period_name,
JL.effective_date,
'U',
SYSDATE,
g_user_id,
BP.entered_dr,
BP.entered_cr,
BP.accounted_dr,
BP.accounted_cr,
' ',
' ',
' ',
'Y',
BP.ussgl_link_to_parent_id,
BP.reference2,
BP.reference3,
BP.reference4,
BP.reference5,
BP.reference6,
BP.reference7,
BP.reference8,
BP.reference9,
'glxfje() generated: ' || g_packet_id /* for unrsv only */,
decode(JL1.context,JL1.context3,null,JL1.context),
decode(JL1.context,JL1.context3,null,JL1.attribute1),
decode(JL1.context,JL1.context3,null,JL1.attribute2),
decode(JL1.context,JL1.context3,null,JL1.attribute3),
decode(JL1.context,JL1.context3,null,JL1.attribute4),
decode(JL1.context,JL1.context3,null,JL1.attribute5),
decode(JL1.context,JL1.context3,null,JL1.attribute6),
decode(JL1.context,JL1.context3,null,JL1.attribute7),
decode(JL1.context,JL1.context3,null,JL1.attribute8),
decode(JL1.context,JL1.context3,null,JL1.attribute9),
decode(JL1.context,JL1.context3,null,JL1.attribute10)
FROM
GL_PERIOD_STATUSES PS,
GL_JE_LINES JL,
GL_JE_LINES JL1,
GL_BC_PACKETS BP
WHERE
PS.application_id = 101
AND PS.ledger_id = g_ledger_id
AND PS.period_name = BP.period_name
AND JL.je_header_id = BP.je_header_id
AND JL.je_line_num = (SELECT max(JL1.je_line_num)
FROM GL_JE_LINES JL1
WHERE JL1.je_header_id = BP.je_header_id)
AND BP.packet_id = g_packet_id
AND BP.ussgl_link_to_parent_id IS NOT NULL
AND JL1.je_header_id = BP.je_header_id
AND JL1.je_line_num = BP.je_line_num;
' Insert GL_JE_LINES -> ' || sql%ROWCOUNT);
delete from gl_je_lines jl
where jl.je_header_id in
(
select distinct bp.je_header_id
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id IS NOT NULL
)
and jl.reference_10 = 'glxfje() generated: ' || g_packet_id_ursvd;
' delete gl_je_lines - Unreservation ' || sql%ROWCOUNT);
update gl_je_headers jh
set (jh.control_total,
jh.running_total_dr,
jh.running_total_cr,
jh.running_total_accounted_dr,
jh.running_total_accounted_cr) =
(
select decode(jh.control_total, null, null, jh.control_total +
sum(nvl(bp.entered_dr, 0)) *
decode(g_fcmode, 'U', -1, 1)),
nvl(jh.running_total_dr, 0) + sum(nvl(bp.entered_dr, 0)) *
decode(g_fcmode, 'U', -1, 1),
nvl(jh.running_total_cr, 0) + sum(nvl(bp.entered_cr, 0)) *
decode(g_fcmode, 'U', -1, 1),
nvl(jh.running_total_accounted_dr, 0) +
sum(nvl(bp.accounted_dr, 0)) *
decode(g_fcmode, 'U', -1, 1),
nvl(jh.running_total_accounted_cr, 0) +
sum(nvl(bp.accounted_cr, 0)) *
decode(g_fcmode, 'U', -1, 1)
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id = jh.je_batch_id
and bp.je_header_id = jh.je_header_id
and bp.ussgl_link_to_parent_id is not null
)
where jh.je_header_id in
(
select distinct je_header_id
from gl_bc_packets bp1
where bp1.packet_id = g_packet_id
and bp1.ussgl_link_to_parent_id is not null
);
' Update Control Total and Running Totals - gl_je_headers ' || SQL%ROWCOUNT);
update gl_je_batches jb
set (jb.control_total,
jb.running_total_dr,
jb.running_total_cr,
jb.running_total_accounted_dr,
jb.running_total_accounted_cr,
jb.budgetary_control_status,
jb.packet_id) =
(
select decode(jb.control_total, null, null, jb.control_total +
sum(nvl(bp.entered_dr, 0)) *
decode(g_fcmode, 'U', -1, 1)),
nvl(jb.running_total_dr, 0) + sum(nvl(bp.entered_dr, 0)) *
decode(g_fcmode, 'U', -1, 1),
nvl(jb.running_total_cr, 0) + sum(nvl(bp.entered_cr, 0)) *
decode(g_fcmode, 'U', -1, 1),
nvl(jb.running_total_accounted_dr, 0) +
sum(nvl(bp.accounted_dr, 0)) * decode(g_fcmode, 'U', -1, 1),
nvl(jb.running_total_accounted_cr, 0) +
sum(nvl(bp.accounted_cr, 0)) * decode(g_fcmode, 'U', -1, 1),
decode(g_fcmode, 'U', 'R', 'P'),
decode(g_fcmode, 'U', null, jb.packet_id)
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id = jb.je_batch_id
and bp.ussgl_link_to_parent_id is not null
)
where jb.je_batch_id = l_je_batch_id;
' Update gl_je_batches - Batch Control Total, Running Totals, ' ||
' Budgetary Control Status ' || SQL%ROWCOUNT);
l_seg_val_ret_code := gl_je_segment_values_pkg.insert_batch_segment_values(l_je_batch_id);
insert into gl_je_batches
(je_batch_id,
last_update_date,
last_updated_by,
set_of_books_id_11i,
name,
status,
status_verified,
actual_flag,
default_effective_date,
creation_date,
created_by,
default_period_name,
date_created,
description,
running_total_dr,
running_total_cr,
running_total_accounted_dr,
running_total_accounted_cr,
budgetary_control_status,
packet_id,
average_journal_flag,
approval_status_code,
chart_of_accounts_id,
period_set_name,
accounted_period_type)
select l_gen_batch_id,
sysdate,
g_user_id,
g_ledger_id,
substrb('CJE: ' || min(jb.name) ||' '||
to_char(sysdate)||
to_char(sysdate,' HH24:MI:SS: ')||
'A', 1, 100),
'U',
'N',
'A',
min(jb.default_effective_date),
sysdate,
g_user_id,
min(bp.period_name),
sysdate,
decode(min(jb.description), null, null,
substrb('CJE: ' || min(jb.description), 1, 240)),
sum(nvl(bp.entered_dr, 0)),
sum(nvl(bp.entered_cr, 0)),
sum(nvl(bp.accounted_dr, 0)),
sum(nvl(bp.accounted_cr, 0)),
'P',
null, /* For Disabling Unreservation on Generated Batches */
min(jb.average_journal_flag),
l_approval_status_code,
min(jb.chart_of_accounts_id),
min(jb.period_set_name),
min(jb.accounted_period_type)
from gl_period_statuses ps,
gl_bc_packets bp,
gl_je_batches jb
where ps.application_id = 101
and ps.ledger_id = g_ledger_id
and ps.period_name = bp.period_name
and bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null
and jb.je_batch_id = l_je_batch_id;
SELECT je_header_id, je_category, je_source, period_name
FROM gl_je_headers
WHERE je_batch_id = l_je_batch_id;
insert into gl_je_headers
(je_header_id,
last_update_date,
last_updated_by,
ledger_id,
je_category,
je_source,
period_name,
name,
currency_code,
status,
date_created,
accrual_rev_flag,
multi_bal_seg_flag,
actual_flag,
conversion_flag,
default_effective_date,
creation_date,
created_by,
je_batch_id,
description,
currency_conversion_rate,
currency_conversion_type,
currency_conversion_date,
attribute1,
accrual_rev_change_sign_flag,
tax_status_code)
select gl_je_headers_s.nextval,
sysdate,
g_user_id,
g_ledger_id,
jh.je_category,
jh.je_source,
jh.period_name,
substrb('CJE: ' || jh.name ||' '||
to_char(sysdate)||
to_char(sysdate,' HH24:MI:SS'),1,100),
jh.currency_code,
'U',
sysdate,
'N',
'N',
'A',
'N',
jh.default_effective_date,
sysdate,
g_user_id,
l_gen_batch_id,
decode(jh.description, null, null,
substrb('CJE: ' || jh.description, 1, 240)),
1,
'User',
sysdate,
to_char(jh.je_header_id),
l_reversal_method,
'N'
from gl_je_headers jh
where jh.je_batch_id = l_je_batch_id
and jh.je_header_id = x.je_header_id
and exists
(
select 'JE headers with associated generated transactions'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id = l_je_batch_id
and bp.je_header_id = jh.je_header_id
and bp.ussgl_link_to_parent_id is not null
);
' Insert gl_je_headers - Headers for Actual Batches ' || SQL%ROWCOUNT);
update gl_je_headers jh
set (jh.running_total_dr,
jh.running_total_cr,
jh.running_total_accounted_dr,
jh.running_total_accounted_cr) =
(
select sum(nvl(bp.entered_dr, 0)),
sum(nvl(bp.entered_cr, 0)),
sum(nvl(bp.accounted_dr, 0)),
sum(nvl(bp.accounted_cr, 0))
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id = l_je_batch_id
and bp.je_header_id = to_number(jh.attribute1)
and bp.ussgl_link_to_parent_id is not null
)
where JH.je_batch_id = l_gen_batch_id;
' update gl_je_headers - running totals - ' || SQL%ROWCOUNT);
insert into gl_je_lines
(je_header_id,
je_line_num,
last_update_date,
last_updated_by,
ledger_id,
code_combination_id,
period_name,
effective_date,
status,
creation_date,
created_by,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
tax_code,
invoice_identifier,
no1,
ignore_rate_flag,
reference_10)
select jh.je_header_id,
10 * row_number() over (partition by jh.je_header_id
order by jh.je_header_id),
sysdate,
g_user_id,
g_ledger_id,
bp.code_combination_id,
bp.period_name,
jh.default_effective_date,
'U',
sysdate,
g_user_id,
bp.entered_dr,
bp.entered_cr,
bp.accounted_dr,
bp.accounted_cr,
' ',
' ',
' ',
'Y',
'glxfje() generated: ' || g_packet_id
from gl_je_headers jh,
gl_bc_packets bp
where jh.je_batch_id = l_gen_batch_id
and jh.attribute1 = to_char(bp.je_header_id)
and bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null;
INSERT INTO GL_JE_LINES
(je_header_id,
je_line_num,
last_update_date,
last_updated_by,
ledger_id,
code_combination_id,
period_name,
effective_date,
status,
creation_date,
created_by,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
tax_code,
invoice_identifier,
no1,
ignore_rate_flag,
reference_10,
context,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10)
SELECT
min(JH.je_header_id),
10*count(BP1.rowid),
SYSDATE,
g_user_id,
g_ledger_id,
min(BP.code_combination_id),
min(BP.period_name),
min(JH.default_effective_date),
'U',
SYSDATE,
g_ledger_id,
min(BP.entered_dr),
min(BP.entered_cr),
min(BP.accounted_dr),
min(BP.accounted_cr),
' ',
' ',
' ',
'Y',
'glxfje() generated: ' || g_packet_id, /* for unrsv only */
decode(min(JL.context),min(JL.context3),null,min(JL.context)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute1)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute2)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute3)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute4)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute5)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute6)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute7)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute8)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute9)),
decode(min(JL.context),min(JL.context3),null,min(JL.attribute10))
FROM
GL_JE_HEADERS JH,
GL_BC_PACKETS BP1,
GL_BC_PACKETS BP,
GL_JE_LINES JL
WHERE
JH.je_batch_id = l_gen_batch_id
AND JH.attribute1 = to_char(BP.je_header_id)
AND BP1.packet_id = BP.packet_id
AND BP1.je_batch_id = BP.je_batch_id
AND BP1.je_header_id = BP.je_header_id
AND BP1.rowid <= BP.rowid
AND BP1.ussgl_link_to_parent_id IS NOT NULL
AND BP.packet_id = g_packet_id
AND BP.ussgl_link_to_parent_id IS NOT NULL
AND JL.je_header_id = BP.je_header_id
AND JL.je_line_num = BP.je_line_num
GROUP BY BP.rowid;
' Insert gl_je_lines - ' || SQL%ROWCOUNT);
update gl_bc_packets bp
set bp.je_batch_id = l_gen_batch_id
where bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null;
' update gl_bc_packets - je_bacth_id - ' || SQL%ROWCOUNT);
update gl_je_headers jh
set jh.attribute1 = null
where jh.je_batch_id = l_gen_batch_id;
' update gl_je_headers -> ' || SQL%ROWCOUNT);
l_seg_val_ret_code := gl_je_segment_values_pkg.insert_batch_segment_values(l_gen_batch_id);
<>
-- ========================= FND LOG ===========================
psa_utils.debug_other_string(g_state_level,l_full_path,
' Reached delete_seperate_batch label ');
delete from gl_je_lines jl
where jl.je_header_id in
(
select distinct jh.je_header_id
from gl_je_headers jh,
gl_bc_packets bp
where jh.je_batch_id = bp.je_batch_id
and bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null
)
and jl.reference_10 = 'glxfje() generated: ' || g_packet_id_ursvd;
' Delete gl_je_lines - ' || SQL%ROWCOUNT);
delete from gl_je_headers jh
where jh.je_batch_id in
(
select distinct bp.je_batch_id
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.ussgl_link_to_parent_id is not null
);
' Delete gl_je_headers - ' || SQL%ROWCOUNT);
delete from gl_je_batches jb
where jb.je_batch_id = x.je_batch_id;
' Delete gl_je_batches - ' || SQL%ROWCOUNT);
l_seg_val_ret_code := gl_je_segment_values_pkg.insert_batch_segment_values(x.je_batch_id);
delete from gl_bc_packets bp
where bp.packet_id in (p_packetid, p_packetid_ursvd);
' delete from gl_bc_packets ' || SQL%ROWCOUNT);
delete from gl_bc_packet_arrival_order ao
where ao.packet_id in (p_packetid, p_packetid_ursvd);
' delete from gl_bc_packet_arrival_order ' || SQL%ROWCOUNT);
delete from gl_bc_packets bp
where bp.event_id = p_eventid;
' delete from gl_bc_packets ' || SQL%ROWCOUNT);
delete from gl_bc_packet_arrival_order ao
where ao.packet_id in (select packet_id
from gl_bc_packets
where event_id = p_eventid);
' delete from gl_bc_packet_arrival_order ' || SQL%ROWCOUNT);
delete from gl_bc_packets_hists bp
where bp.event_id = p_eventid;
' delete from gl_bc_packets_hists ' || SQL%ROWCOUNT);
update gl_bc_packets bp
set bp.status_code = 'T'
where bp.packet_id = g_packet_id;
' update gl_bc_packets with T -> ' || SQL%ROWCOUNT );
update gl_bc_packet_arrival_order ao
set ao.affect_funds_flag = 'N'
where ao.packet_id = g_packet_id;
' update gl_bc_packet_arrival_order to N -> ' || SQL%ROWCOUNT );
SELECT DISTINCT 'Y' status
FROM ALL_OBJECTS
WHERE object_name = 'FV_AP_PREPAY_PKG'
AND object_type = 'PACKAGE'
AND owner = (SELECT oracle_username
FROM fnd_oracle_userid
WHERE read_only_flag = 'U')
AND status = 'VALID';
SELECT count(*) pkt_cnt
FROM gl_bc_packets
WHERE packet_id = c_packet_id;
SELECT 'Y' batch_id
FROM GL_BC_PACKETS
WHERE packet_id = c_packet_id
AND je_batch_id IS NOT NULL
AND rownum = 1;
update gl_bc_packet_arrival_order ao
set ao.affect_funds_flag = 'N'
where ao.packet_id = g_packet_id;
' update gl_bc_packet_arrival_order -> ' || SQL%ROWCOUNT);
select 'Associated Generated JEs to be appended or inserted'
from dual
where exists
(
select 'Associated Generated Row from existing GL Batch'
from gl_bc_packets bp
where bp.packet_id = g_packet_id
and bp.je_batch_id is not null
and bp.je_batch_id >= 0
and bp.ussgl_transaction_code is not null
);
| function. Function deletes rows from gl_bc_packets and |
| inserts them in gl_bc_packets_hists. Only rows with |
| status_code R, S, F, T, P, C are deleted. |
+=======================================================================*/
PROCEDURE optimize_packets (p_ledger_id IN NUMBER, p_purge_days IN NUMBER) IS
l_full_path VARCHAR2(100);
| - These rows should get inserted in gl_bc_packets_hists |
| |
| P, C - All rows for the p_ledger_id for which session has |
| expired or which are older than 5 days (120 hours) |
| - These rows should not be stored in gl_bc_packets_hists |
| |
+----------------------------------------------------------------------*/
DELETE from gl_bc_packets Q
where
Q.status_code IN ('P', 'C')
and ((((sysdate - Q.last_update_date)*24) > 48) OR
(NOT EXISTS (SELECT 'x'
FROM v$session
WHERE audsid = Q.session_id
and Serial# = Q.serial_id)));
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 1 deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from gl_bc_packets Q
where
Q.ledger_id = p_ledger_id
and Q.status_code in ('R','S','F', 'T') -- Bug 10171221
and rownum < 501 returning
PACKET_ID,
LEDGER_ID,
JE_SOURCE_NAME,
JE_CATEGORY_NAME,
CODE_COMBINATION_ID,
ACTUAL_FLAG,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
QUARTER_NUM,
CURRENCY_CODE,
STATUS_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
BUDGET_VERSION_ID,
ENCUMBRANCE_TYPE_ID,
TEMPLATE_ID,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
USSGL_TRANSACTION_CODE,
ORIGINATING_ROWID,
ACCOUNT_SEGMENT_VALUE,
AUTOMATIC_ENCUMBRANCE_FLAG,
FUNDING_BUDGET_VERSION_ID,
FUNDS_CHECK_LEVEL_CODE,
AMOUNT_TYPE,
BOUNDARY_CODE,
TOLERANCE_PERCENTAGE,
TOLERANCE_AMOUNT,
OVERRIDE_AMOUNT,
DR_CR_CODE,
ACCOUNT_TYPE,
ACCOUNT_CATEGORY_CODE,
EFFECT_ON_FUNDS_CODE,
RESULT_CODE,
BUDGET_POSTED_BALANCE,
ACTUAL_POSTED_BALANCE,
ENCUMBRANCE_POSTED_BALANCE,
BUDGET_APPROVED_BALANCE,
ACTUAL_APPROVED_BALANCE,
ENCUMBRANCE_APPROVED_BALANCE,
BUDGET_PENDING_BALANCE,
ACTUAL_PENDING_BALANCE,
ENCUMBRANCE_PENDING_BALANCE,
REFERENCE1,
REFERENCE2,
REFERENCE3,
REFERENCE4,
REFERENCE5,
JE_BATCH_NAME,
JE_BATCH_ID,
JE_HEADER_ID,
JE_LINE_NUM,
JE_LINE_DESCRIPTION,
REFERENCE6,
REFERENCE7,
REFERENCE8,
REFERENCE9,
REFERENCE10,
REFERENCE11,
REFERENCE12,
REFERENCE13,
REFERENCE14,
REFERENCE15,
REQUEST_ID,
USSGL_PARENT_ID,
USSGL_LINK_TO_PARENT_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
BC_DATE,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_CHAR_1,
SOURCE_DISTRIBUTION_ID_CHAR_2,
SOURCE_DISTRIBUTION_ID_CHAR_3,
SOURCE_DISTRIBUTION_ID_CHAR_4,
SOURCE_DISTRIBUTION_ID_CHAR_5,
SOURCE_DISTRIBUTION_ID_NUM_1,
SOURCE_DISTRIBUTION_ID_NUM_2,
SOURCE_DISTRIBUTION_ID_NUM_3,
SOURCE_DISTRIBUTION_ID_NUM_4,
SOURCE_DISTRIBUTION_ID_NUM_5,
SESSION_ID,
SERIAL_ID,
APPLICATION_ID,
ENTITY_ID,
GROUP_ID
bulk collect into g_bc_pkts_hist;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete gl_bc_packets 2 deleted ' || SQL%ROWCOUNT || ' rows');
insert into gl_bc_packets_hists
values g_bc_pkts_hist(i);
DELETE from psa_xla_accounting_errors
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_accounting_errors deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_bc_accounting_errors
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_bc_accounting_errors deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_xla_validation_lines_logs
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_validation_lines_logs deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_xla_events_logs
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_events_logs deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_xla_ae_lines_logs
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_ae_lines_logs deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_xla_ae_headers_logs
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_ae_header_logs deleted ' || SQL%ROWCOUNT || ' rows');
DELETE from psa_xla_dist_links_logs
where (sysdate - creation_date) >= p_purge_days;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete psa_xla_dist_links_logs deleted ' || SQL%ROWCOUNT || ' rows');
| Delete unprocessed payables BC events |
| Delete processed orphan payables BC events |
+=======================================================================*/
PROCEDURE bc_optimizer (err_buf OUT NOCOPY VARCHAR2,
ret_code OUT NOCOPY VARCHAR2,
p_ledger_id IN NUMBER,
p_purge_days IN NUMBER,
p_delete_mode IN VARCHAR2) IS
p_init_msg_list varchar2(1);
psa_utils.debug_other_string(g_state_level,l_path_name,'p_delete_mode = '||p_delete_mode);
IF (NVL(p_delete_mode, 'B') IN ('B', 'P')) THEN
optimize_packets(p_ledger_id, p_purge_days);
IF (NVL(p_delete_mode, 'B') IN ('B', 'E')) THEN
-- R12 upgrade date fetch to delete all unprocessed events from R12 installation date to sysdate
psa_utils.debug_other_string(g_state_level,l_path_name,'Fetch PSA: R12 Upgrade Date profile value');
' PO_DRAFT_EVENTS_PKG. delete_draft_events ('||
' :p_init_msg_list, '||
' :p_ledger_id, '||
' :p_start_date, '||
' :p_end_date, '||
' :p_calling_sequence, '||
' :x_return_status, '||
' :x_msg_count, '||
' :x_msg_data); '||
psa_utils.debug_other_string(g_state_level,l_path_name,'Before calling delete_events PO');
psa_utils.debug_other_string(g_state_level,l_path_name,'After calling delete_events PO');
psa_utils.debug_other_string(g_state_level,l_path_name,'Before calling delete_events');
PSA_AP_BC_PVT.delete_events(
p_init_msg_list => 'F',
p_ledger_id => p_ledger_id,
p_start_date => l_r12_upgrade_date,
p_end_date => sysdate,
p_calling_sequence => 'psa_funds_cecker_pkg.bc_optimizer',
x_return_status => p_return_status,
x_msg_count =>p_msg_count,
x_msg_data => p_msg_data);
psa_utils.debug_other_string(g_state_level,l_path_name,'After calling delete_events');
psa_utils.debug_other_string(g_state_level,l_path_name,'Inside delete_event exception: '||SQLERRM);
psa_utils.debug_other_string(g_state_level,l_path_name,'Before calling delete_processed_orphan_events');
psa_ap_bc_pvt.delete_processed_orphan_events
( p_init_msg_list => 'F',
p_ledger_id => p_ledger_id,
p_calling_sequence => 'psa_funds_cecker_pkg.bc_optimizer',
p_return_status => p_return_status,
p_msg_count =>p_msg_count,
p_msg_data => p_msg_data);
psa_utils.debug_other_string(g_state_level,l_path_name,'After calling delete_processed_orphan_events');
psa_utils.debug_other_string(g_state_level,l_path_name,'Inside delete_processed_orphan_events exception: '||SQLERRM);
| Deletes rows from gl_bc_packets_hists depending upon |
| the criteria selected by user while running SRS |
+=======================================================================*/
PROCEDURE bc_purge_hist (err_buf OUT NOCOPY VARCHAR2,
ret_code OUT NOCOPY VARCHAR2,
p_ledger_id IN NUMBER,
p_purge_mode IN VARCHAR2,
p_purge_statuses IN VARCHAR2,
p_purge_date IN VARCHAR2) IS
l_stmt VARCHAR2(5000);
l_stmt := 'delete from gl_bc_packets_hists '||
'where (last_update_date < :purge_date) '||
' and ledger_id = :p_ledger_id ';
select gl_bc_packets_s.nextval into l_pkt_id
from dual;
| Description : Inserts data in gl_bc_packets using the plsql table |
| passed as parameter. Commits in autonomous mode. |
+=======================================================================*/
FUNCTION populate_bc_pkts (p_bc_pkts IN BC_PKTS_REC) RETURN BOOLEAN IS
-- ========================= FND LOG ===========================
l_full_path VARCHAR2(100);
INSERT INTO gl_bc_packets
VALUES p_bc_pkts(i);
SELECT
B.actual_flag,
H.je_source,
B.default_period_name,
B.je_batch_id,
substrb(B.name,1,88)
FROM
gl_je_headers H,
gl_je_batches B,
gl_automatic_posting_options O,
gl_automatic_posting_sets S
WHERE
S.autopost_set_id = p_autopost_set_id
AND S.autopost_set_id = O.autopost_set_id
AND o.ledger_id = H.ledger_id
AND B.actual_flag = decode(O.actual_flag,
'L', B.actual_flag,
O.actual_flag)
AND B.default_period_name = decode(O.period_name,
'ALL', B.default_period_name,
O.period_name)
AND B.je_batch_id = H.je_batch_id
AND H.je_source = decode(O.je_source_name,
'ALL', H.je_source,
O.je_source_name)
AND B.status = 'U'
AND B.budgetary_control_status in ('R', 'F')
AND NOT EXISTS
( SELECT 'Not all category match'
FROM GL_JE_HEADERS H2
WHERE
H2.je_batch_id = B.je_batch_id
AND H2.je_category <> decode(O.je_category_name,
'ALL', H2.je_category,
O.je_category_name) )
AND NOT EXISTS
( SELECT 'Untaxed Journals'
FROM GL_JE_HEADERS GLH
WHERE GLH.tax_status_code = 'R'
AND GLH.je_batch_id = B.je_batch_id
AND B.actual_flag = 'A'
AND GLH.currency_code <> 'STAT'
AND GLH.je_source = 'Manual' )
GROUP BY B.je_batch_id, B.actual_flag,
B.default_period_name,B.name,H.je_source
ORDER BY B.default_period_name,B.actual_flag;
SELECT
b.actual_flag,
h.je_source,
b.default_period_name,
b.je_batch_id,
substrb(b.name,1,88)
FROM
gl_je_headers h,
gl_je_batches b,
gl_automatic_posting_options o,
gl_automatic_posting_sets s
WHERE
s.autopost_set_id = p_autopost_set_id
AND s.autopost_set_id = o.autopost_set_id
AND o.ledger_id = H.ledger_id
AND b.actual_flag = decode(o.actual_flag,
'L', b.actual_flag,
o.actual_flag)
AND b.default_period_name = decode(o.period_name,
'ALL', b.default_period_name,
o.period_name)
AND b.je_batch_id = h.je_batch_id
AND h.je_source = decode(o.je_source_name,
'ALL', h.je_source,
o.je_source_name)
AND b.status = 'U'
AND b.budgetary_control_status in ('R', 'F')
AND NOT EXISTS
( SELECT 'Not all category match'
FROM gl_je_headers h2
WHERE
h2.je_batch_id = b.je_batch_id
AND h2.je_category <> decode(o.je_category_name,
'ALL', h2.je_category,
o.je_category_name) )
GROUP BY b.je_batch_id, b.actual_flag,
b.default_period_name,b.name,h.je_source
ORDER BY b.default_period_name,b.actual_flag;
SELECT
application_column_name
FROM
fnd_id_flex_segments
WHERE
id_flex_num = (SELECT
chart_of_accounts_id
FROM gl_ledgers
WHERE ledger_id = p_ledger_id)
AND id_flex_code = 'GL#'
AND application_id = 101
AND enabled_flag = 'Y';
SELECT
TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI')
INTO
l_date
FROM
dual;
SELECT
name,
enable_budgetary_control_flag,
enable_automatic_tax_flag,
chart_of_accounts_id,
currency_code
INTO
l_sob_name,
l_budgetary_control_flag,
l_automatic_tax_flag,
l_coa_id,
l_currency_code
FROM
gl_sets_of_books
WHERE
set_of_books_id = p_ledger_id;
SELECT
autopost_set_name
INTO
l_autopost_set_name
FROM
gl_automatic_posting_sets
WHERE
autopost_set_id = p_autopost_set_id;
l_tmp_stmt := 'SELECT '||
l_packet_id||', '||
l_ledger_id||', '||''''||
l_source_name||''''||
', h.je_category'||
', l.code_combination_id, '||''''||
l_actual_flag|| ''''||
', ps.period_name, ps.period_year, ps.period_num, ps.quarter_num, '||
'h.currency_code, decode('||''''||l_check_flag||''''||',''C'',''C'',''M'',''C'',''P'',''P'',''R'',''P''), sysdate, '||
l_user_id;
fnd_file.put_line(fnd_file.log, 'Funds C/R: Inserting into l_failed_bc_pkts for packet_id->'||l_bc_pkts(x).packet_id);
'Funds C/R: Inserting into l_failed_bc_pkts for packet_id->'||l_bc_pkts(x).packet_id);
UPDATE
gl_je_batches
SET
budgetary_control_status = decode(l_check_flag, 'R',
decode (l_glxfck_return_code,
'S', 'P',
'A', 'P',
'F', 'F',
'P', 'F',
'T', 'R', l_glxfck_return_code),
'P',
decode (l_glxfck_return_code,
'S', 'P',
'A', 'P',
'F', 'F',
'P', 'F',
'T', 'R', l_glxfck_return_code),
budgetary_control_status),
packet_id = l_bc_pkts(x).packet_id
WHERE
je_batch_id = l_bc_pkts(x).je_batch_id;
'Funds C/R: Failed to update budgetary_control_status for gl_je_batches');
'Funds C/R: Failed to update budgetary_control_status for gl_je_batches');
SELECT
meaning
INTO
l_fmeaning
FROM
gl_lookups
WHERE
lookup_code = l_glxfck_return_code
AND lookup_type = 'FUNDS_CHECK_RETURN_CODE';
SELECT
l.meaning
INTO
l_jmeaning
FROM
gl_lookups l, gl_je_batches b
WHERE
l.lookup_code = b.budgetary_control_status
AND l.lookup_type = 'JE_BATCH_BC_STATUS'
AND b.je_batch_id = l_bc_pkts(x).je_batch_id;
l_je_stmt := 'SELECT ';
SELECT
l.meaning
INTO
l_priority
FROM
gl_lookups l
WHERE
l.lookup_type = 'BC_SEVERITY_FLAG'
AND l.lookup_code = upper(substr(l_line_result_code,1,1));
l_je_seg_stmt := 'SELECT distinct ';
l_je_bud_stmt := 'SELECT ';
SELECT
nvl(ussgl_parent_id, 0)
INTO
l_ussgl_parent_id
FROM
gl_bc_packets
WHERE
rowid = l_rowid;
l_je_bud_seg_stmt := 'SELECT distinct ';
PROCEDURE glsibc (p_last_updated_by NUMBER,
p_new_template_id NUMBER,
p_ledger_id NUMBER) IS
l_full_path VARCHAR2(100);
INSERT INTO GL_BC_PACKETS
(packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
encumbrance_type_id,
template_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
funding_budget_version_id,
funds_check_level_code,
amount_type,
boundary_code,
dr_cr_code,
account_category_code,
effect_on_funds_code,
result_code,
session_id,
serial_id,
application_id)
SELECT
min(BP.packet_id),
min(BP.ledger_id),
min(BP.je_source_name),
min(BP.je_category_name),
min(AH.summary_code_combination_id),
min(BP.actual_flag),
min(BP.period_name),
min(BP.period_year),
min(BP.period_num),
min(BP.quarter_num),
min(BP.currency_code),
'A', /* approved */
SYSDATE,
p_last_updated_by,
min(decode(BP.actual_flag, 'B', BP.budget_version_id, NULL)),
min(decode(BP.actual_flag, 'E', BP.encumbrance_type_id, NULL)),
p_new_template_id,
sum(nvl(BP.entered_dr,0)),
sum(nvl(BP.entered_cr,0)),
sum(nvl(BP.accounted_dr,0)),
sum(nvl(BP.accounted_cr,0)),
SB.funding_budget_version_id,
SB.funds_check_level_code,
SB.amount_type,
SB.boundary_code,
SB.dr_cr_code,
min(ST.account_category_code),
decode(
decode(min(BP.actual_flag) || SB.dr_cr_code ||
min(ST.account_category_code),
'BCP', 'dec',
'ADP', 'dec',
'EDP', 'dec',
'ACB', 'dec',
'inc'),
'dec', /* +ve net dr => decreasing fa */
decode(sign(sum(nvl(BP.accounted_dr,0) - nvl(BP.accounted_cr,0))),
1, 'D', 'I'),
'inc', /* +ve net dr => increasing fa */
decode(sign(sum(nvl(BP.accounted_dr,0) - nvl(BP.accounted_cr,0))),
-1, 'D', 'I')),
'P04', /* P04 - This summary transaction generated does not */
/* require funds check */
min(BP.session_id),
min(BP.serial_id),
min(BP.application_id)
FROM
GL_ACCOUNT_HIERARCHIES AH,
GL_BC_PACKETS BP,
GL_BC_PACKET_ARRIVAL_ORDER AO,
GL_SUMMARY_TEMPLATES ST,
GL_SUMMARY_BC_OPTIONS SB,
GL_BUDGETS B,
GL_BUDGET_VERSIONS BV,
GL_PERIOD_STATUSES PS
WHERE
AH.ledger_id = p_ledger_id
AND AH.detail_code_combination_id = BP.code_combination_id
AND AH.template_id = p_new_template_id
AND BP.status_code = 'A'
AND BP.ledger_id = p_ledger_id
AND BP.template_id IS NULL
AND BP.packet_id = AO.packet_id
AND BP.account_category_code = ST.account_category_code
AND nvl(BP.budget_version_id, -1) = decode(BP.actual_flag, 'B',
SB.funding_budget_version_id, -1)
AND AO.ledger_id = p_ledger_id
AND AO.affect_funds_flag = 'Y'
AND ST.template_id = p_new_template_id
AND SB.template_id = ST.template_id
AND SB.funding_budget_version_id = BV.budget_version_id
AND BV.budget_name = B.budget_name
AND PS.application_id = 101
AND PS.ledger_id = p_ledger_id
AND PS.period_name = BP.period_name
AND PS.effective_period_num >= (SELECT P1.effective_period_num
FROM GL_PERIOD_STATUSES P1
WHERE P1.period_name = B.first_valid_period_name
AND P1.application_id = 101
AND P1.ledger_id = p_ledger_id)
AND PS.effective_period_num <= (SELECT P2.effective_period_num
FROM GL_PERIOD_STATUSES P2
WHERE P2.period_name = B.last_valid_period_name
AND P2.application_id = 101
AND P2.ledger_id = p_ledger_id)
GROUP BY
BP.packet_id,
AH.summary_code_combination_id,
BP.actual_flag,
BP.period_name,
BP.currency_code,
BP.je_source_name,
BP.je_category_name,
BP.budget_version_id,
BP.encumbrance_type_id,
SB.funding_budget_version_id,
SB.funds_check_level_code,
SB.amount_type,
SB.boundary_code,
SB.dr_cr_code
HAVING
sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
p_last_updated_by IN NUMBER) IS
l_full_path VARCHAR2(100);
fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Inserting into GL_BC_PACKETS ...');
INSERT INTO GL_BC_PACKETS
(packet_id,
ledger_id,
je_source_name,
je_category_name,
code_combination_id,
actual_flag,
period_name,
period_year,
period_num,
quarter_num,
currency_code,
status_code,
last_update_date,
last_updated_by,
budget_version_id,
encumbrance_type_id,
template_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
funding_budget_version_id,
funds_check_level_code,
amount_type,
boundary_code,
dr_cr_code,
account_category_code,
effect_on_funds_code,
result_code,
session_id,
serial_id,
application_id)
SELECT
BP.packet_id,
min(BP.ledger_id),
BP.je_source_name,
BP.je_category_name,
AH.summary_code_combination_id,
BP.actual_flag,
BP.period_name,
min(BP.period_year),
min(BP.period_num),
min(BP.quarter_num),
BP.currency_code,
'A', /* approved */
SYSDATE,
p_last_updated_by,
min(decode(BP.actual_flag, 'B',
BP.budget_version_id, NULL)),
min(decode(BP.actual_flag, 'E',
BP.encumbrance_type_id, NULL)),
p_curr_temp_id,
0, 0, 0, 0,
SB.funding_budget_version_id,
SB.funds_check_level_code,
SB.amount_type,
SB.boundary_code,
SB.dr_cr_code,
min(ST.account_category_code),
'I',
'P04', /* P04 - This summary transaction generated */
/* does not require funds check */
min(BP.session_id),
min(BP.serial_id),
min(BP.application_id)
FROM
GL_ACCOUNT_HIERARCHIES AH,
GL_BC_PACKETS BP,
GL_BC_PACKET_ARRIVAL_ORDER AO,
GL_SUMMARY_TEMPLATES ST,
GL_SUMMARY_BC_OPTIONS SB,
GL_BUDGETS B,
GL_BUDGET_VERSIONS BV,
GL_PERIOD_STATUSES PS
WHERE AH.ledger_id = p_ledger_id
AND AH.detail_code_combination_id = BP.code_combination_id
AND AH.template_id = p_curr_temp_id
AND BP.status_code = 'A'
AND BP.ledger_id = p_ledger_id
AND BP.template_id IS NULL
AND BP.packet_id = AO.packet_id
AND BP.account_category_code = ST.account_category_code
AND nvl(BP.budget_version_id, -1) =
decode(BP.actual_flag, 'B',
SB.funding_budget_version_id, -1)
AND AO.ledger_id = p_ledger_id
AND AO.affect_funds_flag = 'Y'
AND ST.template_id = p_curr_temp_id
AND NOT EXISTS
( Select 'Y'
From GL_BC_PACKETS BP2
Where BP2.ledger_id = p_ledger_id
And BP2.template_id = p_curr_temp_id
And BP2.code_combination_id = AH.summary_code_combination_id
And BP2.packet_id = BP.packet_id
And BP2.actual_flag = BP.actual_flag
And BP2.period_name = BP.period_name
And BP2.currency_code = BP.currency_code
And BP2.je_source_name = BP.je_source_name
And BP2.je_category_name = BP.je_category_name
And nvl(BP2.encumbrance_type_id,-1) = nvl(BP.encumbrance_type_id,-1)
And nvl(BP2.budget_version_id,-1) = nvl(BP.budget_version_id,-1))
AND SB.template_id = p_curr_temp_id
AND SB.funding_budget_version_id = BV.budget_version_id
AND BV.budget_name = B.budget_name
AND PS.application_id = 101
AND PS.ledger_id = p_ledger_id
AND PS.period_name = BP.period_name
AND PS.effective_period_num >=
( Select P1.effective_period_num
From GL_PERIOD_STATUSES P1
Where P1.period_name = B.first_valid_period_name
And P1.application_id = 101
And P1.ledger_id = p_ledger_id)
AND PS.effective_period_num <=
( Select P2.effective_period_num
From GL_PERIOD_STATUSES P2
Where P2.period_name = B.last_valid_period_name
And P2.application_id = 101
And P2.ledger_id = p_ledger_id)
GROUP BY
BP.packet_id,
AH.summary_code_combination_id,
BP.actual_flag,
BP.period_name,
BP.currency_code,
BP.je_source_name,
BP.je_category_name,
BP.budget_version_id,
BP.encumbrance_type_id,
SB.funding_budget_version_id,
SB.funds_check_level_code,
SB.amount_type,
SB.boundary_code,
SB.dr_cr_code
HAVING
sum(nvl(BP.accounted_dr,0)-nvl(BP.accounted_cr,0)) <> 0;
psa_utils.debug_other_string(g_state_level,l_full_path, ' Insert GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
UPDATE gl_bc_packets bp2
SET (entered_dr, entered_cr, accounted_dr, accounted_cr,
effect_on_funds_code) =
(SELECT SUM (NVL (bp.entered_dr, 0)), SUM (NVL (bp.entered_cr,0)),
SUM (NVL (bp.accounted_dr, 0)),
SUM (NVL (bp.accounted_cr, 0)),
DECODE (DECODE ( MIN (bp.actual_flag)
|| MIN(sb.dr_cr_code)
|| MIN (st.account_category_code),
'BCP', 'dec',
'ADP', 'dec',
'EDP', 'dec',
'ACB', 'dec',
'inc'
),
'dec', /* +ve net dr => decreasing fa */
DECODE (SIGN (SUM ( NVL (bp.accounted_dr, 0)
- NVL (bp.accounted_cr, 0)
)
),
1, 'D',
'I'
),
'inc', /* +ve net dr => increasing fa */
DECODE (SIGN (SUM ( NVL (bp.accounted_dr, 0)
- NVL (bp.accounted_cr, 0)
)
),
-1, 'D',
'I'
)
)
FROM gl_bc_packets bp,
gl_account_hierarchies ah,
gl_bc_packet_arrival_order ao,
gl_summary_templates st,
gl_summary_bc_options sb,
gl_budgets b,
gl_budget_versions bv,
gl_period_statuses ps
WHERE ah.ledger_id = p_ledger_id
AND ah.template_id = p_curr_temp_id
AND ah.summary_code_combination_id = bp2.code_combination_id
AND st.template_id = p_curr_temp_id
AND bp.status_code = 'A'
AND bp.ledger_id = p_ledger_id
AND bp.template_id IS NULL
AND bp.code_combination_id = ah.detail_code_combination_id
AND bp.account_category_code = st.account_category_code
AND bp.packet_id = bp2.packet_id
AND bp.actual_flag = bp2.actual_flag
AND bp.period_name = bp2.period_name
AND bp.currency_code = bp2.currency_code
AND bp.je_source_name = bp2.je_source_name
AND bp.je_category_name = bp2.je_category_name
AND nvl(BP.encumbrance_type_id, -1) = nvl(BP2.encumbrance_type_id, -1)
AND nvl(BP.budget_version_id,-1) = nvl(BP2.budget_version_id,-1)
AND sb.template_id = p_curr_temp_id
AND sb.funding_budget_version_id = bv.budget_version_id
AND bv.budget_name = b.budget_name
AND ps.application_id = 101
AND ps.ledger_id = p_ledger_id
AND ps.period_name = bp.period_name
AND ps.effective_period_num >=
(SELECT p1.effective_period_num
FROM gl_period_statuses p1
WHERE p1.period_name = b.first_valid_period_name
AND p1.application_id = 101
AND p1.ledger_id = p_ledger_id)
AND ps.effective_period_num <=
(SELECT p2.effective_period_num
FROM gl_period_statuses p2
WHERE p2.period_name = b.last_valid_period_name
AND p2.application_id = 101
AND p2.ledger_id = p_ledger_id)
AND NVL (bp.budget_version_id, -1) =
DECODE (bp.actual_flag,
'B', sb.funding_budget_version_id,
-1
)
AND ao.ledger_id = p_ledger_id
AND ao.affect_funds_flag = 'Y'
AND ao.packet_id = bp2.packet_id)
WHERE bp2.ledger_id = p_ledger_id
AND bp2.template_id = p_curr_temp_id
AND bp2.code_combination_id IN (SELECT code_combination_id
FROM gl_code_combinations
WHERE template_id = p_curr_temp_id);
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
| The Delete statement will be executed here ALWAYS |
+---------------------------------------------------*/
-- =========================== FND LOG ===========================
fnd_file.put_line(fnd_file.log, 'SHRD0114 ' || '1 ' || 'stmt ' || 'Deleting from GL_BC_PACKETS ...');
DELETE FROM gl_bc_packets bp
WHERE bp.ledger_id = p_ledger_id
AND bp.template_id = p_curr_temp_id
AND bp.packet_id IN (
SELECT ao.packet_id
FROM gl_bc_packet_arrival_order ao
WHERE ao.ledger_id = p_ledger_id
AND ao.affect_funds_flag = 'Y')
AND NOT EXISTS (
SELECT 'Y'
FROM gl_account_hierarchies ah
WHERE ah.ledger_id = p_ledger_id
AND ah.template_id = p_curr_temp_id
AND ah.summary_code_combination_id = bp.code_combination_id);
psa_utils.debug_other_string(g_state_level,l_full_path, ' Delete GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
select s.audsid, s.serial# into x_session_id, x_serial_id
from v$session s, v$process p
where s.paddr = p.addr
and s.audsid = USERENV('SESSIONID');
UPDATE gl_bc_packets
SET group_id = p_grp_id,
je_batch_name = p_je_batch_name
WHERE ae_header_id IN (SELECT ae_header_id
FROM xla_ae_headers
WHERE group_id = p_grp_id
and application_id = p_application_id);
psa_utils.debug_other_string(g_state_level,l_full_path, ' Update GL_BC_PACKETS -> ' || SQL%ROWCOUNT);
| Function : DEBUG_XLA_INSERT |
| Description : This Procedure inserts data from global temporary SLA tables |
| to PSA tables as shown below: |
| |
| SLA Table PSA Table |
| ======== ======== |
| xla_events_gt psa_xla_events_logs |
| xla_validation_lines_gt psa_xla_validation_lines_logs |
| xla_ae_lines_gt psa_xla_ae_lines_logs |
| xla_ae_headers_gt psa_xla_ae_headers_logs |
| |
+===========================================================================================*/
PROCEDURE debug_xla_insert ( xla_events IN xla_events_table ,
xla_validation_lines IN xla_validation_lines_table ,
xla_ae_lines IN xla_ae_lines_table ,
xla_ae_headers IN xla_ae_headers_table ,
xla_distribution_links IN xla_distribution_links_table) IS
PRAGMA autonomous_transaction;
INSERT INTO psa_xla_events_logs
VALUES xla_events(i);
INSERT INTO psa_xla_validation_lines_logs
VALUES xla_validation_lines(i);
INSERT INTO psa_xla_ae_lines_logs
VALUES xla_ae_lines(i);
INSERT INTO psa_xla_ae_headers_logs
VALUES xla_ae_headers(i);
INSERT INTO psa_xla_dist_links_logs
VALUES xla_distribution_links(i);
END debug_xla_insert ;
| It calls DEBUG_XLA_INSERT procedure to transfer data |
| from global temporary SLA tables to PSA tables. |
| |
+===========================================================================================*/
PROCEDURE debug_xla (phase IN VARCHAR2) IS
l_xla_events xla_events_table;
SELECT line_number,
entity_id,
application_id,
ledger_id,
entity_code,
source_id_int_1,
source_id_char_1,
event_id,
event_class_code,
event_status_code,
process_status_code,
reference_num_1,
reference_char_1,
on_hold_flag,
transaction_date,
budgetary_control_flag,
phase,
sysdate
BULK COLLECT INTO l_xla_events
FROM xla_events_gt ;
SELECT event_id,
entity_id,
ae_header_id,
ae_line_num,
accounting_date,
balance_type_code,
je_category_name,
budget_version_id,
ledger_id,
entered_currency_code,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
code_combination_id,
balancing_line_type,
encumbrance_type_id,
accounting_entry_status_code,
period_name,
phase,
sysdate
BULK COLLECT INTO l_xla_validation_lines
FROM xla_validation_lines_gt ;
SELECT ae_header_id,
ae_line_num,
source_distribution_id_char_1,
source_distribution_id_char_2,
source_distribution_id_char_3,
source_distribution_id_char_4,
source_distribution_id_char_5,
source_distribution_id_num_1,
source_distribution_id_num_2,
source_distribution_id_num_3,
source_distribution_id_num_4,
source_distribution_id_num_5,
source_distribution_type,
bflow_application_id,
bflow_entity_code,
bflow_source_id_num_1,
bflow_source_id_num_2,
bflow_source_id_num_3,
bflow_source_id_num_4,
bflow_source_id_char_1,
bflow_source_id_char_2,
bflow_source_id_char_3,
bflow_source_id_char_4,
bflow_distribution_type,
bflow_dist_id_num_1,
bflow_dist_id_num_2,
bflow_dist_id_num_3,
bflow_dist_id_num_4,
bflow_dist_id_num_5,
bflow_dist_id_char_1,
bflow_dist_id_char_2,
bflow_dist_id_char_3,
bflow_dist_id_char_4,
bflow_dist_id_char_5,
phase,
sysdate
BULK COLLECT INTO l_xla_ae_lines
FROM xla_ae_lines_gt ;
SELECT ae_header_id,
ledger_id,
event_id,
event_type_code,
accounting_entry_status_code ,
balance_type_code,
funds_status_code,
phase,
sysdate
BULK COLLECT INTO l_xla_ae_headers
FROM xla_ae_headers_gt ;
Select application_id ,
event_id,
ae_header_id,
ae_line_num ,
source_distribution_type ,
source_distribution_id_char_1,
source_distribution_id_char_2,
source_distribution_id_char_3,
source_distribution_id_char_4,
source_distribution_id_char_5,
source_distribution_id_num_1 ,
source_distribution_id_num_2 ,
source_distribution_id_num_3 ,
source_distribution_id_num_4 ,
source_distribution_id_num_5 ,
tax_line_ref_id ,
tax_summary_line_ref_id ,
tax_rec_nrec_dist_ref_id ,
statistical_amount,
ref_ae_header_id ,
ref_temp_line_num ,
accounting_line_code,
accounting_line_type_code,
merge_duplicate_code ,
temp_line_num ,
ref_event_id ,
line_definition_owner_code ,
line_definition_code ,
event_class_code ,
event_type_code ,
upg_batch_id ,
calculate_acctd_amts_flag,
calculate_g_l_amts_flag ,
gain_or_loss_ref,
rounding_class_code ,
document_rounding_level,
unrounded_entered_dr,
unrounded_entered_cr ,
doc_rounding_entered_amt,
doc_rounding_acctd_amt,
unrounded_accounted_cr ,
unrounded_accounted_dr ,
alloc_to_application_id,
alloc_to_entity_code,
alloc_to_source_id_num_1,
alloc_to_source_id_num_2,
alloc_to_source_id_num_3,
alloc_to_source_id_num_4,
alloc_to_source_id_char_1,
alloc_to_source_id_char_2,
alloc_to_source_id_char_3,
alloc_to_source_id_char_4,
alloc_to_distribution_type,
alloc_to_dist_id_num_1,
alloc_to_dist_id_num_2,
alloc_to_dist_id_num_3,
alloc_to_dist_id_num_4,
alloc_to_dist_id_num_5,
alloc_to_dist_id_char_1,
alloc_to_dist_id_char_2,
alloc_to_dist_id_char_3,
alloc_to_dist_id_char_4,
alloc_to_dist_id_char_5,
applied_to_application_id,
applied_to_entity_code ,
applied_to_entity_id ,
applied_to_source_id_num_1,
applied_to_source_id_num_2,
applied_to_source_id_num_3 ,
applied_to_source_id_num_4 ,
applied_to_source_id_char_1,
applied_to_source_id_char_2,
applied_to_source_id_char_3,
applied_to_source_id_char_4,
applied_to_distribution_type,
applied_to_dist_id_num_1,
applied_to_dist_id_num_2,
applied_to_dist_id_num_3,
applied_to_dist_id_num_4,
applied_to_dist_id_num_5,
applied_to_dist_id_char_1,
applied_to_dist_id_char_2,
applied_to_dist_id_char_3,
applied_to_dist_id_char_4,
applied_to_dist_id_char_5,
phase,
sysdate
BULK COLLECT INTO l_xla_distribution_links
FROM xla_distribution_links d
where exists (select 1
from xla_ae_headers h
where h.event_id IN (SELECT event_id from psa_bc_xla_events_gt)
and h.ae_header_id = d.ae_header_id)
and application_id = psa_bc_xla_pvt.g_application_id;
DEBUG_XLA_INSERT ( l_xla_events, l_xla_validation_lines, l_xla_ae_lines, l_xla_ae_headers , l_xla_distribution_links);
FOR bflow_rec IN (SELECT l.*,
e.entity_id event_entiity_id
FROM xla_ae_lines_gt l,
xla_events_gt e
WHERE l.event_id = e.event_id
AND business_method_code = 'PRIOR_ENTRY'
AND code_combination_status_code = 'INVALID'
AND NVL(bflow_prior_entry_status_code, 'N') <> 'F') LOOP
l_message := 'Related BC Accounting Missing for '||
' Event id: '||bflow_rec.event_id||
' Event Type Code: '||bflow_rec.event_type_code ||
' Distribution Id: '||bflow_rec.source_distribution_id_num_1||
' Related Application Id: '||bflow_rec.bflow_application_id||
' Related Entity Code: '||bflow_rec.bflow_entity_code||
' Related Source identifier Num 1: '||bflow_rec.bflow_source_id_num_1||
' Related Distribution Type: '||bflow_rec.bflow_distribution_type||
' Related Distribution Identifier Num 1: '||bflow_rec.bflow_dist_id_num_1;
FOR events_rec IN (SELECT *
FROM xla_events_gt e
WHERE NOT EXISTS (SELECT 1
FROM xla_ae_lines_gt l
WHERE l.event_id = e.event_id)) LOOP
l_message := 'Event '||events_rec.event_id||' is not processed.';
FOR events_rec IN (SELECT *
FROM xla_events_gt e
WHERE NOT EXISTS (SELECT 1
FROM xla_psa_bc_lines_v l
WHERE l.event_id = e.event_id)) LOOP
l_message := 'Event '||events_rec.event_id||' is not processed.';
SELECT je_source_name
FROM xla_subledgers
WHERE application_id = p_application_id;
SELECT ae_header_id,
count(*) total_cnt,
sum(decode(status_code, 'S', 1, 0)) success_cnt,
sum(decode(status_code, 'A', 1, 0)) approved_cnt,
sum(decode(status_code, 'F', 1, 0)) failed_cnt,
sum(decode(status_code, 'R', 1, 0)) rejected_cnt
FROM gl_bc_packets
WHERE packet_id = p_packet_id
GROUP BY ae_header_id;
SELECT ledger_category_code
FROM gl_ledgers
WHERE ledger_id = p_ledgerid;
SELECT ae_header_id, ledger_id, entity_id, event_id,
event_type_code, funds_status_code, accounting_entry_status_code,
balance_type_code
FROM xla_ae_headers_gt;
SELECT event_id, ae_header_id, ae_line_num
FROM xla_ae_lines_gt;
SELECT event_id, ae_header_id, ae_line_num, period_name,
accounting_entry_status_code, balancing_line_type
FROM xla_validation_lines_gt;
SELECT application_id, event_id, event_date, event_type_code,
reference_num_1
FROM xla_events_gt;
SELECT event_id, ae_header_id, ae_line_num, entity_id, ledger_id,
period_name
FROM xla_psa_bc_lines_v;
SELECT hierarchy_id, ae_header_id, ae_line_num, event_id,
status_code
FROM psa_bc_alloc_gt;
SELECT (SELECT COUNT (*)
FROM xla_events_gt) event_count,
(SELECT COUNT (DISTINCT (event_id))
FROM xla_ae_lines_gt) ae_event_count
FROM DUAL;
SELECT
'Allocation attributes are used'
FROM DUAL
WHERE EXISTS
(
SELECT
'Related transaction allocation setup exists'
FROM psa_bc_alloc_v a,
psa_bc_alloc_v b
WHERE a.ROW_ID <> b.ROW_ID
AND a.ledger_id=p_ledgerid
AND b.ledger_id=p_ledgerid
AND NVL (b.alloc_to_entity_code, 'X') = NVL (a.entity_code, 'X')
AND NVL (b.alloc_to_source_id_num_1, -99) = NVL (a.source_id_int_1, -99)
AND NVL (b.alloc_to_source_id_num_2, -99) = NVL (a.source_id_int_2, -99)
AND NVL (b.alloc_to_source_id_num_3, -99) = NVL (a.source_id_int_3, -99)
AND NVL (b.alloc_to_source_id_num_4, -99) = NVL (a.source_id_int_4, -99)
AND NVL (b.alloc_to_source_id_char_1, 'X') = NVL (a.source_id_char_1, 'X')
AND NVL (b.alloc_to_source_id_char_2, 'X') = NVL (a.source_id_char_2, 'X')
AND NVL (b.alloc_to_source_id_char_3, 'X') = NVL (a.source_id_char_3, 'X')
AND NVL (b.alloc_to_source_id_char_4, 'X') = NVL (a.source_id_char_4, 'X')
AND NVL (b.alloc_to_application_id, -99) = NVL (a.application_id, -99)
AND NVL (b.alloc_to_distribution_type, 'X') = NVL (a.source_distribution_type, 'X')
AND NVL (b.alloc_to_dist_id_num_1, -99) = NVL (a.source_distribution_id_num_1, -99)
AND NVL (b.alloc_to_dist_id_num_2, -99) = NVL (a.source_distribution_id_num_2, -99)
AND NVL (b.alloc_to_dist_id_num_3, -99) = NVL (a.source_distribution_id_num_3, -99)
AND NVL (b.alloc_to_dist_id_num_4, -99) = NVL (a.source_distribution_id_num_4, -99)
AND NVL (b.alloc_to_dist_id_num_5, -99) = NVL (a.source_distribution_id_num_5, -99)
AND NVL (b.alloc_to_dist_id_char_1, 'X') = NVL (a.source_distribution_id_char_1, 'X')
AND NVL (b.alloc_to_dist_id_char_2, 'X') = NVL (a.source_distribution_id_char_2, 'X')
AND NVL (b.alloc_to_dist_id_char_3, 'X') = NVL (a.source_distribution_id_char_3, 'X')
AND NVL (b.alloc_to_dist_id_char_4, 'X') = NVL (a.source_distribution_id_char_4, 'X')
AND NVL (b.alloc_to_dist_id_char_5, 'X') = NVL (a.source_distribution_id_char_5, 'X')
)
AND EXISTS
(
SELECT
'Parent transaction allocation setup exists'
FROM psa_bc_alloc_v a,
psa_bc_alloc_v b
WHERE a.ROW_ID = b.ROW_ID
AND a.ledger_id=p_ledgerid
AND b.ledger_id=p_ledgerid
AND NVL (b.alloc_to_entity_code, 'X') = NVL (a.entity_code, 'X')
AND NVL (b.alloc_to_source_id_num_1, -99) = NVL (a.source_id_int_1, -99)
AND NVL (b.alloc_to_source_id_num_2, -99) = NVL (a.source_id_int_2, -99)
AND NVL (b.alloc_to_source_id_num_3, -99) = NVL (a.source_id_int_3, -99)
AND NVL (b.alloc_to_source_id_num_4, -99) = NVL (a.source_id_int_4, -99)
AND NVL (b.alloc_to_source_id_char_1, 'X') = NVL (a.source_id_char_1, 'X')
AND NVL (b.alloc_to_source_id_char_2, 'X') = NVL (a.source_id_char_2, 'X')
AND NVL (b.alloc_to_source_id_char_3, 'X') = NVL (a.source_id_char_3, 'X')
AND NVL (b.alloc_to_source_id_char_4, 'X') = NVL (a.source_id_char_4, 'X')
AND NVL (b.alloc_to_application_id, -99) = NVL (a.application_id, -99)
AND NVL (b.alloc_to_distribution_type, 'X') = NVL (a.source_distribution_type, 'X')
AND NVL (b.alloc_to_dist_id_num_1, -99) = NVL (a.source_distribution_id_num_1, -99)
AND NVL (b.alloc_to_dist_id_num_2, -99) = NVL (a.source_distribution_id_num_2, -99)
AND NVL (b.alloc_to_dist_id_num_3, -99) = NVL (a.source_distribution_id_num_3, -99)
AND NVL (b.alloc_to_dist_id_num_4, -99) = NVL (a.source_distribution_id_num_4, -99)
AND NVL (b.alloc_to_dist_id_num_5, -99) = NVL (a.source_distribution_id_num_5, -99)
AND NVL (b.alloc_to_dist_id_char_1, 'X') = NVL (a.source_distribution_id_char_1, 'X')
AND NVL (b.alloc_to_dist_id_char_2, 'X') = NVL (a.source_distribution_id_char_2, 'X')
AND NVL (b.alloc_to_dist_id_char_3, 'X') = NVL (a.source_distribution_id_char_3, 'X')
AND NVL (b.alloc_to_dist_id_char_4, 'X') = NVL (a.source_distribution_id_char_4, 'X')
AND NVL (b.alloc_to_dist_id_char_5, 'X') = NVL (a.source_distribution_id_char_5, 'X')
)
;
SELECT
DISTINCT
ENTITY_CODE ,
SOURCE_ID_INT_1 ,
SOURCE_ID_INT_2 ,
SOURCE_ID_INT_3 ,
SOURCE_ID_INT_4 ,
SOURCE_ID_CHAR_1 ,
SOURCE_ID_CHAR_2 ,
SOURCE_ID_CHAR_3 ,
SOURCE_ID_CHAR_4 ,
APPLICATION_ID,
SOURCE_DISTRIBUTION_ID_NUM_1 ,
SOURCE_DISTRIBUTION_ID_NUM_2 ,
SOURCE_DISTRIBUTION_ID_NUM_3 ,
SOURCE_DISTRIBUTION_ID_NUM_4 ,
SOURCE_DISTRIBUTION_ID_NUM_5 ,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_CHAR_1 ,
SOURCE_DISTRIBUTION_ID_CHAR_2 ,
SOURCE_DISTRIBUTION_ID_CHAR_3 ,
SOURCE_DISTRIBUTION_ID_CHAR_4 ,
SOURCE_DISTRIBUTION_ID_CHAR_5
FROM psa_bc_alloc_v a
WHERE EXISTS
(
SELECT
'Accounting line is allocated to itself'
FROM psa_bc_alloc_v b
WHERE b.ROW_ID = a.ROW_ID
AND NVL(b.ALLOC_TO_ENTITY_CODE, 'X') = NVL(a.ENTITY_CODE, 'X')
AND NVL(b.ALLOC_TO_SOURCE_ID_NUM_1, -99)= NVL(a.SOURCE_ID_INT_1, -99)
AND NVL(b.ALLOC_TO_SOURCE_ID_NUM_2, -99) = NVL(a.SOURCE_ID_INT_2, -99)
AND NVL(b.ALLOC_TO_SOURCE_ID_NUM_3, -99) = NVL(a.SOURCE_ID_INT_3, -99)
AND NVL(b.ALLOC_TO_SOURCE_ID_NUM_4, -99) = NVL(a.SOURCE_ID_INT_4, -99)
AND NVL(b.ALLOC_TO_SOURCE_ID_CHAR_1, 'X') = NVL(a.SOURCE_ID_CHAR_1, 'X')
AND NVL(b.ALLOC_TO_SOURCE_ID_CHAR_2, 'X') = NVL(a.SOURCE_ID_CHAR_2, 'X')
AND NVL(b.ALLOC_TO_SOURCE_ID_CHAR_3, 'X') = NVL(a.SOURCE_ID_CHAR_3, 'X')
AND NVL(b.ALLOC_TO_SOURCE_ID_CHAR_4, 'X') = NVL(a.SOURCE_ID_CHAR_4, 'X')
AND NVL(b.alloc_to_application_id, -99) = NVL (a.application_id, -99)
AND NVL(b.alloc_to_distribution_type, 'X') = NVL(a.source_distribution_type, 'X')
AND NVL(b.alloc_to_dist_id_num_1, -99) = NVL(a.source_distribution_id_num_1, -99)
AND NVL(b.alloc_to_dist_id_num_2, -99) = NVL(a.source_distribution_id_num_2, -99)
AND NVL(b.alloc_to_dist_id_num_3, -99) = NVL(a.source_distribution_id_num_3, -99)
AND NVL(b.alloc_to_dist_id_num_4, -99) = NVL(a.source_distribution_id_num_4, -99)
AND NVL(b.alloc_to_dist_id_num_5, -99) = NVL(a.source_distribution_id_num_5, -99)
AND NVL(b.alloc_to_dist_id_char_1, 'X') = NVL(a.source_distribution_id_char_1, 'X')
AND NVL(b.alloc_to_dist_id_char_2, 'X') = NVL(a.source_distribution_id_char_2, 'X')
AND NVL(b.alloc_to_dist_id_char_3, 'X') = NVL(a.source_distribution_id_char_3, 'X')
AND NVL(b.alloc_to_dist_id_char_4, 'X') = NVL(a.source_distribution_id_char_4, 'X')
AND NVL(b.alloc_to_dist_id_char_5, 'X') = NVL(a.source_distribution_id_char_5, 'X')
)
;
SELECT
ae_header_id,
ae_line_num,
event_id
FROM psa_bc_alloc_v
WHERE NVL(ALLOC_TO_ENTITY_CODE, 'X') = NVL(p_entity_code, 'X')
AND NVL(ALLOC_TO_SOURCE_ID_NUM_1, -99) = NVL(p_source_id_int_1, -99)
AND NVL(ALLOC_TO_SOURCE_ID_NUM_2, -99) = NVL(p_source_id_int_2, -99)
AND NVL(ALLOC_TO_SOURCE_ID_NUM_3, -99) = NVL(p_source_id_int_3, -99)
AND NVL(ALLOC_TO_SOURCE_ID_NUM_4, -99) = NVL(p_source_id_int_4, -99)
AND NVL(ALLOC_TO_SOURCE_ID_CHAR_1, 'X') = NVL(p_source_id_char_1, 'X')
AND NVL(ALLOC_TO_SOURCE_ID_CHAR_2, 'X') = NVL(p_source_id_char_2, 'X')
AND NVL(ALLOC_TO_SOURCE_ID_CHAR_3, 'X') = NVL(p_source_id_char_3, 'X')
AND NVL(ALLOC_TO_SOURCE_ID_CHAR_4, 'X') = NVL(p_source_id_char_4, 'X')
AND NVL(ALLOC_TO_APPLICATION_ID, -99) = NVL(p_application_id, -99)
AND NVL(ALLOC_TO_DIST_ID_NUM_1 , -99) = NVL(p_source_dist_id_num_1 , -99)
AND NVL(ALLOC_TO_DIST_ID_NUM_2 , -99) = NVL(p_source_dist_id_num_2 , -99)
AND NVL(ALLOC_TO_DIST_ID_NUM_3 , -99) = NVL(p_source_dist_id_num_3 , -99)
AND NVL(ALLOC_TO_DIST_ID_NUM_4 , -99) = NVL(p_source_dist_id_num_4 , -99)
AND NVL(ALLOC_TO_DIST_ID_NUM_5 , -99) = NVL(p_source_dist_id_num_5 , -99)
AND NVL(ALLOC_TO_DISTRIBUTION_TYPE, 'X') = NVL(p_source_dist_type, 'X')
AND NVL(ALLOC_TO_DIST_ID_CHAR_1 , 'X') = NVL(p_source_dist_id_char_1 , 'X')
AND NVL(ALLOC_TO_DIST_ID_CHAR_2 , 'X') = NVL(p_source_dist_id_char_2 , 'X')
AND NVL(ALLOC_TO_DIST_ID_CHAR_3 , 'X') = NVL(p_source_dist_id_char_3 , 'X')
AND NVL(ALLOC_TO_DIST_ID_CHAR_4 , 'X') = NVL(p_source_dist_id_char_4 , 'X')
AND NVL(ALLOC_TO_DIST_ID_CHAR_5 , 'X') = NVL(p_source_dist_id_char_5 , 'X')
;
SELECT
DISTINCT(hierarchy_id)
FROM psa_bc_alloc_gt;
SELECT 'Funds Failure for hierarchy'
FROM DUAL
WHERE EXISTS
(SELECT 'X' FROM GL_BC_PACKETS
WHERE (ae_header_id, ae_line_num, event_id)
IN (select ae_header_id, ae_line_num, event_id
from psa_bc_alloc_gt
where hierarchy_id = p_hierarchy_id
and status_code = 'P'
)
AND status_code IN ('F', 'R')
AND session_id = p_session_id
AND serial_id = p_serial_id) ;
SELECT DECODE (COUNT (*),
COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'P', 1)), DECODE
(SIGN (COUNT (DECODE (bp.result_code,
'P20', 1,
'P22', 1,
'P25', 1,
'P27', 1,
'P31', 1,
'P35', 1,
'P36', 1,
'P37', 1,
'P38', 1,
'P39', 1
)
)
),
0, 'S',
1, 'A'
),
COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'F', 1)), 'F',
DECODE (DECODE (psa_bc_xla_pvt.g_bc_mode,
'C', 'Y',
'M', 'N',
'P', 'Y',
'N'
),
'Y', 'P',
'F'
)
)
FROM gl_bc_packets bp
WHERE bp.packet_id = p_packet_id AND bp.template_id IS NULL;
SELECT result_code, ae_header_id, ae_line_num
FROM gl_bc_packets
WHERE packet_id = p_packet_id;
for acc_error in ( select document_reference , encoded_message
from psa_bc_accounting_errors b
where event_id in (select event_id from xla_events_gt))
loop
fnd_file.put_line(fnd_file.log , ' document_referece ' || acc_error.document_reference);
( select e.entity_id,g.event_id,g.source_id_int_2,e.transaction_number
from xla_transaction_entities_upg e , xla_events_gt g
where g.entity_id = e.entity_id
and g.event_id not in (select event_id from xla_ae_lines_gt) )
loop
fnd_file.put_line(fnd_file.log , ' BC_Event_id ' || missing_entity.event_id || ' Transaction Number ' || missing_entity.transaction_number || ' Distribution id ' || missing_entity.source_id_int_2 );
FOR check_prepay_rec IN (SELECT event_class_code
FROM xla_events_gt
WHERE event_class_code = 'PREPAYMENT APPLICATIONS') LOOP
psa_utils.debug_other_string(g_state_level,l_full_path, 'Resetting l_alloc_used from Y to N');
INSERT INTO psa_bc_alloc_gt (
hierarchy_id,
ae_header_id,
ae_line_num,
event_id,
status_code
) VALUES (
l_parent_cnt,
l_child_trx.ae_header_id,
l_child_trx.ae_line_num,
l_child_trx.event_id,
'U'
);
SELECT
COUNT(DISTINCT(event_id))
INTO l_alloc_event_cnt
FROM psa_bc_alloc_gt
WHERE hierarchy_id = h.hierarchy_id;
SELECT
COUNT(DISTINCT(xv.event_id))
INTO l_xla_event_cnt
FROM xla_psa_bc_lines_v xv
WHERE xv.event_id IN
(
(
SELECT
pa1.event_id
FROM psa_bc_alloc_gt pa1
WHERE pa1.hierarchy_id = h.hierarchy_id
)
MINUS
(
SELECT
pa2.event_id
FROM psa_bc_alloc_gt pa2
WHERE pa2.event_id = xv.event_id
AND pa2.status_code = 'F'
)
);
UPDATE
psa_bc_alloc_gt
SET status_code = 'P'
WHERE hierarchy_id = h.hierarchy_id;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated '||sql%rowcount||
' rows.');
UPDATE
psa_bc_alloc_gt
SET status_code = 'F'
WHERE hierarchy_id = h.hierarchy_id;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated '||sql%rowcount||
' rows.');
SELECT NULL, -- Packet_id is initially NULL. Populated later in the code
p_ledgerid, -- Since XLA view does not provide this column, use parameter
nvl(l_je_source_name, 'Manual'),
xv.je_category_name,
xv.code_combination_id,
xv.balance_type_code,
xv.period_name,
ps.period_year,
ps.period_num,
ps.quarter_num,
xv.entered_currency_code,
-- decode(psa_bc_xla_pvt.g_bc_mode, 'C', 'C', 'P'), Bug 6452856.
decode(psa_bc_xla_pvt.g_bc_mode, 'C', 'C','M', 'C', 'P'),
sysdate,
g_user_id,
xv.budget_version_id, -- BUDGET_VERSION_ID
xv.encumbrance_type_id,
NULL, -- TEMPLATE_ID
xv.entered_dr,
xv.entered_cr,
xv.accounted_dr,
xv.accounted_cr,
NULL, -- USSGL_TRANSACTION_CODE
NULL, -- ORIGINATING_ROWID
NULL, -- ACCOUNT_SEGMENT_VALUE
NULL, -- AUTOMATIC_ENCUMBRANCE_FLAG
NULL, -- FUNDING_BUDGET_VERSION_ID
NULL, -- FUNDS_CHECK_LEVEL_CODE
NULL, -- AMOUNT_TYPE
NULL, -- BOUNDARY_CODE
NULL, -- TOLERANCE_PERCENTAGE
NULL, -- TOLERANCE_AMOUNT
NULL, -- OVERRIDE_AMOUNT
NULL, -- DR_CR_CODE
NULL, -- ACCOUNT_TYPE
NULL, -- ACCOUNT_CATEGORY_CODE
NULL, -- EFFECT_ON_FUNDS_CODE
NULL, -- RESULT_CODE
NULL, -- BUDGET_POSTED_BALANCE
NULL, -- ACTUAL_POSTED_BALANCE
NULL, -- ENCUMBRANCE_POSTED_BALANCE
NULL, -- BUDGET_APPROVED_BALANCE
NULL, -- ACTUAL_APPROVED_BALANCE
NULL, -- ENCUMBRANCE_APPROVED_BALANCE
NULL, -- BUDGET_PENDING_BALANCE
NULL, -- ACTUAL_PENDING_BALANCE
NULL, -- ENCUMBRANCE_PENDING_BALANCE
NULL, -- REFERENCE1
NULL, -- REFERENCE2
NULL, -- REFERENCE3
NULL, -- REFERENCE4
NULL, -- REFERENCE5
NULL, -- JE_BATCH_NAME
-1, -- JE_BATCH_ID
NULL, -- JE_HEADER_ID
NULL, -- JE_LINE_NUM
NULL, -- JE_LINE_DESCRIPTION
NULL, -- REFERENCE6
NULL, -- REFERENCE7
NULL, -- REFERENCE8
NULL, -- REFERENCE9
NULL, -- REFERENCE10
NULL, -- REFERENCE11
NULL, -- REFERENCE12
NULL, -- REFERENCE13
NULL, -- REFERENCE14
NULL, -- REFERENCE15
NULL, -- REQUEST_ID
NULL, -- USSGL_PARENT_ID
NULL, -- USSGL_LINK_TO_PARENT_ID
xv.event_id,
xv.ae_header_id,
xv.ae_line_num,
NULL, -- BC_DATE
xv.source_distribution_type,
xv.source_distribution_id_char_1,
xv.source_distribution_id_char_2,
xv.source_distribution_id_char_3,
xv.source_distribution_id_char_4,
xv.source_distribution_id_char_5,
xv.source_distribution_id_num_1,
xv.source_distribution_id_num_2,
xv.source_distribution_id_num_3,
xv.source_distribution_id_num_4,
xv.source_distribution_id_num_5,
l_session_id,
l_serial_id,
psa_bc_xla_pvt.g_application_id,
xv.entity_id,
NULL -- GROUP_ID
BULK COLLECT INTO l_bc_pkts
FROM xla_psa_bc_lines_v xv,
gl_period_statuses ps
WHERE ps.ledger_id = p_ledgerid and
xv.period_name = ps.period_name and
ps.application_id = 101 and
-- Bug 4778812 start
(
(l_alloc_used = 'Y' and
xv.event_id IN (
SELECT event_id
FROM psa_bc_alloc_gt
WHERE status_code = 'P')
)
OR
(l_alloc_used = 'N')
)
-- Bug 4778812 end
ORDER BY xv.entity_id, (nvl(entered_dr, 0)-nvl(entered_cr, 0)), source_distribution_id_num_1;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Update funds_status_code '||
'of xla_ae_headers_gt ');
SELECT nvl(min('A'), 'S') into l_xla_hdr_status
FROM gl_bc_packets
WHERE packet_id = l_packets(i) and
ae_header_id = y.ae_header_id and
result_code IN ('P20', 'P22', 'P25', 'P27', 'P31', 'P35', 'P36', 'P37',
'P38', 'P39');
UPDATE xla_ae_headers_gt
SET funds_status_code = l_xla_hdr_status
WHERE ae_header_id = y.ae_header_id and
ledger_id = p_ledgerid;
', Status updated to '||l_xla_hdr_status);
UPDATE xla_ae_headers_gt
SET funds_status_code = l_ret_code
WHERE ae_header_id IN (SELECT ae_header_id
FROM gl_bc_packets
WHERE packet_id = l_packets(i)) and
ledger_id = p_ledgerid;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated funds_status_code of '||
sql%rowcount||' rows successfully. ');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Update funds_status_code '||
'of xla_validation_lines_gt');
UPDATE xla_validation_lines_gt vl
SET vl.funds_status_code = l_result_code_tbl(x)
WHERE vl.ae_header_id = l_xla_hdr_tbl(x) AND
vl.ae_line_num = l_xla_line_tbl(x) AND
vl.ledger_id = p_ledgerid;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated '||sql%rowcount||
' rows.');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Update result_code '||
'of psa_bc_xla_events_gt');
UPDATE psa_bc_xla_events_gt eg
SET result_code = (SELECT decode(min(funds_status_code),
'T', 'FATAL',
'S', 'SUCCESS',
'A', 'ADVISORY',
'F', 'FAIL',
'P', 'PARTIAL',
'XLA_ERROR')
FROM xla_ae_headers_gt hg
WHERE hg.event_id = eg.event_id)
where eg.event_id in (select event_id from xla_ae_headers_gt);
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated valid funds check '||sql%rowcount||
' rows.');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated '||
' PSA_BC_XLA_PVT.G_PACKET_ID to '||PSA_BC_XLA_PVT.G_PACKET_ID);
/* We need to update the related events stauses which were NOT picked up by funds checker.
e.g. PA BURDEN lines failed XLA validation but corresponding PO RAW passed XLA validation
or PO RAW line failed XLA validation but corresponding PA BURDEN lines passed XLA validation.
*/
-- Update Funds_Status_Code column in XLA_AE_HEADERS_GT
UPDATE xla_ae_headers_gt
SET funds_status_code = 'F'
WHERE event_id IN (
SELECT event_id
FROM psa_bc_alloc_gt
WHERE status_code <> 'P');
psa_utils.debug_other_string(g_state_level,l_full_path, ' BCTRL -> Updated '||sql%rowcount||' rows of XLA_AE_HEADERS_GT with fail status.');
UPDATE xla_validation_lines_gt vl
SET vl.funds_status_code = 'F76'
WHERE event_id IN (
SELECT event_id
FROM psa_bc_alloc_gt
WHERE status_code <> 'P');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated ' ||sql%rowcount||' rows of XLA_VALIDATION_LINES_GT with F76 status code.');
UPDATE gl_bc_packets
SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
'M', 'F',
'R', 'R',
'P', 'R')
,result_code = 'F77'
WHERE (ae_header_id, ae_line_num, event_id)
IN (SELECT ae_header_id, ae_line_num, event_id
FROM psa_bc_alloc_gt
WHERE hierarchy_id = h.hierarchy_id
)
AND status_code NOT IN ('F', 'R')
AND session_id = l_session_id
AND serial_id = l_serial_id;
psa_utils.debug_other_string(g_state_level, l_full_path, 'BCTRL -> Updated '
||sql%rowcount||' rows of GL_BC_PACKETS with F77 status.');
UPDATE xla_ae_headers_gt
SET funds_status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
'M', 'F',
'R', 'F',
'P', 'F')
WHERE ae_header_id IN (SELECT ae_header_id
FROM gl_bc_packets
WHERE result_code = 'F77'
AND session_id = l_session_id
AND serial_id = l_serial_id) and
ledger_id = p_ledgerid;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated funds_status_code of '||
sql%rowcount||' rows successfully of XLA_AE_HEADERS_GT. ');
UPDATE xla_validation_lines_gt vl
SET vl.funds_status_code = 'F77'
WHERE vl.ae_header_id IN (SELECT ae_header_id
FROM gl_bc_packets
WHERE result_code = 'F77'
AND session_id = l_session_id
AND serial_id = l_serial_id) and
vl.ledger_id = p_ledgerid;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated '||sql%rowcount||
' rows of XLA_VALIDATION_LINES_GT with F77 status.');
DELETE FROM psa_bc_alloc_gt; --For bug 7607496
psa_utils.debug_other_string(g_state_level,l_full_path, ' Deleted Rows -> ' || SQL%ROWCOUNT);
select count(*) into l_var_1
from xla_psa_bc_lines_v;
SELECT 'Successful event exists in the current packet'
FROM gl_bc_packets
WHERE event_id IN (SELECT event_id
FROM psa_bc_xla_events_gt
)
AND application_id = PSA_BC_XLA_PVT.g_application_id
AND status_code = 'A';
UPDATE gl_bc_packets
SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
'M', 'F',
'R', 'R',
'P', 'R')
,result_code = 'F81'
WHERE event_id = p_failed_evnt_array(i)
AND application_id = PSA_BC_XLA_PVT.g_application_id
AND ledger_id = p_failed_ldgr_array(i)
AND status_code NOT IN ('F', 'R');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f81_cnt||
' rows to F81 status.');
UPDATE gl_bc_packets
SET status_code = decode(PSA_BC_XLA_PVT.G_BC_MODE, 'C', 'F',
'M', 'F',
'R', 'R',
'P', 'R')
,result_code = 'F82'
WHERE event_id = p_failed_evnt_array(j)
AND application_id = PSA_BC_XLA_PVT.g_application_id
AND status_code NOT IN ('F', 'R');
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKETS '||l_f82_cnt||
' rows to F82 status.');
UPDATE psa_bc_xla_events_gt
SET result_code = 'XLA_ERROR'
WHERE event_id = p_failed_evnt_array(k)
AND result_code <> 'XLA_ERROR';
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated PSA_BC_XLA_EVENTS_GT '||sql%rowcount||
' rows to XLA_ERROR status.');
UPDATE gl_bc_packet_arrival_order
SET affect_funds_flag = 'N'
WHERE affect_funds_flag = 'Y'
AND packet_id IN ( SELECT packet_id
FROM gl_bc_packets bc
WHERE event_id IN ( SELECT event_id
FROM psa_bc_xla_events_gt
)
AND application_id = PSA_BC_XLA_PVT.g_application_id
AND result_code IN ('F81', 'F82')
) ;
psa_utils.debug_other_string(g_state_level, l_full_path, ' BCTRL -> Updated GL_BC_PACKET_ARRIVAL_ORDER '||sql%rowcount||
' rows.');