[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_status varchar2(13) ;
g_update_bvid varchar2(13) ;
g_update_brc varchar2(13) ;
select 1
from gms_award_exp_type_act_cost
where award_id = p_award_id
and expenditure_type = p_exp_type
for update;
SELECT nvl(act.req_raw_cost,0) req_raw_cost,
nvl(act.po_raw_cost,0) po_raw_cost,
nvl(act.enc_raw_cost,0) enc_raw_cost,
nvl(act.AP_raw_cost,0) AP_raw_cost,
nvl(act.exp_raw_cost,0) exp_raw_cost,
nvl(act.req_burdenable_cost,0) req_burdenable_cost,
nvl(act.po_burdenable_cost,0) po_burdenable_cost,
nvl(act.enc_burdenable_cost,0) enc_burdenable_cost,
nvl(act.ap_burdenable_cost,0) ap_burdenable_cost,
nvl(act.exp_burdenable_cost,0) exp_burdenable_cost
FROM gms_award_exp_type_act_cost act
WHERE act.award_id = P_award_id
AND act.expenditure_type = P_EXP_TYPE
FOR UPDATE OF REQ_RAW_COST NOWAIT;
select packet_id,
bc_packet_id,
document_header_id,
document_distribution_id,
award_id,
expenditure_type,
document_type,
nvl(entered_cr,0) entered_cr,
nvl(entered_dr,0) entered_dr,
award_set_id,
transaction_source,
request_id
from gms_bc_packets
where packet_id = p_packet_id
and expenditure_type = p_expenditure_type
and award_id = p_award_id
and nvl(entered_cr,0) + nvl(entered_dr,0) <> 0
and Status_code in ('P','I') -- fix for bug : 2927485, To reject transactions that had already failed a setup step
and burdenable_raw_cost is null -- fix for bug 3810247
and document_type <> 'ENC' --Bug 5726575
order by decode(document_type,'REQ', 1, 'PO',2, 'ENC', 3, 'AP', 4, 'EXP', 5, 6 ) asc, ( nvl(entered_dr,0) - nvl( entered_cr,0) ) DESC ;
select packet_id,
bc_packet_id,
document_header_id,
document_distribution_id,
gbp.award_id,
expenditure_type,
gbp.document_type,
nvl(entered_cr,0) entered_cr,
nvl(entered_dr,0) entered_dr,
gbp.award_set_id,
transaction_source,
gbp.request_id
from gms_bc_packets gbp,
gms_award_distributions adl
where gbp.packet_id = p_packet_id
and gbp.expenditure_type = p_expenditure_type
and gbp.award_id = p_award_id
and gbp.document_header_id = adl.expenditure_item_id
and gbp.document_distribution_id = adl.adl_line_num
and adl.document_type = 'ENC'
and adl.adl_status = 'A'
and nvl(gbp.entered_cr,0) + nvl(gbp.entered_dr,0) <> 0
and gbp.document_type = 'ENC'
and gbp.Status_code = 'P'
order by decode(adl.line_num_reversed, NULL, decode(adl.reversed_flag, NULL, 3, 2), 1) ASC,
(nvl(entered_dr,0) - nvl(entered_cr,0)) ASC;
Select DECODE( NVL(allow_burden_flag,'N'), 'N', 'Y', 'N')
into l_allow_burden_flag
from pa_transaction_sources
where transaction_source = p_transaction_source;
select vw.dist_id,
vw.header_id,
vw.amount,
vw.burden,
vw.award_set_id,
vw.adl_line_num,
(vw.amount - NVL(vw.burden,0) ) diff_amount,
vw.award_id,
vw.expenditure_type,
vw.project_id,
vw.task_id,
vw.expenditure_item_date,
vw.expenditure_organization_id,
vw.resource_list_member_id,
vw.bud_task_id,
vw.ind_compiled_set_id
from ( select rd.distribution_id dist_id,
rh.requisition_header_id header_id,
po_intg_document_funds_grp.get_active_encumbrance_func
('REQUISITION',rd.distribution_id) amount,
adl.burdenable_raw_cost burden,
adl.award_set_id award_set_id,
adl.adl_line_num adl_line_num,
adl.award_id award_id,
rd.expenditure_type expenditure_type,
rd.project_id,
rd.task_id,
rd.expenditure_item_date,
rd.expenditure_organization_id,
adl.resource_list_member_id,
adl.bud_task_id,
adl.ind_compiled_set_id
from po_requisition_headers RH,
po_requisition_lines RL,
po_req_distributions RD,
gms_award_distributions ADL
where rh.type_lookup_code = 'PURCHASE'
and rh.requisition_header_id = rl.requisition_header_id
and nvl(rl.modified_by_agent_flag,'N') = 'N'
and rl.source_type_code = 'VENDOR'
and rd.requisition_line_id = rl.requisition_line_id
and nvl(rd.encumbered_flag,'N') = 'Y'
and adl.award_set_id = rd.award_id
and adl.distribution_id = rd.distribution_id
and adl.adl_status = 'A'
and adl.document_type = 'REQ'
and adl.adl_line_num = 1
and adl.award_id = p_award_id
and rd.expenditure_type = p_exp_type
--and nvl(rh.authorization_status,'NULL')= 'APPROVED' -- Commented as part of Bug 5037180
) VW
where ( ( l_choice = 1 and ABS(vw.amount) > ABS(vw.burden) ) OR
( l_choice = 2 and NVL(vw.burden,0) > 0 AND vw.dist_id <> p_dist_id ) OR
( l_choice = 3 and vw.header_id <> p_header_id and vw.burden is not NULL )
)
order by vw.header_id desc , vw.dist_id desc ;
select vw.dist_id,
vw.header_id,
vw.amount,
vw.burden,
vw.award_set_id,
vw.adl_line_num,
(vw.amount - NVL(vw.burden,0) ) diff_amount,
vw.award_id,
vw.expenditure_type,
vw.project_id,
vw.task_id,
vw.expenditure_item_date,
vw.expenditure_organization_id,
vw.resource_list_member_id,
vw.bud_task_id,
vw.ind_compiled_set_id
from ( select pod.po_header_id header_id
, pod.po_distribution_id dist_ID
, po_intg_document_funds_grp.get_active_encumbrance_func('PO', pod.po_distribution_id)
amount
, adl.burdenable_raw_cost burden
, adl.award_set_id award_set_id
, adl.adl_line_num adl_line_num
, adl.award_id award_id
, pod.expenditure_type expenditure_type
, pod.project_id
, pod.task_id
, pod.expenditure_item_date
, pod.expenditure_organization_id
, adl.resource_list_member_id
, adl.bud_task_id
, adl.ind_compiled_set_id
from
po_distributions pod,
gms_award_distributions adl
where nvl(pod.encumbered_flag,'N')= 'Y'
and pod.award_id = adl.award_set_id
and adl.adl_line_num = 1
and adl.po_distribution_id = pod.po_distribution_id
--
-- 4004559 - PJ.M:B8:P13:OTH:PERF: FULL TABLE SCAN COST ON PO_DISTRIBUTIONS_ALL EXCEEDS 5
-- gms_budget_versions criteria was added so that index can be used.
-- and full table scan on po_distributions_all is gone.
--
and pod.project_id in ( select gbv.project_id
from gms_budget_versions gbv
where gbv.budget_type_code = 'AC'
and gbv.budget_status_code in ('S','W' )
and gbv.award_id = p_award_id )
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and adl.document_type = 'PO'
and adl.award_id = p_award_id
and pod.expenditure_type = p_exp_type
) VW
where (vw.amount - NVL(vw.burden,0) ) <> 0
AND ( ( l_choice = 1 and ABS(vw.amount) > ABS(vw.burden) ) OR
( l_choice = 2 and NVL(vw.burden,0) > 0 AND vw.dist_id <> p_dist_id ) OR
( l_choice = 3 and vw.header_id <> p_header_id and vw.burden is not NULL )
)
order by vw.header_id desc , vw.dist_id desc ;
select d.invoice_distribution_id dist_id -- AP Lines change
,I.invoice_id header_id
, pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) amount
--, nvl(d.base_amount,d.amount) amount
, adl.burdenable_raw_cost burden
, adl.award_set_id award_set_id
, adl.adl_line_num adl_line_num
, ( pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
--, (nvl(d.base_amount,d.amount) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
, adl.award_id award_id
, d.expenditure_type expenditure_type
, d.project_id
, d.task_id
, d.expenditure_item_date
, d.expenditure_organization_id
, adl.resource_list_member_id
, adl.bud_task_id
, adl.ind_compiled_set_id
from ap_invoices I,
ap_invoice_distributions D,
gms_award_distributions ADL,
GL_LEDGERS G
where i.invoice_id = d.invoice_id
and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N')) <> 0
and decode(d.pa_addition_flag,'Z','Y','G', 'Y','T','Y','E','Y',null,'N', d.pa_addition_flag) <> 'Y'
and nvl(d.match_status_flag,'N') = 'A'
and d.award_id = adl.award_set_id
and G.LEDGER_ID = D.SET_OF_BOOKS_ID
and adl.invoice_id = i.invoice_id
and adl.invoice_distribution_id = d.invoice_distribution_id
and adl.adl_status = 'A'
and adl.adl_line_num = 1
and adl.document_type = 'AP'
and nvl(adl.fc_status,'N') = 'A'
and d.match_status_flag = 'A'
and adl.award_id = p_award_id
and d.expenditure_type = p_exp_type
and d.line_type_lookup_code <> 'PREPAY'
and I.invoice_type_lookup_code <> 'PREPAYMENT'
and ( --( l_choice = 1 and ABS(nvl(d.base_amount,d.amount ) ) > ABS(NVL(adl.burdenable_raw_cost,0) )
--) OR
( l_choice = 2 and NVL(adl.burdenable_raw_cost,0) > 0
and d.invoice_distribution_id <> p_dist_id ) OR -- AP Lines change
( l_choice = 3 and i.invoice_id <> p_header_id
and adl.burdenable_raw_cost is not NULL )
)
order by I.invoice_id desc , d.invoice_distribution_id desc ; -- AP Lines change
select d.invoice_distribution_id dist_id -- AP Lines change
,I.invoice_id header_id
, pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS' ) amount
--, nvl(d.base_amount,d.amount) amount
, adl.burdenable_raw_cost burden
, adl.award_set_id award_set_id
, adl.adl_line_num adl_line_num
, ( pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
--, (nvl(d.base_amount,d.amount) - NVL(adl.burdenable_raw_cost,0) ) diff_amount
, adl.award_id award_id
, d.expenditure_type expenditure_type
, d.project_id
, d.task_id
, d.expenditure_item_date
, d.expenditure_organization_id
, adl.resource_list_member_id
, adl.bud_task_id
, adl.ind_compiled_set_id
from ap_invoices I,
ap_invoice_distributions D,
gms_award_distributions ADL,
gl_ledgers g
where i.invoice_id = d.invoice_id
and pa_cmt_utils.get_apdist_amt(d.invoice_distribution_id,
I.invoice_id,
nvl(d.base_amount,d.amount),
'N',
'GMS', nvl(g.sla_ledger_cash_basis_flag,'N')) <> 0
and decode(d.pa_addition_flag,'Z','Y','G', 'Y','T','Y','E','Y',null,'N', d.pa_addition_flag) <> 'Y'
and nvl(d.match_status_flag,'N') = 'A'
and d.award_id = adl.award_set_id
and adl.invoice_id = i.invoice_id
and adl.invoice_distribution_id = d.invoice_distribution_id
and adl.adl_status = 'A'
and adl.adl_line_num = 1
and adl.document_type = 'AP'
and nvl(adl.fc_status,'N') = 'A'
and d.match_status_flag = 'A'
and adl.award_id = p_award_id
and d.expenditure_type = p_exp_type
and d.line_type_lookup_code <> 'PREPAY'
and I.invoice_type_lookup_code <> 'PREPAYMENT'
and nvl(d.base_amount,d.amount) > 0
and nvl(d.base_amount,d.amount) <> nvl(adl.burdenable_raw_cost,0)
AND G.LEDGER_ID = D.SET_OF_BOOKS_ID
order by (nvl(d.base_amount,d.amount) - nvl(adl.burdenable_raw_cost,0)) desc ;
SELECT 1 dist_id
, enc.encumbrance_item_id header_id
, enc.amount amount
, adl.burdenable_raw_cost burden
, adl.award_set_id award_set_id
, adl.adl_line_num adl_line_num
, (enc.amount - NVL(adl.burdenable_raw_cost,0) ) diff_amount
, adl.award_id award_id
, enc.encumbrance_type expenditure_type
, adl.project_id
, enc.task_id
, trunc(enc.encumbrance_item_date) expenditure_item_date
, nvl(enc.override_to_organization_id,ge.incurred_by_organization_id) expenditure_organization_id
, adl.resource_list_member_id
, adl.bud_task_id
, adl.ind_compiled_set_id
from gms_encumbrance_items enc,
gms_award_distributions adl,
gms_encumbrances_all ge
where enc.encumbrance_item_id = adl.expenditure_item_id
and nvl(enc.enc_distributed_flag,'N') = 'Y'
and adl.adl_status = 'A'
and adl.document_type = 'ENC'
AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl.line_num_reversed is null --Bug 5726575
and adl.award_id = p_award_id
and enc.encumbrance_type = p_exp_type
and ge.encumbrance_id = enc.encumbrance_id
and ( (l_choice = 1 and ABS(enc.amount ) > ABS(NVL(adl.burdenable_raw_cost,0)) ) OR
(l_choice = 2 and NVL(adl.burdenable_raw_cost,0) > 0
and 1 <> p_dist_id ) OR
(l_choice = 3 and enc.encumbrance_item_id <> p_header_id
and adl.burdenable_raw_cost is not NULL )
)
order by enc.encumbrance_item_id desc; -- Bug 3697483, changed order by
SELECT gae.burden_cost_limit
FROM gms_allowable_expenditures gae,
gms_awards ga
where gae.allowability_schedule_id = ga.allowable_schedule_id
and gae.expenditure_type = P_EXP_TYPE
and ga.award_id = P_award_id;
-- to avoid multiple selects for each documents.
-- Cursor changed was : C_ACT
-- End of comment.
--
CURSOR C_ACT is
SELECT SUM( decode(pkt.document_type, 'ENC',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) enc_raw,
SUM( decode(pkt.document_type, 'EXP',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) exp_raw,
SUM( decode(pkt.document_type, 'PO', (nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) po_raw,
SUM( decode(pkt.document_type, 'REQ',(nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) req_raw,
SUM( decode(pkt.document_type, 'AP', (nvl(pkt.entered_dr,0) -nvl(pkt.entered_cr,0)), 0 ) ) ap_raw,
SUM( decode(pkt.document_type, 'ENC',nvl(pkt.burdenable_raw_cost,0), 0 ) ) enc_brc,
SUM( decode(pkt.document_type, 'EXP',nvl(pkt.burdenable_raw_cost,0), 0 ) ) exp_brc,
SUM( decode(pkt.document_type, 'PO', nvl(pkt.burdenable_raw_cost,0), 0 ) ) po_brc,
SUM( decode(pkt.document_type, 'REQ',nvl(pkt.burdenable_raw_cost,0), 0 ) ) req_brc,
SUM( decode(pkt.document_type, 'AP', nvl(pkt.burdenable_raw_cost,0), 0 ) ) ap_brc
FROM GMS_BC_PACKETS PKT,
gms_budget_versions gbv
WHERE pkt.award_id = p_award_id
and pkt.expenditure_type = p_exp_type
and pkt.status_code in ('A', 'P','I' )
and burden_calculation_seq > 0
and gbv.budget_version_id = pkt.budget_version_id
and gbv.budget_status_code = 'B'
and substr(NVL(pkt.result_code,'P'),1,1) <> 'F'
and decode(pkt.status_code,
'A',1,
'P',decode(SIGN(NVL(entered_dr,0)-NVL(entered_cr,0)),
-1,decode(pkt.packet_id,P_packet_id,1,0),1),
'I', decode(SIGN(NVL(entered_dr,0)-NVL(entered_cr,0)),
-1,decode(pkt.packet_id,P_packet_id,1,0),1)
) = 1;
-- The gms_award_exp_type_act_cost is updated in
-- gms_gl_return_code process and burden_posted_flag
-- is updated to 'Y'. It is possible that there exists
-- some records for which funds_check has approved and
-- amounts are not posted.
-- ---------------------------------------------------
-- Start of comment
-- bug : 3092603
-- Desc : POOR PERFORMANCE FOR APXAPRVL ( INVOICE VALIDATION )
-- Change desc : get the maximum arrival order sequence of the
-- bc packets. This is used to determine the
-- pending totals of raw and burdenable cost.
-- End of comment.
--
SELECT max(arrival_seq)
into l_arrival_seq
from gms_bc_packet_arrival_order ;
PROCEDURE update_exp_rev_cat (x_packet_id IN NUMBER) IS
BEGIN
--
-- To update expenditure_category and revenue category
UPDATE gms_bc_packets pkt
SET (pkt.expenditure_category,pkt.revenue_category) =
(select pe.expenditure_category,pe.revenue_category_code
from pa_expenditure_types pe
where pe.expenditure_type = pkt.expenditure_type)
WHERE pkt.packet_id = x_packet_id;
END update_exp_rev_cat;
PROCEDURE update_top_tsk_par_res (x_packet_id IN NUMBER) IS
BEGIN
UPDATE gms_bc_packets pkt
SET pkt.top_task_id = (SELECT top_task_id
FROM pa_tasks
WHERE task_id = pkt.task_id)
WHERE pkt.packet_id = x_packet_id
AND pkt.top_task_id IS NULL;
UPDATE gms_bc_packets pkt
SET pkt.parent_resource_id = (SELECT parent_member_id
FROM pa_resource_list_members
WHERE resource_list_member_id = pkt.resource_list_member_id)
WHERE pkt.packet_id = x_packet_id
AND pkt.parent_resource_id IS NULL;
END update_top_tsk_par_res;
x_rec_log.last_update_date := SYSDATE ;
x_rec_log.last_updated_by := nvl(fnd_global.user_id,0) ;
x_rec_log.last_update_login := nvl(fnd_global.user_id,0) ;
Select set_of_books_id
into g_set_of_books_id
from pa_implementations;
Select top_task_id
into l_top_task_id
from pa_tasks
where task_id = p_task_id;
Select parent_member_id
into l_parent_resource_id
from pa_resource_list_members
where resource_list_member_id = p_rlmi;
Select budget_version_id
into l_budget_version_id
from gms_budget_versions
where award_id = x_rec_log.award_id
and project_id = p_project_id
and budget_status_code ='B'
and current_flag = 'Y';
insert into gms_bc_packets(
packet_id,
project_id,
award_id,
task_id,
budget_version_id,
expenditure_type,
expenditure_item_date,
actual_flag,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
set_of_books_id,
je_category_name,
je_source_name,
transfered_flag,
document_type,
expenditure_organization_id,
document_header_id,
document_distribution_id,
entered_dr,
entered_cr,
bc_packet_id,
request_id,
burden_adj_bc_packet_id, -- burden_adj_bc_packet_id will store the bc_packet_id of txn. being FC'ed
parent_bc_packet_id, -- parent_bc_packet_id will store the bc_packet_id of txn. being FC'ed
burden_adjustment_flag,
burdenable_raw_cost,
resource_list_member_id,
bud_task_id,
ind_compiled_set_id,
top_task_id,
parent_resource_id,
burden_calculation_seq,
source_event_id) /* Added for Bug 5645290 */
values(x_rec_log.packet_id,
p_project_id,
x_rec_log.award_id,
p_task_id,
l_budget_version_id,
x_rec_log.expenditure_type,
p_expenditure_item_date,
decode(x_rec_log.document_type,'EXP','A','E'), -- Actual_flag
decode(x_rec_log.document_type,'EXP','P','I'), -- Bug 5037180 : Status_code is always 'P'
x_rec_log.last_update_date,
x_rec_log.last_updated_by,
x_rec_log.created_by,
x_rec_log.creation_date,
x_rec_log.last_update_login,
g_set_of_books_id,
decode(x_rec_log.document_type,'REQ','Requisitions',
'PO','Purchases',
'AP','Purchase Invoices',
'ENC','Project Accounting'), --Category
decode(x_rec_log.document_type,'REQ','Purchasing',
'PO','Purchasing',
'AP','Payables',
'ENC','Miscellaneous Transaction'), -- Source:Hard coding 'Misc Tran ..'
'N', --transferred_flag
x_rec_log.document_type,
p_expenditure_org_id,
x_rec_log.document_header_id,
x_rec_log.document_distribution_id,
0, --entered_dr,
0, --entered_cr
gms_bc_packets_s.nextval,
g_request_id,
x_rec_log.bc_packet_id,
x_rec_log.bc_packet_id,
'Y', -- burden_adjustment_flag,
x_rec_log.adj_burdenable_amount,
p_rlmi,
p_bud_task_id,
p_ind_compiled_set_id,
l_top_task_id,
l_parent_resource_id,
x_rec_log.adjustment_id,
(select source_event_id
from gms_bc_packets
where bc_packet_id = x_rec_log.bc_packet_id)); /* Added for Bug 5645290 */
-- Update the running total of award and expenditure type.
-- ---------------------------------------------------------+
IF x_rec_log.source_flag = 'N' THEN
return ;
SELECT SUM(burdenable_raw_cost)
FROM gms_bc_packets
WHERE document_header_id = p_header_id
AND document_distribution_id = p_dist_id
AND document_type = p_doc_type
AND burden_adjustment_flag = 'Y'
AND nvl(burden_posted_flag,'N') <> 'X'
AND status_code IN ('P','A','I');
SELECT project_id, top_task_id
FROM pa_tasks
WHERE task_id = x_task_id;
SELECT idc_schedule_id, cost_ind_sch_fixed_date
FROM gms_override_schedules
WHERE award_id = x_award_id
AND task_id = l_task_id;
SELECT idc_schedule_id, cost_ind_sch_fixed_date
FROM gms_override_schedules
WHERE award_id = x_award_id
AND project_id = l_project_id
AND task_id is NULL;
SELECT idc_schedule_id,
cost_ind_sch_fixed_date
INTO l_rate_sch_id,
l_sch_fixed_date
FROM gms_awards_all -- bug 3117503. changed to _all.
WHERE award_id = x_award_id;
select adl.ind_compiled_set_id
into l_compiled_set_id
from gms_award_distributions adl
-- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
--po_req_distributions_all por
where adl.award_id = x_award_id
and adl.task_id = x_task_id
and adl.distribution_id = x_document_distribution_id
and adl.document_type = 'REQ'
and adl.adl_status = 'A'
and adl.fc_status = 'A';
select adl.ind_compiled_set_id
into l_compiled_set_id
from gms_award_distributions adl
-- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
-- po_distributions_all pod
where adl.award_id = x_award_id
and adl.task_id = x_task_id
and adl.po_distribution_id = x_document_distribution_id
and adl.document_type = 'PO'
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and adl.adl_line_num = 1 ;
select adl.ind_compiled_set_id
into l_compiled_set_id
from gms_award_distributions adl
-- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
-- ap_invoice_distributions_all apd
where adl.award_id = x_award_id
and adl.task_id = x_task_id
and adl.invoice_id = x_document_header_id
and adl.invoice_distribution_id = x_document_distribution_id -- AP Lines change
and adl.document_type = 'AP'
and adl.adl_status = 'A'
and adl.fc_status = 'A'
-- R12 Funds Managment Uptake : Obsolete Ap/PO/REQ usage as its not required
/*and apd.invoice_id = x_document_header_id
and apd.invoice_distribution_id = x_document_distribution_id -- AP Lines change
and apd.award_id = adl.award_set_id */
and adl.adl_line_num = 1 ;
select ind_compiled_set_id
into l_compiled_set_id
from gms_award_distributions
where award_id = x_award_id
and task_id = x_task_id
and expenditure_item_id = x_document_header_id
and cdl_line_num = x_document_distribution_id
and document_type = 'EXP'
and adl_status = 'A'
and fc_status = 'A';
select sum(burdenable_raw_cost)
from gms_bc_packets
where document_header_id = x_hdr_id
and document_distribution_id = x_doc_dist_id
and burden_adjustment_flag = 'Y'
and nvl(burden_posted_flag,'N') <> 'X'
and status_code in ('A','P','I')
and document_type in ( 'PO','REQ', 'AP', 'ENC', 'EXP' ) ;
select
decode( p_doc_type, 'REQ', x_rec_award_exp_tot.req_burdenable_cost,
'PO', x_rec_award_exp_tot.po_burdenable_cost,
'ENC', x_rec_award_exp_tot.enc_burdenable_cost, 1 )
into x_adj_allowed
from dual ;
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
PROCEDURE UPDATE_BC_PACKET( p_doc_type varchar2,
p_burden NUMBER,
p_amount NUMBER,
p_record bc_packets%ROWTYPE )
is
PRAGMA AUTONOMOUS_TRANSACTION; -- R12 Funds Management Uptake : Made this an autonomous procedure.
select gms_adjustments_id_s.NEXTVAL into l_calc_sequence from dual;
UPDATE gms_bc_packets
set burdenable_raw_cost = p_burden,
burden_calculation_seq = l_calc_sequence
where packet_id = p_record.packet_id
and bc_packet_id = p_record.bc_packet_id ;
END UPDATE_BC_PACKET ;
SELECT adl.burdenable_raw_cost burden_amount,
adl.adl_line_num,
adl.award_set_id
FROM gms_award_distributions adl ,
po_req_distributions_all req
WHERE adl.distribution_id = p_record.document_distribution_id
AND req.distribution_id = p_record.document_distribution_id
AND req.award_id = adl.award_set_id
and adl.adl_status = 'A'
and adl.distribution_id = req.distribution_id ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF x_calc_zero_limit THEN
burden_raw_cost := 0 ;
update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('REQ', burden_raw_cost, pkt_amount, p_record ) ;
select nvl(adl.burdenable_raw_cost,0) ,
adl.award_set_id award_set_id,
adl_line_num adl_line_num
FROM po_distributions_all pod,
gms_award_distributions adl
where adl.award_set_id = pod.award_id
AND adl.po_distribution_id = pod.po_distribution_id
and adl.adl_status = 'A'
and pod.po_distribution_id = X_po_dist_id ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF x_calc_zero_limit THEN
burden_raw_cost := 0 ;
update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('PO', burden_raw_cost, pkt_amount, p_record ) ;
select nvl(adl.burdenable_raw_cost,0) ,
adl.award_set_id award_set_id,
adl_line_num adl_line_num
FROM gms_encumbrance_items_all enc,
gms_award_distributions adl
where adl.expenditure_item_id = ENC.encumbrance_item_id
and adl.adl_status = 'A'
and ENC.encumbrance_item_id = bc_pkt_rec.document_header_id
and enc.enc_distributed_flag = 'Y' ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF x_calc_zero_limit THEN
burden_raw_cost := 0 ;
update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF nvl(x_award_exp_limit,0) <= 0 THEN
-- --------------------------
-- IDC Limit is not enabled.
-- -------------------------
burden_raw_cost := pkt_amount ;
update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('ENC', burden_raw_cost, pkt_amount, p_record ) ;
select nvl(apd.base_amount,apd.amount) amount , --Bug 2472802
nvl(adl.burdenable_raw_cost,0) burden,
apd.invoice_id header_id,
apd.invoice_distribution_id DIST_ID, -- AP Lines change
adl.award_set_id award_set_id ,
adl.adl_line_num adl_line_num,
apd.project_id,
apd.task_id,
apd.expenditure_item_date,
apd.expenditure_organization_id,
adl.resource_list_member_id,
adl.bud_task_id,
adl.ind_compiled_set_id
FROM ap_invoice_distributions APD,
gms_award_distributions ADL,
gl_ledgers G
where adl.invoice_distribution_id = APD.invoice_distribution_id
and adl.adl_status = 'A'
and adl.award_id = P_record.award_id
and nvl(adl.burdenable_raw_cost,0) <> 0
and apd.expenditure_type = P_record.expenditure_type
and ADL.award_set_id = APD.award_id
AND G.LEDGER_ID = APD.SET_OF_BOOKS_ID
and pa_cmt_utils.get_apdist_amt( apd.invoice_distribution_id,
apd.invoice_id,
nvl(apd.base_amount,apd.amount),
'N',
'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
and apd.line_type_lookup_code <> 'PREPAY'
and decode(apd.pa_addition_flag,'G', 'Y','Z','Y', 'T','Y', 'E','Y', NULL, 'N', apd.pa_addition_flag ) <> 'Y'
-- Bug 2097676: Fixing GSCC Error File.sql.9
and apd.invoice_id = P_record.document_header_id ;
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
select invoice_id
from ap_invoices inv
where inv.invoice_type_lookup_code IN ('CREDIT','DEBIT')
and inv.invoice_id = bc_pkt_rec.document_header_id ;
select apd1.line_type_lookup_code,
apd1.prepay_distribution_id,
nvl(adl.burdenable_raw_cost ,0),
apd2.invoice_id,
adl.award_set_id,
adl.award_id,
apd2.expenditure_type,
apd2.project_id,
apd2.task_id,
apd2.expenditure_item_date,
apd2.expenditure_organization_id,
adl.resource_list_member_id,
adl.bud_task_id,
adl.ind_compiled_set_id
from ap_invoice_distributions_all apd1,
ap_invoice_distributions_all apd2,
gms_award_distributions adl
where apd1.invoice_distribution_id = bc_pkt_rec.document_distribution_id
and apd2.invoice_distribution_id = apd1.prepay_distribution_id
and apd2.award_id = adl.award_set_id
and adl.invoice_distribution_id = apd2.invoice_distribution_id
and adl.document_type = 'AP'
and adl.fc_status = 'A'
and adl.invoice_id = apd2.invoice_id ;
SELECT adl.award_set_id , adl.adl_line_num
FROM ap_invoice_distributions AP,
gms_award_distributions ADL
where adl.invoice_distribution_id = AP.invoice_distribution_id
and adl.adl_status = 'A'
and adl.award_id = p_record.award_id
and ap.expenditure_type = p_record.expenditure_type
and ADL.award_set_id = AP.award_id
and ap.invoice_id = P_RECORD.document_header_id
and ap.invoice_distribution_id = P_RECORD.document_distribution_id ; -- AP Lines change
select sum( pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
ap.invoice_id,
nvl(ap.base_amount,ap.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount ,
sum(nvl(adl.burdenable_raw_cost,0)) sum_burden,
count(*) sum_count
FROM ap_invoice_distributions AP,
gms_award_distributions ADL,
gl_ledgers G
where adl.invoice_distribution_id = AP.invoice_distribution_id
and adl.adl_status = 'A'
and adl.award_id = bc_pkt_rec.award_id
and ap.expenditure_type = bc_pkt_rec.expenditure_type
and ADL.award_set_id = AP.award_id
and G.LEDGER_ID = AP.SET_OF_BOOKS_ID
and pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
ap.invoice_id,
nvl(ap.base_amount,ap.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
and ap.line_type_lookup_code <> 'PREPAY'
and decode(ap.pa_addition_flag,'Z','Y', 'T','Y', 'E','Y', 'G', 'Y',NULL, 'N', ap.pa_addition_flag ) <> 'Y'
-- Bug 2097676, Fixing GSCC error File.sql.9
and ap.invoice_id = bc_pkt_rec.document_header_id ;
update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
-- We take the amount from ENC and update EXP.
-- The sequence of adjustment is REQ- PO-ENC.
-- -------------------------------------------------
x_dummy := 0 ;
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('AP', burden_raw_cost, pkt_amount, p_record ) ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF x_calc_zero_limit THEN
burden_raw_cost := 0 ;
update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('FAB', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
/* -- Update in FUNCTION update_bc_pkt_burden_raw_cost takes care of this ...
IF x_calc_zero_limit THEN
burden_raw_cost := 0 ;
update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
-- We take the amount from ENC and update EXP.
-- The sequence of adjustment is REQ- PO-ENC.
-- -------------------------------------------------
-- BUG:1349726 : ENC_BURDENABLE_COST is not
-- released in acse of actuals with IDC scenario.
-- --bc_pkt_rec.document_distribution_id COMMENTED
-- because we always adjust from REQ
-- Update the Running Total.
-- -------------------------------------------------------------------
update_bc_packet('EXP', burden_raw_cost, pkt_amount, p_record ) ;
select distinct gbp.award_id,gbp.expenditure_type
from gms_bc_packets gbp,
gms_awards_all ga,
gms_allowable_expenditures gae
where gbp.packet_id = p_packet_id
and ga.award_id = gbp.award_id
and gae.allowability_schedule_id = ga.allowable_schedule_id
and gae.expenditure_type = gbp.expenditure_type
and nvl(gae.burden_cost_limit,0) > 0
and gbp.parent_bc_packet_id is null;
select rowid,
entered_dr ,
nvl(burdenable_raw_cost,0) burden
from gms_bc_packets gbp
where gbp.packet_id = p_packet_id
and gbp.award_id = x_award_id
and gbp.expenditure_type = x_expenditure_type
and nvl(gbp.entered_dr,0) > 0
and nvl(entered_cr,0) = 0
and nvl(gbp.entered_dr,0) <> nvl(gbp.burdenable_raw_cost,0)
and gbp.parent_bc_packet_id is null
order by decode(gbp.document_type,'EXP',1,'AP',2,'ENC',3,'PO',4,'REQ',5,6) asc,
nvl(gbp.entered_dr,0) desc;
X_burden_amt_to_update_on_txn NUMBER;
select sum( pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
ap.invoice_id,
nvl(ap.base_amount,ap.amount),
'N',
'GMS',nvl(g.sla_ledger_cash_basis_flag,'N') ) ) sum_amount
FROM ap_invoice_distributions AP,
gms_award_distributions ADL,
GL_LEDGERS G
where adl.invoice_distribution_id = AP.invoice_distribution_id
and adl.adl_status = 'A'
and adl.award_id = p_award_id
and ap.expenditure_type = p_expenditure_type
and ADL.award_set_id = AP.award_id
and ap.invoice_id = p_invoice_id
and G.LEDGER_ID = AP.SET_OF_BOOKS_ID
and pa_cmt_utils.get_apdist_amt(ap.invoice_distribution_id,
ap.invoice_id,
nvl(ap.base_amount,ap.amount),
'N',
'GMS', nvl(g.sla_ledger_cash_basis_flag,'N') ) <> 0
and decode(ap.pa_addition_flag,'Z','Y','G','Y','T','Y','E','Y',NULL,'N',ap.pa_addition_flag) <> 'Y' ;
select bc_packet_id,
abs(nvl(burdenable_raw_cost,0)) burdenable_raw_cost
from gms_bc_packets gbp
where gbp.packet_id = p_packet_id
and gbp.award_id = p_award_id
and gbp.expenditure_type = p_expenditure_type
and nvl(gbp.burdenable_raw_cost,0) < 0
and gbp.parent_bc_packet_id is null
order by nvl(gbp.burdenable_raw_cost,0) desc;
Update gms_bc_packets
set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + x_avail_burden_amt
where rowid = y.rowid;
Update gms_bc_packets
set burdenable_raw_cost = y.entered_dr
where rowid = y.rowid;
X_burden_amt_to_update_on_txn := x_avail_burden_amt;
X_burden_amt_to_update_on_txn := c_rec.amount - c_rec.burden;
x_avail_burden_amt := x_avail_burden_amt - X_burden_amt_to_update_on_txn;
gms_error_pkg.gms_debug (g_error_procedure_name||':X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
select gms_adjustments_id_s.NEXTVAL
INTO x_adjustment_id
FROM dual ;
x_rec_log.last_update_date := sysdate;
x_rec_log.last_updated_by := -1;
x_rec_log.last_update_login := -1;
gms_error_pkg.gms_debug (g_error_procedure_name||':before loop:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
If z.burdenable_raw_cost >= X_burden_amt_to_update_on_txn then
x_rec_log.adj_burdenable_amount := X_burden_amt_to_update_on_txn;
X_burden_amt_to_update_on_txn := 0;
X_burden_amt_to_update_on_txn := X_burden_amt_to_update_on_txn - z.burdenable_raw_cost;
If X_burden_amt_to_update_on_txn = 0 then
exit;
gms_error_pkg.gms_debug (g_error_procedure_name||':After loop:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
If X_burden_amt_to_update_on_txn <> 0 then
-- A.8 There are no records in bcpkts that can account for the available
-- burden amount that can be maximized on this AP txn...
-- Create record with dummy bc_packet_id
x_rec_log.adj_burdenable_amount := X_burden_amt_to_update_on_txn;
X_burden_amt_to_update_on_txn := 0;
gms_error_pkg.gms_debug (g_error_procedure_name||':After all bckpt:X_burden_amt_to_update_on_txn:'||X_burden_amt_to_update_on_txn,'C');
PROCEDURE update_bc_pkt_brc_bvid_status (p_action IN VARCHAR2,
p_mode IN VARCHAR2 DEFAULT NULL,
p_packet_id IN NUMBER DEFAULT NULL,
p_award_id IN NUMBER DEFAULT NULL,
p_expenditure_type IN VARCHAR2 DEFAULT NULL,
p_full_mode_failure IN VARCHAR2 DEFAULT NULL,
x_result_code IN OUT NOCOPY VARCHAR2
) IS
PRAGMA AUTONOMOUS_TRANSACTION;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS' ||': Start'||l_count,'C');
IF p_action = g_update_brc THEN
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Burdenable Raw cost update :','C');
-- This update will take care of 3 scenarios:
-- A. Non-IDC limit
-- B. Zero$ IDC Limit
-- C. P82 scenarios
-- Logic:
-- i.If result code is 'P82' , update burdenable raw cost to zero (net zero)
-- ii. If not, check transaction source of transaction
-- iii. No txn. source, check limit,
-- iv. if no limit, same as raw_cost,
-- v. if limit=0, burdenable raw cost=0
-- vi. if limit, do not update burdeanable raw cost
-- vii. If txn. source, then check burden allowed?
-- viii. No ..burden is zero
-- ix. yes .. step iv - vi ..
-------------------------------------------------------------------- */
/* Bug 5344693 : The following update is modified such that
if burden_allowed function returns 'Y' (i.e burden is imported from external transaction source and do not calculate in Projects )
then burdenable raw cost should be 0
else if burden_allowed returns 'N' (i.e burden is calculated in projects )
then calculate the burdenable raw cost. */
Update gms_bc_packets gbc
Set gbc.burdenable_raw_cost =
(select decode(gbc.result_code,'P82',0,
decode(gbc.transaction_source,
null,decode(gae.burden_cost_limit,
null,(gbc.entered_dr-entered_cr),
0,0,gbc.burdenable_raw_cost
)
,decode(burden_allowed(gbc.transaction_source),
'N',0,
'Y',decode(gae.burden_cost_limit,
null,(gbc.entered_dr-entered_cr),
0,0,gbc.burdenable_raw_cost
)
)
)
)
from gms_allowable_expenditures gae,
gms_awards_all ga
where ga.award_id = gbc.award_id
and gae.allowability_schedule_id = ga.allowable_schedule_id
and gae.expenditure_type = gbc.expenditure_type
)
where packet_id = p_packet_id
and status_code in ('P','I')
and burdenable_raw_cost is NULL;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Burdenable raw Cost Updated on :'||l_count||' records','C');
ELSIF p_action = g_update_status THEN
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Result Code update :','C');
update gms_bc_packets
set result_code = x_result_code
where packet_id = p_packet_id
and award_id = NVL(p_award_id,award_id)
and expenditure_type = NVL(p_expenditure_type,expenditure_type) ;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':result code '||x_result_code||' Updated on :'||l_count||' records','C');
Update gms_bc_packets
set status_code = 'R',
result_code = decode(substr(result_code,1,1),'P','F65',result_code)
where packet_id = p_packet_id;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':F65 (full mode failure) Updated on :'||l_count||' records','C');
ELSIF p_action = g_update_bvid THEN
-- Update budget_verison_id on gms_bc_packets. This is required as cursor c_Act
-- checks for gms_bc_packet trasnactions that has a baselined budget only
-- during summarization ...
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||': Budget version Id update :','C');
Update gms_bc_packets bcp
set bcp.budget_version_id = (select gbv.budget_version_id
from gms_budget_versions gbv
where gbv.award_id = bcp.award_id
and gbv.project_id = bcp.project_id
and gbv.budget_status_code ='B'
and gbv.current_flag = 'Y'
)
where bcp.packet_id = p_packet_id
and bcp.award_id = p_award_id
and bcp.expenditure_type = p_expenditure_type;
Select 1 into l_count
from dual where exists
(select 1 from gms_bc_packets bcp
where bcp.packet_id = p_packet_id
and bcp.award_id = p_award_id
and bcp.expenditure_type = p_expenditure_type
and bcp.budget_version_id is null);
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Award,Exp.type:'||p_award_id||';'||p_expenditure_type,'C');
Update gms_bc_packets
set status_code = decode(p_mode,'C','F','R'),
result_code = 'F12',
fc_error_message = 'Could not derive budget version during burden calculation'
where packet_id = p_packet_id
and award_id = p_award_id
and expenditure_type = p_expenditure_type
and budget_version_id is null;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':Budget version id failure: Full mode failure','C');
Update gms_bc_packets
set status_code = decode(p_mode,'C','F','R'),
result_code = decode(substr(result_code,1,1),'P','F65',result_code)
where packet_id = p_packet_id;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_BVID_STATUS'||':When Others Exception','C');
END update_bc_pkt_brc_bvid_status;
FUNCTION update_bc_pkt_burden_raw_cost(x_packet_id IN NUMBER,
p_mode IN VARCHAR2, -- Bug : 2557041 - Added p_mode parameter
p_partial_flag IN VARCHAR2 DEFAULT 'N') return boolean
IS
stat boolean;
select distinct bcp.award_id, bcp.expenditure_type
from gms_bc_packets bcp,
gms_awards_all ga,
gms_allowable_expenditures gae
where bcp.packet_id = x_packet_id
and status_code IN ('P','I') -- fix for bug : 2927485 ,to reject the transactions that may have already failed a setup step
and bcp.burdenable_raw_cost is NULL
and ga.award_id = bcp.award_id
and gae.allowability_schedule_id = ga.allowable_schedule_id
and gae.burden_cost_limit is not null;
g_error_procedure_name := 'UPDATE_BC_PKT_BURDEN_RAW_COST';
g_update_status := 'UPDATE-STATUS';
g_update_bvid := 'UPDATE-BVID';
g_update_brc := 'UPDATE-BRC';
update_bc_pkt_brc_bvid_status (p_action => g_update_brc, --'UPDATE-BRC'
p_packet_id => x_packet_id,
x_result_code => l_result_code );
select 'A' into g_actual_flag from dual where exists
(select 1 from gms_bc_packets where packet_id = x_packet_id
and document_type = 'EXP');
update_bc_pkt_brc_bvid_status ( p_action => g_update_bvid,
p_mode => p_mode,
p_packet_id => x_packet_id,
p_award_id => l_award_id,
p_expenditure_type => l_expenditure_type,
p_full_mode_failure => l_full_mode_failure,
x_result_code => l_result_code );
update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
p_packet_id => x_packet_id,
p_award_id => l_award_id,
p_expenditure_type => l_expenditure_type,
p_full_mode_failure => 'N',
x_result_code => l_result_code );
update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
p_packet_id => x_packet_id,
p_award_id => l_award_id,
p_expenditure_type => l_expenditure_type,
p_full_mode_failure => l_full_mode_failure,
x_result_code => l_result_code );
update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
p_packet_id => x_packet_id,
p_award_id => l_award_id,
p_expenditure_type => l_expenditure_type,
p_full_mode_failure => l_full_mode_failure,
x_result_code => x_result_code );
update_bc_pkt_brc_bvid_status ( p_action => g_update_status,
p_packet_id => x_packet_id,
p_award_id => NULL,
p_expenditure_type => NULL,
p_full_mode_failure => 'N',
x_result_code => x_result_code );
END update_bc_pkt_burden_raw_cost;
FUNCTION UPDATE_SOURCE_BURDEN_RAW_COST(x_packet_id in number, p_mode varchar2, p_partial_flag varchar2) return boolean
IS
l_error varchar2(1000);
select rowid,document_header_id, document_distribution_id,
expenditure_type,burdenable_raw_cost,document_type,
burden_adjustment_flag,ind_compiled_set_id
from gms_bc_packets
where packet_id = x_packet_id
and status_code IN ('P','I')
and substr(result_code,1,1) = 'P'
and ((nvl(burden_adjustment_flag,'N') = 'N' and parent_bc_packet_id is null)
-- original raw line
OR
(nvl(burden_adjustment_flag,'N') = 'Y' and nvl(burdenable_raw_cost,0) <> 0)
-- Burden adjustment line
)
and document_type <> 'EXP'; -- EXP adls are created during tieback..
g_error_procedure_name := 'Update_source_burden_raw_cost' ;
update gms_award_distributions
set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
where distribution_id = bc_records.document_distribution_id
and adl_status = 'A'
and document_type = 'REQ';
update gms_award_distributions
set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
where po_distribution_id = bc_records.document_distribution_id
and adl_status = 'A'
and document_type = 'PO';
update gms_award_distributions
set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
where invoice_id = bc_records.document_header_id
and invoice_distribution_id = bc_records.document_distribution_id -- AP Lines change
and adl_status = 'A'
and document_type = 'AP';
update gms_award_distributions
set burdenable_raw_cost = nvl(burdenable_raw_cost,0) + bc_records.burdenable_raw_cost,
ind_compiled_set_id = decode(bc_records.burden_adjustment_flag,
'Y',ind_compiled_set_id,bc_records.ind_compiled_set_id)
where expenditure_item_id = bc_records.document_header_id
and adl_line_num = bc_records.document_distribution_id --Bug 5726575
and cdl_line_num = 1
and adl_status = 'A'
and document_type = 'ENC';
Update gms_bc_packets
set burden_posted_flag = 'X'
where rowid = bc_records.rowid;
Update gms_bc_packets
set status_code = 'R',
result_code = 'F52'
where rowid = bc_records.rowid;
Update gms_bc_packets
set status_code = 'T',
result_code = 'F54',
fc_error_message = l_stage||';'||l_error
END UPDATE_SOURCE_BURDEN_RAW_COST;
select adl.ind_compiled_set_id
into x_ind_compiled_set_id
from gms_award_distributions adl,
po_req_distributions_all req
where req.distribution_id = x_distribution_id
and req.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select adl.ind_compiled_set_id
into x_ind_compiled_set_id
from gms_award_distributions adl,
po_distributions_all po
where po.po_distribution_id = x_distribution_id
and po.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select adl.ind_compiled_set_id
into x_ind_compiled_set_id
from gms_award_distributions adl,
ap_invoice_distributions_all apd
where apd.distribution_line_number = x_distribution_line_number
and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
and apd.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select ind_compiled_set_id
into x_ind_compiled_set_id
from gms_award_distributions
where expenditure_item_id = x_distribution_id
and adl_status = 'A'
and fc_status = 'A'
and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575
and line_num_reversed is null
and document_type = 'ENC';
select adl.burdenable_raw_cost
into x_burdenable_raw_cost
from gms_award_distributions adl,
po_req_distributions_all req
where req.distribution_id = x_distribution_id
and req.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select adl.burdenable_raw_cost
into x_burdenable_raw_cost
from gms_award_distributions adl,
po_distributions_all po
where po.po_distribution_id = x_distribution_id
and po.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select adl.burdenable_raw_cost
into x_burdenable_raw_cost
from gms_award_distributions adl,
ap_invoice_distributions_all apd
where apd.distribution_line_number = x_distribution_line_number
and apd.invoice_distribution_id = x_distribution_id -- AP Lines change
and apd.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
select burdenable_raw_cost
into x_burdenable_raw_cost
from gms_award_distributions
where expenditure_item_id = x_distribution_id
and adl_status = 'A'
and fc_status = 'A'
and nvl(reversed_flag, 'N') <> 'Y' --Bug 5726575
and line_num_reversed is null --Bug 5726575
and document_type = 'ENC';
SELECT 'X'
FROM pa_projects p,
gms_project_types gpt
WHERE p.project_id = x_project_id
AND p.project_type = gpt.project_type
AND gpt.sponsored_flag= 'Y' ;