Introduction/ Issue: Completing a WIP job in EBS usually involves multiple manual steps. Integrating with external systems like APEX requires an automated and reliable method to complete jobs programmatically.
A custom PL/SQL API was created to allow external applications to complete WIP jobs, including material transactions, operation-level completions, and inventory updates
Why we need to do / Cause of the issue:
- Automate WIP completion from external systems
- Ensure inventory and costing accuracy
- Enable real-time updates without EBS manual intervention
- Support bulk and high-volume operations
How do we solve:
APEX or other external systems can call this API to complete jobs in EBS automatically, making the entire WIP lifecycle seamless between external apps and Oracle EBS.
| CREATE OR REPLACE PACKAGE APPS.xx_wip_job_pkg AS
PROCEDURE Complete_job ( p_org_code IN VARCHAR2, p_job_name IN VARCHAR2, p_assembly_item_code IN VARCHAR2, p_subinventory_code IN VARCHAR2, p_quantity IN NUMBER, p_message OUT VARCHAR2 ); END xx_wip_job_pkg; / CREATE OR REPLACE PACKAGE BODY APPS.xx_wip_job_pkg AS
PROCEDURE complete_job ( p_org_code IN VARCHAR2, p_job_name IN VARCHAR2, p_assembly_item_code IN 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_id NUMBER; v_parent_req_id NUMBER; — := 69157192; — ITM request id v_worker_req_id NUMBER; v_phase VARCHAR2 (1); v_status VARCHAR2 (1); v_counter NUMBER := 0; v_max_wait_loops NUMBER := 120; — 120 x 10s = 20 min max wait l_shelf_life_days NUMBER; l_operation_seq_num NUMBER; l_scrap_qty NUMBER; BEGIN DBMS_OUTPUT.put_line (‘Start ‘); DBMS_OUTPUT.put_line (‘Inside IF ‘); get_initialization (l_user_id, l_appl_id, l_resp_id, p_message); –l_user_id := 7616;
IF p_message = ‘SUCCESS’ THEN NULL; ELSE RETURN; END IF;
— MAterial Issue SELECT transaction_type_id INTO l_transaction_type FROM apps.mtl_transaction_types WHERE (transaction_type_name) = ‘WIP Completion’;
l_transaction_action := 2; l_source_code := ‘WIP Completion’;
/* 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 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, primary_item_id INTO l_wip_entity_id, l_item_id FROM 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 — 1. Get Organization ID SELECT primary_uom_code, shelf_life_days INTO l_uom_code, l_shelf_life_days FROM mtl_system_items_b WHERE organization_id = l_org_id AND inventory_item_id = l_item_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Item Not Exists Contact IT Team: organization Name=’ || p_org_code || ‘ item := ‘ || p_assembly_item_code; RETURN; WHEN OTHERS THEN p_message := ‘Item Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
— 3. Get RM item ID and UOM DBMS_OUTPUT.put_line (‘Loop Start ‘);
BEGIN SELECT NVL (MAX (operation_seq_num), 0) INTO l_operation_seq_num FROM apps.wip_operations_v WHERE wip_entity_id = l_wip_entity_id AND organization_id = l_org_id; — AND operation_seq_num = p_to_operation_seq_num; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Final Operation Not Found’; RETURN; WHEN OTHERS THEN p_message := ‘Resources Issue Contact IT Team=’ || SQLERRM; RETURN; END;
/*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 = rm_item.inventory_item_id AND supply_type = ‘Push’ AND NVL (bic.disable_date, TRUNC (SYSDATE)) >= TRUNC (SYSDATE);*/ 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, final_completion_flag ) 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, l_item_id, l_org_id, p_subinventory_code, –In-House –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, l_operation_seq_num, l_group_id, ‘Y’ );
DBMS_OUTPUT.put_line (‘sequence ‘);
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, lot_expiration_date ) VALUES (l_interface_id, — From your mtl_transactions_interface insert p_job_name, — 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 SYSDATE + CASE WHEN l_shelf_life_days IS NULL OR l_shelf_life_days = 0 THEN 365 * 5 ELSE l_shelf_life_days END );
/* insert into mtl_serial_numbers_interface (TRANSACTION_INTERFACE_ID, SOURCE_CODE, SOURCE_LINE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, FM_SERIAL_NUMBER, TO_SERIAL_NUMBER, ERROR_CODE, PROCESS_FLAG) values(l_interface_id, l_source_code, l_wip_entity_id, sysdate, –Last Update date l_user_id, — Last updated by sysdate, –Creastion date l_user_id, –Created by l_user_id, — Last update login l_operation_seq_num, — Needs input l_operation_seq_num, — Needs input NULL, –Error code 1 –Process Flag ); */ FOR l_opr_rec IN (SELECT * FROM apps.wip_operations_v WHERE wip_entity_id = l_wip_entity_id AND organization_id = l_org_id) LOOP l_scrap_qty := NVL (l_opr_rec.quantity_scrapped, 0);
INSERT INTO cst_comp_snap_interface (transaction_interface_id, wip_entity_id, operation_seq_num, last_update_date, last_updated_by, creation_date, created_by, quantity_completed, primary_quantity –,prior_scrap_quantity ) VALUES (l_interface_id, l_wip_entity_id, –Job id l_opr_rec.operation_seq_num, –Operatoin Seq No SYSDATE, –Last update date l_user_id, –Last updated by SYSDATE, –Creation date l_user_id, –Created by l_opr_rec.quantity_completed, — l_opr_rec.scheduled_quantity, — — l_opr_rec.quantity_completed –Quantity Completed p_quantity –Priamry Quantity
–,l_opr_rec.quantity_scrapped ); END LOOP;
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(Complete Job)’, 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 l_dev_status = ‘NORMAL’ THEN p_message := ‘SUCCESS’;
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 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 OR p_message = ‘SUCCESS’ THEN p_message := ‘SUCCESS’; DBMS_OUTPUT.put_line (‘p_message: ‘ || p_message); RETURN; END IF; ELSE 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 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;
DBMS_OUTPUT.put_line (‘p_message1: ‘ || p_message); — — 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 complete_job;
END xx_wip_job_pkg; /
|
