--采购到入库所经历的表--采购到入库所经历的表--采购到入库所经历的表--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_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 = &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 暂记应付表--记录采购订单接收后，产生的暂记应付信息(接收事务处理产生的分配行)--产生分录的程序: RCV_SeedEvents_PVT=>RCV_CreateAccounting_PVTSELECT nvl(poll.accrue_on_receipt_flag, 'N') INTO l_accrue_on_receipt_flag FROM po_line_locations poll WHERE poll.line_location_id = p_rcv_events_tbl(l_ctr_first) .po_line_location_id; DECLAREBEGIN IF ((l_accrue_on_receipt_flag = 'Y' OR p_rcv_events_tbl(i) .procurement_org_flag = 'N') AND p_rcv_events_tbl(i) .event_type_id NOT IN (rcv_seedevents_pvt.intercompany_invoice, rcv_seedevents_pvt.intercompany_reversal)) THEN l_stmt_num := 50 IF g_debug = 'Y' AND fnd_log.level_event >= fnd_log.g_current_runtime_level THEN fnd_log.string(fnd_log.level_event, g_log_head '.' l_api_name '.' l_stmt_num, 'Creating accounting entries in RRS'); END IF;END; IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THENFND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD'.'l_api_name'.'l_stmt_num,'Creating accounting entries for accounting_event_id : 'l_accounting_event_id)END IF -- Call Account generation API to create accounting entriesRCV_CreateAccounting_PVT.Create_AccountingEntry(p_api_version => 1.0,x_return_status => l_return_status,x_msg_count => l_msg_count,x_msg_data => l_msg_data,p_accounting_event_id => l_accounting_event_id,p_lcm_flag => p_lcm_flag)IF l_return_status <> FND_API.g_ret_sts_success THENl_api_message := 'Error in Create_AccountingEntry API'IF G_DEBUG = 'Y' AND FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THENFND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD '.'l_api_namel_stmt_num,'Insert_RAEEvents : 'l_stmt_num' : 'l_api_message)END IFRAISE FND_API.g_exc_unexpected_errorEND IFSELECT * 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_source_type_id = 1;--2.库存退货至供应商（产生两条数据。顺序为: 库存----> 接收----> 供应商）--a.库存退货至接收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 = 'INVENTORY' AND po_header_id = 4105;--b.接收退货至供应商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 VENDOR' --退货至供应商 AND rt.source_document_code = 'PO' AND rt.destination_type_code = 'RECEIVING' AND po_header_id = 4105; 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_source_type_id = 1; --采购订单
