查找事物处理来源

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;