1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

not a GROUP BY expression

Discussion in 'SQL PL/SQL' started by jagadekara, Jun 5, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Below Query getting error of not a GROUP BY expression.

    If I run the first query or second query alone, it is running fine. When we run both with union then it is giving that error. Any suggestions?

    ----This query Returns Draft Invoices Which are transfered to GL--------------

    select a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) Enterd_AMT -- on 16thoct2013 devided by exchange rate for entered amount
    ,a.ACCT_AMT
    ,null entered_revenue_amount
    ,null accounted_revenue_amount
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    -- ,a.line_amoun line_amoun
    ,a.Revenue_Account
    ,'Invoice' Type
    from (SELECT h.name OPERATING_UNIT
    ,C.CUSTOMER_NAME CUSTOMER_NAME
    ,C.CUSTOMER_NUMBER CUSTOMER_NUMBER
    ,rct.trx_number INV_NO
    ,rct.trx_date INV_DATE
    ,pinv.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=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) Enterd_AMT
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) ACCT_AMT
    ,round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT(pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1)) Tax -- on 16thoct2013 multiplied with exchange rate for accounted tax refer 2012131000655 proj
    ,nvl((select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num),0)+round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT(pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1)) TOTAL
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.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
    ,pb.amount line_amoun
    -- ,gcc.concatenated_segments Revenue_Account
    ,null Revenue_Account
    -- ,rct.customer_trx_id
    --,nvl(-(xal.entered_dr),xal.entered_cr) INVOICE_AMOUNT
    FROM PA_CUSTOMERS_V C
    ,PA_PROJECT_CUSTOMERS_V PPCV
    ,PA_DRAFT_INVOICES_ALL PINV
    ,apps.PA_DRAFT_INV_ITEMS_BAS PB
    ,PA_PROJECTS P
    ,hr_all_organization_units h
    ,PA_LOOKUPS LK
    ,xla_transaction_entities xte
    ,xla_ae_headers xah
    ,xla_ae_lines xal
    ,ra_customer_trx_all rct
    ,gl_code_combinations_kfv gcc
    ,(SELECT PI.PROJECT_ID,
    PI.DRAFT_INVOICE_NUM,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) TAX_AMOUNT
    FROM PA_DRAFT_INVOICE_ITEMS PI, ZX_RATES_B ZR
    WHERE PI.OUTPUT_TAX_CLASSIFICATION_CODE = ZR.TAX_RATE_CODE
    AND ZR.ACTIVE_FLAG = 'Y'
    AND PI.OUTPUT_TAX_CLASSIFICATION_CODE IS NOT NULL
    GROUP BY PI.PROJECT_ID,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    PI.DRAFT_INVOICE_NUM
    HAVING SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) <> 0) TAX
    WHERE P.PROJECT_ID = PINV.PROJECT_ID
    AND P.PROJECT_ID = PPCV.PROJECT_ID
    AND PPCV.CUSTOMER_ID = C.CUSTOMER_ID
    AND TAX.PROJECT_ID(+) = PINV.PROJECT_ID
    AND PINV.PROJECT_ID = PB.PROJECT_ID
    AND PINV.DRAFT_INVOICE_NUM = PB.DRAFT_INVOICE_NUM
    AND TAX.DRAFT_INVOICE_NUM(+) = PINV.DRAFT_INVOICE_NUM
    AND PINV.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    and p.ORG_ID = h.organization_id
    AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
    AND LK.LOOKUP_CODE = DECODE(PINV.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PINV.APPROVED_DATE, NULL, 'UNAPPROVED', DECODE(PINV.RELEASED_DATE, NULL, 'APPROVED', DECODE(PINV.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED' ) ) ) )
    AND p.ORG_ID = nvl:)P_ORG_ID,p.org_id)
    AND c.CUSTOMER_NUMBER=nvl:)P_CUST_NAME,c.CUSTOMER_NUMBER)
    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))
    -- and p.segment1='2012131000679'
    and xte.source_id_int_1=pinv.system_reference
    and xte.entity_id=xah.entity_id
    and xah.ae_header_id=xal.ae_header_id
    and gcc.code_combination_id=xal.code_combination_id
    and xal.accounting_class_code='REVENUE'
    and rct.customer_trx_id=pinv.system_reference
    and rct.interface_header_attribute2=pinv.draft_invoice_num
    and rct.interface_header_attribute1=p.segment1
    and rct.org_id=p.org_id) a
    group by a.OPERATING_UNIT
    ,CUSTOMER_NAME
    ,CUSTOMER_NUMBER
    ,a.CUSTOMER_NAME
    ,INV_NO
    ,INV_DATE
    ,GL_DATE
    ,REF_NO
    ,VOUCHER_NO
    ,CURRENCY_CODE
    ,exchange_rate
    ,Enterd_AMT
    ,ACCT_AMT
    ,Tax
    ,TOTAL
    ,Receivable_Amt
    ,CONTEXT
    ,project_num
    ,draft_inv_num
    ,agreement_num
    ,project_org
    ,project_manager
    ,CLASS
    -- ,line_amoun
    ,Revenue_Account
    -- ,a.customer_trx_id
    Union


    --This Query returns only Accepted Draft Revenues generated through EVENTS---
    select b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1 CUSTOMER_NAME
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1 CUSTOMER_NUMBER
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    ,sum(b.entered_revenue_amount) entered_revenue_amount
    ,sum(b.accounted_revenue_amount) accounted_revenue_amount
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type
    from (SELECT h.name OPERATING_UNIT
    ,PPCV.CUSTOMER_NAME
    ,PPCV.CUSTOMER_NAME CUSTOMER_NAME1
    ,PPCV.CUSTOMER_NUMBER
    ,PPCV.CUSTOMER_NUMBER CUSTOMER_NUMBER1
    ,(select rtrim (xmlagg (xmlelement (e, pdia1.ra_invoice_number || ',')).extract ('//text()'), ',') from pa_draft_invoices_all pdia1,pa_draft_invoice_items pdii1
    where pdia1.project_id=pdii1.project_id and pdia1.draft_invoice_num=pdii1.draft_invoice_num
    and ppa.project_id=pdia1.project_id and pdii1.event_num=pcerdl.event_num
    and pdii1.event_num is not null
    )INV_NO
    ,null INV_DATE
    ,padr.gl_date GL_DATE
    ,null REF_NO
    ,null VOUCHER_NO
    , pcerdl.bill_trans_currency_code CURRENCY_CODE
    , pcerdl.revproc_exchange_rate exchange_rate
    , null Enterd_AMT
    -- ,(pcerdl.bill_trans_amount*pcerdl.revproc_exchange_rate) acct
    , null ACCT_AMT
    ,pcerdl.bill_trans_amount entered_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,pcerdl.revtrans_amount accounted_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,null Tax
    ,null TOTAL
    ,null Receivable_Amt
    ,null CONTEXT
    ,ppa.segment1 project_num
    ,null draft_inv_num
    -- ,pdra.draft_revenue_num
    ,pa.agreement_num agreement_num
    ,ppa.name project_org
    ,null project_manager
    ,null CLASS
    --,pcerda.amount line_amoun
    ,gcc.concatenated_segments Revenue_Account
    ,'Revenue' Type
    -- ,round((sum(pcerda.amount))*ST_FN_AVG_CURR_CONVERSION(ppa.project_currency_code,'USD',:p_from_date,:p_to_date),2) in_USD_amt
    FROM PA_DRAFT_REVENUES_ALL PADR,
    PA_PROJECTS PPA,
    PA_PROJECT_CUSTOMERS_V PPCV,
    PA_LOOKUPS LK,
    hr_all_organization_units h
    -- ,pa_draft_revenue_items pdri
    ,pa_cust_event_rdl_all pcerda
    ,gl_code_combinations_kfv gcc
    ,PA_CUST_EVENT_REV_DIST_LINES_V pcerdl
    , pa_agreements_all pa
    WHERE PPA.PROJECT_ID = PADR.PROJECT_ID
    AND PPCV.PROJECT_ID = PPA.PROJECT_ID
    AND LK.LOOKUP_TYPE = 'TRANSFER STATUS'
    AND LK.LOOKUP_CODE = PADR.TRANSFER_STATUS_CODE
    AND PADR.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    AND ST_REV_AMT_FOR_PROJ(PPA.PROJECT_ID, PADR.ACCRUE_THROUGH_DATE) IS NOT NULL
    AND PPA.ORG_ID = nvl:)P_ORG_ID,ppa.org_id)
    and ppa.org_id = h.organization_id
    -- and ppa.project_currency_code <> 'USD'
    and ppcv.customer_number=nvl:)P_CUST_NAME,ppcv.customer_number)
    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))
    and pcerda.project_id=ppa.project_id
    and pcerda.draft_revenue_num=padr.draft_revenue_num
    -- and pcerda.draft_revenue_item_line_num=pdri.line_num
    and pcerda.code_combination_id=gcc.code_combination_id
    -- and ppa.segment1='2012131000562'
    and lk.meaning='Accepted'
    and pcerdl.project_id=ppa.project_id
    and pcerdl.draft_revenue_num=padr.draft_revenue_num
    and pcerdl.draft_revenue_item_line_num=pcerda.draft_revenue_item_line_num
    and padr.agreement_id=pa.agreement_id
    AND ppa.segment1=nvl:)P_PROJ_NUM,ppa.segment1)
    ) b
    where 1=1
    group by b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    ,(b.entered_revenue_amount)
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    The quantity of fields in SELECT doesn't correspond to quantity of fields in GROUP BY
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Then why it is allowing fine when we run the queries alone?
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is necessary to check quantity and a column order that matched in both requests....

    For example (group conditions from your queries):

    Code (SQL):

    SELECT
    1,regexp_count('
    a.OPERATING_UNIT
    ,CUSTOMER_NAME
    ,CUSTOMER_NUMBER
    ,a.CUSTOMER_NAME
    ,INV_NO
    ,INV_DATE
    ,GL_DATE
    ,REF_NO
    ,VOUCHER_NO
    ,CURRENCY_CODE
    ,exchange_rate
    ,Enterd_AMT
    ,ACCT_AMT
    ,Tax
    ,TOTAL
    ,Receivable_Amt
    ,CONTEXT
    ,project_num
    ,draft_inv_num
    ,agreement_num
    ,project_org
    ,project_manager
    ,CLASS
    ,Revenue_Account'
    ,'[^,]+') group_by FROM dual
    UNION ALL
    SELECT 2, regexp_count('
     b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    ,(b.entered_revenue_amount)
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type'
    ,'[^,]+')  FROM dual;

     
    SQL>
     
             1   GROUP_BY
    ---------- ----------
             1         24
             2         27
     


     
     
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I changed like this...

    ----This query Returns Draft Invoices Which are transfered to GL--------------

    select a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) Enterd_AMT -- on 16thoct2013 devided by exchange rate for entered amount
    ,a.ACCT_AMT
    ,null entered_revenue_amount
    ,null accounted_revenue_amount
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    -- ,a.line_amoun line_amoun
    ,a.Revenue_Account
    ,'Invoice' Type
    from (SELECT h.name OPERATING_UNIT
    ,C.CUSTOMER_NAME CUSTOMER_NAME
    ,C.CUSTOMER_NUMBER CUSTOMER_NUMBER
    ,rct.trx_number INV_NO
    ,rct.trx_date INV_DATE
    ,pinv.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=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) Enterd_AMT
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) ACCT_AMT
    ,round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT (pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1 )) Tax -- on 16thoct2013 multiplied with exchange rate for accounted tax refer 2012131000655 proj
    ,nvl((select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num),0) +round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT (pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1 )) TOTAL
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.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
    ,pb.amount line_amoun
    -- ,gcc.concatenated_segments Revenue_Account
    ,null Revenue_Account
    -- ,rct.customer_trx_id
    --,nvl(-(xal.entered_dr),xal.entered_cr) INVOICE_AMOUNT
    FROM PA_CUSTOMERS_V C
    ,PA_PROJECT_CUSTOMERS_V PPCV
    ,PA_DRAFT_INVOICES_ALL PINV
    ,apps.PA_DRAFT_INV_ITEMS_BAS PB
    ,PA_PROJECTS P
    ,hr_all_organization_units h
    ,PA_LOOKUPS LK
    ,xla_transaction_entities xte
    ,xla_ae_headers xah
    ,xla_ae_lines xal
    ,ra_customer_trx_all rct
    ,gl_code_combinations_kfv gcc
    ,(SELECT PI.PROJECT_ID,
    PI.DRAFT_INVOICE_NUM,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) TAX_AMOUNT
    FROM PA_DRAFT_INVOICE_ITEMS PI, ZX_RATES_B ZR
    WHERE PI.OUTPUT_TAX_CLASSIFICATION_CODE = ZR.TAX_RATE_CODE
    AND ZR.ACTIVE_FLAG = 'Y'
    AND PI.OUTPUT_TAX_CLASSIFICATION_CODE IS NOT NULL
    GROUP BY PI.PROJECT_ID,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    PI.DRAFT_INVOICE_NUM
    HAVING SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) <> 0) TAX
    WHERE P.PROJECT_ID = PINV.PROJECT_ID
    AND P.PROJECT_ID = PPCV.PROJECT_ID
    AND PPCV.CUSTOMER_ID = C.CUSTOMER_ID
    AND TAX.PROJECT_ID(+) = PINV.PROJECT_ID
    AND PINV.PROJECT_ID = PB.PROJECT_ID
    AND PINV.DRAFT_INVOICE_NUM = PB.DRAFT_INVOICE_NUM
    AND TAX.DRAFT_INVOICE_NUM(+) = PINV.DRAFT_INVOICE_NUM
    AND PINV.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    and p.ORG_ID = h.organization_id
    AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
    AND LK.LOOKUP_CODE = DECODE(PINV.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PINV.APPROVED_DATE, NULL, 'UNAPPROVED', DECODE(PINV.RELEASED_DATE, NULL, 'APPROVED', DECODE(PINV.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED' ) ) ) )
    AND p.ORG_ID = nvl:)P_ORG_ID,p.org_id)
    AND c.CUSTOMER_NUMBER=nvl:)P_CUST_NAME,c.CUSTOMER_NUMBER)
    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))
    -- and p.segment1='2012131000679'
    and xte.source_id_int_1=pinv.system_reference
    and xte.entity_id=xah.entity_id
    and xah.ae_header_id=xal.ae_header_id
    and gcc.code_combination_id=xal.code_combination_id
    and xal.accounting_class_code='REVENUE'
    and rct.customer_trx_id=pinv.system_reference
    and rct.interface_header_attribute2=pinv.draft_invoice_num
    and rct.interface_header_attribute1=p.segment1
    and rct.org_id=p.org_id) a
    group by a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) -- on 16thoct2013 devided by exchange rate for entered amount
    ,a.ACCT_AMT
    ,null
    ,null
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    -- ,a.line_amoun line_amoun
    ,a.Revenue_Account
    ,'Invoice'
    Union


    --This Query returns only Accepted Draft Revenues generated through EVENTS---
    select b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1 CUSTOMER_NAME
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1 CUSTOMER_NUMBER
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    ,sum(b.entered_revenue_amount) entered_revenue_amount
    ,sum(b.accounted_revenue_amount) accounted_revenue_amount
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type
    from (SELECT h.name OPERATING_UNIT
    ,PPCV.CUSTOMER_NAME
    ,PPCV.CUSTOMER_NAME CUSTOMER_NAME1
    ,PPCV.CUSTOMER_NUMBER
    ,PPCV.CUSTOMER_NUMBER CUSTOMER_NUMBER1
    ,(select rtrim (xmlagg (xmlelement (e, pdia1.ra_invoice_number || ',')).extract ('//text()'), ',') from pa_draft_invoices_all pdia1,pa_draft_invoice_items pdii1
    where pdia1.project_id=pdii1.project_id and pdia1.draft_invoice_num=pdii1.draft_invoice_num
    and ppa.project_id=pdia1.project_id and pdii1.event_num=pcerdl.event_num
    and pdii1.event_num is not null
    )INV_NO
    ,null INV_DATE
    ,padr.gl_date GL_DATE
    ,null REF_NO
    ,null VOUCHER_NO
    , pcerdl.bill_trans_currency_code CURRENCY_CODE
    , pcerdl.revproc_exchange_rate exchange_rate
    , null Enterd_AMT
    -- ,(pcerdl.bill_trans_amount*pcerdl.revproc_exchange _rate) acct
    , null ACCT_AMT
    ,pcerdl.bill_trans_amount entered_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,pcerdl.revtrans_amount accounted_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,null Tax
    ,null TOTAL
    ,null Receivable_Amt
    ,null CONTEXT
    ,ppa.segment1 project_num
    ,null draft_inv_num
    -- ,pdra.draft_revenue_num
    ,pa.agreement_num agreement_num
    ,ppa.name project_org
    ,null project_manager
    ,null CLASS
    --,pcerda.amount line_amoun
    ,gcc.concatenated_segments Revenue_Account
    ,'Revenue' Type
    -- ,round((sum(pcerda.amount))*ST_FN_AVG_CURR_CONVERS ION(ppa.project_currency_code,'USD',_from_date,_to_date),2) in_USD_amt
    FROM PA_DRAFT_REVENUES_ALL PADR,
    PA_PROJECTS PPA,
    PA_PROJECT_CUSTOMERS_V PPCV,
    PA_LOOKUPS LK,
    hr_all_organization_units h
    -- ,pa_draft_revenue_items pdri
    ,pa_cust_event_rdl_all pcerda
    ,gl_code_combinations_kfv gcc
    ,PA_CUST_EVENT_REV_DIST_LINES_V pcerdl
    , pa_agreements_all pa
    WHERE PPA.PROJECT_ID = PADR.PROJECT_ID
    AND PPCV.PROJECT_ID = PPA.PROJECT_ID
    AND LK.LOOKUP_TYPE = 'TRANSFER STATUS'
    AND LK.LOOKUP_CODE = PADR.TRANSFER_STATUS_CODE
    AND PADR.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    AND ST_REV_AMT_FOR_PROJ(PPA.PROJECT_ID, PADR.ACCRUE_THROUGH_DATE) IS NOT NULL
    AND PPA.ORG_ID = nvl:)P_ORG_ID,ppa.org_id)
    and ppa.org_id = h.organization_id
    -- and ppa.project_currency_code <> 'USD'
    and ppcv.customer_number=nvl:)P_CUST_NAME,ppcv.customer_number)
    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))
    and pcerda.project_id=ppa.project_id
    and pcerda.draft_revenue_num=padr.draft_revenue_num
    -- and pcerda.draft_revenue_item_line_num=pdri.line_num
    and pcerda.code_combination_id=gcc.code_combination_id
    -- and ppa.segment1='2012131000562'
    and lk.meaning='Accepted'
    and pcerdl.project_id=ppa.project_id
    and pcerdl.draft_revenue_num=padr.draft_revenue_num
    and pcerdl.draft_revenue_item_line_num=pcerda.draft_revenue_item_line_num
    and padr.agreement_id=pa.agreement_id
    AND ppa.segment1=nvl:)P_PROJ_NUM,ppa.segment1)
    ) b
    where 1=1
    group by b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    --,sum(b.entered_revenue_amount)
    --,sum(b.accounted_revenue_amount)
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type

    But still same issue...

    In first query 28 columns in select as well as 28 in group by.

    In second query 28 columns in select and 26 in group by.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Check quantity and orders of columns in SELECT sentence in both queries.
    Code (SQL):


    SELECT regexp_count ('a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) Enterd_AMT
    ,a.ACCT_AMT
    ,null entered_revenue_amount
    ,null accounted_revenue_amount
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    ,a.Revenue_Account'
    ,'[^,]+') QUERIES FROM dual UNION ALL
    SELECT regexp_count ('b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1 CUSTOMER_NAME
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1 CUSTOMER_NUMBER
    ,b.INV_NO
    ,b.INV_DATE
    ,b.GL_DATE
    ,b.REF_NO
    ,b.VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,b.Enterd_AMT
    ,b.ACCT_AMT
    ,sum(b.entered_revenue_amount) entered_revenue_amount
    ,sum(b.accounted_revenue_amount) accounted_revenue_amount
    ,b.Tax
    ,b.TOTAL
    ,b.Receivable_Amt
    ,b.CONTEXT
    ,b.project_num
    ,b.draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,b.project_manager
    ,b.CLASS
    ,b.Revenue_Account
    ,b.Type'
    ,'[^,]+')   FROM dual;

    SQL>
     
       QUERIES
    ----------
            29
            28

     
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    regexp_count counting based on comma (,).

    i Have ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) Enterd_AMT

    in that a.ACCT_AMT and exchange_rate columns are there.

    so I already used both in group by.
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Yes, I saw that NVL is used.....
    Therefore the quantity of fields will match.
    The order of fields matches?

    What error now arises in case of request execution?
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    It shows same error.

    not a GROUP BY expression

    I just copy paste select columns in group by. So order is also same.
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Well.
    How Oracle version?
    Similar to BUG: ID 9824198
    Till 11.2.0.4 there was a bug
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    PL/SQL Release 11.1.0.7.0 - Production
    "CORE 11.1.0.7.0 Production"
    TNS for Linux: Version 11.1.0.7.0 - Production
    NLSRTL Version 11.1.0.7.0 - Production
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    If isn't present probably to carry out a version upgrade, then it is possible to try such option of request:
    Before execution of request it is necessary to set session parameter : alter session set optimizer_features_enable = '10.2.0.5'
    after to execute request

    or

    use query : SELECT /*+ optimizer_features_enable('10.2.0.5') */ * FROM (....your query);

    NOTE: it is better to carry out upgrade to 11.2.0.3(..4)
     
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Finally I got it....

    Here is the correct Query:

    ----This query Returns Draft Invoices Which are transfered to GL--------------

    select a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) Enterd_AMT -- on 16thoct2013 devided by exchange rate for entered amount
    ,a.ACCT_AMT
    ,null entered_revenue_amount
    ,null accounted_revenue_amount
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    -- ,a.line_amoun line_amoun
    ,a.Revenue_Account
    ,'Invoice' Type
    from (SELECT h.name OPERATING_UNIT
    ,C.CUSTOMER_NAME CUSTOMER_NAME
    ,C.CUSTOMER_NUMBER CUSTOMER_NUMBER
    ,rct.trx_number INV_NO
    ,rct.trx_date INV_DATE
    ,pinv.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=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) Enterd_AMT
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num) ACCT_AMT
    ,round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT (pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1 )) Tax -- on 16thoct2013 multiplied with exchange rate for accounted tax refer 2012131000655 proj
    ,nvl((select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.draft_invoice_num),0) +round(nvl(pa_output_tax.GET_DRAFT_INVOICE_TAX_AMT (pinv.SYSTEM_REFERENCE),0)*nvl(rct.exchange_rate,1 )) TOTAL
    ,(select sum(pdii1.amount) from pa_draft_invoice_items pdii1 where pdii1.project_id=pinv.project_id
    and pdii1.draft_invoice_num=pinv.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
    ,pb.amount line_amoun
    -- ,gcc.concatenated_segments Revenue_Account
    ,null Revenue_Account
    -- ,rct.customer_trx_id
    --,nvl(-(xal.entered_dr),xal.entered_cr) INVOICE_AMOUNT
    FROM PA_CUSTOMERS_V C
    ,PA_PROJECT_CUSTOMERS_V PPCV
    ,PA_DRAFT_INVOICES_ALL PINV
    ,apps.PA_DRAFT_INV_ITEMS_BAS PB
    ,PA_PROJECTS P
    ,hr_all_organization_units h
    ,PA_LOOKUPS LK
    ,xla_transaction_entities xte
    ,xla_ae_headers xah
    ,xla_ae_lines xal
    ,ra_customer_trx_all rct
    ,gl_code_combinations_kfv gcc
    ,(SELECT PI.PROJECT_ID,
    PI.DRAFT_INVOICE_NUM,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) TAX_AMOUNT
    FROM PA_DRAFT_INVOICE_ITEMS PI, ZX_RATES_B ZR
    WHERE PI.OUTPUT_TAX_CLASSIFICATION_CODE = ZR.TAX_RATE_CODE
    AND ZR.ACTIVE_FLAG = 'Y'
    AND PI.OUTPUT_TAX_CLASSIFICATION_CODE IS NOT NULL
    GROUP BY PI.PROJECT_ID,
    PI.OUTPUT_TAX_CLASSIFICATION_CODE,
    PI.DRAFT_INVOICE_NUM
    HAVING SUM(PI.INV_AMOUNT * ZR.PERCENTAGE_RATE / 100) <> 0) TAX
    WHERE P.PROJECT_ID = PINV.PROJECT_ID
    AND P.PROJECT_ID = PPCV.PROJECT_ID
    AND PPCV.CUSTOMER_ID = C.CUSTOMER_ID
    AND TAX.PROJECT_ID(+) = PINV.PROJECT_ID
    AND PINV.PROJECT_ID = PB.PROJECT_ID
    AND PINV.DRAFT_INVOICE_NUM = PB.DRAFT_INVOICE_NUM
    AND TAX.DRAFT_INVOICE_NUM(+) = PINV.DRAFT_INVOICE_NUM
    AND PINV.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    and p.ORG_ID = h.organization_id
    AND LK.LOOKUP_TYPE = 'INVOICE STATUS'
    AND LK.LOOKUP_CODE = DECODE(PINV.GENERATION_ERROR_FLAG, 'Y', 'GENERATION ERROR', DECODE(PINV.APPROVED_DATE, NULL, 'UNAPPROVED', DECODE(PINV.RELEASED_DATE, NULL, 'APPROVED', DECODE(PINV.TRANSFER_STATUS_CODE, 'P', 'RELEASED', 'X', 'REJECTED IN TRANSFER', 'T', 'TRANSFERRED', 'A', 'ACCEPTED', 'R', 'REJECTED' ) ) ) )
    AND p.ORG_ID = nvl:)P_ORG_ID,p.org_id)
    AND c.CUSTOMER_NUMBER=nvl:)P_CUST_NAME,c.CUSTOMER_NUMBER)
    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))
    -- and p.segment1='2012131000679'
    and xte.source_id_int_1=pinv.system_reference
    and xte.entity_id=xah.entity_id
    and xah.ae_header_id=xal.ae_header_id
    and gcc.code_combination_id=xal.code_combination_id
    and xal.accounting_class_code='REVENUE'
    and rct.customer_trx_id=pinv.system_reference
    and rct.interface_header_attribute2=pinv.draft_invoice_num
    and rct.interface_header_attribute1=p.segment1
    and rct.org_id=p.org_id) a
    group by a.OPERATING_UNIT
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NAME
    ,a.CUSTOMER_NUMBER
    ,a.CUSTOMER_NUMBER
    ,a.INV_NO
    ,a.INV_DATE
    ,a.GL_DATE
    ,a.REF_NO
    ,a.VOUCHER_NO
    ,a.CURRENCY_CODE
    ,a.exchange_rate
    ,round((nvl(a.ACCT_AMT,0)/nvl(exchange_rate,1))) -- on 16thoct2013 devided by exchange rate for entered amount
    ,a.ACCT_AMT
    ,null
    ,null
    ,a.Tax
    ,a.TOTAL
    ,a.Receivable_Amt
    ,a.CONTEXT
    ,a.project_num
    ,a.draft_inv_num
    ,a.agreement_num
    ,a.project_org
    ,a.project_manager
    ,a.CLASS
    -- ,a.line_amoun line_amoun
    ,a.Revenue_Account
    ,'Invoice'

    Union


    --This Query returns only Accepted Draft Revenues generated through EVENTS---
    select b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1 CUSTOMER_NAME
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1 CUSTOMER_NUMBER
    ,b.INV_NO
    ,null INV_DATE
    ,b.GL_DATE
    ,null REF_NO
    ,null VOUCHER_NO
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,null Enterd_AMT
    ,null ACCT_AMT
    ,sum(b.entered_revenue_amount) entered_revenue_amount
    ,sum(b.accounted_revenue_amount) accounted_revenue_amount
    ,null Tax
    ,null TOTAL
    ,null Receivable_Amt
    ,null CONTEXT
    ,b.project_num
    ,null draft_inv_num
    ,b.agreement_num
    ,b.project_org
    ,null project_manager
    ,null CLASS
    ,b.Revenue_Account
    ,'Revenue' Type
    from (SELECT h.name OPERATING_UNIT
    ,PPCV.CUSTOMER_NAME
    ,PPCV.CUSTOMER_NAME CUSTOMER_NAME1
    ,PPCV.CUSTOMER_NUMBER
    ,PPCV.CUSTOMER_NUMBER CUSTOMER_NUMBER1
    ,(select rtrim (xmlagg (xmlelement (e, pdia1.ra_invoice_number || ',')).extract ('//text()'), ',') from pa_draft_invoices_all pdia1,pa_draft_invoice_items pdii1
    where pdia1.project_id=pdii1.project_id and pdia1.draft_invoice_num=pdii1.draft_invoice_num
    and ppa.project_id=pdia1.project_id and pdii1.event_num=pcerdl.event_num
    and pdii1.event_num is not null
    )INV_NO
    ,padr.gl_date GL_DATE
    , pcerdl.bill_trans_currency_code CURRENCY_CODE
    , pcerdl.revproc_exchange_rate exchange_rate
    ,pcerdl.bill_trans_amount entered_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,pcerdl.revtrans_amount accounted_revenue_amount -- took sum on 16thoct2013 earlier returns one line for same details refer 2012131000251 proj
    ,ppa.segment1 project_num
    -- ,pdra.draft_revenue_num
    ,pa.agreement_num agreement_num
    ,ppa.name project_org
    ,gcc.concatenated_segments Revenue_Account
    FROM PA_DRAFT_REVENUES_ALL PADR,
    PA_PROJECTS PPA,
    PA_PROJECT_CUSTOMERS_V PPCV,
    PA_LOOKUPS LK,
    hr_all_organization_units h
    -- ,pa_draft_revenue_items pdri
    ,pa_cust_event_rdl_all pcerda
    ,gl_code_combinations_kfv gcc
    ,PA_CUST_EVENT_REV_DIST_LINES_V pcerdl
    , pa_agreements_all pa
    WHERE PPA.PROJECT_ID = PADR.PROJECT_ID
    AND PPCV.PROJECT_ID = PPA.PROJECT_ID
    AND LK.LOOKUP_TYPE = 'TRANSFER STATUS'
    AND LK.LOOKUP_CODE = PADR.TRANSFER_STATUS_CODE
    AND PADR.GL_DATE BETWEEN :p_FROM_G_DATE and :p_TO_G_DATE
    AND ST_REV_AMT_FOR_PROJ(PPA.PROJECT_ID, PADR.ACCRUE_THROUGH_DATE) IS NOT NULL
    AND PPA.ORG_ID = nvl:)P_ORG_ID,ppa.org_id)
    and ppa.org_id = h.organization_id
    -- and ppa.project_currency_code <> 'USD'
    and ppcv.customer_number=nvl:)P_CUST_NAME,ppcv.customer_number)
    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))
    and pcerda.project_id=ppa.project_id
    and pcerda.draft_revenue_num=padr.draft_revenue_num
    -- and pcerda.draft_revenue_item_line_num=pdri.line_num
    and pcerda.code_combination_id=gcc.code_combination_id
    -- and ppa.segment1='2012131000562'
    and lk.meaning='Accepted'
    and pcerdl.project_id=ppa.project_id
    and pcerdl.draft_revenue_num=padr.draft_revenue_num
    and pcerdl.draft_revenue_item_line_num=pcerda.draft_revenue_item_line_num
    and padr.agreement_id=pa.agreement_id
    AND ppa.segment1=nvl:)P_PROJ_NUM,ppa.segment1)
    ) b
    where 1=1
    group by b.OPERATING_UNIT
    ,b.CUSTOMER_NAME
    ,b.CUSTOMER_NAME1
    ,b.CUSTOMER_NUMBER
    ,b.CUSTOMER_NUMBER1
    ,b.INV_NO
    ,null
    ,b.GL_DATE
    ,null
    ,null
    ,b.CURRENCY_CODE
    ,b.exchange_rate
    ,null
    ,null
    ,null
    ,null
    ,null
    ,null
    ,b.project_num
    ,null
    ,b.agreement_num
    ,b.project_org
    ,null
    ,null
    ,b.Revenue_Account
    ,'Revenue'