Wednesday, August 31, 2016

Query to get Multi-Org Setup From Backend

SELECT   d.legal_entity_id le_id,
        d.NAME le_name,
        a.ledger_id ledger_id,
         a.short_name ledger_short_name, a.NAME ledger_name,
         a.currency_code currency,
         (SELECT gcc.segment1
            FROM apps.gl_code_combinations_kfv gcc
           WHERE gcc.code_combination_id = a.ret_earn_code_combination_id)  bsv,
         b.organization_id ou_id, b.short_code ou_short_name, b.NAME ou_name,
         c.organization_id inv_org_id, c.organization_code inv_org_code,
         c.organization_name inv_org_name, e.organization_code mtl_org_name,
         e.calendar_code mtl_calendar_code,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.material_account) material_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.material_overhead_account) material_oh_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.ap_accrual_account) ap_accrual_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.interorg_receivables_account) ic_ar_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.interorg_payables_account) ic_ap_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.cost_of_sales_account) cogs_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.invoice_price_var_account) ipv_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.average_cost_var_account)  avg_cost_var_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.sales_account) sales_account,
         (SELECT concatenated_segments
            FROM apps.gl_code_combinations_kfv
           WHERE code_combination_id = e.expense_account) exp_account,
         e.interorg_trnsfr_charge_percent ic_xfer_percent,
         e.attribute6 wh_link, e.attribute7 inv_org_type,
         e.attribute8 min_max_planning_day, e.attribute10 top_off_allowed,
         e.attribute11 wh_attributes_mandatory, e.attribute12 facility_code,
         e.attribute13 sph_region
    FROM apps.mtl_parameters e,
         apps.xle_entity_profiles d,
         apps.org_organization_definitions c,
         apps.hr_operating_units b,
         apps.gl_ledgers a
   WHERE e.organization_id = c.organization_id
     AND d.legal_entity_id = c.legal_entity
     AND c.disable_date IS NULL
     AND c.organization_name NOT LIKE 'DO NOT USE%'
     AND c.operating_unit = b.organization_id
     AND c.set_of_books_id = a.ledger_id
     AND b.set_of_books_id = a.ledger_id
     AND a.ledger_category_code = 'PRIMARY'
     --AND a.latest_opened_period_name = 'JUN-16'

ORDER BY bsv, ou_id, inv_org_id;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect