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 Finacial 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 Finacial Options details in Oracle apps R12

    Payables
    Set Up
    Options
    Financial Options

    Code (SQL):
    SELECT hou.name "Operating Unit"
          ,fsp.future_period_limit
          ,gcck.concatenated_segments "Liability"
          ,gcck2.concatenated_segments "Prepayment"
          ,gcck3.concatenated_segments "Bills Payable"
          ,gcck4.concatenated_segments "Discount Taken"
          ,gcck5.concatenated_segments "PO Rate Variance Gain"
          ,gcck6.concatenated_segments "PO Rate Variance Loss"
          ,gcck7.concatenated_segments "Expenses Clearing"
          ,gcck8.concatenated_segments "Miscellaneous"
          ,gcck9.concatenated_segments "Retainage"
          ,fsp.rfq_only_site_flag "RFQ Only Site"
          ,plvv.location_code "Ship-To Location"
          ,plvv2.location_code "Bill-To Location"
          ,ood.organization_code||' - '||ood.organization_name "Inventory Organization"
          ,fsp.ship_via_lookup_code "Ship Via"
          ,fsp.fob_lookup_code "FOB"
          ,fsp.freight_terms_lookup_code "Freight Terms"
          ,fsp.req_encumbrance_flag "Use Requisition Encumbrance"
          ,fsp.reserve_at_completion_flag "Reserve at Completion"
          ,fsp.purch_encumbrance_flag "Use PO Encumbrance"
          ,fsp.vat_country_code "Member State"
          ,fsp.vat_registration_num "VAT Registration Number"
          ,hbg.business_group_name "Business Group"
          ,decode(fsp.expense_check_address_flag,'H','Home','O','Office','P','Provisional') "Expense Reimbursement Address"
          ,fsp.use_positions_flag "Use Approval Hierarchies"
      FROM FINANCIALS_SYSTEM_PARAMS_ALL fsp
          ,hr_operating_units hou
          ,gl_code_combinations_kfv gcck
          ,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
          ,gl_code_combinations_kfv gcck9
          ,po_locations_val_v plvv
          ,po_locations_val_v plvv2
          ,org_organization_definitions ood
          ,hrfv_business_groups hbg
     WHERE 1=1
       AND fsp.org_id=hou.organization_id
       AND hou.organization_id=nvl(:P_ORG_ID,fsp.org_id)
       AND fsp.accts_pay_code_combination_id=gcck.code_combination_id
       AND fsp.prepay_code_combination_id=gcck2.code_combination_id
       AND fsp.future_dated_payment_ccid=gcck3.code_combination_id(+)
       AND fsp.disc_taken_code_combination_id=gcck4.code_combination_id
       AND fsp.rate_var_gain_ccid=gcck5.code_combination_id
       AND fsp.rate_var_loss_ccid=gcck6.code_combination_id
       AND fsp.expense_clearing_ccid=gcck7.code_combination_id(+)
       AND fsp.misc_charge_ccid=gcck8.code_combination_id(+)
       AND fsp.retainage_code_combination_id=gcck9.code_combination_id(+)
       AND plvv.location_id=fsp.ship_to_location_id
       AND plvv2.location_id=fsp.ship_to_location_id
       AND ood.organization_id=fsp.inventory_organization_id
       AND fsp.business_group_id=hbg.business_group_id