[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT calendar_code
FROM wsh_calendar_assignments
WHERE customer_id = p_entity_id AND
calendar_type = 'RECEIVING' AND
nvl(location_id, nvl(p_location_id, -1)) = nvl(p_location_id, -1) AND
enabled_flag = 'Y'
ORDER BY location_id;
SELECT calendar_code
FROM wsh_calendar_assignments
WHERE organization_id = p_entity_id AND
calendar_type = 'SHIPPING' AND
nvl(location_id, nvl(p_location_id, -1)) = nvl(p_location_id,-1) AND
enabled_flag = 'Y'
ORDER BY location_id;
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
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
---
AND 'VENDOR' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = p_assoc_type
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
AND ( POVS.VENDOR_SITE_ID = l_site_id OR
WCA.LOCATION_ID = p_location_id)
UNION ALL
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
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'
----
AND 'VENDOR' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = p_assoc_type
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
AND ( WL.WSH_LOCATION_ID = l_site_id OR
WCA.LOCATION_ID = p_location_id)
UNION ALL
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
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'
----
AND 'CUSTOMER' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = p_assoc_type
-- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')--6156495
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
AND ( HCSU.SITE_USE_ID = l_site_id OR
WCA.LOCATION_ID = p_location_id)
UNION ALL
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
WSH_LOCATIONS WLO
WHERE WCA.ASSOCIATION_TYPE = 'HR_LOCATION'
AND WCA.LOCATION_ID = WLO.WSH_LOCATION_ID(+)
----
AND 'ORGANIZATION' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = p_assoc_type
-- AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')---6156495
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
AND (WLO.WSH_LOCATION_ID = l_site_id OR
WCA.LOCATION_ID =p_location_id)
UNION ALL
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
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
----
AND 'CARRIER' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = p_assoc_type
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(p_freight_code,'All')
AND (WCA.CARRIER_SITE_ID = l_site_id OR
WCA.LOCATION_ID =p_location_id);
SELECT WCA.CALENDAR_CODE,WCA.ENABLED_FLAG
FROM HZ_CUST_ACCT_SITES_ALL HCAS,
HZ_CUST_SITE_USES_ALL HCSU,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTY_SITES HPS,
WSH_LOCATIONS WLO,
wsh_calendar_assignments WCA
WHERE HCAS.party_site_id = HPS.party_site_id
AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.cust_account_id = hca.cust_account_id
AND (HCSU.SITE_USE_ID = l_site_id OR
HPS.location_id = p_location_id)
AND WCA.customer_id = HCAS.cust_account_id
AND HPS.LOCATION_ID = WLO.source_location_id(+)
AND WLO.location_source_code(+) ='HZ'
AND WCA.CALENDAR_TYPE = p_calendar_type
AND wca.location_id IS NULL
AND WCA.ASSOCIATION_TYPE = 'CUSTOMER'
AND 'CUSTOMER' = l_entity_type
AND 'CUSTOMER_SITE' = p_assoc_type
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All')
UNION ALL
SELECT WCA.CALENDAR_CODE,
WCA.ENABLED_FLAG
FROM WSH_CALENDAR_ASSIGNMENTS WCA ,
WSH_LOCATIONS WLO,
HR_ORGANIZATION_UNITS HOU
WHERE HOU.LOCATION_ID = WLO.source_LOCATION_ID
And wlo.location_source_code='HR'
AND WLO.wsh_location_id = p_location_id
AND WCA.organization_id = HOU.organization_id
AND 'ORGANIZATION' = l_entity_type
AND WCA.CALENDAR_TYPE = p_calendar_type
AND WCA.ASSOCIATION_TYPE = 'ORGANIZATION'
AND 'HR_LOCATION' = p_assoc_type
AND NVL(WCA.FREIGHT_CODE,'All') = NVL(l_freight_code,'All');