Introduction / Issue
Closing a WIP job in EBS normally means going through the Discrete Jobs form manually — checking if all transactions are done, confirming the job status, and then closing it. This is fine for one or two jobs, but it gets difficult when many jobs need to be closed together or on a regular schedule.
External systems like APEX need to trigger this process, there’s no easy way to do it without manually working through the EBS forms each time.
Why We Need to Do This / Cause of the Issue
Closing WIP jobs one by one doesn’t work well when volumes are high or the process needs to run automatically. Here’s why:
- Closing jobs manually through the forms takes too much time.
- There was no way to close multiple jobs together in one run.
- External systems like APEX can’t trigger a job close directly.
- Bad data (wrong org, missing job, wrong status) could break the whole batch.
- Without proper tracking, it was hard to tell which job caused a failure.
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.
The procedure checks each record, and if everything is valid, loads it into Oracle’s WIP_JOB_SCHEDULE_INTERFACE table. It then submits the standard WIP Mass Load (WICMLP) program to close the jobs.
If even one record in the batch fails validation, the whole batch is rolled back and marked as an error — so partial or inconsistent batches never go through.
Procedure:
PROCEDURE restr_wip_close_job (
p_message OUT VARCHAR2,
p_status OUT VARCHAR2,
p_batch_id OUT NUMBER
)
IS
l_org_id NUMBER;
l_item_id NUMBER;
l_wip_entity_id NUMBER;
l_group_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE,'ddhhmmss'));
l_request_id NUMBER;
l_interface_id NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_resp_name VARCHAR2(40) := '009_WIP_SuperUser';
l_job_status_flag NUMBER;
l_exists NUMBER;
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;
l_timeout_seconds NUMBER := 300;
l_start_time DATE := SYSDATE;
l_success BOOLEAN;
l_status_meaning VARCHAR2(50);
l_processed_count NUMBER;
l_batch_id NUMBER;
l_error_count NUMBER;
TYPE t_stg_id_tab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE t_err_tab IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
l_err_stg_id t_stg_id_tab;
l_err_msg t_err_tab;
l_err_idx NUMBER := 0;
l_val_status VARCHAR2(20);
l_val_message VARCHAR2(4000);
l_wip_compl_rec apps.wip_job_schedule_interface%ROWTYPE;
BEGIN
l_processed_count := 0;
l_error_count := 0;
----------------------------------------
-- Step 1 : Get apps session
----------------------------------------
get_initialization(l_resp_name, l_user_id, l_appl_id, l_resp_id, l_val_message);
IF p_message = 'SUCCESS' THEN
NULL;
ELSE
RETURN;
END IF;
dbms_output.put_line('l_user_id : ' || l_user_id);
dbms_output.put_line('l_resp_id : ' || l_resp_id);
dbms_output.put_line('l_resp_appl_id : ' || l_appl_id);
----------------------------------------
-- Step 2 : Initialize APPS Session
----------------------------------------
fnd_global.apps_initialize(
user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_appl_id
);
dbms_output.put_line('Apps Initialized');
--------------------------------------------------------------
-- Step 3 : Generate and Assign Batch ID to Staging Records
--------------------------------------------------------------
SELECT .xx_wip_batch_s.NEXTVAL
INTO l_batch_id
FROM dual;
p_batch_id := l_batch_id;
UPDATE .xx_wip_compl_close_stg
SET batch_id = l_batch_id
WHERE process_flag = 'N'
AND batch_id IS NULL;
SAVEPOINT batch_start;
--------------------------------------------------------
-- Step 4 : Process Staging Records
--------------------------------------------------------
FOR compl_rec IN (
SELECT *
FROM .xx_wip_compl_close_stg
WHERE batch_id = l_batch_id
AND NVL(process_flag, 'N') = 'N'
AND UPPER(process_msg) = UPPER('NEW')
)
LOOP
p_message := NULL;
p_status := 'Success';
--------------------------------------------------------
-- Step 5 : Validate Action
--------------------------------------------------------
IF compl_rec.action NOT IN ('COMPLETE', 'CLOSE') THEN
p_message := 'Invalid action. Use COMPLETE or CLOSE';
p_status := 'Error';
END IF;
l_status_meaning := CASE compl_rec.action
WHEN 'COMPLETE' THEN 'Complete'
WHEN 'CLOSE' THEN 'Closed'
END;
--------------------------------------------------------
-- Step 6 : Validate ORG ID
--------------------------------------------------------
BEGIN
SELECT organization_id
INTO l_org_id
FROM org_organization_definitions
WHERE organization_code = UPPER(TRIM(compl_rec.org_code));
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_message := p_message || ' Organization Not Exists: ' || compl_rec.org_code;
p_status := 'Error';
WHEN OTHERS THEN
p_message := p_message || ' Org error: ' || SQLERRM;
p_status := 'Error';
END;
--------------------------------------------------------
-- Step 7 : Validate Job exists
--------------------------------------------------------
BEGIN
SELECT wip_entity_id
INTO l_wip_entity_id
FROM apps.wip_entities
WHERE UPPER(wip_entity_name) = UPPER(TRIM(compl_rec.job_name))
AND organization_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_message := p_message || ' Job Not Found: ' || compl_rec.job_name;
p_status := 'Error';
WHEN OTHERS THEN
p_message := p_message || ' Job fetch error: ' || SQLERRM;
p_status := 'Error';
END;
--------------------------------------------------------
-- Step 8 : Get Job Status
--------------------------------------------------------
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 = l_status_meaning;
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_message := p_message || ' Lookup not found for: ' || l_status_meaning;
WHEN OTHERS THEN
p_message := p_message || ' Lookup error: ' || SQLERRM;
END;
-- Error Updating
IF l_val_status = 'Error' THEN
l_err_idx := l_err_idx + 1;
l_err_stg_id(l_err_idx) := compl_rec.stg_id;
l_err_msg(l_err_idx) := l_val_message;
dbms_output.put_line(' Error in Validation: ' || l_val_message);
l_error_count := l_error_count + 1;
END IF;
BEGIN
IF p_status = 'Success' THEN
-- Step 7: Get interface_id
SELECT apps.wip_job_schedule_interface_s.NEXTVAL
INTO l_interface_id
FROM DUAL;
l_wip_compl_rec := NULL;
-- Step * : Insert update record
l_wip_compl_rec.interface_id := l_interface_id;
l_wip_compl_rec.group_id := l_group_id;
l_wip_compl_rec.organization_id := l_org_id;
l_wip_compl_rec.job_name := compl_rec.job_name;
l_wip_compl_rec.wip_entity_id := l_wip_entity_id;
l_wip_compl_rec.load_type := 3; -- Update Existing Job
l_wip_compl_rec.process_phase := 2; -- Validation
l_wip_compl_rec.process_status := 1; -- Pending
l_wip_compl_rec.allow_explosion := 'N';
l_wip_compl_rec.scheduling_method := 1; -- Manual
l_wip_compl_rec.status_type := l_job_status_flag;
-- l_wip_compl_rec.last_unit_start_date := SYSDATE;
-- l_wip_compl_rec.last_unit_completion_date := SYSDATE + 1;
l_wip_compl_rec.creation_date := SYSDATE;
l_wip_compl_rec.created_by := l_user_id;
l_wip_compl_rec.last_update_date := SYSDATE;
l_wip_compl_rec.last_updated_by := l_user_id;
INSERT INTO apps.wip_job_schedule_interface VALUES l_wip_compl_rec;
UPDATE .xx_wip_compl_close_stg
SET process_flag = 'S',
process_msg = 'VALIDATED',
last_update_date = SYSDATE
WHERE stg_id = compl_rec.stg_id;
l_processed_count := l_processed_count + 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_message := p_message || ' Insert error: ' || SQLERRM;
END;
END LOOP;
------------------------------------------------------
-- Step 14 : Updating Error
------------------------------------------------------
IF l_error_count > 0 THEN
ROLLBACK TO batch_start;
-- First mark entire batch as failed
UPDATE .xx_wip_compl_close_stg
SET process_flag = 'E',
process_msg = 'ERROR',
error_message = 'Batch failed because one or more records in the batch have validation errors'
WHERE batch_id = l_batch_id;
FOR i IN 1 .. l_err_idx LOOP
UPDATE .xx_wip_compl_close_stg
SET process_flag = 'E',
process_msg = 'ERROR',
error_message = l_err_msg(i)
WHERE stg_id = l_err_stg_id(i);
END LOOP;
p_status := 'Error';
p_message := 'Batch validation failed. Check staging table for Error details.';
COMMIT;
RETURN;
END IF;
IF l_processed_count = 0 THEN
p_message := 'No Staging records were Processed to interface';
p_status := 'Error';
RETURN;
ELSE
dbms_output.put_line(l_processed_count || ' Staging records Proccessed to Interface');
END IF;
--------------------------------------
-- Step 15 : Submit WIP Mass Load
--------------------------------------
BEGIN
l_request_id := fnd_request.submit_request(
application => 'WIP',
program => 'WICMLP',
description => 'WIP Mass Load ' || 'CLOSE' || ' Re-STR',
start_time => NULL,
sub_request => FALSE,
argument1 => l_group_id,
argument2 => 1
);
COMMIT;
IF l_request_id IS NULL THEN
p_message := 'Concurrent request submission failed';
RETURN;
END IF;
LOOP
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
);
EXIT WHEN l_dev_phase = 'COMPLETE';
IF (SYSDATE - l_start_time) * 86400 > l_timeout_seconds THEN
RAISE_APPLICATION_ERROR(-20001, 'Timeout: ' || l_request_id);
END IF;
DBMS_LOCK.sleep(l_sleep_seconds);
END LOOP;
IF UPPER(l_dev_status) = 'NORMAL' THEN
p_status := 'Success';
p_message := l_processed_count || ' Jobs Processed Successfully';
UPDATE .xx_wip_compl_close_stg
SET request_id = l_request_id,
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE batch_id = l_batch_id;
ELSE
p_status := 'Error';
p_message := 'Concurrent request failed.';
FOR rec IN (
SELECT error
FROM wip.wip_interface_errors
WHERE interface_id = l_interface_id
) LOOP
p_message := rec.error;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_message := 'Submit error: ' || SQLERRM;
RAISE;
END;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
p_message := 'Unexpected error: ' || SQLERRM;
ROLLBACK;
END restr_wip_close_job;
Recent Posts