获取 Transaction Source

报表上要获取物料事务处理表上的的Source（来源）字段，非数据库项。

/*==================================================
    Procedure Name:
        get_txn_source
    Description:
        This procedure is used to calculate transaction
        source name of one specified material transaction.
    Arguments
        p_transaction_id  IN  *material transaction id
        x_txn_source      OUT *transaction source name                  
    History:
.00  2012-03-01  ech0-silence  Creation
  ==================================================*/
  procedure get_txn_source(
              p_transaction_id in number
            , x_txn_source     out nocopy varchar2
            )
  is
    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;
  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(CLM_DOCUMENT_NUMBER, 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
      -- We do not need display txn source for other types.
      -- including Layer_cost_update
      v_process_phase := 'Others';
      x_txn_source := null;
    end if;
  
  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;
      x_txn_source := null;
  end;