Sample query of Project based Invoices

This is sample query for project invoices as well as normal AR invoices.

Select revedist.draft_invoice_item_line_num
       ,hou.name OPERATING_UNIT
       , hp.party_name CUSTOMER_NAME
      , hca.account_number CUSTOMER_NUMBER
      , trx.trx_number INV_NO
      , trx.trx_date INV_DATE
      , draftinv.gl_date GL_DATE
      , trx.ct_reference REF_NO
      , trx.doc_sequence_value VOUCHER_NO
      , trx.invoice_currency_code CURRENCY_CODE
      , nvl(trx.exchange_rate,1) exchange_rate
       ,dinvitms.amount Enterd_AMT
       ,dinvitms.amount ACCT_AMT
       ,null Tax
       ,dinvitms.amount TOTAL
       ,dinvitms.amount Receivable_Amt
       , trx.interface_header_context CONTEXT
      ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute1,
                NULL
               )         project_num
      ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute2,
                NULL
               )  draft_inv_num
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute3,
                NULL
               )  agreement_num
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute4,
                NULL
               ) project_org
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute5,
                NULL
               ) project_manager
      , null  CLASS
      ,dinvitms.amount line_amoun
      ,gcc.concatenated_segments Revenue_Account
      ,trx.customer_trx_id
  From ra_customer_trx_all trx,
            pa_draft_invoices draftinv,
            pa_draft_invoice_items dinvitms,
            pa_cust_rev_dist_lines revedist,
            pa_draft_revenues reve,
            pa_projects proj
            ,gl_code_combinations_kfv gcc
      ,hz_cust_accounts_all hca
      ,hz_parties hp
      ,hr_operating_units hou
      ,ra_cust_trx_types_all rctt
Where draftinv.system_reference = trx.customer_trx_id
     AND draftinv.draft_invoice_num = dinvitms.draft_invoice_num
     AND dinvitms.draft_invoice_num = revedist.draft_invoice_num
     AND dinvitms.line_num = revedist.draft_invoice_item_line_num
     AND revedist.draft_revenue_num = reve.draft_revenue_num
     AND proj.project_id = reve.project_id
     AND proj.project_id = revedist.project_id
     AND proj.project_id = dinvitms.project_id
     AND proj.project_id = draftinv.project_id
     AND draftinv.project_id = dinvitms.project_id
     AND reve.project_id = draftinv.project_id
       AND proj.segment1= trx.interface_header_attribute1
    -- AND trx.trx_number='2012131000118'
     AND gcc.code_combination_id=revedist.code_combination_id
     AND trx.bill_to_customer_id=hca.cust_account_id
     AND hp.party_id=hca.party_id
     AND hou.organization_id=trx.org_id
     AND trx.cust_trx_type_id=rctt.cust_trx_type_id
     AND trx.org_id=nvl(:P_ORG_ID,trx.org_id)
  AND hca.account_number=nvl(:P_CUST_NAME,hca.account_number)
  AND RCTT.NAME=NVL(:P_TRX_TYPE,RCTT.NAME)
  AND   draftinv.gl_date between :P_FROM_G_DATE and :P_TO_G_DATE
  AND   gcc.concatenated_segments between nvl(:P_FROM_GL_ACCOUNT,to_char(gcc.concatenated_segments)) and nvl(:P_TO_GL_ACCOUNT,to_char(gcc.concatenated_segments))

    
  GROUP BY     revedist.draft_invoice_item_line_num
               ,hou.name
               , hp.party_name
      , hca.account_number
      , trx.trx_number
      , trx.trx_date
      , draftinv.gl_date
      , trx.ct_reference
      , trx.doc_sequence_value
      , trx.invoice_currency_code
      , nvl(trx.exchange_rate,1)
      ,dinvitms.amount
      ,dinvitms.amount
      ,null
      ,dinvitms.amount
      ,dinvitms.amount
             , trx.interface_header_context
      ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute1,
                NULL
               )
      ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute2,
                NULL
               )
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute3,
                NULL
               )
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute4,
                NULL
               )
       ,DECODE (trx.interface_header_context,
                'PROJECTS INVOICES', trx.interface_header_attribute5,
                NULL
               )
      , null
      ,dinvitms.amount
      ,gcc.concatenated_segments
      ,trx.customer_trx_id
     
     
union all

select null draft_invoice_item_line_num
       ,hou.name OPERATING_UNIT
       , hp.party_name CUSTOMER_NAME
      , hca.account_number CUSTOMER_NUMBER
      , rct.trx_number INV_NO
      , rct.trx_date INV_DATE
      , pdia.gl_date GL_DATE
      , rct.ct_reference REF_NO
      , rct.doc_sequence_value VOUCHER_NO
      , rct.invoice_currency_code CURRENCY_CODE
      , nvl(rct.exchange_rate,1) exchange_rate
       ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pdii.project_id
          and pdii1.draft_invoice_num=pdii.draft_invoice_num) Enterd_AMT
      ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pdii.project_id
          and pdii1.draft_invoice_num=pdii.draft_invoice_num) ACCT_AMT
      ,null Tax
      ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pdii.project_id
          and pdii1.draft_invoice_num=pdii.draft_invoice_num) TOTAL
      ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pdii.project_id
          and pdii1.draft_invoice_num=pdii.draft_invoice_num) Receivable_Amt
      , rct.interface_header_context CONTEXT
      ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute1,
                NULL
               )         project_num
      ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute2,
                NULL
               )  draft_inv_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute3,
                NULL
               )  agreement_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute4,
                NULL
               ) project_org
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute5,
                NULL
               ) project_manager
      , null  CLASS
      ,pdii.amount line_amoun
      ,gcc.concatenated_segments Revenue_Account
      ,rct.customer_trx_id
from   ra_customer_trx_all rct
      ,pa_projects_all ppa
      ,pa_draft_invoices_all pdia
      ,pa_draft_invoice_items pdii
      ,pa_cust_event_rdl_all pcerda
      ,gl_code_combinations_kfv gcc
      ,hz_cust_accounts_all hca
      ,hz_parties hp
      ,hr_operating_units hou
      ,ra_cust_trx_types_all rctt
where
       --rct.trx_number in ('2012131000085','2012131000026','2012131000088','2012131000118')
--AND   ppa.segment1='2012131000021'
--AND 
ppa.segment1= rct.interface_header_attribute1
AND   pdia.system_reference=rct.customer_trx_id
AND   pdii.project_id=ppa.project_id
AND   pdii.draft_invoice_num=pdia.draft_invoice_num
AND   pcerda.project_id=ppa.project_id
AND   pcerda.event_num=pdii.event_num
AND   pcerda.code_combination_id=gcc.code_combination_id
AND   rct.bill_to_customer_id=hca.cust_account_id
AND   hp.party_id=hca.party_id
AND   hou.organization_id=rct.org_id
AND   rctt.cust_trx_type_id=rct.cust_trx_type_id
AND rct.org_id=nvl(:P_ORG_ID,rct.org_id)
  AND hca.account_number=nvl(:P_CUST_NAME,hca.account_number)
  AND RCTT.NAME=NVL(:P_TRX_TYPE,RCTT.NAME)
  AND   pdia.gl_date between :P_FROM_G_DATE and :P_TO_G_DATE
  AND   gcc.concatenated_segments between nvl(:P_FROM_GL_ACCOUNT,to_char(gcc.concatenated_segments)) and nvl(:P_TO_GL_ACCOUNT,to_char(gcc.concatenated_segments))

union all
     
      SELECT null draft_invoice_item_line_num
       ,hou.NAME OPERATING_UNIT
      , hp.party_name CUSTOMER_NAME
      , hca.account_number CUSTOMER_NUMBER
      , rct.trx_number INV_NO
      , rct.trx_date INV_DATE
      , rctd.gl_date GL_DATE
      , rct.ct_reference REF_NO
      , rct.doc_sequence_value VOUCHER_NO
      , rct.invoice_currency_code CURRENCY_CODE
      , nvl(rct.exchange_rate,1) exchange_rate
      , (select sum(rctd1.Amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id) Enterd_AMT
      ,(select sum(rctd1.Acctd_amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id) ACCT_AMT
      ,(select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id) Tax--, rctd.Acctd_amount ACCT_AMT
    --  , zl.tax_amt "TAXES"
    --  , NVL (zl.tax_amt, 0)
     -- , NVL (rct.exchange_rate, 1) *  rctd.Amount TOTAL
       ,((select sum(rctd1.Acctd_amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id)
      +nvl((select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id),0)) TOTAL
       /*((nvl(rct.exchange_rate,1)*rctd.Amount)
      +
      nvl((select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id),0)) TOTAL*/
      ,(select sum(rctd1.Amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REC' and rctd1.customer_trx_id=rct.customer_trx_id) Receivable_Amt
      , rct.interface_header_context CONTEXT
      ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute1,
                NULL
               )         project_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute2,
                NULL
               )  draft_inv_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute3,
                NULL
               )  agreement_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute4,
                NULL
               ) project_org
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute5,
                NULL
               ) project_manager
      ,  rctd.account_class  CLASS
      -- , rctl.line_number  Line_Number
      -- ,decode(rctd.account_class,'REV',rctd.amount,null) line_amount
       ,nvl(rct.exchange_rate,1)*decode(rctd.account_class,'REV',rctd.amount,null) line_amoun
       --, NVL (rct.exchange_rate, 1) * rctd.Amount line_amount
       , gcc.concatenated_segments Revenue_Account
       , rctd.customer_trx_id
FROM   ra_customer_trx_all            rct
      --,ra_customer_trx_lines_all      rctl
      ,ra_cust_trx_line_gl_dist_all   rctd
      ,hz_cust_accounts_all           hca
      ,hz_parties                     hp
      --,zx_lines                       zl
      ,hr_operating_units             hou
      ,ra_cust_trx_types_all          rctt
      ,gl_code_combinations_kfv       gcc
--      ,AR_AEL_SL_INV_V ARSL
WHERE --rct.customer_trx_id  in ( 757008,6250,6251)
--AND   
rct.customer_trx_id   = rctd.customer_trx_id
--AND    rct.customer_trx_id   = rctd.customer_trx_id
--AND    rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND    rct.bill_to_customer_id  = hca.cust_account_id
AND    hca.party_id         =  hp.party_id
--AND    rctl.tax_line_id     = zl.tax_line_id(+)
AND    rct.org_id         = hou.organization_id
AND    rct.cust_trx_type_id = rctt.cust_trx_type_id
AND    rct.org_id         = rctt.org_id
AND    rctd.code_combination_id = gcc.code_combination_id
AND    rctd.account_class = 'REV'
--AND    arsl.trx_hdr_id=rct.customer_trx_id
AND       decode(rctd.posting_control_id, -3, 'N', 'Y')='Y'
--AND    arsl.GL_TRANSFER_STATUS='Y'
--AND    arsl.code_combination_id=rctd.code_combination_id
--AND    rct.trx_number=nvl(:P_TRX_NUM,rct.trx_number)
AND    rct.org_id=nvl(:P_ORG_ID,rct.org_id)
--AND    HP.PARTY_NAME=NVL(:P_CUST_NAME,HP.PARTY_NAME)
AND hca.account_number=nvl(:P_CUST_NAME,hca.account_number)
AND    RCTT.NAME=NVL(:P_TRX_TYPE,RCTT.NAME)
AND   rct.interface_header_context !='PROJECTS INVOICES'
  --and rct.trx_number='2012131000085'--and rct.trx_number='2012131000328'
--AND    hou.name=nvl(:P_OP_NAME,hou.name)
AND   rctd.gl_date between :P_FROM_G_DATE and :P_TO_G_DATE
AND   gcc.concatenated_segments between nvl(:P_FROM_GL_ACCOUNT,to_char(gcc.concatenated_segments)) and nvl(:P_TO_GL_ACCOUNT,to_char(gcc.concatenated_segments))

union all
     
      SELECT null draft_invoice_item_line_num
       ,hou.NAME OPERATING_UNIT
      , hp.party_name CUSTOMER_NAME
      , hca.account_number CUSTOMER_NUMBER
      , rct.trx_number INV_NO
      , rct.trx_date INV_DATE
      , rctd.gl_date GL_DATE
      , rct.ct_reference REF_NO
      , rct.doc_sequence_value VOUCHER_NO
      , rct.invoice_currency_code CURRENCY_CODE
      , nvl(rct.exchange_rate,1) exchange_rate
      , (select sum(rctd1.Amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id) Enterd_AMT
      ,(select sum(rctd1.Acctd_amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id) ACCT_AMT
      ,(select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id) Tax--, rctd.Acctd_amount ACCT_AMT
    --  , zl.tax_amt "TAXES"
    --  , NVL (zl.tax_amt, 0)
     -- , NVL (rct.exchange_rate, 1) *  rctd.Amount TOTAL
       ,((select sum(rctd1.Acctd_amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REV' and rctd1.customer_trx_id=rct.customer_trx_id)
      +nvl((select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id),0)) TOTAL
       /*((nvl(rct.exchange_rate,1)*rctd.Amount)
      +
      nvl((select sum(z.amount) from ra_cust_trx_line_gl_dist_all z where z.account_class='TAX' and z.customer_trx_id=rct.customer_trx_id),0)) TOTAL*/
      ,(select sum(rctd1.Amount) from ra_cust_trx_line_gl_dist_all rctd1 where rctd1.account_class='REC' and rctd1.customer_trx_id=rct.customer_trx_id) Receivable_Amt
      , rct.interface_header_context CONTEXT
      ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute1,
                NULL
               )         project_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute2,
                NULL
               )  draft_inv_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute3,
                NULL
               )  agreement_num
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute4,
                NULL
               ) project_org
       ,DECODE (rct.interface_header_context,
                'PROJECTS INVOICES', rct.interface_header_attribute5,
                NULL
               ) project_manager
      ,  rctd.account_class  CLASS
      -- , rctl.line_number  Line_Number
      -- ,decode(rctd.account_class,'REV',rctd.amount,null) line_amount
       ,nvl(rct.exchange_rate,1)*decode(rctd.account_class,'REV',rctd.amount,null) line_amoun
       --, NVL (rct.exchange_rate, 1) * rctd.Amount line_amount
       , gcc.concatenated_segments Revenue_Account
       , rctd.customer_trx_id
FROM   ra_customer_trx_all            rct
      --,ra_customer_trx_lines_all      rctl
      ,ra_cust_trx_line_gl_dist_all   rctd
      ,hz_cust_accounts_all           hca
      ,hz_parties                     hp
      --,zx_lines                       zl
      ,hr_operating_units             hou
      ,ra_cust_trx_types_all          rctt
      ,gl_code_combinations_kfv       gcc
--      ,AR_AEL_SL_INV_V ARSL
WHERE --rct.customer_trx_id  in ( 757008,6250,6251)
--AND   
rct.customer_trx_id   = rctd.customer_trx_id
--AND    rct.customer_trx_id   = rctd.customer_trx_id
--AND    rctl.customer_trx_line_id = rctd.customer_trx_line_id
AND    rct.bill_to_customer_id  = hca.cust_account_id
AND    hca.party_id         =  hp.party_id
--AND    rctl.tax_line_id     = zl.tax_line_id(+)
AND    rct.org_id         = hou.organization_id
AND    rct.cust_trx_type_id = rctt.cust_trx_type_id
AND    rct.org_id         = rctt.org_id
AND    rctd.code_combination_id = gcc.code_combination_id
AND    rctd.account_class = 'REV'
--AND    arsl.trx_hdr_id=rct.customer_trx_id
AND       decode(rctd.posting_control_id, -3, 'N', 'Y')='Y'
--AND    arsl.GL_TRANSFER_STATUS='Y'
--AND    arsl.code_combination_id=rctd.code_combination_id
--AND    rct.trx_number=nvl(:P_TRX_NUM,rct.trx_number)
AND    rct.org_id=nvl(:P_ORG_ID,rct.org_id)
--AND    HP.PARTY_NAME=NVL(:P_CUST_NAME,HP.PARTY_NAME)
AND hca.account_number=nvl(:P_CUST_NAME,hca.account_number)
AND    RCTT.NAME=NVL(:P_TRX_TYPE,RCTT.NAME)
AND   rct.interface_header_context is null
  --and rct.trx_number='2012131000085'--and rct.trx_number='2012131000328'
--AND    hou.name=nvl(:P_OP_NAME,hou.name)
AND   rctd.gl_date between :P_FROM_G_DATE and :P_TO_G_DATE
AND   gcc.concatenated_segments between nvl(:P_FROM_GL_ACCOUNT,to_char(gcc.concatenated_segments)) and nvl(:P_TO_GL_ACCOUNT,to_char(gcc.concatenated_segments))