Monday, April 7, 2014

Query to display Finacial Options details in Oracle apps R12

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


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect