[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 INTO dummy FROM sys.dual
WHERE NOT EXISTS
(SELECT 1 FROM bom_inventory_components
WHERE bill_sequence_id = X_Bill_Sequence_Id
AND component_item_id = X_Component_Item_Id
AND operation_seq_num = X_Operation_Seq_Num
AND (X_Disable_Date IS NULL
OR (to_char(X_Disable_Date,'YYYY/MM/DD HH24:MI:SS') > to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS')))
AND ((to_char(X_Effectivity_Date,'YYYY/MM/DD HH24:MI:SS') < to_char(disable_date,'YYYY/MM/DD HH24:MI:SS'))
OR disable_date IS NULL)
AND implementation_date IS NOT NULL
AND NVL(ECO_FOR_PRODUCTION,2) = 2
AND ((rowid <> X_Rowid) OR (X_Rowid IS NULL)));
SELECT 1 INTO dummy FROM sys.dual
WHERE NOT EXISTS
(SELECT 1 FROM bom_inventory_components
WHERE bill_sequence_id = X_Bill_Sequence_Id
AND component_item_id = X_Component_Item_Id
AND operation_seq_num = X_Operation_Seq_Num
AND (X_To_Unit_Number IS NULL
OR (X_To_Unit_Number >= from_end_item_unit_number))
AND ((X_From_Unit_Number <= to_end_item_unit_number)
OR to_end_item_unit_number IS NULL)
AND implementation_date IS NOT NULL
AND NVL(ECO_FOR_PRODUCTION,2) = 2
AND disable_date is NULL
AND ((rowid <> X_Rowid) OR (X_Rowid IS NULL)));
SELECT 1 INTO dummy
FROM bom_bill_of_materials bbom,
mtl_system_items msi1
WHERE bbom.source_bill_sequence_id = X_Bill_Sequence_Id
AND bbom.organization_id <> X_Organization_Id
AND msi1.inventory_item_id = bbom.assembly_item_id
AND msi1.organization_id = bbom.organization_id
AND NOT EXISTS (SELECT null
FROM mtl_system_items msi2
WHERE msi2.organization_id = bbom.organization_id
AND msi2.inventory_item_id = X_Component_Item_Id
--AND msi2.bom_enabled_flag = 'Y'
--Not a required condition.
AND ((bbom.assembly_type = 1 AND
msi2.eng_item_flag='N')
OR (bbom.assembly_type = 2)
OR (eng_items_for_mfg_ecos_flag = 'YES')) --bug1517975
AND msi2.inventory_item_id <> bbom.assembly_item_id
AND ((msi1.bom_item_type = 1
AND msi2.bom_item_type <> 3)
OR (msi1.bom_item_type = 2
AND msi2.bom_item_type <> 3)
OR (msi1.bom_item_type = 3)
OR (msi1.bom_item_type = 4
AND (msi2.bom_item_type = 4
OR (msi2.bom_item_type in (1,2)
AND msi2.replenish_to_order_flag = 'Y'
AND msi1.base_item_id is NOT NULL
AND msi1.replenish_to_order_flag = 'Y'
))))
AND (msi1.bom_item_type = 3
OR msi1.pick_components_flag = 'Y'
OR msi2.pick_components_flag = 'N')
AND (msi1.bom_item_type = 3
OR nvl(msi2.bom_item_type, 4) <> 2
OR (msi2.bom_item_type = 2
AND ((msi1.pick_components_flag = 'Y'
AND msi2.pick_components_flag = 'Y')
OR (msi1.replenish_to_order_flag = 'Y'
AND msi2.replenish_to_order_flag = 'Y'
))))
AND (
(
(nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1)
AND (NOT (msi1.bom_item_type = 4
AND msi1.pick_components_flag = 'Y'
AND msi2.bom_item_type = 4
AND msi2.replenish_to_order_flag = 'Y'
)
)
)
OR (nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) = 1)
) /* bug 10082384 */
);
SELECT atp_components_flag,
atp_flag
INTO l_atp_comps_flag,
l_atp_flag
FROM mtl_system_items msi
WHERE inventory_item_id = X_Component_Item_Id
AND organization_id = X_Organization_Id;
SELECT 1 INTO dummy FROM dual WHERE NOT EXISTS
(SELECT 1 from bom_inventory_components
WHERE bill_sequence_id = X_Bill_Sequence_Id
AND component_item_id = X_Component_Item_Id
AND operation_seq_num = X_Operation_Seq_Num
AND effectivity_date = X_Effectivity_Date
AND NVL(ECO_FOR_PRODUCTION,2) = 2
AND ((X_Rowid is null) OR (rowid <> X_Rowid))
);
SELECT 1 INTO dummy FROM dual WHERE NOT EXISTS
(SELECT 1 from bom_inventory_components
WHERE bill_sequence_id = X_Bill_Sequence_Id
AND component_item_id = X_Component_Item_Id
AND operation_seq_num = X_Operation_Seq_Num
AND from_end_item_unit_number = X_From_Unit_Number
AND ((X_Rowid is null) OR (rowid <> X_Rowid))
AND disable_date is NULL
AND NVL(ECO_FOR_PRODUCTION,2) = 2
AND ((X_bill_or_eco = 1) OR (X_bill_or_eco <> 1
AND implementation_date is null))
);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Operation_Seq_Num NUMBER,
X_Component_Item_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Item_Num NUMBER,
X_Component_Quantity NUMBER,
X_Component_Yield_Factor NUMBER,
X_Component_Remarks VARCHAR2,
X_Effectivity_Date DATE,
X_Change_Notice VARCHAR2,
X_Implementation_Date DATE,
X_Disable_Date DATE,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Planning_Factor NUMBER,
X_Quantity_Related NUMBER,
X_So_Basis NUMBER,
X_Optional NUMBER,
X_Mutually_Exclusive_Options NUMBER,
X_Include_In_Cost_Rollup NUMBER,
X_Check_Atp NUMBER,
X_Required_To_Ship NUMBER,
X_Required_For_Revenue NUMBER,
X_Include_On_Ship_Docs NUMBER,
X_Include_On_Bill_Docs NUMBER,
X_Low_Quantity NUMBER,
X_High_Quantity NUMBER,
X_Acd_Type NUMBER,
X_Old_Component_Sequence_Id NUMBER,
X_Component_Sequence_Id IN OUT NOCOPY NUMBER,
X_Bill_Sequence_Id NUMBER,
X_Wip_Supply_Type NUMBER,
X_Pick_Components NUMBER,
X_Supply_Subinventory VARCHAR2,
X_Supply_Locator_Id NUMBER,
X_Operation_Lead_Time_Percent NUMBER,
X_Revised_Item_Sequence_Id NUMBER,
X_Cost_Factor NUMBER,
X_Bom_Item_Type NUMBER,
X_From_Unit_Number VARCHAR2,
X_To_Unit_Number VARCHAR2,
X_Enforce_Int_Requirements NUMBER DEFAULT NULL,
X_auto_Request_Material VARCHAR2 DEFAULT NULL
,X_Suggested_Vendor_Name VARCHAR2 DEFAULT NULL
,X_Vendor_Id NUMBER DEFAULT NULL
,X_Unit_Price NUMBER DEFAULT NULL
, X_basis_type NUMBER
) IS
CURSOR C IS SELECT rowid FROM BOM_INVENTORY_COMPONENTS
WHERE component_sequence_id = X_Component_Sequence_Id;
CURSOR C2 IS SELECT bom_inventory_components_s.nextval FROM sys.dual;
INSERT INTO BOM_INVENTORY_COMPONENTS(
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
old_component_sequence_id,
component_sequence_id,
bill_sequence_id,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
enforce_int_requirements,
auto_request_material
,suggested_vendor_name
,vendor_id
,unit_price
,FROM_OBJECT_REVISION_ID
,FROM_MINOR_REVISION_ID
--,COMPONENT_ITEM_REVISION_ID
--,COMPONENT_MINOR_REVISION_ID
,basis_type
) VALUES (
X_Operation_Seq_Num,
X_Component_Item_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Item_Num,
X_Component_Quantity,
X_Component_Yield_Factor,
X_Component_Remarks,
X_Effectivity_Date,
X_Change_Notice,
X_Implementation_Date,
X_Disable_Date,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Planning_Factor,
X_Quantity_Related,
X_So_Basis,
X_Optional,
X_Mutually_Exclusive_Options,
X_Include_In_Cost_Rollup,
X_Check_Atp,
X_Required_To_Ship,
X_Required_For_Revenue,
X_Include_On_Ship_Docs,
X_Include_On_Bill_Docs,
X_Low_Quantity,
X_High_Quantity,
X_Acd_Type,
X_Old_Component_Sequence_Id,
X_Component_Sequence_Id,
X_Bill_Sequence_Id,
X_Wip_Supply_Type,
X_Pick_Components,
X_Supply_Subinventory,
X_Supply_Locator_Id,
X_Operation_Lead_Time_Percent,
X_Revised_Item_Sequence_Id,
X_Cost_Factor,
X_Bom_Item_Type,
X_From_Unit_Number,
X_To_Unit_Number,
X_Enforce_Int_Requirements,
X_Auto_Request_Material
,X_Suggested_Vendor_Name
,X_Vendor_Id
,X_Unit_Price
, l_object_revision_id
, l_minor_revision_id
--, l_comp_revision_id
--, l_comp_minor_revision_id
,X_basis_type
);
BOMPCMBM.Insert_Related_Components(p_src_bill_seq_id => X_Bill_Sequence_Id
, p_src_comp_seq_id => X_Component_Sequence_Id
, x_Mesg_Token_Tbl => l_err_tbl
, x_Return_Status => l_return_status);
SELECT bbm.Organization_Id, bbm.alternate_bom_designator, bbm.assembly_item_id, bbm.specific_assembly_comment
INTO org_id, alt_bom_code, ass_item_id, s_ass_comment
FROM Bom_Bill_Of_Materials bbm
WHERE bbm.Bill_Sequence_Id = X_Bill_Sequence_Id;
, p_last_update_date => X_Last_Update_Date
, p_last_updated_by => X_Last_Updated_By
, p_creation_date => X_Creation_Date
, p_created_by => X_Created_By
, p_last_update_login=> X_Last_Update_Login
, p_component_seq_id => X_Component_Sequence_Id
);
END Insert_Row;