[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.EngRoutingFlag
, tmp.CommonAssemblyItemName
, tmp.RoutingComment
, tmp.CompletionSubinventory
, tmp.CompletionLocationName
, tmp.LineCode
, tmp.CFMRoutingFlag
, tmp.MixedModelMapFlag
, tmp.Priority
, TO_NUMBER(tmp.TotalCycleTime)
, tmp.CTPFlag
, 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
, 'CREATE' Transaction_Type
, NULL Return_Status
, NULL Delete_Group_Name
, NULL DG_Description
, NULL ser_start_op_seq
, NULL row_identifier
FROM bom_routing_header_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.organization_id = org.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND NOT EXISTS (SELECT 1
FROM bom_operational_routings rtg
WHERE rtg.assembly_item_id = item.inventory_item_id
AND rtg.organization_id = item.organization_id
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(tmp.AlternateRoutingCode,'$$##$$'))
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.EngRoutingFlag
, tmp.CommonAssemblyItemName
, tmp.RoutingComment
, tmp.CompletionSubinventory
, tmp.CompletionLocationName
, tmp.LineCode
, tmp.CFMRoutingFlag
, tmp.MixedModelMapFlag
, tmp.Priority
, TO_NUMBER(tmp.TotalCycleTime)
, tmp.CTPFlag
, 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
, 'UPDATE' Transaction_Type
, NULL Return_Status
, NULL Delete_Group_Name
, NULL DG_Description
, NULL ser_start_op_seq
, NULL row_identifier
FROM bom_routing_header_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.organization_id = org.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND EXISTS (SELECT 1
FROM bom_operational_routings rtg
WHERE rtg.assembly_item_id = item.inventory_item_id
AND rtg.organization_id = item.organization_id
AND NVL(rtg.alternate_routing_designator,'$$##$$') = NVL(tmp.AlternateRoutingCode,'$$##$$'));
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, NULL Alternate_Routing_Code
, tmp.Revision
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, 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
, 'CREATE' transaction_type
, NULL return_status
, NULL row_identifier
FROM bom_rtg_revisions_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.organization_id = org.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NOT EXISTS (SELECT 1
FROM mtl_rtg_item_revisions rev
WHERE rev.organization_id = item.organization_id
AND rev.inventory_item_id = item.inventory_item_id
AND rev.process_revision = tmp.revision)
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, NULL Alternate_Routing_Code
, tmp.Revision
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, 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
, 'UPDATE' transaction_type
, NULL return_status
, NULL row_identifier
FROM bom_rtg_revisions_temp tmp,
mtl_parameters org,
mtl_system_items_kfv item
WHERE org.organization_code = tmp.OrganizationCode
AND item.organization_id = org.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND EXISTS (SELECT 1
FROM mtl_rtg_item_revisions rev
WHERE rev.organization_id = item.organization_id
AND rev.inventory_item_id = item.inventory_item_id
AND rev.process_revision = tmp.revision);
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewOperationSequenceNumber
, TO_DATE(tmp.NewStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.StandardOperationCode
, tmp.DepartmentCode
, TO_NUMBER(tmp.OpLeadTimePercent)
, TO_NUMBER(tmp.MinimumTransferQuantity)
, tmp.CountPointType
, tmp.OperationDescription
, TO_DATE(tmp.DisableDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.BackflushFlag
, tmp.OptionDependentFlag
, tmp.ReferenceFlag
, tmp.ProcessSeqNumber
, tmp.ProcessCode
, tmp.LineOpSeqNumber
, tmp.LineOpCode
, tmp.Yield
, tmp.CumulativeYield
, TO_NUMBER(tmp.ReverseCUMYield)
, tmp.UserLaborTime
, tmp.UserMachineTime
, tmp.NetPlanningPercent
, tmp.IncludeInRollup
, tmp.OpYieldEnabledFlag
, tmp.ShutdownType
, 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
, 'UPDATE' Transaction_Type
, NULL Return_Status
, NULL Delete_Group_Name
, NULL DG_Description
, NULL long_description
, NULL row_identifier
FROM bom_routing_operations_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND EXISTS (SELECT 1
FROM BOM_OPERATION_SEQUENCES oper,
bom_operational_routings rtg,
mtl_parameters org,
mtl_system_items_kfv item
WHERE rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND rtg.common_assembly_item_id is null)
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewOperationSequenceNumber
, TO_DATE(tmp.NewStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.StandardOperationCode
, tmp.DepartmentCode
, TO_NUMBER(tmp.OpLeadTimePercent)
, TO_NUMBER(tmp.MinimumTransferQuantity)
, tmp.CountPointType
, tmp.OperationDescription
, TO_DATE(tmp.DisableDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.BackflushFlag
, tmp.OptionDependentFlag
, tmp.ReferenceFlag
, tmp.ProcessSeqNumber
, tmp.ProcessCode
, tmp.LineOpSeqNumber
, tmp.LineOpCode
, tmp.Yield
, tmp.CumulativeYield
, TO_NUMBER(tmp.ReverseCUMYield)
, tmp.UserLaborTime
, tmp.UserMachineTime
, tmp.NetPlanningPercent
, tmp.IncludeInRollup
, tmp.OpYieldEnabledFlag
, tmp.ShutdownType
, 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
, 'CREATE' Transaction_Type
, NULL Return_Status
, NULL Delete_Group_Name
, NULL DG_Description
, NULL long_description
, NULL row_identifier
FROM bom_routing_operations_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND NOT EXISTS (SELECT 1
FROM BOM_OPERATION_SEQUENCES oper,
bom_operational_routings rtg,
mtl_parameters org,
mtl_system_items_kfv item
WHERE rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'));
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ResourceSequenceNumber
, tmp.ResourceCode
, tmp.Activity
, tmp.StandardRateFlag
, TO_NUMBER(tmp.AssignedUnits)
, TO_NUMBER(tmp.UsageRateOrAmount)
, TO_NUMBER(tmp.UsageRateOrAmountInverse)
, tmp.BasisType
, tmp.ScheduleFlag
, TO_NUMBER(tmp.ResourceOffsetPercent)
, tmp.AutochargeType
, TO_NUMBER(tmp.SubstituteGroupNumber)
, TO_NUMBER(tmp.ScheduleSequenceNumber)
, tmp.PrincipleFlag
, 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
, 'CREATE'
, NULL
, tmp.SetupType
, NULL row_identifier
FROM bom_rtg_oper_res_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND NOT EXISTS (SELECT 1
FROM BOM_OPERATION_RESOURCES oper_res,
bom_operation_sequences oper,
bom_operational_routings rtg,
mtl_parameters org,
mtl_system_items_kfv item
WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
AND rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber)
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ResourceSequenceNumber
, tmp.ResourceCode
, tmp.Activity
, tmp.StandardRateFlag
, TO_NUMBER(tmp.AssignedUnits)
, TO_NUMBER(tmp.UsageRateOrAmount)
, TO_NUMBER(tmp.UsageRateOrAmountInverse)
, tmp.BasisType
, tmp.ScheduleFlag
, TO_NUMBER(tmp.ResourceOffsetPercent)
, tmp.AutochargeType
, TO_NUMBER(tmp.SubstituteGroupNumber)
, TO_NUMBER(tmp.ScheduleSequenceNumber)
, tmp.PrincipleFlag
, 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
, 'UPDATE'
, NULL
, tmp.SetupType
, NULL row_identifier
FROM bom_rtg_oper_res_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND EXISTS (SELECT 1
FROM BOM_OPERATION_RESOURCES oper_res,
bom_operation_sequences oper,
bom_operational_routings rtg,
mtl_parameters org,
mtl_system_items_kfv item
WHERE oper.operation_sequence_id = oper_res.operation_sequence_id
AND rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND oper_res.resource_seq_num = tmp.ResourceSequenceNumber
AND rtg.common_assembly_item_id is null);
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.SubResourceCode
, tmp.NewSubResourceCode
, TO_NUMBER(tmp.SubstituteGroupNumber)
, TO_NUMBER(tmp.ScheduleSequenceNumber)
, tmp.ReplacementGroupNumber
--Added the following line for bug3776173 START
, TO_NUMBER(NULL) NewReplacementGroupNumber
--Bug3776173 END
, tmp.Activity
, tmp.StandardRateFlag
, TO_NUMBER(tmp.AssignedUnits)
, TO_NUMBER(tmp.UsageRateOrAmount)
, TO_NUMBER(tmp.UsageRateOrAmountInverse)
, tmp.BasisType
, TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
, tmp.ScheduleFlag
, TO_NUMBER(tmp.ResourceOffsetPercent)
, tmp.AutochargeType
, tmp.PrincipleFlag
, 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
, 'UPDATE'
, NULL
, tmp.SetupType
, NULL row_identifier
FROM bom_sub_oper_resources_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND EXISTS (SELECT 1
FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
bom_operational_routings rtg,
BOM_OPERATION_SEQUENCES oper,
mtl_parameters org,
mtl_system_items_kfv item
WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
AND rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber
AND rtg.common_assembly_item_id is null)
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationSequenceNumber
, tmp.OperationType
, TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.SubResourceCode
, tmp.NewSubResourceCode
, TO_NUMBER(tmp.SubstituteGroupNumber)
, TO_NUMBER(tmp.ScheduleSequenceNumber)
, TO_NUMBER(tmp.ReplacementGroupNumber)
--Added the following line for Bug3776173 START
, TO_NUMBER(NULL) NewReplacementGroupNumber
--Bug3776173 END
, tmp.Activity
, tmp.StandardRateFlag
, TO_NUMBER(tmp.AssignedUnits)
, TO_NUMBER(tmp.UsageRateOrAmount)
, TO_NUMBER(tmp.UsageRateOrAmountInverse)
, tmp.BasisType
, TO_NUMBER(NULL) NewBasisType /* Added for 4689856 */
, tmp.ScheduleFlag
, TO_NUMBER(tmp.ResourceOffsetPercent)
, tmp.AutochargeType
, tmp.PrincipleFlag
, 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
, 'CREATE'
, NULL
, tmp.SetupType
, NULL row_identifier
FROM bom_sub_oper_resources_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND NOT EXISTS (SELECT 1
FROM BOM_SUB_OPERATION_RESOURCES sub_oper,
bom_operational_routings rtg,
BOM_OPERATION_SEQUENCES oper,
mtl_parameters org,
mtl_system_items_kfv item
WHERE oper.operation_sequence_id = sub_oper.operation_sequence_id
AND rtg.routing_sequence_id = oper.routing_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode, '##$$##')
AND oper.operation_seq_num = tmp.OperationSequenceNumber
AND oper.operation_type = tmp.OperationType
AND oper.effectivity_date = TO_DATE(tmp.StartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND NVL(sub_oper.schedule_seq_num,-99999) = NVL(tmp.ScheduleSequenceNumber,-99999)
AND sub_oper.substitute_group_num = tmp.SubstituteGroupNumber);
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationType
, tmp.FromOpSeqNumber
, tmp.FromXCoordinate
, tmp.FromYCoordinate
, TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ToOpSeqNumber
, tmp.ToXCoordinate
, tmp.ToYCoordinate
, TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewFromOpSeqNumber
, TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewToOpSeqNumber
, TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ConnectionType
, TO_NUMBER(tmp.PlanningPercent)
, 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
, 'CREATE' transaction_type
, NULL return_status
, NULL row_identifier
FROM bom_oper_networks_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND NOT EXISTS (SELECT 1
FROM bom_operation_networks op_network,
bom_operational_routings rtg,
bom_operation_sequences oper1,
bom_operation_sequences oper2,
mtl_parameters org,
mtl_system_items_kfv item
WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
AND op_network.to_op_seq_id = oper2.operation_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
AND oper1.routing_sequence_id = rtg.routing_sequence_id
AND oper1.operation_seq_num = tmp.FromOpSeqNumber
AND oper1.operation_type = tmp.OperationType
AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND oper2.routing_sequence_id = rtg.routing_sequence_id
AND oper2.operation_seq_num = tmp.ToOpSeqNumber
AND oper2.operation_type = tmp.OperationType
AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS'))
UNION ALL
SELECT tmp.AssemblyItemName
, tmp.OrganizationCode
, tmp.AlternateRoutingCode
, tmp.OperationType
, tmp.FromOpSeqNumber
, tmp.FromXCoordinate
, tmp.FromYCoordinate
, TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ToOpSeqNumber
, tmp.ToXCoordinate
, tmp.ToYCoordinate
, TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewFromOpSeqNumber
, TO_DATE(tmp.NewFromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.NewToOpSeqNumber
, TO_DATE(tmp.NewToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
, tmp.ConnectionType
, TO_NUMBER(tmp.PlanningPercent)
, 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
, 'UPDATE' transaction_type
, NULL return_status
, NULL row_identifier
FROM bom_oper_networks_temp tmp
WHERE tmp.AssemblyItemName = P_assembly_item_name
AND tmp.OrganizationCode = P_organization_code
AND NVL(tmp.AlternateRoutingCode,'$$##$$') = NVL(P_alternate_routing_code,'$$##$$')
AND EXISTS (SELECT 1
FROM bom_operation_networks op_network,
bom_operational_routings rtg,
bom_operation_sequences oper1,
bom_operation_sequences oper2,
mtl_parameters org,
mtl_system_items_kfv item
WHERE op_network.from_op_seq_id = oper1.operation_sequence_id
AND op_network.to_op_seq_id = oper2.operation_sequence_id
AND item.inventory_item_id = rtg.assembly_item_id
AND item.organization_id = rtg.organization_id
AND item.concatenated_segments = tmp.AssemblyItemName
AND org.organization_id = rtg.organization_id
AND org.organization_code = tmp.OrganizationCode
AND NVL(rtg.alternate_routing_designator, '##$$##') = NVL(tmp.AlternateRoutingCode,'##$$##')
AND oper1.routing_sequence_id = rtg.routing_sequence_id
AND oper1.operation_seq_num = tmp.FromOpSeqNumber
AND oper1.operation_type = tmp.OperationType
AND oper1.effectivity_date = TO_DATE(tmp.FromStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND oper2.routing_sequence_id = rtg.routing_sequence_id
AND oper2.operation_seq_num = tmp.ToOpSeqNumber
AND oper2.operation_type = tmp.OperationType
AND oper2.effectivity_date = TO_DATE(tmp.ToStartEffectiveDate,'YYYY-MM-DD HH24:MI:SS')
AND rtg.common_assembly_item_id is null);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_hdr_XML);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_rev_XML);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_XML);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_res_XML);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_sub_op_res_XML);
rows := DBMS_XMLSave.insertXML(insCtx,P_rtg_op_network_XML);
l_rtg_revision_tbl.DELETE;
l_operation_tbl.DELETE;
l_op_resource_tbl.DELETE;
l_sub_resource_tbl.DELETE;
l_op_network_tbl.DELETE;