今天請教天翔Oracle ERP中有無查看SOB、ORG關係的View?
厲害的天翔告訴我就是這一個=> ORG_ORGANIZATION_DEFINITIONS
提供給大家參考,為了方便串所有的Org releationship 我改寫如下:
SELECT hou.business_group_id
, gsob.set_of_books_id set_of_books_id
, gsob.short_name
, gsob.NAME
, gsob.chart_of_accounts_id chart_of_accounts_id
, hou.organization_id organization_id
-- , hou.date_from user_definition_enable_date
--, hou.date_to disable_date
, mp.organization_code organization_code
, hou.NAME organization_name
-- , hoi1.org_information2 inventory_enabled_flag
, DECODE (fpg.multi_org_flag
, 'Y', DECODE (hoi2.org_information_context
, 'Accounting Information', TO_NUMBER
(hoi2.org_information3)
, TO_NUMBER (NULL)
)
, TO_NUMBER (NULL)
) operating_unit
, hou2.NAME ou_name
, DECODE (hoi2.org_information_context
, 'Accounting Information', TO_NUMBER (hoi2.org_information2)
, NULL
) legal_entity
, hou3.NAME le_name
FROM hr_organization_units hou
, hr_organization_units hou2
, hr_organization_units hou3
, hr_organization_information hoi1
, hr_organization_information hoi2
, mtl_parameters mp
, gl_sets_of_books gsob
, fnd_product_groups fpg
WHERE hou.organization_id = hoi1.organization_id
AND hou.organization_id = hoi2.organization_id
AND hou.organization_id = mp.organization_id
AND hou2.organization_id =
DECODE (fpg.multi_org_flag
, 'Y', DECODE (hoi2.org_information_context
, 'Accounting Information', TO_NUMBER
(hoi2.org_information3)
, TO_NUMBER (NULL)
)
, TO_NUMBER (NULL)
)
AND hou3.organization_id =
DECODE (hoi2.org_information_context
, 'Accounting Information', TO_NUMBER (hoi2.org_information2)
, NULL
)
AND hoi1.org_information1 = 'INV'
AND hoi1.org_information2 = 'Y'
AND (hoi1.org_information_context || '') = 'CLASS'
AND (hoi2.org_information_context || '') = 'Accounting Information'
AND hoi2.org_information1 = TO_CHAR (gsob.set_of_books_id)
全站熱搜