[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_login igc_cc_interface.last_update_login%TYPE;
g_update_by igc_cc_interface.last_updated_by%TYPE;
PROCEDURE Update_Event_ID;
SELECT cc_header_id,
cc_version_num,
cc_acct_line_id,
cc_det_pf_line_id,
code_combination_id,
batch_line_num,
cc_transaction_date,
cc_func_dr_amt ,
cc_func_cr_amt ,
je_source_name,
je_category_name,
actual_flag,
set_of_books_id,
encumbrance_type_id,
budget_version_id,
currency_code,
transaction_description,
reference_1,
reference_2 ,
reference_3 ,
reference_4 ,
reference_5 ,
reference_6 ,
reference_7 ,
reference_8 ,
reference_9 ,
reference_10
FROM igc_cc_interface_v a
WHERE cc_header_id = g_cc_header_id
-- AND budget_dest_flag = 'C' /*R12 Uptake. Need to process for both Commitment and Standard budget*/
AND actual_flag = g_actual_flag
AND document_type = g_doc_type
ORDER BY cc_transaction_date;
g_update_login := FND_GLOBAL.LOGIN_ID;
g_update_by := FND_GLOBAL.USER_ID;
SELECT application_id
INTO g_gl_application_id
FROM fnd_application
WHERE application_short_name = 'SQLGL';
SELECT application_id
INTO g_cc_application_id
FROM fnd_application
WHERE application_short_name = 'IGC';
SELECT MIN(Get_Rank(cbc_result_code))
INTO l_rank
FROM igc_cc_interface_v a
WHERE cc_header_id = g_cc_header_id
AND budget_dest_flag = 'C'
AND actual_flag = g_actual_flag
AND document_type = g_doc_type
AND cbc_result_code IS NOT NULL;
g_event_tbl.DELETE;
g_ledger_tbl.DELETE;
Put_Debug_Msg(l_full_path, 'Processed results. Updated Batch Result Code: ' || g_batch_result_code);
Update_Event_ID;
SELECT DISTINCT popup_messg_code
FROM igc_cc_result_code_ranks
WHERE action = DECODE(p_mode,'F','R',p_mode)
AND severity_rank = p_batch_result_code;
SELECT DISTINCT severity_rank
FROM igc_cc_result_code_ranks
WHERE funds_checker_code = p_code;
SELECT DISTINCT result_status_code
FROM igc_cc_result_code_ranks
WHERE funds_checker_code = p_result_code
AND action = DECODE(g_mode, 'F', 'R', g_mode);
SELECT DISTINCT funds_checker_code
FROM igc_cc_result_code_ranks
WHERE severity_rank = p_rank;
SELECT DISTINCT cc_header_id,
DOCUMENT_TYPE,
BUDGET_DEST_FLAG,
REFERENCE_4,
CC_TRANSACTION_DATE,
EVENT_ID,
CC_DET_PF_LINE_ID
FROM IGC_CC_INTERFACE
WHERE event_id IS NULL
AND cc_header_id = g_cc_header_id;
SELECT * FROM psa_bc_xla_events_gt;
DELETE FROM psa_bc_xla_events_gt pgt ;
(SELECT event_id FROM xla_events xe WHERE application_id = 8407 AND pgt.event_id = xe.event_id);
Put_Debug_Msg (l_full_path, 'Deleted all Event entries from psa_bc_xla_events_gt' );
INSERT
INTO psa_bc_xla_events_gt(event_id, result_code)
VALUES(l_event_id, 'XLA_ERROR');
Put_Debug_Msg (l_full_path, 'Inserted Event ID :' || l_event_id || ' into psa_bc_xla_events_gt' );
UPDATE igc_cc_interface
SET event_id = l_event_id
WHERE cc_header_id = g_cc_interface_head_tbl(i).cc_header_id
AND document_type = g_cc_interface_head_tbl(i).document_type
AND budget_dest_flag = g_cc_interface_head_tbl(i).budget_dest_flag
AND reference_4 = g_cc_interface_head_tbl(i).reference_4
AND nvl(cc_det_pf_line_id, 1) = nvl(g_cc_interface_head_tbl(i).cc_det_pf_line_id, 1)
AND cc_transaction_date = g_cc_interface_head_tbl(i).cc_transaction_date;
Put_Debug_Msg (l_full_path, 'Inserted Event ID: ' || l_event_id || ' into psa_bc_xla_events_gt' );
Put_Debug_Msg (l_full_path, 'Update Event ID: ' || l_event_id || ' in IGC_CC_INTERFACE' );
select decode(l_status_code, 'ADVISORY' , 'SUCCESS', 'PARTIAL', 'FAIL', 'XLA_ERROR', 'FATAL', l_status_code)
INTO l_status_code
FROM DUAL;
SELECT result_status_code INTO l_status_flag
FROM igc_cc_result_code_ranks
WHERE severity_rank = l_sev_rank
AND action = decode(g_mode, 'F', 'R', g_mode);
SELECT min(severity_rank) INTO l_max_sev_rank
FROM igc_cc_result_code_ranks
WHERE funds_checker_code IN(SELECT distinct(result_code)
FROM GL_BC_PACKETS
WHERE event_id IN (SELECT event_id
FROM
psa_bc_xla_events_gt));
UPDATE igc_cc_interface int
SET (batch_id,
cbc_result_code,
status_code,
budget_version_id,
period_name,
encumbrance_type_id
)
=
(
SELECT distinct pac.je_batch_id,
pac.result_code,
pac.status_code,
pac.funding_budget_version_id,
pac.period_name,
pac.encumbrance_type_id
FROM gl_bc_packets pac
WHERE int.event_id = pac.event_id
AND int.cc_acct_line_id = pac.source_distribution_id_num_1
-- Commented as it is causing issues with result updation Refer Bug 6628196
-- AND (nvl(pac.accounted_dr, 0) = nvl(INT.cc_func_dr_amt, -1) OR nvl(pac.accounted_cr, 0) = nvl(INT.cc_func_cr_amt, -1))
AND (sign(nvl(pac.accounted_dr, 0)) = sign(nvl(INT.cc_func_dr_amt, -1)) OR sign(nvl(pac.accounted_cr, 0)) = sign(nvl(INT.cc_func_cr_amt, -1)))
)
WHERE
int.cbc_result_code IS NULL AND
int.cc_header_id = g_cc_header_id;
Put_Debug_Msg (l_full_path, 'Number of rows updated: ' || SQL%ROWCOUNT);
SELECT cc_state
FROM igc_cc_headers
WHERE cc_header_id = g_cc_header_id;
SELECT distinct reference_8
INTO l_reference_8
FROM igc_cc_interface
WHERE cc_header_id = g_cc_header_id;
SELECT org_id INTO p_org_id
FROM igc_cc_headers_all
WHERE cc_header_id = g_cc_header_id;
SELECT distinct je_category_name INTO l_je_category_name
FROM igc_cc_interface
WHERE cc_header_id = g_cc_header_id;
SELECT org_id INTO p_org_id
FROM po_requisition_headers_all
WHERE requisition_header_id = g_cc_header_id;
SELECT org_id INTO p_org_id
FROM po_headers_all
WHERE po_header_id = g_cc_header_id;
SELECT proj.org_id INTO p_org_id
FROM pa_budget_versions BUD,
pa_projects_all PROJ
WHERE proj.project_id = bud.project_id
AND bud.budget_version_id = g_cc_header_id;
INSERT INTO psa_bc_xla_events_gt (event_id, result_code)
VALUES (g_xla_events_gt_tbl(i).event_id, g_xla_events_gt_tbl(i).result_code);
PROCEDURE Update_Event_ID IS
BEGIN
FOR i IN 1..g_cc_interface_head_tbl.COUNT
LOOP
UPDATE igc_cc_interface SET event_id = g_cc_interface_head_tbl(i).event_id
WHERE
cc_header_id = g_cc_interface_head_tbl(i).cc_header_id AND
document_type = g_cc_interface_head_tbl(i).document_type AND
budget_dest_flag = g_cc_interface_head_tbl(i).budget_dest_flag AND
cc_transaction_date = g_cc_interface_head_tbl(i).cc_transaction_date AND
nvl(cc_det_pf_line_id, 1) = nvl(g_cc_interface_head_tbl(i).cc_det_pf_line_id, 1) AND
reference_4 = g_cc_interface_head_tbl(i).reference_4;
END Update_Event_ID;
SELECT distinct funds_checker_code, severity_rank INTO l_batch_result_code, x_sev_rank
FROM igc_cc_result_code_ranks
WHERE severity_rank =
(
SELECT min(severity_rank)
FROM igc_cc_result_code_ranks
WHERE funds_checker_code IN
(
SELECT TRIM(cbc_result_code)
FROM igc_cc_interface
WHERE cc_header_id = g_cc_header_id
)
);