[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT hourly_cost_rate
INTO l_labor_cost_rate
FROM pa_compensation_details_all cd
WHERE cd.person_id = p_person_id
AND cd.org_id = p_expenditure_org_id --Bug#5903720
AND p_item_date BETWEEN cd.start_date_active AND NVL(cd.end_date_active,P_item_date);
SELECT multiplier
INTO l_cost_multiplier
FROM PA_LABOR_COST_MULTIPLIERS LCM
WHERE LCM.LABOR_COST_MULTIPLIER_NAME = P_labor_Cost_Mult_Name
AND P_item_date BETWEEN LCM.start_date_active AND NVL(LCM.end_date_active,P_item_date);
SELECT OVERRIDE_TO_ORGANIZATION_ID
INTO l_x_override_to_org_id
FROM pa_cost_dist_overrides CDO,
pa_expenditure_types ET
WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
AND CDO.person_id = P_person_id
AND CDO.project_id = P_project_id
AND CDO.expenditure_category = ET.expenditure_category
AND ET.expenditure_type = P_expenditure_type;
SELECT OVERRIDE_TO_ORGANIZATION_ID
INTO l_x_override_to_org_id
FROM pa_cost_dist_overrides CDO
--,pa_expenditure_types ET
WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
AND CDO.person_id = P_person_id
AND CDO.project_id = P_project_id
AND CDO.expenditure_category IS NULL;
SELECT OVERRIDE_TO_ORGANIZATION_ID
INTO l_x_override_to_org_id
FROM pa_cost_dist_overrides CDO,
pa_expenditure_types ET
WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
AND CDO.project_id = P_project_id
AND CDO.override_from_organization_id = P_incurred_by_organz_id
AND CDO.expenditure_category = ET.expenditure_category
AND ET.expenditure_type = P_expenditure_type;
SELECT OVERRIDE_TO_ORGANIZATION_ID
INTO l_x_override_to_org_id
FROM pa_cost_dist_overrides CDO
--,pa_expenditure_types ET
WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
AND CDO.project_id = P_project_id
AND CDO.override_from_organization_id = P_incurred_by_organz_id
AND CDO.expenditure_category is NULL;
SELECT burden_cost_flag
INTO l_burden_cost_flag
FROM pa_project_types_all typ, pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.project_type = typ.project_type
AND proj.org_id = typ.org_id; -- bug 5365286
SELECT burden_cost_flag
INTO l_burden_cost_flag
FROM pa_project_types_all typ, pa_projects_all proj
WHERE typ.project_type = P_project_type
AND proj.project_type = typ.project_type
AND proj.project_id = p_project_id
AND proj.org_id = typ.org_id; -- bug 5365286
SELECT NVL(tsk.project_rate_date,
DECODE(imp.default_rate_date_code,'E',p_item_date,
'P',get_pa_date(p_item_date,p_exp_org_id))),
NVL(tsk.project_rate_type, imp.default_rate_type)
INTO l_projfunc_rate_date,
l_projfunc_rate_type
FROM pa_projects_all prj,
pa_tasks tsk,
pa_implementations_all imp
WHERE prj.project_id = p_project_id
AND prj.project_id = tsk.project_id
AND tsk.task_id = p_task_id
AND nvl(prj.org_id ,-99) = nvl(imp.org_id,-99)
AND nvl(imp.org_id,-99) = nvl(p_proj_org_id,-99);
SELECT NVL(prj.projfunc_cost_rate_date,
DECODE(imp.default_rate_date_code,'E',p_item_date,
'P',get_pa_date(p_item_date,p_exp_org_id))),
NVL(prj.projfunc_cost_rate_type, imp.default_rate_type)
INTO l_projfunc_rate_date,
l_projfunc_rate_type
FROM pa_projects_all prj,
pa_implementations_all imp
WHERE prj.project_id = p_project_id
AND nvl(prj.org_id,-99) = nvl(imp.org_id,-99)
AND nvl(imp.org_id,-99) = nvl(p_proj_org_id,-99);
SELECT MIN(pap.end_date)
INTO l_pa_date
FROM pa_periods pap
WHERE status in ('O','F')
AND pap.end_date >= P_item_date
AND NVL(pap.org_id, -99) = NVL(p_expenditure_org_id, -99);
SELECT FC.currency_code
INTO l_currency_code
FROM FND_CURRENCIES FC,
GL_SETS_OF_BOOKS GB,
PA_IMPLEMENTATIONS_ALL IMP
WHERE FC.currency_code = DECODE(imp.set_of_books_id, NULL, NULL, GB.currency_code)
AND GB.set_of_books_id = IMP.set_of_books_id
AND IMP.org_id = p_org_id; --Bug#5903720
SELECT irs.ind_rate_sch_id,
DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
'REVENUE', t.rev_ind_sch_fixed_date,
'INVOICE', t.inv_ind_sch_fixed_date)
INTO l_burden_sch_id,
l_burden_sch_fixed_date
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.task_id = irs.task_id
AND ( (p_schedule_type = 'COST'
AND NVL(cost_ovr_sch_flag,'N') = 'Y')
OR (p_schedule_type = 'REVENUE'
AND NVL(rev_ovr_sch_flag,'N') = 'Y')
OR (p_schedule_type = 'INVOICE'
AND NVL(inv_ovr_sch_flag,'N') = 'Y')
);
SELECT irs.ind_rate_sch_id,
DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
'REVENUE', prj.rev_ind_sch_fixed_date,
'INVOICE', prj.inv_ind_sch_fixed_date )
INTO l_burden_sch_id,
l_burden_sch_fixed_date
FROM pa_ind_rate_schedules irs,
pa_projects_all prj
WHERE irs.project_id = prj.project_id
AND irs.project_id = p_project_id
AND irs.task_id is NULL
AND ( (p_schedule_type = 'COST'
AND NVL(cost_ovr_sch_flag,'N') = 'Y')
OR (p_schedule_type = 'REVENUE'
AND NVL(rev_ovr_sch_flag,'N') = 'Y')
OR (p_schedule_type = 'INVOICE'
AND NVL(inv_ovr_sch_flag,'N') = 'Y')
);
/* Use the decode statement to select the ind_rate_sch_id according
to the passed schedule_type to fix the bug 2046094 */
IF p_task_id IS NOT NULL THEN
BEGIN
SELECT DECODE(p_schedule_type,'COST', t.cost_ind_rate_sch_id,
'REVENUE', t.rev_ind_rate_sch_id,
'INVOICE', t.inv_ind_rate_sch_id),
DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
'REVENUE', t.rev_ind_sch_fixed_date,
'INVOICE', t.inv_ind_sch_fixed_date)
INTO l_burden_sch_id,
l_burden_sch_fixed_date
FROM pa_tasks t
WHERE t.task_id = p_task_id
AND ( p_schedule_type = 'COST'
OR p_schedule_type = 'REVENUE'
OR p_schedule_type = 'INVOICE'
);
/* Use the decode statement to select the ind_rate_sch_id according
to the passed schedule_type to fix the bug 2046094 */
BEGIN
SELECT DECODE(p_schedule_type,'COST', prj.cost_ind_rate_sch_id,
'REVENUE', prj.rev_ind_rate_sch_id,
'INVOICE', prj.inv_ind_rate_sch_id),
DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
'REVENUE', prj.rev_ind_sch_fixed_date,
'INVOICE', prj.inv_ind_sch_fixed_date)
INTO l_burden_sch_id,
l_burden_sch_fixed_date
FROM pa_projects_all prj
WHERE prj.project_id = p_project_id
AND ( p_schedule_type = 'COST'
OR p_schedule_type = 'REVENUE'
OR p_schedule_type = 'INVOICE'
);
SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_cost_rate_multiplier,1)),
(b.rate * NVL(p_cost_rate_multiplier,1) * p_quantity),rate_currency_code
INTO l_x_raw_cost_rate, l_x_raw_cost ,l_rate_currency_code
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = p_job_cost_rate_sch_id
AND b.job_id = px_proj_cost_job_id
AND b.rate is NOT NULL
AND to_date(nvl(to_date(p_schedule_date), to_date(p_item_date))+ 0.99999)
BETWEEN b.start_date_active AND NVL(to_date(b.end_date_active),
to_date(nvl(to_date(p_schedule_date), to_date(p_item_date)))) + 0.99999
AND NVL(b.org_id,-99) = NVL(p_org_id,-99);