[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR bom_updates_CUR IS
SELECT AssemblyItemName
, OrganizationCode
, AlternateCode
, TableName
, ColumnName
, ColumnValue
, ColumnType
FROM bom_routing_updates_temp;
SELECT tmp.AssemblyItemName ,
tmp.OrganizationCode ,
tmp.AlternateBomCode ,
tmp.CommonAssemblyItemName ,
tmp.CommonOrganizationCode ,
tmp.AssemblyComment ,
tmp.AssemblyType ,
'UPDATE' TransactionType ,
NULL ReturnStatus ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.OriginalSystemReference ,
tmp.ImplementationDate , --myerrams, bug: 4873339. New Column added for R12.
NULL DeleteGroupName ,
NULL DGDescription ,
NULL RowIdentifier
FROM bom_bill_of_materials_temp tmp,
mtl_system_items_kfv item,
mtl_parameters org
WHERE org.organization_code = tmp.OrganizationCode
AND item.concatenated_segments = tmp.AssemblyItemName
AND item.organization_id = org.organization_id
AND EXISTS (SELECT 1
FROM bom_bill_of_materials bom
WHERE bom.assembly_item_id = item.inventory_item_id
AND bom.organization_id = org.organization_id
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND bom.common_assembly_item_id is null)
UNION ALL
SELECT tmp.AssemblyItemName ,
tmp.OrganizationCode ,
tmp.AlternateBomCode ,
tmp.CommonAssemblyItemName ,
tmp.CommonOrganizationCode ,
tmp.AssemblyComment ,
tmp.AssemblyType ,
'CREATE' TransactionType ,
NULL ReturnStatus ,
tmp.Attributecategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.OriginalSystemReference ,
tmp.ImplementationDate , --myerrams, bug: 4873339. New Column added for R12.
NULL DeleteGroupName ,
NULL DGDescription ,
NULL RowIdentifier
FROM bom_bill_of_materials_temp tmp,
mtl_system_items_kfv item,
mtl_parameters org
WHERE org.organization_code = tmp.OrganizationCode
AND item.concatenated_segments = tmp.AssemblyItemName
AND item.organization_id = org.organization_id
AND NOT EXISTS (SELECT 1
FROM bom_bill_of_materials bom
WHERE bom.assembly_item_id = item.inventory_item_id
AND bom.organization_id = org.organization_id
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##'));
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.DisableDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.NewEffectivityDate ,
tmp.NewOperationSequenceNumber ,
tmp.ItemSequenceNumber ,
tmp.QuantityPerAssembly ,
tmp.PlanningPercent ,
tmp.ProjectedYield ,
tmp.IncludeInCostRollup ,
tmp.WipSupplyType ,
tmp.SoBasis ,
tmp.Optional ,
tmp.MutuallyExclusive ,
tmp.CheckAtp ,
tmp.ShippingAllowed ,
tmp.RequiredToShip ,
tmp.RequiredForRevenue ,
tmp.IncludeOnShipDocs ,
tmp.QuantityRelated ,
tmp.SupplySubinventory ,
tmp.LocationName ,
tmp.MinimumAllowedQuantity ,
tmp.MaximumAllowedQuantity ,
tmp.Comments ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.FromEndItemUnitNumber ,
tmp.NewFromEndItemUnitNumber ,
tmp.ToEndItemUnitNumber ,
tmp.BasisType , --myerrams, bug: 4873339. New Column added for R12.
NULL ReturnStatus ,
'UPDATE' TransactionType ,
tmp.OriginalSystemReference ,
NULL DeleteGroupName ,
NULL DGDescription ,
tmp.EnforceIntRequirements ,
NULL AutoRequestMaterial ,
NULL RowIdentifier ,
tmp.SuggestedVendorName ,
tmp.UnitPrice
FROM bom_inventory_components_temp tmp
WHERE EXISTS (SELECT 1
FROM bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_parameters org
WHERE bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND org.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND bom.common_assembly_item_id is null)
UNION ALL
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.DisableDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.NewEffectivityDate ,
tmp.NewOperationSequenceNumber ,
tmp.ItemSequenceNumber ,
tmp.QuantityPerAssembly ,
tmp.PlanningPercent ,
tmp.ProjectedYield ,
tmp.IncludeInCostRollup ,
tmp.WipSupplyType ,
tmp.SoBasis ,
tmp.Optional ,
tmp.MutuallyExclusive ,
tmp.CheckAtp ,
tmp.ShippingAllowed ,
tmp.RequiredToShip ,
tmp.RequiredForRevenue ,
tmp.IncludeOnShipDocs ,
tmp.QuantityRelated ,
tmp.SupplySubinventory ,
tmp.LocationName ,
tmp.MinimumAllowedQuantity ,
tmp.MaximumAllowedQuantity ,
tmp.Comments ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.FromEndItemUnitNumber ,
tmp.NewFromEndItemUnitNumber ,
tmp.ToEndItemUnitNumber ,
tmp.BasisType , --myerrams, bug: 4873339. New Column added for R12.
NULL ReturnStatus ,
'CREATE' TransactionType ,
tmp.OriginalSystemReference ,
NULL DeleteGroupName ,
NULL DGDescription ,
tmp.EnforceIntRequirements ,
NULL AutoRequestMaterial,
NULL RowIdentifier,
tmp.SuggestedVendorName,
tmp.UnitPrice
FROM bom_inventory_components_temp tmp
WHERE NOT EXISTS
(SELECT 1
FROM bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_parameters org
WHERE bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND org.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'));
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.SubstituteComponentName ,
NULL NewSubstituteComponentName,
tmp.SubstituteItemQuantity ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
null ProgramId ,
tmp.FromEndItemUnitNumber ,
tmp.EnforceIntRequirements ,
tmp.OriginalSystemReference ,
NULL ReturnStatus ,
'UPDATE' TransactionType ,
NULL RowIdentifier
FROM bom_substitute_components_temp tmp
WHERE EXISTS (SELECT 1
FROM bom_substitute_components sub,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_system_items_kfv item3
WHERE comp.component_sequence_id = sub.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND org.organization_id = bom.organization_Id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND item3.inventory_item_id = sub.substitute_component_id
AND item3.organization_id = org.organization_id
AND item3.concatenated_segments = tmp.SubstituteComponentName
AND bom.common_assembly_item_id is null)
UNION ALL
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.SubstituteComponentName ,
NULL NewSubstituteComponentName,
tmp.SubstituteItemQuantity ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.Attribute3 ,
tmp.Attribute7 ,
tmp.Attribute11 ,
null ProgramId ,
tmp.FromEndItemUnitNumber ,
tmp.EnforceIntRequirements ,
tmp.OriginalSystemReference ,
NULL ReturnStatus ,
'CREATE' TransactionType ,
NULL RowIdentifier
FROM bom_substitute_components_temp tmp
WHERE NOT EXISTS (SELECT 1
FROM bom_substitute_components sub,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2,
mtl_system_items_kfv item3
WHERE comp.component_sequence_id = sub.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND org.organization_id = bom.organization_Id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND item3.inventory_item_id = sub.substitute_component_id
AND item3.organization_id = org.organization_id
AND item3.concatenated_segments = tmp.SubstituteComponentName);
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBomCode ,
tmp.ReferenceDesignatorName ,
tmp.RefDesignatorComment ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.FromEndItemUnitNumber ,
tmp.OriginalSystemReference ,
tmp.NewReferenceDesignator ,
NULL ReturnStatus ,
'CREATE' TransactionType ,
NULL RowIdentifier
FROM bom_reference_designators_temp tmp
WHERE NOT EXISTS (SELECT 1
FROM bom_reference_designators ref,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2
WHERE comp.component_sequence_id = ref.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = bom.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND ref.component_reference_designator = tmp.ReferenceDesignatorName)
UNION ALL
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.OperationSequenceNumber ,
tmp.ComponentItemName ,
tmp.AlternateBomCode ,
tmp.ReferenceDesignatorName ,
tmp.RefDesignatorComment ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.FromEndItemUnitNumber ,
tmp.OriginalSystemReference ,
tmp.NewReferenceDesignator ,
NULL ReturnStatus ,
'UPDATE' TransactionType ,
NULL RowIdentifier
FROM bom_reference_designators_temp tmp
WHERE EXISTS (SELECT 1
FROM bom_reference_designators ref,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2
WHERE comp.component_sequence_id = ref.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = bom.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode,'##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND ref.component_reference_designator = tmp.ReferenceDesignatorName
AND bom.common_assembly_item_id is null);
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.FromEndItemUnitNumber ,
tmp.ToEndItemUnitNumber ,
tmp.OperationSequenceNumber ,
tmp.AdditionalOperationSeqNum ,
NULL NewAdditionalOpSeqNum,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.Attributecategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
NULL ReturnStatus ,
'CREATE' TransactionType ,
NULL RowIdentifier
FROM bom_component_operations_temp tmp
WHERE NOT EXISTS (SELECT 1
FROM bom_component_operations comp_oper,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2
WHERE comp.component_sequence_id = comp_oper.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = bom.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode, '##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND comp_oper.operation_seq_num = tmp.AdditionalOperationSeqNum)
UNION ALL
SELECT tmp.OrganizationCode ,
tmp.AssemblyItemName ,
tmp.StartEffectiveDate ,
tmp.FromEndItemUnitNumber ,
tmp.ToEndItemUnitNumber ,
tmp.OperationSequenceNumber ,
tmp.AdditionalOperationSeqNum ,
NULL NewAdditionalOpSeqNum,
tmp.ComponentItemName ,
tmp.AlternateBOMCode ,
tmp.Attributecategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
NULL ReturnStatus ,
'UPDATE' TransactionType ,
NULL RowIdentifier
FROM bom_component_operations_temp tmp
WHERE EXISTS (SELECT 1
FROM bom_component_operations comp_oper,
bom_inventory_components comp,
bom_bill_of_materials bom,
mtl_parameters org,
mtl_system_items_kfv item1,
mtl_system_items_kfv item2
WHERE comp.component_sequence_id = comp_oper.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND item1.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = bom.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(bom.alternate_bom_designator,'##$$##') = NVL(tmp.AlternateBomCode, '##$$##')
AND item2.inventory_item_id = comp.component_item_id
AND item2.organization_id = org.organization_id
AND item2.concatenated_segments = tmp.ComponentItemName
AND comp.operation_seq_num = tmp.OperationSequenceNumber
AND comp.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND comp_oper.operation_seq_num = tmp.AdditionalOperationSeqNum
AND bom.common_assembly_item_id is null);
SELECT tmp.AssemblyItemName ,
tmp.OrganizationCode ,
tmp.Revision ,
tmp.AlternateBomCode ,
tmp.Description ,
tmp.StartEffectiveDate ,
'CREATE' TransactionType,
NULL ReturnStatus,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.OriginalSystemReference,
NULL RowIdentifier
FROM bom_revisions_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.concatenated_segments = tmp.AssemblyItemName
AND item.organization_id = org.organization_id
AND tmp.revision <> org.starting_revision
AND NOT EXISTS (SELECT 1
FROM mtl_item_revisions rev
WHERE rev.organization_id = org.organization_id
AND rev.inventory_item_id = item.inventory_item_id
AND rev.revision = tmp.revision)
UNION ALL
SELECT tmp.AssemblyItemName ,
tmp.OrganizationCode ,
tmp.Revision ,
tmp.AlternateBomCode ,
tmp.Description ,
tmp.StartEffectiveDate ,
'UPDATE' TransactionType,
NULL ReturnStatus ,
tmp.AttributeCategory ,
tmp.Attribute1 ,
tmp.Attribute2 ,
tmp.Attribute3 ,
tmp.Attribute4 ,
tmp.Attribute5 ,
tmp.Attribute6 ,
tmp.Attribute7 ,
tmp.Attribute8 ,
tmp.Attribute9 ,
tmp.Attribute10 ,
tmp.Attribute11 ,
tmp.Attribute12 ,
tmp.Attribute13 ,
tmp.Attribute14 ,
tmp.Attribute15 ,
tmp.OriginalSystemReference,
NULL RowIdentifier
FROM bom_revisions_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.concatenated_segments = tmp.AssemblyItemName
AND item.organization_id = org.organization_id
AND tmp.revision <> org.starting_revision
AND EXISTS (SELECT 1
FROM mtl_item_revisions rev
WHERE rev.organization_id = org.organization_id
AND rev.inventory_item_id = item.inventory_item_id
AND rev.revision = tmp.revision);
l_update_stmt VARCHAR2(1000) := NULL;
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_header_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_revisions_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_inv_comps_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_sub_comps_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_ref_desgs_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_comp_oper_XML);
OPEN bom_updates_CUR;
FETCH bom_updates_CUR
INTO l_assembly_item_name,
l_organization_code,
l_alternate_bom_code,
l_table_name,
l_column_name,
l_column_value,
l_column_type;
IF (bom_updates_CUR%NOTFOUND) THEN
EXIT;
l_update_stmt := 'update ' || l_table_name ||
' set ' || l_column_name;
l_update_stmt := l_update_stmt || ' = TO_DATE(:l_column_value, ''YYYY-MM-DD HH24:MI:SS'') ';
l_update_stmt := l_update_stmt || ' = TO_NUMBER(:l_column_value) ';
l_update_stmt := l_update_stmt || ' = :l_column_value ';
l_update_stmt := l_update_stmt || ' where AssemblyItemName = :l_assembly_item_name '
|| ' and OrganizationCode = :l_organization_code ';
l_update_stmt := l_update_stmt || ' and AlternateBomCode = :l_alternate_bom_code';
EXECUTE IMMEDIATE l_update_stmt
USING l_column_value, l_assembly_item_name, l_organization_code, l_alternate_bom_code;
l_update_stmt := l_update_stmt || ' and AlternateBomCode IS NULL';
EXECUTE IMMEDIATE l_update_stmt
USING l_column_value, l_assembly_item_name, l_organization_code;
CLOSE bom_updates_CUR;
DELETE FROM bom_routing_updates_temp;commit;
l_bom_header_tbl(i).delete_group_name := bom_header_rec.DeleteGroupName;
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_revisions_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_inv_comps_XML);
l_bom_comp_tbl(i).delete_group_name := bom_comp_rec.DeleteGroupName;
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_sub_comps_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_ref_desgs_XML);
rows := DBMS_XMLSave.insertXML(insCtx, P_bom_comp_oper_XML);