[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM gms_budget_versions pbv,
gms_resource_assignments pra,
gms_budget_lines pbl
WHERE pbv.budget_version_id = pra.budget_version_id
AND pbv.award_id = P_AWARD_ID
AND pra.resource_assignment_id = pbl.resource_assignment_id
AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
and pbl.start_date < P_START_DATE
and P_START_DATE NOT BETWEEN pbl.start_date AND pbl.end_date--Condition added for Bug 5402500
and pbl.burdened_cost IS NOT null;--Condition added for Bug 5402500
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
pa_expenditure_items_all exp
WHERE adl.expenditure_item_id = exp.expenditure_item_id
AND adl.document_type = 'EXP'
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND exp.expenditure_item_date < P_START_DATE) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
gms_encumbrance_items_all enc
WHERE adl.expenditure_item_id = enc.encumbrance_item_id
AND adl.document_type = 'ENC'
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
-- ==============================================================
-- = Bug Fix 3543931
-- = Award end date validations :
-- = Allow to change before fully liquidated encumbrances.
-- = ============================================================
AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl.line_num_reversed is null --Bug 5726575
AND enc.encumbrance_item_date < P_START_DATE ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl1,
gms_encumbrance_items_all enc1,
gms_encumbrance_items_all enc2,
gms_award_distributions adl2
WHERE adl1.expenditure_item_id = enc1.encumbrance_item_id
AND adl1.document_type = 'ENC'
AND adl1.adl_status = 'A'
AND nvl(adl1.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl1.line_num_reversed is null --Bug 5726575
AND adl1.award_id = P_AWARD_ID
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
AND adl2.expenditure_item_id = enc2.encumbrance_item_id
AND adl2.document_type = 'ENC'
AND adl2.adl_status = 'A'
AND nvl(adl2.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl2.line_num_reversed is null --Bug 5726575
AND adl2.award_id = P_AWARD_ID
AND adl2.fc_status <> adl1.fc_status
AND ( enc1.encumbrance_item_date < P_START_DATE OR
enc2.encumbrance_item_date < P_START_DATE ) ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
po_distributions_all po ,
po_lines_all pol --Bug 7660803/8250302
WHERE adl.po_distribution_id = po.po_distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND adl.award_set_id = po.award_id -- Bug 3985177
AND adl.adl_line_num = 1 -- Bug 3985177
AND adl.document_type = 'PO' -- Bug 3985177
AND po.expenditure_item_date < P_START_DATE
AND po.po_line_id = pol.po_line_id
AND nvl(cancel_flag,'N') <> 'Y' );
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
ap_invoice_distributions_all ap ,
ap_invoices_all ai --Bug 7660803/8250302
WHERE adl.invoice_distribution_id = ap.invoice_distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND ap.expenditure_item_date < P_START_DATE
AND ap.invoice_id = ai.invoice_id --Bug 7660803 / 8250302
AND ai.cancelled_date is null);
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
po_req_distributions_all req ,
po_requisition_lines_all pol --Bug 7660803 / 8250302
WHERE adl.distribution_id = req.distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND req.expenditure_item_date < P_START_DATE
AND req.requisition_line_id = pol.requisition_line_id --Bug 7660803 / 8250302
AND nvl(pol.cancel_flag,'N') <> 'Y') ;
SELECT 1
FROM gms_budget_versions pbv,
gms_resource_assignments pra,
gms_budget_lines pbl
WHERE pbv.budget_version_id = pra.budget_version_id
AND pbv.award_id = P_AWARD_ID
AND pra.resource_assignment_id = pbl.resource_assignment_id
AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
and pbl.end_date > P_END_DATE
and P_END_DATE NOT BETWEEN pbl.start_date AND pbl.end_date--Condition added for Bug 5411155
and pbl.burdened_cost IS NOT null;--Condition added for Bug 5411155
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
pa_expenditure_items_all exp
WHERE adl.expenditure_item_id = exp.expenditure_item_id
AND adl.document_type = 'EXP'
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND exp.expenditure_item_date > P_END_DATE) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
gms_encumbrance_items_all enc
WHERE adl.expenditure_item_id = enc.encumbrance_item_id
AND adl.document_type = 'ENC'
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
-- ==============================================================
-- = Bug Fix 3543931
-- = Award end date validations :
-- = Allow to change before fully liquidated encumbrances.
-- = ============================================================
AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
AND nvl(adl.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl.line_num_reversed is null --Bug 5726575
AND enc.encumbrance_item_date > P_END_DATE ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl1,
gms_encumbrance_items_all enc1,
gms_encumbrance_items_all enc2,
gms_award_distributions adl2
WHERE adl1.expenditure_item_id = enc1.encumbrance_item_id
AND adl1.document_type = 'ENC'
AND adl1.adl_status = 'A'
AND nvl(adl1.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl1.line_num_reversed is null --Bug 5726575
AND adl1.award_id = P_AWARD_ID
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
AND adl2.expenditure_item_id = enc2.encumbrance_item_id
AND adl2.document_type = 'ENC'
AND adl2.adl_status = 'A'
AND nvl(adl2.reversed_flag, 'N') = 'N' --Bug 5726575
AND adl2.line_num_reversed is null --Bug 5726575
AND adl2.award_id = P_AWARD_ID
AND adl2.fc_status <> adl1.fc_status
AND ( enc1.encumbrance_item_date > P_END_DATE OR
enc2.encumbrance_item_date > P_END_DATE ) ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
po_distributions_all po ,
po_lines_all pol --bug 7660803 / 8250302
WHERE adl.po_distribution_id = po.po_distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND adl.award_set_id = po.award_id -- Bug 3985177
AND adl.adl_line_num = 1 -- Bug 3985177
AND adl.document_type = 'PO' -- Bug 3985177
AND po.expenditure_item_date > P_END_DATE
AND po.po_line_id = pol.po_line_id --Bug 7660803
AND nvl(cancel_flag,'N') <> 'Y');
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
ap_invoice_distributions_all ap ,
ap_invoices_all ai --Bug 7660803 / 8250302
WHERE adl.invoice_distribution_id = ap.invoice_distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND ap.expenditure_item_date > P_END_DATE
AND AP.INVOICE_ID = AI.INVOICE_ID --Bug 7660803 /8250302
AND AI.CANCELLED_DATE is null);
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_award_distributions adl,
po_req_distributions_all req ,
po_requisition_lines_all pol --Bug 7660803 /8250302
WHERE adl.distribution_id = req.distribution_id
AND adl.adl_status = 'A'
AND adl.award_id = P_AWARD_ID
AND req.expenditure_item_date > P_END_DATE
AND req.requisition_line_id = pol.requisition_line_id --Bug 7660803
AND nvl(pol.cancel_flag,'n') <> 'Y') ;
select budget_version_id, project_id
from gms_budget_versions
where award_id = p_award_id
and budget_status_code in ('W','S');
select budget_version_id, project_id
from gms_budget_versions
where award_id = p_award_id
and budget_status_code = 'B'
and current_flag = 'Y';
select gra.resource_list_member_id,
gra.task_id,
gbl.start_date,
gbl.end_date
from gms_resource_assignments gra,
gms_budget_lines gbl
where gra.resource_assignment_id = gbl.resource_assignment_id
and gra.budget_version_id = p_budget_version_id;
SELECT 1
FROM gms_budget_versions pbv,
gms_resource_assignments pra,
gms_budget_lines pbl
WHERE pbv.budget_version_id = pra.budget_version_id
AND pbv.project_id = P_PROJECT_ID
AND pra.resource_assignment_id = pbl.resource_assignment_id
AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
/*Code change for bug 5470902 : Start */
--AND pbl.start_date < P_START_DATE;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_expenditure_items_all exp
/* pa_tasks tsk 4138033 */
WHERE /*exp.task_id = tsk.task_id
AND */ exp.project_id = P_PROJECT_ID
and exp.task_id = nvl(P_TASK_ID, exp.task_id)
AND exp.expenditure_item_date < P_START_DATE) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc,
pa_tasks tsk
WHERE enc.task_id = tsk.task_id
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
-- ==============================================================
-- = Bug Fix 3543931
-- = Award end date validations :
-- = Allow to change before fully liquidated encumbrances.
-- = ============================================================
AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
AND enc.encumbrance_item_date < P_START_DATE ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc1,
gms_encumbrance_items_all enc2,
pa_tasks tsk
WHERE enc1.task_id = tsk.task_id
AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
AND enc2.enc_distributed_flag <> enc1.enc_distributed_flag
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = NVL(P_TASK_ID, tsk.task_id)
AND ( enc1.encumbrance_item_date < P_START_DATE OR
enc2.encumbrance_item_date < P_START_DATE ) ) ; */
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc1,
-- gms_encumbrance_items_all enc2,
pa_tasks tsk
WHERE enc1.task_id = tsk.task_id
-- AND enc2.adjusted_encumbrance_item_id =enc1.encumbrance_item_id
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
-- AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
-- AND enc2.enc_distributed_flag <>enc1.enc_distributed_flag
AND enc1.enc_distributed_flag = 'N'
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = NVL(P_TASK_ID,tsk.task_id)
AND enc1.encumbrance_item_date < P_START_DATE );
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM po_distributions_all po ,
po_lines_all pol --Bug 8431879
WHERE po.project_id = P_PROJECT_ID
AND po.task_id = nvl(P_TASK_ID, po.task_id)
AND po.expenditure_item_date < P_START_DATE
AND po.po_line_id = pol.po_line_id --Bug 8431879
AND nvl(cancel_flag,'N') <> 'Y' ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM ap_invoice_distributions_all ap,
ap_invoices_all ai --Bug 8431879
WHERE ap.project_id = P_PROJECT_ID
AND ap.task_id = nvl(P_TASK_ID, ap.task_id)
AND ap.expenditure_item_date < P_START_DATE
AND ap.invoice_id = ai.invoice_id --Bug 8431879
AND ai.cancelled_date is null ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM po_req_distributions_all req,
po_requisition_lines_all pol --Bug 8431879
WHERE req.project_id = P_PROJECT_ID
AND req.task_id = nvl(P_TASK_ID, req.task_id)
AND req.expenditure_item_date < P_START_DATE
AND req.requisition_line_id = pol.requisition_line_id --Bug 8431879
AND nvl(pol.cancel_flag,'N') <> 'Y') ;
SELECT 1
FROM DUAL
WHERE EXISTS
( SELECT 1
FROM gms_budget_versions pbv,
gms_resource_assignments pra,
gms_budget_lines pbl
WHERE pbv.budget_version_id = pra.budget_version_id
AND pbv.project_id = P_PROJECT_ID
AND pra.resource_assignment_id = pbl.resource_assignment_id
AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y')));
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM gms_award_distributions adl
WHERE adl.project_id = P_PROJECT_ID
AND adl.task_id = nvl(P_TASK_ID, adl.task_id));
SELECT 1
FROM gms_budget_versions pbv,
gms_resource_assignments pra,
gms_budget_lines pbl
WHERE pbv.budget_version_id = pra.budget_version_id
AND pbv.project_id = P_PROJECT_ID
AND pra.resource_assignment_id = pbl.resource_assignment_id
AND (pbv.budget_status_code = 'W' or (pbv.budget_status_code = 'B' and pbv.current_flag = 'Y'))
AND pbl.start_date > P_COMPLETION_DATE
AND pbl.burdened_cost IS NOT null; /*Code change for bug 5470902 */
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM pa_expenditure_items_all exp
/* pa_tasks tsk */
WHERE /* exp.task_id = tsk.task_id
AND */ exp.project_id = P_PROJECT_ID
AND exp.task_id = nvl(P_TASK_ID, exp.task_id)
AND exp.expenditure_item_date > P_COMPLETION_DATE) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc,
pa_tasks tsk
WHERE enc.task_id = tsk.task_id
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
-- ==============================================================
-- = Bug Fix 3543931
-- = Award end date validations :
-- = Allow to change before fully liquidated encumbrances.
-- = ============================================================
AND NVL(enc.net_zero_adjustment_flag,'N') <> 'Y'
AND enc.encumbrance_item_date > P_COMPLETION_DATE ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc1,
gms_encumbrance_items_all enc2,
pa_tasks tsk
WHERE enc1.task_id = tsk.task_id
AND enc2.adjusted_encumbrance_item_id = enc1.encumbrance_item_id
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
AND enc2.enc_distributed_flag <> enc1.enc_distributed_flag
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = nvl(P_TASK_ID, tsk.task_id)
AND ( enc1.encumbrance_item_date > P_COMPLETION_DATE OR
enc2.encumbrance_item_date > P_COMPLETION_DATE ) ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM gms_encumbrance_items_all enc1,
-- gms_encumbrance_items_all enc2,
pa_tasks tsk
WHERE enc1.task_id = tsk.task_id
-- AND enc2.adjusted_encumbrance_item_id =enc1.encumbrance_item_id
AND NVL(enc1.net_zero_adjustment_flag,'N') = 'Y'
-- AND NVL(enc2.net_zero_adjustment_flag,'N') = 'Y'
-- AND enc2.enc_distributed_flag <>enc1.enc_distributed_flag
AND enc1.enc_distributed_flag = 'N'
AND tsk.project_id = P_PROJECT_ID
AND tsk.task_id = nvl(P_TASK_ID,tsk.task_id)
AND enc1.encumbrance_item_date >P_COMPLETION_DATE ) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM po_distributions_all po,
po_lines_all pol --Bug 8431879
WHERE po.project_id = P_PROJECT_ID
AND po.task_id = nvl(P_TASK_ID, po.task_id)
AND po.expenditure_item_date > P_COMPLETION_DATE
AND po.po_line_id = pol.po_line_id --Bug 8431879
AND nvl(cancel_flag,'N') <> 'Y') ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM ap_invoice_distributions_all ap,
ap_invoices_all ai --Bug 8431879
WHERE ap.project_id = P_PROJECT_ID
AND ap.task_id = nvl(P_TASK_ID, ap.task_id)
AND ap.expenditure_item_date > P_COMPLETION_DATE
AND ap.invoice_id = ai.invoice_id --Bug 8431879
AND ai.cancelled_date is null) ;
SELECT 1
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM po_req_distributions_all req,
po_requisition_lines_all pol --Bug 8431879
WHERE req.project_id = P_PROJECT_ID
AND req.task_id = nvl(P_TASK_ID, req.task_id)
AND req.expenditure_item_date > P_COMPLETION_DATE
AND req.requisition_line_id = pol.requisition_line_id --Bug 8431879
AND nvl(pol.cancel_flag,'N') <> 'Y') ;