[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
(SELECT ei.expenditure_item_id
FROM pa_cc_dist_lines ccd,
Pa_expenditure_items ei,
Pa_expenditures exp
WHERE ccd.transfer_status_code in ('P','R')
AND (p_gl_category is null OR ccd.line_type = p_gl_category)
AND (ccd.line_type = 'BL' OR
(Ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
FROM pa_draft_invoices_all di,
Pa_draft_invoice_details_all did
WHERE di.draft_invoice_num = did.draft_invoice_num
AND ccd.reference_1 = did.draft_invoice_detail_id
AND di.transfer_status_code = 'A')))
AND TRUNC (CCD.GL_Date) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID
AND ei.expenditure_item_id = ccd.expenditure_item_id
AND exp.expenditure_id = ei.expenditure_id
AND exp.expenditure_group = p_EXPENDITURE_BATCH)
AND p_EXPENDITURE_BATCH is NOT NULL
UNION ALL
SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
(SELECT ei.expenditure_item_id
FROM pa_cc_dist_lines ccd,
pa_expenditure_items ei,
pa_projects_all proj
WHERE ccd.transfer_status_code in ('P','R')
AND (p_gl_category is null OR ccd.line_type = p_gl_category)
AND (ccd.line_type = 'BL' OR
(Ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
FROM pa_draft_invoices_all di,
Pa_draft_invoice_details_all did
WHERE di.draft_invoice_num = did.draft_invoice_num
AND ccd.reference_1 = did.draft_invoice_detail_id
AND di.transfer_status_code = 'A')))
AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID
AND ei.expenditure_item_id = ccd.expenditure_item_id
AND ei.project_id = proj.project_id
AND (((p_FROM_PROJECT_NUMBER is not null and proj. SEGMENT1 >= p_FROM_PROJECT_NUMBER)
AND (p_TO_PROJECT_NUMBER is not null and proj. SEGMENT1 <= p_TO_PROJECT_NUMBER))
OR
(p_FROM_PROJECT_NUMBER is not null and p_TO_PROJECT_NUMBER is null
and proj.SEGMENT1 >= p_FROM_PROJECT_NUMBER)
OR
(p_TO_PROJECT_NUMBER is not null and p_FROM_PROJECT_NUMBER is null
and proj.SEGMENT1 <= p_TO_PROJECT_NUMBER)))
AND (p_FROM_PROJECT_NUMBER is NOT NULL or p_TO_PROJECT_NUMBER is NOT NULL)
AND p_EXPENDITURE_BATCH is NULL
UNION ALL
SELECT expenditure_item_id from pa_expenditure_items_all where expenditure_item_id in
(SELECT ccd.expenditure_item_id
FROM pa_cc_dist_lines ccd
WHERE ccd.transfer_status_code in ('P','R')
AND (p_gl_category is null OR ccd.line_type = p_gl_category)
AND (ccd.line_type = 'BL' OR
(ccd.line_type = 'PC' AND EXISTS (SELECT 'X'
FROM pa_draft_invoices_all di,
pa_draft_invoice_details_all did
WHERE di.draft_invoice_num = did.draft_invoice_num
AND ccd.reference_1 = did.draft_invoice_detail_id
AND di.transfer_status_code = 'A')))
AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID)
AND p_FROM_PROJECT_NUMBER is NULL
AND p_TO_PROJECT_NUMBER is NULL
AND p_EXPENDITURE_BATCH is NULL;
UPDATE pa_expenditure_items
SET last_updated_by = last_updated_by
WHERE expenditure_item_id = EiidTab (i);
UPDATE pa_cc_dist_lines ccd
SET ccd.transfer_status_code = 'X',
ccd.transfer_rejection_code = NULL,
ccd.request_id = G_conc_request_id
WHERE ccd.expenditure_item_id = EiidTab (i)
AND CCD.transfer_status_code IN ('P', 'R')
AND (p_gl_category IS NULL OR CCD.line_type = p_gl_category)
AND (CCD.line_type = 'BL'
OR (CCD.line_type = 'PC' AND EXISTS (SELECT 'X'
FROM pa_draft_invoices_all di,
Pa_draft_invoice_details_all did
WHERE di.draft_invoice_num = did.draft_invoice_num
AND ccd.reference_1 = did.draft_invoice_detail_id
AND di.transfer_status_code = 'A')))
AND TRUNC (CCD.GL_DATE) <= NVL (p_END_GL_DATE, CCD.GL_DATE)
AND NVL (CCD.request_id, G_Conc_Request_ID+1) <> G_Conc_Request_ID ;
log_message('50 : CCDL updated with '||sql%rowcount ||' records');
select set_of_books_id
into G_Current_SOB_Id
from pa_implementations;