View: FIIBV_PA_REVENUE_F_FCV

View Text

SELECT NULL SEQ_ID
, 'EXP' VIEW_TYPE
, TO_CHAR(RDL.EXPENDITURE_ITEM_ID) || '-' || TO_CHAR(RDL.LINE_NUM) || '-EXP-' || I.INSTANCE_CODE REVENUE_PK
, NULL REVENUE_G
, RDL.AMOUNT REVENUE_B
, I.INSTANCE_CODE INSTANCE_FK
, TO_CHAR(SOB.SET_OF_BOOKS_ID) || '-' || I.INSTANCE_CODE SET_OF_BOOKS_FK
, TO_CHAR(EI.TASK_ID) || '-' || I.INSTANCE_CODE PROJECT_FK
, TO_CHAR( NULL ) PROJECT_ORG_FK
, AG.CUSTOMER_ID || '-' || I.INSTANCE_CODE || '-CUST_ACCT-TPRT' CUSTOMER_FK
, SOB.CURRENCY_CODE CURRENCY_GL_FK
, DECODE ( DR.GL_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(DR.GL_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || SOB.ACCOUNTED_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-CD' ) GL_DATE_FK
, DECODE ( DR.PA_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(DR.PA_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || PI.PA_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-PD') PA_DATE_FK
, DECODE ( EI.EXPENDITURE_ITEM_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(EI.EXPENDITURE_ITEM_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || PI.PA_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-PD' ) TRANSACTION_DATE_FK
, 'NA_EDW' GL_ACCT1_FK
, 'NA_EDW' GL_ACCT2_FK
, 'NA_EDW' GL_ACCT3_FK
, 'NA_EDW' GL_ACCT4_FK
, 'NA_EDW' GL_ACCT5_FK
, 'NA_EDW' GL_ACCT6_FK
, 'NA_EDW' GL_ACCT7_FK
, 'NA_EDW' GL_ACCT8_FK
, 'NA_EDW' GL_ACCT9_FK
, 'NA_EDW' GL_ACCT10_FK
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, SOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, RDL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, NVL(EDW_CURRENCY.GET_RATE( SOB.CURRENCY_CODE
, DR.PA_DATE
, NULL)
, -1) GLOBAL_CURRENCY_RATE
, RDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE
, '_DF:PA:PA_AGREEMENTS_DESC_FLEX:AG'
, '_DF:PA:PA_TASKS_DESC_FLEX:T'
, '_DF:_DUMMY:PA:PA_EVENTS_DESC_FLEX:EV'
, '_DF:PA:PA_EXPENDITURE_ITEMS_DESC_FLEX:EI'
, '_DF:SQLGL:GL_SETS_OF_BOOKS:SOB'
, '_KF:SQLGL:GL#:GCC'
FROM FII_PA_REVENUE_EXP_PK FIIPK
, PA_CUST_REV_DIST_LINES_ALL RDL
, PA_EXPENDITURE_ITEMS_ALL EI
, PA_DRAFT_REVENUES_ALL DR
, PA_AGREEMENTS_ALL AG
, PA_TASKS T
, PA_IMPLEMENTATIONS_ALL PI
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE I
, GL_CODE_COMBINATIONS GCC
WHERE RDL.EXPENDITURE_ITEM_ID = FIIPK.PRIMARY_KEY1
AND RDL.LINE_NUM = FIIPK.PRIMARY_KEY2
AND DR.RELEASED_DATE IS NOT NULL
AND RDL.FUNCTION_CODE NOT IN ('LRL'
, 'LRB'
, 'URL'
, 'URB')
AND DR.PROJECT_ID = RDL.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = RDL.DRAFT_REVENUE_NUM
AND RDL.EXPENDITURE_ITEM_ID = EI.EXPENDITURE_ITEM_ID
AND SOB.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID
AND EI.TASK_ID = T.TASK_ID
AND NVL(RDL.ORG_ID
, -99) = NVL(PI.ORG_ID
, -99)
AND DR.AGREEMENT_ID = AG.AGREEMENT_ID
AND GCC.CODE_COMBINATION_ID = RDL.CODE_COMBINATION_ID UNION ALL SELECT NULL SEQ_ID
, 'EVT' VIEW_TYPE
, TO_CHAR(ERDL.EVENT_NUM) || '-' || TO_CHAR(ERDL.LINE_NUM) || '-' || DECODE( ERDL.TASK_ID
, NULL
, 'NA'
, TO_CHAR(ERDL.TASK_ID) ) || '-' || TO_CHAR(ERDL.PROJECT_ID) || '-EVN-' || I.INSTANCE_CODE REVENUE_PK
, NULL REVENUE_G
, ERDL.AMOUNT REVENUE_B
, I.INSTANCE_CODE INSTANCE_FK
, TO_CHAR(SOB.SET_OF_BOOKS_ID) || '-' || I.INSTANCE_CODE SET_OF_BOOKS_FK
, DECODE( ERDL.TASK_ID
, NULL
, ERDL.PROJECT_ID || '-' || I.INSTANCE_CODE || '-PJ-PRJ'
, ERDL.TASK_ID || '-' || I.INSTANCE_CODE ) PROJECT_FK
, TO_CHAR( NULL ) PROJECT_ORG_FK
, AG.CUSTOMER_ID || '-' || I.INSTANCE_CODE || '-CUST_ACCT-TPRT' CUSTOMER_FK
, SOB.CURRENCY_CODE CURRENCY_GL_FK
, DECODE ( DR.GL_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(DR.GL_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || SOB.ACCOUNTED_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-CD' ) GL_DATE_FK
, DECODE ( DR.PA_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(DR.PA_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || PI.PA_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-PD') PA_DATE_FK
, DECODE ( EV.COMPLETION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(EV.COMPLETION_DATE
, 'DD-MM-YYYY') || '-' || SOB.PERIOD_SET_NAME || '-' || PI.PA_PERIOD_TYPE || '-' || I.INSTANCE_CODE || '-PD') TRANSACTION_DATE_FK
, 'NA_EDW' GL_ACCT1_FK
, 'NA_EDW' GL_ACCT2_FK
, 'NA_EDW' GL_ACCT3_FK
, 'NA_EDW' GL_ACCT4_FK
, 'NA_EDW' GL_ACCT5_FK
, 'NA_EDW' GL_ACCT6_FK
, 'NA_EDW' GL_ACCT7_FK
, 'NA_EDW' GL_ACCT8_FK
, 'NA_EDW' GL_ACCT9_FK
, 'NA_EDW' GL_ACCT10_FK
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, SOB.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID
, ERDL.CODE_COMBINATION_ID CODE_COMBINATION_ID
, NVL(EDW_CURRENCY.GET_RATE( SOB.CURRENCY_CODE
, DR.PA_DATE
, NULL)
, -1) GLOBAL_CURRENCY_RATE
, ERDL.PROGRAM_UPDATE_DATE LAST_UPDATE_DATE
, '_DF:PA:PA_AGREEMENTS_DESC_FLEX:AG'
, '_DF:PA:PA_TASKS_DESC_FLEX:T'
, '_DF:PA:PA_EVENTS_DESC_FLEX:EV'
, '_DF:_DUMMY:PA:PA_EXPENDITURE_ITEMS_DESC_FLEX:EI'
, '_DF:SQLGL:GL_SETS_OF_BOOKS:SOB'
, '_KF:SQLGL:GL#:GCC'
FROM FII_PA_REVENUE_EVT_PK FIIPK
, PA_CUST_EVENT_RDL_ALL ERDL
, PA_DRAFT_REVENUES_ALL DR
, PA_AGREEMENTS_ALL AG
, PA_TASKS T
, PA_PROJECTS_ALL P
, PA_EVENTS EV
, PA_IMPLEMENTATIONS_ALL PI
, GL_SETS_OF_BOOKS SOB
, EDW_LOCAL_INSTANCE I
, GL_CODE_COMBINATIONS GCC
WHERE ERDL.PROJECT_ID = FIIPK.PRIMARY_KEY1
AND ERDL.EVENT_NUM = FIIPK.PRIMARY_KEY2
AND ERDL.LINE_NUM = FIIPK.PRIMARY_KEY3
AND NVL(ERDL.TASK_ID
, -99) = NVL(FIIPK.PRIMARY_KEY4
, -99)
AND DR.RELEASED_DATE IS NOT NULL
AND DR.PROJECT_ID = ERDL.PROJECT_ID
AND DR.DRAFT_REVENUE_NUM = ERDL.DRAFT_REVENUE_NUM
AND ERDL.PROJECT_ID = EV.PROJECT_ID
AND ERDL.EVENT_NUM = EV.EVENT_NUM
AND NVL(ERDL.TASK_ID
, -99) = NVL(EV.TASK_ID
, -99)
AND SOB.SET_OF_BOOKS_ID = PI.SET_OF_BOOKS_ID
AND NVL(ERDL.ORG_ID
, -99) = NVL(PI.ORG_ID
, -99)
AND NVL(ERDL.TASK_ID
, -99) = T.TASK_ID (+)
AND ERDL.PROJECT_ID = P.PROJECT_ID
AND DR.AGREEMENT_ID = AG.AGREEMENT_ID
AND GCC.CODE_COMBINATION_ID = ERDL.CODE_COMBINATION_ID

Columns