[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT location_id
into l_location_id
--bug 3346564
--from HR_ORGANIZATION_UNITS
from HR_ALL_ORGANIZATION_UNITS
where organization_id = p_organization_id;
select location_id
into l_location_id
from PO_LOCATION_ASSOCIATIONS
where SITE_USE_ID = p_customer_site_id;
select intransit_time
into l_intransit_time
from mtl_interorg_ship_methods
where from_location_id = p_from_location_id
and to_location_id = p_to_location_id
and ship_method = p_ship_method
and rownum = 1;
-- ngoel 9/25/2001, need to select most specific lead time based on regions
-- bug 2974334. Change the SQL into static.
SELECT intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
INTO l_intransit_time, l_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = p_from_location_id
AND mism.ship_method = p_ship_method
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_partner_site_id
ORDER BY 2;
SELECT ship_method
INTO l_ship_method
FROM mtl_interorg_ship_methods
WHERE from_location_id = p_from_location_id
AND to_location_id = p_to_location_id
AND default_flag = 1
AND rownum = 1;
SELECT ship_method,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
INTO l_ship_method, l_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = p_from_location_id
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_partner_site_id
AND default_flag = 1
ORDER BY 2;
select ship_method
into l_ship_method
from mtl_interorg_ship_methods
where from_organization_id = p_from_org_id
and to_organization_id = p_to_org_id
and default_flag = 1
and rownum = 1;
SELECT intransit_time
INTO l_intransit_time
FROM mtl_interorg_ship_methods
WHERE from_location_id = p_from_location_id
AND to_location_id = p_to_location_id
AND default_flag = 1
AND rownum = 1;
SELECT intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
INTO l_intransit_time, l_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = p_from_location_id
AND mism.default_flag = 1
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_partner_site_id
ORDER BY 2;
select intransit_time
into l_intransit_time
from mtl_interorg_ship_methods
where from_organization_id = p_from_org_id
and to_organization_id = p_to_org_id
and default_flag = 1
and rownum = 1;
SELECT cal.prior_date
INTO l_return_date
FROM bom_calendar_dates cal,
mtl_parameters org
WHERE cal.calendar_code = org.calendar_code
AND cal.exception_set_id = org.calendar_exception_set_id
AND cal.calendar_date = TRUNC(p_date)
AND org.organization_id = p_organization_id;
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES cal,
mtl_parameters org
WHERE cal.calendar_code = org.calendar_code
AND cal.exception_set_id = org.calendar_exception_set_id
AND org.organization_id = p_organization_id
AND cal.seq_num is not null;
SELECT cal.next_date
INTO l_return_date
FROM bom_calendar_dates cal,
mtl_parameters org
WHERE cal.calendar_code = org.calendar_code
AND cal.exception_set_id = org.calendar_exception_set_id
AND cal.calendar_date = TRUNC(p_date)
AND org.organization_id = p_organization_id;
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES cal,
mtl_parameters org
WHERE cal.calendar_code = org.calendar_code
AND cal.exception_set_id = org.calendar_exception_set_id
AND org.organization_id = p_organization_id
AND cal.seq_num is not null;
SELECT cal2.calendar_date
INTO l_return_date
FROM bom_calendar_dates cal1,
bom_calendar_dates cal2,
mtl_parameters org
WHERE cal1.calendar_code = org.calendar_code
AND cal1.exception_set_id = org.calendar_exception_set_id
AND cal1.calendar_date = TRUNC(p_date)
AND org.organization_id = p_organization_id
AND cal2.exception_set_id = cal1.exception_set_id
AND cal2.calendar_code = cal1.calendar_code
AND cal2.seq_num = cal1.prior_seq_num + l_days;
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES cal,
mtl_parameters org
WHERE cal.calendar_code = org.calendar_code
AND cal.exception_set_id = org.calendar_exception_set_id
AND org.organization_id = p_organization_id
AND cal.seq_num is not null;
p_atp_tab.Insert_Flag.Extend(p_index);
p_atp_tab.ato_delete_flag.Extend(p_index); -- added by ngoel 6/15/2001
x_atp_table.Insert_Flag(l_count + 1):= p_atp_table.Insert_Flag(p_index);
IF p_atp_table.ato_delete_flag.Exists(p_index) THEN
x_atp_table.ato_delete_flag(l_count + 1):=
p_atp_table.ato_delete_flag(p_index);
x_atp_table.Insert_Flag(l_count + l_atp_count):=
p_atp_table.Insert_Flag(l_atp_count);
x_atp_table.ato_delete_flag(l_count + l_atp_count):=
p_atp_table.ato_delete_flag(l_atp_count);
/* Old Select statement -- Incorrect and hence commented out.
SELECT a.postal_code, a.city, a.state, a.country
INTO l_postal_code, l_city, l_state, l_country
FROM hz_locations a, hz_party_sites s
WHERE a.location_id = s.location_id
AND s.party_site_id = p_customer_site_id;
/* New Select Statement */
-- For bug 2732267 select province if state is not specified
IF ((p_country is not null) --2814895, use address parameter directly when they are passed by calling module
AND (p_customer_site_id is NULL)) THEN
l_postal_code := p_postal_code;
SELECT LOC.POSTAL_CODE, LOC.CITY, NVL(LOC.STATE, LOC.PROVINCE), LOC.COUNTRY
INTO l_postal_code, l_city, l_state, l_country
FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE_USES_ALL.site_use_id = p_customer_site_id;
l_stmt := 'SELECT region_id
FROM WSH_REGIONS_V';
l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
-- SELECT :p_session_id, commented for performance tuning bug 2484964
(SELECT DISTINCT :p_session_id,
:p_customer_site_id,
region_id,
region_type,
''N'',
:partner_type
FROM WSH_REGIONS
START WITH region_id = :l_region_id
CONNECT BY PRIOR parent_region_id = region_id)';
l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT :p_session_id,
:l_partner_site_id,
a.region_id,
a.zone_level,
''Y'',
:partner_type
FROM WSH_REGIONS a, WSH_ZONE_REGIONS b
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND b.region_id IN (
SELECT c.region_id
FROM msc_regions_temp' || l_dynstring || ' c
WHERE c.session_id = :p_session_id1
AND c.partner_site_id = :p_partner_site_id1
AND c.partner_type = :partner_type1)';
SELECT mtps.postal_code, mtps.city, mtps.state, mtps.country
INTO l_postal_code, l_city, l_state, l_country
FROM msc_trading_partner_sites mtps,
msc_tp_site_id_lid tpsid
WHERE tpsid.sr_tp_site_id = p_customer_site_id
AND tpsid.sr_instance_id = p_instance_id
AND rownum = 1
AND tpsid.partner_type = 2
AND tpsid.tp_site_id = mtps.partner_site_id;
l_stmt := 'SELECT region_id
FROM MSC_REGIONS
WHERE sr_instance_id = :p_instance_id';
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
-- Begin Bug 2498174
-- Changed Query to enhance performance
SELECT DISTINCT p_session_id,
p_customer_site_id,
region_id,
region_type,
'N',
l_customer_type -- For supplier intransit LT project
FROM MSC_REGIONS
WHERE sr_instance_id = p_instance_id
START WITH region_id = l_region_id
CONNECT BY PRIOR parent_region_id = region_id;
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT p_session_id,
p_customer_site_id,
a.region_id,
a.zone_level,
'Y',
l_customer_type -- For supplier intransit LT project
FROM MSC_REGIONS_TEMP c, MSC_ZONE_REGIONS b, MSC_REGIONS a
WHERE a.region_id = b.parent_region_id
AND c.region_id = b.region_id
AND a.sr_instance_id = b.sr_instance_id
AND b.sr_instance_id = p_instance_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND c.session_id = p_session_id
AND c.partner_site_id = p_customer_site_id
AND c.partner_type = l_customer_type; -- For supplier intransit LT project
/* bug 3425497: First insert into table locally and then transfer over dblink
IF p_dblink IS NOT NULL THEN
-- also update Partner_type for supplier intransit LT project
l_stmt := ' INSERT INTO msc_regions_temp' || l_dynstring ||
' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT :p_session_id,
:p_customer_site_id1,
region_id,
region_type,
:l_NO,
:partner_type
FROM WSH_REGION_LOCATIONS
WHERE location_id IN
(SELECT LOC.LOCATION_ID
FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE_USES_ALL.site_use_id = :p_customer_site_id
)
AND location_source = :l_HZ
AND region_id is not null'; -- 2837468
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT p_session_id, p_customer_site_id, region_id, region_type, l_NO, l_customer_type
FROM WSH_REGION_LOCATIONS
WHERE location_id IN
(SELECT LOC.LOCATION_ID
FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE_USES_ALL.site_use_id = p_customer_site_id
)
AND location_source = l_HZ
AND region_id is not null; -- 2837468
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT p_session_id, p_customer_site_id, wrl.region_id, wrl.region_type, l_NO, l_partner_type --2814895
FROM WSH_REGION_LOCATIONS WRL,
HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
WHERE WRL.location_id = LOC.LOCATION_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
AND SITE_USES_ALL.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SITE_USES_ALL.site_use_id = p_customer_site_id
AND WRL.location_source = l_HZ
AND WRL.region_id is not null; -- 2837468
INSERT INTO msc_regions_temp
(session_id,partner_site_id,region_id,region_type,zone_flag, partner_type) --2814895
SELECT p_session_id,p_party_site_id,wrl.region_id,wrl.region_type,l_NO,l_partner_type
FROM WSH_REGION_LOCATIONS WRL,
HZ_PARTY_SITES PARTY_SITE
WHERE WRL.location_id = PARTY_SITE.LOCATION_ID
AND PARTY_SITE.party_site_id = p_party_site_id
AND WRL.location_source = l_HZ
AND WRL.region_id is not null;
/* 3425497: first insert locally.
IF p_dblink IS NOT NULL THEN
-- also update Partner_type for supplier intransit LT project
l_stmt := 'INSERT INTO msc_regions_temp' || l_dynstring ||
' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT :p_session_id,
-1,
region_id,
region_type,
:l_NO,
:partner_type
FROM WSH_REGION_LOCATIONS
WHERE location_id = :p_location_id
AND location_source = :p_location_source
AND region_id is not null'; --2837468
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type
FROM WSH_REGION_LOCATIONS
WHERE location_id = p_location_id
AND location_source = p_location_source
AND region_id is not null; --2837468
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT p_session_id, -1, region_id, region_type, l_NO, l_customer_type --2814895
FROM WSH_REGION_LOCATIONS
WHERE location_id = p_location_id
AND location_source = p_location_source
AND region_id is not null; --2837468
/* 3425497: Insert data locally
IF p_dblink IS NOT NULL THEN
-- also update Partner_type for supplier intransit LT project
-- partner_type is also included in the where clause
l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
' (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT DISTINCT :p_session_id,
:p_customer_site_id,
a.region_id,
a.zone_level,
:l_YES,
:partner_type
FROM WSH_REGIONS a, WSH_ZONE_REGIONS b,
MSC_REGIONS_TEMP' || l_dynstring || ' c
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND b.region_id = c.region_id
AND c.session_id = :p_session_id1
AND c.partner_site_id = :p_customer_site_id1
AND c.partner_type = :partner_type1'; -- For supplier intransit LT project
INSERT into msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT DISTINCT p_session_id,
p_customer_site_id,
a.region_id,
a.zone_level,
l_YES,
l_customer_type
FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND b.region_id = c.region_id
AND c.session_id = p_session_id
AND c.partner_site_id = p_customer_site_id
AND c.partner_type = l_customer_type; -- For supplier intransit LT project
INSERT into msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT DISTINCT p_session_id,
p_customer_site_id,
a.region_id,
a.zone_level,
l_YES,
l_customer_type
FROM WSH_REGIONS a, WSH_ZONE_REGIONS b, MSC_REGIONS_TEMP c
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND b.region_id = c.region_id
AND c.session_id = p_session_id
AND c.partner_site_id = decode(l_partner_type, l_customer_type, p_customer_site_id, p_party_site_id) --2814895
AND c.partner_type = l_partner_type; -- 2814895 -- For supplier intransit LT project
l_stmt:= 'INSERT into msc_regions_temp' || l_dynstring ||
' (SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE)
select SESSION_ID, PARTNER_SITE_ID, REGION_ID, REGION_TYPE, ZONE_FLAG, PARTNER_TYPE
from msc_regions_temp
where session_id = :p_session_id';
delete msc_regions_temp where session_id = p_session_id;
insert into msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
select p_session_id,
p_customer_site_id,
region_id,
region_type,
'N',
l_customer_type
from msc_region_locations
where location_id in (
select location_id
from msc_tp_site_id_lid tpsid
where tpsid.sr_instance_id = p_instance_id
and tpsid.sr_tp_site_id = p_customer_site_id
and tpsid.partner_type = 2
)
and sr_instance_id = p_instance_id
and region_id is not null
and location_source = 'HZ';
insert into msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
select p_session_id,
p_customer_site_id,
mrl.region_id,
mrl.region_type,
'N',
l_customer_type
from msc_region_locations mrl,
msc_tp_site_id_lid tpsid
where mrl.location_id = tpsid.location_id
and tpsid.sr_instance_id = p_instance_id
and tpsid.sr_tp_site_id = p_customer_site_id
and tpsid.partner_type = 2
and mrl.sr_instance_id = p_instance_id
and mrl.region_id is not null
and mrl.location_source = 'HZ';
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT DISTINCT p_session_id,
p_customer_site_id,
a.region_id,
a.zone_level,
'Y',
l_customer_type
FROM MSC_REGIONS a, MSC_ZONE_REGIONS b
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND a.sr_instance_id = b.sr_instance_id
and b.sr_instance_id = p_instance_id
AND b.region_id IN (
SELECT c.region_id
FROM msc_regions_temp c
WHERE c.session_id = p_session_id
AND c.partner_site_id = p_customer_site_id
AND c.partner_type = l_customer_type -- For supplier intransit LT project
);
INSERT INTO msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
SELECT DISTINCT p_session_id,
p_customer_site_id,
a.region_id,
a.zone_level,
'Y',
l_customer_type
FROM MSC_REGIONS a, MSC_ZONE_REGIONS b, msc_regions_temp c
WHERE a.region_id = b.parent_region_id
AND a.region_type = 10
AND a.zone_level IS NOT NULL
AND a.sr_instance_id = b.sr_instance_id
and b.sr_instance_id = p_instance_id
AND b.region_id = c.region_id
AND c.session_id = p_session_id
AND c.partner_site_id = p_customer_site_id
AND c.partner_type = l_customer_type -- For supplier intransit LT project
;
insert into msc_regions_temp
(session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
select p_session_id,
p_supplier_site_id,
region_id,
region_type,
null, -- not required anymore because collected data is already translated
l_vendor_type
from msc_region_sites
where vendor_site_id = p_supplier_site_id
and sr_instance_id = p_instance_id;
delete msc_regions_temp where session_id = p_session_id;
SELECT intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = p_from_loc_id
AND mism.ship_method = x_ship_method
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
--AND mrt.partner_type = 2 -- For supplier intransit LT project
AND mrt.partner_site_id = p_partner_site_id --2814895
AND mrt.partner_type = NVL(p_partner_type,2)
ORDER BY 2;
SELECT ship_method, intransit_time,
((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM mtl_interorg_ship_methods mism,
msc_regions_temp mrt
WHERE mism.from_location_id = p_from_loc_id
AND mism.default_flag = 1
AND mism.to_region_id = mrt.region_id
AND mrt.session_id = p_session_id
AND mrt.partner_site_id = p_partner_site_id
--2814895
AND mrt.partner_type = NVL(p_partner_type,2)
--AND mrt.partner_type = 2 -- For supplier intransit LT project
ORDER BY 3; -- was earlier ordered wrongly by 2. changed it to 3 along with supplier intransit LT changes
SELECT intransit_time
INTO x_intransit_time
FROM mtl_interorg_ship_methods
WHERE from_location_id = p_from_loc_id
AND to_location_id = p_to_loc_id
AND ship_method = x_ship_method
AND rownum = 1;
SELECT ship_method, intransit_time
INTO x_ship_method, x_intransit_time
FROM mtl_interorg_ship_methods
WHERE from_location_id = p_from_loc_id
AND to_location_id = p_to_loc_id
AND default_flag = 1
AND rownum = 1;
select intransit_time
into x_intransit_time
from mtl_interorg_ship_methods
where from_organization_id = p_from_org_id
and to_organization_id = p_to_org_id
and ship_method = x_ship_method
and rownum = 1;
select ship_method, intransit_time
into x_ship_method, x_intransit_time
from mtl_interorg_ship_methods
where from_organization_id = p_from_org_id
and to_organization_id = p_to_org_id
and default_flag = 1
and rownum = 1;
select 1
into l_temp_var
from msc_region_locations
where sr_instance_id = p_instance_id
and rownum = 1;
select 1
into l_temp_var
from wsh_region_locations
where rownum = 1;
reclength := p_atp_tab.Insert_Flag.count;
p_atp_tab.Insert_Flag.extend (totlength - reclength);
reclength := p_atp_tab.ato_delete_flag.count;
p_atp_tab.ato_delete_flag.extend (totlength - reclength);
SELECT calendar_code
INTO l_calendar_code
FROM (SELECT wca.CALENDAR_CODE
FROM WSH_CARRIERS wc,
WSH_CALENDAR_ASSIGNMENTS wca,
WSH_CARRIER_SERVICES wcs,
WSH_CARRIER_SERVICES wcs1
WHERE wc.FREIGHT_CODE(+) = wca.FREIGHT_CODE
AND wc.CARRIER_ID = wcs.CARRIER_ID(+)
AND wca.CARRIER_ID = wcs1.CARRIER_ID(+)
AND wca.ENABLED_FLAG = 'Y'
AND wca.CUSTOMER_ID = p_customer_id
AND wca.CALENDAR_TYPE in ('RECEIVING', 'CARRIER')
AND NVL(wca.CUSTOMER_SITE_USE_ID, l_customer_site_id) = l_customer_site_id
AND NVL(decode(wca.ASSOCIATION_TYPE,
'CARRIER',wcs1.SHIP_METHOD_CODE,
'CARRIER_SITE',wcs1.SHIP_METHOD_CODE,
wcs.SHIP_METHOD_CODE),
l_ship_method_code) = l_ship_method_code
AND wca.ASSOCIATION_TYPE in ('VENDOR_SITE','CUSTOMER_SITE','VENDOR','CUSTOMER','ORGANIZATION','CARRIER')
ORDER BY LENGTH(decode(wca.association_type,
'CUSTOMER', decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_CUSTOMER','CUSTOMER'),
'CUSTOMER_SITE',decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_CUSTOMER_SITE','CUSTOMER_SITE'))) DESC)
WHERE ROWNUM = 1;
SELECT calendar_code
INTO l_calendar_code
FROM (SELECT wca.CALENDAR_CODE
FROM WSH_CARRIERS wc,
WSH_CALENDAR_ASSIGNMENTS wca,
WSH_CARRIER_SERVICES wcs,
WSH_CARRIER_SERVICES wcs1
WHERE wc.FREIGHT_CODE(+) = wca.FREIGHT_CODE
AND wc.CARRIER_ID = wcs.CARRIER_ID(+)
AND wca.CARRIER_ID = wcs1.CARRIER_ID(+)
AND wca.ENABLED_FLAG = 'Y'
AND wca.ORGANIZATION_ID = p_organization_id
AND wca.CALENDAR_TYPE in (l_calendar_type, 'CARRIER') -- Bug 3449812
AND NVL(decode(wca.ASSOCIATION_TYPE,
'CARRIER',wcs1.SHIP_METHOD_CODE,
'CARRIER_SITE',wcs1.SHIP_METHOD_CODE,
wcs.SHIP_METHOD_CODE),
l_ship_method_code) = l_ship_method_code
AND wca.ASSOCIATION_TYPE in ('VENDOR_SITE','CUSTOMER_SITE','VENDOR','CUSTOMER','ORGANIZATION','CARRIER')
ORDER BY LENGTH(decode(wca.association_type,
'ORGANIZATION', decode(wca.CALENDAR_TYPE,'CARRIER','CARRIER_ORGANIZATION','ORGANIZATION'))) DESC)
WHERE ROWNUM = 1;
SELECT wca.CALENDAR_CODE
INTO l_calendar_code
FROM WSH_CALENDAR_ASSIGNMENTS wca,
WSH_CARRIER_SERVICES wcs
WHERE wca.CARRIER_ID = wcs.CARRIER_ID
AND wca.CALENDAR_TYPE = 'CARRIER'
AND wca.ASSOCIATION_TYPE = 'CARRIER'
AND wca.ENABLED_FLAG = 'Y'
AND NVL(wcs.SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code;
SELECT calendar_code
INTO l_calendar_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id
AND CALENDAR_EXCEPTION_SET_ID = -1;
SELECT NEXT_DATE
INTO l_next_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND EXCEPTION_SET_ID = -1
AND CALENDAR_DATE = TRUNC(p_calendar_date);
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SEQ_NUM is not null;
SELECT PRIOR_DATE
INTO l_prev_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND EXCEPTION_SET_ID = -1
AND CALENDAR_DATE = TRUNC(p_calendar_date);
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SEQ_NUM is not null;
SELECT cal2.calendar_date
INTO l_offsetted_day
FROM BOM_CALENDAR_DATES cal1, BOM_CALENDAR_DATES cal2
WHERE cal1.calendar_code = p_calendar_code
AND cal1.exception_set_id = -1
AND cal1.calendar_date = TRUNC(p_calendar_date)
AND cal2.calendar_code = cal1.calendar_code
AND cal2.exception_set_id = cal1.exception_set_id
AND cal2.seq_num = cal1.prior_seq_num + l_days_offset; --bug3558412
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SEQ_NUM is not null;
SELECT cal2.calendar_date
INTO l_offsetted_day
FROM BOM_CALENDAR_DATES cal1, BOM_CALENDAR_DATES cal2
WHERE cal1.calendar_code = p_calendar_code
AND cal1.exception_set_id = -1
AND cal1.calendar_date = TRUNC(p_calendar_date)
AND cal2.calendar_code = cal1.calendar_code
AND cal2.exception_set_id = cal1.exception_set_id
AND cal2.seq_num = cal1.next_seq_num + l_days_offset; --bug3558412
SELECT min(calendar_date), max(calendar_date)
INTO l_first_work_day, l_last_work_day
FROM BOM_CALENDAR_DATES
WHERE CALENDAR_CODE = p_calendar_code
AND SEQ_NUM is not null;
SELECT instance_id, ltrim(rtrim(a2m_dblink))
INTO x_instance_id, x_dblink
FROM mrp_ap_apps_instances;
| allocation enh -3940999 Inserts values of profiles at source in table
| msc_atp_src_profile_temp..
+-------------------------------------------------------------------------*/
PROCEDURE put_src_to_dstn_profiles(
p_session_id IN NUMBER,
x_return_status OUT NoCopy VARCHAR2
) IS
l_profile_name MRP_ATP_PUB.char255_arr := MRP_ATP_PUB.char255_arr();
Delete from msc_atp_src_profile_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'Number of rows deleted ' || SQL%ROWCOUNT);
INSERT INTO msc_atp_src_profile_temp
(
session_id,
profile_name,
profile_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
values
(p_session_id,
l_profile_name(j),
fnd_profile.value(l_profile_name(j)),
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
l_user_id
);
msc_sch_wb.atp_debug('put_src_to_dstn_profiles: ' || 'Rows inserted ' || SQL%ROWCOUNT );
| allocation enh -3940999 Inserts values of profiles at destination by
| reading them from table msc_atp_src_profile_temp at source.
+-------------------------------------------------------------------------*/
PROCEDURE get_src_to_dstn_profiles(
p_dblink IN VARCHAR2,
p_session_id IN NUMBER,
x_return_status OUT NoCopy VARCHAR2
) IS
l_profile_name MRP_ATP_PUB.char255_arr := MRP_ATP_PUB.char255_arr();
Delete from msc_atp_src_profile_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('get_src_to_dstn_profiles: ' || 'Number of rows deleted ' || SQL%ROWCOUNT);
'Insert into msc_atp_src_profile_temp
(session_id,
profile_name,
profile_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login';
' )select
session_id,
profile_name,
profile_value,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login';
msc_sch_wb.atp_debug('After Inserting the profiles in msc_atp_src_profile_temp');
msc_sch_wb.atp_debug('rows inserted = ' || SQL%ROWCOUNT);