[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(cnt)
FROM
(
-- Locator Level
select count(*) cnt
FROM mtl_item_locations_kfv locf,
mtl_parameters mp,
hr_organization_units hou
where
locf.last_update_date between p_from_date and p_to_date AND
locf.organization_id = mp.organization_id AND
hou.organization_id=mp.organization_id AND
(locf.physical_location_id = locf.inventory_location_id OR
locf.physical_location_id IS NULL)
union all
-- Sub Inventory Level
select count(*) cnt
FROM mtl_secondary_inventories msi,
hr_all_organization_units bg,
hr_all_organization_units org,
mtl_parameters mp
WHERE msi.organization_id = mp.organization_id + 0
AND bg.organization_id = org.business_group_id
AND org.organization_id = mp.organization_id
and msi.last_update_date between p_from_date and p_to_date
union all
-- Inventory Organization Level
select count(*) cnt
FROM
hr_all_organization_units bg,
hr_all_organization_units org,
HR_ORGANIZATION_INFORMATION HOI,
mtl_parameters mp
WHERE bg.organization_id = org.business_group_id
AND org.organization_id = mp.organization_id
and org.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND ( HOI.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
and mp.last_update_date between p_from_date and p_to_date
union all
-- Operating Unit Level
select count(*) cnt
FROM EDW_ORGA_OPER_UNIT_LCV
where last_update_date between p_from_date and p_to_date
-- Inventory Organization Parent Goup level
union all
SELECT COUNT(*) cnt
FROM SY_ORGN_MST
WHERE ORGN_CODE = CO_CODE
-- Inventory Organization Parent Goup level
union all
SELECT COUNT(*) cnt
FROM SY_ORGN_MST
-- Locator level non location controlled
UNION ALL
SELECT COUNT(*) cnt
FROM IC_LOCT_INV ILI,
IC_ITEM_MST IIM,
IC_WHSE_MST IWM,
MTL_PARAMETERS MP
WHERE
ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL = 0
-- Locator level Non Validated
UNION ALL
SELECT COUNT(*) cnt
FROM IC_LOCT_INV ILI,
IC_ITEM_MST IIM,
IC_WHSE_MST IWM,
MTL_PARAMETERS MP
WHERE
ILI.ITEM_ID = IIM.ITEM_ID
AND ILI.WHSE_CODE = IWM.WHSE_CODE
AND MP.ORGANIZATION_ID = IWM.MTL_ORGANIZATION_ID
AND IWM.LOCT_CTL * IIM.LOCT_CTL >1 );
x_Locator_Last_Update_Date NUMBER ;
x_Stock_Room_Last_Update_Date NUMBER;
x_Plant_Last_Update_Date NUMBER;
SELECT
-- LOCATOR_PK needs to add instance_code and org_code
avg(nvl(vsize(locf.inventory_location_id), 0)),
-- INSTANCE_CODE, needs to instance_Code
-- STOCK_ROOM_FK needs to add org_code and instance code
avg(nvl(vsize(locf.SUBINVENTORY_CODE), 0)),
-- Locator_Name needs to add org_code
avg(nvl(vsize(locf.CONCATENATED_SEGMENTS), 0)),
-- Description
avg(nvl(vsize(locf.description), 0)),
-- Enabled_Flag
avg(nvl(vsize(locf.ENABLED_FLAG), 0)),
-- Locator_DP
-- length('LOCATOR'),
7, -- for some strange reason, the package doesn't compile the length function here
-- Name needs to add name
avg(nvl(vsize(locf.CONCATENATED_SEGMENTS), 0)),
-- Creation_Date
avg(nvl(vsize(locf.creation_date), 0)),
-- Last_Update_Date
avg(nvl(vsize(locf.last_update_date), 0)),
-- Inventory_Location_ID
avg(nvl(vsize(locf.inventory_location_id), 0)),
-- Organization_ID
avg(nvl(vsize(locf.organization_id), 0))
From
mtl_item_locations_kfv locf;
Select
-- Org_Code
10*avg(nvl(vsize(mp.organization_code), 0)),
-- Plant_Last_Update_Date
avg(nvl(vsize(mp.last_update_date), 0)),
-- Plant_Creation_date
avg(nvl(vsize(mp.creation_date), 0))
From
mtl_parameters mp;
Select
-- Org_Name
avg(nvl(vsize(hou.name), 0))
From
hr_organization_units hou;
SELECT
-- Instance
15*avg(nvl(vsize(instance_code), 0))
FROM EDW_LOCAL_INSTANCE ;
Select
-- STOCK_ROOM_PK, needs to add org_code and instance_Code
3*avg(nvl(vsize(msi.secondary_inventory_name), 0)),
-- PLANT_FK, needs to add org_code and instance_Code
-- INSTANCE_CODE, needs to instance_Code
-- STOCK_ROOM, needs to add org_code
-- avg(nvl(vsize(msi.secondary_inventory_name), 0)),
-- DESCRIPTION
avg(nvl(vsize(msi.description), 0)),
-- STOCK_ROOM_DP
length('SUB_INVENTORY'),
-- NAME
-- avg(nvl(vsize(msi.secondary_inventory_name), 0)),
-- CREATION_DATE
avg(nvl(vsize(msi.creation_date), 0)),
-- LAST_UPDATE_DATE
avg(nvl(vsize(msi.last_update_date), 0))
From
mtl_secondary_inventories msi;
Select
-- PLANT_PK, it's org_code and instance_code
-- INSTANCE_CODE, needs to instance_Code
-- PLANT_DP
length('PLANT'),
-- NAME and Org_Name
3*avg(nvl(vsize(bg.name), 0))
From
hr_all_organization_units bg;
Select
-- OPERATING_UNIT_FK needs to add instance_code
-- OPM_ORGANIZATION_FK needs to add instance_code
2*avg(nvl(vsize(HOI.ORG_INFORMATION3), 0))
From
HR_ORGANIZATION_INFORMATION HOI;
SELECT avg(nvl(vsize(ORGN_CODE),0)) ORGN_CODE,
avg(nvl(vsize(ORGN_NAME),0)) ORGN_NAME
FROM SY_ORGN_MST;
x_Locator_Last_Update_Date,
x_Inventory_Location_ID,
x_Organization_ID;
ceil(x_Locator_Last_Update_Date + 1) +
ceil(x_Inventory_Location_ID + 1) +
ceil(x_Organization_ID + 1) ;
x_Plant_Last_Update_Date,
x_Plant_Creation_date;
x_total := x_total + ceil(x_Org_Code + 1) + ceil(x_Plant_Last_Update_Date + 1) + ceil(x_Plant_Creation_date + 1);
x_Stock_Room_Last_Update_Date;
ceil(x_Stock_Room_Last_Update_Date + 1);