采购到入库所经历的表.docx

采购到入库所经历的表-采购到入库所经历的表-0.请购单-创建请购单方式有-a.从外挂系统导入请购的接口表PO_REQUISITIONS_INTERFACE_ALL,并允许请求(名称:导入申请)SELECT *FROM PO_REQUISITIONS_INTERFACE_ALLWHERE INTERFACE_SOURCE_CODE = TEST KHJ-b.在系统中创建请购单(路径：PO/申请/申请)-请购单头信息SELECT prh.requisition_header_id ,prh.authorization_status-未审批时为INCOMPLETE,审批完后为from PO_REQUISITION_HEADERS_ALL PRHwhere prh.segment1= 600000 andprh.type_lookup_code=PURCHASE-请购单行信息SELECT prL.Requisition_Line_Id ,prL.*from PO_REQUISITION_LINES_ALL PRLwhere PRL.REQUISITION_HEADER_ID IN(SELECT prh.requisition_header_id from PO_REQUISITION_HEADERS_ALL PRHwhere prh.segment1= 600000 andprh.type_lookup_code=PURCHASE)-请购单分配行select *from Po_Req_Distributions_All prdawhere 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 phawhere segment1 = 300446-采购单号码-po_lines_all 采购订单行表select pla.po_line_id, pla.line_type_idfrom po_lines_all plawhere 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 pllawhere 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 pllawhere 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 pdaWHEREpda.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_allwhere po_header_id = (select po_header_id from po_headers_all where segment1 = 300446) -或者select *from po_distributions_all pdawherepda.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 rshwhere 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的信息selectRT.TRANSACTION_ID, rt.transaction_type, rt.destination_type_code, rt.*fromrcv_erface_source_code = RCV andrt.source_document_code= PO and ( rt.po_header_id = (select pha.po_header_idfrom po_headers_all pha where segment1 = 300446 ) orRT.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) orRT.SHIPMENT_HEADER_ID = (select RSH.SHIPMENT_HEADER_IDfrom rcv_shipment_headers rshwhere shipment_header_id in (select shipment_header_id from rcv_shipment_lines where po_header_id = 4105) orrt.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_ledgerwhere rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id = 4105);-接受（路径：INV/事务处理/接收/接收事务处理）-接收事务处理：接收之后，其实现在还并没有入库。-rcv_transactions 接收事务处理表-记录采购订单的发送行的ACCEPT的信息selectRT.TRANSACTION_ID,rt.transaction_type,rt.destination_type_code,rt.*fromrcv_erface_source_code = RCV -做接收的条件 andrt.source_document_code= PO -做接收的条件 andrt.TRANSACTION_TYPE = RECEIVE-做接收的条件 andrt.DESTINATION_TYPE_CODE = RECEIVE-做接收的条件 and ( rt.po_header_id = (select pha.po_header_idfrom po_headers_all pha where segment1 = 300446 ) orRT.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) orRT.SHIPMENT_HEADER_ID = (select RSH.SHIPMENT_HEADER_IDfrom rcv_shipment_headers rshwhere shipment_header_id in (select shipment_header_id from rcv_shipment_lines where po_header_id = 4105) orrt.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 mmtwhere 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 erface_source_code = RCV and rt.source_document_code = PO and (rt.po_header_id = (select pha.po_header_id from po_headers_all phawhere segment1 = 300446)-此时,rcv_transactions的状态变为selectRT.TRANSACTION_ID,rt.transaction_type,rt.destination_type_code,rt.*fromrcv_erface_source_code = RCV -做入库的条件 andrt.source_document_code= PO -做入库的条件 andrt.TRANSACTION_TYPE = DELIVER-做入库的条件 andrt.DESTINATION_TYPE_CODE = INVENTORY-做入库的条件 and ( rt.po_header_id = (select pha.po_header_idfrom po_headers_all pha where segment1 = 300446 ) orRT.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) orRT.SHIPMENT_HEADER_ID = (select RSH.SHIPMENT_HEADER_IDfrom rcv_shipment_headers rshwhere shipment_header_id in (select shipment_header_id from rcv_shipment_lines where po_header_id = 4105) orrt.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 rtWHERE erface_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 = 9938SELECT MMT.*FROM mtl_material_transactions MMTWHERE MMT.TRANSACTION_SOURCE_ID=4105AND MMT.TRANSACTION_TYPE_ID =36AND MMT.TRANSACTION_ACTION_ID=1AND MMT.TR