[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_bc_packet_has_P82_records Varchar2(1); -- Used in handle_net_zero_txn/update_fc_sequence
Select nvl(gpt.sponsored_flag,'N')
into x_sponsored_flag
from gms_project_types gpt,
pa_projects_all pp
where pp.project_id = p_project_id
and gpt.project_type = pp.project_type;
select budget_version_id
from gms_budget_versions
where budget_version_id in
(select budget_version_id from gms_bc_packets_bvid)
for update;
Procedure : update_status_on_failed_txns
Purpose : Update status code on failed transactions. Used for expenditure items.
-------------------------------------------------------------------------------------------------- */
Procedure update_status_on_failed_txns(p_packet_id IN Number)
IS
BEGIN
g_error_procedure_name := 'update_status_on_failed_txns';
UPDATE gms_bc_packets
SET status_code = 'R'
WHERE packet_id = p_packet_id
AND result_code like 'F%';
END update_status_on_failed_txns;
Procedure : delete_pending_txns
Purpose : This procedure will delete pending records in gms_bc_packets associated with a
request that has been terminated.
After deleting the records from gms_bc_packets, corresponding request_id entry will
be deleted from gms_concurrency_control table.
-------------------------------------------------------------------------------------------------- */
Procedure delete_pending_txns
(x_err_code OUT NOCOPY NUMBER,
x_err_buff OUT NOCOPY VARCHAR2 ) IS
RESOURCE_BUSY EXCEPTION;
select request_id
from gms_concurrency_control
where request_id <> nvl(l_request_id,-1)
and process_name = 'GMSFCSYS';
g_error_procedure_name := 'delete_pending_txns';
select phase_code
into l_phase_code
from fnd_concurrent_requests
where request_id = c_request_id.request_id;
Update gms_bc_packets
set status_code = 'T',
fc_error_message = 'Packet had terminated,status updated to (T) by document_type,request_id:'||l_request_id||g_doc_type
where request_id = c_request_id.request_id
and status_code = 'P';
Delete
from gms_concurrency_control
where request_id = c_request_id.request_id;
End delete_pending_txns;
/* Update gms_bc_packets bp Commented for bug 5726575; Moved below the update below next update
(select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') or
(p_level = 'ALL' and substr(nvl(bp1.result_code,'P'),1,1) = 'F')
)
);*/
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F63',null,'F63',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'RAW_BURDEN_FAILURE at stage:'||p_level,bp.fc_error_message)
where bp.packet_id = p_packet_id
and bp.status_code = 'P'
and ((p_level = 'RES' and substr(bp.res_result_code,1,1) = 'P') or
(p_level = 'RESG' and substr(bp.res_grp_result_code,1,1) = 'P') or
(p_level = 'TSK' and substr(bp.task_result_code,1,1) = 'P') or
(p_level = 'TTSK' and substr(bp.top_task_result_code,1,1)= 'P') or
(p_level = 'AWD' and substr(bp.award_result_code,1,1) = 'P') or
(p_level = 'ALL' and substr(nvl(bp.result_code,'P'),1,1) = 'P')
)
and bp.parent_bc_packet_id IS NULL
and exists
(select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.parent_bc_packet_id = bp.bc_packet_id -- GMS_BC_PACKETS_N3
and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') or
(p_level = 'ALL' and substr(nvl(bp1.result_code,'P'),1,1) = 'F')
)
);
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F75',null,'F75',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'RAW_BURDEN_FAILURE at stage:'||p_level,bp.fc_error_message)
where bp.packet_id = p_packet_id
and bp.status_code = 'P'
and ((p_level = 'RES' and substr(bp.res_result_code,1,1) = 'P') or
(p_level = 'RESG' and substr(bp.res_grp_result_code,1,1) = 'P') or
(p_level = 'TSK' and substr(bp.task_result_code,1,1) = 'P') or
(p_level = 'TTSK' and substr(bp.top_task_result_code,1,1)= 'P') or
(p_level = 'AWD' and substr(bp.award_result_code,1,1) = 'P') or
(p_level = 'ALL' and substr(bp.result_code,1,1) = 'P')
)
and bp.parent_bc_packet_id IS NOT NULL
and exists
(select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.bc_packet_id = bp.parent_bc_packet_id -- GMS_BC_PACKETS_U1
and substr(bp1.result_code,1,1) = 'F'
/*and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') or
(p_level = 'ALL' and substr(bp1.result_code,1,1) = 'F')
)*/
);
Select 1
into l_dummy
from dual
where exists
(select 1 from gms_bc_packets bp1
where bp1.packet_id = p_packet_id
and (bp1.status_code in ('R','F') or
(p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') --or
--(p_level = 'ALL' and substr(bp1.result_code,1,1) = 'F')
)
);
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (R/U/C/I mode) at stage:'||p_level,bp.fc_error_message)
where packet_id = p_packet_id
and status_code = 'P';
select 1
into l_dummy
from dual
where exists (select 1
from gms_bc_packets
where packet_id = p_packet_id
and document_type = 'AP'
and substr(result_code,1,1) = 'F');
update gms_bc_packets
set result_code = 'F65',
fc_error_message = decode(fc_error_message,NULL,'FULL_MODE_FAILURE (Related invoice distribution failed) '||p_level,fc_error_message)
where packet_id = p_packet_id
and substr(result_code,1,1) = 'P'
and (document_header_id,document_distribution_id) in
( select distinct b.invoice_id,b.invoice_distribution_id
from ap_invoice_distributions_all a,
ap_invoice_distributions_all b
where (a.invoice_id,a.invoice_distribution_id) in
(select document_header_id,
document_distribution_id
from gms_bc_packets gbc
where gbc.packet_id = p_packet_id
and substr(gbc.result_code,1,1) = 'F'
and gbc.document_type = 'AP'
and gbc.parent_bc_packet_id is null)
and b.invoice_id = a.invoice_id
and COALESCE(b.charge_applicable_to_dist_id,b.related_id,b.invoice_distribution_id) =
COALESCE(a.charge_applicable_to_dist_id,a.related_id,a.invoice_distribution_id));
gms_error_pkg.gms_debug (SQL%rowcount||' records updated','C');
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X/E/R/U/C mode, INV matched to PO has failed) at stage:'||p_level,bp.fc_error_message)
where bp.document_type = 'PO'
and bp.packet_id = p_packet_id
and bp.status_code = 'P'
and bp.document_distribution_id in ( select distinct apid.po_distribution_id
from gms_bc_packets bp1,
ap_invoice_distributions_all apid
where bp1.packet_id = p_packet_id
and bp1.document_type = 'AP'
and bp1.document_distribution_id = apid.invoice_distribution_id
and substr(bp1.result_code,1,1) = 'F'
and apid.po_distribution_id IS NOT NULL ) ;
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X/E/R/U/C mode, one of the burden failed) at stage:'||p_level,bp.fc_error_message)
where bp.packet_id = p_packet_id
and bp.status_code = 'P'
and bp.document_type in ('EXP','ENC','AP','PO','REQ')
and bp.parent_bc_packet_id is NOT NULL
and exists (select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id /* Changed the order for Bug 6043224 */
and bp1.parent_bc_packet_id = bp.parent_bc_packet_id
and bp1.bc_packet_id <> bp.bc_packet_id /* Uncommented for Bug 6043224 */
and bp1.document_type = bp.document_type /* Uncommented for Bug 6043224 */
and bp1.parent_bc_packet_id is NOT NULL
and bp1.document_header_id = bp.document_header_id /* Added for Bug 6043224 */
and bp1.document_distribution_id = bp.document_distribution_id /* Added for Bug 6043224 */
and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') or
(p_level = 'ALL' and substr(bp1.result_code,1,1) = 'F')
)
);
Update gms_bc_packets bp
set bp.status_code = decode(p_mode,'C','F','R'),
bp.result_code = decode(substr(bp.result_code,1,1),'P','F65',null,'F65',bp.result_code),
bp.fc_error_message = decode(bp.fc_error_message,NULL,'FULL_MODE_FAILURE (X mode - one of the CDL failed) at stage:'||p_level,bp.fc_error_message)
where bp.packet_id = p_packet_id
and bp.status_code = 'P'
and bp.document_type = 'EXP'
and exists (select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.document_header_id = bp.document_header_id
and bp1.document_distribution_id <> bp.document_distribution_id
and bp1.document_type = bp.document_type
and ((p_level = 'RES' and substr(bp1.res_result_code,1,1) = 'F') or
(p_level = 'RESG' and substr(bp1.res_grp_result_code,1,1) = 'F') or
(p_level = 'TSK' and substr(bp1.task_result_code,1,1) = 'F') or
(p_level = 'TTSK' and substr(bp1.top_task_result_code,1,1)= 'F') or
(p_level = 'AWD' and substr(bp1.award_result_code,1,1) = 'F') or
(p_level = 'ALL' and substr(bp1.result_code,1,1) = 'F')
)
);
transactions are present in the same packet. If so, update the
transactions result_code with 'P82' and effect_on_funds_code to
'I'. This will ensure that 'Funds Available' calculations are
not carried out for these transactions.
-------------------------------------------------------------------------------------------*/
Procedure Handle_net_zero_txn(p_packetid IN number, p_mode IN varchar2 ) is
-- R12 Funds management uptake
PRAGMA AUTONOMOUS_TRANSACTION;
select adjusted_document_header_id,
nvl(ind_compiled_set_id,-1) ind_compiled_set_id
from gms_bc_packets
where packet_id = p_packetid
and document_type = 'ENC'
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 gbc.document_type = 'ENC'
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_encumbrance_items gei
where gei.encumbrance_item_id = gbc.adjusted_document_header_id
and nvl(gei.enc_distributed_flag,'N') = 'N'
and nvl(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
and nvl(ind_compiled_set_id,-1) = recs.ind_compiled_set_id;
select distinct gbc.project_id,
gbc.award_id,
gbc.task_id,
gbc.expenditure_item_date,
gbc.expenditure_type,
gbc.expenditure_organization_id,
null
BULK COLLECT into t_project_id,
t_award_id,
t_task_id,
t_exp_date,
t_exp_type,
t_exp_org,
t_ind_set
from gms_bc_packets gbc
where gbc.packet_id = p_packet_id
and gbc.status_code = 'P'
and gbc.ind_compiled_set_id is null
and nvl(gbc.burden_adjustment_flag,'N') = 'N' -- 3389292
and (gbc.adjusted_document_header_id is NULL OR
gbc.adjusted_document_header_id = gbc.document_header_id);
/* Bug#7034365 :Modified this update to pick up ind_compiled_set_id from gms_award_distributions
so as to ensure that we relieve burden component same as it was reserved for a PO/AP/REQ transaction
*/
FORALL j IN t_project_id.FIRST .. t_project_id.LAST
Update /*+ index(gbc GMS_BC_PACKETS_N1) */ gms_bc_packets gbc /*Added hint for bug 5683910 */
set ind_compiled_Set_id = (nvl((select ind_compiled_set_id from gms_award_distributions
where document_type = gbc.document_type
and ((document_type = 'AP' and
invoice_id = gbc.document_header_id and
distribution_line_number = gbc.document_distribution_id) OR
(document_type = 'PO' and
po_distribution_id = gbc.document_distribution_id) OR
(document_type = 'REQ' and
distribution_id = gbc.document_distribution_id))
and burdenable_raw_cost<>0
),t_ind_set(j))
)
where packet_id = p_packet_id
and project_id = t_project_id(j)
and award_id = t_award_id(j)
and task_id = t_task_id(j)
and expenditure_item_date = t_exp_date(j)
and expenditure_type = t_exp_type(j)
and expenditure_organization_id = t_exp_org(j)
and status_code = 'P' --Bug 5726575
and ind_compiled_set_id is null --Bug 5726575
and nvl(burden_adjustment_flag,'N') = 'N' --Bug 5726575
and (adjusted_document_header_id is NULL OR --Bug 5122879
adjusted_document_header_id = document_header_id);
Update gms_bc_packets gbc
set gbc.ind_compiled_Set_id = (Select gbc1.ind_compiled_set_id
from gms_bc_packets gbc1
where gbc1.packet_id = p_packet_id
and gbc1.document_header_id = gbc.adjusted_document_header_id
/* bug 6414366 start */
and gbc1.document_distribution_id =
(select max(gbc2.document_distribution_id)
from gms_bc_packets gbc2
where gbc2.packet_id = p_packet_id
and gbc2.document_header_id = gbc.adjusted_document_header_id))
/* bug 6414366 end */
where gbc.packet_id = p_packet_id
and gbc.status_code = 'P'
--and gbc.result_code = 'P82'
and gbc.ind_compiled_set_id is null
and gbc.adjusted_document_header_id is not NULL
and nvl(gbc.burden_adjustment_flag,'N') = 'N' -- 3389292
and gbc.adjusted_document_header_id <> gbc.document_header_id;
gms_error_pkg.gms_debug ( 'After Update 1','C');
Update gms_bc_packets gbc
set gbc.ind_compiled_Set_id = (Select nvl(gei.ind_compiled_set_id, adl.ind_compiled_set_id) --Bug 5122879
from gms_encumbrance_items gei,
gms_award_distributions adl
where adl.expenditure_item_id =gbc.adjusted_document_header_id
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and nvl(adl.reversed_flag, 'N') = 'N'
and adl.line_num_reversed is null
and adl.document_type = 'ENC'
and gei.encumbrance_item_id = adl.expenditure_item_id)
where gbc.packet_id = p_packet_id
and gbc.status_code = 'P'
and gbc.result_code is NULL
and gbc.ind_compiled_set_id is null
and gbc.adjusted_document_header_id is not NULL
and nvl(gbc.burden_adjustment_flag,'N') = 'N' -- 3389292
and gbc.adjusted_document_header_id <> gbc.document_header_id;
PROCEDURE status_code_update (p_packet_id NUMBER, p_mode VARCHAR2, p_partial VARCHAR2 DEFAULT 'N') IS
x_err_code NUMBER;
SELECT 1
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND SUBSTR (nvl(result_code,'F65'), 1, 1) = 'F' ;
CURSOR update_status IS
SELECT document_header_id, document_type, result_code,
status_code, entered_dr, entered_cr,
bud_task_id, project_id, resource_list_member_id,
document_distribution_id, task_id, expenditure_item_date,
expenditure_type , -- Bug 3003584
award_id, expenditure_organization_id, packet_id,
bc_packet_id, ind_compiled_set_id -- Added for bug : 2927485
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND parent_bc_packet_id IS NULL
AND nvl(burden_adjustment_flag,'N') = 'N'
AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
AND document_type IN ('REQ','PO','AP');
CURSOR update_status1 IS
SELECT document_header_id, document_type, result_code,
status_code, entered_dr, entered_cr,
bud_task_id, project_id, resource_list_member_id,
document_distribution_id, task_id, expenditure_item_date,
expenditure_type , -- Bug 3003584
award_id, expenditure_organization_id, packet_id,
bc_packet_id, ind_compiled_set_id, -- Added for bug : 2927485
set_of_books_id --Bug 5845974
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND parent_bc_packet_id IS NULL
AND nvl(burden_adjustment_flag,'N') = 'N'
AND status_code in ('A','B') --Added to fix bug 2138376 from 'B'*/
AND document_type in ('EXP','ENC');
CURSOR update_status_enc IS --Bug 5726575
SELECT gbp.document_header_id,
gbp.document_type,
gbp.result_code,
gbp.document_distribution_id,
adl.ind_compiled_set_id,
gbp.packet_id
FROM gms_bc_packets gbp,
gms_award_distributions adl
WHERE gbp.document_header_id = adl.expenditure_item_id
and gbp.document_distribution_id = adl.adl_line_num
and gbp.packet_id = p_packet_id
AND gbp.parent_bc_packet_id IS NULL
AND nvl(gbp.burden_adjustment_flag,'N') = 'N'
AND gbp.status_code in ('A','B')
AND gbp.document_type in ('ENC')
and adl.document_type = 'ENC'
and adl.adl_status = 'A'
and nvl(adl.reversed_flag, 'N') <> 'Y'
and adl.line_num_reversed is null;
g_error_procedure_name := 'status_code_update';
gms_error_pkg.gms_debug ( 'STATUS_CODE_UPDATE - START ','C');
UPDATE gms_bc_packets
SET status_code = decode(p_mode,'S','E','C','F','R'),
result_code =
DECODE (SUBSTR (NVL (result_code, 'F65'), 1, 1), 'P','F65', NVL(result_code,'F65')), --Bug 2092791 Added NVL Clause
fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
WHERE packet_id = p_packet_id;
UPDATE gms_bc_packets
SET status_code = decode(p_mode,'S','S','B','B','C','C','A')
WHERE packet_id = p_packet_id;
gms_error_pkg.gms_debug ('STATUS_CODE_UPDATE - SUBMIT UPDATE FOR PASS TRANSACTIONS', 'C');
UPDATE gms_bc_packets
SET status_code = DECODE (SUBSTR (nvl(result_code,'F65'), 1, 1), 'P', 'A', 'R'),
fc_error_message = decode(fc_error_message,NULL,g_error_procedure_name,fc_error_message)
WHERE packet_id = p_packet_id;
gms_error_pkg.gms_debug ('STATUS_CODE_UPDATE - NO RECORDS UPDATED IN PARTIAL MODE', 'C');
gms_error_pkg.gms_debug ('STATUS_CODE_UPDATE - NO RECORDS UPDATED ', 'C');
OPEN update_status;
FETCH update_status
BULK COLLECT INTO
tdocument_header_id, tdocument_type, tresult_code,
tstatus_code, tentered_dr, tentered_cr,
tbud_task_id, tproject_id, tresource_list_member_id,
tdocument_distribution_id, ttask_id, texpenditure_item_date,
texpenditure_type,
taward_id, texpenditure_organization_id, tpacket_id,
tbc_packet_id, tind_compiled_set_id
LIMIT l_batch_size;
UPDATE gms_award_distributions
SET resource_list_member_id = tresource_list_member_id(i),
bud_task_id = tbud_task_id(i),
fc_status = DECODE(p_mode,'B',fc_status,
DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R'))
WHERE DECODE(tdocument_type(i), 'AP', invoice_id, tdocument_header_id(i) ) = tdocument_header_id(i)
AND DECODE(tdocument_type(i), 'REQ', distribution_id,
'PO', po_distribution_id,
'AP', invoice_distribution_id) = tdocument_distribution_id(i)
/* Bug 5344693 : tdocument_distribution_id(i) stores the invoice_distribution_id for an AP invoice.
So for an AP invoice , tdocument_distribution_id(i) should be compared with invoice_distribution_id. */
AND adl_status = 'A'
AND document_type = tdocument_type(i)
AND project_id = tproject_id(i)
AND task_id = ttask_id(i)
AND award_id = taward_id(i);
/* Used .delete instead of assigning null table to these tables.*/
tdocument_header_id.delete;
tdocument_type.delete;
tresult_code.delete;
tstatus_code.delete;
tentered_dr.delete;
tentered_cr.delete;
tbud_task_id.delete;
tproject_id.delete;
tresource_list_member_id.delete;
tdocument_distribution_id.delete;
ttask_id.delete;
texpenditure_item_date.delete;
taward_id.delete;
texpenditure_organization_id.delete;
tpacket_id.delete;
tbc_packet_id.delete;
texpenditure_type.delete;
tind_compiled_set_id.delete;
EXIT WHEN update_status%NOTFOUND;
CLOSE update_status;
open update_status1;
FETCH update_status1
BULK COLLECT INTO
tdocument_header_id, tdocument_type, tresult_code,
tstatus_code, tentered_dr, tentered_cr,
tbud_task_id, tproject_id, tresource_list_member_id,
tdocument_distribution_id, ttask_id, texpenditure_item_date,
texpenditure_type,
taward_id, texpenditure_organization_id, tpacket_id,
tbc_packet_id, tind_compiled_set_id, tset_of_books_id --Bug 5845974
LIMIT l_batch_size;
UPDATE gms_award_distributions
SET cost_distributed_flag =
DECODE(P_MODE,'B',cost_distributed_flag,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'Y', 'N')),
fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'A', 'R')),
raw_cost = DECODE(P_MODE,'B',RAW_COST,NVL (tentered_dr(i), 0) - NVL (tentered_cr(i), 0)),
bud_task_id = tbud_task_id(i),
resource_list_member_id = tresource_list_member_id(i),
ind_compiled_set_id = DECODE(P_MODE,'B',ind_compiled_set_id, tind_compiled_set_id(i))
WHERE expenditure_item_id = tdocument_header_id(i)
AND adl_line_num = decode(tdocument_type(i), 'ENC', tdocument_distribution_id(i), adl_line_num)--Bug 5726575
AND cdl_line_num = decode(tdocument_type(i), 'ENC', 1, tdocument_distribution_id(i)) /* Bug 6066845 */
AND document_type = tdocument_type(i)
AND adl_status = 'A';
g_error_stage := 'UPDATE_ENC_ITEM';
UPDATE gms_encumbrance_items_all
SET enc_distributed_flag = DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'Y', 'N'),
ind_compiled_set_id = tind_compiled_set_id(i)
WHERE encumbrance_item_id = tdocument_header_id(i)
AND tdocument_type(i) = 'ENC';
g_error_stage := 'UPDATE_ADL_WITH_GL_DATE';
update gms_award_distributions
set gl_date = pa_utils2.get_prvdr_gl_date(texpenditure_item_date(i), 101, tset_of_books_id(i))
where document_type = 'ENC'
and adl_status = 'A'
and expenditure_item_id = tdocument_header_id(i)
and adl_line_num = tdocument_distribution_id(i);
/* Used .delete instead of assigning null table to these tables. Bug# 4337250*/
tdocument_header_id.delete;
tdocument_type.delete;
tresult_code.delete;
tstatus_code.delete;
tentered_dr.delete;
tentered_cr.delete;
tbud_task_id.delete;
tproject_id.delete;
tresource_list_member_id.delete;
tdocument_distribution_id.delete;
ttask_id.delete;
texpenditure_item_date.delete;
taward_id.delete;
texpenditure_organization_id.delete;
tpacket_id.delete;
tbc_packet_id.delete;
texpenditure_type.delete;
tind_compiled_set_id.delete;
EXIT WHEN update_status1%NOTFOUND;
close update_status1;
open update_status_enc;
FETCH update_status_enc
BULK COLLECT INTO tdocument_header_id,
tdocument_type,
tresult_code,
tdocument_distribution_id,
tind_compiled_set_id,
tpacket_id
LIMIT l_batch_size;
UPDATE gms_encumbrance_items_all
SET enc_distributed_flag = DECODE (SUBSTR (tresult_code(i), 1, 1), 'P', 'Y', 'N'),
ind_compiled_set_id = tind_compiled_set_id(i)
WHERE encumbrance_item_id = tdocument_header_id(i);
tdocument_header_id.delete;
tdocument_type.delete;
tresult_code.delete;
tdocument_distribution_id.delete;
tpacket_id.delete;
tind_compiled_set_id.delete;
EXIT WHEN update_status_enc%NOTFOUND; /*bug 5840237 */
close update_status_enc;
IF update_status%ISOPEN THEN
CLOSE update_status;
IF update_status1%ISOPEN THEN
CLOSE update_status1;
IF update_status_enc%ISOPEN THEN
CLOSE update_status_enc;
END status_code_update;
PROCEDURE result_status_code_update (
p_packet_id IN NUMBER,
p_status_code IN VARCHAR2,
p_result_code IN VARCHAR2,
p_bc_packet_id IN NUMBER DEFAULT NULL,
p_fc_error_message IN VARCHAR2 DEFAULT NULL ) IS
x_err_code NUMBER;
gms_error_pkg.gms_debug ('BEGIN result_status_code_update', 'C');
g_error_procedure_name := 'result_status_code_update' ;
UPDATE gms_bc_packets
SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
WHERE packet_id = p_packet_id;
UPDATE gms_bc_packets
SET status_code = decode(status_code,'P',p_status_code,'I',p_status_code,status_code),
result_code = decode(substr(result_code,1,1),'F',result_code,p_result_code),
fc_error_message = decode(fc_error_message,null,p_fc_error_message,fc_error_message)
WHERE packet_id = p_packet_id
AND bc_packet_id = p_bc_packet_id;
gms_error_pkg.gms_debug ('END result_status_code_update', 'C');
END result_status_code_update;
SELECT DISTINCT adl.award_set_id,
adl.document_type, -- Bug 2433889
adl.invoice_id, -- Bug 2433889
adl.invoice_distribution_id -- Bug 2433889
FROM ap_invoice_distributions_all ap,
gms_award_distributions adl
WHERE ap.invoice_distribution_id = p_inv_dist_id
AND ap.award_id IS NOT NULL
AND ap.award_id = adl.award_set_id
AND adl.adl_line_num = 1;
SELECT *
FROM gms_award_distributions
WHERE document_type = 'EXP'
AND adl_status = 'A'
AND expenditure_item_id = NVL (p_expenditure_item_id, -1)
AND cdl_line_num = NVL (p_cdl_line_num, -1);
SELECT adl.award_set_id, adl.adl_status
FROM gms_award_distributions adl,
po_req_distributions pd
WHERE pd.distribution_id = p_distribution_id
AND pd.award_id = adl.award_set_id
AND adl.adl_line_num = 1
AND not exists (select 1 from gms_award_distributions gad
where gad.award_set_id = pd.award_id
and gad.document_type = 'REQ'
and gad.distribution_id = pd.distribution_id
and gad.adl_status = 'A');
SELECT pod.award_id award_set_id,
adl.adl_status -- Bug 2155774
FROM po_distributions_all pod,
gms_award_distributions adl
WHERE pod.po_distribution_id = p_po_distribution_id
AND pod.award_id IS NOT NULL
AND pod.award_id = adl.award_set_id
AND adl.adl_line_num = 1 -- Bug 2155774
AND (adl.document_type = 'REQ'
OR NOT EXISTS (SELECT 1
FROM gms_award_distributions gad
WHERE gad.award_set_id = pod.award_id
AND gad.po_distribution_id = pod.po_distribution_id
AND gad.adl_status = 'A')); -- Bug 2155774, added to pick distribution lines
UPDATE gms_award_distributions adl
SET adl_status = 'I'
WHERE adl.adl_status = 'A'
AND (adl.award_set_id,adl.document_type) IN ( SELECT adl2.award_set_id,adl2.document_type
FROM gms_award_distributions adl2,
po_req_distributions_all pd
WHERE g_document_type_tab(i) = 'REQ'
AND adl2.document_type = 'REQ'
AND adl2.adl_line_num = 1
AND adl2.distribution_id = pd.distribution_id
AND pd.distribution_id = g_doc_dist_id_tab(i)
AND pd.award_id <> adl2.award_set_id
UNION ALL
SELECT adl2.award_set_id,adl2.document_type
FROM gms_award_distributions adl2,
po_distributions_all pd
WHERE g_document_type_tab(i) = 'PO'
AND adl2.document_type = 'PO'
AND adl2.adl_line_num = 1
AND adl2.po_distribution_id = pd.po_distribution_id
AND pd.po_distribution_id = g_doc_dist_id_tab(i)
AND pd.award_id <> adl2.award_set_id);
-- and also update the distribution line to create a
-- link between distribution line and ADLS.
-- gms_awards_dist_pkg.copy_adls( p_award_set_id IN NUMBER ,
-- P_NEW_AWARD_SET_ID OUT NOCOPY NUMBER,
-- p_doc_type IN varchar2,
-- p_dist_id IN NUMBER,
-- P_INVOICE_ID IN NUMBER DEFAULT NULL,
-- p_dist_line_num IN NUMBER DEFAULT NULL )
-- --------------------------------------------------------------------
g_error_stage := 'MISC_ADL: REQ and PO ';
update gms_award_distributions
set adl_status = 'A'
where award_set_id = l_award_set_id ;
update gms_award_distributions
set adl_status = 'I'
where award_set_id = l_award_set_id ;
-- AP invoice distribution lines having ADls inactive. This occurs due to update
-- in when validate item. We are changing the status to 'I' when award change.
-- Now if someone clears record then ADLs stays at inactive status.
-- =============================================================================
-- 2308005 ( CLEARING INVOICE DIST. LINE AFTER CHANGING AWARD MAKES ADL STATUS 'I' ).
-- ===================
FOR i in 1..g_set_of_books_id_tab.count LOOP
update gms_award_distributions adl
set adl.adl_status = 'A'
where adl.document_type = 'AP'
and adl.adl_status = 'I'
and adl.award_set_id in ( select adl2.award_set_id
from gms_award_distributions adl2,
ap_invoice_distributions_all apd
where apd.invoice_id = g_doc_header_id_tab(i)
AND apd.invoice_distribution_id = g_doc_dist_id_tab(i)
and apd.award_id is not null
and adl2.award_set_id = apd.award_id
and adl2.invoice_id = apd.invoice_id
and adl2.document_type = 'AP'
and adl2.invoice_distribution_id = apd.invoice_distribution_id
and adl2.adl_status = 'I' ) ;
-- and also update the distribution line to create a
-- link between distribution line and ADLS.
-- gms_awards_dist_pkg.copy_adls( p_award_set_id IN NUMBER ,
-- P_NEW_AWARD_SET_ID OUT NOCOPY NUMBER,
-- p_doc_type IN varchar2,
-- p_dist_id IN NUMBER,
-- P_INVOICE_ID IN NUMBER DEFAULT NULL,
-- p_dist_line_num IN NUMBER DEFAULT NULL )
-- --------------------------------------------------------------------
-- Find out NOCOPY do we need to create ADLS
-- ----------------------------------
-- Bug 2433889 : Added following If statement to incorporate cursor logic in the
-- If statement
-- R12 Funds Managment Uptake : Unique identifier of invoice distribution is invoice_distribution_id
OPEN c_ap(g_doc_dist_id_tab(i));
UPDATE ap_invoice_distributions_all
SET award_id = x_rec.award_set_id
WHERE invoice_id = g_doc_header_id_tab(i)
AND invoice_distribution_id = g_doc_dist_id_tab(i);
PROCEDURE UPDATE_BC_PKT_BRC_STATUS ( p_packet_id IN NUMBER,
p_result_code IN VARCHAR2,
p_partial_flag IN VARCHAR2,
p_mode IN VARCHAR2 ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT count(*)
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND status_code in ( 'I' ,'P')
AND substr(result_code,1,1) = 'F' ;
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_STATUS'||':'|| 'Start','C');
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_STATUS'||':'|| 'Updating gms and gl packets to failed status F76/F67','C');
result_status_code_update ( p_packet_id=> p_packet_id,
p_status_code=> 'T',
p_result_code=> 'F76');
/*UPDATE gl_bc_packets
SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
WHERE packet_id = P_packet_id; */
gms_error_pkg.gms_debug ('UPDATE_BC_PKT_BRC_STATUS'||':'|| 'Updating gms packets to Full mode failure','C');
-- If failure, update result/status code
Update gms_bc_packets
set status_code = decode(p_mode,'C','F','R'),
result_code =decode(result_code,null,'F65',
decode(substr(result_code,1,1),'P','F65',result_code)),
fc_error_message = decode(fc_error_message,NULL,
'COPY_GL_PKT_TO_GMS_PKT: Post burden calculation Check',fc_error_message)
where packet_id = p_packet_id
AND status_code in ( 'I' ,'P');
END UPDATE_BC_PKT_BRC_STATUS;
SELECT SYSDATE
INTO l_sysdate
FROM DUAL;
gms_error_pkg.gms_debug ('Load_gms_pkts'||':'|| 'Starting loop to insert '||l_rec_count||'into gms_bc_packets','C');
INSERT INTO gms_bc_packets
(packet_id,
set_of_books_id,
je_source_name,
je_category_name,
actual_flag,
project_id,
task_id,
award_id,
result_code,
status_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
entered_dr,
entered_cr,
expenditure_type,
burdenable_raw_cost,
expenditure_organization_id,
expenditure_item_date,
document_type,
document_header_id,
document_distribution_id,
transfered_flag,
account_type,
bc_packet_id,
vendor_id,
expenditure_category,
revenue_category,
request_id,
ind_compiled_set_id,
source_event_id)
SELECT p_packet_id
,p_set_of_books_id_tab(i)
,p_je_source_name_tab(i)
,p_je_category_name_tab(i)
,p_actual_flag_tab(i)
,p_project_id_tab(i)
,p_task_id_tab(i)
,p_award_id_tab(i)
,p_result_code_tab(i)
,DECODE (p_award_id_tab(i), NULL, 'R', DECODE(p_document_type_tab(i),'PO','I'
,'REQ','I'
,'AP','I')) --Check for GMSIP impact as it was always 'P'
,l_sysdate
,l_user_id
,l_user_id
,l_sysdate
,l_login_id
,p_entered_dr_tab(i)
,p_entered_cr_tab(i)
,p_etype_tab(i)
,DECODE(p_document_type_tab(i),'AP',p_burdenable_raw_cost_tab(i),NULL) --R12 AP Lines Uptake
,p_exp_org_id_tab(i)
,p_exp_item_date_tab(i)
,p_document_type_tab(i)
,p_doc_header_id_tab(i)
,p_doc_dist_id_tab(i)
,'N' -- For GMSIP 'N' should not cause an issue
,NULL
,gms_bc_packets_s.NEXTVAL
,p_vendor_id_tab(i)
,p_exp_category_tab(i)
,p_revenue_category_tab(i)
,l_request_id
,p_ind_cmp_set_id_tab(i)
,p_source_event_id_tab(i)
FROM dual;
UPDATE gms_bc_packets
SET result_code = 'F65',
fc_error_message = decode(fc_error_message,NULL,'Load_gms_pkts:Full mode failure',fc_error_message)
WHERE packet_id = p_packet_id
and SUBSTR(result_code,1,1) <> 'F' ;
SELECT pobc.ledger_id,
'Purchasing' je_source_name,
DECODE(pobc.distribution_type,
'REQUISTION','Requisitions',
'BLANKET' ,'Release',
'SCHEDULED' ,'Release',
'Purchases') je_category_name,
'E' actual_flag,
pobc.pa_project_id,
pobc.pa_task_id,
pobc.pa_award_id,
pobc.accounted_amt, -- Bug 5614467
pobc.event_type_code,
pobc.main_or_backing_code,
pobc.pa_exp_type,
pobc.pa_exp_org_id,
TRUNC (pobc.pa_exp_item_date),
DECODE(pobc.distribution_type,'REQUISITION','REQ','PO'),
pobc.header_id,
pobc.distribution_id,
et.expenditure_category,
et.revenue_category_code,
pobc.ae_event_id source_event_id,
NULL, -- result_code
NULL, -- vendor_id
NULL, -- ind_cmp_set_id
NULL, -- burdenable_raw_cost
pobc.reference6, -- For GMSIP
pobc.reference13 --Vendor id for GMSIP transactions
FROM po_bc_distributions pobc ,
psa_bc_xla_events_gt xlaevt,
pa_expenditure_types et,
gms_project_types gpt,
pa_projects_all pp
WHERE pobc.ae_event_id = xlaevt.event_id
AND pobc.pa_project_id IS NOT NULL
AND pobc.pa_project_id = pp.project_id
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y'
AND pobc.pa_exp_type = et.expenditure_type;
SELECT apd.invoice_distribution_id,
/* Commenting for Bug 5645290
apd.line_type_lookup_code */
/* Added for Bug 5645290 */
decode (apd.prepay_distribution_id ,NULL,line_type_lookup_code,'PREPAY')
/* Bug 5645290 - End */
FROM psa_bc_xla_events_gt xlaevt,
ap_invoice_distributions_all apd,
gms_project_types gpt,
pa_projects_all pp,
ap_invoices_all apinv
WHERE apd.bc_event_id = xlaevt.event_id
AND apd.project_id IS NOT NULL
AND apinv.invoice_id = apd.invoice_id
AND apd.project_id = pp.project_id
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y'
AND NVL (apd.pa_addition_flag, 'X' ) <> 'T'
--AND apinv.invoice_type_lookup_code <> 'EXPENSE REPORT' -- need to check if this check is required for Grants in R12
-- R12 : Prepayments mathed to PO will not be fundschecked
AND ((apinv.invoice_type_lookup_code = 'PREPAYMENT'
AND apd.po_distribution_id IS NULL )
OR apinv.invoice_type_lookup_code <> 'PREPAYMENT')
--R12 : Application of Prepayment matched to PO will not be fundschecked
AND ((apd.line_type_lookup_code ='PREPAY' AND
apd.po_distribution_id IS NULL) OR
apd.line_type_lookup_code <> 'PREPAY' );
SELECT 'Payables' je_source_name,
'Purchase Invoices' je_category_name,
'E' actual_flag,
apext.aid_project_id project_id,
apext.aid_task_id task_id,
apext.aid_award_id award_id,
DECODE(SIGN(apext.ENCUMBRANCE_BASE_AMOUNT),-1,0,apext.ENCUMBRANCE_BASE_AMOUNT) entered_dr, -- Bug 5614467
DECODE(SIGN(apext.ENCUMBRANCE_BASE_AMOUNT),-1,ABS(apext.ENCUMBRANCE_BASE_AMOUNT),0) entered_cr, -- Bug 5231395 -- Bug 5614467
DECODE(SIGN(apext.ENCUMBRANCE_AMOUNT),-1,0,apext.ENCUMBRANCE_AMOUNT) txn_dr, -- Bug 5614467
DECODE(SIGN(apext.ENCUMBRANCE_AMOUNT),-1,ABS(apext.ENCUMBRANCE_AMOUNT),0) txn_cr, -- Bug 5614467
apext.aid_expenditure_type expenditure_type,
apext.aid_expenditure_org_id org_id,
NULL expenditure_item_date,--populated in later code
'AP' document_type,
apext.bus_flow_inv_id invoice_id,
apext.aid_invoice_dist_id invoice_distribution_id,
NULL prepay_source_inv_id,
apext.event_id source_event_id,
NULL result_code,
NULL vendor_id,
NULL ind_cmp_set_id,
NULL burdenable_raw_cost,
apext.aid_base_quantity_variance ap_quantity_variance, -- Bug 5614467
apext.aid_base_amount_variance ap_amount_variance, -- Bug 5614467
/* Bug 5344693 : In the scenario where an Invoice is matched to a PO with variance , the ap_po_distribution_id,
ap_po_header_id and ap_po_release_id for the variance distribution should be NULL. */
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE,'ITEM',apext.po_distribution_id
,'ACCRUAL',apext.po_distribution_id
,'NONREC_TAX',apext.po_distribution_id
,NULL) ap_po_distribution_id,
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE,'ITEM',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
,'ACCRUAL',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
,'NONREC_TAX',DECODE(apext.po_distribution_id,NULL,NULL,apext.bus_flow_po_doc_id)
, NULL ) ap_po_header_id,
DECODE(apext.AID_LINE_TYPE_LOOKUP_CODE,'ITEM', DECODE(apext.bus_flow_po_dist_type,'RELEASE',apext.bus_flow_po_doc_id,NULL)
,'ACCRUAL',DECODE(apext.bus_flow_po_dist_type,'RELEASE',apext.bus_flow_po_doc_id,NULL)
,'NONREC_TAX',DECODE(apext.bus_flow_po_dist_type,'RELEASE',apext.bus_flow_po_doc_id,NULL)
,NULL) ap_po_release_id,
-- Below columns will be populated later in code
NULL set_of_books_id,
NULL exp_category,
NULL revenue_category,
NULL doc_dist_line_num,
NULL invoice_type_code,
NULL inv_source,
NULL inv_dist_reference_1,
NULL inv_dist_reference_2,
NULL ap_prepay_app_dist_id
FROM ap_extract_invoice_dtls_bc_v apext -- Bug 5500126
WHERE apext.aid_invoice_dist_id IN (select Column_Value from Table(g_ap_inv_dist_id))
AND apext.event_id in ( SELECT event_id FROM psa_bc_xla_events_gt)
-- Bug 5238282 : Prepayment application will be treated as standard invoice line for check funds
-- as there will be no data in ap_prepay_app_dists table.This table is populated during invoice
-- validation.
/* Commenting the following condition for Bug 5645290
AND (p_mode ='C' OR (apext.aid_line_type_lookup_code <> 'PREPAY' AND p_mode <>'C')) */
/* Adding for Bug 5645290*/
AND exists (
select 1
from ap_invoice_distributions_all apd
where apd.invoice_distribution_id = apext.aid_invoice_dist_id
and ((apd.prepay_distribution_id is NULL AND p_mode <>'C') OR p_mode ='C' ))
/* Bug 5645290 - End */
AND p_stdinvoice_exists = 'Y'
UNION ALL
SELECT 'Payables' je_source_name,
'Purchase Invoices' je_category_name,
'E' actual_flag,
AID.project_id project_id,
AID.task_id task_id,
AID.award_id award_id,
DECODE(SIGN(APAD.BASE_AMOUNT),-1,0,APAD.BASE_AMOUNT) entered_dr, -- Bug 5614467
DECODE(SIGN(APAD.BASE_AMOUNT),-1,ABS(APAD.BASE_AMOUNT),0) entered_cr, -- Bug 5231395 -- Bug 5614467
DECODE(SIGN(APAD.AMOUNT),-1,0,APAD.AMOUNT) txn_dr, -- Bug 5614467
DECODE(SIGN(APAD.AMOUNT),-1,ABS(APAD.AMOUNT),0) txn_cr, -- Bug 5614467
AID.expenditure_type expenditure_type,
AID.expenditure_organization_id org_id,
NULL expenditure_item_date, --populated later in code
'AP' document_type,
AID.invoice_id invoice_id,
APAD.Prepay_App_Distribution_ID invoice_distribution_id,
AID.invoice_distribution_id prepay_source_inv_id,
APPH.bc_event_id source_event_id,
NULL result_code,
NULL vendor_id,
NULL ind_cmp_set_id,
NULL burdenable_raw_cost,
NULL ap_quantity_variance,
NULL ap_amount_variance,
AID.po_distribution_id ap_po_distribution_id,
NULL ap_po_header_id,
NULL ap_po_release_id,
-- Below columns will be populated later in code
NULL set_of_books_id,
NULL exp_category,
NULL revenue_category,
NULL doc_dist_line_num,
NULL invoice_type_code,
NULL inv_source,
NULL inv_dist_reference_1,
NULL inv_dist_reference_2,
APAD.prepay_app_dist_id ap_prepay_app_dist_id
-- Last col. will be used in Synch_gms_gl_packets ...
FROM AP_PREPAY_HISTORY_ALL APPH,
AP_PREPAY_APP_DISTS APAD,
AP_INVOICE_LINES_ALL AIL,
AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.bc_event_id = APPH.bc_Event_id
AND APPH.prepay_history_id = APAD.prepay_history_id
AND AID.invoice_line_number = AIL.line_number
AND AID.invoice_id = AIL.invoice_id
AND AID.line_type_lookup_code IN ( 'PREPAY' ,'NONREC_TAX' ) --Bug 5490378
and APPH.bc_Event_id IN ( SELECT event_id FROM psa_bc_xla_events_gt)
and AID.invoice_distribution_id IN (select Column_Value from Table(g_ap_inv_dist_id))
AND p_prepay_exists = 'Y'
and aid.invoice_distribution_id = apad.prepay_app_distribution_id
and APAD.PREPAY_DIST_LOOKUP_CODE <> 'AWT';
SELECT adl.award_id,
DECODE (
adl.award_id,
l_dist_award_id, 'F21',
DECODE (adl.award_id, NULL, 'F62', NULL)), --Bug Fix 1599750(2)-- RESULT CODE for missing ADLS F62
pov.vendor_id,
adl.ind_compiled_set_id
FROM gms_award_distributions adl,
po_requisition_lines_all porl,
po_req_distributions_all pord,
po_vendors pov
WHERE pord.distribution_id = p_req_dist_id
AND pord.project_id IS NOT NULL
AND NVL (pord.award_id, l_dist_award_id) = adl.award_set_id
AND pord.requisition_line_id = porl.requisition_line_id
AND pord.distribution_id = NVL (adl.distribution_id, pord.distribution_id)
AND pord.project_id = NVL (adl.project_id, pord.project_id)
AND pord.task_id = NVL (adl.task_id, pord.task_id)
AND porl.suggested_vendor_name = pov.vendor_name (+)
AND NVL (adl.adl_status, 'I') = 'A'
AND NVL (adl.document_type, 'REQ') IN ('REQ', 'DST');
SELECT adl.award_id,
DECODE (
adl.award_id,
l_dist_award_id, 'F21',
NULL, 'F62',
decode(pll.accrue_on_receipt_flag, 'Y', 'F07',NULL)),
poh.vendor_id,
adl.ind_compiled_set_id,
pod.rate -- Bug 5614467
FROM po_distributions_all pod,
po_headers_all poh,
po_lines_all pol, --BUG 3022249
po_line_locations_all pll, -- BUG 3022249
gms_award_distributions adl
WHERE pod.po_distribution_id = p_po_dist_id
AND pod.project_id IS NOT NULL
AND pod.po_header_id = poh.po_header_id
and pol.po_header_id = poh.po_header_id
and pol.po_line_id = pod.po_line_id
and pll.line_location_id = pod.line_location_id
and pll.po_line_id = pol.po_line_id
AND NVL (pod.award_id, l_dist_award_id) = adl.award_set_id
AND pod.po_distribution_id = NVL (adl.po_distribution_id, pod.po_distribution_id)
AND pod.project_id = NVL (adl.project_id, pod.project_id)
AND pod.task_id = NVL (adl.task_id, pod.task_id)
AND NVL (adl.adl_status, 'I') = 'A' -- Bug 2092791
AND NVL (adl.document_type, 'PO') IN ('PO', 'DST');
SELECT apd.set_of_books_id,
apd.expenditure_item_date,
et.expenditure_category,
et.revenue_category_code,
apd.distribution_line_number,
api.invoice_type_lookup_code,
api.source,
apd.reference_1, --expenditure_item_id for ER imported to Payables from projects
apd.reference_2, --cdl_line_num for ER imported to Payables from projects
adl.award_id,
DECODE (
adl.award_id,
l_dist_award_id, 'F21',
DECODE (adl.award_id, NULL, 'F62', NULL)), --Bug Fix 1599750(2)-- RESULT CODE for missing ADLS F62
api.vendor_id,
adl.ind_compiled_set_id , --Bug 2456878
/* Bug 5519731 : The following code is modified such that for a reversing invoice distribution if the parent invoice
distribution is interfaced to Grants then calculate the BRC else the burdenable raw cost for the reversing
distribution is the negative of the BRC for the parent distribution. */
( SELECT decode(ap1.pa_addition_flag,'Y',NULL,-1 * nvl(adl1.burdenable_raw_cost,0)) --R12 AP Lines Uptake enhancement : Forward porting bug 4450291
-- Reversing AP distributions should copy the BRC from reversed Distribution
FROM gms_award_distributions adl1,
ap_invoice_distributions ap1
WHERE adl1.document_type = 'AP'
AND adl1.adl_status = 'A' -- Bug 5654186
AND adl1.fc_status = 'A' -- Bug 5654186
AND ap1.invoice_id = apd.invoice_id
AND ap1.invoice_distribution_id = apd.parent_reversal_id
AND apd.reversal_flag = 'Y'
AND ap1.reversal_flag ='Y'
AND apd.parent_reversal_id IS NOT NULL
AND adl1.award_set_id = ap1.award_id
AND adl1.adl_line_num =1 ) burdenable_raw_cost ,
apd.parent_reversal_id parent_reversal_id -- Bug 5369296
FROM ap_invoice_distributions_all apd,
ap_invoices_all api,
gms_award_distributions adl,
pa_expenditure_types et
WHERE apd.invoice_distribution_id = p_ap_dist_id
AND apd.project_id IS NOT NULL
AND (NVL (apd.pa_addition_flag, 'X') <> 'T')
AND NVL (apd.award_id, l_dist_award_id) = adl.award_set_id
AND apd.invoice_id = NVL (adl.invoice_id, apd.invoice_id)
AND apd.distribution_line_number =
NVL (adl.distribution_line_number, apd.distribution_line_number)
AND apd.invoice_distribution_id =
NVL (adl.invoice_distribution_id, apd.invoice_distribution_id)
AND apd.project_id = NVL (adl.project_id, apd.project_id)
AND apd.task_id = NVL (adl.task_id, apd.task_id)
AND apd.invoice_id = api.invoice_id
AND NVL (adl.adl_status, 'I') = 'A' -- Bug 2092791
AND NVL (adl.document_type, 'AP') IN ('AP', 'DST')
AND NVL (adl.fc_status, 'X') <> 'A'
AND apd.expenditure_type = et.expenditure_type;
g_set_of_books_id_tab.delete;
g_je_source_name_tab.delete;
g_je_category_name_tab.delete;
g_actual_flag_tab.delete;
g_project_id_tab.delete;
g_task_id_tab.delete;
g_award_id_tab.delete;
g_result_code_tab.delete;
g_entered_dr_tab.delete;
g_entered_cr_tab.delete;
g_txn_dr_tab.delete; -- Bug 5614467
g_txn_cr_tab.delete; -- Bug 5614467
g_po_rate_tab.delete; -- Bug 5614467
g_etype_tab.delete;
g_exp_org_id_tab.delete;
g_exp_item_date_tab.delete;
g_document_type_tab.delete;
g_doc_header_id_tab.delete;
g_doc_dist_id_tab.delete;
g_vendor_id_tab.delete;
g_exp_category_tab.delete;
g_revenue_category_tab.delete;
g_ind_cmp_set_id_tab.delete;
g_burdenable_raw_cost_tab.delete; --R12 AP Lines Uptake enhancement : Forward porting bug 4450291
g_parent_reversal_id_tab.delete; -- Bug 5369296
g_doc_dist_line_num_tab.delete;
g_invoice_type_code_tab.delete;
g_inv_source_tab.delete;
g_inv_dist_reference_1_tab.delete;
g_inv_dist_reference_2_tab.delete;
g_source_event_id_tab.delete;
g_entered_amount_tab.delete;
g_event_type_code_tab.delete;
g_main_or_backing_tab.delete;
g_ap_line_type_lkup.delete;
g_prepay_std_inv_dist_id.delete;
g_quantity_variance_tab.delete;
g_amount_variance_tab.delete;
g_po_distribution_id_tab.delete;
g_po_header_id_tab.delete;
g_po_release_id_tab.delete;
SELECT gms_bc_packets_s.nextval
INTO l_packet_id
FROM dual;
SELECT NVL (default_dist_award_id, 0),
NVL (award_distribution_option, 'N')
INTO l_dist_award_id,
l_award_dist_option
FROM gms_implementations;
-- Insert a dummy record into gms_award_distributions for the default award id
-- to remove the outer joints on gms_award_distributions.
-- --------------------------------------------------------------------------+
x_adl_rec.award_set_id := l_dist_award_id;
x_adl_rec.last_update_date := SYSDATE;
x_adl_rec.last_updated_by := 0;
x_adl_rec.last_update_login := 0;
DELETE gms_award_distributions
WHERE award_set_id = NVL (l_dist_award_id, 0)
AND document_type = 'DST'
AND adl_line_num = 1
AND adl_status = 'A'
AND request_id = -9999;
IF NOT gms_cost_plus_extn.update_bc_pkt_burden_raw_cost (l_packet_id,l_mode,p_partial_flag) THEN
x_return_code := 'F';
update_bc_pkt_brc_status ( l_packet_id,
x_return_code,
p_partial_flag,
l_mode);
DELETE gms_award_distributions
WHERE award_set_id = l_dist_award_id
AND document_type = 'DST'
AND adl_line_num = 1
AND adl_status = 'A'
AND request_id = -9999;
gms_error_pkg.gms_debug ('misc_gms_insert - Exception '||' SQLCODE:'||SQLCODE||' SQLERRM:'||SQLERRM, 'C');
SELECT document_type
INTO doc_type
FROM gms_bc_packets
WHERE packet_id = x_packet_id
AND nvl(burden_adjustment_flag,'N') = 'N'
AND ROWNUM = 1;
UPDATE gms_bc_packets gbc
SET ind_compiled_set_id = gms_cost_plus_extn.get_award_cmt_compiled_set_id (
gbc.task_id,
gbc.expenditure_item_date,
gbc.expenditure_type, --Bug 3003584
gbc.expenditure_organization_id,
'C',
gbc.award_id)
WHERE gbc.packet_id = x_packet_id
AND gbc.status_code = 'P'
AND gbc.ind_compiled_set_id is null
AND nvl(gbc.burden_adjustment_flag,'N') = 'N'; -- 3389292
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,
resource_list_member_id,
account_type,
entered_dr,
entered_cr,
tolerance_amount,
tolerance_percentage,
override_amount,
effect_on_funds_code,
result_code,
amount_type,
boundary_code,
time_phased_type_code,
categorization_code,
request_id,
gl_bc_packets_rowid,
bc_packet_id,
parent_bc_packet_id,
person_id,
job_id,
expenditure_category,
revenue_category,
adjusted_document_header_id,
award_set_id,
transaction_source,
burden_adjustment_flag,
burden_adj_bc_packet_id,
source_event_id,
session_id,
serial_id)
SELECT /*+ index(gbc GMS_BC_PACKETS_N1) */ gbc.packet_id, /* Added the index hint for performance - Bug 5656276 */
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.resource_list_member_id,
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)), /* Bug 3620801 --entered_dr*/
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 3620801 entered_cr*/
gbc.tolerance_amount,
gbc.tolerance_percentage,
gbc.override_amount,
gbc.effect_on_funds_code,
gbc.result_code,
gbc.amount_type,
gbc.boundary_code,
gbc.time_phased_type_code,
gbc.categorization_code,
gbc.request_id,
gbc.gl_bc_packets_rowid,
gms_bc_packets_s.NEXTVAL,
decode(gbc.burden_adjustment_flag,'Y',gbc.parent_bc_packet_id,gbc.bc_packet_id),
-- In case of burden adjustment flag, use parent_bc_packet_id on raw adjsutment line
gbc.person_id,
gbc.job_id,
et.expenditure_category,
et.revenue_category_code,
gbc.adjusted_document_header_id,
gbc.award_set_id,
gbc.transaction_source,
gbc.burden_adjustment_flag,
gbc.burden_adj_bc_packet_id,
gbc.source_event_id,
gbc.session_id,
gbc.serial_id
FROM /*pa_ind_rate_sch_revisions irsr, Bug 5656276 */
pa_expenditure_types et,
pa_ind_cost_codes icc,
pa_cost_base_cost_codes cbcc, -- Bug 5656276
pa_cost_base_exp_types cbet,
/*pa_ind_compiled_sets ics, Bug 5656276 */
pa_compiled_multipliers cm,
gms_bc_packets gbc
WHERE /*irsr.cost_plus_structure = cbet.cost_plus_structure Bug 5656276 */
et.expenditure_type = icc.expenditure_type -- 2092791 ( RLMI Change)
AND icc.ind_cost_code = cm.ind_cost_code
AND cbet.cost_base = cm.cost_base
AND cbcc.cost_plus_structure = cbet.cost_plus_structure
AND cbet.cost_base_type = 'INDIRECT COST'
/*AND ics.cost_base = cbet.cost_base -- Bug 3003584 Bug 5656276 */
AND cbet.expenditure_type = gbc.expenditure_type
AND cbcc.cost_base = cbet.cost_base /* Bug 5656276 start */
AND cm.cost_base_cost_code_id = cbcc.cost_base_cost_code_id
AND cm.ind_cost_code = cbcc.ind_cost_code /* Bug 5656276 end */
/*AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
AND ics.organization_id = gbc.expenditure_organization_id
AND ics.ind_compiled_set_id = gbc.ind_compiled_set_id Bug 5656276 */
AND cm.ind_compiled_set_id = gbc.ind_compiled_set_id /* Bug 5656276 */
AND cm.compiled_multiplier <> 0
AND NVL (gbc.burdenable_raw_cost, 0) <> 0
AND gbc.packet_id = x_packet_id
AND status_code in ('I', 'P');
/* This Function updates following setup columns of gms_bc_packets
budget_version_id
amount_type
boundary_code
time_phased_type_code
categorization_code
resource_list_id
effect_on_funds_code
Note : Budget Version Id is updated only if it is null , in case of
Award budget submit/Baseline Process. Budget_version_id is inserted during
insertion of records in gms_bc_packets. So if budget_version_id is
alreay present this procedure will not update budget_version_id.
The earlier logic of calculating budget_version_id in case of
mode ('S'/'B') is removed, as budget_version_id logic is already
present while inserting records in gms_bc_packets.
*/
--===============================================================================================
FUNCTION initialize_setup (x_packet_id IN NUMBER,
p_mode IN VARCHAR2)
RETURN BOOLEAN IS
x_budget_version_id gms_bc_packets.budget_version_id%TYPE;
SELECT DISTINCT project_id,
award_id,
budget_version_id
FROM gms_bc_packets
WHERE packet_id = x_packet_id
AND status_code in ('P','A') --Bug 2143160
;
SELECT NVL (default_dist_award_id, 0),
NVL (award_distribution_option, 'N')
INTO x_dist_award_id,
x_award_distribution_option
FROM gms_implementations;
SELECT pb.budget_version_id,
ga.amount_type,
ga.boundary_code,
pbm.time_phased_type_code,
pbm.categorization_code,
pb.resource_list_id
INTO x_budget_version_id,
x_amount_type,
x_boundary_code,
x_time_phased_type_code,
x_categorization_code,
x_resource_list_id
FROM gms_budget_versions pb, pa_budget_entry_methods pbm, gms_awards ga
WHERE ga.award_id = pb.award_id
AND pb.project_id = x_project_id
AND pb.award_id = x_award_id
AND pb.budget_entry_method_code = pbm.budget_entry_method_code
AND pb.award_id = ga.award_id
AND pb.budget_version_id =
DECODE (x_budget_version_id, NULL, pb.budget_version_id, x_budget_version_id)
AND pb.current_flag = DECODE (x_budget_version_id, NULL, 'Y', pb.current_flag);
UPDATE gms_bc_packets
SET budget_version_id = x_budget_version_id,
amount_type = x_amount_type,
boundary_code = x_boundary_code,
time_phased_type_code = x_time_phased_type_code,
categorization_code = x_categorization_code,
resource_list_id = x_resource_list_id,
-- Bug 2927485 : Added decode in following statement, we shouldn't
-- override effect_on_funds_code if it is already populated
effect_on_funds_code = DECODE(effect_on_funds_code,NULL,DECODE (SIGN (NVL (entered_dr, 0) - NVL (entered_cr, 0)), 1, 'D', 'I'),effect_on_funds_code) --Bug 2069132 ( code Transferred from setup_rlmi )
WHERE packet_id = x_packet_id
AND project_id = x_project_id
AND award_id = x_award_id
AND status_code in ('P','A') --Bug 2143160
;
UPDATE gms_bc_packets
SET result_code = 'F12',
status_code = decode(p_mode,'S','E','C','F','R')
WHERE packet_id = x_packet_id
AND project_id = x_project_id
AND award_id = x_award_id
AND status_code in ('P','A'); --Bug 2143160
UPDATE gms_bc_packets
SET status_code = decode(p_mode,'S','E','C','F','R'),
result_code = 'F21',
res_result_code = 'F21',
res_grp_result_code = 'F21',
task_result_code = 'F21',
top_task_result_code = 'F21',
award_result_code = 'F21'
WHERE packet_id = x_packet_id;
SELECT resource_list_id,
categorization_code,
document_type,
expenditure_type,
expenditure_organization_id,
expenditure_category,
revenue_category,
person_id,
job_id,
vendor_id,
ROWID,
resource_list_member_id,
fc_error_message
BULK COLLECT INTO t_res_list,
t_cat_code,
t_doc_type,
t_exp_type,
t_org_id,
t_exp_cat,
t_rev_cat,
t_person_id,
t_job_id,
t_vendor_id,
t_row_id,
t_rlmi_value,
t_fc_error -- Bug 2178694
FROM gms_bc_packets
WHERE packet_id = x_packet_id
AND status_code NOT IN ('F','R') -- Bug 2927485
AND resource_list_member_id is NULL
AND nvl(burden_adjustment_flag ,'N') = 'N' -- 3389292
ORDER BY resource_list_id,
categorization_code,
document_type,
expenditure_type,
expenditure_organization_id,
expenditure_category,
person_id,
vendor_id,
job_id,
revenue_category ;
UPDATE gms_bc_packets
SET status_code = decode(t_rlmi_value (bcpkt_txns),NULL,decode(x_mode,'S','E','C','F','R'),status_code),
result_code = decode(t_rlmi_value (bcpkt_txns),NULL,'F94',result_code),
resource_list_member_id = t_rlmi_value (bcpkt_txns),
fc_error_message = t_fc_error (bcpkt_txns) -- Bug 2178694
WHERE ROWID = t_row_id (bcpkt_txns);
PROCEDURE budget_task_id_update (
x_packetid IN NUMBER) IS
BEGIN
g_error_procedure_name := 'budget_task_id_update';
UPDATE gms_bc_packets bc
SET (bc.bud_task_id, bc.top_task_id) =
(SELECT DECODE (bem.entry_level_code, 'P', 0, 'L', bc.task_id, t.top_task_id),
DECODE (bem.entry_level_code, 'P', 0, t.top_task_id)
FROM pa_budget_entry_methods bem, gms_budget_versions bv,
pa_tasks t
WHERE bv.budget_version_id = bc.budget_version_id
AND bv.budget_entry_method_code = bem.budget_entry_method_code
AND bem.entry_level_code IN ('P', 'L', 'T')
AND t.task_id = bc.task_id)
WHERE bc.packet_id = x_packetid
AND bc.status_code = 'P'
AND bc.bud_task_id IS NULL
AND nvl(bc.burden_adjustment_flag,'N') = 'N' -- 3389292
AND EXISTS ( SELECT 1
FROM pa_budget_entry_methods bem1, gms_budget_versions bv1
WHERE bv1.budget_version_id = bc.budget_version_id
AND bv1.budget_entry_method_code = bem1.budget_entry_method_code
AND bem1.entry_level_code IN ('P', 'L', 'T'));
gms_error_pkg.gms_debug('BUDGET_TASK_ID_UPDATE - Update for Entry Level code P,L,T Complete ','C');
UPDATE gms_bc_packets bc
SET (bc.bud_task_id, bc.top_task_id) =
(SELECT t.task_id,
t.top_task_id
FROM pa_budget_entry_methods bem, gms_budget_versions bv,
pa_tasks t
WHERE bv.budget_version_id = bc.budget_version_id
AND bv.budget_entry_method_code = bem.budget_entry_method_code
AND bem.entry_level_code = 'M'
AND t.task_id = (SELECT task_id
FROM gms_balances
WHERE budget_version_id = bc.budget_version_id
AND project_id = bc.project_id
AND award_id = bc.award_id
AND task_id = bc.task_id
AND balance_type = 'BGT'
AND ROWNUM = 1))
WHERE bc.packet_id = x_packetid
AND bud_task_id IS NULL
AND status_code = 'P';
UPDATE gms_bc_packets bc
SET (bc.bud_task_id, bc.top_task_id) =
(SELECT t.task_id,
t.top_task_id
FROM pa_budget_entry_methods bem, gms_budget_versions bv,pa_tasks t
WHERE bv.budget_version_id = bc.budget_version_id
AND bv.budget_entry_method_code = bem.budget_entry_method_code
AND bem.entry_level_code = 'M'
AND t.task_id = (SELECT task_id
FROM gms_balances
WHERE task_id = (SELECT top_task_id
FROM pa_tasks
WHERE task_id = bc.task_id)
AND budget_version_id = bc.budget_version_id
AND project_id = bc.project_id
AND award_id = bc.award_id
AND balance_type = 'BGT'
AND ROWNUM = 1))
WHERE bc.packet_id = x_packetid
AND bud_task_id IS NULL
AND status_code = 'P';
gms_error_pkg.gms_debug('BUDGET_TASK_ID_UPDATE - Update for Entry Level code M Complete ','C');
UPDATE gms_bc_packets bc
SET (bc.bud_task_id, bc.top_task_id) =
(SELECT t.task_id,
t.top_task_id
FROM pa_tasks t
WHERE t.task_id = bc.task_id)
WHERE bc.packet_id = x_packetid
AND bc.bud_task_id IS NULL
AND status_code = 'P'
;
END budget_task_id_update;
PROCEDURE bud_res_list_id_update (
x_packetid IN NUMBER) IS
BEGIN
g_error_procedure_name := 'bud_res_list_id_update';
UPDATE gms_bc_packets gms
SET (parent_resource_id) =
(SELECT pr.parent_member_id
FROM pa_resource_list_members pr
WHERE pr.resource_list_member_id = gms.resource_list_member_id
AND ROWNUM = 1)
WHERE packet_id = x_packetid
AND status_code = 'P'
AND parent_resource_id is NULL
AND nvl(burden_adjustment_flag,'N') = 'N'; -- 3389292
END bud_res_list_id_update;
UPDATE gms_bc_packets gms
SET a_funds_control_level_code = (SELECT funds_control_level_code
FROM gms_budgetary_controls gbc
WHERE gbc.project_id = gms.project_id
AND gbc.award_id = gms.award_id
AND gbc.task_id IS NULL
AND gbc.parent_member_id IS NULL
AND gbc.resource_list_member_id IS NULL)
WHERE packet_id = x_packet_id
AND status_code = 'P'
;
gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for Award Result code Complete ','C');
UPDATE gms_bc_packets gms
SET tt_funds_control_level_code = (SELECT funds_control_level_code
FROM gms_budgetary_controls gbc
WHERE gbc.project_id = gms.project_id
AND gbc.award_id = gms.award_id
AND gbc.task_id = gms.top_task_id
AND gbc.parent_member_id IS NULL
AND gbc.resource_list_member_id IS NULL)
WHERE packet_id = x_packet_id
AND bud_task_id <> 0
AND status_code = 'P'
;
gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for Top Task Result code Complete ','C');
UPDATE gms_bc_packets gms
SET t_funds_control_level_code = (SELECT funds_control_level_code
FROM gms_budgetary_controls gbc
WHERE gbc.project_id = gms.project_id
AND gbc.award_id = gms.award_id
AND gbc.task_id = gms.task_id -- bug 2579619 : gms.bud_task_id
AND gbc.parent_member_id IS NULL
AND gbc.resource_list_member_id IS NULL)
WHERE packet_id = x_packet_id
AND bud_task_id <> 0
AND status_code = 'P'
;
gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for Task Result code Complete ','C');
UPDATE gms_bc_packets gms
SET rg_funds_control_level_code = (SELECT funds_control_level_code
FROM gms_budgetary_controls gbc
WHERE gbc.project_id = gms.project_id
AND gbc.award_id = gms.award_id
AND gbc.task_id = gms.bud_task_id
AND gbc.resource_list_member_id =
gms.parent_resource_id
AND gbc.parent_member_id = 0)
WHERE packet_id = x_packet_id
AND categorization_code <> 'N'
AND status_code = 'P'
;
gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for resource Group Result code Complete ','C');
UPDATE gms_bc_packets gms
SET r_funds_control_level_code = (SELECT funds_control_level_code
FROM gms_budgetary_controls gbc
WHERE gbc.project_id = gms.project_id
AND gbc.award_id = gms.award_id
AND gbc.task_id = gms.bud_task_id
AND gbc.resource_list_member_id =
gms.resource_list_member_id)
WHERE packet_id = x_packet_id
AND categorization_code <> 'N'
AND status_code = 'P'
;
gms_error_pkg.gms_debug('FUNDS_CTRL_LEVEL_CODE - Update for resource Level Result code Complete ','C');
UPDATE gms_bc_packets gms
SET r_funds_control_level_code =
DECODE (r_funds_control_level_code, NULL, 'N', r_funds_control_level_code),
rg_funds_control_level_code =
DECODE (rg_funds_control_level_code, NULL, 'N', rg_funds_control_level_code),
t_funds_control_level_code =
DECODE (t_funds_control_level_code, NULL, 'N', t_funds_control_level_code),
tt_funds_control_level_code =
DECODE (tt_funds_control_level_code, NULL, 'N', tt_funds_control_level_code),
a_funds_control_level_code =
DECODE (a_funds_control_level_code, NULL, 'N', a_funds_control_level_code)
WHERE packet_id = x_packet_id
AND status_code = 'P'
;
SELECT start_date,
completion_date
INTO project_start_date,
project_end_date
FROM pa_projects_all
WHERE project_id = x_project_id;
SELECT MIN (gb.start_date)
INTO l_gb_start_date
FROM gms_balances gb
WHERE gb.budget_version_id = x_budget_version_id ;
SELECT MAX (gb.END_date)
INTO l_gb_end_date
FROM gms_balances gb
WHERE gb.budget_version_id = x_budget_version_id ;
SELECT MAX (end_date) --Bug Fix 1828613 From
INTO gb_end_date
FROM gms_balances
WHERE budget_version_id = x_budget_version_id;
SELECT MAX (expenditure_item_date)
INTO exp_date
FROM gms_bc_packets
WHERE budget_version_id = x_budget_version_id;
SELECT TRUNC (gps.end_date)
INTO gl_period_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND TRUNC (exp_date) BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC (end_date)
INTO pa_period_end_date
FROM pa_periods gpa
WHERE TRUNC (exp_date) BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC (end_date)
INTO gs_end_date
FROM pa_periods gpa
WHERE project_end_date BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC (MAX (end_date))
INTO gs_end_date
FROM gms_balances
WHERE budget_version_id = x_budget_version_id;
SELECT TRUNC (gps.end_date)
INTO gs_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND project_end_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC (MAX (end_date))
INTO gs_end_date
FROM gms_balances
WHERE budget_version_id = x_budget_version_id;
SELECT gps.year_start_date
INTO year_start_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND TRUNC (x_expenditure_item_date) BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC (gps.start_date),
TRUNC (gps.end_date)
INTO gl_period_start_date,
gl_period_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND TRUNC (x_expenditure_item_date) BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC (start_date),
TRUNC (end_date)
INTO pa_period_start_date,
pa_period_end_date
FROM pa_periods gpa
WHERE TRUNC (x_expenditure_item_date) BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC (MAX (start_date)),
TRUNC (MIN (end_date))
INTO dr_period_start_date,
dr_period_end_date
FROM gms_balances
WHERE project_id = x_project_id
AND budget_version_id = x_budget_version_id
AND award_id = x_award_id
AND balance_type <> 'BGT'
AND TRUNC (x_expenditure_item_date) BETWEEN start_date AND end_date;
SELECT TRUNC (MAX (start_date)),
TRUNC (MIN (end_date))
INTO dr_period_start_date,
dr_period_end_date
FROM gms_balances
WHERE project_id = x_project_id
AND budget_version_id = x_budget_version_id
AND award_id = x_award_id
AND balance_type = 'BGT'
AND TRUNC (x_expenditure_item_date) BETWEEN start_date AND end_date;
SELECT TRUNC (gps.start_date),
TRUNC (gps.end_date)
INTO dr_period_start_date,
dr_period_end_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND TRUNC (x_expenditure_item_date) BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
Select decode(g_mode,'C','F','R') into x_error_code from dual;
result_status_code_update (
p_status_code=>x_error_code,
p_result_code=> 'F78',
p_packet_id=> x_packetid,
p_bc_packet_id=> x_bc_packet_id);
SELECT TRUNC (start_date)
INTO gs_start_date
FROM pa_periods gpa
WHERE project_start_date BETWEEN gpa.start_date AND gpa.end_date;
SELECT TRUNC (MIN (start_date))
INTO gs_start_date
FROM gms_balances
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_version_id = x_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC (gps.start_date)
INTO gs_start_date
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = x_set_of_books_id
AND project_start_date BETWEEN gps.start_date AND gps.end_date
AND gps.adjustment_period_flag = 'N';
SELECT TRUNC (MIN (start_date))
INTO gs_start_date
FROM gms_balances
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_version_id = x_budget_version_id
AND balance_type = 'BGT';
SELECT TRUNC (MIN (start_date))
INTO gs_start_date
FROM gms_balances
WHERE project_id = x_project_id
AND award_id = x_award_id
AND budget_version_id = x_budget_version_id
AND balance_type = 'BGT';
SELECT p.end_date
INTO gs_end_date
FROM pa_periods p
WHERE year_end_date BETWEEN p.start_date AND p.end_date;
SELECT p.start_date
INTO gs_start_date
FROM pa_periods p
WHERE year_start_date BETWEEN p.start_date AND p.end_date;
SELECT p.end_date
INTO gs_end_date
FROM pa_periods p
WHERE year_end_date BETWEEN p.start_date AND p.end_date;
PROCEDURE call_start_end_date_update (
x_packetid IN NUMBER ,
p_mode IN VARCHAR2) IS
TYPE t_packetid IS TABLE OF gms_bc_packets.packet_id%TYPE;
g_error_procedure_name := 'call_start_end_date_update';
SELECT project_id,
award_id,
budget_version_id,
time_phased_type_code,
expenditure_item_date,
amount_type,
boundary_code,
set_of_books_id,
bc_packet_id,
budget_period_start_date,
budget_period_end_date
BULK COLLECT INTO
t_project_id,
t_award_id,
t_budget_version_id,
t_time_phased_type_code,
t_expenditure_item_date,
t_amount_type,
t_boundary_code,
t_set_of_books_id,
t_bc_packet_id,
t_start_date,
t_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND parent_bc_packet_id IS NULL ;
UPDATE gms_bc_packets
SET status_code = DECODE (
t_start_date (bcpkt_txns),
NULL, decode(p_mode,'S','E','C','F','R'),
DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
result_code = DECODE (
t_start_date (bcpkt_txns),
NULL, DECODE (
t_time_phased_type_code (bcpkt_txns),
'R', 'F95',
'G', 'F79',
'P', 'F73',
'F78'),
DECODE (
t_end_date (bcpkt_txns),
NULL, DECODE (
t_time_phased_type_code (bcpkt_txns),
'R', 'F95',
'G', 'F79',
'P', 'F73',
'F78'),
result_code)),
budget_period_start_date = t_start_date (bcpkt_txns),
budget_period_end_date = t_end_date (bcpkt_txns)
WHERE bc_packet_id = t_bc_packet_id (bcpkt_txns);
UPDATE gms_bc_packets
SET status_code = DECODE (
t_start_date (bcpkt_txns),
NULL, decode(p_mode,'S','E','C','F','R'),
DECODE (t_end_date (bcpkt_txns), NULL, decode(p_mode,'S','E','C','F','R'), status_code)),
result_code = DECODE (
t_start_date (bcpkt_txns),
NULL, DECODE (
t_time_phased_type_code (bcpkt_txns),
'R', 'F95',
'G', 'F79',
'P', 'F73',
'F78'),
DECODE (
t_end_date (bcpkt_txns),
NULL, DECODE (
t_time_phased_type_code (bcpkt_txns),
'R', 'F95',
'G', 'F79',
'P', 'F73',
'F78'),
result_code)),
budget_period_start_date = t_start_date (bcpkt_txns),
budget_period_end_date = t_end_date (bcpkt_txns)
WHERE parent_bc_packet_id = t_bc_packet_id (bcpkt_txns);
END call_start_end_date_update;
SELECT SYSDATE
INTO l_sys_date
FROM DUAL;
SELECT 0
INTO x_err_code
FROM gms_concurrency_control
WHERE process_name = 'GMSFCTRL'
FOR UPDATE;
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 ('GMSFCTRL',
0,
0,
l_sys_date,
-1,
-1,
l_sys_date,
-1);
PROCEDURE insert_arrival_order_seq (
x_packetid IN NUMBER,
x_mode IN VARCHAR2) IS
x_err_code NUMBER;
g_error_procedure_name := '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;
-- Note : The Insert statement below should always be the last statement of the
-- setup, Before actual funds check happens. Do Not write any code after
-- this insert statement.
--*******************************************************************************
g_error_stage := 'IN ARRIVAL ORD: INSRT';
INSERT INTO gms_bc_packet_arrival_order
(packet_id,
arrival_seq,
last_update_date,
last_updated_by)
VALUES (x_packetid,
x_arrival_order_seq, -- Bug 2176230
SYSDATE,
fnd_global.user_id);
END insert_arrival_order_seq;
PROCEDURE update_burdened_cost (
x_packetid IN NUMBER ) IS
BEGIN
g_error_procedure_name := 'update_burdened_cost';
UPDATE gms_bc_packets a
SET burdened_cost =
(SELECT SUM ( NVL(entered_dr,0) - NVL(entered_cr,0) )
FROM gms_bc_packets b
WHERE b.packet_id + 0 = a.packet_id /* Bug 5689194 */
AND b.document_type = a.document_type
AND b.document_header_id = a.document_header_id
AND ((b.document_type='EXP')
OR (b.document_type<>'EXP' AND
b.document_distribution_id = a.document_distribution_id
)
)
)
WHERE packet_id = x_packetid
AND status_code = 'P'
;
END update_burdened_cost;
UPDATE gms_bc_packets gms
SET gms.status_code = 'T',
gms.result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.res_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.res_grp_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.task_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.top_task_result_code= DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.award_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19'))))))
WHERE gms.packet_id = x_packetid
AND status_code = 'P'
AND ( budget_version_id IS NULL
OR resource_list_member_id IS NULL
-- OR bud_resource_list_member_id IS NULL
OR bud_task_id IS NULL
OR amount_type IS NULL
OR boundary_code IS NULL
OR top_task_id IS NULL
-- OR decode(categorization_code,'R',parent_resource_id,1) IS NULL commented for bug 2006221
);
UPDATE gms_bc_packets bp
SET bp.result_code = nvl(bp.result_code,'F65'),
bp.status_code = 'R',
bp.fc_error_message = decode(bp.fc_error_message,NULL,'CHECK_SETUP_FAILURE - net zero txn. - full mode failure',bp.fc_error_message)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'I'
AND bp.result_code = 'P82'
AND bp.status_code = 'P'
AND bp.document_header_id <> bp.adjusted_document_header_id
AND bp.document_type in ('EXP','ENC')
AND EXISTS (select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.document_header_id = bp.adjusted_document_header_id
and SUBSTR (bp1.result_code, 1, 1) = 'F');
UPDATE gms_bc_packets bp
SET bp.result_code = nvl(bp.result_code,'F65'),
bp.status_code = 'R',
bp.fc_error_message = decode(bp.fc_error_message,NULL,'CHECK_SETUP_FAILURE - original fail as reversing fail-full mode failure',bp.fc_error_message)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'I'
AND bp.result_code = 'P82'
AND bp.status_code = 'P'
AND bp.document_header_id = bp.adjusted_document_header_id
AND bp.document_type in ('EXP','ENC')
AND EXISTS (select 1
from gms_bc_packets bp1
where bp1.packet_id = bp.packet_id
and bp1.adjusted_document_header_id = bp.document_header_id
and SUBSTR (bp1.result_code, 1, 1) = 'F');
SELECT 1
INTO x_err_count
FROM dual
WHERE EXISTS (SELECT 1
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND (
budget_version_id IS NULL
OR resource_list_member_id IS NULL
-- OR bud_resource_list_member_id IS NULL -- Bug 2605070
OR bud_task_id IS NULL
OR amount_type IS NULL
OR boundary_code IS NULL
OR top_task_id IS NULL
OR budget_period_start_date IS NULL
OR budget_period_end_date IS NULL
-- OR decode(categorization_code,'R',parent_resource_id,1) IS NULL commented for bug 2006221
));
UPDATE gms_bc_packets gms
SET gms.status_code = 'T',
gms.result_code = DECODE(result_code,NULL,DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19','F65')))))),result_code),
gms.res_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.res_grp_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.task_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.top_task_result_code= DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19')))))),
gms.award_result_code = DECODE(budget_version_id,NULL,'F12',
DECODE(resource_list_member_id,NULL,'F13',
-- DECODE(bud_resource_list_member_id,NULL,'F14',
DECODE(bud_task_id,NULL,'F15',
DECODE(amount_type,NULL,'F16',
DECODE(boundary_code,NULL,'F17',
DECODE(top_task_id,NULL,'F19'))))))
WHERE gms.packet_id = x_packetid
AND status_code = 'P';
PROCEDURE update_fc_sequence (
x_packetid IN NUMBER) IS
TYPE t_fcseq IS TABLE OF gms_bc_packets.funds_check_seq%TYPE;
select 'ALL' rcode from dual union all
select 'P82' rcode from dual
order by 1;
g_error_procedure_name := 'update_fc_sequence';
gms_error_pkg.gms_debug ( 'update_fc_sequence : Start ', 'C' );
SELECT ROWID,
funds_check_seq
BULK COLLECT INTO
t_row_id,
t_fc_seq
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND nvl(result_code,'XX') <> 'P82'
ORDER BY burdened_cost,
document_type,
document_header_id,
decode(nvl(entered_dr,0),0,1), -- Bug 2092791
-- Added to include credit transaction
-- first in a Re-costing scenario
document_distribution_id,
bc_packet_id;
t_row_id.DELETE;
t_fc_seq.DELETE;
SELECT ROWID,
funds_check_seq
BULK COLLECT INTO
t_row_id,
t_fc_seq
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND nvl(result_code,'XX') = 'P82'
ORDER BY adjusted_document_header_id,
burdened_cost,
bc_packet_id;
UPDATE gms_bc_packets
SET funds_check_seq = t_fc_seq(bcpkt_txns)
WHERE ROWID = t_row_id (bcpkt_txns);
gms_error_pkg.gms_debug ( 'update_fc_sequence : End ', 'C' );
END update_fc_sequence;
It Also inserts record in gms_bc_packet_arrival_order Table
*/
-------------------------------------------------------------------------------------------------
FUNCTION gms_setup (
x_packetid IN NUMBER,
x_mode IN VARCHAR2,
x_partial IN VARCHAR2,
x_err_code OUT NOCOPY NUMBER,
x_err_buff OUT NOCOPY VARCHAR2)
RETURN BOOLEAN IS
BEGIN
g_error_procedure_name := 'gms_setup';
gms_error_pkg.gms_debug ('GMS_SETUP - Before Burdened Cost Update ', 'C');
update_burdened_cost (x_packetid);
gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted Task Update -> packet_id'|| x_packetid, 'C');
budget_task_id_update (x_packetid);
gms_error_pkg.gms_debug ('GMS_SETUP - After Budgeted Task Update', 'C');
gms_error_pkg.gms_debug ('GMS_SETUP - Before Budgeted rlmi Update -> packet_id'|| x_packetid, 'C');
bud_res_list_id_update (x_packetid);
gms_error_pkg.gms_debug ('GMS_SETUP - After Budgeted rlmi Update', 'C');
call_start_end_date_update (x_packetid,x_mode);
update_fc_sequence(x_packetid);
insert_arrival_order_seq (x_packetid, x_mode);
update_fc_sequence(x_packetid);
gms_error_pkg.gms_debug ( 'GMS_SETUP -After Inserting Records in gms_bc_packet_arrival_order ', 'C' );
SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) r_bc_tot,
budget_version_id,
bud_task_id,
resource_list_member_id, -- Bug 2605070, Replaced bud_resource_list_member_id with this column
budget_period_start_date,
budget_period_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND
r_funds_control_level_code = 'B'
GROUP BY budget_version_id,
bud_task_id,
resource_list_member_id, -- Bug 2605070, Replaced bud_resource_list_member_id with this column
budget_period_start_date,
budget_period_end_date
ORDER BY budget_version_id,
bud_task_id,
resource_list_member_id,
budget_period_start_date,
budget_period_end_date;
SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) rg_bc_tot,
budget_version_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND rg_funds_control_level_code = 'B'
GROUP BY budget_version_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date
ORDER BY budget_version_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date;
SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) t_bc_tot,
budget_version_id,
bud_task_id,
budget_period_start_date,
budget_period_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND t_funds_control_level_code = 'B'
GROUP BY budget_version_id, bud_task_id, budget_period_start_date, budget_period_end_date
ORDER BY budget_version_id, bud_task_id, budget_period_start_date, budget_period_end_date;
SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) tt_bc_tot,
budget_version_id,
top_task_id,
budget_period_start_date,
budget_period_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND tt_funds_control_level_code = 'B'
GROUP BY budget_version_id, top_task_id,
budget_period_start_date, budget_period_end_date
ORDER BY budget_version_id, top_task_id,
budget_period_start_date, budget_period_end_date;
SELECT SUM (NVL (entered_dr, 0) - NVL (entered_cr, 0)) a_bc_tot,
budget_version_id,
budget_period_start_date,
budget_period_end_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P'
AND a_funds_control_level_code = 'B'
GROUP BY budget_version_id, budget_period_start_date, budget_period_end_date
ORDER BY budget_version_id, budget_period_start_date, budget_period_end_date;
SELECT NVL(SUM (NVL (budget_period_to_date, 0)),0)
INTO l_balance_available
FROM gms_balances
WHERE budget_version_id = x_budget_version_id
AND start_date >= x_budget_period_start_date
AND end_date <= x_budget_period_end_date
AND resource_list_member_id = x_res_list_member_id
AND task_id = x_bud_task_id;
SELECT NVL(SUM (NVL (budget_period_to_date, 0)),0)
INTO l_balance_available
FROM gms_balances
WHERE budget_version_id = x_budget_version_id
AND start_date >= x_budget_period_start_date
AND end_date <= x_budget_period_end_date
AND DECODE (
parent_member_id,
NULL, resource_list_member_id,
parent_member_id) = x_parent_resource_id
AND task_id = x_bud_task_id;
SELECT NVL(SUM (NVL (budget_period_to_date, 0)),0)
INTO l_balance_available
FROM gms_balances
WHERE budget_version_id = x_budget_version_id
AND start_date >= x_budget_period_start_date
AND end_date <= x_budget_period_end_date
AND task_id = x_bud_task_id;
SELECT NVL(SUM (NVL (budget_period_to_date, 0)),0)
INTO l_balance_available
FROM gms_balances
WHERE budget_version_id = x_budget_version_id
AND start_date >= x_budget_period_start_date
AND end_date <= x_budget_period_end_date
AND DECODE (top_task_id, NULL, task_id, top_task_id) = x_top_task_id;
SELECT NVL(SUM (NVL (budget_period_to_date, 0)),0)
INTO l_balance_available
FROM gms_balances
WHERE budget_version_id = x_budget_version_id
AND start_date >= x_budget_period_start_date
AND end_date <= x_budget_period_end_date;
gms_error_pkg.gms_debug ('BUDGET_FUNDSCHECK BEFORE P50 UPDATE '|| x_packetid, 'C');
UPDATE gms_bc_packets
SET result_code = 'P50',
award_result_code = 'P50',
res_result_code = 'P50',
res_grp_result_code = 'P50',
task_result_code = 'P50',
top_task_result_code = 'P50'
WHERE packet_id = x_packetid
AND status_code = 'P';
gms_error_pkg.gms_debug ('BUDGET_FUNDSCHECK - AFTER P50 UPDATE '|| x_packetid, 'C');
UPDATE gms_bc_packets
SET result_code = 'F25',
award_result_code = 'F25',
res_result_code = 'F25',
res_grp_result_code = 'F25',
task_result_code = 'F25',
top_task_result_code = 'F25'
WHERE packet_id = x_packetid;
UPDATE gms_bc_packets
SET result_code = 'F26',
award_result_code = 'F26',
res_result_code = 'F26',
res_grp_result_code = 'F26',
task_result_code = 'F26',
top_task_result_code = 'F26'
WHERE packet_id = x_packetid;
UPDATE gms_bc_packets
SET result_code = 'F27',
award_result_code = 'F27',
res_result_code = 'F27',
res_grp_result_code = 'F27',
task_result_code = 'F27',
top_task_result_code = 'F27'
WHERE packet_id = x_packetid;
UPDATE gms_bc_packets
SET result_code = 'F28',
award_result_code = 'F28',
res_result_code = 'F28',
res_grp_result_code = 'F28',
task_result_code = 'F28',
top_task_result_code = 'F28'
WHERE packet_id = x_packetid;
UPDATE gms_bc_packets
SET result_code = 'F29',
award_result_code = 'F29',
res_result_code = 'F29',
res_grp_result_code = 'F29',
task_result_code = 'F29',
top_task_result_code = 'F29'
WHERE packet_id = x_packetid;
SELECT arrival_seq
INTO x_arrival_seq
FROM gms_bc_packet_arrival_order ao
WHERE ao.packet_id = x_packetid;
gms_error_pkg.gms_debug ('RESOURCE LEVEL - SUMMARY INSERT ', 'C');
INSERT INTO gms_bc_packets_bvid
(packet_id,
budget_version_id,
creation_date)
SELECT DISTINCT x_packetid,
budget_version_id,
x_date
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code = 'P' -- This is to ignore Transactions which failed during setup.
;
INSERT INTO gms_bc_packets_summary
(packet_id,
creation_date,
budget_version_id,
top_task_id,
bud_task_id,
parent_resource_id,
resource_list_member_id,
budget_period_start_date,
budget_period_end_date,
actual_approved,
actual_pending,
enc_approved,
enc_pending)
SELECT
x_packetid,
x_date,
bcpkt.budget_version_id,
bcpkt.top_task_id,
bcpkt.bud_task_id,
bcpkt.parent_resource_id,
bcpkt.resource_list_member_id,
bcpkt.budget_period_start_date,
bcpkt.budget_period_end_date,
nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'AE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0),
nvl(sum(decode(bcpkt.status_code || bcpkt.actual_flag, 'PE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
FROM gms_bc_packets bcpkt,
gms_bc_packet_arrival_order ao,
gms_bc_packets_bvid a
WHERE bcpkt.status_code IN ('A', 'P')
AND bcpkt.budget_version_id = a.budget_version_id
AND bcpkt.packet_id = ao.packet_id
AND a.packet_id = x_packetid
AND ao.arrival_seq <= x_arrival_seq
AND decode(bcpkt.status_code,
'A',1,
'P',decode(SIGN(NVL(bcpkt.entered_dr,0)-NVL(bcpkt.entered_cr,0)),
-1,decode(bcpkt.packet_id,x_packetid,1,0),
1)) = 1
GROUP BY bcpkt.budget_version_id,
bcpkt.top_task_id,
bcpkt.bud_task_id,
bcpkt.parent_resource_id,
bcpkt.resource_list_member_id,
bcpkt.budget_period_start_date,
bcpkt.budget_period_end_date;
gms_error_pkg.gms_debug ('RESOURCE POSTED BALANCE UPDATE', 'C');
UPDATE gms_bc_packets bp
SET (bp.res_budget_posted, bp.res_actual_posted, bp.res_enc_posted) =
(SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
FROM gms_balances gb
WHERE gb.budget_version_id = bp.budget_version_id
AND gb.project_id = bp.project_id
AND gb.award_id = bp.award_id
AND (
(bp.bud_task_id = 0) or -- budget at project
(bp.bud_task_id > 0 and gb.task_id = bp.bud_task_id and bp.task_id = bp.bud_task_id) or -- budget at lowest task
(bp.bud_task_id > 0 and bp.top_task_id = bp.bud_task_id
and DECODE (gb.top_task_id, NULL, gb.task_id, gb.top_task_id) = bp.top_task_id ) -- top task
) -- 2379815
AND gb.resource_list_member_id = bp.resource_list_member_id -- Bug 2605070
AND gb.balance_type <> 'REV'
AND gb.start_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_end_date)
AND gb.end_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_end_date))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.categorization_code = 'R'
AND bp.r_funds_control_level_code <> 'N';
UPDATE gms_bc_packets bp
SET (bp.res_actual_approved, bp.res_actual_pending, bp.res_enc_approved,
bp.res_enc_pending) =
(SELECT
SUM(actual_approved), --Bug 2490381 : Added SUM
SUM(actual_pending), --Bug 2490381 : Added SUM
SUM(enc_approved), --Bug 2490381 : Added SUM
SUM(enc_pending) --Bug 2490381 : Added SUM
FROM gms_bc_packets_summary gmsbcs
WHERE gmsbcs.packet_id = x_packetid
AND gmsbcs.bud_task_id = bp.bud_task_id
AND gmsbcs.budget_version_id = bp.budget_version_id
AND gmsbcs.resource_list_member_id =
bp.resource_list_member_id -- Bug 2605070
--Bug 2490381 : Changed "=" to "<=" to consider all the
-- records from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
-- Bug 2897560 : changed the strat date comparision to ">="
-- as we need to consider all the recods from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
AND gmsbcs.budget_period_start_date >= bp.budget_period_start_date
AND gmsbcs.budget_period_end_date <= bp.budget_period_end_date)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.r_funds_control_level_code <> 'N'
AND bp.categorization_code = 'R' ;
SELECT (nvl(entered_dr,0)-nvl(entered_cr,0)) entered_dr, --Bug 2092791
ROWID,
budget_version_id,
bud_task_id,
resource_list_member_id, -- Bug 2605070
effect_on_funds_code, -- Bug 2927485
TRUNC ( budget_period_start_date ) budget_period_start_date,
TRUNC ( budget_period_end_date ) budget_period_end_date,
actual_flag
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND effect_on_funds_code in('D','I') --Bug 2092791
AND status_code = 'P'
AND r_funds_control_level_code <> 'N'
AND categorization_code = 'R'
-- AND budgeted_at_resource_level = 'Y' -- Bug 2605070
ORDER BY budget_version_id,
bud_task_id,
resource_list_member_id,
budget_period_start_date,
budget_period_end_date,
funds_check_seq DESC;
IF nvl(res_level.entered_dr,0)>= 0 AND res_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
UPDATE gms_bc_packets -- if funds are decreasing)
SET res_actual_pending = NVL (res_actual_pending, 0)
- DECODE (
res_level.actual_flag,
'A', NVL (x_pending_actual, 0),
0),
res_enc_pending = NVL (res_enc_pending, 0)
- DECODE (
res_level.actual_flag,
'E', NVL (x_pending_enc, 0),
0)
WHERE ROWID = res_level.ROWID;
gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE', 'C');
UPDATE gms_bc_packets bp
SET bp.res_result_code = DECODE (
bp.effect_on_funds_code,
'I', decode(bp.result_code,null,'P78',bp.result_code) ,
'D', DECODE (
bp.r_funds_control_level_code,
'N', 'P76',
'D', DECODE (
SIGN (
NVL (bp.res_budget_posted, 0)
- NVL (bp.res_actual_posted, 0)
- NVL (bp.res_enc_posted, 0)
- NVL (bp.res_actual_approved, 0)
- NVL (bp.res_actual_pending, 0)
- NVL (bp.res_enc_approved, 0)
- NVL (bp.res_enc_pending, 0)),
-1, 'P69',
'P68'),
'B', DECODE (
SIGN (
NVL (bp.res_budget_posted, 0)
- NVL (bp.res_actual_posted, 0)
- NVL (bp.res_enc_posted, 0)
- NVL (bp.res_actual_approved, 0)
- NVL (bp.res_actual_pending, 0)
- NVL (bp.res_enc_approved, 0)
- NVL (bp.res_enc_pending, 0)),
-1, 'F92',
'P68')))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P';
UPDATE gms_bc_packets bp
SET bp.res_grp_result_code = res_result_code,
bp.task_result_code = res_result_code,
bp.top_task_result_code = res_result_code,
bp.award_result_code = res_result_code,
bp.result_code = res_result_code,
bp.status_code = DECODE(x_mode,'C','F','R')
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P'
AND SUBSTR (bp.res_result_code, 1, 1) = 'F';
g_error_stage := 'FC PR : RES INSERT NEG';
INSERT INTO gms_bc_packets_summary
(packet_id,
creation_date,
budget_version_id,
top_task_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date,
actual_pending,
enc_pending)
Select x_packetid,
x_date,
budget_version_id,
top_task_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date,
-1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
-1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
from gms_bc_packets
where packet_id = x_packetid
and res_result_code in ('F92','F63','F75','F65')
group by budget_version_id, top_task_id,bud_task_id, parent_resource_id,budget_period_start_date, budget_period_end_date;
gms_error_pkg.gms_debug ('RESOURCE GROUP POSTED BALANCE UPDATE', 'C');
UPDATE gms_bc_packets bp
SET (bp.res_grp_budget_posted, bp.res_grp_actual_posted, bp.res_grp_enc_posted) =
(SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
FROM gms_balances gb
WHERE gb.budget_version_id = bp.budget_version_id
AND gb.project_id = bp.project_id
AND gb.award_id = bp.award_id
AND (
(bp.bud_task_id = 0) or -- budget at project
(bp.bud_task_id > 0 and gb.task_id = bp.bud_task_id and bp.task_id = bp.bud_task_id) or -- budget at lowest task
(bp.bud_task_id > 0 and bp.top_task_id = bp.bud_task_id
and DECODE (gb.top_task_id, NULL, gb.task_id, gb.top_task_id) = bp.top_task_id ) -- top task
) -- 2379815
--AND gb.resource_list_member_id = bp.bud_resource_list_member_id
AND ( (-- gb.balance_type = 'BGT' and -- Bug 2605070
gb.resource_list_member_id = bp.parent_resource_id -- Bug 2605070
)
OR
(-- gb.balance_type <> 'BGT' and -- Bug 2605070
gb.parent_member_id = bp.parent_resource_id
)
)
AND gb.balance_type <> 'REV'
AND gb.start_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_end_date)
AND gb.end_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_end_date))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.categorization_code = 'R'
AND bp.rg_funds_control_level_code <> 'N' ;
UPDATE gms_bc_packets bp
SET (bp.res_grp_actual_approved, bp.res_grp_actual_pending, bp.res_grp_enc_approved,
bp.res_grp_enc_pending) =
(SELECT
SUM(actual_approved),
SUM(actual_pending),
SUM(enc_approved),
SUM(enc_pending)
FROM gms_bc_packets_summary gmsbcs
WHERE gmsbcs.packet_id = x_packetid
AND gmsbcs.bud_task_id = bp.bud_task_id
AND gmsbcs.budget_version_id = bp.budget_version_id
AND gmsbcs.parent_resource_id = bp.parent_resource_id
--Bug 2490381 : Changed "=" to "<=" to consider all the
-- records from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
-- Bug 2897560 : changed the strat date comparision to ">="
-- as we need to consider all the recods from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
AND gmsbcs.budget_period_start_date >= bp.budget_period_start_date
AND gmsbcs.budget_period_end_date <= bp.budget_period_end_date)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.rg_funds_control_level_code <> 'N'
AND bp.categorization_code = 'R';
SELECT (nvl(entered_dr,0)-nvl(entered_cr,0)) entered_dr, --Bug 2092791
ROWID,
budget_version_id,
bud_task_id,
parent_resource_id,
effect_on_funds_code, -- Bug 2927485
TRUNC (budget_period_start_date) budget_period_start_date,
TRUNC (budget_period_end_date) budget_period_end_date,
actual_flag
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND effect_on_funds_code in ('D','I') -- Bug 2092791
AND status_code = 'P'
AND rg_funds_control_level_code <> 'N'
AND categorization_code = 'R'
ORDER BY budget_version_id,
bud_task_id,
parent_resource_id,
budget_period_start_date,
budget_period_end_date,
funds_check_seq DESC;
IF nvl(res_grp_level.entered_dr,0) >= 0 AND res_grp_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
UPDATE gms_bc_packets -- if funds are decreasing)
SET res_grp_actual_pending = NVL (res_grp_actual_pending, 0)
- DECODE (
res_grp_level.actual_flag,
'A', NVL (x_pending_actual, 0),
0),
res_grp_enc_pending = NVL (res_grp_enc_pending, 0)
- DECODE (
res_grp_level.actual_flag,
'E', NVL (x_pending_enc, 0),
0)
WHERE ROWID = res_grp_level.ROWID;
gms_error_pkg.gms_debug ('RESULT CODE UPDATE - RESOURCE GROUP', 'C');
UPDATE gms_bc_packets bp
SET bp.res_grp_result_code = DECODE (
bp.effect_on_funds_code,
'I',decode(bp.result_code,null,'P78',bp.result_code) ,
'D', DECODE (
bp.rg_funds_control_level_code,
'N', 'P76',
'D', DECODE (
SIGN (
NVL (bp.res_grp_budget_posted, 0)
- NVL (bp.res_grp_actual_posted, 0)
- NVL (bp.res_grp_enc_posted, 0)
- NVL (bp.res_grp_actual_approved, 0)
- NVL (bp.res_grp_actual_pending, 0)
- NVL (bp.res_grp_enc_approved, 0)
- NVL (bp.res_grp_enc_pending, 0)),
-1, 'P73',
'P72'),
'B', DECODE (
SIGN (
NVL (bp.res_grp_budget_posted, 0)
- NVL (bp.res_grp_actual_posted, 0)
- NVL (bp.res_grp_enc_posted, 0)
- NVL (bp.res_grp_actual_approved, 0)
- NVL (bp.res_grp_actual_pending, 0)
- NVL (bp.res_grp_enc_approved, 0)
- NVL (bp.res_grp_enc_pending, 0)),
-1, 'F93',
'P72')))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P';
UPDATE gms_bc_packets bp
SET bp.task_result_code = res_grp_result_code,
bp.top_task_result_code = res_grp_result_code,
bp.award_result_code = res_grp_result_code,
bp.result_code = res_grp_result_code,
bp.status_code = DECODE(x_mode,'C','F','R')
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P'
AND SUBSTR (bp.res_grp_result_code, 1, 1) = 'F';
INSERT INTO gms_bc_packets_summary
(packet_id,
creation_date,
budget_version_id,
top_task_id,
bud_task_id,
budget_period_start_date,
budget_period_end_date,
actual_pending,
enc_pending)
Select x_packetid,
x_date,
budget_version_id,
top_task_id,
bud_task_id,
budget_period_start_date,
budget_period_end_date,
-1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
-1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
from gms_bc_packets
where packet_id = x_packetid
and res_grp_result_code in ('F93','F63','F75','F65')
and nvl(substr(res_result_code,1,1),'P') = 'P'
group by budget_version_id, top_task_id, bud_task_id, budget_period_start_date, budget_period_end_date;
UPDATE gms_bc_packets bp
SET (bp.task_budget_posted, bp.task_actual_posted, bp.task_enc_posted) =
(SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
FROM gms_balances gb
WHERE gb.budget_version_id = bp.budget_version_id
AND gb.project_id = bp.project_id
AND gb.award_id = bp.award_id
AND gb.task_id = bp.task_id
AND gb.balance_type <> 'REV'
AND gb.start_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_end_date)
AND gb.end_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_end_date))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.t_funds_control_level_code <> 'N'
AND bp.bud_task_id <> 0;
UPDATE gms_bc_packets bp
SET (bp.task_actual_approved, bp.task_actual_pending, bp.task_enc_approved,
bp.task_enc_pending) =
(SELECT
SUM (actual_approved),
SUM (actual_pending),
SUM (enc_approved),
SUM (enc_pending)
FROM gms_bc_packets_summary gmsbcs
WHERE gmsbcs.packet_id = x_packetid
AND gmsbcs.budget_version_id = bp.budget_version_id
AND gmsbcs.bud_task_id = bp.bud_task_id
--Bug 2490381 : Changed "=" to "<=" to consider all the
-- records from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
-- Bug 2897560 : changed the start date comparision to ">="
-- as we need to consider all the recods from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
AND gmsbcs.budget_period_start_date >= bp.budget_period_start_date
AND gmsbcs.budget_period_end_date <= bp.budget_period_end_date)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.t_funds_control_level_code <> 'N'
AND bp.bud_task_id <> 0;
SELECT (nvl(entered_dr,0)- nvl(entered_cr,0)) entered_dr, -- Bug 2092791
ROWID,
budget_version_id,
bud_task_id,
effect_on_funds_code, -- Bug 2927485
TRUNC (budget_period_start_date) budget_period_start_date,
TRUNC (budget_period_end_date) budget_period_end_date,
actual_flag
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND effect_on_funds_code in ('D','I') -- Bug 2092791
AND status_code = 'P'
AND t_funds_control_level_code <> 'N'
AND bud_task_id <> 0
ORDER BY budget_version_id,
bud_task_id,
budget_period_start_date,
budget_period_end_date,
funds_check_seq DESC;
IF nvl(task_level.entered_dr,0) >=0 AND task_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
UPDATE gms_bc_packets -- if funds are decreasing)
SET task_actual_pending = NVL (task_actual_pending, 0)
- DECODE (
task_level.actual_flag,
'A', NVL (x_pending_actual, 0),
0),
task_enc_pending = NVL (task_enc_pending, 0)
- DECODE (
task_level.actual_flag,
'E', NVL (x_pending_enc, 0),
0)
WHERE ROWID = task_level.ROWID;
UPDATE gms_bc_packets bp
SET bp.task_result_code = DECODE (
bp.effect_on_funds_code,
'I', decode(bp.result_code,null,'P78',bp.result_code) ,
'D', DECODE (
bp.t_funds_control_level_code,
'N', 'P76',
'D', DECODE (
SIGN (
NVL (bp.task_budget_posted, 0)
- NVL (bp.task_actual_posted, 0)
- NVL (bp.task_enc_posted, 0)
- NVL (bp.task_actual_approved, 0)
- NVL (bp.task_actual_pending, 0)
- NVL (bp.task_enc_approved, 0)
- NVL (bp.task_enc_pending, 0)),
-1, 'P65',
'P64'),
'B', DECODE (
SIGN (
NVL (bp.task_budget_posted, 0)
- NVL (bp.task_actual_posted, 0)
- NVL (bp.task_enc_posted, 0)
- NVL (bp.task_actual_approved, 0)
- NVL (bp.task_actual_pending, 0)
- NVL (bp.task_enc_approved, 0)
- NVL (bp.task_enc_pending, 0)),
-1, 'F91',
'P64')))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P';
UPDATE gms_bc_packets bp
SET bp.result_code = bp.task_result_code,
bp.top_task_result_code = bp.task_result_code,
bp.award_result_code = bp.task_result_code,
bp.status_code = DECODE(x_mode,'C','F','R')
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P'
AND SUBSTR (bp.task_result_code, 1, 1) = 'F';
INSERT INTO gms_bc_packets_summary
(packet_id,
creation_date,
budget_version_id,
top_task_id,
budget_period_start_date,
budget_period_end_date,
actual_pending,
enc_pending)
Select x_packetid,
x_date,
budget_version_id,
top_task_id,
budget_period_start_date,
budget_period_end_date,
-1 * nvl(sum(decode(status_code || actual_flag,'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
-1 * nvl(sum(decode(status_code || actual_flag,'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
from gms_bc_packets
where packet_id = x_packetid
and task_result_code in ('F91','F63','F75','F65')
and substr(res_grp_result_code,1,1) = 'P'
group by budget_version_id, top_task_id, budget_period_start_date, budget_period_end_date;
UPDATE gms_bc_packets bp
SET (bp.top_task_budget_posted, bp.top_task_actual_posted, bp.top_task_enc_posted) =
(SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
FROM gms_balances gb
WHERE gb.budget_version_id = bp.budget_version_id
AND gb.project_id = bp.project_id
AND gb.award_id = bp.award_id
AND gb.balance_type <> 'REV'
AND DECODE (gb.top_task_id, NULL, gb.task_id, gb.top_task_id) =
bp.top_task_id
AND gb.start_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_end_date)
AND gb.end_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_end_date))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.tt_funds_control_level_code <> 'N'
AND bp.bud_task_id <> 0;
UPDATE gms_bc_packets bp
SET (bp.top_task_actual_approved, bp.top_task_actual_pending, bp.top_task_enc_approved,
bp.top_task_enc_pending) =
(SELECT
SUM (actual_approved),
SUM (actual_pending),
SUM (enc_approved),
SUM (enc_pending)
FROM gms_bc_packets_summary gmsbcs
WHERE gmsbcs.packet_id = x_packetid
AND gmsbcs.budget_version_id = bp.budget_version_id
AND gmsbcs.top_task_id = bp.top_task_id
--Bug 2490381 : Changed "=" to "<=" to consider all the
-- records from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
-- Bug 2897560 : changed the start date comparision to ">="
-- as we need to consider all the recods from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
AND gmsbcs.budget_period_start_date >= bp.budget_period_start_date
AND gmsbcs.budget_period_end_date <= bp.budget_period_end_date)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.tt_funds_control_level_code <> 'N'
AND bp.bud_task_id <> 0;
SELECT (nvl(entered_dr,0)- nvl(entered_cr,0)) entered_dr, -- Bug 2092791
ROWID,
budget_version_id,
top_task_id,
effect_on_funds_code, -- Bug 2927485
TRUNC (budget_period_start_date) budget_period_start_date,
TRUNC (budget_period_end_date) budget_period_end_date,
actual_flag
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND effect_on_funds_code in ('D','I') -- Bug 2092791
AND status_code = 'P'
AND tt_funds_control_level_code <> 'N'
AND bud_task_id <> 0
ORDER BY budget_version_id,
top_task_id,
budget_period_start_date,
budget_period_end_date,
funds_check_seq DESC;
IF nvl(top_task_level.entered_dr,0) >=0 AND top_task_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485 (Update pending balance
UPDATE gms_bc_packets -- if funds are decreasing)
SET top_task_actual_pending = NVL (top_task_actual_pending, 0)
- DECODE (
top_task_level.actual_flag,
'A', NVL (x_pending_actual, 0),
0),
top_task_enc_pending = NVL (top_task_enc_pending, 0)
- DECODE (
top_task_level.actual_flag,
'E', NVL (x_pending_enc, 0),
0)
WHERE ROWID = top_task_level.ROWID;
UPDATE gms_bc_packets bp
SET bp.top_task_result_code = DECODE (
bp.effect_on_funds_code,
'I', decode(bp.result_code,null,'P78',bp.result_code),
'D', DECODE (
bp.tt_funds_control_level_code,
'N', 'P76',
'D', DECODE (
SIGN (
NVL (bp.top_task_budget_posted, 0)
- NVL (bp.top_task_actual_posted, 0)
- NVL (bp.top_task_enc_posted, 0)
- NVL (bp.top_task_actual_approved, 0)
- NVL (bp.top_task_actual_pending, 0)
- NVL (bp.top_task_enc_approved, 0)
- NVL (bp.top_task_enc_pending, 0)),
-1, 'P80',
'P79'),
'B', DECODE (
SIGN (
NVL (bp.top_task_budget_posted, 0)
- NVL (bp.top_task_actual_posted, 0)
- NVL (bp.top_task_enc_posted, 0)
- NVL (bp.top_task_actual_approved, 0)
- NVL (bp.top_task_actual_pending, 0)
- NVL (bp.top_task_enc_approved, 0)
- NVL (bp.top_task_enc_pending, 0)),
-1, 'F60',
'P79')))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P';
UPDATE gms_bc_packets bp
SET bp.result_code = bp.top_task_result_code,
bp.award_result_code = bp.top_task_result_code,
bp.status_code = DECODE(x_mode,'C','F','R')
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P'
AND SUBSTR (bp.top_task_result_code, 1, 1) = 'F';
INSERT INTO gms_bc_packets_summary
(packet_id,
creation_date,
budget_version_id,
budget_period_start_date,
budget_period_end_date,
actual_pending,
enc_pending)
Select x_packetid,
x_date,
budget_version_id,
budget_period_start_date,
budget_period_end_date,
-1 * nvl(sum(decode(status_code || actual_flag, 'RA',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0) ,
-1 * nvl(sum(decode(status_code || actual_flag, 'RE',nvl(entered_dr,0) - nvl(entered_cr,0),0)),0)
from gms_bc_packets
where packet_id = x_packetid
and top_task_result_code in ('F60','F63','F65','F75')
and substr(task_result_code,1,1) = 'P'
group by budget_version_id, budget_period_start_date, budget_period_end_date;
UPDATE gms_bc_packets bp
SET (bp.award_budget_posted, bp.award_actual_posted, bp.award_enc_posted) =
(SELECT SUM (NVL (budget_period_to_date, 0) * DECODE (balance_type, 'BGT', 1, 0)),
SUM (NVL (actual_period_to_date, 0) * DECODE (balance_type, 'EXP', 1, 0)),
SUM (NVL (encumb_period_to_date, 0) * DECODE (balance_type, 'REQ', 1, 'PO', 1, 'AP', 1, 'ENC', 1, 0))
FROM gms_balances gb
WHERE gb.budget_version_id = bp.budget_version_id
AND gb.project_id = bp.project_id
AND gb.award_id = bp.award_id
AND gb.start_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.start_date,
bp.budget_period_end_date)
AND gb.end_date BETWEEN DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_start_date)
AND DECODE (
bp.time_phased_type_code,
'N', gb.end_date,
bp.budget_period_end_date))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.a_funds_control_level_code <> 'N';
UPDATE gms_bc_packets bp
SET (bp.award_actual_approved, bp.award_actual_pending, bp.award_enc_approved,
bp.award_enc_pending) =
(SELECT
SUM (actual_approved),
SUM (actual_pending),
SUM (enc_approved),
SUM (enc_pending)
FROM gms_bc_packets_summary gmsbcs
WHERE gmsbcs.packet_id = x_packetid
AND gmsbcs.budget_version_id = bp.budget_version_id
--Bug 2490381 : Changed "=" to "<=" to consider all the
-- records from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
-- Bug 2897560 : changed the start date comparision to ">="
-- as we need to consider all the recods from summary table which fall
-- under budget_period_start_date and budget_period_end_date
-- of current transaction.
AND gmsbcs.budget_period_start_date >= bp.budget_period_start_date
AND gmsbcs.budget_period_end_date <= bp.budget_period_end_date)
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code = 'D'
AND bp.status_code = 'P'
AND bp.a_funds_control_level_code <> 'N';
SELECT (nvl(entered_dr,0) - nvl(entered_cr,0)) entered_dr, --Bug 2092791
ROWID,
budget_version_id,
effect_on_funds_code, -- Bug 2927485
TRUNC (budget_period_start_date) budget_period_start_date,
TRUNC (budget_period_end_date) budget_period_end_date,
actual_flag
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND effect_on_funds_code in ('D','I') -- 2092791
AND status_code = 'P'
AND a_funds_control_level_code <> 'N'
ORDER BY budget_version_id,
budget_period_start_date,
budget_period_end_date,
funds_check_seq DESC;
IF nvl(award_level.entered_dr,0) >= 0 AND award_level.effect_on_funds_code = 'D' THEN --Bug 2092791 and 2927485(Update pending balance
-- if funds are decreasing)
UPDATE gms_bc_packets
SET award_actual_pending = NVL (award_actual_pending, 0)
- DECODE (
award_level.actual_flag,
'A', NVL (x_pending_actual, 0),
0),
award_enc_pending = NVL (award_enc_pending, 0)
- DECODE (
award_level.actual_flag,
'E', NVL (x_pending_enc, 0),
0)
WHERE ROWID = award_level.ROWID;
UPDATE gms_bc_packets bp
SET bp.award_result_code = DECODE (
bp.effect_on_funds_code,
'I',decode(bp.result_code,null,'P78',bp.result_code ),
'D', DECODE (
bp.a_funds_control_level_code,
'N', 'P76',
'D', DECODE (
SIGN (
NVL (bp.award_budget_posted, 0)
- NVL (bp.award_actual_posted, 0)
- NVL (bp.award_enc_posted, 0)
- NVL (bp.award_actual_approved, 0)
- NVL (bp.award_actual_pending, 0)
- NVL (bp.award_enc_approved, 0)
- NVL (bp.award_enc_pending, 0)),
-1, 'P61',
'P60'),
'B', DECODE (
SIGN (
NVL (bp.award_budget_posted, 0)
- NVL (bp.award_actual_posted, 0)
- NVL (bp.award_enc_posted, 0)
- NVL (bp.award_actual_approved, 0)
- NVL (bp.award_actual_pending, 0)
- NVL (bp.award_enc_approved, 0)
- NVL (bp.award_enc_pending, 0)),
-1, 'F90',
'P60')))
WHERE bp.packet_id = x_packetid
AND bp.effect_on_funds_code IN ('D', 'I')
AND bp.status_code = 'P';
UPDATE gms_bc_packets
SET result_code = NVL (award_result_code, 'F53'),
status_code = DECODE (status_code,'P', DECODE (SUBSTR (NVL (award_result_code, 'F53'), 1, 1),'F', DECODE(x_mode,'C','F','R'),status_code),status_code)
WHERE packet_id = x_packetid
AND effect_on_funds_code IN ('D', 'I')
AND status_code = 'P';
UPDATE gms_bc_packets
SET result_code = DECODE (top_task_result_code,'P80', 'P80',
DECODE (task_result_code,'P65', 'P65',
DECODE (res_grp_result_code,'P73', 'P73',
DECODE (res_result_code,'P69', 'P69',
result_code))))
WHERE packet_id = x_packetid
AND effect_on_funds_code IN ('D', 'I')
AND status_code = 'P'
AND SUBSTR (result_code,1,1) <> 'F'
AND result_code <> 'P61'
AND ( top_task_result_code = 'P80'
OR task_result_code = 'P65'
OR res_grp_result_code = 'P73'
OR res_result_code = 'P69'
);
g_error_stage := 'FC PR : DELETE';
DELETE gms_bc_packets_summary
WHERE packet_id = x_packetid;
DELETE gms_bc_packets_bvid
WHERE packet_id = x_packetid;
DELETE gms_bc_packet_arrival_order
WHERE packet_id = x_packetid;
status_code_update (x_packetid, x_mode);
SELECT 0
INTO x_err_code
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND status_code IN ('S', 'B')); --Bug Fix 1350100 Change status_code from 'A'
SELECT DECODE (x_err_code, 0, 'S', 'H')
INTO x_e_code
FROM DUAL;
SELECT 'F'
INTO x_result_code
FROM DUAL
WHERE EXISTS ( SELECT result_code
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND SUBSTR (result_code, 1, 1) = 'F');
gms_error_pkg.gms_debug ( 'Calling status_code_update for encumbrances', 'C' );
status_code_update (x_packetid, 'E');
ELSE -- update gms_bc_packets with approved status
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ( 'Calling status_code_update for encumbrances', 'C' );
status_code_update (x_packetid, 'E');
SELECT 1
INTO x_err_code
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND SUBSTR (result_code, 1, 1) = 'F'
AND status_code = 'T');
SELECT 1
INTO x_err_code
FROM DUAL
WHERE EXISTS ( SELECT 'X'
FROM gms_bc_packets
WHERE packet_id = x_packetid
AND SUBSTR (result_code, 1, 1) = 'F');
SELECT count(*) gms_txn_count,
SUM(DECODE(gms.document_type,'REQ',1,0)) req_count,
SUM(DECODE(gms.document_type,'PO',1,0)) po_count,
SUM(DECODE(gms.document_type,'AP',1,0)) ap_count,
SUM(DECODE(gms.document_type,'FAB',1,0)) fab_count
FROM gms_bc_packets gms
WHERE gms.packet_id= g_packet_id;
-- R12 FundsCheck Management Uptake : Deleted existing logic which was deriving g_doc_type and
-- g_non_gms_txn based on records in GL_bc_packets/AP/PO/REQ tables.
-- With new architecture the gl_bc_packets/AP/PO/REQ validations will be performed during
-- insertion of GMS packets in main session and current logic derives document type based on
-- the data inserted into gms_bc_packets for current packet_id.
OPEN c_count_rec;
SELECT inv_encumbrance_type_id
FROM financials_system_parameters;
SELECT pkt.ROWID pkt_row_id,
pod.po_distribution_id po_dist_id,
pod.po_header_id po_header_id,
pod.project_id project_id,
pod.task_id task_id,
adl.award_id award_id
FROM gms_bc_packets pkt,
gl_bc_packets gl,
ap_invoice_distributions_all ap,
po_distributions_all pod,
gms_award_distributions adl
WHERE pkt.packet_id = x_packet_id
AND pkt.document_type = 'AP'
AND gl.packet_id = pkt.packet_id
AND ROWIDTOCHAR (gl.ROWID) = pkt.gl_bc_packets_rowid
AND gl.encumbrance_type_id <> p_inv_encumbrance_type_id
AND NVL (pkt.entered_cr, 0) + NVL (pkt.entered_dr, 0) <> 0
AND pod.po_distribution_id = ap.po_distribution_id
AND ap.distribution_line_number= pkt.document_distribution_id
AND ap.invoice_id = pkt.document_header_id
and pod.award_id = adl.award_set_id
and adl.adl_line_num = 1
and nvl(pkt.burden_adjustment_flag,'N') = 'N'
FOR UPDATE OF pkt.document_type,
pkt.document_header_id,
pkt.document_distribution_id NOWAIT;
SELECT packet_id,
document_header_id,
document_distribution_id,
award_id,
expenditure_type,
document_type,
SUM ( NVL (entered_dr, 0) - NVL (entered_cr, 0)) raw_cost
FROM gms_bc_packets
WHERE packet_id = x_packet_id
and nvl(burden_adjustment_flag,'N') = 'N'
AND EXISTS ( SELECT 1
FROM gms_bc_packets
WHERE packet_id = x_packet_id
AND document_type = 'AP')
GROUP BY packet_id,
document_header_id,
document_distribution_id,
award_id,
expenditure_type,
document_type;
SELECT MIN (bc_packet_id)
FROM gms_bc_packets
WHERE packet_id = x_packet_id
AND document_type = x_doc_type
AND document_header_id = x_document_header_id
AND document_distribution_id = x_document_distribution_id
AND award_id = x_award_id
AND expenditure_type = x_expenditure_type;
UPDATE gms_bc_packets
SET document_type = 'PO',
document_header_id = bc_packets.po_header_id,
document_distribution_id = bc_packets.po_dist_id,
project_id = bc_packets.project_id ,
task_id = bc_packets.task_id ,
award_id = bc_packets.award_id
WHERE ROWID = bc_packets.pkt_row_id;
Delete from gms_bc_packets
Where packet_id = x_packet_id
And document_type = 'AP'
And bc_packet_id in
( select a.bc_packet_id
from gms_bc_packets a,
ap_invoice_distributions_all apd
where a.packet_id = x_packet_id
and a.document_type = 'AP'
and a.document_header_id = apd.invoice_id
and a.document_distribution_id = apd.distribution_line_number
and NVL(apd.pa_addition_flag,'X') = 'T') ;
UPDATE gms_bc_packets
SET entered_dr = bc_packets.raw_cost,
entered_cr = 0
WHERE packet_id = x_packet_id
AND document_type = bc_packets.document_type
AND bc_packet_id = l_bc_packet_id;
UPDATE gms_bc_packets
SET entered_cr = 0,
entered_dr = 0
WHERE packet_id = x_packet_id
AND bc_packet_id > l_bc_packet_id
AND document_type = bc_packets.document_type
AND document_header_id = bc_packets.document_header_id
AND document_distribution_id = bc_packets.document_distribution_id
AND award_id = bc_packets.award_id
AND expenditure_type = bc_packets.expenditure_type;
UPDATE gms_bc_packets
SET entered_cr = bc_packets.raw_cost * -1,
entered_dr = 0
WHERE packet_id = x_packet_id
AND document_type = bc_packets.document_type
AND bc_packet_id = l_bc_packet_id;
UPDATE gms_bc_packets
SET entered_cr = 0,
entered_dr = 0
WHERE packet_id = x_packet_id
AND bc_packet_id > l_bc_packet_id
AND document_type = bc_packets.document_type
AND document_header_id = bc_packets.document_header_id
AND document_distribution_id = bc_packets.document_distribution_id
AND award_id = bc_packets.award_id
AND expenditure_type = bc_packets.expenditure_type;
l_stage := 'SELECT' ;
select 1
into l_pkt_row
from dual
where exists ( select 1 from gms_bc_packets
where packet_id = x_packet_id ) ;
IF l_stage = 'SELECT' THEN
X_pkt_row := 0 ;
Select gbc.packet_id
from gms_bc_packets gbc
where gbc.source_event_id in
(select glbc.event_id
from gl_bc_packets glbc
where glbc.packet_id = p_packet_id)
and gbc.status_code ='I'
and gbc.document_type in ('AP','PO','REQ')
and rownum =1;
/* Bug 5285217 : Created the cursor c_gl_bc_pkt. This cursor selects all the records from gl_bc_packets for the current packet_id. */
cursor c_gl_bc_pkt IS
select p_packet_id packet_id,'P' status_code,
glbc.session_id session_id,glbc.serial_id serial_id,
glbc.je_category_name je_category_name,
glbc.je_source_name je_source_name,glbc.period_name period_name,
glbc.period_year period_year,glbc.period_num period_num,
glbc.account_type account_type,
rowidtochar(glbc.rowid) gl_rowid,
glbc.event_id event_id,
glbc.source_distribution_id_num_1 source_distribution_id_num_1,
glet.encumbrance_type_key encumbrance_type_key,
glbc.accounted_dr entered_dr , -- Bug 5614467
glbc.accounted_cr entered_cr, -- Bug 5614467
source_distribution_type
from gl_bc_packets glbc,
gl_encumbrance_types glet
where glbc.packet_id = p_packet_id
and glbc.encumbrance_type_id = glet.encumbrance_type_id;
/* Bug 5250793 : Added a join with gl_encumbrance_types so that the gl_bc_packets_rowid on gms_bc_packets is updated
correctly for an invoice matched to a PO scenario. Before this change , the same gl_bc_packets_rowid was updated on
gms_bc_packets for both the PO reversal and AP reserve records irrespective of the corresponding rowid on gl_bc_packets.*/
-- Update gms_bc_packets data
/* Bug 5285217 : Changed the code to use "FOR" loop so that gl_bc_packets_rowid on gms_bc_packets is updated correctly
for an invoice matched to a PO with Quantity Variance Scenario. Before this change , the same gl_bc_packets_rowid was
updated on gms_bc_packets for both the invoice reserve and the quantity variance reserve records irrespective of
the corresponding rowid on gl_bc_packets.*/
FOR glbcrec in c_gl_bc_pkt LOOP
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ('Synch_gms_gl_packets: glbcrec.source_distribution_type:'|| glbcrec.source_distribution_type,'C');
-- Update gms_bc_packets data
Update gms_bc_packets gbc
set (gbc.packet_id,gbc.status_code,
gbc.session_id,gbc.serial_id,
gbc.je_category_name,
gbc.je_source_name,gbc.period_name,
gbc.period_year,gbc.period_num,
gbc.account_type,
gl_bc_packets_rowid) =
(select glbcrec.packet_id,glbcrec.status_code,
glbcrec.session_id,glbcrec.serial_id,
glbcrec.je_category_name,
glbcrec.je_source_name,glbcrec.period_name,
glbcrec.period_year,glbcrec.period_num,
glbcrec.account_type,
glbcrec.gl_rowid from dual
)
where gbc.packet_id = l_gms_packet_id
and gbc.status_code = 'I'
and gbc.source_event_id = glbcrec.event_id
and (( gbc.document_distribution_id = glbcrec.source_distribution_id_num_1
AND glbcrec.source_distribution_type <> 'AP_PREPAY') OR
(glbcrec.source_distribution_type = 'AP_PREPAY' AND -- Bug 5561741
gbc.document_distribution_id = l_dist_id
-- Following cannot be used as ap_prepay_app_dists not visible ..autonomous ..
-- (SELECT APAD.PREPAY_APP_DISTRIBUTION_ID
-- FROM ap_prepay_app_dists APAD
-- WHERE APAD.PREPAY_APP_DIST_ID = glbcrec.source_distribution_id_num_1 )
))
and gbc.document_type = decode(glbcrec.encumbrance_type_key,'Commitment','REQ'
,'Obligation','PO'
,'Invoices','AP')
/* Bug 5285217 : For an Invoice Matched to a PO with Quantity Variance , there are two records in gl_bc_packets
with encumbrance type as 'Invoices' (one for the invoice reserve and the other for the Quantity variance reserve).
Also the packet_id,event_id and source_distribution_id_num_1 on both the records are same.
So the 'gl_bc_packets_rowid IS NULL' and 'ROWNUM = 1' conditions are used to differentiate between
the two records.
For the first AP record (either invoice reserve record or the Quantity variance reserve record) in gl_bc_packets,
the 'ROWNUM=1' and the (entered_dr-entered_cr) conditions are used to identify the corresponding record
in gms_bc_packets. The (entered_dr-entered_cr) check is not suitable for the scenario in which the invoice amount and the quantity
variance amount are same. But as the amounts are same for both the invoice reserve and quantity variance reserve
records , only 'ROWNUM=1' check will suffice as we need not distinguish between the invoice reserve and the
quantity variance record.
For the second AP record (one among the invoice reserve record or the Quantity variance reserve record for which the
corresponding record is not yet updated in gms_bc_packets) in gl_bc_packets , the 'gl_bc_packets_rowid IS NULL'
condition is used to identify the corresponding record in gms_bc_packets.*/
and gbc.gl_bc_packets_rowid IS NULL
and ((nvl(gbc.entered_dr,0) - nvl(gbc.entered_cr,0)) = (nvl(glbcrec.entered_dr,0) - nvl(glbcrec.entered_cr,0)))
and ROWNUM = 1
-- If it's PO mathed to an AP then for the PO reversal record in gl_bc_packets source_distribution_id_num_1 is populated as invoice_distribution_id
-- whereas for the corresponding record gms_bc_packets will have source_distribution_id_num_1 as po_distribution_id
-- Hence for this scenario we will check encumbrance_type_id to get PO record from GL.
-- This update is NOT for the PO reversal record in an AP matched to a PO scenario.
and 1 >= (select count(distinct glbc1.encumbrance_type_id) -- This will return more than one count for the PO reversal record in an AP matched to a PO scenario.
from gl_bc_packets glbc1
where glbc1.packet_id = p_packet_id
and glbc1.event_id = gbc.source_event_id
and glbc1.source_distribution_type = 'AP_INV_DIST'
and gbc.document_type ='PO' );
gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets except PO matched to an invoice '|| SQL%ROWCOUNT,'C');
-- This update is for the PO reversal records , which will be only records left with gbc.packet_id = l_gms_packet_id , in an AP matched to a PO scenario.
Update gms_bc_packets gbc
set (gbc.packet_id,gbc.status_code,
gbc.session_id,gbc.serial_id,
gbc.period_name,
gbc.period_year,gbc.period_num,
gbc.account_type,
gl_bc_packets_rowid) =
(select p_packet_id,'P',
glbc.session_id,glbc.serial_id,
glbc.period_name,
glbc.period_year,glbc.period_num,
glbc.account_type,
rowidtochar(glbc.rowid)
from gl_bc_packets glbc
where glbc.packet_id = p_packet_id
and glbc.event_id = gbc.source_event_id
and glbc.source_distribution_id_num_1 <> gbc.document_distribution_id
and glbc.encumbrance_type_id IN (SELECT glenc.encumbrance_type_id --Seeded encumbrance type for PO
FROM gl_encumbrance_types glenc
WHERE glenc.encumbrance_type_key = 'Obligation')
and rownum = 1)
where gbc.packet_id = l_gms_packet_id
and gbc.status_code = 'I'
and gbc.document_type ='PO';
gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets for PO matched to an invoice '|| SQL%ROWCOUNT,'C');
Update gms_bc_packets gbc
set (gbc.packet_id,gbc.status_code,
gbc.session_id,gbc.serial_id,
gbc.je_category_name,
gbc.je_source_name,gbc.period_name,
gbc.period_year,gbc.period_num,
gbc.account_type,
gl_bc_packets_rowid) =
(select gbcparent.packet_id,'P',
gbcparent.session_id,gbcparent.serial_id,
gbcparent.je_category_name,
gbcparent.je_source_name,gbcparent.period_name,
gbcparent.period_year,gbcparent.period_num,
gbcparent.account_type,
gbcparent.gl_bc_packets_rowid
from gms_bc_packets gbcparent
where gbcparent.bc_packet_id = gbc.parent_bc_packet_id)
where gbc.packet_id = l_gms_packet_id
and gbc.status_code = 'I'
and gbc.gl_bc_packets_rowid IS NULL;
gms_error_pkg.gms_debug ('Synch_gms_gl_packets Strat : Updated bc packets for prepayment burden adjustment lines '|| SQL%ROWCOUNT,'C');
x_e_stage := 'delete_pending_txns';
delete_pending_txns(x_err_code,x_err_buff);
IF NOT gms_cost_plus_extn.update_bc_pkt_burden_raw_cost (x_packetid,g_derived_mode) THEN
result_status_code_update (
p_packet_id=> x_packetid,
p_status_code=> 'T',
p_result_code=> 'F76');
-- Update gl_bc_packets result_code to F67 if update Burdenable Raw Cost
-- failed.
UPDATE gl_bc_packets
SET result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F67',result_code)
WHERE packet_id = x_packetid;
x_e_code := 'U'; -- Bug : 2557041 - Added , same as done for misc_gms_insert
'GMS_FCK-After Calling gms_cost_plus_extn.update_bc_pkt_burden_raw_cost','C');
IF NOT gms_cost_plus_extn.update_source_burden_raw_cost (x_packetid, x_mode, g_partial_flag) THEN
IF g_debug = 'Y' THEN
gms_error_pkg.gms_debug ('GMS_FCK- Posting for encumbrances ..failed','C');
result_status_code_update (
p_packet_id=> x_packetid,
p_status_code=> 'T',
p_result_code=> 'F64');
x_e_stage := 'Update status on failed bcpkt';
update_status_on_failed_txns(x_packetid);
result_status_code_update (
p_packet_id=> x_packetid,
p_status_code=> 'T',
p_result_code=> 'F89',
p_fc_error_message=>SUBSTR((g_error_program_name || '.' || g_error_procedure_name || '.' || g_error_stage ||' SQLCODE :'||SQLCODE||' SQLERRM :'||SQLERRM),1,2000)
);
-- Bug 2176230 - Delete arrival_order record in case of any failure.
IF x_mode = 'C' THEN
DELETE gms_bc_packet_arrival_order
WHERE packet_id = x_packetid;
UPDATE gl_bc_packets SET
result_code = DECODE (NVL (SUBSTR (result_code, 1, 1), 'P'),'P', 'F71',result_code)
WHERE packet_id = x_packetid;
UPDATE gms_bc_packets
SET status_code = DECODE(p_bc_mode,'C','F','R'),
result_code = 'F22'
WHERE status_code in ('I','A','S')
AND source_event_id IN
(SELECT event_id
FROM PSA_BC_XLA_EVENTS_GT
WHERE upper(result_code) in ('XLA_ERROR','FATAL'));