Protected by Copyscape Web Copyright Protection Software

Pages

Search This Blog

Friday, June 28, 2013

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

No comments:

Post a Comment