Monday 4 March 2019

Function To Get Bill Rate Of Employee in Specific Project in Oracle Fusion

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;

1 comment: