The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bom.BILL_SEQUENCE_ID BILL_SEQUENCE_ID, --myerrams, Bug: 5142847
item1.concatenated_segments AssemblyItemName,
p_target_orgcode OrganizationCode,
bom.alternate_bom_designator AlternateBomCode,
item2.concatenated_segments CommonAssemblyItemName,
org2.organization_code CommonOrganizationCode,
bom.specific_assembly_comment AssemblyComment,
bom.assembly_type AssemblyType,
'CREATE' TransactionType,
NULL ReturnStatus,
bom.attribute_category AttributeCategory,
bom.attribute1 Attribute1,
bom.attribute2 Attribute2,
bom.attribute3 Attribute3,
bom.attribute4 Attribute4,
bom.attribute5 Attribute5,
bom.attribute6 Attribute6,
bom.attribute7 Attribute7,
bom.attribute8 Attribute8,
bom.attribute9 Attribute9,
bom.attribute10 Attribute10,
bom.attribute11 Attribute11,
bom.attribute12 Attribute12,
bom.attribute13 Attribute13,
bom.attribute14 Attribute14,
bom.attribute15 Attribute15,
bom.original_system_reference OriginalSystemReference,
NULL DeleteGroupName,
NULL DGDescription,
NULL RowIdentifier,
to_char(bom.implementation_date,'YYYY-MM-DD HH24:MI:SS') ImplementationDate --Bug: 5174575: New attribute added for R12
FROM bom_bill_of_materials bom,
mtl_system_items_kfv item1,
mtl_parameters org1,
mtl_parameters org2,
mtl_system_items_kfv item2
WHERE org1.organization_id = bom.organization_id
AND item1.organization_id = bom.organization_id
AND item1.inventory_item_id = bom.assembly_item_id
AND org2.organization_id(+) = bom.common_organization_id
AND item2.organization_id(+) = bom.common_organization_id
AND item2.inventory_item_id(+) = bom.common_assembly_item_id
AND bom.organization_id = p_model_org_id
Order by bom.alternate_bom_designator desc, bom.assembly_item_id; --myerrams, Bug: 5142847
SELECT comp.COMPONENT_SEQUENCE_ID COMPONENT_SEQUENCE_ID, --myerrams, Bug: 5142847
p_target_orgcode OrganizationCode,
item1.concatenated_segments AssemblyItemName,
TO_CHAR(comp.effectivity_date,'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
TO_CHAR(comp.disable_date, 'YYYY-MM-DD HH24:MI:SS') DisableDate,
comp.operation_seq_num OperationSequenceNumber,
item2.concatenated_segments ComponentItemName,
bom.alternate_bom_designator AlternateBOMCode,
null NewEffectivityDate,
null NewOperationSequenceNumber,
item_num ItemSequenceNumber,
comp.component_quantity QuantityPerAssembly,
comp.planning_factor PlanningPercent,
comp.component_yield_factor ProjectedYield,
comp.include_in_cost_rollup IncludeInCostRollup,
comp.wip_supply_type WipSupplyType,
comp.so_basis SoBasis,
comp.optional Optional,
comp.mutually_exclusive_options MutuallyExclusive,
comp.check_atp CheckAtp,
comp.shipping_allowed ShippingAllowed,
comp.required_to_ship RequiredToShip,
comp.required_for_revenue RequiredForRevenue,
comp.include_on_ship_docs IncludeOnShipDocs,
comp.quantity_related QuantityRelated,
comp.supply_subinventory SupplySubinventory,
null LocationName,
comp.low_quantity MinimumAllowedQuantity,
comp.high_quantity MaximumAllowedQuantity,
comp.component_remarks Comments,
comp.attribute_category AttributeCategory,
comp.attribute1 Attribute1,
comp.attribute2 Attribute2,
comp.attribute3 Attribute3,
comp.attribute4 Attribute4,
comp.attribute5 Attribute5,
comp.attribute6 Attribute6,
comp.attribute7 Attribute7,
comp.attribute8 Attribute8,
comp.attribute9 Attribute9,
comp.attribute10 Attribute10,
comp.attribute11 Attribute11,
comp.attribute12 Attribute12,
comp.attribute13 Attribute13,
comp.attribute14 Attribute14,
comp.attribute15 Attribute15,
comp.from_end_item_unit_number FromEndItemUnitNumber,
null NewFromEndItemUnitNumber,
comp.to_end_item_unit_number ToEndItemUnitNumber,
NULL ReturnStatus,
'CREATE' TransactionType,
comp.original_system_reference OriginalSystemReference,
NULL DeleteGroupName,
NULL DGDescription,
comp.enforce_int_requirements EnforceIntRequirements,
NULL AutoRequestMaterial,
NULL RowIdentifier,
comp.suggested_vendor_name SuggestedVendorName,
comp.unit_price UnitPrice,
comp.basis_type BasisType --Bug: 5174575: New attribute added for R12
FROM bom_inventory_components comp,
mtl_system_items_kfv item1,
mtl_parameters org,
bom_bill_of_materials bom,
mtl_system_items_kfv item2
WHERE bom.bill_sequence_id = comp.bill_sequence_id
AND org.organization_id = bom.organization_id
AND item1.organization_id = bom.organization_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item2.organization_id = bom.organization_id
AND item2.inventory_item_id = comp.component_item_id
AND bom.organization_id = p_model_org_id
and comp.BILL_SEQUENCE_ID = BILL_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
SELECT p_target_orgcode OrganizationCode,
item1.concatenated_segments AssemblyItemName,
TO_CHAR(comp.effectivity_date, 'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
comp.operation_seq_num OperationSequenceNumber,
item3.concatenated_segments ComponentItemName,
bom.alternate_bom_designator AlternateBomCode,
item2.concatenated_segments SubstituteComponentName,
NULL NewSubstituteComponentName,
sub.substitute_item_quantity SubstituteItemQuantity,
sub.attribute_category AttributeCategory,
sub.attribute1 Attribute1,
sub.attribute2 Attribute2,
sub.attribute3 Attribute3,
sub.attribute4 Attribute4,
sub.attribute5 Attribute5,
sub.attribute6 Attribute6,
sub.attribute7 Attribute7,
sub.attribute8 Attribute8,
sub.attribute9 Attribute9,
sub.attribute10 Attribute10,
sub.attribute11 Attribute11,
sub.attribute12 Attribute12,
sub.attribute13 Attribute13,
sub.attribute14 Attribute14,
sub.attribute15 Attribute15,
null ProgramId,
null FromEndItemUnitNumber,
sub.enforce_int_requirements EnforceIntRequirements,
sub.original_system_reference OriginalSystemReference,
NULL ReturnStatus,
'CREATE' TransactionType,
NULL RowIdentifier
FROM bom_substitute_components sub,
mtl_system_items_kfv item1,
mtl_parameters org,
bom_bill_of_materials bom,
bom_inventory_components comp,
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 org.organization_id = bom.organization_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item1.organization_id = bom.organization_id
AND item2.inventory_item_id = sub.substitute_component_id
AND item2.organization_id = bom.organization_id
AND item3.organization_id = bom.organization_id
AND item3.inventory_item_id = comp.component_item_id
AND bom.organization_id = p_model_org_id
AND sub.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
SELECT p_target_orgcode OrganizationCode,
item1.concatenated_segments AssemblyItemName,
TO_CHAR(comp.effectivity_date, 'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
comp.operation_seq_num OperationSequenceNumber,
item2.concatenated_segments ComponentItemName,
bom.alternate_bom_designator AlternateBomCode,
ref.component_reference_designator ReferenceDesignatorName,
ref.ref_designator_comment RefDesignatorComment,
ref.attribute_category AttributeCategory,
ref.attribute1 Attribute1,
ref.attribute2 Attribute2,
ref.attribute3 Attribute3,
ref.attribute4 Attribute4,
ref.attribute5 Attribute5,
ref.attribute6 Attribute6,
ref.attribute7 Attribute7,
ref.attribute8 Attribute8,
ref.attribute9 Attribute9,
ref.attribute10 Attribute10,
ref.attribute11 Attribute11,
ref.attribute12 Attribute12,
ref.attribute13 Attribute13,
ref.attribute14 Attribute14,
ref.attribute15 Attribute15,
null FromEndItemUnitNumber,
ref.original_system_reference OriginalSystemReference,
null NewReferenceDesignator,
NULL ReturnStatus,
'CREATE' TransactionType,
NULL RowIdentifier
FROM bom_reference_designators ref,
mtl_system_items_kfv item1,
mtl_parameters org,
bom_inventory_components comp,
mtl_system_items_kfv item2,
bom_bill_of_materials bom
WHERE comp.component_sequence_id = ref.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND org.organization_id = bom.organization_id
AND item1.organization_id = bom.organization_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item2.organization_id = bom.organization_id
AND item2.inventory_item_id = comp.component_item_id
AND bom.organization_id = p_model_org_id
AND ref.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
SELECT p_target_orgcode OrganizationCode,
item1.concatenated_segments AssemblyItemName,
to_char(comp.effectivity_date,'YYYY-MM-DD HH24:MI:SS') StartEffectiveDate,
null FromEndItemUnitNumber,
null ToEndItemUnitNumber,
comp.operation_seq_num OperationSequenceNumber,
comp_oper.operation_seq_num AdditionalOperationSeqNum,
NULL NewAdditionalOpSeqNum,
item2.concatenated_segments ComponentItemName,
bom.alternate_bom_designator AlternateBOMCode,
comp_oper.attribute_category Attributecategory,
comp_oper.attribute1 Attribute1,
comp_oper.attribute2 Attribute2,
comp_oper.attribute3 Attribute3,
comp_oper.attribute4 Attribute4,
comp_oper.attribute5 Attribute5,
comp_oper.attribute6 Attribute6,
comp_oper.attribute7 Attribute7,
comp_oper.attribute8 Attribute8,
comp_oper.attribute9 Attribute9,
comp_oper.attribute10 Attribute10,
comp_oper.attribute11 Attribute11,
comp_oper.attribute12 Attribute12,
comp_oper.attribute13 Attribute13,
comp_oper.attribute14 Attribute14,
comp_oper.attribute15 Attribute15,
NULL ReturnStatus,
'CREATE' TransactionType,
NULL RowIdentifier
FROM bom_component_operations comp_oper,
mtl_system_items_kfv item1,
mtl_parameters org,
bom_inventory_components comp,
mtl_system_items_kfv item2,
bom_bill_of_materials bom
WHERE comp.component_sequence_id = comp_oper.component_sequence_id
AND bom.bill_sequence_id = comp.bill_sequence_id
AND org.organization_id = bom.organization_id
AND item1.organization_id = bom.organization_id
AND item1.inventory_item_id = bom.assembly_item_id
AND item2.organization_id = bom.organization_id
AND item2.inventory_item_id = comp.component_item_id
AND bom.organization_id = p_model_org_id
AND comp_oper.COMPONENT_SEQUENCE_ID = COMPONENT_SEQUENCE_ID_VAR
AND comp_oper.BILL_SEQUENCE_ID = BILL_SEQUENCE_ID_VAR; --myerrams, Bug: 5142847
l_bom_header_tbl(i).delete_group_name := bom_header_rec.DeleteGroupName;
l_bom_comp_tbl(j).delete_group_name := bom_comp_rec.DeleteGroupName;
l_bom_header_tbl.DELETE;
l_bom_comp_tbl.DELETE;
l_bom_ref_desig_tbl.DELETE;
l_bom_sub_comp_tbl.DELETE;
l_bom_comp_oper_tbl.DELETE;
l_bom_header_tbl(1).transaction_type := 'UPDATE';
l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
l_bom_header_tbl.DELETE;
l_bom_comp_tbl.DELETE;
l_bom_ref_desig_tbl.DELETE;
l_bom_sub_comp_tbl.DELETE;
l_bom_comp_oper_tbl.DELETE;
l_bom_header_tbl(1).transaction_type := 'UPDATE';
l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
l_bom_header_tbl.DELETE;
l_bom_comp_tbl.DELETE;
l_bom_ref_desig_tbl.DELETE;
l_bom_sub_comp_tbl.DELETE;
l_bom_comp_oper_tbl.DELETE;
l_bom_header_tbl(1).transaction_type := 'UPDATE';
l_bom_comp_tbl(1) .transaction_type := 'UPDATE';
l_bom_header_tbl.DELETE;
l_bom_comp_tbl.DELETE;
l_bom_ref_desig_tbl.DELETE;
l_bom_sub_comp_tbl.DELETE;
l_bom_comp_oper_tbl.DELETE;
l_bom_header_tbl.DELETE;
l_bom_comp_tbl.DELETE;
l_bom_ref_desig_tbl.DELETE;
l_bom_sub_comp_tbl.DELETE;
l_bom_comp_oper_tbl.DELETE;
X_bom_header_tbl.DELETE;
X_bom_revisions_tbl.DELETE;
X_bom_comp_tbl.DELETE;
X_bom_ref_desig_tbl.DELETE;
X_bom_sub_comp_tbl.DELETE;
X_bom_comp_oper_tbl.DELETE;