BOM:工艺路线导入程序

1.应用环境：12.0.6
2.业务需求：部分产品在生产过程根据特殊订单需要增加一项指标（测试），导致与实际工时变差比较大，决定启用替代工艺。具体操作替代工序就是把标准工艺路线第一工序产能和人数不同，其他工序 不变。  

3.测试过程：
  由于之前在11i，曾经进行过类似的导入，所以就想测试已经一路PASS，
  先把Routing，Operations，Resource分别导入接口表，然后运行《清单和工艺路线接口》程序，  总是提示如下：

 Resource为默认标准工序里面资源，手动进行删除，在把Resource进行insert 到接口表，  还是报同样错误，资源也没有导入。推测可能是标准工序代码原因，尽管把参考没有勾选。
  从oracle网站下载两个文档进行参考，感觉这样操作，没有什么问题呀?
  于是换一种思路是先导入Routing&Opertion，运行接口程序，在对要修改Resource进行update，
  再运行接口程序，没有问题了。
 

4.后台导入程序Script：

  ----Routing
 INSERT INTO BOM_OP_ROUTINGS_INTERFACE
      (
       ORGANIZATION_ID,
       --BOR.ORGANIZATION_CODE,
       ASSEMBLY_ITEM_ID,
       --BOR.ASSEMBLY_ITEM_NUMBER,
       ALTERNATE_ROUTING_DESIGNATOR,
       PROCESS_FLAG,
       TRANSACTION_TYPE,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_LOGIN
       )
 SELECT BOR.ORGANIZATION_ID,
       --BOR.ORGANIZATION_CODE,
       BOR.ASSEMBLY_ITEM_ID,
       --BOR.ASSEMBLY_ITEM_NUMBER,
       'SXH',--BOR.ALTERNATE_ROUTING_DESIGNATOR,
       1,--BOR.PROCESS_FLAG,
       'CREATE',--BOR.TRANSACTION_TYPE,
       SYSDATE,--BOR.CREATION_DATE,
       0,--BOR.CREATED_BY,
       SYSDATE,--BOR.LAST_UPDATE_DATE,
       0,--BOR.LAST_UPDATED_BY,
       -1--BOR.LAST_UPDATE_LOGIN
  FROM BOM_OPERATIONAL_ROUTINGS BOR
 WHERE BOR.ASSEMBLY_ITEM_ID=5843;

  --- Operations--
INSERT INTO BOM_OP_SEQUENCES_INTERFACE
     (
       ORGANIZATION_ID,
       --ORGANIZATION_CODE,
       ASSEMBLY_ITEM_ID,
      -- ASSEMBLY_ITEM_NUMBER,
       ALTERNATE_ROUTING_DESIGNATOR,
       OPERATION_SEQ_NUM,
       STANDARD_OPERATION_ID,
       --OPERATION_CODE,     
       REFERENCE_FLAG,
        --DEPARTMENT_CODE,
       EFFECTIVITY_DATE,
       PROCESS_FLAG,
       TRANSACTION_TYPE,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_LOGIN
     )
SELECT 
       BOR.ORGANIZATION_ID,
       --BOS.ORGANIZATION_CODE,
       BOR.ASSEMBLY_ITEM_ID,
      -- BOS.ASSEMBLY_ITEM_NUMBER,
       'SXH',--BOS.ALTERNATE_ROUTING_DESIGNATOR,
       BOS.OPERATION_SEQ_NUM,
       BOS.STANDARD_OPERATION_ID,
       --BOS.OPERATION_CODE,     
       BOS.REFERENCE_FLAG,
        --BOS.DEPARTMENT_CODE,
       SYSDATE,--BOS.EFFECTIVITY_DATE,      
       1,--BOS.PROCESS_FLAG,
       'CREATE',--BOS.TRANSACTION_TYPE,
       SYSDATE,--BOS.CREATION_DATE,
       0,--BOS.CREATED_BY,
       SYSDATE,--BOS.LAST_UPDATE_DATE,
       0,--BOS.LAST_UPDATED_BY,
       -1--BOS.LAST_UPDATE_LOGIN
  FROM BOM_OPERATION_SEQUENCES BOS,
       BOM_OPERATIONAL_ROUTINGS BOR
 WHERE 1=1
   AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
   AND BOR.ROUTING_SEQUENCE_ID =BOS.ROUTING_SEQUENCE_ID
   AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
   AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
       ) OR (TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd')))
   AND BOR.ASSEMBLY_ITEM_ID=5843;

------Resources--
INSERT INTO BOM_OP_RESOURCES_INTERFACE
     (
       ORGANIZATION_ID,
       --ORGANIZATION_CODE,
       ASSEMBLY_ITEM_ID,
       --BORE.ASSEMBLY_ITEM_NUMBER,
       ALTERNATE_ROUTING_DESIGNATOR,
       --ROUTING_SEQUENCE_ID,
       OPERATION_SEQUENCE_ID,
       OPERATION_SEQ_NUM,
       RESOURCE_SEQ_NUM,
       RESOURCE_ID,
       RESOURCE_CODE,
       BASIS_TYPE,      
       USAGE_RATE_OR_AMOUNT,          
       ASSIGNED_UNITS,
       /*SCHEDULE_FLAG,
       AUTOCHARGE_TYPE, */ 
       EFFECTIVITY_DATE,   
       PROCESS_FLAG,
       TRANSACTION_TYPE,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       LAST_UPDATE_LOGIN
     )
  SELECT
       BOR.ORGANIZATION_ID,
       --BORE.ORGANIZATION_CODE,
       BOR.ASSEMBLY_ITEM_ID,
       --BORE.ASSEMBLY_ITEM_NUMBER,
       BOR.ALTERNATE_ROUTING_DESIGNATOR,
       --BOS.ROUTING_SEQUENCE_ID,
       BOS.OPERATION_SEQUENCE_ID,
       BOS.OPERATION_SEQ_NUM,
       BORE.RESOURCE_SEQ_NUM,
       BORE.RESOURCE_ID,
       BORE.RESOURCE_CODE,
       BORE.BASIS_TYPE,      
       0.21645,--BORE.USAGE_RATE_OR_AMOUNT,          
       13,--BORE.ASSIGNED_UNITS,
       /*BORE.SCHEDULE_FLAG,
       BORE.AUTOCHARGE_TYPE, */ 
       SYSDATE,--BORE.EFFECTIVITY_DATE,     
       1,--BORE.PROCESS_FLAG,
       'UPDATE',--BORE.TRANSACTION_TYPE,
       SYSDATE,--BORE.CREATION_DATE,
       0,--BORE.CREATED_BY,
       SYSDATE,--BORE.LAST_UPDATE_DATE,
       0,--BORE.LAST_UPDATED_BY,
       -1--BORE.LAST_UPDATE_LOGIN
  FROM BOM_OPERATION_RESOURCES_V BORE,
       BOM_OPERATION_SEQUENCES BOS,
       BOM_OPERATIONAL_ROUTINGS BOR
 WHERE 1=1
   AND BOS.OPERATION_SEQUENCE_ID=BORE.OPERATION_SEQUENCE_ID
   AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL
   AND BOR.ROUTING_SEQUENCE_ID =BOS.ROUTING_SEQUENCE_ID
   AND ((TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')<=TO_CHAR(SYSDATE,'yyyy-mm-dd')
   AND (BOS.DISABLE_DATE IS NULL OR TO_CHAR(BOS.DISABLE_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd'))
       ) OR (TO_CHAR(BOS.EFFECTIVITY_DATE,'yyyy-mm-dd')>=TO_CHAR(SYSDATE,'yyyy-mm-dd')))
   AND BOR.ASSEMBLY_ITEM_ID=5843

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-630471/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-630471/