INV Interface介绍

1 INV Items物料物料 Open Interface Unopened API 1 1 快速参考快速参考 物料接口一般用来做期初数据导入 Item发布 参考点内容 功能导航 N INV Items Organization Items N INV Items Organization Items Tools Revisions 并发请求 Import Items N INV Items Import Import Items 接口表 mtl system items interface mtl item revisions interface APIinv item grp create item 错误信息表mtl interface errors 接口FormN A 接口ReportN A Debug ProfileINV Debug Level 大于0即可 详细例子 oracle iface scripts pkg mtl sys items iface cux inv item load pvt item load 官方文档 数据验证包 invpopif inopinp open interface process INVPVALI INVPVDR5 1 2 开发参考开发参考 接口表 错误信息表接口表 错误信息表 Table List 接口表数据表说明 mtl system items interface mtl system items b mtl system items tl 物料 mtl item revisions interface mtl item revisions b mtl item revisions tl 物料版本 可选 不能单独使用 关联字段 organization code item number mtl interface errors错误信息表 Table Relation mtl system items interface organization code mtl item revisions interface organization code AND mtl system items interface item number mtl item revisions interface item number mtl interface errors SELECT mie error message FROM mtl system items interface mii mtl interface errors mie WHERE mii transaction id mie transaction id 并发程序并发程序Import Items N INV Items Import Import Items 参数说明 All OrganiztionsYes处理所有Org数据 No仅处理当前用户Change到的Org数据 Validate Items是否验证接口数据 请选Yes Process Items是否处理接口数据 请选Yes Delete Processed Rows是否删除成功处理过的接口数据 请选Yes Process Set Null for All 放空处理所有数据 如果输入XXX 则仅处理SET PROCESS ID XXX的记录 Create or Update Items1处理新增的物料 2处理更新的物料 3没用过 参数组合 Validate ItemsProcess Items Delete Processed Rows 作用 YesNoNo验证未验证过的数据 NoYesNo处理已验证过的数据 NoNoYes删除已处理过的数据 YesYesYes啥都做 通常都用这个 Interface Form 无 所以用户无法参与处理 1 3 关键字段 参数 关键字段 参数 最简单例子最简单例子 关键字段 mtl system items interface 字段说明 process flag 插入接口时需要给1 1 pending 2 assign complete 3 assign validation failed 4 validation succeeded imported failed 5 import in process 7 import succeeded transaction type CREATE或者UPDATE 相对组织来说的 比如一个物料 Master Inv Organization已经建立了 但本组织 还没有 这个时候仍要用CREATE set process id随便给 默认0 organization id给organization code也行 segment1 通常物料弹性域只启用1段 所以这里仅列出segment1 给 item number也行 description物料描述 primary uom code 不给的话从Template取 Template没有从Profile INV Default Primary Unit of Measure 取 再没有则报错 template id 模版ID 也可以给template name 5个who字段习惯上都要给 inv item grp item rec type 字段说明 organization idCurrent Inv Organization 给organization code也行 item number物料代码 description物料描述 primary uom code 不给的话从Template取 Template没有从Profile INV Default Primary Unit of Measure 取 再没有则报错 template id 模版ID 5个who字段运行时自动获得 mtl item revisions interface 字段说明 字段说明 process flag 插入接口时需要给1 1 pending 2 assign complete 3 assign validation failed 4 validation succeeded imported failed 5 import in process 7 import succeeded transaction typeCREATE或者UPDATE set process id随便给 organization id给organization code也行 item number物料编码 revision物料版本 revision label版本标签 implementation date实施日期 effectivity date生效日期 5个who字段习惯上都要给 Open Interface例子 模版创建例子 模版创建Item R 12 1 3环境测试通过 环境测试通过 预先定义模版 然后在接口表中引用该模版 这样所需的字段最少 脚本如下 DECLARE l iface rec inv mtl system items interface ROWTYPE l user id NUMBER 0 User ID Sysadmin here l mst mfg org id NUMBER 204 Master Inv Organization l cur mfg org id NUMBER 4555 Current Inv Organization l rev rec inv mtl item revisions interface ROWTYPE BEGIN l iface rec last update date SYSDATE l iface rec last updated by l user id l iface rec creation date SYSDATE l iface rec created by l user id l iface rec last update login 1 l iface rec process flag 1 l iface rec transaction type CREATE l iface rec set process id 2014021001 l iface rec segment1 TLH0001 l iface rec primary uom code Kg l iface rec description For Interface Test l iface rec template id 2 Purchased Item table inv mtl item templates Master Inv Organization l iface rec organization id l mst mfg org id INSERT INTO mtl system items interface VALUES l iface rec Current Inv Organization l iface rec organization id l cur mfg org id INSERT INTO inv mtl system items interface VALUES l iface rec COMMIT Item 版本 l rev rec last update date SYSDATE l rev rec last updated by l user id l rev rec creation date SYSDATE l rev rec created by l user id l rev rec last update login 1 l rev rec process flag 1 l rev rec transaction type CREATE l rev rec set process id 2014021001 l rev rec item number TLH0001 l rev rec revision 0 l rev rec revision label A l rev rec implementation date sysdate l rev rec effectivity date sysdate l rev rec organization id l mst mfg org id INSERT INTO inv mtl item revisions interface VALUES l rev rec COMMIT END 提交请求 Import Items Open Interface例子 组织分配 例子 组织分配 R 12 1 3环境测试通过 环境测试通过 仅需要给定组织和物料 物料已经在组组织存在 脚本如下 DECLARE l iface rec inv mtl system items interface ROWTYPE l user id NUMBER 0 User ID Sysadmin here l cur mfg org id NUMBER 4700 Current Inv Organization BEGIN l iface rec last update date SYSDATE l iface rec last updated by l user id l iface rec creation date SYSDATE l iface rec created by l user id l iface rec last update login 1 l iface rec process flag 1 l iface rec transaction type CREATE l iface rec set process id 2014021002 l iface rec organization id l cur mfg org id l iface rec segment1 TLH0001 INSERT INTO inv mtl system items interface VALUES l iface rec commit END 提交请求 Import Items Open API例子 模版创建例子 模版创建Item R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE l item rec inv item grp item rec type x item rec inv item grp item rec type x error tbl inv item grp error tbl type l template id NUMBER 2 Purchased Item table inv mtl item templates x return status VARCHAR2 1 l user id NUMBER 0 User ID Sysadmin here l mst mfg org id NUMBER 204 Master Inv Organization l cur mfg org id NUMBER 4555 Current Inv Organization BEGIN Initialize first or create by will be 1 fnd global apps initialize user id l user id resp id 20420 resp appl id 1 Item l item rec item number TLH0003 l item rec description For API Test l item rec primary uom code KG Master Inv Organization first then Current Inv Organization l item rec organization id l mst mfg org id API inv item grp create item p commit fnd api g false p item rec l item rec x item rec x item rec x return status x return status x error tbl x error tbl p template id l template id Result IF x return status fnd api g ret sts success THEN ROLLBACK FOR i IN 1 x error tbl COUNT LOOP dbms output put line Transaction ID x error tbl i transaction id dbms output put line Unique ID x error tbl i unique id dbms output put line Message Name x error tbl i message name dbms output put line Message Text x error tbl i message text dbms output put line Table Name x error tbl i table name dbms output put line Column Name x error tbl i column name dbms output put line Organization ID x error tbl i organization id END LOOP IF x return status fnd api g ret sts unexp error THEN RAISE fnd api g exc unexpected error ELSIF x return status fnd api g ret sts error THEN RAISE fnd api g exc error END IF ELSE COMMIT dbms output put line x item rec inventory item id END IF END Open API例子 更新物料状态 例子 更新物料状态 R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE l item rec inv item grp item rec type x item rec inv item grp item rec type x error tbl inv item grp error tbl type x return status VARCHAR2 1 l user id NUMBER 0 User ID Sysadmin here l cur mfg org id NUMBER 204 Current Inv Organization BEGIN Initialize first or create by will be 1 fnd global apps initialize user id l user id resp id 20420 resp appl id 1 Item l item rec item number TLH0003 l item rec inventory item status code Inactive Active Inactive l item rec organization id l cur mfg org id API inv item grp update item p commit fnd api g false p item rec l item rec x item rec x item rec x return status x return status x error tbl x error tbl Result IF x return status fnd api g ret sts success THEN ROLLBACK FOR i IN 1 x error tbl COUNT LOOP dbms output put line Transaction ID x error tbl i transaction id dbms output put line Unique ID x error tbl i unique id dbms output put line Message Name x error tbl i message name dbms output put line Message Text x error tbl i message text dbms output put line Table Name x error tbl i table name dbms output put line Column Name x error tbl i column name dbms output put line Organization ID x error tbl i organization id END LOOP ELSE COMMIT dbms output put line x item rec item number END IF END 1 4 已知问题已知问题 数据重复问题数据重复问题 mtl system items interface没有unique索引 transaction id不算 所以注意接口数据 不要重复 否则Master Inv Organization的物料可以进入系统 但其他组织的就无法进 去 报物料已存在 属性控制层次问题属性控制层次问题 导非Master Inv Organization的Item时 Master Org Control的字段需要保持Master Inv Organization的值 mtl item attributes中conctrol level 1 还有一些字段也需要保持 Master Inv Organization的值 否则接口会报错 它们是 create supply flag dual uom control buyer id allowed units lookup code purchasing item flag internal order flag inventory item flag 2 INV Item Categories物料类别物料类别 Open Interface Unopened API 2 1 快速参考快速参考 做物料类别分配的时候 主要需考虑类别的控制层次 一个类别集下是否允许多类别 目前接口和API都不支持Update操作 需要通过Delete后Create来实现 API有Update功能 参考点内容 功能导航 定义类别代码 N INV Setup Items Categories Category Codes 定义类别集 N INV Setup Items Categories Category Sets 分配物料类别 N INV Items Organization Items Tools Categories 并发请求N INV Items Import Item Category Assignment Open Interface 接口表mtl item categories interface API inv item category pub delete category assignment inv item category pub create category assignment 错误信息表 接口FormN 接口ReportN A Debug Profile 详细例子 官方文档 数据验证包 2 2 开发参考开发参考 接口表 错误信息表接口表 错误信息表 Table List 接口表数据表说明 mtl item categories interfacemtl item categories物料的类别 错误信息表 并发程序并发程序Item Category Assignment Open Interface N INV Items Import Item Category Assignment Open Interface 参数说明 Set Process ID对应接口字段set process id 参数组合 作用 Interface Form N 2 3 关键字段 参数 关键字段 参数 最简单例子最简单例子 关键字段关键字段 mtl item categories interface 字段说明 字段说明 set process id导入批号 transaction typeCREATE或者DELETE 没有Update 其 DELETE CREATE process flag1 organization id库存组织 inventory item id物料ID category set id类别集 category id类别 5个who字段习惯上都给 Open Interface例子 例子 R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE l iface rec mtl item categories interface ROWTYPE l cur mfg org id NUMBER 204 Current Inv Organization l user id NUMBER 0 User ID Sysadmin here BEGIN l iface rec last updated by l user id l iface rec last update date SYSDATE l iface rec created by l user id l iface rec creation date SYSDATE l iface rec set process id 20140921001 l iface rec transaction type CREATE l iface rec process flag 1 l iface rec organization id l cur mfg org id l iface rec inventory item id 223083 item Code TLH0003 l iface rec category set id 1100000284 l iface rec category id 11833 INSERT INTO mtl item categories interface VALUES l iface rec commit END 提交请求 Unopened API最简单例子 物料新增最简单例子 物料新增 category R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE x return status VARCHAR2 1 x msg count NUMBER x msg data VARCHAR2 2000 x errorcode VARCHAR2 30 l cur mfg org id NUMBER 204 Current Inv Organization l user id NUMBER 0 User ID Sysadmin here BEGIN Initialize first or create by will be 1 fnd global apps initialize user id l user id resp id 20420 resp appl id 1 inv item category pub create category assignment p api version 1 0 p init msg list fnd api g true p commit fnd api g false x return status x return status x errorcode x errorcode x msg count x msg count x msg data x msg data p category id 4836 84716052 p category set id 1100000205 FISCAL CLASSIFICATION p inventory item id 223083 TLH0003 p organization id l cur mfg org id IF x return status fnd api g ret sts success THEN ROLLBACK dbms output put line error code x errorcode fnd msg pub count and get p count x msg count p data x msg data dbms output put line error count to char x msg count dbms output put line REPLACE x msg data chr 0 FOR i IN 2 x msg count LOOP x msg data fnd msg pub get dbms output put line REPLACE x msg data chr 0 END LOOP ELSE COMMIT dbms output put line Successfully END IF END 物料界面查询结果 Unopened API最简单例子 物料删除最简单例子 物料删除category R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE x return status VARCHAR2 1 x msg count NUMBER x msg data VARCHAR2 2000 x errorcode VARCHAR2 30 l cur mfg org id NUMBER 204 Current Inv Organization l user id NUMBER 0 User ID Sysadmin here BEGIN Initialize first or create by will be 1 fnd global apps initialize user id l user id resp id 20420 resp appl id 1 inv item category pub Delete Category Assignment p api version 1 0 p init msg list fnd api g true p commit fnd api g false x return status x return status x errorcode x errorcode x msg count x msg count x msg data x msg data p category id 4836 84716052 p category set id 1100000205 FISCAL CLASSIFICATION p inventory item id 223083 TLH0003 p organization id l cur mfg org id IF x return status fnd api g ret sts success THEN ROLLBACK dbms output put line error code x errorcode fnd msg pub count and get p count x msg count p data x msg data dbms output put line error count to char x msg count dbms output put line REPLACE x msg data chr 0 FOR i IN 2 x msg count LOOP x msg data fnd msg pub get dbms output put line REPLACE x msg data chr 0 END LOOP ELSE COMMIT dbms output put line Successfully END IF END 物料界面查询结果 2 4 已知问题已知问题 3 INV Material Transactions物料事务处理物料事务处理 Open Open Interface Interface 3 1 快速参考快速参考 物料事务处理的内容非常丰富 有库存本身的三种杂项 子库转移 组织间转移 物料搬 运单促发的库存事务 采购入库和退回 RMA入库和退回 销售订单挑库和发货 WIP装 配件完工和退回 WIP组件发料和退回 平均成本更新等等 都可以通过物料事务接口来 处理 不管哪种类型 关键是控制Source和Type字段 transaction type id transaction source id 参考点内容 功能导航 N INV Transactions Miscellaneous Transactions N INV Transactions Material Transactions 并发请求 Process transaction interface N INV Setup Transactions Interface Managers Process transaction interface Tools Launch Manager 接口表 mtl transactions interface mtl transaction lots interface mtl serial numbers interface cst comp snap interface 错误信息表mtl transactions interface 接口FormN INV Transactions Transaction Open Interface 接口ReportN A Debug ProfileINV Debug Level 大于0即可 详细例子 oracle iface scripts pkg mtl misc trx mtl subinv transfer wip issue ifa ce 官方文档115mfgapi pdf Oracle Manufacturing APIs and Open Interfaces Manual 数据验证包 3 2 开发参考开发参考 接口表 错误信息表接口表 错误信息表 Table List 接口表数据表说明 mtl transactions interface mtl material transactions mtl transaction accounts 物料事务 明细分录 mtl transaction lots interfacemtl transaction lot numbers 批次 可选 视Item是否 启用批次控制 mtl serial numbers interfacemtl serial numbers 序列号 可选 视Item是 否启用序列号控制 cst comp snap interface cst comp snapshot 仅当做WIP装配件完工或 退回且组织使用平均成本 时使用 接口表数据表说明 mtl transactions interface错误信息表 不要使用xxx temp接口表 如mtl material transactions temp 用它们会绕过系统id验 证 直接进入数据表 Table Relation 仅启用批次 mtl transactions interface transaction interface id mtl transaction lots interface transaction interface id 仅启用序列 mtl transactions interface transaction interface id mtl serial numbers interface transaction interface id 同时启用批次 序列 mtl transactions interface transaction interface id mtl transaction lots interface transaction interface id mtl transaction lots interface serial transaction temp id mtl serial numbers interface transaction interface id mtl transactions interface select mti error code mti error explanation from mtl transactions interface mti 并发程序并发程序Process transaction interface N INV Setup Transactions Interface Managers Process transaction interface Tools Launch Manager 这个是库存事务处理主程序 正式环境一般是Schedule运行的 参数说明 参数组合 作用 并发程序并发程序Inventory transaction worker 通常这个是由Process transaction interface根据接口中是否有数据自动调用的 无需手工 跑 这里介绍它 主要是因为也可以在插入数据到接口表后 立即用程序调用它 达到类似联 机处理的效果 并发程序并发程序Cost Manager N INV Setup Transactions Interface Managers Cost Manager Tools Launch Manager 上面的请求如果顺利跑完 物料事务已经做完 但是并没有产生财务信息 必须由Cost Manager来完成此事 正式环境一般是Schedule运行的 Interface Form N INV Transactions Transaction Open Interface 对于未处理或者失败的记录可以在这里查看 更正 重新提交 打勾或者Tools Resubmit All 保存之后仍需要运行接口请求才能被处理 3 3 关键字段 参数 关键字段 参数 最简单例子最简单例子 关键字段关键字段 mtl transactions interface 字段说明 transaction mode 给3 2 Concurrent Process transaction interface不处理 需要程序 调用Inventory transaction worker处理 3 Background 由Process transaction interface处理 process flag 给1 1 Yes 2 No 3 Error transaction type id 事务处理类型 关键的是它关联的transaction action id 系 统标准事务的几个ID是固定的 transaction source id 账户别名杂项 用别名ID即disposition id 账户杂项 用账户ID即code combination id WIP物料事务 用任务ID即wip entity id SO物料事务 用mtl sales orders sales order id 子库转移或组织间转移 为空 organization idCurrent Inv Organization inventory item id物料ID subinventory code子库代码 transfer organization子库转移事务时的Current Inv Organization transfer subinventory子库转移事务时的目标子库代码 transaction quantity数量 收是正 出 子库转移是负 平均成本更新是0 transaction uom单位 transaction date事务处理日期 source code源代码 随便给 source header id源头ID 随便给 source line id源行ID 随便给 5个who字段习惯上都要给 Open Interface例子 杂项接收 例子 杂项接收 R 12 1 3环境测试通过 环境测试通过 账户别名杂项接收事务处理 算是比较简单的例子了 脚本如下 DECLARE l iface rec inv mtl transactions interface ROWTYPE l cur mfg org id NUMBER 82 Current Inv Organization l user id NUMBER 0 User ID Sysadmin here BEGIN l iface rec last update date SYSDATE l iface rec last updated by l user id l iface rec creation date SYSDATE l iface rec created by l user id l iface rec last update login 1 SELECT mtl material transactions s NEXTVAL INTO l iface rec transaction interface id FROM dual l iface rec transaction header id l iface rec transaction interface id l iface rec transaction mode 3 l iface rec process flag 1 l iface rec transaction type id 41 mtl transaction types l iface rec transaction source id 2 l iface rec organization id l cur mfg org id l iface rec inventory item id 5194 l iface rec subinventory code LLJ01 l iface rec transaction quantity 12345 l iface rec transaction uom GG l iface rec transaction date SYSDATE l iface rec source code Test Only l iface rec source header id 987654321 l iface rec source line id 987654321 INSERT INTO inv mtl transactions interface VALUES l iface rec Commit END Open Interface例子 组织间转移 例子 组织间转移 R 12 1 3环境测试通过 环境测试通过 脚本如下 DECLARE l iface rec inv mtl transactions interface ROWTYPE l cur mfg org id NUMBER 82 Current Inv Organization l trf mfg org id NUMBER 444 Current Inv Organization l user id NUMBER 0 User ID Sysadmin here BEGIN l iface rec last update date SYSDATE l iface rec last updated by l user id l iface rec creation date SYSDATE l iface rec created by l user id l iface rec last update login 1 SELECT mtl material transactions s NEXTVAL INTO l iface rec transaction interface id FROM dual l iface rec