[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_nonbinary_indices.delete;
g_facility_descriptions.Delete;
g_nonindexed_descriptions.delete;
SELECT hou.name company_name,
hou.name site,
hou.creation_date cdate,
'ORGANIZATION' company_type
FROM wsh_locations wl, hr_organization_units hou,
HR_ORGANIZATION_INFORMATION HOI1, MTL_PARAMETERS MP
WHERE wl.source_location_id = p_location_id
AND wl.location_source_code = 'HR'
AND wl.source_location_id = hou.location_id
AND HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
ORDER BY cdate;
SELECT wc.carrier_name Company_Name,
wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date Cdate,
'CARRIER' Company_Type
FROM wsh_locations wl, hz_party_sites hps,
wsh_carriers_v wc
WHERE wl.source_location_id = p_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_id = wc.carrier_id
AND wc.active= 'A'
UNION
--CUSTOMER
SELECT hp.party_name Company_Name,
hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date Cdate,
'CUSTOMER' Company_Type
FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
hz_cust_acct_sites_all hcas
WHERE wl.source_location_id = p_location_id
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hps.party_id=hp.party_id
AND hp.status='A'
AND hcas.party_site_id = hps.party_site_id
UNION
--SUPPLIER
SELECT hp.party_name Company_Name,
hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date Cdate,
'SUPPLIER' Company_Type
FROM wsh_locations wl, po_vendors po, hz_relationships rel,
hz_party_sites hps, hz_parties hp
WHERE wl.source_location_id = p_location_id
AND wl.source_location_id = hps.location_id
AND hps.party_id = hp.party_id
AND hp.status='A'
AND rel.relationship_type = 'POS_VENDOR_PARTY'
AND rel.object_id = hp.party_id
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.object_type = 'ORGANIZATION'
AND rel.subject_table_name = 'PO_VENDORS'
AND rel.subject_id = po.vendor_id
AND rel.subject_type = 'POS_VENDOR'
ORDER BY cdate;
SELECT location_source_code INTO l_hzr
FROM WSH_LOCATIONS
WHERE wsh_location_id = p_location_id;
SELECT hou.name Company_Name,
wl.wsh_location_id LocId,
hou.name Site,
hou.creation_date Cdate,
'ORGANIZATION' Company_Type
FROM wsh_locations wl, hr_organization_units hou,
HR_ORGANIZATION_INFORMATION HOI1, MTL_PARAMETERS MP,fte_location_parameters flp
WHERE wl.wsh_location_id = flp.location_id(+)
AND flp.location_id IS NULL
AND wl.location_source_code = 'HR'
AND wl.source_location_id = hou.location_id
AND HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND ( HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS'
UNION
--CARRIER
SELECT wc.carrier_name Company_Name,
wl.wsh_location_id LocId,
wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date cdate,
'CARRIER' company_type
FROM wsh_locations wl, hz_party_sites hps, wsh_carriers_v wc,
fte_location_parameters flp
WHERE wl.wsh_location_id = flp.location_id(+)
AND flp.location_id IS NULL
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND wc.active = 'A'
AND hps.party_id = wc.carrier_id
UNION
--CUSTOMER
SELECT hp.party_name Company_Name,
wl.wsh_location_id LocId,
hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date Cdate,
'CUSTOMER' Company_Type
FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
hz_cust_acct_sites_all hcas, fte_location_parameters flp,
wsh_location_owners wlo
WHERE wl.wsh_location_id = flp.location_id(+)
AND wl.wsh_location_id = wlo.wsh_location_id
AND wlo.owner_type = 2
AND wlo.owner_party_id = hp.party_id
AND flp.location_id IS NULL
AND wl.location_source_code = 'HZ'
AND wl.source_location_id = hps.location_id
AND hp.status='A'
AND hps.party_id=hp.party_id
AND hcas.party_site_id = hps.party_site_id
UNION
--SUPPLIER
SELECT hp.party_name Company_Name,
wl.wsh_location_id LocId,
hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
hps.creation_date Cdate, 'SUPPLIER' Company_Type
FROM wsh_locations wl, po_vendors po, hz_relationships rel,
hz_party_sites hps, hz_parties hp, fte_location_parameters flp
WHERE wl.wsh_location_id = flp.location_id(+)
AND flp.location_id IS NULL
AND wl.source_location_id = hps.location_id
AND hp.status='A'
AND hps.party_id = hp.party_id
AND rel.relationship_type = 'POS_VENDOR_PARTY'
AND rel.object_id = hp.party_id
AND rel.object_table_name = 'HZ_PARTIES'
AND rel.object_type = 'ORGANIZATION'
AND rel.subject_table_name = 'PO_VENDORS'
AND rel.subject_id = po.vendor_id
AND rel.subject_type = 'POS_VENDOR'
ORDER BY locid, cdate;
SELECT owner_type
FROM wsh_location_owners
WHERE wsh_location_id = c_location_id
AND rownum = 1;
SELECT uom_class
FROM mtl_units_of_measure_vl
WHERE uom_code = c_uom_code;
l_facility_description := 'BAD_FACILITY_DELETE_' || l_loc_id;
logmsg(l_Module_name, 'Inserting Facilities into FTE_LOCATION_PARAMETERS...');
INSERT INTO fte_location_parameters (
FACILITY_ID,
FACILITY_CODE,
LOCATION_ID,
DESCRIPTION,
CONSOLIDATION_ALLOWED,
DECONSOLIDATION_ALLOWED,
CROSSDOCKING_ALLOWED,
PARCEL_LTL_CONSOLIDATION,
PARCEL_TL_CONSOLIDATION,
LTL_TL_CONSOLIDATION,
LTL_LTL_CONSOLIDATION,
LTL_PARCEL_DECONSOLIDATION,
TL_PARCEL_DECONSOLIDATION,
TL_LTL_DECONSOLIDATION,
LTL_LTL_DECONSOLIDATION,
PARCEL_INBOUND_CROSSDOCKING,
LTL_INBOUND_CROSSDOCKING,
TL_INBOUND_CROSSDOCKING,
PARCEL_OUTBOUND_CROSSDOCKING,
LTL_OUTBOUND_CROSSDOCKING,
TL_OUTBOUND_CROSSDOCKING,
STORAGE_FACILITY,
CARRIER_OWNED_HAUL,
FLOW_THROUGH_TIME,
FLOW_THROUGH_TIME_UOM,
NON_ADJACENT_LOADING,
NON_ADJACENT_UNLOADING,
MODIFIER_LIST,
HANDLE_STACKED_PALLETS,
NONPALLETIZED_LOADING_RATE,
NONPALLETIZED_UNLOADING_RATE,
NONPALLETIZED_HANDLING_UOM,
NONPALLETIZED_HANDLING_TYPE,
PALLETIZED_LOADING_RATE,
PALLETIZED_UNLOADING_RATE,
PALLETIZED_HANDLING_UOM,
PALLETIZED_HANDLING_TYPE,
LOAD_UNLOAD_TIME_UOM,
PRIVATE_RESIDENCE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
EFFECTIVE_DATE_FROM,
LOAD_UNLOAD_PROTOCOL,
INCLUDE_MILEAGE_FLAG)
VALUES (
fte_location_parameters_s.nextval,-- FACILITY_ID
l_facility_codes(k), -- FACILITY_CODE
p_location_ids(k), -- LOCATION_ID
l_facility_descriptions(k), -- DESCRIPTION
'N', -- CONSOLIDATION_ALLOWED
'N', -- DECONSOLIDATION_ALLOWED
'N', -- CROSSDOCKING_ALLOWED
'N', -- PARCEL_LTL_CONSOLIDATION
'N', -- PARCEL_TL_CONSOLIDATION
'N', -- LTL_TL_CONSOLIDATION
'N', -- LTL_LTL_CONSOLIDATION
'N', -- LTL_PARCEL_DECONSOLIDATION
'N', -- TL_PARCEL_DECONSOLIDATION
'N', -- TL_LTL_DECONSOLIDATION
'N', -- LTL_LTL_DECONSOLIDATION
'N', -- PARCEL_INBOUND_CROSSDOCKING
'N', -- LTL_INBOUND_CROSSDOCKING
'N', -- TL_INBOUND_CROSSDOCKING
'N', -- PARCEL_OUTBOUND_CROSSDOCKING
'N', -- LTL_OUTBOUND_CROSSDOCKING
'N', -- TL_OUTBOUND_CROSSDOCKING
'N', -- STORAGE_FACILITY
'NEITHER', -- CARRIER_OWNED_HAUL
l_flow_thru_time, -- FLOW_THROUGH_TIME
l_flow_thru_time_uom, -- FLOW_THROUGH_TIME_UOM
'N', -- NON_ADJACENT_LOADING
'N', -- NON_ADJACENT_UNLOADING
NULL, -- MODIFIER_LIST
'Y', -- HANDLE_STACKED_PALLETS
l_NPall_Loading_Rate, -- NONPALLETIZED_LOADING_RATE
l_NPall_Unloading_Rate, -- NONPALLETIZED_UNLOADING_RATE
l_NPall_Handling_Uom, -- NONPALLETIZED_HANDLING_UOM
l_NPall_Handling_type, -- NONPALLETIZED_HANDLING_TYPE
l_Pall_Loading_Rate, -- PALLETIZED_LOADING_RATE
l_Pall_Unloading_Rate, -- PALLETIZED_UNLOADING_RATE
l_Pall_Handling_Uom, -- PALLETIZED_HANDLING_UOM
l_Pall_Handling_type, -- PALLETIZED_HANDLING_TYPE
l_loadUnload_Time_Uom, -- LOAD_UNLOAD_TIME_UOM
l_private_residence, -- PRIVATE_RESIDENCE
sysdate, -- CREATION_DATE
l_userId, -- CREATED_BY
sysdate, -- LAST_UPDATE_DATE
l_userId, -- LAST_UPDATED_BY
sysdate, -- EFFECTIVE_DATE_FROM
l_loadUnload_Protocol, -- LOAD_UNLOAD_PROTOCOL
l_include_mileage_flag(k)); --INCLUDE_MILEAGE_FLAG
logMsg(l_module_name, 'Finished Inserting Facilities ');
DELETE FROM FTE_LOCATION_PARAMETERS
WHERE location_id IN (
SELECT wl.wsh_location_id WSH_LOCATION_ID
FROM hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu, po_location_associations_all pla,
wsh_locations wl
WHERE pla.customer_id = hcas.cust_account_id
AND pla.site_use_id = hcsu.site_use_id
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
AND hps.location_id = wl.source_location_id
AND wl.location_source_code = 'HZ'
);
DELETE FROM FTE_LOCATION_PARAMETERS
WHERE Description LIKE 'BAD_FACILITY_DELETE%';
x_error_msg := 'Successfully inserted '||k||' facilities ';
UPDATE fte_location_parameters
SET facility_code = p_facility_code
WHERE location_id = p_location_id;
SELECT fl.facility_code facility_code,
fl.load_unload_protocol load_unload_protocol,
fl.modifier_list modifier_id,
prc_rc_id.value_from pricelist_id,
prc_cbasis.value_from charge_basis,
prc_cbasis_uom.value_from charge_basis_uom,
prc_currency.value_from currency_code
INTO x_fac_info_rows(k).fac_code,
x_fac_info_rows(k).loading_protocol,
x_fac_info_rows(k).fac_modifier_id,
x_fac_info_rows(k).fac_pricelist_id,
l_charge_basis,
l_charge_basis_uom,
x_fac_info_rows(k).fac_currency
FROM fte_location_parameters fl,
fte_prc_parameters prc_cbasis,
fte_prc_parameters prc_cbasis_uom,
fte_prc_parameters prc_currency,
fte_prc_parameters prc_rc_id
WHERE fl.location_id = x_fac_info_rows(k).location_id
AND prc_cbasis.list_header_id(+) = fl.modifier_list
AND prc_cbasis_uom.list_header_id(+)= fl.modifier_list
AND prc_currency.list_header_id(+) = fl.modifier_list
AND prc_rc_id.list_header_id(+) = fl.modifier_list
AND prc_cbasis.parameter_id(+) = 57
AND prc_cbasis_uom.parameter_id(+) = 58
AND prc_rc_id.parameter_id (+)= 59
AND prc_currency.parameter_id(+) = 60;