[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
P_period_name IN g_tab_period_name%TYPE,
P_budget_ccid IN g_tab_budget_ccid%TYPE,
P_allow_flag IN g_tab_allow_flag%TYPE,
P_result_code IN VARCHAR2);
PROCEDURE Update_budget_ccid(P_budget_version_id IN NUMBER,
P_budget_ccid IN g_tab_budget_ccid%TYPE,
P_budget_line_id IN g_tab_budget_line_id%TYPE,
P_budget_entry_level_code IN VARCHAR2,
P_period_name IN g_tab_period_name%TYPE,
P_rlmi IN g_tab_rlmi%TYPE,
P_task_id IN g_tab_task_id%TYPE,
P_derived_ccid IN g_tab_budget_ccid%TYPE,
P_allowed_flag IN g_tab_allow_flag%TYPE,
P_result_code IN OUT NOCOPY VARCHAR2);
p_record_updated OUT NOCOPY Varchar2);
g_tab_res_level_cache_amt.delete;
g_tab_res_grp_level_cache_amt.delete;
g_tab_task_level_cache_amt.delete;
g_tab_top_task_level_cache_amt.delete;
g_tab_proj_level_cache_amt.delete;
g_tab_prj_acct_level_cache_amt.delete;
g_tab_res_level_cache.delete;
g_tab_res_grp_level_cache.delete;
g_tab_task_level_cache.delete;
g_tab_top_task_level_cache.delete;
g_tab_proj_level_cache.delete;
g_tab_proj_acct_level_cache.delete;
g_tab_rowid.delete;
g_tab_bc_packet_id.delete;
g_tab_p_bc_packet_id.delete;
g_tab_budget_version_id.delete;
g_tab_project_id.delete;
g_tab_task_id.delete;
g_tab_doc_type.delete;
g_tab_doc_header_id.delete;
g_tab_doc_distribution_id.delete;
g_tab_exp_item_date.delete;
g_tab_exp_org_id.delete;
g_tab_OU.delete;
g_tab_actual_flag.delete;
g_tab_period_name.delete;
g_tab_time_phase_type_code.delete;
g_tab_amount_type.delete;
g_tab_boundary_code.delete;
g_tab_entry_level_code.delete;
g_tab_category_code.delete;
g_tab_rlmi.delete;
g_tab_p_resource_id.delete;
g_tab_r_list_id.delete;
g_tab_p_member_id.delete;
g_tab_bud_task_id.delete;
g_tab_bud_rlmi.delete;
g_tab_tt_task_id.delete;
g_tab_r_fclevel_code.delete;
g_tab_rg_fclevel_code.delete;
g_tab_t_fclevel_code.delete;
g_tab_tt_fclevel_code.delete;
g_tab_p_fclevel_code.delete;
g_tab_p_acct_fclevel_code.delete;
g_tab_burd_cost_flag.delete;
g_tab_pkt_trx_amt.delete;
g_tab_accounted_dr.delete;
g_tab_accounted_cr.delete;
g_tab_PA_amt.delete;
g_tab_PE_amt.delete;
g_tab_status_code.delete;
g_tab_effect_on_funds_code.delete;
g_tab_result_code.delete;
g_tab_r_result_code.delete;
g_tab_rg_result_code.delete;
g_tab_t_result_code.delete;
g_tab_tt_result_code.delete;
g_tab_p_result_code.delete;
g_tab_r_budget_posted.delete;
g_tab_rg_budget_posted.delete;
g_tab_t_budget_posted.delete;
g_tab_tt_budget_posted.delete;
g_tab_p_budget_posted.delete;
g_tab_r_actual_posted.delete;
g_tab_rg_actual_posted.delete;
g_tab_t_actual_posted.delete;
g_tab_tt_actual_posted.delete;
g_tab_p_actual_posted.delete;
g_tab_r_enc_posted.delete;
g_tab_rg_enc_posted.delete;
g_tab_t_enc_posted.delete;
g_tab_tt_enc_posted.delete;
g_tab_p_enc_posted.delete;
g_tab_r_budget_bal.delete;
g_tab_rg_budget_bal.delete;
g_tab_t_budget_bal.delete;
g_tab_tt_budget_bal.delete;
g_tab_p_budget_bal.delete;
g_tab_r_actual_approved.delete;
g_tab_rg_actual_approved.delete;
g_tab_t_actual_approved.delete;
g_tab_tt_actual_approved.delete;
g_tab_p_actual_approved.delete;
g_tab_r_enc_approved.delete;
g_tab_rg_enc_approved.delete;
g_tab_t_enc_approved.delete;
g_tab_tt_enc_approved.delete;
g_tab_p_enc_approved.delete;
g_tab_effect_fclevel.delete;
g_tab_trxn_ccid.delete;
g_tab_budget_ccid.delete;
g_tab_p_acct_result_code.delete;
g_tab_exp_category.delete;
g_tab_rev_category.delete;
g_tab_sys_link_func.delete;
g_tab_exp_type.delete;
g_tab_gl_date.delete;
g_tab_pa_date.delete;
g_tab_start_date.delete;
g_tab_end_date.delete;
g_tab_encum_type_id.delete;
g_tab_process_funds_level.delete;
g_tab_old_budget_ccid.delete;
g_tab_group_resource_type_id.delete;
g_tab_person_id.delete;
g_tab_job_id.delete;
g_tab_vendor_id.delete;
g_tab_non_lab_res.delete;
g_tab_non_lab_res_org.delete;
g_tab_non_cat_rlmi.delete;
g_tab_proj_OU.delete;
g_tab_exp_OU.delete;
g_tab_doc_line_id.delete;
g_tab_ext_bdgt_link.delete;
g_tab_sob_id.delete;
g_tab_exp_gl_date.delete;
g_tab_exp_pa_date.delete; /*9487097*/
g_tab_exp_item_id.delete;
g_tab_burden_method_code.delete; -- for r12
g_tab_budget_line_id.delete; -- for r12
PROCEDURE update_EIS (p_packet_id IN NUMBER,
p_calling_module IN VARCHAR2,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE update_GL_CBC_result_code(
p_packet_id IN number,
p_calling_module IN varchar2,
p_mode IN varchar2,
p_partial_flag IN varchar2,
p_reference1 IN varchar2 default null,
p_reference2 IN varchar2 default null,
p_packet_status IN varchar2,
x_return_status OUT NOCOPY varchar2);
SELECT decode(count(*),count(decode(substr(nvl(pbc.result_code,'P'),1,1),'P',1)),'S','P')
FROM pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id;
SELECT decode(NVL(ppt.burden_cost_flag, 'N'),'Y',
NVL(ppt.burden_amt_display_method,'S'),'N')
-- decode(NVL(ppt.burden_amt_display_method,'S'), 'S','SAME','D','DIFFERENT'),'NONE')
INTO l_burden_method
FROM pa_project_types ppt,
pa_projects_all pp
WHERE
ppt.project_type = pp.project_type
AND pp.project_id = p_project_id;
PROCEDURE result_status_code_update
( p_status_code IN VARCHAR2 default null
,p_result_code IN VARCHAR2 default null
,p_res_result_code IN VARCHAR2 default null
,p_res_grp_result_code IN VARCHAR2 default null
,p_task_result_code IN VARCHAR2 default null
,p_top_task_result_code IN VARCHAR2 default null
,p_project_result_code IN VARCHAR2 default null
,p_proj_acct_result_code IN VARCHAR2 default null
,p_bc_packet_id IN NUMBER default null
,p_packet_id IN NUMBER ) IS
/* Commented as part of 13009945 cursor cur_pkts is
SELECT packet_id,
bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id;
pa_debug.init_err_stack('PA_FUNDS_CONTROL_PKG.result_status_code_update');
--1:UPDATE STATEMENT :(cost=2,rows=1)
--2:UPDATE PA_BC_PACKETS :(cost=,rows=)
--3:TABLE ACCESS BY INDEX ROWID PA_BC_PACKETS :(cost=2,rows=1)
--4:INDEX UNIQUE SCAN PA_BC_PACKETS_U1 :(cost=1,rows=1)
*******/
If p_status_code = 'T' then
/* Commented as part of 13009945
OPEN cur_pkts;
l_tab_packet_id.delete;
l_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'T',
result_code = decode(substr(nvl(result_code,'P'),1,1)
,'P', decode(substr(nvl(p_result_code,'P'),1,1)
,'F', p_result_code,'F142')
,result_code),
res_result_code = nvl(res_result_code,p_res_result_code),
res_grp_result_code = nvl(res_grp_result_code,p_res_grp_result_code),
task_result_code = nvl(task_result_code,p_task_result_code),
top_task_result_code = nvl(top_task_result_code,p_top_task_result_code),
project_result_code = nvl(project_result_code,p_project_result_code),
project_acct_result_code =nvl(project_acct_result_code,p_proj_acct_result_code)
WHERE packet_id = p_packet_id;
l_tab_packet_id.delete;
l_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = nvl(p_status_code,status_code),
result_code = nvl(p_result_code ,result_code),
res_result_code = nvl(p_res_result_code,res_result_code),
res_grp_result_code = nvl(p_res_grp_result_code,res_grp_result_code),
task_result_code = nvl(p_task_result_code,task_result_code),
top_task_result_code = nvl(p_top_task_result_code,top_task_result_code),
project_result_code = nvl(p_project_result_code,project_result_code),
project_acct_result_code =nvl(p_proj_acct_result_code,project_acct_result_code)
WHERE packet_id = p_packet_id
AND bc_packet_id = p_bc_packet_id
AND substr(nvl(result_code,'P'),1,1) <> 'F';
l_tab_packet_id.delete;
l_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = nvl(p_status_code,status_code),
result_code = nvl(p_result_code ,result_code),
res_result_code = nvl(p_res_result_code,res_result_code),
res_grp_result_code = nvl(p_res_grp_result_code,res_grp_result_code),
task_result_code = nvl(p_task_result_code,task_result_code),
top_task_result_code = nvl(p_top_task_result_code,top_task_result_code),
project_result_code = nvl(p_project_result_code,project_result_code),
project_acct_result_code =nvl(p_proj_acct_result_code,project_acct_result_code)
WHERE packet_id = p_packet_id
AND substr(nvl(result_code,'P'),1,1) <> 'F';
END result_status_code_update;
SELECT 'Y'
INTO l_return_var
FROM pa_implementations;
--1:SELECT STATEMENT :(cost=269,rows=2)
--2:SORT UNIQUE :(cost=269,rows=2)
--3:UNION-ALL :(cost=,rows=)
--4:NESTED LOOPS :(cost=137,rows=1)
--5:NESTED LOOPS :(cost=122,rows=1)
--6:TABLE ACCESS BY INDEX ROWID PA_BC_PACKETS :(cost=120,rows=1)
--7:INDEX RANGE SCAN PA_BC_PACKETS_U1 :(cost=5,rows=1)
--6:TABLE ACCESS BY INDEX ROWID PA_EXPENDITURE_ITEMS_ALL :(cost=2,rows=2345)
--7:INDEX RANGE SCAN PA_EXPENDITURE_ITEMS_N9 :(cost=1,rows=2345)
--5:TABLE ACCESS BY INDEX ROWID PA_COST_DISTRIBUTION_LINES_ALL :(cost=15,rows=47647)
--6:INDEX RANGE SCAN PA_COST_DISTRIBUTION_LINES_U1 :(cost=1,rows=47647)
--4:NESTED LOOPS :(cost=128,rows=1)
--5:NESTED LOOPS :(cost=121,rows=1)
--6:TABLE ACCESS BY INDEX ROWID PA_BC_PACKETS :(cost=120,rows=1)
--7:INDEX RANGE SCAN PA_BC_PACKETS_U1 :(cost=5,rows=1)
--6:TABLE ACCESS BY INDEX ROWID AP_EXPENSE_REPORT_HEADERS_ALL :(cost=1,rows=290)
--7:INDEX RANGE SCAN AP_EXPENSE_REPORT_HEADERS_N1 :(cost=,rows=290)
--5:TABLE ACCESS FULL AP_EXPENSE_REPORT_LINES_ALL :(cost=7,rows=1260)
CURSOR invoice_cdls is
SELECT pbc.bc_packet_id,
pbc.project_id,
nvl(exp.system_linkage_function,'VI') system_linkage_function,
pbc.burden_method_code
FROM pa_bc_packets pbc
,pa_cost_distribution_lines_all cdl
,pa_expenditure_items_all exp
WHERE pbc.packet_id = p_packet_id
AND pbc.document_header_id = cdl.system_reference2
AND pbc.document_distribution_id = cdl.system_reference3
AND pbc.document_type = 'AP'
AND cdl.line_type = 'R'
AND cdl.expenditure_item_id = exp.expenditure_item_id
AND pbc.task_id = exp.task_id -- added to use index N9
AND pbc.expenditure_item_date = exp.expenditure_item_date --added to use index N9
AND exp.system_linkage_function in ('VI','ER')
UNION
SELECT pbc.bc_packet_id,
pbc.project_id,
'ER' system_linkage_function,
pbc.burden_method_code
FROM pa_bc_packets pbc
,ap_expense_report_headers_all exphead
,ap_expense_report_lines_all expline
WHERE pbc.packet_id = p_packet_id
AND pbc.document_header_id = exphead.vouchno
AND pbc.set_of_books_id = exphead.set_of_books_id
AND exphead.report_header_id = expline.report_header_id /* added for bug#2634995 */
AND pbc.document_distribution_id = expline.distribution_line_number
AND pbc.document_type = 'AP'
AND pbc.project_id = expline.project_id
AND pbc.task_id = expline.task_id
AND pbc.expenditure_type = expline.expenditure_type
AND pbc.set_of_books_id = expline.set_of_books_id;
--update the result and status code in pa bc packets
FORALL i IN g_tab_bc_packet_id.FIRST .. g_tab_bc_packet_id.LAST
UPDATE pa_bc_packets
SET status_code = g_tab_status_code(i),
result_code = g_tab_result_code(i),
res_result_code = g_tab_r_result_code(i),
res_grp_result_code = g_tab_rg_result_code(i),
task_result_code = g_tab_t_result_code(i),
top_task_result_code = g_tab_tt_result_code(i),
project_result_code = g_tab_p_result_code(i),
project_acct_result_code = g_tab_p_acct_result_code(i)
WHERE bc_packet_id = g_tab_bc_packet_id(i)
AND packet_id = p_packet_id;
-- funds check just update the status and result code to failed
IF p_function = 'ADD' then
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'inside ADD function');
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE * decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE * decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE * decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE ),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE ),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
/* Bug fix: 3450756 Start */
---AND pb.task_id = p_record.bud_task_id
AND ( (pb.task_id = p_record.bud_task_id and pb.balance_type in ('BGT'))
OR
(pb.balance_type NOT IN ('BGT','REV')
AND
((p_record.entry_level_code = 'L' and p_record.bud_task_id = pb.task_id)
OR
(p_record.entry_level_code = 'P' and p_record.bud_task_id = 0)
OR
(p_record.entry_level_code = 'T'
and p_record.bud_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
OR
(p_record.entry_level_code = 'M'
and ( p_record.bud_task_id = pb.task_id
OR
p_record.bud_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
)))
)
/* Bug fix: 3450756 End */
AND ((pb.resource_list_member_id = l_rlmi AND pb.balance_type not in ('BGT','REV'))
OR (pb.resource_list_member_id = l_rlmi AND pb.balance_type ='BGT')
)
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1 ) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1 ) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1) -- 7531681
;
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao --7531681
WHERE pbc.project_id = p_record.project_id
AND (
(nvl(pbc.top_task_id,0) = p_record.bud_task_id)
or (nvl(pbc.task_id,0) = p_record.bud_task_id)
or p_record.entry_level_code = 'P'
)
AND pbc.resource_list_member_id = l_rlmi
AND NVL(pbc.parent_resource_id,0) = nvl(l_parent_res_id,0) /* Added nvl for bug fix 2658952 */
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id /* 7531681 */
and substr(nvl(pbc.result_code,'P'),1,1)= 'P'
AND pbc.balance_posted_flag = 'N'
AND exists
( select 1 from pa_bc_packet_arrival_order ao
where ao.packet_id = pbc.packet_id
AND (
-- This condition is added to avoid the concurrency issues like when two packets arrive for funds check
-- one is funds check completed but not updated the status to Aprroved as the final status is updated
-- after getting the status from gl tie back.mean time antother packet which arrives has to consider
-- the amount which already consumeed in previous packet.
-- the status code takes care of the following
-- A -- Approved but not yet posted to balances / not yet swept
-- P -- Pending packet which is funds checked not yet approved / when two packets arrives in queue
-- has to consider the amounts in previous packet
-- C -- packets arrives during baseline process will be updated with intermedidate status after FC
-- B -- the approved the transaction will be updated to B during CHECK_BASELINE mode these transaction
-- must be considered during RESERVE_BASELINE mode
-- R12 note: all code related to status code 'C' and 'B' being deleted ..
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
) );
-- This CURSOR select the sum of amount from pa bc balances for the given parent
-- resource id between the start and end date - RESOURCE GROUP CURSOR
--------------------------------------------------------------------------------------
CURSOR res_grp_level_bal (l_parent_member_id number,l_bud_rlmi number) is
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681*/
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE ),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE ),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
/* Bug fix: 3450756 Start */
---AND pb.task_id = p_record.bud_task_id
AND ( (pb.task_id = p_record.bud_task_id and pb.balance_type in ('BGT'))
OR
(pb.balance_type NOT IN ('BGT','REV')
AND
((p_record.entry_level_code = 'L' and p_record.bud_task_id = pb.task_id)
OR
(p_record.entry_level_code = 'P' and p_record.bud_task_id = 0)
OR
(p_record.entry_level_code = 'T'
and p_record.bud_task_id = pb.top_task_id /*(select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
OR
(p_record.entry_level_code = 'M'
and (p_record.bud_task_id = pb.task_id
OR
p_record.bud_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
)))
)
/* Bug fix: 3450756 End */
AND (( NVL(pb.parent_member_id,0) = NVL(l_parent_member_id,0) /* Added NVL for bug fix 2658952 */
and pb.balance_type not in ('BGT','REV')
and pb.parent_member_id is NOT NULL)
OR (pb.resource_list_member_id = l_bud_rlmi AND
pb.balance_type not in ('BGT','REV') AND
pb.parent_member_id is NULL )
OR (pb.resource_list_member_id = l_bud_rlmi AND pb.balance_type ='BGT'
AND pb.parent_member_id is null)
)
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
-- this cursor selects the sum of amounts and rolls up the resource group level
-- RESOURCE ROLLUP
----------------------------------------------------------------------------------------
CURSOR res_rollup_bal (l_parent_member_id number) is
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE ),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE ),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
/* Bug fix: 3450756 Start */
---AND pb.task_id = p_record.bud_task_id
AND ( (pb.task_id = p_record.bud_task_id and pb.balance_type in ('BGT'))
OR
(pb.balance_type NOT IN ('BGT','REV')
AND
((p_record.entry_level_code = 'L' and p_record.bud_task_id = pb.task_id)
OR
(p_record.entry_level_code = 'P' and p_record.bud_task_id = 0)
OR
(p_record.entry_level_code = 'T'
and p_record.bud_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
OR
(p_record.entry_level_code = 'M'
and ( p_record.bud_task_id = pb.task_id
OR
p_record.bud_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
)))
)
/* Bug fix: 3450756 End */
AND ((NVL(pb.parent_member_id,0) = nvl(l_parent_member_id,0) /*Added NVL for bug fix 2658952 */
and pb.balance_type not in ('BGT','REV')
and pb.parent_member_id is NOT NULL )
OR (NVL(pb.parent_member_id,0) = nvl(l_parent_member_id,0) /*Added NVL for bug fix 2658952 */
AND pb.balance_type ='BGT'
AND pb.parent_member_id is NOT NULL)
)
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao --7531681
WHERE pbc.project_id = p_record.project_id
AND (
(nvl(pbc.top_task_id,0) = p_record.bud_task_id)
or (nvl(pbc.task_id,0) = p_record.bud_task_id)
or p_record.entry_level_code = 'P'
)
AND ( (NVL(pbc.parent_resource_id,0) = NVl(l_parent_res_id,0) /*Added NVL for bug fix 2658952 */
and NVl(l_parent_res_id,0) <> 0 )
OR ( pbc.resource_list_member_id = l_rlmi
and NVl(l_parent_res_id,0) = 0)
)
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id -- 7531681
and substr(nvl(pbc.result_code,'P'),1,1)= 'P'
AND pbc.balance_posted_flag = 'N'
AND exists
( select 1 from pa_bc_packet_arrival_order ao /* 7531681 */
where pbc.packet_id = ao.packet_id
AND (
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
));
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE ),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE ),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
AND pb.budget_version_id = p_record.budget_version_id
/* Bug fix: 3450756 Start */
--- AND ( ( pb.task_id = l_task_id AND pb.balance_type='BGT')
--- OR (pb.task_id = l_task_id AND pb.balance_type not in ('REV','BGT'))
--- )
AND ( (pb.task_id = l_task_id and pb.balance_type in ('BGT'))
OR
(pb.balance_type NOT IN ('BGT','REV')
AND
((p_record.entry_level_code = 'L' and pb.task_id = l_task_id )
OR
(p_record.entry_level_code = 'P' and p_record.bud_task_id = 0)
OR
(p_record.entry_level_code = 'T'
and l_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
OR
(p_record.entry_level_code = 'M'
and ( pb.task_id = l_task_id
OR
l_task_id = pb.top_task_id /* (select t.top_task_id
From pa_tasks t
Where t.task_id = pb.task_id) 7531681 */
)
)))
)
/* Bug fix: 3450756 End */
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao -- 7531681
WHERE pbc.project_id = p_record.project_id
AND pbc.top_task_id = l_top_task_id
AND pbc.task_id = l_task_id
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id -- 7531681
and substr(nvl(pbc.result_code,'P'),1,1)= 'P' /* 7531681 */
AND pbc.balance_posted_flag = 'N'
and exists
(select 1 from pa_bc_packet_arrival_order ao
where ao.packet_id = pbc.packet_id
AND (
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
) );
/*SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
AND ((pb.task_id = l_bud_task_id AND pb.balance_type='BGT' AND pb.task_id = l_top_task_id)
OR (pb.top_task_id = l_top_task_id AND pb.balance_type not in ('REV','BGT'))
)
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE ),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE ),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
AND ((pb.top_task_id = l_top_task_id and pb.balance_type not in ('REV','BGT'))
OR (pb.top_task_id = l_top_task_id AND pb.balance_type='BGT'))
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1)AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao -- 7531681
WHERE pbc.project_id = p_record.project_id
AND pbc.top_task_id = l_top_task_id
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id
and pbc.balance_posted_flag = 'N' /* 7531681 */
and substr(nvl(pbc.result_code,'P'),1,1)= 'P'
AND exists
( select 1 from pa_bc_packet_arrival_order ao
where ao.packet_id = pbc.packet_id
AND (
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
) );
/* SELECT nvl(sum(BUDGET_PERIOD_TO_DATE *decode(balance_type,'BGT',1,0)) ,0),
nvl(sum(ACTUAL_PERIOD_TO_DATE *decode(balance_type,'EXP',1,0)) ,0),
nvl(sum(ENCUMB_PERIOD_TO_DATE *decode(balance_type,'REQ',1,
'PO',1,
'AP',1,
'ENC',1,
'CC_C_PAY',1,
'CC_C_CO',1,
'CC_P_PAY',1,
'CC_P_CO',1,
0)),0) 7531681 */
SELECT NVL(SUM(BUDGET_PERIOD_TO_DATE),0),
NVL(SUM(ACTUAL_PERIOD_TO_DATE ),0),
NVL(SUM(ENCUMB_PERIOD_TO_DATE),0)
FROM pa_bc_balances pb
WHERE pb.project_id = p_record.project_id
AND pb.budget_version_id = p_record.budget_version_id
AND pb.start_date between
decode(p_record.time_phased_type_code,'N', pb.start_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.start_date, p_end_date_1) -- 7531681
AND pb.end_date between
decode(p_record.time_phased_type_code,'N', pb.end_date, p_start_date_1) AND -- 7531681
decode(p_record.time_phased_type_code,'N', pb.end_date, p_end_date_1); -- 7531681
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao -- 7531681
WHERE pbc.project_id = p_record.project_id
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id -- 7531681
and pbc.balance_posted_flag = 'N'
and substr(nvl(pbc.result_code,'P'),1,1)= 'P'
AND exists
( select 1 from pa_bc_packet_arrival_order ao
where pbc.packet_id = ao.packet_id
AND (
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
) );
-- This CURSOR select and sums all the entered dr and entered cr columns from pa_bc_packets table
-- for the given budget code combination id falling under the start and end date
-- R12 note: all code related to status code 'C' and 'B' being deleted ..
-------------------------------------------------------------------------------------------------
CURSOR project_acct_tot_bc_pkt(l_bdgt_ccid NUMBER) is
SELECT nvl(sum(decode(pbc.status_code,'P',nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0)
+nvl(sum(decode(pbc.status_code||substr(pbc.result_code,1,1)||pbc.effect_on_funds_code,'ZPI',
nvl(accounted_dr,0) - nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AE',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0),
nvl(sum(decode(pbc.status_code||pbc.actual_flag, 'AA',nvl(accounted_dr,0)-nvl(accounted_cr,0),0)),0)
FROM pa_bc_packets pbc
-- pa_bc_packet_arrival_order ao -- 7531681
WHERE pbc.project_id = p_record.project_id
AND pbc.budget_version_id = p_record.budget_version_id
AND pbc.set_of_books_id = p_sob
ANd pbc.budget_ccid = l_bdgt_ccid
AND ((p_record.time_phased_type_code = 'G' and pbc.gl_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'P' and pbc.pa_date
between p_start_date_1 and p_end_date_1) OR -- 7531681
(p_record.time_phased_type_code = 'N' and pbc.expenditure_item_date
between p_start_date_1 and p_end_date_1) -- 7531681
)
-- AND pbc.packet_id = ao.packet_id -- 7531681
and pbc.balance_posted_flag = 'N'
and substr(nvl(pbc.result_code,'P'),1,1)= 'P'
AND exists
(select 1 from pa_bc_packet_arrival_order ao
where pbc.packet_id = ao.packet_id
AND (
( ao.arrival_seq < p_arrival_seq
--AND ao.affect_funds_flag = 'Y'
AND ao.set_of_books_id = p_sob
AND pbc.status_code in ( 'A','P')
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
-- AND pbc.balance_posted_flag = 'N' -- 7531681
)
OR(pbc.packet_id = p_packet_id
and pbc.status_code = 'Z'
and pbc.effect_on_funds_code = 'I'
and p_partial_flag <> 'Y'
-- and pbc.balance_posted_flag = 'N' -- 7531681
-- and substr(nvl(pbc.result_code,'P'),1,1)= 'P' -- 7531681
)
));
PROCEDURE update_pkt_amts(p_packet_id IN number) IS
PRAGMA AUTONOMOUS_TRANSACTION;
log_message(p_msg_token1 => 'before update of pa bc packets ');
UPDATE pa_bc_packets
SET result_code = nvl(g_tab_result_code(i),result_code),
res_result_code =nvl( g_tab_r_result_code(i),res_result_code),
res_grp_result_code = nvl(g_tab_rg_result_code(i),res_grp_result_code),
task_result_code = nvl(g_tab_t_result_code(i),task_result_code),
top_task_result_code = nvl(g_tab_tt_result_code(i),top_task_result_code),
project_result_code = nvl(g_tab_p_result_code(i),project_result_code),
project_acct_result_code = nvl(g_tab_p_acct_result_code(i),project_acct_result_code),
status_code = decode(nvl(g_tab_status_code(i),status_code),'Z','P',
nvl(g_tab_status_code(i),status_code)),
res_budget_posted =nvl( g_tab_r_budget_posted(i),res_budget_posted),
res_grp_budget_posted = nvl(g_tab_rg_budget_posted(i),res_grp_budget_posted),
task_budget_posted = nvl(g_tab_t_budget_posted(i),task_budget_posted),
top_task_budget_posted = nvl(g_tab_tt_budget_posted(i),top_task_budget_posted),
project_budget_posted = nvl(g_tab_p_budget_posted(i),project_budget_posted),
res_actual_posted = nvl(g_tab_r_actual_posted(i),res_actual_posted ),
res_grp_actual_posted = nvl(g_tab_rg_actual_posted(i),res_grp_actual_posted),
task_actual_posted = nvl(g_tab_t_actual_posted(i),task_actual_posted),
top_task_actual_posted = nvl(g_tab_tt_actual_posted(i),top_task_actual_posted),
project_actual_posted = nvl(g_tab_p_actual_posted(i),project_actual_posted),
res_enc_posted = nvl(g_tab_r_enc_posted(i),res_enc_posted),
res_grp_enc_posted = nvl(g_tab_rg_enc_posted(i),res_grp_enc_posted),
task_enc_posted = nvl(g_tab_t_enc_posted(i),task_enc_posted ),
top_task_enc_posted = nvl(g_tab_tt_enc_posted(i),top_task_enc_posted),
project_enc_posted = nvl(g_tab_p_enc_posted(i),project_enc_posted),
res_budget_bal = nvl(g_tab_r_budget_bal(i),res_budget_bal),
res_grp_budget_bal = nvl(g_tab_rg_budget_bal(i),res_grp_budget_bal),
task_budget_bal = nvl(g_tab_t_budget_bal(i),task_budget_bal),
top_task_budget_bal = nvl(g_tab_tt_budget_bal(i),top_task_budget_bal),
project_budget_bal = nvl(g_tab_p_budget_bal(i),project_budget_bal),
res_actual_approved = nvl(g_tab_r_actual_approved(i),res_actual_approved),
res_grp_actual_approved = nvl(g_tab_rg_actual_approved(i),res_grp_actual_approved),
task_actual_approved =nvl( g_tab_t_actual_approved(i),task_actual_approved),
top_task_actual_approved = nvl(g_tab_tt_actual_approved(i),top_task_actual_approved),
project_actual_approved = nvl(g_tab_p_actual_approved(i),project_actual_approved),
res_enc_approved = nvl(g_tab_r_enc_approved(i),res_enc_approved),
res_grp_enc_approved = nvl(g_tab_rg_enc_approved(i),res_grp_enc_approved),
task_enc_approved = nvl(g_tab_t_enc_approved(i),task_enc_approved),
top_task_enc_approved = nvl(g_tab_tt_enc_approved(i),top_task_enc_approved),
project_enc_approved = nvl(g_tab_p_enc_approved(i),project_enc_approved)
WHERE packet_id = p_packet_id
AND rowid = g_tab_rowid(i);
log_message(p_msg_token1 => 'End of FORALL update statement');
END update_pkt_amts;
/** This api updates the Encumbrance approved bal for the packets
* which contains transaction PO,AP,CC_P_PAY,CC_C_PAY
* This API is primarily intended to update the encumbrance_approved_bal
* to display in the funds check view form refer to Bug:2021199
**/
PROCEDURE update_enc_approvl_bal(p_packet_id IN pa_bc_packets.packet_id%type
,p_mode IN varchar2
,p_calling_module IN varchar2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
* If the inner sub query in update statement returns no rows then all the
* enc approved columns are updated to zero
* So moving the subquery into cursor and updating the bc_packets enc_approved
* columns in a loop
*/
/** Bug fix : if Invoice has Tax lines then api fails with sql error
** ORA-01427: single-row subquery returns more than one row to avoid this
** sum() function has been used. For proper fix we need to add few columns to
** pa_bc_packets and pa_bc_commitments to distiguish the lines as ITEM / TAX
** the fix required here to add one more condition a.line_type = b.line_type
**/
CURSOR updEnc(p_bc_pkt_id Number
,p_res_enc_approved Number
,p_res_grp_enc_approved Number
,p_task_enc_approved Number
,p_top_task_enc_approved Number
,p_project_enc_approved Number
) IS
select decode(nvl(p_res_enc_approved,0),0,0,p_res_enc_approved -
sum(nvl(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0),0))),
decode(nvl(p_res_grp_enc_approved,0),0,0,p_res_grp_enc_approved -
sum(nvl(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0),0))),
decode(nvl(p_task_enc_approved,0),0,0,p_task_enc_approved -
sum(nvl(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0),0))),
decode(nvl(p_top_task_enc_approved,0),0,0,p_top_task_enc_approved -
sum(nvl(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0),0))),
decode(nvl(p_project_enc_approved,0),0,0,p_project_enc_approved -
sum(nvl(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0),0)))
from pa_bc_packets a
,pa_bc_packets b
where
a.packet_id = p_packet_id
and a.bc_packet_id = p_bc_pkt_id
and a.packet_id = b.packet_id
and b.result_code like 'P%'
and abs((nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0))) -
abs((nvl(b.accounted_dr,0)-nvl(b.accounted_cr,0))) < .1
and ( (a.parent_bc_packet_id is null and b.parent_bc_packet_id is null)
or (a.parent_bc_packet_id is not null and b.parent_bc_packet_id is not null)
)
and ((( a.document_type = 'PO' and b.document_type = 'REQ')
and exists ( select 'Y'
from po_distributions_all po
,po_req_distributions_all req
where req.distribution_id = b.document_distribution_id
and po.po_distribution_id = a.document_distribution_id
and po.req_distribution_id = req.distribution_id
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
)
)
OR (( a.document_type = 'AP' and b.document_type = 'PO' )
and exists ( select 'Y'
from po_distributions_all po
,ap_invoice_distributions_all ap
where po.po_distribution_id = b.document_distribution_id
and ap.invoice_id = a.document_header_id
and ap.distribution_line_number = a.document_distribution_id
and ap.po_distribution_id = po.po_distribution_id
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
union /*Bug 13602288: Self Assessed Tax Changes*/
select 'Y'
from po_distributions_all po
,ap_self_assessed_tax_dist_all ap
where po.po_distribution_id = b.document_distribution_id
and ap.invoice_id = a.document_header_id
and ap.distribution_line_number = a.document_distribution_id
and ap.po_distribution_id = po.po_distribution_id
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
)
)
OR (( a.document_type = 'AP' and b.document_type in ( 'CC_P_PAY','CC_C_PAY' ))
and exists ( select 'Y'
from po_distributions_all po
,ap_invoice_distributions_all ap
where po.po_distribution_id = ap.po_distribution_id
and ap.invoice_id = a.document_header_id
and ap.distribution_line_number = a.document_distribution_id
and po.req_header_reference_num = b.document_header_id
and po.req_line_reference_num = b.document_distribution_id
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
union /*Bug 13602288: Self Assessed Tax Changes*/
select 'Y'
from po_distributions_all po
,ap_self_assessed_tax_dist_all ap
where po.po_distribution_id = ap.po_distribution_id
and ap.invoice_id = a.document_header_id
and ap.distribution_line_number = a.document_distribution_id
and po.req_header_reference_num = b.document_header_id
and po.req_line_reference_num = b.document_distribution_id
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
)
)
OR (( a.document_type = 'EXP' and b.document_type = 'AP' )
and (abs(nvl(a.accounted_dr,0) - nvl(a.accounted_cr,0)) =
abs(nvl(b.accounted_dr,0) - nvl(b.accounted_cr,0)))
and exists (select 'Y'
from ap_invoice_distributions_all ap
,pa_bc_packets pbc
where ap.invoice_id = b.document_header_id
and ap.distribution_line_number = b.document_distribution_id
/** and pbc.packet_id = 4003 commented out NOCOPY the hardcoded **/
and pbc.packet_id = p_packet_id
and pbc.document_distribution_id = a.document_distribution_id
and pbc.document_header_id = a.document_header_id
and pbc.document_type = 'EXP'
and pbc.result_code like 'P%'
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
union /*Bug 13602288: Self Assessed Tax Changes*/
select 'Y'
from ap_self_assessed_tax_dist_all ap
,pa_bc_packets pbc
where ap.invoice_id = b.document_header_id
and ap.distribution_line_number = b.document_distribution_id
and pbc.packet_id = p_packet_id
and pbc.document_distribution_id = a.document_distribution_id
and pbc.document_header_id = a.document_header_id
and pbc.document_type = 'EXP'
and pbc.result_code like 'P%'
and a.packet_id = b.packet_id
and a.bc_packet_id = p_bc_pkt_id
and b.packet_id = p_packet_id
)
)
);
SELECT pkts.bc_packet_id,
pkts.res_enc_approved,
pkts.res_grp_enc_approved,
pkts.task_enc_approved,
pkts.top_task_enc_approved,
pkts.project_enc_approved
FROM pa_bc_packets pkts
WHERE pkts.packet_id = p_packet_id
AND nvl(pkts.accounted_dr,0) - nvl(pkts.accounted_cr,0) > 0
AND substr(pkts.result_code ,1,1) = 'P';
log_message(p_msg_token1 => 'Inside update_enc_apprvoled_bal api');
UPDATE pa_bc_packets
SET
res_enc_approved = nvl(g_tab_r_enc_approved(i),res_enc_approved),
res_grp_enc_approved = nvl(g_tab_rg_enc_approved(i),res_grp_enc_approved),
task_enc_approved = nvl(g_tab_t_enc_approved(i),task_enc_approved),
top_task_enc_approved = nvl(g_tab_tt_enc_approved(i),top_task_enc_approved),
project_enc_approved = nvl(g_tab_p_enc_approved(i),project_enc_approved)
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
log_message(p_msg_token1 =>'End of update_enc_approved_bal api');
log_message(p_msg_token1 => 'Failed in update_enc_approval_bal api SQLERR:'||SQLCODE||SQLERRM);
END update_enc_approvl_bal;
SELECT document_type,
document_header_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND status_code = 'P'
AND NVL(substr(result_code,1,1),'P') <> 'F'
ORDER BY document_type,
document_header_id;
SELECT bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND status_code = 'Z';
SELECT bc_packet_id,
effect_on_funds_code
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND status_code = 'P'
AND nvl(substr(result_code,1,1),'P') <> 'F'
AND effect_on_funds_code = 'I';
PROCEDURE update_docs_status
(p_packet_id in number
,p_status_code in varchar2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
log_message(p_msg_token1 => 'Inside update_docs_status');
UPDATE /*+ INDEX (pbp PA_BC_PACKETS_U1) */ pa_bc_packets
SET status_code = p_status_code
WHERE packet_id = p_packet_id
AND document_type = l_tab_doc_type(i)
AND document_header_id = l_tab_doc_header_id(i)
AND status_code = 'P'
AND nvl(substr(result_code,1,1),'P') <> 'F';
UPDATE pa_bc_packets
SET status_code = p_status_code
WHERE packet_id = p_packet_id
AND bc_packet_id = l_tab_bc_packet_id(i);
-- update the result codes to pass as the funds check result in
-- increase in amounts
FORALL i IN l_tab_bc_packet_id.FIRST .. l_tab_bc_packet_id.LAST
UPDATE pa_bc_packets
SET status_code = 'Z',
result_code = 'P113',
res_result_code = 'P113',
res_grp_result_code = 'P113',
task_result_code = 'P113',
top_task_result_code = 'P113',
project_result_code = 'P113',
Project_acct_result_code = 'P113'
WHERE packet_id = p_packet_id
AND bc_packet_id = l_tab_bc_packet_id(i);
END update_docs_status;
l_tab_doc_type.delete;
l_tab_doc_header_id.delete;
log_message(p_msg_token1 => 'calling update_docs_status api');
-- update the status_code to intermediate state
update_docs_status(p_packet_id, 'Z');
-- update the result codes of the all the transactions which
-- donot require funds check
OPEN cur_get_fc_incr_trxn;
l_tab_bc_packet_id.delete;
l_tab_effect_fc_level.delete;
log_message(p_msg_token1 => 'calling update_docs_status for Increase funds trxn');
update_docs_status(p_packet_id, 'I');
log_message(p_msg_token1 => 'calling update_docs_status api to reset the status ');
update_docs_status(p_packet_id, 'P');
update_enc_approvl_bal(p_packet_id => p_packet_id
,p_calling_module => p_calling_module
,p_mode => p_mode);
-- if there is any error then update the transaction
-- back to earlier status from intermediate status
-- reset the status code back to earlier stage
OPEN cur_reset_doc_sts;
log_message(p_msg_token1 => 'calling update_docs_status api to reset the status ');
update_docs_status(p_packet_id, 'P');
SELECT pbc.rowid,
pbc.bc_packet_id,
pbv.budget_version_id ,
pbc.project_id ,
pbc.task_id ,
pbc.document_type,
pbc.document_header_id ,
pbc.document_distribution_id,
pbc.expenditure_item_date ,
pbc.expenditure_organization_id ,
pbc.actual_flag ,
pbc.period_name ,
pm.time_phased_type_code,
pb.amount_type ,
pb.boundary_code ,
pm.entry_level_code,
pm.categorization_code ,
pbc.resource_list_member_id ,
NVL(pbc.parent_resource_id,0) , /* Added for Bug fix: 2658952 */
pbv.resource_list_id ,
NVL(rlm.parent_member_id,0) , /* Added for Bug fix: 2658952 */
pbc.bud_task_id ,
pbc.bud_resource_list_member_id ,
pbc.top_task_id ,
pbc.r_funds_control_level_code ,
pbc.rg_funds_control_level_code ,
pbc.t_funds_control_level_code ,
pbc.tt_funds_control_level_code ,
pbc.p_funds_control_level_code ,
pm.burdened_cost_flag ,
nvl(pbc.accounted_dr,0) accounted_dr,
nvl(pbc.accounted_cr,0) accounted_cr,
nvl(pbc.accounted_dr ,0) - nvl(pbc.accounted_cr,0) pkt_trx_amt,
decode(pbc.status_code||actual_flag,'PE',
nvl(pbc.accounted_dr ,0)-nvl(pbc.accounted_cr,0)*1,0) PE_amt,
decode(pbc.status_code||actual_flag,'PA',
nvl(pbc.accounted_dr ,0)-nvl(pbc.accounted_cr,0)*1,0) PA_amt,
pbc.status_code,
pbc.effect_on_funds_code,
pbc.result_code ,
pbc.res_result_code ,
pbc.res_grp_result_code ,
pbc.task_result_code ,
pbc.top_task_result_code ,
pbc.project_result_code ,
pbc.res_budget_posted,
pbc.res_grp_budget_posted,
pbc.task_budget_posted,
pbc.top_task_budget_posted,
pbc.project_budget_posted,
pbc.res_actual_posted,
pbc.res_grp_actual_posted,
pbc.task_actual_posted,
pbc.top_task_actual_posted,
pbc.project_actual_posted,
pbc.res_enc_posted,
pbc.res_grp_enc_posted,
pbc.task_enc_posted,
pbc.top_task_enc_posted,
pbc.project_enc_posted,
pbc.res_budget_bal,
pbc.res_grp_budget_bal,
pbc.task_budget_bal,
pbc.top_task_budget_bal,
pbc.project_budget_bal,
pbc.res_actual_approved,
pbc.res_grp_actual_approved,
pbc.task_actual_approved,
pbc.top_task_actual_approved,
pbc.project_actual_approved,
pbc.res_enc_approved,
pbc.res_grp_enc_approved,
pbc.task_enc_approved,
pbc.top_task_enc_approved,
pbc.project_enc_approved ,
pbc.effect_on_funds_code,
pbc.txn_ccid,
pbc.budget_ccid,
pbc.gl_date,
pbc.pa_date,
pbc.parent_bc_packet_id,
/** added for bug fix : 1992734 **/
pbc.fc_start_date, /* PAM changes */
pbc.fc_end_date,
nvl(list.GROUP_RESOURCE_TYPE_ID,0) GROUP_RESOURCE_TYPE_ID, /* added for bug fix2658952 */
pbc.ext_bdgt_flag
FROM
pa_bc_packets pbc,
pa_budget_versions pbv,
pa_budget_entry_methods pm,
pa_budgetary_control_options pb,
pa_projects_all pp,
pa_resource_list_members rlm,
PA_RESOURCE_LISTS_ALL_BG list
WHERE pbc.packet_id = p_packet_id
AND pbc.budget_version_id = pbv.budget_version_id
AND pbv.budget_entry_method_code = pm.budget_entry_method_code
AND pbc.resource_list_member_id = rlm.resource_list_member_id
AND pp.project_id = pbc.project_id
AND pbc.project_id = pb.project_id
AND nvl(substr(pbc.result_code,1,1),'P') NOT IN ( 'F','R')
AND ( (pbc.status_code = 'P'
and p_partial_flag <> 'Y'
)
OR
(pbc.status_code = 'Z'
and (pbc.funds_process_mode = 'B' or p_partial_flag = 'Y')) /* 15894144 Changed the condition pbc.effect_on_funds_code to
funds_process_mode */
/*Bug 13707987: During budget base line p_partial_flag will N and all the records having effect_on_funds_code = I
will be marked Z hence to pick these records added or condition for pbc.effect_on_funds_code*/
)
AND pb.BDGT_CNTRL_FLAG = 'Y'
AND pb.BUDGET_TYPE_CODE = pbv.budget_type_code
AND ((pbc.document_type in ('AP','PO','REQ','EXP','CC_P_PAY','CC_C_PAY')
and pb.EXTERNAL_BUDGET_CODE = 'GL')
OR
(pbc.document_type in ('AP','PO','REQ','EXP','CC_P_PAY','CC_C_PAY')
and pb.EXTERNAL_BUDGET_CODE is NULL)
OR
(pbc.document_type in ('CC_P_CO','CC_C_CO')
and pb.EXTERNAL_BUDGET_CODE = 'CC' )
)
/* added for bug fix 2658952 */
AND list.RESOURCE_LIST_ID = rlm.RESOURCE_LIST_ID
ORDER BY
pbc.project_id,
pbc.budget_version_id,
pbc.fc_start_date , /** added for bug fix : 1992734 **/
pbc.fc_end_date , /* bug 8635962 */
--decode(p_partial_flag,'Y',pbc.effect_on_funds_code,0) desc, commented for 13707987
decode(p_calling_module,'DISTERADJ',pbc.document_header_id,0),
pbc.task_id,
pbc.bud_task_id,
NVL(pbc.parent_resource_id,0) , /* Added for Bug fix: 2658952 */
pbc.resource_list_member_id,
pbc.effect_on_funds_code desc, --added for Bug 13707987
pbc.bud_resource_list_member_id,
nvl(pbc.accounted_dr,0) - nvl(pbc.accounted_cr,0)
;
-- update the bc_packets with result and status codes in batch
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Calling update pkt amts autonomous transaction statement');
update_pkt_amts(p_packet_id);
INSERT INTO pa_bc_packet_arrival_order
( packet_id
, set_of_books_id
, arrival_seq
, affect_funds_flag
, last_update_date
, last_updated_by
)
VALUES (
p_packet_id
,p_sobid
,pa_bc_packet_arrival_order_s.nextval
,DECODE ( p_mode, 'B', 'N', 'Y' )
--For budget submit and baselining(S and B) it does not affect the funds.
--For Encumbrances (E) it afffects funds.
,SYSDATE
,fnd_global.user_id
);
log_message(p_msg_token1 => 'No of records inserted into pa_bc_packet_arrival_order ='||sql%rowcount);
SELECT arrival_seq
INTO v_arrival_seq
FROM pa_bc_packet_arrival_order ao
WHERE ao.packet_id = p_packet_id;
result_status_code_update(p_packet_id => p_packet_id,
p_status_code => 'R',
p_result_code => 'F141',
p_res_result_code => 'F141',
p_res_grp_result_code => 'F141',
p_task_result_code => 'F141',
p_project_result_code => 'F141',
p_proj_acct_result_code => 'F141');
result_status_code_update(p_packet_id => p_packet_id,
p_status_code => 'R',
p_result_code => 'F141',
p_res_result_code => 'F141',
p_res_grp_result_code => 'F141',
p_task_result_code => 'F141',
p_project_result_code => 'F141',
p_proj_acct_result_code => 'F141');
result_status_code_update(p_packet_id => p_packet_id,
p_status_code => 'T',
p_result_code => 'F141',
p_res_result_code => 'F141',
p_res_grp_result_code => 'F141',
p_task_result_code => 'F141',
p_project_result_code => 'F141',
p_proj_acct_result_code => 'F141');
FUNCTION bud_res_list_id_update
( p_project_id IN NUMBER,
p_budget_version_id IN NUMBER,
p_resource_list_member_id IN NUMBER,
p_categorization_code IN VARCHAR2,
x_bud_resource_list_member_id OUT NOCOPY NUMBER,
x_parent_resource_id OUT NOCOPY NUMBER
) return BOOLEAN IS
----------------------------------------------------------------------------
-- find the correct resource list id for funds checking.
-- if no budget at the resource level then check the budget at the parent
---level and get the resource list id from pa_bc_balances
---------------------------------------------------------------------------
CURSOR cur_bud_res_list_id IS
SELECT pr.resource_list_id,
pr.parent_member_id
FROM pa_resource_list_members pr
WHERE pr.resource_list_member_id = p_resource_list_member_id;
SELECT resource_list_member_id
FROM pa_bc_balances
WHERE budget_version_id = v_bdgt_version_id
AND project_id = v_project_id
AND resource_list_member_id = v_res_list_mem_id
AND balance_type = 'BGT';
SELECT resource_list_member_id
FROM pa_bc_balances
WHERE budget_version_id = v_bdgt_version_id
ANd project_id = v_project_id
AND resource_list_member_id = v_parent_res_list_mem_id
AND balance_type = 'BGT';
log_message(p_msg_token1 => 'Inside bud_res_list_id_update api');
SELECT pb.resource_list_member_id
INTO l_bud_res_list_member_id
FROM pa_bc_balances pb
WHERE pb.budget_version_id = p_budget_version_id
AND pb.project_id = p_project_id
AND balance_type = 'BGT'
AND ROWNUM = 1;
END bud_res_list_id_update;
FUNCTION budget_task_id_update
( p_project_id IN NUMBER,
p_task_id IN NUMBER,
p_budget_version_id IN NUMBER,
p_entry_level_code IN VARCHAR2,
x_bud_task_id OUT NOCOPY NUMBER,
x_top_task_id OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
-- This cursor picks the LOW_TASK_ID from pa_balances
CURSOR cur_low_task_id( l_project_id NUMBER,
l_task_id NUMBER,
l_bdgt_version_id NUMBER) IS
SELECT task_id
FROM pa_bc_balances
WHERE budget_version_id = l_bdgt_version_id
AND project_id = l_project_id
AND task_id = l_task_id
AND balance_type = 'BGT';
SELECT task_id
FROM pa_bc_balances
WHERE budget_version_id = l_bdgt_version_id
AND project_id = l_project_id
AND balance_type = 'BGT'
AND task_id = (SELECT top_task_id
FROM pa_tasks
WHERE task_id = l_task_id
);
-- if the budget entry level in 'L','T','P' -- update directly.
-------------------------------------------------------------------------------------------
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Inside budget_task_id_update api');
SELECT top_task_id
INTO l_top_task_id
FROM pa_tasks
WHERE task_id = p_task_id;
-- if the budget entry level = 'M' update by record.first select the budget task id
-- (LOW TASK)based on the project,task,budget version from pa_bc_balances if not found then
-- select the budget task id(TOP TASK) based on the project,task,budget version from
-- pa_bc_balacnes for the toptask in pa_tasks
-------------------------------------------------------------------------------------
OPEN cur_low_task_id( p_project_id,
p_task_id,
p_budget_version_id);
log_message(p_msg_token1 => 'failed in budget task id update api SQLERR :'||sqlcode||sqlerrm);
END budget_task_id_update;
SELECT funds_control_level_code
FROM pa_budgetary_controls pbc
,pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.project_id = pbc.project_id
AND bv.budget_type_code = pbc.budget_type_code
AND pbc.project_id = p_project_id
AND ( pbc.task_id = 0
OR pbc.task_id = p_task_id )
AND ((pbc.resource_list_member_id = p_resource_list_member_id
AND pbc.parent_member_id = p_parent_member_id) OR
(pbc.resource_list_member_id = p_resource_list_member_id
AND decode(pbc.parent_member_id,0,-1,NVL(pbc.parent_member_id,-1)) = -1 )
) ;
SELECT funds_control_level_code
FROM pa_budgetary_controls pbc
,pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.project_id = pbc.project_id
AND bv.budget_type_code = pbc.budget_type_code
AND pbc.project_id = p_project_id
AND ( pbc.task_id = 0
OR pbc.task_id = p_task_id )
AND pbc.resource_list_member_id = p_parent_member_id
AND decode(pbc.parent_member_id,0,-1,NVL(pbc.parent_member_id,-1)) = -1;
SELECT funds_control_level_code
FROM pa_budgetary_controls pbc
,pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.project_id = pbc.project_id
AND bv.budget_type_code = pbc.budget_type_code
AND pbc.project_id = p_project_id
AND pbc.task_id = p_task_id
/* Bug fix: 2658952 AND NVL(pbc.parent_member_id,0) = 0 */
AND decode(pbc.parent_member_id,0,-1,NVL(pbc.parent_member_id,-1)) = -1
AND NVL(pbc.resource_list_member_id,0) = 0;
SELECT funds_control_level_code
FROM pa_budgetary_controls pbc
,pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.project_id = pbc.project_id
AND bv.budget_type_code = pbc.budget_type_code
AND pbc.project_id = p_project_id
AND pbc.task_id = p_top_task_id
/* Bug fix: 2658952 AND NVL(pbc.parent_member_id,0) = 0 */
AND decode(pbc.parent_member_id,0,-1,NVL(pbc.parent_member_id,-1)) = -1
AND NVL(pbc.resource_list_member_id,0) = 0;
SELECT funds_control_level_code
FROM pa_budgetary_controls pbc
,pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id
AND bv.project_id = pbc.project_id
AND bv.budget_type_code = pbc.budget_type_code
AND pbc.project_id = p_project_id
AND NVL(pbc.task_id,0) = 0
/* Bug fix: 2658952 AND NVL(pbc.parent_member_id,0) = 0 */
AND decode(pbc.parent_member_id,0,-1,NVL(pbc.parent_member_id,-1)) = -1
AND NVL(pbc.resource_list_member_id,0) = 0;
select nvl(rlm.funds_control_level_code,'N')
from PA_RESOURCE_LIST_MEMBERS rlm
where rlm.resource_list_member_id = c_resource_list_member_id
and rlm.ENABLED_FLAG = 'Y'
and DECODE(rlm.RESOURCE_TYPE_CODE, 'UNCLASSIFIED', 'Y', DISPLAY_FLAG) = 'Y'
and nvl(rlm.migration_code, 'M') = 'M';
FUNCTION encum_detail_update
(p_mode IN VARCHAR2,
p_project_id IN NUMBER,
p_Task_id IN NUMBER,
p_Budget_version_id IN NUMBER,
p_Resource_list_member_id IN NUMBER,
p_sob_id IN NUMBER,
p_Period_name IN varchar2,
p_Expenditure_item_date IN date,
p_document_type IN VARCHAR2,
p_ext_bdgt_type IN VARCHAR2,
p_ext_bdgt_link IN VARCHAR2,
p_bdgt_entry_level IN VARCHAR2,
p_top_task_id IN NUMBER,
p_OU IN NUMBER,
p_calling_module IN VARCHAR2,
x_budget_ccid IN OUT NOCOPY NUMBER,
x_budget_line_id IN OUT NOCOPY NUMBER,
x_gl_date OUT NOCOPY date,
x_pa_date OUT NOCOPY date,
x_result_code OUT NOCOPY varchar2,
x_r_result_code OUT NOCOPY varchar2,
x_rg_result_code OUT NOCOPY varchar2,
x_t_result_code OUT NOCOPY varchar2,
x_tt_result_code OUT NOCOPY varchar2,
x_p_result_code OUT NOCOPY varchar2,
x_p_acct_result_code OUT NOCOPY varchar2
) return BOOLEAN IS
l_pa_date pa_bc_packets.pa_date%type := null;
log_message(p_msg_token1 =>'Inside encum_detail_update api');
log_message(p_msg_token1 =>'selecting gl date');
SELECT gl.end_date,
gl.start_date
INTO l_gl_date,l_gl_start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob_id
AND gl.period_name = p_period_name
AND gl.closing_status in ('O','F');
SELECT gl.end_date,
gl.start_date
INTO l_gl_date,l_gl_start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob_id
AND gl.period_name = p_period_name;
-- inserting records into pa_bc_packets
-- get the gl start date from gl_period_status for getting the budget ccid
Elsif p_document_type = 'EXP' THEN
SELECT gl.start_date
INTO l_gl_start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob_id
AND gl.period_name = p_period_name;
SELECT gl.end_date,
gl.start_date
INTO l_gl_date,l_gl_start_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob_id
AND gl.period_name = p_period_name;
log_message(p_msg_token1 =>'Selecting pa date');
SELECT end_date
INTO l_pa_date
FROM pa_periods
WHERE --(gl_period_name = p_period_name
--OR
trunc(l_gl_date) between start_date and end_date
--)
AND status in ('O','F') ;
END encum_detail_update;
PROCEDURE update_pkts(p_packet_id number) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE pa_bc_packets
SET parent_resource_id = nvl(g_tab_p_resource_id(i),parent_resource_id),
bud_task_id = nvl(g_tab_bud_task_id(i) ,bud_task_id) ,
bud_resource_list_member_id = nvl(g_tab_bud_rlmi(i) ,bud_resource_list_member_id),
top_task_id = nvl(g_tab_tt_task_id(i) ,top_task_id),
r_funds_control_level_code = nvl(g_tab_r_fclevel_code(i),r_funds_control_level_code),
rg_funds_control_level_code =nvl( g_tab_rg_fclevel_code(i),rg_funds_control_level_code),
t_funds_control_level_code = nvl(g_tab_t_fclevel_code(i), t_funds_control_level_code),
tt_funds_control_level_code = nvl(g_tab_tt_fclevel_code(i),tt_funds_control_level_code),
p_funds_control_level_code = nvl(g_tab_p_fclevel_code(i),p_funds_control_level_code),
result_code = nvl(g_tab_result_code(i) ,result_code),
res_result_code = nvl(g_tab_r_result_code(i) ,res_result_code),
res_grp_result_code = nvl(g_tab_rg_result_code(i),res_grp_result_code) ,
task_result_code = nvl(g_tab_t_result_code(i),task_result_code),
top_task_result_code = nvl(g_tab_tt_result_code(i), top_task_result_code),
project_result_code = nvl(g_tab_p_result_code(i),project_result_code),
project_acct_result_code = nvl(g_tab_p_acct_result_code(i),project_acct_result_code),
budget_ccid = nvl(budget_ccid,g_tab_budget_ccid(i)),
budget_line_id = nvl(budget_line_id,g_tab_budget_line_id(i)),
burden_method_code = nvl(burden_method_code,g_tab_burden_method_code(i)),
txn_ccid = nvl(g_tab_trxn_ccid(i),txn_ccid),
effect_on_funds_code = nvl(g_tab_effect_fclevel(i), effect_on_funds_code),
proj_encumbrance_type_id = nvl(g_tab_encum_type_id(i) ,proj_encumbrance_type_id),
gl_date = nvl(g_tab_gl_date(i),gl_date),
pa_date =nvl( g_tab_pa_date(i),pa_date),
ext_bdgt_flag = nvl(g_tab_ext_bdgt_link(i),ext_bdgt_flag),
fc_start_date = nvl(g_tab_start_date(i),fc_start_date),
fc_end_date = nvl(g_tab_end_date(i),fc_end_date)
WHERE packet_id = p_packet_id
AND rowid = g_tab_rowid(i);
log_message(p_msg_token1 => 'Failed in update_pkts api SQLERR'||sqlerrm||sqlcode);
END update_pkts;
SELECT pbc.rowid,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.document_type,
pbc.document_header_id,
pbc.expenditure_organization_id,
pbc.expenditure_type,
TYPE.expenditure_category,
TYPE.revenue_category_code,
/* bug fix: 3700261 NVL ( ei.system_linkage_function, 'VI' ) */
decode(pbc.document_type,'EXP',NVL ( ei.system_linkage_function, 'VI' ),'VI')
system_linkage_function,
pm.categorization_code resource_category_code,
pbc.parent_bc_packet_id,
pm.entry_level_code ,
pbc.period_name,
pbc.expenditure_item_date,
pbc.bc_packet_id,
pbc.org_id exp_org_id,
pp.org_id proj_org_id,
pbc.document_line_id,
bv.resource_list_id,
pbc.vendor_id
FROM pa_bc_packets pbc,
pa_projects_all pp,
pa_budget_versions bv,
pa_budget_entry_methods pm,
pa_expenditure_types type,
pa_expenditure_items_all ei
WHERE pbc.packet_id = p_packet_id
AND pp.project_id = pbc.project_id
AND bv.project_id = pp.project_id
AND pbc.budget_version_id = bv.budget_version_id
AND bv.budget_entry_method_code = pm.budget_entry_method_code
AND pbc.expenditure_type = TYPE.expenditure_type(+)
AND pbc.document_header_id = ei.expenditure_item_id(+)
AND pbc.status_code in ('P','L','I')
AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F')
ORDER BY /** Bug fix :2004139 order by clause is changed to column names **/
pbc.project_id,
pbc.budget_version_id,
pm.entry_level_code ,
pm.categorization_code,
pbc.task_id,
pbc.expenditure_type,
pbc.document_type,
pbc.document_header_id,
ei.system_linkage_function ;
SELECT resource_list_member_id
FROM pa_bc_balances gb
WHERE gb.budget_version_id = v_bdgt_ver_id
AND balance_type = 'BGT'
AND ROWNUM = 1;
SELECT line.vendor_id
FROM po_requisition_lines line,
po_requisition_headers req
WHERE line.requisition_header_id = req.requisition_header_id
AND req.requisition_header_id = v_doc_header_id ;
SELECT head.vendor_id
FROM po_headers_all head
WHERE head.po_header_id = v_doc_header_id;
SELECT head.vendor_id
FROM ap_invoices_all head
WHERE head.invoice_id = v_doc_header_id;
SELECT head.vendor_id
FROM igc_cc_headers_all head
WHERE head.cc_header_id = v_doc_header_id;
SELECT EXP.incurred_by_person_id,
item.job_id
FROM pa_expenditures_all exp,
pa_expenditure_items_all item
WHERE item.expenditure_item_id = v_doc_header_id
AND item.expenditure_id = EXP.expenditure_id;
SELECT tp.attribute2,
tp.attribute3
FROM pa_expenditure_types tp
WHERE tp.expenditure_type = v_doc_exp_type;
SELECT EXP.incurred_by_person_id,
item.job_id,
item.non_labor_resource,
item.organization_id
FROM pa_expenditures_all exp,
pa_expenditure_items_all item
WHERE item.expenditure_item_id = v_doc_header_id
AND item.expenditure_id = EXP.expenditure_id;
SELECT distinct bv.resource_list_id
,bv.budget_version_id
,bv.project_id
,NVL(pm.categorization_code,'N') resource_category_code
FROM pa_budget_versions bv
,pa_budget_entry_methods pm
,pa_bc_packets pkt
WHERE pkt.packet_id = p_packet_id
AND bv.budget_version_id = pkt.budget_version_id
AND substr(nvl(pkt.result_code,'P'),1,1) <> ('F')
AND pkt.status_code in ('P','L','I')
AND bv.budget_entry_method_code = pm.budget_entry_method_code
;
SELECT pkt.bc_packet_id
,pkt.project_id
,pkt.task_id
,pkt.budget_version_id
,pkt.document_type
,pkt.document_header_id
,pkt.document_distribution_id
,pkt.document_line_id
,pkt.expenditure_type
,pkt.resource_list_member_id
,decode(NVL(pt.burden_amt_display_method,'N'),'D'
,decode(parent_bc_packet_id,NULL,'RAW','BURDEN'),'RAW') pkt_line_type
FROM pa_bc_packets pkt
,pa_project_types_all pt
,pa_projects_all pp
WHERE pkt.packet_id = p_packet_id
AND pkt.document_line_id is not null
AND pkt.document_type in ('PO','EXP')
ANd NVL(pkt.summary_record_flag,'N') <> 'Y'
AND substr(nvl(pkt.result_code,'P'),1,1) <> 'F'
ANd pkt.status_code in ('P','L','I')
AND pp.project_id = pkt.project_id
AND pp.project_type = pt.project_type
and pt.org_id = pp.org_id ;
--insert the records into tmp table
IF g_tab_bc_packet_id.EXISTS(1) THEN
BEGIN
FOR resList in cur_resList LOOP
IF resList.resource_category_code = 'R' Then
-- call resource mapping api if the budget is categorized by resource
-- Insert the plsql values into a temp tables
FORALL i IN g_tab_bc_packet_id.First ..g_tab_bc_packet_id.Last
Insert into PA_MAPPABLE_TXNS_TMP
(txn_id
,person_id
,job_id
,organization_id
,vendor_id
,expenditure_type
,event_type
,non_labor_resource
,expenditure_category
,revenue_category
,non_labor_resource_org_id
,event_type_classification
,system_linkage_function
,project_role_id
,resource_list_id
,system_reference1
,system_reference2
,system_reference3
)
SELECT
pa_mappable_txns_tmp_s.NEXTVAL
,g_tab_person_id(i)
,g_tab_job_id(i)
,g_tab_exp_org_id(i)
,g_tab_vendor_id(i)
,g_tab_exp_type(i)
,null
,g_tab_non_lab_res(i)
,g_tab_exp_category(i)
,g_tab_rev_category(i)
,g_tab_non_lab_res_org(i)
,null
,g_tab_sys_link_func(i)
,null
,g_tab_r_list_id(i)
,p_packet_id
,g_tab_bc_packet_id(i)
,g_tab_project_id(i)
FROM DUAL
WHERE substr(nvl(g_tab_result_code(i),'P'),1,1) not in ('R','F')
AND g_tab_r_list_id(i) = resList.resource_list_id
AND g_tab_budget_version_id(i) = resList.budget_version_id
AND g_tab_category_code(i) = 'R' ;
/* 7531681 Update PA_BC_PACKETS pkt
SET (pkt.resource_list_member_id
,pkt.result_code
,pkt.res_result_code
,pkt.res_grp_result_code
,pkt.task_result_code
,pkt.top_task_result_code
,pkt.project_result_code
,pkt.project_acct_result_code) =
(select tmp.resource_list_member_id
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(tmp.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
from PA_MAPPABLE_TXNS_TMP tmp
where tmp.system_reference1 = pkt.packet_id
and tmp.system_reference2 = pkt.bc_packet_id)
WHERE pkt.packet_id = p_packet_id
AND EXISTS ( SELECT 'Y'
FROM PA_MAPPABLE_TXNS_TMP tmp
WHERE tmp.system_reference1 = pkt.packet_id
AND tmp.system_reference2 = pkt.bc_packet_id);
UPDATE PA_BC_PACKETS PKT SET (PKT.RESOURCE_LIST_MEMBER_ID) =
(select TMP.RESOURCE_LIST_MEMBER_ID
FROM
PA_MAPPABLE_TXNS_TMP TMP
WHERE
TMP.SYSTEM_REFERENCE1 = p_packet_id AND
TMP.SYSTEM_REFERENCE2 = g_tab_bc_packet_id(i))
WHERE
PKT.PACKET_ID = p_packet_id and
pkt.bc_packet_id = g_tab_bc_packet_id(i)
AND pkt.budget_version_id = resList.budget_version_id
AND pkt.budget_version_id = g_tab_budget_version_id(i)
AND g_tab_r_list_id(i) = resList.resource_list_id ;
log_message(p_msg_token1 =>'Finally one Update for Non Categoriztion Resource as wells as Failed transactions');
UPDATE pa_bc_packets pkt
SET pkt.resource_list_member_id = decode(g_tab_category_code(i),'R',pkt.resource_list_member_id
,g_tab_non_cat_rlmi(i))
,pkt.result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.res_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.res_grp_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.task_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.top_task_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.project_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
,pkt.project_acct_result_code = decode(g_tab_category_code(i),'R'
,decode(pkt.resource_list_member_id,NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
,decode(g_tab_non_cat_rlmi(i),NULL
,decode(substr(nvl(pkt.result_code,'P'),1,1),'P','F128',pkt.result_code)
,pkt.result_code)
)
WHERE pkt.packet_id = p_packet_id
AND pkt.bc_packet_id = g_tab_bc_packet_id(i)
AND pkt.budget_version_id = resList.budget_version_id
AND pkt.budget_version_id = g_tab_budget_version_id(i)
AND g_tab_r_list_id(i) = resList.resource_list_id
;
/* delete the records from tmp table */
DELETE FROM PA_MAPPABLE_TXNS_TMP tmp
WHERE tmp.system_reference1 = p_packet_id;
/* CWK labor changes update the pkts with reosurce list member ids of the summary records
* information on the transactions */
IF p_calling_module NOT IN ('CBC') Then
log_message(p_msg_token1 => 'Updating rlmi with summary record rlmi for Contigent Wkr transactions');
g_tab_bc_packet_id.delete;
g_tab_project_id.delete;
g_tab_task_id.delete;
g_tab_budget_version_id.delete;
g_tab_doc_type.delete;
g_tab_doc_header_id.delete;
g_tab_doc_distribution_id.delete;
g_tab_doc_line_id.delete;
g_tab_exp_type.delete;
g_tab_rlmi.delete;
l_tab_resmap_pkt_line_type.delete;
-- Bulk update the cwkRlmi
FORALL i IN g_tab_bc_packet_id.FIRST .. g_tab_bc_packet_id.LAST
UPDATE pa_bc_packets pkt
SET pkt.resource_list_member_id = NVL(g_tab_rlmi(i),pkt.resource_list_member_id)
WHERE pkt.packet_id = p_packet_id
AND pkt.bc_packet_id = g_tab_bc_packet_id(i)
AND pkt.document_type in ('PO','EXP')
ANd NVL(pkt.summary_record_flag,'N') <> 'Y'
AND substr(nvl(pkt.result_code,'P'),1,1) <> 'F' ;
log_message(p_msg_token1 => 'No of rows updated['||sql%rowcount||']');
SELECT pbc.rowid,
pbc.budget_version_id,
pbc.project_id,
pbc.task_id,
pbc.document_type,
pbc.document_header_id,
pbc.expenditure_organization_id,
pbc.expenditure_type,
pm.categorization_code,
pbc.parent_bc_packet_id,
pm.entry_level_code ,
pbc.accounted_dr,
pbc.accounted_cr,
pbc.period_name,
pbc.expenditure_item_date,
pbc.bc_packet_id,
pbc.txn_ccid,
pbc.old_budget_ccid,
pbc.org_id,
pbc.resource_list_member_id,
bv.resource_list_id,
pm.time_phased_type_code,
pb.amount_type,
pb.boundary_code,
pbc.set_of_books_id,
pbc.gl_date,
pbc.pa_date, /*9487097 */
pbc.burden_method_code,
--decode(pbc.burden_method_code,'S','SAME',
-- 'D','DIFFERENT',
-- 'N','NONE',
-- pbc.burden_method_code) burden_method_code,
pbc.budget_line_id,
pbc.budget_ccid
FROM pa_bc_packets pbc,
pa_budget_versions bv,
pa_budget_entry_methods pm,
pa_budgetary_control_options pb
WHERE pbc.packet_id = p_packet_id
AND pbc.budget_version_id = bv.budget_version_id
AND bv.budget_entry_method_code = pm.budget_entry_method_code
AND pbc.status_code in ('P','L')
AND substr(nvl(pbc.result_code,'P'),1,1) not in ('R','F')
AND pb.project_id = pbc.project_id
AND pb.BDGT_CNTRL_FLAG = 'Y'
AND pb.BUDGET_TYPE_CODE = bv.budget_type_code
AND ((pbc.document_type in ('AP','PO','REQ','EXP','CC_P_PAY','CC_C_PAY')
and pb.EXTERNAL_BUDGET_CODE = 'GL')
OR
(pbc.document_type in ('AP','PO','REQ','EXP','CC_P_PAY','CC_C_PAY')
and pb.EXTERNAL_BUDGET_CODE is NULL)
OR
(pbc.document_type in ('CC_P_CO','CC_C_CO')
and pb.EXTERNAL_BUDGET_CODE = 'CC' )
)
ORDER BY /** Bug fix :2004139 order by clause is changed to column names **/
pbc.project_id,
pbc.budget_version_id,
pm.entry_level_code ,
pm.categorization_code,
pbc.task_id,
pbc.expenditure_type,
pbc.document_type,
pbc.document_header_id
;
select fund_control_level_project,
fund_control_level_task ,
fund_control_level_res_grp,
fund_control_level_res
from pa_budgetary_control_options pb,
pa_budget_versions pv
where pv.project_id = pb.project_id
AND pb.BDGT_CNTRL_FLAG = 'Y'
AND pb.BUDGET_TYPE_CODE = pv.budget_type_code
AND pv.budget_version_id = bud_version_id;
log_message(p_msg_token1 => 'Calling bud_res_list_id_update api ');
IF NOT bud_res_list_id_update
( p_project_id => g_tab_project_id(i),
p_budget_version_id => g_tab_budget_version_id(i),
p_resource_list_member_id => g_tab_rlmi(i),
p_categorization_code => g_tab_category_code(i),
x_bud_resource_list_member_id => l_bud_rlmi,
x_parent_resource_id => l_parent_resource_id
) Then
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 =>
'Failed to derive bud_rlmi and parent resource id');
log_message(p_msg_token1 => 'Calling bud task id update api ');
IF NOT budget_task_id_update
( p_project_id => g_tab_project_id(i),
p_task_id => g_tab_task_id(i),
p_budget_version_id => g_tab_budget_version_id(i),
p_entry_level_code => g_tab_entry_level_code(i),
x_bud_task_id => l_bud_task_id,
x_top_task_id => l_top_task_id
) then
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Failed to derive top task and bud task ids');
IF NOT encum_detail_update
(p_mode => p_mode,
p_project_id => g_tab_project_id(i),
p_Task_id => g_tab_task_id(i),
p_Budget_version_id => g_tab_budget_version_id(i),
p_Resource_list_member_id => g_tab_rlmi(i),
p_sob_id => p_sob,
p_Period_name => g_tab_period_name(i),
p_Expenditure_item_date => g_tab_exp_item_date(i),
p_document_type => g_tab_doc_type(i),
p_ext_bdgt_type => l_ext_bdgt_type,
p_ext_bdgt_link => l_ext_bdgt_link,
p_bdgt_entry_level => g_tab_entry_level_code(i),
p_top_task_id => l_top_task_id,
p_OU => g_tab_OU(i),
p_calling_module => p_calling_module,
x_budget_ccid => l_budget_ccid,
x_budget_line_id => l_budget_line_id,
x_gl_date => l_gl_date,
x_pa_date => l_pa_date,
x_result_code => l_result_code,
x_r_result_code => l_r_result_code,
x_rg_result_code => l_rg_result_code,
x_t_result_code => l_t_result_code,
x_tt_result_code => l_tt_result_code,
x_p_result_code => l_p_result_code,
x_p_acct_result_code => l_p_acct_result_code
) then
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Failed to derive Encumbrance Details ');
-- then update the burden line tranaction ccid with budget ccid
log_message(p_msg_token1 => 'Update the trxn ccid for bdn lines ');
SELECT gl.start_date,gl.end_date
INTO l_fc_start_date,l_fc_end_date
FROM gl_period_statuses gl
WHERE gl.application_id = 101
AND gl.set_of_books_id = p_sob
AND gl.period_name = g_tab_period_name(i);
-- all the setup parameters if there is no error then update the result code
-- to success and donot call pa_fcp_process
If p_mode in ('F') and substr(nvl(l_result_code,'P'),1,1) = 'P' then
l_result_code := 'P116'; -- Transaction passed funds check in forcepass mode
log_message(p_msg_token1 => 'After loop calling FORALL update statement');
-- update the pa bc pakcets in a batch of 200 records after dering the setup
-- param values
/*****
log_message(p_msg_token1 => 'update bc packets for batch of 200 record ');
log_message(p_msg_token1 => 'calling update pkt autonomous transaction api');
update_pkts(p_packet_id => p_packet_id);
result_status_code_update(
p_status_code => 'T',
p_result_code => 'F120',
p_res_result_code => 'F120',
p_res_grp_result_code => 'F120',
p_task_result_code => 'F120',
p_top_task_result_code => 'F120',
p_project_result_code => 'F120',
p_proj_acct_result_code => 'F120',
p_packet_id => p_packet_id);
PROCEDURE result_code_update_burden
(p_packet_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR update_burden_rows IS
SELECT bc_packet_id,
result_code,
res_result_code,
res_grp_result_code,
task_result_code,
top_task_result_code,
project_result_code,
project_acct_result_code
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND parent_bc_packet_id IS NULL
AND nvl(SUBSTR ( result_code,1,1),'P') IN ('F','R');
log_message(p_msg_token1 => 'Indide result_code_update_burden api ');
OPEN update_burden_rows; LOOP
log_message(p_msg_token1 => 'opened the update_burden_rows cursor ');
g_tab_bc_packet_id.delete;
g_tab_r_result_code.delete;
g_tab_rg_result_code.delete;
g_tab_t_result_code.delete;
g_tab_tt_result_code.delete;
g_tab_p_result_code.delete;
g_tab_p_acct_result_code.delete;
FETCH update_burden_rows BULK COLLECT INTO
g_tab_bc_packet_id,
g_tab_result_code,
g_tab_r_result_code,
g_tab_rg_result_code,
g_tab_t_result_code,
g_tab_tt_result_code,
g_tab_p_result_code,
g_tab_p_acct_result_code
LIMIT l_num_rows;
UPDATE pa_bc_packets
--SET result_code = 'F116' the line is commented out NOCOPY as the user need not be shown
-- difference between raw and burden
SET result_code = g_tab_result_code(i),
res_result_code = g_tab_r_result_code(i),
res_grp_result_code = g_tab_rg_result_code(i),
task_result_code = g_tab_t_result_code(i),
top_task_result_code = g_tab_tt_result_code(i),
project_result_code = g_tab_p_result_code(i),
project_acct_result_code = g_tab_p_acct_result_code(i)
WHERE packet_id = p_packet_id
AND parent_bc_packet_id = g_tab_bc_packet_id(i)
AND nvl(substr(result_code,1,1),'P') in ('P','A');
EXIT when update_burden_rows%NOTFOUND ;
CLOSE update_burden_rows;
log_message(p_msg_token1 => 'end of update_burden_rows cursor');
if update_burden_rows%ISOPEN THEN
close update_burden_rows ;
log_message(p_msg_token1 => 'Exception portion in result_code_update_burden api');
END result_code_update_burden;
PROCEDURE result_code_update_raw
(p_packet_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2 )IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR update_raw_rows IS
SELECT a.parent_bc_packet_id,
a.result_code,
a.res_result_code,
a.res_grp_result_code,
a.task_result_code,
a.top_task_result_code,
a.project_result_code,
a.project_acct_result_code
FROM pa_bc_packets a,
pa_bc_packets b
WHERE a.packet_id = p_packet_id
AND nvl(SUBSTR ( a.result_code,1,1),'P') in ('R','F')
AND a.parent_bc_packet_id IS NOT NULL
ANd a.packet_id = b.packet_id
AND b.bc_packet_id = a.parent_bc_packet_id
AND nvl(substr(b.result_code,1,1),'P') in ('A','P');
log_message(p_msg_token1 => 'Indide result_code_update_raw api ');
OPEN update_raw_rows; LOOP
log_message(p_msg_token1 => 'opened the cursor update_raw_rows cursor');
g_tab_p_bc_packet_id.delete;
g_tab_r_result_code.delete;
g_tab_rg_result_code.delete;
g_tab_t_result_code.delete;
g_tab_tt_result_code.delete;
g_tab_p_result_code.delete;
g_tab_p_acct_result_code.delete;
FETCH update_raw_rows BULK COLLECT INTO
g_tab_p_bc_packet_id,
g_tab_result_code,
g_tab_r_result_code,
g_tab_rg_result_code,
g_tab_t_result_code,
g_tab_tt_result_code,
g_tab_p_result_code,
g_tab_p_acct_result_code
LIMIT l_num_rows;
UPDATE pa_bc_packets
--SET result_code = 'F115'
SET result_code = g_tab_result_code(i),
res_result_code = g_tab_r_result_code(i),
res_grp_result_code = g_tab_rg_result_code(i),
task_result_code = g_tab_t_result_code(i),
top_task_result_code = g_tab_tt_result_code(i),
project_result_code = g_tab_p_result_code(i),
project_acct_result_code = g_tab_p_acct_result_code(i)
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_p_bc_packet_id(i)
AND nvl(substr(result_code,1,1),'P') in ('A','P');
EXIT WHEN update_raw_rows%NOTFOUND;
CLOSE update_raw_rows;
log_message(p_msg_token1 => 'end of update_raw_rows api ');
if update_raw_rows%ISOPEN THEN
close update_raw_rows ;
log_message(p_msg_token1 => 'exception in result_code_update_raw api ');
END result_code_update_raw;
PROCEDURE update_trxn_doc_levl
(p_packet_id IN NUMBER,
p_mode IN VARCHAR2,
p_calling_module IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR update_headers IS
SELECT document_header_id,
document_line_id,
exp_item_id,
result_code
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND nvl(substr(result_code,1,1),'P') in ('F','R');
PA_DEBUG.init_err_stack('PA_FUNDS_CONTROL_PKG.update_trxn_doc_levl');
log_message(p_msg_token1 => 'inside the update_trxn_doc_levl api');
OPEN update_headers; LOOP
log_message(p_msg_token1 => 'opened the update_headers cursor ');
g_tab_doc_header_id.delete;
g_tab_doc_line_id.delete;
g_tab_exp_item_id.delete;
g_tab_result_code.delete;
FETCH update_headers BULK COLLECT INTO
g_tab_doc_header_id,
g_tab_doc_line_id,
g_tab_exp_item_id,
g_tab_result_code LIMIT l_num_rows;
UPDATE pa_bc_packets
SET result_code = decode(substr(nvl(result_code,'P'),1,1),'P',
decode(p_calling_module,'CBC',g_tab_result_code(i),'F117'),result_code)
WHERE packet_id = p_packet_id
AND ( (document_header_id = g_tab_doc_header_id(i)
and document_type in ('EXP','AP','CC_P_PAY','CC_C_PAY','CC_C_CO','CC_P_CO')
and p_calling_module in ('DISTBTC','CBC','DISTVIADJ','TRXIMPORT','DISTERADJ')
)
OR
(p_calling_module = 'DISTCWKST'
and document_line_id = g_tab_doc_line_id(i)
and exp_item_id = g_tab_exp_item_id(i)
and document_type in ('PO','EXP')
)
)
AND nvl(substr(result_code,1,1),'P') in ('P','A');
log_message(p_msg_token1 => 'Num of Rows updated['||sql%rowcount||']');
EXIT WHEN update_headers%NOTFOUND ;
CLOSE update_headers;
log_message(p_msg_token1 => 'end of update_headers cursor');
IF update_headers%ISOPEN THEN
close update_headers;
log_message(p_msg_token1 => 'exception in update_trxn_doc_levl api ');
END update_trxn_doc_levl;
-- call for update the burden transaction with failure status
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Calling result_code_update_burden api ');
result_code_update_burden
(p_packet_id => p_packet_id,
x_return_status => x_return_status);
-- call for update of the raw transaction with the failure status
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Calling result_code_update_raw api ');
result_code_update_raw
(p_packet_id => p_packet_id,
x_return_status => x_return_status);
log_message(p_msg_token1 => 'After result_code_update_raw api call');
--call for update at the ei level if ei is a adjusted cdls
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'Calling update_trxn_doc_levl api ');
update_trxn_doc_levl
(p_packet_id => p_packet_id,
p_mode => p_mode,
p_calling_module => p_calling_module,
x_return_status => x_return_status);
log_message(p_msg_token1 => 'After update_trxn_doc_levl api call');
SELECT wf_status_code
FROM pa_budget_versions
WHERE project_id = p_project_id
AND wf_status_code is NOT NULL;
PROCEDURE status_code_update_autonomous (
p_calling_module IN VARCHAR2,
p_packet_id IN NUMBER,
p_mode IN VARCHAR2,
p_partial IN VARCHAR2 DEFAULT 'N',
p_packet_status IN VARCHAR2 DEFAULT 'S',
x_return_status OUT NOCOPY varchar2 )
IS
PRAGMA AUTONOMOUS_TRANSACTION;
status_code_update (
p_calling_module => p_calling_module,
p_packet_id => p_packet_id,
p_mode => p_mode,
p_partial => p_partial,
p_packet_status => p_packet_status,
x_return_status => x_return_status);
End status_code_update_autonomous;
PROCEDURE status_code_update (
p_calling_module IN VARCHAR2,
p_packet_id IN NUMBER,
p_mode IN VARCHAR2,
p_partial IN VARCHAR2 DEFAULT 'N',
p_packet_status IN VARCHAR2 DEFAULT 'S',
x_return_status OUT NOCOPY varchar2 ) IS
-- PRAGMA AUTONOMOUS_TRANSACTION;
SELECT rowid,
bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND EXISTS(
SELECT 'x'
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'F'
);
SELECT rowid,
bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND status_code = 'P'
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'P';
SELECT distinct project_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id;
SELECT bc_packet_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id;
PA_DEBUG.init_err_stack('PA_FUNDS_CONTROL_PKG.status_code_update');
log_message(p_msg_token1 =>'Inside the status code update api p_calling_module['
||p_calling_module||']packet_id['||p_packet_id||']mode['
||p_mode||']partial flag['||p_partial||']packet_status['
||p_packet_status||']');
* update the status code of the packets to fatal so that it will not pickup
* for updating the balances
*/
IF p_packet_status = 'T' then
OPEN cur_fatal_error;
g_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'T'
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
g_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'E'
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
g_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'S'
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
-- update the status to intermediate status of B - baseline finally the
-- the base line process will udate the status to A and sweeper programm
-- picks all the records
g_error_stage := 'STATUS_CODE: BASELINE';
g_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'R' -- rejected
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
log_message(p_msg_token1 => 're-baseline fails [ '||sql%rowcount||' ] records updated to R');
g_tab_bc_packet_id.delete;
UPDATE pa_bc_packets
SET status_code = 'A'
WHERE packet_id = p_packet_id
AND bc_packet_id = g_tab_bc_packet_id(i);
log_message(p_msg_token1 => 're-baseline passed [ '||sql%rowcount||' ] records updated to R');
log_message(p_msg_token1 => 'end of baseline update staus');
UPDATE pa_bc_packets
SET status_code = DECODE ( SUBSTR (
nvl(result_code,'P'), 1, 1 )
, 'P', decode(status_code,'P','S',status_code)
, 'F' )
WHERE packet_id = p_packet_id
AND status_code in ('P','L','S');
UPDATE pa_bc_packets
SET status_code = 'F'
WHERE packet_id = p_packet_id
AND EXISTS (SELECT 'x'
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND ( SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'F'
OR p_packet_status in ('F','R','T')
));
UPDATE pa_bc_packets
SET status_code = 'S'
WHERE packet_id = p_packet_id
AND status_code in ('P','L','S')
AND SUBSTR ( nvl(result_code,'P'),1,1 ) = 'P';
UPDATE pa_bc_packets
SET status_code = 'R'
WHERE packet_id = p_packet_id
AND EXISTS (SELECT 'x'
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND ( SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'F'
OR p_packet_status in ('F','R','T')
));
log_message(p_msg_token1 => 'no rows updated = '||l_rowcount);
UPDATE pa_bc_packets
SET status_code = 'A'
WHERE packet_id = p_packet_id
AND project_id = l_project_id
AND status_code in ('P')
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'P';
-- if there is any failed transaction then update the bc packet with
-- each record as Rejected.
g_error_stage := 'STATUS_CODE:RESERVE - Partial';
UPDATE pa_bc_packets
SET status_code = DECODE ( SUBSTR (
nvl(result_code,'P'), 1, 1 )
, 'P', decode(status_code,'P','A',status_code)
, 'R' )
WHERE packet_id = p_packet_id
AND project_id = l_project_id
AND status_code in ('P','L');
log_message(p_msg_token1 => 'no of rows updated ='||sql%rowcount);
log_message (p_msg_token1 =>'Updated the status code for Partial Mode');
-- if there is any failed transaction then update the whole packet with Rejected
g_error_stage := 'STATUS_CODE:RESERVE - Full';
UPDATE pa_bc_packets
SET status_code = 'R'
WHERE packet_id = p_packet_id
AND project_id = l_project_id
AND EXISTS (SELECT 'x'
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND ( SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'F'
OR p_packet_status in ('F','R','T')
));
UPDATE pa_bc_packets
SET status_code = 'A'
WHERE packet_id = p_packet_id
AND project_id = l_project_id
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'P'
AND status_code in ('P');
log_message(p_msg_token1 => 'failed in status code update api SQLERR :'||sqlcode||sqlerrm);
END status_code_update;
PROCEDURE update_EIS (p_packet_id IN NUMBER,
p_calling_module IN VARCHAR2,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR ei_details is
SELECT project_id,
document_type,
document_header_id,
document_distribution_id,
GL_DATE ,
budget_ccid,
proj_encumbrance_type_id,
status_code,
result_code,
bc_packet_id,
parent_bc_packet_id,
res_result_code,
res_grp_result_code,
task_result_code,
top_task_result_code,
project_result_code,
project_acct_result_code,
accounted_dr,
accounted_cr,
budget_version_id,
budget_line_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id
ORDER BY document_header_id,document_distribution_id,bc_packet_id;
pa_debug.init_err_stack('PA_FUNDS_CONTROL_PKG.update_EIS');
log_message(p_msg_token1 => 'Inside the Update EIS api');
log_message(p_msg_token1 => 'Inside the Update EIS api');
g_tab_project_id.delete;
g_tab_doc_type.delete;
g_tab_doc_header_id.delete;
g_tab_doc_distribution_id.delete;
g_tab_gl_date.delete;
g_tab_budget_ccid.delete;
g_tab_encum_type_id.delete;
g_tab_status_code.delete;
g_tab_result_code.delete;
g_tab_bc_packet_id.delete;
g_tab_p_bc_packet_id.delete;
g_tab_r_result_code.delete;
g_tab_rg_result_code.delete;
g_tab_t_result_code.delete;
g_tab_tt_result_code.delete;
g_tab_p_result_code.delete;
g_tab_p_acct_result_code.delete;
l_tab_dist_warn_code.delete;
l_tab_warning_code.delete;
g_tab_accounted_dr.delete;
g_tab_accounted_cr.delete;
l_tab_ext_bdgt_flag.delete;
g_tab_budget_version_id.delete;
g_tab_budget_line_id.delete;
log_message(p_msg_token1 => 'Calling FORALL update for EI');
UPDATE pa_expenditure_items_all
SET cost_dist_rejection_code =
decode(substr(g_tab_result_code(i),1,1),'F',
g_tab_result_code(i),null)
,cost_dist_warning_code = l_tab_warning_code(i)
WHERE expenditure_item_id = g_tab_doc_header_id(i)
AND (g_tab_p_bc_packet_id(i) is NULL OR g_tab_p_bc_packet_id(i) = -7777 );
log_message(p_msg_token1 => 'Calling FORALL update for CDL');
UPDATE pa_cost_distribution_lines_all
SET --gl_date = g_tab_gl_date(i)
budget_ccid = g_tab_budget_ccid(i)
,budget_version_id = g_tab_budget_version_id(i)
,budget_line_id = g_tab_budget_line_id(i)
,liquidate_encum_flag = 'Y'
,encumbrance_type_id = g_tab_encum_type_id(i)
,encumbrance_amount = nvl(g_tab_accounted_dr(i),0) -
nvl(g_tab_accounted_cr(i),0)
WHERE expenditure_item_id = g_tab_doc_header_id(i)
AND line_num = g_tab_doc_distribution_id(i)
AND line_type = 'R'
AND g_tab_p_bc_packet_id(i) is null
AND l_tab_ext_bdgt_flag(i) = 'Y'
AND substr(nvl(g_tab_result_code(i),'P'),1,1) = 'P';
log_message(p_msg_token1 => 'end of FORALL update for CDL');
log_message(p_msg_token1 => 'SQLERR :'||sqlcode||sqlerrm|| 'failed in update_EIS api');
END update_EIS;
SELECT project_id,
budget_version_id,
budget_ccid,
period_name,
sum(nvl(accounted_dr,0)),
sum(nvl(accounted_cr,0))
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND substr(nvl(result_code,'P'),1,1) = 'P'
AND status_code = 'A'
AND NVL(ext_bdgt_flag,'N') = 'Y' /*PAM changes */
GROUP BY project_id,
budget_version_id,
budget_ccid,
period_name
ORDER BY project_id,
budget_version_id,
budget_ccid,
period_name;
log_message(p_msg_token1 => 'inside the update budget acct api'||
'calling module ['||p_calling_module||']p_mode ['||p_mode||
']p_packet_status ['||p_packet_status ||']' );
g_tab_project_id.delete;
g_tab_budget_version_id.delete;
g_tab_budget_ccid.delete;
g_tab_period_name.delete;
g_tab_accounted_dr.delete;
g_tab_accounted_cr.delete;
g_tab_encum_type_id.delete;
g_tab_gl_date.delete;
g_tab_doc_type.delete;
result_status_code_update
(p_packet_id => p_packet_id,
p_result_code => 'F162',
p_res_result_code => 'F162',
p_res_grp_result_code => 'F162',
p_task_result_code => 'F162',
p_top_task_result_code => 'F162',
p_project_result_code => 'F162',
p_proj_acct_result_code => 'F162',
p_status_code => 'T');
/** Bug fix : 1900229 During Check mode also insert liquidation and burden transaction
* to gl_bc_packets and igc_cc_interface tables
*/
IF g_debug_mode = 'Y' THEN
log_message(p_msg_token1 => 'p_calling_module'||p_calling_module||' p_mode= '||p_mode);
SELECT nvl(MAX(batch_line_num),0)
INTO l_max_batch_line_id
FROM igc_cc_interface
WHERE document_type = 'CC'
AND cc_header_id = p_reference2;
INSERT INTO igc_cc_interface(
CC_HEADER_ID,
CC_VERSION_NUM,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID ,
CODE_COMBINATION_ID,
BATCH_LINE_NUM ,
CC_TRANSACTION_DATE ,
CC_FUNC_DR_AMT ,
CC_FUNC_CR_AMT ,
JE_SOURCE_NAME ,
JE_CATEGORY_NAME,
PERIOD_SET_NAME ,
PERIOD_NAME ,
ACTUAL_FLAG ,
BUDGET_DEST_FLAG ,
SET_OF_BOOKS_ID ,
ENCUMBRANCE_TYPE_ID ,
CBC_RESULT_CODE ,
STATUS_CODE ,
BUDGET_VERSION_ID ,
BUDGET_AMT ,
COMMITMENT_ENCMBRNC_AMT ,
OBLIGATION_ENCMBRNC_AMT ,
CC_ENCMBRNC_DATE ,
FUNDS_AVAILABLE_AMT ,
CURRENCY_CODE ,
TRANSACTION_DESCRIPTION ,
REFERENCE_1 ,
REFERENCE_2 ,
REFERENCE_3 ,
REFERENCE_4 ,
REFERENCE_5 ,
REFERENCE_6 ,
REFERENCE_7 ,
REFERENCE_8 ,
REFERENCE_9 ,
REFERENCE_10,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
DOCUMENT_TYPE,
Project_line
--BATCH_ID ,
--PA_FLAG ,
--RESULT_CODE_LEVEL ,
--RESULT_CODE_SOURCE
)
SELECT
igc.CC_HEADER_ID,
igc.CC_VERSION_NUM,
igc.CC_ACCT_LINE_ID,
igc.CC_DET_PF_LINE_ID ,
pbc.txn_ccid,
l_max_batch_line_id + to_number(rownum), --igc.BATCH_LINE_NUM ,
igc.CC_TRANSACTION_DATE ,
decode(nvl(pbc.accounted_cr,0),0,NULL,pbc.accounted_cr),
decode(nvl(pbc.accounted_dr,0),0,NULL,pbc.accounted_dr),
igc.JE_SOURCE_NAME ,
igc.JE_CATEGORY_NAME,
igc.PERIOD_SET_NAME ,
igc.PERIOD_NAME ,
'E',
igc.BUDGET_DEST_FLAG ,
igc.SET_OF_BOOKS_ID ,
pbc.proj_encumbrance_type_id,
igc.CBC_RESULT_CODE ,
igc.STATUS_CODE ,
igc.BUDGET_VERSION_ID ,
igc.BUDGET_AMT ,
igc.COMMITMENT_ENCMBRNC_AMT ,
igc.OBLIGATION_ENCMBRNC_AMT ,
igc.CC_ENCMBRNC_DATE ,
igc.FUNDS_AVAILABLE_AMT ,
igc.CURRENCY_CODE ,
igc.TRANSACTION_DESCRIPTION ,
igc.REFERENCE_1 ,
igc.REFERENCE_2 ,
igc.REFERENCE_3 ,
igc.REFERENCE_4 ,
igc.REFERENCE_5 ,
igc.REFERENCE_6 ,
igc.REFERENCE_7 ,
'PKT_ID:'||pbc.packet_id, --igc.REFERENCE_8 ,
'BC_PKT_ID:'||pbc.bc_packet_id, --igc.REFERENCE_9 ,
igc.REFERENCE_10,
--igc.REFERENCE_10,
igc.LAST_UPDATE_DATE ,
igc.LAST_UPDATED_BY ,
igc.LAST_UPDATE_LOGIN ,
igc.CREATION_DATE ,
igc.CREATED_BY ,
igc.DOCUMENT_TYPE ,
'Y'
--igc.BATCH_ID ,
--igc.PA_FLAG ,
--igc.RESULT_CODE_LEVEL ,
--igc.RESULT_CODE_SOURCE
FROM igc_cc_interface igc,
pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.document_header_id = igc.cc_header_id
AND pbc.document_distribution_id = igc.cc_acct_line_id
AND pbc.document_type in ('CC_C_CO','CC_P_CO')
AND pa_funds_control_utils.get_bdgt_link(
pbc.project_id,decode(pbc.document_type,'CC_C_CO','CBC',
'CC_P_CO','CBC',
'STD')) = 'Y'
AND pbc.status_code NOT IN ('Z','T','V','B')
AND substr(nvl(pbc.result_code,'P'),1,1) = 'P'
AND ( pbc.gl_row_number = igc.rowid
OR
(to_char(pbc.bc_packet_id) = substr(igc.reference_9,
length('BC_PKT_ID:')+1)
)
);
log_message(p_msg_token1 => 'No of rows inserted into CBC = '||sql%rowcount);
result_status_code_update
(p_packet_id => p_packet_id,
p_result_code => 'F161',
p_res_result_code => 'F161',
p_res_grp_result_code => 'F161',
p_task_result_code => 'F161',
p_top_task_result_code => 'F161',
p_project_result_code => 'F161',
p_proj_acct_result_code => 'F161',
p_status_code => 'T');
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT null
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND status_code = 'T'
);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT null
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'F'
);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT null
FROM pa_bc_packets
WHERE packet_id = p_packet_id
AND SUBSTR ( nvl(result_code,'P'), 1, 1 ) = 'P'
);
result_status_code_update(p_packet_id => p_packet_id,
p_result_code => 'F160',
p_status_code => 'T',
p_res_result_code => 'F160',
p_res_grp_result_code => 'F160',
p_task_result_code => 'F160',
p_top_task_result_code => 'F160',
p_project_result_code => 'F160',
p_proj_acct_result_code => 'F160');
SELECT BC_Packet_ID
FROM PA_BC_Packets
WHERE Packet_ID=p_Packet_ID AND
Parent_BC_Packet_ID IS NOT NULL;
SELECT RowID
FROM GL_BC_Packets
WHERE Template_ID=l_BCPacketID;
SELECT nvl(MAX(batch_line_num),0)
INTO l_max_batch_line_id
FROM igc_cc_interface
WHERE document_type = 'CC'
AND cc_header_id = p_reference2;
INSERT INTO igc_cc_interface(
CC_HEADER_ID,
CC_VERSION_NUM,
CC_ACCT_LINE_ID,
CC_DET_PF_LINE_ID ,
CODE_COMBINATION_ID,
BATCH_LINE_NUM ,
CC_TRANSACTION_DATE ,
CC_FUNC_DR_AMT ,
CC_FUNC_CR_AMT ,
JE_SOURCE_NAME ,
JE_CATEGORY_NAME,
PERIOD_SET_NAME ,
PERIOD_NAME ,
ACTUAL_FLAG ,
BUDGET_DEST_FLAG ,
SET_OF_BOOKS_ID ,
ENCUMBRANCE_TYPE_ID ,
CBC_RESULT_CODE ,
STATUS_CODE ,
BUDGET_VERSION_ID ,
BUDGET_AMT ,
COMMITMENT_ENCMBRNC_AMT ,
OBLIGATION_ENCMBRNC_AMT ,
CC_ENCMBRNC_DATE ,
FUNDS_AVAILABLE_AMT ,
CURRENCY_CODE ,
TRANSACTION_DESCRIPTION ,
REFERENCE_1 ,
REFERENCE_2 ,
REFERENCE_3 ,
REFERENCE_4 ,
REFERENCE_5 ,
REFERENCE_6 ,
REFERENCE_7 ,
REFERENCE_8 ,
REFERENCE_9 ,
REFERENCE_10,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATION_DATE ,
CREATED_BY ,
DOCUMENT_TYPE ,
Project_Line
--BATCH_ID ,
--PA_FLAG ,
--RESULT_CODE_LEVEL ,
--RESULT_CODE_SOURCE
)
SELECT
igc.CC_HEADER_ID,
igc.CC_VERSION_NUM,
igc.CC_ACCT_LINE_ID,
igc.CC_DET_PF_LINE_ID ,
pbc.txn_ccid,
l_max_batch_line_id + to_number(rownum), -- igc.BATCH_LINE_NUM ,
igc.CC_TRANSACTION_DATE ,
decode(nvl(pbc.accounted_dr,0),0,NULL,pbc.accounted_dr),
decode(nvl(pbc.accounted_cr,0),0,NULL,pbc.accounted_cr),
igc.JE_SOURCE_NAME ,
igc.JE_CATEGORY_NAME,
igc.PERIOD_SET_NAME ,
igc.PERIOD_NAME ,
igc.actual_flag,
igc.BUDGET_DEST_FLAG ,
igc.SET_OF_BOOKS_ID ,
pbc.encumbrance_type_id,
igc.CBC_RESULT_CODE ,
igc.STATUS_CODE ,
igc.BUDGET_VERSION_ID ,
igc.BUDGET_AMT ,
igc.COMMITMENT_ENCMBRNC_AMT ,
igc.OBLIGATION_ENCMBRNC_AMT ,
igc.CC_ENCMBRNC_DATE ,
igc.FUNDS_AVAILABLE_AMT ,
igc.CURRENCY_CODE ,
igc.TRANSACTION_DESCRIPTION ,
igc.REFERENCE_1 ,
igc.REFERENCE_2 ,
igc.REFERENCE_3 ,
igc.REFERENCE_4 ,
igc.REFERENCE_5 ,
igc.REFERENCE_6 ,
igc.REFERENCE_7 ,
'PKT_ID:'||pbc.packet_id, --igc.REFERENCE_8 , /** checked with Arkadi cbc team **/
'BC_PKT_ID:'||pbc.bc_packet_id, --igc.REFERENCE_9 , /** to use these two columns **/
igc.REFERENCE_10,
igc.LAST_UPDATE_DATE ,
igc.LAST_UPDATED_BY ,
igc.LAST_UPDATE_LOGIN ,
igc.CREATION_DATE ,
igc.CREATED_BY ,
igc.DOCUMENT_TYPE ,
'Y'
--igc.BATCH_ID ,
--igc.PA_FLAG ,
--igc.RESULT_CODE_LEVEL ,
--igc.RESULT_CODE_SOURCE
FROM igc_cc_interface igc,
pa_bc_packets pbc
WHERE pbc.packet_id = p_packet_id
AND pbc.document_type in ('CC_C_CO','CC_P_CO')
/*** bug fix : 1883119
AND ( pbc.status_code = 'P'
OR (pbc.status_code in ('P','S') and g_mode = 'C')
)
**/
AND pbc.status_code NOT IN ('Z','T','V','B','L')
AND substr(nvl(pbc.result_code,'P'),1,1) = 'P'
AND pbc.document_header_id = igc.cc_header_id
AND pbc.document_distribution_id = igc.cc_acct_line_id
AND ( ( pbc.parent_bc_packet_id is NOT NULL)
or (pbc.parent_bc_packet_id is NULL
and check_bdn_on_sep_item (pbc.project_id) = 'S')
)
ANd igc.document_type = 'CC';
result_status_code_update(p_packet_id => p_packet_id,
p_result_code => 'F163',
p_status_code => 'T',
p_res_result_code => 'F163',
p_res_grp_result_code => 'F163',
p_task_result_code => 'F163',
p_top_task_result_code => 'F163',
p_project_result_code => 'F163',
p_proj_acct_result_code => 'F163');
result_status_code_update(p_packet_id => p_packet_id,
p_result_code => 'F164',
p_status_code => 'T',
p_res_result_code => 'F164',
p_res_grp_result_code => 'F164',
p_task_result_code => 'F164',
p_top_task_result_code => 'F164',
p_project_result_code => 'F164',
p_proj_acct_result_code => 'F164');
PROCEDURE update_GL_CBC_result_code(
p_packet_id IN number,
p_calling_module IN varchar2,
p_mode IN varchar2,
p_partial_flag IN varchar2,
p_reference1 IN varchar2 default null,
p_reference2 IN varchar2 default null,
p_packet_status IN varchar2,
x_return_status OUT NOCOPY varchar2) IS
l_igc_status varchar2(100);
SELECT 'Y'
FROM pa_bc_packets
WHERE status_code = 'T'
AND packet_id = p_packet_id
AND rownum = 1;
SELECT decode(count(*), count(decode(substr(nvl
(igc.cbc_result_code,'P'),1,1),'P',1)),'P','F')
FROM igc_cc_interface igc
WHERE igc.cc_header_id = l_cc_header_id;
SELECT decode(count(*), count(decode(substr(nvl
(gl.result_code,'P'),1,1),'P',1)),'P','F')
FROM gl_bc_packets gl
WHERE gl.packet_id = p_packet_id;
PA_DEBUG.init_err_stack('PA_FUNDS_CONTROL_PKG.update_GL_CBC_result_code');
log_message(p_msg_token1 => 'Inside the update_GL_CBC_result_code api');
log_message(p_msg_token1 =>' update gl bc packet with result code ');
UPDATE gl_bc_packets gl
SET gl.result_code =
(select MAX(
decode(substr(nvl(gl.result_code,'P'),1,1),'P',
decode( pbc.result_code,'F100','X00',
'F101','X59',
'F102','X60',
'F103','X61',
'F104','X62',
'F105','X63',
'F106','X64',
'F107','X29',
'F108','X30',
'F109','X31',
'F110','X32',
'F111','X33',
'F112','X34',
'F113','X35',
'F114','X36',
'F115','X36',
'F116','X36',
'F117','X36',
'F118','X38',
'F119','X37',
'F120','X36',
'F121','X40',
'F122','X41',
'F123','X42',
'F124','X43',
'F125','X44',
'F127','X45',
'F128','X46',
'F129','X47',
'F130','X48',
'F131','X49',
'F132','X50',
'F134','X51',
'F135','X52',
'F136','X36',
'F137','X54',
'F138','X55',
'F140','X36',
'F141','X56',
'F142','X36',
'F143','X53',
'F144','X36',
'F145','X36',
'F146','X36',
'F160','X36',
'F161','X36',
'F162','X36',
'F163','X36',
'F164','X36',
'F165','X39',
'F166','X38', -- added during CC import testing 2891273
'F168','X36', -- added fo r12 ..
/** added decodes for stamping advisory warnings bug :1975786 **/
'P101',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P28')))))),
'P102',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P29')))))),
'P103',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P104',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P31')))))),
'P105',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P106',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P38')))))),
'P107',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P108',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P37')))))),
'P109',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P110',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P36')))))),
'P111',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P112',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P35')))))),
'P113','P32',
'P114','P33',
'P115','P34',
'P116','P05',
'F150','F58',
'F151','F58',
'F155','F58',
'F156','F58',
'F152','F57',
'F153','F57',
'F157','F57',
'F158','F57',
'F169','F35',
'F170','F36',
'F171','F36',
'F172','F36',
'F173','F36',
gl.result_code )
, gl.result_code ))
from pa_bc_packets pbc
where pbc.packet_id = p_packet_id
and pbc.document_distribution_id = gl.source_distribution_id_num_1
and ((pbc.source_event_id = gl.event_id
and (pbc.document_type = decode(gl.source_distribution_type,'AP_INV_DIST','AP','AP_PREPAY','AP','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'PO_DISTRIBUTIONS_ALL','PO','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'PO_REQ_DISTRIBUTIONS_ALL','REQ','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'CC','CC_C_PAY')
OR
pbc.document_type = decode(gl.source_distribution_type,'CC','CC_P_PAY')
)
)
OR
(pbc.bc_event_id = gl.event_id
and (pbc.document_type = decode(gl.source_distribution_type,'PA_AP_BURDEN','AP','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'PA_PO_BURDEN','PO','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'PA_REQ_BURDEN','REQ','X')
OR
pbc.document_type = decode(gl.source_distribution_type,'CC','CC_C_PAY')
OR
pbc.document_type = decode(gl.source_distribution_type,'CC','CC_P_PAY')
)
)
)
)
WHERE gl.packet_id = p_packet_id
-- Bug 5352185 : Added the nvl to the following AND condition.
AND nvl(substr(gl.result_code,1,1),'P') not in ('X') -- In AP matched case if PO fails, the PO rec. already failed
AND (gl.event_id, gl.source_distribution_id_num_1
--,source_distribution_type
) in
(Select pb.bc_event_id,
pb.document_distribution_id
--,decode(pb.document_type,
-- 'AP','AP_INV_DIST',
-- 'AP','PA_AP_BURDEN',
-- 'PO','PO_DISTRIBUTIONS_ALL',
-- 'PO','PA_PO_BURDEN',
-- 'REQ','PA_REQ_BURDEN',
-- 'REQ','PO_REQ_DISTRIBUTIONS_ALL') source_distribution_type
from pa_bc_packets pb
where pb.packet_id = p_packet_id
UNION ALL
Select pb.source_event_id,
pb.document_distribution_id
--,decode(pb.document_type,
-- 'AP','AP_INV_DIST',
-- 'AP','PA_AP_BURDEN',
-- 'PO','PO_DISTRIBUTIONS_ALL',
-- 'PO','PA_PO_BURDEN',
-- 'REQ','PA_REQ_BURDEN',
-- 'REQ','PO_REQ_DISTRIBUTIONS_ALL') source_distribution_type
from pa_bc_packets pb
where pb.packet_id = p_packet_id);
log_message(p_msg_token1 =>'no of rows result code updated after= '||sql%rowcount);
log_message(p_msg_token1 =>' update gl bc packet with result code for PO for AP matched');
UPDATE gl_bc_packets gl
SET gl.result_code =
(select MAX(
decode(substr(nvl(gl.result_code,'P'),1,1),'P',
decode( pbc.result_code,'F100','X00',
'F101','X59',
'F102','X60',
'F103','X61',
'F104','X62',
'F105','X63',
'F106','X64',
'F107','X29',
'F108','X30',
'F109','X31',
'F110','X32',
'F111','X33',
'F112','X34',
'F113','X35',
'F114','X36',
'F115','X36',
'F116','X36',
'F117','X36',
'F118','X38',
'F119','X37',
'F120','X36',
'F121','X40',
'F122','X41',
'F123','X42',
'F124','X43',
'F125','X44',
'F127','X45',
'F128','X46',
'F129','X47',
'F130','X48',
'F131','X49',
'F132','X50',
'F134','X51',
'F135','X52',
'F136','X36',
'F137','X54',
'F138','X55',
'F140','X36',
'F141','X56',
'F142','X36',
'F143','X53',
'F144','X36',
'F145','X36',
'F146','X36',
'F160','X36',
'F161','X36',
'F162','X36',
'F163','X36',
'F164','X36',
'F165','X39',
'F166','X38', -- added during CC import testing 2891273
'F168','X36', -- added fo r12 ..
/** added decodes for stamping advisory warnings bug :1975786 **/
'P101',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P28')))))),
'P102',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P29')))))),
'P103',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P104',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P31')))))),
'P105',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P106',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P38')))))),
'P107',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P108',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P37')))))),
'P109',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P110',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P36')))))),
'P111',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P112',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P35')))))),
'P113','P32',
'P114','P33',
'P115','P34',
'P116','P05',
'F150','F58',
'F151','F58',
'F155','F58',
'F156','F58',
'F152','F57',
'F153','F57',
'F157','F57',
'F158','F57',
'F169','F35',
'F170','F36',
'F171','F36',
'F172','F36',
'F173','F36',
gl.result_code )
, gl.result_code ))
from pa_bc_packets pbc
where pbc.packet_id = p_packet_id
and pbc.document_type = 'PO'
and pbc.bc_event_id is null
and pbc.reference3 = gl.source_distribution_id_num_1
and (nvl(pbc.accounted_dr,0) - nvl(pbc.accounted_cr,0)) =
-1* (nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0))
)
WHERE gl.packet_id = p_packet_id
AND gl.source_distribution_type = 'AP_INV_DIST'
AND substr(gl.result_code,1,1) not in ('X','F')
AND exists
( select 1
from pa_bc_packets pbc1
where pbc1.packet_id = p_packet_id
and pbc1.document_type = 'PO'
and pbc1.bc_event_id is null
and pbc1.reference3 = gl.source_distribution_id_num_1
and (nvl(pbc1.accounted_dr,0) - nvl(pbc1.accounted_cr,0)) =
-1* (nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0))
);
log_message(p_msg_token1 =>'(AP match,non int) no of rows,result code updated= '||sql%rowcount);
UPDATE gl_bc_packets gl
SET gl.result_code = decode(substr(gl.result_code,1,1),'P',
decode(sign(nvl(gl.accounted_dr,0) - nvl(gl.accounted_cr,0)),
-1, 'P32',
gl.result_code),gl.result_code),
gl.status_code = decode(nvl(l_pkt_fatal_error_flag,'N'),'Y','T',
decode(p_partial_flag
,'Y', decode(substr(nvl(gl.result_code,'P'),1,1) ,
'P',gl.status_code,
'F',decode(p_mode,'C','F','R'),
'X',decode(p_mode,'C','F','R'),
gl.status_code)
,'N',decode(p_packet_status,
'S',decode(l_igc_status,'P', decode(p_mode,'C','S','A'),
'F', decode(p_mode,'C','F','R')),
'F',decode(p_mode,'C','F','R'),
'T',decode(p_mode,'C','F','R'),'R')))
WHERE gl.packet_id = p_packet_id
AND (gl.event_id, gl.source_distribution_id_num_1
--,source_distribution_type
) in
(Select pb.bc_event_id,
pb.document_distribution_id
--,decode(pb.document_type,
-- 'AP','AP_INV_DIST',
-- 'AP','PA_AP_BURDEN',
-- 'PO','PO_DISTRIBUTIONS_ALL',
-- 'PO','PA_PO_BURDEN',
-- 'REQ','PA_REQ_BURDEN',
-- 'REQ','PO_REQ_DISTRIBUTIONS_ALL') source_distribution_type
from pa_bc_packets pb
where pb.packet_id = p_packet_id
UNION ALL
Select pb.source_event_id,
pb.document_distribution_id
--,decode(pb.document_type,
-- 'AP','AP_INV_DIST',
-- 'AP','PA_AP_BURDEN',
-- 'PO','PO_DISTRIBUTIONS_ALL',
-- 'PO','PA_PO_BURDEN',
-- 'REQ','PA_REQ_BURDEN',
-- 'REQ','PO_REQ_DISTRIBUTIONS_ALL') source_distribution_type
from pa_bc_packets pb
where pb.packet_id = p_packet_id);
log_message(p_msg_token1 =>'no of rows status code updated after= '||sql%rowcount);
log_message(p_msg_token1 =>' update CBC packet with result code ');
UPDATE igc_cc_interface igc
SET igc.cbc_result_code =
(select MAX(
decode(substr(nvl(igc.cbc_result_code,'P'),1,1),'P',
decode( pbc.result_code,'F100','F00',
'F101','F59',
'F102','F60',
'F103','F61',
'F104','F62',
'F105','F63',
'F106','F64',
'F107','F29',
'F108','F30',
'F109','F31',
'F110','F32',
'F111','F33',
'F112','F34',
'F113','F35',
'F114','F36',
'F115','F36',
'F116','F36',
'F117','F36',
'F118','F38',
'F119','F37',
'F120','F36',
'F121','F40',
'F122','F41',
'F123','F42',
'F124','F43',
'F125','F44',
'F127','F45',
'F128','F46',
'F129','F47',
'F130','F48',
'F131','F49',
'F132','F50',
'F134','F51',
'F135','F52',
'F136','F36',
'F137','F54',
'F138','F55',
'F140','F36',
'F141','F56',
'F142','F36',
'F143','F53',
'F144','F36',
'F145','F36',
'F146','F36',
'F160','F36',
'F161','F36',
'F162','F36',
'F163','F36',
'F164','F36',
'F165','F39',
'F166','F38', -- added during CC import testing 2891273
-- added decodes for stamping advisory warnings bug :1975786
'P101',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P28')))))),
'P102',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P29')))))),
'P103',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P104',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P31')))))),
'P105',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P106',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P38')))))),
'P107',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P108',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P37')))))),
'P109',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P110',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P36')))))),
'P111',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P30')))))),
'P112',decode(pbc.res_result_code,'P112','P35',
decode(pbc.res_grp_result_code,'P110','P36',
decode(pbc.task_result_code,'P108','P37',
decode(pbc.top_task_result_code,'P106','P38',
decode(pbc.project_result_code,'P104','P31',
decode(pbc.project_acct_result_code,'P102','P29',
'P35')))))),
'P113','P32',
'P114','P33',
'P115','P34',
'P116','P05',
'F150','F58',
'F151','F58',
'F155','F58',
'F156','F58',
'F152','F57',
'F153','F57',
'F157','F57',
'F158','F57', igc.cbc_result_code )
, igc.cbc_result_code))
from pa_bc_packets pbc
where pbc.packet_id = p_packet_id
and (pbc.gl_row_number = igc.rowid
or
( substr(igc.reference_9,length('BC_PKT_ID:')+1) in
( pbc.bc_packet_id)
)
)
)
WHERE igc.rowid in (SELECT pkt.gl_row_number
FROM pa_bc_packets pkt
WHERE pkt.packet_id = p_packet_id
AND pkt.gl_row_number = igc.rowid
)
OR
( substr(igc.reference_9,length('BC_PKT_ID:')+1) in
( SELECT pbc.bc_packet_id
FROM pa_bc_packets pbc
WHERE pbc.packet_id = substr(reference_8,length('PKT_ID:')+1)
AND pbc.bc_packet_id = substr(reference_9,length('BC_PKT_ID:')+1)
)
);
/** the calling module CBC_TIEBACK is used to update the
* status code in igc_cc_interface table if pa pass and
* cbc fc fails, if pa fails then cbc doesnot call tie back
* since payment forcast lines are not funds checked we
* should not update the status code of payment forcast line
*/
open igc_status(p_reference2,p_reference1);
UPDATE igc_cc_interface igc
SET igc.cbc_result_code = decode(substr(cbc_result_code,1,1),'P',
decode(sign(nvl(igc.cc_func_dr_amt,0)
- nvl(igc.cc_func_cr_amt,0)),
-1, 'P32',
-- --1,'P28', commented for bug :1975786
cbc_result_code),cbc_result_code),
igc.status_code = decode(nvl(l_pkt_fatal_error_flag,'N'),'Y','T',
decode(p_partial_flag
,'Y', decode(substr(nvl(igc.cbc_result_code,'P'),1,1) ,
'P',igc.status_code,
'F',decode(p_mode,'C','F','R'),
'X',decode(p_mode,'C','F','R'),
igc.status_code)
,'N',decode(p_packet_status,
'S',decode(l_igc_status,'P', decode(p_mode,'C','S','A'),
'F', decode(p_mode,'C','F','R')),
'F',decode(p_mode,'C','F','R'),
'T',decode(p_mode,'C','F','R'),'R')))
WHERE ((p_calling_module = 'CBC_TIEBACK'
and igc.cc_header_id = p_reference2)
OR
( p_calling_module = 'CBC' and
(igc.cc_header_id,igc.cc_acct_line_id) in
(SELECT pkt.document_header_id,pkt.document_distribution_id
FROM pa_bc_packets pkt
WHERE pkt.packet_id = p_packet_id
AND pkt.document_header_id = igc.cc_header_id
AND pkt.document_distribution_id = igc.cc_acct_line_id
AND pkt.document_type in ('CC_C_CO','CC_P_CO')
)));
log_message(p_msg_token1 =>'no of rows updated = '||sql%rowcount);
log_message(p_msg_token1 => 'failed in update gl cbc result code apiSQLERR :'||sqlcode||sqlerrm);
END update_GL_CBC_result_code;
SELECT project_id
FROM pa_bc_packets
WHERE packet_id = p_packet_id;
g_tab_project_id.delete;
result_status_code_update(
p_status_code => 'R',
p_result_code => 'F143',
p_res_result_code => 'F143',
p_res_grp_result_code => 'F143',
p_task_result_code => 'F143',
p_top_task_result_code => 'F143',
p_project_result_code => 'F143',
p_proj_acct_result_code => 'F143',
p_packet_id => p_packet_id);
result_status_code_update(
p_status_code => 'R',
p_result_code => 'F119',
p_res_result_code => 'F119',
p_res_grp_result_code => 'F119',
p_task_result_code => 'F119',
p_top_task_result_code => 'F119',
p_project_result_code => 'F119',
p_proj_acct_result_code => 'F119',
p_packet_id => p_packet_id);
result_status_code_update
(p_status_code => 'T',
p_packet_id => p_packet_id);
SELECT gl_bc_packets_s.nextval
FROM dual;
SELECT set_of_books_id
FROM pa_bc_packets
WHERE packet_id = v_packet_id;
-- unreserved packet update the status and return, in tie back process
-- update the budget_acct_balances
If l_mode in ('U') and p_calling_module in ('GL','CBC') then
If g_debug_mode = 'Y' then
log_message(p_msg_token1 => ' calling create_unrsvd_lines api');
-- the return status of gl and cbc update the status code
-- of packets
If p_calling_module = 'GL' then
g_pa_gl_return_status := l_packet_status;
-- update the result code in pa bc packets with error code
------------------------------------------------------------------------
If g_debug_mode = 'Y' then
log_message( p_stage => 30, p_msg_token1 => 'Check whether the project is under Baseline process');
-- Update ei and cdls with gl date, encum type id, budget ccid etc if the
-- funds check pass else update ei with cost dist reject code
--------------------------------------------------------------------------
If g_debug_mode = 'Y' then
log_message(p_stage => 90 ,p_msg_token1 => 'Calling update_EIS API');
update_EIS (p_packet_id => l_packet_id
,p_calling_module => p_calling_module
,p_mode => l_mode
,x_return_status => l_return_status);
log_message(p_msg_token1 => 'Failed to update EI and CDLs with status');
log_message(p_msg_token1 => 'End of update_EIS API');
log_message(p_stage => 130, p_msg_token1 => 'calling update status code for failed packet');
status_code_update (
p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_partial =>p_partial_flag
,p_packet_status => l_packet_status
,x_return_status => l_return_status
);
log_message(p_msg_token1 => 'Failed to update status codes');
log_message(p_stage => 140, p_msg_token1 =>'Calling update_GL_CBC_result_code API');
update_GL_CBC_result_code(
p_packet_id => l_packet_id
,p_calling_module => p_calling_module
,p_mode => l_mode
,p_partial_flag => p_partial_flag
,p_reference1 => p_reference1
,p_reference2 => p_reference2
,p_packet_status => l_packet_status
,x_return_status => l_return_status
);
log_message(p_msg_token1 => 'After update_GL_CBC_result_code api call');
log_message(p_msg_token1 => 'Failed to update result_code in GL /CBC ');
log_message(p_msg_token1 =>'End of update_GL_CBC_result_code APIl_packet_status['||l_packet_status);
log_message(p_stage => 140, p_msg_token1 =>'Calling update_GL_CBC_result_code API');
update_GL_CBC_result_code(
p_packet_id => l_packet_id
,p_calling_module => p_calling_module
,p_mode => l_mode
,p_partial_flag => p_partial_flag
,p_reference1 => p_reference1
,p_reference2 => p_reference2
,p_packet_status => 'F' -- failure
,x_return_status => l_return_status);
log_message(p_msg_token1 => 'After update_GL_CBC_result_code api call');
update_EIS (p_packet_id => l_packet_id
,p_calling_module => p_calling_module
,p_mode => l_mode
,x_return_status => l_return_status);
result_status_code_update
( p_packet_id => l_packet_id,
p_status_code => 'T',
p_result_code => 'F142',
p_res_result_code => 'F142',
p_res_grp_result_code => 'F142',
p_task_result_code => 'F142',
p_top_task_result_code => 'F142',
p_proj_acct_result_code => 'F142');
update_EIS(p_packet_id => l_packet_id
,p_calling_module => p_calling_module
,p_mode => l_mode
,x_return_status => l_return_status);
'Updateing EIS with rejection_code');
update_GL_CBC_result_code(
p_packet_id =>l_packet_id
,p_calling_module =>p_calling_module
,p_partial_flag => p_partial_flag
,p_reference1 => p_reference1
,p_reference2 => p_reference2
,p_mode =>l_mode
,p_packet_status => 'T'
,x_return_status => l_return_status);
SELECT 1
FROM gl_bc_packets a
WHERE a.packet_id = p_packet_id
AND EXISTS (
SELECT 'Y'
FROM gl_bc_packets b
WHERE b.status_code = 'T'
AND b.packet_id = a.packet_id
);
SELECT 1
FROM gl_bc_packets a
WHERE a.packet_id = p_packet_id
AND EXISTS (
SELECT 'Y'
FROM gl_bc_packets b
WHERE b.packet_id = a.packet_id
AND ((b.status_code in ('R','F','T')
AND substr(b.result_code,1,1) = ('F')
) OR
( b.status_code = 'T' )
)
);
SELECT 1
FROM gl_bc_packets a
WHERE a.packet_id = p_packet_id
AND EXISTS
(SELECT 'Y'
FROM gl_bc_packets b
WHERE b.status_code in ('S','A','P')
AND substr(b.result_code,1,1) IN ('P','A')
AND b.packet_id = a.packet_id
);
* status and return code should be updated in
* pa_bc_packets using autonmous transaction other wise
* it causes a deadlock while calling sync_raw_burden from tie back api
**/
PROCEDURE tie_back_status(p_calling_module in varchar2,
p_packet_id in number,
p_partial_flag in varchar2,
p_mode in varchar2,
p_glcbc_return_code in varchar2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE pa_bc_packets
SET result_code =
decode(p_calling_module,
'GL',
decode(p_partial_flag,
'Y',decode(p_mode,'C','F150','F156'),
'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F150',
'R','F151',
'T','F151')
,'R',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155')
,'A',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155')
,'F',decode(p_glcbc_return_code,'F','F155',
'R','F155',
'T','F155'))),
'CBC',
decode(p_partial_flag,
'Y',decode(p_mode,'C','F152','F158'),
'N',decode(p_mode,'C',decode(p_glcbc_return_code,'F','F152',
'R','F153',
'T','F153')
,'R',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157')
,'A',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157')
,'F',decode(p_glcbc_return_code,'F','F157',
'R','F157',
'T','F157'))))
WHERE packet_id = p_packet_id
AND substr(nvl(result_code,'P'),1,1) = 'P'
AND document_distribution_id = g_tab_src_dist_id_num_1(i)
AND (source_event_id = g_tab_gl_bc_event_id(i)
OR
bc_event_id = g_tab_gl_bc_event_id(i))
AND document_type = g_tab_src_dist_type(i);
log_message(p_msg_token1 => 'In tie_back_status, pa_bc_pkt records updated:'||SQL%ROWCOUNT);
UPDATE pa_bc_packets
SET result_code = decode(substr(nvl(result_code,'P'),1,1),'P','F151',result_code)
WHERE packet_id = p_packet_id;
* This is the Tie back api which updates the status of pa_bc_packets table after
* confirming the funds checking status of GL / Contract Commitments
*Parameters:
* P_packet_id : Packet Identifier of the funds check process
* P_mode :Funds Checker Operation Mode
* R - Reserve Default
* B - Base line
* C - Check
* P_calling_module :This holds the info of budget type
* GL --- Standard Default
* CBC --- Contract Commitments
* P_reference1 :This Param is not null in case of Contract Commitment
* If P_ext_bdgt_type = CBC
* This param holds the information of document type
* P_reference2 = Igc_cc_interface.document_type
* elsif p_mode = B then
* P_reference1 = project_id
* Else
* P_reference1 = NULL;
SELECT DISTINCT gl.source_distribution_id_num_1 distribution_id,
gl.event_id,
decode(gl.source_distribution_type,
'AP_INV_DIST','AP',
'AP_PREPAY','AP',
'PA_AP_BURDEN','AP',
'PO_DISTRIBUTIONS_ALL','PO',
'PA_PO_BURDEN','PO',
'PA_REQ_BURDEN','REQ',
'PO_REQ_DISTRIBUTIONS_ALL','REQ') source_distribution_type
FROM gl_bc_packets gl
WHERE gl.packet_id = v_packet_id
AND ( (nvl(substr(gl.result_code,1,1),'P') = 'F'
AND gl.status_code in ('F','R'))
OR (gl.status_code = 'T')
);
-- Note : No need to have partial flag logic here as procedure update_GL_CBC_result_code has already stamped
-- gl bc packets status code based on partial flag.
-- Output values :
-- return 'F' if all have failed --fail
-- return 'P' if some of the records have failed --partial
-- return 'S' if all have success --success
CURSOR gl_return_code IS
SELECT decode(count(*)
,count(decode(substr(nvl(gl.result_code,'P'),1,1),'P',1)),'S'
,count(decode(substr(nvl(gl.result_code,'P'),1,1),'F',1,'X',1)),'F'
,decode(p_partial_flag,'N','F','P')) -- Bug 5522810 : p_partial_flag is also checked before returning partial mode
FROM gl_bc_packets gl
WHERE gl.packet_id = p_packet_id;
SELECT igc.rowid,igc.reference_9
FROM igc_cc_interface igc
WHERE igc.document_type = p_reference1
AND igc.cc_header_id = p_reference2
AND ((nvl(substr(igc.cbc_result_code,1,1),'P') = 'F'
AND igc.status_code in ('F','R'))
OR (igc.status_code = 'T')
);
g_tab_src_dist_id_num_1.delete;
g_tab_gl_bc_event_id.delete;
g_tab_src_dist_type.delete;
g_tab_rowid.delete;
g_tab_tieback_id.delete;
-- After update of packet status based on GL return code if there are
-- any transactions failed in GL for raw line if not integrated
-- then we have to tie up raw and burden lines in partial mode
If p_calling_module in ('GL','CBC') and
( g_pa_gl_return_status = 'S' OR g_pa_cbc_return_status = 'S' )then
If g_debug_mode = 'Y' Then
log_message(p_msg_token1 => 'calling sync_raw_burden api in tie back');
log_message(p_msg_token1 => 'calling status_code_update api in tie back');
status_code_update (
p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_partial => p_partial_flag
,p_packet_status => l_gl_cbc_return_code
,x_return_status => l_return_status
);
log_message(p_msg_token1 => 'Failed to update status codes');
-- if the return status from gl/ cbc is success then update the budget
-- account balances
IF l_gl_cbc_return_code = 'S' and p_calling_module in ('GL','CBC')
and (g_pa_gl_return_status = 'S' OR g_pa_cbc_return_status = 'S' )
and l_mode in ('R','U','F') then
If g_debug_mode = 'Y' Then
log_message(p_msg_token1 => 'calling upd_bdgt_encum_bal api from tieback');
-- update the cwk attributes for the passed transactions
pa_funds_control_pkg1.upd_cwk_attributes
(p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_reference => 'UPD_AMTS'
,x_return_status => l_return_status
);
-- update the cwk compiled_multiplier
pa_funds_control_pkg1.upd_cwk_attributes(
p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_reference => 'UPD_MULTIPLIER'
,x_return_status => l_return_status
);
update_GL_CBC_result_code(
p_packet_id => l_packet_id,
p_calling_module => l_calling_module,
p_partial_flag => p_partial_flag,
p_reference1 => p_reference1,
p_reference2 => p_reference2,
p_mode => l_mode,
p_packet_status => l_gl_cbc_return_code,
x_return_status => l_return_status
);
* even though pa funds check updates the result code and status codes in gl
* it is being setting to null after funds check call
*/
--If p_calling_module = 'GL' and p_partial_flag = 'Y' and l_mode = 'C' then
-- commit;
select decode(p_gl_cbc_return_code,'S',decode(nvl(g_adv_result_code,'X'),'A','A',p_gl_cbc_return_code),p_gl_cbc_return_code)
into p_gl_cbc_return_code -- Added for bug 9156359
from dual;
result_status_code_update(p_status_code => 'T',
p_packet_id => l_packet_id);
* Moved the API from main fc process to tieback process to update the bdgt acct balance and status code
* Added for bug : 2961161 to update the status of packet if
* called in DISTVIADJ mode and packet status is success
*/
PROCEDURE tieback_pkt_status
(p_calling_module in varchar2
,p_packet_id in number
,p_partial_flag in varchar2 default 'N'
,p_mode in varchar2 default 'R'
,p_tieback_status in varchar2 default 'T' --'S' for Success, 'T' -- fatal Error
,p_request_id in number
,x_return_status OUT NOCOPY varchar2) IS
cursor curViPkts IS
SELECT distinct packet_id
FROM pa_bc_packets
WHERE request_id = p_request_id;
log_message(p_msg_token1 =>'Looping through each packet for the requestId to update the status');
log_message(p_msg_token1 => 'calling status_code_update in tie back['||l_packet_id||']');
status_code_update (
p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_partial => l_partial_flag
,p_packet_status => l_tieback_status
,x_return_status => x_return_status
);
log_message(p_msg_token1 => 'After StatuscodeUpdate return status['||x_return_status);
-- update the cwk attributes for the passed transactions
pa_funds_control_pkg1.upd_cwk_attributes
(p_calling_module => p_calling_module
,p_packet_id => l_packet_id
,p_mode => l_mode
,p_reference => 'UPD_AMTS'
,x_return_status => x_return_status
);
/* Bug 5589452 : Update to fail all AP/PO records in packet which are associated with related invoice distributions */
Update pa_bc_packets pbc
set pbc.result_code = 'F170'
where pbc.packet_id = p_packet_id
and substr(pbc.result_code,1,1) = 'P'
and pbc.document_type in ('PO','AP')
and ( decode (pbc.document_type , 'PO' , to_number(pbc.reference2) , 'AP' , pbc.document_header_id),
decode (pbc.document_type , 'PO' , to_number(pbc.reference3) , 'AP' , pbc.document_distribution_id)) IN
/** Select to fetch all related invoice distributions associated with a failed record in packet.
This sql fetches all invoice distributions linked to each other with charge_applicable_to_dist_id and related id**/
( select distinct b.invoice_id,b.invoice_distribution_id
from ap_invoice_distributions_all a
,ap_invoice_distributions_all b
where (a.invoice_id,a.invoice_distribution_id) in
/**select to fetch Invoice id and Inv distribution id associated with failed AP/PO records in a packet**/
(select DECODE(pbc1.document_type,'PO',to_number(pbc1.reference2),pbc1.document_header_id),
DECODE(pbc1.document_type,'PO',to_number(pbc1.reference3),pbc1.document_distribution_id)
from pa_bc_packets pbc1
where pbc1.packet_id = p_packet_id
and substr(pbc1.result_code,1,1) = 'F'
and pbc1.document_type in ('AP','PO')
and pbc1.parent_bc_packet_id is null)
and b.invoice_id = a.invoice_id
and COALESCE(b.charge_applicable_to_dist_id,b.related_id,b.invoice_distribution_id) =
COALESCE(a.charge_applicable_to_dist_id,a.related_id,a.invoice_distribution_id)
UNION /*Bug 13602288: Self Assessed Tax Changes - If one transaction is failed funds check, we need to fail all other transactions which are associated to that packet */
select distinct b.invoice_id,b.invoice_distribution_id
from ap_self_assessed_tax_dist_all a
,ap_self_assessed_tax_dist_all b
where (a.invoice_id,a.invoice_distribution_id) in
/**select to fetch Invoice id and Inv distribution id associated with failed AP/PO records in a packet**/
(select DECODE(pbc1.document_type,'PO',to_number(pbc1.reference2),pbc1.document_header_id),
DECODE(pbc1.document_type,'PO',to_number(pbc1.reference3),pbc1.document_distribution_id)
from pa_bc_packets pbc1
where pbc1.packet_id = p_packet_id
and substr(pbc1.result_code,1,1) = 'F'
and pbc1.document_type in ('AP','PO')
and pbc1.parent_bc_packet_id is null)
and b.invoice_id = a.invoice_id
and COALESCE(b.charge_applicable_to_dist_id,b.related_id,b.invoice_distribution_id) =
COALESCE(a.charge_applicable_to_dist_id,a.related_id,a.invoice_distribution_id)
);
select pbc.reference1,pbc.reference3
BULK COLLECT into tt_reference1,tt_reference3
from pa_bc_packets pbc
where pbc.packet_id = p_packet_id
and pbc.parent_bc_packet_id is null -- this is ok. as this proc. is fired after raw/burden synch
and ((pbc.document_type = 'PO' and
pbc.reference1 = 'AP' and
substr(pbc.result_code,1,1) = 'F')
OR
(pbc.document_type = 'AP' and
pbc.reference1 = 'PO' and
substr(pbc.result_code,1,1) = 'F')
);
Update pa_bc_packets pbc
set pbc.result_code = 'F170'
where pbc.packet_id = p_packet_id
and pbc.reference3 = tt_reference3(x) -- All rel matched AP dist. has same PO as ref3
and pbc.document_type = 'AP'
and substr(pbc.result_code,1,1) = 'P'
and tt_reference1(x) = 'PO'; -- AP record has PO as reference1
log_message(p_msg_token1 => 'Full_mode_failure: Rel. dist. fail, records updated:'||SQL%ROWCOUNT);
Update pa_bc_packets pbc
set pbc.result_code = 'F170'
where pbc.packet_id = p_packet_id
and pbc.document_distribution_id = tt_reference3(x)
and pbc.document_type = tt_reference1(x)
and substr(pbc.result_code,1,1) = 'P';
log_message(p_msg_token1 => 'Full_mode_failure: PO-AP full mode, records updated:'||SQL%ROWCOUNT);
tt_reference1.delete;
tt_reference3.delete;
Update pa_bc_packets pbc
set pbc.result_code = 'F173',
pbc.status_code = decode(p_mode,'C','F','R'),
pbc.packet_id = p_gl_packet_id
where pbc.packet_id = p_pa_packet_id
and pbc.bc_event_id = g_event_id(i)
and pbc.document_distribution_id = g_doc_dist_id(i)
and pbc.document_type = g_document_type(i);
Update pa_bc_packets pbc
set pbc.result_code = 'F170',
pbc.status_code = decode(p_mode,'C','F','R'),
pbc.packet_id = p_gl_packet_id
where pbc.packet_id = p_pa_packet_id
and substr(nvl(pbc.result_code,'P'),1,1) = 'P';
log_message(p_msg_token1 =>'no of rows Updated in missing records failure ='||sql%rowcount);
SELECT session_id,
serial_id ,event_id , source_distribution_type
FROM gl_bc_packets
WHERE packet_id = p_gl_packet_id
AND ROWNUM =1 ;
SELECT 'Y'
FROM dual
WHERE EXISTS(
select 1
from pa_bc_packets pbc1
where pbc1.packet_id = p_pa_packet_id
and pbc1.bc_event_id is null
and pbc1.document_type = 'PO'
and exists (select 1
from pa_bc_packets pbc2
where pbc2.packet_id = p_gl_packet_id
and pbc2.bc_event_id is null
and pbc2.document_type = 'AP'));
Update pa_bc_packets pb
set pb.packet_id = p_gl_packet_id,
pb.status_code = decode(pb.status_code,'I','P',pb.status_code),
pb.session_id = DECODE(pb.session_id,NULL,l_session_id,pb.session_id),
pb.serial_id = DECODE(pb.serial_id,NULL,l_serial_id,pb.serial_id)
where pb.packet_id = p_pa_packet_id --Bug 14408086 Added the condition to check event_id and document type
and ( pb.source_event_id in (select distinct event_id
from gl_bc_packets where packet_id = p_gl_packet_id)
or pb.bc_event_id in (select distinct event_id
from gl_bc_packets where packet_id = p_gl_packet_id)
);
log_message(p_msg_token1 =>'no of rows Updated ='||sql%rowcount);
Select pb.packet_id
from pa_bc_packets pb
where pb.bc_event_id in
(select glbc.event_id
from gl_bc_packets glbc
where glbc.packet_id = x_packet_id)
union all
Select pb.packet_id
from pa_bc_packets pb
where pb.source_event_id in
(select glbc.event_id
from gl_bc_packets glbc
where glbc.packet_id = x_packet_id);
Select pbc.bc_event_id,pbc.document_distribution_id,pbc.document_type
BULK COLLECT into g_event_id,g_doc_dist_id,g_document_type
from pa_bc_packets pbc
where packet_id = l_pa_packet_id
and pbc.bc_event_id is not null -- to filter out non-integrated budgets ...
group by pbc.bc_event_id,pbc.document_distribution_id,pbc.document_type
having count(pbc.bc_event_id) > (select count(glbc.event_id)
from gl_bc_packets glbc--,
--xla_distribution_links xlad
where glbc.packet_id = x_packet_id
and glbc.event_id = pbc.bc_event_id
and glbc.source_distribution_id_num_1 = pbc.document_distribution_id
and decode(glbc.source_distribution_type,
'PA_AP_BURDEN','AP',
'PA_PO_BURDEN','PO',
'PA_REQ_BURDEN','REQ','CC','CC_')||
decode(glbc.source_distribution_type,'CC',substr(pbc.document_type,4),'')
= pbc.document_type);
g_event_id.DELETE;
g_doc_dist_id.DELETE;
g_document_type.DELETE;
select null -- null is ok ..
into x_result_code
from dual
where exists
(Select 1
from pa_bc_packets
where packet_id = l_pa_packet_id
and status_code = 'I');
Select pbc.bc_event_id,pbc.document_distribution_id,pbc.document_type
BULK COLLECT into g_event_id,g_doc_dist_id,g_document_type
from pa_bc_packets pbc
where pbc.packet_id = l_pa_packet_id
and pbc.status_code = 'I'
and pbc.source_event_id is not null
and not exists (select 1
from gl_bc_packets glbc
where glbc.packet_id = x_packet_id
and glbc.event_id = pbc.source_event_id
and glbc.source_distribution_id_num_1 = pbc.document_distribution_id);
g_event_id.DELETE;
g_doc_dist_id.DELETE;
g_document_type.DELETE;
select null -- null is ok ..
into x_result_code
from dual
where exists
(Select 1
from pa_bc_packets
where packet_id = l_pa_packet_id
and status_code = 'I');
Select DISTINCT pbc.bc_event_id,pbc.document_distribution_id,pbc.document_type
BULK COLLECT into g_event_id,g_doc_dist_id,g_document_type
from pa_bc_packets pbc
where pbc.packet_id = l_pa_packet_id
and pbc.status_code = 'I'
and pbc.bc_event_id is not null
and (document_type not like 'CC%' or parent_bc_packet_id is not null) -- added for bug 10429325
and not exists (select glbc.source_distribution_id_num_1
from gl_bc_packets glbc
where glbc.packet_id = x_packet_id
and (glbc.event_id = pbc.bc_event_id
OR
glbc.event_id = pbc.source_event_id)
-- 2nd clause to take care of sep. line burdening
and glbc.source_distribution_id_num_1 = pbc.document_distribution_id
and (nvl(glbc.accounted_dr,0) - nvl(glbc.accounted_cr,0)) = -1 * (pbc.accounted_dr - pbc.accounted_cr)
);
g_event_id.DELETE;
g_doc_dist_id.DELETE;
g_document_type.DELETE;
select null -- null is ok ..
into x_result_code
from dual
where exists
(Select 1
from pa_bc_packets
where packet_id = l_pa_packet_id
and status_code = 'I');
select distinct pabc.project_id
from pa_bc_packets pabc
where pabc.packet_id = p_packet_id;
Update gl_bc_packets glbc
set status_code = 'P' -- Open Issue no 4 in DLD: Check if this is final ?????
where glbc.rowid in
(select pabc.gl_row_number
from pa_bc_packets pabc
where pabc.packet_id = p_packet_id
and pabc.project_id = x.project_id
and pabc.parent_bc_packet_id is null
);
Select 'TXN_FC'
into p_return_code
from dual
where exists
(select 1 from pa_bc_packets
where packet_id = p_packet_id);
Update pa_bc_packets
set status_code = 'R',
result_code = P_result_code
where budget_version_id = P_budget_version_id
and budget_ccid = P_budget_ccid(i)
and period_name = P_period_name(i)
and P_allow_flag(i) = 'N';
Update pa_bc_packets
set status_code = 'R',
result_code = 'F170'
where budget_version_id = P_budget_version_id
and status_code in ('P','A');
PROCEDURE Update_failure_in_acct_summary(P_budget_version_id IN NUMBER,
P_period_name IN g_tab_period_name%TYPE,
P_budget_ccid IN g_tab_budget_ccid%TYPE,
P_allow_flag IN g_tab_allow_flag%TYPE,
P_result_code IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
log_message(p_msg_token1=>'Update_failure_in_acct_summary:P_budget_version_id['||P_budget_version_id
||'], P_result_code['||P_result_code||']' );
log_message(p_msg_token1=>'Update_failure_in_acct_summary: No of records to fail:'||P_budget_ccid.COUNT);
Update pa_budget_acct_lines
set funds_check_status_code = 'R',
funds_check_result_code = P_result_code
where budget_version_id = P_budget_version_id
and code_combination_id = P_budget_ccid(i)
and gl_period_name = P_period_name(i)
and P_allow_flag(i) = 'N'
and P_budget_ccid(i) is not null;
log_message(p_msg_token1=>'Update_failure_in_acct_summary: Records Updated:'||SQL%ROWCOUNT);
log_message(p_msg_token1=>'Update_failure_in_acct_summary: Fail other records with F170');
Update pa_budget_acct_lines
set funds_check_status_code = 'R',
funds_check_result_code = 'F170'
where budget_version_id = P_budget_version_id
and nvl(funds_check_status_code,'P') <> 'R';
log_message(p_msg_token1=>'Update_failure_in_acct_summary: Records Updated:'||SQL%ROWCOUNT);
End Update_failure_in_acct_summary;
Update pa_bc_packets
set project_acct_result_code = 'P101',
result_code = 'P101'
where budget_version_id = P_budget_version_id;
log_message(p_msg_token1=>l_program_name||'Upd_bc_pkt_acct_result_code records updated:'||SQL%ROWCOUNT);
PROCEDURE Update_budget_ccid(P_budget_version_id IN NUMBER,
P_budget_ccid IN g_tab_budget_ccid%TYPE,
P_budget_line_id IN g_tab_budget_line_id%TYPE,
P_budget_entry_level_code IN VARCHAR2,
P_period_name IN g_tab_period_name%TYPE,
P_rlmi IN g_tab_rlmi%TYPE,
P_task_id IN g_tab_task_id%TYPE,
P_derived_ccid IN g_tab_budget_ccid%TYPE,
P_allowed_flag IN g_tab_allow_flag%TYPE,
P_result_code IN OUT NOCOPY VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
log_message(p_msg_token1=>'Update_budget_ccid: p_budget_entry_level_code['
||p_budget_entry_level_code||'] Null->Update new CCID else Synch' );
Update pa_bc_packets pbc
set pbc.budget_ccid = p_derived_ccid(x)
where pbc.budget_ccid = p_budget_ccid(x)
and pbc.period_name = p_period_name(x)
and p_allowed_flag(x) = 'Y';
Update pa_bc_packets pbc
set pbc.budget_ccid = p_budget_ccid(i),
pbc.budget_line_id = p_budget_line_id(i)
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_resource_list_member_id = p_rlmi(i)
and pbc.period_name = p_period_name(i);
Update pa_bc_packets pbc
set pbc.budget_ccid = p_budget_ccid(i),
pbc.budget_line_id = p_budget_line_id(i)
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_task_id = p_task_id(i)
and pbc.bud_resource_list_member_id = p_rlmi(i)
and pbc.period_name = p_period_name(i);
log_message(p_msg_token1=>'Update_budget_ccid on pa_bc_packets:Budget_ccid updated on:'
|| SQL%ROWCOUNT||' records');
Update pa_bc_packets pbc
set pbc.result_code = 'F132',
pbc.status_code = 'R'
where pbc.budget_version_id = p_budget_version_id
and pbc.budget_ccid is null;
log_message(p_msg_token1=>'Update_budget_ccid on pa_bc_packets:'
||' Records with null ccid'||SQL%ROWCOUNT||' If >0 then F132');
End Update_budget_ccid;
Select 'Y' into p_txn_exists_in_bc_pkt from dual where exists
(select 1 from pa_bc_packets where project_id = p_project_id and status_code in ('A','P','I'));
Select 'Y' into p_txn_exists_in_bc_cmt from dual where exists
(select 1 from pa_bc_commitments where project_id = p_project_id);
t_draft_needs_update pa_plsql_datatypes.char50TabTyp;
l_record_updated_flag VARCHAR2(1);
select pbl.code_combination_id,
pbl.budget_line_id,
pra.resource_list_member_id,
pra.task_id,
pbl.period_name --glps.period_name
from pa_budget_lines pbl,
pa_resource_assignments pra--,
--gl_period_statuses glps
where pra.budget_version_id = p_budget_version_id
and pra.project_id = p_project_id -- added to improve performance ..
and pra.budget_version_id = pbl.budget_version_id
and pra.resource_assignment_id = pbl.resource_assignment_id;
select pbl.code_combination_id budget_ccid,
pbl.period_name period_name,
'N' allow_flag ,
pra.project_id,
pra.task_id,
pra.resource_list_member_id rlmi,
pbl.start_date,
pbl.txn_currency_code
from gl_bc_packets glbc,
pa_budget_lines pbl,
pa_resource_assignments pra
where glbc.packet_id = p_packet_id
and pbl.budget_version_id = p_budget_version_id
and pbl.budget_line_id = glbc.source_distribution_id_num_1
and substr(nvl(glbc.result_code,'P'),1,1) = 'F'
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id;
select glbc.code_combination_id sla_ccid,
glbc.rowid gl_rowid,
pbl.code_combination_id budget_ccid,
pbl.resource_assignment_id budget_raid,
pbl.rowid budget_rowid,
pbl.start_date start_date,
pbl.period_name period_name,
'Y' allow_flag,
pbl.budget_line_id budget_line_id,
pbl.txn_currency_code txn_currency_code,
'N' draft_needs_update,
pra.project_id,
pra.task_id,
pra.resource_list_member_id,
nvl(prlm.parent_member_id,-99) parent_rlmi
from gl_bc_packets glbc,
pa_budget_lines pbl,
pa_resource_assignments pra,
pa_resource_list_members prlm
where glbc.packet_id = p_packet_id
and pbl.budget_version_id = p_budget_version_id
and pbl.budget_line_id = glbc.source_distribution_id_num_1
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and prlm.resource_list_member_id = pra.resource_list_member_id;
select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
from pa_budget_acct_lines pbl
where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
(select pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
from pa_budget_acct_lines pbl
where (pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date) in
(select distinct a.budget_version_id,a.budget_ccid,a.fc_start_date,a.fc_end_date
from pa_bc_packets a
where a.budget_version_id = p_current_bvid)
group by pbl.budget_version_id,pbl.code_combination_id,pbl.start_date,pbl.end_date
having sum(nvl(pbl.Curr_Ver_Available_Amount,0)) < 0
)
UNION
-- Bug 5206341 : Cursor to validate account level balances for Open and Closed periods
select pbl.gl_period_name, pbl.code_combination_id ,'N' allow_flag
from pa_budget_acct_lines pbl
where (pbl.budget_version_id,pbl.code_combination_id) in
(select pbl.budget_version_id,pbl.code_combination_id
from pa_budget_acct_lines pbl
where pbl.budget_version_id = p_current_bvid
group by pbl.budget_version_id,pbl.code_combination_id
having sum(nvl(pbl.Curr_Ver_Available_Amount,0)) < 0
)
AND PA_FUNDS_CONTROL_UTILS.CLOSED_PERIODS_EXISTS_IN_BUDG(p_current_bvid) ='Y' ;
select glbc.rowid,glbc.ae_header_id,glbc.ledger_id
from gl_bc_packets glbc
where glbc.event_id in
(select event_id from psa_bc_xla_events_gt);
Select 1 into l_dummy_value from dual
where exists (Select 1
from gl_bc_packets glbc,
pa_budget_lines pbl
where glbc.packet_id = p_packet_id
and pbl.budget_version_id = pa_budget_fund_pkg.g_cost_current_bvid
and pbl.budget_line_id = glbc.source_distribution_id_num_1);
Select pbv.budget_version_id
into l_draft_budget_version_id
from pa_budget_versions pbv
where (pbv.project_id,pbv.budget_type_code) in
(select project_id,budget_type_code
from pa_budget_versions
where budget_version_id = l_current_budget_version_id)
and pbv.budget_status_code = decode(pa_budget_fund_pkg.g_processing_mode,
'YEAR_END','W','S');
select pbem.entry_level_code
into l_budget_entry_level_code
from pa_budget_entry_methods pbem,
pa_budget_versions pbv
where pbv.budget_version_id = l_current_budget_version_id
and pbem.budget_entry_method_code = pbv.budget_entry_method_code;
l_record_updated_flag := 'N';
select project_id into l_project_id from pa_budget_versions
where budget_version_id = l_current_budget_version_id;
select set_of_books_id into l_set_of_books_id from pa_implementations;
log_message(p_msg_token1=>l_program_name||'Update budget info. on pa_bc_packets');
Update_budget_ccid(P_budget_version_id => l_current_budget_version_id,
P_budget_ccid => g_tab_budget_ccid,
P_budget_line_id => g_tab_budget_line_id,
P_budget_entry_level_code => l_budget_entry_level_code,
P_period_name => g_tab_period_name,
P_rlmi => g_tab_rlmi,
P_task_id => g_tab_task_id,
P_derived_ccid => t_ccid,
P_allowed_flag => g_tab_allow_flag,
P_result_code => P_return_status);
g_tab_budget_ccid.DELETE;
g_tab_budget_line_id.DELETE;
g_tab_rlmi.DELETE;
g_tab_task_id.DELETE;
g_tab_period_name.DELETE;
log_message(p_msg_token1=>l_program_name||'Update_budget_ccid complete, status is:'||p_return_status);
Select 'Y' into l_gl_failure_flag from dual where exists
(select 1 from gl_bc_packets
where packet_id = p_packet_id
and substr(nvl(result_code,'P'),1,1) = 'F');
select code_combination_id draft_ccid
into t_draft_ccid(x)
from pa_budget_lines bl,
pa_resource_assignments pra
where pra.budget_version_id = l_draft_budget_version_id
and pra.project_id = t_project_id(x)
and pra.task_id = t_task_id(x)
and pra.resource_list_member_id = t_rlmi(x)
and bl.resource_assignment_id = pra.resource_assignment_id
and bl.start_date = t_start_date(x)
and bl.txn_currency_code = t_txn_currency_code(x);
log_message(p_msg_token1=>l_program_name||': Update failure in draft acct. summary');
log_message(p_msg_token1=>l_program_name||': <> CF, Calling Update_failure_in_acct_summary');
UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
P_period_name => g_tab_period_name,
P_budget_ccid => t_draft_ccid,
P_allow_flag => g_tab_allow_flag,
P_result_code => l_result_code);
log_message(p_msg_token1=>l_program_name||': = CF, Calling Update_failure_in_acct_summary');
UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
P_period_name => g_tab_period_name,
P_budget_ccid => t_bud_ccid,
P_allow_flag => g_tab_allow_flag,
P_result_code => l_result_code);
t_bud_ccid.DELETE;
g_tab_period_name.DELETE;
g_tab_allow_flag.DELETE;
t_project_id.DELETE;
t_task_id.DELETE;
t_rlmi.DELETE;
t_start_date.DELETE;
t_txn_currency_code.DELETE;
if t_draft_ccid.exists(1) then t_draft_ccid.DELETE; end if;
log_message(p_msg_token1=>l_program_name||':Fail pa_bc_packets as GL Failed - F170 update');
t_draft_needs_update,
t_project_id,
t_task_id,
t_rlmi,
t_parent_rlmi
LIMIT l_limit;
t_draft_needs_update(x) := 'Y';
select top_task_id into t_top_task_id(x)
from pa_tasks
where task_id = t_task_id(x);
select pbl.code_combination_id into
t_prev_ver_ccid(x)
from pa_budget_lines pbl,
pa_resource_assignments pra
where pbl.start_date = t_budget_start_date(x)
and pbl.txn_currency_code = t_txn_currency_code(x)
and pbl.budget_version_id = pa_budget_fund_pkg.g_cost_prev_bvid
and pbl.budget_version_id = pra.budget_version_id
and pbl.resource_assignment_id = pra.resource_assignment_id
and pra.project_id = t_project_id(x)
and (pra.task_id = t_task_id(x) OR
pra.task_id = t_top_task_id(x))
and (pra.resource_list_member_id= t_rlmi(x) OR
pra.resource_list_member_id= t_parent_rlmi(x));
select pbl.rowid,pbl.code_combination_id into
t_draft_budget_rowid(x),t_draft_ccid(x)
from pa_budget_lines pbl,
pa_resource_assignments pra
where pbl.start_date = t_budget_start_date(x)
and pbl.txn_currency_code = t_txn_currency_code(x)
and pbl.budget_version_id = pra.budget_version_id
and pbl.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = l_draft_budget_version_id
and pra.project_id = t_project_id(x)
and pra.task_id = t_task_id(x)
and pra.resource_list_member_id= t_rlmi(x);
If t_draft_needs_update(x) = 'N' then
t_draft_budget_rowid(x) := null;
End If; --If t_draft_needs_update(x) = 'Y' then
Update gl_bc_packets glbc
set glbc.result_code = 'F35'
where glbc.packet_id = p_packet_id
and glbc.source_distribution_id_num_1 = g_tab_budget_line_id(x)
and g_tab_allow_flag(x) = 'N';
log_message(p_msg_token1=>l_program_name||SQL%ROWCOUNT||' GL line updated');
log_message(p_msg_token1=>l_program_name||': Acct. change. val. failed(F169) - Update draft account summary');
UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
P_period_name => g_tab_period_name,
P_budget_ccid => t_draft_ccid,
P_allow_flag => g_tab_allow_flag,
P_result_code => 'F169');
UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
P_period_name => g_tab_period_name,
P_budget_ccid => t_bud_ccid,
P_allow_flag => g_tab_allow_flag,
P_result_code => 'F169');
log_message(p_msg_token1=>l_program_name||': Update changed account info on budget lines'
||'l_acct_changed['||l_acct_changed||'] l_validation_failed['
||l_validation_failed||']' );
Update pa_budget_lines pbl
set pbl.code_combination_id = t_ccid(x)
where pbl.rowid = t_budget_rowid(x)
and t_bud_ccid(x) <> t_ccid(x)
and g_tab_allow_flag(x) = 'Y';
||' budget lines updated for current version' );
log_message(p_msg_token1=>l_program_name||': Update new account on draft summary'
||': l_record_updated_flag:'||l_record_updated_flag);
p_record_updated => l_record_updated_flag);
If l_record_updated_flag = 'Y' then
l_draft_acct_changed_flag := 'Y';
log_message(p_msg_token1=>l_program_name||SQL%ROWCOUNT||' budget line records updated');
t_ccid.DELETE;
t_bud_ccid.DELETE;
t_gl_rowid.DELETE;
t_budget_rowid.DELETE;
t_raid.DELETE;
t_budget_start_date.DELETE;
g_tab_period_name.DELETE;
g_tab_allow_flag.DELETE;
g_tab_budget_line_id.DELETE;
t_txn_currency_code.DELETE;
t_project_id.DELETE;
t_task_id.DELETE;
t_rlmi.DELETE;
t_parent_rlmi.DELETE;
if t_top_task_id.exists(1) then t_top_task_id.DELETE; end if;
if t_prev_ver_ccid.exists(1) then t_prev_ver_ccid.DELETE; end if;
if t_draft_needs_update.exists(1) then t_draft_needs_update.DELETE; end if;
if t_draft_ccid.exists(1) then t_draft_ccid.DELETE; end if;
if t_draft_budget_rowid.exists(1) then t_draft_budget_rowid.DELETE; end if;
log_message(p_msg_token1=>l_program_name||':Fail pa_bc_packets as account info. changed - F170 update');
Update gl_bc_packets glbc
set glbc.result_code = 'F35'
where glbc.packet_id = p_packet_id
and glbc.code_combination_id = g_tab_budget_ccid(x)
and glbc.period_name = g_tab_period_name(x);
log_message(p_msg_token1=>l_program_name||SQL%ROWCOUNT||' GL line updated');
UPDATE_FAILURE_IN_ACCT_SUMMARY(P_budget_version_id => l_draft_budget_version_id,
P_period_name => g_tab_period_name,
P_budget_ccid => g_tab_budget_ccid,
P_allow_flag => g_tab_allow_flag,
P_result_code => 'F113');
g_tab_budget_ccid.DELETE;
g_tab_period_name.DELETE;
g_tab_allow_flag.DELETE;
log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Current');
Update pa_budget_acct_lines
set funds_check_status_code = 'A',
funds_check_result_code = 'P101'
where budget_version_id = l_current_budget_version_id
and nvl(funds_check_status_code,'A') <> 'R';
log_message(p_msg_token1=>l_program_name||'Current Acct Summary-P101 update:'||SQL%ROWCOUNT);
log_message(p_msg_token1=>'End:'||l_program_name||'Update Pass status/result code on pa_budget_acct_lines - Draft');
Update pa_budget_acct_lines
set funds_check_status_code = 'A',
funds_check_result_code = 'P101'
where budget_version_id = l_draft_budget_version_id;
log_message(p_msg_token1=>l_program_name||'Draft Acct Summary-P101 update:'||SQL%ROWCOUNT);
log_message(p_msg_token1=>l_program_name||'Update acct level result code on pa_bc_packets');
select pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.code_combination_id,
sum(decode(nvl(p_balance_type,'X'),
'E', decode(NVL(pbl.Burdened_Cost,0),
0,nvl(pbl.raw_cost,0),
pbl.burdened_cost ) ,
'B',decode(p_budget_amount_code,
'R',nvl(pbl.revenue,0) ,
'C', decode(NVL(pbl.Burdened_Cost,0),
0,nvl(pbl.raw_cost,0),
pbl.burdened_cost ),
0 ),
0 )) total_amount
from pa_budget_lines pbl
where budget_version_id = p_budget_version_id
group by pbl.period_name,
pbl.start_date,
pbl.end_date,
pbl.code_combination_id;
Delete from PA_Budget_Acct_Lines
where budget_version_id = P_budget_version_id;
log_message(p_msg_token1=>l_program_name||'Deleted '||SQL%ROWCOUNT||' PA_Budget_Acct_Lines records');
INSERT INTO PA_Budget_Acct_Lines (
Budget_Acct_Line_ID,
Budget_version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
Accounted_Amount,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Request_ID,
funds_check_status_code,
funds_check_result_code)
VALUES(PA_Budget_Acct_Lines_S.NEXTVAL, -- Budget acct line id
P_budget_version_id, -- Budget version id
t_period_name(i), -- Period name
t_start_date(i), -- Start date
t_end_date(i), -- End date
t_ccid(i), -- CCID
0, -- Prev. version bud. amt
0, -- Prev. version avail.amt
t_amt(i), -- Curr. version bud. amt
0, -- Curr. version avail.amt
0, -- Accounted amount
l_date, -- Last update date
l_login, -- Last update by
l_date, -- Created date
l_login, -- Created by
l_login, -- Last update login
l_request, -- Request
'A', -- funds_check_status_code
'P101'); -- funds_check_result_code
t_period_name.DELETE;
t_start_date.DELETE;
t_end_date.DELETE;
t_ccid.DELETE;
t_amt.DELETE;
log_message(p_msg_token1=>l_program_name||' After PA_Budget_Acct_Lines Insert DML');
Update PA_Budget_Acct_Lines pbl
set (pbl.Prev_Ver_Budget_Amount,pbl.Prev_Ver_Available_Amount) =
(select pbl1.Curr_Ver_Budget_Amount,
pbl1.Curr_Ver_Available_Amount
from PA_Budget_Acct_Lines pbl1
where pbl1.budget_version_id = P_prev_budget_version_id
and pbl1.code_combination_id = pbl.code_combination_id
and pbl1.start_date = pbl.start_date)
where pbl.budget_version_id = p_budget_version_id
and exists
(select 1
from PA_Budget_Acct_Lines pbl2
where pbl2.budget_version_id = P_prev_budget_version_id
and pbl2.code_combination_id = pbl.code_combination_id
and pbl2.start_date = pbl.start_date);
Update PA_Budget_Acct_Lines
set Curr_Ver_Available_Amount = Curr_Ver_Budget_Amount -
(Prev_Ver_Budget_Amount - Prev_Ver_Available_Amount),
Accounted_Amount = Curr_Ver_Budget_Amount - Prev_Ver_Budget_Amount
-- Accounted_Amount = Prev_Ver_Budget_Amount - Curr_Ver_Budget_Amount
where budget_version_id = p_budget_version_id;
INSERT INTO PA_BUDGET_ACCT_LINES (
Budget_Acct_Line_ID,
Budget_Version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
accounted_amount,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
funds_check_status_code,
funds_check_result_code
)
SELECT
PA_BUDGET_ACCT_LINES_S.nextval,
P_Budget_Version_ID, -- current version
BL1.GL_Period_Name,
BL1.Start_Date,
BL1.End_Date,
BL1.Code_Combination_ID,
BL1.Curr_Ver_Budget_Amount,
BL1.Curr_Ver_Available_Amount,
0,
0,
0 - BL1.Curr_Ver_Budget_Amount,
l_date,
l_login,
l_login,
l_date,
l_login,
l_request,
'A',
'P101'
FROM PA_BUDGET_ACCT_LINES BL1
WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
AND NOT EXISTS
( SELECT 'x'
FROM PA_BUDGET_ACCT_LINES BL2
WHERE BL2.Budget_Version_ID = P_budget_version_id
AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
AND BL2.Start_Date = BL1.Start_Date ) ;
INSERT INTO PA_BUDGET_ACCT_LINES (
Budget_Acct_Line_ID,
Budget_Version_ID,
GL_Period_Name,
Start_Date,
End_Date,
Code_Combination_ID,
Prev_Ver_Budget_Amount,
Prev_Ver_Available_Amount,
Curr_Ver_Budget_Amount,
Curr_Ver_Available_Amount,
accounted_amount,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
funds_check_status_code,
funds_check_result_code
)
SELECT
PA_BUDGET_ACCT_LINES_S.nextval,
P_Budget_Version_ID, -- current version
BL1.GL_Period_Name,
BL1.Start_Date,
BL1.End_Date,
BL1.Code_Combination_ID,
BL1.Curr_Ver_Budget_Amount,
BL1.Curr_Ver_Available_Amount,
0,
0,
0 - BL1.Curr_Ver_Budget_Amount,
l_date,
l_login,
l_login,
l_date,
l_login,
l_request,
'A',
'P101'
FROM PA_BUDGET_ACCT_LINES BL1
WHERE BL1.Budget_Version_ID = P_prev_budget_version_id
AND (BL1.Curr_Ver_Budget_Amount <> 0 OR
BL1.Prev_Ver_Budget_Amount <> 0)
-- this is to filter the zero $ lines ...
AND NOT EXISTS
( SELECT 'x'
FROM PA_BUDGET_ACCT_LINES BL2
WHERE BL2.Budget_Version_ID = P_budget_version_id
AND BL2.Code_Combination_ID = BL1.Code_Combination_ID
AND BL2.Start_Date = BL1.Start_Date ) ;
Select gl_period_name,
code_combination_id,
funds_check_result_code
BULK COLLECT
into tt_gl_period_name,
tt_code_combination_id,
tt_result_code
from pa_budget_acct_lines
where budget_version_id = X_budget_version_id
and funds_check_result_code like 'F%';
update pa_budget_acct_lines
set funds_check_result_code = tt_result_code(x)
where budget_version_id = X_budget_version_id
and code_combination_id = tt_code_combination_id(x)
and gl_period_name = tt_gl_period_name(x);
tt_gl_period_name.DELETE;
tt_code_combination_id.DELETE;
tt_result_code.DELETE;
Update pa_budget_acct_lines
set funds_check_status_code = 'A', --null,
funds_check_result_code = 'P101' --null
where budget_version_id = p_budget_version_id;
p_record_updated OUT NOCOPY Varchar2)
IS
l_count number;
Update pa_budget_lines pbl
set pbl.code_combination_id = p_new_ccid(x)
where pbl.rowid = p_budget_line_rowid(x)
and p_budget_ccid(x) <> p_new_ccid(x)
and p_change_allowed(x) = 'Y'
and p_budget_line_rowid(x) is not null;
log_message(p_msg_token1=>'Upd_new_acct_on_draft_budget:Acct. updated on:'||l_count || ' records');
p_record_updated := 'Y';
p_record_updated := 'N';
select glbc.rowid,glbc.ae_header_id,glbc.ledger_id
from gl_bc_packets glbc
where glbc.event_id in
(select event_id from psa_bc_xla_events_gt);
select 'Y'
into l_baseline_failed
from dual
where exists
(select packet_id
from gl_bc_packets glbc
where glbc.event_id in
(select event_id from psa_bc_xla_events_gt)
and glbc.result_code like 'F%');
Update gl_bc_packets glbc
set glbc.result_code = decode(substr(glbc.result_code,1,1),'F',glbc.result_code,'F35'),
glbc.status_code = decode(pa_budget_fund_pkg.g_processing_mode,
'CHECK_FUNDS','F','R')
where rowid = t_glrowid(i);
log_message(p_msg_token1=>l_program_name||'GL packets, records updated['||sql%rowcount||']');
UPDATE xla_ae_headers_gt
SET funds_status_code = decode(pa_budget_fund_pkg.g_processing_mode,
'CHECK_FUNDS','F','R')
WHERE ae_header_id = t_ae_header_id(i)
AND ledger_id = t_ledger_id(i);
log_message(p_msg_token1=>l_program_name||'xla_ae_headers_gt, records updated['||sql%rowcount||']');
UPDATE xla_validation_lines_gt
SET funds_status_code = 'F77'
WHERE ae_header_id = t_ae_header_id(i);
log_message(p_msg_token1=>l_program_name||'xla_validation_lines_gt, records updated['||sql%rowcount||']');
t_glrowid.delete;
t_ae_header_id.delete;
t_ledger_id.delete;