FUNCTION get_bill_rate (
p_project_id IN NUMBER,
p_person_id IN NUMBER
)
RETURN NUMBER
AS
ln_bill_rate NUMBER;
BEGIN
SELECT DISTINCT bro.rate
INTO ln_bill_rate
FROM pjb_bill_plans_b bpb,
pjb_bill_rate_ovrrds bro,
per_all_people_f_v ppn,
(SELECT DISTINCT pcb.contract_id, pcb.project_id, ppb.segment1 AS project_number
FROM pjb_cntrct_proj_links pcb, pjf_projects_all_b ppb
WHERE pcb.project_id = ppb.project_id) proj
WHERE bpb.bill_plan_id = bro.bill_plan_id
AND bro.person_id = ppn.person_id
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
AND TRUNC (SYSDATE) BETWEEN bro.start_date_active AND bro.end_date_active
AND bpb.contract_id = proj.contract_id
AND proj.project_id = p_project_id
AND ppn.person_id = p_person_id
AND bpb.version_type = 'C'
AND bro.version_type = 'C';
RETURN ln_bill_rate;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
p_project_id IN NUMBER,
p_person_id IN NUMBER
)
RETURN NUMBER
AS
ln_bill_rate NUMBER;
BEGIN
SELECT DISTINCT bro.rate
INTO ln_bill_rate
FROM pjb_bill_plans_b bpb,
pjb_bill_rate_ovrrds bro,
per_all_people_f_v ppn,
(SELECT DISTINCT pcb.contract_id, pcb.project_id, ppb.segment1 AS project_number
FROM pjb_cntrct_proj_links pcb, pjf_projects_all_b ppb
WHERE pcb.project_id = ppb.project_id) proj
WHERE bpb.bill_plan_id = bro.bill_plan_id
AND bro.person_id = ppn.person_id
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND ppn.effective_end_date
AND TRUNC (SYSDATE) BETWEEN bro.start_date_active AND bro.end_date_active
AND bpb.contract_id = proj.contract_id
AND proj.project_id = p_project_id
AND ppn.person_id = p_person_id
AND bpb.version_type = 'C'
AND bro.version_type = 'C';
RETURN ln_bill_rate;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
Good Blog, Thanks For Sharing This Informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad