查找事物处理来源

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;