VIEW: APPS.OZF_TARGET_CLOB_V

Source

View Text - Preformatted

SELECT ACCT.ACCOUNT_ALLOCATION_ID , OZF_ACCOUNT_HEADER (ACCT.ACCOUNT_ALLOCATION_ID, ACCT.ALLOCATION_FOR, ACCT.ALLOCATION_FOR_ID, ACCT.CUST_ACCOUNT_ID, ACCT.SITE_USE_ID, ACCT.SITE_USE_CODE, ACCT.LOCATION_ID, ACCT.BILL_TO_SITE_USE_ID, ACCT.BILL_TO_LOCATION_ID, ACCT.PARENT_PARTY_ID, ACCT.ROLLUP_PARTY_ID, ACCT.SELECTED_FLAG, ACCT.TARGET, ACCT.LYSP_SALES, ACCT.PARENT_ACCOUNT_ALLOCATION_ID, ACCT.OBJECT_VERSION_NUMBER, ACCT.CREATION_DATE, ACCT.CREATED_BY, ACCT.LAST_UPDATE_DATE, ACCT.LAST_UPDATED_BY, ACCT.LAST_UPDATE_LOGIN, ACCT.ATTRIBUTE_CATEGORY, ACCT.ATTRIBUTE1, ACCT.ATTRIBUTE2, ACCT.ATTRIBUTE3, ACCT.ATTRIBUTE4, ACCT.ATTRIBUTE5, ACCT.ATTRIBUTE6, ACCT.ATTRIBUTE7, ACCT.ATTRIBUTE8, ACCT.ATTRIBUTE9, ACCT.ATTRIBUTE10, ACCT.ATTRIBUTE11, ACCT.ATTRIBUTE12, ACCT.ATTRIBUTE13, ACCT.ATTRIBUTE14, ACCT.ATTRIBUTE15, ACCT.ORG_ID, ACCT.SECURITY_GROUP_ID, OZF_LOCATION_PVT.GET_PARTY_NAME(ACCT.PARENT_PARTY_ID, ACCT.SITE_USE_ID), OZF_LOCATION_PVT.GET_PARTY_NAME(ACCT.ROLLUP_PARTY_ID, ACCT.BILL_TO_SITE_USE_ID), HZ1.PARTY_NAME, HZ2.PARTY_NAME, B.FUND_ID, B.SHORT_NAME, B.FUND_TYPE, B.STATUS_CODE, B.START_PERIOD_ID, B.END_PERIOD_ID, B.START_DATE_ACTIVE, B.END_DATE_ACTIVE, B.HIERARCHY_ID, B.HIERARCHY_LEVEL, B.PARENT_NODE_ID, B.NODE_ID, B.ORIGINAL_BUDGET, B.TRANSFERED_IN_AMT, B.TRANSFERED_OUT_AMT, B.THRESHOLD_ID, B.START_PERIOD_NAME, B.END_PERIOD_NAME, B.PRODUCT_SPREAD_TIME_ID, J.TERR_ID, T.NAME, cast(multiset(select TIME_ALLOCATION_ID, ALLOCATION_FOR, ALLOCATION_FOR_ID, TIME_ID, PERIOD_TYPE_ID, TARGET, LYSP_SALES, OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ORG_ID, SECURITY_GROUP_ID FROM OZF_TIME_ALLOCATIONS WHERE ALLOCATION_FOR = 'CUST' AND ALLOCATION_FOR_ID = PROD1.ALLOCATION_FOR_ID ) AS OZF_TIME_TABLE ) , target.t) target FROM OZF_ACCOUNT_ALLOCATIONS ACCT,OZF_FUNDS_ALL_VL B, JTF_TERR_RSC_ALL J, JTF_TERR_V T, HZ_PARTIES HZ1, HZ_PARTIES HZ2, OZF_PRODUCT_ALLOCATIONS PROD1, (select PROD.ALLOCATION_FOR_ID ALLOCATION_FOR_ID, ozf_time_product_type ( PROD.PRODUCT_ALLOCATION_ID, PROD.ALLOCATION_FOR, PROD.ALLOCATION_FOR_ID, PROD.FUND_ID, PROD.ITEM_TYPE, PROD.ITEM_ID, PROD.SELECTED_FLAG , PROD.TARGET , PROD.LYSP_SALES, PROD.PARENT_PRODUCT_ALLOCATION_ID, PROD.OBJECT_VERSION_NUMBER, PROD.CREATION_DATE , PROD.CREATED_BY, PROD.LAST_UPDATE_DATE , PROD.LAST_UPDATED_BY, PROD.LAST_UPDATE_LOGIN, PROD.ATTRIBUTE_CATEGORY, PROD.ATTRIBUTE1, PROD.ATTRIBUTE2, PROD.ATTRIBUTE3, PROD.ATTRIBUTE4, PROD.ATTRIBUTE5, PROD.ATTRIBUTE6, PROD.ATTRIBUTE7, PROD.ATTRIBUTE8, PROD.ATTRIBUTE9, PROD.ATTRIBUTE10 , PROD.ATTRIBUTE11 , PROD.ATTRIBUTE12 , PROD.ATTRIBUTE13 , PROD.ATTRIBUTE14 , PROD.ATTRIBUTE15 , PROD.ORG_ID , PROD.SECURITY_GROUP_ID, cast(multiset(select TIME_ALLOCATION_ID, ALLOCATION_FOR, ALLOCATION_FOR_ID, TIME_ID, PERIOD_TYPE_ID, TARGET, LYSP_SALES, OBJECT_VERSION_NUMBER, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ORG_ID, SECURITY_GROUP_ID FROM OZF_TIME_ALLOCATIONS WHERE ALLOCATION_FOR = 'PROD' AND ALLOCATION_FOR_ID = PROD.PRODUCT_ALLOCATION_ID ) AS OZF_TIME_TABLE )) t FROM ozf_product_allocations PROD WHERE PROD.ALLOCATION_FOR = 'CUST' ) TARGET WHERE ACCT.ALLOCATION_FOR = 'FUND' AND ACCT.ALLOCATION_FOR_ID = B.FUND_ID AND ACCT.ACCOUNT_ALLOCATION_ID = PROD1.ALLOCATION_FOR_ID AND PROD1.ALLOCATION_FOR = 'CUST' AND target.ALLOCATION_FOR_ID = ACCT.ACCOUNT_ALLOCATION_ID AND B.FUND_TYPE = 'QUOTA' AND J.RESOURCE_ID = B.OWNER AND J.PRIMARY_CONTACT_FLAG = 'Y' AND J.TERR_ID = T.TERR_ID AND ACCT.PARENT_PARTY_ID = HZ1.PARTY_ID AND ACCT.ROLLUP_PARTY_ID = HZ2.PARTY_ID

View Text - HTML Formatted

SELECT ACCT.ACCOUNT_ALLOCATION_ID
, OZF_ACCOUNT_HEADER (ACCT.ACCOUNT_ALLOCATION_ID
, ACCT.ALLOCATION_FOR
, ACCT.ALLOCATION_FOR_ID
, ACCT.CUST_ACCOUNT_ID
, ACCT.SITE_USE_ID
, ACCT.SITE_USE_CODE
, ACCT.LOCATION_ID
, ACCT.BILL_TO_SITE_USE_ID
, ACCT.BILL_TO_LOCATION_ID
, ACCT.PARENT_PARTY_ID
, ACCT.ROLLUP_PARTY_ID
, ACCT.SELECTED_FLAG
, ACCT.TARGET
, ACCT.LYSP_SALES
, ACCT.PARENT_ACCOUNT_ALLOCATION_ID
, ACCT.OBJECT_VERSION_NUMBER
, ACCT.CREATION_DATE
, ACCT.CREATED_BY
, ACCT.LAST_UPDATE_DATE
, ACCT.LAST_UPDATED_BY
, ACCT.LAST_UPDATE_LOGIN
, ACCT.ATTRIBUTE_CATEGORY
, ACCT.ATTRIBUTE1
, ACCT.ATTRIBUTE2
, ACCT.ATTRIBUTE3
, ACCT.ATTRIBUTE4
, ACCT.ATTRIBUTE5
, ACCT.ATTRIBUTE6
, ACCT.ATTRIBUTE7
, ACCT.ATTRIBUTE8
, ACCT.ATTRIBUTE9
, ACCT.ATTRIBUTE10
, ACCT.ATTRIBUTE11
, ACCT.ATTRIBUTE12
, ACCT.ATTRIBUTE13
, ACCT.ATTRIBUTE14
, ACCT.ATTRIBUTE15
, ACCT.ORG_ID
, ACCT.SECURITY_GROUP_ID
, OZF_LOCATION_PVT.GET_PARTY_NAME(ACCT.PARENT_PARTY_ID
, ACCT.SITE_USE_ID)
, OZF_LOCATION_PVT.GET_PARTY_NAME(ACCT.ROLLUP_PARTY_ID
, ACCT.BILL_TO_SITE_USE_ID)
, HZ1.PARTY_NAME
, HZ2.PARTY_NAME
, B.FUND_ID
, B.SHORT_NAME
, B.FUND_TYPE
, B.STATUS_CODE
, B.START_PERIOD_ID
, B.END_PERIOD_ID
, B.START_DATE_ACTIVE
, B.END_DATE_ACTIVE
, B.HIERARCHY_ID
, B.HIERARCHY_LEVEL
, B.PARENT_NODE_ID
, B.NODE_ID
, B.ORIGINAL_BUDGET
, B.TRANSFERED_IN_AMT
, B.TRANSFERED_OUT_AMT
, B.THRESHOLD_ID
, B.START_PERIOD_NAME
, B.END_PERIOD_NAME
, B.PRODUCT_SPREAD_TIME_ID
, J.TERR_ID
, T.NAME
, CAST(MULTISET(SELECT TIME_ALLOCATION_ID
, ALLOCATION_FOR
, ALLOCATION_FOR_ID
, TIME_ID
, PERIOD_TYPE_ID
, TARGET
, LYSP_SALES
, OBJECT_VERSION_NUMBER
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORG_ID
, SECURITY_GROUP_ID
FROM OZF_TIME_ALLOCATIONS
WHERE ALLOCATION_FOR = 'CUST'
AND ALLOCATION_FOR_ID = PROD1.ALLOCATION_FOR_ID ) AS OZF_TIME_TABLE )
, TARGET.T) TARGET
FROM OZF_ACCOUNT_ALLOCATIONS ACCT
, OZF_FUNDS_ALL_VL B
, JTF_TERR_RSC_ALL J
, JTF_TERR_V T
, HZ_PARTIES HZ1
, HZ_PARTIES HZ2
, OZF_PRODUCT_ALLOCATIONS PROD1
, (SELECT PROD.ALLOCATION_FOR_ID ALLOCATION_FOR_ID
, OZF_TIME_PRODUCT_TYPE ( PROD.PRODUCT_ALLOCATION_ID
, PROD.ALLOCATION_FOR
, PROD.ALLOCATION_FOR_ID
, PROD.FUND_ID
, PROD.ITEM_TYPE
, PROD.ITEM_ID
, PROD.SELECTED_FLAG
, PROD.TARGET
, PROD.LYSP_SALES
, PROD.PARENT_PRODUCT_ALLOCATION_ID
, PROD.OBJECT_VERSION_NUMBER
, PROD.CREATION_DATE
, PROD.CREATED_BY
, PROD.LAST_UPDATE_DATE
, PROD.LAST_UPDATED_BY
, PROD.LAST_UPDATE_LOGIN
, PROD.ATTRIBUTE_CATEGORY
, PROD.ATTRIBUTE1
, PROD.ATTRIBUTE2
, PROD.ATTRIBUTE3
, PROD.ATTRIBUTE4
, PROD.ATTRIBUTE5
, PROD.ATTRIBUTE6
, PROD.ATTRIBUTE7
, PROD.ATTRIBUTE8
, PROD.ATTRIBUTE9
, PROD.ATTRIBUTE10
, PROD.ATTRIBUTE11
, PROD.ATTRIBUTE12
, PROD.ATTRIBUTE13
, PROD.ATTRIBUTE14
, PROD.ATTRIBUTE15
, PROD.ORG_ID
, PROD.SECURITY_GROUP_ID
, CAST(MULTISET(SELECT TIME_ALLOCATION_ID
, ALLOCATION_FOR
, ALLOCATION_FOR_ID
, TIME_ID
, PERIOD_TYPE_ID
, TARGET
, LYSP_SALES
, OBJECT_VERSION_NUMBER
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ORG_ID
, SECURITY_GROUP_ID
FROM OZF_TIME_ALLOCATIONS
WHERE ALLOCATION_FOR = 'PROD'
AND ALLOCATION_FOR_ID = PROD.PRODUCT_ALLOCATION_ID ) AS OZF_TIME_TABLE )) T
FROM OZF_PRODUCT_ALLOCATIONS PROD
WHERE PROD.ALLOCATION_FOR = 'CUST' ) TARGET
WHERE ACCT.ALLOCATION_FOR = 'FUND'
AND ACCT.ALLOCATION_FOR_ID = B.FUND_ID
AND ACCT.ACCOUNT_ALLOCATION_ID = PROD1.ALLOCATION_FOR_ID
AND PROD1.ALLOCATION_FOR = 'CUST'
AND TARGET.ALLOCATION_FOR_ID = ACCT.ACCOUNT_ALLOCATION_ID
AND B.FUND_TYPE = 'QUOTA'
AND J.RESOURCE_ID = B.OWNER
AND J.PRIMARY_CONTACT_FLAG = 'Y'
AND J.TERR_ID = T.TERR_ID
AND ACCT.PARENT_PARTY_ID = HZ1.PARTY_ID
AND ACCT.ROLLUP_PARTY_ID = HZ2.PARTY_ID