View: AMW_FIN_STMNT_KEY_ACCT_V

Product:	AMW - Internal Controls Manager
Description:	Stores a List of Natural/Key Accounts and how it form the hierarchy within a Financial Statement in Oracle ICM
Implementation/DBA Data:	APPS.AMW_FIN_STMNT_KEY_ACCT_V

View Text

SELECT B.STATEMENT_GROUP_ID
, B.FINANCIAL_STATEMENT_ID
, TL.NAME
, 'FINANCIAL STATEMENT' AS OBJECT_TYPE
, TO_NUMBER(NULL) AS FINANCIAL_ITEM_ID
, TO_NUMBER(NULL) AS PARENT_FINANCIAL_ITEM_ID
, TO_NUMBER(NULL) AS ACCOUNT_GROUP_ID
, TO_NUMBER(NULL) AS NATURAL_ACCOUNT_ID
, TO_NUMBER(NULL) AS PARENT_NATURAL_ACCOUNT_ID
, NULL AS NATURAL_ACCOUNT_VALUE
, 0 AS SEQUENCE_NUMBER
FROM AMW_FIN_STMNT_B B
, AMW_FIN_STMNT_TL TL
WHERE B.STATEMENT_GROUP_ID = TL.STATEMENT_GROUP_ID
AND B.FINANCIAL_STATEMENT_ID = TL.FINANCIAL_STATEMENT_ID
AND TL.LANGUAGE=USERENV('LANG') UNION SELECT B.STATEMENT_GROUP_ID
, B.FINANCIAL_STATEMENT_ID
, TL.NAME
, 'FINANCIAL ITEM' AS OBJECT_TYPE
, B.FINANCIAL_ITEM_ID
, B.PARENT_FINANCIAL_ITEM_ID
, TO_NUMBER(NULL) AS ACCOUNT_GROUP_ID
, TO_NUMBER(NULL) AS NATURAL_ACCOUNT_ID
, TO_NUMBER(NULL) AS PARENT_NATURAL_ACCOUNT_ID
, NULL AS NATURAL_ACCOUNT_VALUE
, B.SEQUENCE_NUMBER
FROM AMW_FIN_STMNT_ITEMS_B B
, AMW_FIN_STMNT_ITEMS_TL TL
WHERE B.STATEMENT_GROUP_ID = TL.STATEMENT_GROUP_ID
AND B.FINANCIAL_STATEMENT_ID = TL.FINANCIAL_STATEMENT_ID
AND B.FINANCIAL_ITEM_ID = TL.FINANCIAL_ITEM_ID
AND TL.LANGUAGE=USERENV('LANG') UNION SELECT FINITEMACC.STATEMENT_GROUP_ID
, FINITEMACC.FINANCIAL_STATEMENT_ID
, TL.NAME
, 'ACCOUNT' AS OBJECT_TYPE
, FINITEMACC.FINANCIAL_ITEM_ID
, FINITEMACC.FINANCIAL_ITEM_ID AS PARENT_FINANCIAL_ITEM_ID
, KEYACCCHILD.ACCOUNT_GROUP_ID
, KEYACCCHILD.NATURAL_ACCOUNT_ID
, KEYACCCHILD.PARENT_NATURAL_ACCOUNT_ID
, KEYACCCHILD.NATURAL_ACCOUNT_VALUE
, 99999 AS SEQUENCE_NUMBER
FROM AMW_FIN_ITEMS_KEY_ACC FINITEMACC
, AMW_FIN_KEY_ACCOUNTS_B KEYACCCHILD
, AMW_FIN_KEY_ACCOUNTS_TL TL
WHERE FINITEMACC.ACCOUNT_GROUP_ID = KEYACCCHILD.ACCOUNT_GROUP_ID
AND TL.LANGUAGE=USERENV('LANG')
AND TL.NATURAL_ACCOUNT_ID=KEYACCCHILD.NATURAL_ACCOUNT_ID
AND TL.ACCOUNT_GROUP_ID = KEYACCCHILD.ACCOUNT_GROUP_ID
AND (KEYACCCHILD.ACCOUNT_GROUP_ID
, KEYACCCHILD.NATURAL_ACCOUNT_ID
, NVL(KEYACCCHILD.PARENT_NATURAL_ACCOUNT_ID
, -1)) IN (SELECT KEYACC.ACCOUNT_GROUP_ID
, KEYACC.NATURAL_ACCOUNT_ID
, NVL(KEYACC.PARENT_NATURAL_ACCOUNT_ID
, -1)
FROM AMW_FIN_KEY_ACCOUNTS_B KEYACC START WITH KEYACC.NATURAL_ACCOUNT_ID = FINITEMACC.NATURAL_ACCOUNT_ID
AND KEYACC.ACCOUNT_GROUP_ID = FINITEMACC.ACCOUNT_GROUP_ID
AND KEYACC.PARENT_NATURAL_ACCOUNT_ID IS NULL CONNECT BY PRIOR KEYACC.NATURAL_ACCOUNT_ID = KEYACC.PARENT_NATURAL_ACCOUNT_ID
AND PRIOR KEYACC.ACCOUNT_GROUP_ID = KEYACC.ACCOUNT_GROUP_ID)

Columns