Automating Oracle EBS WIP Move Transactions with PL/SQL
Moving a discrete job through its routing — queue, scrap, hold, or across plants — is another WIP activity that’s fine to do by hand occasionally but becomes a bottleneck at volume. The move_job_operation procedure automates it end to end: validating the job, staging the move through the WIP open interface, running the two concurrent programs that process it, and — for plant-to-plant moves — even creating the destination job automatically.
Purpose
Given a job, a from/to operation sequence, a quantity, and a move type, the procedure moves that job’s quantity through the routing and reports back success or a specific error. For plant-to-plant transfers, it also creates the child job at the receiving organization when one doesn’t already exist.
Inputs
It takes a broader parameter set than a simple RM issue, since a move transaction carries more context: p_org_code, p_job_name, p_fr_operation_seq_num, p_to_operation_seq_num, p_move_qty, p_move_date, p_trxn_type, p_operation_step (QUEUE / SCRAP / HOLD), p_operation_type (INHOUSE / OSP / PLANTTOPLANT), p_intermediate_item, p_final_flag, p_reject_reason_id, p_resources, p_to_org_code (for plant-to-plant), and p_message (OUT).
How It Works
- Validate organization and job, following the same pattern as the RM issue procedure — each lookup wrapped in its own exception handler with a message pointing at exactly what failed.
- Resolve the primary UOM for the job’s assembly item from mtl_system_items_b.
- Determine the move logic from p_trxn_type / p_operation_step:
- MOVE + QUEUE → a standard move to the next operation.
- MOVE + SCRAP → scraps the quantity, and resolves a scrap GL account based on organization code.
- MOVE + HOLD → holds the job at its current operation without progressing it.
- Anything else → returns a validation error rather than guessing at intent.
- Insert into wip_move_txn_interface with the resolved from/to operation sequence, quantity, transaction type, UOM, and (where applicable) scrap account and reject reason.
- Submit and wait for two concurrent programs in sequence:
- WICTMS (WIP Move Transactions Import) — validates and stages the interface row.
- WICTWS (WIP Move Transactions Worker) — actually processes the move. Each is submitted via fnd_request.submit_request and polled with fnd_concurrent.wait_for_request until it reaches COMPLETE, mirroring the completion-tracking approach used for RM issue but doubled up since a move genuinely goes through two processing stages in EBS.
- Error reporting reads from the WIP transaction error table keyed on the move transaction ID, returning either ‘SUCCESS’ or the specific error text.
Why It’s Built This Way
- Two-stage concurrent processing reflects how WIP move transactions actually flow through EBS — import validates and stages, worker commits — so the procedure has two separate submit-and-wait loops rather than treating it as one step.
- OSP resource-assignment logic exists but is commented out — the scaffolding for adding a resource (department, resource ID, sequence) to an operation is present in the code, suggesting it’s a planned or partially-rolled-back extension rather than something never considered.
- Scrap account resolution by hardcoded org code is the one part that needs attention as new organizations come online — it currently only maps two org codes explicitly and returns an error for anything else.
Sample Code:
PROCEDURE move_job_operation (
p_org_code IN VARCHAR2,
p_job_name IN VARCHAR2,
p_fr_operation_seq_num IN NUMBER,
p_to_operation_seq_num IN NUMBER,
p_move_qty IN NUMBER,
p_move_date IN DATE DEFAULT SYSDATE,
p_trxn_type IN VARCHAR2,
p_operation_step IN VARCHAR2,
p_operation_type IN VARCHAR2,
p_intermediate_item IN VARCHAR2,
p_final_flag VARCHAR2,
p_reject_reason_id NUMBER,
p_resources IN VARCHAR2,
p_to_org_code IN VARCHAR2,
p_message OUT VARCHAR2
)
IS
l_org_id NUMBER;
l_wip_entity_id NUMBER;
l_move_txn_id NUMBER;
— := wip.wip_move_txn_interface_s.NEXTVAL;
l_routing_step_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_request_id NUMBER;
l_transaction_type NUMBER;
l_transaction_uom VARCHAR2 (30);
l_from_step_type NUMBER;
l_to_step_type NUMBER;
l_assembly_item_id NUMBER;
l_trxn_type_number NUMBER;
l_fm_operation_seq_num NUMBER;
l_to_operation_seq_num NUMBER;
l_scrap_account_id 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; — Check every 5 seconds
l_timeout_seconds NUMBER := 300;
— Timeout after 5 minutes (optional)
l_start_time DATE := SYSDATE;
l_success BOOLEAN;
l_request_id_wrk NUMBER;
— l_wip_entity_id NUMBER;
— l_org_id NUMBER;
— l_operation_seq_num NUMBER := 50; — 5th operation
l_department_id NUMBER;
l_resource_id NUMBER;
l_resource_seq_num NUMBER;
— l_user_id NUMBER := fnd_global.user_id;
l_login_id NUMBER := fnd_global.login_id;
l_group_id NUMBER := TO_CHAR (SYSDATE, ‘ddhhmmss’);
l_attribute1 VARCHAR2 (200);
l_attribute3 VARCHAR2 (200);
l_attribute2 VARCHAR2 (200);
l_attribute4 VARCHAR2 (200);
l_parent_job VARCHAR2 (200);
l_child_job VARCHAR2 (200);
l_child_job_count NUMBER;
l_to_org_id NUMBER;
l_operation_seq_num NUMBER;
l_complete_flag VARCHAR2 (100);
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Param p_org_code ‘ || p_org_code ||’ p_job_name ‘ ||
p_job_name ||’ p_fr_operation_seq_num ‘ ||
p_fr_operation_seq_num ||’ p_to_operation_seq_num ‘ ||
p_to_operation_seq_num ||’ p_move_qty ‘ ||
p_move_qty ||’ p_move_date ‘ ||
p_move_date ||’ ‘ ||
p_trxn_type ||’ ‘ ||
p_operation_step ||’ ‘ ||
p_operation_type ||’ ‘ ||
p_intermediate_item ||’ ‘ ||
p_final_flag ||’ ‘ ||
p_reject_reason_id ||’ ‘ ||
p_resources ||’ ‘ ||
p_to_org_code );
— IF NVL (p_operation_type, ‘N’) <> ‘PLANTTOPLANT’
— AND NVL (p_final_flag, ‘N’) <> ‘Y’
— THEN
— 1. Get Organization ID
— p_message := ‘test’;
— return;
BEGIN
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 Found organization=’ || p_org_code;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Organization Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
— 2. Get WIP Entity ID
BEGIN
SELECT wip_entity_id, primary_item_id
INTO l_wip_entity_id, l_assembly_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 := ‘Job Name Not Found p_job_name=’ || p_job_name;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Job Name Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
EXCEPTION
WHEN OTHERS
THEN
p_message :=
‘Job or organization Validation error.’ || SQLCODE || SQLERRM;
RETURN;
END;
— 2. Get primary_uom_code from MTL_SYSTEM_ITEMS_B
BEGIN
SELECT primary_uom_code
INTO l_transaction_uom
FROM mtl_system_items_b
WHERE inventory_item_id = l_assembly_item_id
AND organization_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_message :=
‘Item Not Found l_assembly_item_id=’ || l_assembly_item_id;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Item Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
/*
p_operation_type = ‘INHOUSE’
p_operation_type = ‘OSP’
p_operation_type = ‘PLANTTOPLANT’
*/ — 3. Insert Move Transaction
— IF p_operation_type = ‘INHOUSE’– THEN
IF p_operation_type = ‘OSP’
THEN
null; /*
BEGIN
BEGIN
SELECT department_id
INTO l_department_id
FROM wip_operations
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 :=
‘Department Not Found l_wip_entity_id=’
|| l_wip_entity_id
|| ‘ p_to_operation_seq_num=’
|| p_to_operation_seq_num;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Department Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
— 3. Get resource ID for your existing resource
BEGIN
SELECT resource_id
INTO l_resource_id
FROM bom_resources
WHERE resource_code = p_resources
AND organization_id = l_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_message :=
‘Resources Not Found resources=’ || p_resources;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Resources Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
— existing resource code you want to add
— 4. Find next available resource_seq_num
SELECT NVL (MAX (resource_seq_num), 0) + 10
INTO l_resource_seq_num
FROM wip_operation_resources
WHERE wip_entity_id = l_wip_entity_id
AND organization_id = l_org_id
AND operation_seq_num = p_to_operation_seq_num;
END;
*/
END IF;
BEGIN
— trxn types
— MOVE
— Complete
— FM_INTRAOPERATION_STEP_TYPE
— ‘TO MOVE’ Move to next operation id=1
–IF p_operation_type IN (‘INHOUSE’, ‘OSP’)
–THEN
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 := ‘Resources Not Found resources=’ || p_resources;
RETURN;
WHEN OTHERS
THEN
p_message := ‘Resources Issue Contact IT Team=’ || SQLERRM;
RETURN;
END;
— existing resource code you want to add
— 4. Find next available resource_seq_num
IF p_trxn_type = ‘MOVE’
AND p_operation_step = ‘QUEUE’ — APPROVE in application
THEN
l_trxn_type_number := 1; — QUEUE
l_fm_operation_seq_num := 1;
l_to_operation_seq_num := 1;
ELSIF p_trxn_type = ‘MOVE’
AND p_operation_step = ‘SCRAP’ — Reject application
THEN
l_trxn_type_number := 1; — SCRAP
l_fm_operation_seq_num := 1;
l_to_operation_seq_num := 5;
— SCRAP does not go to another operation
— Scrap account needed
IF p_org_code = ‘009’
THEN
SELECT code_combination_id
INTO l_scrap_account_id
FROM apps.gl_code_combinations_kfv
WHERE concatenated_segments = ‘DA-09-00-1334-0000-000-000-000’;
ELSIF p_org_code = ‘019’
THEN
SELECT code_combination_id
INTO l_scrap_account_id
FROM apps.gl_code_combinations_kfv
WHERE concatenated_segments = ‘DA-07-00-1334-0000-000-000-000’;
ELSE
p_message := ‘ORG code not correct for scrap’;
RETURN;
END IF;
–l_scrap_account_id := 5399;
ELSIF p_trxn_type = ‘MOVE’ AND p_operation_step = ‘HOLD’
— Hold application but not process just keep in queue
THEN
l_trxn_type_number := 1; — HOLD
l_fm_operation_seq_num := 1;
l_to_operation_seq_num := 4;
— Reject reason need
— NULL for REJECT to prior op, if not specified
ELSE
p_message :=
‘Not a valid P_Trxn_type = ‘
|| p_trxn_type
|| ‘p_operation_step:= ‘
|| p_operation_step;
RETURN;
END IF;
IF l_operation_seq_num = p_fr_operation_seq_num AND p_operation_step = ‘QUEUE’
— AND p_fr_operation_seq_num = l_operation_seq_num
THEN
l_to_operation_seq_num := 3;
l_trxn_type_number := 1;
END IF;
–END IF;
/* IF p_operation_type = ‘OSP’
THEN
BEGIN
— 1. Get organization and job info
INSERT INTO apps.wip_job_schedule_interface
(last_update_date, last_updated_by, creation_date,
created_by, GROUP_ID, organization_id, load_type,
wip_entity_id, process_phase, process_status,
header_id, allow_explosion,
— net_quantity, start_quantity,
class_code, status_type
)
VALUES (SYSDATE, — LAST UPDATE DATE
l_user_id, — LAST UPDATED BY
SYSDATE, — CREATION DATE
l_user_id, — CREATED BY
–1318, — LAST UPDATE LOGIN
l_group_id, — GROUP ID
l_org_id, — ORG ID
3,
–3. update standard or non standard job
l_wip_entity_id, — WIP ENTITY ID
2,
–2. validation, 3. EXPLOSION, 4. COMPLETION, 5. CREATION
1,
–1. pending 2. running, 3. error 4. complete 5. warning 1318
l_group_id –HEADER ID
, ‘N’ –ALLOW EXPLOSION
,
‘Standard’ –CLASS CODE
, 3 –STATUS_TYPE
);
INSERT INTO apps.wip_job_dtls_interface
(GROUP_ID, organization_id,
operation_seq_num,
resource_seq_num,
resource_id_new, usage_rate_or_amount,
assigned_units, basis_type,
autocharge_type, standard_rate_flag,
load_type, substitution_type,
process_phase, process_status,
last_update_date,
last_updated_by, creation_date,
created_by, parent_header_id
)
VALUES (l_group_id — group id
, l_org_id — org id
,
p_to_operation_seq_num –operation sequnece number
,
l_resource_seq_num –RESOURCE_SEQ_NUM
,
l_resource_id –resource id new(CT)
, 1 — usage rate
,
1 –ASSIGNED_UNITS
, 1 –BASIS_TYPE
,
1 –AUTOCHARGE_TYPE
, 1 –STANDARD_RATE_FLAG
,
1 –load_type
, 2 –substitution_type
,
2 –process_phase
, 1 — process_status
,
SYSDATE –last update date
,
l_user_id –last updated by
, SYSDATE — creation date
,
l_user_id –created by
, l_group_id –parent header id
);
DBMS_OUTPUT.put_line
( ‘Resource added successfully to operation ‘
|| p_to_operation_seq_num
);
— 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 IN (
SELECT interface_id
FROM apps.wip_job_dtls_interface
WHERE GROUP_ID = l_group_id))
LOOP
DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TYPE);
p_message := rec.MESSAGE_TEXT;
END LOOP;
— Delete interface record
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;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (‘Error: ‘ || SQLERRM);
END;
END IF;
*/
DBMS_OUTPUT.put_line (‘Insert: ‘ || l_request_id);
DBMS_OUTPUT.put_line ( ‘l_trxn_type_number: ‘
|| l_trxn_type_number
|| ‘ l_fm_operation_seq_num: ‘
|| l_fm_operation_seq_num
|| ‘ l_to_operation_seq_num: ‘
|| l_to_operation_seq_num
);
SELECT apps.wip_transactions_s.NEXTVAL
INTO l_move_txn_id
FROM DUAL;
INSERT INTO wip_move_txn_interface
(transaction_id, wip_entity_id, organization_id,
fm_operation_seq_num, to_operation_seq_num,
transaction_quantity, transaction_type, process_phase,
process_status, transaction_date, creation_date,
created_by, last_update_date, last_updated_by,
transaction_uom, fm_intraoperation_step_type,
to_intraoperation_step_type, scrap_account_id,
reason_id, GROUP_ID
)
VALUES (l_move_txn_id, l_wip_entity_id, l_org_id,
p_fr_operation_seq_num, p_to_operation_seq_num,
p_move_qty, l_trxn_type_number, — 2 = Move
1, — 2 = Validation
1, — 1 = Pending
p_move_date, SYSDATE,
l_user_id, SYSDATE, l_user_id,
l_transaction_uom, l_fm_operation_seq_num,
l_to_operation_seq_num, l_scrap_account_id,
p_reject_reason_id, l_move_txn_id
);
/*
IF l_operation_seq_num = p_to_operation_seq_num
— AND p_fr_operation_seq_num = l_operation_seq_num
THEN
l_to_operation_seq_num := 3;
SELECT apps.wip_transactions_s.NEXTVAL
INTO l_move_txn_id
FROM DUAL;
INSERT INTO wip_move_txn_interface
(transaction_id, wip_entity_id, organization_id,
fm_operation_seq_num, to_operation_seq_num,
transaction_quantity, transaction_type,
process_phase, process_status, transaction_date,
creation_date, created_by, last_update_date,
last_updated_by, transaction_uom,
fm_intraoperation_step_type,
to_intraoperation_step_type, scrap_account_id,
reason_id, GROUP_ID
)
VALUES (l_move_txn_id, l_wip_entity_id, l_org_id,
p_fr_operation_seq_num, p_to_operation_seq_num,
p_move_qty, l_trxn_type_number, — 2 = Move
1, — 2 = Validation
1, — 1 = Pending
p_move_date,
SYSDATE, l_user_id, SYSDATE,
l_user_id, l_transaction_uom,
l_fm_operation_seq_num,
l_to_operation_seq_num, l_scrap_account_id,
p_reject_reason_id, l_move_txn_id
);
END IF;
*/
EXCEPTION
WHEN OTHERS
THEN
p_message :=
‘Insertion Issue Contact IT Team.’ || SQLCODE || SQLERRM;
DBMS_OUTPUT.put_line (‘Insert: Error ‘ || SQLCODE || SQLERRM);
ROLLBACK;
RETURN;
END;
— Get values for a specific responsibility
BEGIN
— Initialize EBS application context
apps.fnd_global.apps_initialize (user_id => l_user_id,
resp_id => l_resp_id,
resp_appl_id => l_appl_id
);
— Submit the Move Transactions concurrent program
–program name –WIP Move Transaction Manager— WICTMS
l_request_id :=
fnd_request.submit_request
(application => ‘WIP’,
program => ‘WICTMS’,
description => ‘WIP Move Transactions Import’,
start_time => NULL,
sub_request => FALSE
);
COMMIT;
DBMS_OUTPUT.put_line (‘Request ID: ‘ || l_request_id);
END;
p_message :=
‘Move transaction inserted for job ‘
|| p_job_name
|| ‘ at operation ‘
|| p_to_operation_seq_num;
— 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;
l_request_id_wrk :=
fnd_request.submit_request
(application => ‘WIP’,
program => ‘WICTWS’,
description => ‘WIP Move Transactions Worker’,
start_time => NULL,
sub_request => FALSE,
argument1 => l_move_txn_id, — Group ID
argument2 => 1, — Org ID
argument3 => 0, — Commit flag
argument4 => 1
); — Debug flag
COMMIT;
l_phase := ”;
l_status := ”;
l_dev_phase := ”;
l_dev_status := ”;
l_message := ”;
LOOP
— Check status
l_success :=
apps.fnd_concurrent.wait_for_request
(request_id => l_request_id_wrk,
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_message AS MESSAGE_TEXT
FROM apps.wip_txn_interface_errors
WHERE transaction_id = l_move_txn_id)
LOOP
DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TEXT);
p_message := rec.MESSAGE_TEXT;
END LOOP;
IF p_message = ” OR p_message IS NULL
THEN
p_message := ‘SUCCESS’;
— RETURN;
END IF;
DBMS_LOCK.sleep (l_sleep_seconds);
ELSE
DBMS_OUTPUT.put_line (‘Interface ID l_move_txn_id: ‘ || l_move_txn_id
);
FOR rec IN (SELECT error_message AS MESSAGE_TEXT
FROM apps.wip_txn_interface_errors
WHERE transaction_id = l_move_txn_id)
LOOP
DBMS_OUTPUT.put_line (‘Error Type: ‘ || rec.MESSAGE_TEXT);
p_message := rec.MESSAGE_TEXT;
END LOOP;
ROLLBACK; — Commit deletion
DBMS_OUTPUT.put_line ( ‘Request Failed or Warning. Message: ‘
|| l_message
);
RETURN;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
p_message := ‘Job or organization not found.’ || SQLCODE || SQLERRM;
ROLLBACK;
WHEN OTHERS
THEN
p_message := ‘Error: ‘ || SQLERRM;
ROLLBACK;
END move_job_operation;
