[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 06-May-05 Abhishek Rudresh Common BOM Attr updates
+==========================================================================*/
-- ERES change begins :
G_PKG_NAME VARCHAR2(30) := 'BOMPCMBM';
* Added for bug 11895331. This Procedure updates the bom_structures_b.request_id column
* to original value after Common Bom process completes either successfully or
* with exception.
* Explicit commit is required, so that other ECO implementation process will be allowed
* to progress on the same revised item. Autonomous Transaction is not required as
* COMMIT or ROLLBACK will be performed before call to this procedure.
*
* @param p_req_id Request id to update.
* @param p_organization_id Master Bill Organization ID.
* @param p_assembly_item_id Master Bill Assembly Item Id.
* @param p_alternate Identify Alternate Bill default value NULL.
* @param p_seq_num Identify the place from where this proc being called.
*/
PROCEDURE Update_BSB_Request_Id_Column ( p_request_id IN NUMBER,
p_organization_id IN NUMBER,
p_assembly_item_id IN NUMBER,
p_alternate IN VARCHAR2 DEFAULT NULL,
p_sequence_num IN NUMBER,
p_commit IN VARCHAR2
) IS
P_COMMONBOM_IS_RUNNING CONSTANT NUMBER := -666;
FND_FILE.PUT_LINE( FND_FILE.LOG, '************* Update_BSB_Request_Id_Column procedure Start *************') ;
UPDATE BOM_BILL_OF_MATERIALS bbm
SET bbm.request_id = p_request_id
WHERE bbm.organization_id = p_organization_id
AND bbm.assembly_item_id = p_assembly_item_id
AND nvl(bbm.alternate_bom_designator,'NONE') = nvl(p_alternate,'NONE')
AND bbm.request_id = P_COMMONBOM_IS_RUNNING;
FND_FILE.PUT_LINE( FND_FILE.LOG, '************* Update_BSB_Request_Id_Column procedure End *************') ;
FND_FILE.PUT_LINE( FND_FILE.LOG, 'Exception occured in Update_BSB_Request_Id_Column proc') ;
FND_FILE.PUT_LINE( FND_FILE.LOG, '************* Update_BSB_Request_Id_Column procedure End *************') ;
END Update_BSB_Request_Id_Column;
, enable_attrs_update IN VARCHAR2
) IS
t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
delete_group_id NUMBER;
delete_entity_id NUMBER;
delete_error_rec NUMBER := 0;
SELECT request_id INTO p_orig_request_id
FROM BOM_BILL_OF_MATERIALS bbm1
WHERE bbm1.organization_id = current_org_id
AND bbm1.assembly_item_id = common_item_from
AND nvl(bbm1.alternate_bom_designator,'NONE') = nvl(alternate,'NONE');
If no row got updated means request_id column already stamped with
P_ECOIMPL_IS_RUNNING and some ECO implementation process is already in progress on that
assembly, In this case a exception will be raised and the request will be completed with error.
If a row got updated means no other ECO process is running on that assembly, In this case
P_COMMONBOM_IS_RUNNING value will be stamped on request_id column and Common BOM
request will be continued. To make visible the stamped request_id column value to all sessions
COMMIT is required. Actual Common BOM process not started yet, so autonomous transaction not
required in this case. */
update BOM_BILL_OF_MATERIALS bbm2
set bbm2.request_id = P_COMMONBOM_IS_RUNNING
WHERE nvl(bbm2.request_id, 0) <> P_ECOIMPL_IS_RUNNING
AND bbm2.organization_id = current_org_id
AND bbm2.assembly_item_id = common_item_from
AND nvl(bbm2.alternate_bom_designator,'NONE') = nvl(alternate,'NONE') ;
SELECT concatenated_segments
INTO common_item_from_name
FROM MTL_SYSTEM_ITEMS_VL
WHERE inventory_item_id = common_item_from
AND organization_id = current_org_id;
SELECT organization_code
INTO common_org_to_code
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE Organization_id = common_org_to ;
SELECT concatenated_segments, description
INTO common_item_to_name,l_item_desc
FROM MTL_SYSTEM_ITEMS_VL
WHERE inventory_item_id = common_item_to
AND organization_id = common_org_to ;
SELECT bill_sequence_id,assembly_type
INTO l_bill_sequence_id,l_assembly_type
FROM BOM_STRUCTURES_B
WHERE organization_id = current_org_id
AND assembly_item_id = common_item_from
AND nvl(alternate_bom_designator,'NONE') = nvl(alternate,'NONE') ;
SELECT organization_name
INTO l_org_name
FROM org_organization_definitions
WHERE organization_id = current_org_id;
SELECT
orgs.ORGANIZATION_ID
FROM
ORG_ACCESS_VIEW oav,
MTL_SYSTEM_ITEMS_B msi,
MTL_PARAMETERS orgs,
MTL_PARAMETERS child_org
WHERE
orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
AND msi.INVENTORY_ITEM_ID = common_item_from
AND orgs.ORGANIZATION_ID <> current_org_id
AND child_org.ORGANIZATION_ID = current_org_id
)
LOOP
N:=N+1;
SELECT organization_code
INTO l_org_code
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_id = current_org_id ;
SELECT organization_code
INTO l_org_code_to
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE organization_id = t_org_code_list(I) ;
SELECT concatenated_segments, DESCRIPTION
INTO l_assy_item_name, l_item_desc
FROM MTL_SYSTEM_ITEMS_VL
WHERE organization_id = t_org_code_list(I)
AND inventory_item_id = common_item_from ;
SELECT COUNT(*)
INTO l_bill_exists
FROM BOM_STRUCTURES_B
WHERE ASSEMBLY_ITEM_ID = common_item_from
AND ORGANIZATION_ID = t_org_code_list(I)
AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
SELECT COUNT(*)
INTO l_bill_exists
FROM BOM_STRUCTURES_B
WHERE ASSEMBLY_ITEM_ID = common_item_to
AND ORGANIZATION_ID = common_org_to
AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
SELECT common_bill_sequence_id, bill_sequence_id
INTO common_bill_seq_id, bill_seq_id
FROM BOM_STRUCTURES_B
WHERE ASSEMBLY_ITEM_ID = common_item_from
AND ORGANIZATION_ID = t_org_code_list(I)
AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
SELECT common_bill_sequence_id, bill_sequence_id
INTO common_bill_seq_id, bill_seq_id
FROM BOM_STRUCTURES_B
WHERE ASSEMBLY_ITEM_ID = common_item_to
AND ORGANIZATION_ID = common_org_to
AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
l_bill_exists := 0; -- Cannot delete as existing bill is not common
SELECT BILL_SEQUENCE_ID
INTO l_bill_seq_id
FROM BOM_STRUCTURES_B
WHERE ASSEMBLY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = l_org_id
AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
SELECT BOM_DELETE_GROUPS_S.NEXTVAL
INTO delete_group_id
FROM DUAL;
SELECT BOM_DELETE_ENTITIES_S.NEXTVAL
INTO delete_entity_id
FROM dual;
if (length(delete_group_id) > 7) Then
del_group_name := substr(delete_group_id,length(delete_group_id) - 6,7);
del_group_name := delete_group_id;
INSERT INTO BOM_DELETE_GROUPS
(DELETE_GROUP_SEQUENCE_ID,
DELETE_GROUP_NAME,
DELETE_ORG_TYPE,
ORGANIZATION_ID,
DELETE_TYPE,
ACTION_TYPE,
DELETE_COMMON_BILL_FLAG,
ENGINEERING_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
(delete_group_id,
l_organization_code||del_group_name,
1,--bug:4201690 The delete group procedure should get executed for every org.
t_org_code_list(I),
2,
1,
2,
l_assembly_type,
SYSDATE,
to_number(FND_PROFILE.Value('USER_ID')),
SYSDATE,
to_number(FND_PROFILE.Value('USER_ID')));
INSERT INTO bom_delete_entities
(DELETE_ENTITY_SEQUENCE_ID,
DELETE_GROUP_SEQUENCE_ID,
DELETE_ENTITY_TYPE,
DELETE_STATUS_TYPE,
BILL_SEQUENCE_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_DESCRIPTION,
ALTERNATE_DESIGNATOR,
ITEM_CONCAT_SEGMENTS,
PRIOR_PROCESS_FLAG,
PRIOR_COMMIT_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES
(delete_entity_id,
delete_group_id,
2,
1,
l_bill_seq_id,
l_item_id,
l_org_id,
l_item_desc,
nvl(alternate,NULL),
l_assembly_item_name,
1,
1,
SYSDATE,
to_number(FND_PROFILE.Value('USER_ID')),
SYSDATE,
to_number(FND_PROFILE.Value('USER_ID')));
Bom_Delete_Groups_Api.Delete_Groups
(ERRBUF => ERROR_MSG,
RETCODE => RETCOD,
delete_group_id => delete_group_id,
action_type => 2,
delete_type => 2,
archive => 2) ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' could not be deleted');
delete_error_rec := 0;
Select count(*)
into delete_error_rec
From bom_delete_errors
where DELETE_ENTITY_SEQUENCE_ID = delete_entity_id;
If (delete_error_rec = 0) then
to_proceed := 2;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' has been deleted succesfully');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' could not be deleted because of delete constraints');
l_bom_header_rec.delete_group_name := NULL ;
l_bom_header_rec.enable_attrs_update := enable_attrs_update;
( p_event_name => 'oracle.apps.bom.billUpdate'
, p_event_key => TO_CHAR( l_bill_sequence_id)
, p_event_status => 'SUCCESS');
( p_event_name => 'oracle.apps.bom.billUpdate'
, p_event_key => TO_CHAR( l_bill_sequence_id)
, p_event_status => 'FAILURE');
Update_BSB_Request_Id_Column( p_request_id => p_orig_request_id,
p_organization_id => current_org_id,
p_assembly_item_id => common_item_from,
p_alternate => alternate,
p_sequence_num => 1,
p_commit => FND_API.G_TRUE);
Update_BSB_Request_Id_Column( p_request_id => p_orig_request_id,
p_organization_id => current_org_id,
p_assembly_item_id => common_item_from,
p_alternate => alternate,
p_sequence_num => 2,
p_commit => FND_API.G_TRUE);
Update_BSB_Request_Id_Column( p_request_id => p_orig_request_id,
p_organization_id => current_org_id,
p_assembly_item_id => common_item_from,
p_alternate => alternate,
p_sequence_num => 3,
p_commit => FND_API.G_TRUE);
Update_BSB_Request_Id_Column( p_request_id => p_orig_request_id,
p_organization_id => current_org_id,
p_assembly_item_id => common_item_from,
p_alternate => alternate,
p_sequence_num => 4,
p_commit => FND_API.G_TRUE);
Update_BSB_Request_Id_Column( p_request_id => p_orig_request_id,
p_organization_id => current_org_id,
p_assembly_item_id => common_item_from,
p_alternate => alternate,
p_sequence_num => 5,
p_commit => FND_API.G_TRUE);
* This Procedure will modify the bill header attributes of a common BOM to make it updateable.
* @param p_bill_sequence_id IN Bill Sequence Id of the common BOM
*/
PROCEDURE Dereference_Header(p_bill_sequence_id NUMBER)
IS
BEGIN
Update BOM_STRUCTURES_B
Set source_bill_sequence_id = common_bill_Sequence_id,
common_bill_sequence_id = bill_sequence_id
Where bill_sequence_id = p_bill_sequence_id;
SELECT Bom_Inventory_Components_S.NEXTVAL Component_Sequence
FROM SYS.DUAL;
SELECT implementation_date
INTO l_impl_date
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_orig_old_comp_seq;
select max(component_sequence_id) into l_comp_seq_id from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq
AND bic.implementation_date is null;
select count(*) into l_count from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq;
select component_sequence_id into l_comp_seq_id from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq;
select max(old_component_sequence_id) into old_comp_seq from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq;
select count(*) into l_old_count from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq
and bic.old_component_sequence_id = old_comp_seq;
select component_sequence_id into l_comp_seq_id from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq
and bic.old_component_sequence_id = old_comp_seq;
select max(component_sequence_id) into l_comp_seq_id from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq
and bic.old_component_sequence_id = old_comp_seq;
SELECT component_sequence_id, wip_supply_type, supply_subinventory, supply_locator_id, include_in_cost_rollup, operation_seq_num
INTO x_old_comp_seq_id, x_wip_supply_type, x_wip_supply_subinv, x_wip_supply_locator_id, x_inc_in_cost_rollup, x_op_seq
FROM BOM_COMPONENTS_B bic
WHERE bic.component_sequence_id = l_comp_seq_id;
/* SELECT component_sequence_id, wip_supply_type, supply_subinventory, supply_locator_id, include_in_cost_rollup, operation_seq_num
INTO x_old_comp_seq_id, x_wip_supply_type, x_wip_supply_subinv, x_wip_supply_locator_id, x_inc_in_cost_rollup, x_op_seq
FROM BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = p_dest_bill_seq_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq --bic.old_component_sequence_id
AND (
(bic.implementation_date is not null
AND l_impl_date is not null
/*
*commented by jewen on 2008-11-6 to fix bug 7487640
AND sysdate between bic.effectivity_date and nvl (bic.disable_date, sysdate + 1)
*/ /*
)
OR
(l_impl_date is null
and bic.implementation_date is null
)
); */
SELECT distinct organization_id
FROM BOM_STRUCTURES_B
WHERE source_bill_sequence_id = p_bill_seq_id;
SELECT component_item_revision_id
INTO l_comp_rev_id
FROM bom_components_b
WHERE component_sequence_id = p_src_comp_seq_id;
SELECT *
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_sequence_id;
SELECT structure_type_id
from BOM_STRUCTURES_B
where bill_sequence_id = p_bill_seq_id;
SELECT *
FROM BOM_COMPONENTS_B
WHERE common_component_sequence_id = p_src_comp_seq_id
and component_sequence_id <> common_component_sequence_id
AND bill_sequence_id = p_dest_bill_sequence_id;
SELECT assembly_item_id, organization_id, alternate_bom_designator, effectivity_control
into l_assy_item_id, l_dest_org_id, l_alt_bom_desg, l_effectivity_ctrl
from BOM_STRUCTURES_B
where bill_sequence_id = p_dest_bill_sequence_id;
SELECT ITM.WIP_SUPPLY_TYPE, ITM.WIP_SUPPLY_LOCATOR_ID, ITM.WIP_SUPPLY_SUBINVENTORY
--INTO l_wip_supply_type, l_locator_id, l_supply_subinventory
FROM MTL_SYSTEM_ITEMS_B ITM
WHERE inventory_item_id = comp_rec.component_item_id
AND organization_id = l_dest_org_id;
/*insert into BOM_COMPONENTS_B();*/
INSERT INTO BOM_COMPONENTS_B
( SUPPLY_SUBINVENTORY
, OPERATION_LEAD_TIME_PERCENT
, REVISED_ITEM_SEQUENCE_ID
, COST_FACTOR
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, PROGRAM_APPLICATION_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, 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
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, INCLUDE_ON_BILL_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PICK_COMPONENTS
, Original_System_Reference
, From_End_Item_Unit_Number
, To_End_Item_Unit_Number
, Eco_For_Production -- Added by MK
, Enforce_Int_Requirements
, Auto_Request_Material -- Added in 11.5.9 by ADEY
, Obj_Name -- Added by hgelli.
, pk1_value
, pk2_value
, Suggested_Vendor_Name --- Deepu
, Vendor_Id --- Deepu
, Unit_Price --- Deepu
,from_object_revision_id
, from_minor_revision_id
, common_component_sequence_id
, basis_type
, component_item_revision_id
)
SELECT decode(default_wip_params, 1, item.wip_supply_subinventory, null)
, comp_rec.OPERATION_LEAD_TIME_PERCENT
, comp_rec.REVISED_ITEM_SEQUENCE_ID
, comp_rec.COST_FACTOR
, comp_rec.REQUIRED_FOR_REVENUE
, comp_rec.HIGH_QUANTITY
, Bom_Inventory_Components_S.NEXTVAL
, comp_rec.PROGRAM_APPLICATION_ID
--, decode(default_wip_params, 1, item.WIP_SUPPLY_TYPE, null)--supply type can be null --commented out for 9438586
, decode(default_wip_params, 1, item.WIP_SUPPLY_TYPE, comp_rec.WIP_SUPPLY_TYPE) --changes made for bug 9438586
, decode(default_wip_params, 1, item.WIP_SUPPLY_LOCATOR_ID, null)
, comp_rec.BOM_ITEM_TYPE
, comp_rec.OPERATION_SEQ_NUM
, comp_rec.COMPONENT_ITEM_ID
, comp_rec.LAST_UPDATE_DATE
, comp_rec.LAST_UPDATED_BY
, comp_rec.CREATION_DATE
, comp_rec.CREATED_BY
, comp_rec.LAST_UPDATE_LOGIN
, comp_rec.ITEM_NUM
, comp_rec.COMPONENT_QUANTITY
, comp_rec.COMPONENT_YIELD_FACTOR
, comp_rec.COMPONENT_REMARKS
, comp_rec.EFFECTIVITY_DATE
, comp_rec.CHANGE_NOTICE
, comp_rec.IMPLEMENTATION_DATE
, comp_rec.DISABLE_DATE
, comp_rec.ATTRIBUTE_CATEGORY
, comp_rec.ATTRIBUTE1
, comp_rec.ATTRIBUTE2
, comp_rec.ATTRIBUTE3
, comp_rec.ATTRIBUTE4
, comp_rec.ATTRIBUTE5
, comp_rec.ATTRIBUTE6
, comp_rec.ATTRIBUTE7
, comp_rec.ATTRIBUTE8
, comp_rec.ATTRIBUTE9
, comp_rec.ATTRIBUTE10
, comp_rec.ATTRIBUTE11
, comp_rec.ATTRIBUTE12
, comp_rec.ATTRIBUTE13
, comp_rec.ATTRIBUTE14
, comp_rec.ATTRIBUTE15
, comp_rec.PLANNING_FACTOR
, comp_rec.QUANTITY_RELATED
, comp_rec.SO_BASIS
, comp_rec.OPTIONAL
, comp_rec.MUTUALLY_EXCLUSIVE_OPTIONS
, comp_rec.INCLUDE_IN_COST_ROLLUP
, comp_rec.CHECK_ATP
, comp_rec.SHIPPING_ALLOWED
, comp_rec.REQUIRED_TO_SHIP
, comp_rec.INCLUDE_ON_SHIP_DOCS
, comp_rec.INCLUDE_ON_BILL_DOCS
, comp_rec.LOW_QUANTITY
, comp_rec.ACD_TYPE
, comp_rec.OLD_COMPONENT_SEQUENCE_ID
, p_dest_bill_sequence_id
, comp_rec.REQUEST_ID
, comp_rec.PROGRAM_ID
, comp_rec.PROGRAM_UPDATE_DATE
, comp_rec.PICK_COMPONENTS
, comp_rec.Original_System_Reference
, comp_rec.From_End_Item_Unit_Number
, comp_rec.To_End_Item_Unit_Number
, comp_rec.Eco_For_Production -- Added by MK
, comp_rec.Enforce_Int_Requirements
, comp_rec.Auto_Request_Material -- Added in 11.5.9 by ADEY
, comp_rec.Obj_Name -- Added by hgelli.
, comp_rec.pk1_value
, l_dest_org_id
, comp_rec.Suggested_Vendor_Name --- Deepu
, comp_rec.Vendor_Id --- Deepu
, comp_rec.Unit_Price --- Deepu
, comp_rec.from_object_revision_id
, comp_rec.from_minor_revision_id
, comp_rec.component_sequence_id
, comp_rec.basis_type
, decode(comp_rec.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(comp_rec.component_item_revision_id, l_dest_org_id))
FROM BOM_COMPONENTS_B comp_rec, MTL_SYSTEM_ITEMS_B item, BOM_STRUCTURES_B bom
WHERE comp_rec.bill_sequence_id = p_src_bill_sequence_id
AND bom.bill_sequence_id = comp_rec.bill_sequence_id
AND comp_rec.COMPONENT_ITEM_ID = item.inventory_item_id
AND item.organization_id = l_dest_org_id
--and comp_rec.implementation_date is not null
;
UPDATE BOM_COMPONENTS_B
SET old_component_sequence_id = destn_comp.old_component_sequence_id,
wip_supply_type = destn_comp.wip_supply_type,
supply_subinventory = destn_comp.supply_subinventory,
supply_locator_id = destn_comp.supply_locator_id,
include_in_cost_rollup = destn_comp.include_in_cost_rollup,
operation_seq_num = destn_comp.operation_seq_num
WHERE component_sequence_id = destn_comp.component_sequence_id;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
PROCEDURE Insert_Related_Components
( p_src_bill_seq_id IN NUMBER
, p_src_comp_seq_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
Cursor get_related_bills(p_src_bill_sequence_id NUMBER) IS
Select *
from BOM_STRUCTURES_B
where source_bill_sequence_id <> common_bill_sequence_id
and source_bill_sequence_id = p_src_bill_sequence_id;
Select *
From BOM_COMPONENTS_B
where component_sequence_id = p_src_comp_seq_id;
SELECT operation_seq_num
INTO l_operation_seq_num
FROM BOM_COMPONENTS_B
WHERE component_Sequence_id = p_src_comp_seq_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv item, bom_components_b comp
where item.inventory_item_id = comp.pk1_value
and item.organization_id = comp.pk2_value
and comp.component_sequence_id = p_src_comp_seq_id;
l_token_tbl.DELETE;
SELECT WIP_SUPPLY_TYPE, WIP_SUPPLY_LOCATOR_ID, WIP_SUPPLY_SUBINVENTORY
INTO l_wip_supply_type, l_locator_id, l_supply_subinventory
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = src_comp_details.component_item_id
AND organization_id = bill_rec.organization_id;
INSERT INTO BOM_COMPONENTS_B
( SUPPLY_SUBINVENTORY
, OPERATION_LEAD_TIME_PERCENT
, REVISED_ITEM_SEQUENCE_ID
, COST_FACTOR
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, PROGRAM_APPLICATION_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, 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
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, INCLUDE_ON_BILL_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PICK_COMPONENTS
, Original_System_Reference
, From_End_Item_Unit_Number
, To_End_Item_Unit_Number
, Eco_For_Production -- Added by MK
, Enforce_Int_Requirements
, Auto_Request_Material -- Added in 11.5.9 by ADEY
, Obj_Name -- Added by hgelli.
, pk1_value
, pk2_value
, Suggested_Vendor_Name --- Deepu
, Vendor_Id --- Deepu
-- , Purchasing_Category_id --- Deepu
, Unit_Price --- Deepu
,from_object_revision_id
, from_minor_revision_id
--,component_item_revision_id
--,component_minor_revision_id
, common_component_sequence_id
, basis_type
, component_item_revision_id
)
VALUES
( src_comp_details.supply_subinventory
, src_comp_details.OPERATION_LEAD_TIME_PERCENT --check this
, src_comp_details.revised_item_sequence_id
, NULL /* Cost Factor */
, src_comp_details.required_for_revenue
, src_comp_details.HIGH_QUANTITY
, src_comp_details.component_sequence_id
, BOM_Globals.Get_Prog_AppId
, src_comp_details.wip_supply_type
, DECODE(src_comp_details.supply_locator_id, FND_API.G_MISS_NUM,
NULL, src_comp_details.supply_locator_id)
, src_comp_details.bom_item_type
, src_comp_details.operation_seq_num --Check this too
, src_comp_details.component_item_id
, SYSDATE /* Last Update Date */
, src_comp_details.last_updated_by /* Last Updated By */
, SYSDATE /* Creation Date */
, src_comp_details.created_by /* Created By */
, src_comp_details.last_update_login /* Last Update Login */
, DECODE(src_comp_details.ITEM_NUM, FND_API.G_MISS_NUM,
1, NULL,1,src_comp_details.ITEM_NUM)
, src_comp_details.component_quantity
, src_comp_details.COMPONENT_YIELD_FACTOR
, src_comp_details.COMPONENT_REMARKS
, nvl(src_comp_details.effectivity_date,SYSDATE) --2169237
, src_comp_details.Change_Notice
, src_comp_details.implementation_date/* Implementation Date */
/*
, DECODE(l_Bo_Id,
Bom_Globals.G_BOM_BO,
SYSDATE,
NULL
) -- Implementation Date
*/
, src_comp_details.disable_date
, src_comp_details.attribute_category
, src_comp_details.attribute1
, src_comp_details.attribute2
, src_comp_details.attribute3
, src_comp_details.attribute4
, src_comp_details.attribute5
, src_comp_details.attribute6
, src_comp_details.attribute7
, src_comp_details.attribute8
, src_comp_details.attribute9
, src_comp_details.attribute10
, src_comp_details.attribute11
, src_comp_details.attribute12
, src_comp_details.attribute13
, src_comp_details.attribute14
, src_comp_details.attribute15
, src_comp_details.planning_factor
, src_comp_details.quantity_related
, src_comp_details.so_basis
, src_comp_details.optional
, src_comp_details.mutually_exclusive_options
, src_comp_details.include_in_cost_rollup
, src_comp_details.check_atp
, src_comp_details.shipping_allowed
, src_comp_details.required_to_ship
, src_comp_details.include_on_ship_docs
, NULL /* Include On Bill Docs */
, src_comp_details.low_quantity
, src_comp_details.acd_type
-- , DECODE( p_rev_comp_Unexp_rec.old_component_sequence_id
-- , FND_API.G_MISS_NUM
-- , NULL
-- ,p_rev_comp_Unexp_rec.old_component_sequence_id
-- )
, l_old_component_sequence_id --Chk this
, src_comp_details.bill_sequence_id
, NULL /* Request Id */
, BOM_Globals.Get_Prog_Id
, SYSDATE /* program_update_date */
, src_comp_details.pick_components
, src_comp_details.original_system_reference
, DECODE( src_comp_details.from_end_item_unit_number
, FND_API.G_MISS_CHAR
, null
, src_comp_details.from_end_item_unit_number
)
, DECODE( src_comp_details.to_end_item_unit_number
, FND_API.G_MISS_CHAR
, null
, src_comp_details.to_end_item_unit_number
)
, BOM_Globals.Get_Eco_For_Production
-- DECODE( l_Bo_Id, BOM_Globals.G_ECO_BO, l_Eco_For_Production, 2) /* Eco for Production flag */
, src_comp_details.Enforce_Int_Requirements
, src_comp_details.auto_request_material -- Added in 11.5.9 by ADEY
, NULL-- Added by hgelli. Identifies this record as Bom Component.
, src_comp_details.component_item_id
, bill_rec.organization_id
, src_comp_details.Suggested_Vendor_Name --- Deepu
, src_comp_details.Vendor_Id --- Deepu
-- , p_rev_component_rec.purchasing_category_id --- Deepu
, src_comp_details.Unit_Price --- Deepu
,src_comp_details.from_object_revision_id
,src_comp_details.from_minor_revision_id
, src_comp_details.common_component_sequence_id
, src_comp_details.basis_type
, decode(src_comp_details.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(src_comp_details.component_item_revision_id, bill_rec.organization_id))
--,l_comp_revision_id
--,l_comp_minor_revision_id
);
UPDATE BOM_COMPONENTS_B
SET old_component_sequence_id = src_comp_details.old_component_sequence_id,
wip_supply_type = src_comp_details.wip_supply_type,
supply_subinventory = src_comp_details.supply_subinventory,
supply_locator_id = src_comp_details.supply_locator_id,
include_in_cost_rollup = src_comp_details.include_in_cost_rollup,
operation_seq_num = src_comp_details.operation_seq_num
WHERE COMPONENT_SEQUENCE_ID = src_comp_details.component_Sequence_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv
where inventory_item_id = src_comp_details.component_item_id
and organization_id = l_dest_org_id;
SELECT concatenated_segments
into l_dest_assy_item
from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
where item.inventory_item_id = bom.assembly_item_id
and item.organization_id = bom.organization_id
and bom.bill_sequence_id = src_comp_details.bill_sequence_id;
SELECT organization_code
into l_dest_org_code
from mtl_parameters
where organization_id = l_dest_org_id;
SELECT 'Y'
INTO l_dummy
FROM BOM_COMPONENTS_B
WHERE wip_supply_type = 6
AND basis_type = 2
AND component_sequence_id = src_comp_details.component_sequence_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv
where inventory_item_id = src_comp_details.component_item_id
and organization_id = l_dest_org_id;
SELECT concatenated_segments
into l_dest_assy_item
from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
where item.inventory_item_id = bom.assembly_item_id
and item.organization_id = bom.organization_id
and bom.bill_sequence_id = src_comp_details.bill_sequence_id;
SELECT organization_code
into l_dest_org_code
from mtl_parameters
where organization_id = l_dest_org_id;
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
l_err_text := G_PKG_NAME ||' : Utility (Related Component Insert) '
||SUBSTR(SQLERRM, 1, 200);
END Insert_Related_Components;
PROCEDURE Insert_Related_Components( p_src_bill_seq_id IN NUMBER
, p_src_comp_seq_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Related_Components(p_src_bill_seq_id => p_src_bill_seq_id
, p_src_comp_seq_id => p_src_comp_seq_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
END Insert_Related_Components;
PROCEDURE Update_Impl_Rel_Comp
( p_src_comp_seq_id IN NUMBER
)
IS
Cursor get_related_Components(p_src_comp_seq_id NUMBER) IS
SELECT *
FROM BOM_COMPONENTS_B
WHERE common_component_sequence_id = p_src_comp_seq_id
AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID order by bill_sequence_id;
SELECT *
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_src_comp_seq_id;
SELECT *
FROM BOM_COMPONENTS_B
WHERE common_component_sequence_id = p_src_comp_seq_id
AND bill_sequence_id = p_dest_bill_seq_id
AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID;*/
select max(implementation_date) into b_impl_date from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = dest_comp.bill_sequence_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq_id
AND bic.implementation_date is not null;
select component_sequence_id into l_comp_seq_id from BOM_COMPONENTS_B bic
WHERE bic.bill_sequence_id = dest_comp.bill_sequence_id
AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq_id
AND bic.implementation_date = b_impl_date;
UPDATE BOM_COMPONENTS_B dest_cmpo
SET DISABLE_DATE = DECODE(src_comp.IMPLEMENTATION_DATE,
NULL, src_comp.DISABLE_DATE,
DECODE(DISABLE_DATE,
NULL, src_comp.DISABLE_DATE,
Greatest(src_comp.EFFECTIVITY_DATE, DISABLE_DATE), DECODE(DISABLE_DATE,
GREATEST(DISABLE_DATE, SYSDATE), src_comp.DISABLE_DATE,
DISABLE_DATE
),
DISABLE_DATE
)
),
to_object_revision_id = src_comp.to_object_revision_id,
overlapping_changes = src_comp.overlapping_changes,
change_notice = src_comp.change_notice,
last_update_date = sysdate,
last_updated_by = src_comp.last_updated_by,
last_update_login = src_comp.last_update_login,
request_id = src_comp.request_id,
program_application_id = src_comp.program_application_id,
program_id = src_comp.program_id,
program_update_date = sysdate
WHERE COMPONENT_SEQUENCE_ID = l_comp_seq_id
AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID
;
END Update_Impl_Rel_Comp;
* This Procedure should be called when a component is updated in a bom that is commoned by other boms.
* This will update the component in the common boms.
* @param p_src_comp_seq_id IN Component Sequence Id of the component updated
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Update_Related_Components
( p_src_comp_seq_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2
)
IS
Cursor get_related_Components(p_src_comp_seq_id NUMBER) IS
SELECT *
FROM BOM_COMPONENTS_B
WHERE common_component_sequence_id = p_src_comp_seq_id
AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID;
SELECT *
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_src_comp_seq_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv item, bom_components_b comp
where item.inventory_item_id = comp.pk1_value
and item.organization_id = comp.pk2_value
and comp.component_sequence_id = p_src_comp_seq_id;
l_token_tbl.DELETE;
UPDATE BOM_COMPONENTS_B dest_cmpo
SET REQUIRED_FOR_REVENUE = src_comp.required_for_revenue
, HIGH_QUANTITY = src_comp.HIGH_QUANTITY
/* , WIP_SUPPLY_TYPE = p_rev_component_rec.wip_supply_type
, SUPPLY_LOCATOR_ID =
DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
NULL, p_rev_comp_Unexp_rec.supply_locator_id)
, OPERATION_SEQ_NUM = l_operation_seq_num*/
, EFFECTIVITY_DATE = src_comp.effectivity_date
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = src_comp.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = src_comp.LAST_UPDATE_LOGIN
, ITEM_NUM = src_comp.ITEM_NUM
, COMPONENT_QUANTITY = src_comp.COMPONENT_QUANTITY
, COMPONENT_YIELD_FACTOR = src_comp.COMPONENT_YIELD_FACTOR
, COMPONENT_REMARKS = src_comp.COMPONENT_REMARKS
--Modified DECODE function for bug 9786178
, DISABLE_DATE = DECODE(src_comp.IMPLEMENTATION_DATE,
NULL, src_comp.DISABLE_DATE,
DECODE(DISABLE_DATE,
NULL, src_comp.DISABLE_DATE,
Greatest(src_comp.EFFECTIVITY_DATE, DISABLE_DATE), src_comp.DISABLE_DATE,
DISABLE_DATE
)
)
, ATTRIBUTE_CATEGORY = src_comp.attribute_category
, ATTRIBUTE1 = src_comp.attribute1
, ATTRIBUTE2 = src_comp.attribute2
, ATTRIBUTE3 = src_comp.attribute3
, ATTRIBUTE4 = src_comp.attribute4
, ATTRIBUTE5 = src_comp.attribute5
, ATTRIBUTE6 = src_comp.attribute6
, ATTRIBUTE7 = src_comp.attribute7
, ATTRIBUTE8 = src_comp.attribute8
, ATTRIBUTE9 = src_comp.attribute9
, ATTRIBUTE10 = src_comp.attribute10
, ATTRIBUTE11 = src_comp.attribute11
, ATTRIBUTE12 = src_comp.attribute12
, ATTRIBUTE13 = src_comp.attribute13
, ATTRIBUTE14 = src_comp.attribute14
, ATTRIBUTE15 = src_comp.attribute15
, PLANNING_FACTOR = src_comp.planning_factor
, QUANTITY_RELATED = src_comp.quantity_related
, SO_BASIS = src_comp.so_basis
, OPTIONAL = src_comp.optional
, MUTUALLY_EXCLUSIVE_OPTIONS = src_comp.mutually_exclusive_options
--, INCLUDE_IN_COST_ROLLUP = src_comp.include_in_cost_rollup
, CHECK_ATP = src_comp.check_atp
, SHIPPING_ALLOWED = src_comp.shipping_allowed
, REQUIRED_TO_SHIP = src_comp.required_to_ship
, INCLUDE_ON_SHIP_DOCS = src_comp.include_on_ship_docs
, LOW_QUANTITY = src_comp.LOW_QUANTITY
, ACD_TYPE = src_comp.acd_type
, PROGRAM_UPDATE_DATE = SYSDATE
, PROGRAM_ID = BOM_Globals.Get_Prog_Id
, OPERATION_LEAD_TIME_PERCENT = src_comp.operation_lead_time_percent
, Original_System_Reference =
src_comp.original_system_reference
, From_End_Item_Unit_Number = src_comp.From_End_Item_Unit_Number
, To_End_Item_Unit_Number = src_comp.To_End_Item_Unit_Number
, Enforce_Int_Requirements = src_comp.Enforce_Int_Requirements
, Auto_Request_Material = src_comp.auto_request_material -- Added in 11.5.9 by ADEY
, Suggested_Vendor_Name = src_comp.Suggested_Vendor_Name --- Deepu
, Vendor_Id = src_comp.Vendor_Id --- Deepu
-- , Purchasing_Category_id = src_comp.purchasing_category_id --- Deepu
, Unit_Price = src_comp.Unit_Price --- Deepu
, Basis_type = src_comp.Basis_type
, COMPONENT_ITEM_REVISION_ID = decode(src_comp.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(src_comp.component_item_revision_id, dest_comp.pk2_value))
WHERE COMPONENT_SEQUENCE_ID = dest_comp.component_sequence_id
AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID
;
SELECT Organization_id
INTO l_dest_org_id
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = dest_comp.bill_sequence_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv
where inventory_item_id = src_comp.component_item_id
and organization_id = l_dest_org_id;
SELECT concatenated_segments
into l_dest_assy_item
from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
where item.inventory_item_id = bom.assembly_item_id
and item.organization_id = bom.organization_id
and bom.bill_sequence_id = src_comp.bill_sequence_id;
SELECT organization_code
into l_dest_org_code
from mtl_parameters
where organization_id = l_dest_org_id;
SELECT basis_type
INTO l_dummy
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = dest_comp.component_sequence_id;
SELECT 'Y'
INTO l_dummy
FROM BOM_COMPONENTS_B
WHERE wip_supply_type = 6
AND basis_type = 2
AND component_sequence_id = dest_comp.component_sequence_id;
SELECT Organization_id
INTO l_dest_org_id
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = dest_comp.bill_sequence_id;
SELECT concatenated_segments
into l_comp_name
from mtl_system_items_kfv
where inventory_item_id = src_comp.component_item_id
and organization_id = l_dest_org_id;
SELECT concatenated_segments
into l_dest_assy_item
from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
where item.inventory_item_id = bom.assembly_item_id
and item.organization_id = bom.organization_id
and bom.bill_sequence_id = src_comp.bill_sequence_id;
SELECT organization_code
into l_dest_org_code
from mtl_parameters
where organization_id = l_dest_org_id;
UPDATE BOM_COMPONENTS_B bic
SET (wip_supply_type, supply_locator_id, supply_subinventory, operation_seq_num, include_in_cost_rollup) =
(SELECT wip_supply_type, supply_locator_id, supply_subinventory, operation_seq_num, include_in_cost_rollup
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_src_comp_Seq_id)
WHERE old_component_sequence_id = p_src_comp_Seq_id
AND implementation_date IS NULL
AND nvl(common_component_sequence_id, component_sequence_id) <> component_sequence_id
AND bill_sequence_id NOT IN (SELECT bill_sequence_id
FROM eng_revised_items
WHERE change_notice = bic.change_notice);
l_err_text := G_PKG_NAME ||' : Utility (Related Component Update) '
||SUBSTR(SQLERRM, 1, 200);
END Update_Related_Components;
* This overloaded Procedure should be called from java when a component is updated in a bom that is commoned by other boms.
* This will update the component in the common boms.
* @param p_src_comp_seq_id IN Component Sequence Id of the component updated
*/
PROCEDURE Update_Related_Components( p_src_comp_seq_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Update_Related_Components( p_src_comp_seq_id => p_src_comp_seq_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
END Update_Related_Components;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
Procedure Replicate_Ref_Desg(p_component_sequence_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
INSERT INTO BOM_REFERENCE_DESIGNATORS
( COMPONENT_REFERENCE_DESIGNATOR
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REF_DESIGNATOR_COMMENT
, CHANGE_NOTICE
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, Original_System_Reference
, Common_component_sequence_id
)
SELECT
ref_desg.component_reference_designator
, SYSDATE
, ref_desg.LAST_UPDATED_BY
, SYSDATE
, ref_desg.CREATED_BY
, ref_desg.LAST_UPDATE_LOGIN
, DECODE( ref_desg.ref_designator_comment
, FND_API.G_MISS_CHAR
, NULL
, ref_desg.ref_designator_comment )
, ref_desg.change_notice
, comp.component_sequence_id
, ref_desg.acd_type
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, Bom_Globals.Get_Prog_Id
, SYSDATE
, ref_desg.attribute_category
, ref_desg.attribute1
, ref_desg.attribute2
, ref_desg.attribute3
, ref_desg.attribute4
, ref_desg.attribute5
, ref_desg.attribute6
, ref_desg.attribute7
, ref_desg.attribute8
, ref_desg.attribute9
, ref_desg.attribute10
, ref_desg.attribute11
, ref_desg.attribute12
, ref_desg.attribute13
, ref_desg.attribute14
, ref_desg.attribute15
, ref_desg.Original_System_Reference
, p_component_sequence_id
FROM BOM_COMPONENTS_B comp, BOM_REFERENCE_DESIGNATORS ref_desg
WHERE comp.component_sequence_id <> comp.common_component_sequence_id
AND comp.common_component_sequence_id = ref_desg.component_sequence_id
AND ref_desg.component_sequence_id = p_component_sequence_id
AND NOT EXISTS
(
SELECT 1
FROM bom_reference_designators ref2
where ref2.component_sequence_id = comp.component_sequence_id
and ref2.component_reference_designator = ref_desg.component_reference_designator
)
;
/*insert into bom_reference_designators()
values();*/
* @param p_component_sequence_id IN Component Sequence Id of the component updated.
*/
Procedure Replicate_Ref_Desg(p_component_sequence_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_ref_desg IN Reference Designator added.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Insert_Related_Ref_Desg(p_component_sequence_id IN NUMBER
, p_ref_desg IN VARCHAR2
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_reference_designators
where component_sequence_id = p_comp_seq_id
and component_reference_designator = p_ref_desg;
select dest.component_sequence_id
from BOM_COMPONENTS_B dest, BOM_COMPONENTS_B src
where dest.component_sequence_id <> dest.common_component_sequence_id
and dest.common_component_sequence_id = p_comp_seq_id
and src.component_sequence_id = dest.common_component_sequence_id
and ((src.implementation_date is null
and dest.implementation_date is null
)
OR
dest.implementation_date is not null
);
INSERT INTO BOM_REFERENCE_DESIGNATORS
( COMPONENT_REFERENCE_DESIGNATOR
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REF_DESIGNATOR_COMMENT
, CHANGE_NOTICE
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, Original_System_Reference
, Common_component_sequence_id
)
VALUES
( ref_desg.component_reference_designator
, SYSDATE
, ref_desg.LAST_UPDATED_BY
, SYSDATE
, ref_desg.CREATED_BY
, ref_desg.LAST_UPDATE_LOGIN
, DECODE( ref_desg.ref_designator_comment
, FND_API.G_MISS_CHAR
, NULL
, ref_desg.ref_designator_comment )
, ref_desg.Change_Notice
, dest_comp.component_sequence_id
, ref_desg.acd_type
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, Bom_Globals.Get_Prog_Id
, SYSDATE
, ref_desg.attribute_category
, ref_desg.attribute1
, ref_desg.attribute2
, ref_desg.attribute3
, ref_desg.attribute4
, ref_desg.attribute5
, ref_desg.attribute6
, ref_desg.attribute7
, ref_desg.attribute8
, ref_desg.attribute9
, ref_desg.attribute10
, ref_desg.attribute11
, ref_desg.attribute12
, ref_desg.attribute13
, ref_desg.attribute14
, ref_desg.attribute15
, ref_desg.Original_System_Reference
, ref_desg.component_sequence_id
);
l_err_text := G_PKG_NAME ||' : Common BOM (Related Ref Desg Insert) '
||SUBSTR(SQLERRM, 1, 200);
END Insert_Related_Ref_Desg;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_ref_desg IN Reference Designator added.
*/
PROCEDURE Insert_Related_Ref_Desg(p_component_sequence_id IN NUMBER
, p_ref_desg IN VARCHAR2)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Related_Ref_Desg(p_component_sequence_id => p_component_sequence_id
, p_ref_desg => p_ref_desg
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
END Insert_Related_Ref_Desg;
* This Procedure is used to update reference designators of the related components of the common boms whenever
* reference designator of a component of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_ref_desg IN Reference Designator updated.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Update_Related_Ref_Desg(p_component_sequence_id IN NUMBER
, p_old_ref_desg IN VARCHAR2
, p_new_ref_desg IN VARCHAR2
, p_acd_type IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_reference_designators
where component_sequence_id = p_comp_seq_id
and component_reference_designator = p_ref_desg;
select component_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_comp_seq_id;
UPDATE BOM_REFERENCE_DESIGNATORS
SET COMPONENT_REFERENCE_DESIGNATOR = ref_desg.COMPONENT_REFERENCE_DESIGNATOR
, LAST_UPDATE_DATE = SYSDATE
, LAST_UPDATED_BY = ref_desg.LAST_UPDATED_BY
, LAST_UPDATE_LOGIN = ref_desg.LAST_UPDATE_LOGIN
, REF_DESIGNATOR_COMMENT = ref_desg.REF_DESIGNATOR_COMMENT
, ATTRIBUTE_CATEGORY = ref_desg.attribute_category
, ATTRIBUTE1 = ref_desg.attribute1
, ATTRIBUTE2 = ref_desg.attribute2
, ATTRIBUTE3 = ref_desg.attribute3
, ATTRIBUTE4 = ref_desg.attribute4
, ATTRIBUTE5 = ref_desg.attribute5
, ATTRIBUTE6 = ref_desg.attribute6
, ATTRIBUTE7 = ref_desg.attribute7
, ATTRIBUTE8 = ref_desg.attribute8
, ATTRIBUTE9 = ref_desg.attribute9
, ATTRIBUTE10 = ref_desg.attribute10
, ATTRIBUTE11 = ref_desg.attribute11
, ATTRIBUTE12 = ref_desg.attribute12
, ATTRIBUTE13 = ref_desg.attribute13
, ATTRIBUTE14 = ref_desg.attribute14
, ATTRIBUTE15 = ref_desg.attribute15
, Original_System_Reference =
ref_desg.Original_System_Reference
WHERE COMPONENT_REFERENCE_DESIGNATOR = p_old_ref_desg
AND COMMON_COMPONENT_SEQUENCE_ID = p_component_sequence_id
AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
AND NVL(ACD_TYPE, 0) = nvl(p_acd_type, 0);
, p_Message_Text => 'ERROR in Update Row (Related Ref Desgs)' ||
substr(SQLERRM, 1, 100) || ' ' ||
to_char(SQLCODE)
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => x_Mesg_Token_Tbl);
END Update_Related_Ref_Desg;
* This overloaded Procedure is called from Java to update reference designators of the related components of the common boms whenever
* reference designator of a component of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_ref_desg IN Reference Designator updated.
*/
PROCEDURE Update_Related_Ref_Desg(p_component_sequence_id IN NUMBER
, p_old_ref_desg IN VARCHAR2
, p_new_ref_desg IN VARCHAR2
, p_acd_type IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Update_Related_Ref_Desg(p_component_sequence_id => p_component_sequence_id
, p_new_ref_desg => p_new_ref_desg
, p_old_ref_desg => p_old_ref_desg
, p_acd_type => p_acd_type
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Replicate_Sub_Comp(p_component_sequence_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
Cursor get_sub_comps(p_component_sequence_id NUMBER)
is
SELECT * from bom_substitute_components
where component_sequence_id = p_component_sequence_id;
select component_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_component_sequence_id;
INSERT INTO BOM_SUBSTITUTE_COMPONENTS
( SUBSTITUTE_COMPONENT_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, SUBSTITUTE_ITEM_QUANTITY
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, CHANGE_NOTICE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PROGRAM_ID
, Original_System_Reference
, Enforce_Int_Requirements
, Common_component_sequence_id
)
SELECT
sub_comp.substitute_component_id
, SYSDATE
, sub_comp.LAST_UPDATED_BY
, SYSDATE
, sub_comp.CREATED_BY
, sub_comp.LAST_UPDATE_LOGIN
, sub_comp.substitute_item_quantity
, dest_comp.component_sequence_id
, sub_comp.acd_type
, sub_comp.Change_Notice
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, SYSDATE
, sub_comp.attribute_category
, sub_comp.attribute1
, sub_comp.attribute2
, sub_comp.attribute3
, sub_comp.attribute4
, sub_comp.attribute5
, sub_comp.attribute6
, sub_comp.attribute7
, sub_comp.attribute8
, sub_comp.attribute9
, sub_comp.attribute10
, sub_comp.attribute11
, sub_comp.attribute12
, sub_comp.attribute13
, sub_comp.attribute14
, sub_comp.attribute15
, Bom_Globals.Get_Prog_Id
, sub_comp.Original_System_Reference
, sub_comp.enforce_int_requirements
, sub_comp.component_sequence_id
FROM BOM_SUBSTITUTE_COMPONENTS sub_comp, BOM_COMPONENTS_B dest_comp
WHERE dest_comp.component_Sequence_id <> dest_comp.common_component_sequence_id
AND dest_comp.common_component_sequence_id = sub_comp.component_sequence_id
AND sub_comp.component_sequence_id = p_component_sequence_id
AND NOT EXISTS
(
SELECT 1
FROM bom_substitute_components bsc2
where bsc2.component_sequence_id = dest_comp.component_sequence_id
and bsc2.substitute_component_id = sub_comp.substitute_component_id
)
;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
*/
PROCEDURE Replicate_Sub_Comp(p_component_sequence_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_sub_comp_item_id IN Substitute Component Id added.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Insert_Related_Sub_Comp(p_component_sequence_id IN NUMBER
, p_sub_comp_item_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_substitute_components
where component_sequence_id = p_comp_seq_id
and substitute_component_id = p_sub_comp_item_id;
select dest.component_sequence_id
from BOM_COMPONENTS_B dest, BOM_COMPONENTS_B src
where dest.component_sequence_id <> dest.common_component_sequence_id
and dest.common_component_sequence_id = p_comp_seq_id
and src.component_sequence_id = dest.common_component_sequence_id
and ((src.implementation_date is null
and dest.implementation_date is null
)
OR
dest.implementation_date is not null
);
INSERT INTO BOM_SUBSTITUTE_COMPONENTS
( SUBSTITUTE_COMPONENT_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, SUBSTITUTE_ITEM_QUANTITY
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, CHANGE_NOTICE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PROGRAM_ID
, Original_System_Reference
, Enforce_Int_Requirements
, Common_component_sequence_id
)
VALUES
( sub_comp.substitute_component_id
, SYSDATE
, sub_comp.LAST_UPDATED_BY
, SYSDATE
, sub_comp.CREATED_BY
, sub_comp.LAST_UPDATE_LOGIN
, sub_comp.substitute_item_quantity
, dest_comp.component_sequence_id
, sub_comp.acd_type
, sub_comp.Change_Notice
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, SYSDATE
, sub_comp.attribute_category
, sub_comp.attribute1
, sub_comp.attribute2
, sub_comp.attribute3
, sub_comp.attribute4
, sub_comp.attribute5
, sub_comp.attribute6
, sub_comp.attribute7
, sub_comp.attribute8
, sub_comp.attribute9
, sub_comp.attribute10
, sub_comp.attribute11
, sub_comp.attribute12
, sub_comp.attribute13
, sub_comp.attribute14
, sub_comp.attribute15
, Bom_Globals.Get_Prog_Id
, sub_comp.Original_System_Reference
, sub_comp.enforce_int_requirements
, sub_comp.component_sequence_id
);
l_err_text := G_PKG_NAME ||'Utility (Related Substitute Component Insert)'
||SUBSTR(SQLERRM, 1, 100);
END Insert_Related_Sub_Comp;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_sub_comp_item_id IN Substitute Component Id added.
*/
PROCEDURE Insert_Related_Sub_Comp(p_component_sequence_id IN NUMBER
, p_sub_comp_item_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Related_Sub_Comp(p_component_sequence_id => p_component_sequence_id
, p_sub_comp_item_id => p_sub_comp_item_id
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
* This Procedure is used to update substitutes of the related components of the common boms whenever
* substitute of a component of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_sub_comp_item_id IN Substitute Component Id updated.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Update_Related_Sub_Comp(p_component_sequence_id IN NUMBER
, p_old_sub_comp_item_id IN NUMBER
, p_new_sub_comp_item_id IN NUMBER
, p_acd_type IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_substitute_components
where component_sequence_id = p_comp_seq_id
and substitute_component_id = p_new_sub_comp_item_id
and nvl(acd_type, 0) = nvl(p_acd_type, 0);
select component_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_comp_seq_id;
UPDATE BOM_SUBSTITUTE_COMPONENTS
SET SUBSTITUTE_COMPONENT_ID = sub_comp.substitute_component_id
, SUBSTITUTE_ITEM_QUANTITY = sub_comp.substitute_item_quantity
, ATTRIBUTE_CATEGORY = sub_comp.attribute_category
, ATTRIBUTE1 = sub_comp.attribute1
, ATTRIBUTE2 = sub_comp.attribute2
, ATTRIBUTE3 = sub_comp.attribute3
, ATTRIBUTE4 = sub_comp.attribute4
, ATTRIBUTE5 = sub_comp.attribute5
, ATTRIBUTE6 = sub_comp.attribute6
, ATTRIBUTE7 = sub_comp.attribute7
, ATTRIBUTE8 = sub_comp.attribute8
, ATTRIBUTE9 = sub_comp.attribute9
, ATTRIBUTE10 = sub_comp.attribute10
, ATTRIBUTE11 = sub_comp.attribute11
, ATTRIBUTE12 = sub_comp.attribute12
, ATTRIBUTE13 = sub_comp.attribute13
, ATTRIBUTE14 = sub_comp.attribute14
, ATTRIBUTE15 = sub_comp.attribute15
, Original_system_Reference =
sub_comp.original_system_reference
, Enforce_Int_Requirements = sub_comp.Enforce_Int_Requirements
WHERE SUBSTITUTE_COMPONENT_ID = p_old_sub_comp_item_id
AND COMMON_COMPONENT_SEQUENCE_ID = sub_comp.component_sequence_id
AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
AND nvl(ACD_TYPE,0) = nvl(p_acd_type, 0)
;
l_err_text := G_PKG_NAME ||'Utility (Related Substitute Component Insert)'
||SUBSTR(SQLERRM, 1, 100);
END Update_Related_Sub_Comp;
* This overloaded Procedure is called from Java to update substitutes of the related components of the common boms whenever
* substitute of a component of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_sub_comp_item_id IN Substitute Component Id updated.
*/
PROCEDURE Update_Related_Sub_Comp(p_component_sequence_id IN NUMBER
, p_old_sub_comp_item_id IN NUMBER
, p_new_sub_comp_item_id IN NUMBER
, p_acd_type IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Update_Related_Sub_Comp(p_component_sequence_id => p_component_sequence_id
, p_old_sub_comp_item_id => p_old_sub_comp_item_id
, p_new_sub_comp_item_id => p_new_sub_comp_item_id
, p_acd_type => p_acd_type
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Replicate_Comp_Ops(p_component_sequence_id IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
Cursor get_comp_ops(p_component_sequence_id NUMBER)
is
SELECT * from bom_component_operations
where component_sequence_id = p_component_sequence_id;
select *
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_comp_seq_id;
SELECT 'Y'
INTO l_comp_op_exists
FROM BOM_COMPONENT_OPERATIONS
WHERE component_sequence_id = p_component_sequence_id;
SELECT bco.operation_seq_num
INTO l_dummy
FROM bom_component_operations bco, BOM_COMPONENTS_B bic
WHERE bco.component_sequence_id = bic.component_sequence_id
AND bic.component_sequence_id = p_component_sequence_id
AND EXISTS(
SELECT operation_seq_num, bos.routing_sequence_id
FROM bom_operational_routings bor, bom_operation_sequences bos, BOM_STRUCTURES_B bom
WHERE bos.routing_sequence_id = bor.common_routing_sequence_id
AND bos.operation_seq_num = bco.operation_seq_num
AND bor.assembly_item_id = bom.assembly_item_id
AND bor.organization_id = bom.ORGANIZATION_id
AND nvl(bor.alternate_routing_designator, 'XXX') = Nvl(bom.alternate_bom_designator, 'XXX')
AND bom.bill_sequence_id = destn_comps.bill_sequence_id
);
INSERT INTO bom_component_operations
(
COMP_OPERATION_SEQ_ID ,
OPERATION_SEQ_NUM ,
OPERATION_SEQUENCE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
COMPONENT_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
COMMON_COMPONENT_SEQUENCE_ID)
SELECT
bom_component_operations_s.NEXTVAL ,
comp_ops.OPERATION_SEQ_NUM ,
comp_ops.OPERATION_SEQUENCE_ID ,
comp_ops.LAST_UPDATE_DATE ,
comp_ops.LAST_UPDATED_BY ,
comp_ops.CREATION_DATE ,
comp_ops.CREATED_BY ,
comp_ops.LAST_UPDATE_LOGIN ,
dest_comp.COMPONENT_SEQUENCE_ID ,
dest_comp.BILL_SEQUENCE_ID ,
comp_ops.ATTRIBUTE_CATEGORY ,
comp_ops.ATTRIBUTE1 ,
comp_ops.ATTRIBUTE2 ,
comp_ops.ATTRIBUTE3 ,
comp_ops.ATTRIBUTE4 ,
comp_ops.ATTRIBUTE5 ,
comp_ops.ATTRIBUTE6 ,
comp_ops.ATTRIBUTE7 ,
comp_ops.ATTRIBUTE8 ,
comp_ops.ATTRIBUTE9 ,
comp_ops.ATTRIBUTE10 ,
comp_ops.ATTRIBUTE11 ,
comp_ops.ATTRIBUTE12 ,
comp_ops.ATTRIBUTE13 ,
comp_ops.ATTRIBUTE14 ,
comp_ops.ATTRIBUTE15 ,
comp_ops.COMPONENT_SEQUENCE_ID
FROM BOM_COMPONENT_OPERATIONS comp_ops, BOM_COMPONENTS_B dest_comp
WHERE dest_comp.component_Sequence_id <> dest_comp.common_component_sequence_id
AND dest_comp.common_component_sequence_id = comp_ops.component_sequence_id
AND comp_ops.component_sequence_id = p_component_sequence_id
AND NOT EXISTS
(
SELECT 1
FROM BOM_COMPONENT_OPERATIONS ops2
where ops2.component_sequence_id = dest_comp.component_sequence_id
)
;
* @param p_component_sequence_id IN Component Sequence Id of the component updated
*/
PROCEDURE Replicate_Comp_Ops(p_component_sequence_id IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
* @param p_component_sequence_id IN Component Sequence Number of the component updated
* @param p_operation_seq_num IN Operation Sequence number added.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Insert_Related_Comp_Ops(p_component_sequence_id IN NUMBER
, p_operation_seq_num IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_component_operations
where component_sequence_id = p_comp_seq_id
and operation_seq_num= p_operation_seq_num;
select component_sequence_id, bill_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_comp_seq_id;
INSERT INTO bom_component_operations
(
COMP_OPERATION_SEQ_ID ,
OPERATION_SEQ_NUM ,
OPERATION_SEQUENCE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
COMPONENT_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
COMMON_COMPONENT_SEQUENCE_ID)
VALUES(
bom_component_operations_s.NEXTVAL ,
comp_ops.OPERATION_SEQ_NUM ,
comp_ops.OPERATION_SEQUENCE_ID ,
comp_ops.LAST_UPDATE_DATE ,
comp_ops.LAST_UPDATED_BY ,
comp_ops.CREATION_DATE ,
comp_ops.CREATED_BY ,
comp_ops.LAST_UPDATE_LOGIN ,
dest_comp.COMPONENT_SEQUENCE_ID ,
dest_comp.BILL_SEQUENCE_ID ,
comp_ops.ATTRIBUTE_CATEGORY ,
comp_ops.ATTRIBUTE1 ,
comp_ops.ATTRIBUTE2 ,
comp_ops.ATTRIBUTE3 ,
comp_ops.ATTRIBUTE4 ,
comp_ops.ATTRIBUTE5 ,
comp_ops.ATTRIBUTE6 ,
comp_ops.ATTRIBUTE7 ,
comp_ops.ATTRIBUTE8 ,
comp_ops.ATTRIBUTE9 ,
comp_ops.ATTRIBUTE10 ,
comp_ops.ATTRIBUTE11 ,
comp_ops.ATTRIBUTE12 ,
comp_ops.ATTRIBUTE13 ,
comp_ops.ATTRIBUTE14 ,
comp_ops.ATTRIBUTE15 ,
comp_ops.component_sequence_id
);
l_err_text := G_PKG_NAME ||'Utility (Related Component Operation Insert)'
||SUBSTR(SQLERRM, 1, 100);
END Insert_Related_Comp_Ops;
* @param p_component_sequence_id IN Component Sequence number of the component updated
* @param p_operation_seq_num IN Operation Sequence Number added.
*/
PROCEDURE Insert_Related_Comp_Ops(p_component_sequence_id IN NUMBER
, p_operation_seq_num IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Insert_Related_Comp_Ops(p_component_sequence_id => p_component_sequence_id
, p_operation_seq_num => p_operation_seq_num
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
* This Procedure is used to update Component Operations of the related components of the common boms whenever
* Component Operations of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_old_operation_seq_num IN Component Operation Id added.
* @param p_new_operation_seq_num IN Component Operation Id added.
* @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
* @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
*/
PROCEDURE Update_Related_Comp_Ops(p_component_sequence_id IN NUMBER
, p_old_operation_seq_num IN NUMBER
, p_new_operation_seq_num IN NUMBER
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_return_status varchar2(80);
select *
from bom_component_operations
where component_sequence_id = p_comp_seq_id
and operation_seq_num = p_new_operation_seq_num;
select component_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id <> common_component_sequence_id
and common_component_sequence_id = p_comp_seq_id;
UPDATE bom_component_operations SET
OPERATION_SEQ_NUM = comp_ops.OPERATION_SEQ_NUM ,
OPERATION_SEQUENCE_ID = comp_ops.OPERATION_SEQUENCE_ID,
LAST_UPDATE_DATE = comp_ops.LAST_UPDATE_DATE ,
LAST_UPDATED_BY = comp_ops.LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN = comp_ops.LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY = comp_ops.ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = comp_ops.ATTRIBUTE1 ,
ATTRIBUTE2 = comp_ops.ATTRIBUTE2 ,
ATTRIBUTE3 = comp_ops.ATTRIBUTE3 ,
ATTRIBUTE4 = comp_ops.ATTRIBUTE4 ,
ATTRIBUTE5 = comp_ops.ATTRIBUTE5 ,
ATTRIBUTE6 = comp_ops.ATTRIBUTE6 ,
ATTRIBUTE7 = comp_ops.ATTRIBUTE7 ,
ATTRIBUTE8 = comp_ops.ATTRIBUTE8 ,
ATTRIBUTE9 = comp_ops.ATTRIBUTE9 ,
ATTRIBUTE10 = comp_ops.ATTRIBUTE10 ,
ATTRIBUTE11 = comp_ops.ATTRIBUTE11 ,
ATTRIBUTE12 = comp_ops.ATTRIBUTE12 ,
ATTRIBUTE13 = comp_ops.ATTRIBUTE13 ,
ATTRIBUTE14 = comp_ops.ATTRIBUTE14 ,
ATTRIBUTE15 = comp_ops.ATTRIBUTE15,
REQUEST_ID = comp_ops.REQUEST_ID,
PROGRAM_ID = comp_ops.PROGRAM_ID,
PROGRAM_APPLICATION_ID = comp_ops.PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = comp_ops.PROGRAM_UPDATE_DATE
WHERE OPERATION_SEQ_NUM = p_old_operation_seq_num
AND COMMON_COMPONENT_SEQUENCE_ID = p_component_sequence_id
AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
;
l_err_text := G_PKG_NAME ||'Utility (Related Component Operation Insert)'
||SUBSTR(SQLERRM, 1, 100);
END Update_Related_Comp_Ops;
* This overloaded Procedure is called from Java to update Component Operations of the common boms whenever
* Component Operations of a source bom is updated.
* @param p_component_sequence_id IN Component Sequence Id of the component updated
* @param p_old_operation_seq_num IN Component Operation Id added.
* @param p_new_operation_seq_num IN Component Operation Id added.
*/
PROCEDURE Update_Related_Comp_Ops(p_component_sequence_id IN NUMBER
, p_old_operation_seq_num IN NUMBER
, p_new_operation_seq_num IN NUMBER)
IS
l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
Update_Related_Comp_Ops(p_component_sequence_id => p_component_sequence_id
, p_old_operation_seq_num => p_old_operation_seq_num
, p_new_operation_seq_num => p_new_operation_seq_num
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Return_Status => l_Return_Status);
* This Procedure is used to delete related comp ops from the referencing boms when comp ops
* from the source bom is deleted.
* @param p_src_comp_seq_id IN Component Sequence Id of the source component.
* @param p_operation_seq_num IN Operation sequence number of the dest source component.
*/
PROCEDURE Delete_Related_Comp_Ops(p_src_comp_seq_id IN NUMBER,
p_operation_seq_num IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
DELETE FROM BOM_COMPONENT_OPERATIONS
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq_id
AND OPERATION_SEQ_NUM = p_operation_seq_num;
Select 'Routing Exists'
from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
Where bom.assembly_item_id = bor.assembly_item_id
And bom.organization_id = bor.organizatin_id
And nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
And bom.bill_sequence_id = p_src_bill_sequence_id*/
Cursor get_src_op_seq(p_src_bill_sequence_id NUMBER)
IS
Select OPERATION_SEQ_NUM
From BOM_COMPONENTS_B
Where bill_sequence_id = p_src_bill_sequence_id;
Select OPERATION_SEQ_NUM
From BOM_OPERATION_SEQUENCES
Where Routing_Sequence_Id = (Select common_routing_sequence_id
from bom_operational_routings
where assembly_item_id = p_assy_item_id
and organization_id = p_org_id
and alternate_routing_designator is null
and not exists
(select 1
from bom_operational_routings
where assembly_item_id = p_assy_item_id
and organization_id = p_org_id
and alternate_routing_designator = p_alt_desg
)
)
UNION
SELECT 1 from dual;
Select OPERATION_SEQ_NUM
From BOM_OPERATION_SEQUENCES
Where Routing_Sequence_Id = (Select common_routing_sequence_id
from bom_operational_routings
where assembly_item_id = p_assy_item_id
and organization_id = p_org_id
and nvl(alternate_routing_designator, 'XXX') = nvl(p_alt_desg, 'XXX'))
UNION
SELECT 1 from dual;
Select 'Routing Exists'
INTO l_rtg_exist
from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
Where bom.assembly_item_id = bor.assembly_item_id
And bom.organization_id = bor.organization_id
And (nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
OR bor.alternate_routing_designator IS NULL)
And bom.bill_sequence_id = p_src_bill_sequence_id;
SELECT 1
INTO l_alt_rtg_exists
FROM BOM_OPERATIONAL_ROUTINGS
WHERE assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND alternate_routing_designator = p_alt_desg;
SELECT
component_sequence_id, common_component_sequence_id
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_sequence_id
and component_sequence_id <> common_component_sequence_id;
Select structure_type_id
from BOM_STRUCTURES_B
where bill_sequence_id = p_bill_seq_id;
SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B
WHERE DATA_LEVEL_NAME = p_data_level_name;
SELECT bcb.component_sequence_id, bcb.bill_sequence_id
FROM BOM_COMPONENTS_B bcb, BOM_STRUCTURES_B bsb
WHERE bcb.common_component_sequence_id = p_src_comp_seq_id
AND bcb.common_component_sequence_id <> bcb.component_sequence_id
AND bsb.structure_type_id = p_str_type_id
AND bsb.bill_sequence_id = bcb.bill_sequence_id
;
SELECT structure_type_id
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = p_bill_seq_id;
SELECT ATTR_GROUP_ID
FROM BOM_COMPONENTS_EXT_B
WHERE component_sequence_id = p_component_seq_id
AND bill_Sequence_id = p_bill_seq_id;
SELECT bill_sequence_id
into l_src_bill_seq_id
from BOM_COMPONENTS_B
where component_sequence_id = p_src_comp_seq_id;
Select bill_sequence_id, organization_id, assembly_item_id, alternate_bom_designator
from BOM_STRUCTURES_B
where source_bill_sequence_id <> common_bill_sequence_id
and source_bill_sequence_id = p_src_bill_sequence_id;
Select OPERATION_SEQ_NUM
From BOM_OPERATION_SEQUENCES
Where Routing_Sequence_Id = (Select common_routing_sequence_id
from bom_operational_routings
where assembly_item_id = p_assy_item_id
and organization_id = p_org_id
and nvl(alternate_routing_designator, 'XXX') = nvl(p_alt_desg, 'XXX'));
Select 'Routing Exists'
INTO l_rtg_exist
from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
Where bom.assembly_item_id = bor.assembly_item_id
And bom.organization_id = bor.organization_id
And (nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
OR bor.alternate_routing_designator IS NULL
)
And bom.bill_sequence_id = p_src_bill_seq_id;
* This Procedure is used to delete components as well as related ref desg and sub comps
* from the non referencing boms when component
* from the source bom is deleted.
* @param p_src_comp_seq IN Component Sequence Id of the source component.
*/
Procedure Delete_Related_Components(p_src_comp_seq IN NUMBER)
IS
BEGIN
IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('In Delete_Related_Components' ); END IF;
DELETE FROM BOM_COMPONENTS_B
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq;
DELETE FROM BOM_REFERENCE_DESIGNATORS
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq;
DELETE FROM BOM_SUBSTITUTE_COMPONENTS
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq;
END Delete_Related_Components;
* This Procedure is used to delete related ref desgs from the referencing boms when ref desg
* from the source bom is deleted.
* @param p_src_comp_seq IN Component Sequence Id of the source component.
* @param p_ref_desg IN Ref Desg of the dest source component.
*/
Procedure Delete_Related_Ref_Desg(p_src_comp_seq IN NUMBER
, p_ref_desg IN VARCHAR2
, x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
DELETE FROM BOM_REFERENCE_DESIGNATORS
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq
AND COMPONENT_REFERENCE_DESIGNATOR = p_ref_desg;
* This Procedure is used to delete related sub comps from the referencing boms when sub comps
* from the source bom is deleted.
* @param p_src_comp_seq IN Component Sequence Id of the source component.
* @param p_sub_comp_item_id IN Sub Comp of the dest source component.
*/
Procedure Delete_Related_Sub_Comp(p_src_comp_seq IN NUMBER
, p_sub_comp_item_id IN NUMBER
, x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
DELETE FROM BOM_SUBSTITUTE_COMPONENTS
WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq
AND SUBSTITUTE_COMPONENT_ID = p_sub_comp_item_id;
* This Function is used to dedetermine if the insert/update of replicated component records
* caused an overlap in the destination bill.
* @param p_dest_bill_sequence_id IN Bill Sequence Id of the dest bill.
* @param p_dest_comp_seq_id IN Component Sequence Id of the dest component.
* @param p_comp_item_id IN Inv item Id of the component.
* @param p_op_seq_num IN Op Sequence num of the source component.
* @param p_change_notice IN change notice of the source component.
* @param p_eff_date IN Effectivity date of the source component.
* @param p_disable_date IN disable date component.
*/
Function Check_Component_Overlap(p_dest_bill_sequence_id IN NUMBER
, p_dest_comp_seq_id IN NUMBER
, p_comp_item_id IN NUMBER
, p_op_seq_num IN NUMBER
, p_change_notice IN VARCHAR2
, p_eff_date IN DATE
, p_disable_date IN DATE
, p_impl_date IN DATE
, p_rev_item_seq_id IN NUMBER
, p_src_bill_seq_id IN NUMBER
)
Return Boolean
IS
l_dummy NUMBER;
SELECT bill_Sequence_id
INTO l_rev_itm_bill_seq
FROM eng_revised_items
WHERE revised_item_sequence_id = p_rev_item_seq_id;
SELECT 1
INTO l_dummy
FROM BOM_COMPONENTS_B bic
WHERE bill_sequence_id = p_dest_bill_sequence_id
AND component_sequence_id <> p_dest_comp_seq_id
AND component_item_id = p_comp_item_id
AND operation_seq_num = p_op_seq_num
AND (
change_notice is not null
and(
implementation_date is not null and p_change_notice is null
OR
(implementation_date is null and change_notice = p_change_notice
AND EXISTS(
SELECT 1 from eng_revised_items eri
where eri.revised_item_sequence_id = bic.revised_item_sequence_id
and eri.bill_Sequence_id = l_rev_itm_bill_seq
)
)
)
OR
(change_notice is null and p_change_notice is null)
)
AND (
( p_disable_date IS NULL OR p_disable_date > effectivity_Date ) AND
( p_eff_date < disable_Date OR disable_Date IS NULL)
)
AND rownum = 1
;
SELECT 1
INTO l_dummy
FROM BOM_COMPONENTS_B bic
WHERE bill_sequence_id = p_dest_bill_sequence_id
AND component_sequence_id <> p_dest_comp_seq_id
AND component_item_id = p_comp_item_id
AND operation_seq_num = p_op_seq_num
AND (
change_notice is not null
and(
implementation_date is not null and p_change_notice is null
OR
(implementation_date is null and change_notice = p_change_notice
AND EXISTS(
SELECT 1 from eng_revised_items eri
where eri.revised_item_sequence_id = bic.revised_item_sequence_id
and eri.bill_Sequence_id = bic.bill_Sequence_id
)
)
)
OR
(change_notice is null and p_change_notice is null)
)
AND (
( p_disable_date IS NULL OR p_disable_date > effectivity_Date ) AND
( p_eff_date < disable_Date OR disable_Date IS NULL)
)
AND rownum = 1
;
Procedure Delete_Related_Pending_Comps(p_src_comp_seq_id IN NUMBER
, x_Return_Status IN OUT NOCOPY VARCHAR2)
IS
l_impl_date DATE;
/* SELECT implementation_date
INTO l_impl_date
FROM BOM_COMPONENTS_B
where component_sequence_id = p_src_comp_seq_id;
DELETE BOM_COMPONENTS_B
WHERE common_component_sequence_id = p_src_comp_seq_id;
DELETE BOM_SUBSTITUTE_COMPONENTS
WHERE common_component_sequence_id = p_src_comp_seq_id;
DELETE BOM_REFERENCE_DESIGNATORS
WHERE common_component_sequence_id = p_src_comp_seq_id;
DELETE BOM_COMPONENT_OPERATIONS
WHERE common_component_sequence_id = p_src_comp_seq_id;
SELECT *
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = cp_old_component_sequence_id
AND bcb.bill_sequence_id = cp_bill_sequence_id
AND bcb.implementation_date IS NULL
-- The following exists clause is to ensure that the pending component is not a source
-- referenced component but the one actually created for the destination bill itself
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
AND eri.change_notice= bcb.change_notice
AND eri.bill_sequence_id = bcb.bill_sequence_id)
ORDER BY change_notice, revised_item_sequence_id;
SELECT bcb.component_sequence_id, old_component_sequence_id, bill_sequence_id, effectivity_date
FROM bom_components_b bcb
WHERE bcb.change_notice = p_change_notice
AND bcb.revised_item_sequence_id = p_revised_item_sequence_id
AND bcb.common_component_sequence_id = p_src_comp_seq_id
AND bcb.common_component_sequence_id <> bcb.component_sequence_id
AND bcb.implementation_date IS NULL;
SELECT bom_inventory_components_s.NEXTVAL INTO l_dest_new_comp_seq_id FROM dual;
l_component_rec.last_update_date := sysdate;
l_component_rec.last_updated_by := FND_PROFILE.value('USER_ID');
l_component_rec.last_update_login := FND_PROFILE.value('LOGIN_ID');
l_component_rec.program_update_date := sysdate;
INSERT INTO BOM_COMPONENTS_B
( SUPPLY_SUBINVENTORY
, OPERATION_LEAD_TIME_PERCENT
, REVISED_ITEM_SEQUENCE_ID
, COST_FACTOR
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, PROGRAM_APPLICATION_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, 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
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, INCLUDE_ON_BILL_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PICK_COMPONENTS
, Original_System_Reference
, From_End_Item_Unit_Number
, To_End_Item_Unit_Number
, Eco_For_Production -- Added by MK
, Enforce_Int_Requirements
, Auto_Request_Material -- Added in 11.5.9 by ADEY
, Obj_Name -- Added by hgelli.
, pk1_value
, pk2_value
, Suggested_Vendor_Name --- Deepu
, Vendor_Id --- Deepu
--, Purchasing_Category_id --- Deepu
, Unit_Price --- Deepu
, from_object_revision_id
, from_minor_revision_id
--,component_item_revision_id
--,component_minor_revision_id
, common_component_sequence_id
, basis_type
, component_item_revision_id
) VALUES
( l_component_rec.supply_subinventory
, l_component_rec.OPERATION_LEAD_TIME_PERCENT --check this
, l_component_rec.revised_item_sequence_id
, l_component_rec.cost_factor /* Cost Factor */
, l_component_rec.required_for_revenue
, l_component_rec.HIGH_QUANTITY
, l_component_rec.component_sequence_id
, l_component_rec.program_application_id
, l_component_rec.wip_supply_type
, l_component_rec.supply_locator_id
, l_component_rec.bom_item_type
, l_component_rec.operation_seq_num --Check this too
, l_component_rec.component_item_id
, SYSDATE /* Last Update Date */
, l_component_rec.last_updated_by /* Last Updated By */
, SYSDATE /* Creation Date */
, l_component_rec.created_by /* Created By */
, l_component_rec.last_update_login /* Last Update Login */
, l_component_rec.ITEM_NUM
, l_component_rec.component_quantity
, l_component_rec.COMPONENT_YIELD_FACTOR
, l_component_rec.COMPONENT_REMARKS
, nvl(l_component_rec.effectivity_date,SYSDATE) --2169237
, l_component_rec.Change_Notice
, l_component_rec.implementation_date/* Implementation Date */
, l_component_rec.disable_date
, l_component_rec.attribute_category
, l_component_rec.attribute1
, l_component_rec.attribute2
, l_component_rec.attribute3
, l_component_rec.attribute4
, l_component_rec.attribute5
, l_component_rec.attribute6
, l_component_rec.attribute7
, l_component_rec.attribute8
, l_component_rec.attribute9
, l_component_rec.attribute10
, l_component_rec.attribute11
, l_component_rec.attribute12
, l_component_rec.attribute13
, l_component_rec.attribute14
, l_component_rec.attribute15
, l_component_rec.planning_factor
, l_component_rec.quantity_related
, l_component_rec.so_basis
, l_component_rec.optional
, l_component_rec.mutually_exclusive_options
, l_component_rec.include_in_cost_rollup
, l_component_rec.check_atp
, l_component_rec.shipping_allowed
, l_component_rec.required_to_ship
, l_component_rec.include_on_ship_docs
, l_component_rec.include_on_bill_docs /* Include On Bill Docs */
, l_component_rec.low_quantity
, l_component_rec.acd_type
, l_component_rec.old_component_sequence_id --Chk this
, l_component_rec.bill_sequence_id
, l_component_rec.request_id
, l_component_rec.program_id
, SYSDATE /* program_update_date */
, l_component_rec.pick_components
, l_component_rec.original_system_reference
, l_component_rec.from_end_item_unit_number
, l_component_rec.to_end_item_unit_number
, l_component_rec.Eco_For_Production
, l_component_rec.Enforce_Int_Requirements
, l_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
, NULL-- Added by hgelli. Identifies this record as Bom Component.
, l_component_rec.component_item_id
, p_organization_id
, l_component_rec.Suggested_Vendor_Name --- Deepu
, l_component_rec.Vendor_Id --- Deepu
--, p_rev_component_rec.purchasing_category_id --- Deepu
, l_component_rec.Unit_Price --- Deepu
, l_component_rec.from_object_revision_id
, l_component_rec.from_minor_revision_id
, l_component_rec.common_component_sequence_id
, l_component_rec.basis_type
, decode(l_component_rec.component_item_revision_id,
NULL, NULL,
BOMPCMBM.get_rev_id_for_local_org(l_component_rec.component_item_revision_id, p_organization_id))
--, l_comp_revision_id
--, l_comp_minor_revision_id
);
SELECT count(*)
INTO l_comp_count
FROM bom_components_b
WHERE bill_sequence_id = p_src_bill_seq_id
AND COMPONENT_ITEM_REVISION_ID IS NOT NULL;
SELECT count(*)
INTO l_rev_count
FROM MTL_ITEM_REVISIONS_B source, MTL_ITEM_REVISIONS_B dest
WHERE source.inventory_item_id = dest.inventory_item_id
AND source.revision_id IN (SELECT COMPONENT_ITEM_REVISION_ID
FROM BOM_COMPONENTS_B
WHERE BILL_SEQUENCE_ID = p_src_bill_seq_id)
AND dest.organization_id = p_org_id
AND source.revision = dest.revision;
SELECT dest.revision_id
INTO l_rev_id
FROM MTL_ITEM_REVISIONS_B src, MTL_ITEM_REVISIONS_B dest
WHERE dest.inventory_item_id = src.inventory_item_id
AND dest.organization_id = p_org_id
AND dest.revision = src.revision
AND src.revision_id = p_rev_id;
SELECT DISTINCT organization_id
FROM bom_structures_b
WHERE source_bill_Sequence_id = p_src_bill_seq_id;