[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT t.cost_ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO l_sch_id ,l_sch_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.cost_ind_rate_sch_id = irs.ind_rate_sch_id
AND irs.cost_ovr_sch_flag = 'Y';
-- Select the Task level schedule override if not found
-- then select the Project level override
SELECT irs.ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO l_sch_id,l_sch_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.task_id = irs.task_id
AND irs.cost_ovr_sch_flag = 'Y';
-- Select the project level sch override
BEGIN
SELECT irs.ind_rate_sch_id,
p.cost_ind_sch_fixed_date
INTO l_sch_id,l_sch_date
FROM pa_tasks t,
pa_projects_all p,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.project_id = p.project_id
AND t.project_id = irs.project_id
AND irs.cost_ovr_sch_flag = 'Y'
AND irs.task_id is null;
-- select the schedule at the task
BEGIN
SELECT t.cost_ind_rate_sch_id,
t.cost_ind_sch_fixed_date
INTO l_sch_id ,l_sch_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.cost_ind_rate_sch_id = irs.ind_rate_sch_id;
SELECT SUM(NVL(cm.compiled_multiplier,0))
INTO l_multiplier
FROM pa_ind_rate_sch_revisions irsr,
pa_cost_base_exp_types cbet,
pa_compiled_multipliers cm
WHERE irsr.ind_rate_sch_revision_id = l_sch_rev_id
AND cbet.cost_plus_structure = irsr.cost_plus_structure
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = p_exp_type
AND cm.cost_base = cbet.cost_base
AND cm.ind_compiled_set_id = l_compile_set_id
AND cm.compiled_multiplier <> 0 ;
-- SELECT SUM(cm.compiled_multiplier)
-- INTO l_multiplier
-- FROM
-- pa_ind_rate_sch_revisions irsr,
-- pa_cost_bases cb,
-- pa_expenditure_types et,
-- pa_ind_cost_codes icc,
-- pa_cost_base_exp_types cbet,
-- pa_ind_rate_schedules_all_bg irs,
-- pa_ind_compiled_sets ics,
-- pa_compiled_multipliers cm
-- WHERE irsr.cost_plus_structure = cbet.cost_plus_structure
-- AND cb.cost_base = cbet.cost_base
-- AND cb.cost_base_type = 'INDIRECT COST' /*cbet.cost_base_type changed the order */
-- AND et.expenditure_type = icc.expenditure_type
-- AND icc.ind_cost_code = cm.ind_cost_code
-- AND cbet.cost_base = cm.cost_base
-- AND cbet.cost_base_type = cb.cost_base_type /* 'INDIRECT COST' changed the order */
-- AND cbet.expenditure_type = p_exp_type
-- AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id
-- AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
-- AND irsr.ind_rate_sch_revision_id = l_sch_rev_id /* Bug fix :2795051 to make use of index */
-- AND ics.organization_id = p_exp_org_id
-- AND cm.ind_compiled_set_id = ics.ind_compiled_set_id
-- AND cm.compiled_multiplier <> 0
-- AND ics.ind_compiled_set_id = l_compile_set_id
-- AND ics.cost_base = cb.cost_base; -- added for burdening enhancements
SELECT NVL(cdl.burdened_cost,cdl.amount)
+ NVL(PROJFUNC_BURDENED_CHANGE,0) /* added for Burdening Enhanceents */
INTO l_burdened_cost
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei,
pa_transaction_sources pts
WHERE
cdl.expenditure_item_id = p_exp_item_id
AND cdl.line_num = p_line_num
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND ei.cost_dist_rejection_code is NULL
AND cdl.line_type ='R'
AND ei.system_linkage_function <> 'BTC'
AND NVL(ei.cost_distributed_flag,'N') = 'Y'
AND (ei.transaction_source = pts.transaction_source (+)
AND nvl(pts.cost_burdened_flag,'N') <> 'Y');
SELECT period_name
,start_date
,end_date
,closing_status
FROM gl_period_statuses
WHERE application_id = 101
AND adjustment_period_flag = 'N'
AND set_of_books_id = p_set_of_books_id
AND ( (start_date between trunc(p_start_date) and trunc(p_end_date)
AND end_date between trunc(p_start_date) and trunc(p_end_date)
)
OR (
trunc(p_start_date) between start_date and end_date
or trunc(p_end_date) between start_date and end_date
)
)
AND closing_status in ('C','O','P');
x_tab_pds.delete;
SELECT encumbrance_type_id
FROM gl_encumbrance_types
WHERE encumbrance_type_key = 'Projects';
select pbl.code_combination_id, pbl.budget_line_id
into x_budget_ccid, x_budget_line_id
from pa_resource_assignments pra,
pa_budget_lines pbl
where ((p_entry_level_code = 'P' and
pra.task_id = 0)
or
(p_entry_level_code in ('L','M','T') and
pra.task_id in (p_task_id,p_top_task_id)))
and pra.budget_version_id = p_budget_version_id
and pra.project_id = p_project_id
and pra.resource_list_member_id = p_res_list_mem_id
and pra.resource_assignment_id = pbl.resource_assignment_id
and trunc(pbl.start_date) = trunc(p_start_date);
select time_phased_type_code
into x_time_phased_type_code
from pa_budget_entry_methods a,
pa_budget_versions b
where a.budget_entry_method_code = b.budget_entry_method_code
and b.budget_version_id = p_budget_version_id;
select pbv.budget_version_id, pbv.resource_list_id, pbm.entry_level_code
into x_base_version_id,x_res_list_id,x_entry_level_code
from pa_budget_versions pbv,
--pa_budget_types pbt,
pa_budget_entry_methods pbm,
pa_budgetary_control_options pbco
where pbv.project_id = p_project_id
and pbv.current_flag = 'Y'
and pbv.budget_status_code = 'B'
and pbv.budget_type_code = pbco.budget_type_code
and pbv.project_id = pbco.project_id
and pbco.bdgt_cntrl_flag = 'Y'
and ((p_calling_mode = 'GL' and pbco.external_budget_code = 'GL')
or
(p_calling_mode = 'CC' and pbco.external_budget_code = 'CC')
or
(p_calling_mode = 'GL' and pbco.external_budget_code is null))
--and pbv.budget_type_code = pbt.budget_type_code
--and pbt.budget_amount_code = 'C'
and pbv.budget_entry_method_code = pbm.budget_entry_method_code;
--select bal.Curr_Ver_Available_Amount
--into x_avail_balance
--from pa_budget_acct_lines bal,
-- gl_period_statuses gps
--where trunc(gps.start_date) = trunc(p_start_date)
--and trunc(gps.end_date) = trunc(p_end_date)
--and gps.period_name = bal.gl_period_name
--and gps.application_id = 101
--and bal.budget_version_id = p_budget_version_id
--and bal.code_combination_id = p_budget_ccid;
SELECT sum(nvl(bal.Curr_Ver_Available_Amount,0))
INTO x_avail_balance
FROM pa_budget_acct_lines bal
WHERE bal.budget_version_id = p_budget_version_id
AND bal.code_combination_id = p_budget_ccid
AND start_date between trunc(p_start_date) and trunc(p_end_date)
AND end_date between trunc(p_start_date) and trunc(p_end_date);
select 1 into l_count
from pa_budget_versions pbv,
pa_budget_types pbt
where pbv.project_id = p_project_id
and pbv.budget_status_code = 'B'
and pbv.budget_type_code = pbt.budget_type_code
and pbt.budget_amount_code = 'C'
and rownum = 1;
select pbv.budget_version_id,pbv.project_id
into g_current_baseline_bvid,g_api_project_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 = p_budget_version_id)
and pbv.budget_status_code = 'B'
and pbv.current_flag = 'Y';
select 'Y'
into g_txn_exists_for_bvid
from dual
where exists (select 1
from pa_bc_balances pbb
where pbb.budget_version_id = g_current_baseline_bvid
and pbb.project_id = g_api_project_id
and pbb.balance_type <> 'BGT');
select 'Y'
into g_txn_exists_for_bvid
from dual
where exists (select 1
from pa_bc_packets pbb
where pbb.project_id = g_api_project_id
and pbb.status_code in ('A','P','I','Z') );
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 = P_budget_version_id
and pbem.budget_entry_method_code = pbv.budget_entry_method_code;
Select 'T'
into l_budget_entry_level_code
from pa_resource_assignments pra,
pa_tasks pt
where pra.resource_assignment_id = P_resource_assignment_id
and pra.budget_version_id = P_budget_version_id
and pt.task_id = pra.task_id
and pt.top_task_id = pra.task_id;
select task_id,resource_list_member_id
into l_task_id,l_rlmi
from pa_resource_assignments pra
where pra.resource_assignment_id = P_resource_assignment_id;
select nvl(parent_member_id,-99) into g_api_parent_rlmi
from pa_resource_list_members prlm where resource_list_member_id = l_rlmi;
select top_task_id into g_api_top_task_id
from pa_tasks where task_id = l_task_id;
select distinct start_date into l_start_date from pa_budget_lines
where budget_version_id = p_budget_version_id
and resource_assignment_id = P_resource_assignment_id
and period_name = l_period_name;
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.task_id = pra.task_id);
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_commitments_all pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.task_id = pra.task_id);
Select 'N'
into l_return_status
from pa_resource_assignments pra,
pa_budget_lines pbl
where pbl.budget_version_id = p_budget_version_id
and pbl.resource_assignment_id = p_resource_assignment_id
and pbl.period_name = P_period_name
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = pra.budget_version_id
and pbb.project_id = pra.project_id
and pbb.task_id = pra.task_id
and pbb.resource_list_member_id = pra.resource_list_member_id
and trunc(pbb.start_date) = trunc(pbl.start_date)
and pbb.balance_type <> 'BGT');
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = pra.budget_version_id
and pbc.project_id = pra.project_id
and pbc.task_id = pra.task_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P') );
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.top_task_id = pra.task_id);
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_commitments_all pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.top_task_id = pra.task_id);
Select 'N'
into l_return_status
from pa_resource_assignments pra,
pa_budget_lines pbl
where pbl.budget_version_id = p_budget_version_id
and pbl.resource_assignment_id = p_resource_assignment_id
and pbl.period_name = P_period_name
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = pra.budget_version_id
and pbb.project_id = pra.project_id
and pbb.top_task_id = pra.task_id
and pbb.resource_list_member_id = pra.resource_list_member_id
and trunc(pbb.start_date) = trunc(pbl.start_date)
and pbb.balance_type <> 'BGT');
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = pra.budget_version_id
and pbc.project_id = pra.project_id
and pbc.top_task_id = pra.task_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P') );
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name);
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_commitments_all pbc
where pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name);
Select 'N'
into l_return_status
from pa_resource_assignments pra,
pa_budget_lines pbl
where pbl.budget_version_id = p_budget_version_id
and pbl.resource_assignment_id = p_resource_assignment_id
and pbl.period_name = P_period_name
and pra.resource_assignment_id = pbl.resource_assignment_id
and pra.budget_version_id = pbl.budget_version_id
and exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = pra.budget_version_id
and pbb.project_id = pra.project_id
and pbb.resource_list_member_id = pra.resource_list_member_id
and trunc(pbb.start_date) = trunc(pbl.start_date)
and pbb.balance_type <> 'BGT');
Select 'N'
into l_return_status
from pa_resource_assignments pra
where pra.budget_version_id = p_budget_version_id
and pra.resource_assignment_id = p_resource_assignment_id
and exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = pra.budget_version_id
and pbc.project_id = pra.project_id
and pbc.resource_list_member_id = pra.resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P') );
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and pbb.task_id = p_task_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_task_id = p_task_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P','I','Z') );
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and pbb.top_task_id = p_top_task_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_task_id = p_top_task_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P','I','Z') );
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code in ('A','P','I','Z') );
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_task_id = p_task_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code = 'A');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and pbb.task_id = p_task_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_task_id = p_top_task_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code = 'A');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and pbb.top_task_id = p_top_task_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_packets pbc
where pbc.budget_version_id = p_budget_version_id
and pbc.bud_resource_list_member_id = p_resource_list_member_id
and pbc.period_name = p_period_name
and pbc.status_code = 'A');
Select 'N'
into l_allowed_flag
from dual
where exists(select 1
from pa_bc_balances pbb
where pbb.budget_version_id = p_budget_version_id
and nvl(pbb.parent_member_id,-99) = nvl(p_parent_resource_id,-99)
and pbb.resource_list_member_id = p_resource_list_member_id
and pbb.start_date = p_start_date
and pbb.balance_type <> 'BGT');
SELECT 'UPGRADED'
FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND bc_event_id IS NOT NULL
AND rownum = 1;
SELECT 'Y'
FROM PA_BUDGET_ACCT_LINES PBA,
GL_PERIOD_STATUSES GLS
WHERE GLS.application_id = 101
AND GLS.set_of_books_id in (SELECT set_of_books_id FROM pa_implementations)
AND GLS.period_name = PBA.gl_period_name
AND GLS.closing_status = 'C'
AND PBA.budget_version_id = p_budget_version_id
AND rownum = 1;
PROCEDURE Update_bvid_blid_on_cdl_bccom ( p_bud_ver_id IN NUMBER,
p_calling_mode IN VARCHAR2) IS
l_DocHdrTab PA_PLSQL_DATATYPES.IdTabTyp;
SELECT bc.document_header_id,
bc.document_distribution_id,
bc.bc_commitment_id,
bc.project_id,
bc.task_id,
bc.top_task_id,
bc.resource_list_member_id,
NULL start_date, -- Required only for closed period transactions
bc.burden_method_code,
NULL entry_level_code, -- Required only for closed period transactions
bc.budget_version_id,
bc.budget_line_id,
NULL gl_period_status -- Required only for closed period transactions
from pa_bc_packets bc
WHERE bc.budget_version_id = p_bud_ver_id -- current baselined version id
AND bc.status_code ='A'
-- Parent bc packet id will be -99 for BTC and CWK lines --check logic in PA_BGT_BASELINE_PKG
AND NVL(bc.parent_bc_packet_id,-99) = -99
AND bc.actual_flag ='A'
AND bc.document_type ='EXP'
UNION ALL
-- Bug 5206341 : Cursor to pick transactions associated with last baselined version and which were not picked in current
-- run as the GL period has been closed.
SELECT bc.exp_item_id,
to_number(bc.reference3),
bc.bc_commitment_id,
bc.project_id,
bc.task_id,
bc.top_task_id,
bc.resource_list_member_id,
gl.start_date,
bc.burden_method_code,
BEM.entry_level_code,
p_bud_ver_id budget_version_id,
NULL budget_line_id,
'C' gl_period_status
FROM pa_bc_commitments bc,
pa_budget_versions pbv,
pa_budget_entry_methods bem,
gl_period_statuses gl
WHERE GL.application_id = 101
AND GL.set_of_books_id = bc.set_of_books_id
AND gl.period_name = bc.period_name
AND GL.closing_status = 'C'
AND bc.budget_version_id = pbv.budget_version_id
AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code
AND pbv.budget_version_id = pa_budget_fund_pkg.g_cost_prev_bvid
AND l_closed_prd_exists = 'Y';
SELECT bc.exp_item_id,
to_number(bc.reference3),
bc.bc_commitment_id,
bc.project_id,
bc.task_id,
bc.top_task_id,
bc.resource_list_member_id,
gl.start_date,
bc.burden_method_code,
BEM.entry_level_code,
p_bud_ver_id budget_version_id,
NULL budget_line_id,
DECODE(pbv.budget_version_id,pa_budget_fund_pkg.g_cost_prev_bvid,'C',NULL) gl_period_status
from pa_bc_commitments bc,
pa_budget_versions pbv,
pa_budget_entry_methods bem,
gl_period_statuses gl
WHERE GL.application_id = 101
AND GL.set_of_books_id = bc.set_of_books_id
AND gl.period_name = bc.period_name
AND GL.closing_status = DECODE(pbv.budget_version_id,pa_budget_fund_pkg.g_cost_prev_bvid,'C',GL.closing_status)
AND bc.budget_version_id = pbv.budget_version_id
AND BEM.Budget_Entry_Method_Code = PBV.Budget_Entry_Method_Code
AND pbv.budget_version_id in (SELECT p_bud_ver_id
FROM dual
UNION ALL
-- Bug 5206341 :Transactions in closed period are picked for latest budget details stamping
SELECT pa_budget_fund_pkg.g_cost_prev_bvid
FROM dual
WHERE l_closed_prd_exists = 'Y');
l_DocHdrTab.delete;
l_DocDistTab.delete;
l_bccomidTab.delete;
l_bvidTab.delete;
l_blidTab.delete;
l_burcodeTab.delete;
l_projidTab.delete;
l_taskidTab.delete;
l_toptaskidTab.delete;
l_rlmidTab.delete;
l_startdateTab.delete;
l_entrylevelcode.delete;
l_glprdstatustab.delete;
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_bud_ver_id ='||p_bud_ver_id );
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_calling_mode ='||p_calling_mode );
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start l_closed_prd_exists ='||l_closed_prd_exists );
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Start p_bud_ver_id ='||p_bud_ver_id );
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: Deriving budget details for closed period txns');
UPDATE pa_cost_distribution_lines_all cdl
SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
WHERE cdl.expenditure_item_id = l_DocHdrTab(i)
AND ( cdl.line_num = l_DocDistTab(i) OR (l_burcodeTab(i) = 'S' AND cdl.line_type ='D'))
AND cdl.budget_version_id IS NOT NULL
AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
EXISTS (SELECT 1
FROM xla_events xev
WHERE xev.event_id = cdl.acct_event_id
AND xev.application_id = 275
AND xev.process_status_code <> 'P' )
);
pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
UPDATE pa_bc_commitments_all bccom
SET bccom.budget_version_id = NVL(l_bvidTab(i), bccom.budget_version_id),
bccom.budget_line_id = NVL(l_blidTab(i),bccom.budget_line_id)
WHERE bccom.bc_commitment_id = l_bccomidTab(i)
AND l_glprdstatustab(i) = 'C'
AND bccom.budget_version_id = pa_budget_fund_pkg.g_cost_prev_bvid;
pa_fck_util.debug_msg( 'Number of pa_bc_commitments_all updated'||SQL%ROWCOUNT);
UPDATE pa_cost_distribution_lines_all cdl
SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
WHERE cdl.expenditure_item_id = l_DocHdrTab(i)
-- All the pending EXP lines associated with commitment should get updated ,hence no doc_dist_id join
AND ( cdl.line_type = 'R' OR (l_burcodeTab(i) = 'S' AND cdl.line_type ='D'))
AND cdl.budget_version_id IS NOT NULL
AND l_DocHdrTab(i) is NOT NULL -- this record corresponds to EXP record in projects
AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
EXISTS (SELECT 1
FROM xla_events xev
WHERE xev.event_id = cdl.acct_event_id
AND xev.application_id = 275
AND xev.process_status_code <> 'P' )
);
pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
UPDATE pa_cost_distribution_lines_all cdl
SET cdl.budget_version_id = NVL(l_bvidTab(i),cdl.budget_version_id),
cdl.budget_line_id = NVL(l_blidTab(i),cdl.budget_line_id)
WHERE cdl.expenditure_item_id IN (SELECT exp2.expenditure_item_id
FROM pa_cost_distribution_lines_all cdl1,
pa_expenditure_items_all exp2 -- BTC
WHERE cdl1.expenditure_item_id = l_DocHdrTab(i)
AND cdl1.burden_sum_source_run_id = exp2.burden_sum_dest_run_id
AND exp2.system_linkage_function = 'BTC')
AND l_burcodeTab(i) <> 'S'
AND l_DocHdrTab(i) is NOT NULL -- this record corresponds to EXP record in projects
AND cdl.budget_version_id IS NOT NULL
AND (cdl.acct_event_id IS NULL OR -- events which are not processed by SLA
EXISTS (SELECT 1
FROM xla_events xev
WHERE xev.event_id = cdl.acct_event_id
AND xev.application_id = 275
AND xev.process_status_code <> 'P' )
);
pa_fck_util.debug_msg( 'Number of pa_cost_distribution_lines_all updated'||SQL%ROWCOUNT);
UPDATE pa_bc_commitments_all bccom
SET bccom.budget_version_id = NVL(l_bvidTab(i), bccom.budget_version_id),
bccom.budget_line_id = NVL(l_blidTab(i),bccom.budget_line_id)
WHERE bccom.bc_commitment_id = l_bccomidTab(i);
pa_fck_util.debug_msg( 'Number of pa_bc_commitments updated'||SQL%ROWCOUNT);
pa_fck_util.debug_msg('Update_bvid_blid_on_cdl_bccom: End' );
END Update_bvid_blid_on_cdl_bccom;
SELECT LOOKUP.Meaning
FROM PA_Lookups LOOKUP
WHERE LOOKUP.Lookup_Type IN ('IND COST DIST REJECTION CODE','COST DIST REJECTION CODE', 'FC_RESULT_CODE', 'TRANSACTION REJECTION REASON')
AND LOOKUP.Lookup_Code = p_Lookup_code;
SELECT GMSLKUP.Meaning
FROM GMS_Lookups GMSLKUP
WHERE GMSLKUP.Lookup_Type = 'FC_RESULT_CODE'
AND GMSLKUP.Lookup_Code = p_Lookup_code;
SELECT AID.Parent_Reversal_id
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = p_document_distribution_id
AND decode(nvl(AID.cancellation_flag,'N'), 'Y', decode(AID.Parent_Reversal_id, null,'N','Y'),'N') = 'Y';
SELECT APAD.REVERSED_PREPAY_APP_DIST_ID
FROM AP_PREPAY_APP_DISTS APAD,
AP_PREPAY_HISTORY_ALL APPH,
AP_INVOICES_ALL AI
WHERE APAD.Prepay_App_Distribution_ID = p_document_distribution_id
AND APPH.prepay_history_id = APAD.prepay_history_id
AND AI.invoice_id = APPH.invoice_id
AND decode(p_event_type_code, 'PREPAYMENT UNAPPLIED', decode(nvl(AI.historical_flag,'N'), 'Y','N',
decode(APAD.REVERSED_PREPAY_APP_DIST_ID, null, 'N','Y') ),'N') = 'Y' ;
SELECT nvl(AID.cancellation_flag,'N')
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = p_invoice_distribution_id
AND decode(nvl(AID.cancellation_flag,'N'), 'Y', decode(AID.Parent_Reversal_id, null,'N','Y'),'N') = 'Y';
SELECT 'Y'
FROM dual
WHERE exists ( select 1
from ap_invoice_distributions_all aid
where aid.invoice_id = p_invoice_id
and aid.Parent_Reversal_id = p_invoice_distribution_id);