Protected by Copyscape Web Copyright Protection Software

Search This Blog

Loading...

Friday, June 28, 2013

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))