Batch Open Interface programs allows you to bulk import OPM Batches and facilitate lifecycle transitions of the batches in bulk via a staging interface tables approach. These interface tables are:
GME_BATCH_HEADER_INTERFACE
GME_BATCH_DTLS_INTERFACE
GME_BATCH_ACTIONS_INTERFACE
GME_RESOURCE_TXNS_INTERFACE
GME_INTF_ERRORS
The following table summarizes the GME_BATCH_HEADER_INTERFACE table
| Column Name | Surrogate columns | Data Type | Size | Not Null | Internal Only | Key Col | PK | Column Description |
|---|---|---|---|---|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | Yes | No | Yes | Unique identifier of the row as provided by the user. User may use the sequence GME_BATCH_HEADER_INTERFACE_S to retrieve and populate a unique value. | |||
| ORGANIZATION_CODE | VARCHAR2 | 3 | No | Yes | No | Organization Code of batch to be imported. Organization Code will be ignored if the organization Id is provided. | ||
| ORAGNIZATION_ID | NUMBER | 10 | No | Yes | No | Organization Id of the batch to be imported. | ||
| BATCH_NO | VARCHAR2 | 32 | Yes | Yes | No | Whenever batch number is manual, batch number must be populated when importing a new batch. | ||
| BATCH_ID | BATCH_NO + ORGANIZATION_CODE | NUMBER | 10 | No | Yes | No | Batch identifier of an existing batch in Oracle Process Manufacturing to be updated using the import interface. Populate this column when updating, rerouting, or rescheduling an existing batch. User can optionally provide a combination of BATCH_NO and ORGANIZATION_CODE to identify the batch. | |
| INT_BATCH_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| RECIPE_VALIDITY_RULE_ID | RECIPE_NO + RECIPE_VERS | NUMBER | 10 | No | Yes | No | Recipe Validity Rule Identifier helps in deriving the recipe based upon which the batch is to be created. Alternately, user can either provide the RECIPY_ID or a combination of RECIPE_NO and RECIPE_VERS to identify the recipe. | |
| RECIPE_ID | RECIPE_NO + RECIPE_VERS | NUMBER | 10 | No | Yes | No | Recipe identifier of the recipe using which the batch is to be created. Alternately user can provide a combination of RECIPE_NO and RECIPE_VERS column values to identify the recipe. | |
| INT_RECIPE_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| RECIPE_NO | VARCHAR2 | 32 | No | Yes | No | Recipe Number using which the batch should be created. | ||
| RECIPE_VERS | NUMBER | 5 | No | Yes | No | Recipe Version of the Recipe Number using which the batch is to be created. | ||
| PLAN_START_DATE | DATE | No | No | No | Planned release date and time for the batch. | |||
| ACTUAL_START_DATE | DATE | No | No | No | Actual start date and time for the batch. This column can be used for updating actual start date correction when batch is already released ( LOAD_TYPE = 30). | |||
| DUE_DATE | DATE | No | No | No | Batch completion date and time. | |||
| PLAN_CMPLT_DATE | DATE | No | No | No | Planned batch completion date and time. | |||
| ACTUAL_CMPLT_DATE | DATE | No | No | No | Actual completion date and time. This column can be used for correcting actual completion date when batch is already completed ( LOAD_TYPE = 30). | |||
| BATCH_SIZE | NUMBER | No | No | No | Batch size value corresponds to the value in CREATION_MODE column. As an example, if the batch is created based on Product Quantity then this value refers to product quantity. | |||
| BATCH_SIZE_UOM | VARCHAR2 | 4 | No | No | No | Unit Of Measure of the batch size | ||
| CREATION_MODE | NUMBER | 5 | No | Yes | No | Mode of creation 10 Recipe Based, 20 Product Based, 30 Output Based, 40 Input Based |
||
| INVENTORY_ITEM_ID | ITEM + ORGANIZATION_CODE | NUMBER | No | Yes | No | Item Identifier of the primary product being produced using this batch. User can alternately provide a combination of ITEM and ORGANIZATION_CODE field values to identify the primary product. | ||
| INT_INVENTORY_ITEM_ID | NUMBER | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| ITEM | VARCHAR2 | 300 | No | Yes | No | Item number for the primary product. | ||
| ITEM_REVISION | VARCHAR2 | 3 | No | Yes | No | Revision number for the primary product. | ||
| UPDATE_INVENTORY_IND | VARCHAR2 | 1 | No | No | No | This indicates if a Lab batch updates inventory with inventory consumed. This indicator can only be set to Y if the organization is a Lab organization. Enter 'Y' if inventory should be updated with consumed ingredients, and 'N' otherwise | ||
| FIRMED_IND | NUMBER | 5 | No | No | No | If a batch should be firmed, not scheduled by Oracle scheduling engine, populate this column value as 1, otherwise enter 0. | ||
| LABORATORY_IND | NUMBER | 1 | No | No | No | Indicates if a Batch is a laboratory batch or a production batch. Possible values are 0 Production Batch 1 Laboratory Batch |
||
| INT_ORGANIZATION_ID | NUMBER | No | Yes | Internal data processing column. User should not populate or modify this column value. | ||||
| USE_WORKDAY_CAL | VARCHAR2 | 1 | No | No | No | Whether to use workday calendar or not. Possible values are T or F | ||
| ignore_qty_below_cap | VARCHAR2 | 1 | No | No | No | Ignore quantity below capacity. Possible values are T or F | ||
| contiguity_override | VARCHAR2 | 1 | No | No | No | Batch must be contained in a contiguous calendar period. Possible values are T or F | ||
| use_least_cost_vr | VARCHAR2 | 1 | No | No | No | Possible values are T or F | ||
| APPLY_FIXED_PROCESS_LOSS | VARCHAR2 | 1 | No | No | No | Apply Fixed process Loss as per recipe. Possible values are Y or N | ||
| ATTRIBUTE1 – ATTRIBUTE40 | VARCHAR2 | 240 | No | No | Descriptive Flex field Attributes 1 to 40 for Header Interface | |||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 30 | No | No | Descriptive Flex field category | |||
| LAST_UPDATE_DATE | DATE | No | No | Standard row WHO column | ||||
| LAST_UPDATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| CREATION_DATE | DATE | No | No | Standard row WHO column | ||||
| CREATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| LAST_UPDATE_LOGIN | NUMBER | 15 | No | No | Standard row WHO column | |||
| GROUP_ID | NUMBER | 15 | Yes | Yes | Group identifier provided by user to group rows for processing. Multiple rows in the interface table can have the same value to group rows of batches to be processed together by a single concurrent request. | |||
| REQUEST_ID | NUMBER | 15 | No | Yes | No | Interface code processing column. User must not update this column value. Import process populates the identifier of the concurrent request that processes this row. | ||
| PROCESS_PHASE | VARCHAR2 | 32 | No | Yes | No | Interface Code processing column. User must not update this column value. Used to populate the status of processing of this row by the concurrent process. | ||
| PROCESS_STATUS | VARCHAR2 | 32 | No | No | User needs to populate a value of '1' at the time of data load. If the corresponding row is not processed successfully for some reason, correct the problem in this row and update the value back to '1' after correcting the error column values. Re processing will happen only for rows where the process status column has a value of '1'. | |||
| LOAD_TYPE | NUMBER | 3 | Yes | No | Identify the type of action that needs to be performed on a specific row during the import process. Acceptable values are 10 Create Batch From Recipe 15 Create Batch and Release 30 Update Batch 40 Reschedule Batch 50 Reroute Batch |
The following table summarizes the GME_BATCH_DTLS_INTERFACE table
| Column Name | Surrogate Columns | Data Type | Size | Not Null | Internal Only | Key Col | PK | Column Description | Interface column relevant for Object Type |
|---|---|---|---|---|---|---|---|---|---|
| INTERFACE_HEADER_ID | NUMBER | No | No | Foreign key column to GME_BATCH_HEADER_INTERFACE table. This column should be populated only when a new batch is to be created and updated in the same run of the import process, and the batch numbering scheme is set to batch auto numbering in Oracle Process Manufacturing. If this record is being populated to simply update an already existing batch in Oracle, user can leave this value as NULL. | If this column is NULL, it means that there is no corresponding parent row in the Batch Header Import Interface table. This column is checked only when the batch is created in batch header interface within the same batch and the organization parameter set as auto-numbering. | ||||
| INTERFACE_ID | NUMBER | Yes | Yes | Unique identifier of the row as provided by the user. User may use the sequence GME_BATCH_DTLS_INTERFACE_S to retrieve and populate a unique value. | A unique value for each row must be provided for this column. | ||||
| ORGANIZATION_CODE | VARCHAR2 | 3 | No | Yes | No | Organization code of the batch owning organization. | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||
| BATCH_NO | VARCHAR2 | 32 | No | Yes | No | Batch Number of the batch being updated. This value can be NULL if BATCH_ID column is being populated, or a new batch is being created and details being updated at the same time. | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||
| BATCHSTEP_NO | NUMBER | 10 | No | Yes | No | Batch Step Number of the batch. Relevant for OBJECT_TYPE 300, 400, 500, 600, and 700. | 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||
| ACTIVITY | VARCHAR2 | 16 | No | Yes | No | Batch Activity for the batch step. Relevant for OBJECT_TYPE 400 ,500 and 700. | 400 - Activities 500 - Resource Requirements 700 - Process Parameters |
||
| RESOURCES | VARCHAR2 | 16 | No | Yes | No | Resource name used for the activity in the batch step. Relevant for OBJECT_TYPE 500 and 700. | 500 - Resource Requirements 700 - Process Parameters |
||
| ITEM | VARCHAR2 | 3000 | No | Yes | No | Concatenated segment value for an Item, the material to be added to the batch. Relevant for OBJECT_TYPE 200. | 200 – Material | ||
| LINE_NO | NUMBER | 5 | No | Yes | No | Sequential line number for each line type in a batch. Relevant for OBJECT_TYPE 200 and 600. | 200 – Material 600 - Step Items |
||
| LINE_TYPE | NUMBER | 5 | No | Yes | No | Indicator for material type. -1 = Ingredient 1 = Product & Co-Products 2 = Byproduct Relevant for OBJECT_TYPE 200 |
200 - Material | ||
| INT_ORGANIZATION_ID | NUMBER | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||||
| BATCH_ID | BATCH_NO + ORGANIZATION_CODE | NUMBER | 10 | No | Yes | No | Batch identifier of a valid batch in Oracle Process Manufacturing. User can alternately provide values of BATCH_NO and ORGANIZATIION_CODE. When creating a new batch, this field value will be NULL. | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
|
| INT_BATCH_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| BATCHSTEP_ID | BATCHSTEP_NO+ BATCH_NO + ORGANIZATION_CODE | NUMBER | 15 | No | Yes | No | Batch Step Identifier of the batch. User can alternately provide column values of BATCH_NO, BATCHSTEP_NO, and ORGANIZATION_CODE. Relevant for OBJECT_TYPE 300, 400, 500, 600, and 700. | 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
|
| INT_BATCHSTEP_ID | NUMBER | 15 | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| BATCHSTEP_ACTIVITY_ID | BATCH_NO + ORGANIZATION_CODE + BATCH_STEP_NO + ACTIVITY | NUMBER | 15 | No | Yes | No | Identifier for the activity in a batch step. Alternately, user can provide values of BATCH_NO, ORGANIZATION_CODE, BATCH_STEP_NO, and ACTIVITY to identify the batch step activity. If BATCHSTEP_ACTIVITY_ID is not provided by the user for an existing resource (update/delete resource, add process parameter to a resource), all the actions are performed on the lowest level of details provided for the batch. Relevant for OBJECT_TYPE 400, 500, and 700. | 400 - Activities 500 - Resource Requirements 700 - Process Parameters |
|
| INT_BATCHSTEP_ACTIVITY_ID | NUMBER | 15 | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| INT_BATCHSTEP_RESOURCE_ID | NUMBER | 15 | No | Yes | Internal data processing column. User should not populate or modify this column value. | ||||
| MATERIAL_DETAIL_ID | BATCH_NO + ORGANIZATION_CODE + LINE_NO + LINE_TYPE | NUMBER | 10 | No | Yes | No | Identifier for the material line. Alternately, user can provide column values of BATCH_NO, ORGANIZATION_CODE, LINE_NO, and LINE_TYPE. Relevant for OBJECT_TYPE 200 and 600. | 200 - Material 600 - Step Items |
|
| INT_MATERIAL_DETAIL_ID | NUMBER | 10 | No | Yes | Internal data processing column. User should not populate or modify this column value. | ||||
| ACTUAL_ACTIVITY_FACTOR | NUMBER | No | No | No | Actual activity factor. Value is required to add an activity for a Completed Batch Step. Update of ACTUAL_ACTIVITY_FACTOR will impact resource transactions. Relevant for OBJECT_TYPE 400. | 400 - Activities | |||
| ACTUAL_CMPLT_DATE | DATE | No | No | No | Actual completion date. Column value is required to add an Activity or a Resource to a step in Complete status. Relevant for OBJECT_TYPE 300, 400, and 500. | 300 - Steps 400 - Activities 500 - Resource Requirements |
|||
| ACTUAL_QTY | NUMBER | No | No | No | Direct population of material is allowed for non-stockable and non-transactable items only. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| ACTUAL_RSRC_COUNT | NUMBER | No | No | No | Actual resource count must be a positive value. Column value is required to add a Resource in Completed Status. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| ACTUAL_RSRC_QTY | NUMBER | No | No | No | Actual quantity processed by the resource. Column is required to add a Resource in Completed Status. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| ACTUAL_RSRC_USAGE | NUMBER | No | No | No | Used to add a resource usage in Completed Status only. For all usage updates for a batch, user should use Resource Transaction Interface. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| ACTUAL_START_DATE | DATE | No | No | No | Actual start date. Column is required to add an activity, or a resource to a step in Complete or WIP Status. Relevant for OBJECT_TYPE 300, 400, and 500. | 300 - Steps 400 - Activities 500 - Resource Requirements |
|||
| ACTUAL_STEP_QTY | NUMBER | No | No | No | Actual material quantity processed at the step. Column is required to add an activity, or a resource to a step in Complete or WIP Status. Relevant for OBJECT_TYPE 300. | 300 - Steps | |||
| ACTUAL_VALUE | VARCHAR2 | 16 | No | No | No | Actual value for this process parameter. Relevant for OBJECT_TYPE 700. | 700 - Process Parameters | ||
| BY_PRODUCT_TYPE | VARCHAR2 | 1 | No | No | No | This column is used to show whether the by-product is a sample type or other types. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| CONTRIBUTE_STEP_QTY_IND | VARCHAR2 | 1 | No | No | No | Indicator to specify if the item contributes to step quantity. Possible value are N for No. and Y for Yes. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| CONTRIBUTE_YIELD_IND | VARCHAR2 | 1 | No | No | No | Indicator to specify if the item (product or ingredient) contributes to yield. Value is N for No, and Y for Yes. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| COST_ALLOC | NUMBER | No | No | No | For products, fraction of cost allocated to this product. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| COST_ANALYSIS_CODE | VARCHAR2 | 32 | No | No | No | Cost analysis code. Relevant for OBJECT_TYPE 500. | 500 - Resource | ||
| COST_CMPNTCLS_CODE | VARCHAR2 | 32 | No | No | No | Cost component class code. Relevant for OBJECT_TYPE 500. | 500 - Resource | ||
| INT_ COST_CMPNTCLS_ID | NUMBER | No | No | No | Internal data processing column. User should not populate or modify this column value. | 500 - Resource | |||
| DTL_UM | VARCHAR2 | 3 | No | No | No | Detail Unit of Measure for the quantity. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| DUE_DATE | DATE | No | No | No | Required step completion date and time. Relevant for OBJECT_TYPE 300. | 300 - Steps | |||
| INVENTORY_ITEM_ID | ITEM | NUMBER | No | Yes | No | Identifier of the Item (Material). User can alternately provide the value of ITEM to identify the item. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| INT_INVENTORY_ITEM_ID | NUMBER | No | Yes | Internal data processing column. User should not populate or modify this column value. | |||||
| LOCATOR | VARCHAR2 | 3000 | No | No | No | Concatenated segments value for yield or supply inventory locator. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| LOCATOR_ID | LOCATOR | NUMBER | No | No | No | Identifier for yield or supply locator. Alternately, user can provide the value of LOCATOR field to identify the locator. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| INT_LOCATOR_ID | NUMBER | No | Yes | Internal data processing column. User should not populate or modify this column value. | |||||
| MATERIAL_REQUIREMENT_DATE | DATE | No | No | No | Requirement or Yield Date of the material. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| OFFSET_INTERVAL | NUMBER | No | No | No | Time offset in hours from step start to this activity. Must be a positive value. Relevant for OBJECT_TYPE 400 and 500. | 400 - Activities 500 - Resource Requirements |
|||
| OPRN_ID | OPRN_NO + OPRN_VERS | NUMBER | 10 | No | Yes | No | Valid operation identifier obtained from GMD_OPERATIONS. Alternately, user can provide value of OPRN_NO and OPRN_VERS column values to identify the operation. Relevant for OBJECT_TYPE 300. | 300 - Steps | |
| INT_OPRN_ID | NUMBER | 10 | No | Yes | Internal data processing column. User should not populate or modify this column value. | ||||
| OPRN_NO | VARCHAR2 | 32 | No | Yes | No | Operation Number to identify the Operation this step is based on. Relevant for OBJECT_TYPE 300. | 300 - Steps | ||
| OPRN_VERS | NUMBER | 5 | No | Yes | No | Operation Version to identify the Operation this step is based on. User should always provide this value along with OPRN_NO column value. Relevant for OBJECT_TYPE 300. | 300 - Steps | ||
| ORIGINAL_PRIMARY_QTY | NUMBER | No | No | No | Primary unit of measure for the ORIGINAL_QTY column value. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| ORIGINAL_QTY | NUMBER | No | No | No | The recipe quantity scaled for the batch. Planned quantity prior to any changes, value cannot be updated thereafter. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| ORIGINAL_RSRC_QTY | NUMBER | No | No | No | Original (populated during batch creation) Resource quantity. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| ORIGINAL_RSRC_USAGE | NUMBER | No | No | No | Original (populated during batch creation) Resource usage. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| PARAMETER_ID | PROCESS_PARAMETER | NUMBER | No | Yes | No | Valid unique identifier of a GMP Process parameter. Relevant for OBJECT_TYPE 700. | 700 - Process Parameters | ||
| INT_PARAMETER_ID | NUMBER | No | Yes | Internal data processing column. User should not populate or modify this column value. | |||||
| PHANTOM_TYPE | NUMBER | 5 | No | No | No | Phantom indicator. 0 = not a phantom 1 = automatic phantom replacement 2 = manual phantom Relevant for OBJECT_TYPE 200. |
200 - Material | ||
| PLAN_ACTIVITY_FACTOR | NUMBER | No | No | No | Number of times the activity is repeated. Relevant for OBJECT_TYPE 400. | 400 - Activities | |||
| PLAN_CMPLT_DATE | DATE | No | No | No | Planned completion date. Relevant for OBJECT_TYPE 300, 400, and 500. | 300 - Steps 400 - Activities 500 - Resource Requirements |
|||
| PLAN_QTY | NUMBER | No | No | No | The quantity that this line was planned to produce or consume, in the same unit of measure as that of the batch. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| PLAN_RSRC_COUNT | NUMBER | No | No | No | Planned resource count. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| PLAN_RSRC_QTY | NUMBER | No | No | No | Planned quantity processed by the resource. From GMD_OPERATIONS_RESOURCES and scaled to batch size. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| PLAN_RSRC_USAGE | NUMBER | No | No | No | Planned resource usage. Relevant for OBJECT_TYPE 500. | 500 - Resource Requirements | |||
| PLAN_START_DATE | DATE | No | No | No | Planned start date. Relevant for OBJECT_TYPE 300, 400, and 500. | 300 - Steps 400 - Activities 500 - Resource Requirements |
|||
| PLAN_STEP_QTY | NUMBER | No | No | No | Material quantity to be processed at the step. Relevant for OBJECT_TYPE 300. | 300 - Steps | |||
| PRIM_RSRC_IND | NUMBER | 5 | No | Yes | No | Primary resource indicator. 0 = Not a primary resource 1 = Primary resource 2 = Auxiliary resource Relevant for OBJECT_TYPE 500. |
500 - Resource Requirements | ||
| PROCESS_PARAM_ID | BATCH_NO + ORGANIZATION_CODE + STEP_NO + ACTIVITY + RESOURCE + PROCESS_PARAMETER | NUMBER | 15 | No | Yes | No | Resource Process Parameter Identifier is needed when updating process parameter values. User can alternately provide values of the columns BATCH_NO, ORGANIZATION_CODE, STEP_NO, ACTIVITY, RESOURCE, and PROCESS_PARAMETER to identify the process parameter. If the value of an existing process parameter is not provided by the user when updating or deleting a parameter, the action is performed at the lowest level of batch info available. Relevant for OBJECT_TYPE 700. |
700 - Process Parameters | |
| INT_PROCESS_PARAM_ID | NUMBER | 15 | No | Yes | Internal data processing column. User should not populate or modify this column value. | ||||
| PROCESS_PARAMETER | VARCHAR2 | 40 | No | Yes | No | Process parameter. Relevant for OBJECT_TYPE 700. | 700 - Process Parameters | ||
| RELEASE_TYPE | NUMBER | 5 | No | No | No | Consumption (or yield) type. 0 = Automatic (completion) 1 = Manual (completion) 2 = Incremental 3 = Automatic by Step Relevant for OBJECT_TYPE 200. |
200 - Material | ||
| RESOURCE_QTY_UM | VARCHAR2 | 3 | No | No | No | Unit of Measure for RESOURCE_QTY column value. Relevant for OBJECT_TYPE 500 |
500 - Resource Requirements | ||
| REVISION | VARCHAR2 | 3 | No | Yes | No | Item Revision. Relevant for OBJECT_TYPE 500. |
200 - Material | ||
| ROUNDING_DIRECTION | NUMBER | 5 | No | No | No | Whether to round up or round down to the nearest SCALE_MULTIPLE 0 = UP 1 = DOWN 3 = EITHER Relevant for OBJECT_TYPE 200. |
200 - Material | ||
| SCALE_MULTIPLE | NUMBER | No | No | No | The multiples of the batch unit of measure (in Scale UOM) for scaling. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| SCALE_ROUNDING_VARIANCE | NUMBER | No | No | No | Percentage, positive or negative, variance of scaled quantity allowed when scaling. Default value is zero. Relevant for OBJECT_TYPE 200. |
200 - Material | |||
| SCALE_TYPE | NUMBER | 5 | No | No | No | Following values are relevant for Object Type 200 0 = Non-scalable 1 = Scalable 2 = Scale by increment Following values are relevant for Object Type 500 0 = Non-scalable 1 = Linear scaling 2 = Scale by Charge |
200 - Material 500 - Resource Requirements |
||
| SCRAP_FACTOR | NUMBER | No | No | No | Scrap factor is given in percentage. Relevant for OBJECT_TYPE 200. |
200 - Material | |||
| SEQUENCE_DEPENDENT_IND | NUMBER | 5 | No | No | No | Allow user to select sequence dependent activities. A value of 1 means that the activity is sequence dependent. Any other value means activity is not sequence dependent. Relevant for OBJECT_TYPE 400. |
400 - Activities | ||
| STEP_CLOSE_DATE | DATE | No | No | No | Updated at the time when the step is closed. Defaults to system date and time and can be overridden. Relevant for OBJECT_TYPE 300. | 300 - Steps | |||
| STEPRELEASE_TYPE | NUMBER | 5 | No | No | No | Step Release Type. 1 = Manual 2 = Automatic Relevant for OBJECT_TYPE 300. |
300 - Steps | ||
| SUBINVENTORY | VARCHAR2 | 10 | No | No | No | Yield or Supply Sub-inventory. Relevant for OBJECT_TYPE 200. | 200 - Material | ||
| TARGET_VALUE | VARCHAR2 | 16 | No | No | No | Ideal value for this process parameter. Not required if Min & Max value are provided. May be text or a number. Relevant for OBJECT_TYPE 700. | 700 - Process Parameters | ||
| WIP_PLAN_QTY | NUMBER | No | No | No | This quantity is used in place of PLAN_QTY, if the batch status is WIP or above. Relevant for OBJECT_TYPE 200. | 200 - Material | |||
| RESCHEDULE_PRECEDING_STEPS | VARCHAR2 | 1 | No | No | No | Reschedule Preceding Steps. Populate T for True, or F for False. Relevant for OBJECT_TYPE 300. | 300 - Steps | ||
| RESCHEDULE_SUCCEEDING_STEPS | VARCHAR2 | 1 | No | No | No | Reschedule succeeding steps. Populate T for True, or F for False. Relevant for OBJECT_TYPE 300. | 300 - Steps | ||
| USE_WORK_DAY_CAL | VARCHAR2 | 1 | No | No | No | Use work day calendar. Populate Y for Yes, or N for No. Relevant for OBJECT_TYPE 300. | 300 - Steps | ||
| CONTIGUITY_OVERRIDE | VARCHAR2 | 1 | No | No | No | Contiguity Override. Populate T for True, or F for False. Relevant for OBJECT_TYPE 300 , reschedule step. | 300 – Steps | ||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 30 | No | No | No | Descriptive Flex field Category | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||
| ATTRIBUTE1 – ATTRIBUTE30 | VARCHAR2 | 240 | No | No | No | Descriptive Flex Fields Attribute1 to Attribute30 are supported. | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||
| CREATED_BY | NUMBER | 15 | No | No | Standard row WHO column | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
|||
| CREATION_DATE | DATE | No | No | Standard row WHO column | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||||
| LAST_UPDATE_DATE | DATE | No | No | Standard row WHO column | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
||||
| LAST_UPDATE_LOGIN | NUMBER | 15 | No | No | Standard row WHO column | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
|||
| LAST_UPDATED_BY | NUMBER | 15 | No | No | Standard row WHO column | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters | |||
| GROUP_ID | NUMBER | 15 | Yes | No | Group identifier provided by user to group rows for processing. It is recommended that all rows for batch detail processing of a single batch have the same group id. | Control Columns | |||
| REQUEST_ID | NUMBER | 15 | No | Yes | No | Interface code processing column. User should not update this column value. Import process populates the identifier of the concurrent request that processes this row. | Control Columns | ||
| PROCESS_PHASE | VARCHAR2 | 32 | No | Yes | No | Interface Code processing column. User should not update this column value. Used to populate the status of processing of this row by the concurrent process. | Control Columns | ||
| PROCESS_STATUS | VARCHAR2 | 32 | No | No | User needs to populate a value of '1' at the time of data load. If the corresponding row is not processed successfully for some reason, correct the problem in this row and update the value back to '1' after correcting the error column values. Re processing will happen only for rows where the process status column has a value of '1'. | Control Columns | |||
| OBJECT_TYPE | NUMBER | 3 | Yes | No | Interface Code processing column. User needs to provide this value to identify what kind of detail needs to be added to a batch. | 200 - Material 300 - Steps 400 - Activities 500 - Resource Requirements 600 - Step Items 700 - Process Parameters |
|||
| LOAD_TYPE | NUMBER | 3 | Yes | No | Interface Code processing column. User needs to provide the kind of action to be performed for the object specified in OBJECT_TYPE column. | 10 – Add 20 – Update 30 – Delete 40 – Reschedule Step |
The following table summarizes the GME_BATCH_ACTIONS_INTERFACE table
| Column Name | Surrogate Columns | Data Type | Size | Not Null | Internal Only | Key Col | PK | Column Description |
|---|---|---|---|---|---|---|---|---|
| INTERFACE_ID | NUMBER | Yes | Yes | Unique identifier of the row as provided by the user. User may use the sequence GME_BATCH_ACTIONS_INTERFACE_S to retrieve and populate a unique value. | ||||
| ORGANIZATION_CODE | VARCHAR2 | 3 | No | Yes | No | Organization Code to which the batch belongs to | ||
| BATCH_NO | VARCHAR2 | 32 | No | Yes | No | Batch Number of an existing batch in Oracle Process Manufacturing. Value provided by user is validated against an existing batch number in GME_BATCH_HEADER table | ||
| BATCHSTEP_NO | NUMBER | 10 | No | Yes | No | Batch Step Number of an already existing batch in Oracle Process Manufacturing. | ||
| INT_ORGANIZATION_ID | NUMBER | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| BATCH_ID | BATCH_NO + ORGANIZATION_CODE | NUMBER | 10 | No | Yes | No | Batch Identifier of an existing batch in Oracle Process Manufacturing for which action is to be performed. User can alternatively provide the BATCH_NO and ORGANIZATION_CODE to identify the batch. | |
| INT_BATCH_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| BATCHSTEP_ID | BATCH_NO+ ORGANIZATION_CODE+ BATCHSTEP_NO | NUMBER | 15 | No | Yes | No | Batch step identifier of the batch step for which action needs to be performed. Alternately, user can provide a combination of BATCH_NO, ORGANIZATION_CODE, and BATCHSTEP_NO to identify the batch step. | |
| INT_BATCHSTEP_ID | NUMBER | 15 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| ACTUAL_START_DATE | DATE | No | No | No | Actual start date and time for batch or batch step | |||
| ACTUAL_CMPLT_DATE | DATE | No | No | No | Actual completion date and time for batch or batch step | |||
| CLOSE_DATE | DATE | No | No | No | Batch or Step Close Date and time. | |||
| IGNORE_MATERIAL_EXCEPTION | VARCHAR2 | 1 | No | No | No | Ignore Material Exceptions during batch action. Valid values are Y or N | ||
| TERMINATE_REASON_ID | TERMINATE_REASON_CODE | NUMBER | 10 | No | No | No | Batch termination reason identifier. User can provide the TERMINATE_REASON_CODE value instead. | |
| INT_TERMINATE_REASON_ID | NUMBER | 10 | No | Yes | Internal data processing column. User should not populate or modify this column value. | |||
| TERMINATE_REASON_CODE | VARCHAR2 | 30 | No | No | No | Batch terminate reason name | ||
| create_resv_pend_lots | NUMBER | 2 | No | No | No | When a Step or a Batch Reverted to pending this flag need to be passed. If the value is not passed then it will be treated as “No”. Create reservation for pending lots Valid values are 0 = No 1 = Yes |
||
| continue_lpn_txn | VARCHAR2 | 1 | No | No | No | Y or N | ||
| REOPEN_STEPS | VARCHAR2 | 1 | No | No | No | Y or N | ||
| CREATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| CREATION_DATE | DATE | No | No | Standard row WHO column | ||||
| LAST_UPDATE_DATE | DATE | No | No | Standard row WHO column | ||||
| LAST_UPDATE_LOGIN | NUMBER | 15 | No | No | Standard row WHO column | |||
| LAST_UPDATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| GROUP_ID | NUMBER | 15 | Yes | Yes | No | Interface code processing column. Please do not update. Used for internal grouping of rows for efficient concurrent processing of many batch actions. | ||
| REQUEST_ID | NUMBER | 15 | No | Yes | No | Interface Code processing column. Please do not update. Processing logic populates the identifier of the concurrent request that processed this row. | ||
| PROCESS_PHASE | VARCHAR2 | 32 | No | Yes | No | Interface Code processing column. Please do not update. Used to populate the status of processing of this row by the concurrent process. | ||
| PROCESS_STATUS | VARCHAR2 | 32 | No | No | User needs to populate a value of '1' at the time of data load. If the corresponding row is not processed successfully for some reason, correct the problem in this row and update the value back to '1' after correcting the error column values. Re processing will happen only for rows where the process status column has a value of '1'. | |||
| OBJECT_TYPE | NUMBER | 3 | Yes | No | The object, Batch or Step, for which action is to be performed. 100 - Batch 300 - Steps |
|||
| ACTION | NUMBER | 3 | Yes | No | The type of batch or batch step action to be performed 10 – Release 20 – Complete 30 – Close 40 – Re Open 50 – Revert to WIP 60 – Un Release 70 – Terminate Batch 80 – Cancel Batch |
The following table summarizes the GME_RESOURCE_TXNS_INTERFACE table
| Column Name | Surrogate Columns | Data Type | Size | Not Null | Internal Only | Key Col | PK | Column Description |
|---|---|---|---|---|---|---|---|---|
| INTERFACE_ID | NUMBER | Yes | Yes | Unique identifier of the row as provided by the user. User may use the sequence GME_RESOURCE_TXNS_INTERFACE_S to retrieve and populate a unique value. | ||||
| ORGANIZATION_CODE | VARCHAR2 | 3 | No | Yes | No | Organization Code to which the batch belongs to | ||
| BATCH_NO | VARCHAR2 | 32 | No | Yes | No | Batch Number of an existing batch in Oracle Process Manufacturing. Value provided by user is validated against an existing batch number in GME_BATCH_HEADER table. | ||
| BATCH_STEP_NO | NUMBER | 10 | No | Yes | No | Batch Step Number of an already existing batch in Oracle Process Manufacturing. Value provided by user is validated against an existing batch step number in table GME_BATCH_STEPS. | ||
| ACTIVITY | VARCHAR2 | 16 | No | Yes | No | Batch Activity to which the Resource is associated. | ||
| RESOURCES | VARCHAR2 | 16 | No | Yes | No | Resource used for this transaction. | ||
| RESOURCE_INSTANCE_NO | NUMBER | 10 | NO | Yes | No | Resource Instance Number used for this transaction. | ||
| BATCH_ID | BATCH_NO + ORGANIZATION_CODE | NUMBER | 10 | No | Yes | No | Batch Identifier of an existing batch in Oracle Process Manufacturing for which transaction is to be performed. User can alternatively provide the BATCH_NO and ORGANIZATION_CODE to identify the batch. | |
| INT_BATCH_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| BATCHSTEP_RESOURCE_ID | BATCH_NO + ORGANIZATION_CODE + BATCH_STEP_NO + ACTIVITY + RESOURCE | NUMBER | 10 | No | Yes | No | This column value can be obtained from GME_BATCHSTEP_RESOURCES table to identify a unique combination of the batch, batch step, activity and resource being transacted. The user can either provide this value or provide a combination of BATCH_NO, ORGANIZATION_CODE, BATCH_STEP_NO, ACTIVITY, and RESOURCE | |
| INT_ BATCHSTEP_RESOURCE_ID | NUMBER | 10 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| RESOURCE_USAGE | NUMBER | No | No | No | Quantity of the resource used. | |||
| TRANS_DATE | DATE | No | No | No | Date of the resource transaction. | |||
| REASON_NAME | VARCHAR2 | 30 | No | No | No | Reason code associated with the resource transaction. | ||
| START_DATE | DATE | No | No | No | Date and time the resource transaction started. Beginning of the resource transaction date must fall within the start and end date of the resources as defined in GME_BATCH_RESOURCES | |||
| END_DATE | DATE | No | No | No | Date and time the resource transaction ended. End of the resource transaction must fall within the begin and end date of the resource as defined in, GME_BATCH_RESOURCES | |||
| INSTANCE_ID | RESOURCE_ INSTANCE_NO | NUMBER | 15 | No | No | No | Resource Instance Identifier used for this resource transaction. | |
| INT_RESOURCE_INSTANCE_ID | NUMBER | 15 | No | Yes | Internal data processing column. User must not populate or modify this column value. | |||
| INT_ORGANIZATION_ID | NUMBER | No | Yes | Internal data processing column. User must not populate or modify this column value. | ||||
| FROM_INSTANCE_NO | NUMBER | The old INSTANCE_NO. | ||||||
| FROM_INSTANCE_ID | NUMBER | The old INSTANCE_ID. | ||||||
| INT_FROM_INSTANCE_ID | NUMBER | Yes | Internal data processing column. User must not populate or modify this column value. | |||||
| ACTION | NUMBER | 3 | The type of resource transaction action to perform. 10 - Start Resource, 20 - End Resource, 30 - Assign Resource, NULL - Import completed resource transaction. | |||||
| COST_SOURCE | NUMBER | Indicate the source of the OSP resource charges. 1=Resource Cost, 2=PO Price. | ||||||
| PO_HEADER_ID | NUMBER | Purchase order header unique identifier, derived from the RCV_TRANSACTIONS table. | ||||||
| PO_LINE_ID | NUMBER | Purchase order line unique identifier, derived from the RCV_TRANSACTIONS table. | ||||||
| ACTUAL_RESOURCE_ RATE | NUMBER | Actual rate of the resource. | ||||||
| CURRENCY_CODE | VARCHAR2 | 15 | Source document foreign currency, derived from the RCV_TRANSACTIONS table. | |||||
| CURRENCY_CONVERSION_DATE | DATE | Currency conversion date, derived from the RCV_TRANSACTIONS table. | ||||||
| CURRENCY_CONVERSION_TYPE | VARCHAR2 | 30 | Foreign currency conversion type, derived from the RCV_TRANSACTIONS table. | |||||
| RCV_TRANSACTION_ID | NUMBER | Receiving transaction unique identifier, which corresponds to the receiving transaction that generates resource transactions. | ||||||
| CURRENCY_CONVERSION_RATE | NUMBER | Currency conversion rate, derived from the RCV_TRANSACTIONS table. | ||||||
| TRANS_QTY_UM | VARCHAR2 | 3 | No | No | No | Unit of Measure of the resource to be transacted. | ||
| REASON_ID | NUMBER | No | No | No | Reason unique identifier | |||
| CREATION_DATE | DATE | No | No | Standard row WHO column | ||||
| LAST_UPDATE_DATE | DATE | No | No | Standard row WHO column | ||||
| CREATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| LAST_UPDATED_BY | NUMBER | 15 | No | No | Standard row WHO column | |||
| LAST_UPDATE_LOGIN | NUMBER | 15 | No | No | Standard row WHO column | |||
| ATTRIBUTE1 – ATTRIBUTE30 | VARCHAR2 | 240 | No | No | Descriptive Flex Fields Attribute1 to Attribute30 are supported. | |||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 30 | No | No | Descriptive Flex Category | |||
| PROCESS_PHASE | VARCHAR2 | 32 | No | Yes | No | Interface Code processing column. Please do not update. Used to populate the status of processing of this row by the concurrent process. | ||
| PROCESS_STATUS | VARCHAR2 | 32 | No | No | User needs to populate a value of '1' at the time of data load. If the corresponding row is not processed successfully for some reason, correct the problem in this row and update the value back to '1' after correcting the error column values. Re processing will happen only for rows where the process status column has a value of '1'. | |||
| REQUEST_ID | NUMBER | 15 | No | Yes | No | Interface Code processing column. Please do not update. Processing logic populates the identifier of the concurrent request that processed this row. | ||
| GROUP_ID | NUMBER | 15 | Yes | Yes | No | Interface code processing column. Please do not update. Used for internal grouping of rows for efficient concurrent processing of many batch actions. |
The following table summarizes the GME_INTF_ERRORS table.
| Column Name | Data Type | Size | Not Null | PK | Column Description |
|---|---|---|---|---|---|
| GROUP_ID | NUMBER | 10 | No | No | User input for "grouping" the rows together for processing |
| OBJECT_TYPE | VARCHAR2 | 30 | Yes | No | Indicates whether it's a Batch or Step or Material or Resource Transaction |
| INTERFACE_ID | NUMBER | 240 | Yes | No | Interface ID for which error is being logged |
| COLUMN_NAME | VARCHAR2 | 30 | No | No | Actual Column name of Interface Table |
| MESSAGE_NAME | VARCHAR2 | 60 | No | No | Message Token or Internal Name of Message from FND_NEW_MESSAGES |
| MESSAGE_TYPE | VARCHAR2 | 30 | No | No | Severity ( Warning , Critical or Fatal Error ) |
| MESSAGE_TEXT | VARCHAR2 | 1000 | No | No | Complete Message Text in User's language |
| SOURCE_TABLE_NAME | VARCHAR2 | 30 | Yes | No | Coming from Which Interface Table |
| REQUEST_ID | NUMBER | No | No | Concurrent Request ID which processed this data | |
| PARAM1 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter , used for substitution and generation of messages in other Languages |
| PARAM2 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter, used for substitution and generation of messages in other Languages |
| PARAM3 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter, used for substitution and generation of messages in other Languages |
| PARAM4 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter, used for substitution and generation of messages in other Languages |
| PARAM5 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter, used for substitution and generation of messages in other Languages |
| PARAM6 | VARCHAR2 | 30 | No | No | For Storing the Message Parameter, used for substitution and generation of messages in other Languages |
| TOKEN1 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| TOKEN2 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| TOKEN3 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| TOKEN4 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| TOKEN5 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| TOKEN6 | VARCHAR2 | 60 | No | No | For Storing the values of Tokens. |
| LAST_UPDATED_BY | NUMBER | No | No | Standard WHO Columns | |
| CREATION_DATE | DATE | No | No | Standard WHO Columns | |
| CREATED_BY | NUMBER | 15 | No | No | Standard WHO Columns |
| LAST_UPDATE_LOGIN | NUMBER | 15 | No | No | Standard WHO Columns |
| LAST_UPDATE_DATE | DATE | No | No | Standard WHO Columns |