VIEW: APPS.IGC_CC_HEADERS_V

Source

View Text - Preformatted

SELECT cchd.ROWID, cchd.cc_header_id, cchd.org_id, l.meaning, cchd.cc_type, cchd.cc_num, cchd.cc_ref_num, cchd.cc_version_num, cchd.currency_code, sl.meaning , cchd.cc_state, asl.meaning, cchd.cc_apprvl_status, csl.meaning, cchd.cc_ctrl_status, pv.vendor_name, pv.segment1, cchd.vendor_id, pvs.vendor_site_code, cchd.vendor_site_id, DECODE(cchd.vendor_contact_id, NULL, NULL, pvc.last_name|| ',' || pvc.first_name), cchd.vendor_contact_id, fndu.user_name , cchd.cc_preparer_user_id, hr.full_name, fndu.employee_id, ofndu.user_name, cchd.cc_owner_user_id, ohr.full_name, ofndu.employee_id, atp.name, cchd.term_id, hrl.location_code, cchd.location_id , cchd.cc_desc, cchd.cc_acct_date, cchd.cc_start_date, cchd.cc_end_date, cchd.conversion_type, cchd.conversion_date, cchd.conversion_rate, cchd.parent_header_id, el.meaning, cchd.cc_encmbrnc_status, cchd.set_of_books_id, pcchd.cc_num, pv.hold_flag, cfndu.user_name, cchd.cc_current_user_id, SUBSTR(IGC_CC_ACCESS_PKG.get_access_level(cchd.cc_header_id, FND_GLOBAL.USER_ID, cchd.cc_preparer_user_id, cchd.cc_owner_user_id), 1, 1), cchd.wf_item_type, cchd.wf_item_key, cchd.context, cchd.attribute1, cchd.attribute2, cchd.attribute3, cchd.attribute4, cchd.attribute5, cchd.attribute6, cchd.attribute7, cchd.attribute8, cchd.attribute9, cchd.attribute10, cchd.attribute11, cchd.attribute12, cchd.attribute13, cchd.attribute14, cchd.attribute15, cchd.created_by, cchd.creation_date, cchd.last_updated_by, cchd.last_update_date, cchd.last_update_login , cchd.cc_guarantee_flag FROM igc_cc_headers cchd, fnd_lookups l, fnd_lookups sl, fnd_lookups asl, fnd_lookups csl, fnd_lookups el, po_vendors pv, po_vendor_sites_all pvs, po_vendor_contacts pvc, fnd_user fndu, per_people_f hr, fnd_user ofndu, per_people_f ohr, fnd_user cfndu, ap_terms atp, hr_locations hrl, gl_sets_of_books sob, igc_cc_headers_all pcchd WHERE sob.set_of_books_id (+) = cchd.set_of_books_id AND l.lookup_type (+) = 'IGC_CC_TYPE' AND l.lookup_code (+) = cchd.cc_type AND sl.lookup_type (+) = 'IGC_CC_STATE' AND sl.lookup_code (+) = cchd.cc_state AND asl.lookup_type (+) = 'IGC_CC_APPROVAL_STATUS' AND asl.lookup_code (+) = cchd.cc_apprvl_status AND csl.lookup_type (+) = 'IGC_CC_CONTROL_STATUS' AND csl.lookup_code (+) = cchd.cc_ctrl_status AND el.lookup_type (+) = 'IGC_CC_ENCUMBRANCE_STATUS' AND el.lookup_code (+) = cchd.cc_encmbrnc_status AND pv.vendor_id (+) = cchd.vendor_id AND pvs.vendor_site_id (+) = cchd.vendor_site_id AND pvc.vendor_contact_id (+) = cchd.vendor_contact_id AND fndu.user_id (+) = cchd.cc_preparer_user_id AND hr.person_id (+) = fndu.employee_id AND ofndu.user_id (+) = cchd.cc_owner_user_id AND ohr.person_id (+) = ofndu.employee_id AND cfndu.user_id (+) = cchd.cc_current_user_id AND atp.term_id (+) = cchd.term_id AND hrl.location_id (+) = cchd.location_id AND pcchd.cc_header_id (+) = cchd.parent_header_id AND IGC_CC_ACCESS_PKG.get_access_level(cchd.cc_header_id, FND_GLOBAL.USER_ID,cchd.cc_preparer_user_id, cchd.cc_owner_user_id) IN ('M', 'R') AND ohr.employee_number IS NOT NULL AND TRUNC(sysdate) BETWEEN ohr.effective_start_date AND ohr.effective_end_date AND TRUNC(sysdate) BETWEEN hr.effective_start_date AND hr.effective_end_date AND EXISTS (SELECT 'Asssinment Exisits' FROM per_assignments_f a, per_assignment_status_types past WHERE a.person_id = ohr.person_id AND a.primary_flag = 'Y' AND a.assignment_status_type_id = past.assignment_status_type_id AND past.per_system_status IN('ACTIVE_ASSIGN', 'SUSP_ASSIGN'))

View Text - HTML Formatted

SELECT CCHD.ROWID
, CCHD.CC_HEADER_ID
, CCHD.ORG_ID
, L.MEANING
, CCHD.CC_TYPE
, CCHD.CC_NUM
, CCHD.CC_REF_NUM
, CCHD.CC_VERSION_NUM
, CCHD.CURRENCY_CODE
, SL.MEANING
, CCHD.CC_STATE
, ASL.MEANING
, CCHD.CC_APPRVL_STATUS
, CSL.MEANING
, CCHD.CC_CTRL_STATUS
, PV.VENDOR_NAME
, PV.SEGMENT1
, CCHD.VENDOR_ID
, PVS.VENDOR_SITE_CODE
, CCHD.VENDOR_SITE_ID
, DECODE(CCHD.VENDOR_CONTACT_ID
, NULL
, NULL
, PVC.LAST_NAME|| '
, ' || PVC.FIRST_NAME)
, CCHD.VENDOR_CONTACT_ID
, FNDU.USER_NAME
, CCHD.CC_PREPARER_USER_ID
, HR.FULL_NAME
, FNDU.EMPLOYEE_ID
, OFNDU.USER_NAME
, CCHD.CC_OWNER_USER_ID
, OHR.FULL_NAME
, OFNDU.EMPLOYEE_ID
, ATP.NAME
, CCHD.TERM_ID
, HRL.LOCATION_CODE
, CCHD.LOCATION_ID
, CCHD.CC_DESC
, CCHD.CC_ACCT_DATE
, CCHD.CC_START_DATE
, CCHD.CC_END_DATE
, CCHD.CONVERSION_TYPE
, CCHD.CONVERSION_DATE
, CCHD.CONVERSION_RATE
, CCHD.PARENT_HEADER_ID
, EL.MEANING
, CCHD.CC_ENCMBRNC_STATUS
, CCHD.SET_OF_BOOKS_ID
, PCCHD.CC_NUM
, PV.HOLD_FLAG
, CFNDU.USER_NAME
, CCHD.CC_CURRENT_USER_ID
, SUBSTR(IGC_CC_ACCESS_PKG.GET_ACCESS_LEVEL(CCHD.CC_HEADER_ID
, FND_GLOBAL.USER_ID
, CCHD.CC_PREPARER_USER_ID
, CCHD.CC_OWNER_USER_ID)
, 1
, 1)
, CCHD.WF_ITEM_TYPE
, CCHD.WF_ITEM_KEY
, CCHD.CONTEXT
, CCHD.ATTRIBUTE1
, CCHD.ATTRIBUTE2
, CCHD.ATTRIBUTE3
, CCHD.ATTRIBUTE4
, CCHD.ATTRIBUTE5
, CCHD.ATTRIBUTE6
, CCHD.ATTRIBUTE7
, CCHD.ATTRIBUTE8
, CCHD.ATTRIBUTE9
, CCHD.ATTRIBUTE10
, CCHD.ATTRIBUTE11
, CCHD.ATTRIBUTE12
, CCHD.ATTRIBUTE13
, CCHD.ATTRIBUTE14
, CCHD.ATTRIBUTE15
, CCHD.CREATED_BY
, CCHD.CREATION_DATE
, CCHD.LAST_UPDATED_BY
, CCHD.LAST_UPDATE_DATE
, CCHD.LAST_UPDATE_LOGIN
, CCHD.CC_GUARANTEE_FLAG
FROM IGC_CC_HEADERS CCHD
, FND_LOOKUPS L
, FND_LOOKUPS SL
, FND_LOOKUPS ASL
, FND_LOOKUPS CSL
, FND_LOOKUPS EL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_VENDOR_CONTACTS PVC
, FND_USER FNDU
, PER_PEOPLE_F HR
, FND_USER OFNDU
, PER_PEOPLE_F OHR
, FND_USER CFNDU
, AP_TERMS ATP
, HR_LOCATIONS HRL
, GL_SETS_OF_BOOKS SOB
, IGC_CC_HEADERS_ALL PCCHD
WHERE SOB.SET_OF_BOOKS_ID (+) = CCHD.SET_OF_BOOKS_ID
AND L.LOOKUP_TYPE (+) = 'IGC_CC_TYPE'
AND L.LOOKUP_CODE (+) = CCHD.CC_TYPE
AND SL.LOOKUP_TYPE (+) = 'IGC_CC_STATE'
AND SL.LOOKUP_CODE (+) = CCHD.CC_STATE
AND ASL.LOOKUP_TYPE (+) = 'IGC_CC_APPROVAL_STATUS'
AND ASL.LOOKUP_CODE (+) = CCHD.CC_APPRVL_STATUS
AND CSL.LOOKUP_TYPE (+) = 'IGC_CC_CONTROL_STATUS'
AND CSL.LOOKUP_CODE (+) = CCHD.CC_CTRL_STATUS
AND EL.LOOKUP_TYPE (+) = 'IGC_CC_ENCUMBRANCE_STATUS'
AND EL.LOOKUP_CODE (+) = CCHD.CC_ENCMBRNC_STATUS
AND PV.VENDOR_ID (+) = CCHD.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+) = CCHD.VENDOR_SITE_ID
AND PVC.VENDOR_CONTACT_ID (+) = CCHD.VENDOR_CONTACT_ID
AND FNDU.USER_ID (+) = CCHD.CC_PREPARER_USER_ID
AND HR.PERSON_ID (+) = FNDU.EMPLOYEE_ID
AND OFNDU.USER_ID (+) = CCHD.CC_OWNER_USER_ID
AND OHR.PERSON_ID (+) = OFNDU.EMPLOYEE_ID
AND CFNDU.USER_ID (+) = CCHD.CC_CURRENT_USER_ID
AND ATP.TERM_ID (+) = CCHD.TERM_ID
AND HRL.LOCATION_ID (+) = CCHD.LOCATION_ID
AND PCCHD.CC_HEADER_ID (+) = CCHD.PARENT_HEADER_ID
AND IGC_CC_ACCESS_PKG.GET_ACCESS_LEVEL(CCHD.CC_HEADER_ID
, FND_GLOBAL.USER_ID
, CCHD.CC_PREPARER_USER_ID
, CCHD.CC_OWNER_USER_ID) IN ('M'
, 'R')
AND OHR.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN OHR.EFFECTIVE_START_DATE
AND OHR.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HR.EFFECTIVE_START_DATE
AND HR.EFFECTIVE_END_DATE
AND EXISTS (SELECT 'ASSSINMENT EXISITS'
FROM PER_ASSIGNMENTS_F A
, PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE A.PERSON_ID = OHR.PERSON_ID
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS IN('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN'))