VIEW: APPS.OKE_K_FUND_VERS_SUMMARY_V

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )