MES Client Extensions

This appendix describes the client extensions, functions procedures, and packages integrated into Oracle MES for Discrete Manufacturing.

This appendix covers the following topics:

Overview of MES Client Extensions and Packages

Oracle MES for Discrete Manufacturing provides custom programming instructions to control job operation readiness, transaction movements, reordering features, and viewing logic. The following client extensions and packages are provided:

Component Tracking for Non-serialized Items

The View Assembly Serial Number is not designed to display non-serialized components. This package enables you to view non-serialized components issued from the Express Transact Component page to a specific serialized assembly in the View Assembly Serial Number page.

Technical Specifications for using the Functions:

/* Hook to get Component issued Quantity per Assembly Serial in Exp transact component page
as well as view assembly serial Page. */
Function get_assy_serial_issue_qty(p_org_id IN NUMBER,
p_job_id IN NUMBER,
p_op_seq IN NUMBER,
p_assy_item_id IN NUMBER,
p_assy_serial IN VARCHAR2,
p_comp_item_id IN NUMBER,
p_new_wip_entity_id IN NUMBER DEFAULT NULL)
return Number
IS
l_issue_qty Number := null;
BEGIN
/* Customize by mentioning the correct column of DFF which is mapped to Assembly Serial
Number in the below SQL. */
/*
select (nvl(sum(mmt.transaction_quantity),0)) * -1
into l_issue_qty
from mtl_material_transactions mmt
where mmt.transaction_source_type_id = 5
and ((mmt.transaction_source_id = p_job_id and mmt.operation_seq_num = p_op_seq)
or mmt.transaction_source_id = p_new_wip_entity_id)
and mmt.organization_id= p_org_id
and mmt.inventory_item_id = p_comp_item_id
and mmt.transaction_action_id in (1,27) --Only considering issue/return, and not -ve
issue/return
and mmt.attributeXX = p_assy_serial;
Custom Hooks Page 4 of 16
*/
return l_issue_qty;
END get_assy_serial_issue_qty;
/* Hook to get the DFF Column Mapping for Assembly Serial in Exp transact component page. */
Function get_assy_serial_dff_column
return Number
IS
l_attr_col Number := null;
BEGIN
/* Customize by defining the Transaction History DFF column mapping to Assembly Serial
Number below .
Valid values are only values between 1-15 which maps to the corresponding columns Attribute1 -
Attribute15 . */
/* l_attr_col := 1; */
return l_attr_col;
END get_assy_serial_dff_column;

Backflush Lot and Component Selection

The Move - Backflush Component Lot Selection client extension enables custom logic to derive the component lot numbers for backflush transactions during move, and move and complete transactions when using the Express Transact Components page. If the program does not return a value, the logic derives the lot number.

A PL/SQL package: WIP_WS_CUSTOM, contains the custom programming instructions invoked.

Procedure: LOT_HOOK

Parameter IN/OUT Type Description
org_id IN NUMBER Identification of the organization.
wip_entity_id IN NUMBER WIP entity identification of the job.
operation_seq_num IN NUMBER Operation sequence number of the operation.
repetitive_schedule_id IN NUMBER Identification of the repetitive schedule.
inventory_item_id IN NUMBER Identification of the component.
supply_subinv IN VARCHAR2 Supply subinventory.
supply_locator_id IN NUMBER Supply locator.
revision IN VARCHAR2 Revision of the assembly.
primary_qty IN NUMBER Quantity moved.
lot_number IN OUT VARCHAR2 Lot number derived by the program.
return_status OUT VARCHAR2 Value indicating performance status, choices are:

  • E: Error

  • S: Success
return_message OUT VARCHAR2 Error string the program returns.

Related Topics

Examples for Lot Selection Defaulting during Backflush Transactions

Examples for Lot Selection Defaulting during Backflush Transactions

When there are multiple backflush transactions for the same component, on the same job—you can set the selection method for defaulting lot numbers used in the previous transactions. The following examples demonstrate how transactions and parameter setup determine the lot selection. See: Material Parameters , Oracle Work in Process User's Guide

No Existing Previous Backflush Transactions

Manual Push Transactions

In this example, the Work in Process Parameter setup is:

Previous Backflush Transaction History, No Alternate Lot Selection Method

Previous Backflush Transaction History with Alternate Lot Selection Method

Alternate Lot Selection Method with No On-hand Lot Quantity

All Backflush Transactions Considered as Transaction History

Backflush History Does Not Span Across Jobs

Backflush Transaction History Includes Multiple Lots with On-hand Quantity

Component Revision Default During Backflush

The following procedure contains the file of wipwsctb.pls to provide the client extension to default the component revision during backflush moves. You can insert your revision change code in this program.

the picture is described in the document text

Express Move – To Operation Sequence and To Operation Step

This client extension provides the ability to derive the To Operation Sequence and To Operation Step when using the Express Transact Components page during a move transaction. This program can have several possible results:

A PL/SQL package: WIP_WS_CUSTOM, contains the custom programming instructions invoked.

Procedure: MOVE_TO_STEP_HOOK

Parameter IN/OUT Type Description
org_id IN NUMBER Identification of the organization.
wip_entity_id IN NUMBER WIP entity identification of the job.
fm_op_seq IN NUMBER Originating operation sequence number that the assembly is being moved from.
fm_op_step_type IN NUMBER Originating operation sequence step number that the assembly is being moved from.
fm_dept_id IN NUMBER Identification of department where assembly is being moved from.
to_op_seq IN OUT NUMBER Operation sequence number where assembly is being moved.
to_op_step_type IN OUT NUMBER Operation sequence step where assembly is being moved
return_status OUT VARCHAR2 Value indicating performance status, choices are:

  • E: Error

  • S: Success
return_message OUT VARCHAR2 Error string the program returns.

Related Topics

Creating Express Component Transactions for Serialized Assemblies

Last Updated By Value

The Last Updated By column value in the Dispatch List defaults to the employee login value. This program provides the ability to display the employee resource who Clocked In, instead of the employee login.

Procedure: get_last_updated_by

function get_last_updated_by(p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_operation_seq_num IN NUMBER) return varchar2
is
begin

/* Enter values in the following code lines. This can be customized to return the employee name to display on the Dispatch List in the Last Updated By column. The column value will be the return value of this function. */

return NULL;
end get_last_updated_by;

Ordering Attributes

This program enables you to use the sequence attributes, Custom Order Attribute 1 and Custom Order Attribute 2, in the Dispatch List Ordering Criteria parameter to customize your dispatch list order.

Custom Ordering Attributes

To create customized values for the Dispatch List Ordering Criteria parameter

  1. In the wipwsctb.pls file, add Custom Order Attribute values:

    • wip_ws_customer.get_orderby_attribute_1()

    • wip_ws_customer.get_orderby_attribute_2()

  2. Navigate to the Dispatch List Ordering Criteria parameter page.

  3. In the Sequence Attribute field, select the applicable Custom Order Attributes.

  4. Select your other values for the parameter, see: Dispatch List Ordering Criteria

  5. Save your work.

Ready Status

In the Dispatch List, the Ready Status icon for each job operation is set in the Ready Status Criteria parameter. The options are No Exceptions, No Shop Floor Status Assigned, and Quantity in Queue or Run. When a job operation meets the conditions, the Ready check mark is automatically updated and displayed as Ready. This client extension enables custom logic to determine if a job operation is ready with your specific definitions. In addition to the Ready Status icon visual indicator in the Dispatch List, the Ready Status client extension's value displays on the Work Content page. The program returns one of two different values, Yes or No:

A PL/SQL package, WIP_WS_CUSTOM, contains the custom programming instructions invoked.

Note: When the job operation is not ready prior to implementation of the client extension, the program does not effect the ready status of the job, displaying a Not Ready status.

When the job operation is ready prior to implementation of the client extension, if the program returns a Yes value—the job status in the Dispatch List is ready with the Ready status visual indicator enabled. If the client extension return a No value, the job status in the Dispatch List is not ready.

Ready Status Function—get_custom_ready_status

Parameter IN/OUT Type Description
wip_entity_id IN NUMBER WIP entity identification of the job
operation_seq_num IN NUMBER Operation Sequence number
serial_number IN VARCHAR2 Serial number of assembly
attribute1 IN VARCHAR2 Reserved for future use
attribute2 IN VARCHAR2 Reserved for future use
attribute3 IN VARCHAR2 Reserved for future use
return_value OUT VARCHAR2 Value is one of the following:

  • Yes: Indicates the job operation passes the Ready Status program is in Ready Status.

  • No: Indicates the job operation did not pass the Ready Status program.

Serial Number Page Customizations

Two programs provide the ability to customize serial number pages:

Configuring Attributes for Context Display

get_dummy_serial_dff_context:

Using this program you can customize the Viiew Assembly Serial Number page context so that results are displayed based on the context selected; by default no data is displayed.

Once the serial attributes Digital Forensics Framework (DFF) is enabled in forms, you can select the context and provide the context values while performing express material transactions. These details are viewed in the View Assembly Serial Number page by selecting the attributes context.

/* Hook to get Dummy DFF context which is configured to show all the attributes from all
different context */
Function get_dummy_serial_dff_context
return varchar2
IS
l_dff_context_code varchar2(50) :=null;
BEGIN
/* This dummy context used by Component serials table in view assembly serial page to show all
the attributes of different context */
l_dff_context_code := 'Physical Parameters';
return l_dff_context_code;
END get_dummy_serial_dff_context;

Validating Serialized Components

validate_comp_serial

This program provides the ability to validate components with serial attributes in the Express Transact Components page. You can set up business specific conditions to validate components before clicking Add on this page:

PROCEDURE validate_comp_serial( p_org_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_assy_serial IN VARCHAR2,
p_op_seq_num IN NUMBER,
p_comp_item_id IN NUMBER,
p_comp_serial IN VARCHAR2,
p_quantity IN NUMBER,
p_txn_type_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2,
p_errmsg OUT NOCOPY VARCHAR2)
IS
l_dummy number :=-1;
BEGIN
p_return_status := 'S';
p_errmsg := null;
/*p_return_status should return E/S/W/I.
S- will add component serial successfully.
W- will add component serial along with warning message.
I- will add component serial along with information message.
E- will throw error message and doesn't add the component serial.*/
begin
select 1 into l_dummy
from mtl_serial_numbers
where serial_number=p_comp_serial
and inventory_item_id=p_comp_item_id
and ( C_ATTRIBUTE10='Red' or C_ATTRIBUTE10 IS NULL);
exception
when NO_DATA_FOUND then
p_return_status := 'E';
p_errmsg := 'Custom validation message : Only serial number with physical attribute
Red allowed';
end;
END validate_comp_serial;

Time and Attendance Systems Integration

Actual time spent on a work order by an operator is recorded using the Clock In and Clock Out capability. This time input is used to charge the manual resource transactions for calculating the work in process inventory value for particular accounting periods. MES for Discrete Manufacturing can be integrated with other time and attendance systems to collect actual time. Third party systems are often deployed to collect definite and exact actual recorded times, and pass the data to a payroll application. These systems might be used by organizations where labor costs are high, or actual time recording is a mandatory requirement for government regulations.

To integrate other time and attendance systems into MES, an interface table and concurrent program are used:

WIP_TIME_ENTRY_INTERFACE Table

This interface table is used to enter records for direct labor hours and actual attendance hours. The TIME_ENTRY_TYPE column is used for differentiating between various time type records using Direct Labor Hours, Scheduled Available Hours, and Actual Attendance Hours. The table supports the following features:

In the following table, if the WIP_ENTITY_TYPE or JOB_NAME columns are:

Column Name Type Description
INTERFACE_ID NUMBER Unique record identification
TIME_ENTRY_TYPE NUMBER Time entry record types:

  • 1 - Actual Attendance Hours

  • 2- Scheduled Available Hours

  • 3 - Direct Labor Hours
ORGANIZATION_ID NUMBER Organization identifier
ORGANIZATION_CODE VARCHAR2 Organization code
WIP_ENTITY_ID NUMBER Work order identifier
JOB_NAME VARCHAR2 Job name
OPERATION_SEQ_NUMBER NUMBER Operation sequence number
DEPARTMENT_CODE VARCHAR2 Department code
DEPARTMENT_ID NUMBER Department identifier
RESOURCE_SEQ_NUM NUMBER Resource sequence number
RESOURCE_CODE VARCHAR2 Resource code
RESOURCE_ID NUMBER Resource identifier
EQUIPMENT_ITEM VARCHAR2 Equipment name corresponding to a machine resource
INSTANCE_ID NUMBER Instance identifier of the equipment as per bill of material definition
SERIAL_NUMBER VARCHAR2 Equipment serial number
ASSEMBLY_SERIAL_NUMBER VARCHAR2 Assembly serial number reported for this time
UOM_CODE VARCHAR2 Unit of Measure code
EMPLOYEE_ID NUMBER Employee identifier
ACTUAL_START_DATE DATE Actual start date
ACTUAL_END_DATE DATE Actual end date
DURATION NUMBER Duration
ACTION_FLAG NUMBER Action:

  • 1- Add

  • 2 - Subtract

  • Null - Actual time entry record
MODE_FLAG NUMBER Values are:

  • 1 - Null = Create

  • 2 - Update: Update mode can be used for updating duration and end date.
PROCESS_STATUS NUMBER Process status
ERROR VARCHAR2 Error Message
REQUEST_ID NUMBER Concurrent request identification
PROGRAM_ID NUMBER Program identification
PROGRAM_APPLICATION_ID NUMBER Program application identification
Program_update_date
PROGRAM_UPDATE_DATE		 DATE Program update date
CREATED_BY NUMBER Created by
CREATION_DATE DATE Record date creation
LAST_UPDATED_BY NUMBER Last updated by action
LAST_UPDATE_DATE DATE Last update date
LAST_UPDATE_LOGIN NUMBER Login information for last update

Import Time Entry (WIP_TIME_ENTRY_DATA) Concurrent Program

The Import Time Entry concurrent request imports actual times captured through the WIP_TIME_ENTRY_INTERFACE table into MES for calculation of labor performance metrics.

For each valid row in the interface table, the API, WIP_TIME_ENTRY_PUB.process reads all pending transactions for the given organization, and performs the following validations:

Transaction Validation

This client extension enables custom logic to determine if move, scrap, reject, and completion transactions are processed or stopped. The program returns one of several values. If the value returned is:

A PL/SQL package, WIP_WS_CUSTOM, contains the custom programming instructions invoked.

Transaction Validation Function—get_transaction_validation_value

Parameter IN/OUT Type Description
wip_entity_id IN Number WIP entity identification of the job
mtl_header_id IN Number Material header identification
txn_type f IN Varchar2 Transaction type
from_op_seq IN Number From operation sequence
from_step IN Varchar2 From operation step
to_op_seq IN Number To operation sequence
to_step IN Varchar2 To operation step
txn_quantity IN Number Transaction quantity
txn_uom IN Varchar2 Transaction unit of measure
scrap_quantity IN NUMBER Scrap quantity (for combination transaction)
reject_quantity IN NUMBER Reject quantity (for combination transaction)
subinv locator_id IN VARCHAR2 Subinventory locator identification
assembly_lot IN NUMBER Assembly lot number.
assembly_serial IN VARCHAR2 Assembly serial number
reason_id IN NUMBER Reason identification
reference IN VARCHAR2 Transaction reference
sales_order_id IN VARCHAR2 Sales order identification
sales_order_line_id IN NUMBER Sales order line identification
overcompletion IN STRING Overcompletion
project_id IN NUMBER Project identification
task_id IN NUMBER Task identification
scrap_acct_id IN NUMBER Scrap account identification
kanban_id IN NUMBER Kanban identification
attribute1 IN VARCHAR2 Reserved for future use
attribute2 IN VARCHAR2 Reserved for future use
attribute3 IN VARCHAR2 Reserved for future use
attribute4 IN VARCHAR2 Reserved for future use
attribute5 IN VARCHAR2 Reserved for future use
return_status OUT VARCHAR2 Indicates return status of the program: Yes, No, Warning, Informational
return_message OUT VARCHAR2 Custom message returned by the program
return_attribute1 OUT VARCHAR2 Reserved for future use
return_attribute2 OUT VARCHAR2 Reserved for future use

Viewing Attachments

The View Attachment icon is hidden by default on the Dispatch List, Search Job, and Search Serial Number pages. This program provides the ability to view attachments and also restrict the ability to view for user specific conditions. Attachments are added at the operations level on the discrete job.

Configure your view on these pages in the Personalization Structure page to view attachments on these pages. See: Configuring Document Attachments

Procedure: get_attachment_exists

function get_attachment_exists(p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_department_id IN NUMBER) return NUMBER
is
l_dummy number;
begin
l_dummy := 0;

/* ---Enter information in the following code lines.

You can customize the program per your business requirements at various levels and the type of attachments according to the setting in the Attachment Sources parameter. If the return values is:

*/
begin
select 1 into l_dummy from fnd_attached_documents
where entity_name='WIP_DISCRETE_OPERATIONS'
and pk1_value=p_wip_entity_id
Custom Hooks Page 5 of 16
and pk2_value=p_operation_seq_num
and pk3_value=p_organization_id;
exception
when no_data_found then
l_dummy := 0;
end;
return l_dummy;
end get_attachment_exists;

Viewing Exceptions

In the Dispatch List, the default display for exceptions is:

This program customizes the code so you can view the number of exceptions both open and closed.

get_custom_exception

function get_custom_exception(wip_entity_id number, operation_seq_num
number,serial_number varchar2) return varchar2
is
l_count number;
l_message varchar2(50);
l_wip_entity_id number;
l_operation_seq_num number;
begin
l_wip_entity_id := wip_entity_id;
l_operation_seq_num := operation_seq_num;
--Fill the custom implmentation here and return the final status the user wants to see on the page
for Exception column
select count(*) into l_count
from wip_exceptions
where wip_entity_id=l_wip_entity_id
and operation_seq_num=l_operation_seq_num;
l_message := '(Custom)Exceptions Count:'||l_count;
return l_message;
end;