oracle-EBS-查看销售订单已经确认出货，和过账到AR应收的订单行明细，查看物流操作时间

有时候物流同事说已经做了确认出货， 但ar的同事跑了自动开票导入，还是没在ar中找到该订单，就可以用这个sql 去查询原因， 可能是 发运事务处理的 订单处于【运行接口】 状态。

 

详细对图文说明请看这份博客说明：  http://www.zyxpp.com/oracle-ebs-om-confirm-not-transfer-to-ar/

 

SELECT    wdd.last_update_date,

  to_char(oola.LAST_UPDATE_DATE,'yyyy-mm-dd hh24:mi:ss') 订单行修改日期,
  (select TRANSACTION_DATE from  mtl_material_transactions mmt where  
  mmt.transaction_source_id=mso.sales_order_id
  and mmt.trx_source_line_id=oola.line_id  and mmt.TRANSACTION_ID =wdd.TRANSACTION_ID  ) 挑库单库存日期,
  to_char(wnd.INITIAL_PICKUP_DATE,'yyyy-mm-dd hh24:mi:ss') 库存总帐日期 ,
    to_char(wnd.ACCEPTED_DATE,'yyyy-mm-dd hh24:mi:ss') 打印时间 ,
  to_char(wnd.CONFIRM_DATE,'yyyy-mm-dd hh24:mi:ss') 物流点击鼠标操作时间,
  -- to_char(rila.creation_date,'YYYY-MM-DD HH24:MI:SS') 接口表创建时间,
    (select to_char(CREATION_DATE,'yyyy-mm-dd hh24:mi:ss')  from RA_CUSTOMER_TRX_LINES_ALL rctl where to_number(interface_line_attribute6) = oola.line_id and ORG_ID =oola.org_id  ) AR正式创建时间,
  --  trunc((rila.creation_date - wnd.CONFIRM_DATE)*24 *60,2)  点确认到写接口的间隔,
  ooha.FLOW_STATUS_CODE,wdd.OE_INTERFACED_FLAG,wdd.INV_INTERFACED_FLAG,
  wdd.released_status,hou.organization_id ,
  ooha.order_category_code order_type,
  ooha.order_number,oola.line_id  ,
  oola.LINE_NUMBER||'.'||Shipment_number Line_Number,
  wdd.delivery_detail_id,
  oola.ACTUAL_SHIPMENT_DATE,
  to_char(wnd.INITIAL_PICKUP_DATE,'yyyy-mm-dd')  pickup_date ,
  wnd.delivery_id,
  wnd.name delivery_number,
  mp.organization_code, mp.operating_unit,
  oola.SUBINVENTORY,
  hou.short_code ou_code,
  hou.name ou_name,
  hca.account_number,
  hp.party_name customer_name ,
  msi.segment1 item_number,
  msi.description item_description ,
    oola.UNIT_SELLING_PRICE,
  oola.UNIT_LIST_PRICE,
  msi.attribute1 unit_weight,
  msi.weight_uom_code,
  wdd.requested_quantity_uom uom,
  wdd.shipped_quantity,
  wdd.REQUESTED_QUANTITY,
  msi.attribute1 * wdd.shipped_quantity/1000  freight_MT,
  oola.UNIT_SELLING_PRICE * wdd.shipped_quantity amt,
  wdd.SHIP_FROM_LOCATION_ID,wdd.SHIP_TO_LOCATION_ID , oola.invoice_interface_status_code,
  decode(rila.interface_line_attribute6,  null ,  '未过账', rila.INTERFACE_LINE_CONTEXT || '已过账') arpost,
-- decode(oola.INVOICE_INTERFACE_STATUS_CODE,'YES','已过账','未过账') INVOICE_INTERFACE_STATUS,
--NVL((select '已过账' from RA_CUSTOMER_TRX_ALL rctl where rctl.org_id = oola.org_id and interface_header_attribute6 = oola.line_id  ),'未过账') AR_INVOICE_INTERFACE_STATUS
  (SELECT hold_comment
  FROM oe_hold_sources_all hs,
    oe_order_holds_all oh,
    oe_order_headers_all ooh
  WHERE hs.released_flag  = 'N'
  AND hs.hold_id          = 1
  AND hs.hold_entity_code = 'O'
  AND oh.hold_source_id   = hs.hold_source_id
  AND hs.hold_entity_id   = ooh.header_id
  AND oh.line_id         IS NULL
  AND oola.HEADER_ID         = ooh.header_id
  AND ooh.cancelled_flag  = 'N'
  ) Credit_Hold
FROM wsh_new_deliveries wnd,
  wsh_delivery_assignments wda,
  wsh_delivery_details wdd,
  oe_order_lines_all oola,
  oe_order_headers_all ooha ,
  hz_cust_accounts hca,
  hz_parties hp,
  hr_operating_units hou,
  org_organization_definitions mp,
  mtl_system_items msi ,--, --,org_organization_definitions ood,Mtl_Item_Locations mil
  RA_CUSTOMER_TRX_LINES_ALL rila,
  oe_transaction_types_tl ot,
  mtl_sales_orders mso
WHERE   1=1     
and ooha.order_type_id             = ot.transaction_type_id
AND ot.language                   = userenv('LANG')
and mso.segment1=ooha.order_number
and mso.SEGMENT2 = ot.name
AND wda.delivery_detail_id=wdd.delivery_detail_id
AND wdd.source_header_id  =oola.header_id
AND wdd.source_line_id    =oola.line_id
AND wdd.source_header_id  =ooha.header_id
AND wdd.customer_id       =hca.cust_account_id
and wda.delivery_id =wnd.delivery_id(+) --可能出现挑库后，但出货单号为Null 情况，需要手动执行创建单号
--and ooha.header_id = wnd.source_header_id
AND hca.party_id          =hp.party_id
AND wdd.organization_id   =msi.organization_id
AND wdd.inventory_item_id =msi.inventory_item_id
AND wdd.organization_id   =mp.organization_id
AND hou.organization_id   =ooha.org_id
and ooha.header_id =oola.header_id
and oola.line_id = rila.interface_line_attribute6(+)
and oola.org_id = rila.org_id(+)
and hou.short_code = 2222　　　　　　 --公司代码
and ooha.order_number in (1066583)   --订单号
and wdd.released_status  in ('C')
and rila.interface_line_attribute6   is  null   --判断是否有数据，没数据就是没过到ar  
and wnd.INITIAL_PICKUP_DATE  between to_date('20200101','RRRRMMDD') and to_date('20200131', 'RRRRMMDD')

--PICK_STATUS B 已延交 未能在库存管理系统中分配行 WSH.665
--PICK_STATUS C 已发运 已发运行 WSH.665
--PICK_STATUS D 已取消 已取消行 WSH.665
--PICK_STATUS I 已连接 已发送行并将其连接至 Oracle Order Management 和 Oracle Inventory。 WSH.665
--PICK_STATUS L 已关闭 已接收行 WSH.665
--PICK_STATUS N 未准备发放 行没有准备好进行发放 WSH.665
--PICK_STATUS P 已清除 已从源系统中清除行 WSH.665
--PICK_STATUS R 准备发放 行已准备好进行发放 WSH.665
--PICK_STATUS S 已发放至仓库 已将行发放至库存管理系统进行处理 WSH.665
--PICK_STATUS X 不适用 行不适合挑库发放 WSH.665
--PICK_STATUS Y 分批发运/确认挑库 库存管理系统已挑库并分批发运行 WSH.665