eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: APPS.FIIBV_PA_REVENUE_F_FCV

Source

View Text - Preformatted


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

View Text - HTML Formatted

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