FA:SQL: Query to get inventory organization/inventory organization location and business unit and Legal entity

SELECT iod.organization_code,
       iod.organization_name,
       iod.business_unit_name,
       iod.business_unit_id,
       iod.legal_entity,
       xep.NAME legal_entity_name,
       hla.location_code,
       hla.location_name,
       hla.internal_location_code ,
              Concat(Concat(Concat(Concat(Concat(Concat( Concat(Concat(hla.address_line_1
              || ', ', Nvl2(hla.address_line_2, hla.address_line_2
              || ', ', hla.address_line_2)), Nvl2(hla.address_line_3, hla.address_line_3
              || ', ', hla.address_line_3)), Nvl2(hla.address_line_4, hla.address_line_4
              || ', ', hla.address_line_4)), Nvl2(hla.town_or_city, hla.town_or_city
              || ', ', hla.town_or_city)), Nvl2(hla.region_1, hla.region_1
              || ', ', hla.region_1)), Nvl2(hla.region_2, hla.region_2
              || ', ', hla.region_2)), Nvl2(hla.country, country
              || ', ', hla.country)), hla.postal_code) concatenated_address,
       iod.inventory_enabled_flag,
       iod.inventory_flag,
       iod.distributed_organization_flag,
       iod.profit_center_bu_id,
       iod.mfg_plant_flag,
       iod.contract_mfg_flag,
       iod.eam_enabled_flag,
       iod.timezone_code
 FROM inv_organization_definitions_v iod,
      hr_locations_all hla,
      xle_entity_profiles xep
WHERE iod.location_id = hla.location_id
  AND iod.legal_entity = xep.legal_entity_id
  AND NVL(iod.disable_date, sysdate+1) > sysdate;

Fusion: SQL: Query to Extract the Business Units and its related Inventory Organizations, Business Units and Costing Organizations

 

 

SELECT hou.organization_id
       , hou.name organization_name
       , iop.organization_code organization_code
       , lgr.ledger_id set_of_book_id
       , lgr.chart_of_accounts_id chart_of_account_id
       , lgr.currency_code
       , lgr.period_set_name
       , Decode(hoi.status, 'A', 'Y', 'N') inv_enabled_flag
       , bu.bu_name business_unit_name
       , iop.business_unit_id business_unit_id
       , iop.legal_entity_id legal_entity
       , hou.type organization_type
  FROM hr_all_organization_units_x hou
       , hr_org_unit_classifications_x hoi
       , inv_org_parameters iop
       , gl_ledgers lgr
       , fun_all_business_units_v bu
 WHERE hou.organization_id = hoi.organization_id
   AND hou.organization_id = iop.organization_id
   AND hoi.classification_code = 'INV'
   AND bu.primary_ledger_id = lgr.ledger_id(+)
   AND lgr.object_type_code(+) = 'L'
   AND NVL(lgr.complete_flag, 'Y') = 'Y'
   AND bu.bu_id(+) = iop.business_unit_id;  

SQL : Get Business Unit Details

===================================

 SELECT hauft.NAME BusinessUnit, hauft.organization_id
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date    AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first ; 

SQL : Get Costing Organization Details

===================================

 SELECT hauft.NAME BusinessUnit
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'CST'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first; 

Oracle Fusion: Anonymous blocks (Procedural Call) data model in Oracle BI Publisher (Oracle Fusion)

 BI Publisher supports executing PL/SQL anonymous blocks. You can perform calculations in the PL/SQL block and return the result set. BI Publisher uses callable statements to execute anonymous blocks.

The requirements are:

• The PL/SQL block must return a result set of type REF cursor
• You must declare the out variable with the name, xdo_cursor;. This needs to be the first parameter in position in Data model. 
• If you do not declare the name properly, the first bind variable is treated as an out variable type and binds with REF cursor.
• Declare the data model parameter with name xdo_cursor. This name is reserved for out variable type for procedure/anonymous blocks.

Sample Script:

DECLARE

    TYPE refcursor IS REF CURSOR;

    xdo_cursor refcursor;

BEGIN

    OPEN :xdo_cursor FOR SELECT gl.name

                              , fnd_flex_ext.get_segs('GL', 'GL#', fnd.id_flex_num, gc.code_combination_id) concatenated_segments

                              , decode(nvl(gb.translated_flag, 'X'), 'R',(nvl(gb.begin_balance_dr, 0) - nvl(gb.begin_balance_cr, 0))

                                       , 'X',(nvl(gb.begin_balance_dr_beq, 0) - nvl(gb.begin_balance_cr_beq, 0)), - 99) begin_balance

                              , decode(nvl(gb.translated_flag, 'X'), 'R', 'No', 'X', 'Yes', 'XXX') is_functional_currency

                           FROM gl_ledgers gl

                              , gl_balances gb

                              , gl_code_combinations gc

                              , fnd_id_flex_structures_tl fnd

                          WHERE gc.chart_of_accounts_id = gl.chart_of_accounts_id

                            AND gl.ledger_id = gb.ledger_id

                            AND gc.code_combination_id = gb.code_combination_id

                            AND fnd.id_flex_num = gc.chart_of_accounts_id

                            AND fnd.application_id = 101

                            AND fnd.id_flex_code = 'GL#'

                            AND fnd.language = userenv('LANG')

                            AND gb.period_name = :p_period_name

                            AND gc.segment1 = :p_segment1

                            AND gc.segment3 = :p_segment3

                            AND gl.ledger_id = :p_pedger_id;

END;

Data Model:

Parameter Window:

Sample Output: