[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mst.company_name
from msc_st_trading_partners mst
where sr_instance_id = p_sr_instance_id
and company_name is not null
MINUS
SELECT mc.company_name
from msc_companies mc;
update msc_companies
set company_name = v_my_company_new_name
where company_id = G_OEM_ID;
update msc_trading_partners
set partner_name = v_my_company_new_name
where
partner_name = v_my_company_old_name
and partner_type in (G_SUPPLIER, G_CUSTOMER)
and sr_tp_id = -1
and nvl(company_id, 1) <> 1;
-- ========== Check for updates in company site names ==========
UPDATE_COMPANY_SITE_NAMES;
lv_sql_stmt1 := ' delete msc_trading_partner_maps mtpm '||
' where exists(select 1 '||
' from msc_company_site_id_lid mcsil '||
' where mcsil.company_site_id = mtpm.company_key '||
' and mcsil.partner_type = 3'||
' and mcsil.sr_instance_id = :v_sr_instance_id ) '||
' and mtpm.map_type = 2';
DELETE MSC_COMPANY_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
DELETE MSC_COMPANY_SITE_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
lv_sql_stmt:= ' delete msc_trading_partner_maps mtpm '||
' where exists (select 1 '||
' from msc_trading_partners mtp '||
' where mtp.partner_type = 3'||
' and mtp.sr_instance_id = :v_sr_instance_id'||
' and mtp.partner_id = mtpm.tp_key '||
' )'||
' and mtpm.map_type = 2';
lv_sql_stmt1 := ' delete msc_trading_partner_maps mtpm '||
' where exists(select 1 '||
' from msc_company_site_id_lid mcsil '||
' where mcsil.company_site_id = mtpm.company_key '||
' and mcsil.partner_type = 3'||
' and mcsil.sr_instance_id = :v_sr_instance_id ) '||
' and mtpm.map_type = 2';
DELETE MSC_COMPANY_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
DELETE MSC_COMPANY_SITE_ID_LID WHERE SR_INSTANCE_ID= p_instance_id;
lv_sql_stmt:= ' delete msc_trading_partner_maps mtpm '||
' where exists (select 1 '||
' from msc_trading_partners mtp '||
' where mtp.partner_type = 3'||
' and mtp.sr_instance_id = :v_sr_instance_id'||
' and mtp.partner_id = mtpm.tp_key '||
' )'||
' and mtpm.map_type = 2';
select company_name into p_my_company
from msc_companies
where company_id = 1;
PROCEDURE UPDATE_COMPANY_SITE_NAMES IS
-- Cursor for changed company site names
CURSOR updCompanyNameRecords IS
SELECT mcs.company_id, mcs.company_site_id,
decode(mtps.partner_type,G_SUPPLIER, mtps.tp_site_code, G_CUSTOMER, mtps.LOCATION)
FROM msc_st_Trading_partner_sites mtps,
msc_company_site_id_lid mcsl,
msc_company_sites mcs --bug 5097405
WHERE mtps.sr_instance_id = mcsl.sr_instance_id
AND mtps.sr_instance_id = v_sr_instance_id
AND mtps.partner_type = mcsl.partner_type
AND mtps.sr_tp_site_id = mcsl.sr_company_site_id
AND mcs.company_site_id = mcsl.company_site_id
AND mcs.company_site_name <> decode(mtps.partner_type,G_SUPPLIER, mtps.tp_site_code, G_CUSTOMER, mtps.LOCATION)
AND mtps.partner_type in (G_SUPPLIER, G_CUSTOMER);
UPDATE msc_company_sites
SET company_site_name=a_company_site_name(i)
WHERE company_site_id = a_company_site_id(i)
AND company_id = a_company_id(i);
END UPDATE_COMPANY_SITE_NAMES;
/* Bulk insert the new Companies in msc_companies */
/* Do this step only if there is data fetched for insert */
if names.LAST > 0 then
BEGIN
FORALL i IN names.FIRST..names.LAST
insert into msc_companies
(COMPANY_ID ,
COMPANY_NAME ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
( msc_companies_s.nextval,
names(i),
p_current_date,
p_current_user,
p_current_date,
p_current_user,
p_current_user
);
select distinct
mst.sr_instance_id sr_instance_id,
nvl(mst.company_id, -1) sr_company_id,
decode(mst.partner_type, G_SUPPLIER, G_CUSTOMER,
G_CUSTOMER, G_SUPPLIER,
mst.partner_type) partner_type,
mc.company_id company_id
from msc_st_trading_partners mst,
msc_companies mc
where nvl(mst.company_name, v_my_company) = mc.company_name
and mst.sr_instance_id = v_sr_instance_id
MINUS
select mcil.sr_instance_id,
mcil.sr_company_id,
mcil.partner_type,
mcil.company_id
from msc_company_id_lid mcil;
insert into msc_company_id_lid
( sr_instance_id,
sr_company_id,
partner_type,
company_id
)
values
( a_sr_instance_id(i),
a_sr_company_id(i),
a_partner_type(i),
a_company_id(i)
);
select mc1.company_id subject_id,
mc2.company_id object_id,
mst.partner_type relationship_type
from msc_st_trading_partners mst,
msc_companies mc1,
msc_companies mc2
where nvl(mst.company_name, v_my_company) = mc2.company_name
and nvl(mst.partner_name, v_my_company) = mc1.company_name
and mst.sr_instance_id = v_sr_instance_id
-- Do not include Inventory Organizations in relationship records.
and mst.partner_type <> 3
MINUS
select subject_id, object_id, relationship_type
from msc_company_relationships;
INSERT INTO msc_company_relationships
( RELATIONSHIP_ID ,
SUBJECT_ID ,
OBJECT_ID ,
RELATIONSHIP_TYPE ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY
)
values
( msc_company_rels_s.nextval,
a_subject_id(i),
a_object_id(i),
a_relationship_type(i),
sysdate,
-1,
sysdate,
-1
);
Select mc.company_id company_id,
mst.organization_code company_site_name
from msc_st_trading_partners mst,
msc_companies mc
where nvl(mst.company_name, v_my_company) = mc.company_name
and mst.sr_instance_id = v_sr_instance_id
and mst.partner_type = 3
UNION
-- Add Sites from msc_st_trading_partner_sites for CUSTOMERS
-- This step is required because for Oracle ERP data there won't be
-- any record in msc_trading_partners for Supplier and Customer Sites.
select mcil.company_id company_id,
mstp.LOCATION company_site_name
from msc_st_trading_partner_sites mstp,
msc_company_id_lid mcil
where
-- Make sure that the Trading partner is defined as Company
mstp.sr_tp_id = mcil.sr_company_id
and mstp.sr_instance_id = mcil.sr_instance_id
and mstp.partner_type = mcil.partner_type
and mstp.sr_instance_id = v_sr_instance_id
-- Partner_type should not be 3, i.e. organization
-- because this Partner Type is already included in previous query.
and mstp.partner_type = G_CUSTOMER
UNION
-- Add sites from msc_trading_partner_sites for Suppliers.
SELECT mcil.company_id company_id,
mstp.tp_site_code company_site_name
from msc_st_trading_partner_sites mstp,
msc_company_id_lid mcil
where
-- Make sure that the Trading partner is defined as Company
mstp.sr_tp_id = mcil.sr_company_id
and mstp.sr_instance_id = mcil.sr_instance_id
and mstp.sr_instance_id = v_sr_instance_id
and mstp.partner_type = mcil.partner_type
-- Partner_type should not be 3, i.e. organization
-- Partner Type is already included in above query.
and mstp.partner_type = G_SUPPLIER
MINUS
select mcs.company_id company_id,
mcs.company_site_name company_site_name
from msc_company_sites mcs;
INSERT INTO msc_company_sites
( company_site_id,
company_id,
company_site_name,
creation_date,
created_by,
last_update_date,
last_updated_by
)
values
( msc_company_sites_s.nextval,
a_company_id(i),
a_company_site_name(i),
sysdate,
-1,
sysdate,
-1
);
INSERT INTO MSC_COMPANY_SITE_ID_LID
( SR_INSTANCE_ID,
SR_COMPANY_ID,
SR_COMPANY_SITE_ID,
PARTNER_TYPE,
COMPANY_SITE_ID
)
SELECT mst.sr_instance_id sr_instace_id,
nvl(mst.company_id, -1) sr_company_id,
mst.sr_tp_id sr_company_site_id,
mst.partner_type partner_type,
mcs.company_site_id
from msc_st_trading_partners mst,
msc_company_id_lid mcil,
msc_company_sites mcs
where nvl(mst.company_id, -1) = mcil.sr_company_id
and mst.sr_instance_id = mcil.sr_instance_id
and mst.partner_type = mcil.partner_type
and mst.sr_instance_id = v_sr_instance_id
and mst.partner_type = G_ORGANIZATION
and mcil.company_id = mcs.company_id
and mst.organization_code = mcs.company_site_name
and not exists (select 1
from msc_company_site_id_lid mcsil
where mcsil.sr_instance_id = mst.sr_instance_id
and mcsil.sr_company_id = nvl(mst.company_id, -1)
and mcsil.sr_company_site_id = mst.sr_tp_id
and mcsil.partner_type = mst.partner_type
and mcsil.company_site_id = mcs.company_site_id)
UNION
-- Local Id - Source Id map for Customer and Supplier Sites.
SELECT mtps.sr_instance_id,
mtps.sr_tp_id sr_company_id,
mtps.sr_tp_site_id sr_company_site_id,
mtps.partner_type,
mcs.company_site_id
from msc_st_trading_partner_sites mtps,
msc_company_id_lid mcil,
msc_company_sites mcs
where mtps.sr_instance_id = mcil.sr_instance_id
and mtps.sr_instance_id = v_sr_instance_id
and mtps.sr_tp_id = mcil.sr_company_id
and mtps.partner_type = mcil.partner_type
and mcil.company_id = mcs.company_id
and decode(mtps.partner_type, 2, mtps.LOCATION,
1, tp_site_code ) = mcs.company_site_name
and not exists (select 1
from msc_company_site_id_lid mcsil
where mcsil.sr_instance_id = mtps.sr_instance_id
and mcsil.sr_company_id = mtps.sr_tp_id
and mcsil.sr_company_site_id = mtps.sr_tp_site_id
and mcsil.partner_type = mtps.partner_type
and mcsil.company_site_id = mcs.company_site_id);
select
sr_instance_id,
sr_tp_id company_id,
nvl(company_id, -1) sr_tp_id,
partner_name company_name,
decode(partner_type,G_SUPPLIER, G_CUSTOMER, G_CUSTOMER, G_SUPPLIER) partner_type,
nvl(company_name, p_my_company) partner_name
from msc_st_trading_partners
where sr_instance_id = p_instance_id
and partner_type in (G_SUPPLIER, G_CUSTOMER)
MINUS
select
sr_instance_id,
nvl(company_id, -1) company_id,
sr_tp_id,
nvl(company_name, p_my_company) company_name,
partner_type,
partner_name
from msc_st_trading_partners
where sr_instance_id = p_instance_id
and partner_type in (G_SUPPLIER, G_CUSTOMER);
select
nvl(company_id ,-1),
using_organization_id,
organization_id,
supplier_id,
supplier_site_id,
item_name,
count(*) count
from msc_st_item_suppliers
where sr_instance_id = p_instance_id
and item_name is not null
group by nvl(company_id ,-1), using_organization_id,
organization_id, supplier_id, supplier_site_id, item_name
having count(*) > 1 ;
select
customer_id,
customer_site_id,
customer_item_name,
count(*) count
from msc_st_item_customers mic
where sr_instance_id = p_instance_id
and item_name is not null
group by customer_id,
customer_site_id,
company_id,
customer_item_name
having count(*) > 1;
select count(*)
into c_non_my_company
from msc_st_trading_partners mstp
where nvl(mstp.company_name, p_my_company) <> p_my_company
and mstp.sr_instance_id = p_instance_id;
select instance_type
into a_instance_type
from msc_apps_instances mai
where mai.instance_id = p_instance_id;
UPDATE msc_st_item_suppliers msis
set process_flag = MSC_CL_COLLECTION.G_ERROR
where msis.organization_id = a_organization_id(i)
and msis.supplier_id = a_supplier_id(i)
and nvl(msis.supplier_site_id, -99) = nvl(a_supplier_site_id (i), -99)
and msis.item_name = a_item_name(i);
UPDATE msc_st_item_customers msic
set process_flag = MSC_CL_COLLECTION.G_ERROR
where msic.customer_id = a_customer_id(i)
and nvl(msic.customer_site_id, -99) = nvl(a_customer_site_id (i), -99)
and msic.customer_item_name = a1_item_name(i);
- Update Company_name and company_id column for TPs whose
sites are modeled as Inventory Organizaion
*/
/* Create Bi-Directional records */
OPEN biDirectional;
INSERT INTO MSC_ST_TRADING_PARTNERS
( SR_INSTANCE_ID,
COMPANY_ID,
COMPANY_NAME,
SR_TP_ID,
PARTNER_NAME,
PARTNER_TYPE
)
VALUES
( a_sr_instance_id(i),
a_sr_company_id(i),
a_company_name(i),
a_sr_tp_id(i),
a_partner_name(i),
a_partner_type(i)
);
select ROWIDTOCHAR(msis.rowid),
mcil.company_id,
-99
from msc_st_item_suppliers msis,
msc_company_id_lid mcil
where msis.supplier_id = mcil.sr_company_id
and msis.sr_instance_id = mcil.sr_instance_id
and mcil.partner_type = G_SUPPLIER
and msis.sr_instance_id = p_instance_id
and msis.supplier_site_id is null
and msis.item_name is not null
UNION
select ROWIDTOCHAR(msis.rowid),
mcil.company_id,
mcsil.company_site_id
from msc_st_item_suppliers msis,
msc_company_id_lid mcil,
msc_company_site_id_lid mcsil
where msis.supplier_id = mcil.sr_company_id
and msis.sr_instance_id = mcil.sr_instance_id
and mcil.partner_type = G_SUPPLIER
and msis.sr_instance_id = p_instance_id
and msis.supplier_site_id = mcsil.sr_company_site_id
and msis.sr_instance_id = mcsil.sr_instance_id
and msis.supplier_id = mcsil.sr_company_id
and mcsil.partner_type = G_SUPPLIER
and msis.supplier_site_id is not null
and msis.item_name is not null;
select ROWIDTOCHAR(msic.rowid),
mcil.company_id,
-99
from msc_st_item_customers msic,
msc_company_id_lid mcil
where msic.customer_id = mcil.sr_company_id
and msic.sr_instance_id = mcil.sr_instance_id
and mcil.partner_type = G_CUSTOMER
and msic.sr_instance_id = p_instance_id
and msic.customer_site_id is null
and msic.customer_item_name is not null
UNION
select ROWIDTOCHAR(msic.rowid),
mcil.company_id,
mcsil.company_site_id
from msc_st_item_customers msic,
msc_company_id_lid mcil,
msc_company_site_id_lid mcsil
where msic.customer_id = mcil.sr_company_id
and msic.sr_instance_id = mcil.sr_instance_id
and mcil.partner_type = G_CUSTOMER
and msic.sr_instance_id = p_instance_id
and msic.customer_site_id = mcsil.sr_company_site_id
and msic.sr_instance_id = mcsil.sr_instance_id
and msic.customer_id = mcsil.sr_company_id
and mcsil.partner_type = G_CUSTOMER
and msic.customer_site_id is not null
and msic.customer_item_name is not null;
UPDATE msc_st_item_suppliers mis
set supplier_company_id = a_company_id(i),
supplier_company_site_id = decode(a_company_site_id(i), -99, null, a_company_site_id(i))
where mis.rowid = CHARTOROWID(a_rowid(i));
UPDATE msc_st_item_customers msic
set company_id = a_company_id(i),
company_site_id = decode(a_company_site_id(i), -99, null, a_company_site_id(i))
where msic.rowid = CHARTOROWID(a_rowid(i));
SELECT
mcsil.COMPANY_SITE_ID,
nvl(mtp.PLANNING_ENABLED_FLAG, 'Y')
from msc_st_trading_partners mtp,
msc_company_site_id_lid mcsil
where nvl(mtp.company_id, -1) = mcsil.sr_company_id
and mtp.sr_instance_id = mcsil.sr_instance_id
and mtp.sr_instance_id = v_sr_instance_id
and mtp.sr_tp_id = mcsil.sr_company_site_id
and mtp.partner_type = mcsil.partner_type
and mtp.partner_type = G_ORGANIZATION;
SELECT distinct
mcsil.COMPANY_SITE_ID,
mstps.LOCATION,
mstps.LONGITUDE,
mstps.LATITUDE,
mstps.ADDRESS1,
mstps.ADDRESS2,
mstps.ADDRESS3,
mstps.ADDRESS4,
mstps.country,
mstps.state,
mstps.city,
mstps.county,
mstps.province,
mstps.postal_code
FROM MSC_ST_TRADING_PARTNER_SITES mstps,
msc_company_site_id_lid mcsil
WHERE mcsil.SR_COMPANY_ID = nvl(mstps.sr_tp_id, -1)
AND mcsil.SR_COMPANY_SITE_ID= mstps.SR_TP_SITE_ID
AND mcsil.SR_INSTANCE_ID= mstps.SR_INSTANCE_ID
AND mcsil.partner_type = mstps.partner_type
AND mstps.SR_INSTANCE_ID= v_sr_instance_id
AND mstps.partner_type IN (G_SUPPLIER, G_CUSTOMER)
ORDER BY
mcsil.COMPANY_SITE_ID;
SELECT
mcsil.COMPANY_SITE_ID,
mtps.location,
mtps.longitude,
mtps.latitude,
mtps.ADDRESS1,
mtps.ADDRESS2,
mtps.ADDRESS3,
mtps.ADDRESS4,
mtps.country,
mtps.state,
mtps.city,
mtps.county,
mtps.province,
mtps.postal_code
from msc_st_trading_partner_sites mtps,
msc_company_site_id_lid mcsil
where nvl(mtps.company_id, -1) = mcsil.sr_company_id
and mtps.sr_instance_id = mcsil.sr_instance_id
and mtps.sr_instance_id = v_sr_instance_id
and mtps.sr_tp_id = mcsil.sr_company_site_id
and mtps.partner_type = mcsil.partner_type
and mtps.partner_type = G_ORGANIZATION;
UPDATE MSC_COMPANY_SITES
SET PLANNING_ENABLED = a_PLANNING_ENABLED_FLAG(i)
WHERE COMPANY_SITE_ID = a_COMPANY_SITE_ID(i);
UPDATE MSC_COMPANY_SITES
set LOCATION = a_LOCATION(i),
LONGITUDE = a_LONGITUDE(i),
LATITUDE = a_LATITUDE(i),
ADDRESS1 = a_ADDRESS1(i),
ADDRESS2 = a_ADDRESS2(i),
ADDRESS3 = a_ADDRESS3(i),
ADDRESS4 = a_ADDRESS4(i),
country = a_country(i),
state = a_state(i),
city = a_city(i),
county = a_county(i),
province = a_province(i),
postal_code = a_postal_code(i)
where company_site_id = a_company_site_id(i);
UPDATE MSC_COMPANY_SITES
set LOCATION = a_LOCATION(i),
LONGITUDE = a_LONGITUDE(i),
LATITUDE = a_LATITUDE(i),
ADDRESS1 = a_ADDRESS1(i),
ADDRESS2 = a_ADDRESS2(i),
ADDRESS3 = a_ADDRESS3(i),
ADDRESS4 = a_ADDRESS4(i),
country = a_country(i),
state = a_state(i),
city = a_city(i),
county = a_county(i),
province = a_province(i),
postal_code = a_postal_code(i)
where company_site_id = a_company_site_id(i);
select DISTINCT mtp.partner_id,
mcr.relationship_id
from msc_trading_partners mtp,
msc_tp_id_lid mtil,
msc_company_id_lid mcil,
msc_company_relationships mcr
where mtp.partner_id = mtil.tp_id
and mtil.sr_instance_id = p_instance_id
and mtil.sr_tp_id = mcil.sr_company_id
and mtil.sr_instance_id = mcil.sr_instance_id
and mtil.partner_type = mcil.partner_type
and mcil.company_id = mcr.object_id
/* Perf changes start */
/* Removed nvl(mtp.company_id,...) */
and mtp.company_id IS NULL
and mcr.subject_id = MSC_CL_COLLECTION.G_MY_COMPANY_ID
/* and nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID) = mcr.subject_id */
/* Perf changes end */
and decode(mtp.partner_type, G_SUPPLIER, G_CUSTOMER, G_CUSTOMER, G_SUPPLIER)
= mcr.relationship_type
-- Make sure that only trading Partner records are considered.
and mtp.partner_type IN (G_SUPPLIER, G_CUSTOMER)
/* Perf changes */
/* Removed Minus and added following code lines for performance fix */
and not exists ( select 1
from msc_trading_partner_maps mtpm
where mtpm.tp_key = mtp.partner_id
and mtpm.company_key = mcr.relationship_id
and mtpm.map_type = 1);
select tp_key,
company_key
from msc_trading_partner_maps
where map_type = 1; */
select DISTINCT mtp.partner_id,
mcs.company_site_id
from msc_company_sites mcs,
msc_company_site_id_lid mcsil,
msc_trading_partners mtp
where mcs.company_site_id = mcsil.company_site_id
-- Process for the current instance only
and mcsil.sr_instance_id = p_instance_id
-- Join for Organization
and mcsil.sr_instance_id = mtp.sr_instance_id
and mcsil.sr_company_site_id = mtp.sr_tp_id
and mcsil.partner_type = mtp.partner_type
and mtp.partner_type = G_ORGANIZATION
-- Join for company_id
/* Perf changes */
/* and mcs.company_id = nvl(mtp.company_id, MSC_CL_COLLECTION.G_MY_COMPANY_ID) */
and mcs.company_id = MSC_CL_COLLECTION.G_MY_COMPANY_ID
and mtp.company_id IS NULL
and not exists ( select 1
from msc_trading_partner_maps mtpm
where mtpm.tp_key = mtp.partner_id
and mtpm.company_key
= mcs.company_site_id
and mtpm.map_type = 2);
select tp_key,
company_key
from msc_trading_partner_maps
where map_type = 2; */
select DISTINCT mtsil.tp_site_id,
mcs.company_site_id
from msc_company_sites mcs,
msc_company_site_id_lid mcsil,
msc_tp_site_id_lid mtsil
where mcs.company_site_id = mcsil.company_site_id
and mcsil.sr_instance_id = p_instance_id
and mcsil.sr_instance_id = mtsil.sr_instance_id
and mcsil.partner_type = mtsil.partner_type
and mtsil.sr_company_id = -1
and mcsil.sr_company_site_id = mtsil.sr_tp_site_id
and not exists (select 1
from msc_trading_partner_maps mtpm
where mtpm.tp_key = mtsil.tp_site_id
and mtpm.company_key = mcs.company_site_id
and mtpm.map_type = 3);
select tp_key,
company_key
from msc_trading_partner_maps
where map_type = 3; */
insert into msc_trading_partner_maps
( map_id,
map_type,
tp_key,
COMPANY_KEY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
) values
( msc_tp_maps_s.nextval,
1,
a_tp_id(i),
a_company_id(i),
sysdate,
-1,
sysdate,
-1,
-1
);
insert into msc_trading_partner_maps
( map_id,
map_type,
tp_key,
COMPANY_KEY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
( msc_tp_maps_s.nextval,
2,
a_partner_id(i),
a_company_site_id(i),
sysdate,
-1,
sysdate,
-1,
-1
);
insert into msc_trading_partner_maps
( map_id,
map_type,
tp_key,
COMPANY_KEY ,
CREATION_DATE,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
( msc_tp_maps_s.nextval,
3,
a_tp_site_id(i),
a1_company_site_id(i),
sysdate,
-1,
sysdate,
-1,
-1
);
select t1.inventory_item_id,
mtil.tp_id,
mtsil.tp_site_id,
mic.customer_item_name,
mic.description,
mic.lead_time,
mic.uom_code,
mic.list_price,
mic.planner_code,
mic.refresh_number
from msc_st_item_customers mic,
msc_tp_id_lid mtil,
msc_tp_site_id_lid mtsil,
msc_item_id_lid t1
where t1.SR_INVENTORY_ITEM_ID = mic.inventory_item_id
AND t1.sr_instance_id= mic.sr_instance_id
and mic.customer_id = mtil.sr_tp_id
and nvl(mic.company_id, -1) = nvl(mtil.sr_company_id, -1)
and mic.sr_instance_id = mtil.sr_instance_id
and mic.sr_instance_id = p_instance_id
and mtil.partner_type = G_CUSTOMER
and mic.customer_site_id = mtsil.sr_tp_site_id (+)
and mic.sr_instance_id = mtsil.sr_instance_id (+)
and nvl(mic.company_id, -1) = nvl(mtsil.sr_company_id, -1)
and mtsil.partner_type (+) = G_CUSTOMER;
/* Variables initiated for insert operation */
a_ins_inventory_item_id number_arr := number_arr();
/* Update the record if it already exists */
BEGIN
FORALL i IN 1..a_inventory_item_id.COUNT
UPDATE MSC_ITEM_CUSTOMERS mic
set lead_time = a_lead_time(i),
uom_code = a_uom_code(i),
list_price = a_list_price(i),
refresh_number = a_refresh_number(i),
last_update_date = sysdate,
last_updated_by = -1
where mic.plan_id = -1
and inventory_item_id = a_inventory_item_id(i)
and customer_id = a_tp_id(i)
and nvl(customer_site_id, -99) = nvl(a_tp_site_id(i), -99);
/* Build the collection objects for insertion */
FOR i IN 1..a_inventory_item_id.COUNT LOOP
IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
/* Extend the Collection objects */
a_ins_count.EXTEND;
/* Insert the record if the record does not exist */
IF a_ins_count.COUNT > 0 THEN
FORALL i IN 1..a_ins_count.COUNT
INSERT INTO MSC_ITEM_CUSTOMERS
(PLAN_ID ,
CUSTOMER_ID ,
CUSTOMER_SITE_ID,
INVENTORY_ITEM_ID,
CUSTOMER_ITEM_NAME ,
DESCRIPTION ,
LEAD_TIME ,
UOM_CODE,
LIST_PRICE ,
PLANNER_CODE,
REFRESH_NUMBER ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE ,
CREATED_BY
)
VALUES
(
-1, -- Plan Id for Collections Plan
a_ins_tp_id(i),
a_ins_tp_site_id(i),
a_ins_inventory_item_id(i),
a_ins_item_name(i),
a_ins_description(i),
a_ins_lead_time(i),
a_ins_uom_code(i),
a_ins_list_price(i),
a_ins_planner_code(i),
a_ins_refresh_number(i),
sysdate,
-1,
sysdate,
-1
);
LOG_MESSAGE('Error while inserting into msc_item_customers');
' insert into msc_st_company_users '
||' ( user_name ,'
||' sr_company_id ,'
||' sr_instance_id ,'
||' partner_type ,'
||' start_date ,'
||' end_date ,'
||' description ,'
||' email_address ,'
||' fax ,'
||' collection_parameter '
||' ) '
||' select distinct'
||' x.user_name ,'
||' x.sr_company_id ,'
||' :v_sr_instance_id ,'
||' x.partner_type ,'
||' x.start_date ,'
||' x.end_date ,'
||' x.description ,'
||' x.email_address ,'
||' x.fax ,'
||' :v_collection_parameter '
||' from MRP_AP_COMPANY_USERS_V'||p_dblink||' x';
select distinct user_name,
start_date,
end_date,
description,
email_address,
fax
from msc_st_company_users mscu
where mscu.sr_instance_id = p_sr_instance_id
and not exists (select '1'
from fnd_user fu
where fu.user_name = UPPER(mscu.user_name))
-- ================================================================================
-- Pull only if Collection Parameter is set to "Users and User Company Association"
-- ================================================================================
and mscu.collection_parameter = USER_AND_COMPANY;
select distinct mscu.user_name user_name
from msc_st_company_users mscu,
fnd_user fu
where UPPER(mscu.user_name) = fu.user_name
and mscu.sr_instance_id = p_sr_instance_id
and exists (select '1'
from msc_company_users mcu
where mcu.user_id = fu.user_id
and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
);
CURSOR validUsersUpdate IS
select fu.user_name user_name,
mscu.sr_instance_id sr_instance_id,
mscu.start_date start_date,
mscu.end_date end_date,
mscu.description description,
mscu.email_address email_address,
mscu.fax fax
from msc_st_company_users mscu,
fnd_user fu,
msc_company_id_lid mcil
where mscu.sr_instance_id = p_sr_instance_id
and UPPER(mscu.user_name) = fu.user_name
and mscu.sr_company_id = mcil.sr_company_id
and mscu.sr_instance_id = mcil.sr_instance_id
and mscu.partner_type = mcil.partner_type
and not exists (select '1'
from msc_company_users mcu
where mcu.user_id = fu.user_id
and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
)
-- ==========================================================
-- Get Users for updation only if the collection_parameter is
-- "Users and User Company Association"
-- ==========================================================
and mscu.collection_parameter = USER_AND_COMPANY
MINUS
select fu.user_name user_name,
mcu.sr_instance_id sr_instance_id,
fu.start_date start_date,
fu.end_date end_date,
fu.description description,
fu.email_address email_address,
fu.fax fax
from msc_company_users mcu,
fnd_user fu
where mcu.user_id = fu.user_id
and mcu.sr_instance_id = p_sr_instance_id;
select fu.user_id user_id,
mscu.sr_instance_id sr_instance_id,
mcil.company_id company_id
from msc_st_company_users mscu,
fnd_user fu,
msc_company_id_lid mcil
where mscu.sr_instance_id = p_sr_instance_id
and UPPER(mscu.user_name) = fu.user_name
and mscu.sr_company_id = mcil.sr_company_id
and mscu.sr_instance_id = mcil.sr_instance_id
and mscu.partner_type = mcil.partner_type
and mscu.sr_company_id <>-1
and not exists (select '1'
from msc_company_users mcu
where mcu.user_id = fu.user_id
and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
)
UNION
select fu.user_id user_id,
mscu.sr_instance_id sr_instance_id,
1 company_id
from msc_st_company_users mscu,
fnd_user fu
where UPPER(mscu.user_name) = fu.user_name
and mscu.sr_company_id = -1
and not exists (select '1'
from msc_company_users mcu
where mcu.user_id = fu.user_id
and nvl(mcu.sr_instance_id, -999) <> mscu.sr_instance_id
)
MINUS
select mcu.user_id,
mcu.sr_instance_id,
mcu.company_id
from msc_company_users mcu
where sr_instance_id = p_sr_instance_id;
UPDATE msc_company_users mcu
set
company_id = a_company_id(i)
where mcu.user_id = a_user_id(i)
and mcu.sr_instance_id = a_instance_id(i);
/* Build the collection objects for insertion */
FOR i IN 1..a_user_id.COUNT LOOP
IF (SQL%BULK_ROWCOUNT(i) = 0) THEN
/* Extend the Collection objects */
a_ins_count.EXTEND;
INSERT INTO MSC_COMPANY_USERS
( USER_ID,
COMPANY_ID,
SR_INSTANCE_ID
)
VALUES
( a_ins_user_id(i),
a_ins_company_id(i),
a_ins_instance_id(i)
);
LOG_MESSAGE('Error while inserting records in msc_company_users');
FOR C1 IN validUsersUpdate LOOP
FND_USER_PKG.UpdateUser( x_user_name => C1.user_name,
x_owner => 'CUST',
x_start_date => C1.start_date,
x_end_date => C1.end_date,
x_description => C1.description,
x_email_address => C1.email_address,
x_fax => C1.fax
);