WIP Raw Material Issue with PL/SQL

Automating Oracle EBS WIP Raw Material Issue with PL/SQL

Manually issuing raw materials to a discrete job through the WIP Material Transactions form works fine at small volumes, but it doesn’t scale — every issue is a few clicks, and every mistake (wrong subinventory, over-issued quantity) has to be tracked down and corrected by hand. The assign_rm_to_job_single procedure automates this by driving the same open interface tables and concurrent program the form uses, but in a parameter-driven, callable way.

Purpose

Given an organization, job, component item, operation, lot, subinventory, and quantity, the procedure issues that raw material to the job and reports back success or a specific error message — no manual form entry required.

Inputs

Parameter Description
p_org_code Inventory organization code
p_job_name WIP job (discrete job) name
p_item_id Inventory item ID of the component being issued
p_operation_seq Operation sequence the material is issued against
p_lot_number Lot number of the material
p_subinventory_code Subinventory to issue from
p_quantity Quantity to issue
p_message (OUT) Result / error message returned to the caller

How It Works

  1. Validate the organization and job. p_org_code is resolved to an organization_id via org_organization_definitions, and p_job_name is resolved to a wip_entity_id via wip_entities. Each lookup sits in its own exception block, so a bad org code and a bad job name produce two different, specific messages instead of one generic failure.
  2. Pull job quantity and assembly item. wip_discrete_jobs supplies the job’s start_quantity and primary_item_id, used later for a quantity sanity check.
  3. Guard against over-issuing. Before inserting anything, the procedure sums what’s already been issued for that item on that job (wip_requirement_operations.quantity_issued) and compares it against what the BOM requires. If the new issue would push the total past the required quantity, it exits immediately with a message instead of creating a transaction that would need to be reversed later.
  4. Resolve the item’s UOM from mtl_system_items_b.
  5. Insert into the interface tables. A row goes into mtl_transactions_interface (transaction type “WIP Issue,” quantity negated since it’s a consumption), with a matching row in mtl_transaction_lots_interface carrying the lot number and quantity, linked by transaction_interface_id.
  6. Submit and wait for the Inventory Transaction Manager (INCTCM). After fnd_global.apps_initialize sets the applications context, fnd_request.submit_request kicks off the concurrent program that processes the interface rows. The procedure then polls fnd_concurrent.wait_for_request every few seconds, up to a timeout, until the request phase reports COMPLETE.
  7. Report back. On success, it checks for any leftover error rows tied to the group ID it stamped on the transaction and returns either ‘SUCCESS’ or the specific error text.

Why It’s Built This Way

  • Per-lookup exception handling means a caller (or support person) sees “Organization Not Exists,” “Organization and Job Not Exists,” or “Primary Item not Available” — each pointing at exactly what to fix, rather than an opaque Oracle error.
  • The pre-issue quantity check is the procedure’s main data-quality safeguard — it stops a component from being over-consumed relative to the BOM before the transaction is even created.
  • Synchronous polling on the concurrent request trades a bit of session time for certainty: the caller gets a definitive success/failure result instead of firing the request and hoping.

Sample Code:

 

 

PROCEDURE assign_rm_to_job_single (

p_org_code            IN       VARCHAR2,

p_job_name            IN       VARCHAR2,

p_item_id             IN       NUMBER,

p_operation_seq       IN       NUMBER,

p_lot_number                   VARCHAR2,

p_subinventory_code   IN       VARCHAR2,

p_quantity            IN       NUMBER,

p_message             OUT      VARCHAR2

)

IS

l_org_id               NUMBER;

l_rm_qty               NUMBER;

l_wip_entity_id        NUMBER;

l_rm_item_id           NUMBER;

l_assembly_item_id     NUMBER;

l_uom_code             VARCHAR2 (30);

l_interface_id         NUMBER;

—  := inv.mtl_transactions_interface_s.NEXTVAL;

l_request_id           NUMBER;

l_tot_qty              NUMBER;

l_user_id              NUMBER;                         —      := 7616;

— Replace with your actual user_id

l_resp_id              NUMBER;                         —     := 20560;

— Replace with your responsibility_id

l_appl_id              NUMBER;                          —      := 706;

— Replace with your application_id (usually WIP = 401)

l_transaction_type     NUMBER;

l_transaction_action   NUMBER;

l_source_code          VARCHAR2 (50);

l_phase                VARCHAR2 (100);

l_status               VARCHAR2 (100);

l_dev_phase            VARCHAR2 (100);

l_dev_status           VARCHAR2 (100);

l_message              VARCHAR2 (4000);

l_sleep_seconds        NUMBER          := 5;   — Check every 5 seconds

l_timeout_seconds      NUMBER          := 300;

— Timeout after 5 minutes (optional)

l_start_time           DATE            := SYSDATE;

l_success              BOOLEAN;

v_remaining_qty        NUMBER;

v_used_qty             NUMBER;

l_group_id             NUMBER          := TO_CHAR (SYSDATE, ‘ddhhmmss’);

l_first_operation      NUMBER;

l_exists               NUMBER;

l_item_type            VARCHAR2 (50);

— L_subinventory_code varchar2(50);

BEGIN

DBMS_OUTPUT.put_line (‘Start ‘);

 

 

DBMS_OUTPUT.put_line (‘Inside IF ‘);

 

— MAterial Issue

SELECT transaction_type_id

INTO l_transaction_type

FROM apps.mtl_transaction_types

WHERE (transaction_type_name) = ‘WIP Issue’;

 

l_transaction_action := 2;

l_source_code := ‘WIP RM ISSUE’;

 

/* ELSE

          l_transaction_action := 3;          — Transaction Action ID: Return

 

          SELECT transaction_type_id

            INTO l_transaction_type

            FROM apps.mtl_transaction_types

           WHERE  (transaction_type_name) = ‘WIP Return’;

 

          — Transaction Type ID: WIP Component Return

          l_source_code := ‘WIP RM RETURN’;*/

BEGIN

— 1. Get Organization ID

SELECT organization_id

INTO l_org_id

FROM apps.org_organization_definitions

WHERE organization_code = UPPER (TRIM (p_org_code));

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_message :=

‘Organization Not Exists Contact IT Team: organization Name=’

|| p_org_code;

RETURN;

WHEN OTHERS

THEN

p_message :=

‘Organization Unexpected issue: Contact IT Team: ‘ || SQLERRM;

RETURN;

END;

 

— 2. Get WIP Job ID

BEGIN

SELECT wip_entity_id

INTO l_wip_entity_id

FROM apps.wip_entities

WHERE UPPER (wip_entity_name) = UPPER (TRIM (p_job_name))

AND organization_id = l_org_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_message :=

‘Organization and Job Not Exists Contact IT Team: organization Name=’

|| p_org_code

|| ‘ Job= ‘

|| p_job_name;

RETURN;

WHEN OTHERS

THEN

p_message :=

‘Organization and Job Unexpected issue: Contact IT Team: ‘

|| SQLERRM;

RETURN;

END;

 

BEGIN

SELECT start_quantity, primary_item_id

INTO l_tot_qty, l_assembly_item_id

FROM apps.wip_discrete_jobs

WHERE wip_entity_id = l_wip_entity_id AND organization_id = l_org_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_message :=

‘ Primary Item not Available for the Organization and Job organization Name=’

|| p_org_code

|| ‘ Job= ‘

|| p_job_name;

RETURN;

WHEN OTHERS

THEN

p_message :=

‘Primary Item not Available Unexpected issue: Contact IT Team: ‘

|| SQLERRM;

RETURN;

END;

 

DBMS_OUTPUT.put_line (‘Before loop ‘);

— 3. Get RM item ID and UOM

DBMS_OUTPUT.put_line (‘Loop Start ‘);

l_rm_item_id := ”;

l_uom_code := ”;

l_rm_qty := 0;

 

SELECT bic.component_quantity

INTO l_rm_qty

FROM apps.bom_bill_of_materials bom,

apps.bom_inventory_components_v bic

WHERE bom.bill_sequence_id = bic.bill_sequence_id

AND bom.organization_id = l_org_id

AND bom.assembly_item_id = l_assembly_item_id

AND bic.component_item_id = p_item_id

AND supply_type = ‘Push’

AND NVL (bic.disable_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE);

 

DBMS_OUTPUT.put_line (‘UOM ‘);

 

BEGIN

SELECT SUM (wro.quantity_issued)

INTO l_exists

FROM apps.wip_requirement_operations wro

WHERE wro.wip_entity_id = l_wip_entity_id

AND wro.inventory_item_id = p_item_id

AND organization_id = l_org_id;

 

DBMS_OUTPUT.put_line (   ‘l_tot_qty= ‘

|| l_tot_qty

|| ‘l_exists = ‘

|| l_exists

|| ‘p_quantity= ‘

|| p_quantity

);

 

IF l_tot_qty < l_exists + p_quantity                    –l_item_type

THEN

p_message :=

‘RM Already Issued: and new issue QTY greaten than Required Qty’

|| l_wip_entity_id

|| ‘ inventory_item_id=’

|| p_item_id;

RETURN;

END IF;

END;

 

SELECT inventory_item_id, primary_uom_code, item_type

INTO l_rm_item_id, l_uom_code, l_item_type

FROM apps.mtl_system_items_b

WHERE inventory_item_id = p_item_id AND organization_id = l_org_id;

 

—    if L_item_type=’INTERMEDIATE’ then

—    L_subinventory_code:=’In-House’;

—    else

—    L_subinventory_code:=’Shop Floor’;

—    end if;

—  L_subinventory_code:=’RM Non-Ex’;

DBMS_OUTPUT.put_line (‘l_transaction_action= ‘ || l_transaction_action);

 

SELECT inv.mtl_transactions_interface_s.NEXTVAL

INTO l_interface_id

FROM DUAL;

 

— 4. Insert into MTL_TRANSACTIONS_INTERFACE

INSERT INTO mtl_transactions_interface

(transaction_interface_id, source_code, source_line_id,

source_header_id,

–source_header_type_id,

process_flag, transaction_mode,

lock_flag, transaction_type_id,    –transaction_action_id,

transaction_quantity,

— primary_quantity,

transaction_uom, inventory_item_id, organization_id,

subinventory_code,

— locator_id,

transaction_date,

–wip_entity_id,

transaction_source_type_id, transaction_source_id,

creation_date, created_by, last_update_date,

last_updated_by, operation_seq_num, attribute1

)

VALUES (l_interface_id, l_source_code, l_wip_entity_id,

l_wip_entity_id,

— NULL,

1,                                               — Pending

3,                                     — Background mode

2,                              — Lock flag (2 = unlocked)

l_transaction_type,

— Transaction Type: WIP Component Issue

—  l_transaction_action,

— Transaction Action: Issue

—      44,                            — Transaction Type ID: WIP Component Return

— 3,                             — Transaction Action ID: Return

-p_quantity,

l_uom_code, p_item_id, l_org_id,

p_subinventory_code,

–p_subinventory_code,

— p_locator_id,

SYSDATE,

–l_wip_entity_id,

5,                                  — Source Type: WIP Job

l_wip_entity_id,

SYSDATE, l_user_id, SYSDATE,

l_user_id, p_operation_seq, l_group_id

);

 

DBMS_OUTPUT.put_line (   ‘ -(l_rm_qty * l_tot_qty) ‘

|| – (l_rm_qty * l_tot_qty)

);

DBMS_OUTPUT.put_line (‘ l_tot_qty ‘ || l_tot_qty);

DBMS_OUTPUT.put_line (‘ l_rm_qty ‘ || l_rm_qty);

DBMS_OUTPUT.put_line (‘sequence ‘);

v_remaining_qty := l_rm_qty * l_tot_qty;

DBMS_OUTPUT.put_line (‘ insert Start ‘);

 

INSERT INTO mtl_transaction_lots_interface

(transaction_interface_id, lot_number,

— lot_number_id,

transaction_quantity, primary_quantity, last_update_date,

last_updated_by, creation_date, created_by, product_code,

product_transaction_id, process_flag

)

VALUES (l_interface_id,

— From your mtl_transactions_interface insert

p_lot_number,

— Lot number from inventory data (loop variable)

— rec.lot_number_id,          — Lot number ID (optional but preferred)

p_quantity,

— Issue quantity (negative for issue)

p_quantity,

— Primary quantity (same unless using secondary UOM)

SYSDATE,

l_user_id,

— Your user ID

SYSDATE, l_user_id, ‘ODM’,

l_interface_id, 1     –,l_org_id,rm_item.inventory_item_id

— Must match the transaction_interface_id above

);

 

DBMS_OUTPUT.put_line (‘after loop ‘);

COMMIT;

 

BEGIN

— Initialize apps context (if not already done)

apps.fnd_global.apps_initialize (user_id           => l_user_id,

— <your_user_id>,

resp_id           => l_resp_id,

— <your_resp_id>,

resp_appl_id      => l_appl_id

);            –<your_resp_appl_id>);

— Submit the Inventory Transaction Manager program

l_request_id :=

fnd_request.submit_request

(application      => ‘INV’,

— Inventory Application

program          => ‘INCTCM’,

— Short name of the program

description      => ‘Inventory Transaction Manager’,

start_time       => NULL,

sub_request      => FALSE

);

COMMIT;

DBMS_OUTPUT.put_line (   ‘Request submitted. Request ID: ‘

|| l_request_id

);

END;

 

— Wait for completion

LOOP

— Check status

l_success :=

apps.fnd_concurrent.wait_for_request (request_id      => l_request_id,

INTERVAL        => 5,

max_wait        => 300,

phase           => l_phase,

status          => l_status,

dev_phase       => l_dev_phase,

dev_status      => l_dev_status,

MESSAGE         => l_message

);

 

IF l_success

THEN

DBMS_OUTPUT.put_line (‘Request Completed.’);

DBMS_OUTPUT.put_line (‘Phase: ‘ || l_dev_phase);

DBMS_OUTPUT.put_line (‘Status: ‘ || l_dev_status);

DBMS_OUTPUT.put_line (‘Message: ‘ || l_message);

ELSE

DBMS_OUTPUT.put_line

(‘Failed to wait for request or it timed out.’);

END IF;

 

EXIT WHEN l_dev_phase = ‘COMPLETE’;

 

— Optional: timeout check

IF (SYSDATE – l_start_time) * 86400 > l_timeout_seconds

THEN

raise_application_error (-20001,

‘Timeout waiting for request ‘

|| l_request_id

);

END IF;

 

DBMS_LOCK.sleep (l_sleep_seconds);

END LOOP;

 

— Output result

DBMS_OUTPUT.put_line (‘Request Completed. Status: ‘ || l_dev_status);

 

IF upper(l_dev_status) = ‘NORMAL’

THEN

p_message := ”;

 

FOR rec IN

(SELECT ERROR_CODE AS MESSAGE_TYPE,

error_explanation AS MESSAGE_TEXT

FROM apps.mtl_transactions_interface

WHERE transaction_interface_id IN (

SELECT transaction_interface_id

FROM apps.mtl_transactions_interface

WHERE attribute1 = l_group_id

AND NVL (process_flag, 0) <> 1)

AND organization_id = l_org_id)

LOOP

DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TYPE);

p_message := rec.MESSAGE_TEXT;

END LOOP;

 

IF p_message = ” OR p_message IS NULL

THEN

p_message := ‘SUCCESS’;

 

END IF;

ELSE

FOR rec IN

(SELECT MESSAGE_TYPE, error_message AS MESSAGE_TEXT

FROM apps.mtl_interface_errors

WHERE transaction_id IN (SELECT transaction_interface_id

FROM mtl_transactions_interface

WHERE attribute1 = l_group_id)

AND organization_id = l_org_id)

LOOP

DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TYPE);

p_message := rec.MESSAGE_TEXT;

END LOOP;

END IF;

 

—            — Delete interface record

—            DELETE FROM apps.wip_job_schedule_interface

—                  WHERE interface_id = l_interface_id;

COMMIT;                                               — Commit deletion

EXCEPTION

WHEN NO_DATA_FOUND

THEN

p_message := ‘Invalid organization, job, or item.’ || SQLERRM;

ROLLBACK;

WHEN OTHERS

THEN

p_message := ‘Error: ‘ || SQLERRM;

ROLLBACK;

END assign_rm_to_job_single;

 

 

 

 

 

 

Recent Posts