This appendix covers the following topics:
Using Oracle Workflow, you can automatically trigger actions based on the following Oracle Bills of Material business events. Examples of actions you can trigger include issuing notifications or launching a workflow.
| Event Name | Event Description | Event Parameter Names and Descriptions |
|---|---|---|
| Oracle.apps.bom.structure.created | Raise this event each time a bill of material is created. When importing bills in bulk using the open interface program, set the event type to BULK. | PK1_Value: The PK1_Value of the bill. When Obj_Name = NULL, PK1_Value = the value of the Assembly_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for items. Structure_Name:TThe alternate designator. Event_Entity_Name: This value equals 'Structure'. Event_Parent_Entity_Id: The value of the product bill's Bill_Sequence_Id. The value equals NULL for a bulk event. |
| Oracle.apps.bom.structure.modified | Raise this event when:
|
PK1_Value: The PK1_Value of the bill. When Obj_Name = NULL, PK1_Value = the value of the Assembly_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for items. Structure_Name: The alternate designator. Event_Entity_Name: This value equals 'Structure'. Event_Parent_Entity_Id: The value of the product bill's Bill_Sequence_Id. The value equals NULL for a bulk event. REVISED_ITEM_SEQUENCE: Indicates the revised item on an ECO, which affects a component row. It shows all the components that are updated for the particular revised item while implementing the ECO. |
| Oracle.apps.bom.structure.deleteSuccess | Raise this event when deleting a bill of material. | PK1_Value: The PK1_Value of the bill. When Obj_Name = NULL, PK1_Value = the value of the Assembly_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for items. Structure_Name:TThe alternate designator. Event_Entity_Name: This value equals 'Structure'. Event_Parent_Entity_Id: The value of the product bill's Bill_Sequence_Id. The value equals NULL for a bulk event. |
| Oracle.apps.bom.structure.deleteError | Raise this event when an error occurs during a bill of material deletion. | PK1_Value: The PK1_Value of the bill. When Obj_Name = NULL, PK1_Value = the value of the Assembly_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for items. Structure_Name:TThe alternate designator. Event_Entity_Name: This value equals 'Structure'. Event_Parent_Entity_Id: The value of the product bill's Bill_Sequence_Id. The value equals NULL for a bulk event. |
| Oracle.apps.bom.component.modified | Raise this event when creating or modifying a:
Depending on the child entity that is modified, the event name will have either one of the values listed above. |
PK1_Value: The PK1_Value of the component. When Obj_Name = NULL, PK1_Value = the value of the Component_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for component-type items. Structure_Name:TThe alternate designator. Event_Entity_Name: Possible values include:
Event_Parent_Entity_Id: The value of the component's Component_Sequence_Id . The value equals NULL for a bulk event. |
| Oracle.apps.bom.component.deleteSuccess | Raise this event when deleting the components in a structure using a delete group. | PK1_Value: The PK1_Value of the component. When Obj_Name = NULL, PK1_Value = the value of the Component_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for component-type items. Structure_Name:The alternate designator. Event_Entity_Name: Possible values include:
Event_Parent_Entity_Id: The value of the component's Component_Sequence_Id . The value equals NULL for a bulk event. |
| Oracle.apps.bom.component.deleteError | Raise this event when an error occurs during a component deletion. | PK1_Value: The PK1_Value of the component. When Obj_Name = NULL, PK1_Value = the value of the Component_Item_Id. PK2_Value: The PK2_Value of the bill. When Obj_Name = NULL, PK2_Value = the value of the Organization_Id. Obj_Name: This value is NULL for component-type items. Structure_Name:The alternate designator. Event_Entity_Name: Possible values include:
Event_Parent_Entity_Id: The value of the component's Component_Sequence_Id . The value equals NULL for a bulk event. |
Note: For bulk business events, the event parameters PK1_Value, PK2_Value, and Obj_Name parameters are NULL.
| Event Parameter Name | Description |
|---|---|
| Event_Type | This parameter identifies the type of transaction that raised the event.
|
| Last_Updated_By | Identifier of the user |
| Last_Update_Date | Identifies the date the transaction occurred |
| Created_By | Identifier of the user.
Note: In bulk events that include both creation and update transactions, this parameter is irrelevant and not available. |
| Creation_Date | Identifies the date the creation transaction occurred.
Note: In bulk events that include both creation and update transactions, this parameter is irrelevant and not available. |
| Request_Identifier | When Event_Type = Bulk, this identifies the concurrent request that processed the bulk data set. |
Related Topics
Managing Business Events, Oracle Workflow Developer's Guide
Defining Procedures and Functions for Oracle Workflow, Oracle Workflow Developer's Guide
In order to trigger actions based on business events, you must subscribe to the business events. A subscription can trigger a simple action, such as delivering electronic notifications, or a more complex action, such as triggering a function. A function is a PL/SQL stored procedure that can define business rules, perform automated tasks within an application, or retrieve application information. The stored procedure accepts standard arguments and returns a completion result.
A function conforms to a specific signature as required by Oracle Workflow and business events. A generic subscription function looks like this:
CREATE OR REPLACE PACKAGE My_Subscription_PKG AS
FUNCTION Test_Subscription
( p_subscription_guid IN RAW,
, p_event IN OUT NOCOPY wf_event_t
)
RETURN VARCHAR2;
END My_Subscription_PKG;
CREATE OR REPLACE PACKAGE BODY My_Subscription_PKG AS
FUNCTION Test_Subscription
( p_subscription_guid IN RAW,
, p_event IN OUT NOCOPY wf_event_t
)
RETURN VARCHAR2 IS
l_event_name VARCHAR2(2000);
l_event_key VARCHAR2(2000);
l_err_text VARCHAR2(3000);
l_param_list WF_PARAMETER_LIST_T ;
BEGIN
l_event_name := p_event.getEventName();
l_event_key := p_event.GetEventKey()
...
...
...
RETURN 'SUCCESS';
EXCEPTION WHEN OTHERS THEN
RETURN 'ERROR';
END;
/* End of test scription */
END My_Subscription_PKG;
/* End of package My_Subscription_PKG */
Following are two examples of subscription functions that perform specific tasks.
Example: Synchronizing Bills of Material Across Multiple Organizations
You maintain bills of material in the master organization. Every time you create a new bill of material in the master organization, you must synchronize the bill with one or more child organizations. You need to maintain the component attributes and substitutes in individual organizations, so you cannot use a common bill of material. To meet these requirements, a function now needs to raise the following three events:
oracle.apps.bom.structure.created
oracle.apps.bom.structure.modified
oracle.apps.bom.component.modified
For the oracle.apps.bom.structure.created event, the subscription function creates the bill of material in all child organizations.
CREATE or REPLACE PACKAGE Bom_Struct_Created_Sub AS
FUNCTION process_structure_created (p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2;
END Bom_Struct_Created_Sub;
CREATE or REPLACE PACKAGE BODY Bom_Struct_Created_Sub AS
/***********************************************************************************
* Function : process_structure_created
* Parameters : Subscription guid
* : Event
* Return : Sucess/Error
* Purpose : This is a subscription function for oracle.apps.bom.structure.created
* event. When a structure is created the business event oracle.apps.
* bom.structure.created event is raised which will invoke this
* API as part of subscription.
************************************************************************************/
FUNCTION process_structure_created (p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
/*
*Cursor to get all child orgs for a given organization id.
*/
CURSOR get_child_orgs(org_id NUMBER)
IS
SELECT organization_id child_org_id
FROM mtl_parameters
WHERE master_organization_id = org_id
AND master_organization_id <> organization_id;
/*
* Cursor to get the structure details for a given item, org and structure name.
*/
CURSOR get_structure_details(item_id NUMBER, org_id NUMBER, structure_name VARCHAR2)
IS
SELECT *
FROM bom_structures_b
WHERE assembly_item_id = item_id
AND organization_id = org_id
AND NVL(alternate_bom_designator,'PRIMARY') = structure_name;
l_param_list WF_PARAMETER_LIST_T ;
l_param_name VARCHAR2(240);
l_param_value VARCHAR2(2000);
l_event_name VARCHAR2(2000);
l_event_key VARCHAR2(2000);
l_debug_file_dir VARCHAR2(512);
l_log_file VARCHAR2(240);
l_log_return_status VARCHAR2(1);
l_error VARCHAR2(4000);
l_structure_rec get_structure_details%ROWTYPE;
l_bom_header_rec BOM_BO_PUB.Bom_Head_Rec_Type;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_bom_comp_ops_tbl Bom_Bo_Pub.Bom_Comp_Ops_Tbl_Type;
l_ass_item_id NUMBER;
l_struct_name VARCHAR2(10);
l_org_id NUMBER;
l_child_org_id NUMBER;
is_MasterOrg VARCHAR2(1);
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_assembly_type NUMBER;
l_assembly_comment VARCHAR2(240);
l_attr_category VARCHAR2(30);
l_attr1 VARCHAR2(150);
l_attr2 VARCHAR2(150);
l_attr3 VARCHAR2(150);
l_attr4 VARCHAR2(150);
l_attr5 VARCHAR2(150);
l_attr6 VARCHAR2(150);
l_attr7 VARCHAR2(150);
l_attr8 VARCHAR2(150);
l_attr9 VARCHAR2(150);
l_attr10 VARCHAR2(150);
l_attr11 VARCHAR2(150);
l_attr12 VARCHAR2(150);
l_attr13 VARCHAR2(150);
l_attr14 VARCHAR2(150);
l_attr15 VARCHAR2(150);
l_orig_sys_ref VARCHAR2(50);
l_imp_date DATE;
l_entity_type VARCHAR2(100);
BEGIN
/*
* Select the debug directory to write the log file and create the file name.
*
* These steps are required only for debugging and troubleshooting.
* The location of the debug file is based on the utl_file_dir parameter
*/
SELECT VALUE
INTO l_debug_file_dir
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
IF INSTR(l_debug_file_dir,',') <> 0 THEN
l_debug_file_dir := SUBSTR(l_debug_file_dir, 1, INSTR(l_debug_file_dir, ',') - 1);
END IF;
/*
* Create the file name for debug log file.
*/
l_log_file := 'BOM_STR_SUBSCR'||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
l_param_list := p_event.getparameterlist;
l_event_name := p_event.getEventName();
l_event_key := p_event.GetEventKey();
/*
* Loop through the payload for the business event raised and get all the parameters.
* This gives the information about the structure created.
*/
IF l_param_list IS NOT NULL THEN
FOR i IN l_param_list.FIRST..l_param_list.LAST
LOOP
l_param_name := l_param_list(i).getname;
l_param_value := l_param_list(i).getvalue;
IF (l_param_name = 'PK1_VALUE')
THEN
l_ass_item_id := l_param_value;
ELSIF (l_param_name = 'PK2_VALUE')
THEN
l_org_id := l_param_value;
ELSIF(l_param_name = 'STRUCTURE_NAME')
THEN
l_struct_name := l_param_value;
END IF;
END LOOP;
END IF;
/*
* Check if it is a Master org.If it is a Master Org create structure in all the
* child orgs.
*/
BEGIN
SELECT 'Y'
INTO is_MasterOrg
FROM DUAL
WHERE EXISTS
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = l_org_id
AND ( master_organization_id IS NULL
OR master_organization_id = l_org_id)
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
is_MasterOrg := 'N';
END;
/*
* This subscription API works only for event type as Single.
*/
IF (is_MasterOrg = 'Y' AND l_entity_type= 'Single')
THEN
l_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_bom_comp_ops_tbl := Bom_Bo_Pub.G_MISS_BOM_COMP_OPS_TBL;
OPEN get_structure_details(l_ass_item_id,l_org_id,l_struct_name);
LOOP
FETCH get_structure_details into l_structure_rec;
EXIT WHEN get_structure_details%NOTFOUND;
IF (l_struct_name = 'PRIMARY')
THEN
l_struct_name := '';
END IF;
l_assembly_type := l_structure_rec.Assembly_Type ;
l_assembly_comment := l_structure_rec.Specific_Assembly_Comment;
l_attr_category := l_structure_rec.Attribute_category;
l_attr1 := l_structure_rec.Attribute1;
l_attr2 := l_structure_rec.Attribute2;
l_attr3 := l_structure_rec.Attribute3;
l_attr4 := l_structure_rec.Attribute4;
l_attr5 := l_structure_rec.Attribute5;
l_attr6 := l_structure_rec.Attribute6;
l_attr7 := l_structure_rec.Attribute7;
l_attr8 := l_structure_rec.Attribute8;
l_attr8 := l_structure_rec.Attribute9;
l_attr10 := l_structure_rec.Attribute10;
l_attr11 := l_structure_rec.Attribute11;
l_attr12 := l_structure_rec.Attribute12;
l_attr13 := l_structure_rec.Attribute13;
l_attr14 := l_structure_rec.Attribute14;
l_attr15 := l_structure_rec.Attribute15;
l_orig_sys_ref := l_structure_rec.Original_System_Reference;
l_imp_date := l_structure_rec.Implementation_Date;
END LOOP;
CLOSE get_structure_details;
FOR each_record in get_child_orgs(org_id => l_org_id)
LOOP
BEGIN
l_child_org_id := each_record.child_org_id;
l_bom_header_rec.Assembly_item_name := Get_Item_Name(l_ass_item_id,l_org_id);
l_bom_header_rec.Organization_Code := Get_Org_Code(l_child_org_id);
l_bom_header_rec.Alternate_Bom_Code := l_struct_name;
l_bom_header_rec.Assembly_Type := nvl(l_assembly_type,1);
l_bom_header_rec.Transaction_Type := 'CREATE';
l_bom_header_rec.Return_Status := NULL;
l_bom_header_rec.Row_Identifier := NULL;
l_bom_header_rec.Assembly_Comment := l_assembly_comment;
l_bom_header_rec.Attribute_category := l_attr_category;
l_bom_header_rec.Attribute1 := l_attr1;
l_bom_header_rec.Attribute2 := l_attr2;
l_bom_header_rec.Attribute3 := l_attr3;
l_bom_header_rec.Attribute4 := l_attr4;
l_bom_header_rec.Attribute5 := l_attr5;
l_bom_header_rec.Attribute6 := l_attr6;
l_bom_header_rec.Attribute7 := l_attr7;
l_bom_header_rec.Attribute8 := l_attr8;
l_bom_header_rec.Attribute9 := l_attr9;
l_bom_header_rec.Attribute10 := l_attr10;
l_bom_header_rec.Attribute11 := l_attr11;
l_bom_header_rec.Attribute12 := l_attr12;
l_bom_header_rec.Attribute13 := l_attr13;
l_bom_header_rec.Attribute14 := l_attr14;
l_bom_header_rec.Attribute15 := l_attr15;
l_bom_header_rec.Original_System_Reference := l_orig_sys_ref;
l_bom_header_rec.BOM_Implementation_Date := l_imp_date;
/*
* Assign the item to child org if it is not assigned. The item assignment
* code is not in here.So if the item is not assigned to the child org
* we need to do the item assignment first before trying to create structure
* in the child org.
*/
Bom_Bo_Pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0
, p_init_msg_list => TRUE
, p_bom_header_rec => l_bom_header_rec
, p_bom_revision_tbl => l_bom_revision_tbl
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_bom_header_rec
, x_bom_revision_tbl => l_bom_revision_tbl
, x_bom_component_tbl => l_bom_component_tbl
, x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, p_debug => 'N' -- Set this to Y for debugging
, p_output_dir => l_debug_file_dir
, p_debug_filename => l_log_file
, p_write_err_to_debugfile => 'Y'
);
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
END;
END LOOP;
END IF;
RETURN 'SUCCESS';
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
RETURN 'ERROR';
END process_structure_created;
/*****************************************************************
* Function : Get_Item_Name
* Parameters : Inventory item id
* : Organization id
* Return : Assembly Item name
* Purpose : This function is used to get the assembly item name
* for a given item id and organzation id.
******************************************************************/
FUNCTION Get_Item_Name(p_item_id IN NUMBER,p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
BEGIN
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
RETURN l_item_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Item_Name;
/*****************************************************************
* Function : Get_Org_Code
* Parameters : Organization id
* Return : Organization Code
* Purpose : This function is used to get the organization code
* for a organzation id.
******************************************************************/
FUNCTION Get_Org_Code(p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_org_code MTL_PARAMETERS.organization_code%TYPE;
BEGIN
SELECT organization_code
INTO l_org_code
FROM MTL_PARAMETERS
WHERE organization_id = p_org_id;
RETURN l_org_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Org_Code;
END Bom_Struct_Created_Sub;
For the oracle.apps.bom.structure.modified event, the subscription function identifies the new or modified components and adds or changes them in the child organizations:
CREATE or REPLACE PACKAGE Bom_Components_Sync_Sub AS
FUNCTION sync_components(p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2;
END Bom_Components_Sync_Sub;
/*********************************************************************
* Function : Sync_Components
* Parameters : Subscription guid
* : Event
* Return : Sucess/Error
* Purpose : This function is subscribed for oracle.apps.bom.structure.modified
* event.When a structure is modified either by adding new components
* or by modifying existing component attributes,the business event
* is raised.
**********************************************************************/
CREATE or REPLACE PACKAGE BODY Bom_Components_Sync_Sub AS
FUNCTION sync_components (p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
/*
*Cursor to get all child orgs for a given organization id.
*/
CURSOR get_child_orgs(org_id NUMBER)
IS
SELECT organization_id child_org_id
FROM mtl_parameters
WHERE master_organization_id = org_id
AND master_organization_id <> organization_id;
/*
* Cursor to get the component details for a given component sequence id.
*/
CURSOR get_component_details(comp_seq_id NUMBER)
IS
SELECT *
FROM bom_components_b
WHERE component_sequence_id = comp_seq_id ;
l_param_list WF_PARAMETER_LIST_T ;
l_param_name VARCHAR2(240);
l_param_value VARCHAR2(2000);
l_event_name VARCHAR2(2000);
l_event_key VARCHAR2(2000);
l_debug_file_dir VARCHAR2(512);
l_log_file VARCHAR2(240);
l_log_return_status VARCHAR2(1);
l_error VARCHAR2(4000);
l_assembly_item_id NUMBER;
l_comp_item_id NUMBER;
l_org_id NUMBER;
l_struct_name VARCHAR2(10);
l_entity_name VARCHAR2(240);
l_comp_seq_id NUMBER;
l_bill_seq_id NUMBER;
l_component_rec get_component_details%ROWTYPE;
l_bom_header_rec BOM_BO_PUB.Bom_Head_Rec_Type;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_bom_component_rec Bom_Bo_Pub.Bom_Comps_Rec_Type;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_bom_comp_ops_tbl Bom_Bo_Pub.Bom_Comp_Ops_Tbl_Type;
is_MasterOrg VARCHAR2(1);
l_child_org_id NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_disable_date DATE;
l_planning_percent NUMBER;
l_yield NUMBER;
l_cost_rollup NUMBER;
l_wip_supply_type NUMBER;
l_so_basis NUMBER;
l_optional NUMBER;
l_exclusive NUMBER;
l_check_atp NUMBER;
l_shipping_allowed NUMBER;
l_req_to_ship NUMBER;
l_req_for_revenue NUMBER;
l_include_on_ship_docs NUMBER;
l_qty_related NUMBER;
l_supply_subinv VARCHAR2(10);
l_low_qty NUMBER;
l_high_qty NUMBER;
l_attr_category VARCHAR2(30);
l_attr1 VARCHAR2(150);
l_attr2 VARCHAR2(150);
l_attr3 VARCHAR2(150);
l_attr4 VARCHAR2(150);
l_attr5 VARCHAR2(150);
l_attr6 VARCHAR2(150);
l_attr7 VARCHAR2(150);
l_attr8 VARCHAR2(150);
l_attr9 VARCHAR2(150);
l_attr10 VARCHAR2(150);
l_attr11 VARCHAR2(150);
l_attr12 VARCHAR2(150);
l_attr13 VARCHAR2(150);
l_attr14 VARCHAR2(150);
l_attr15 VARCHAR2(150);
l_from_end_item_unit_number VARCHAR2(30);
l_to_end_item_unit_number VARCHAR2(30);
l_orig_sys_ref VARCHAR2(50) ;
l_enforce_int_req NUMBER;
l_auto_req_material VARCHAR2(1);
l_sugg_vendor_name VARCHAR2(240);
l_unit_price NUMBER;
l_event_type VARCHAR2(500);
BEGIN
/*
* Select the debug directory to write the log file and create the file name.
*
* These steps are required only for debugging and troubleshooting.
* The location of the debug file is based on the utl_file_dir parameter
*/
SELECT VALUE
INTO l_debug_file_dir
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
IF INSTR(l_debug_file_dir,',') <> 0 THEN
l_debug_file_dir := SUBSTR(l_debug_file_dir, 1, INSTR(l_debug_file_dir, ',') - 1);
END IF;
l_log_file := 'BOM_COMP_SUBSCR'||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
/* End of the section required for debug file */
l_param_list := p_event.getparameterlist;
l_event_name := p_event.getEventName();
l_event_key := p_event.GetEventKey();
/*
* Loop through the payload for the business event raised and get all the parameters.
* This gives the information about the structure created.
*/
IF l_param_list IS NOT NULL THEN
FOR i IN l_param_list.FIRST..l_param_list.LAST
LOOP
l_param_name := l_param_list(i).getname;
l_param_value := l_param_list(i).getvalue;
IF (l_param_name = 'PK1_VALUE')
THEN
l_comp_item_id := l_param_value;
ELSIF (l_param_name = 'PK2_VALUE')
THEN
l_org_id := l_param_value;
ELSIF(l_param_name = 'STRUCTURE_NAME')
THEN
l_struct_name := l_param_value;
ELSIF (l_param_name = 'EVENT_ENTITY_NAME')
THEN
l_entity_name := l_param_value;
ELSIF (l_param_name = 'COMPONENT_SEQUENCE_ID')
THEN
l_comp_seq_id := l_param_value;
ELSIF (l_param_name = 'EVENT_ENTITY_PARENT_ID')
THEN
l_bill_seq_id := l_param_value;
ELSIF (l_param_name = 'EVENT_TYPE')
THEN
l_event_type := l_param_value;
END IF;
END LOOP;
END IF;
BEGIN
IF (l_entity_name = 'Component' AND l_event_type = 'Single')
THEN
BEGIN
SELECT 'Y'
INTO is_MasterOrg
FROM DUAL
WHERE EXISTS
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = l_org_id
AND ( master_organization_id IS NULL
OR master_organization_id = l_org_id)
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
is_MasterOrg := 'N';
END;
IF (is_MasterOrg = 'Y')
THEN
SELECT assembly_item_id
INTO l_assembly_item_id
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = l_bill_seq_id AND
organization_id = l_org_id;
l_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_rec := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_REC;
l_bom_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_bom_comp_ops_tbl := Bom_Bo_Pub.G_MISS_BOM_COMP_OPS_TBL;
OPEN get_component_details(l_comp_seq_id);
LOOP
FETCH get_component_details into l_component_rec;
EXIT WHEN get_component_details%NOTFOUND;
IF (l_struct_name = 'PRIMARY')
THEN
l_struct_name := '';
END IF;
--l_bom_component_rec.Start_Effective_Date := l_component_rec.Effectivity_Date;
--l_bom_component_rec.Operation_Sequence_Number := l_component_rec.Operation_Seq_Num;
l_disable_date := l_component_rec.Disable_Date;
l_planning_percent := l_component_rec.Planning_Factor;
l_yield := l_component_rec.Component_Yield_Factor;
l_cost_rollup := l_component_rec.Include_In_Cost_Rollup;
l_wip_supply_type := l_component_rec.Wip_Supply_Type;
l_so_basis := l_component_rec.So_Basis;
l_optional := l_component_rec.Optional;
l_exclusive := l_component_rec.Mutually_Exclusive_Options;
l_check_atp := l_component_rec.Check_Atp;
l_shipping_allowed := l_component_rec.Shipping_Allowed;
l_req_to_ship := l_component_rec.Required_To_Ship;
l_req_for_revenue := l_component_rec.Required_For_Revenue;
l_include_on_ship_docs := l_component_rec.Include_On_Ship_Docs;
l_qty_related := l_component_rec.Quantity_Related;
l_supply_subinv := l_component_rec.Supply_Subinventory;
l_low_qty := l_component_rec.Low_Quantity;
l_high_qty := l_component_rec.High_Quantity;
l_attr_category := l_component_rec.Attribute_category;
l_attr1 := l_component_rec.Attribute1;
l_attr2 := l_component_rec.Attribute2;
l_attr3 := l_component_rec.Attribute3;
l_attr4 := l_component_rec.Attribute4;
l_attr5 := l_component_rec.Attribute5;
l_attr6 := l_component_rec.Attribute6;
l_attr7 := l_component_rec.Attribute7;
l_attr8 := l_component_rec.Attribute8;
l_attr9 := l_component_rec.Attribute9;
l_attr10 := l_component_rec.Attribute10;
l_attr11 := l_component_rec.Attribute11;
l_attr12 := l_component_rec.Attribute12;
l_attr13 := l_component_rec.Attribute13;
l_attr14 := l_component_rec.Attribute14;
l_attr15 := l_component_rec.Attribute15;
l_from_end_item_unit_number := l_component_rec.From_End_Item_Unit_Number;
l_to_end_item_unit_number := l_component_rec.To_End_Item_Unit_Number;
l_orig_sys_ref := l_component_rec.Original_System_Reference;
l_enforce_int_req := l_component_rec.Enforce_Int_Requirements ;
l_auto_req_material := l_component_rec.Auto_Request_Material;
l_sugg_vendor_name := l_component_rec.Suggested_Vendor_Name;
l_unit_price := l_component_rec.Unit_Price;
END LOOP;
CLOSE get_component_details;
FOR each_record in get_child_orgs(org_id => l_org_id)
LOOP
BEGIN
l_child_org_id := each_record.child_org_id;
l_bom_component_rec.Organization_Code := Get_Org_Code(l_child_org_id);
l_bom_component_rec.Assembly_Item_Name := Get_Item_Name(l_assembly_item_id,l_org_id);
l_bom_component_rec.Component_Item_Name := Get_Item_Name(l_comp_item_id,l_org_id);
l_bom_component_rec.Alternate_BOM_Code := l_struct_name;
l_bom_component_rec.Disable_Date := l_disable_date;
l_bom_component_rec.Planning_Percent := l_planning_percent;
l_bom_component_rec.Projected_Yield := l_yield;
l_bom_component_rec.Include_In_Cost_Rollup := l_cost_rollup;
l_bom_component_rec.Wip_Supply_Type := l_wip_supply_type;
l_bom_component_rec.So_Basis := l_so_basis;
l_bom_component_rec.Optional := l_optional;
l_bom_component_rec.Mutually_Exclusive := l_exclusive;
l_bom_component_rec.Check_Atp := l_check_atp;
l_bom_component_rec.Shipping_Allowed := l_shipping_allowed;
l_bom_component_rec.Required_To_Ship := l_req_to_ship;
l_bom_component_rec.Required_For_Revenue := l_req_for_revenue;
l_bom_component_rec.Include_On_Ship_Docs := l_include_on_ship_docs;
l_bom_component_rec.Quantity_Related := l_qty_related;
l_bom_component_rec.Supply_Subinventory := l_supply_subinv;
l_bom_component_rec.Minimum_Allowed_Quantity:= l_low_qty;
l_bom_component_rec.Maximum_Allowed_Quantity:= l_high_qty;
l_bom_component_rec.Attribute_category := l_attr_category;
l_bom_component_rec.Attribute1 := l_attr1;
l_bom_component_rec.Attribute2 := l_attr2;
l_bom_component_rec.Attribute3 := l_attr3;
l_bom_component_rec.Attribute4 := l_attr4;
l_bom_component_rec.Attribute5 := l_attr5;
l_bom_component_rec.Attribute6 := l_attr6;
l_bom_component_rec.Attribute7 := l_attr7;
l_bom_component_rec.Attribute8 := l_attr8;
l_bom_component_rec.Attribute9 := l_attr9;
l_bom_component_rec.Attribute10 := l_attr10;
l_bom_component_rec.Attribute11 := l_attr11;
l_bom_component_rec.Attribute12 := l_attr12;
l_bom_component_rec.Attribute13 := l_attr13;
l_bom_component_rec.Attribute14 := l_attr14;
l_bom_component_rec.Attribute15 := l_attr15;
l_bom_component_rec.From_End_Item_Unit_Number := l_from_end_item_unit_number;
l_bom_component_rec.To_End_Item_Unit_Number := l_to_end_item_unit_number;
l_bom_component_rec.Original_System_Reference := l_orig_sys_ref;
l_bom_component_rec.Enforce_Int_Requirements:= l_enforce_int_req;
l_bom_component_rec.Auto_Request_Material := l_auto_req_material;
l_bom_component_rec.Suggested_Vendor_Name := l_sugg_vendor_name;
l_bom_component_rec.Unit_Price := l_unit_price;
l_bom_component_rec.Transaction_Type := 'SYNC';
l_bom_component_rec.Return_Status := NULL;
l_bom_component_rec.Row_Identifier := NULL;
l_bom_component_tbl(1) := l_bom_component_rec;
Bom_Bo_Pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0
, p_init_msg_list => TRUE
, p_bom_header_rec => l_bom_header_rec
, p_bom_revision_tbl => l_bom_revision_tbl
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_bom_header_rec
, x_bom_revision_tbl => l_bom_revision_tbl
, x_bom_component_tbl => l_bom_component_tbl
, x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, p_debug => 'N' -- Set this parameter to Y for debugging
, p_output_dir => l_debug_file_dir
, p_debug_filename => l_log_file
, p_write_err_to_debugfile => 'Y'
);
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
END;
END LOOP;
END IF;
END IF;
END;
RETURN 'SUCCESS';
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
RETURN 'ERROR';
END Sync_Components;
/*****************************************************************
* Function : Get_Item_Name
* Parameters : Inventory item id
* : Organization id
* Return : Assembly Item name
* Purpose : This function is used to get the assembly item name
* for a given item id and organzation id.
******************************************************************/
FUNCTION Get_Item_Name(p_item_id IN NUMBER,p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
BEGIN
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
RETURN l_item_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Item_Name;
/*****************************************************************
* Function : Get_Org_Code
* Parameters : Organization id
* Return : Organization Code
* Purpose : This function is used to get the organization code
* for a organzation id.
******************************************************************/
FUNCTION Get_Org_Code(p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_org_code MTL_PARAMETERS.organization_code%TYPE;
BEGIN
SELECT organization_code
INTO l_org_code
FROM MTL_PARAMETERS
WHERE organization_id = p_org_id;
RETURN l_org_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Org_Code;
END Bom_Components_Sync_Sub;
Note: This example does not provide the code for the oracle.apps.bom.component.modified event, but you can extend the above sample code to accommodate this event. Use the oracle.apps.bom.component.modified event to add or delete reference designators, substitute components or component operations.
Example: Creating a Common Bill of Material Across Multiple Organizations
You use the common bill of material feature to maintain bills in a master organization. You require that, for each bill created in the master organization, common bills (bills that reference the master organization bill) are created in the child organizations. To meet this requirement, use the oracle.apps.bom.structure.created event and the following function.
CREATE OR REPLACE PACKAGE Bom_Struct_Created_Sub AS
FUNCTION create_common_structure(p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2;
END Bom_common_Struct_Create_Sub;
CREATE or REPLACE PACKAGE BODY Bom_common_Struct_Create_Sub AS
/*****************************************************************
* Function : create_common_structure
* Parameters : Subscription guid
* : Event
* Return : Sucess/Error
* Purpose : This function can be subscribed to oracle.apps.bom.structure.
* crated event. When the event is raised this API as part of
* subscription. This API checks if the structure
* is created in the master org, if the structure is created in
* the master org then it creates common structure in all child orgs.
******************************************************************/
FUNCTION create_common_structure (p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2 IS
/*
* Cursor to get all child orgs for a given organization id.
*/
CURSOR get_child_orgs(org_id NUMBER)
IS
SELECT organization_id child_org_id
FROM mtl_parameters
WHERE master_organization_id = org_id
AND master_organization_id <> organization_id;
/*
* Cursor to get the structure details for a given item, org and
* structure name.
*/
CURSOR get_structure_details(item_id NUMBER, org_id NUMBER, structure_name VARCHAR2)
IS
SELECT *
FROM bom_structures_b
WHERE assembly_item_id = item_id
AND organization_id = org_id
AND NVL(alternate_bom_designator,'PRIMARY') = structure_name;
l_param_list WF_PARAMETER_LIST_T ;
l_param_name VARCHAR2(240);
l_param_value VARCHAR2(2000);
l_event_name VARCHAR2(2000);
l_event_key VARCHAR2(2000);
l_debug_file_dir VARCHAR2(512);
l_log_file VARCHAR2(240);
l_log_return_status VARCHAR2(1);
l_error VARCHAR2(4000);
l_structure_rec get_structure_details%ROWTYPE;
l_bom_header_rec BOM_BO_PUB.Bom_Head_Rec_Type;
l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type;
l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
l_bom_comp_ops_tbl Bom_Bo_Pub.Bom_Comp_Ops_Tbl_Type;
l_ass_item_id NUMBER;
l_struct_name VARCHAR2(10);
l_org_id NUMBER;
l_child_org_id NUMBER;
is_MasterOrg VARCHAR2(1);
l_return_status VARCHAR2(1);
l_msg_count NUMBER := 0;
l_assembly_type NUMBER;
l_assembly_comment VARCHAR2(240);
l_attr_category VARCHAR2(30);
l_attr1 VARCHAR2(150);
l_attr2 VARCHAR2(150);
l_attr3 VARCHAR2(150);
l_attr4 VARCHAR2(150);
l_attr5 VARCHAR2(150);
l_attr6 VARCHAR2(150);
l_attr7 VARCHAR2(150);
l_attr8 VARCHAR2(150);
l_attr9 VARCHAR2(150);
l_attr10 VARCHAR2(150);
l_attr11 VARCHAR2(150);
l_attr12 VARCHAR2(150);
l_attr13 VARCHAR2(150);
l_attr14 VARCHAR2(150);
l_attr15 VARCHAR2(150);
l_orig_sys_ref VARCHAR2(50);
l_entity_type VARCHAR2(100);
l_imp_date DATE;
BEGIN
/*
* Select the debug directory to write the log file and create
* the file name.
*
* These steps are required only for debugging and
* troubleshooting.
* The location of the debug file is based on the
* utl_file_dir parameter
*/
SELECT VALUE
INTO l_debug_file_dir
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
IF INSTR(l_debug_file_dir,',') <> 0 THEN
l_debug_file_dir := SUBSTR(l_debug_file_dir, 1, INSTR(l_debug_file_dir, ',') - 1);
END IF;
l_log_file := 'BOM_STR_SUBSCR'||'_'||TO_CHAR(SYSDATE, 'DDMONYYYY_HH24MISS')||'.err';
l_param_list := p_event.getparameterlist;
l_event_name := p_event.getEventName();
l_event_key := p_event.GetEventKey();
/*
* Loop through the payload for the business event raised and get
* all the parameters.
* This gives the information about the structure created.
*/
IF l_param_list IS NOT NULL THEN
FOR i IN l_param_list.FIRST..l_param_list.LAST
LOOP
l_param_name := l_param_list(i).getname;
l_param_value := l_param_list(i).getvalue;
IF (l_param_name = 'PK1_VALUE')
THEN
l_ass_item_id := l_param_value;
ELSIF (l_param_name = 'PK2_VALUE')
THEN
l_org_id := l_param_value;
ELSIF(l_param_name = 'STRUCTURE_NAME')
THEN
l_struct_name := l_param_value;
ELSIF (l_param_name = 'EVENT_TYPE')
THEN
l_entity_type := l_param_value;
END IF;
END LOOP;
END IF;
/*
* Check if it is a Master org.If it is a Master Org create structure
* in all the child orgs.
*/
BEGIN
SELECT 'Y'
INTO is_MasterOrg
FROM DUAL
WHERE EXISTS
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = l_org_id
AND ( master_organization_id IS NULL
OR master_organization_id = l_org_id)
);
EXCEPTION
WHEN NO_DATA_FOUND THEN
is_MasterOrg := 'N';
END;
IF (is_MasterOrg = 'Y' AND l_entity_type = 'Single')
THEN
l_bom_header_rec := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
l_bom_revision_tbl := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
l_bom_component_tbl := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
l_bom_ref_designator_tbl := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
l_bom_sub_component_tbl := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
l_bom_comp_ops_tbl := Bom_Bo_Pub.G_MISS_BOM_COMP_OPS_TBL;
OPEN get_structure_details(l_ass_item_id,l_org_id,l_struct_name);
LOOP
FETCH get_structure_details into l_structure_rec;
EXIT WHEN get_structure_details%NOTFOUND;
IF (l_struct_name = 'PRIMARY')
THEN
l_struct_name := '';
END IF;
l_assembly_type := l_structure_rec.Assembly_Type ;
l_assembly_comment := l_structure_rec.Specific_Assembly_Comment;
l_attr_category := l_structure_rec.Attribute_category;
l_attr1 := l_structure_rec.Attribute1;
l_attr2 := l_structure_rec.Attribute2;
l_attr3 := l_structure_rec.Attribute3;
l_attr4 := l_structure_rec.Attribute4;
l_attr5 := l_structure_rec.Attribute5;
l_attr6 := l_structure_rec.Attribute6;
l_attr7 := l_structure_rec.Attribute7;
l_attr8 := l_structure_rec.Attribute8;
l_attr8 := l_structure_rec.Attribute9;
l_attr10 := l_structure_rec.Attribute10;
l_attr11 := l_structure_rec.Attribute11;
l_attr12 := l_structure_rec.Attribute12;
l_attr13 := l_structure_rec.Attribute13;
l_attr14 := l_structure_rec.Attribute14;
l_attr15 := l_structure_rec.Attribute15;
l_orig_sys_ref := l_structure_rec.Original_System_Reference;
l_imp_date := l_structure_rec.Implementation_Date;
END LOOP;
CLOSE get_structure_details;
FOR each_record in get_child_orgs(org_id => l_org_id)
LOOP
BEGIN
l_child_org_id := each_record.child_org_id;
l_bom_header_rec.Assembly_item_name := Get_Item_Name(l_ass_item_id,l_org_id);
l_bom_header_rec.Organization_Code := Get_Org_Code(l_child_org_id);
l_bom_header_rec.common_Assembly_Item_Name := Get_Item_Name(l_ass_item_id,l_org_id);
l_bom_header_rec.common_Organization_Code := Get_Org_Code(l_child_org_id);
l_bom_header_rec.Alternate_Bom_Code := l_struct_name;
l_bom_header_rec.Assembly_Type := nvl(l_assembly_type,1);
l_bom_header_rec.Transaction_Type := 'CREATE';
l_bom_header_rec.Return_Status := NULL;
l_bom_header_rec.Row_Identifier := NULL;
l_bom_header_rec.Assembly_Comment := l_assembly_comment;
l_bom_header_rec.Attribute_category := l_attr_category;
l_bom_header_rec.Attribute1 := l_attr1;
l_bom_header_rec.Attribute2 := l_attr2;
l_bom_header_rec.Attribute3 := l_attr3;
l_bom_header_rec.Attribute4 := l_attr4;
l_bom_header_rec.Attribute5 := l_attr5;
l_bom_header_rec.Attribute6 := l_attr6;
l_bom_header_rec.Attribute7 := l_attr7;
l_bom_header_rec.Attribute8 := l_attr8;
l_bom_header_rec.Attribute9 := l_attr9;
l_bom_header_rec.Attribute10 := l_attr10;
l_bom_header_rec.Attribute11 := l_attr11;
l_bom_header_rec.Attribute12 := l_attr12;
l_bom_header_rec.Attribute13 := l_attr13;
l_bom_header_rec.Attribute14 := l_attr14;
l_bom_header_rec.Attribute15 := l_attr15;
l_bom_header_rec.Original_System_Reference := l_orig_sys_ref;
l_bom_header_rec.BOM_Implementation_Date := l_imp_date;
/*
* Assign the item to child org if it is not assigned. The
* item assignment code is not in here. So if the item is
* not assigned to the child org we need to do the item
* assignment first before trying to create structure in
* the child org.
*/
Bom_Bo_Pub.Process_Bom
( p_bo_identifier => 'BOM'
, p_api_version_number => 1.0
, p_init_msg_list => TRUE
, p_bom_header_rec => l_bom_header_rec
, p_bom_revision_tbl => l_bom_revision_tbl
, p_bom_component_tbl => l_bom_component_tbl
, p_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, p_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_bom_header_rec => l_bom_header_rec
, x_bom_revision_tbl => l_bom_revision_tbl
, x_bom_component_tbl => l_bom_component_tbl
, x_bom_ref_designator_tbl => l_bom_ref_designator_tbl
, x_bom_sub_component_tbl => l_bom_sub_component_tbl
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, p_debug => 'N'
, p_output_dir => l_debug_file_dir
, p_debug_filename => l_log_file
, p_write_err_to_debugfile => 'Y'
);
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
END;
END LOOP;
END IF;
RETURN 'SUCCESS';
EXCEPTION WHEN OTHERS THEN
l_error := SQLERRM;
RETURN 'ERROR';
END create_common_structure;
/*****************************************************************
* Function : Get_Item_Name
* Parameters : Inventory item id
* : Organization id
* Return : Assembly Item name
* Purpose : This function is used to get the assembly item name
* for a given item id and organzation id.
******************************************************************/
FUNCTION Get_Item_Name(p_item_id IN NUMBER,p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
BEGIN
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
RETURN l_item_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Item_Name;
/*****************************************************************
* Function : Get_Org_Code
* Parameters : Organization id
* Return : Organization Code
* Purpose : This function is used to get the organization code
* for a organzation id.
******************************************************************/
FUNCTION Get_Org_Code(p_org_id IN NUMBER)
RETURN VARCHAR2
IS
l_org_code MTL_PARAMETERS.organization_code%TYPE;
BEGIN
SELECT organization_code
INTO l_org_code
FROM MTL_PARAMETERS
WHERE organization_id = p_org_id;
RETURN l_org_code;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return null;
END Get_Org_Code;
END Bom_common_Struct_Create_Sub;
Related Topics
Managing Business Events, Oracle Workflow Developer's Guide
Defining Procedures and Functions for Oracle Workflow, Oracle Workflow Developer's Guide