Project Query

Reuirement

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

Display all Below columns in report.





Solution
----------------
SELECT pdil.project_id
      ,ppa.name project_name
      ,ppa.segment1 project_num
     -- ,ppa.project_type
    --  ,ppa.start_date project_from
    --  ,ppa.completion_date project_to
      ,hca.account_number bill_to_cust_num
      ,pdil.employee_name employee_name
      ,pdil.employee_number
      --,person_id
      ,pbre.start_date_active from_bill_date
      ,pbre.end_date_active to_bill_date
      ,pdil.task_name
      ,pdil.billable_flag
    --  ,pbre.rate_currency_code bill_rate_card_currency
      ,sum(pdil.quantity) hours
       ,pbre.rate
    --  ,pdia.inv_currency_code invoice_currency_code
      --,pdii.amount invoice_amount
      --,pdia.ra_invoice_number ar_invoice_num
      ,sum(revedist.amount) total_revenue_amount
      ,sum(pdil.bill_amount)  total_billed_amount


  FROM PA_DRAFT_INV_LINE_DETAILS_V pdil
       ,PA_PROJECTS_ALL ppa
       ,PA_PROJECT_CUSTOMERS ppc
       ,HZ_CUST_ACCOUNTS_ALL hca
       ,PA_BILL_RATES_EMP pbre
       ,pa_draft_invoice_items pdii
       ,pa_draft_invoices_all pdia
       ,pa_cust_rev_dist_lines revedist
       ,pa_draft_revenues_all pdra
       ,hr_operating_units hou
  WHERE --ppa.segment1='2012131000128'
    --AND pdil.draft_invoice_num=1
    --AND
    ppa.project_id=pdil.project_id
    AND ppc.project_id=ppa.project_id
    AND hca.cust_account_id= ppc.customer_id
    AND pbre.std_bill_rate_schedule=ppa.segment1
    AND pbre.person_id=pdil.incurred_by_person_id
    AND pdii.project_id=ppa.project_id
    AND pdii.draft_invoice_num=pdil.draft_invoice_num
    AND pdii.line_num=pdil.draft_invoice_item_line_num
    AND pdia.project_id=ppa.project_id
    AND pdia.draft_invoice_num=pdii.draft_invoice_num
    AND ppa.project_id = revedist.project_id
    AND pdii.draft_invoice_num = revedist.draft_invoice_num
    AND pdia.draft_invoice_num=revedist.draft_invoice_num
    AND pdil.draft_invoice_item_line_num=revedist.draft_invoice_item_line_num
    AND pdil.expenditure_item_id=revedist.expenditure_item_id
    AND pdra.project_id=ppa.project_id
    AND pdra.draft_revenue_num=revedist.draft_revenue_num
    AND hou.organization_id=ppa.org_id
    AND hou.organization_id=:P_ORG_ID
    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 pbre.full_name=nvl(:P_CONTRACTOR_NAME,pbre.full_name)
 --  AND pdia.gl_period_name='JUN-12'
GROUP BY pdil.project_id
      ,ppa.name
      ,ppa.segment1
     -- ,ppa.project_type
    --  ,ppa.start_date project_from
    --  ,ppa.completion_date project_to
      ,hca.account_number
      ,pdil.employee_name
      ,pdil.employee_number
      --,person_id
      ,pbre.start_date_active
      ,pbre.end_date_active
      ,pdil.task_name
      ,pdil.billable_flag
    --  ,pbre.rate_currency_code bill_rate_card_currency
      ,pbre.rate
    --  ,pdia.inv_currency_code invoice_currency_code
      ,pdii.amount
      ,pdia.ra_invoice_number;