VIEW: APPS.IGC_CC_HEADER_VERSION_V

Source

View Text - Preformatted

SELECT CH.CC_HEADER_ID, CH.ORG_ID, CH.CC_TYPE, CH.CC_NUM, CH.CC_REF_NUM, CH.CC_VERSION_NUM, CH.PARENT_HEADER_ID, CH.CC_STATE, CH.CC_CTRL_STATUS, CH.CC_ENCMBRNC_STATUS, CH.CC_APPRVL_STATUS, CH.VENDOR_ID, CH.VENDOR_SITE_ID, CH.VENDOR_CONTACT_ID, CH.TERM_ID, CH.LOCATION_ID, CH.SET_OF_BOOKS_ID, CH.CC_ACCT_DATE, CH.CC_DESC, CH.CC_START_DATE, CH.CC_END_DATE, CH.CC_OWNER_USER_ID, ohr.full_name, CH.CC_PREPARER_USER_ID, hr.full_name, CH.CURRENCY_CODE, CH.CONVERSION_TYPE, CH.CONVERSION_DATE, CH.CONVERSION_RATE, CH.LAST_UPDATE_DATE, CH.LAST_UPDATED_BY, CH.LAST_UPDATE_LOGIN, CH.CREATED_BY, CH.CREATION_DATE, CH.CC_CURRENT_USER_ID, CH.WF_ITEM_TYPE, CH.WF_ITEM_KEY, CH.CONTEXT, CH.ATTRIBUTE1, CH.ATTRIBUTE2, CH.ATTRIBUTE3, CH.ATTRIBUTE4, CH.ATTRIBUTE5, CH.ATTRIBUTE6, CH.ATTRIBUTE7, CH.ATTRIBUTE8, CH.ATTRIBUTE9, CH.ATTRIBUTE10, CH.ATTRIBUTE11, CH.ATTRIBUTE12, CH.ATTRIBUTE13, CH.ATTRIBUTE14, CH.ATTRIBUTE15, CH.CC_GUARANTEE_FLAG FROM IGC_CC_HEADERS CH, fnd_user fndu, hr_employees hr, fnd_user ofndu, hr_employees ohr WHERE CH.CC_HEADER_ID IN (SELECT CC_HEADER_ID FROM IGC_CC_HEADERS_ALL WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM MINUS SELECT CC_HEADER_ID FROM IGC_CC_HEADER_HISTORY_ALL WHERE (CC_HEADER_ID, CC_VERSION_NUM) IN (SELECT CC_HEADER_ID, MIN(CC_VERSION_NUM) FROM IGC_CC_HEADER_HISTORY_ALL WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM GROUP BY CC_HEADER_ID)) AND fndu.user_id (+) = ch.cc_preparer_user_id AND hr.employee_id (+) = fndu.employee_id AND ofndu.user_id (+) = ch.cc_owner_user_id AND ohr.employee_id (+) = ofndu.employee_id UNION SELECT CHH.CC_HEADER_ID, CHH.ORG_ID, CHH.CC_TYPE, CHH.CC_NUM, CHH.CC_REF_NUM, CHH.CC_VERSION_NUM, CHH.PARENT_HEADER_ID, CHH.CC_STATE, CHH.CC_CTRL_STATUS, CHH.CC_ENCMBRNC_STATUS, CHH.CC_APPRVL_STATUS, CHH.VENDOR_ID, CHH.VENDOR_SITE_ID, CHH.VENDOR_CONTACT_ID, CHH.TERM_ID, CHH.LOCATION_ID, CHH.SET_OF_BOOKS_ID, CHH.CC_ACCT_DATE, CHH.CC_DESC, CHH.CC_START_DATE, CHH.CC_END_DATE, CHH.CC_OWNER_USER_ID, ohr.full_name, CHH.CC_PREPARER_USER_ID, hr.full_name, CHH.CURRENCY_CODE, CHH.CONVERSION_TYPE, CHH.CONVERSION_DATE, CHH.CONVERSION_RATE, CHH.LAST_UPDATE_DATE, CHH.LAST_UPDATED_BY, CHH.LAST_UPDATE_LOGIN, CHH.CREATED_BY, CHH.CREATION_DATE, CHH.CC_CURRENT_USER_ID, CHH.WF_ITEM_TYPE, CHH.WF_ITEM_KEY, CHH.CONTEXT, CHH.ATTRIBUTE1, CHH.ATTRIBUTE2, CHH.ATTRIBUTE3, CHH.ATTRIBUTE4, CHH.ATTRIBUTE5, CHH.ATTRIBUTE6, CHH.ATTRIBUTE7, CHH.ATTRIBUTE8, CHH.ATTRIBUTE9, CHH.ATTRIBUTE10, CHH.ATTRIBUTE11, CHH.ATTRIBUTE12, CHH.ATTRIBUTE13, CHH.ATTRIBUTE14, CHH.ATTRIBUTE15, CHH.CC_GUARANTEE_FLAG FROM IGC_CC_HEADER_HISTORY CHH, fnd_user fndu, hr_employees hr, fnd_user ofndu, hr_employees ohr WHERE (CHH.CC_HEADER_ID, CHH.CC_VERSION_NUM) IN (SELECT CC_HEADER_ID, MIN(CC_VERSION_NUM) FROM IGC_CC_HEADER_HISTORY WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM AND CC_VERSION_ACTION <> 'I' GROUP BY CC_HEADER_ID) AND fndu.user_id (+) = chh.cc_preparer_user_id AND hr.employee_id (+) = fndu.employee_id AND ofndu.user_id (+) = chh.cc_owner_user_id AND ohr.employee_id (+) = ofndu.employee_id

View Text - HTML Formatted

SELECT CH.CC_HEADER_ID
, CH.ORG_ID
, CH.CC_TYPE
, CH.CC_NUM
, CH.CC_REF_NUM
, CH.CC_VERSION_NUM
, CH.PARENT_HEADER_ID
, CH.CC_STATE
, CH.CC_CTRL_STATUS
, CH.CC_ENCMBRNC_STATUS
, CH.CC_APPRVL_STATUS
, CH.VENDOR_ID
, CH.VENDOR_SITE_ID
, CH.VENDOR_CONTACT_ID
, CH.TERM_ID
, CH.LOCATION_ID
, CH.SET_OF_BOOKS_ID
, CH.CC_ACCT_DATE
, CH.CC_DESC
, CH.CC_START_DATE
, CH.CC_END_DATE
, CH.CC_OWNER_USER_ID
, OHR.FULL_NAME
, CH.CC_PREPARER_USER_ID
, HR.FULL_NAME
, CH.CURRENCY_CODE
, CH.CONVERSION_TYPE
, CH.CONVERSION_DATE
, CH.CONVERSION_RATE
, CH.LAST_UPDATE_DATE
, CH.LAST_UPDATED_BY
, CH.LAST_UPDATE_LOGIN
, CH.CREATED_BY
, CH.CREATION_DATE
, CH.CC_CURRENT_USER_ID
, CH.WF_ITEM_TYPE
, CH.WF_ITEM_KEY
, CH.CONTEXT
, CH.ATTRIBUTE1
, CH.ATTRIBUTE2
, CH.ATTRIBUTE3
, CH.ATTRIBUTE4
, CH.ATTRIBUTE5
, CH.ATTRIBUTE6
, CH.ATTRIBUTE7
, CH.ATTRIBUTE8
, CH.ATTRIBUTE9
, CH.ATTRIBUTE10
, CH.ATTRIBUTE11
, CH.ATTRIBUTE12
, CH.ATTRIBUTE13
, CH.ATTRIBUTE14
, CH.ATTRIBUTE15
, CH.CC_GUARANTEE_FLAG
FROM IGC_CC_HEADERS CH
, FND_USER FNDU
, HR_EMPLOYEES HR
, FND_USER OFNDU
, HR_EMPLOYEES OHR
WHERE CH.CC_HEADER_ID IN (SELECT CC_HEADER_ID
FROM IGC_CC_HEADERS_ALL
WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM MINUS SELECT CC_HEADER_ID
FROM IGC_CC_HEADER_HISTORY_ALL
WHERE (CC_HEADER_ID
, CC_VERSION_NUM) IN (SELECT CC_HEADER_ID
, MIN(CC_VERSION_NUM)
FROM IGC_CC_HEADER_HISTORY_ALL
WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM GROUP BY CC_HEADER_ID))
AND FNDU.USER_ID (+) = CH.CC_PREPARER_USER_ID
AND HR.EMPLOYEE_ID (+) = FNDU.EMPLOYEE_ID
AND OFNDU.USER_ID (+) = CH.CC_OWNER_USER_ID
AND OHR.EMPLOYEE_ID (+) = OFNDU.EMPLOYEE_ID UNION SELECT CHH.CC_HEADER_ID
, CHH.ORG_ID
, CHH.CC_TYPE
, CHH.CC_NUM
, CHH.CC_REF_NUM
, CHH.CC_VERSION_NUM
, CHH.PARENT_HEADER_ID
, CHH.CC_STATE
, CHH.CC_CTRL_STATUS
, CHH.CC_ENCMBRNC_STATUS
, CHH.CC_APPRVL_STATUS
, CHH.VENDOR_ID
, CHH.VENDOR_SITE_ID
, CHH.VENDOR_CONTACT_ID
, CHH.TERM_ID
, CHH.LOCATION_ID
, CHH.SET_OF_BOOKS_ID
, CHH.CC_ACCT_DATE
, CHH.CC_DESC
, CHH.CC_START_DATE
, CHH.CC_END_DATE
, CHH.CC_OWNER_USER_ID
, OHR.FULL_NAME
, CHH.CC_PREPARER_USER_ID
, HR.FULL_NAME
, CHH.CURRENCY_CODE
, CHH.CONVERSION_TYPE
, CHH.CONVERSION_DATE
, CHH.CONVERSION_RATE
, CHH.LAST_UPDATE_DATE
, CHH.LAST_UPDATED_BY
, CHH.LAST_UPDATE_LOGIN
, CHH.CREATED_BY
, CHH.CREATION_DATE
, CHH.CC_CURRENT_USER_ID
, CHH.WF_ITEM_TYPE
, CHH.WF_ITEM_KEY
, CHH.CONTEXT
, CHH.ATTRIBUTE1
, CHH.ATTRIBUTE2
, CHH.ATTRIBUTE3
, CHH.ATTRIBUTE4
, CHH.ATTRIBUTE5
, CHH.ATTRIBUTE6
, CHH.ATTRIBUTE7
, CHH.ATTRIBUTE8
, CHH.ATTRIBUTE9
, CHH.ATTRIBUTE10
, CHH.ATTRIBUTE11
, CHH.ATTRIBUTE12
, CHH.ATTRIBUTE13
, CHH.ATTRIBUTE14
, CHH.ATTRIBUTE15
, CHH.CC_GUARANTEE_FLAG
FROM IGC_CC_HEADER_HISTORY CHH
, FND_USER FNDU
, HR_EMPLOYEES HR
, FND_USER OFNDU
, HR_EMPLOYEES OHR
WHERE (CHH.CC_HEADER_ID
, CHH.CC_VERSION_NUM) IN (SELECT CC_HEADER_ID
, MIN(CC_VERSION_NUM)
FROM IGC_CC_HEADER_HISTORY
WHERE CC_VERSION_NUM >= IGC_CC_VERSION_VIEW_PKG.GET_VERSION_NUM
AND CC_VERSION_ACTION <> 'I' GROUP BY CC_HEADER_ID)
AND FNDU.USER_ID (+) = CHH.CC_PREPARER_USER_ID
AND HR.EMPLOYEE_ID (+) = FNDU.EMPLOYEE_ID
AND OFNDU.USER_ID (+) = CHH.CC_OWNER_USER_ID
AND OHR.EMPLOYEE_ID (+) = OFNDU.EMPLOYEE_ID