VIEW: APPS.WSH_CAL_ASG_DETAIL_V

Source

View Text - Preformatted

SELECT POV.VENDOR_ID , 'VENDOR' , -1 , NULL , WCA.ASSOCIATION_TYPE , POVS.VENDOR_SITE_CODE || '-' || hr.name , POVS.VENDOR_SITE_ID , WCA.CALENDAR_CODE , WCA.CALENDAR_TYPE , WCA.ENABLED_FLAG , WCA.CALENDAR_ASSIGNMENT_ID , WCA.FREIGHT_CODE , WCA.FREIGHT_ORG_ID , WCA.ATTRIBUTE_CATEGORY , WCA.ATTRIBUTE1 , WCA.ATTRIBUTE2 , WCA.ATTRIBUTE3 , WCA.ATTRIBUTE4 , WCA.ATTRIBUTE5 , WCA.ATTRIBUTE6 , WCA.ATTRIBUTE7 , WCA.ATTRIBUTE8 , WCA.ATTRIBUTE9 , WCA.ATTRIBUTE10 , WCA.ATTRIBUTE11 , WCA.ATTRIBUTE12 , WCA.ATTRIBUTE13 , WCA.ATTRIBUTE14 , WCA.ATTRIBUTE15 , WCA.LOCATION_ID,WCA.CREATION_DATE,WCA.CREATED_BY,WCA.LAST_UPDATE_DATE,WCA.LAST_UPDATED_BY,WCA.LAST_UPDATE_LOGIN FROM WSH_CALENDAR_ASSIGNMENTS WCA , PO_VENDORS POV , PO_VENDOR_SITES_all POVS , hr_organization_units HR WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_SITE' AND WCA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID AND POV.VENDOR_ID = POVS.VENDOR_ID AND hr.organization_id = povs.org_id UNION SELECT REL.OBJECT_ID , 'VENDOR' , -1 , NULL , WCA.ASSOCIATION_TYPE , wl.ui_location_code , wl.wsh_location_id , WCA.CALENDAR_CODE , WCA.CALENDAR_TYPE , WCA.ENABLED_FLAG , WCA.CALENDAR_ASSIGNMENT_ID , WCA.FREIGHT_CODE , WCA.FREIGHT_ORG_ID , WCA.ATTRIBUTE_CATEGORY , WCA.ATTRIBUTE1 , WCA.ATTRIBUTE2 , WCA.ATTRIBUTE3 , WCA.ATTRIBUTE4 , WCA.ATTRIBUTE5 , WCA.ATTRIBUTE6 , WCA.ATTRIBUTE7 , WCA.ATTRIBUTE8 , WCA.ATTRIBUTE9 , WCA.ATTRIBUTE10 , WCA.ATTRIBUTE11 , WCA.ATTRIBUTE12 , WCA.ATTRIBUTE13 , WCA.ATTRIBUTE14 , WCA.ATTRIBUTE15 , WCA.LOCATION_ID, WCA.CREATION_DATE, WCA.CREATED_BY, WCA.LAST_UPDATE_DATE, WCA.LAST_UPDATED_BY, WCA.LAST_UPDATE_LOGIN FROM WSH_CALENDAR_ASSIGNMENTS WCA, hz_relationships rel, hz_party_sites hps, hz_party_site_uses hpsu, wsh_locations wl WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_LOCATION' AND WCA.LOCATION_ID = WL.WSH_LOCATION_ID AND hps.party_id = rel.subject_id AND hps.party_site_id = hpsu.party_site_id AND hps.location_id = wl.wsh_location_id AND site_use_type = 'SUPPLIER_SHIP_FROM' AND rel.relationship_type = 'POS_VENDOR_PARTY' AND rel.object_table_name = 'PO_VENDORS' AND rel.object_type = 'POS_VENDOR' AND rel.subject_table_name = 'HZ_PARTIES' AND rel.subject_type = 'ORGANIZATION' UNION SELECT hca.CUST_ACCOUNT_ID , 'CUSTOMER' , -1 , NULL , WCA.ASSOCIATION_TYPE , WLO.UI_LOCATION_CODE , HCSU.site_use_id , WCA.CALENDAR_CODE , WCA.CALENDAR_TYPE , WCA.ENABLED_FLAG , WCA.CALENDAR_ASSIGNMENT_ID , WCA.FREIGHT_CODE , WCA.FREIGHT_ORG_ID , WCA.ATTRIBUTE_CATEGORY , WCA.ATTRIBUTE1 , WCA.ATTRIBUTE2 , WCA.ATTRIBUTE3 , WCA.ATTRIBUTE4 , WCA.ATTRIBUTE5 , WCA.ATTRIBUTE6 , WCA.ATTRIBUTE7 , WCA.ATTRIBUTE8 , WCA.ATTRIBUTE9 , WCA.ATTRIBUTE10 , WCA.ATTRIBUTE11 , WCA.ATTRIBUTE12 , WCA.ATTRIBUTE13 , WCA.ATTRIBUTE14 , WCA.ATTRIBUTE15 , WCA.LOCATION_ID, WCA.CREATION_DATE, WCA.CREATED_BY, WCA.LAST_UPDATE_DATE, WCA.LAST_UPDATED_BY, WCA.LAST_UPDATE_LOGIN FROM HZ_CUST_ACCT_SITES_ALL HCAS, HZ_CUST_SITE_USES_ALL HCSU, HZ_PARTY_SITES HPS, HZ_CUST_ACCOUNTS HCA, WSH_LOCATIONS WLO, WSH_CALENDAR_ASSIGNMENTS WCA WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID and HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND HCAS.cust_account_id = hca.cust_account_id AND HPS.LOCATION_ID = WLO.source_location_id(+) AND WLO.location_source_code(+) ='HZ' AND WCA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID AND WCA.ASSOCIATION_TYPE = 'CUSTOMER_SITE' UNION SELECT WCA.ORGANIZATION_ID , 'ORGANIZATION' , -1 , NULL , WCA.ASSOCIATION_TYPE , WLO.UI_LOCATION_CODE , WLO.WSH_LOCATION_ID , WCA.CALENDAR_CODE , WCA.CALENDAR_TYPE , WCA.ENABLED_FLAG , WCA.CALENDAR_ASSIGNMENT_ID , WCA.FREIGHT_CODE , WCA.FREIGHT_ORG_ID , WCA.ATTRIBUTE_CATEGORY , WCA.ATTRIBUTE1 , WCA.ATTRIBUTE2 , WCA.ATTRIBUTE3 , WCA.ATTRIBUTE4 , WCA.ATTRIBUTE5 , WCA.ATTRIBUTE6 , WCA.ATTRIBUTE7 , WCA.ATTRIBUTE8 , WCA.ATTRIBUTE9 , WCA.ATTRIBUTE10 , WCA.ATTRIBUTE11 , WCA.ATTRIBUTE12 , WCA.ATTRIBUTE13 , WCA.ATTRIBUTE14 , WCA.ATTRIBUTE15 , WCA.LOCATION_ID, WCA.CREATION_DATE, WCA.CREATED_BY, WCA.LAST_UPDATE_DATE, WCA.LAST_UPDATED_BY, WCA.LAST_UPDATE_LOGIN FROM WSH_CALENDAR_ASSIGNMENTS WCA , WSH_LOCATIONS WLO WHERE WCA.ASSOCIATION_TYPE = 'HR_LOCATION' AND WCA.LOCATION_ID = WLO.WSH_LOCATION_ID(+) UNION SELECT WCA.CARRIER_ID , 'CARRIER' , -1 , NULL , WCA.ASSOCIATION_TYPE , HPS.PARTY_SITE_NUMBER, WCA.CARRIER_SITE_ID, WCA.CALENDAR_CODE , WCA.CALENDAR_TYPE , WCA.ENABLED_FLAG , WCA.CALENDAR_ASSIGNMENT_ID , WCA.FREIGHT_CODE , WCA.FREIGHT_ORG_ID , WCA.ATTRIBUTE_CATEGORY , WCA.ATTRIBUTE1 , WCA.ATTRIBUTE2 , WCA.ATTRIBUTE3 , WCA.ATTRIBUTE4 , WCA.ATTRIBUTE5 , WCA.ATTRIBUTE6 , WCA.ATTRIBUTE7 , WCA.ATTRIBUTE8 , WCA.ATTRIBUTE9 , WCA.ATTRIBUTE10 , WCA.ATTRIBUTE11 , WCA.ATTRIBUTE12 , WCA.ATTRIBUTE13 , WCA.ATTRIBUTE14 , WCA.ATTRIBUTE15 , WCA.LOCATION_ID , WCA.CREATION_DATE, WCA.CREATED_BY, WCA.LAST_UPDATE_DATE, WCA.LAST_UPDATED_BY, WCA.LAST_UPDATE_LOGIN FROM WSH_CALENDAR_ASSIGNMENTS WCA , HZ_PARTY_SITES HPS WHERE WCA.ASSOCIATION_TYPE = 'CARRIER_SITE' AND WCA.CARRIER_ID = HPS.PARTY_ID AND WCA.CARRIER_SITE_ID = HPS.PARTY_SITE_ID

View Text - HTML Formatted

SELECT POV.VENDOR_ID
, 'VENDOR'
, -1
, NULL
, WCA.ASSOCIATION_TYPE
, POVS.VENDOR_SITE_CODE || '-' || HR.NAME
, POVS.VENDOR_SITE_ID
, WCA.CALENDAR_CODE
, WCA.CALENDAR_TYPE
, WCA.ENABLED_FLAG
, WCA.CALENDAR_ASSIGNMENT_ID
, WCA.FREIGHT_CODE
, WCA.FREIGHT_ORG_ID
, WCA.ATTRIBUTE_CATEGORY
, WCA.ATTRIBUTE1
, WCA.ATTRIBUTE2
, WCA.ATTRIBUTE3
, WCA.ATTRIBUTE4
, WCA.ATTRIBUTE5
, WCA.ATTRIBUTE6
, WCA.ATTRIBUTE7
, WCA.ATTRIBUTE8
, WCA.ATTRIBUTE9
, WCA.ATTRIBUTE10
, WCA.ATTRIBUTE11
, WCA.ATTRIBUTE12
, WCA.ATTRIBUTE13
, WCA.ATTRIBUTE14
, WCA.ATTRIBUTE15
, WCA.LOCATION_ID
, WCA.CREATION_DATE
, WCA.CREATED_BY
, WCA.LAST_UPDATE_DATE
, WCA.LAST_UPDATED_BY
, WCA.LAST_UPDATE_LOGIN
FROM WSH_CALENDAR_ASSIGNMENTS WCA
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, HR_ORGANIZATION_UNITS HR
WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_SITE'
AND WCA.VENDOR_SITE_ID = POVS.VENDOR_SITE_ID
AND POV.VENDOR_ID = POVS.VENDOR_ID
AND HR.ORGANIZATION_ID = POVS.ORG_ID UNION SELECT REL.OBJECT_ID
, 'VENDOR'
, -1
, NULL
, WCA.ASSOCIATION_TYPE
, WL.UI_LOCATION_CODE
, WL.WSH_LOCATION_ID
, WCA.CALENDAR_CODE
, WCA.CALENDAR_TYPE
, WCA.ENABLED_FLAG
, WCA.CALENDAR_ASSIGNMENT_ID
, WCA.FREIGHT_CODE
, WCA.FREIGHT_ORG_ID
, WCA.ATTRIBUTE_CATEGORY
, WCA.ATTRIBUTE1
, WCA.ATTRIBUTE2
, WCA.ATTRIBUTE3
, WCA.ATTRIBUTE4
, WCA.ATTRIBUTE5
, WCA.ATTRIBUTE6
, WCA.ATTRIBUTE7
, WCA.ATTRIBUTE8
, WCA.ATTRIBUTE9
, WCA.ATTRIBUTE10
, WCA.ATTRIBUTE11
, WCA.ATTRIBUTE12
, WCA.ATTRIBUTE13
, WCA.ATTRIBUTE14
, WCA.ATTRIBUTE15
, WCA.LOCATION_ID
, WCA.CREATION_DATE
, WCA.CREATED_BY
, WCA.LAST_UPDATE_DATE
, WCA.LAST_UPDATED_BY
, WCA.LAST_UPDATE_LOGIN
FROM WSH_CALENDAR_ASSIGNMENTS WCA
, HZ_RELATIONSHIPS REL
, HZ_PARTY_SITES HPS
, HZ_PARTY_SITE_USES HPSU
, WSH_LOCATIONS WL
WHERE WCA.ASSOCIATION_TYPE = 'VENDOR_LOCATION'
AND WCA.LOCATION_ID = WL.WSH_LOCATION_ID
AND HPS.PARTY_ID = REL.SUBJECT_ID
AND HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID
AND HPS.LOCATION_ID = WL.WSH_LOCATION_ID
AND SITE_USE_TYPE = 'SUPPLIER_SHIP_FROM'
AND REL.RELATIONSHIP_TYPE = 'POS_VENDOR_PARTY'
AND REL.OBJECT_TABLE_NAME = 'PO_VENDORS'
AND REL.OBJECT_TYPE = 'POS_VENDOR'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_TYPE = 'ORGANIZATION' UNION SELECT HCA.CUST_ACCOUNT_ID
, 'CUSTOMER'
, -1
, NULL
, WCA.ASSOCIATION_TYPE
, WLO.UI_LOCATION_CODE
, HCSU.SITE_USE_ID
, WCA.CALENDAR_CODE
, WCA.CALENDAR_TYPE
, WCA.ENABLED_FLAG
, WCA.CALENDAR_ASSIGNMENT_ID
, WCA.FREIGHT_CODE
, WCA.FREIGHT_ORG_ID
, WCA.ATTRIBUTE_CATEGORY
, WCA.ATTRIBUTE1
, WCA.ATTRIBUTE2
, WCA.ATTRIBUTE3
, WCA.ATTRIBUTE4
, WCA.ATTRIBUTE5
, WCA.ATTRIBUTE6
, WCA.ATTRIBUTE7
, WCA.ATTRIBUTE8
, WCA.ATTRIBUTE9
, WCA.ATTRIBUTE10
, WCA.ATTRIBUTE11
, WCA.ATTRIBUTE12
, WCA.ATTRIBUTE13
, WCA.ATTRIBUTE14
, WCA.ATTRIBUTE15
, WCA.LOCATION_ID
, WCA.CREATION_DATE
, WCA.CREATED_BY
, WCA.LAST_UPDATE_DATE
, WCA.LAST_UPDATED_BY
, WCA.LAST_UPDATE_LOGIN
FROM HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSU
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCOUNTS HCA
, WSH_LOCATIONS WLO
, WSH_CALENDAR_ASSIGNMENTS WCA
WHERE HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HPS.LOCATION_ID = WLO.SOURCE_LOCATION_ID(+)
AND WLO.LOCATION_SOURCE_CODE(+) ='HZ'
AND WCA.CUSTOMER_SITE_USE_ID = HCSU.SITE_USE_ID
AND WCA.ASSOCIATION_TYPE = 'CUSTOMER_SITE' UNION SELECT WCA.ORGANIZATION_ID
, 'ORGANIZATION'
, -1
, NULL
, WCA.ASSOCIATION_TYPE
, WLO.UI_LOCATION_CODE
, WLO.WSH_LOCATION_ID
, WCA.CALENDAR_CODE
, WCA.CALENDAR_TYPE
, WCA.ENABLED_FLAG
, WCA.CALENDAR_ASSIGNMENT_ID
, WCA.FREIGHT_CODE
, WCA.FREIGHT_ORG_ID
, WCA.ATTRIBUTE_CATEGORY
, WCA.ATTRIBUTE1
, WCA.ATTRIBUTE2
, WCA.ATTRIBUTE3
, WCA.ATTRIBUTE4
, WCA.ATTRIBUTE5
, WCA.ATTRIBUTE6
, WCA.ATTRIBUTE7
, WCA.ATTRIBUTE8
, WCA.ATTRIBUTE9
, WCA.ATTRIBUTE10
, WCA.ATTRIBUTE11
, WCA.ATTRIBUTE12
, WCA.ATTRIBUTE13
, WCA.ATTRIBUTE14
, WCA.ATTRIBUTE15
, WCA.LOCATION_ID
, WCA.CREATION_DATE
, WCA.CREATED_BY
, WCA.LAST_UPDATE_DATE
, WCA.LAST_UPDATED_BY
, WCA.LAST_UPDATE_LOGIN
FROM WSH_CALENDAR_ASSIGNMENTS WCA
, WSH_LOCATIONS WLO
WHERE WCA.ASSOCIATION_TYPE = 'HR_LOCATION'
AND WCA.LOCATION_ID = WLO.WSH_LOCATION_ID(+) UNION SELECT WCA.CARRIER_ID
, 'CARRIER'
, -1
, NULL
, WCA.ASSOCIATION_TYPE
, HPS.PARTY_SITE_NUMBER
, WCA.CARRIER_SITE_ID
, WCA.CALENDAR_CODE
, WCA.CALENDAR_TYPE
, WCA.ENABLED_FLAG
, WCA.CALENDAR_ASSIGNMENT_ID
, WCA.FREIGHT_CODE
, WCA.FREIGHT_ORG_ID
, WCA.ATTRIBUTE_CATEGORY
, WCA.ATTRIBUTE1
, WCA.ATTRIBUTE2
, WCA.ATTRIBUTE3
, WCA.ATTRIBUTE4
, WCA.ATTRIBUTE5
, WCA.ATTRIBUTE6
, WCA.ATTRIBUTE7
, WCA.ATTRIBUTE8
, WCA.ATTRIBUTE9
, WCA.ATTRIBUTE10
, WCA.ATTRIBUTE11
, WCA.ATTRIBUTE12
, WCA.ATTRIBUTE13
, WCA.ATTRIBUTE14
, WCA.ATTRIBUTE15
, WCA.LOCATION_ID
, WCA.CREATION_DATE
, WCA.CREATED_BY
, WCA.LAST_UPDATE_DATE
, WCA.LAST_UPDATED_BY
, WCA.LAST_UPDATE_LOGIN
FROM WSH_CALENDAR_ASSIGNMENTS WCA
, HZ_PARTY_SITES HPS
WHERE WCA.ASSOCIATION_TYPE = 'CARRIER_SITE'
AND WCA.CARRIER_ID = HPS.PARTY_ID
AND WCA.CARRIER_SITE_ID = HPS.PARTY_SITE_ID