[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
t_last_update_date tt_last_update_date;
t_last_updated_by tt_last_updated_by;
t_last_update_login tt_last_update_login;
l_last_update date := trunc(sysdate);
Procedure Delete_Concurrency_Records;
Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2, p_request_id IN NUMBER);
Procedure Insert_Arrival_Order_Seq (x_packetid IN NUMBER,
x_mode IN VARCHAR2);
select gl_bc_packets_s.nextval, set_of_books_id
into l_packet_id, l_sob_id
from pa_implementations;
t_project_id.delete;
t_award_id.delete;
t_task_id.delete;
t_expenditure_type.delete;
t_expenditure_item_date.delete;
t_expenditure_organization_id.delete;
t_document_header_id.delete;
t_document_distribution_id.delete;
t_entered_dr.delete;
t_entered_cr.delete;
t_burdenable_raw_cost.delete; --R12 AP lines uptake :Forward port bug 4217161
t_ind_compiled_set_id.delete;
t_person_id.delete;
t_job_id.delete;
t_expenditure_category.delete;
t_revenue_category.delete;
t_adjusted_document_header_id.delete;
t_award_set_id.delete;
t_transaction_source.delete;
t_system_linkage_function.delete;
select cdl.expenditure_item_id, -- document_header_id
cdl.line_num, -- document_distribution_id
decode(exp.net_zero_adjustment_flag,
'Y', nvl(exp.adjusted_expenditure_item_id, cdl.expenditure_item_id),
NULL), -- adjusted_document_header_id
exp.transaction_source, -- transaction_source
adl.award_set_id, -- award_set_id
adl.award_id, -- award_id
cdl.project_id, -- project_id
exp.task_id, -- task_id
exp.expenditure_type, -- expenditure_type
exp.expenditure_item_date, -- expenditure_item_date
exp.system_linkage_function,
cdl.ind_compiled_set_id, -- ind_compiled_set_id
exp.job_id, -- job_id
nvl(exp.override_to_organization_id, pae.incurred_by_organization_id) expenditure_org, -- Expenditure org
pae.incurred_by_person_id, -- incurred by person_id
et.expenditure_category, -- expenditure catg
et.revenue_category_code, -- revenue catg
decode(sign(cdl.amount), 1, cdl.amount, 0), -- entered_dr
decode(sign(cdl.amount), -1, -1 * cdl.amount, 0), -- entered_cr
-- R12 AP Lines Uptake: Reversing CDL lines should have same BRC as that of original line.
-- This forward port of fix 4217161.
(SELECT NVL(adl.burdenable_raw_cost,0) * -1
FROM gms_award_distributions adl
WHERE adl.cdl_line_num = cdl.line_num_reversed
AND adl.expenditure_item_id = cdl.expenditure_item_id
AND adl.document_type = 'EXP'
AND adl.adl_status = 'A'
AND cdl.line_num_reversed IS NOT NULL ) burdenable_raw_cost
from pa_cost_distribution_lines cdl,
pa_expenditure_items exp,
gms_award_distributions adl,
pa_expenditures_all pae,
pa_expenditure_types et
where cdl.request_id = g_request_id
and cdl.line_type = 'R'
and nvl(cdl.reversed_flag,'N') <> 'Y'
and cdl.expenditure_item_id = exp.expenditure_item_id
and exp.cost_distributed_flag = 'S'
and cdl.expenditure_item_id = adl.expenditure_item_id
and nvl(adl.cdl_line_num, 1) = 1
and adl.adl_status = 'A'
and adl.document_type = 'EXP'
and exp.expenditure_id = pae.expenditure_id
and exp.expenditure_type = et.expenditure_type
and exp.cost_dist_rejection_code is null;
SELECT bc_packet_id
FROM gms_bc_packets gbc
WHERE packet_id = p_packet_id
AND document_type = 'AP'
AND parent_bc_packet_id IS NULL
AND entered_dr <> 0 OR entered_cr <> 0 ;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM ap_invoice_payments Pay,
ap_invoices_all inv
WHERE pay.invoice_payment_id = g_xface_rec.cdl_system_reference4
AND pay.invoice_id = inv.invoice_id
AND NVL(pay.exchange_rate,0) <> NVL(inv.exchange_rate,0));
SELECT NVL(paydist.invoice_dist_base_amount,paydist.invoice_dist_amount)
FROM ap_payment_hist_dists Paydist
WHERE paydist.pay_dist_lookup_code = 'CASH'
AND Paydist.invoice_distribution_id = g_xface_rec.invoice_distribution_id
AND PayDIST.invoice_payment_id = g_xface_rec.cdl_system_reference4;
SELECT nvl(adl.burdenable_raw_cost,0),NVL(adl.payment_status_flag,'N')
FROM ap_invoice_distributions APD,
gms_award_distributions ADL
where adl.invoice_distribution_id = APD.invoice_distribution_id
and adl.adl_status = 'A'
and adl.document_type = 'AP'
and ADL.award_set_id = APD.award_id
and apd.invoice_id = g_xface_rec.invoice_id
and apd.invoice_distribution_id = g_xface_rec.invoice_distribution_id;
SELECT sum(NVL(gbc.burdenable_raw_cost,0))
FROM gms_bc_packets gbc
WHERE gbc.packet_id = g_packet_id
AND gbc.request_id = g_request_id
AND gbc.status_code = 'P'
AND gbc.document_header_id = g_xface_rec.invoice_id
AND gbc.document_distribution_id = g_xface_rec.invoice_distribution_id
AND gbc.document_type = 'AP';
SELECT NVL(apdist.base_amount,apdist.amount)
FROM ap_invoice_distributions_all apdist
WHERE apdist.invoice_distribution_id = g_xface_rec.invoice_distribution_id
AND apdist.invoice_id = g_xface_rec.invoice_id ;
SELECT SUM(NVL(paydist1.invoice_dist_base_amount,paydist1.invoice_dist_amount))
+ SUM(NVL(paydist2.invoice_dist_base_amount,paydist2.invoice_dist_amount))
FROM ap_payment_hist_dists Paydist1,
ap_payment_hist_dists Paydist2
WHERE paydist1.pay_dist_lookup_code = 'CASH'
AND Paydist1.invoice_distribution_id = g_xface_rec.invoice_distribution_id
and Paydist2.invoice_distribution_id = g_xface_rec.invoice_distribution_id
AND ( paydist1.pa_addition_flag = 'Y' OR --interfaced payments
PayDIST1.invoice_payment_id IN ( SELECT xface.cdl_system_reference4 -- Payments marked for interface in current run
FROM pa_transaction_interface_all xface
WHERE xface.transaction_source = G_txn_source
and xface.cdl_system_reference2 = g_xface_rec.invoice_id
and xface.cdl_system_reference5 = g_xface_rec.invoice_distribution_id
and xface.cdl_system_reference4 is not NULL
and xface.TRANSACTION_STATUS_CODE ='P'))
AND paydist2.invoice_distribution_id = paydist1.invoice_distribution_id
and paydist2.payment_history_id = paydist1.payment_history_id
and paydist2.invoice_payment_id = paydist1.invoice_payment_id
and paydist2.pay_dist_lookup_code = 'DISCOUNT' ;
SELECT NVL(b.invoice_dist_base_amount , b.invoice_dist_amount) amount
from ap_payment_hist_dists b,
ap_invoice_distributions_all apd
where b.invoice_payment_id = g_xface_rec.cdl_system_reference4
and b.invoice_distribution_id = g_xface_rec.invoice_distribution_id
and b.pay_dist_lookup_code = 'DISCOUNT'
and apd.invoice_distribution_id = b.invoice_distribution_id
and NVL(apd.historical_flag,'N') <> 'Y'
and apd.expenditure_item_date >= PA_TRX_IMPORT.G_Profile_Discount_Start_date
and apd.line_type_lookup_code = decode ( PA_TRX_IMPORT.G_discount_Method,
'TAX', decode (apd.line_type_lookup_code,
'TIPV', 'TIPV',
'TERV','TERV',
'TRV', 'TRV',
'NONREC_TAX') ,
'SYSTEM', 'NOT APPLICABLE',
apd.line_type_lookup_code ) ;
select gbv.budget_version_id, pb.entry_level_code
into l_budget_version_id, l_entry_level_code
from gms_budget_versions gbv, pa_budget_entry_methods pb
where award_id = p_award_id
and project_id = p_project_id
and budget_status_code = 'B'
and current_flag = 'Y'
and gbv.budget_entry_method_code = pb.budget_entry_method_code;
select top_task_id
into l_top_task_id
from pa_tasks
where task_id = p_task_id;
select task_id
into g_xface_rec.bud_task_id
from gms_balances
where budget_version_id = l_budget_version_id
and task_id = p_task_id
and balance_type = 'BGT'
--Added the following conditions for Bug 4859071
and project_id = g_xface_rec.project_id
and award_id = g_xface_rec.award_id
and rownum =1;
select task_id
into l_bud_task_id
from gms_balances
where budget_version_id = l_budget_version_id
and task_id = (select top_task_id
from pa_tasks
where task_id = p_task_id)
and balance_type = 'BGT'
--Added the following conditions for Bug 4859071
and project_id = g_xface_rec.project_id
and award_id = g_xface_rec.award_id
and rownum =1;
g_error_stage := 'Calling delete pending transactions';
gms_funds_control_pkg.delete_pending_txns(l_err_code, l_err_buf);
select decode(t_system_linkage_function(1), 'ST', 'ST',
'OT', 'ST',
'USG', 'USG',
'PJ', 'USG',
'ER', 'ER',
'INV', 'USG',
'WIP', 'USG',
'VI', 'VI')
into l_system_linkage
from dual;
SELECT DECODE(po_distribution_id,NULL,'N','Y')
INTO l_po_matched_flag
FROM ap_invoice_distributions
WHERE invoice_id = g_xface_rec.invoice_id
AND invoice_distribution_id = g_xface_rec.invoice_distribution_id
AND line_type_lookup_code = 'ITEM';
if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
g_error_stage := 'Could not get burdenable raw cost..fail';
if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
g_error_stage := 'Could not get burdenable raw cost..fail';
-- Start of code for Commitment line record inserting in gms_bc_packets
IF l_adl_fully_paid <> 'Y' THEN -- Will be YES for fully paid cash based accounting invoice
l_bc_pkt.project_id := g_xface_rec.project_id;
if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
g_error_stage := 'Could not get burdenable raw cost..fail';
-- End of code for Commitment line record insertion in gms_bc_packets
-- Start of code for actual line record insertion in gms_bc_packets
l_bc_pkt.project_id := t_project_id(i);
-- calculate the burdenable raw cost and update bc_packet entries.
if not gms_cost_plus_extn.update_bc_pkt_burden_raw_cost(g_packet_id, 'R') then
g_error_stage := 'Could not get burdenable raw cost..fail';
g_error_stage := 'Calling update top task and parent resource for non-FCd txns';
gms_cost_plus_extn.update_top_tsk_par_res(g_packet_id);
Insert_Arrival_Order_Seq(g_packet_id, 'R');
g_error_stage := 'Update the status and results codes for non-fcd txns';
update gms_bc_packets gbc
set gbc.result_code = 'P76',
gbc.award_result_code = 'P76',
gbc.top_task_result_code = 'P76',
gbc.task_result_code = 'P76',
gbc.res_grp_result_code = 'P76',
gbc.res_result_code = 'P76'
where gbc.packet_id = g_packet_id
and nvl(result_code, 'P76') like 'P%';
update gms_bc_packets
set document_header_id = txn_interface_id,
document_distribution_id = 1,
document_type = 'EXP',
actual_flag = 'A'
where packet_id = g_packet_id;
update gms_bc_packets
set document_header_id = txn_interface_id,
document_distribution_id = 1,
document_type = 'EXP',
actual_flag = 'A'
where packet_id = g_packet_id
AND effect_on_funds_code = 'D';
-- Update bc records where entered_dr <>0 OR entered_cr <> 0
-- This update is for payment with exchange rate variance in cash based accounting ,
update gms_bc_packets
set document_header_id = txn_interface_id,
document_distribution_id = 1,
document_type = 'EXP',
actual_flag = 'A'
where packet_id = g_packet_id
AND bc_packet_id NOT IN (SELECT l_ap_bc_pkt_id
FROM DUAL
UNION ALL
SELECT bc_packet_id
FROM gms_bc_packets
WHERE parent_bc_packet_id = l_ap_bc_pkt_id
AND packet_id = g_packet_id )
AND (entered_dr <> 0 OR entered_cr <>0 ) ;
update gms_bc_packets
set document_header_id = txn_interface_id,
document_distribution_id = 1,
document_type = 'EXP',
entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
1, g_xface_rec.acct_raw_cost, 0),
entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
-1, abs(g_xface_rec.acct_raw_cost), 0)
where packet_id = g_packet_id
and ind_compiled_set_id = l_new_compiled_set_id
and parent_bc_packet_id is null
and document_type = 'AP'
and nvl(burden_adjustment_flag, 'N') = 'N'
AND entered_dr = 0
AND entered_cr = 0 ;
update gms_bc_packets
set document_type = 'EXP',
document_header_id = txn_interface_id,
document_distribution_id = 1
where packet_id = g_packet_id
and document_type = 'AP'
and parent_bc_packet_id in ( select a.bc_packet_id
from gms_bc_packets a
where a.ind_compiled_set_id = l_new_compiled_set_id
and a.document_type = 'EXP'
and a.packet_id = g_packet_id )
AND entered_dr = 0
AND entered_cr = 0 ;
update gms_bc_packets
set entered_dr = decode(sign(g_xface_rec.acct_raw_cost),
-1, abs(g_xface_rec.acct_raw_cost), 0),
entered_cr = decode(sign(g_xface_rec.acct_raw_cost),
1, g_xface_rec.acct_raw_cost, 0)
where packet_id = g_packet_id
and ind_compiled_set_id = g_xface_rec.ind_compiled_set_id
and document_type = 'AP'
and parent_bc_packet_id is null
and nvl(burden_adjustment_flag, 'N') = 'N'
AND entered_dr = 0
AND entered_cr = 0 ;
insert into gms_bc_packets(
packet_id,
project_id,
award_id,
task_id,
expenditure_type,
expenditure_item_date,
actual_flag,
status_code,
transfered_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
set_of_books_id,
je_category_name,
je_source_name,
document_type,
ind_compiled_set_id,
expenditure_organization_id,
document_header_id,
document_distribution_id,
entered_dr,
entered_cr,
bc_packet_id,
request_id,
person_id,
job_id,
expenditure_category,
revenue_category,
adjusted_document_header_id,
award_set_id,
transaction_source,
burdenable_raw_cost --R12 AP lines uptake :Forward port bug 4217161
) values
(
g_packet_id,
t_project_id(i),
t_award_id(i),
t_task_id(i),
t_expenditure_type(i),
t_expenditure_item_date(i),
l_actual_flag,
l_status_code,
'N',
l_last_update,
fnd_global.user_id,
fnd_global.user_id,
l_last_update,
fnd_global.login_id,
g_set_of_books_id,
decode(t_system_linkage_function(i),
'OT', 'Labor Cost',
'ST', 'Labor Cost',
'ER', 'Purchase Invoices',
'VI', 'Purchase Invoices',
'USG', 'Usage Cost',
'INV', 'Inventory',
'PJ', 'Miscellaneous Transaction',
'WIP', 'WIP'),
l_je_source,
l_doc_type,
t_ind_compiled_set_id(i),
t_expenditure_organization_id(i),
t_document_header_id(i),
t_document_distribution_id(i),
t_entered_dr(i),
t_entered_cr(i),
gms_bc_packets_s.nextval,
g_request_id,
t_person_id(i),
t_job_id(i),
t_expenditure_category(i),
t_revenue_category(i),
t_adjusted_document_header_id(i),
t_award_set_id(i),
t_transaction_source(i),
t_burdenable_raw_cost(i) ); --R12 AP lines uptake :Forward port bug 4217161);
INSERT into gms_bc_packets ( packet_id,
bc_packet_id,
document_header_id,
document_distribution_id,
Document_type,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
bud_task_id,
top_task_id,
entered_dr,
entered_cr,
budget_version_id,
burdenable_raw_cost,
actual_flag,
status_code,
set_of_books_id,
je_category_name,
je_source_name,
transfered_flag,
status_flag,
result_code,
request_id ,
ind_compiled_set_id,
effect_on_funds_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
gl_bc_packets_rowid,
vendor_id,
expenditure_category, --Bug: 5003642
revenue_category) values --Bug: 5003642
(p_bc_pkt.packet_id,
gms_bc_packets_s.nextval,
p_bc_pkt.document_header_id,
p_bc_pkt.document_distribution_id,
p_bc_pkt.Document_type,
p_bc_pkt.project_id,
p_bc_pkt.task_id,
p_bc_pkt.award_id,
p_bc_pkt.expenditure_type,
p_bc_pkt.expenditure_item_date,
p_bc_pkt.expenditure_organization_id,
p_bc_pkt.bud_task_id,
p_bc_pkt.top_task_id,
p_bc_pkt.entered_dr,
p_bc_pkt.entered_cr,
p_bc_pkt.budget_version_id,
p_bc_pkt.burdenable_raw_cost,
p_bc_pkt.actual_flag,
p_bc_pkt.status_code,
p_bc_pkt.set_of_books_id,
p_bc_pkt.je_category_name,
p_bc_pkt.je_source_name,
p_bc_pkt.transfered_flag,
p_bc_pkt.status_flag,
p_bc_pkt.result_code,
p_bc_pkt.request_id,
p_bc_pkt.ind_compiled_set_id,
p_bc_pkt.effect_on_funds_code,
l_last_update,
fnd_global.user_id,
fnd_global.user_id,
l_last_update,
fnd_global.login_id,
p_bc_pkt.gl_bc_packets_rowid,
p_bc_pkt.vendor_id,
p_bc_pkt.expenditure_category, --Bug: 5003642
p_bc_pkt.revenue_category); --Bug: 5003642
select adjusted_document_header_id,
nvl(ind_compiled_set_id,-1) ind_compiled_set_id
from gms_bc_packets
where packet_id = p_packetid
having sum(entered_dr-entered_cr) = 0
group by adjusted_document_header_id,
nvl(ind_compiled_set_id,-1);
update gms_bc_packets gbc
set gbc.result_code = 'F08',
gbc.award_result_code = 'F08',
gbc.top_task_result_code = 'F08',
gbc.task_result_code = 'F08',
gbc.res_grp_result_code = 'F08',
gbc.res_result_code = 'F08',
gbc.status_code = 'R'
where gbc.packet_id = p_packetid
and nvl(gbc.result_code,'XX') <> 'P82'
and gbc.adjusted_document_header_id is NOT NULL
and gbc. adjusted_document_header_id <> gbc.document_header_id
and exists
(select 1
from gms_award_distributions adl
where adl.expenditure_item_id = gbc.adjusted_document_header_id
and nvl(adl.fc_status, 'N') = 'N'
and adl.adl_status = 'A'
and nvl(adl.request_id,-1) <> gbc.request_id
) ;
update gms_bc_packets gbc
set gbc.result_code = 'P82',
gbc.award_result_code = 'P82',
gbc.top_task_result_code = 'P82',
gbc.task_result_code = 'P82',
gbc.res_grp_result_code = 'P82',
gbc.res_result_code = 'P82',
gbc.effect_on_funds_code = 'I'
where gbc.packet_id = p_packetid
and gbc.adjusted_document_header_id = recs.adjusted_document_header_id /* bug 3604195 */
and nvl(gbc.ind_compiled_set_id,-1) = recs.ind_compiled_set_id;
select '1'
into l_exists
from dual
where exists (select '1' from gms_concurrency_control
where process_name = 'GMSFCSYS'
and request_id = g_request_id);
insert into gms_concurrency_control
(PROCESS_NAME,
PROCESS_KEY ,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATE_LOGIN )
values('GMSFCSYS',
DECODE(p_system_linkage,
'ST', 1,
'USG', 2,
'ER', 3,
'VI', 4),
g_request_id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
select distinct document_header_id,
result_code
from gms_bc_packets gbc
where packet_id = g_packet_id
and substr(nvl(result_code, 'P75'), 1, 1) = 'F'
and result_code not in ('F75', 'F63')
and document_type = 'EXP';
update pa_expenditure_items
set cost_distributed_flag = decode(cost_distributed_flag,'Y','N',cost_distributed_flag),
cost_dist_rejection_code = fc_result_code(i) /*Added for bug 7047986 */
/* decode(fc_result_code(i), -- Commented for bug 7047986
'F10','F143',
'F90','F10',
'F91','F110',
'F92','F108',
'F93','F109',
'F60','F111',
'F12','F118',
'F89','F142',
'F15','F01',
'F16','F122',
'F17','F122',
'F18','F02',
'F19','F03',
'F21','F04',
'F13','F128',
'F14','F128',
'F94','F128',
'F67','F05',
'F73','F05',
'F78','F05',
'F79','F05',
'F95','F05',
'F40','F06',
'F44','F06',
'F45','F06',
'F46','F06',
'F47','F06',
'F48','F06',
'F49','F06',
'F76','F06',
'F50','F07',
'F51','F07',
'F52','F07',
'F53','F07',
'F54','F07',
fc_result_code(i)) */ -- Mapping GMS to PA code
where expenditure_item_id = fc_expenditure_item_id(i)
and request_id = g_request_id;
delete from pa_cost_distribution_lines
where expenditure_item_id = fc_expenditure_item_id(i)
and request_id = g_request_id
and line_num in (select document_distribution_id
from gms_bc_packets
where document_header_id = fc_expenditure_item_id(i)
and packet_id = g_packet_id
and parent_bc_packet_id is null
and document_type = 'EXP');
update pa_cost_distribution_lines
set reversed_flag = NULL
where expenditure_item_id = fc_expenditure_item_id(i)
and nvl(reversed_flag, 'N') = 'Y'
and request_id = g_request_id;
fc_expenditure_item_id.delete;
fc_result_code.delete;
PROCEDURE insert_arrival_order_seq (x_packetid IN NUMBER,
x_mode IN VARCHAR2) IS
x_err_code NUMBER;
g_error_stage := 'insert_arrival_order_seq';
SELECT 0
INTO x_err_code
FROM gms_concurrency_control
WHERE process_name = 'GMSFCTRL'
FOR UPDATE;
SELECT gms_bc_packet_arrival_order_s.NEXTVAL
INTO x_arrival_order_seq
FROM DUAL;
INSERT INTO gms_bc_packet_arrival_order
(packet_id,
arrival_seq,
last_update_date,
last_updated_by)
VALUES (x_packetid,
x_arrival_order_seq,
SYSDATE,
fnd_global.user_id);
END insert_arrival_order_seq;
if not gms_cost_plus_extn.update_source_burden_raw_cost(g_packet_id, 'R', 'Y') then
p_return_status := -1;
g_error_stage := 'Update Source Burden Raw Cost failed..rollback';
g_error_stage := 'Calling update_gms_bc_packets from FundsCheck_TieBack';
update_gms_bc_packets(g_process, g_request_id);
g_error_stage := 'Calling Delete_concurrency_records from FundsCheck_TieBack';
delete_concurrency_records;
Procedure Update_GMS_BC_Packets(p_process IN VARCHAR2,
p_request_id IN NUMBER) is
Begin
g_error_stage := 'Update_GMS_BC_Packets...start';
gms_error_pkg.gms_debug ('Update GMS BC Packets start..', 'C');
update gms_bc_packets
set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
'P', 'A',
'R')
where request_id = p_request_id
and status_code = 'P';
update gms_bc_packets
set status_code = decode(substr(nvl(result_code, 'F65'), 1, 1),
'P', 'A',
'R')
where packet_id = g_packet_id
and status_code = 'P';
gms_error_pkg.gms_debug ('Update_GMS_BC_Packets...end', 'C');
End Update_GMS_BC_Packets;
select cdl.expenditure_item_id, cdl.line_num
from pa_cost_distribution_lines cdl,
--pa_expenditure_items_all exp,
gms_bc_packets gbc
where gbc.packet_id = g_packet_id
and gbc.parent_bc_packet_id is null
and gbc.status_code = 'A'
--and exp.expenditure_item_id = gbc.document_header_id
and cdl.expenditure_item_id = gbc.document_header_id
and cdl.request_id + 0 = p_request_id
and mod(gbc.document_distribution_id,2) = 0
--and exp.cost_distributed_flag = 'Y'
and cdl.reversed_flag = 'Y';
select gbc.rowid, txn.expenditure_item_id
from pa_transaction_interface_all txn, gms_bc_packets gbc
where gbc.request_id = p_request_id
and txn.txn_interface_id = gbc.document_header_id
and nvl(txn.transaction_status_code, 'Z') <> 'R'
and gbc.status_code = 'P'
and substr(nvl(result_code, 'F'), 1, 1) = 'P'
and gbc.document_type = 'EXP';
select gbc.document_header_id,
gbc.document_distribution_id,
cdl.billable_flag,
gbc.resource_list_member_id,
gbc.bud_task_id,
nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0) raw_cost,
gbc.status_code,
gbc.ind_compiled_set_id,
gbc.burdenable_raw_cost
from gms_bc_packets gbc,
pa_cost_distribution_lines cdl
where gbc.packet_id = g_packet_id
and gbc.document_header_id = cdl.expenditure_item_id
and gbc.document_distribution_id = cdl.line_num
and gbc.document_distribution_id = 1
and gbc.parent_bc_packet_id is null
and gbc.status_code = 'A';
update gms_award_distributions
set ind_compiled_set_id = v_ind_compiled_set_id(i),
billable_flag = v_billable_flag(i),
cdl_line_num = v_line_num(i),
cost_distributed_flag = 'Y',
resource_list_member_id = v_rlmi(i),
bud_task_id = v_bud_task_id(i),
raw_cost = v_raw_cost(i),
fc_status = v_status_code(i),
burdenable_raw_cost = v_burdenable_raw_cost(i)
where expenditure_item_id = v_expenditure_item_id(i)
and adl_status = 'A'
and fc_status = 'N'
and nvl(cdl_line_num,1) = 1;
insert into gms_award_distributions(
AWARD_SET_ID,
ADL_LINE_NUM,
DISTRIBUTION_VALUE,
RAW_COST,
DOCUMENT_TYPE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_ITEM_ID,
CDL_LINE_NUM,
IND_COMPILED_SET_ID,
REQUEST_ID,
LINE_NUM_REVERSED,
RESOURCE_LIST_MEMBER_ID,
ADL_STATUS,
FC_STATUS,
LINE_TYPE,
CAPITALIZED_FLAG,
REVERSED_FLAG,
REVENUE_DISTRIBUTED_FLAG,
BILLED_FLAG,
BILL_HOLD_FLAG,
BURDENABLE_RAW_COST,
COST_DISTRIBUTED_FLAG,
BUD_TASK_ID,
BILLABLE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN)
select gbc.award_set_id,
gbc.document_distribution_id,
100,
nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0),
'EXP',
gbc.project_id,
gbc.task_id,
gbc.award_id,
gbc.document_header_id,
cdl.line_num,
gbc.ind_compiled_set_id,
cdl.request_id,
cdl.line_num_reversed,
gbc.resource_list_member_id,
'A', -- adl_status
'A', -- fc_status
'R', -- line_type
'N', -- capitalized_flag
NULL, -- reversed_flag
'N', -- revenue_distributed_flag
'N', -- billed_flag
exp.bill_hold_flag,
gbc.burdenable_raw_cost,
'Y', -- cost_distributed_flag
gbc.bud_task_id,
cdl.billable_flag,
v_date,
v_userid,
v_userid,
v_date,
v_login
from pa_cost_distribution_lines cdl,
pa_expenditure_items_all exp,
gms_bc_packets gbc
where gbc.packet_id = g_packet_id
and exp.expenditure_item_id = cdl.expenditure_item_id
and cdl.expenditure_item_id = gbc.document_header_id
and cdl.line_num = gbc.document_distribution_id
and exp.cost_distributed_flag = 'Y'
and gbc.document_distribution_id > 1
and gbc.parent_bc_packet_id is null
and gbc.status_code = 'A';
-- update the reversed flag on adls.
g_error_stage := 'Created costing ADLs..update reversed flag';
update gms_award_distributions adl
set adl.reversed_flag = 'Y'
where expenditure_item_id = v_exp_item_id(i)
and cdl_line_num = v_cdl_line_num(i)
and document_type = 'EXP'; --added for bug 6622800
update gms_bc_packets
set document_header_id = v_expenditure_item_id(i)
where rowid = v_rowid(i);
insert into gms_award_distributions(
AWARD_SET_ID,
ADL_LINE_NUM,
DISTRIBUTION_VALUE,
RAW_COST,
DOCUMENT_TYPE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_ITEM_ID,
CDL_LINE_NUM,
IND_COMPILED_SET_ID,
REQUEST_ID,
LINE_NUM_REVERSED,
RESOURCE_LIST_MEMBER_ID,
ADL_STATUS,
FC_STATUS,
LINE_TYPE,
CAPITALIZED_FLAG,
REVERSED_FLAG,
REVENUE_DISTRIBUTED_FLAG,
BILLED_FLAG,
BILL_HOLD_FLAG,
BURDENABLE_RAW_COST,
COST_DISTRIBUTED_FLAG,
BUD_TASK_ID,
BILLABLE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN)
select gms_adls_award_set_id_s.NEXTVAL,
gbc.document_distribution_id,
100,
nvl(gbc.entered_dr, 0) - nvl(gbc.entered_cr, 0),
'EXP',
gbc.project_id,
gbc.task_id,
gbc.award_id,
gbc.document_header_id,
cdl.line_num,
gbc.ind_compiled_set_id,
cdl.request_id,
cdl.line_num_reversed,
gbc.resource_list_member_id,
'A', -- adl_status
'A', -- fc_status
'R', -- line_type
'N', -- capitalized_flag
NULL, -- reversed_flag
'N', -- revenue_distributed_flag
'N', -- billed_flag
exp.bill_hold_flag,
gbc.burdenable_raw_cost,
'Y', -- cost_distributed_flag
gbc.bud_task_id,
cdl.billable_flag,
v_date,
v_userid,
v_userid,
v_date,
v_login
from pa_cost_distribution_lines cdl,
pa_expenditure_items_all exp,
gms_bc_packets gbc
where gbc.rowid = v_rowid(i)
and exp.expenditure_item_id = cdl.expenditure_item_id
and cdl.expenditure_item_id = gbc.document_header_id
and cdl.line_num = gbc.document_distribution_id
and gbc.document_type = 'EXP'
and gbc.parent_bc_packet_id is null
and gbc.status_code = 'P'
and substr(nvl(result_code, 'F'), 1, 1) = 'P';
v_rowid.delete;
v_expenditure_item_id.delete;
Procedure Delete_Concurrency_Records is
Begin
g_error_stage := 'Delete_Concurrency_Records..starts';
delete from gms_concurrency_control
where process_name = 'GMSFCSYS'
and request_id = g_request_id;
g_error_stage := 'Delete_Concurrency_Records..end';
End Delete_Concurrency_Records;
select xface.acct_raw_cost
FROM pa_transaction_interface xface
WHERE xface.transaction_source = G_txn_source
and xface.cdl_system_reference2 = p_sys_ref2
and xface.cdl_system_reference5 = p_sys_ref5
and (xface.cdl_system_reference4 = p_sys_ref4 OR p_sys_ref4 IS NULL)
and xface.SC_XFER_CODE = 'V'
and xface.interface_id = p_interface_id
and xface.TRANSACTION_STATUS_CODE = 'P';
t_txn_interface_id.delete;
t_transaction_source.delete;
t_invoice_id.delete;
t_invoice_distribution_id.delete;
t_sys_ref4.delete;
t_project_id.delete;
t_task_id.delete;
t_award_id.delete;
t_ind_compiled_set_id.delete;
t_burdenable_raw_cost.delete;
t_bud_task_id.delete;
t_expenditure_type.delete;
t_expenditure_item_date.delete;
t_expenditure_organization_id.delete;
t_acct_raw_cost.delete;
t_expenditure_category.delete;
t_revenue_category.delete;
t_adjusted_expenditure_item_id.delete;
t_nz_adj_flag.delete ;
SELECT
g_xface_rec.txn_interface_id,
g_xface_rec.transaction_source,
g_xface_rec.invoice_id,
g_xface_rec.invoice_distribution_id,
g_xface_rec.project_id,
g_xface_rec.task_id,
g_xface_rec.award_id,
g_xface_rec.ind_compiled_set_id,
g_xface_rec.burdenable_raw_cost,
g_xface_rec.bud_task_id,
g_xface_rec.expenditure_type,
g_xface_rec.expenditure_item_date,
g_xface_rec.expenditure_organization_id,
g_xface_rec.acct_raw_cost,
g_xface_rec.expenditure_category,
g_xface_rec.revenue_category_code,
g_xface_rec.adjusted_expenditure_item_id,
g_xface_rec.net_zero_adjustment_flag
BULK COLLECT INTO
t_txn_interface_id,
t_transaction_source,
t_invoice_id,
t_invoice_distribution_id,
t_project_id,
t_task_id,
t_award_id,
t_ind_compiled_set_id,
t_burdenable_raw_cost,
t_bud_task_id,
t_expenditure_type,
t_expenditure_item_date,
t_expenditure_organization_id,
t_acct_raw_cost,
t_expenditure_category,
t_revenue_category,
t_adjusted_expenditure_item_id,
t_nz_adj_flag
FROM DUAL;
Insert into gms_bc_packets
( PACKET_ID,
PROJECT_ID,
AWARD_ID,
TASK_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,
PERIOD_NAME,
PERIOD_YEAR,
PERIOD_NUM,
DOCUMENT_HEADER_ID ,
DOCUMENT_DISTRIBUTION_ID,
TOP_TASK_ID,
BUDGET_VERSION_ID,
BUD_TASK_ID,
RESOURCE_LIST_MEMBER_ID,
ACCOUNT_TYPE,
ENTERED_DR,
ENTERED_CR ,
TOLERANCE_AMOUNT,
TOLERANCE_PERCENTAGE,
OVERRIDE_AMOUNT,
EFFECT_ON_FUNDS_CODE ,
RESULT_CODE,
GL_BC_PACKETS_ROWID,
BC_PACKET_ID,
PARENT_BC_PACKET_ID,
VENDOR_ID,
REQUEST_ID,
IND_COMPILED_SET_ID,
AWARD_SET_ID,
TRANSACTION_SOURCE,
EXPENDITURE_CATEGORY, --Bug: 5003642
REVENUE_CATEGORY) --Bug: 5003642
select
gbc.PACKET_ID,
gbc.PROJECT_ID,
gbc.AWARD_ID,
gbc.TASK_ID,
icc.EXPENDITURE_TYPE,
trunc(gbc.EXPENDITURE_ITEM_DATE),
gbc.ACTUAL_FLAG,
gbc.STATUS_CODE,
gbc.LAST_UPDATE_DATE,
gbc.LAST_UPDATED_BY,
gbc.CREATED_BY,
gbc.CREATION_DATE,
gbc.LAST_UPDATE_LOGIN,
gbc.SET_OF_BOOKS_ID,
gbc.JE_CATEGORY_NAME,
gbc.JE_SOURCE_NAME,
gbc.TRANSFERED_FLAG,
gbc.DOCUMENT_TYPE,
gbc.EXPENDITURE_ORGANIZATION_ID,
gbc.PERIOD_NAME,
gbc.PERIOD_YEAR,
gbc.PERIOD_NUM,
gbc.DOCUMENT_HEADER_ID ,
gbc.DOCUMENT_DISTRIBUTION_ID,
gbc.TOP_TASK_ID,
gbc.BUDGET_VERSION_ID,
gbc.BUD_TASK_ID,
NULL,
gbc.ACCOUNT_TYPE,
pa_currency.round_currency_amt(decode(sign(gbc.BURDENABLE_RAW_COST * nvl(cm.compiled_multiplier,0)), 1, gbc.burdenable_raw_cost * nvl(cm.compiled_multiplier, 0), 0)),
pa_currency.round_currency_amt(decode(sign(gbc.BURDENABLE_RAW_COST * nvl(cm.compiled_multiplier,0)), -1, abs(gbc.burdenable_raw_cost * nvl(cm.compiled_multiplier, 0)), 0)), --> bug 3637934
gbc.TOLERANCE_AMOUNT,
gbc.TOLERANCE_PERCENTAGE,
gbc.OVERRIDE_AMOUNT,
gbc.EFFECT_ON_FUNDS_CODE ,
gbc.RESULT_CODE,
gbc.gl_bc_packets_rowid,
gms_bc_packets_s.nextval,
gbc.BC_PACKET_ID,
gbc.vendor_id,
gbc.request_id,
gbc.ind_compiled_set_id,
gbc.award_set_id,
gbc.transaction_source,
et.expenditure_category, --Bug: 5003642
et.revenue_category_code --Bug: 5003642
from --pa_ind_rate_sch_revisions irsr, /*6054504*/
--pa_cost_bases cb, /*6054504*/
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
PA_COST_BASE_COST_CODES CBCC, /*6054504*/
--pa_ind_rate_schedules_all_bg irs, /*6054504*/
--pa_ind_compiled_sets ics, /*6054504*/
pa_compiled_multipliers cm,
gms_bc_packets gbc
where 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 = 'INDIRECT COST'
and cbet.expenditure_type = gbc.expenditure_type
and cm.ind_compiled_set_id = gbc.ind_compiled_set_id
and cm.compiled_multiplier <> 0
and cbcc.cost_plus_structure = cbet.cost_plus_structure
and cbcc.cost_base = cbet.cost_base
and cbcc.cost_base_type = cbet.cost_base_type
and cm.cost_base_cost_code_Id = cbcc.cost_base_cost_code_Id
and cm.ind_cost_code = cbcc.ind_cost_code
and gbc.burdenable_raw_cost <> 0
and gbc.packet_id = p_packet_id;
cursor pkt_for_summary_update is
select distinct packet_id
from gms_bc_packets
where request_id = p_request_id
and substr(nvl(result_code, 'P65'), 1, 1) = 'P'
and status_code = 'P';
select distinct gbp.packet_id,
xface.transaction_rejection_code
from pa_transaction_interface_all xface,
gms_bc_packets gbp
where to_number(gbp.gl_bc_packets_rowid) = xface.txn_interface_id
and gbp.request_id = p_request_id
and gbp.parent_bc_packet_id is null
and xface.transaction_status_code = 'R'
and substr(nvl(gbp.result_code, 'Z'), 1, 1) <> 'F'
and gbp.status_code = 'P';
update gms_bc_packets
set result_code = 'F89',
status_code = 'T',
fc_error_message = 'PA_FC_ERROR: ' ||
v_reject_code(i)
where packet_id = v_packet_id(i);
open pkt_for_summary_update;
fetch pkt_for_summary_update into x_packet_id;
if pkt_for_summary_update%ROWCOUNT = 0 then
v_all_pkts_failed := 'Y';
close pkt_for_summary_update;
if pkt_for_summary_update%NOTFOUND then
close pkt_for_summary_update;
gms_cost_plus_extn.update_source_burden_raw_cost(x_packet_id, 'R', 'Y') then
g_error_stage := 'TieBack_Xface: Error returned from update_source_burden_raw_cost';
UPDATE gms_award_distributions adl
SET adl.payment_status_flag = 'Y'
WHERE (adl.invoice_id ,adl.invoice_distribution_id) IN
(SELECT trx.cdl_system_reference2,trx.cdl_system_reference5
FROM gms_bc_packets pkt,
pa_transaction_interface_all trx
WHERE pkt.request_id = p_request_id
AND pkt.txn_interface_id = trx.txn_interface_id
AND substr(nvl(pkt.result_code, 'P65'), 1, 1) = 'P'
AND pkt.status_code = 'P'
AND pkt.document_type = 'AP'
AND pkt.parent_bc_packet_id IS NULL
AND pa_trx_import.g_finalPaymentId = trx.cdl_system_reference4) -- Final payment
AND adl.document_type ='AP'
AND adl.adl_status = 'A' ;
g_error_stage := 'TieBack_Xface: Calling update_gms_bc_packets';
update_gms_bc_packets('Interface', p_request_id);
insert into gms_award_distributions(
AWARD_SET_ID,
ADL_LINE_NUM,
DISTRIBUTION_VALUE,
RAW_COST,
DOCUMENT_TYPE,
PROJECT_ID,
TASK_ID,
AWARD_ID,
EXPENDITURE_ITEM_ID,
CDL_LINE_NUM,
IND_COMPILED_SET_ID,
REQUEST_ID,
LINE_NUM_REVERSED,
RESOURCE_LIST_MEMBER_ID,
ADL_STATUS,
FC_STATUS,
LINE_TYPE,
CAPITALIZED_FLAG,
REVERSED_FLAG,
REVENUE_DISTRIBUTED_FLAG,
BILLED_FLAG,
BILL_HOLD_FLAG,
BURDENABLE_RAW_COST,
COST_DISTRIBUTED_FLAG,
BUD_TASK_ID,
BILLABLE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN)
select gms_adls_award_set_id_s.NEXTVAL,
1,
100,
cdl.amount,
'EXP',
txn.project_id,
txn.task_id,
adl.award_id,
cdl.expenditure_item_id,
cdl.line_num,
cdl.ind_compiled_set_id,
cdl.request_id,
cdl.line_num_reversed,
adl.resource_list_member_id,
'A', -- adl_status
'A', -- fc_status
'R', -- line_type
'N', -- capitalized_flag
NULL, -- reversed_flag
'N', -- revenue_distributed_flag
'N', -- billed_flag
ei.bill_hold_flag,
cdl.amount,
'Y', -- cost_distributed_flag
adl.bud_task_id,
cdl.billable_flag,
v_date,
v_userid,
v_userid,
v_date,
v_login
from pa_transaction_interface_all txn,
pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl,
ap_invoice_distributions_all apd,
gms_award_distributions adl
where txn.interface_id = p_xface_id
and nvl(txn.transaction_status_code, 'Z') <> 'R'
and txn.batch_name = p_batch
and txn.adjusted_expenditure_item_id = 0
and txn.expenditure_item_id = cdl.expenditure_item_id
and txn.expenditure_item_id = ei.expenditure_item_id
and txn.cdl_system_reference5 = apd.invoice_distribution_id
and apd.award_id = adl.award_set_id
and adl.adl_line_num = 1
and not exists ( select 1 from gms_award_distributions adl2
where adl2.expenditure_item_id = ei.expenditure_item_id
and adl2.document_type = 'EXP'
and adl2.adl_status = 'A'
and adl2.adl_line_num = 1 );
select distinct txn_interface_id
from gms_bc_packets
where packet_id = p_packet_id
and substr(result_code, 1, 1) = 'F';
update pa_transaction_interface
set transaction_rejection_code = 'GMS_FC_ERROR',
transaction_status_code = 'R'
where txn_interface_id = v_txn_interface_id;