[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
INTO l_unreserv_dist_rec
FROM po_bc_distributions
WHERE header_id = p_bc_dist_rec.header_id
AND distribution_id = p_bc_dist_rec.distribution_id
AND ae_event_id = (SELECT max(ae_event_id)
FROM po_bc_distributions pbd
WHERE pbd.header_id = p_bc_dist_rec.header_id
AND pbd.distribution_id = p_bc_dist_rec.distribution_id
AND pbd.ae_event_id <> p_bc_dist_rec.ae_event_id
AND pbd.distribution_type NOT IN ('REQUISITION', 'AGREEMENT', 'PLANNED')
AND pbd.main_or_backing_code = 'M'
AND EXISTS (SELECT 1
FROM xla_ae_headers xah
WHERE application_id = p_application_id
AND xah.event_id = pbd.ae_event_id
AND xah.accounting_entry_status_code = 'F') );
stack_error (l_procedure_name, 'SELECT_po_bc_distributions', p_error_desc);
trace(C_STATE_LEVEL, l_procedure_name, 'ERROR:SELECT_po_bc_distributions:'||p_error_desc);
FOR gt_rec IN (SELECT fed.event_id,
NVL(fed.old_ccid, pbd.code_combination_id) code_combination_id,
pbd.header_id,
pbd.distribution_id,
fed.ent_unanticipated_bud_amount,
fed.acc_unanticipated_bud_amount,
fed.ent_anticipated_budget_amount,
fed.acc_anticipated_budget_amount,
fed.rowid fed_rowid
FROM fv_extract_detail_gt fed,
po_bc_distributions pbd
WHERE fed.event_id = pbd.ae_event_id
AND fed.line_number = pbd.line_number
AND pbd.main_or_backing_code = 'M') LOOP
trace(C_STATE_LEVEL, l_procedure_name, 'gt_rec.event_id='||gt_rec.event_id);
SELECT SUM(NVL(fed.ent_unanticipated_bud_amount, 0)),
SUM(NVL(fed.acc_unanticipated_bud_amount, 0)),
SUM(NVL(fed.ent_anticipated_budget_amount, 0)),
SUM(NVL(fed.acc_anticipated_budget_amount, 0))
INTO l_tot_ent_unanticipated_amt,
l_tot_acc_unanticipated_amt,
l_tot_ent_anticipated_amt,
l_tot_acc_anticipated_amt
FROM fv_extract_detail_gt fed,
po_bc_distributions pbd
WHERE fed.event_id = pbd.ae_event_id
AND fed.event_id = gt_rec.event_id
AND fed.line_number = pbd.line_number
AND NVL(fed.old_ccid, pbd.code_combination_id) = gt_rec.code_combination_id
AND pbd.applied_to_dist_id_2 = gt_rec.distribution_id
AND pbd.main_or_backing_code <> 'M';
UPDATE fv_extract_detail_gt
SET ent_unanticipated_bud_amount = NVL(ent_unanticipated_bud_amount, 0) + NVL(l_tot_ent_unanticipated_amt, 0),
acc_unanticipated_bud_amount = NVL(acc_unanticipated_bud_amount, 0) + NVL(l_tot_acc_unanticipated_amt, 0),
ent_anticipated_budget_amount = NVL(ent_anticipated_budget_amount, 0) + NVL(l_tot_ent_anticipated_amt, 0),
acc_anticipated_budget_amount = NVL(acc_anticipated_budget_amount, 0) + NVL(l_tot_acc_anticipated_amt, 0)
WHERE rowid = gt_rec.fed_rowid;
trace(C_STATE_LEVEL, l_procedure_name, 'Updated '||SQL%ROWCOUNT||'rows with rowid');
UPDATE fv_extract_detail_gt fed
SET ent_unanticipated_bud_amount = 0,
acc_unanticipated_bud_amount = 0,
ent_anticipated_budget_amount = 0,
acc_anticipated_budget_amount = 0
WHERE fed.event_id = gt_rec.event_id
AND EXISTS (SELECT 1
FROM po_bc_distributions pbd
WHERE fed.event_id = pbd.ae_event_id
AND fed.line_number = pbd.line_number
AND fed.event_id = gt_rec.event_id
AND NVL(fed.old_ccid, pbd.code_combination_id) = gt_rec.code_combination_id
AND pbd.applied_to_dist_id_2 = gt_rec.distribution_id
AND pbd.main_or_backing_code <> 'M');
trace(C_STATE_LEVEL, l_procedure_name, 'Updated '||SQL%ROWCOUNT||'rows with non M');
SELECT pbd.*
FROM po_bc_distributions pbd,
xla_events_gt e
where pbd.ae_event_id = e.event_id
ORDER BY pbd.ae_event_id,
pbd.header_id,
pbd.distribution_id;
trace(C_STATE_LEVEL, l_procedure_name, 'Inserting data into GT table');
INSERT INTO fv_extract_detail_gt
VALUES p_fv_extract_detail(l_index);
trace(C_STATE_LEVEL, l_procedure_name, 'No of rows inserted into FV_EXTRACT_DETAIL_GT: '|| SQL%ROWCOUNT );