Employee Bill Rate Card Details Report Sample

Sample Query

--------------------

SELECT ppa.name project_name
      ,ppa.segment1 project_num
      ,hca.account_number bill_to_cust_num
      ,ppf.full_name employee_name
      ,DECODE(Ppf.Current_NPW_Flag, 'Y', Ppf.NPW_Number, Ppf.EMPLOYEE_NUMBER) employee_number
      ,pbr.start_date_active from_bill_date
      ,pbr.end_date_active to_bill_date
      ,decode(pbr.end_date_active,(select start_date from gl_periods where period_name=:P_FROM_PERIOD),0,(
      decode(pbr.start_date_active,
       (select start_date from FA_CALENDAR_PERIODS where period_name=:P_FROM_PERIOD),1,0))) begining_of_month
      ,decode(pbr.end_date_active,(select end_date from FA_CALENDAR_PERIODS where period_name=:P_TO_PERIOD),1,0) end_of_month
  from pa_projects_all ppa
       ,per_people_f ppf
       ,hz_cust_accounts hca
       ,pa_project_customers ppc
       ,PA_BILL_RATES_ALL pbr
  where ppf.effective_start_date=(select MAX (PP.EFFECTIVE_START_DATE)
    FROM PER_ALL_PEOPLE_F PP
    WHERE PP.PERSON_ID        = ppf.PERSON_ID
    AND (Current_Employee_Flag='Y'
    OR Current_NPW_Flag       ='Y'))
    AND ppc.project_id=ppa.project_id
    AND hca.cust_account_id=ppc.customer_id
    and    pbr.std_bill_rate_schedule=ppa.segment1
   AND ppf.person_id=pbr.person_id
   AND ppa.org_id=:P_ORG_ID
   AND ppa.segment1=nvl(:P_PROJECT_NUM,ppa.segment1)

Sample XML Layout for the Report look like this....

 Employee Bill Rate Card Details Report

Operating unit	:	CF_UNIT
From Period	:
To Period	:	To Period
For project	:	Project

Project Name	Project Number	Bill to Customer Number	Employee name	Employee Number	Beginning Of the Month	End of the Month
					Open Period	Close Period
For Project	Project No	Bill To	Emp	Emp No	Beg	En END