ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 导入客户物料总结

导入客户物料总结

Linux操作系统 作者:kawontony 时间:2013-11-06 10:02:46 0 删除 编辑
/*
包括了两个接口表,分别为:
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
需要再inv职责下,提交两个请求
Import
Customer Items  -- 处理头接口表的记录
Import Customer Item Cross References --
处理交叉接口表中的记录
两个接口表分别对应数据库基表
MTL_CUSTOMER_ITEMS
MTL_CUSTOMER_ITEM_XREFS
相互之间为主从关系
---
关于客户编码的一些说明
据功能顾问介绍,客户编码可以分地址层和客户层,且客户编码和系统编码是多对多的关系
因此在被导出数据中,客户+客户地址+客户编码
有可能相同,所以在处理的时候,不能再需要插入接口表中,插入1
次即可
---

还有表中的org_id应该和主组织有关系
---
如果客户地点的地址是使用系统中连接的方式,可以使用函数ARP_ADDR_PKG.FORMAT_ADDRESS做计算
如:
Select 
ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE
                                 
,LOC.ADDRESS1
                                 
,LOC.ADDRESS2
                                 
,LOC.ADDRESS3
                                 
,LOC.ADDRESS4
                                 
,LOC.CITY
                                 
,LOC.COUNTY
                                 
,LOC.STATE
                                 
,LOC.PROVINCE
                                 
,LOC.POSTAL_CODE
                                 
,TERR.TERRITORY_SHORT_NAME) From hz_locations
LOC
                                  ,FND_TERRITORIES_VL TERR  Where
LOC.location_id=890
                                     AND LOC.COUNTRY =
TERR.TERRITORY_CODE(+);
下面是导入客户接口的实例
INSERT INTO MTL_CI_INTERFACE
 
(ITEM_DEFINITION_LEVEL,
   CUSTOMER_ITEM_DESC,
  
CUSTOMER_ITEM_NUMBER,
   ADDRESS_ID,
   COMMODITY_CODE_ID,
  
CONTAINER_ITEM_ORG_ID,
   CUSTOMER_ID,
   CREATED_BY,
  
CREATION_DATE,
   INACTIVE_FLAG,
   LAST_UPDATE_DATE,
  
LAST_UPDATED_BY,
   LOCK_FLAG,
   PROCESS_FLAG,
   PROCESS_MODE,
  
TRANSACTION_TYPE)
  select 3
        ,cic.customer_dec
       
,cic.customer_segment
        ,cic.customer_site_id
        ,1
       
,103
        ,cic.CUSTOMER_ID
        ,-1
        ,sysdate
       
,2
        ,sysdate
        ,-1
        ,'Y'
        ,1
       
,1
        ,'CREATE'
    from cux_import_customer_item cic
    where
cic.process_flag is null;
--- 实际再上面应该使用distinct的
declare
  i
number;
begin
  for c in (select distinct
cic.customer_id
                          
,cic.customer_site_id
                          
,cic.customer_segment
              from cux_import_customer_item cic)
loop
    i := 1;
    for f in (select cic.item_id
                from
cux_import_customer_item cic
               where cic.customer_id =
c.customer_id
                 and cic.customer_site_id =
c.customer_site_id
                 and cic.customer_segment =
c.customer_segment) loop
      INSERT INTO MTL_CI_XREFS_INTERFACE
       
(PROCESS_FLAG,
         PROCESS_MODE,
        
LAST_UPDATE_DATE,
         LAST_UPDATED_BY,
        
CREATION_DATE,
         CREATED_BY,
         TRANSACTION_TYPE,
        
CUSTOMER_ITEM_NUMBER,
         INACTIVE_FLAG,
        
ADDRESS_ID,
         CUSTOMER_ID,
        
MASTER_ORGANIZATION_ID,
         INVENTORY_ITEM_ID,
        
PREFERENCE_NUMBER,
         LOCK_FLAG,
        
ITEM_DEFINITION_LEVEL)
      values
        (1,
         1,
        
SYSDATE,
         -1,
         SYSDATE,
         -1,
        
'CREATE',
         c.customer_segment ,
         2,
        
c.customer_site_id ,
         c.customer_id ,
         103,
        
f.item_id ,
         i,
         'Y',
         3);
         i
:=i+1;
    end loop;
  end loop;
end;
*/

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

请登录后发表评论 登录
全部评论

注册时间:2010-11-17

  • 博文量
    164
  • 访问量
    362754