ORACEL EBS 采购到入库所经历的表.doc

-采购到入库所经历的表-0.请购单-创建请购单方式有-a.从外挂系统导入请购的接口表PO_REQUISITIONS_INTERFACE_ALL,并允许请求(名称:导入申请)select*frompo_requisitions_interface_allwhereinterface_source_code=TESTKHJ;-b.在系统中创建请购单(路径：PO/申请/申请)-请购单头信息selectprh.requisition_header_id,prh.authorization_status-未审批时为INCOMPLETE,审批完后为frompo_requisition_headers_allprhwhereprh.segment1=600000andprh.type_lookup_code=PURCHASE;-请购单行信息selectprl.requisition_line_id,prl.*frompo_requisition_lines_allprlwhereprl.requisition_header_idin(selectprh.requisition_header_idfrompo_requisition_headers_allprhwhereprh.segment1=600000andprh.type_lookup_code=PURCHASE);-请购单分配行select*frompo_req_distributions_allprdawhereprda.requisition_line_idin(selectprl.requisition_line_idfrompo_requisition_lines_allprlwhereprl.requisition_header_idin(selectprh.requisition_header_idfrompo_requisition_headers_allprhwhereprh.segment1=600000andprh.type_lookup_code=PURCHASE);-1.采购订单的创建（路径：PO/采购订单/采购订单）-po_headers_all采购订单头表selectpha.po_header_id,pha.segment1,pha.agent_id,pha.type_lookup_code,-标准采购单为STANDARD,一揽子协议为BLANKETdecode(pha.approved_flag,R,pha.approved_flag,nvl(pha.authorization_status,INCOMPLETE),-审批,未审批时为INCOMPLETE，审批后为APPROVEDpo_headers_sv3.get_po_status(pha.po_header_id)-刚下完采购单，未审批时，po状态为未完成，审批后，状态为批准frompo_headers_allphawheresegment1=300446;-采购单号码-po_lines_all采购订单行表selectpla.po_line_id,pla.line_type_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=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*frompo_line_locations_allpllawhereplla.po_line_id=(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446);-或者select*frompo_line_locations_allpllawhereplla.po_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446);-4、po_distributions_all采购订单发送行的分配表(路径：PO/采购订单/采购订单/发运(T)/分配(T)-line_location_id=po_line_location_all.line_location_id-发往同一地点的物料也可能放在不同的子库存，此表记录物料分配情况select*frompo_distributions_allpdawherepda.line_location_idin(selectplla.line_location_idfrompo_line_locations_allpllawhereplla.po_line_id=(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446);-或者select*frompo_distributions_allwherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446);-或者select*frompo_distributions_allpdawherepda.po_line_id=(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446);-对于po_distribution_all表而言，如果其SOURCE_DISTRIBUTION_ID有值,其对应于计划采购单发放/*以上各表从上到下是一对多关系的*/-po_releases_all订单发放-该表包含一揽子协议以及计划采购单的release，对于每一张发放的一揽子协议或者计划采购单都有相关行与之对应-其包含采购员，日期，释放状态，释放号码，每一个释放行都有至少一条的采购单的发运信息与之对应(PO_LINE_LOCATIONS_ALL).-每做一次Realese，PO_distributions_all就会新增一条记录。这是计划订单的特性。-select*frompo_releases_allwherepo_header_id=&po_header_id;-接收（路径：INV/事务处理/接收/接收）-1.rcv_shipment_headers接收发送头表-记录采购订单的接收情况的头表select*fromrcv_shipment_headersrshwherersh.shipment_header_idin(selectshipment_header_idfromrcv_shipment_lineswherepo_header_id=4105);-2.rcv_shipment_lines接收发送行表-记录采购订单的发送的行的接收情况select*fromrcv_shipment_lineswherepo_header_id=4105;-3.rcv_transactions接收事务处理表-记录采购订单的发送行的RECEIVE的信息selectrt.transaction_id,rt.transaction_type,rt.destination_type_code,rt.*fromrcv_erface_source_code=RCVandrt.source_document_code=POand(rt.po_header_id=(selectpha.po_header_idfrompo_headers_allphawheresegment1=300446)orrt.po_line_idin(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446)orrt.shipment_header_id=(selectrsh.shipment_header_idfromrcv_shipment_headersrshwhereshipment_header_idin(selectshipment_header_idfromrcv_shipment_lineswherepo_header_id=4105)orrt.shipment_line_idin(selectshipment_line_idfromrcv_shipment_lineswherepo_header_id=4105);-4.rcv_receiving_sub_ledger暂记应付表-记录采购订单接收后，产生的暂记应付信息(接收事务处理产生的分配行)-产生分录的程序:RCV_SeedEvents_PVT=RCV_CreateAccounting_PVT/*po_line_locations.accrue_on_receipt_flag控制是否产生分录*/selectnvl(poll.accrue_on_receipt_flag,N)intol_accrue_on_receipt_flagfrompo_line_locationspollwherepoll.line_location_id=p_rcv_events_tbl(l_ctr_first).po_line_location_id;IF(l_accrue_on_receipt_flag=YORp_rcv_events_tbl(i).procurement_org_flag=N)ANDp_rcv_events_tbl(i).event_type_idNOTIN(RCV_SeedEvents_PVT.INTERCOMPANY_INVOICE,RCV_SeedEvents_PVT.INTERCOMPANY_REVERSAL)THENl_stmt_num:=50IFG_DEBUG=YANDFND_LOG.LEVEL_EVENT=FND_LOG.G_CURRENT_RUNTIME_LEVELTHENFND_LOG.string(FND_LOG.LEVEL_EVENT,G_LOG_HEAD.l_api_name.l_stmt_num,CreatingaccountingentriesinRRS)ENDIFIFG_DEBUG=YANDFND_LOG.LEVEL_STATEMENT=FND_LOG.G_CURRENT_RUNTIME_LEVELTHENFND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD.l_api_name.l_stmt_num,Creatingaccountingentriesforaccounting_event_id:l_accounting_event_id)ENDIF-CallAccountgenerationAPItocreateaccountingentriesRCV_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,/*SupportforLandedCostManagement*/p_lcm_flag=p_lcm_flag)IFl_return_statusFND_API.g_ret_sts_successTHENl_api_message:=ErrorinCreate_AccountingEntryAPIIFG_DEBUG=YANDFND_LOG.LEVEL_UNEXPECTED=FND_LOG.G_CURRENT_RUNTIME_LEVELTHENFND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD.l_api_namel_stmt_num,Insert_RAEEvents:l_stmt_num:l_api_message)ENDIFRAISEFND_API.g_exc_unexpected_errorENDIFselect*fromrcv_receiving_sub_ledgerwherercv_transaction_idin(selecttransaction_idfromrcv_transactionswherepo_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=(selectpha.po_header_idfrompo_headers_allphawheresegment1=300446)orrt.po_line_idin(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446)orrt.shipment_header_id=(selectrsh.shipment_header_idfromrcv_shipment_headersrshwhereshipment_header_idin(selectshipment_header_idfromrcv_shipment_lineswherepo_header_id=4105)orrt.shipment_line_idin(selectshipment_line_idfromrcv_shipment_lineswherepo_header_id=4105);-入库-因为涉及入库操作，所以，在库存事务处理表中会留下相应的记录。-即在Mtl_material_transactions表中，会存在相应的两条入库记录。selectmmt.*frommtl_material_transactionsmmtwheremmt.transaction_type_id=18-po接收andmmt.transaction_action_id=27-接收至库存andmmt.transaction_source_type_id=1-采购订单and(mmt.transaction_source_id=4105-po_header_idormmt.rcv_transaction_idin(selectrt.transaction_idfromrcv_erface_source_code=RCVandrt.source_document_code=POand(rt.po_header_id=(selectpha.po_header_idfrompo_headers_allphawheresegment1=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=(selectpha.po_header_idfrompo_headers_allphawheresegment1=300446)orrt.po_line_idin(selectpla.po_line_idfrompo_lines_allplawherepo_header_id=(selectpo_header_idfrompo_headers_allwheresegment1=300446)orrt.shipment_header_id=(selectrsh.shipment_header_idfromrcv_shipment_headersrshwhereshipment_header_idin(selectshipment_header_idfromrcv_shipment_lineswherepo_header_id=4105)orrt.shipment_line_idin(selectshipment_line_idfromrcv_shipment_lineswherepo_header_id=4105);-退货-说明:-退货至接收时，产生一条记录，退货至供应商时，产生两条数据。可见退货的实际顺序为:库存-接收-供应商-不管是退货至接收还是退货至供应商，在事务处理中，都会产生两条记录。-而且，数量符号与接收的数据正好相反。而且产生的记录都是RETURNTORECEIVING。-1.库存退货至接受selectrt.destination_type_code,erface_source_code,rt.*fromrcv_erface_source_codeisnullandrt.transaction_type=RETURNTORECEIVING-退货至接受andrt.source_document_code=POandrt.destination_type_code=RECEIVINGandpo_header_id=4105andpo_line_id=9938;sel