子库转移 杂项出入库脚本

--Subinventory Transfer

  BEGIN
    SELECT OP.ACCT_PERIOD_ID
      INTO V_ACCT_PERIOD_ID
      FROM ORG_ACCT_PERIODS OP
     WHERE OP.ORGANIZATION_ID = :PARAMETER.ORG_ID
       AND SYSDATE BETWEEN OP.PERIOD_START_DATE AND OP.SCHEDULE_CLOSE_DATE;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      MESSAGE_ERROR(SQLERRM || '未找到ACCT_PERIOD_ID');
  END;

      INSERT INTO MTL_TRANSACTIONS_INTERFACE
        (TRANSACTION_INTERFACE_ID
        ,SOURCE_CODE
        ,SOURCE_LINE_ID
        ,SOURCE_HEADER_ID
        ,PROCESS_FLAG
        ,TRANSACTION_MODE
        ,LOCK_FLAG
        ,INVENTORY_ITEM_ID
        ,ORGANIZATION_ID
        ,TRANSACTION_QUANTITY
        ,PRIMARY_QUANTITY
        ,TRANSACTION_UOM
        ,TRANSACTION_DATE
        ,SUBINVENTORY_CODE
        ,TRANSFER_SUBINVENTORY
        ,TRANSACTION_TYPE_ID
        ,TRANSACTION_ACTION_ID
        ,TRANSACTION_SOURCE_TYPE_ID
        ,TRANSACTION_REFERENCE --参考
        ,CREATED_BY
        ,CREATION_DATE
        ,LAST_UPDATED_BY
        ,LAST_UPDATE_DATE
        ,ACCT_PERIOD_ID)
      VALUES
        (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL --transaction_interface_id
        ,'SubInv Transfer' --source_code
        ,:EDI_TRANS.LINE_ID --source_line_id
        ,-1 --source_header_id
        ,1 --process_flag
        ,3 --transaction_mode
        ,2 --lock_flag
        ,:EDI_TRANS.INVENTORY_ITEM_ID --inventory_item_id
        ,:EDI_TRANS.ORGANIZATION_ID --organization_id
        ,:EDI_TRANS.TRANSFER_QTY1 --transaction_quantity
        ,:EDI_TRANS.TRANSFER_QTY1 --primary_quantity
        ,:EDI_TRANS.PRIMARY_UOM_CODE --transaction_uom
        ,SYSDATE
        ,:EDI_TRANS.FROM_SUBINV
        ,:EDI_TRANS.TO_SUBINV1
        ,2 --transaction_type_id
        ,2 --TRANSACTION_ACTION_ID
        ,13 --TRANSACTION_SOURCE_TYPE_ID
        ,:EDI_TRANS.RECEIPT_NUMBER
        ,FND_GLOBAL.USER_ID
        ,SYSDATE
        ,FND_GLOBAL.USER_ID
        ,SYSDATE
        ,V_ACCT_PERIOD_ID);

 

--此请求系统定时运行，可手动提交

    V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'INV'
                                              ,PROGRAM     => 'INCTCM'
                                              ,DESCRIPTION => '处理事务处理接口'
                                              ,START_TIME  => TO_CHAR(SYSDATE
                                                                     ,'yyyy/mm/dd hh24:mi:ss')
                                              ,SUB_REQUEST => FALSE);

--接口处理错误信息查询

        SELECT MTI.ERROR_EXPLANATION
          INTO V_ERROR_EXPLANATION
          FROM MTL_TRANSACTIONS_INTERFACE MTI
         WHERE MTI.TRANSACTION_REFERENCE = P_TRANSACTION_REFERENCE
           AND MTI.SOURCE_CODE = 'SubInv Transfer'
           AND MTI.SOURCE_HEADER_ID = -1
           AND MTI.ERROR_CODE IS NOT NULL

--杂项出库

PROCEDURE MISCELLANEOUS_ISSUE IS

  V_INVENTORY_ITEM_CODE        VARCHAR2(30);
  V_PRIMARY_UOM_CODE           VARCHAR2(30);
  V_SUBINVENTORY_CODE          VARCHAR2(30);
  V_INVENTORY_LOCATION_ID      NUMBER;
  V_TRANSACTION_TYPE_ID        NUMBER;
  V_TRANSACTION_ACTION_ID      NUMBER;
  V_TRANSACTION_SOURCE_TYPE_ID NUMBER;
  v_transaction_source_id number;
 
BEGIN
  SELECT INVENTORY_ITEM_CODE
        ,PRIMARY_UOM_CODE
    INTO V_INVENTORY_ITEM_CODE
        ,V_PRIMARY_UOM_CODE
    FROM CUX_MTL_SYSTEM_ITEMS_V
   WHERE INVENTORY_ITEM_ID = :SCRAP_LINES.MOULD_ITEM_ID
     AND ORGANIZATION_ID = :PARAMETER.ORG_ID;

  SELECT SUBINVENTORY_CODE
        ,INVENTORY_LOCATION_ID
    INTO V_SUBINVENTORY_CODE
        ,V_INVENTORY_LOCATION_ID
    FROM MTL_ITEM_LOCATIONS
   WHERE INVENTORY_LOCATION_ID =
         (SELECT CURRENT_LOCATION_ID
            FROM CUX_MOD_MOULD_INFO
           WHERE MOULD_ITEM_ID = :SCRAP_LINES.MOULD_ITEM_ID
             AND ORGANIZATION_ID = :PARAMETER.ORG_ID)
     AND ORGANIZATION_ID = :PARAMETER.ORG_ID;

SELECT DISPOSITION_ID into v_transaction_source_id
            FROM MTL_GENERIC_DISPOSITIONS
           WHERE SEGMENT1 = 'M.模具报废出库'
             AND ORGANIZATION_ID = :PARAMETER.ORG_ID;
  BEGIN
    INSERT INTO MTL_TRANSACTIONS_INTERFACE
      (TRANSACTION_INTERFACE_ID
      ,SOURCE_CODE
      ,SOURCE_LINE_ID
      ,SOURCE_HEADER_ID
      ,PROCESS_FLAG
      ,TRANSACTION_MODE
      ,LOCK_FLAG
      ,INVENTORY_ITEM_ID
      ,ITEM_SEGMENT1
      ,ORGANIZATION_ID
      ,TRANSACTION_QUANTITY
      ,PRIMARY_QUANTITY
      ,TRANSACTION_UOM
      ,TRANSACTION_DATE
      ,SUBINVENTORY_CODE
      ,TRANSFER_SUBINVENTORY
      ,LOCATOR_ID
      ,TRANSFER_LOCATOR
      ,TRANSACTION_TYPE_ID
      ,TRANSACTION_ACTION_ID
      ,TRANSACTION_SOURCE_TYPE_ID
      ,TRANSACTION_SOURCE_ID
      ,TRANSACTION_SOURCE_NAME --来源
      ,TRANSACTION_REFERENCE --参考
      ,CREATED_BY
      ,CREATION_DATE
      ,LAST_UPDATED_BY
      ,LAST_UPDATE_DATE)
    VALUES
      (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL --transaction_interface_id
      ,'Miscellaneous Issue' --source_code
      ,-1 --source_line_id
      ,-1 --source_header_id
      ,1 --process_flag
      ,3 --transaction_mode
      ,2 --lock_flag
      ,:SCRAP_LINES.MOULD_ITEM_ID --inventory_item_id
      ,V_INVENTORY_ITEM_CODE --item_segment1
      ,:PARAMETER.ORG_ID --organization_id
      ,-1 --transaction_quantity
      ,-1 --primary_quantity
      ,V_PRIMARY_UOM_CODE --transaction_uom
      ,SYSDATE
      ,V_SUBINVENTORY_CODE
      ,NULL --:CUX_LOC.TRANSFER_SUBINV_CODE
      ,V_INVENTORY_LOCATION_ID
      ,NULL --:CUX_LOC.TRANSFER_LOCATION_ID
      ,31--transaction_type_id
      ,1 --TRANSACTION_ACTION_ID
      ,6--TRANSACTION_SOURCE_TYPE_ID
      ,v_transaction_source_id --TRANSACTION_SOURCE_ID
      ,'M.模具报废出库' --TRANSACTION_SOURCE_NAME
      ,:SCRAP_HEADERS.MOULD_SCRAP_NUMBER
      ,FND_GLOBAL.USER_ID
      ,SYSDATE
      ,FND_GLOBAL.USER_ID
      ,SYSDATE);
  EXCEPTION
    WHEN OTHERS THEN
      MESSAGE_ERROR('数据插入事务处理接口失败：' || SQLERRM);
  END;
  UPDATE CUX_MOD_MOULD_INFO
     SET CURRENT_LOCATION_ID = NULL,
        SCRAPPED_REASON = :SCRAP_LINES.MOULD_SCRAP_REASON,
        SCRAPPED_DATE = :SCRAP_LINES.MOULD_SCRAP_DATE
  WHERE  ORGANIZATION_ID = :PARAMETER.ORG_ID
    AND  MOULD_NUMBER = :SCRAP_LINES.MOULD_NUMBER;
END;