View: OKE_K_FUND_VERS_SUMMARY_V

Product:	OKE - Project Contracts
Description:	Funding allocation summary by version view
Implementation/DBA Data:	APPS.OKE_K_FUND_VERS_SUMMARY_V

View Text

SELECT OBJECT_TYPE
, OBJECT_ID
, MAJOR_VERSION
, FUNDING_CURRENCY_CODE
, GROUP_BY1
, GROUP_BY1_CODE
, GROUP_BY2
, GROUP_BY2_CODE
, GROUP_BY3
, GROUP_BY3_CODE
, SUM(F_CURR_INIT_AMOUNT)
, SUM(F_CURR_INCR_AMOUNT)
, SUM(F_CURR_AMOUNT)
, SUM(F_CURR_HARD_LIMIT)
, SUM(F_CURR_REV_HARD_LIMIT)
, SUM(K_CURR_INIT_AMOUNT)
, SUM(K_CURR_INCR_AMOUNT)
, SUM(K_CURR_AMOUNT)
, SUM(K_CURR_HARD_LIMIT)
, SUM(K_CURR_REV_HARD_LIMIT)
FROM ( SELECT S.OBJECT_TYPE
, S.OBJECT_ID
, A.MAJOR_VERSION MAJOR_VERSION
, S.CURRENCY_CODE FUNDING_CURRENCY_CODE
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY1
, 'FUND_TYPE'
, A.FUND_TYPE
, 'FUND_STATUS'
, A.FUNDING_STATUS
, 'PROJECT'
, A.PROJECT
, 'TASK'
, A.TASK
, 'SOURCE'
, S.FUNDING_PARTY
, 'LINE'
, A.CONTRACT_LINE
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY1
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY1
, 'FUND_TYPE'
, A.FUND_TYPE_CODE
, 'FUND_STATUS'
, A.FUNDING_STATUS_CODE
, 'PROJECT'
, A.PROJECT_ID
, 'TASK'
, A.TASK_ID
, 'SOURCE'
, S.K_PARTY_ID
, 'LINE'
, A.K_LINE_ID
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY1_CODE
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY2
, 'FUND_TYPE'
, A.FUND_TYPE
, 'FUND_STATUS'
, A.FUNDING_STATUS
, 'PROJECT'
, A.PROJECT
, 'TASK'
, A.TASK
, 'SOURCE'
, S.FUNDING_PARTY
, 'LINE'
, A.CONTRACT_LINE
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY2
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY2
, 'FUND_TYPE'
, A.FUND_TYPE_CODE
, 'FUND_STATUS'
, A.FUNDING_STATUS_CODE
, 'PROJECT'
, A.PROJECT_ID
, 'TASK'
, A.TASK_ID
, 'SOURCE'
, S.K_PARTY_ID
, 'LINE'
, A.K_LINE_ID
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY2_CODE
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY3
, 'FUND_TYPE'
, A.FUND_TYPE
, 'FUND_STATUS'
, A.FUNDING_STATUS
, 'PROJECT'
, A.PROJECT
, 'TASK'
, A.TASK
, 'SOURCE'
, S.FUNDING_PARTY
, 'LINE'
, A.CONTRACT_LINE
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY3
, DECODE( OKE_FUNDING_INQUIRY.GROUP_BY3
, 'FUND_TYPE'
, A.FUND_TYPE_CODE
, 'FUND_STATUS'
, A.FUNDING_STATUS_CODE
, 'PROJECT'
, A.PROJECT_ID
, 'TASK'
, A.TASK_ID
, 'SOURCE'
, S.K_PARTY_ID
, 'LINE'
, A.K_LINE_ID
, 'FISCAL'
, A.FISCAL_YEAR
, 'REFERENCE1'
, A.REFERENCE1
, 'REFERENCE2'
, A.REFERENCE2
, 'REFERENCE3'
, A.REFERENCE3
, NULL ) GROUP_BY3_CODE
, NVL(A.INIT_AMOUNT
, 0) F_CURR_INIT_AMOUNT
, NVL(A.INCR_AMOUNT
, 0) F_CURR_INCR_AMOUNT
, NVL(A.INIT_AMOUNT
, 0) + NVL(A.INCR_AMOUNT
, 0) F_CURR_AMOUNT
, NVL(A.HARD_LIMIT
, 0) F_CURR_HARD_LIMIT
, NVL(A.REVENUE_HARD_LIMIT
, 0) F_CURR_REV_HARD_LIMIT
, NVL(A.INIT_AMOUNT
, 0) * NVL(S.K_CONVERSION_RATE
, 1) K_CURR_INIT_AMOUNT
, NVL(A.INCR_AMOUNT
, 0) * NVL(S.K_CONVERSION_RATE
, 1) K_CURR_INCR_AMOUNT
, NVL(A.INIT_AMOUNT
, 0) + NVL(A.INCR_AMOUNT
, 0) * NVL(S.K_CONVERSION_RATE
, 1) K_CURR_AMOUNT
, NVL(A.HARD_LIMIT
, 0) * NVL(S.K_CONVERSION_RATE
, 1) K_CURR_HARD_LIMIT
, NVL(A.REVENUE_HARD_LIMIT
, 0) * NVL(S.K_CONVERSION_RATE
, 1) K_CURR_REV_HARD_LIMIT
FROM OKE_K_FUNDING_SOURCES_V S
, ( SELECT FUNDING_SOURCE_ID
, FUND_ALLOCATION_ID
, FUND_TYPE
, FUND_TYPE_CODE
, FUNDING_STATUS
, FUNDING_STATUS_CODE
, PROJECT
, PROJECT_ID
, TASK
, TASK_ID
, CONTRACT_LINE
, K_LINE_ID
, FISCAL_YEAR
, REFERENCE1
, REFERENCE2
, REFERENCE3
, V.MAJOR_VERSION
, DECODE( V.MAJOR_VERSION
, 1
, AMOUNT
, 0 ) INIT_AMOUNT
, DECODE( V.MAJOR_VERSION
, 1
, 0
, AMOUNT ) INCR_AMOUNT
, HARD_LIMIT
, REVENUE_HARD_LIMIT
FROM OKE_K_FUND_ALLOCATIONS_V AC
, OKE_K_VERS_NUMBERS_V V
WHERE V.CHR_ID = AC.OBJECT_ID
AND V.MAJOR_VERSION <= OKE_FUNDING_INQUIRY.MAJOR_VERSION UNION ALL SELECT FUNDING_SOURCE_ID
, FUND_ALLOCATION_ID
, FUND_TYPE
, FUND_TYPE_CODE
, FUNDING_STATUS
, FUNDING_STATUS_CODE
, PROJECT
, PROJECT_ID
, TASK
, TASK_ID
, CONTRACT_LINE
, K_LINE_ID
, FISCAL_YEAR
, REFERENCE1
, REFERENCE2
, REFERENCE3
, MAJOR_VERSION + V.VERSION
, DECODE(V.VERSION
, 0
, DECODE( MAJOR_VERSION
, 1
, AMOUNT
, 0 )
, 0) INIT_AMOUNT
, DECODE(V.VERSION
, 0
, DECODE( MAJOR_VERSION
, 1
, 0
, AMOUNT )
, (-1) * AMOUNT) INCR_AMOUNT
, DECODE(V.VERSION
, 0
, 1
, -1) * HARD_LIMIT
, DECODE(V.VERSION
, 0
, 1
, -1) * REVENUE_HARD_LIMIT
FROM OKE_K_FUND_ALLOCATIONS_HV AH
, ( SELECT LOOKUP_CODE VERSION
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'FUND_INQUIRY_VERSION'
AND VIEW_APPLICATION_ID = 777
AND LANGUAGE = USERENV('LANG')
AND LOOKUP_CODE IN ( 0
, 1 ) ) V
WHERE MAJOR_VERSION + V.VERSION <= OKE_FUNDING_INQUIRY.MAJOR_VERSION ) A
WHERE S.FUNDING_SOURCE_ID = A.FUNDING_SOURCE_ID ) F GROUP BY OBJECT_TYPE
, OBJECT_ID
, FUNDING_CURRENCY_CODE
, MAJOR_VERSION
, GROUP_BY1
, GROUP_BY2
, GROUP_BY3
, GROUP_BY1_CODE
, GROUP_BY2_CODE
, GROUP_BY3_CODE HAVING ( SUM(F_CURR_INIT_AMOUNT) <> 0 OR SUM(F_CURR_INCR_AMOUNT) <> 0 OR SUM(F_CURR_HARD_LIMIT) <> 0 OR SUM(F_CURR_REV_HARD_LIMIT) <> 0 )

Columns