CREATE OR REPLACE FUNCTION cux_trans_source(p_trans_id NUMBER) RETURN VARCHAR2 IS
ln_type_id NUMBER;
ln_source_line_id NUMBER;
ln_trx_source_line_id NUMBER;
ln_source_type_id NUMBER;
ln_transaction_source_id NUMBER;
ls_type_name VARCHAR2(80);
ls_result VARCHAR2(80);
BEGIN
SELECT mmt.transaction_type_id,
mmt.source_line_id,
mtt.transaction_type_name,
mmt.trx_source_line_id,
mmt.transaction_source_type_id,
mmt.transaction_source_id
INTO ln_type_id,
ln_source_line_id,
ls_type_name,
ln_trx_source_line_id,
ln_source_type_id,
ln_transaction_source_id
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt
WHERE mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.transaction_id = p_trans_id;
IF ln_source_type_id = 1 THEN
IF ln_type_id = 18 THEN
--接收入库
SELECT '采购订单编号' || '.' || ph.segment1 ||
nvl(ph.comments, ' no comments')
INTO ls_result
FROM rcv_transactions rt,
po_headers_all ph
WHERE rt.transaction_id = ln_source_line_id
AND rt.po_header_id = ph.po_header_id;
END IF;
IF ln_type_id = 71 THEN
--交货调整
SELECT '采购订单' || '.' || ph.segment1 ||
nvl(ph.comments, ' no comments')
INTO ls_result
FROM rcv_transactions rt,
po_headers_all ph
WHERE rt.transaction_id = ln_source_line_id
AND rt.po_header_id = ph.po_header_id;
END IF;
IF ln_type_id = 36 THEN
--退货
SELECT '采购订单' || '.' || ph.segment1 ||
nvl(ph.comments, ' no comments')
INTO ls_result
FROM rcv_transactions rt,
po_headers_all ph
WHERE rt.transaction_id = ln_source_line_id
AND rt.po_header_id = ph.po_header_id;
END IF;
IF ln_type_id = 74 THEN
--转移至常规目的地
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 2 THEN
--销售订单来源
IF ln_type_id = 33 THEN
--销售订单发运
SELECT ott.name || '.' || ooh.order_number || '.' ||
nvl(ooh.cust_po_number, 'no cust po')
INTO ls_result
FROM oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.header_id = ooh.header_id
AND ool.line_id = ln_trx_source_line_id
AND ott.LANGUAGE = userenv('LANG')
AND ooh.order_type_id = ott.transaction_type_id;
END IF;
IF ln_type_id = 52 THEN
-- 销售订单挑库
SELECT ott.name || '.' || ooh.order_number || '.' ||
nvl(ooh.cust_po_number, 'no cust po')
INTO ls_result
FROM oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.header_id = ooh.header_id
AND ool.line_id = ln_trx_source_line_id
AND ott.LANGUAGE = userenv('LANG')
AND ooh.order_type_id = ott.transaction_type_id;
END IF;
END IF;
IF ln_source_type_id = 3 THEN
--Account
IF ln_type_id = 1 THEN
--Account issue
ls_result := ls_type_name;
END IF;
IF ln_type_id = 40 THEN
--Account receipt
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 4 THEN
-- 物料搬运单 ln_type_id=63,64
SELECT ls_type_name || '.' || H.REQUEST_NUMBER || '.' ||
NVL(H.DEscriptION, 'NO DESC')
INTO ls_result
FROM MTL_TXN_REQUEST_HEADERS_V H,
MTL_TXN_REQUEST_LINES_V L
WHERE H.HEADER_ID = l.HEADER_ID
AND l.LINE_ID = ln_source_line_id;
END IF;
IF ln_source_type_id = 5 THEN
--任务或计划 type id :17,25,35,38,43,44,48,55,56,57,58,90,91,92
SELECT ls_type_name || '.' || wdj.WIP_ENTITY_NAME || '.' ||
NVL(wdj.ATTRIBUTE12, 'NO 机型') || '.' ||
NVL(wdj.ATTRIBUTE1, 'NO 批次')
INTO ls_result
FROM WIP_DISCRETE_JOBS_V wdj
WHERE wdj.WIP_ENTITY_ID = ln_transaction_source_id;
END IF;
IF ln_source_type_id = 6 THEN
--帐户别名 type id :31,41
SELECT mgd.concatenated_segments
INTO ls_result
FROM MTL_GENERIC_DISPOSITIONS_KFV mgd
WHERE mgd.disposition_id = ln_transaction_source_id;
END IF;
IF ln_source_type_id = 7 THEN
--内部申请
IF ln_type_id = 61 THEN
ls_result := ls_type_name;
END IF;
IF ln_type_id = 72 THEN
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 8 THEN
--内部订单
IF ln_type_id = 34 THEN
--Internal order issue
ls_result := ls_type_name;
END IF;
IF ln_type_id = 50 THEN
--Internal Order Xfer
ls_result := ls_type_name;
END IF;
IF ln_type_id = 53 THEN
--Internal Order Pick
ls_result := ls_type_name;
END IF;
IF ln_type_id = 54 THEN
--Int Order Direct Ship
ls_result := ls_type_name;
END IF;
IF ln_type_id = 62 THEN
--Int Order Intr Ship
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 9 THEN
-- 周期盘点
IF ln_type_id = 4 THEN
--Cycle Count Adjust
ls_result := ls_type_name;
END IF;
IF ln_type_id = 5 THEN
--Cycle Count Transfer
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 10 THEN
-- 实地盘点
IF ln_type_id = 8 THEN
--实地盘点调整
ls_result := ls_type_name;
END IF;
IF ln_type_id = 9 THEN
--实地盘点仓库转移
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 11 THEN
--标准成本更新
IF ln_type_id = 24 THEN
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 12 THEN
-- RMA 退货 ln_type_id is 15,37
IF ln_type_id = 15 THEN
--RMA 接收
SELECT ott.name || '.' || ooh.order_number || '.' ||
nvl(ooh.cust_po_number, 'no cust po')
INTO ls_result
FROM oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.header_id = ooh.header_id
AND ool.line_id = ln_trx_source_line_id
AND ott.LANGUAGE = userenv('LANG')
AND ooh.order_type_id = ott.transaction_type_id;
END IF;
IF ln_type_id = 37 THEN
--RMA 退货
SELECT ott.name || '.' || ooh.order_number || '.' ||
nvl(ooh.cust_po_number, 'no cust po')
INTO ls_result
FROM oe_order_lines_all ool,
oe_order_headers_all ooh,
oe_transaction_types_tl ott
WHERE ool.header_id = ooh.header_id
AND ool.line_id = ln_trx_source_line_id
AND ott.LANGUAGE = userenv('LANG')
AND ooh.order_type_id = ott.transaction_type_id;
END IF;
END IF;
IF ln_source_type_id = 13 THEN
--库存
IF ln_type_id = 2 THEN
--子库存转移
SELECT nvl(mmt1.transaction_reference, 'no reference')
INTO ls_result
FROM mtl_material_transactions mmt1
WHERE mmt1.transaction_id = p_trans_id;
END IF;
IF ln_type_id = 3 THEN
--组织间直接转移
SELECT nvl(mmt.waybill_airbill, '无陆运单号')
INTO ls_result
FROM mtl_material_transactions mmt
WHERE mmt.transaction_id = p_trans_id;
END IF;
IF ln_type_id = 12 THEN
--Intransit Receipt
ls_result := ls_type_name;
END IF;
IF ln_type_id = 21 THEN
--Intransit Shipment
ls_result := ls_type_name;
END IF;
IF ln_type_id = 42 THEN
--杂项接收
ls_result := ls_type_name;
END IF;
IF ln_type_id = 32 THEN
--Miscellaneous issue
ls_result := ls_type_name;
END IF;
IF ln_type_id = 51 THEN
--Backflush Transfer
ls_result := ls_type_name;
END IF;
IF ln_type_id = 66 THEN
--Project Borrow
ls_result := ls_type_name;
END IF;
IF ln_type_id = 67 THEN
--Project Transfer
ls_result := ls_type_name;
END IF;
IF ln_type_id = 68 THEN
--Project Payback
ls_result := ls_type_name;
END IF;
IF ln_type_id = 70 THEN
--Shipment Rcpt Adjust
ls_result := ls_type_name;
END IF;
IF ln_type_id = 73 THEN
--Planning Transfer
ls_result := ls_type_name;
END IF;
IF ln_type_id = 75 THEN
--Transfer to Consigned
ls_result := ls_type_name;
END IF;
IF ln_type_id = 80 THEN
--平均成本更新
ls_result := ls_type_name;
END IF;
IF ln_type_id = 82 THEN
--Inventory Lot Split
ls_result := ls_type_name;
END IF;
IF ln_type_id = 83 THEN
--Inventory Lot Merge
ls_result := ls_type_name;
END IF;
IF ln_type_id = 84 THEN
--Inventory Lot Translate
ls_result := ls_type_name;
END IF;
IF ln_type_id = 86 THEN
--Cost Group Transfer
ls_result := ls_type_name;
END IF;
IF ln_type_id = 87 THEN
--Container Pack
ls_result := ls_type_name;
END IF;
IF ln_type_id = 88 THEN
--Container Unpack
ls_result := ls_type_name;
END IF;
IF ln_type_id = 89 THEN
--Container Split
ls_result := ls_type_name;
END IF;
IF ln_type_id = 93 THEN
--Field Service Usage
ls_result := ls_type_name;
END IF;
IF ln_type_id = 94 THEN
--Field Service Recovery
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 14 THEN
--Periodic Cost Update
IF ln_type_id = 26 THEN
--Periodic Cost Update
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 15 THEN
--Layer Cost Update
IF ln_type_id = 28 THEN
ls_result := ls_type_name;
END IF;
END IF;
IF ln_source_type_id = 16 THEN
--Project Contract
IF ln_type_id = 77 THEN
--ProjectContract Issue
ls_result := ls_type_name;
END IF;
END IF;
RETURN ls_result;
EXCEPTION
WHEN OTHERS THEN
ls_result := '无来源';
RETURN ls_result;
END;