物料事务处理来源

1.通过标准API实现：

SELECT inv_object_genealogy.getsource(mmt.organization_id,
mmt.transaction_source_type_id,
mmt.transaction_source_id)
FROM mtl_material_transactions mmt
WHERE mmt.organization_id = 121
AND mmt.transaction_id = 5360398;

2.通过自定义方法实现：
FUNCTION get_txn_source(p_transaction_id IN NUMBER) RETURN VARCHAR2 IS
g_debug_mode VARCHAR2(120) := ‘Y’;
po CONSTANT NUMBER := 1;
sales_order CONSTANT NUMBER := 2;
account CONSTANT NUMBER := 3;
move_order CONSTANT NUMBER := 4;
wip_job_or_schedule CONSTANT NUMBER := 5;
account_alias CONSTANT NUMBER := 6;
requisition CONSTANT NUMBER := 7;
internal_order CONSTANT NUMBER := 8;
cycle_count CONSTANT NUMBER := 9;
physical_inventory CONSTANT NUMBER := 10;
cost_update CONSTANT NUMBER := 11;
rma CONSTANT NUMBER := 12;
inventory CONSTANT NUMBER := 13;
–Layer_cost_update CONSTANT NUMBER := 15;
prjcontracts CONSTANT NUMBER := 16;
v_process_phase VARCHAR2(30);
n_organization_id NUMBER;
n_txn_source_type_id NUMBER;
n_txn_source_id NUMBER;
v_txn_source_name mtl_material_transactions.transaction_source_name%TYPE;
x_txn_source VARCHAR2(120);
BEGIN
v_process_phase := ‘Fetch txn infomation’;
– get transaction information
SELECT mmt.organization_id
,mmt.transaction_source_type_id
,mmt.transaction_source_id
,mmt.transaction_source_name
INTO n_organization_id
,n_txn_source_type_id
,n_txn_source_id
,v_txn_source_name
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_transaction_id;
IF n_txn_source_type_id = cost_update THEN
v_process_phase := ‘Cost Update’;
SELECT description
INTO x_txn_source
FROM cst_cost_updates
WHERE cost_update_id = n_txn_source_id;
ELSIF n_txn_source_type_id = cycle_count THEN
v_process_phase := ‘Cycle Count’;
SELECT cycle_count_header_name
INTO x_txn_source
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = n_txn_source_id
AND organization_id = n_organization_id;
ELSIF (n_txn_source_type_id = inventory OR n_txn_source_type_id >= 100) THEN
v_process_phase := ‘Inventory’;
x_txn_source := v_txn_source_name;
ELSIF n_txn_source_type_id = physical_inventory THEN
v_process_phase := ‘Physical Inventory’;
SELECT physical_inventory_name
INTO x_txn_source
FROM mtl_physical_inventories
WHERE physical_inventory_id = n_txn_source_id
AND organization_id = n_organization_id;
ELSIF n_txn_source_type_id = po THEN
v_process_phase := ‘PO’;
SELECT nvl(poh.segment1,
poh.segment1)
INTO x_txn_source
FROM po_headers_all poh
WHERE poh.po_header_id = n_txn_source_id;
ELSIF n_txn_source_type_id = prjcontracts THEN
v_process_phase := ‘PrjContracts’;
SELECT contract_number
INTO x_txn_source
FROM okc_k_headers_b
WHERE id = n_txn_source_id;
ELSIF n_txn_source_type_id = requisition THEN
v_process_phase := ‘Requisition’;
SELECT segment1
INTO x_txn_source
FROM po_requisition_headers_all
WHERE requisition_header_id = n_txn_source_id;
ELSIF n_txn_source_type_id = wip_job_or_schedule THEN
v_process_phase := ‘WIP Job or Schedule’;
SELECT wip_entity_name
INTO x_txn_source
FROM wip_entities
WHERE wip_entity_id = n_txn_source_id
AND organization_id = n_organization_id;
ELSIF n_txn_source_type_id = move_order THEN
v_process_phase := ‘Move Order’;
SELECT request_number
INTO x_txn_source
FROM mtl_txn_request_headers
WHERE header_id = n_txn_source_id
AND organization_id = n_organization_id;
ELSIF ((n_txn_source_type_id = sales_order) OR
(n_txn_source_type_id = internal_order) OR
(n_txn_source_type_id = rma)) THEN
v_process_phase := ‘Sales Order’;
SELECT concatenated_segments
INTO x_txn_source
FROM mtl_sales_orders_kfv
WHERE sales_order_id = n_txn_source_id;
ELSIF n_txn_source_type_id = account_alias THEN
v_process_phase := ‘Account Alias’;
SELECT concatenated_segments
INTO x_txn_source
FROM mtl_generic_dispositions_kfv
WHERE disposition_id = n_txn_source_id;
ELSIF n_txn_source_type_id = account THEN
v_process_phase := ‘Account’;
SELECT concatenated_segments
INTO x_txn_source
FROM gl_code_combinations_kfv
WHERE code_combination_id = n_txn_source_id;
ELSE
v_process_phase := ‘Others’;
x_txn_source := NULL;
END IF;
RETURN x_txn_source;
EXCEPTION
WHEN no_data_found OR too_many_rows THEN
IF g_debug_mode = ‘Y’ THEN
dbms_output.put_line('GET_TXN_SOURCE: ’ || SQLERRM);
dbms_output.put_line('Process phase : ’ || v_process_phase);
END IF;
RETURN NULL;
END;