Monday, April 7, 2014

Query to Display Payables Options Details In Oracle Apps R12

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

;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect