[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
times for a given bom level. Procedure update_lt updates
mtl_system_items with cumulative lead time values.
Required Tables :
BOM_PARAMETERS
BOM_INVENTORY_COMPONENTS
BOM_BILL_OF_MATERIALS
BOM_LOW_LEVEL_CODES
MTL_SYSTEM_ITEMS
History
20-Feb-1996 Manu Chadha
-Added the MTL.ORGANIZATION_ID = org_id; line to to
update_lc as a fix for bug#343531
02-Oct-1997 Rob Yee
Streamline for performance by using
recursion for explosion and updating leadtimes
in mtl_system_items directly
21-Aug-1998 Mani
Added unit number and changed SQL statements
to implement Serial Effectivity.
13-May-2004 Rahul Chitko
-Added alternate_bom_code parameter to be able to
perform rollup for specified alternate.
-----------------------------------------------------------------------------*/
Type StackTabType is table of number index by binary_integer;
SELECT COM.COMPONENT_ITEM_ID
FROM MTL_SYSTEM_ITEMS MTL2,
BOM_INVENTORY_COMPONENTS COM,
MTL_SYSTEM_ITEMS MTL1,
BOM_BILL_OF_MATERIALS BOM
WHERE NVL(BOM.ALTERNATE_BOM_DESIGNATOR,'XXXXXXXXXXX') =
NVL(p_alternate_bom_code,'XXXXXXXXXXX')
AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
AND BOM.ORGANIZATION_ID = p_org_id
AND BOM.ASSEMBLY_ITEM_ID = p_item_id
AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND COM.IMPLEMENTATION_DATE IS NOT NULL
AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
AND NOT (mtl1.replenish_to_order_flag = 'Y'
AND mtl1.bom_item_type = 4
AND mtl1.base_item_id IS NOT NULL
AND MTL2.BOM_ITEM_TYPE IN (1,2))
AND (
COM.DISABLE_DATE IS NULL
OR
COM.DISABLE_DATE > p_eff_date
)
AND ((MTL1.EFFECTIVITY_CONTROL <> 1
AND p_unit_number is NOT NULL
AND COM.DISABLE_DATE IS NULL
AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER AND
NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number))
OR (MTL1.EFFECTIVITY_CONTROL = 1
AND COM.EFFECTIVITY_DATE <= p_eff_date));
Select item_number
From mtl_item_flexfields
Where item_id = P_ItemId
And organization_id = P_OrgId;
Update bom_low_level_codes
Set low_level_code = p_level,
program_update_date = sysdate
Where rollup_id = p_roll_id
And inventory_item_id = l_comps_rec.component_item_id
And low_level_code < p_level;
Insert into bom_low_level_codes(
rollup_id,
inventory_item_id,
low_level_code,
request_id,
program_application_id,
program_id,
program_update_date)
Select
p_roll_id,
l_comps_rec.component_item_id,
p_level,
p_req_id,
p_prgm_app_id,
p_prgm_id,
sysdate
From dual
Where not exists(
Select null
From bom_low_level_codes
Where rollup_id = p_roll_id
And inventory_item_id = l_comps_rec.component_item_id
And low_level_code >= p_level);
SELECT MAXIMUM_BOM_LEVEL
FROM BOM_PARAMETERS
WHERE ORGANIZATION_ID = org_id;
Select bl.assembly_item_id,
bl.conc_flex_string
From bom_lists bl, mtl_system_items msi
Where bl.sequence_id = roll_id
and msi.organization_id = org_id
and msi.inventory_item_id = bl.assembly_item_id
and (unit_number is NOT NULL
or (unit_number is NULL and msi.effectivity_control = 1));
Insert into bom_low_level_codes(
rollup_id,
inventory_item_id,
low_level_code,
request_id,
program_application_id,
program_id,
program_update_date)
Select
roll_id,
l_bill_rec.assembly_item_id,
0,
req_id,
prgm_app_id,
prgm_id,
sysdate
From dual
Where not exists(
Select null
From bom_low_level_codes
Where rollup_id = roll_id
And inventory_item_id = l_bill_rec.assembly_item_id
And low_level_code >= 0);
PROCEDURE update_lt(
org_id IN NUMBER,
roll_id IN NUMBER,
prgm_id IN NUMBER,
prgm_app_id IN NUMBER,
req_id IN NUMBER,
unit_number IN VARCHAR2,
rev_date IN DATE,
err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
X_include_in_rollup varchar2(1) := 'N';
Select nvl(max(low_level_code), -1) depth
From bom_low_level_codes
Where rollup_id = roll_id;
select MTL.ROWID row_id,
MTL.INVENTORY_ITEM_ID,
NVL(MTL.PREPROCESSING_LEAD_TIME, 0) +
NVL(MTL.POSTPROCESSING_LEAD_TIME, 0) +
NVL(MTL.FULL_LEAD_TIME, 0) TOTAL_LEAD_TIME,
DECODE(MTL.PLANNING_MAKE_BUY_CODE,
2, 0,
NVL(MTL.FULL_LEAD_TIME, 0)) FULL_LEAD_TIME,
MTL.PLANNING_MAKE_BUY_CODE,
MTL.bom_item_type bom_item_type
from mtl_system_items MTL,
bom_low_level_codes LLC
where LLC.ROLLUP_ID = roll_id
AND LLC.LOW_LEVEL_CODE = p_level
AND MTL.INVENTORY_ITEM_ID = LLC.INVENTORY_ITEM_ID
AND MTL.ORGANIZATION_ID = org_id
For update of mtl.CUMULATIVE_TOTAL_LEAD_TIME,
mtl.CUM_MANUFACTURING_LEAD_TIME NOWAIT;
SELECT NVL(MTL2.CUMULATIVE_TOTAL_LEAD_TIME, 0) CUMULATIVE_TOTAL_LEAD_TIME,
NVL(MTL2.CUM_MANUFACTURING_LEAD_TIME, 0) CUM_MANUFACTURING_LEAD_TIME,
COM.OPERATION_SEQ_NUM
FROM MTL_SYSTEM_ITEMS MTL2,
BOM_INVENTORY_COMPONENTS COM,
MTL_SYSTEM_ITEMS MTL1,
BOM_BILL_OF_MATERIALS BOM
WHERE BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
AND BOM.ORGANIZATION_ID = p_org_id
AND BOM.ASSEMBLY_ITEM_ID = p_item_id
AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND COM.IMPLEMENTATION_DATE IS NOT NULL
AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
AND COM.COMPONENT_QUANTITY > 0
AND NOT (mtl1.replenish_to_order_flag = 'Y'
AND mtl1.bom_item_type = 4
AND mtl1.base_item_id IS NOT NULL
AND MTL2.BOM_ITEM_TYPE IN (1,2))
AND (
COM.DISABLE_DATE IS NULL
OR
COM.DISABLE_DATE > p_eff_date
)
AND ((MTL1.EFFECTIVITY_CONTROL <> 1
AND p_unit_number is NOT NULL
AND COM.DISABLE_DATE IS NULL
AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER AND
NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number))
OR (MTL1.EFFECTIVITY_CONTROL = 1
AND COM.EFFECTIVITY_DATE <= p_eff_date));
Select nvl(bos.operation_lead_time_percent, 0) operation_lead_time_percent
From Bom_Operation_Sequences bos,
Bom_Operational_Routings bor
Where bor.assembly_item_id = P_Assembly
And bor.organization_Id = P_Org_Id
And bor.alternate_routing_designator is null
And bor.common_routing_sequence_id = bos.routing_sequence_id
And bos.operation_seq_num = P_Operation
And NVL(bos.eco_for_production,2) = 2
-- Changed for bug 2647027
/** And bos.effectivity_date <= trunc(rev_date)
And nvl(bos.disable_date, rev_date + 1) >= trunc(rev_date); **/
l_last_updated_by number; -- BUG 4990802
l_last_update_login number; -- BUG 4990802
SELECT INCLUDE_MODELS_IN_ROLLUP
INTO X_include_models
FROM bom_parameters
WHERE organization_id = org_id;
l_last_updated_by := NVL(fnd_global.user_id, -1); -- BUG 4990802
l_last_update_login := NVL(fnd_global.login_id, -1); -- BUG 4990802
/* Modified update statement to include the attributes last_update_date, last_updated_by, last_update_login for BUG 4990802 */
if (X_include_in_rollup ='Y') then
Update mtl_system_items set
CUMULATIVE_TOTAL_LEAD_TIME = l_assy_rec.total_lead_time + l_ctlt ,
CUM_MANUFACTURING_LEAD_TIME = l_assy_rec.full_lead_time + l_cmlt ,
REQUEST_ID = req_id,
PROGRAM_APPLICATION_ID = prgm_app_id,
PROGRAM_ID = prgm_id,
PROGRAM_UPDATE_DATE = SYSDATE,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
where ROWID = l_assy_rec.row_id;
p_procedure_name => 'update_lt('||l_stmt||')');
END update_lt;
* Delete Processed Rows will delete the processed rows within the session
* under a given rollup id
* @param p_rollup_id current rollup identifier
* @rep:scope private
* @rep:lifecycle active
* @rep:displayname Delete Processed Rows within a rollup session.
********************************************************************/
PROCEDURE Delete_Processed_Rows
(p_rollup_id IN NUMBER)
IS
l_RowsFound BOOLEAN;
DELETE FROM BOM_LOW_LEVEL_CODES
WHERE ROLLUP_ID = p_rollup_id
and rownum <= G_CommitRows;
END Delete_Processed_Rows;
DELETE FROM BOM_LOW_LEVEL_CODES
WHERE ROLLUP_ID = roll_id
and rownum <= G_CommitRows;
update_lt(
org_id => org_id,
roll_id => roll_id,
prgm_id => prgm_id,
prgm_app_id => prgm_app_id,
req_id => req_id,
unit_number => unit_number,
rev_date => eff_date,
err_msg => l_err_msg);
DELETE FROM BOM_LOW_LEVEL_CODES
WHERE ROLLUP_ID = roll_id
and rownum <= G_CommitRows;