[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'AP_PROJ_TASK_EXIST_PA'
FROM
PA_COST_DISTRIBUTION_LINES CDL,
PA_EXPENDITURE_ITEMS EI
WHERE
EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND CDL.TRANSFER_STATUS_CODE IN ('P','R','X')
AND EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
AND CDL.LINE_TYPE = 'R'
AND CDL.system_reference2 = to_char(P_invoice_id);
SELECT 'AP_SPLIT_EXIST_PA'
FROM
PA_COST_DISTRIBUTION_LINES CDL
WHERE
CDL.system_reference2 = to_char(P_invoice_id)
AND CDL.transfer_status_code||'' IN ('V','A')
AND CDL.line_type = 'R'
AND EXISTS
( SELECT ' There are Splits/Transfers on EI'
FROM PA_EXPENDITURE_ITEMS EI
WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI', 'ER' )
AND EI.TRANSFERRED_FROM_EXP_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID IS NULL
AND EI.COST_DISTRIBUTED_FLAG||'' = 'N'
);
SELECT 'AP_RECALC_COST_PA'
FROM
PA_COST_DISTRIBUTION_LINES CDL
WHERE
CDL.system_reference2 = to_char(P_invoice_id)
AND CDL.transfer_status_code||'' IN ( 'V', 'A' )
AND CDL.line_type = 'R'
AND EXISTS
( SELECT 'Marked for recalc'
FROM PA_EXPENDITURE_ITEMS EI
WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
AND EI.EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EI.COST_DISTRIBUTED_FLAG = 'N'
);
SELECT 'AP_ADJ_EXIST_PA'
FROM
PA_COST_DISTRIBUTION_LINES CDL
WHERE
CDL.system_reference2 = to_char(P_invoice_id)
AND CDL.transfer_status_code ||'' IN ('V','A')
AND CDL.line_type = 'R'
AND EXISTS
( SELECT ' Reversed EI '
FROM PA_EXPENDITURE_ITEMS EI
WHERE EI.SYSTEM_LINKAGE_FUNCTION in ( 'VI','ER')
AND EI.ADJUSTED_EXPENDITURE_ITEM_ID = CDL.EXPENDITURE_ITEM_ID
AND EI.COST_DISTRIBUTED_FLAG||'' = 'N'
);
SELECT CANCELLED_DATE,
CANCELLED_BY
INTO v_cancelled_date,
v_cancelled_by
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
*SQL to select the earliest open PA_DATE.
*Select the earliest open date ONLY if the global earliest date is NOT yet populated.
*Because , earliest pa_date will remain the same for a run.
*/
IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
-- Note : This SQL uses the p_accounting_date filter criteria.
SELECT pap1.start_date
,pap1.end_date
,pap1.period_name
INTO l_earliest_start_date
,l_earliest_end_date
,l_earliest_period_name
FROM pa_periods_all pap1
WHERE pap1.status IN ('O','F')
AND pap1.org_id = p_org_id
AND pap1.start_date = ( SELECT MIN(pap.start_date)
FROM pa_periods_all pap
WHERE status IN ('O','F')
AND org_id = p_org_id
);
SELECT pap.start_date
,pap.end_date
,p_accounting_date
,pap.period_name
INTO l_start_date
,l_end_date
,l_pa_date
,l_period_name
FROM pa_periods_all pap
WHERE pap.status in ('O','F')
AND pap.end_date >= TRUNC(p_expenditure_item_date)
AND p_accounting_date BETWEEN pap.start_date and pap.end_date
AND org_id = p_org_id ;
SELECT pap1.start_date
,pap1.end_date
,pap1.start_date
,pap1.period_name
INTO l_start_date
,l_end_date
,l_pa_date
,l_period_name
FROM pa_periods_all pap1
WHERE pap1.org_id = p_org_id /*Added While fixing bug 1657231*/
AND pap1.start_date = ( SELECT MIN(pap.start_date)
FROM pa_periods_all pap
WHERE status IN ('O','F')
AND pap.start_date >= TRUNC(p_expenditure_item_date)
AND org_id = p_org_id
);
*SQL to select the earliest open PA_DATE.
*Select the earliest open date ONLY if the global earliest date is NOT yet populated.
*Because , earliest pa_date will remain the same for a run.
*/
IF ( p_caller_flag = 'R' AND g_r_earliest_pa_start_date IS NULL ) OR
( p_caller_flag = 'P' AND g_p_earliest_pa_start_date IS NULL ) THEN
-- Note : This SQL uses the p_accounting_date filter criteria.
SELECT pap1.start_date
,pap1.end_date
,pap1.period_name
INTO l_earliest_start_date
,l_earliest_end_date
,l_earliest_period_name
FROM pa_periods_all pap1
WHERE pap1.status IN ('O', 'F')
AND pap1.org_id = p_org_id
AND pap1.end_date = ( SELECT MIN(pap.end_date)
FROM pa_periods_all pap
WHERE pap.status IN ('O','F')
-- AND p_accounting_date BETWEEN pap.start_date AND pap.end_date /* commented for bug 1982225 */
AND pap.org_id = p_org_id
);
SELECT pap.start_date
,pap.end_date
,pap.end_date
,pap.period_name
INTO l_start_date
,l_end_date
,l_pa_date
,l_period_name
FROM pa_periods_all pap
WHERE status in ('O','F')
AND pap.end_date >= TRUNC(p_expenditure_item_date)
AND p_accounting_date BETWEEN pap.start_date and pap.end_date
AND org_id = p_org_id ;
SELECT pap1.start_date
,pap1.end_date
,pap1.end_date
,pap1.period_name
INTO l_start_date
,l_end_date
,l_pa_date
,l_period_name
FROM pa_periods_all pap1
WHERE pap1.end_date = ( SELECT MIN(pap.end_date)
FROM pa_periods_all pap
WHERE pap.status IN ('O','F')
AND pap.end_date >= TRUNC(p_expenditure_item_date)
AND pap.org_id = p_org_id
)
AND pap1.org_id = p_org_id; /* Added While fixing bug 1657231
SELECT MIN(pap.end_date)
INTO l_pa_period_end_date
FROM pa_periods pap
WHERE pap.status in ( 'O', 'F')
AND pap.end_date >= p_raw_cdl_date;
SELECT PERIOD.period_name, PERIOD.start_date, PERIOD.end_date
INTO l_gl_period_name, l_gl_start_date, l_gl_end_date
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.set_of_books_id = p_set_of_books_id
AND PERIOD.application_id = Pa_Period_Process_Pkg.Application_Id
AND PERIOD.adjustment_period_flag = 'N'
AND p_gl_date BETWEEN PERIOD.start_date AND PERIOD.end_date
;