CST_ORGANIZATION_DEFINITIONS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
ORGANIZATION_ID ORGANIZATION_NAME CLASSIFICATION_CODE STATUS ORGANIZATION_CODE MASTER_ORGANIZATION_ID INVENTORY_FLAG BUSINESS_UNIT_ID INV_ORG_BUSINESS_UNIT_ID PROFIT_CENTER_BUSINESS_UNIT_ID BUSINESS_UNIT_NAME LEGAL_ENTITY_ID LEGAL_ENTITY_NAME LEDGER_ID LEDGER_NAME LEDGER_CATEGORY_CODE CURRENCY_CODE PERIOD_SET_NAME ACCOUNTED_PERIOD_TYPE CHART_OF_ACCOUNTS_ID COST_ORG_ID COST_ORG_NAME MASTER_ORGANIZATION_FLAG

Name

ORGANIZATION_ID

ORGANIZATION_NAME

CLASSIFICATION_CODE

STATUS

ORGANIZATION_CODE

MASTER_ORGANIZATION_ID

INVENTORY_FLAG

BUSINESS_UNIT_ID

INV_ORG_BUSINESS_UNIT_ID

PROFIT_CENTER_BUSINESS_UNIT_ID

BUSINESS_UNIT_NAME

LEGAL_ENTITY_ID

LEGAL_ENTITY_NAME

LEDGER_ID

LEDGER_NAME

LEDGER_CATEGORY_CODE

CURRENCY_CODE

PERIOD_SET_NAME

ACCOUNTED_PERIOD_TYPE

CHART_OF_ACCOUNTS_ID

COST_ORG_ID

COST_ORG_NAME

MASTER_ORGANIZATION_FLAG

Query

SQL_Statement
SELECT distinct haou.organization_id, haou.name organization_name, houc.classification_code, houc.status Status, mp.organization_code, mp.master_organization_id, mp.inventory_flag, haoubu.organization_id business_unit_id, mp.business_unit_id inv_org_business_unit_id, mp.profit_center_bu_id profit_center_business_unit_id, haoubu.name business_unit_name, mp.legal_entity_id, XEP.NAME LEGAL_ENTITY_NAME, LGR.LEDGER_ID, LGR.NAME LEDGER_NAME, LGR.ledger_category_code, LGR.CURRENCY_CODE, LGR.PERIOD_SET_NAME, LGR.ACCOUNTED_PERIOD_TYPE, LGR.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID, ccio.cost_org_id COST_ORG_ID, haouco.name COST_ORG_NAME, Decode(mp.master_organization_id, haou.organization_id, 'Y', NULL,NULL, 'N') Master_Organization_Flag FROM hr_all_organization_units_f_vl haou, hr_org_unit_classifications_f houc, INV_ORG_PARAMETERS mp, (SELECT org_information_number1 AS ORG_INFORMATION_NUMBER1, organization_id, org_information_context, to_number(org_information1) AS org_information1, effective_start_date, effective_end_date FROM fusion.hr_organization_information_f WHERE decode((REPLACE(TRANSLATE(TRIM(ORG_INFORMATION_NUMBER1), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254 AND decode((REPLACE(TRANSLATE(TRIM(org_information1), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254) hoifai, GL_LEDGERS LGR, XLE_ENTITY_PROFILES XEP, (SELECT organization_id, org_information_context, to_number(org_information2) AS org_information2, effective_start_date, effective_end_date FROM fusion.hr_organization_information_f WHERE decode((REPLACE(TRANSLATE(TRIM(org_information2), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254) hoiffbu, hr_all_organization_units_f_vl haoubu, cst_cost_inv_orgs ccio, hr_all_organization_units_f_vl haouco WHERE houc.organization_id = haou.organization_id AND mp.organization_id(+) = haou.organization_id AND HAOU.ORGANIZATION_ID = HOIFAI.ORGANIZATION_ID(+) AND HOIFAI.ORG_INFORMATION_CONTEXT(+) = 'CST_ACCOUNTING_INFO' AND XEP.legal_entity_id(+) = HOIFAI.ORG_INFORMATION_NUMBER1 AND to_number(HOIFAI.ORG_INFORMATION1) = LGR.LEDGER_ID(+) AND HAOU.ORGANIZATION_ID = HOIFFBU.ORGANIZATION_ID(+) AND HOIFFBU.ORG_INFORMATION_CONTEXT(+) = 'FUN_BUSINESS_UNIT' AND to_number(HOIFFBU.ORG_INFORMATION2) =haoubu.organization_id(+) AND houc.classification_code in ('CST','INV','FUN_BUSINESS_UNIT','HR_LEGAL') AND TRUNC(sysdate) between haou.effective_start_date and haou.effective_end_date AND TRUNC(sysdate) between hoifai.effective_start_date(+) and hoifai.effective_end_date(+) AND TRUNC(sysdate) between houc.effective_start_date and houc.effective_end_date AND TRUNC(sysdate) between hoiffbu.effective_start_date(+) and hoiffbu.effective_end_date(+) AND TRUNC(sysdate) between haouco.effective_start_date(+) and haouco.effective_end_date(+) AND ccio.inv_org_id(+) = haou.organization_id AND haouco.organization_id(+) = ccio.cost_org_id order by haou.organization_id

SQL_Statement

SELECT distinct haou.organization_id,

haou.name organization_name,

houc.classification_code,

houc.status Status,

mp.organization_code,

mp.master_organization_id,

mp.inventory_flag,

haoubu.organization_id business_unit_id,

mp.business_unit_id inv_org_business_unit_id,

mp.profit_center_bu_id profit_center_business_unit_id,

haoubu.name business_unit_name,

mp.legal_entity_id,

XEP.NAME LEGAL_ENTITY_NAME,

LGR.LEDGER_ID,

LGR.NAME LEDGER_NAME,

LGR.ledger_category_code,

LGR.CURRENCY_CODE,

LGR.PERIOD_SET_NAME,

LGR.ACCOUNTED_PERIOD_TYPE,

LGR.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,

ccio.cost_org_id COST_ORG_ID,

haouco.name COST_ORG_NAME,

Decode(mp.master_organization_id, haou.organization_id, 'Y',

NULL,NULL,

'N') Master_Organization_Flag

FROM hr_all_organization_units_f_vl haou,

hr_org_unit_classifications_f houc,

INV_ORG_PARAMETERS mp,

(SELECT org_information_number1 AS

ORG_INFORMATION_NUMBER1,

organization_id,

org_information_context,

to_number(org_information1) AS

org_information1,

effective_start_date,

effective_end_date

FROM fusion.hr_organization_information_f

WHERE decode((REPLACE(TRANSLATE(TRIM(ORG_INFORMATION_NUMBER1), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254

AND decode((REPLACE(TRANSLATE(TRIM(org_information1), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254)

hoifai,

GL_LEDGERS LGR,

XLE_ENTITY_PROFILES XEP,

(SELECT organization_id,

org_information_context,

to_number(org_information2) AS

org_information2,

effective_start_date,

effective_end_date

FROM fusion.hr_organization_information_f

WHERE decode((REPLACE(TRANSLATE(TRIM(org_information2), '0123456789', '00000000000'), '0', NULL)), NULL, -9876121254, -12345) = -9876121254)

hoiffbu,

hr_all_organization_units_f_vl haoubu,

cst_cost_inv_orgs ccio,

hr_all_organization_units_f_vl haouco

WHERE houc.organization_id = haou.organization_id

AND mp.organization_id(+) = haou.organization_id

AND HAOU.ORGANIZATION_ID = HOIFAI.ORGANIZATION_ID(+)

AND HOIFAI.ORG_INFORMATION_CONTEXT(+) = 'CST_ACCOUNTING_INFO'

AND XEP.legal_entity_id(+) = HOIFAI.ORG_INFORMATION_NUMBER1

AND to_number(HOIFAI.ORG_INFORMATION1) = LGR.LEDGER_ID(+)

AND HAOU.ORGANIZATION_ID = HOIFFBU.ORGANIZATION_ID(+)

AND HOIFFBU.ORG_INFORMATION_CONTEXT(+) = 'FUN_BUSINESS_UNIT'

AND to_number(HOIFFBU.ORG_INFORMATION2) =haoubu.organization_id(+)

AND houc.classification_code in

('CST','INV','FUN_BUSINESS_UNIT','HR_LEGAL')

AND TRUNC(sysdate) between haou.effective_start_date and

haou.effective_end_date

AND TRUNC(sysdate) between hoifai.effective_start_date(+) and

hoifai.effective_end_date(+)

AND TRUNC(sysdate) between houc.effective_start_date and

houc.effective_end_date

AND TRUNC(sysdate) between hoiffbu.effective_start_date(+) and

hoiffbu.effective_end_date(+)

AND TRUNC(sysdate) between haouco.effective_start_date(+) and

haouco.effective_end_date(+)

AND ccio.inv_org_id(+) = haou.organization_id

AND haouco.organization_id(+) = ccio.cost_org_id

order by haou.organization_id