VIEW: APPS.AMW_FIN_STMNT_KEY_ACCT_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)