Introduction/ Issue: In Oracle E-Business Suite (EBS), WIP Discrete Jobs are typically created manually. However, when integrating with external applications like Oracle APEX, automation is required to streamline the process.
This solution provides a PL/SQL Package that allows external applications to create WIP jobs programmatically and integrate seamlessly with EBS.
Why we need to do / Cause of the issue:
- External systems (APEX / MES / other applications) need to create jobs dynamically
- Reduce dependency on manual EBS operations
- Ensure accurate and consistent job data
- Enable real-time job creation for manufacturing processes
How do we solve:
A custom Oracle PL/SQL package was developed to:
- Validate Organization, Item, and Job data
- Insert records into WIP Job Schedule Interface
- Submit the WIP Mass Load concurrent program
- Return status messages for integration feedback
Integration with APEX:
APEX can call this package via PL/SQL blocks allowing external users or systems to create WIP jobs directly in EBS, without manual intervention.
| CREATE OR REPLACE PACKAGE APPS.xx_wip_job_pkg AS
PROCEDURE create_job_with_validation ( p_org_code IN VARCHAR2, p_item_code IN VARCHAR2, p_job_name IN VARCHAR2, p_quantity IN NUMBER, p_class_code IN VARCHAR2, p_schedule_date IN DATE, p_completion_date IN DATE, p_batch_number IN VARCHAR2, p_runcard_number IN VARCHAR2, P_creation_flag in varchar2, p_Child_job_name in Varchar2, p_message OUT VARCHAR2 ); END xx_wip_job_pkg; /
CREATE OR REPLACE PACKAGE BODY APPS.xx_wip_job_pkg AS PROCEDURE get_initialization ( p_user_id OUT NUMBER, p_appl_id OUT NUMBER, p_resp_id OUT NUMBER, p_message OUT VARCHAR2 ) IS BEGIN SELECT user_id INTO p_user_id FROM apps.fnd_user WHERE UPPER (user_name) = ‘THARUNKUMAR’;
SELECT application_id INTO p_appl_id FROM apps.fnd_application_tl WHERE application_name = ‘Work in Process’;
SELECT responsibility_id INTO p_resp_id FROM apps.fnd_responsibility_tl WHERE responsibility_name = ‘Work in Process’;
p_message := ‘SUCCESS’; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Invalid Initialization.’ || SQLERRM; ROLLBACK; WHEN OTHERS THEN p_message := ‘Invalid Initialization. Contact IT Team: ‘ || SQLERRM; ROLLBACK; END get_initialization;
PROCEDURE create_job_with_validation ( p_org_code IN VARCHAR2, p_item_code IN VARCHAR2, p_job_name IN VARCHAR2, p_quantity IN NUMBER, p_class_code IN VARCHAR2, p_schedule_date IN DATE, p_completion_date IN DATE, p_batch_number IN VARCHAR2, p_runcard_number IN VARCHAR2, p_creation_flag IN VARCHAR2, p_child_job_name IN VARCHAR2, p_message OUT VARCHAR2 ) IS l_org_id NUMBER; l_item_id NUMBER; l_dummy NUMBER; l_group_id NUMBER := TO_CHAR (SYSDATE, ‘ddhhmmss’); — fnd_global.conc_request_id; — or a sequence if needed l_request_id NUMBER; l_interface_id 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_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; l_job_status_flag NUMBER; l_exists NUMBER; l_lot_number VARCHAR2 (200); l_completion_sub_inv VARCHAR2 (200); l_load_type NUMBER; l_process_phase NUMBER; l_process_status NUMBER; BEGIN get_initialization (l_user_id, l_appl_id, l_resp_id, p_message);
IF p_message = ‘SUCCESS’ THEN NULL; ELSE RETURN; END IF;
IF p_creation_flag = ‘CREATION’ THEN l_load_type := 1; l_process_phase := 2; l_process_status := 1;
BEGIN 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; DBMS_OUTPUT.put_line (p_message); RETURN; WHEN OTHERS THEN p_message := ‘Organization Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
DBMS_OUTPUT.put_line (p_message);
— Step 1: Get Org ID BEGIN SELECT 1 INTO l_exists FROM apps.wip_discrete_jobs_v WHERE wip_entity_name = p_job_name AND organization_id = l_org_id;
p_message := ‘Job Already Exists: ‘ || p_job_name; RETURN; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN p_message := ‘Job Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
DBMS_OUTPUT.put_line (‘p_message’ || p_message);
— Step 2: Get Item ID BEGIN SELECT inventory_item_id INTO l_item_id FROM apps.mtl_system_items_b WHERE segment1 = UPPER (TRIM (p_item_code)) AND organization_id = l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Item Not Exists Contact IT Team: Item Name=’ || p_item_code || ‘ l_org_id=’ || l_org_id; RETURN; WHEN OTHERS THEN p_message := ‘Organization Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
DBMS_OUTPUT.put_line (‘p_message’ || p_message);
BEGIN SELECT lookup_code INTO l_job_status_flag FROM apps.fnd_lookup_values WHERE lookup_type = ‘WIP_JOB_STATUS’ AND enabled_flag = ‘Y’ AND meaning = ‘Released’; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Lookup code Not Exists fro for lookup_type = ”WIP_JOB_STATUS” and meaning = ”Released” Contact IT Team: ‘; RETURN; WHEN OTHERS THEN p_message := ‘Lookup code Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END; /* — Step 3: BOM Validation BEGIN SELECT 1 INTO l_dummy FROM bom_bill_of_materials WHERE assembly_item_id = l_item_id AND organization_id = l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘No BOM found for Item: ‘ || p_item_code; DBMS_OUTPUT.put_line (‘p_message’ || p_message); RETURN; WHEN OTHERS THEN p_message := ‘bom_bill_of_materials Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;*/ ELSIF p_creation_flag = ‘COMPLETE’ THEN l_load_type := 3; l_process_phase := 2; l_process_status := 1;
BEGIN SELECT lookup_code INTO l_job_status_flag FROM apps.fnd_lookup_values WHERE lookup_type = ‘WIP_JOB_STATUS’ AND enabled_flag = ‘Y’ AND meaning = ‘Complete’; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Lookup code Not Exists fro for lookup_type = ”WIP_JOB_STATUS” and meaning = ”Released” Contact IT Team: ‘; RETURN; WHEN OTHERS THEN p_message := ‘Lookup code Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
IF p_job_name IS NOT NULL THEN l_lot_number := p_job_name; l_completion_sub_inv := ‘Shop Floor’; –Shop Floor ELSE p_message := ‘ job_name is null P_job_name: ‘ || p_job_name; END IF;
BEGIN 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; DBMS_OUTPUT.put_line (p_message); RETURN; WHEN OTHERS THEN p_message := ‘Organization Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
DBMS_OUTPUT.put_line (p_message);
— Step 1: Get Org ID BEGIN SELECT 1 INTO l_exists FROM apps.wip_discrete_jobs_v WHERE wip_entity_name = p_job_name AND organization_id = l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Job Not Exists: ‘ || p_job_name; RETURN; WHEN OTHERS THEN p_message := ‘Job Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
DBMS_OUTPUT.put_line (‘p_message’ || p_message);
— Step 2: Get Item ID BEGIN SELECT inventory_item_id INTO l_item_id FROM apps.mtl_system_items_b WHERE segment1 = UPPER (TRIM (p_item_code)) AND organization_id = l_org_id; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Item Not Exists Contact IT Team: Item Name=’ || p_item_code || ‘ l_org_id=’ || l_org_id; RETURN; WHEN OTHERS THEN p_message := ‘Organization Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END; ELSIF p_creation_flag = ‘CLOSED’ THEN BEGIN SELECT lookup_code INTO l_job_status_flag FROM apps.fnd_lookup_values WHERE lookup_type = ‘WIP_JOB_STATUS’ AND enabled_flag = ‘Y’ AND meaning = ‘Closed’; EXCEPTION WHEN NO_DATA_FOUND THEN p_message := ‘Lookup code Not Exists fro for lookup_type = ”WIP_JOB_STATUS” and meaning = ”Released” Contact IT Team: ‘; RETURN; WHEN OTHERS THEN p_message := ‘Lookup code Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END; ELSE p_message := ‘Creation Flag Not Valid for : ‘ || p_creation_flag; END IF;
–Released –3 –Complete –4 –Closed–12
— Step 4: Insert into WIP_JOB_SCHEDULE_INTERFACE SELECT apps.wip_job_schedule_interface_s.NEXTVAL INTO l_interface_id FROM DUAL;
—WIP.WIP_INTERFACE_ERRORS BEGIN INSERT INTO apps.wip_job_schedule_interface (interface_id, GROUP_ID, organization_id, job_name, primary_item_id, start_quantity, net_quantity, first_unit_start_date, class_code, status_type, load_type, process_phase, process_status, creation_date, created_by, last_update_date, last_updated_by, attribute1, attribute2, attribute3, attribute4, completion_subinventory, lot_number ) VALUES (l_interface_id, l_group_id, l_org_id, p_job_name, l_item_id, p_quantity, p_quantity, p_schedule_date, p_class_code, –p_class_code, l_job_status_flag, — released,closed,comolete l_load_type, — Create Standard Discrete Job l_process_phase, — Validation phase l_process_status, — Pending SYSDATE, l_user_id — user id , SYSDATE, l_user_id, — user id, p_child_job_name, p_runcard_number, — job card no p_batch_number, — original job referance p_batch_number, l_completion_sub_inv, l_lot_number );
DBMS_OUTPUT.put_line (‘l_org_id: ‘ || l_org_id); DBMS_OUTPUT.put_line (‘p_job_name: ‘ || p_job_name); DBMS_OUTPUT.put_line (‘l_item_id: ‘ || l_item_id); DBMS_OUTPUT.put_line (‘p_quantity: ‘ || p_quantity); DBMS_OUTPUT.put_line (‘p_class_code: ‘ || p_class_code); DBMS_OUTPUT.put_line (‘l_job_status_flag: ‘ || l_job_status_flag); DBMS_OUTPUT.put_line (‘p_Child_job_name: ‘ || p_child_job_name); DBMS_OUTPUT.put_line (‘p_runcard_number: ‘ || p_runcard_number); DBMS_OUTPUT.put_line (‘l_completion_sub_inv: ‘ || l_completion_sub_inv ); DBMS_OUTPUT.put_line (‘l_lot_number: ‘ || l_lot_number); EXCEPTION WHEN OTHERS THEN p_message := ‘Insert Unexpected issue: Contact IT Team: ‘ || SQLERRM; RETURN; END;
COMMIT; — Step 5: Submit WIP Mass Load apps.fnd_global.apps_initialize (user_id => l_user_id, –fnd_global.user_id, resp_id => l_resp_id, –fnd_global.resp_id, resp_appl_id => l_appl_id –fnd_global.resp_appl_id );
BEGIN l_request_id := fnd_request.submit_request (application => ‘WIP’, program => ‘WICMLP’, description => ‘WIP Mass Load from APEX’, start_time => NULL, sub_request => FALSE, argument1 => l_group_id, argument2 => 1 ); COMMIT; p_message := ‘WIP Mass Load submitted. Request ID: ‘ || l_request_id;
— 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’; ELSE FOR rec IN (SELECT ERROR_TYPE MESSAGE_TYPE, error AS MESSAGE_TEXT FROM wip.wip_interface_errors WHERE interface_id = l_interface_id) LOOP DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TYPE); p_message := rec.MESSAGE_TEXT; END LOOP;
— Delete interface record /*DELETE FROM apps.wip_job_schedule_interface WHERE interface_id = l_interface_id;*/ COMMIT; — Commit deletion DBMS_OUTPUT.put_line (‘Interface record deleted.’); DBMS_OUTPUT.put_line ( ‘Request Failed or Warning. Message: ‘ || l_message ); END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (‘Error occurred: ‘ || SQLERRM); RAISE; END;
— Optional: Log /* INSERT INTO xxttk_wip_job_log ( interface_id, job_name, item_code, org_code, bom_validated, status, message ) VALUES ( l_interface_id, p_job_name, p_item_code, p_org_code, ‘YES’, ‘SUBMITTED’, p_message );*/ COMMIT; EXCEPTION WHEN OTHERS THEN p_message := ‘Error: ‘ || SQLERRM; ROLLBACK; END create_job_with_validation;
END xx_wip_job_pkg; / |
