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 Payables Options Details In Oracle Apps R12

Discussion in 'Oracle Apps Technical' started by jagadekara, May 28, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Query to Display Payables Options Details In Oracle Apps R12

    Code (SQL):
    -------------------------------------------------------------
    SELECT hou.name "Operating unit"
    -------Accounting Option Tab Details--------------------------------------------
          ,aspa.when_to_account_pmt "When Payment is Issued" --Account For Payment
          ,aspa.recon_accounting_flag "When Payment Clears"  --Account For Payment
          ,aspa.when_to_account_gain_loss "When Payment Is Issued" --Account For Gain/Loss
          ,aspa.recon_accounting_flag "When Payment Clears2"       --Account For Gain/Loss
          ,aspa.future_dated_pmt_acct_source "Bills Payable Account Source"
          ,aspa.liability_post_lookup_code "Automatic Offset Method"
          ,aspa.discount_distribution_method "Discount Method"
          ,aspa.prorate_int_inv_across_dists "Interest"
          ,aspa.build_prepayment_accounts_flag "Prepayment Account"
    -------Currency Tab Details-----------------------------------------------------      
          ,aspa.multi_currency_flag "Use Multiple Currencies"
          ,aspa.make_rate_mandatory_flag "Require Exchange Rate Entry"
          ,aspa.calc_user_xrate "Calculate User Excange Rate"
          ,aspa.default_exchange_rate_type "Exchange Rate Type"
          ,gcck.concatenated_segments "Realized Gain"
          ,gcck2.concatenated_segments "Realized Loss"
          ,gcck3.concatenated_segments "Rounding"
    -------Tax Reporting Tab Details------------------------------------------------
          ,aspa.combined_filing_flag "Combined Filing Program"
          ,aspa.income_tax_region_flag "Use Pay Site Tax Region"
          ,aspa.income_tax_region "Income Tax Region"
    -------Invoice Tab Details------------------------------------------------------
          ,aspa.confirm_date_as_inv_num_flag "Confirm Date as Invoice Number"
          ,aspa.approvals_option "Allow Online Validation"
          ,aspa.inv_doc_category_override "Allow Document Catgry Override"
          ,aspa.allow_paid_invoice_adjust "Allow Adjustmts to Paid Invces"
          ,aspa.recalc_pay_schedule_flag "Recalculate Scheduled Payment"
          ,aspa.allow_supplier_bank_override "Allow Remit-To Acct Override"
          ,aspa.allow_inv_third_party_ovrd "Allow Remit-To Supplr Override"
          ,aspa.receipt_acceptance_days "Receipt Acceptance Days"
          ,aspa.gl_date_from_receipt_flag "GL Date Basis"
          ,gcck4.concatenated_segments "Freight Account"
          ,att.name " Pre Payment Terms"
          ,aspa.add_days_settlement_date "Settlement Days"
          ,gcck5.concatenated_segments "Tax Difference Account"
    -------Approval Tab Details-----------------------------------------------------
          ,aspa.approval_workflow_flag "Use Invoice Approval Workflow"
          ,aspa.allow_force_approval_flag "Allow Force Approval"
          ,aspa.validate_before_approval_flag "Require Validtn Befr Approval"
          ,aspa.approval_timing "Require Accting Befr Approval"
    -------Matching Tab Details-----------------------------------------------------
          ,aspa.allow_final_match_flag "Allow Final Matching"
          ,aspa.allow_dist_match_flag "Allow Distrbtn Level Matching"
          ,aspa.allow_flex_override_flag "Allow Matchng Acct Override"
          ,aspa.transfer_desc_flex_flag "Trnsf PO DFF Information"
          ,at.tolerance_name "Goods Tolerances"
          ,at2.tolerance_name "Services Tolerances"
    -------Interest Tab Details-----------------------------------------------------
          ,aspa.auto_calculate_interest_flag "Allow Interest Invoices"
          ,aspa.interest_tolerance_amount "Minimum Interest Amount"
          ,gcck6.concatenated_segments "Expense"
          ,gcck7.concatenated_segments "Liability"
    -------Expense Report Tab Details-----------------------------------------------
          ,aer.report_type "Default Template"
          ,aspa.apply_advances_default "Apply Advances"
          ,aspa.create_employee_vendor_flag "Automatclly Crate Emp as Supp"
          ,att2.name "Payment Terms"
          ,aspa.employee_pay_group_lookup_code "Pay Group"
          ,aspa.employee_payment_priority "Payment Priority"
          ,aspa.hold_unmatched_invoices_flag "Hold Unmatched Expense Reports"
    -------Payment Tab Details-----------------------------------------------------      
          ,aspa.disc_is_inv_less_tax_flag "Exclude Tax Frm Discnt Calcltn"
          ,aspa.post_dated_payments_flag "Allow Pre-Date"
          ,replace_check_flag "Allow Void and Reissue"
          ,aspa.update_pay_site_flag "Allow Address Change"
          ,aspa.use_bank_charge_flag "Enbl BankCharg Dedctn Frm Payt"
          ,aspa.bank_charge_bearer "Deduct Bank Charge Frm Paymt"
          ,aspa.allow_pymt_third_party_ovrd "Allow Remit-To Supplr Ovrride"
          ,aspa.pay_doc_category_override "Allow Document Category Ovrrde"
    -------Withholding Tax Tab Details-----------------------------------------------------      
          ,aspa.allow_awt_flag "Use Withholding Tax"
          ,aspa.allow_awt_override "Allow Manual Withholding"
          ,aag.name "Tax Group"
          ,aspa.awt_include_discount_amt "Include Discount Amount"
          ,aspa.awt_include_tax_amt "Include Tax Amount"
          ,aspa.create_awt_dists_type "Apply Withholding Tax"
          ,aspa.create_awt_invoices_type "Create Withholding Invoice"
          ,aspa.withholding_date_basis "Withholding Date Basis"
          ,aspa.enable_1099_on_awt_flag "Incl IT Tax Type on WH Dists" --"Include Income Tax Type on Withholding Distributions"
    -------Supplier Tab Details-----------------------------------------------------      
          ,aspa.invoice_currency_code "Invoice Currency"
          ,aspa.vendor_pay_group_lookup_code "Pay Group2"
          ,aspa.terms_date_basis "Terms Date Basis"
          ,aspa.pay_date_basis_lookup_code "Pay Date Basis"
          ,att2.name "Payment Terms2" --Need to confirm
    -------Reports Tab Details-----------------------------------------------------      
          ,aspa.sort_by_alternate_field "Sort By Alternate Field"
      FROM ap_system_parameters_all aspa
          ,hr_operating_units hou
          ,gl_code_combinations_kfv gcck
          ,gl_code_combinations_kfv gcck2
          ,gl_code_combinations_kfv gcck3
          ,gl_code_combinations_kfv gcck4
          ,ap_terms_tl att
          ,gl_code_combinations_kfv gcck5
          ,ap_tolerances at
          ,ap_tolerances at2
          ,gl_code_combinations_kfv gcck6
          ,gl_code_combinations_kfv gcck7
          ,ap_expense_reports aer
          ,ap_terms_tl att2
          ,ap_awt_groups aag
     WHERE 1=1
       AND hou.organization_id=nvl(:P_ORG_ID,hou.organization_id)
       AND aspa.org_id=hou.organization_id
       AND aspa.gain_code_combination_id=gcck.code_combination_id
       AND aspa.loss_code_combination_id=gcck2.code_combination_id
       AND aspa.rounding_error_ccid=gcck3.code_combination_id
       AND aspa.freight_code_combination_id=gcck4.code_combination_id(+)
       AND att.term_id=aspa.prepayment_terms_id
       AND aspa.prepay_tax_diff_ccid=gcck5.code_combination_id(+)
       AND aspa.tolerance_id=at.tolerance_id(+)
       AND aspa.services_tolerance_id=at2.tolerance_id(+)
       AND aspa.interest_code_combination_id=gcck6.code_combination_id(+)
       AND aer.expense_report_id(+)=aspa.expense_report_id
       AND aspa.interest_accts_pay_ccid=gcck7.code_combination_id(+)
       AND att2.term_id(+)=aspa.terms_id
       AND aag.group_id(+)=aspa.default_awt_group_id
    ;
     
  2. Abdo

    Abdo Guest

    Good Query