Order Management Package

CREATE OR REPLACE PACKAGE APPS.cux_wip_pkg IS

/**************************************************************************
File name : cux_wip_pkg.pck
Doc Ref(s) :
Project : Emerson etrace
Description : process WIP transactions

Change History Information
--------------------------
Version Date Author Change Reference / Description
------- ----------- --------------- ------------------------------------
1.0 2009-07-29 Randolph First Version

**************************************************************************/
g_created_by NUMBER := fnd_global.user_id;
g_last_updated_by NUMBER := fnd_global.user_id;

TYPE cur_reference IS REF CURSOR;
TYPE t_line_qty IS RECORD(
line_id NUMBER,
temp_id NUMBER,
quantity NUMBER,
lot_ctrl NUMBER,
ser_ctrl NUMBER,
mt_quantity NUMBER);

TYPE t_line_id IS TABLE OF t_line_qty INDEX BY BINARY_INTEGER;
--initialize global
PROCEDURE initialize
(
p_user_id IN NUMBER,
p_resp_id IN NUMBER,
p_appl_id IN NUMBER
);
--Get item reversion
--get locator segments
FUNCTION get_locator
(
p_locator_id IN NUMBER,
p_org_id IN NUMBER
) RETURN VARCHAR2;
FUNCTION get_item_last_rev
(
p_item_id IN NUMBER,
p_org_id IN NUMBER
) RETURN VARCHAR2;
--get released DJ
PROCEDURE get_release_dj
(
p_discreate_job IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor IN OUT cur_reference
);
--get DJ lines
PROCEDURE get_release_lines
(
p_discreate_job IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor IN OUT cur_reference
);
--get item onhand quantity
FUNCTION get_item_qoh
(
p_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_location_id NUMBER DEFAULT NULL,
p_item_rev VARCHAR2 DEFAULT NULL,
p_lot_number VARCHAR2 DEFAULT NULL
) RETURN NUMBER;
--get item available quantity
FUNCTION get_item_att_qty
(
p_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_location_id NUMBER DEFAULT NULL,
p_item_rev VARCHAR2 DEFAULT NULL,
p_lot_number VARCHAR2 DEFAULT NULL
) RETURN NUMBER;
FUNCTION get_supply_type
(
p_wip_entity_id IN NUMBER,
p_wip_line IN NUMBER,
p_item_id IN NUMBER
) RETURN VARCHAR2;
--Process WIP Issue transaction
PROCEDURE process_wip_issue
(
p_dj_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_subinventory IN VARCHAR2,
p_locator IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_issue_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
);
--Porcess wip component return
PROCEDURE process_cmpnt_return
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_subinventory IN VARCHAR2,
p_locator IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_return_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
p_ref IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--Process WIP completed transaction
PROCEDURE process_wip_complete
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_complete_qty IN NUMBER,
p_uom IN VARCHAR2,
p_rev IN VARCHAR2,
x_subinventory IN OUT VARCHAR2,
x_locator IN OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
);
--wip complete,for osp
PROCEDURE wip_osp_complete
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_complete_qty IN NUMBER,
p_uom IN VARCHAR2,
p_rev IN VARCHAR2,
p_fm_seq_num IN NUMBER,
x_subinventory IN OUT VARCHAR2,
x_locator IN OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
);
-- using move transaction return
PROCEDURE process_wip_return
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_return_qty IN NUMBER,
p_uom IN VARCHAR2,
o_subinventory OUT VARCHAR2,
o_locator OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
);
--Get Move Order lines: 1:Component Pick Released 2:Manual Move Order
PROCEDURE get_morders
(
p_org_code IN VARCHAR2,
p_dj_name IN VARCHAR2,
o_cursor OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--Get Move Order lines
PROCEDURE get_morder_lines
(
p_org_code IN VARCHAR2,
p_move_order IN VARCHAR2,
o_cursor OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--process move order transact
PROCEDURE process_move_order
(
p_org_code IN VARCHAR2,
p_move_order IN VARCHAR2,
p_item_num IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_quantity IN NUMBER,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--process componet return
--Step 1 : Misc issue via PN
--Step 2 : Misc receipt via PN,LotNum
PROCEDURE misc_issue_rcpt
(
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_lot_num IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_source_sub IN VARCHAR2,
p_source_loc IN VARCHAR2,
p_dest_sub IN VARCHAR2,
p_dest_loc IN VARCHAR2,
p_exp_date IN DATE,
p_reason IN VARCHAR2,
p_reference IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--PN transfer
--Setp 1:Misc issue ; Step 2:Misc receipt
PROCEDURE pn_transfer
(
p_org_code IN VARCHAR2,
p_item_num_a IN VARCHAR2,
p_item_rev_a IN VARCHAR2 DEFAULT NULL,
p_lot_num_a IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_sub_a IN VARCHAR2,
p_loc_a IN VARCHAR2,
p_item_num_b IN VARCHAR2,
p_item_rev_b IN VARCHAR2 DEFAULT NULL,
--p_lot_num_b IN VARCHAR2,
p_sub_b IN VARCHAR2,
p_loc_b IN VARCHAR2,
p_exp_date IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
-- Get item description
PROCEDURE get_item_desc
(
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
o_rev OUT VARCHAR2,
o_desc OUT VARCHAR2
);
--Validate sub and locator
PROCEDURE valid_subinvloc
(
p_org_code IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
o_flag OUT VARCHAR2,
o_msg OUT VARCHAR2
);
--process inter-org transfer transaction
PROCEDURE org_transfer
(
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_lotnum IN VARCHAR2,
p_reason IN VARCHAR2,
p_org_code_fm IN VARCHAR2,
p_subinv_fm IN VARCHAR2,
p_locator_fm IN VARCHAR2,
p_org_code_to IN VARCHAR2,
p_subinv_to IN VARCHAR2,
p_locator_to IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
--get item informaton
PROCEDURE get_item_master
(
p_item_num IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor OUT cur_reference
);
PROCEDURE validate_revision
(
p_item_num IN VARCHAR2,
p_org_code IN VARCHAR2,
p_item_rev IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
);
END cux_wip_pkg;
/


CREATE OR REPLACE PACKAGE BODY APPS.cux_wip_pkg IS
g_mmt_iface mtl_transactions_interface%ROWTYPE;
g_wmti_iface wip_move_txn_interface%ROWTYPE;

g_user_name VARCHAR2(30) := fnd_global.user_name;
g_completion_subinventory VARCHAR2(30);
g_completion_locator VARCHAR2(120);
g_misc_refference VARCHAR2(240);
g_misc_reason_id NUMBER;
g_move_order_type NUMBER;

--initialize global
PROCEDURE initialize
(
p_user_id IN NUMBER,
p_resp_id IN NUMBER,
p_appl_id IN NUMBER
) IS
BEGIN
fnd_global.apps_initialize(user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_appl_id);

END;
--get locator segments
FUNCTION get_locator
(
p_locator_id IN NUMBER,
p_org_id IN NUMBER
) RETURN VARCHAR2 IS
l_return VARCHAR2(60);
BEGIN
SELECT concatenated_segments
INTO l_return
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_locator_id
AND organization_id = p_org_id;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_locator;
--get organization_id
FUNCTION get_organization_id(p_org_code IN VARCHAR2) RETURN NUMBER IS
l_organization_id NUMBER;
BEGIN
SELECT organization_id
INTO l_organization_id
FROM org_organization_definitions
WHERE organization_code = p_org_code;
RETURN l_organization_id;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END get_organization_id;
--get item_id
FUNCTION get_item_id
(
p_item_num IN VARCHAR2,
p_org_id IN NUMBER
) RETURN NUMBER IS
l_return NUMBER;
BEGIN
SELECT inventory_item_id
INTO l_return
FROM mtl_system_items_kfv
WHERE concatenated_segments = p_item_num
AND organization_id = p_org_id;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END get_item_id;
--get wip entity id
FUNCTION get_wip_entity_id
(
p_org_id IN NUMBER,
p_dj_name IN VARCHAR2
) RETURN NUMBER IS
l_return NUMBER;
BEGIN
SELECT wip_entity_id
INTO l_return
FROM wip_entities
WHERE wip_entity_name = p_dj_name
AND organization_id = p_org_id;
RETURN l_return;

EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END get_wip_entity_id;

--get locator id
FUNCTION get_locator_id
(
p_locator IN VARCHAR2,
p_subinv IN VARCHAR2,
p_org_id IN NUMBER
) RETURN NUMBER IS
l_return NUMBER;
BEGIN
SELECT inventory_location_id
INTO l_return
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_locator
AND enabled_flag = 'Y'
AND subinventory_code = p_subinv
AND organization_id = p_org_id;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN - 1;
END;

--Get current item rev
FUNCTION get_item_last_rev
(
p_item_id IN NUMBER,
p_org_id IN NUMBER
) RETURN VARCHAR2 IS
l_return VARCHAR2(30);
BEGIN
SELECT revision
INTO l_return
FROM mtl_item_revisions_vl mv
WHERE mv.inventory_item_id = p_item_id
AND mv.organization_id = p_org_id
AND mv.effectivity_date =
(SELECT MAX(effectivity_date)
FROM mtl_item_revisions_vl
WHERE inventory_item_id = mv.inventory_item_id
AND organization_id = mv.organization_id);
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_item_last_rev;
--Get released discreate job
PROCEDURE get_release_dj
(
p_discreate_job IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor IN OUT cur_reference
) IS
BEGIN
OPEN o_cursor FOR
SELECT msi.concatenated_segments product_number,
wdj.bom_revision revision,
wro.quantity_waiting_to_move qty_to_move,
msi.description item_desc,
msi.primary_uom_code uom_code,
wdj.completion_subinventory,
wdj.start_quantity,
wdj.start_quantity - wdj.quantity_completed -
wdj.quantity_scrapped open_quantity,
get_locator(wdj.completion_locator_id,
wdj.organization_id) completion_locator,
wdj.attribute2 production_line
FROM wip_discrete_jobs wdj,
wip_entities we,
mtl_system_items_kfv msi,
org_organization_definitions o,
wip_operations_v wro
WHERE we.wip_entity_id = wdj.wip_entity_id
AND msi.inventory_item_id = wdj.primary_item_id
AND msi.organization_id = wdj.organization_id
AND wdj.organization_id = o.organization_id
AND wro.organization_id(+) = wdj.organization_id
AND wro.wip_entity_id(+) = wdj.wip_entity_id
AND wro.department_code(+) = 'OQA'
AND wdj.status_type = '3' --released
AND wdj.start_quantity > 0
AND o.organization_code = p_org_code
AND we.wip_entity_name =
nvl(p_discreate_job,
we.wip_entity_name);
EXCEPTION
WHEN OTHERS THEN
OPEN o_cursor FOR
SELECT 'Unhandled exception' FROM dual;
END get_release_dj;
--get dj componet
PROCEDURE get_release_lines
(
p_discreate_job IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor IN OUT cur_reference
) IS
BEGIN
OPEN o_cursor FOR
SELECT msi.concatenated_segments component,
wdj.bom_revision revision,
msi.description component_desc,
get_item_last_rev(wro.inventory_item_id,
wro.organization_id) component_rev,
wro.required_quantity,
wro.quantity_issued,
wro.quantity_per_assembly,
msi.primary_uom_code uom_code,
ml.meaning sypply_type,
wro.supply_subinventory,
get_locator(wro.supply_locator_id,
wro.organization_id) supply_loc,
get_item_qoh(wro.inventory_item_id,
wro.organization_id,
NULL) qty_onhand,
wro.attribute2 mpn,
wro.attribute3 Manufacturer
FROM wip_discrete_jobs wdj,
wip_entities we,
mtl_system_items_kfv msi,
wip_requirement_operations wro,
mfg_lookups ml,
org_organization_definitions o
WHERE we.wip_entity_id = wdj.wip_entity_id
AND wdj.wip_entity_id = wro.wip_entity_id
AND msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND wdj.organization_id = o.organization_id
AND ml.lookup_type = 'WIP_SUPPLY'
AND ml.lookup_code = wro.wip_supply_type
AND wdj.status_type = '3' --released
AND wro.wip_supply_type != 6
AND o.organization_code = p_org_code
AND we.wip_entity_name = p_discreate_job;
EXCEPTION
WHEN OTHERS THEN
OPEN o_cursor FOR
SELECT 'Unhandled exception' FROM dual;

END get_release_lines;
--get item onhand quantity
FUNCTION get_item_qoh
(
p_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_location_id NUMBER DEFAULT NULL,
p_item_rev VARCHAR2 DEFAULT NULL,
p_lot_number VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
l_qoh NUMBER;
l_rqoh NUMBER;
l_qr NUMBER;
l_qs NUMBER;
l_att NUMBER;
l_atr NUMBER;
l_tree_mode NUMBER;
l_msg_count VARCHAR2(100);
l_msg_data VARCHAR2(1000);
l_return_status VARCHAR2(1);
l_rev_control BOOLEAN;
l_lot_control BOOLEAN;
BEGIN
-- Transact mode
l_tree_mode := 2;
inv_quantity_tree_pub.clear_quantity_cache;

IF p_item_rev IS NOT NULL THEN
l_rev_control := TRUE;
ELSE
l_rev_control := FALSE;
END IF;

IF p_lot_number IS NOT NULL THEN
l_lot_control := TRUE;
ELSE
l_lot_control := FALSE;
END IF;

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_item_id,
p_tree_mode => l_tree_mode,
p_is_revision_control => l_rev_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_revision => p_item_rev,
p_lot_number => p_lot_number,
p_lot_expiration_date => NULL,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_location_id,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att, --¿¿¿
x_atr => l_atr); --¿¿¿¿
RETURN l_qoh;
END get_item_qoh;
FUNCTION get_item_att_qty
(
p_item_id NUMBER,
p_organization_id NUMBER,
p_subinventory_code VARCHAR2,
p_location_id NUMBER DEFAULT NULL,
p_item_rev VARCHAR2 DEFAULT NULL,
p_lot_number VARCHAR2 DEFAULT NULL
) RETURN NUMBER IS
l_qoh NUMBER;
l_rqoh NUMBER;
l_qr NUMBER;
l_qs NUMBER;
l_att NUMBER;
l_atr NUMBER;
l_tree_mode NUMBER;
l_msg_count VARCHAR2(100);
l_msg_data VARCHAR2(1000);
l_return_status VARCHAR2(1);
l_rev_control BOOLEAN;
l_lot_control BOOLEAN;
--l
l_lot_ctrl NUMBER;
l_rev_ctrl NUMBER;
l_lot_number VARCHAR2(30);
l_item_rev VARCHAR2(10);
BEGIN
-- Transact mode
l_tree_mode := 2;
inv_quantity_tree_pub.clear_quantity_cache;

SELECT nvl(lot_control_code,
1),
nvl(revision_qty_control_code,
1)
INTO l_lot_ctrl,
l_rev_ctrl
FROM mtl_system_items_b
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;

IF l_lot_ctrl = 2 THEN
l_lot_control := TRUE;
l_lot_number := p_lot_number;
ELSE
l_lot_control := FALSE;
l_lot_number := NULL;
END IF;
IF l_rev_ctrl = 2 THEN
l_rev_control := TRUE;
l_item_rev := p_item_rev;
ELSE
l_rev_control := FALSE;
l_item_rev := NULL;
END IF;
--
IF l_lot_control THEN
IF l_lot_number IS NULL THEN
l_lot_control := FALSE;
END IF;
END IF;

IF l_rev_control THEN
IF l_item_rev IS NULL THEN
l_rev_control := NULL;
END IF;
END IF;

inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => p_organization_id,
p_inventory_item_id => p_item_id,
p_tree_mode => l_tree_mode,
p_is_revision_control => l_rev_control,
p_is_lot_control => l_lot_control,
p_is_serial_control => FALSE,
p_revision => l_item_rev,
p_lot_number => l_lot_number,
p_lot_expiration_date => NULL,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_location_id,
p_onhand_source => inv_quantity_tree_pvt.g_all_subs,
x_qoh => l_qoh,
x_rqoh => l_rqoh,
x_qr => l_qr,
x_qs => l_qs,
x_att => l_att, --¿¿¿
x_atr => l_atr); --¿¿¿¿
RETURN l_att;
END;
--
FUNCTION get_supply_type
(
p_wip_entity_id IN NUMBER,
p_wip_line IN NUMBER,
p_item_id IN NUMBER
) RETURN VARCHAR2 IS
l_return VARCHAR2(30);
BEGIN
SELECT ml.meaning sypply_type
INTO l_return
FROM wip_requirement_operations wro,
mfg_lookups ml
WHERE ml.lookup_type = 'WIP_SUPPLY'
AND ml.lookup_code = wro.wip_supply_type
AND wro.operation_seq_num = p_wip_line
AND wro.inventory_item_id = p_item_id
AND wro.wip_entity_id = p_wip_entity_id
AND rownum = 1;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_supply_type;
--Validate data from etrace
--p_type ISSUE : WIP Component Issue
-- RETURN: WIP Component Return
PROCEDURE validate_wip_txn
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_subinventory IN VARCHAR2,
p_locator IN VARCHAR2,
p_issue_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
p_type IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_subinventory VARCHAR2(30);
l_organization VARCHAR2(30);
l_locator VARCHAR2(30);
l_dj_name VARCHAR2(30);
l_item_num VARCHAR2(30);
l_issue_qty NUMBER;
l_item_rev VARCHAR2(30);
l_uom VARCHAR2(30);
l_reason VARCHAR2(30);
--
--Attribute field
l_exists NUMBER;
l_quantity_issued NUMBER;
l_requried_qty NUMBER;
l_available_qty NUMBER;
BEGIN
o_success_flag := 'Y';
l_subinventory := p_subinventory;
l_organization := p_organization_code;
l_locator := p_locator;
l_dj_name := p_dj_name;
l_item_num := p_item_num;
l_item_rev := p_item_rev;
l_uom := p_uom;
l_reason := p_reason;
l_issue_qty := p_issue_quantity;
--Validate oraganization
BEGIN
SELECT organization_id
INTO g_mmt_iface.organization_id
FROM org_organization_definitions o
WHERE organization_code = l_organization;

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Organization CODE:' ||
l_organization;
RETURN;
END;
--Validate dj_name
BEGIN
SELECT wip_entity_id
INTO g_mmt_iface.transaction_source_id
FROM wip_entities
WHERE wip_entity_name = l_dj_name
AND organization_id = g_mmt_iface.organization_id;

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid DJ name:' || l_dj_name;
RETURN;
END;
--Validate DJ status must be released
SELECT COUNT(1)
INTO l_exists
FROM wip_discrete_jobs
WHERE wip_entity_id = g_mmt_iface.transaction_source_id
AND organization_id = g_mmt_iface.organization_id
AND status_type = '3';
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'DJ status must be released.';
RETURN;
END IF;
--Validate Component

BEGIN
SELECT nvl(wro.quantity_issued,
0),
wro.required_quantity,
wro.inventory_item_id,
wro.operation_seq_num,
wro.department_id
INTO l_quantity_issued,
l_requried_qty,
g_mmt_iface.inventory_item_id,
g_mmt_iface.operation_seq_num,
g_mmt_iface.department_id
FROM wip_requirement_operations wro,
mtl_system_items_kfv msk
WHERE wip_entity_id = g_mmt_iface.transaction_source_id
AND msk.organization_id = wro.organization_id
AND msk.inventory_item_id = wro.inventory_item_id
AND msk.concatenated_segments = l_item_num;

EXCEPTION
WHEN no_data_found THEN

SELECT inventory_item_id
INTO g_mmt_iface.inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = l_item_num
AND organization_id = g_mmt_iface.organization_id;

l_requried_qty := 0;
l_quantity_issued := 0;

--get seq num and dept
SELECT operation_seq_num,
department_id
INTO g_mmt_iface.operation_seq_num,
g_mmt_iface.department_id
FROM wip_operations
WHERE wip_entity_id = g_mmt_iface.transaction_source_id
AND organization_id = g_mmt_iface.organization_id
AND operation_seq_num =
(SELECT MAX(operation_seq_num)
FROM wip_operations
WHERE wip_entity_id =
g_mmt_iface.transaction_source_id
AND organization_id = g_mmt_iface.organization_id);

WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Component:' || l_item_num;
RETURN;
END;
--Validate Componet version
IF l_item_rev IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_item_revisions_vl
WHERE revision = l_item_rev
AND organization_id = g_mmt_iface.organization_id;

IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Item Rev.:' || l_item_rev;
RETURN;
ELSE
g_mmt_iface.revision := l_item_rev;
END IF;
END IF;
--Validate subinventory
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_subinventory
AND organization_id = g_mmt_iface.organization_id;

IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Subinventory:' || l_subinventory;
RETURN;
ELSE
g_mmt_iface.subinventory_code := l_subinventory;
END IF;
--Validate locator
IF l_locator IS NOT NULL THEN
BEGIN
SELECT inventory_location_id
INTO g_mmt_iface.locator_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = l_locator
AND organization_id = g_mmt_iface.organization_id
AND subinventory_code = l_subinventory;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Locator:' || l_locator;
RETURN;
END;

END IF;
--Validate component whether has fix location control or not
IF p_type = 'RETURN' THEN

NULL;

END IF;
--Validate UOM
SELECT COUNT(1)
INTO l_exists
FROM mtl_units_of_measure_vl
WHERE uom_code = l_uom;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid UOM:' || l_uom;
RETURN;
ELSE
g_mmt_iface.transaction_uom := l_uom;
END IF;
--Validate Issue quantity
IF l_issue_qty <= 0 OR
l_issue_qty IS NULL THEN
o_success_flag := 'N';
IF p_type = 'ISSUE' THEN

o_error_mssg := 'Issue quantity cannot be 0:' ||
l_issue_qty;
ELSE
o_error_mssg := 'Return quantity cannot be 0:' ||
l_issue_qty;
END IF;
RETURN;
ELSE
IF p_type = 'ISSUE' THEN
IF l_issue_qty > (l_requried_qty - l_quantity_issued) AND
l_requried_qty <> 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Issue quantity must be less than open quantity:' ||
l_issue_qty;
RETURN;
ELSE
g_mmt_iface.transaction_quantity := l_issue_qty * -1;
END IF;
ELSE
--return
IF l_issue_qty > l_quantity_issued THEN
o_success_flag := 'N';
o_error_mssg := 'Return qty(' || l_issue_qty ||
') must be less than issue qty(' ||
l_quantity_issued || ')';
RETURN;
ELSE
g_mmt_iface.transaction_quantity := l_issue_qty;
END IF;
END IF;
END IF;
--validate wip supply type must be push
/*SELECT COUNT(1)
INTO l_exists
FROM mfg_lookups
WHERE lookup_type = 'WIP_SUPPLY'
AND meaning = 'Push'
AND lookup_code = l_wip_supply_type;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid supply type:' || l_wip_supply_type;
RETURN;
END IF;*/
--validate reason
IF l_reason IS NOT NULL THEN
BEGIN
SELECT r.reason_id
INTO g_mmt_iface.reason_id
FROM mtl_transaction_reasons r
WHERE r.reason_name = l_reason;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Reason:' || l_reason;
RETURN;
END;
END IF;
IF p_type = 'ISSUE' THEN
--validate item available quantity whether enough to issue
l_available_qty := get_item_att_qty(g_mmt_iface.inventory_item_id,
g_mmt_iface.organization_id,
g_mmt_iface.subinventory_code,
g_mmt_iface.locator_id);
IF l_issue_qty > l_available_qty THEN
o_success_flag := 'N';
o_error_mssg := 'Qty is not enough.';
END IF;
END IF;
--validate inv period whether open
SELECT COUNT(1)
INTO l_exists
FROM org_acct_periods p,
gl_periods gp
WHERE p.organization_id = g_mmt_iface.organization_id
AND p.period_start_date <= trunc(SYSDATE)
AND p.schedule_close_date >= trunc(SYSDATE)
AND p.open_flag = 'Y'
AND p.period_name = gp.period_name
AND p.period_set_name = gp.period_set_name
AND gp.start_date <= trunc(SYSDATE)
AND gp.end_date >= trunc(SYSDATE);
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'INV period is not opened.';
RETURN;
END IF;
END validate_wip_txn;
--Validate data wip complete transaction from etrace
PROCEDURE validate_wip_complete
(
p_dj_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_uom IN VARCHAR2,
p_quantity IN NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_exists NUMBER;
l_locator_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--Validate oraganization
BEGIN
SELECT organization_id
INTO g_wmti_iface.organization_id
FROM org_organization_definitions o
WHERE organization_code = p_org_code;
g_wmti_iface.organization_code := p_org_code;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Organization CODE:' ||
p_org_code;
RETURN;
END;
--Validate dj_name
BEGIN
SELECT wip_entity_id
INTO g_wmti_iface.wip_entity_id
FROM wip_entities
WHERE wip_entity_name = p_dj_name
AND organization_id = g_wmti_iface.organization_id;
g_wmti_iface.wip_entity_name := p_dj_name;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid DJ name:' || p_dj_name;
RETURN;
END;
--Validate DJ status must be released
SELECT COUNT(1)
INTO l_exists
FROM wip_discrete_jobs
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id
AND status_type = '3';
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'DJ status must be released.';
RETURN;
END IF;
--Validate DJ default completion subinventory
BEGIN
SELECT completion_subinventory,
completion_locator_id
INTO g_completion_subinventory,
l_locator_id
FROM wip_discrete_jobs
WHERE wip_entity_id = g_wmti_iface.wip_entity_id;

IF l_locator_id IS NOT NULL THEN
SELECT concatenated_segments
INTO g_completion_locator
FROM mtl_item_locations_kfv
WHERE organization_id = g_wmti_iface.organization_id
AND inventory_location_id = l_locator_id;
ELSE
g_completion_locator := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
g_completion_subinventory := NULL;
g_completion_locator := NULL;
END;
--Validate quantity
IF p_quantity IS NULL OR
p_quantity <= 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Complete quantity.:' || p_quantity;
RETURN;
ELSE
g_wmti_iface.transaction_quantity := p_quantity;
END IF;
--
--Validate UOM
SELECT COUNT(1)
INTO l_exists
FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom;
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid UOM:' || p_uom;
RETURN;
ELSE
g_wmti_iface.transaction_uom := p_uom;
END IF;
END validate_wip_complete;
--
PROCEDURE validate_dj_issue
(
p_dj_name IN VARCHAR2,
p_org_code IN VARCHAR2,
o_org_id OUT NUMBER,
o_entity_id OUT NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_picked_flag VARCHAR2(1);
l_exists NUMBER;
BEGIN
o_success_flag := 'Y';
--Validate oraganization
BEGIN
SELECT organization_id
INTO o_org_id
FROM org_organization_definitions o
WHERE organization_code = p_org_code;

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Organization CODE:' ||
p_org_code;
RETURN;
END;
--Validate dj_name
BEGIN
SELECT wip_entity_id
INTO o_entity_id
FROM wip_entities
WHERE wip_entity_name = p_dj_name
AND organization_id = g_mmt_iface.organization_id;

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid DJ name:' || p_dj_name;
RETURN;
END;
--Validate DJ status must be released
SELECT COUNT(1)
INTO l_exists
FROM wip_discrete_jobs
WHERE wip_entity_id = o_entity_id
AND organization_id = g_mmt_iface.organization_id
AND status_type = '3';
IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'DJ status must be released.';
RETURN;
END IF;
--Validate DJ whether picked
BEGIN
SELECT attribute15
INTO l_picked_flag
FROM wip_discrete_jobs wd
WHERE wd.organization_id = g_mmt_iface.organization_id
AND wd.wip_entity_id = g_mmt_iface.transaction_source_id;
IF l_picked_flag IS NOT NULL THEN
o_success_flag := 'N';
o_error_mssg := 'DJ have been picked.' || p_dj_name;
RETURN;
END IF;
END;

END validate_dj_issue;

--
PROCEDURE validate_morder
(
p_org_code IN VARCHAR2,
p_req_number IN VARCHAR2,
p_item_number IN VARCHAR2,
p_inventory IN VARCHAR2,
p_locator IN VARCHAR2,
o_organization_id OUT NUMBER,
o_item_id OUT NUMBER,
o_header_id OUT NUMBER,
o_locator_id OUT NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_transaction_date DATE;
l_acct_period_id NUMBER;
l_open_past_period BOOLEAN := FALSE;
l_organization_id NUMBER;
l_temp NUMBER;
l_temp2 NUMBER;
l_locator_id NUMBER;
l_exists NUMBER;

BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--validate org code
SELECT organization_id
INTO l_organization_id
FROM org_organization_definitions
WHERE organization_code = p_org_code;
o_organization_id := l_organization_id;
--validate subinventory and locator
--Validate sub
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_inventory
AND organization_id = l_organization_id;

IF l_exists = 0 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid Subinventory.' || p_inventory;
RETURN;
END IF;

l_locator_id := get_locator_id(p_locator,
p_inventory,
l_organization_id);
IF l_locator_id = -1 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid locator segments.';
RETURN;
ELSE
o_locator_id := l_locator_id;
END IF;
-- check transaction_date is in open period.
l_transaction_date := SYSDATE;
invttmtx.tdatechk(l_organization_id,
trunc(l_transaction_date),
l_acct_period_id,
l_open_past_period);

IF l_acct_period_id = 0 OR
l_acct_period_id = -1 THEN
o_success_flag := 'N';
o_error_mssg := 'Current period is not open.';
RETURN;
END IF;
--check item num
SELECT DISTINCT mtl.inventory_item_id,
mth.header_id,
mth.move_order_type
INTO l_temp,
l_temp2,
g_move_order_type
FROM mtl_txn_request_lines mtl,
mtl_txn_request_headers mth,
mtl_system_items_kfv msk
WHERE mth.header_id = mtl.header_id
AND mtl.organization_id = l_organization_id
--
AND mth.request_number = p_req_number
AND msk.concatenated_segments = p_item_number
--
--AND mth.move_order_type = '5' --Manufacturing Component Pick
AND mtl.line_status IN ('3', '7', '9')
AND mtl.inventory_item_id = msk.inventory_item_id
AND mtl.organization_id = msk.organization_id;
IF l_temp IS NULL THEN
o_success_flag := 'N';
o_error_mssg := 'Doesnot exist pending transaction record or no allocation.';
ELSE
o_item_id := l_temp;
o_header_id := l_temp2;
END IF;
EXCEPTION
WHEN no_data_found THEN
o_success_flag := 'N';
o_error_mssg := 'Doesnot exist pending transaction record or no allocation.';
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur exception:' || SQLERRM;
END validate_morder;
--process data in the mmi table to mmt table
PROCEDURE handle_mmt_iface
(
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2,
p_table IN VARCHAR2
) IS
l_result NUMBER;
l_return_status VARCHAR2(2);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_trans_count NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--Call api process transaction
l_result := inv_txn_manager_pub.process_transactions(p_api_version => 1.0,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data, --error message
x_trans_count => l_trans_count, --record processed
p_table => p_table, --interface
p_header_id => g_mmt_iface.transaction_header_id);

IF l_result = 0 THEN
o_success_flag := 'Y';
o_error_mssg := NULL;
ELSE
o_success_flag := 'N';
IF p_table = '1' THEN
--retreve error from interface
FOR i IN (SELECT DISTINCT error_explanation
FROM mtl_transactions_interface
WHERE transaction_header_id =
g_mmt_iface.transaction_header_id) LOOP
o_error_mssg := o_error_mssg || '.Error:' ||
i.error_explanation;
END LOOP;
ELSE
o_error_mssg := l_msg_data;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception:' || SQLERRM;
END handle_mmt_iface;

PROCEDURE handle_wmti_iface
(
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_errormsg VARCHAR2(1000);
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--call api process wmti
wip_movproc_pub.processinterface(p_txn_id => g_wmti_iface.transaction_id,
p_do_backflush => fnd_api.g_true,
p_commit => fnd_api.g_false,
x_returnstatus => l_success_flag,
x_errormsg => l_errormsg);
IF l_success_flag <> fnd_api.g_ret_sts_success THEN
o_success_flag := 'N';
o_error_mssg := l_errormsg;
END IF;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception:' || SQLERRM;
END handle_wmti_iface;
--if Move Order Type = 1 , need to allocate
PROCEDURE morder_allocation
(
p_org_id IN NUMBER,
p_header_id IN NUMBER,
p_item_id IN NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
CURSOR cur_data IS
SELECT mtl.*
FROM mtl_txn_request_lines mtl
WHERE mtl.organization_id = p_org_id
AND mtl.inventory_item_id = p_item_id
AND mtl.header_id = p_header_id
FOR UPDATE NOWAIT;

l_header_id NUMBER;
l_num_of_rows NUMBER;
l_detailed_qty NUMBER;
l_return_status VARCHAR2(10);
l_count NUMBER;
l_msg VARCHAR2(2000);
l_transaction_temp_id NUMBER;
l_rev VARCHAR2(3);
l_from_loc_id NUMBER;
l_to_loc_id NUMBER;
l_lot_number VARCHAR2(80);
l_expiration_date DATE;
BEGIN
--when allcating , programme do not check available qty,it
--checks when transact move order
FOR rec_data IN cur_data LOOP

SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;

inv_replenish_detail_pub.line_details_pub(p_line_id => rec_data.line_id,
x_number_of_rows => l_num_of_rows,
x_detailed_qty => l_detailed_qty,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_msg,
x_revision => l_rev,
x_locator_id => l_from_loc_id,
x_transfer_to_location => l_to_loc_id,
x_lot_number => l_lot_number,
x_expiration_date => l_expiration_date,
x_transaction_temp_id => l_transaction_temp_id,
p_transaction_header_id => l_header_id,
p_transaction_mode => 1,
p_move_order_type => g_move_order_type,
p_serial_flag => 'F',
p_commit => FALSE);
IF l_return_status <> fnd_api.g_ret_sts_success OR
l_num_of_rows <= 0 THEN
o_success_flag := 'N';
o_error_mssg := l_msg;
RETURN;
END IF;

END LOOP;
END morder_allocation;

--
PROCEDURE transact_morder
(
p_org_id IN NUMBER,
p_modline_id IN NUMBER,
p_tran_temp_id IN NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
CURSOR cur_data IS
SELECT mtl.*
FROM mtl_txn_request_lines mtl
WHERE mtl.organization_id = p_org_id
AND mtl.line_id = p_modline_id
FOR UPDATE NOWAIT;
l_header_id NUMBER;
l_num_of_rows NUMBER;
l_detailed_qty NUMBER;
l_return_status VARCHAR2(10);
l_count NUMBER;
l_msg VARCHAR2(2000);
l_transaction_temp_id NUMBER;
l_rev VARCHAR2(3);
l_from_loc_id NUMBER;
l_to_loc_id NUMBER;
l_lot_number VARCHAR2(80);
l_expiration_date DATE;
--
l_creation_date DATE;
l_created_by NUMBER;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_period_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := '';
FOR rec_data IN cur_data LOOP

/*SELECT DISTINCT transaction_header_id
INTO l_header_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = rec_data.line_id;

IF l_header_id IS NULL THEN
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_header_id
WHERE move_order_line_id = rec_data.line_id;
END IF;*/

SELECT mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;

UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_header_id
WHERE transaction_temp_id = p_tran_temp_id;

IF nvl(rec_data.quantity_detailed,
0) = 0 THEN
--Auto detailed function
inv_replenish_detail_pub.line_details_pub(p_line_id => rec_data.line_id,
x_number_of_rows => l_num_of_rows,
x_detailed_qty => l_detailed_qty,
x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_msg,
x_revision => l_rev,
x_locator_id => l_from_loc_id,
x_transfer_to_location => l_to_loc_id,
x_lot_number => l_lot_number,
x_expiration_date => l_expiration_date,
x_transaction_temp_id => l_transaction_temp_id,
p_transaction_header_id => l_header_id,
p_transaction_mode => 1,
p_move_order_type => g_move_order_type,

p_serial_flag => 'F',
p_commit => FALSE);
END IF;
IF l_return_status <> fnd_api.g_ret_sts_success OR
l_num_of_rows <= 0 THEN
o_success_flag := 'N';
o_error_mssg := l_msg;
RETURN;
END IF;
--whether line detailed or not
/*inv_mo_line_detail_util.is_line_detailed(x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_msg,
p_move_order_line_id => rec_data.line_id);
IF l_return_status <> fnd_api.g_ret_sts_success THEN
o_success_flag := 'N';
o_error_mssg := 'Line detailed error:' || l_msg;
RETURN;
END IF;*/

--Call transfer transaction
inv_to_form_trolin.validate_and_write(x_return_status => l_return_status,
x_msg_count => l_count,
x_msg_data => l_msg,
p_attribute1 => rec_data.attribute1,
p_attribute10 => rec_data.attribute10,
p_attribute11 => rec_data.attribute11,
p_attribute12 => rec_data.attribute12,
p_attribute13 => rec_data.attribute13,
p_attribute14 => rec_data.attribute14,
p_attribute15 => rec_data.attribute15,
p_attribute2 => rec_data.attribute2,
p_attribute3 => rec_data.attribute3,
p_attribute4 => rec_data.attribute4,
p_attribute5 => rec_data.attribute5,
p_attribute6 => rec_data.attribute6,
p_attribute7 => rec_data.attribute7,
p_attribute8 => rec_data.attribute8,
p_attribute9 => rec_data.attribute9,

p_attribute_category => rec_data.attribute_category,
p_date_required => rec_data.date_required,
p_from_locator_id => rec_data.from_locator_id,
p_from_subinventory_code => rec_data.from_subinventory_code,
p_from_subinventory_id => rec_data.from_subinventory_id,
p_header_id => rec_data.header_id,
p_inventory_item_id => rec_data.inventory_item_id,
p_line_id => rec_data.line_id,
p_line_number => rec_data.line_number,
p_line_status => rec_data.line_status,
p_lot_number => l_lot_number,
p_organization_id => rec_data.organization_id,
p_project_id => rec_data.project_id,
p_quantity => rec_data.quantity,
p_quantity_delivered => rec_data.quantity_delivered,
p_quantity_detailed => rec_data.quantity_detailed,
p_reason_id => rec_data.reason_id,
p_reference => rec_data.reference,
p_reference_id => rec_data.reference_id,
p_reference_type_code => rec_data.reference_type_code,
p_revision => l_rev,
p_serial_number_end => rec_data.serial_number_end,
p_serial_number_start => rec_data.serial_number_start,
p_status_date => rec_data.status_date,
p_task_id => rec_data.task_id,
p_to_account_id => rec_data.to_account_id,
p_to_locator_id => rec_data.to_locator_id,
p_to_subinventory_code => rec_data.to_subinventory_code,
p_to_subinventory_id => rec_data.to_subinventory_id,
p_transaction_header_id => l_header_id,
p_uom_code => rec_data.uom_code,
p_transaction_type_id => rec_data.transaction_type_id,

p_transaction_source_type_id => rec_data.transaction_source_type_id,
p_txn_source_line_detail_id => rec_data.txn_source_line_detail_id,

p_txn_source_id => rec_data.txn_source_id,
p_txn_source_line_id => rec_data.txn_source_line_id,
p_primary_quantity => rec_data.primary_quantity,
p_to_organization_id => rec_data.to_organization_id,
p_pick_strategy_id => rec_data.pick_strategy_id,
p_put_away_strategy_id => rec_data.put_away_strategy_id,
p_unit_number => rec_data.unit_number,
p_ship_to_location_id => rec_data.ship_to_location_id,
p_lpn_id => NULL,
p_db_flag => 'T',
x_creation_date => l_creation_date,
x_created_by => l_created_by,
x_last_update_date => l_last_update_date,
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login);

IF l_return_status <> fnd_api.g_ret_sts_success THEN
o_success_flag := 'N';
o_error_mssg := 'Error calling server API in procedure Validate_And_Write:' ||
l_msg;
RETURN;
END IF;
--Call online process
g_mmt_iface.transaction_header_id := l_header_id;

BEGIN
SELECT acct_period_id
INTO l_period_id
FROM org_acct_periods
WHERE period_close_date IS NULL
AND organization_id = p_org_id
AND trunc(schedule_close_date) >=
trunc(inv_le_timezone_pub.get_le_day_for_inv_org(SYSDATE,
p_org_id))
AND trunc(period_start_date) <=
trunc(inv_le_timezone_pub.get_le_day_for_inv_org(SYSDATE,
p_org_id));
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Period is not open';
RETURN;
END;

UPDATE mtl_material_transactions_temp
SET transaction_status = '1', --online
source_line_id = p_modline_id,
process_flag = 'Y',
transaction_date = SYSDATE,
acct_period_id = l_period_id
WHERE move_order_line_id = p_modline_id
AND transaction_temp_id = p_tran_temp_id;

handle_mmt_iface(l_return_status,
l_msg,
'2');
IF l_return_status <> 'Y' THEN
o_success_flag := 'N';
o_error_mssg := 'Error calling on line process:' || l_msg;
RETURN;
END IF;
END LOOP;

EXCEPTION
WHEN app_exception.record_lock_exception THEN
o_success_flag := 'N';
o_error_mssg := 'Record locked failure.';
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception[transact_morder]:' ||
SQLERRM;
END transact_morder;

--
PROCEDURE allocation_quantity
(
p_header_id IN NUMBER,
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_lot_num IN VARCHAR2,
p_quantity IN NUMBER,
p_inventory IN VARCHAR2,
p_locator_id IN NUMBER,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
CURSOR cur_mmtt IS
SELECT mt.ROWID mt_rowid,
mlt.transaction_quantity,
mlt.primary_quantity,

mt.transaction_quantity mt_transaction_quantity,
mt.primary_quantity mt_primary_quantity,

mt.transaction_uom,
mt.transaction_temp_id,
mt.move_order_line_id,
mt.item_lot_control_code lot_control_code,
mt.item_serial_control_code serial_control_code,
mt.organization_id
FROM mtl_material_transactions_temp mt,
mtl_txn_request_lines l,
mtl_transaction_lots_temp mlt
WHERE l.line_id = mt.move_order_line_id
AND mt.transaction_temp_id = mlt.transaction_temp_id(+)
AND nvl(mlt.lot_number,
'-1') = nvl(p_lot_num,
'-1')
AND l.header_id = p_header_id
AND l.organization_id = p_org_id
AND l.inventory_item_id = p_item_id
AND mt.subinventory_code = p_inventory
AND nvl(mt.locator_id,
-1) = nvl(p_locator_id,
nvl(mt.locator_id,
-1))
ORDER BY mt.transaction_quantity
FOR UPDATE OF mt.transaction_quantity, mt.primary_quantity NOWAIT;

l_primary_uom_code VARCHAR2(30);
l_lot_control_code VARCHAR2(60);
l_quantity NUMBER;
l_rowid ROWID;
l_temp_id NUMBER;
l_available_qty NUMBER;
l_morder_line_id NUMBER;
l_return VARCHAR2(1);
l_error_mssg VARCHAR2(2000);
l_lineid_tbl t_line_id;
l_index NUMBER := 1;
l_exe_flag VARCHAR2(1);
l_exists_mmtt NUMBER;
--
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
l_msg_count NUMBER;
l_new_record_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
l_quantity := p_quantity;
SELECT primary_uom_code,
lot_control_code
INTO l_primary_uom_code,
l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;

IF g_move_order_type = 1 THEN

SELECT COUNT(1)
INTO l_exists_mmtt
FROM mtl_material_transactions_temp
WHERE move_order_header_id = p_header_id
AND organization_id = p_org_id
AND inventory_item_id = p_item_id;

IF l_exists_mmtt = 0 THEN
--need to allocation
morder_allocation(p_org_id => p_org_id,
p_header_id => p_header_id,
p_item_id => p_item_id,
o_success_flag => l_return,
o_error_mssg => l_error_mssg);
IF l_return <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
END IF;

END IF;
--execute flag
l_exe_flag := 'N';

FOR rec_data IN cur_mmtt LOOP
l_exe_flag := 'Y';
IF rec_data.transaction_quantity <= l_quantity THEN
l_quantity := l_quantity -
rec_data.transaction_quantity;
l_rowid := rec_data.mt_rowid;
l_temp_id := rec_data.transaction_temp_id;
l_morder_line_id := rec_data.move_order_line_id;
--save the list of move order line ID
l_lineid_tbl(l_index).line_id := rec_data.move_order_line_id;
l_lineid_tbl(l_index).quantity := rec_data.transaction_quantity;
l_lineid_tbl(l_index).temp_id := rec_data.transaction_temp_id;
l_lineid_tbl(l_index).mt_quantity := rec_data.mt_transaction_quantity;
l_lineid_tbl(l_index).lot_ctrl := rec_data.lot_control_code;
l_lineid_tbl(l_index).ser_ctrl := rec_data.serial_control_code;

l_index := l_index + 1;
IF l_quantity = 0 THEN
EXIT;
END IF;

ELSE
--should be split line
--populate temp table
--save the lot informations
inv_missing_qty_actions_engine.populate_table(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_transaction_temp_id => rec_data.transaction_temp_id,
p_mo_line_id => rec_data.move_order_line_id,
p_lot_control_code => rec_data.lot_control_code,
p_serial_control_code => rec_data.serial_control_code);

UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_quantity,
primary_quantity = l_quantity
WHERE transaction_temp_id = rec_data.transaction_temp_id;

IF rec_data.lot_control_code = 2 THEN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_quantity,
primary_quantity = l_quantity
WHERE transaction_temp_id =
rec_data.transaction_temp_id
AND lot_number = p_lot_num;
--if exists multiply rows then delete them
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id =
rec_data.transaction_temp_id
AND lot_number <> p_lot_num;

END IF;
--Call API process split quantity
inv_missing_qty_actions_engine.process_action(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_new_record_id => l_new_record_id,
p_action => 2, --split allocation
p_transaction_temp_id => rec_data.transaction_temp_id,
p_remaining_quantity => rec_data.transaction_quantity -
l_quantity,
p_lot_control_code => rec_data.lot_control_code,
p_serial_control_code => rec_data.serial_control_code);

/*inv_mo_line_detail_util.reduce_allocation_quantity(l_return,
rec_data.transaction_temp_id,
rec_data.transaction_quantity -
l_quantity);
IF l_return <> fnd_api.g_ret_sts_success THEN
l_error_mssg := 'Error when reduce allocation quantity.';
RAISE fnd_api.g_exc_error;
END IF;*/

--Transact move order
transact_morder(p_org_id => rec_data.organization_id,
p_modline_id => rec_data.move_order_line_id,
p_tran_temp_id => rec_data.transaction_temp_id,
o_success_flag => l_return,
o_error_mssg => l_error_mssg);
IF l_return <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
l_quantity := l_quantity - rec_data.transaction_quantity;
EXIT;
END IF;
END LOOP rec_data;

IF l_exe_flag = 'N' THEN
l_error_mssg := 'No allocation for this move order.';
RAISE fnd_api.g_exc_error;
END IF;

IF l_quantity > 0 THEN
--¿¿¿¿¿¿¿¿¿¿¿,¿¿¿¿¿¿¿
--¿¿¿¿¿¿¿¿¿¿¿¿¿,¿¿¿¿
l_available_qty := get_item_att_qty(p_item_id => p_item_id,
p_organization_id => p_org_id,
p_subinventory_code => p_inventory,
p_location_id => p_locator_id,
p_item_rev => NULL,
p_lot_number => p_lot_num);

IF l_available_qty >= l_quantity THEN
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity +
l_quantity,
primary_quantity = primary_quantity + l_quantity
WHERE ROWID = l_rowid;

UPDATE mtl_txn_request_lines
SET quantity_detailed = nvl(quantity_detailed,
0) + l_quantity
WHERE line_id = l_morder_line_id;

IF l_lot_control_code = 2 THEN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity +
l_quantity,
primary_quantity = primary_quantity +
l_quantity
WHERE transaction_temp_id = l_temp_id
AND lot_number = p_lot_num;
END IF;
ELSE
o_success_flag := 'N';
o_error_mssg := 'Qty is not enough.';
RETURN;
END IF; --l_available_qty >= l_quantity
END IF;

--transact
IF l_index > 1 THEN
FOR i IN l_lineid_tbl.FIRST .. l_lineid_tbl.LAST LOOP

IF g_move_order_type = 1 THEN
--update req lines quantity detailed
UPDATE mtl_txn_request_lines
SET quantity_detailed = nvl(quantity_detailed,
0) + l_lineid_tbl(i)
.quantity
WHERE line_id = l_lineid_tbl(i).line_id;
END IF;
--IF MMT qty <> MMLT qty then need to split
--this case means one MMT record has multiply MMLT recors
-- MT 100 Lot1 40
-- Lot2 60
IF l_lineid_tbl(i).mt_quantity <> l_lineid_tbl(i).quantity THEN

inv_missing_qty_actions_engine.populate_table(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_transaction_temp_id => l_lineid_tbl(i)
.temp_id,
p_mo_line_id => l_lineid_tbl(i)
.line_id,
p_lot_control_code => l_lineid_tbl(i)
.lot_ctrl,
p_serial_control_code => l_lineid_tbl(i)
.ser_ctrl);

UPDATE mtl_material_transactions_temp
SET transaction_quantity = l_lineid_tbl(i).quantity,
primary_quantity = l_lineid_tbl(i).quantity
WHERE transaction_temp_id = l_lineid_tbl(i).temp_id;

IF l_lineid_tbl(i).lot_ctrl = 2 THEN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_lineid_tbl(i)
.quantity,
primary_quantity = l_lineid_tbl(i)
.quantity
WHERE transaction_temp_id = l_lineid_tbl(i)
.temp_id
AND lot_number = p_lot_num;
--if exists multiply rows then delete them
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = l_lineid_tbl(i)
.temp_id
AND lot_number <> p_lot_num;

END IF;
--Call API process split quantity
inv_missing_qty_actions_engine.process_action(x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_new_record_id => l_new_record_id,
p_action => 2, --split allocation
p_transaction_temp_id => l_lineid_tbl(i)
.temp_id,
p_remaining_quantity => l_lineid_tbl(i)
.mt_quantity -
l_lineid_tbl(i)
.quantity,
p_lot_control_code => l_lineid_tbl(i)
.lot_ctrl,
p_serial_control_code => l_lineid_tbl(i)
.ser_ctrl);

END IF;

--
transact_morder(p_org_id => p_org_id,
p_modline_id => l_lineid_tbl(i).line_id,
p_tran_temp_id => l_lineid_tbl(i).temp_id,
o_success_flag => l_return,
o_error_mssg => l_error_mssg);
IF l_return <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

END LOOP;
END IF;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_mssg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur exception[allocation_quantity]:' ||
SQLERRM;
END allocation_quantity;
--Insert into wip_move_txn_interface
PROCEDURE ins_mtxn_interface
(
p_transaction_id IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER DEFAULT NULL,
p_last_updated_by_name IN VARCHAR2 DEFAULT NULL,
p_creation_date IN DATE,
p_created_by IN NUMBER DEFAULT NULL,
p_created_by_name IN VARCHAR2 DEFAULT NULL,
p_last_update_login IN NUMBER DEFAULT NULL,
p_request_id IN NUMBER DEFAULT NULL,
p_program_application_id IN NUMBER DEFAULT NULL,
p_program_id IN NUMBER DEFAULT NULL,
p_program_update_date IN DATE DEFAULT NULL,
p_group_id IN NUMBER DEFAULT NULL,
p_kanban_card_id IN NUMBER DEFAULT NULL,
p_source_code IN VARCHAR2 DEFAULT NULL,
p_source_line_id IN NUMBER DEFAULT NULL,
p_process_phase IN NUMBER,
p_process_status IN NUMBER,
p_transaction_type IN NUMBER DEFAULT NULL,
p_organization_id IN NUMBER DEFAULT NULL,
p_organization_code IN VARCHAR2 DEFAULT NULL,
p_wip_entity_id IN NUMBER DEFAULT NULL,
p_wip_entity_name IN VARCHAR2 DEFAULT NULL,
p_entity_type IN NUMBER DEFAULT NULL,
p_primary_item_id IN NUMBER DEFAULT NULL,
p_line_id IN NUMBER DEFAULT NULL,
p_line_code IN VARCHAR2 DEFAULT NULL,
p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
p_transaction_date IN DATE,
p_acct_period_id IN NUMBER DEFAULT NULL,
p_fm_operation_seq_num IN NUMBER DEFAULT NULL,
p_fm_operation_code IN VARCHAR2 DEFAULT NULL,
p_fm_department_id IN NUMBER DEFAULT NULL,
p_fm_department_code IN VARCHAR2 DEFAULT NULL,
p_fm_intraoperation_step_type IN NUMBER DEFAULT NULL,
p_to_operation_seq_num IN NUMBER DEFAULT NULL,
p_to_operation_code IN VARCHAR2 DEFAULT NULL,
p_to_department_id IN NUMBER DEFAULT NULL,
p_to_department_code IN VARCHAR2 DEFAULT NULL,
p_to_intraoperation_step_type IN NUMBER DEFAULT NULL,
p_transaction_quantity IN NUMBER,
p_transaction_uom IN VARCHAR2,
p_primary_quantity IN NUMBER DEFAULT NULL,
p_primary_uom IN VARCHAR2 DEFAULT NULL,
p_scrap_account_id IN NUMBER DEFAULT NULL,
p_reason_id IN NUMBER DEFAULT NULL,
p_reason_name IN VARCHAR2 DEFAULT NULL,
p_reference IN VARCHAR2 DEFAULT NULL,
p_qa_collection_id IN NUMBER DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL,
p_attribute1 IN VARCHAR2 DEFAULT NULL,
p_attribute2 IN VARCHAR2 DEFAULT NULL,
p_attribute3 IN VARCHAR2 DEFAULT NULL,
p_attribute4 IN VARCHAR2 DEFAULT NULL,
p_attribute5 IN VARCHAR2 DEFAULT NULL,
p_attribute6 IN VARCHAR2 DEFAULT NULL,
p_attribute7 IN VARCHAR2 DEFAULT NULL,
p_attribute8 IN VARCHAR2 DEFAULT NULL,
p_attribute9 IN VARCHAR2 DEFAULT NULL,
p_attribute10 IN VARCHAR2 DEFAULT NULL,
p_attribute11 IN VARCHAR2 DEFAULT NULL,
p_attribute12 IN VARCHAR2 DEFAULT NULL,
p_attribute13 IN VARCHAR2 DEFAULT NULL,
p_attribute14 IN VARCHAR2 DEFAULT NULL,
p_attribute15 IN VARCHAR2 DEFAULT NULL,
p_oc_transaction_qty IN NUMBER DEFAULT NULL,
p_oc_primary_qty IN NUMBER DEFAULT NULL,
p_oc_transaction_id IN NUMBER DEFAULT NULL,
p_xml_document_id IN VARCHAR2 DEFAULT NULL,
p_processing_order IN NUMBER DEFAULT NULL,
p_batch_id IN NUMBER DEFAULT NULL
)

IS
BEGIN

INSERT INTO wip_move_txn_interface
(transaction_id,
last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
group_id,
kanban_card_id,
source_code,
source_line_id,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code,
wip_entity_id,
wip_entity_name,
entity_type,
primary_item_id,
line_id,
line_code,
repetitive_schedule_id,
transaction_date,
acct_period_id,
fm_operation_seq_num,
fm_operation_code,
fm_department_id,
fm_department_code,
fm_intraoperation_step_type,
to_operation_seq_num,
to_operation_code,
to_department_id,
to_department_code,
to_intraoperation_step_type,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
scrap_account_id,
reason_id,
reason_name,
reference,
qa_collection_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
overcompletion_transaction_qty,
overcompletion_primary_qty,
overcompletion_transaction_id,
xml_document_id,
processing_order,
batch_id)
VALUES
(p_transaction_id,
p_last_update_date,
p_last_updated_by,
p_last_updated_by_name,
p_creation_date,
p_created_by,
p_created_by_name,
p_last_update_login,
p_request_id,
p_program_application_id,
p_program_id,
p_program_update_date,
p_group_id,
p_kanban_card_id,
p_source_code,
p_source_line_id,
p_process_phase,
p_process_status,
p_transaction_type,
p_organization_id,
p_organization_code,
p_wip_entity_id,
p_wip_entity_name,
p_entity_type,
p_primary_item_id,
p_line_id,
p_line_code,
p_repetitive_schedule_id,
p_transaction_date,
p_acct_period_id,
p_fm_operation_seq_num,
p_fm_operation_code,
p_fm_department_id,
p_fm_department_code,
p_fm_intraoperation_step_type,
p_to_operation_seq_num,
p_to_operation_code,
p_to_department_id,
p_to_department_code,
p_to_intraoperation_step_type,
p_transaction_quantity,
p_transaction_uom,
p_primary_quantity,
p_primary_uom,
p_scrap_account_id,
p_reason_id,
p_reason_name,
p_reference,
p_qa_collection_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_oc_transaction_qty,
p_oc_primary_qty,
p_oc_transaction_id,
p_xml_document_id,
p_processing_order,
p_batch_id);
END ins_mtxn_interface;
--p_txn_type : RETURN
-- COMPLETE
PROCEDURE ins_move_txn_iface
(
p_txn_type IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_fm_operation_seq_num NUMBER;
l_to_operation_seq_num NUMBER;
l_transaction_type NUMBER;
l_fm_step NUMBER;
l_to_step NUMBER;
l_oqa_qty NUMBER;
l_oqa_seq NUMBER;
l_fm_seq_old NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
SELECT wip_transactions_s.NEXTVAL
INTO g_wmti_iface.transaction_id
FROM dual;

IF g_wmti_iface.fm_operation_seq_num IS NOT NULL THEN
l_fm_seq_old := g_wmti_iface.fm_operation_seq_num;
END IF;

--get seq num max(min)
BEGIN
SELECT MAX(operation_seq_num),
MIN(operation_seq_num)
INTO g_wmti_iface.to_operation_seq_num,
g_wmti_iface.fm_operation_seq_num
FROM wip_operations
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'DJ does not exists operations.';
RETURN;
END;

--get OQA quantity in tomove
BEGIN
SELECT nvl(quantity_waiting_to_move,
0),
operation_seq_num
INTO l_oqa_qty,
l_oqa_seq
FROM wip_operations_v
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id
AND department_code = 'OQA';
EXCEPTION
WHEN OTHERS THEN
l_oqa_qty := -1;
END;
--¿¿¿¿OQA¿¿¿¿¿¿¿¿,¿¿¿
IF l_oqa_seq = g_wmti_iface.to_operation_seq_num THEN
o_success_flag := 'Y';
o_error_mssg := 'OQA';
RETURN;
END IF;

--if l_oqa_qty > 0 must check oqa quantity
IF l_oqa_qty <> -1 THEN
IF l_oqa_qty < g_wmti_iface.transaction_quantity THEN
o_success_flag := 'N';
o_error_mssg := 'DJ Completed quantity must be less than OQA quantity.';
RETURN;
ELSE
g_wmti_iface.fm_operation_seq_num := l_oqa_seq;
END IF;
l_fm_step := 3; --to move
ELSE
l_fm_step := 1; --Queue
END IF;
--
IF p_txn_type = 'COMPLETE' THEN
IF l_fm_seq_old IS NOT NULL THEN
l_fm_operation_seq_num := l_fm_seq_old;
ELSE
l_fm_operation_seq_num := g_wmti_iface.fm_operation_seq_num;
END IF;
l_to_operation_seq_num := g_wmti_iface.to_operation_seq_num;
l_transaction_type := 2; --COMPLETE
--l_fm_step := 1; --Queue
l_to_step := 3; --To Move
ELSIF p_txn_type = 'RETURN' THEN
l_fm_operation_seq_num := g_wmti_iface.to_operation_seq_num;
l_to_operation_seq_num := g_wmti_iface.fm_operation_seq_num;
l_transaction_type := 3; --RETURN
l_fm_step := 3; --To Move
l_to_step := 1; --Queue
ELSE
o_success_flag := 'N';
o_error_mssg := 'Invalid arguments for ins_move_txn_iface.p_txn_type';
END IF;
ins_mtxn_interface(p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_last_update_login => -1,
p_last_updated_by_name => g_user_name,
p_created_by_name => g_user_name,
--
p_group_id => g_wmti_iface.transaction_id,
p_transaction_id => g_wmti_iface.transaction_id,
p_transaction_type => l_transaction_type, --complete
p_process_phase => 1, --validate
p_process_status => 2, --prevent move manager picking
p_wip_entity_name => g_wmti_iface.wip_entity_name,
p_wip_entity_id => g_wmti_iface.wip_entity_id,
p_organization_id => g_wmti_iface.organization_id,
p_organization_code => g_wmti_iface.organization_code,
p_transaction_date => SYSDATE,
p_transaction_quantity => g_wmti_iface.transaction_quantity,
p_transaction_uom => g_wmti_iface.transaction_uom,
p_fm_operation_seq_num => l_fm_operation_seq_num,

p_fm_intraoperation_step_type => l_fm_step, --Queue
p_to_intraoperation_step_type => l_to_step, --To move

p_to_operation_seq_num => l_to_operation_seq_num,
p_source_code => 'Etrace',
p_source_line_id => -1);

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception:' || SQLERRM;
END ins_move_txn_iface;
--insert into mtl_transaction_interface
PROCEDURE ins_mmt_interface
(
p_txn_type_id IN NUMBER,
p_source_type_id IN NUMBER,
p_source_code IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO g_mmt_iface.transaction_header_id
FROM dual;
cux_mtl_tran_inte_pkg.ins_mtl_trans_inte(p_organization_id => g_mmt_iface.organization_id,
p_transaction_interface_id => g_mmt_iface.transaction_header_id,
p_transaction_header_id => g_mmt_iface.transaction_header_id,
p_transaction_type_id => p_txn_type_id,
p_transaction_source_type_id => p_source_type_id,
p_transaction_source_id => g_mmt_iface.transaction_source_id,
p_department_id => g_mmt_iface.department_id,
p_operation_seq_num => g_mmt_iface.operation_seq_num,
p_transaction_uom => g_mmt_iface.transaction_uom,
p_transaction_date => SYSDATE,
p_source_code => p_source_code,
p_source_line_id => -1,
p_source_header_id => -1,
p_process_flag => 1,
p_transaction_mode => 2,
p_lock_flag => 2,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_inventory_item_id => g_mmt_iface.inventory_item_id,
p_revision => g_mmt_iface.revision,
p_subinventory_code => g_mmt_iface.subinventory_code,
p_locator_id => g_mmt_iface.locator_id,
p_transaction_quantity => g_mmt_iface.transaction_quantity,
p_reason_id => g_mmt_iface.reason_id,
p_transaction_reference => g_mmt_iface.transaction_reference,
p_final_completion_flag => g_mmt_iface.final_completion_flag,
p_transfer_subinventory => g_mmt_iface.transfer_subinventory,
p_transfer_organization => g_mmt_iface.transfer_organization,
p_transfer_locator => g_mmt_iface.transfer_locator);
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Occur Exception:[ins_mmt_interface]' ||
SQLERRM;
END ins_mmt_interface;

--process wip issue(supply push)
PROCEDURE process_wip_issue
(
p_dj_name IN VARCHAR2,
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_subinventory IN VARCHAR2,
p_locator IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_issue_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_exists NUMBER;
BEGIN
l_success_flag := 'Y';
--setp 1 : validate data
validate_wip_txn(p_dj_name => p_dj_name,
p_organization_code => p_org_code,
p_item_num => p_item_num,
p_item_rev => p_item_rev,
p_subinventory => p_subinventory,
p_locator => p_locator,
p_issue_quantity => p_issue_quantity,
p_uom => p_uom,
p_reason => p_reason,
p_type => 'ISSUE',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2 : insert mmt interface
ins_mmt_interface(p_txn_type_id => 35,
p_source_type_id => 5,
p_source_code => 'Component Issue',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2.1 : insert mlt interface
--insert into lot interface
IF p_lot_number IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE inventory_item_id = g_mmt_iface.inventory_item_id
AND organization_id = g_mmt_iface.organization_id
AND lot_number = p_lot_number;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid lot number.' || p_lot_number;
RAISE fnd_api.g_exc_error;
END IF;
cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => g_mmt_iface.transaction_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => p_lot_number,
p_transaction_quantity => g_mmt_iface.transaction_quantity);
END IF;
--setp 3 : call online process
handle_mmt_iface(l_success_flag,
l_error_msgg,
'1');
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 4 : return success or failure message
o_success_flag := 'Y';
o_error_msgg := NULL;
--COMMIT ;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_msgg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_msgg := 'Unhandled exception:' || SQLERRM;
END process_wip_issue;

--Porcess wip component return
PROCEDURE process_cmpnt_return
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_subinventory IN VARCHAR2,
p_locator IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_return_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
p_ref IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_exists NUMBER;
l_lot_ctrl NUMBER;
BEGIN
l_success_flag := 'Y';
--setp 1 : validate data
validate_wip_txn(p_dj_name => p_dj_name,
p_organization_code => p_organization_code,
p_item_num => p_item_num,
p_item_rev => p_item_rev,
p_subinventory => p_subinventory,
p_locator => p_locator,
p_issue_quantity => p_return_quantity,
p_uom => p_uom,
p_reason => p_reason,
p_type => 'RETURN',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2 : insert mmt interface
g_mmt_iface.transaction_reference := p_ref;
ins_mmt_interface(p_txn_type_id => 43,
p_source_type_id => 5,
p_source_code => 'Component Return',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--insert into lot interface
--get item lot control code
SELECT nvl(lot_control_code,
1)
INTO l_lot_ctrl
FROM mtl_system_items_b
WHERE organization_id = g_mmt_iface.organization_id
AND inventory_item_id = g_mmt_iface.inventory_item_id;
IF p_lot_number IS NOT NULL THEN
IF l_lot_ctrl = 2 THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE inventory_item_id = g_mmt_iface.inventory_item_id
AND organization_id = g_mmt_iface.organization_id
AND lot_number = p_lot_number
AND status_id = 1;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid lot number.' || p_lot_number;
RAISE fnd_api.g_exc_error;
END IF;
cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => g_mmt_iface.transaction_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => p_lot_number,
p_transaction_quantity => g_mmt_iface.transaction_quantity);
END IF;
ELSE
IF l_lot_ctrl = 2 THEN
l_error_msgg := 'Must enter lot number.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--setp 3 : call online process
handle_mmt_iface(l_success_flag,
l_error_msgg,
'1');
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 4 : return success or failure message
o_success_flag := 'Y';
o_error_mssg := NULL;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception:' || SQLERRM;
END process_cmpnt_return;

-- using move transaction complete
PROCEDURE process_wip_complete
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_complete_qty IN NUMBER,
p_uom IN VARCHAR2,
p_rev IN VARCHAR2,
x_subinventory IN OUT VARCHAR2,
x_locator IN OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
)

IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_locator_id NUMBER;
l_locator_type VARCHAR2(2);
l_oqa_qty NUMBER;
l_dj_open_qty NUMBER;
BEGIN
l_success_flag := 'Y';
--setp 1 : validate data
validate_wip_complete(p_dj_name => p_dj_name,
p_org_code => p_organization_code,
p_uom => p_uom,
p_quantity => p_complete_qty,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--lock row

--
IF x_subinventory IS NULL THEN
--if parameter is null , use default
IF g_completion_subinventory IS NULL THEN
l_error_msgg := 'Cannot get DJ completion subinventory.';
RAISE fnd_api.g_exc_error;
END IF;
ELSE
--not null then use parameter subinventory
g_completion_subinventory := x_subinventory;
--validate subinventory
BEGIN
SELECT locator_type
INTO l_locator_type
FROM mtl_secondary_inventories
WHERE organization_id = g_wmti_iface.organization_id
AND secondary_inventory_name = g_completion_subinventory;

EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid subinventory.';
RAISE fnd_api.g_exc_error;
END;

IF x_locator IS NOT NULL THEN
g_completion_locator := x_locator;
--get locator id
l_locator_id := get_locator_id(g_completion_locator,
g_completion_subinventory,
g_wmti_iface.organization_id);
IF l_locator_id = -1 THEN
l_error_msgg := 'Invalid locator.';
RAISE fnd_api.g_exc_error;
END IF;
ELSE
l_locator_id := NULL;
END IF;

--Sub donot have locator
IF l_locator_type = '1' THEN
l_locator_id := NULL;
ELSE
IF l_locator_id IS NULL THEN
l_error_msgg := 'Locator can not be null.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--update DJ completion subinventory
UPDATE wip_discrete_jobs
SET completion_subinventory = g_completion_subinventory,
completion_locator_id = l_locator_id
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id;

END IF;

--setp 2 : insert wip_move_txn_interface interface
ins_move_txn_iface(p_txn_type => 'COMPLETE',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--¿¿QA¿¿¿¿¿¿¿,¿¿¿¿¿¿¿Move Transaction ¿¿
--¿¿¿¿Material Transaction ¿¿

dbms_output.put_line('Mssg :' || l_error_msgg);

IF l_error_msgg = 'OQA' THEN
--¿¿¿¿¿¿¿¿¿¿¿¿¿

--get OQA quantity in tomove
BEGIN
SELECT nvl(quantity_waiting_to_move,
0)
INTO l_oqa_qty
FROM wip_operations_v
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id
AND department_code = 'OQA';
EXCEPTION
WHEN OTHERS THEN
l_oqa_qty := -1;
END;

IF l_oqa_qty <> -1 THEN
IF p_complete_qty > l_oqa_qty THEN
l_error_msgg := 'DJ Completed qty must be less than OQA qty.';
RAISE fnd_api.g_exc_error;
END IF;

ELSE
SELECT (start_quantity - quantity_completed -
quantity_scrapped)
INTO l_dj_open_qty
FROM wip_discrete_jobs
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id;
IF p_complete_qty > l_dj_open_qty THEN
l_error_msgg := 'DJ Completed qty must be less than open qty.';
RAISE fnd_api.g_exc_error;
END IF;

END IF;
--

SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO g_mmt_iface.transaction_header_id
FROM dual;
g_mmt_iface.organization_id := g_wmti_iface.organization_id;
g_mmt_iface.transaction_source_id := g_wmti_iface.wip_entity_id;
g_mmt_iface.department_id := NULL;
g_mmt_iface.transaction_uom := p_uom;
g_mmt_iface.subinventory_code := g_completion_subinventory;
g_mmt_iface.locator_id := l_locator_id;
g_mmt_iface.reason_id := NULL;
g_mmt_iface.transaction_quantity := p_complete_qty;
g_mmt_iface.final_completion_flag := 'N';
--get item id from wip
SELECT primary_item_id
INTO g_mmt_iface.inventory_item_id
FROM wip_discrete_jobs
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id;
--get complete seq
--SELECT MAX
SELECT MAX(operation_seq_num)
INTO g_mmt_iface.operation_seq_num
FROM wip_operations
WHERE wip_entity_id = g_wmti_iface.wip_entity_id
AND organization_id = g_wmti_iface.organization_id;
--get reversion
IF p_rev IS NULL THEN
SELECT revision
INTO g_mmt_iface.revision
FROM (SELECT revision
FROM mtl_item_revisions
WHERE inventory_item_id =
g_mmt_iface.inventory_item_id
AND organization_id =
g_wmti_iface.organization_id
AND effectivity_date <= SYSDATE
ORDER BY effectivity_date) a
WHERE rownum = 1;
ELSE
g_mmt_iface.revision := p_rev;
END IF;
dbms_output.put_line('IfaceID:' ||
g_mmt_iface.transaction_header_id);
--insert mmt interface
ins_mmt_interface(p_txn_type_id => 44,
p_source_type_id => 5,
p_source_code => 'WIP Assy Completion',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

--call online process
handle_mmt_iface(l_success_flag,
l_error_msgg,
'1');
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

ELSE
--setp 3 : call online process wip_move_txn_interface
handle_wmti_iface(l_success_flag,
l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

END IF;
--setp 4 : return success or failure message
o_success_flag := 'Y';
o_error_msgg := NULL;
x_subinventory := g_completion_subinventory;
x_locator := g_completion_locator;
--commit
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_msgg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_msgg := 'Unhandled exception:' || SQLERRM;
END process_wip_complete;
--wip complete,for osp
PROCEDURE wip_osp_complete
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_complete_qty IN NUMBER,
p_uom IN VARCHAR2,
p_rev IN VARCHAR2,
p_fm_seq_num IN NUMBER,
x_subinventory IN OUT VARCHAR2,
x_locator IN OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
BEGIN
g_wmti_iface.fm_operation_seq_num := p_fm_seq_num;

process_wip_complete(p_dj_name => p_dj_name,
p_organization_code => p_organization_code,
p_complete_qty => p_complete_qty,
p_uom => p_uom,
p_rev => p_rev,
x_subinventory => x_subinventory,
x_locator => x_locator,
o_success_flag => l_success_flag,
o_error_msgg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
o_success_flag := 'Y';
o_error_msgg := NULL;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_msgg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_msgg := 'Unhandled exception:' || SQLERRM;
END wip_osp_complete;

-- using move transaction return
PROCEDURE process_wip_return
(
p_dj_name IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_return_qty IN NUMBER,
p_uom IN VARCHAR2,
o_subinventory OUT VARCHAR2,
o_locator OUT VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_msgg OUT VARCHAR2
)

IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
BEGIN
l_success_flag := 'Y';
o_subinventory := NULL;
o_locator := NULL;
--setp 1 : validate data
validate_wip_complete(p_dj_name => p_dj_name,
p_org_code => p_organization_code,
p_uom => p_uom,
p_quantity => p_return_qty,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2 : insert wip_move_txn_interface interface
ins_move_txn_iface(p_txn_type => 'RETURN',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 3 : call online process wip_move_txn_interface
handle_wmti_iface(l_success_flag,
l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 4 : return success or failure message
o_success_flag := 'Y';
o_error_msgg := NULL;
o_subinventory := g_completion_subinventory;
o_locator := g_completion_locator;
--commit
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_msgg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_msgg := 'Unhandled exception:' || SQLERRM;
END process_wip_return;

--Get Wip component pick release
PROCEDURE get_morder_list
(
p_org_code IN VARCHAR2,
p_dj_name IN VARCHAR2,
o_cursor OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_organization_id NUMBER;
l_wip_entity_id NUMBER;
BEGIN
l_wip_entity_id := NULL;
l_organization_id := NULL;
SELECT organization_id
INTO l_organization_id
FROM org_organization_definitions
WHERE organization_code = p_org_code;

SELECT wip_entity_id
INTO l_wip_entity_id
FROM wip_entities we
WHERE we.wip_entity_name = p_dj_name
AND organization_id = l_organization_id;

OPEN o_cursor FOR
SELECT mtrh.request_number,
mfg2.meaning move_order_type_name,
mtrl.line_number,
mtt.transaction_type_name,
msi.concatenated_segments item_num,
msi.description item_desc,
mtrl.to_subinventory_code,
mil.concatenated_segments to_locator,
mtrl.revision,
mtrl.uom_code,
mtrl.quantity requested_qty,
mtrl.quantity_detailed allocated_qty,
mtrl.date_required,
mtrl.lot_number,
mtrl.serial_number_start,
mtrl.serial_number_end
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh,
mtl_transaction_types mtt,
mtl_secondary_inventories mss,
mtl_secondary_inventories mss1,
mfg_lookups mfg2,
mtl_system_items_kfv msi,
mtl_item_locations_kfv mil
WHERE mtrh.header_id = mtrl.header_id
AND mtt.transaction_type_id = mtrl.transaction_type_id
AND mss.secondary_inventory_name(+) =
mtrl.from_subinventory_code
AND mss.organization_id(+) = mtrl.organization_id
AND mss1.secondary_inventory_name(+) =
mtrl.to_subinventory_code
AND mss1.organization_id(+) = mtrl.organization_id
AND mfg2.lookup_type = 'MOVE_ORDER_TYPE'
AND mfg2.lookup_code = to_char(mtrh.move_order_type)
AND msi.inventory_item_id = mtrl.inventory_item_id
AND msi.organization_id = mtrl.organization_id
AND mil.organization_id(+) =
nvl(mtrl.organization_id,
-1)
AND mil.inventory_location_id(+) =
nvl(mtrl.to_locator_id,
-1)
AND mtrl.organization_id = l_organization_id
AND mtrl.txn_source_id = l_wip_entity_id --wip source
AND mtrh.move_order_type = '5' --Manufacturing Component Pick
AND mtrl.line_status IN ('3', '7', '9')
--Has been allocated
AND EXISTS
(SELECT 'x'
FROM mtl_material_transactions_temp mt
WHERE mt.move_order_line_id = mtrl.line_id);

EXCEPTION
WHEN no_data_found THEN
o_success_flag := 'N';
IF l_organization_id IS NULL THEN
o_error_mssg := 'Invalid organization code:' || p_org_code;
ELSE
o_error_mssg := 'Invalid DJ name:' || p_dj_name;
END IF;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception:' || SQLERRM;
END get_morder_list;
--reutrn move order lists belong to one DJ
PROCEDURE get_morders
(
p_org_code IN VARCHAR2,
p_dj_name IN VARCHAR2,
o_cursor OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_wip_entity_id NUMBER;
l_osp_flag VARCHAR2(1);
l_temp NUMBER;
BEGIN
l_org_id := get_organization_id(p_org_code);
IF l_org_id = -1 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid org code.';
OPEN o_cursor FOR
SELECT 'Invalid org code.' FROM dual;
RETURN;
END IF;

l_wip_entity_id := get_wip_entity_id(l_org_id,
p_dj_name);
IF l_wip_entity_id = -1 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid DJ name.';
OPEN o_cursor FOR
SELECT 'Invalid DJ name.' FROM dual;
RETURN;
END IF;

SELECT COUNT(1)
INTO l_temp
FROM wip_operations_v
WHERE wip_entity_id = l_wip_entity_id
AND department_code LIKE '%OSP%';

IF l_temp > 0 THEN
l_osp_flag := 'Y';
ELSE
l_osp_flag := 'N';
END IF;

OPEN o_cursor FOR
SELECT request_number,
creation_date,
l_osp_flag
FROM mtl_txn_request_headers h
WHERE h.header_status IN (3, 7, 9)
AND h.move_order_type = '5' --Manufacturing Component Pick
AND h.organization_id = l_org_id
AND EXISTS (SELECT 'x'
FROM mtl_txn_request_lines l
WHERE l.header_id = h.header_id
AND l.txn_source_id = l_wip_entity_id
--AND l.transaction_type_id = 51
AND l.line_status IN (3, 7, 9))
UNION ALL
--Manual Move Order.User transaction type
SELECT request_number,
creation_date,
l_osp_flag
FROM mtl_txn_request_headers h
WHERE h.header_status IN (3, 7, 9)
AND h.move_order_type = '1' --requisition
AND h.organization_id = l_org_id
AND EXISTS (SELECT 'x'
FROM mtl_txn_request_lines l,
mtl_material_transactions_temp mtt
WHERE l.header_id = h.header_id
AND mtt.move_order_line_id = l.line_id
AND l.reference = p_dj_name
AND l.line_status IN (3, 7, 9));

EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[get_morders]:' ||
SQLERRM;
OPEN o_cursor FOR
SELECT 'Unhandled exception' FROM dual;
END get_morders;
--
PROCEDURE get_morder_lines
(
p_org_code IN VARCHAR2,
p_move_order IN VARCHAR2,
o_cursor OUT cur_reference,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_header_id NUMBER;
BEGIN
l_org_id := get_organization_id(p_org_code);
IF l_org_id = -1 THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid org code.';
OPEN o_cursor FOR
SELECT 'Invalid org code.' FROM dual;
RETURN;
END IF;

SELECT header_id
INTO l_header_id
FROM mtl_txn_request_headers
WHERE request_number = p_move_order;

OPEN o_cursor FOR
SELECT msk.concatenated_segments item_number,
mlt.lot_number,
mtt.subinventory_code source_inv,
get_locator(mtt.locator_id,
l_org_id) source_loc,
get_supply_type(txn_source_id,
txn_source_line_id,
ml.inventory_item_id) supply_type,
ml.to_subinventory_code,
get_locator(ml.to_locator_id,
l_org_id) to_locator,
--SUM(ml.quantity) ml_quantity,
SUM(ml.quantity_detailed) allocation_quantity,
SUM(mtt.transaction_quantity) mmtt_quantity,
SUM(mlt.transaction_quantity) request_quantity
FROM mtl_txn_request_lines ml,
mtl_material_transactions_temp mtt,
mtl_transaction_lots_temp mlt,
mtl_system_items_kfv msk
WHERE mtt.move_order_line_id = ml.line_id
AND mtt.transaction_temp_id = mlt.transaction_temp_id(+)
AND ml.inventory_item_id = msk.inventory_item_id
AND ml.organization_id = msk.organization_id
AND ml.organization_id = l_org_id
AND ml.header_id = l_header_id
-- AND ml.transaction_type_id = 51 --Backflush
AND ml.line_status IN ('3', '7', '9')
GROUP BY msk.concatenated_segments,
mlt.lot_number,
get_supply_type(txn_source_id,
txn_source_line_id,
ml.inventory_item_id),
mtt.subinventory_code,
mtt.locator_id,
ml.to_subinventory_code,
ml.to_locator_id;

EXCEPTION
WHEN no_data_found THEN
o_success_flag := 'N';
o_error_mssg := 'Invalid org code or move order number.';
OPEN o_cursor FOR
SELECT 'Invalid org code or move order number.' FROM dual;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[get_morder_lines]:' ||
SQLERRM;
OPEN o_cursor FOR
SELECT 'Unhandled exception' FROM dual;
END get_morder_lines;
--
PROCEDURE get_item_desc
(
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
o_rev OUT VARCHAR2,
o_desc OUT VARCHAR2
) IS
l_item_desc mtl_system_items_kfv.description%TYPE;
l_rev VARCHAR2(10);
BEGIN
SELECT m.description,
get_item_last_rev(m.inventory_item_id,
m.organization_id)
INTO l_item_desc,
l_rev
FROM mtl_system_items_kfv m,
org_organization_definitions o
WHERE m.concatenated_segments = p_item_num
AND m.organization_id = o.organization_id
AND o.organization_code = p_org_code;
o_desc := l_item_desc;
o_rev := l_rev;
EXCEPTION
WHEN OTHERS THEN
o_desc := NULL;
o_rev := NULL;
END get_item_desc;
--
PROCEDURE misc_issue
(
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_tran_qty IN NUMBER,
p_rev IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator_id IN NUMBER,
p_exp_date IN DATE,
p_tran_uom IN VARCHAR2,
p_dist_ccid IN NUMBER,
x_error_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS

l_header_id NUMBER;
l_return_status VARCHAR2(1);
l_available_qty NUMBER;
l_index NUMBER := 1;
l_total_qty NUMBER;
l_msg_data VARCHAR2(1000);
l_iface_id NUMBER;

--save witch lot issued quantity
TYPE lot_qty IS RECORD(
lot_number VARCHAR2(30),
quantity NUMBER);
TYPE lotqty_tle IS TABLE OF lot_qty INDEX BY BINARY_INTEGER;
l_lot_tbl lotqty_tle;

CURSOR cur_data IS
SELECT lot_number,
SUM(transaction_quantity) moh_qty,
get_item_att_qty(p_item_id,
p_org_id,
p_subinv,
p_locator_id,
p_rev,
lot_number) ava_qty

FROM mtl_onhand_quantities_detail
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND subinventory_code = p_subinv
AND nvl(revision,
'@@') = nvl(p_rev,
nvl(revision,
'@@'))
AND nvl(locator_id,
-1) = nvl(p_locator_id,
nvl(locator_id,
-1))
GROUP BY lot_number,
get_item_att_qty(p_item_id,
p_org_id,
p_subinv,
p_locator_id,
p_rev,
lot_number);

BEGIN
x_error_flag := 'Y';
x_error_mssg := NULL;
l_total_qty := p_tran_qty;
--Validate AVQ whether enough
--All lot available quantity is enough
l_available_qty := get_item_att_qty(p_item_id,
p_org_id,
p_subinv,
p_locator_id,
p_rev);

dbms_output.put_line('Ava:' || l_available_qty);
dbms_output.put_line('Tl :' || l_total_qty);

dbms_output.put_line('p_item_id :' || p_item_id);
dbms_output.put_line('p_org_id :' || p_org_id);
dbms_output.put_line('p_subinv :' || p_subinv);
dbms_output.put_line('p_locator_id :' || p_locator_id);
dbms_output.put_line('p_rev :' || p_rev);

IF l_available_qty < l_total_qty THEN
x_error_flag := 'N';
x_error_mssg := 'Qty is not enough.';
RETURN;
END IF;
--allocation lot quantity to issue
FOR rec_data IN cur_data LOOP

IF rec_data.ava_qty <= l_total_qty THEN
l_lot_tbl(l_index).lot_number := rec_data.lot_number;
l_lot_tbl(l_index).quantity := rec_data.ava_qty;
l_total_qty := l_total_qty - rec_data.ava_qty;
--continue loop until eque 0
IF l_total_qty = 0 THEN
EXIT;
END IF;
ELSE
l_lot_tbl(l_index).lot_number := rec_data.lot_number;
l_lot_tbl(l_index).quantity := l_total_qty;
EXIT;
END IF;
l_index := l_index + 1;
END LOOP;
--Insert into mmt interface
SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;

FOR i IN l_lot_tbl.FIRST .. l_lot_tbl.LAST LOOP

SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO l_iface_id
FROM dual;
cux_mtl_tran_inte_pkg.ins_mtl_trans_inte(p_organization_id => p_org_id,
p_transaction_interface_id => l_iface_id,
p_transaction_header_id => l_header_id,
p_transaction_type_id => 32, --Miscellaneous issue
p_transaction_source_type_id => 13, --Inventory
p_transaction_uom => p_tran_uom,
p_transaction_date => SYSDATE,
p_source_code => 'eTrace',
p_source_line_id => -1,
p_source_header_id => -1,
p_process_flag => 1,
p_transaction_mode => 2,
p_lock_flag => 2,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_inventory_item_id => p_item_id,
p_revision => p_rev,
p_subinventory_code => p_subinv,
p_locator_id => p_locator_id,
p_distribution_account_id => p_dist_ccid,
p_transaction_quantity => (-1) *
l_lot_tbl(i)
.quantity,
p_reason_id => g_misc_reason_id,
p_transaction_reference => g_misc_refference);

--insert into lot interface
IF l_lot_tbl(i).lot_number IS NOT NULL THEN
cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => l_iface_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => l_lot_tbl(i)
.lot_number,
p_lot_expiration_date => p_exp_date,
p_transaction_quantity => (-1) *
l_lot_tbl(i)
.quantity);
END IF;
END LOOP;

g_mmt_iface.transaction_header_id := l_header_id;
handle_mmt_iface(l_return_status,
l_msg_data,
'1');
g_mmt_iface.transaction_header_id := NULL;
IF l_return_status <> 'Y' THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_issue],when posting.' ||
l_msg_data;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_issue]:' || SQLERRM;
END misc_issue;
--
--
PROCEDURE misc_issuea
(
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_tran_qty IN NUMBER,
p_rev IN VARCHAR2,
p_lot_num IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator_id IN NUMBER,
p_exp_date IN DATE,
p_tran_uom IN VARCHAR2,
p_dist_ccid IN NUMBER,
x_error_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS

l_header_id NUMBER;
l_return_status VARCHAR2(1);
l_available_qty NUMBER;
l_msg_data VARCHAR2(1000);
l_iface_id NUMBER;

BEGIN
x_error_flag := 'Y';
x_error_mssg := NULL;
--Validate AVQ whether enough
--All lot available quantity is enough
l_available_qty := get_item_att_qty(p_item_id,
p_org_id,
p_subinv,
p_locator_id,
p_rev);

IF l_available_qty < p_tran_qty THEN
x_error_flag := 'N';
x_error_mssg := 'Qty is not enough.';
RETURN;
END IF;

--Insert into mmt interface
SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;

SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO l_iface_id
FROM dual;
cux_mtl_tran_inte_pkg.ins_mtl_trans_inte(p_organization_id => p_org_id,
p_transaction_interface_id => l_iface_id,
p_transaction_header_id => l_header_id,
p_transaction_type_id => 32, --Miscellaneous issue
p_transaction_source_type_id => 13, --Inventory
p_transaction_uom => p_tran_uom,
p_transaction_date => SYSDATE,
p_source_code => 'eTrace',
p_source_line_id => -1,
p_source_header_id => -1,
p_process_flag => 1,
p_transaction_mode => 2,
p_lock_flag => 2,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_inventory_item_id => p_item_id,
p_revision => p_rev,
p_subinventory_code => p_subinv,
p_locator_id => p_locator_id,
p_distribution_account_id => p_dist_ccid,
p_transaction_quantity => (-1) *
p_tran_qty,
p_reason_id => g_misc_reason_id,
p_transaction_reference => g_misc_refference);

--insert into lot interface
IF p_lot_num IS NOT NULL THEN
cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => l_iface_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => p_lot_num,
p_lot_expiration_date => p_exp_date,
p_transaction_quantity => (-1) *
p_tran_qty);

END IF;
g_mmt_iface.transaction_header_id := l_header_id;
handle_mmt_iface(l_return_status,
l_msg_data,
'1');
g_mmt_iface.transaction_header_id := NULL;
IF l_return_status <> 'Y' THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_issue],when posting.' ||
l_msg_data;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_issue]:' || SQLERRM;
END misc_issuea;

PROCEDURE misc_receipt
(
p_org_id IN NUMBER,
p_item_id IN NUMBER,
p_tran_qty IN NUMBER,
p_rev IN VARCHAR2,
p_lot_num IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator_id IN NUMBER,
p_exp_date IN DATE,
p_tran_uom IN VARCHAR2,
p_dist_ccid IN NUMBER,
x_error_flag OUT VARCHAR2,
x_error_mssg OUT VARCHAR2
) IS

l_return_status VARCHAR2(1);
l_header_id NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN
x_error_flag := 'Y';
x_error_mssg := NULL;

--Insert into mmt interface
SELECT inv.mtl_material_transactions_s.NEXTVAL
INTO l_header_id
FROM dual;

cux_mtl_tran_inte_pkg.ins_mtl_trans_inte(p_organization_id => p_org_id,
p_transaction_interface_id => l_header_id,
p_transaction_header_id => l_header_id,
p_transaction_type_id => 42, --Miscellaneous receipt
p_transaction_source_type_id => 13, --Inventory
p_transaction_uom => p_tran_uom,
p_transaction_date => SYSDATE,
p_source_code => 'eTrace',
p_source_line_id => -1,
p_source_header_id => -1,
p_process_flag => 1,
p_transaction_mode => 2,
p_lock_flag => 2,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_inventory_item_id => p_item_id,
p_revision => p_rev,
p_subinventory_code => p_subinv,
p_locator_id => p_locator_id,
p_distribution_account_id => p_dist_ccid,
p_transaction_quantity => p_tran_qty,
p_reason_id => g_misc_reason_id,
p_transaction_reference => g_misc_refference);
--insert into lot interface
IF p_lot_num IS NOT NULL THEN

cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => l_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => p_lot_num,
p_lot_expiration_date => p_exp_date,
p_transaction_quantity => p_tran_qty);

END IF;

g_mmt_iface.transaction_header_id := l_header_id;
handle_mmt_iface(l_return_status,
l_msg_data,
'1');
g_mmt_iface.transaction_header_id := NULL;
IF l_return_status <> 'Y' THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_receipt],when posting.' ||
l_msg_data;
END IF;
EXCEPTION
WHEN OTHERS THEN
x_error_flag := 'N';
x_error_mssg := 'Error at [misc_receipt]:' || SQLERRM;
END misc_receipt;

--process componet return , PN transfer
--Step 1 : Misc issue via PN
--Step 2 : Misc receipt via PN,LotNum
PROCEDURE misc_issue_rcpt
(
p_org_code IN VARCHAR2,
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_lot_num IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_source_sub IN VARCHAR2,
p_source_loc IN VARCHAR2,
p_dest_sub IN VARCHAR2,
p_dest_loc IN VARCHAR2,
p_exp_date IN DATE,
p_reason IN VARCHAR2,
p_reference IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_item_id NUMBER;
l_error_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_exists NUMBER;
l_source_locid NUMBER;
l_dest_locid NUMBER;
l_exp_date DATE;

l_missue_account_id NUMBER;
l_mrec_account_id NUMBER;
l_expiration_date DATE;
l_lot_ctrl NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--Validate parameters
--validate org code,return org id
l_org_id := get_organization_id(p_org_code);
IF l_org_id = -1 THEN
l_error_msgg := 'Invalid Org code.';
RAISE fnd_api.g_exc_error;
END IF;
--validate item number
l_item_id := get_item_id(p_item_num,
l_org_id);
IF l_item_id = -1 THEN
l_error_msgg := 'Invalid PN.';
RAISE fnd_api.g_exc_error;
END IF;
--validate lot number
--get item lot control code
SELECT nvl(lot_control_code,
1)
INTO l_lot_ctrl
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id;
dbms_output.put_line(l_lot_ctrl);
IF p_lot_num IS NOT NULL THEN
IF l_lot_ctrl = 2 THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE lot_number = p_lot_num
AND inventory_item_id = l_item_id
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid Lot No.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
ELSE
IF l_lot_ctrl = 2 THEN
l_error_msgg := 'Must enter lot No.';
RAISE fnd_api.g_exc_error;
END IF;

END IF;

--validate transction quantity
IF p_quantity IS NULL OR
p_quantity <= 0 THEN
l_error_msgg := 'Invalid Quantity.';
RAISE fnd_api.g_exc_error;
END IF;
--validate sub
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_source_sub
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid source subinventory.';
RAISE fnd_api.g_exc_error;
END IF;

SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_dest_sub
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid source subinventory.';
RAISE fnd_api.g_exc_error;
END IF;
--validate locator
IF p_source_loc IS NOT NULL THEN
l_source_locid := get_locator_id(p_source_loc,
p_source_sub,
l_org_id);
IF l_source_locid = -1 THEN
l_error_msgg := 'Invalid source loaction.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
IF p_dest_loc IS NOT NULL THEN
l_dest_locid := get_locator_id(p_dest_loc,
p_dest_sub,
l_org_id);
IF l_dest_locid = -1 THEN
l_error_msgg := 'Invalid dest loaction.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;

/*BEGIN
l_exp_date := fnd_date.canonical_to_date(p_exp_date);
dbms_output.put_line('Date:' || l_exp_date);
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid exp date.';
RAISE fnd_api.g_exc_error;
END;*/
l_exp_date := p_exp_date;
--validate reason
IF p_reason IS NOT NULL THEN
BEGIN
SELECT r.reason_id
INTO g_misc_reason_id
FROM mtl_transaction_reasons r
WHERE r.reason_name = p_reason;
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid Reason:' || p_reason;
RAISE fnd_api.g_exc_error;
END;
END IF;
IF p_reference IS NOT NULL THEN
g_misc_refference := substr(p_reference,
1,
240);
END IF;

--end Validate
--Misc issue
l_missue_account_id := 79730; --testing data
misc_issue(p_org_id => l_org_id,
p_item_id => l_item_id,
p_tran_qty => p_quantity,
p_rev => p_item_rev,
p_subinv => p_source_sub,
p_locator_id => l_source_locid,
p_exp_date => l_exp_date,
p_tran_uom => p_uom,
p_dist_ccid => l_missue_account_id,
x_error_flag => l_error_flag,
x_error_mssg => l_error_msgg);
IF l_error_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

--Misc receipt
l_mrec_account_id := 74370; --testing data
misc_receipt(p_org_id => l_org_id,
p_item_id => l_item_id,
p_tran_qty => p_quantity,
p_rev => p_item_rev,
p_lot_num => p_lot_num,
p_subinv => p_dest_sub,
p_locator_id => l_dest_locid,
p_exp_date => l_exp_date,
p_tran_uom => p_uom,
p_dist_ccid => l_mrec_account_id,
x_error_flag => l_error_flag,
x_error_mssg => l_error_msgg);
IF l_error_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
IF p_lot_num IS NOT NULL THEN
--validate expiration date
SELECT expiration_date
INTO l_expiration_date
FROM mtl_lot_numbers
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND lot_number = p_lot_num
FOR UPDATE NOWAIT;
--You can use API update expiration date also
IF l_expiration_date <> l_exp_date THEN
UPDATE mtl_lot_numbers
SET expiration_date = l_exp_date
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND lot_number = p_lot_num;
END IF;
END IF;
EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[misc_issue_rcpt]:' ||
SQLERRM;
END misc_issue_rcpt;
--
PROCEDURE pn_transfer
(
p_org_code IN VARCHAR2,
p_item_num_a IN VARCHAR2,
p_item_rev_a IN VARCHAR2 DEFAULT NULL,
p_lot_num_a IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_sub_a IN VARCHAR2,
p_loc_a IN VARCHAR2,
p_item_num_b IN VARCHAR2,
p_item_rev_b IN VARCHAR2 DEFAULT NULL,
--p_lot_num_b IN VARCHAR2,
p_sub_b IN VARCHAR2,
p_loc_b IN VARCHAR2,
p_exp_date IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_item_id_a NUMBER;
l_item_id_b NUMBER;
l_error_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_exists NUMBER;
l_locid_a NUMBER;
l_locid_b NUMBER;
l_exp_date DATE;

l_missue_account_id NUMBER;
l_mrec_account_id NUMBER;
l_lot_ctrl NUMBER;
l_rev_ctrl NUMBER;
l_item_rev_a VARCHAR2(10);
l_item_rev_b VARCHAR2(10);
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--Validate parameters
--validate org code,return org id
l_org_id := get_organization_id(p_org_code);
IF l_org_id = -1 THEN
l_error_msgg := 'Invalid Org code.';
RAISE fnd_api.g_exc_error;
END IF;
--validate item number
l_item_id_a := get_item_id(p_item_num_a,
l_org_id);
IF l_item_id_a = -1 THEN
l_error_msgg := 'Invalid PN of a.' || p_item_num_a;
RAISE fnd_api.g_exc_error;
END IF;

l_item_id_b := get_item_id(p_item_num_b,
l_org_id);
IF l_item_id_a = -1 THEN
l_error_msgg := 'Invalid PN of b.' || p_item_num_b;
RAISE fnd_api.g_exc_error;
END IF;
--get item lot control code
SELECT nvl(lot_control_code,
1),
nvl(revision_qty_control_code,
1)
INTO l_lot_ctrl,
l_rev_ctrl
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id_a;
--validate lot number
IF p_lot_num_a IS NOT NULL AND
l_lot_ctrl = 2 THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE lot_number = p_lot_num_a
AND inventory_item_id = l_item_id_a
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid Lot No.';
RAISE fnd_api.g_exc_error;
END IF;
ELSE

IF l_lot_ctrl = 2 THEN
l_error_msgg := 'Must enter lot No.';
RAISE fnd_api.g_exc_error;
END IF;

END IF;
--validate transction quantity
IF p_quantity IS NULL OR
p_quantity <= 0 THEN
l_error_msgg := 'Invalid Quantity.';
RAISE fnd_api.g_exc_error;
END IF;
--validate sub
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_sub_a
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid subinventory.';
RAISE fnd_api.g_exc_error;
END IF;
--validate locator
IF p_loc_a IS NOT NULL THEN
l_locid_a := get_locator_id(p_loc_a,
p_sub_a,
l_org_id);
IF l_locid_a = -1 THEN
l_error_msgg := 'Invalid source loaction.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--validate sub
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_sub_b
AND organization_id = l_org_id;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid subinventory.';
RAISE fnd_api.g_exc_error;
END IF;
--validate locator
IF p_loc_b IS NOT NULL THEN
l_locid_b := get_locator_id(p_loc_b,
p_sub_b,
l_org_id);
IF l_locid_b = -1 THEN
l_error_msgg := 'Invalid destination loaction.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;

BEGIN
l_exp_date := fnd_date.canonical_to_date(p_exp_date);
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid exp date.';
RAISE fnd_api.g_exc_error;
END;
--end Validate
--
IF l_rev_ctrl = 2 THEN
IF p_item_rev_a IS NULL THEN
l_error_msgg := 'Invalid Rev.';
RAISE fnd_api.g_exc_error;
ELSE
l_item_rev_a := p_item_rev_a;
END IF;
ELSE
l_item_rev_a := NULL;
END IF;
--
--Misc issue
l_missue_account_id := 79730; --testing data
misc_issuea(p_org_id => l_org_id,
p_item_id => l_item_id_a,
p_tran_qty => p_quantity,
p_rev => l_item_rev_a,
p_lot_num => p_lot_num_a,
p_subinv => p_sub_a,
p_locator_id => l_locid_a,
p_exp_date => l_exp_date,
p_tran_uom => p_uom,
p_dist_ccid => l_missue_account_id,
x_error_flag => l_error_flag,
x_error_mssg => l_error_msgg);
IF l_error_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--Misc receipt
--get item lot control code
SELECT nvl(revision_qty_control_code,
1)
INTO l_rev_ctrl
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id_b;
IF l_rev_ctrl = 2 THEN
IF p_item_rev_b IS NULL THEN
l_error_msgg := 'Invalid Rev.';
RAISE fnd_api.g_exc_error;
ELSE
l_item_rev_b := p_item_rev_b;
END IF;
ELSE
l_item_rev_b := NULL;
END IF;

l_mrec_account_id := 74370; --testing data
misc_receipt(p_org_id => l_org_id,
p_item_id => l_item_id_b,
p_tran_qty => p_quantity,
p_rev => l_item_rev_b,
p_lot_num => p_lot_num_a,
p_subinv => p_sub_b,
p_locator_id => l_locid_b,
p_exp_date => l_exp_date,
p_tran_uom => p_uom,
p_dist_ccid => l_mrec_account_id,
x_error_flag => l_error_flag,
x_error_mssg => l_error_msgg);
IF l_error_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[misc_issue_rcpt]:' ||
SQLERRM;
END pn_transfer;

--process move order transact
PROCEDURE process_move_order
(
p_org_code IN VARCHAR2,
p_move_order IN VARCHAR2,
p_item_num IN VARCHAR2,
p_lot_number IN VARCHAR2,
p_quantity IN NUMBER,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_organization_id NUMBER;
l_item_id NUMBER;
l_header_id NUMBER;
l_locator_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--validate
validate_morder(p_org_code => p_org_code,
p_req_number => p_move_order,
p_item_number => p_item_num,
p_inventory => p_subinv,
p_locator => p_locator,
o_organization_id => l_organization_id,
o_item_id => l_item_id,
o_header_id => l_header_id,
o_locator_id => l_locator_id,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
IF p_quantity IS NULL OR
p_quantity <= 0 THEN
l_error_msgg := 'Invalid transaction quantity.';
RAISE fnd_api.g_exc_error;
END IF;
--setup mmt , allcate quantity and transact move order

allocation_quantity(p_header_id => l_header_id,
p_org_id => l_organization_id,
p_item_id => l_item_id,
p_lot_num => p_lot_number,
p_quantity => p_quantity,
p_inventory => p_subinv,
p_locator_id => l_locator_id,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;

EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[process_move_order]:' ||
SQLERRM;
END process_move_order;
--Validate sub and locator
PROCEDURE valid_subinvloc
(
p_org_code IN VARCHAR2,
p_subinv IN VARCHAR2,
p_locator IN VARCHAR2,
o_flag OUT VARCHAR2,
o_msg OUT VARCHAR2
) IS
l_org_id NUMBER;
l_exists NUMBER;
l_locator_type NUMBER;
BEGIN

l_org_id := get_organization_id(p_org_code);
IF l_org_id = -1 THEN
o_flag := 'N';
o_msg := 'Invalid Organization Code.' || p_org_code;
RETURN;
END IF;

IF p_locator IS NULL THEN
--Validate sub
BEGIN
SELECT locator_type
INTO l_locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv
AND organization_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
o_flag := 'E';
o_msg := 'Invalid subinventory.';
RETURN;
END;
IF l_locator_type = 2 THEN
o_flag := 'Y';
o_msg := NULL;
RETURN;
ELSE
o_flag := 'N';
o_msg := NULL;
RETURN;
END IF;

ELSE
SELECT COUNT(1)
INTO l_exists
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_locator
AND subinventory_code = p_subinv
AND organization_id = l_org_id;
IF l_exists = 0 THEN
o_flag := 'E';
o_msg := 'Invalid Locator.' || p_locator;
RETURN;
ELSE
o_flag := 'Y';
o_msg := NULL;
RETURN;
END IF;

END IF;

END valid_subinvloc;
--validate org transfer
PROCEDURE validate_orgtrsfer
(
p_org_code_fm IN VARCHAR2,
p_org_code_to IN VARCHAR2,
p_item_number IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_lotnumber IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_reason IN VARCHAR2,
p_subinv_fm IN VARCHAR2,
p_locator_fm IN VARCHAR2,
p_subinv_to IN VARCHAR2,
p_locator_to IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_item_id NUMBER;
l_error_msgg VARCHAR2(2000);
l_org_id_fm NUMBER;
l_org_id_to NUMBER;
l_exists NUMBER;
l_reason_id NUMBER;
l_fmlocator_id NUMBER;
l_tolocator_id NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--validate quantity
IF p_quantity <= 0 THEN
l_error_msgg := 'Invalid transaction quantity.';
RAISE fnd_api.g_exc_error;
END IF;
--validate uom code
SELECT COUNT(1)
INTO l_exists
FROM mtl_units_of_measure
WHERE uom_code = p_uom;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid UOM code.';
RAISE fnd_api.g_exc_error;
END IF;
--validate inter-org transfer (shipping networks)
l_org_id_fm := get_organization_id(p_org_code_fm);
IF l_org_id_fm = -1 THEN
l_error_msgg := 'Invalid source org code.';
RAISE fnd_api.g_exc_error;
END IF;
l_org_id_to := get_organization_id(p_org_code_to);
IF l_org_id_fm = -1 THEN
l_error_msgg := 'Invalid destination org code.';
RAISE fnd_api.g_exc_error;
END IF;
SELECT COUNT(1)
INTO l_exists
FROM org_organization_definitions orgg,
mtl_parameters mp,
mfg_lookups lu,
mtl_interorg_parameters mip
WHERE orgg.organization_id = mp.organization_id
AND mp.organization_id = mip.to_organization_id
AND mip.from_organization_id = l_org_id_fm
AND orgg.organization_id = l_org_id_to
AND nvl(orgg.disable_date,
SYSDATE + 1) > SYSDATE
AND mip.internal_order_required_flag = 2
AND lu.lookup_code = mip.intransit_type
AND lu.lookup_type = 'ORG_INTRANSIT';

IF l_exists = 0 THEN
l_error_msgg := 'Invalid shipping networks.';
RAISE fnd_api.g_exc_error;
END IF;
--
--validate item
l_item_id := get_item_id(p_item_number,
l_org_id_fm);
IF l_item_id = -1 THEN
l_error_msgg := 'Invalid item:' || p_item_number || ',' ||
p_org_code_fm;
RAISE fnd_api.g_exc_error;
END IF;
l_item_id := get_item_id(p_item_number,
l_org_id_to);
IF l_item_id = -1 THEN
l_error_msgg := 'Invalid item:' || p_item_number || ',' ||
p_org_code_to;
RAISE fnd_api.g_exc_error;
END IF;
--validate item rev
IF p_item_rev IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_item_revisions_b
WHERE organization_id = l_org_id_fm
AND inventory_item_id = l_item_id
AND revision = p_item_rev;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid item revision.';
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--validate reason code
IF p_reason IS NOT NULL THEN
BEGIN
SELECT r.reason_id
INTO l_reason_id
FROM mtl_transaction_reasons r
WHERE r.reason_name = p_reason;
EXCEPTION
WHEN OTHERS THEN
l_error_msgg := 'Invalid Reason:' || p_reason;
RAISE fnd_api.g_exc_error;
END;
ELSE
l_reason_id := NULL;
END IF;
--validate lotnumber
IF p_lotnumber IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id_fm
AND lot_number = p_lotnumber;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid lot number.' || p_lotnumber;
RAISE fnd_api.g_exc_error;
END IF;
END IF;
--validate subinventory and locator
--Validate sub
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv_fm
AND organization_id = l_org_id_fm;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid Subinventory.' || p_subinv_fm;
RAISE fnd_api.g_exc_error;
END IF;
--Validate locator
IF p_locator_fm IS NOT NULL THEN
l_fmlocator_id := get_locator_id(p_locator_fm,
p_subinv_fm,
l_org_id_fm);
IF l_fmlocator_id = -1 THEN
l_error_msgg := 'Invalid Locator.' || p_locator_fm;
RAISE fnd_api.g_exc_error;
END IF;
ELSE
l_fmlocator_id := NULL;
END IF;
--
SELECT COUNT(1)
INTO l_exists
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv_to
AND organization_id = l_org_id_to;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid Subinventory.' || p_subinv_to;
RAISE fnd_api.g_exc_error;
END IF;
--
IF p_locator_to IS NOT NULL THEN
l_tolocator_id := get_locator_id(p_locator_to,
p_subinv_to,
l_org_id_to);
IF l_tolocator_id = -1 THEN
l_error_msgg := 'Invalid Locator.' || p_locator_to;
RAISE fnd_api.g_exc_error;
END IF;
ELSE
l_tolocator_id := NULL;
END IF;
--initialize global interface data

g_mmt_iface.organization_id := l_org_id_fm;
g_mmt_iface.transaction_uom := p_uom;
g_mmt_iface.inventory_item_id := l_item_id;
g_mmt_iface.revision := p_item_rev;
g_mmt_iface.subinventory_code := p_subinv_fm;
g_mmt_iface.locator_id := l_fmlocator_id;
g_mmt_iface.transaction_quantity := p_quantity;
g_mmt_iface.reason_id := l_reason_id;
g_mmt_iface.transaction_reference := NULL;

g_mmt_iface.transfer_subinventory := p_subinv_to;
g_mmt_iface.transfer_organization := l_org_id_to;
g_mmt_iface.transfer_locator := l_tolocator_id;
--get acct_period_id
SELECT acct_period_id
INTO g_mmt_iface.acct_period_id
FROM org_acct_periods_v
WHERE organization_id = 2298
AND SYSDATE BETWEEN start_date AND end_date;
--
o_success_flag := 'Y';
o_error_mssg := NULL;

EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[validate_orgtrsfer]:' ||
SQLERRM;
END validate_orgtrsfer;

--process inter-org transfer transaction
PROCEDURE org_transfer
(
p_item_num IN VARCHAR2,
p_item_rev IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_lotnum IN VARCHAR2,
p_reason IN VARCHAR2,
p_org_code_fm IN VARCHAR2,
p_subinv_fm IN VARCHAR2,
p_locator_fm IN VARCHAR2,
p_org_code_to IN VARCHAR2,
p_subinv_to IN VARCHAR2,
p_locator_to IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_success_flag VARCHAR2(1);
l_error_msgg VARCHAR2(2000);
l_exists NUMBER;
BEGIN
o_success_flag := 'Y';
o_error_mssg := NULL;
--validate
validate_orgtrsfer(p_org_code_fm => p_org_code_fm,
p_org_code_to => p_org_code_to,
p_item_number => p_item_num,
p_item_rev => p_item_rev,
p_lotnumber => p_lotnum,
p_quantity => p_quantity,
p_uom => p_uom,
p_reason => p_reason,
p_subinv_fm => p_subinv_fm,
p_locator_fm => p_locator_fm,
p_subinv_to => p_subinv_to,
p_locator_to => p_locator_to,
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2 : insert mmt interface
ins_mmt_interface(p_txn_type_id => 3, --inter-org transfer
p_source_type_id => 13,
p_source_code => 'Inter-Org Transfer',
o_success_flag => l_success_flag,
o_error_mssg => l_error_msgg);
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 2.1 : insert mlt interface
--insert into lot interface
IF p_lotnum IS NOT NULL THEN
SELECT COUNT(1)
INTO l_exists
FROM mtl_lot_numbers
WHERE inventory_item_id = g_mmt_iface.inventory_item_id
AND organization_id = g_mmt_iface.organization_id
AND lot_number = p_lotnum;
IF l_exists = 0 THEN
l_error_msgg := 'Invalid lot number.' || p_lotnum;
RAISE fnd_api.g_exc_error;
END IF;
cux_mtl_tran_inte_pkg.ins_lot_iface(p_transaction_interface_id => g_mmt_iface.transaction_header_id,
p_last_update_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_created_by => fnd_global.user_id,
p_lot_number => p_lotnum,
p_transaction_quantity => g_mmt_iface.transaction_quantity);
END IF;
--setp 3 : call online process
handle_mmt_iface(l_success_flag,
l_error_msgg,
'1');
IF l_success_flag <> 'Y' THEN
RAISE fnd_api.g_exc_error;
END IF;
--setp 4 : return success or failure message
o_success_flag := 'Y';
o_error_mssg := NULL;

EXCEPTION
WHEN fnd_api.g_exc_error THEN
o_success_flag := 'N';
o_error_mssg := l_error_msgg;
WHEN OTHERS THEN
o_success_flag := 'N';
o_error_mssg := 'Unhandled exception[process_move_order]:' ||
SQLERRM;
END org_transfer;
--get item information
PROCEDURE get_item_master
(
p_item_num IN VARCHAR2,
p_org_code IN VARCHAR2,
o_cursor OUT cur_reference
) IS
BEGIN
OPEN o_cursor FOR
SELECT msi.description item_desc,
mic.segment10 commdity_code,
get_item_last_rev(msi.inventory_item_id,
msi.organization_id) item_rev,
msi.primary_uom_code uom_code,
msi.receiving_routing_id routing_id,
NULL item_msl,
NULL item_temp_ctrl
FROM mtl_system_items_kfv msi,
mtl_item_categories_v mic,
org_organization_definitions org
WHERE msi.inventory_item_id = mic.inventory_item_id(+)
AND msi.organization_id = mic.organization_id(+)
AND mic.category_set_name(+) = 'EMR COMMODITY'
AND msi.organization_id = org.organization_id
AND msi.concatenated_segments = p_item_num
AND org.organization_code = p_org_code;
EXCEPTION
WHEN OTHERS THEN
OPEN o_cursor FOR
SELECT 'Error' FROM dual;
END get_item_master;

PROCEDURE validate_revision
(
p_item_num IN VARCHAR2,
p_org_code IN VARCHAR2,
p_item_rev IN VARCHAR2,
o_success_flag OUT VARCHAR2,
o_error_mssg OUT VARCHAR2
) IS
l_rev_ctrl NUMBER;
l_exists NUMBER;
BEGIN
IF p_item_rev IS NULL THEN
BEGIN
SELECT revision_qty_control_code
INTO l_rev_ctrl
FROM mtl_system_items_b msi,
org_organization_definitions o
WHERE o.organization_id = msi.organization_id
AND o.organization_code = p_org_code
AND msi.segment1 = p_item_num;
EXCEPTION
WHEN OTHERS THEN
o_success_flag := 'E';
o_error_mssg := 'Invalid item num or org code.';
RETURN ;
END;
IF l_rev_ctrl = 2 THEN
o_success_flag := 'Y';
o_error_mssg := NULL;
ELSE
o_success_flag := 'N';
o_error_mssg := NULL;
END IF;
ELSE
SELECT COUNT(1)
INTO l_exists
FROM mtl_item_revisions_b mb,
org_organization_definitions o,
mtl_system_items_b msi
WHERE mb.organization_id = msi.organization_id
AND mb.inventory_item_id = msi.inventory_item_id
AND mb.revision = p_item_rev
AND o.organization_code = p_org_code
AND msi.segment1 = p_item_num
AND msi.revision_qty_control_code = 2;
IF l_exists = 0 THEN
o_success_flag := 'E';
o_error_mssg := 'Invalid item revision.';
ELSE
o_success_flag := 'Y';
o_error_mssg := NULL;
END IF;
END IF;

END validate_revision;

END cux_wip_pkg;