[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE gl_result_code_update ( p_packet_id IN NUMBER) ;
PROCEDURE gms_result_code_update ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
p_packet_id IN NUMBER,
p_mode IN VARCHAR2) ;
PROCEDURE status_code_update ( p_packet_id IN NUMBER,
p_mode IN VARCHAR2,
p_partial IN VARCHAR2 DEFAULT 'N') ;
l_gl_update VARCHAR2(1);
SELECT count(*)
FROM gms_bc_packets
WHERE packet_id = p_packet_id;
budget_ver_check (l_gl_update, p_packet_id, l_new_partial_flag);
NVL (l_gl_update,'N') = 'Y' THEN
-- Bug : 2557041 - Added for IP check funds Enhancement
-- Set the value of gl return code, in case of grants failing
-- in full mode comment out NOCOPY call to update_gl_packet
--
x_gl_return_code := p_gms_return_code;
gms_result_code_update(x_gl_return_code, p_packet_id, p_mode);
update gms_bc_packets
set burdenable_raw_cost = l_burdenable_cost(l_index)
where bc_packet_id = l_bc_packet_id(l_index)
and packet_id = p_packet_id ;
-- raw cost was not getting updated and burden posting expects
-- packet in the pending status.
-- ===============================================================
IF ( p_mode = 'C' OR p_gms_return_code = 'X' ) THEN
-- Bug 5039545 : status_code_update is called with p_partial parameter as p_gms_partial_flag.
status_code_update (p_packet_id, p_mode,p_gms_partial_flag);
gl_result_code_update(p_packet_id);
UPDATE gl_bc_packets gl
SET gl.result_code = DECODE (
NVL (SUBSTR (result_code, 1, 1), 'P'),
'P', 'F71',
result_code)
WHERE gl.packet_id = p_packet_id;
UPDATE gms_bc_packets gms
SET gms.status_code = 'T',
gms.fc_error_message = l_error_message,
gms.result_code = DECODE (
NVL (SUBSTR (result_code, 1, 1), 'P'),
'P', 'F68',
result_code)
WHERE gms.packet_id = p_packet_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 -- 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'
g_error_procedure_name := 'status_code_update';
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'))
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;
is updated correctly. */
/* Bug 5217281 : Modified the code such that when the GL funds check fails but the GMS fundscheck passes then
the status_code is updated correctly on gms_bc_packets. */
ELSIF NVL(p_mode,'R') in ('U','S','B','C', 'R') AND p_partial = 'Y' THEN
UPDATE gms_bc_packets
SET status_code =
DECODE(status_code,'P',decode(p_mode,'S',DECODE (SUBSTR (result_code, 1, 1), 'P', 'S', 'E')
,'B',DECODE (SUBSTR (result_code, 1, 1), 'P', 'B', 'R')
,'C',DECODE (SUBSTR (result_code, 1, 1), 'P', 'C', 'F')
,DECODE (SUBSTR (result_code, 1, 1), 'P', 'A', 'R')) -- This will cover p_mode 'U' and 'R'
,status_code)
WHERE packet_id = p_packet_id;
UPDATE gms_bc_packets
SET status_code = DECODE (SUBSTR (nvl(result_code,'F65'), 1, 1), 'P', 'A', 'R')
WHERE packet_id = p_packet_id;
FOR bc_records IN update_status LOOP
IF bc_records.document_type = 'REQ' THEN
g_error_stage := 'UPDATE_ADL:REQ';
UPDATE gms_award_distributions
SET resource_list_member_id = bc_records.resource_list_member_id,
bud_task_id = bc_records.bud_task_id,
fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
WHERE distribution_id = bc_records.document_distribution_id
AND adl_status = 'A'
AND document_type = 'REQ'
AND project_id = bc_records.project_id
AND task_id = bc_records.task_id
AND award_id = bc_records.award_id ;
g_error_stage := 'UPDATE_ADL:PO';
UPDATE gms_award_distributions
SET resource_list_member_id = bc_records.resource_list_member_id,
bud_task_id = bc_records.bud_task_id,
fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
WHERE po_distribution_id = bc_records.document_distribution_id
AND adl_status = 'A'
AND document_type = 'PO'
AND project_id = bc_records.project_id
AND task_id = bc_records.task_id
AND award_id = bc_records.award_id;
g_error_stage := 'UPDATE_ADL:AP';
UPDATE gms_award_distributions
SET resource_list_member_id = bc_records.resource_list_member_id,
bud_task_id = bc_records.bud_task_id,
fc_status = DECODE(P_MODE,'B',FC_STATUS,DECODE (SUBSTR (bc_records.result_code, 1, 1), 'P', 'A', 'R'))
WHERE invoice_id = bc_records.document_header_id
/* Bug 5453662 : bc_records.document_distribution_id stores the invoice_distribution_id for an AP invoice.
So for an AP invoice , bc_records.document_distribution_id should be compared with invoice_distribution_id. */
AND invoice_distribution_id = bc_records.document_distribution_id
AND adl_status = 'A'
AND document_type = 'AP'
AND project_id = bc_records.project_id
AND task_id = bc_records.task_id
AND award_id = bc_records.award_id;
IF update_status%ISOPEN THEN
CLOSE update_status;
END status_code_update;
SELECT gl.packet_id
FROM ap_invoice_distributions_all ap,
gms_award_distributions adl,
pa_projects_all pp,
gms_project_types gpt,
gl_bc_packets gl
WHERE gl.packet_id = p_packet_id
AND gl.je_source_name = 'Payables'
AND gl.template_id IS NULL
AND gl.je_category_name = 'Purchase Invoices'
AND gl.reference2 = ap.invoice_id
AND gl.reference3 = ap.distribution_line_number
AND ap.project_id IS NOT NULL
AND (NVL (ap.pa_addition_flag, 'X') = 'T')
AND ap.project_id = pp.project_id
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y'
AND ap.award_id = adl.award_set_id
AND ap.invoice_id = NVL (adl.invoice_id, ap.invoice_id)
AND ap.distribution_line_number =
NVL (adl.distribution_line_number, ap.distribution_line_number)
AND ap.invoice_distribution_id =
NVL (adl.invoice_distribution_id, ap.invoice_distribution_id)
AND ap.project_id = NVL (adl.project_id, ap.project_id)
AND ap.task_id = NVL (adl.task_id, ap.task_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';
SELECT gl.packet_id
FROM ap_invoice_distributions_all ap,
pa_projects_all pp,
gms_project_types gpt,
gl_bc_packets gl
WHERE gl.packet_id = p_packet_id
AND gl.je_source_name = 'Payables'
AND gl.template_id IS NULL
AND gl.je_category_name = 'Purchase Invoices'
AND gl.reference2 = ap.invoice_id
AND gl.reference3 = ap.distribution_line_number
AND ap.project_id IS NOT NULL
AND (NVL (ap.pa_addition_flag, 'X') <> 'T')
AND ap.project_id = pp.project_id
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y';
SELECT gl.packet_id
FROM pa_projects_all pp,
gms_project_types gpt,
po_req_distributions_all pord,
gl_bc_packets gl
WHERE gl.packet_id = p_packet_id
AND gl.reference1 = 'REQ'
AND gl.template_id IS NULL
AND gl.reference3 = pord.distribution_id
AND pord.project_id IS NOT NULL
AND pord.project_id = pp.project_id
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y';
SELECT gl.packet_id
FROM gl_bc_packets gl,
pa_projects pp,
gms_project_types gpt
WHERE gl.packet_id = p_packet_id
AND pp.project_id = TO_NUMBER (gl.reference7)
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y'
AND NVL (gl.reference6, 'XXXXX') = 'GMSIP' ;
SELECT gl.packet_id
FROM po_distributions_all pod, pa_projects_all pp, gms_project_types gpt, gl_bc_packets gl
WHERE gl.packet_id = p_packet_id
AND gl.reference1 = 'PO'
AND gl.template_id IS NULL
AND gl.reference3 = pod.po_distribution_id
AND pod.project_id IS NOT NULL
AND pod.project_id = pp.project_id
AND pod.distribution_type <> 'PREPAYMENT' -- subcontractor/complex work uptake
AND pp.project_type = gpt.project_type
AND gpt.sponsored_flag = 'Y';
SELECT DISTINCT NVL (reference1, 'X'),
je_source_name,
je_category_name
FROM gl_bc_packets
WHERE packet_id = p_packet_id
AND template_id IS NULL
AND ( ( reference1 in ('PO', 'REQ') ) OR
( je_source_name = 'Payables' AND je_category_name = 'Purchase Invoices' ) OR
( reference6 = 'GMSIP' )
) ;
SELECT DISTINCT budget_version_id
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND SUBSTR (result_code, 1, 1) = 'P';
SELECT budget_version_id
FROM gms_budget_versions
WHERE budget_version_id = l_budget_version_id
AND current_flag = 'Y'
AND budget_status_code = 'B';
UPDATE gms_bc_packets
SET budget_version_id = NULL,
result_code = 'F10'
WHERE packet_id = p_packet_id
AND budget_version_id = records.budget_version_id
AND SUBSTR (result_code, 1, 1) = 'P';
UPDATE gms_bc_packets
SET result_code = 'F11'
WHERE packet_id = p_packet_id
AND substr(result_code,1,1) = 'P'; -- Bug 2181546, Added
PROCEDURE gl_result_code_update (p_packet_id IN NUMBER) IS
-- =================================================================
-- This cursor return records in following scenario's
-- A. In gms_bc_packets there exists Funds check failed records
-- for the current packet.
-- B. In gms_bc_packets there exists records which passed Funds
-- check in advisory mode for the current packet.
-- =================================================================
CURSOR c_gl_update_required IS
SELECT 1
FROM DUAL
WHERE EXISTS ( SELECT 1
FROM gms_bc_packets
WHERE packet_id = p_packet_id
AND ( result_code IN ('P61', 'P65', 'P69', 'P73', 'P80')
OR NVL (SUBSTR (result_code, 1, 1), 'P') = 'F'
));
g_error_procedure_name := 'gl_result_code_update';
OPEN c_gl_update_required;
FETCH c_gl_update_required INTO l_dummy;
IF c_gl_update_required%FOUND THEN
g_error_stage := 'GL_RESULT_CODE UPD :REC_FOUND';
UPDATE gl_bc_packets glc
SET glc.result_code = (SELECT DECODE (
SUBSTR (bp.result_code, 1, 1),
'P', DECODE (
bp.result_code,
'P61', 'P39', -- advisory result code
'P65', 'P39', -- advisory result code
'P69', 'P39', -- advisory result code
'P73', 'P39', -- advisory result code
'P80', 'P39', -- advisory result code
glc.result_code
),
'F', DECODE (
bp.result_code,
'F21', 'F68', --Invalid award number
'F60', 'F69', --Top Task Failure
'F90', 'F71', --Award Failure
'F91', 'F72', --Task Failure
'F92', 'F73', --Resource Failure
'F93', 'F74', --Resource Group Failure
'F65', 'F70', --Full Mode
'F68', 'F67', --Funds Check processing error
'F89', 'F67', --Funds Check processing error
'F09', 'F67', --Funds Check processing error
'F10', 'F67', --Funds Check processing error
'F11', 'F67', --Funds Check processing error
'F12', 'F67', --Funds Check processing error
'F13', 'F67', --Funds Check processing error
'F14', 'F67', --Funds Check processing error
'F15', 'F67', --Funds Check processing error
'F16', 'F67', --Funds Check processing error
'F17', 'F67', --Funds Check processing error
'F18', 'F67', --Funds Check processing error
'F19', 'F67', --Funds Check processing error
'F40', 'F67', --Funds Check processing error
'F41', 'F67', --Funds Check processing error
'F42', 'F67', --Funds Check processing error
'F43', 'F67', --Funds Check processing error
'F44', 'F67', --Funds Check processing error
'F45', 'F67', --Funds Check processing error
'F46', 'F67', --Funds Check processing error
'F47', 'F67', --Funds Check processing error
'F48', 'F67', --Funds Check processing error
'F49', 'F67', --Funds Check processing error
'F50', 'F67', --Funds Check processing error
'F51', 'F67', --Funds Check processing error
'F52', 'F67', --Funds Check processing error
'F53', 'F67', --Funds Check processing error
'F54', 'F67', --Funds Check processing error
'F62', 'F67', --Funds Check processing error
'F64', 'F67', --Funds Check processing error
'F73', 'F67', --Funds Check processing error
'F76', 'F67', --Funds Check processing error
'F78', 'F67', --Funds Check processing error
'F79', 'F67', --Funds Check processing error
'F82', 'F67', --Funds Check processing error
'F94', 'F67', --Funds Check processing error
'F95', 'F67', --Funds Check processing error
-- Update gl_bc_packets with Failure status if gl.result_code
-- is Pxx and gms.result_code is Fxx but the result_code is
-- not there in the above List
DECODE(NVL(SUBSTR(glc.result_code,1,1),'P'),'P','F67',glc.result_code)
)
)
FROM gms_bc_packets bp
WHERE bp.gl_bc_packets_rowid = ROWIDTOCHAR(glc.ROWID)
AND bp.result_code NOT IN ('F63', 'F75')
AND bp.packet_id = p_packet_id
AND ROWNUM = 1)
WHERE glc.packet_id = p_packet_id
AND glc.template_id IS NULL
AND substr(nvl(glc.result_code,'P'),1,1) = 'P'
-- Bug 2896476 : We should only override if GL Funds check passed
-- Bug 3277370 : Added following exists statement to filter out non-GMS transactions , we shouldn't
-- update result_code on Non-GMS Transactions.
AND EXISTS (SELECT 1
FROM gms_bc_packets gms1
WHERE gms1.packet_id = glc.packet_id
AND gms1.gl_bc_packets_rowid = ROWIDTOCHAR(glc.ROWID)
);
CLOSE c_gl_update_required;
IF c_gl_update_required%ISOPEN THEN
CLOSE c_gl_update_required;
END gl_result_code_update;
PROCEDURE gms_result_code_update ( x_gl_return_code IN OUT NOCOPY VARCHAR2,
p_packet_id IN NUMBER,
p_mode IN VARCHAR2) IS
l_result_code varchar2(3) ;
g_error_procedure_name := 'gms_result_code_update';
g_error_procedure_name := 'update_gms_packet';
UPDATE gms_bc_packets
SET result_code = l_result_code
WHERE packet_id = p_packet_id
AND SUBSTR (result_code, 1, 1) = 'P';
END gms_result_code_update;
l_gl_update VARCHAR2 (1);
l_post_ret := gms_cost_plus_extn.update_source_burden_raw_cost
( :l_packet_id,
:l_mode,
:l_gms_partial_flag
);
SELECT DECODE (
COUNT (*),
COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'P', 1)),
DECODE (SIGN(COUNT (DECODE (bp.result_code,
'P20', 1,
'P22', 1,
'P25', 1,
'P27', 1,
'P39', 1))), -- Bug 2469309 : Added P39
0, 'S',
1, 'A'),
COUNT (DECODE (SUBSTR (bp.result_code, 1, 1), 'F', 1)), 'F',
DECODE (p_gms_partial_flag, 'Y', 'P', 'F'))
INTO t_return_code
FROM gl_bc_packets bp
WHERE bp.packet_id = p_packet_id
AND bp.template_id IS NULL; /* detail transactions only */