[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT item_quantity
INTO l_item_quantity
FROM pa_forecast_items
WHERE forecast_item_id = p_forecast_item_id;
SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
labor_schedule_fixed_date,labor_sch_type,job_bill_rate_schedule_id,org_id,
projfunc_currency_code, /* Added the following column for MCB2 */
NVL(assign_precedes_task,'1') /* Added for Asgmt overide */
INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
l_labor_schdl_fixed_date,l_labor_sch_type,l_job_bill_rate_schedule_id,l_project_org_id,
l_projfunc_currency_code, /* Added the following columns for MCB2 */
l_assignment_precedes_task
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
labor_schedule_fixed_date,labor_sch_type
INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
l_labor_schdl_fixed_date,l_labor_sch_type
FROM pa_tasks
WHERE task_id = p_task_id;
/* There was a call for PA_COST.get_projfunc_raw_burdened , it has been deleted
for Org Forecasting */
ELSIF (l_labor_sch_type = 'B' ) THEN
-- Calling job id conversion procedure from resource
PA_RESOURCE_UTILS.GetToJobId( p_forecast_job_grp_id,
p_forecast_job_id,
p_project_bill_job_grp_id,
px_project_bill_job_id);
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
),
DECODE(asgn.bill_rate_override, NULL,
((100 + asgn.markup_percent_override)
* p_raw_cost / 100),
(asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
* p_quantity)),
DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_discount_pct
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code ,
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code ,-- Added for MCB2
l_discount_pct
FROM pa_job_bill_rate_overrides j
WHERE j.task_id = p_task_id
AND TO_DATE(p_item_date)
BETWEEN TO_DATE(j.start_date_active)
AND NVL(TO_DATE(j.end_date_active),
TO_DATE(p_item_date))
AND j.job_id = px_project_bill_job_id;
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
),
DECODE(asgn.bill_rate_override, NULL,
((100 + asgn.markup_percent_override)
* p_raw_cost / 100),
(asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
* p_quantity)),
DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_discount_pct
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
j.discount_percentage /* Added for MCB2 */
INTO l_bill_rate,
l_raw_revenue,
l_rate_currency_code, /* Added for MCB2 */
l_discount_pct /* Added for discount percentage*/
FROM pa_job_bill_rate_overrides j
WHERE j.project_id = p_project_id
/* 0.99999 added to the dates so that the starting clause of
the between condition does not have aby function on it so
as to better use the index */
AND trunc(p_item_date) + 0.99999 /* BUG#3118592 */
BETWEEN j.start_date_active
AND NVL(trunc(j.end_date_active) + 0.99999, /* BUG#3118592 */
trunc(p_item_date) + 0.99999) /* BUG#3118592 */
AND j.job_id = px_project_bill_job_id;
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)
),
DECODE(b.rate, NULL,
((100 + b.markup_percentage)
* p_raw_cost / 100),
(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity)),
DECODE(NVL( l_discount_pct,l_labor_schdl_discnt), NULL, NULL,
(b.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) /100)),
DECODE(NVL( l_discount_pct,l_labor_schdl_discnt), NULL, NULL,
DECODE(b.rate, NULL,
((100 + b.markup_percentage)
* (p_raw_cost / 100)
* (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) / 100),
((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) / 100)
)
),
DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code),
b.markup_percentage
INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
l_rate_currency_code /* Added for MCB2 */,
l_markup_percentage /* Added for Asgmt overide */
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = px_project_bill_job_id
AND trunc(NVL(l_labor_schdl_fixed_date, p_item_date)) + 0.99999 /* BUG#3118592 */
BETWEEN b.start_date_active
AND NVL(trunc(b.end_date_active),trunc(NVL(l_labor_schdl_fixed_date, p_item_date))) + 0.99999; /* BUG#3118592 */
select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
into l_mcb_cost_flag
from pa_projects_all
where project_id = p_project_id;
SELECT projfunc_currency_code
INTO l_rev_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT item_quantity
INTO l_item_quantity
FROM pa_forecast_items
WHERE forecast_item_id = p_forecast_item_id;
SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
labor_schedule_fixed_date,labor_sch_type,bill_job_group_id,org_id,
emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
projfunc_currency_code, /* Added the following column for MCB2 */
NVL(assign_precedes_task,'1') /* Added for Asgmt overide */
INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
l_labor_schdl_fixed_date,l_labor_sch_type,l_bill_job_grp_id,l_project_org_id,
l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id,
l_projfunc_currency_code, /* Added the following columns for MCB2 */
l_assignment_precedes_task
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
labor_schedule_fixed_date,labor_sch_type
INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
l_labor_schdl_fixed_date,l_labor_sch_type
FROM pa_tasks
WHERE task_id = p_task_id;
/* There was a call for PA_COST.get_projfunc_raw_burdened , it has been deleted
for Org Forecasting */
ELSIF (l_labor_sch_type = 'B' ) THEN
/* This override is added for Assignment level override functionality ,
it executed if the override precedence takes at assignment level i.e
assignment_precedes_task is 'Y' */
/*------------------------------------------------------------------+
| 1. Assignment level overrides |
+------------------------------------------------------------------+
| Set bill rate and raw revenue using Assignment level |
| overrides . |
+------------------------------------------------------------------*/
/* If the call is from Assignment api then the item_id will be null so this override will
not execute */
/* Changes done for bug 2668753. Whenever MCB is 'Y' the denom_raw_cost and denom_curr_code
are changed to l_mcb_raw_cost and l_mcb_currency_code */
IF (p_item_id IS NOT NULL) THEN
IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL ) THEN
IF ( l_assignment_precedes_task = 'Y') THEN
IF (p_mcb_flag ='Y') THEN
BEGIN
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)),
DECODE(asgn.bill_rate_override, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
* l_mcb_raw_cost / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT ((asgn.bill_rate_override *
NVL(p_bill_rate_multiplier,1) * p_quantity),asgn.bill_rate_curr_override)),
DECODE(asgn.bill_rate_override,NULL,l_mcb_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
'O',
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_amount_calculation_code,
l_discount_percentage
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
),
DECODE(asgn.bill_rate_override, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
* p_raw_cost / 100),l_projfunc_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
* NVL(p_bill_rate_multiplier,1)
* p_quantity),asgn.bill_rate_curr_override)),
DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
'O',
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_amount_calculation_code,
l_discount_percentage
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
CURSOR C_Task IS SELECT o.rate * NVL(p_bill_rate_multiplier,1) b_rate,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((o.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),o.rate_currency_code) r_revenue,
o.rate_currency_code,
'O',
o.discount_percentage
FROM pa_emp_bill_rate_overrides o
WHERE o.person_id+0 = p_person_id
AND o.task_id = p_task_id
AND p_item_date
BETWEEN o.start_date_active
AND NVL(o.end_date_active,p_item_date);
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)),
DECODE(asgn.bill_rate_override, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
* l_mcb_raw_cost / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
* NVL(p_bill_rate_multiplier,1)
* p_quantity),asgn.bill_rate_curr_override)),
DECODE(asgn.bill_rate_override,NULL,l_mcb_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
'O',
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_amount_calculation_code,
l_discount_percentage
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
),
DECODE(asgn.bill_rate_override, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
* p_raw_cost / 100),l_projfunc_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
* NVL(p_bill_rate_multiplier,1)
* p_quantity),asgn.bill_rate_curr_override)),
DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
asgn.markup_percent_override,
'O',
asgn.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_markup_percentage,
l_amount_calculation_code,
l_discount_percentage
FROM pa_project_assignments asgn
WHERE asgn.assignment_id = p_item_id;
SELECT o2.rate * NVL(p_bill_rate_multiplier,1) b_rate,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((o2.rate *
NVL(p_bill_rate_multiplier,1) * p_quantity),o2.rate_currency_code) r_revenue,
o2.rate_currency_code,
'O',
o2.discount_percentage
FROM pa_emp_bill_rate_overrides o2
WHERE o2.person_id = p_person_id
AND o2.project_id = p_project_id
AND l_called_process <>2 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN o2.start_date_active
AND NVL(o2.end_date_active,p_item_date);
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','T'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
WHERE j.task_id = p_task_id
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,p_item_date)
AND j.job_id+0 = a.job_id
AND a.person_id = p_person_id
AND a.task_id = p_task_id
AND p_item_date
BETWEEN a.start_date_active
AND NVL(a.end_date_active,p_item_date);
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','T'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
l_amount_calculation_code,l_discount_percentage
FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
WHERE j.project_id = p_project_id
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,p_item_date)
AND j.job_id+0 = a.job_id
AND a.person_id = p_person_id
AND a.task_id = p_task_id
AND l_called_process =0 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN a.start_date_active
AND NVL(a.end_date_active,p_item_date);
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','P'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
l_amount_calculation_code,l_discount_percentage
FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
WHERE j.task_id = p_task_id
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,
p_item_date)
AND j.job_id+0 = a.job_id
AND a.person_id = p_person_id
AND a.project_id = p_project_id
AND l_called_process =0 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN a.start_date_active
AND NVL(a.end_date_active,p_item_date);
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','P'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
WHERE j.project_id = p_project_id
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,p_item_date)
AND j.job_id+0 = a.job_id
AND a.person_id = p_person_id
AND a.project_id = p_project_id
AND l_called_process <>2 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN a.start_date_active
AND NVL(a.end_date_active,p_item_date) ;
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','J'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM -- per_assignments_f a, /* Bug 6058676 : Removed per_assignments_f and related joins */
pa_job_bill_rate_overrides j
-- Bug 4398492 query made to refer base table per_all_assignments_f
-- (0 * a.person_id) is used to make assignments as the driving table
WHERE j.task_id = p_task_id -- + (0 * a.person_id)
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,p_item_date)
/* AND j.job_id = a.job_id commented for bug 3193077 */
AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Added for bug 3193077 */
--AND a.person_id = p_person_id /* Commented for Bug 6058676*/
--AND a.primary_flag || '' = 'Y'
-- AND a.assignment_type = 'E' /* bug 2911451 */
--AND a.assignment_type IN ('E','C') -- Modified for CWK changes /* Commented for Bug 6058676*/
AND l_called_process <>1 ; /*Added for Doosan rate api change */
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','J'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_job_bill_rate_overrides j
WHERE j.task_id = p_task_id
AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */ /*bug3737994*/
AND p_item_date
BETWEEN j.start_date_active
AND NVL(j.end_date_active,p_item_date)
AND l_called_process <>1; /*Added for Doosan rate api change */
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','J'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_job_bill_rate_overrides j --, per_all_assignments_f a /* Bug 6058676: Removed per_assignments_f and related predicates*/
-- Bug 4398492 query made to refer base table per_all_assignments_f
WHERE j.project_id = p_project_id -- + (0 * a.person_id)
AND p_item_date + 0.99999
BETWEEN j.start_date_active
AND NVL(j.end_date_active + 0.99999,p_item_date + 0.99999)
AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id);
SELECT j.rate * NVL(p_bill_rate_multiplier,1),
PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
j.rate_currency_code,
decode(j.discount_percentage,NULL,'O','J'),
j.discount_percentage
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_job_bill_rate_overrides j--, per_assignments_f a
WHERE j.project_id = p_project_id --+ (0 * a.person_id)
AND p_item_date + 0.99999
BETWEEN j.start_date_active
AND NVL(j.end_date_active + 0.99999,p_item_date + 0.99999)
AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) ;--pa_cross_business_grp.IsmappedTojob(a.job_id,l_bill_job_grp_id) /* Bug 6058676 */
CURSOR C1 IS( SELECT NULL b_rate,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(m.labor_multiplier * decode(p_mcb_flag,'Y',l_mcb_raw_cost,p_raw_cost)),
decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code)) r_revenue,
decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code) curr_code
FROM pa_labor_multipliers m
WHERE m.task_id = p_task_id
AND l_called_process <>1 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN m.start_date_active
AND NVL(m.end_date_active,p_item_date)
UNION ALL
SELECT NULL b_rate,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(m2.labor_multiplier * decode(p_mcb_flag,'Y',l_mcb_raw_cost,p_raw_cost)),
decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code)) r_revenue,
decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code) curr_code
FROM pa_labor_multipliers m2
WHERE m2.project_id = p_project_id
AND l_called_process <>2 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN m2.start_date_active
AND NVL(m2.end_date_active,p_item_date)
AND NOT EXISTS
( SELECT NULL
FROM pa_labor_multipliers m3
WHERE m3.task_id = p_task_id
AND l_called_process <>1 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN m3.start_date_active
AND NVL(m3.end_date_active,p_item_date)
));
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)
),
DECODE(b.rate, NULL,
((100 + b.markup_percentage) * l_mcb_raw_cost / 100),
(b.rate * NVL(p_bill_rate_multiplier,1) *
p_quantity)),
DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
(b.rate * NVL(p_bill_rate_multiplier,1) *
(100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + b.markup_percentage)
* (l_mcb_raw_cost / 100)
* (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100),
l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((b.rate * p_quantity) *
NVL(p_bill_rate_multiplier,1) *
(100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100),
b.rate_currency_code))),
DECODE(b.rate, NULL,l_mcb_currency_code,b.rate_currency_code) /* Added for MCB2-Added for bug 2697945 */,
b.markup_percentage, /* Added for Asgmt overide */
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
l_rate_currency_code /* Added for MCB2 */,
l_markup_percentage, /* Added for Asgmt overide */
l_amount_calculation_code,
l_discount_percentage
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = l_emp_bill_rate_schedule_id
AND b.person_id = p_person_id
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)
),
DECODE(b.rate, NULL,((100 + b.markup_percentage) * p_raw_cost / 100),
(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
(b.rate * NVL(p_bill_rate_multiplier,1) *
(100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + b.markup_percentage)
* (p_raw_cost / 100) * (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100), l_projfunc_currency_Code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((b.rate * p_quantity)* NVL(p_bill_rate_multiplier,1)* (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100), b.rate_currency_code))),
DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code) /* Added for MCB2 */,
b.markup_percentage ,/* Added for Asgmt overide */
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
l_rate_currency_code /* Added for MCB2 */,
l_markup_percentage ,/* Added for Asgmt overide */
l_amount_calculation_code,
l_discount_percentage
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = l_emp_bill_rate_schedule_id
AND b.person_id = p_person_id
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_bill_rate_multiplier,1)),
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) *
(100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
DECODE( nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
PA_CURRENCY.ROUND_CURRENCY_AMT((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100)),
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt),
b.rate_currency_code
INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue, l_amount_calculation_code,
l_discount_percentage,
l_rate_currency_code /*Rate added for bug 2636678 */
FROM pa_bill_rates_all b, pa_job_assignment_overrides ao
WHERE ao.person_id = p_person_id
AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = ao.job_id(+)
AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
AND p_task_id = ao.task_id
AND l_called_process <>1 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN ao.start_date_active
AND NVL(ao.end_date_active,p_item_date)
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_bill_rate_multiplier,1)),
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)*
p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
b.rate_currency_code,
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
l_amount_calculation_code,l_discount_percentage
FROM pa_bill_rates_all b, pa_job_assignment_overrides ao, pa_tasks t
WHERE ao.person_id = p_person_id
AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = ao.job_id(+)
AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
AND t.project_id = ao.project_id
AND t.task_id = p_task_id
AND l_called_process <>2 /*Added for Doosan rate api change */
AND p_item_date
BETWEEN ao.start_date_active
AND NVL(ao.end_date_active,p_item_date)
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)),
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
b.rate_currency_code,
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_bill_rates_all b, pa_job_assignment_overrides ao
WHERE ao.person_id = p_person_id
AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = ao.job_id
AND ao.project_id = p_project_id
AND p_item_date
BETWEEN ao.start_date_active
AND NVL(ao.end_date_active,p_item_date)
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date))
AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99);
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)),
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
b.rate_currency_code,
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_bill_rates_all b -- per_assignments_f pa Commented for Bug 4398492 query made to refer base table
, per_all_assignments_f pa
WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND pa.person_id = p_person_id
AND pa.primary_flag = 'Y'
-- AND pa.assignment_type = 'E'
AND pa.assignment_type IN ('E','C') -- Modified for CWK changes
AND p_item_date /* BUG#3118592 */
BETWEEN pa.effective_start_date
AND pa.effective_end_date
AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Changed the join instead of joining with p_resource_job_id, now joining using function IsmappedTojob to fix bug 2155331 */ /* Bug 6058676 */
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,
NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)),
PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
b.rate_currency_code,
DECODE(l_discount_percentage,NULL,'B','O'),
nvl(l_discount_percentage,l_labor_schdl_discnt)
INTO l_bill_rate,l_raw_revenue,
l_rate_currency_code,
l_amount_calculation_code,
l_discount_percentage
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,
NVL(l_labor_schdl_fixed_date,p_item_date));
SELECT DECODE(b.rate, NULL, NULL,
b.rate * NVL(p_bill_rate_multiplier,1)
),
DECODE(b.rate, NULL,
((100 + b.markup_percentage) *
p_raw_cost / 100),
(b.rate * NVL(p_bill_rate_multiplier,1) *
p_quantity)),
DECODE(l_labor_schdl_discnt, NULL, NULL,
(b.rate * NVL(p_bill_rate_multiplier,1) *
(100 - l_labor_schdl_discnt) /100)),
DECODE(l_labor_schdl_discnt, NULL, NULL,
DECODE(b.rate, NULL,
((100 + b.markup_percentage)
* (p_raw_cost / 100)
* (100 - l_labor_schdl_discnt) / 100),
((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - l_labor_schdl_discnt) / 100)
)
),
DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code)
b.markup_percentage
INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
l_rate_currency_code
l_markup_percentage
FROM pa_bill_rates_all b
WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id)
AND NVL(l_labor_schdl_fixed_date,p_item_date)
BETWEEN b.start_date_active
AND NVL(b.end_date_active,
NVL(l_labor_schdl_fixed_date,p_item_date))
AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99);
select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
into l_mcb_cost_flag
from pa_projects_all
where project_id = p_project_id;
/*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
other audit columns.
- Amount calculation code = 'O' for overrides
- Bill Transaction Currency code is from overrides table.
- Change column from raw_revenue to bill_trans_raw_revnue
(Bill rate and Raw revenue should update only in Bill transaction currency)
- Change the WHERE clause from raw_revenue IS NULL to
bill_trans_raw_revenue IS NULL
- Update denom raw cost if markup applied
- Update denom burden cost if markup applied ***/
/* Changes done for bug 2668753. In the cursor C_Nl_Bill_Rate_Overrides_Mcb, denom_raw_cost,denom_burdened_cost and denom_currency_code
are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
IF ( l_raw_revenue IS NULL) THEN
DECLARE
CURSOR C_Nl_Bill_Rate_Overrides_Mcb IS
SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
DECODE(o.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (DECODE(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity , o.rate_currency_code)) r_revenue,
DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource = p_non_labor_resource
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
UNION
SELECT DECODE(o.bill_rate, NULL,NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
DECODE(o.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o.rate_currency_code)),
DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource is NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource = p_non_labor_resource
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
)
UNION
SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)),
DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
o2.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource = p_non_labor_resource
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
)
UNION
SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)) r_revenue,
DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
o2.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource is NULL
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date))) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.project_id = p_project_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource = p_non_labor_resource
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
);
SELECT DECODE(o.bill_rate, NULL, NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(o.markup_percentage, NULL, NULL, o.markup_percentage) b_markup,
DECODE(o.bill_rate,NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o.rate_currency_code)) r_revenue,
DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource = p_non_labor_resource
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
UNION
SELECT DECODE(o.bill_rate, NULL,NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o.markup_percentage, NULL, NULL, o.markup_percentage) b_markup,
DECODE(o.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o.rate_currency_code)),
DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code ,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource is NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource = p_non_labor_resource
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
)
UNION
SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o2.markup_percentage, NULL, NULL, o2.markup_percentage) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)),
DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code ,
o2.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource = p_non_labor_resource
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
)
UNION
SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o2.markup_percentage, NULL, NULL, o2.markup_percentage) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100), p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)),
DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code,
o2.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource is NULL
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
)
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.project_id = p_project_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource = p_non_labor_resource
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
);
the select statements below
then l_proj_std_bill_rate_sch_id and
l_task_std_bill_rate_sch_id can be passed as
input parameters to these functions*/
/* Commenting out the below select statements as the schedule ids are now passed as input
parameters to the function */
/* SELECT non_lab_std_bill_rt_sch_id
into l_proj_std_bill_rate_sch_id
FROM pa_projects_all
WHERE project_id=p_project_id;
SELECT non_lab_std_bill_rt_sch_id
into l_task_std_bill_rate_sch_id
FROM pa_tasks
WHERE task_id=p_task_id; */
SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b.rate_currency_code)) r_revenue,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100 , b.rate_currency_code)) adjusted_rate,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))) trans_adjusted_revenue,
DECODE(b.rate, NULL, l_mcb_currency_code, b.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
FROM pa_bill_rates_all b
WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b.expenditure_type = p_expenditure_type
AND b.non_labor_resource = p_non_labor_resource
AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
UNION
SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
DECODE(b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
DECODE(b2.rate, NULL, PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage) *
(decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100), l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1) * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
DECODE(b2.rate, NULL, l_mcb_currency_code, b2.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
FROM pa_bill_rates_all b2
WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
AND b2.expenditure_type = p_expenditure_type
AND b2.non_labor_resource = p_non_labor_resource
AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
AND NOT EXISTS
(SELECT b3.rate
FROM pa_bill_rates_all b3
WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b3.bill_rate_organization_id =p_task_bill_rate_org_id commented for bug2690011*/
b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b3.expenditure_type = p_expenditure_type
AND b3.non_labor_resource = p_non_labor_resource
AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b3.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
);
SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b.rate_currency_code)) r_revenue,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code)) adjusted_rate,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(100 + b.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), p_proj_func_currency ),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))) trans_adjusted_revenue,
DECODE(b.rate, NULL, p_proj_func_currency, b.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
FROM pa_bill_rates_all b
WHERE/* b.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b.expenditure_type = p_expenditure_type
AND b.non_labor_resource = p_non_labor_resource
AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
UNION
SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
DECODE(b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),
p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100)
,p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
DECODE(b2.rate, NULL, p_proj_func_currency, b2.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
FROM pa_bill_rates_all b2
WHERE/* b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
AND b2.expenditure_type = p_expenditure_type
AND b2.non_labor_resource = p_non_labor_resource
AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
AND NOT EXISTS
(SELECT b3.rate
FROM pa_bill_rates_all b3
WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b3.bill_rate_organization_id =p_task_bill_rate_org_id commented for bug2690011*/
b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b3.expenditure_type = p_expenditure_type
AND b3.non_labor_resource = p_non_labor_resource
AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b3.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
);
/*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
other audit columns.
- Amount calculation code = 'O' for Overrides
- Bill Transaction Currency code is from overrides table.
- Change column from raw_revenue to bill_trans_raw_revnue
(Bill rate and Raw revenue should update only in Bill transaction currency)
- Change the WHERE clause from raw_revenue IS NULL to
bill_trans_raw_revenue IS NULL
- Update denom raw cost if markup applied
- Update denom burden cost if markup applied ***/
/* Changes done for bug 2668753. In the cursor C_Exp_Type_Overrides_Ncb , denom_raw_cost,denom_burdened_cost and denom_currency_code are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
IF ( l_raw_revenue IS NULL and l_rate_discount_pct is null) THEN
DECLARE
CURSOR C_Exp_Type_Overrides_Ncb IS
SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
DECODE(o.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o.rate_currency_code)) r_revenue,
DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource IS NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
UNION
SELECT DECODE(o2.bill_rate, NULL, NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)),
DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
o2.discount_percentage
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource IS NULL
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource IS NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
);
SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
DECODE(o.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o.rate_currency_code)) r_revenue,
DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code,
o.discount_percentage discount_pct
FROM pa_nl_bill_rate_overrides o
WHERE o.task_id = p_task_id
AND o.expenditure_type = p_expenditure_type
AND o.non_labor_resource IS NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
UNION
SELECT DECODE(o2.bill_rate, NULL, NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
DECODE(o2.bill_rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, o2.rate_currency_code)),
DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code,
o2.discount_percentage
FROM pa_nl_bill_rate_overrides o2
WHERE o2.project_id = p_project_id
AND o2.expenditure_type = p_expenditure_type
AND o2.non_labor_resource IS NULL
AND l_called_process <> 2 /*Added for Doosan rate api change */
AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
AND NOT EXISTS
(SELECT o3.bill_rate
FROM pa_nl_bill_rate_overrides o3
WHERE o3.task_id = p_task_id
AND o3.expenditure_type = p_expenditure_type
AND o3.non_labor_resource IS NULL
AND l_called_process <> 1 /*Added for Doosan rate api change */
AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(p_expenditure_item_date) /* BUG#3118592 */
BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
);
/*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
other audit columns.
- Amount calculation code = 'B' for Bill Rates.
- Bill Transaction Currency code is from overrides table.
- Change column from raw_revenue to bill_trans_raw_revnue
(Bill rate and Raw revenue should update only in Bill transaction currency)
- Change the WHERE clause from raw_revenue IS NULL to
bill_trans_raw_revenue IS NULL
- Update denom raw cost if markup applied
- Update denom burden cost if markup applied ***/
l_true :=false ;
SELECT DECODE (b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE (b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
DECODE (b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
l_mcb_currency_code) ,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b.rate_currency_code)) r_revenue,
DECODE (NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code) ) adjusted_rate ,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(100 + b.markup_percentage) * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))
) trans_adjusted_amount,
DECODE(b.rate, NULL, l_mcb_currency_code, b.rate_currency_code) rate_currency_code ,
NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
FROM pa_bill_rates_all b
WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b.expenditure_type = p_expenditure_type
AND b.non_labor_resource IS NULL
AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
)
UNION
SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE (b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
* (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b2.rate_currency_code)) r_revenue,
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)) adjusted_rate,
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(100 + b2.markup_percentage) * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100),l_mcb_currency_code),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1) * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100,
b2.rate_currency_code))) trans_adjusted_amount,
DECODE(b2.rate, NULL, l_mcb_currency_code, b2.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
FROM pa_bill_rates_all b2
WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
AND b2.expenditure_type = p_expenditure_type
AND b2.non_labor_resource IS NULL
AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b2.end_date_active), trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
AND NOT EXISTS
(SELECT b3.rate
FROM pa_bill_rates_all b3
WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b3.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b3.expenditure_type = p_expenditure_type
AND b3.non_labor_resource IS NULL
AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b3.end_date_active), /* BUG#3118592 */
trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
);
SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
DECODE (b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b.rate_currency_code)) r_revenue,
DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code)) adjusted_rate,
DECODE (NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,
DECODE(b.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(100 + b.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100,
b.rate_currency_code))) trans_adjusted_amount,
DECODE(b.rate, NULL, p_proj_func_currency, b.rate_currency_code) rate_currency_code ,
NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
FROM pa_bill_rates_all b
WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
AND b.expenditure_type = p_expenditure_type
AND b.non_labor_resource IS NULL
AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
UNION
SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
DECODE (b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
* (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* p_quantity, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,
DECODE(b2.rate, NULL,
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
(100 + b2.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
* ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100), p_proj_func_currency),
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
* NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
DECODE(b2.rate, NULL, p_proj_func_currency, b2.rate_currency_code) rate_currency_code,
NVL(l_rate_discount_pct,p_project_sch_discount)
FROM pa_bill_rates_all b2
WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id /*added for bug2690011*/
AND b2.expenditure_type = p_expenditure_type
AND b2.non_labor_resource IS NULL
AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
AND NOT EXISTS
(SELECT b3.rate
FROM pa_bill_rates_all b3
WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
AND b3.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id /*added for bug2690011*/
AND b3.expenditure_type = p_expenditure_type
AND b3.non_labor_resource IS NULL
AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
AND NVL(trunc(b3.end_date_active), trunc(NVL(p_task_sch_date, /* BUG#3118592 */
p_expenditure_item_date)))
);