[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Bug 2879644. Added NVL() while selecting org_id. |
| g_func_curr is not used anywhere and hence selecting |
| currency_code can be removed later. |
+======================================================*/
if ( g_func_curr is null or g_ou_id is null ) /* Reverted the fix done via 6908073 for bug 7365397 */
/* Removed the g_ou_id nul lcondition for bug 6908073 */
then
l_stage := 'Selecting Functional Currency, l_curr_org_id: ' || l_curr_org_id ||', p_org_id: ' || p_org_id;
select nvl(p_org_id, imp.org_id)
,sob.currency_code
into g_ou_id
,g_func_curr
from gl_sets_of_books sob
,pa_implementations imp
where imp.set_of_books_id = sob.set_of_books_id and
imp.org_id = nvl(l_curr_org_id, imp.org_id); /* Reverted the fix of 7191479 for bug 7365397 */
/* Modified the above select for bug 7191479 */
/* Bug 9913685: added nvl in the above condition */
l_stage := 'Org Id [' || to_char(g_ou_id) || '] Func Curr [' || g_func_curr || ']';
l_stage := 'Selecting Organization and Job Ids';
select nvl(x_organization_id, per.organization_id)
,nvl(x_job_id, per.job_id)
into x_organization_id
,x_job_id
from per_assignments_f per
,per_assignment_status_types type
where trunc(p_txn_date) between trunc(effective_start_date) and trunc(nvl(effective_end_date,p_txn_date))
and per.person_id = p_person_id
and per.primary_flag = 'Y'
and per.assignment_type in ('E', 'C')
and per.assignment_status_type_id = type.assignment_status_type_id
and type.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK')
;
l_stage := 'Selecting Override';
select detail.compensation_rule_set
,detail.hourly_cost_rate
,detail.rate_schedule_id
,detail.override_type
,detail.cost_rate_currency_code
,detail.acct_rate_type
,detail.acct_rate_date_code
,detail.acct_exchange_rate
,detail.start_date_active
,detail.end_date_active
,detail.rbc_element_type_id
into x_costing_rule
,x_cost_rate
,x_rate_sch_id
,l_override_type
,x_cost_rate_curr_code
,x_acct_rate_type
,x_acct_rate_date_code
,x_acct_exch_rate
,x_start_date_active
,x_end_date_active
,l_rbc_elem_type_id
from pa_compensation_details_all detail /*2879644:Added ALL*/
where trunc(p_txn_date) between trunc(detail.start_date_active)
and trunc(nvl(detail.end_date_active,p_txn_date))
and NVL(detail.org_id,-99) = NVL(NVL(p_org_id, g_ou_id),-99) /*2879644 :Added org_id join 7158405 */
and detail.person_id = p_person_id;
l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
select rule.costing_method, rule.rate_source_code
into l_costing_method, l_rate_source_code
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = x_costing_rule
;
l_stage := 'selecting costing method from rule [' || x_costing_rule || ']';
select rule.costing_method, rule.rate_source_code
into l_costing_method, l_rate_source_code
from pa_compensation_rule_sets rule
where rule.compensation_rule_set = x_costing_rule;
select assignment_id
into l_assignment_id
from per_all_assignments_f
where person_id = p_person_id
and p_txn_date between effective_start_date and nvl(effective_end_date, p_txn_date)
and primary_flag = 'Y';
select a.currency_code
into x_cost_rate_curr_code
from pqh_criteria_rate_defn a,
pqh_criteria_rate_elements c
where a.criteria_rate_defn_id = c.criteria_rate_defn_id
and c.element_type_id = l_rbc_elem_type_id;
select rate_sch.schedule_type
,rate_sch.rate_sch_currency_code
,rate_sch.job_group_id
into l_sch_type
,x_cost_rate_curr_code
,l_job_group_id
from pa_std_bill_rate_schedules_all rate_sch
where rate_sch.bill_rate_sch_id = x_rate_sch_id;
select bill_rates.rate
,bill_rates.start_date_active
,bill_rates.end_date_active
into x_cost_rate
,x_start_date_active
,x_end_date_active
from pa_bill_rates_all bill_rates
where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
and trunc(nvl(bill_rates.end_date_active,p_txn_date))
and bill_rates.person_id = p_person_id
and bill_rates.bill_rate_sch_id = x_rate_sch_id
;
select bill_rates.rate
,bill_rates.start_date_active
,bill_rates.end_date_active
into x_cost_rate
,x_start_date_active
,x_end_date_active
from pa_bill_rates_all bill_rates
where trunc(p_txn_date) between trunc(bill_rates.start_date_active)
and trunc(nvl(bill_rates.end_date_active, p_txn_date))
and bill_rates.job_id = l_dest_job_id
and bill_rates.bill_rate_sch_id = x_rate_sch_id
;
select assign.org_labor_sch_rule_id
,decode(p_calling_module ,'REQUIREMENT'
,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
,assign.labor_costing_rule
,assign.overtime_project_id
,assign.overtime_task_id
,assign.acct_rate_date_code
,assign.acct_rate_type
,assign.acct_exchange_rate
,assign.base_hours
,assign.rbc_element_type_id
from pa_org_labor_sch_rule assign
,pa_org_hierarchy_denorm hier
,pa_implementations imp
where hier.child_organization_id = p_organization_id
and imp.exp_org_structure_version_id=hier.org_hierarchy_version_id
and hier.pa_org_use_type = 'TP_SCHEDULE'
and assign.organization_id = hier.parent_organization_id
and (assign.org_id = p_org_id or assign.org_id is null)
and trunc(p_txn_date) between trunc(assign.start_date_active)
and trunc(nvl(assign.end_date_active,p_txn_date));
select assign.org_labor_sch_rule_id
,decode(p_calling_module ,'REQUIREMENT'
,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
,assign.labor_costing_rule
,assign.overtime_project_id
,assign.overtime_task_id
,assign.acct_rate_date_code
,assign.acct_rate_type
,assign.acct_exchange_rate
,assign.base_hours
,assign.rbc_element_type_id
from pa_org_labor_sch_rule assign
,pa_org_hierarchy_denorm hier
,pa_implementations imp
where hier.child_organization_id = p_organization_id
and imp.exp_org_structure_version_id=hier.org_hierarchy_version_id
and hier.pa_org_use_type = 'TP_SCHEDULE'
and assign.organization_id = hier.parent_organization_id
and (assign.org_id = p_org_id or assign.org_id is null)
and trunc(p_txn_date) between trunc(assign.start_date_active)
and trunc(nvl(assign.end_date_active,p_txn_date))
and (exists( select null
from pa_std_bill_rate_schedules_all rate_sch
,pa_bill_rates_all bill_rates
where rate_sch.bill_rate_sch_id =
decode(p_calling_module ,'REQUIREMENT'
,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
and rate_sch.schedule_type = 'EMPLOYEE'
and bill_rates.person_id = p_person_id
and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
trunc(nvl(bill_rates.end_date_active,p_txn_date))
)
or
exists( select null
from pa_std_bill_rate_schedules_all rate_sch
,pa_bill_rates_all bill_rates
where rate_sch.bill_rate_sch_id =
decode(p_calling_module ,'REQUIREMENT'
,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
and bill_rates.bill_rate_sch_id = rate_sch.bill_rate_sch_id
and rate_sch.schedule_type = 'JOB'
and bill_rates.job_id =
pa_cross_business_grp.IsMappedToJob(p_job_id, rate_sch.job_group_id)
and trunc(p_txn_date) between trunc(bill_rates.start_date_active) and
trunc(nvl(bill_rates.end_date_active,p_txn_date))
)
)
order by hier.parent_level desc
,assign.organization_id
,assign.org_id;
select costing_method, rate_source_code
into l_costing_method, l_rate_source_code
from pa_compensation_rule_sets
where compensation_rule_set = l_costing_rule;
select assign.org_id
,assign.org_labor_sch_rule_id
,decode(p_calling_module ,'REQUIREMENT'
,assign.forecast_cost_rate_sch_id,assign.cost_rate_sch_id)
,assign.labor_costing_rule
,assign.overtime_project_id
,assign.overtime_task_id
,assign.acct_rate_date_code
,assign.acct_rate_type
,assign.acct_exchange_rate
,assign.base_hours
,assign.rbc_element_type_id
into g_ou_id
,g_ou_org_labor_sch_rule_id
,g_ou_cost_rate_sch_id
,g_ou_labor_costing_rule
,g_ou_ot_project_id
,g_ou_ot_task_id
,g_ou_acct_rate_date_code
,g_ou_acct_rate_type
,g_ou_acct_exch_rate
,g_base_hours
,g_rbc_elem_type_id
from pa_org_labor_sch_rule assign
where assign.organization_id is null
and trunc(p_txn_date) between trunc(assign.start_date_active)
and trunc(nvl(assign.end_date_active,p_txn_date))
and nvl(assign.org_id,-99) = nvl(p_org_id, -99);