Contractor Billing Report

Requirement

----------------
Display the listed column in Report.






Solution
-----------------

I developed below query for that Report requirement.
May be some changes required for final report.


SELECT pdii.line_num,pbre.full_name contractor_name
      ,pbre.employee_number ID
      ,ppa.name project_name
      ,ppa.segment1 project_num
      ,ppa.project_type
      ,ppa.start_date project_from_date
      ,ppa.completion_date project_to_date
      ,ppa.long_name bill_to_customer_number
      ,pbre.start_date_active from_bill_date
      ,pbre.end_date_active to_bill_date
      ,pbre.rate
      ,pbre.rate_currency_code bill_rate_card_currency
      ,decode(pbre.rate,0,0,(pdii.amount/pbre.rate)) Hours
      ,pdia.inv_currency_code invoice_currency_code
      ,pdii.amount invoice_amount
      ,sum(revedist.amount) revenue_amount
      ,pdia.ra_invoice_number ar_invoice_number
  FROM pa_projects_all ppa
      ,pa_bill_rates_emp pbre
      ,pa_draft_invoices_all pdia
      ,pa_draft_invoice_items pdii
      ,pa_cust_rev_dist_lines revedist
      ,pa_draft_revenues reve
      ,pa_draft_revenue_items pdri
      --,pa_draft_revenues_all pdra
 WHERE ppa.segment1= pbre.std_bill_rate_schedule
  -- AND ppa.segment1='2012131000138'
   AND pbre.full_name=nvl(:P_CONTRACTOR_NAME,pbre.full_name)
   AND pdia.project_id=ppa.project_id
   AND pdii.project_id=ppa.project_id
   AND pdii.draft_invoice_num=pdia.draft_invoice_num
   AND pdia.gl_date between :P_FROM_GL_DATE and :P_TO_GL_DATE
   AND reve.gl_date between :P_FROM_GL_DATE and :P_TO_GL_DATE
   AND pdii.draft_invoice_num = revedist.draft_invoice_num
     AND pdii.line_num = revedist.draft_invoice_item_line_num
     AND revedist.draft_revenue_num = reve.draft_revenue_num
     AND ppa.project_id = reve.project_id
     AND ppa.project_id = revedist.project_id
     AND pdri.project_id=ppa.project_id
     AND pdri.draft_revenue_num=reve.draft_revenue_num
     AND pbre.employee_number >10000
     group by pdii.line_num,pbre.full_name
      ,pbre.employee_number
      ,ppa.name
      ,ppa.segment1
      ,ppa.project_type
      ,ppa.start_date
      ,ppa.completion_date
      ,ppa.long_name
      ,pbre.start_date_active
      ,pbre.end_date_active
      ,pbre.rate
      ,pbre.rate_currency_code
      ,decode(pbre.rate,0,0,(pdii.amount/pbre.rate))
      ,pdia.inv_currency_code
      ,pdii.amount
      --,pdri.amount
      ,pdia.ra_invoice_number

union all

SELECT pdii.line_num,pbre.full_name contractor_name
      ,pbre.employee_number ID
      ,ppa.name project_name
      ,ppa.segment1 project_num
      ,ppa.project_type
      ,ppa.start_date project_from_date
      ,ppa.completion_date project_to_date
      ,ppa.long_name bill_to_customer_number
      ,pbre.start_date_active from_bill_date
      ,pbre.end_date_active to_bill_date
      ,pbre.rate
      ,pbre.rate_currency_code bill_rate_card_currency
      ,decode(pbre.rate,0,0,(pdii.amount/pbre.rate)) Hours
      ,pdia.inv_currency_code invoice_currency_code
      ,pdii.amount invoice_amount
      ,pcerda.project_revenue_amount
      ,pdia.ra_invoice_number ar_invoice_number
  FROM pa_projects_all ppa
      ,pa_bill_rates_emp pbre
      ,pa_draft_invoices_all pdia
      ,pa_draft_invoice_items pdii
     ,pa_cust_event_rdl_all pcerda
     ,pa_draft_revenues_all pdra
 WHERE ppa.segment1= pbre.std_bill_rate_schedule
  -- AND ppa.segment1='2012131000138'
   AND pbre.full_name=nvl(:P_CONTRACTOR_NAME,pbre.full_name)
   AND pdia.project_id=ppa.project_id
   AND pdii.project_id=ppa.project_id
   AND pdii.draft_invoice_num=pdia.draft_invoice_num
   AND pdia.gl_date between :P_FROM_GL_DATE and :P_TO_GL_DATE
   AND pdra.gl_date between :P_FROM_GL_DATE and :P_TO_GL_DATE
   AND   pcerda.project_id=ppa.project_id
   AND   pcerda.event_num=pdii.event_num
   AND pbre.employee_number >10000
   AND pcerda.project_id=pdra.project_id
   AND pcerda.draft_revenue_num=pdra.draft_revenue_num;

 The XML Layout will look like this.


Contractor Billing details Report

Operating Unit	:	CF_UNIT
For Contractor	:	CF_Cont
From GL Date	:	From GL Date
To GL Date	:	To GL Date

Bill Rate Card

Billed Amount

Contractor name

ID

Project Name

Project Number

Project Type

Project From

Project To

Bill to Customer Number

From date

To date

Rate

Task Name

Billable Flag

Bill Rate Card Currency

No Of Hours Billed

Invoice currency

Invoice Amount

Revenue Amount

AR Invoice Number

For Contractor

ID

Project

Project No

Project type

Project From

Project To

Bill To

From Bill date

To Bill Date

0.00

Task

Bill flag

bill curr

0.00

Inv Curr

0.00

0.00

Ar Inv No END