oracle的opm,Oracle EBS OPM 事务处理

--事务处理

--created by jenrry

DECLARE

l_iface_rec inv.mtl_transactions_interface%ROWTYPE;

l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE;

l_cur_mfg_org_id NUMBER := 127; --Current Inv Organization

l_user_id NUMBER := 0; --User ID, Sysadmin here

l_outcome BOOLEAN;

l_timeout NUMBER := 100;

l_error_code VARCHAR2(4000);

l_error_explanation VARCHAR2(4000);

BEGIN

fnd_global.apps_initialize (user_id => l_user_id,

resp_id => 51010,

resp_appl_id => 553

);

fnd_profile.put ('AFLOG_LEVEL', '1');

l_iface_rec.last_update_date := SYSDATE;

l_iface_rec.last_updated_by := l_user_id;

l_iface_rec.creation_date := SYSDATE;

l_iface_rec.created_by := l_user_id;

l_iface_rec.last_update_login := -1;

l_iface_lot_rec.last_update_date := SYSDATE;

l_iface_lot_rec.last_updated_by := l_user_id;

l_iface_lot_rec.creation_date := SYSDATE;

l_iface_lot_rec.created_by := l_user_id;

l_iface_lot_rec.last_update_login := -1;

SELECT mtl_material_transactions_s.NEXTVAL INTO l_iface_rec.transaction_interface_id FROM dual;

l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id;

l_iface_rec.transaction_mode := 3;

l_iface_rec.process_flag := 1;

l_iface_rec.transaction_type_id := 35; --mtl_transaction_types

l_iface_rec.transaction_source_id := 408953; --wip_entity_id

l_iface_rec.wip_entity_type := 10;

--

l_iface_rec.organization_id := l_cur_mfg_org_id;

l_iface_rec.inventory_item_id := 623;

l_iface_rec.subinventory_code := 'Y02';

l_iface_rec.transaction_quantity := -1;

l_iface_rec.primary_quantity := -1;

l_iface_rec.transaction_uom := '个';

l_iface_rec.transaction_date := SYSDATE ;

l_iface_rec.source_code := 'Test Only';

l_iface_rec.source_header_id := 987654321;

l_iface_rec.source_line_id := 987654321;

l_iface_rec.locator_id := 3;

INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec;

l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id;

l_iface_lot_rec.lot_number := '201308-001';

l_iface_lot_rec.transaction_quantity := l_iface_rec.transaction_quantity;

l_iface_lot_rec.source_code := l_iface_rec.source_code;

l_iface_lot_rec.source_line_id := l_iface_rec.source_line_id;

INSERT INTO inv.mtl_transaction_lots_interface VALUES l_iface_lot_rec;

l_timeout := 100;

l_outcome := mtl_online_transaction_pub.process_online(p_transaction_header_id => l_iface_rec.transaction_header_id,

p_timeout => l_timeout,

p_error_code => l_error_code,

p_error_explanation => l_error_explanation);

IF (l_outcome = FALSE) THEN

dbms_output.put_line('Failed to process the transaction');

dbms_output.put_line('Error code: ' || l_error_code);

dbms_output.put_line('Error message: ' || l_error_explanation);

DELETE inv.mtl_transactions_interface

WHERE transaction_header_id = l_iface_rec.transaction_header_id;

COMMIT;

ELSE

dbms_output.put_line('Transaction with header id ' ||

to_char(l_iface_rec.transaction_header_id) ||

' has been processed successfully');

COMMIT;

END IF;

END;