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!

Query To Display Receipt Classes in R12

Discussion in 'General' started by jagadekara, Sep 13, 2014.

  1. jagadekara

    jagadekara Forum Guru

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

    Receivables Resp--> Oracle receivables-->Setup-->Receipts-->Receipt Classes

    Code (SQL):
    --Receipt Class and Bank Accounts attached to them
    SELECT arc.name
          ,al.meaning creation_method
          ,nvl(al1.meaning,'No Remittance') remittance_method
          ,al2.meaning clearance_method
          ,arm.name receipt_method_name
          ,arm.printed_name receipt_method_printed_name
          ,arm.start_date effective_date_from
          ,arm.end_date effective_date_to
          ,hou.name operating_unit
          ,cbbv.bank_name
          ,cbbv.bank_branch_name
          ,cba.bank_account_name
          ,cba.currency_code
          ,arma.min_receipt_amount
          ,arma.start_date
          ,arma.end_date
          ,arma.override_remit_account_flag override_bank
          ,arma.primary_flag PRIMARY
          ,gcck.concatenated_segments cash
        --  ,gcck1.concatenated_segments receipt_confirmation
          ,gcck2.concatenated_segments remittance
        --  ,gcck3.concatenated_segments factoring
       --   ,gcck4.concatenated_segments short_term_debt
          ,gcck5.concatenated_segments bank_charges
          ,gcck6.concatenated_segments unapplied_receipts
          ,gcck7.concatenated_segments unidentified_receipts
          ,gcck8.concatenated_segments on_account_receipts
          ,arta.name unearned_discounts
          ,arta1.name earned_discounts
      FROM ar_receipt_classes arc
          ,ar_lookups al
          ,ar_lookups al1
          ,ar_lookups al2
          ,ar_receipt_methods arm
          ,ar_receipt_method_accounts_All arma
          ,hr_operating_units hou
          ,ce_bank_accounts cba
          ,ce_bank_branches_v cbbv
          ,gl_code_combinations_kfv gcck
          ,gl_code_combinations_kfv gcck1
          ,gl_code_combinations_kfv gcck2
          ,gl_code_combinations_kfv gcck3
          ,gl_code_combinations_kfv gcck4
          ,gl_code_combinations_kfv gcck5
          ,gl_code_combinations_kfv gcck6
          ,gl_code_combinations_kfv gcck7
          ,gl_code_combinations_kfv gcck8
          ,ar_receivables_trx_all arta
          ,ar_receivables_trx_all arta1
    WHERE 1=1
       AND al.lookup_code(+)=arc.creation_method_code
       AND al.lookup_type(+)='RECEIPT_CREATION_METHOD'
       AND al1.lookup_code(+)=arc.remit_method_code
       AND al1.lookup_type(+)='REMITTANCE_METHOD'
       AND al2.lookup_code(+)=arc.clear_flag
       AND al2.lookup_type(+)='REQUIRE_CLEARANCE'
       AND arm.receipt_class_id=arc.receipt_class_id
       AND arma.receipt_method_id(+)=arm.receipt_method_id
       AND hou.organization_id(+)=arma.org_id
       AND cba.bank_account_id(+)=arma.remit_bank_acct_use_id
       AND cbbv.branch_party_id(+)=cba.bank_branch_id
       AND gcck.code_combination_id(+)=arma.cash_ccid
       AND gcck1.code_combination_id(+)=arma.receipt_clearing_ccid
       AND gcck2.code_combination_id(+)=arma.remittance_ccid
       AND gcck3.code_combination_id(+)=arma.factor_ccid
       AND gcck4.code_combination_id(+)=arma.short_term_debt_ccid
       AND gcck5.code_combination_id(+)=arma.bank_charges_ccid
       AND gcck6.code_combination_id(+)=arma.unapplied_ccid
       AND gcck7.code_combination_id(+)=arma.unidentified_ccid
       AND gcck8.code_combination_id(+)=arma.on_account_ccid
       AND arta.receivables_trx_id(+)=arma.unedisc_receivables_trx_id
       AND arta1.receivables_trx_id(+)=arma.edisc_receivables_trx_id
    ORDER BY 1,5
     
  2. annamaneni

    annamaneni Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    100
    Very nice query and thanks a lot for your hardwork.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    You are most welcome. :hurray