客制化物料搬运单流程

最新推荐文章于 2022-05-23 09:19:58 发布
VIP文章 最新推荐文章于 2022-05-23 09:19:58 发布
阅读量2.8k 收藏 1
点赞数
分类专栏： ORACLE EBS OM
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mia502908009/article/details/50577977
版权



1.首先对制做需要处理的搬运单对应的料号批次数量信息，然后通过excel导入临时表

    ----2016.1.7整理导入Excel的临时数据：
--STP1:切换到系统管理员(或者应用开发员)-->通用文件管理器-->加载 功能，将要上传的Excel文档加载上去。

--STP2:直接在Toad执行下面的语句，读取Excel的内容。
DECLARE
   L_BLOB BLOB;
   L_FILE_NAME VARCHAR2(240);
   L_FILE_ID NUMBER;
BEGIN
   L_FILE_NAME := '客制搬运单物料.xls';
   SELECT FILE_DATA,FILE_ID
     INTO L_BLOB,L_FILE_ID
     FROM FND_LOBS
    WHERE FILE_NAME = L_FILE_NAME;
  XYG_ALD_ANALYDOC_PKG.GT_INIT;
  XYG_ALD_ANALYDOC_PKG.EXCEL2TAB(L_BLOB);
  --删除临时文件
  fnd_gfm.DELETE_LOB(L_FILE_ID);
  COMMIT;
END;

--STP3:下面的语句就是这个Excel的所有内容。注意Excel的页签。

SELECT G.COL1 DELIVERY_NAME
            ,G.COL2 REQUEST_NUMBER
            ,MSIB.INVENTORY_ITEM_ID
            ,MSIB.SEGMENT1
            ,MSIB.DESCRIPTION
            ,G.COL4 SUBINVENTORY_CODE
            ,G.COL5 LOCATOR
            ,G.COL6 LOT_NUMBER
            ,G.COL7 TRANSACTION_QTY
            ,G.COL8 TRANSACTION_UOM
            ,MTRL.LINE_ID MOVE_ORDER_LINE_ID
            ,MTRH.ORGANIZATION_ID
            ,MTRL.QUANTITY
            ,MTRL.UOM_CODE
            ,MTRL.TO_SUBINVENTORY_CODE
            ,MTRL.TO_LOCATOR_ID
            ,MTRL.FROM_SUBINVENTORY_CODE
            ,MTRL.FROM_LOCATOR_ID
            ,MIL.INVENTORY_LOCATION_ID
        FROM XYG_ALD_ANALYDOC_GT G
            ,MTL_TXN_REQUEST_LINES MTRL
            ,MTL_TXN_REQUEST_HEADERS MTRH
            ,MTL_SYSTEM_ITEMS_B MSIB
            ,MTL_ITEM_LOCATIONS MIL
       WHERE DOC_ROW != 1
         AND MTRH.HEADER_ID = MTRL.HEADER_ID
         AND MTRH.REQUEST_NUMBER = G.COL2
         AND MSIB.DESCRIPTION = G.COL3
         AND MSIB.ORGANIZATION_ID = 107
         AND MSIB.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
         AND MTRH.MOVE_ORDER_TYPE != 6
         AND MTRL.LINE_STATUS IN (3, 7, 9)
         AND MIL.ORGANIZATION_ID = MTRH.ORGANIZATION_ID
         AND MIL.SUBINVENTORY_CODE = G.COL4
         AND MIL.SEGMENT1 =G.COL5
         and MTRL.LINE_NUMBER = G.COL9;

4.处理导入的excel资料并做物料搬运单处理

DECLARE
   V_TOT_TRANS_QTY   NUMBER := 0;
   L_TXN_HEADER_ID   NUMBER;
   L_MSG_COUNT       NUMBER;
   X_QUANTITY        NUMBER := 0;
   X_UOM_CODE        VARCHAR2( 3 );
   L_RETURN_STATUS   VARCHAR2( 10 );
   L_MSG_DATA        VARCHAR2( 1000 );
   X_LINE_ID         NUMBER ;
   CURSOR CUR_LINE_DEL( X_REQUEST_NUMBER VARCHAR2 )
   IS
      SELECT G.COL1 DELIVERY_NAME
            ,G.COL2 REQUEST_NUMBER
            ,MSIB.INVENTORY_ITEM_ID
            ,MSIB.SEGMENT1
            ,MSIB.DESCRIPTION
            ,G.COL4 SUBINVENTORY_CODE
            ,G.COL5 LOCATOR
            ,G.COL6 LOT_NUMBER
            ,G.COL7 TRANSACTION_QTY
            ,G.COL8 TRANSACTION_UOM
            ,MTRL.LINE_ID MOVE_ORDER_LINE_ID
            ,MTRH.ORGANIZATION_ID
            ,MTRL.QUANTITY
            ,MTRL.UOM_CODE
            ,MTRL.TO_SUBINVENTORY_CODE
            ,MTRL.TO_LOCATOR_ID
            ,MTRL.FROM_SUBINVENTORY_CODE
            ,MTRL.FROM_LOCATOR_ID
            ,MIL.INVENTORY_LOCATION_ID
        FROM XYG_ALD_ANALYDOC_GT G
            ,MTL_TXN_REQUEST_LINES MTRL
            ,MTL_TXN_REQUEST_HEADERS MTRH
            ,MTL_SYSTEM_ITEMS_B MSIB
            ,MTL_ITEM_LOCATIONS MIL
       WHERE DOC_ROW != 1
         AND MTRH.HEADER_ID = MTRL.HEADER_ID
         AND MTRH.REQUEST_NUMBER = G.COL2
         AND MSIB.DESCRIPTION = G.COL3
         AND MSIB.ORGANIZATION_ID = 107
         AND MSIB.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
         AND MTRH.MOVE_ORDER_TYPE != 6
         AND MTRL.LINE_STATUS IN (3, 7, 9)
         AND MIL.ORGANIZATION_ID = MTRH.ORGANIZATION_ID
         AND MIL.SUBINVENTORY_CODE = G.COL4
         AND MIL.SEGMENT1 =G.COL5
         and MTRL.LINE_NUMBER = G.COL9
         AND G.COL2 = X_REQUEST_NUMBER;

   CURSOR CU_COUNT_HEADER
   IS
        SELECT COL2 REQUEST_NUMBER
              ,COL8 UOM_CODE
              ,MSIB.INVENTORY_ITEM_ID
              ,SUM( COL7 ) T_TRANSACTION_QTY
              ,G.COL9 LINE_NUMBER
          FROM XYG_ALD_ANALYDOC_GT G,
               MTL_SYSTEM_ITEMS_B MSIB
         WHERE G.DOC_ROW != 1
           AND MSIB.DESCRIPTION = G.COL3
           AND MSIB.ORGANIZATION_ID = 107
      GROUP BY COL2, COL8,MSIB.INVENTORY_ITEM_ID,G.COL9;
BEGIN
   IF :P_TRAN_DATE IS NULL
   THEN
      /*FND_MESSAGE.SET_STRING('请先输入事务处理日期再做完成！');
      FND_MESSAGE.ERROR;
      RAISE FORM_TRIGGER_FAILURE;
      */
      DBMS_OUTPUT.PUT_LINE( '请先输入事务处理日期再往下操作' );
      RETURN;
   END IF;

   FOR LR_COUNT_HEADER IN CU_COUNT_HEADER
   LOOP
      X_LINE_ID := 0 ;
      X_QUANTITY := 0 ;
      X_UOM_CODE := NULL;
      BEGIN
         SELECT MTRL.QUANTITY, MTRL.UOM_CODE,LINE_ID
           INTO X_QUANTITY, X_UOM_CODE,X_LINE_ID
           FROM MTL_TXN_REQUEST_LINES MTRL, MTL_TXN_REQUEST_HEADERS MTRH
          WHERE MTRH.HEADER_ID = MTRL.HEADER_ID
            AND MTRH.MOVE_ORDER_TYPE != 6
            AND MTRL.LINE_STATUS IN (3, 7, 9)
            AND MTRH.REQUEST_NUMBER = LR_COUNT_HEADER.REQUEST_NUMBER
            AND MTRL.INVENTORY_ITEM_ID =LR_COUNT_HEADER.INVENTORY_ITEM_ID
            AND MTRL.LINE_NUMBER =LR_COUNT_HEADER.LINE_NUMBER ;
      EXCEPTION
         WHEN OTHERS
         THEN
            X_QUANTITY := 0;
            X_UOM_CODE := NULL;
            X_LINE_ID := 0 ;
      END;
      
      IF X_LINE_ID = 0 THEN
      DBMS_OUTPUT.PUT_LINE('X_LINE_ID 为空');
      GOTO AA;
      
      END IF ;
      DBMS_OUTPUT.PUT_LINE('X_LINE_ID '||X_LINE_ID);
      

      IF NVL( X_UOM_CODE, 'X_UOM_CODE' ) <> LR_COUNT_HEADER.UOM_CODE
      THEN
         DBMS_OUTPUT.PUT_LINE(   'EXCEL中交易单位与请求单位不一致，搬运单号:'
                              || LR_COUNT_HEADER.REQUEST_NUMBER
                              || '不做处理');
         GOTO AA;
      END IF;

      IF X_QUANTITY < LR_COUNT_HEADER.T_TRANSACTION_QTY
      THEN
         DBMS_OUTPUT.PUT_LINE(   'EXCEL中交易数量大于请求数量,搬运单号:'
                              || LR_COUNT_HEADER.REQUEST_NUMBER
                              || '不做处理');
         GOTO AA;
      END IF;

      FOR LR_LINE_DEL IN CUR_LINE_DEL( LR_COUNT_HEADER.REQUEST_NUMBER )
      LOOP
      
      DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.ORGANIZATION_ID  ---->' || LR_LINE_DEL.ORGANIZATION_ID);
      DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.INVENTORY_ITEM_ID  ---->' || LR_LINE_DEL.INVENTORY_ITEM_ID);
      DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.INVENTORY_LOCATION_ID  ---->' || LR_LINE_DEL.INVENTORY_LOCATION_ID);
      DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.SUBINVENTORY_CODE  ---->' || LR_LINE_DEL.SUBINVENTORY_CODE);
      DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.LOT_NUMBER  ---->' || LR_LINE_DEL.LOT_NUMBER);
        
        
      XYG_COMMON_UTIL_PKG.SET_ITEM_ALL_QTY(
                                               P_ORGANIZATION_ID     => LR_LINE_DEL.ORGANIZATION_ID
                                              ,P_ITEM_ID             => LR_LINE_DEL.INVENTORY_ITEM_ID
                                              ,P_LOCATION_ID         => LR_LINE_DEL.INVENTORY_LOCATION_ID
                                              ,P_SUBINVENTORY_CODE   => LR_LINE_DEL.SUBINVENTORY_CODE
                                              ,P_LOT_NUMBER          => LR_LINE_DEL.LOT_NUMBER
         );
         
         DBMS_OUTPUT.PUT_LINE('LR_LINE_DEL.TRANSACTION_QTY  ---->' || LR_LINE_DEL.TRANSACTION_QTY);
         DBMS_OUTPUT.PUT_LINE('XYG_COMMON_UTIL_PKG.GET_ITEM_ATT_QTY  ---->' ||  XYG_COMMON_UTIL_PKG.GET_ITEM_ATT_QTY);
        
         IF LR_LINE_DEL.TRANSACTION_QTY <=
               XYG_COMMON_UTIL_PKG.GET_ITEM_ATT_QTY
         THEN
            IF L_TXN_HEADER_ID IS NULL
            THEN
               SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
                 INTO L_TXN_HEADER_ID
                 FROM DUAL;
            END IF;

            -- :PARAMETER.TXN_DATE:=SYSDATE-1;
            -- CALL API TO DO PRE PICK CONFIRM
            XYG_INV_PICK_CONFIRM_PKG.PROCESS_MOVE_ORDER_LINE(
                                                              P_TXN_HEADER_ID       => L_TXN_HEADER_ID
                                                             ,P_MO_LINE_ID          => X_LINE_ID--LR_LINE_DEL.MOVE_ORDER_LINE_ID
                                                             ,P_SUBINVENTORY_CODE   => LR_LINE_DEL.SUBINVENTORY_CODE
                                                             ,P_LOCATOR_ID          => LR_LINE_DEL.INVENTORY_LOCATION_ID
                                                             ,P_LOT_NUMBER          => LR_LINE_DEL.LOT_NUMBER
                                                             ,P_PROCESS_QTY         => LR_LINE_DEL.TRANSACTION_QTY
                                                             ,X_RETURN_STATUS       => L_RETURN_STATUS
                                                             ,X_MSG_DATA            => L_MSG_DATA
                                                             ,P_TXN_DATE            => :P_TRAN_DATE
            );

            IF L_RETURN_STATUS <> 'S' THEN
               --FND_MESSAGE.DEBUG('处理搬运单错误：'||L_MSG_DATA);
               --EXIT;
               DBMS_OUTPUT.PUT_LINE( '处理搬运单错误：' || L_MSG_DATA );
               GOTO AA;
            END IF;
         ELSE
            /* FND_MESSAGE.DEBUG('错误！当前行输入的事务处理数量超出物料的可用数量！');
             L_RETURN_STATUS := 'E';
             EXIT;
             */
            DBMS_OUTPUT.PUT_LINE('错误！当前行输入的事务处理数量超出物料的可用数量！');
            GOTO AA;
         END IF;

      END LOOP;
      
      IF L_RETURN_STATUS = 'S'
      THEN
            XYG_INV_PICK_CONFIRM_PKG.PICK_CONFIRM(
                                                   P_MO_LINE_ID      => X_LINE_ID
                                                  ,X_RETURN_STATUS   => L_RETURN_STATUS
                                                  ,X_MSG_COUNT       => L_MSG_COUNT
                                                  ,X_MSG_DATA        => L_MSG_DATA
            );

            IF L_RETURN_STATUS = 'S'
            THEN
               XYG_INV_PICK_CONFIRM_PKG.SET_MO_LINE_PC_FLAG(
                                                             X_LINE_ID
                                                            ,'Y'
               );
               /*FND_MESSAGE.DEBUG('物料搬运单挑库处理成功！');
               */
               DBMS_OUTPUT.PUT_LINE( '物料搬运单挑库处理成功！' );
            ELSE
               DBMS_OUTPUT.PUT_LINE(
                                     '物料搬运单挑库处理失败！' || L_MSG_DATA
               );
            -- FND_MESSAGE.DEBUG('物料搬运单挑库处理失败！'||L_MSG_DATA);
            END IF;
      END IF;

     <<AA>>
      UPDATE XYG_ALD_ANALYDOC_GT
      SET PROCESS_FLAG = 1 ,PROCESS_MESSAGE ='此单号未进行处理'
      WHERE COL2 = LR_COUNT_HEADER.REQUEST_NUMBER;  
   END LOOP;
END;
   
 5.返还界面查看状态是否变更，已经物料是否已产生交易记录以及保留记录。



--------参考代码

程式中用到的SQL如下:

创建type类型: vartabletype
CREATE OR REPLACE TYPE APPS.vartabletype as table of varchar2(1000);

创建表:XYG_ALD_UPLOAD_TEMP
CREATE TABLE XYG.XYG_ALD_UPLOAD_TEMP
(
  UPLOAD_TEMP_ID     NUMBER,
  BATCH_ID           NUMBER                     NOT NULL,
  DOC_TYPE           VARCHAR2(240 BYTE),
  DOC_CODE           VARCHAR2(480 BYTE),
  DOC_ROW            NUMBER,
  COL1               VARCHAR2(4000 BYTE),
  COL2               VARCHAR2(4000 BYTE),
  COL3               VARCHAR2(4000 BYTE),
  COL4               VARCHAR2(4000 BYTE),
  COL5               VARCHAR2(4000 BYTE),
  COL6               VARCHAR2(4000 BYTE),
  COL7               VARCHAR2(4000 BYTE),
  COL8               VARCHAR2(4000 BYTE),
  COL9               VARCHAR2(4000 BYTE),
  COL10              VARCHAR2(4000 BYTE),
  COL11              VARCHAR2(4000 BYTE),
  COL12              VARCHAR2(4000 BYTE),
  COL13              VARCHAR2(4000 BYTE),
  COL14              VARCHAR2(4000 BYTE),
  COL15              VARCHAR2(4000 BYTE),
  COL16              VARCHAR2(4000 BYTE),
  COL17              VARCHAR2(4000 BYTE),
  COL18              VARCHAR2(4000 BYTE),
  COL19              VARCHAR2(4000 BYTE),
  COL20              VARCHAR2(4000 BYTE),
  COL21              VARCHAR2(4000 BYTE),
  COL22              VARCHAR2(4000 BYTE),
  COL23              VARCHAR2(4000 BYTE),
  COL24              VARCHAR2(4000 BYTE),
  COL25              VARCHAR2(4000 BYTE),
  COL26              VARCHAR2(4000 BYTE),
  COL27              VARCHAR2(4000 BYTE),
  COL28              VARCHAR2(4000 BYTE),
  COL29              VARCHAR2(4000 BYTE),
  COL30              VARCHAR2(4000 BYTE),
  COL31              VARCHAR2(4000 BYTE),
  COL32              VARCHAR2(4000 BYTE),
  COL33              VARCHAR2(4000 BYTE),
  COL34              VARCHAR2(4000 BYTE),
  COL35              VARCHAR2(4000 BYTE),
  COL36              VARCHAR2(4000 BYTE),
  COL37              VARCHAR2(4000 BYTE),
  COL38              VARCHAR2(4000 BYTE),
  COL39              VARCHAR2(4000 BYTE),
  COL40              VARCHAR2(4000 BYTE),
  COL41              VARCHAR2(4000 BYTE),
  COL42              VARCHAR2(4000 BYTE),
  COL43              VARCHAR2(4000 BYTE),
  COL44              VARCHAR2(4000 BYTE),
  COL45              VARCHAR2(4000 BYTE),
  COL46              VARCHAR2(4000 BYTE),
  COL47              VARCHAR2(4000 BYTE),
  COL48              VARCHAR2(4000 BYTE),
  COL49              VARCHAR2(4000 BYTE),
  COL50              VARCHAR2(4000 BYTE),
  COL51              VARCHAR2(4000 BYTE),
  COL52              VARCHAR2(4000 BYTE),
  COL53              VARCHAR2(4000 BYTE),
  COL54              VARCHAR2(4000 BYTE),
  COL55              VARCHAR2(4000 BYTE),
  PROCESS_FLAG       NUMBER                     DEFAULT 0                     NOT NULL,
  PROCESS_MESSAGE    VARCHAR2(4000 BYTE),
  CREATED_BY         NUMBER                     DEFAULT -1                    NOT NULL,
  CREATION_DATE      DATE                       DEFAULT SYSDATE               NOT NULL,
  LAST_UPDATED_BY    NUMBER                     DEFAULT -1                    NOT NULL,
  LAST_UPDATE_DATE   DATE                       DEFAULT SYSDATE               NOT NULL,
  LAST_UPDATE_LOGIN  NUMBER                     DEFAULT -1                    NOT NULL
)
TABLESPACE XYG_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.CREATED_BY IS '创建者';

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.CREATION_DATE IS '创建日期';

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.LAST_UPDATED_BY IS '最后更新人';

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.LAST_UPDATE_DATE IS '最后更新日期';

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.LAST_UPDATE_LOGIN IS '最后登陆人';

COMMENT ON COLUMN XYG.XYG_ALD_UPLOAD_TEMP.UPLOAD_TEMP_ID IS '表格ID';


CREATE INDEX XYG.XYG_ALD_UPLOAD_TEMP_N1 ON XYG.XYG_ALD_UPLOAD_TEMP
(BATCH_ID)
NOLOGGING
TABLESPACE XYG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE UNIQUE INDEX XYG.XYG_ALD_UPLOAD_TEMP_U1 ON XYG.XYG_ALD_UPLOAD_TEMP
(UPLOAD_TEMP_ID)
NOLOGGING
TABLESPACE XYG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


DROP SYNONYM APPS.XYG_ALD_UPLOAD_TEMP;

CREATE SYNONYM APPS.XYG_ALD_UPLOAD_TEMP FOR XYG.XYG_ALD_UPLOAD_TEMP;


ALTER TABLE XYG.XYG_ALD_UPLOAD_TEMP ADD (
  CONSTRAINT XYG_ALD_UPLOAD_TEMP_U1
 PRIMARY KEY
 (UPLOAD_TEMP_ID)
    USING INDEX
    TABLESPACE XYG_IDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          128K
                NEXT             128K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));


创建表:XYG_ALD_ANALYDOC_GT
DROP TABLE XYG.XYG_ALD_ANALYDOC_GT CASCADE CONSTRAINTS;

CREATE GLOBAL TEMPORARY TABLE XYG.XYG_ALD_ANALYDOC_GT
(
  DOC_TYPE         VARCHAR2(240 BYTE),
  DOC_CODE         VARCHAR2(480 BYTE),
  DOC_ROW          NUMBER,
  COL1             VARCHAR2(4000 BYTE),
  COL2             VARCHAR2(4000 BYTE),
  COL3             VARCHAR2(4000 BYTE),
  COL4             VARCHAR2(4000 BYTE),
  COL5             VARCHAR2(4000 BYTE),
  COL6             VARCHAR2(4000 BYTE),
  COL7             VARCHAR2(4000 BYTE),
  COL8             VARCHAR2(4000 BYTE),
  COL9             VARCHAR2(4000 BYTE),
  COL10            VARCHAR2(4000 BYTE),
  COL11            VARCHAR2(4000 BYTE),
  COL12            VARCHAR2(4000 BYTE),
  COL13            VARCHAR2(4000 BYTE),
  COL14            VARCHAR2(4000 BYTE),
  COL15            VARCHAR2(4000 BYTE),
  COL16            VARCHAR2(4000 BYTE),
  COL17            VARCHAR2(4000 BYTE),
  COL18            VARCHAR2(4000 BYTE),
  COL19            VARCHAR2(4000 BYTE),
  COL20            VARCHAR2(4000 BYTE),
  COL21            VARCHAR2(4000 BYTE),
  COL22            VARCHAR2(4000 BYTE),
  COL23            VARCHAR2(4000 BYTE),
  COL24            VARCHAR2(4000 BYTE),
  COL25            VARCHAR2(4000 BYTE),
  COL26            VARCHAR2(4000 BYTE),
  COL27            VARCHAR2(4000 BYTE),
  COL28            VARCHAR2(4000 BYTE),
  COL29            VARCHAR2(4000 BYTE),
  COL30            VARCHAR2(4000 BYTE),
  COL31            VARCHAR2(4000 BYTE),
  COL32            VARCHAR2(4000 BYTE),
  COL33            VARCHAR2(4000 BYTE),
  COL34            VARCHAR2(4000 BYTE),
  COL35            VARCHAR2(4000 BYTE),
  COL36            VARCHAR2(4000 BYTE),
  COL37            VARCHAR2(4000 BYTE),
  COL38            VARCHAR2(4000 BYTE),
  COL39            VARCHAR2(4000 BYTE),
  COL40            VARCHAR2(4000 BYTE),
  COL41            VARCHAR2(4000 BYTE),
  COL42            VARCHAR2(4000 BYTE),
  COL43            VARCHAR2(4000 BYTE),
  COL44            VARCHAR2(4000 BYTE),
  COL45            VARCHAR2(4000 BYTE),
  COL46            VARCHAR2(4000 BYTE),
  COL47            VARCHAR2(4000 BYTE),
  COL48            VARCHAR2(4000 BYTE),
  COL49            VARCHAR2(4000 BYTE),
  COL50            VARCHAR2(4000 BYTE),
  COL51            VARCHAR2(4000 BYTE),
  COL52            VARCHAR2(4000 BYTE),
  COL53            VARCHAR2(4000 BYTE),
  COL54            VARCHAR2(4000 BYTE),
  COL55            VARCHAR2(4000 BYTE),
  PROCESS_FLAG     NUMBER,
  PROCESS_MESSAGE  VARCHAR2(4000 BYTE)
)
ON COMMIT PRESERVE ROWS
NOCACHE;


CREATE INDEX XYG.XYG_ALD_ANALYDOC_GT_N1 ON XYG.XYG_ALD_ANALYDOC_GT
(DOC_ROW, DOC_CODE);


DROP SYNONYM APPS.XYG_ALD_ANALYDOC_GT;

CREATE SYNONYM APPS.XYG_ALD_ANALYDOC_GT FOR XYG.XYG_ALD_ANALYDOC_GT;

---日志包
CREATE TABLE APPS.XYG_LOG_MESSAGES
(
  LOG_ID          NUMBER,
  APPLICATION_ID  NUMBER,
  LOG_DATE        DATE,
  LOG_MODULE      VARCHAR2(100 BYTE),
  LOG_RUNNER      NUMBER,
  LOG_MESSAGE     VARCHAR2(1000 BYTE)
)
TABLESPACE APPS_TS_TX_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;


CREATE UNIQUE INDEX APPS.XYG_LOG_MESSAGE_N1 ON APPS.XYG_LOG_MESSAGES
(LOG_ID)
NOLOGGING
TABLESPACE XYG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX APPS.XYG_LOG_MESSAGE_N2 ON APPS.XYG_LOG_MESSAGES
(APPLICATION_ID, LOG_MODULE)
NOLOGGING
TABLESPACE XYG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX APPS.XYG_LOG_MESSAGE_N3 ON APPS.XYG_LOG_MESSAGES
(LOG_DATE)
NOLOGGING
TABLESPACE XYG_IDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;



--创建包  XYG_COMMON_UTIL_PKG

CREATE OR REPLACE PACKAGE APPS.XYG_COMMON_UTIL_PKG AS

  /*=======================
   Declare Global Variable
  =========================*/
  g_package_name        VARCHAR2(50) := 'XYG_COMMON_UTIL_PKG';

  -- define variable for get ITEM quantity
  g_qoh                 NUMBER;      --quantity on hand
  g_rqoh                NUMBER;      --reservable quantity on hand
  g_qr                  NUMBER;      --quantity reserved
  g_qs                  NUMBER;      --quantity suggested
  g_att                 NUMBER;      --available to transact
  g_atr                 NUMBER;      --available to reserve

  /*======================================================================
  Public Procedure : log_messages
       Logic for Log Messages Procedure
  ======================================================================*/
  PROCEDURE log_messages(p_appl_id  NUMBER, p_log_module  VARCHAR2, p_log_message  VARCHAR2);


  PROCEDURE global_variable_initialize;

 
  PROCEDURE set_item_all_qty(p_organization_id    NUMBER,
                             p_item_id            NUMBER,
                             p_location_id        NUMBER   DEFAULT NULL,
                             p_subinventory_code  VARCHAR2 DEFAULT NULL,                             
                             p_lot_number         VARCHAR2 DEFAULT NULL
                             );
                             
                             
  FUNCTION get_item_att_qty RETURN NUMBER;
 
  Function IS_NUMBER(P_STRING in VARCHAR2) return number
 
END XYG_COMMON_UTIL_PKG;
 
 
CREATE OR REPLACE PACKAGE BODY APPS.XYG_COMMON_UTIL_PKG AS

  /*======================================================================
  Public Procedure : log_messages
       Logic for Log Messages Procedure
  ======================================================================*/
  PROCEDURE log_messages(p_appl_id  NUMBER, p_log_module  VARCHAR2, p_log_message  VARCHAR2)
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_log_id          NUMBER;
  BEGIN

    SELECT xyg_log_messages_s.NEXTVAL
      INTO l_log_id
      FROM dual;

    INSERT INTO xyg_log_messages
      (log_id, application_id, log_date, log_module, log_runner, log_message)
    VALUES
      (l_log_id, p_appl_id, SYSDATE, p_log_module, fnd_profile.VALUE('USER_ID'), p_log_message);

    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

  /*======================================================================
  Public Procedure : global_variable_initialize
       Logic for global variable initialize procedure       
  ======================================================================*/
  PROCEDURE global_variable_initialize
  IS
  BEGIN
    g_qoh               := 0;
    g_rqoh              := 0;
    g_qr                := 0;
    g_qs                := 0;
    g_att               := 0;
    g_atr               := 0;
  END;
 
 
  FUNCTION get_item_att_qty RETURN NUMBER
  IS
  BEGIN
    RETURN nvl(g_att, 0);       
  END;
 
 
  /*======================================================================
  Public Function : set_item_all_qty
       Logic for set item various quantities function
  ======================================================================*/
  PROCEDURE set_item_all_qty(p_organization_id    NUMBER,
                             p_item_id            NUMBER,
                             p_location_id        NUMBER   DEFAULT NULL,
                             p_subinventory_code  VARCHAR2 DEFAULT NULL,                             
                             p_lot_number         VARCHAR2 DEFAULT NULL
                             )
  IS
    l_return_status            VARCHAR2(50);
    l_msg_count                NUMBER;
    l_msg_data                 VARCHAR2(1000);  
    l_lot_control              BOOLEAN := FALSE;              
  BEGIN  
    -- initialize
    inv_quantity_tree_pub.clear_quantity_cache;
    global_variable_initialize;
    IF p_lot_number IS NOT NULL THEN
       l_lot_control := TRUE;
    END IF;
    
    -- query tree for quantities
    inv_quantity_tree_pub.query_quantities(p_api_version_number  => 1.0,
                                           p_init_msg_lst        => NULL,
                                           x_return_status       => l_return_status,
                                           x_msg_count           => l_msg_count,
                                           x_msg_data            => l_msg_data,
                                           p_organization_id     => p_organization_id,
                                           p_inventory_item_id   => p_item_id,
                                           p_tree_mode           => 3,
                                           p_is_revision_control => FALSE,
                                           p_is_lot_control      => l_lot_control,
                                           p_is_serial_control   => FALSE,
                                           p_revision            => NULL,
                                           p_lot_number          => p_lot_number,
                                           p_lot_expiration_date => NULL,
                                           p_subinventory_code   => p_subinventory_code,
                                           p_locator_id          => p_location_id,
                                           p_cost_group_id       => NULL,
                                           p_onhand_source       => inv_quantity_tree_pvt.g_all_subs,
                                           x_qoh                 => g_qoh,
                                           x_rqoh                => g_rqoh,
                                           x_qr                  => g_qr,
                                           x_qs                  => g_qs,
                                           x_att                 => g_att,
                                           x_atr                 => g_atr);
  END;
 
 
--======================================================  
--判断是否数字
--======================================================
  Function IS_NUMBER(P_STRING in VARCHAR2) return number is
    l_number number;
    result number;
  begin
    begin
      select to_number(P_STRING)
        into l_number
        from dual;
      
      result := 1;
    exception
      when others then
      result := 0;
    end;
    
    return result;
  end;  
END XYG_COMMON_UTIL_PKG;

---包:XYG_INV_PICK_CONFIRM_PKG
CREATE OR REPLACE PACKAGE APPS.XYG_INV_PICK_CONFIRM_PKG AS
  /*==================================================
  Copyright (C) XYG Enterprise Co.,Ltd.
             AllRights Reserved
  ==================================================*/
  /*==================================================
  Program Name:
      XYG_INV_PICK_CONFIRM_PKG
  Description:
      This package provides procedure for more efficient
      to pick confirm for move order line. the suggested
      picking will be remove in EBS, because picking rule
      is not very match WMS.

  History:
      1.00  2009-05-13  jiehua.liang  Creation
  ==================================================*/

  /*=======================
   Declare Global Variable
  =========================*/
  g_package_name        VARCHAR2(50) := 'XYG_INV_PICK_CONFIRM_PKG';
  g_appl_id             NUMBER       := 401; -- INV application id
  g_mmtt_rec            mtl_material_transactions_temp%ROWTYPE;

  TYPE mo_line_tbl_type IS TABLE OF VARCHAR2(30)
  INDEX BY PLS_INTEGER;
  g_mo_line_tbl         mo_line_tbl_type;

  /*======================================================================
  Public Procedure : log_messages
       Logic for Log Messages Procedure
  ======================================================================*/
  PROCEDURE log_messages(p_appl_id  NUMBER, p_log_module  VARCHAR2, p_log_message  VARCHAR2);

  /*======================================================================
  Public Procedure : set_mo_line_pc_flag
       Logic for set_mo_line_pc_flag Procedure
  ======================================================================*/
  PROCEDURE set_mo_line_pc_flag(p_mo_line_id  NUMBER, p_flag  VARCHAR2 DEFAULT 'Y');

  /*======================================================================
  Public Procedure : get_mo_line_pc_flag
       Logic for get_mo_line_pc_flag Procedure
  ======================================================================*/
  FUNCTION get_mo_line_pc_flag(p_mo_line_id  NUMBER) RETURN VARCHAR2;

  /*======================================================================
  Public Procedure : insert_row_mmtt
       Logic for insert_row_mmtt Procedure
  ======================================================================*/
  PROCEDURE insert_row_mmtt(p_txn_header_id          NUMBER,
                            p_mo_line_id             NUMBER,
                            p_subinventory_code      VARCHAR2,
                            p_locator_id             NUMBER,
                            p_process_qty            NUMBER,
                            x_return_status          OUT VARCHAR2,
                            x_msg_data               OUT VARCHAR2,
                            p_txn_date                date,
                            --x_transaction_temp_id    OUT NUMBER,
                            x_mmtt_rec               OUT mtl_material_transactions_temp%ROWTYPE);

  /*======================================================================
  Public Procedure : insert_row_mtlt
       Logic for insert_row_mtlt Procedure
  ======================================================================*/
  PROCEDURE insert_row_mtlt(p_mmtt_rec               mtl_material_transactions_temp%ROWTYPE,
                            p_lot_number             VARCHAR2,
                            x_return_status          OUT VARCHAR2,
                            x_msg_data               OUT VARCHAR2);

  /*======================================================================
  Public Procedure : assign_pick_slip_number
       Logic for assign_pick_slip_number Procedure
  ======================================================================*/
  PROCEDURE assign_pick_slip_number(p_mmtt_rec               mtl_material_transactions_temp%ROWTYPE,
                                    x_return_status          OUT VARCHAR2,
                                    x_msg_data               OUT VARCHAR2);

  /*======================================================================
  Public Procedure : process_move_order_line
       Logic for process_move_order_line Procedure
  ======================================================================*/
  PROCEDURE process_move_order_line(p_txn_header_id          NUMBER,
                                    p_mo_line_id             NUMBER,
                                    p_subinventory_code      VARCHAR2,
                                    p_locator_id             NUMBER,
                                    p_lot_number             VARCHAR2,
                                    p_process_qty            NUMBER,
                               

最低0.47元/天 解锁文章
mia502908009
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
微信小程序按钮控件设置呈现效果
zfan520的博客
03-10 5万+
1、效果图 在小程序中完成上述布局，需要设置四个按钮位置，控制每个按钮大小、设置按钮背景颜色，设置标题和图标的位置。 2、代码说明 2.1 index.wxml <view class="block"> <!--呈现标题--> <view class="title-bar"> <text>助手功能模块</text&...
创建物料搬运单demo
chosen1brain的专栏
08-15 1395
--创建物料搬运单 DECLARE -- Common Declarations l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(2) := fnd_api.g_true; l_return_values VARCHAR2(2) := fnd_api.g_false; l_commit VARCHAR
epicor 客制化开发代码
04-27
epicor 客制化开发代码，C#语言，里面有epicor开发基本语法和框架
可靠的客制化系统整合服务
10-23
系统介绍     自19世纪晚期诞生以来，铁路一直是最受欢迎的一种大众交通工具。铁路网络遍布各个国家，旅客的巨大数量使得铁路系统迫切需要一种高效、迅速和可靠的售票系统，以保证旅客的出行。在周末和节假日期间，旅客的数量更加壮观。因此，铁路管理部门想要寻找一种自助式解决方案来提供自动售票功能，而研华公司能够满足这种需求。这种售票系统省略了过去人力售票的多个步骤，因此不仅完全改变了旅客的购票习惯，并且加快了购票速度。    系统要求     为了满足客户的特定需求，研华公司需要在较短的交货周期内生产一种高性能的设备。该款设备需要能够通过插入式访问接口连接多个外围设备，能够经受旅客的持续使用。因
amlogic客制化工具
09-10
amlogic固件客制化修改工具，可以修改产品型号，生产日期，开机动画，开机logo等
flow-3D客制化流程
12-20
详细的介绍了flow-3D如何进行二次开发，客制化的操作步骤
客制化键盘GH60外壳图纸
03-20
客制化机械键盘外壳，GH60 键盘外壳 。SOLIDWORKS 图纸，上机床加工就可，详细尺寸标注。
PLS-00201: 必须声明标识符 'VARTABLETYPE'
cuixie2370的博客
06-03 926
Oracle 10.2.0.4 CREATE OR REPLACE FUNCTION DMP2.strVarList( p_string in varchar2 ) return varTableType ...
INV 模块常用表总结---物料的类别
congdunyi6756的博客
01-06 526
INV 模块常用表总结： inv.mtl_system_items_b 物料主表 常用表和字段： inventory_item_id 物料标识表 segment1 编码 description 描述 or...
物料搬运单Move Order Type
AlexLiu_2019的博客
05-22 106
物料搬运单MoveOrderType MTL_TXN_REQUEST_HEADERS.MOVE_ORDER_TYPE LOOKUP_TYPE LOOKUP_CODE MEANING DESCRIPTION MOVE_ORDER_TYPE 1 Requisition Move Order Requisition MOVE_ORDER_TYPE 2 Replenishment Move Order Type created from re..
ship_confirm API of my test script
雨人 的专栏
09-03 1888
method 2 , exit delivery_id and change the line/orders to shipmethod1: by pick realease API add a parameter: lr_batch_rec. ship_confirm_rule_id:=1;  select wda.delivery_id,ol.shipment_number ,
API-ORACLE EBS MoveOrder自动处理物料搬运单
kikiwhq的专栏
11-04 1048
摘自：ORACLE EBS自动处理物料搬运单_ITPUB博客 ORACLE EBS自动处理物料搬运单 原创Oracle作者：box时间：2016-01-04 18:10:1435960 文档来源：http://blog.csdn.net/cunxiyuan108/article/details/6006961 目的：模拟EBS Form界面的功能，调用标准的API实现自动处理物料搬运单。 实现思路： 1. 先创建一个客户化的表，这个表中用来保存物料搬运单的一些信息。 2. 调用...
SO保留库存
cizha3055的博客
07-17 202
-- Created on 2013-7-8 by ADMINISTRATOR declare -- Local variables here l_rsv_rec inv_reservation_global.mtl_re...
物料搬运单(Move Order)
专注Oracle E-Business Suite
10-08 5059
        物料搬运单是在一个组织内对物料进行移动的请求。它们用于将在仓库或工厂内移动物料的请求流程规范化，以便执行物料补充、改变物料存储地点和质量处理等事务。可以根据使用的来源类型人工或自动生成物料搬运单。        物料搬运单只限于组织内的事务处理使用，组织间的转移需要进行内部申请。                物料搬运单的执行步骤:        1. 创建物料搬运单
物料搬运单(Move Order) 收藏
linsandlins的专栏
04-13 854
        物料搬运单是在一个组织内对物料进行移动的请求。它们用于将在仓库或工厂内移动物料的请求流程规范化，以便执行物料补充、改变物料存储地点和质量处理等事务。可以根据使用的来源类型人工或自动生成物料搬运单。        物料搬运单只限于组织内的事务处理使用，组织间的转移需要进行内部申请。                物料搬运单的执行步骤:        1. 创建物料搬运单
计划工作台中物料ID区分
11-14 569
SELECT msi.sr_inventory_item_id,--真正的物料ID：表MTL_SYSTEM_ITEMS_B中的        msi.inventory_item_id  --工作台中查出来的物料ID   FROM msc_system_items msi
oracle numlist,oracle sql str2numlist numtabletype
weixin_32467749的博客
04-07 375
在实际使用中，经常会有带in的子查询，如where id in (1,2,3)这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池SQL碎片。所以，在实际应用中，可以采用其他方法，将这些in list给绑定起来。如果需要绑定in list，首先，需要创建两个类型(type)：针对数据类型的CREATEOR REPLACE TYPE NUMTABLETYPE as ta...
Inventory 物料库存 mtl_transactions_interface 开发[转]
AlexLiu_2019的博客
05-23 558
Inventory物料库存mtl_transactions_interface开发
Inlist的绑定优化（书摘备查）
wzy0623的专栏
12-29 805
-- 建立类型CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER;CREATE OR REPLACE TYPE vartabletype AS TABLE OF VARCHAR2 (1000); -- 解析字符串CREATE OR REPLACE FUNCTION str2numlist (p_string IN VARCHAR2)   R
android14客制化
最新发布
01-04
Android 14客制化是指将Android操作系统根据用户的需求和喜好进行个性化定制和修改。与传统的手机操作系统相比，Android 14客制化允许用户自定义各种设置和界面样式，以及安装和卸载不同的应用程序。以下是Android ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值