[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO PA_FP_CALCULATE_LOG
(SESSIONID
,SEQ_NUMBER
,LOG_MESSAGE)
VALUES
(userenv('sessionid')
,HR.PAY_US_GARN_FEE_RULES_S.nextval
,substr(P_MSG,1,240)
);
SELECT 'N'
INTO l_exists_flag
FROM dual
WHERE EXISTS (select null
from pa_tasks t
where t.task_id = p_task_id
and t.project_id = p_project_id);
SELECT nvl(exp.cost_rate_flag,'N')
FROM pa_expenditure_types exp
WHERE exp.expenditure_type = p_exp_type;
SELECT lcm.multiplier
INTO l_cost_rate_multiplier
FROM pa_tasks t
,pa_labor_cost_multipliers lcm
WHERE t.task_id = p_task_id
AND t.labor_cost_multiplier_name = lcm.labor_cost_multiplier_name
AND trunc(P_exp_item_date) BETWEEN LCM.start_date_active AND
NVL(LCM.end_date_active,P_exp_item_date);
SELECT irs.ind_rate_sch_id
INTO x_burden_sch_id
FROM pa_tasks t,
pa_ind_rate_schedules irs
WHERE t.task_id = p_task_id
AND t.project_id = p_project_id
AND t.task_id = irs.task_id
AND irs.cost_ovr_sch_flag = 'Y';
SELECT irs.ind_rate_sch_id
INTO x_burden_sch_id
FROM pa_ind_rate_schedules irs
,pa_projects_all pp
WHERE pp.project_id = p_project_id
AND irs.project_id = pp.project_id
AND irs.cost_ovr_sch_flag = 'Y' ;
SELECT t.cost_ind_rate_sch_id
INTO x_burden_sch_id
FROM pa_tasks t
WHERE t.task_id = p_task_id
AND t.project_id = p_project_id;
SELECT irs.ind_rate_sch_id
INTO x_burden_sch_id
FROM pa_ind_rate_schedules irs
,pa_projects_all pp
WHERE pp.project_id = p_project_id
AND irs.project_id = pp.project_id
AND irs.cost_ovr_sch_flag = 'Y' ;
SELECT pp.cost_ind_rate_sch_id
INTO x_burden_sch_id
FROM pa_projects_all pp
WHERE pp.project_id = p_project_id ;
SELECT irs.ind_rate_sch_revision_id
INTO x_burden_sch_revision_id
FROM pa_ind_rate_sch_revisions irs
WHERE irs.ind_rate_sch_id = x_burden_sch_id
AND irs.compiled_flag = 'Y'
AND trunc(p_exp_item_date) BETWEEN irs.start_date_active
and NVL(irs.end_date_active ,p_exp_item_date );
SELECT sch.rate_sch_currency_code
,rates.rate
,rates.markup_percentage
FROM pa_std_bill_rate_schedules_all sch
,pa_bill_rates_all rates
WHERE sch.bill_rate_sch_id = p_rate_sch_id
AND sch.schedule_type = 'NON-LABOR'
AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
AND rates.expenditure_type = p_expenditure_type
AND ( rates.non_labor_resource is NULL
OR rates.non_labor_resource = p_non_labor_resource
)
AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
and trunc(nvl(rates.end_date_active,p_exp_item_date))
/*Bug fix:3793618 This is to ensure that records with NLR and Exp combo orders first */
ORDER BY decode(rates.non_labor_resource,p_non_labor_resource,0,1),rates.expenditure_type ;
SELECT sch.rate_sch_currency_code
,rates.rate
,rates.markup_percentage
FROM pa_std_bill_rate_schedules_all sch
,pa_bill_rates_all rates
WHERE sch.bill_rate_sch_id = p_rate_sch_id
AND sch.schedule_type = 'EMPLOYEE'
AND rates.person_id = p_person_id
AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
and trunc(nvl(rates.end_date_active,p_exp_item_date));
SELECT sch.rate_sch_currency_code
,rates.rate
,rates.markup_percentage
FROM pa_std_bill_rate_schedules_all sch
,pa_bill_rates_all rates
WHERE sch.bill_rate_sch_id = p_rate_sch_id
AND sch.schedule_type = 'JOB'
AND rates.job_id = pa_cross_business_grp.IsMappedToJob(p_job_id, rates.job_group_id)
AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
and trunc(nvl(rates.end_date_active,p_exp_item_date));
SELECT R.Cost_Rate
INTO l_expTypeCostRate
FROM PA_Expenditure_Types T
,PA_Expenditure_Cost_Rates_all R
WHERE T.Expenditure_type = R.Expenditure_type
AND T.Cost_Rate_Flag = 'Y'
AND R.Expenditure_type = p_exp_type
AND R.org_id = p_org_id --Bug#5903720
AND trunc(p_exp_item_date)
BETWEEN R.Start_Date_Active AND NVL(R.End_Date_Active, p_exp_item_date);
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 NVL(burden_cost_flag,'N')
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#5903720
SELECT sch.rate_sch_currency_code
,rates.rate
FROM pa_std_bill_rate_schedules_all sch
,pa_bill_rates_all rates
WHERE sch.bill_rate_sch_id = p_nlr_schedule_id
AND sch.schedule_type = 'NON-LABOR'
AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
AND rates.expenditure_type = p_expenditure_type
AND ( rates.non_labor_resource is NULL
OR rates.non_labor_resource = p_non_labor_resource
)
AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
and trunc(nvl(rates.end_date_active,p_exp_item_date))
/*Bug fix:3793618 This is to ensure that records with NLR and Exp combo orders first */
ORDER BY decode(rates.non_labor_resource,p_non_labor_resource,0,1),rates.expenditure_type ;
SELECT R.Rate
INTO l_nlr_raw_cost_rate
FROM PA_Expenditure_Types T,
PA_Usage_Cost_Rate_Ovr_all R
WHERE T.Expenditure_type = R.Expenditure_type
AND T.Cost_Rate_Flag = 'Y'
AND R.Expenditure_type = p_expenditure_type
AND R.Non_Labor_Resource = p_Non_Labor_Resource
AND R.Organization_Id = NVL(p_nlr_organization_id,p_override_organization_id)
AND NVL(R.org_id,-99) = NVL(p_org_id,-99)
AND trunc(p_exp_item_date)
BETWEEN R.Start_Date_Active
AND NVL(R.End_Date_Active, p_exp_item_date);
SELECT pp.project_id
,bv.budget_version_id
,cur.txn_currency_code txn_currency_code
/* budget line currency attributes selected for testing
--,bl.project_currency_code bgl_project_curr_code
--,bl.projfunc_currency_code bgl_projfunc_curr_code
--,bl.project_cost_rate_type bgl_project_rate_type
--,bl.project_cost_rate_date_type bgl_project_rate_date_type
--,bl.start_date bgl_project_rate_date
--,bl.project_cost_exchange_rate bgl_project_exchange_rate */
/* -----------Project Currency conversion Atrributes -----------------------------------------*/
,NVL(bl.project_currency_code,pp.project_currency_code) project_currency_code
,NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type) project_rate_type
,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
,'User',NULL
,NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)) project_rate_date_type
,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
,'START_DATE', NVL(bl.start_date,p_txn_date)
,'END_DATE' , NVL(bl.end_date,p_txn_date)
, NVL(bl.project_cost_rate_date,Nvl(fpo.project_cost_rate_date,p_txn_date))) project_rate_date
,decode(bl.project_cost_exchange_rate,NULL
, decode(NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type)
,'User',cur.PROJECT_COST_EXCHANGE_RATE
, null ),bl.project_cost_exchange_rate) project_exchange_rate
/* -------------project functinal currency conversion attributes -------------------------------*/
,NVL(bl.projfunc_currency_code,pp.projfunc_currency_code) ProjFunc_currency_code
,NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type) projfunc_rate_type
,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
,'User',NULL
,NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)) projfunc_rate_date_type
,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
,'START_DATE', NVL(bl.start_date, p_txn_date)
,'END_DATE' , NVL(bl.end_date ,p_txn_date)
, NVL(bl.projfunc_cost_rate_date,Nvl(fpo.projfunc_cost_rate_date,p_txn_date))) projfunc_rate_date
,decode(bl.projfunc_cost_exchange_rate,NULL
, decode(NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type)
,'User',cur.PROJFUNC_COST_EXCHANGE_RATE
, null),bl.projfunc_cost_exchange_rate) projfunc_exchange_rate
from pa_budget_versions bv
,pa_proj_fp_options fpo
,pa_projects_all pp
,pa_fp_txn_currencies cur
,pa_budget_lines bl
where bv.project_id = pp.project_id
and fpo.project_id = pp.project_id
and nvl(fpo.fin_plan_type_id,0) = nvl(bv.fin_plan_type_id,0)
and fpo.fin_plan_version_id = bv.budget_version_id
and bv.budget_version_id = cur.fin_plan_version_id
and cur.txn_currency_code = p_txn_curr_code
and pp.project_id = p_project_id
and bv.budget_version_id = lv_budget_version_id
and bv.budget_version_id = bl.budget_version_id (+)
and ( (nvl(bl.resource_assignment_id,lv_resource_assignment_id) = lv_resource_assignment_id
and trunc(p_txn_date) between trunc(bl.start_date) and nvl(bl.end_date,p_txn_date)
and bl.txn_currency_code = p_txn_curr_code
) OR
(NOT EXISTS
(select null from pa_budget_lines bl1
where bl1.budget_version_id = bv.budget_version_id
and bl1.resource_assignment_id = lv_resource_assignment_id
and trunc(p_txn_date) between trunc(bl1.start_date) and nvl(bl1.end_date,p_txn_date)
and bl.txn_currency_code = p_txn_curr_code
))
)
order by bv.budget_version_id ;
SELECT bl.budget_version_id
,bl.resource_assignment_id
INTO l_budget_version_id
,l_resource_assignment_id
FROM pa_budget_lines bl
WHERE bl.budget_line_id = p_budget_line_id;