Draft Invoices / Revenues in project model

Draft Revenue

-- draft revenue against project headers

 

SELECT ppa.segment1

     , ppa.name

     , pdra.draft_revenue_num rev_num

     , pdra.creation_date

     , pdra.transfer_status_code

     , pdra.transferred_date

     , pdra.transfer_rejection_reason

     , pdra.*

  FROM pa.pa_draft_revenues_all pdra

     , pa.pa_projects_all ppa

 WHERE pdra.project_id = ppa.project_id

      AND pdra.creation_date >= '26-FEB-2013';

 

-- draft revenue against tasks

 

SELECT ppa.segment1

     , ppa.name

     , pdri.creation_date

     , pdri.amount

     , pdri.draft_revenue_num rev_num

     , pt.task_number

     , pt.task_name

  FROM pa.pa_draft_revenue_items pdri

     , pa.pa_projects_all ppa

     , pa.pa_tasks pt

 WHERE pdri.project_id = ppa.project_id

   AND pt.project_id = ppa.project_id

   AND pdri.task_id = pt.task_id

   AND pdri.creation_date >= '31-OCT-2012';

 

Draft Invoices

 

-- invoices

 

  SELECT ppa.segment1

       , ppa.name

       , ppa.project_id

       , ppa.distribution_rule

       , pdia.draft_invoice_num inv_num

       , fu.user_name created_by

       , pdia.creation_date

       , pdia.approved_date

       , pdia.released_date

       , pdia.unearned_revenue_cr amt

       , pdia.generation_error_flag err_flag

       , pdia.request_id

       , pdia.draft_invoice_num invno

       , pdia.ra_invoice_number ar_inv

    FROM pa.pa_draft_invoices_all pdia

       , applsys.fnd_user fu

       , pa.pa_projects_all ppa

   WHERE ppa.created_by = fu.user_id

     AND pdia.project_id = ppa.project_id

     AND pdia.creation_date >= '06-JAN-2005'

ORDER BY pdia.creation_date DESC;

 

-- invoices and invoice items

 

SELECT pdia.draft_invoice_num

     , pdia.creation_date

     , pdia.transfer_status_code

     , pdia.generation_error_flag

     , pdia.pa_date

     , pdia.invoice_date

     , pdia.ra_invoice_number

     , pdia.transferred_date

     , pdia.transfer_rejection_reason

     , pdia.gl_date

     , pdii.event_num

     , pdii.inv_amount

     , pdii.taxable_flag

  FROM pa.pa_draft_invoices_all pdia

     , pa.pa_draft_invoice_items pdii

 WHERE pdia.draft_invoice_num = pdii.draft_invoice_num

   AND pdia.project_id = pdii.project_id

   AND pdia.creation_date >= '06-JAN-2005';