[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_XLA_EVENT_DATA(p_data_set_id1 IN NUMBER,
p_data_set_id2 IN NUMBER,
p_calling_module IN VARCHAR2,
p_events_to_delete IN OUT NOCOPY VARCHAR2);
l_events_to_delete Varchar2(1);
select decode(predefined_flag,
'N', decode(posted_flag,
'N', 'PA',
'EXT'),
acct_source_code)
into l_acct_source
from pa_transaction_sources
where transaction_source = p_transaction_source;
select org_id, set_of_books_id, def_supplier_cost_cr_ccid
into g_org_id, g_ledger_id, g_imp_cr_ccid
from pa_implementations ;
select to_number(org_information2)
into g_legal_entity_id
from hr_organization_information
where organization_id = g_org_id
and org_information_context = 'Operating Unit Information';
Insert into psa_bc_xla_events_gt(event_id,result_code)
values(t_event_id(i),'XLA_ERROR');
pa_debug.g_err_stage:= 'Create Events: Delete PL/sql table';
tt_source_info.DELETE;
t_event_type_code.DELETE;
t_event_date.DELETE;
t_transaction_date.DELETE;
tt_security_info.DELETE;
t_event_id.DELETE;
t_source_id_int_1.DELETE;
t_entity_id.delete;
t_event_date.delete;
t_txn_type.delete;
t_event_id.delete;
t_event_type_code.delete;
g_tab_module.delete;
g_tab_event_type_code.delete;
select expenditure_item_id,
cc_dist_line_id,
adjusted_expenditure_item_id,
transferred_from_exp_item_id,
historical_flag,
parent_dist_line_id,
dist_line_id_reversed,
line_type,
gl_date,
orig_historic,
orig_dist_line_id,
orig_acct_source
from (
select cdl.expenditure_item_id,
cdl.cc_dist_line_id cc_dist_line_id,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
NVL(exp.historical_flag, 'Y') historical_flag,
NULL parent_dist_line_id,
NULL dist_line_id_reversed,
cdl.line_type,
trunc(cdl.gl_date) gl_date,
NULL orig_historic,
NULL orig_dist_line_id,
NULL orig_acct_source
from pa_cc_dist_lines_all cdl,
pa_expenditure_items_all exp
where exp.expenditure_item_id = cdl.expenditure_item_id
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and cdl.dist_line_id_reversed is null
and exp.adjusted_expenditure_item_id is null
UNION ALL
select cdl.expenditure_item_id,
cdl.cc_dist_line_id cc_dist_line_id,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
NVL(exp.historical_flag, 'Y') historical_flag,
cdl.dist_line_id_reversed parent_dist_line_id,
NULL dist_line_id_reversed,
cdl.line_type line_type,
trunc(cdl.gl_date) gl_date,
nvl(pe.historical_flag, 'Y') orig_historic,
cd.cc_dist_line_id orig_dist_line_id,
cd.acct_source_code orig_acct_source
from pa_cc_dist_lines_all cdl,
pa_expenditure_items_all exp,
pa_expenditure_items_all pe,
pa_cc_dist_lines_all cd
where exp.expenditure_item_id = cdl.expenditure_item_id
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and exp.adjusted_expenditure_item_id is not null
and exp.adjusted_expenditure_item_id = pe.expenditure_item_id
and pe.expenditure_item_id = cd.expenditure_item_id
and cdl.dist_line_id_reversed = cd.cc_dist_line_id
UNION ALL
select cdl.expenditure_item_id,
cdl.cc_dist_line_id cc_dist_line_id,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
NVL(exp.historical_flag, 'Y') historical_flag,
NULL parent_dist_id,
cdl.dist_line_id_reversed dist_line_id_reversed,
cdl.line_type line_type,
trunc(cdl.gl_date) gl_date,
NULL orig_historic,
cd.cc_dist_line_id orig_dist_line_id,
cd.acct_source_code orig_acct_source
from pa_cc_dist_lines_all cdl,
pa_expenditure_items_all exp,
pa_cc_dist_lines_all cd
where exp.expenditure_item_id = cdl.expenditure_item_id
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and cdl.dist_line_id_reversed is not null
and cdl.expenditure_item_id = cd.expenditure_item_id
and cdl.dist_line_id_reversed = cd.cc_dist_line_id)
order by expenditure_item_id, cc_dist_line_id;
select expenditure_item_id,
line_num,
adjusted_expenditure_item_id,
transferred_from_exp_item_id,
transaction_source,
historical_flag,
parent_line_num,
system_linkage_function,
line_num_reversed,
line_type,
gl_date,
document_payment_id,
document_header_id,
document_distribution_id,
orig_historic,
orig_line_num,
orig_acct_source,
orig_tsc,
system_reference5,
cr_code_combination_id,
pts_source,
orig_cr_ccid
from (
select cdl.expenditure_item_id,
cdl.line_num,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
exp.transaction_source,
NVL(exp.historical_flag, 'Y') historical_flag,
cdl.parent_line_num,
decode(cdl.line_type,
'C', 'TBC',
'D', 'TBC',
exp.system_linkage_function) system_linkage_function,
cdl.line_num_reversed,
decode(cdl.line_type, 'R', 'R', 'B') line_type,
trunc(cdl.gl_date) gl_date,
exp.document_payment_id,
exp.document_header_id,
exp.document_distribution_id,
NVL(exp.historical_flag, 'Y') orig_historic,
NULL orig_line_num,
NULL orig_acct_source,
NULL orig_tsc,
cdl.system_reference5,
cdl.cr_code_combination_id,
decode(pts.predefined_flag,
NULL, 'PA',
'N', decode(pts.posted_flag,
'N', 'PA', 'EXT'),
pts.acct_source_code) pts_source,
cdl.cr_code_combination_id orig_cr_ccid
from pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all exp,
pa_transaction_sources pts
where exp.expenditure_item_id = cdl.expenditure_item_id
and exp.transaction_source = pts.transaction_source(+)
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and cdl.line_num_reversed is null
and ( exp.adjusted_expenditure_item_id is null or ( cdl.parent_line_num is null
and NVL(exp.historical_flag, 'Y') = 'Y'
)
)
UNION ALL
select cdl.expenditure_item_id,
cdl.line_num,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
exp.transaction_source,
NVL(exp.historical_flag, 'Y') historical_flag,
cdl.parent_line_num,
decode(cdl.line_type,
'C', 'TBC',
'D', 'TBC',
exp.system_linkage_function) system_linkage_function,
cdl.line_num_reversed,
decode(cdl.line_type, 'R', 'R', 'B') line_type,
trunc(cdl.gl_date) gl_date,
exp.document_payment_id ,
exp.document_header_id ,
exp.document_distribution_id ,
nvl(pe.historical_flag, 'Y') orig_historic,
cd.line_num orig_line_num,
cd.acct_source_code orig_acct_source,
cd.transfer_status_code orig_tsc,
cdl.system_reference5,
cdl.cr_code_combination_id,
decode(pts.predefined_flag,
NULL, 'PA',
'N', decode(pts.posted_flag,
'N', 'PA',
'EXT'),
pts.acct_source_code) pts_source,
cd.cr_code_combination_id orig_cr_ccid
from pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all exp,
pa_expenditure_items_all pe,
pa_cost_distribution_lines_all cd,
pa_transaction_sources pts
where exp.expenditure_item_id = cdl.expenditure_item_id
and pe.transaction_source = pts.transaction_source(+)
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and exp.adjusted_expenditure_item_id is not null
and exp.adjusted_expenditure_item_id = pe.expenditure_item_id
and pe.expenditure_item_id = cd.expenditure_item_id
and cdl.parent_line_num = cd.line_num
UNION ALL
select cdl.expenditure_item_id,
cdl.line_num,
exp.adjusted_expenditure_item_id,
exp.transferred_from_exp_item_id,
exp.transaction_source,
NVL(exp.historical_flag, 'Y') historical_flag,
cdl.parent_line_num,
decode(cdl.line_type,
'C', 'TBC',
'D', 'TBC',
exp.system_linkage_function) system_linkage_function,
cdl.line_num_reversed,
decode(cdl.line_type, 'R', 'R', 'B') line_type,
trunc(cdl.gl_date) gl_date,
exp.document_payment_id ,
exp.document_header_id ,
exp.document_distribution_id ,
NULL orig_historic,
cd.line_num orig_line_num,
cd.acct_source_code orig_acct_source,
cd.transfer_status_code orig_tsc,
cdl.system_reference5,
cdl.cr_code_combination_id,
decode(pts.predefined_flag,
NULL, 'PA',
'N', decode(pts.posted_flag,
'N', 'PA',
'EXT'),
pts.acct_source_code) pts_source,
cd.cr_code_combination_id orig_cr_ccid
from pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all exp,
pa_cost_distribution_lines_all cd,
pa_transaction_sources pts
where exp.expenditure_item_id = cdl.expenditure_item_id
and exp.transaction_source = pts.transaction_source(+)
and cdl.request_id = g_data_set_id
and cdl.transfer_status_code = 'X'
and cdl.line_num_reversed is not null
and cdl.expenditure_item_id = cd.expenditure_item_id
and cdl.line_num_reversed = cd.line_num)
order by expenditure_item_id, line_num;
update pa_cost_distribution_lines_all
set acct_event_id = decode(t_tsc(i), 'R', NULL, t_event_id(i)),
acct_source_code = decode(t_tsc(i), 'R', NULL, t_acct_source(i)),
transfer_status_code = decode(t_tsc(i), 'G', 'G', 'R', 'R', 'A'),
transfer_rejection_reason = decode(t_tsc(i),
'R',( SELECT meaning --bug 6033420
FROM pa_lookups
WHERE lookup_code = 'PA_XLA_NOT_FINAL_ACCT'
AND lookup_type = 'TRANSFER REJECTION REASON'),
transfer_rejection_reason),
transferred_date = trunc(sysdate),
system_reference5 = decode(t_tsc(i), 'R', system_reference5,
nvl(t_sr5(i), system_reference5)),
cr_code_combination_id = decode(t_tsc(i), 'R', cr_code_combination_id,
nvl(t_cr_ccid(i), cr_code_combination_id))
where expenditure_item_id = t_entity_id(i)
and transfer_status_code = 'X'
and TRUNC(gl_date) = t_event_date(i) --Bug 5081153
and line_num = t_line_num(i)
and line_type = decode(t_line_type(i), 'B', line_type, t_line_type(i))
and request_id = g_data_set_id;
update pa_cc_dist_lines_all
set acct_event_id = t_event_id(i),
acct_source_code = t_acct_source(i),
transferred_date = trunc(sysdate),
transfer_status_code = decode(t_tsc(i), 'G', 'G', 'A')
where expenditure_item_id = t_entity_id(i)
and cc_dist_line_id = t_cc_dist_line_id(i)
and transfer_status_code = 'X'
and request_id = g_data_set_id
and line_type = t_line_type(i)
and gl_date = t_event_date(i);
update pa_cost_distribution_lines_all
set transfer_status_code = 'R',
transfer_rejection_reason = 'Create Events API did not pick this line'
where transfer_status_code = 'X'
and request_id = g_data_set_id;
update pa_cc_dist_lines_all
set transfer_status_code = 'R',
transfer_rejection_code = 'Create Events API did not pick this line'
where transfer_status_code = 'X'
and request_id = g_data_set_id;
transferred_from_exp_item_id as reversal of already adjusted EI will have both fields updated
*/
-- New item as a result of adjustment interfacing now
t_acct_source(i) := 'PA';
Select budget_type_code,budget_status_code,project_id
into l_budget_type_code,p_curr_budget_status_code,l_project_id
from pa_budget_versions
where budget_version_id = p_budget_version_id;
Select budget_version_id
into p_old_budget_version_id
from pa_budget_versions
where project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'B'
and current_flag = 'Y';
Select MAX(budget_version_id)
into p_old_budget_version_id
from pa_budget_versions
where project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'B'
and current_flag = 'N'
and budget_version_id <> p_budget_version_id;
Procedure Delete_xla_event_data(p_data_set_id1 IN NUMBER,
p_data_set_id2 IN NUMBER,
p_calling_module IN VARCHAR2,
p_events_to_delete IN OUT NOCOPY VARCHAR2)
Is
Begin
IF g_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Delete_xla_event_data'||'Module:['||p_calling_module
||']packet_id or last baselined version['||p_data_set_id1
||']draft budget version['||p_data_set_id2||']';
pa_debug.g_err_stage:= 'Delete_xla_event_data'||':Collect data for deleting reversing events';
select txn.source_application_id,
txn.application_id,
txn.legal_entity_id,
txn.ledger_id,
txn.entity_code entity_type_code,
txn.transaction_number,
txn.source_id_int_1,
txn.source_id_int_2,
txn.source_id_int_3,
txn.source_id_int_4,
txn.source_id_char_1,
txn.source_id_char_2,
txn.source_id_char_3,
txn.source_id_char_4,
txn.security_id_int_1,
evt.event_id
BULK COLLECT INTO
t_source_application_id,
t_application_id,
t_legal_entity_id,
t_ledger_id,
t_entity_type_code,
t_transaction_number,
t_source_id_int_1,
t_source_id_int_2,
t_source_id_int_3,
t_source_id_int_4,
t_source_id_char_1,
t_source_id_char_2,
t_source_id_char_3,
t_source_id_char_4,
t_security_org_id,
t_source_event_id
from xla_events evt,
xla_transaction_entities txn
where evt.entity_id = txn.entity_id
and evt.event_id in
(Select distinct bc_rev_event_id
from pa_budget_lines
where budget_version_id = p_data_set_id1
and bc_rev_event_id is not null
and p_data_set_id1 is not null
UNION ALL
Select distinct bc_event_id
from pa_budget_lines
where budget_version_id = p_data_set_id2
and bc_event_id is not null
and p_data_set_id2 is not null
);
select txn.source_application_id,
txn.application_id,
txn.legal_entity_id,
txn.ledger_id,
txn.entity_code entity_type_code,
txn.transaction_number,
txn.source_id_int_1,
txn.source_id_int_2,
txn.source_id_int_3,
txn.source_id_int_4,
txn.source_id_char_1,
txn.source_id_char_2,
txn.source_id_char_3,
txn.source_id_char_4,
txn.security_id_int_1,
evt.event_id
BULK COLLECT INTO
t_source_application_id,
t_application_id,
t_legal_entity_id,
t_ledger_id,
t_entity_type_code,
t_transaction_number,
t_source_id_int_1,
t_source_id_int_2,
t_source_id_int_3,
t_source_id_int_4,
t_source_id_char_1,
t_source_id_char_2,
t_source_id_char_3,
t_source_id_char_4,
t_security_org_id,
t_source_event_id
from xla_events evt,
xla_transaction_entities txn
where evt.entity_id = txn.entity_id
and evt.event_id in
(select distinct pbc1.bc_event_id
from pa_bc_packets pbc1
where pbc1.packet_id <> p_data_set_id1
and (pbc1.document_header_id,
pbc1.document_distribution_id,
pbc1.document_type) in
(select pbc2.document_header_id,
pbc2.document_distribution_id,
pbc2.document_type
from pa_bc_packets pbc2
where pbc2.packet_id = p_data_set_id1
and pbc2.status_code = 'I'
and pbc2.ext_bdgt_flag = 'Y')
and pbc1.status_code in ('S','F','T','R')
and pbc1.bc_event_id is not null)
and evt.event_status_code <> 'P';
p_events_to_delete := 'Y';
pa_debug.g_err_stage:= 'Delete_xla_event_data:'||t_source_event_id.COUNT||' event(s) to be deleted';
pa_debug.g_err_stage:= 'Delete_xla_event_data:'||'Call xla_events_pub_pkg.delete_event';
XLA_EVENTS_PUB_PKG.DELETE_EVENT(p_event_source_info => tt_source_info(i)
,p_event_id => t_source_event_id(i)
,p_valuation_method => NULL
,p_security_context => tt_security_info(i));
pa_debug.g_err_stage:= 'Delete_xla_event_data:'||'initalize pl/sql table';
t_source_application_id.DELETE;
t_application_id.DELETE;
t_legal_entity_id.DELETE;
t_ledger_id.DELETE;
t_entity_type_code.DELETE;
t_transaction_number.DELETE;
t_source_id_int_1.DELETE;
t_source_id_int_2.DELETE;
t_source_id_int_3.DELETE;
t_source_id_int_4.DELETE;
t_source_id_char_1.DELETE;
t_source_id_char_2.DELETE;
t_source_id_char_3.DELETE;
t_source_id_char_4.DELETE;
t_security_org_id.DELETE;
t_source_event_id.DELETE;
pa_debug.g_err_stage:= 'Delete_xla_event_data: No event to delete';
End Delete_xla_event_data;
pa_debug.g_err_stage:= 'Reset_event_id'||':Calling DELETE_XLA_EVENT_DATA';
l_events_to_delete := 'N';
DELETE_XLA_EVENT_DATA(p_data_set_id1 => p_old_budget_version_id,
p_data_set_id2 => p_budget_version_id,
p_calling_module => 'BUDGETS',
p_events_to_delete => l_events_to_delete);
DELETE_XLA_EVENT_DATA(p_data_set_id1 => p_old_budget_version_id,
p_data_set_id2 => NULL,
p_calling_module => 'BUDGETS',
p_events_to_delete => l_events_to_delete);
If l_events_to_delete = 'Y' then
If p_curr_budget_status_code in ('S','W') then
-- ----------------------------------------------------------------------------------------- +
-- Update draft budget's event_id to null
IF g_debug_mode = 'Y' THEN
pa_debug.g_err_stage:= 'Reset_event_id'||': Reset bc_event_id for p_budget_version_id:'||p_budget_version_id;
Update pa_budget_lines
set bc_event_id = NULL
where budget_version_id = p_budget_version_id;
Update pa_budget_lines
set bc_rev_event_id = NULL
where budget_version_id = p_old_budget_version_id;
End If; -- If l_events_to_delete = 'Y' then
' :Event count being inserted'||p_Source_Id_Int1.COUNT;
insert into xla_events_gt ( LINE_NUMBER,
ENTITY_ID,
APPLICATION_ID,
LEDGER_ID,
LEGAL_ENTITY_ID,
ENTITY_CODE,
TRANSACTION_NUMBER,
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,
EVENT_ID,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
EVENT_NUMBER,
EVENT_DATE,
EVENT_STATUS_CODE,
PROCESS_STATUS_CODE,
EVENT_CREATED_BY,
REFERENCE_NUM_1,
REFERENCE_NUM_2,
REFERENCE_NUM_3,
REFERENCE_NUM_4,
REFERENCE_CHAR_1,
REFERENCE_CHAR_2,
REFERENCE_CHAR_3,
REFERENCE_CHAR_4,
REFERENCE_DATE_1,
REFERENCE_DATE_2,
REFERENCE_DATE_3,
REFERENCE_DATE_4,
VALUATION_METHOD,
SECURITY_ID_INT_1,
SECURITY_ID_INT_2,
SECURITY_ID_INT_3,
SECURITY_ID_CHAR_1,
SECURITY_ID_CHAR_2,
SECURITY_ID_CHAR_3,
ON_HOLD_FLAG,
TRANSACTION_DATE,
BUDGETARY_CONTROL_FLAG)
values (NULL, -- line number
NULL, -- entity_id
l_application_id, -- application id
g_ledger_id, -- ledger id (set in init)
NULL, -- legal entity id
g_entity_code, -- entity_code (set in init)
NULL, -- transaction num
p_Source_Id_Int1(i), -- source_id_int_1
NULL, -- source_id_int_2
NULL, -- source_id_int_3
NULL, -- source_id_int_4
NULL, -- source_id_char_1
NULL, -- source_id_char_2
NULL, -- source_id_char_3
NULL, -- source_id_char_4
NULL, -- event_id
NULL, -- EVENT_CLASS_CODE
g_event_type_code, -- EVENT_TYPE_CODE
NULL, -- EVENT_NUMBER
p_event_date(i), -- EVENT_DATE
l_event_status_code, -- EVENT_STATUS_CODE
NULL, -- PROCESS_STATUS_CODE
l_user_id, -- EVENT_CREATED_BY
NULL, -- REFERENCE_NUM_1
NULL, -- REFERENCE_NUM_2
NULL, -- REFERENCE_NUM_3
NULL, -- REFERENCE_NUM_4
NULL, -- REFERENCE_CHAR_1
NULL, -- REFERENCE_CHAR_2
NULL, -- REFERENCE_CHAR_3
NULL, -- REFERENCE_CHAR_4
NULL, -- REFERENCE_DATE_1
NULL, -- REFERENCE_DATE_2
NULL, -- REFERENCE_DATE_3
NULL, -- REFERENCE_DATE_4
NULL, -- VALUATION_METHOD
g_project_org_id, -- SECURITY_ID_INT_1
NULL, -- SECURITY_ID_INT_2
NULL, -- SECURITY_ID_INT_3
NULL, -- SECURITY_ID_CHAR_1
NULL, -- SECURITY_ID_CHAR_2
NULL, -- SECURITY_ID_CHAR_3
NULL, -- ON_HOLD_FLAG
NULL, -- TRANSACTION_DATE
'Y'); -- BUDGETARY_CONTROL_FLAG
select pp.org_id,pp.segment1,
pbv.version_number,pbt.budget_type
into g_project_org_id,l_project_number,
l_budget_version_number,l_budget_type
from pa_projects_all pp,
pa_budget_versions pbv,
pa_budget_types pbt
where pbv.budget_version_id = g_data_set_id
and pp.project_id = pbv.project_id
and pbt.budget_type_code = pbv.budget_type_code;
select pbv.version_number
into l_rev_budget_version_number
from pa_budget_versions pbv
where pbv.budget_version_id = l_bvid_to_reverse;
select distinct budget_version_id, start_date
BULK COLLECT INTO t_source_id_int_1,t_event_date
from pa_budget_lines
where budget_version_id in (g_data_set_id,l_bvid_to_reverse);
pa_debug.g_err_stage:= 'Populate_enc_event_array'||':Calling DELETE_XLA_EVENT_DATA';
l_events_to_delete := 'N';
DELETE_XLA_EVENT_DATA(p_data_set_id1 => g_data_set_id,
p_data_set_id2 => NULL,
p_calling_module => 'FUNDS_CHECK',
p_events_to_delete => l_events_to_delete);
select txn.source_application_id,
txn.application_id,
txn.legal_entity_id,
txn.ledger_id,
txn.entity_code entity_type_code,
txn.transaction_number,
txn.source_id_int_1,
txn.source_id_int_2,
txn.source_id_int_3,
txn.source_id_int_4,
txn.source_id_char_1,
txn.source_id_char_2,
txn.source_id_char_3,
txn.source_id_char_4,
evt.event_date,
evt.transaction_date,
txn.security_id_int_1,
evt.event_id,
decode(evt.event_type_code,
'REQ_RESERVED','REQ_BURDEN_RESERVED',
'REQ_ADJUSTED','REQ_BURDEN_ADJUSTED',
'REQ_UNRESERVED','REQ_BURDEN_UNRESERVED',
'REQ_CANCELLED','REQ_BURDEN_CANCELLED',
'REQ_FINAL_CLOSED','REQ_BURDEN_FINAL_CLOSED',
'REQ_REJECTED','REQ_BURDEN_REJECTED',
'REQ_RETURNED','REQ_BURDEN_RETURNED',
'PO_PA_RESERVED','PO_BURDEN_RESERVED',
'PO_PA_ADJUSTED','PO_BURDEN_ADJUSTED',
'PO_PA_UNRESERVED','PO_BURDEN_UNRESERVED',
'PO_PA_CANCELLED','PO_BURDEN_CANCELLED',
'PO_PA_FINAL_CLOSED','PO_BURDEN_FINAL_CLOSED',
'PO_PA_REJECTED','PO_BURDEN_REJECTED',
'PO_PA_REOPEN_FINAL_MATCH','PO_BURDEN_REOPEN_FINAL_MATCH',
'PO_PA_INV_CANCELLED','PO_BURDEN_INV_CANCELLED',
'PO_PA_CR_MEMO_CANCELLED','PO_BURDEN_CR_MEMO_CANCELLED',
'RELEASE_RESERVED','REL_BURDEN_RESERVED',
'RELEASE_ADJUSTED','REL_BURDEN_ADJUSTED',
'RELEASE_UNRESERVED','REL_BURDEN_UNRESERVED',
'RELEASE_CANCELLED','REL_BURDEN_CANCELLED',
'RELEASE_FINAL_CLOSED','REL_BURDEN_FINAL_CLOSED',
'RELEASE_REJECTED','REL_BURDEN_REJECTED',
'RELEASE_REOPEN_FINAL_CLOSED','REL_BURDEN_REOPEN_FINAL_CLOSED',
'RELEASE_INV_CANCELLED','REL_BURDEN_INV_CANCELLED',
'RELEASE_CR_MEMO_CANCELLED','REL_BURDEN_CR_MEMO_CANCELLED',
'INVOICE VALIDATED','INVOICE_BURDEN_VALIDATED',
'INVOICE CANCELLED','INVOICE_BURDEN_CANCELLED',
'INVOICE ADJUSTED','INVOICE_BURDEN_ADJUSTED',
'CREDIT MEMO VALIDATED','INVOICE_BURDEN_VALIDATED',
'CREDIT MEMO CANCELLED','INVOICE_BURDEN_CANCELLED',
'CREDIT MEMO ADJUSTED','INVOICE_BURDEN_ADJUSTED',
'DEBIT MEMO VALIDATED','INVOICE_BURDEN_VALIDATED',
'DEBIT MEMO CANCELLED','INVOICE_BURDEN_CANCELLED',
'DEBIT MEMO ADJUSTED','INVOICE_BURDEN_ADJUSTED',
'PREPAYMENT VALIDATED','PREPAYMENT_VALIDATED_BURDEN',
'PREPAYMENT ADJUSTED','PREPAYMENT_ADJUSTED_BURDEN',
'PREPAYMENT CANCELLED','PREPAYMENT_CANCELLED_BURDEN',
'PREPAYMENT APPLIED','PREPAYMENT_APPLIED_BURDEN',
'PREPAYMENT UNAPPLIED','PREPAYMENT_UNAPPLIED_BURDEN',
'PREPAYMENT APPLICATION ADJ','PREPAY_APPLICATION_ADJ_BURDEN'
) event_type_code,
evt.event_id
BULK COLLECT INTO
t_source_application_id,
t_application_id,
t_legal_entity_id,
t_ledger_id,
t_entity_type_code,
t_transaction_number,
t_source_id_int_1,
t_source_id_int_2,
t_source_id_int_3,
t_source_id_int_4,
t_source_id_char_1,
t_source_id_char_2,
t_source_id_char_3,
t_source_id_char_4,
t_event_date,
t_transaction_date,
t_security_org_id,
t_source_event_id,
t_event_type_code,
t_reference_num_1
from xla_events evt,
xla_transaction_entities txn
where evt.entity_id = txn.entity_id
and evt.event_id in
(select distinct source_event_id
from pa_bc_packets
where packet_id = g_data_set_id
and status_code = 'I'
and ext_bdgt_flag = 'Y'
--and burden_method_code in ('S','D')
--and bc_event_id is null
);
pa_debug.g_err_stage:= 'Populate_enc_event_array'||':FC: Delete temp. pl/sql table';
t_source_application_id.DELETE;
t_application_id.DELETE;
t_legal_entity_id.DELETE;
t_ledger_id.DELETE;
t_entity_type_code.DELETE;
t_transaction_number.DELETE;
t_source_id_int_2.DELETE;
t_source_id_int_3.DELETE;
t_source_id_int_4.DELETE;
t_source_id_char_1.DELETE;
t_source_id_char_2.DELETE;
t_source_id_char_3.DELETE;
t_source_id_char_4.DELETE;
t_security_org_id.DELETE;
t_reference_num_1.DELETE;
Update pa_budget_lines
set bc_event_id = t_event_id(i)
where budget_version_id = t_source_id_int_1(i)
and start_date = t_event_date(i)
and t_source_id_int_1(i) = g_data_set_id;
Update pa_budget_lines
set bc_rev_event_id = t_event_id(i)
where budget_version_id = t_source_id_int_1(i)
and start_date = t_event_date(i)
and t_source_id_int_1(i) = g_bvid_to_reverse;
Update pa_bc_packets pb
set pb.bc_event_id = t_event_id(i)
where pb.packet_id = g_data_set_id
and pb.source_event_id = t_source_event_id(i)
and pb.status_code = 'I'
and pb.ext_bdgt_flag = 'Y';
select cd.acct_event_id,
cd.transfer_status_code,
cd.dr_code_combination_id,
cd.system_reference5, -- for RCV this holds rcv_subledger_id
pe.document_distribution_id,
pe.document_payment_id
into l_acct_event_id,
l_transfer_status_code,
l_ccid,
l_sys_ref5,
l_document_distribution_id,
l_document_payment_id
from pa_cost_distribution_lines_all cd,
pa_expenditure_items_all pe
where cd.expenditure_item_id = pe.expenditure_item_id
and cd.expenditure_item_id = p_distribution_id_1
and cd.line_num = p_distribution_id_2;
SELECT ts.Predefined_Flag, ts.Acct_Source_Code
INTO l_predefined_flag
,l_acct_source_code
FROM PA_Transaction_Sources ts
WHERE Transaction_Source = P_Transaction_Source;
SELECT code_combination_id
into l_Ccid
FROM xla_distribution_links xdl,
xla_ae_headers aeh,
xla_ae_lines ael,
xla_acct_class_assgns xaca,
xla_assignment_defns_b xad,
xla_post_acct_progs_b xpap,
gl_ledgers gl
WHERE xdl.source_distribution_id_num_1 = l_Source_Distribution_Id_Num_1
AND NVL(xdl.source_distribution_id_num_2, -99) = NVL(l_Source_Distribution_Id_Num_2, -99)
AND xdl.source_distribution_type = l_distribution_type
AND xdl.application_id = l_application_id
AND xdl.ae_header_id = aeh.ae_header_id
AND xdl.ae_line_num = ael.ae_line_num
AND xdl.ae_header_id = ael.ae_header_id
AND aeh.application_id = ael.application_id
AND ael.application_id = xdl.application_id
AND aeh.balance_type_code = 'A'
AND aeh.accounting_entry_status_code = 'F'
AND aeh.ledger_id = P_Ledger_Id
AND ael.accounting_class_code = xaca.accounting_class_code
AND xaca.program_code = xad.program_code
AND xaca.program_owner_code = xad.program_owner_code
AND xad.program_code = xpap.program_code
AND xpap.program_owner_code = 'S'
AND xaca.assignment_code = xad.assignment_code
AND xaca.assignment_owner_code = xad.assignment_owner_code
AND (xad.ledger_id IS NULL OR xad.ledger_id = P_Ledger_Id)
AND xad.enabled_flag = 'Y'
AND gl.ledger_id = P_Ledger_Id
AND xpap.program_code = DECODE ( xaca.accounting_class_code , 'DISCOUNT' ,
DECODE( gl.sla_ledger_cash_basis_flag,
'Y', DECODE ( P_Account_Type,
'CREDIT', 'PA_POSTACCOUNTING_DEBIT',
'DEBIT', ''
)
,l_program_code )
,l_program_code)
/*
Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
and fetched from Debit side of post acc program 'PA_POSTACCOUNTING_DEBIT
For R12+, this need be reverted out and create seperate post acc program
for cash basis include 'Discount' in credit side and remove from Debit.
*/
AND xpap.application_id = 275;
SELECT code_combination_id
into l_ccid
FROM XLA_Distribution_Links xdl,
XLA_Ae_Headers aeh,
XLA_Ae_Lines ael,
XLA_Acct_Class_Assgns xaca,
XLA_Assignment_Defns_b xad,
XLA_Post_acct_Progs_b xpap,
gl_ledgers gl
WHERE xdl.source_Distribution_id_num_1 = P_Distribution_Id_1
AND NVL(xdl.source_Distribution_id_num_2, -99) = NVL(P_Distribution_Id_2 , -99)
AND xdl.Source_Distribution_Type = P_Distribution_Type
AND xdl.application_id = P_Application_Id
AND xdl.ae_header_id = aeh.ae_header_id
AND xdl.ae_line_num = ael.ae_line_num
AND xdl.ae_header_id = ael.ae_header_id
AND aeh.application_id = ael.application_id
AND ael.application_id = xdl.application_id
AND aeh.balance_type_code = 'A'
AND aeh.accounting_entry_status_code = 'F'
AND aeh.ledger_id = P_Ledger_Id
AND ael.accounting_class_code = xaca.accounting_class_code
AND xaca.program_code = xad.program_code
AND xaca.program_owner_code = xad.program_owner_code
AND xad.program_code = xpap.program_code
AND xpap.program_owner_code = 'S'
AND xaca.assignment_code = xad.assignment_code
AND xaca.assignment_owner_code = xad.assignment_owner_code
AND (xad.ledger_id IS NULL OR xad.ledger_id = P_Ledger_Id)
AND xad.enabled_flag = 'Y'
AND gl.ledger_id = P_Ledger_Id
AND xpap.program_code = DECODE ( xaca.accounting_class_code , 'DISCOUNT' ,
DECODE( gl.sla_ledger_cash_basis_flag,
'Y', DECODE ( P_Account_Type,
'CREDIT', 'PA_POSTACCOUNTING_DEBIT',
'DEBIT', ''
)
,l_program_code )
,l_program_code)
/*
Bug 5039683 For Cash Basis : Hard coded acc class 'Discount'
and fetched from Debit side of post acc program 'PA_POSTACCOUNTING_DEBIT
For R12+, this need be reverted out and create seperate post acc program
for cash basis include 'Discount' in credit side and remove from Debit.
*/
AND xpap.application_id = 275;