EBS采购到入库所经历的表

11i 分销 采购到入库所经历的表 购到 经历 入库 采购到入库所经历的表 0 请购单 创建请购单方式有 a 从外挂系统导入请购的接口表PO REQUISITIONS INTERFACE ALL 并允许请求 名称 导入申请 SELECT FROM po requisitions interface all WHERE interface source code TEST KHJ b 在系统中创建请购单 路径 PO 申请 申请 请购单头信息 SELECT prh requisition header id prh authorization status 未审 批时为INCOMPLETE 审批完后为 FROM po requisition headers all prh WHERE prh segment1 600000 AND prh type lookup code PURCHASE 请购单行信息 SELECT prl requisition line id prl FROM po requisition lines all prl WHERE prl requisition header id IN SELECT prh requisition header id FROM po requisition headers all prh WHERE prh segment1 600000 AND prh type lookup code PURCHASE 请购单分配行 SELECT FROM po req distributions all prda WHERE prda requisition line id IN SELECT prl requisition line id FROM po requisition lines all prl WHERE prl requisition header id IN SELECT prh requisition header id FROM po requisition headers all prh WHERE prh segment1 600000 AND prh type lookup code PURCHASE 1 采购订单的创建 路径 PO 采购订单 采购订单 po headers all 采购订单头表 SELECT pha po header id pha segment1 pha agent id pha type lookup code 标准采购单为STANDARD 一揽子协议为 BLANKET decode pha approved flag R pha approved flag nvl pha authorization status INCOMPLETE 审批 未审批时为INCOMPLETE 审批后为APPROVED po headers sv3 get po status pha po header id 刚下完采购单 未审批时 po状态为未完成 审批后 状态为批准 FROM po headers all pha WHERE segment1 300446 采购单号码 po lines all 采购订单行表 SELECT pla po line id pla line type id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 取已审批销售订单头和行的数据 涉及表 Po headers all Po lines all 逻辑如下 限制头表的如下属性 并通过Po header id把头 行表关联起来 APPROVED FLAG Y po line locations all 采购订单行的发送表 路径 PO 采购订单 采购订单 发 运 T po line id po lines all po line id 当点击发运按钮时 系统会自动创建第一行发运行 可根据需要手工创建新的发运行 例如同一采购订单行的物料可能会发往不同的地点 此表记录物料发送情况 下面为取订单与其发运的关系 可能存在多次发运 SELECT FROM po line locations all plla WHERE plla po line id SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 或者 SELECT FROM po line locations all plla WHERE plla po header id SELECT po header id FROM po headers all WHERE segment1 300446 4 po distributions all 采购订单发送行的分配表 路径 PO 采购订单 采购 订单 发运 T 分配 T line location id po line location all line location id 发往同一地点的物料也可能放在不同的子库存 此表记录物料分配情况 SELECT FROM po distributions all pda WHERE pda line location id IN SELECT plla line location id FROM po line locations all plla WHERE plla po line id SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 或者 SELECT FROM po distributions all WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 或者 SELECT FROM po distributions all pda WHERE pda po line id SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 对于po distribution all 表而言 如果其SOURCE DISTRIBUTION ID 有值 其对应于计划采购单发放 以上各表从上到下是一对多关系的 po releases all 订单发放 该表包含一揽子协议以及计划采购单的release 对于每一张发放的一揽子协议或者 计划采购单都有相关行与之对应 其包含采购员 日期 释放状态 释放号码 每一个释放行都有至少一条的采购单的发 运信息与之对应 PO LINE LOCATIONS ALL 每做一次Realese PO distributions all就会新增一条记录 这是计划订单的 特性 SELECT FROM po releases all WHERE po header id 接收 路径 INV 事务处理 接收 接收 1 rcv shipment headers 接收发送头表 记录采购订单的接收情况的头表 SELECT FROM rcv shipment headers rsh WHERE rsh shipment header id IN SELECT shipment header id FROM rcv shipment lines WHERE po header id 4105 2 rcv shipment lines 接收发送行表 记录采购订单的发送的行的接收情况 SELECT FROM rcv shipment lines WHERE po header id 4105 3 rcv transactions 接收事务处理表 记录采购订单的发送行的RECEIVE的信息 SELECT rt transaction id rt transaction type rt destination type code rt FROM rcv transactions rt WHERE rt interface source code RCV AND rt source document code PO AND rt po header id SELECT pha po header id FROM po headers all pha WHERE segment1 300446 OR rt po line id IN SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 OR rt shipment header id SELECT rsh shipment header id FROM rcv shipment headers rsh WHERE shipment header id IN SELECT shipment header id FROM rcv shipment lines WHERE po header id 4105 OR rt shipment line id IN SELECT shipment line id FROM rcv shipment lines WHERE po header id 4105 4 rcv receiving sub ledger 暂记应付表 记录采购订单接收后 产生的暂记应付信息 接收事务处理产生的分配行 SELECT FROM rcv receiving sub ledger WHERE rcv transaction id IN SELECT transaction id FROM rcv transactions WHERE po header id 4105 接受 路径 INV 事务处理 接收 接收事务处理 接收事务处理 接收之后 其实现在还并没有入库 rcv transactions 接收事务处理表 记录采购订单的发送行的ACCEPT的信息 SELECT rt transaction id rt transaction type rt destination type code rt FROM rcv transactions rt WHERE rt interface source code RCV 做接收的条件 AND rt source document code PO 做接收的条件 AND rt transaction type RECEIVE 做接收的条件 AND rt destination type code RECEIVE 做接收的条件 AND rt po header id SELECT pha po header id FROM po headers all pha WHERE segment1 300446 OR rt po line id IN SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 OR rt shipment header id SELECT rsh shipment header id FROM rcv shipment headers rsh WHERE shipment header id IN SELECT shipment header id FROM rcv shipment lines WHERE po header id 4105 OR rt shipment line id IN SELECT shipment line id FROM rcv shipment lines WHERE po header id 4105 入库 因为涉及入库操作 所以 在库存事务处理表中会留下相应的记录 即在Mtl material transactions表中 会存在相应的两条入库记录 SELECT mmt FROM mtl material transactions mmt WHERE mmt transaction type id 18 po接收 AND mmt transaction action id 27 接收至库存 AND mmt transaction source type id 1 采购订单 AND mmt transaction source id 4105 po header id OR mmt rcv transaction id IN SELECT rt transaction id FROM rcv transactions rt WHERE rt interface source code RCV AND rt source document code PO AND rt po header id SELECT pha po header id FROM po headers all pha WHERE segment1 300446 此时 rcv transactions的状态变为 SELECT rt transaction id rt transaction type rt destination type code rt FROM rcv transactions rt WHERE rt interface source code RCV 做入库的条件 AND rt source document code PO 做入库的条件 AND rt transaction type DELIVER 做入库的条件 AND rt destination type code INVENTORY 做入库的条件 AND rt po header id SELECT pha po header id FROM po headers all pha WHERE segment1 300446 OR rt po line id IN SELECT pla po line id FROM po lines all pla WHERE po header id SELECT po header id FROM po headers all WHERE segment1 300446 OR rt shipment header id SELECT rsh shipment header id FROM rcv shipment headers rsh WHERE shipment header id IN SELECT shipment header id FROM rcv shipment lines WHERE po header id 4105 OR rt shipment line id IN SELECT shipment line id FROM rcv shipment lines WHERE po header id 4105 退货 说明 退货至接收时 产生一条记录 退货至供应商时 产生两条数据 可见退货的实际顺 序为 库存 接收 供应商 不管是退货至接收还是退货至供应商 在事务处理中 都会产生两条记录 而且 数量符号与接收的数据正好相反 而且产生的记录都是RETURN TO RECEIVING 1 库存退货至接受 SELECT rt destination type code rt interface source code rt FROM rcv transactions rt WHERE rt interface source code IS NULL AND rt transaction type RETURN TO RECEIVING 退货至接受 AND rt source document code PO AND rt destination type code RECEIVING AND po header id 4105 AND po line id 9938 SELECT mmt FROM mtl material transactions mmt WHERE mmt transaction source id 4105 AND mmt transaction type id 36 AND mmt transaction action id 1 AND mmt transaction