销售单-请购单-采购单连接SQL

最新推荐文章于 2024-11-24 14:29:44 发布

转载最新推荐文章于 2024-11-24 14:29:44 发布 · 3.3k 阅读

文章标签：

#header #c #flex #system #电话 #sql

Oracle EBS SQL实例专栏收录该内容

8 篇文章

订阅专栏

本文展示了如何使用SQL查询从多个表中提取关键数据，包括销售订单、采购请求、供应商信息和收货地点等，以实现销售数量与需申请采购数量的对比分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select distinct oh.order_number ,prh.segment1 as "请购单", 
prl.line_num 请购单行号,
v.vendor_name 供应商,
L.description 收货地点,
poh.segment1  购进单号,
pol.line_num  购进行号,
ol.line_number 销售订单行号, it.segment1 EBS料号, it.description
       , OL.ORDERED_QUANTITY-nvl(OL.ATTRIBUTE13,0)  as "需申请采购数量"
         ,OL.ORDERED_QUANTITY  as "销售数量"
        , ol.flow_status_code 订单状态
       ,oh.attribute2 as "用户"
       ,oh.attribute7 as "收货人"
       ,oh.attribute8 as "电话"
       ,oh.attribute6 as "详细地址"
      --       , c1.SEGMENT1||','|| c1.SEGMENT2||','|| c1.SEGMENT2||','|| c1.SEGMENT4  as "C1"
       --, c1.description as "基本分类"
      --, c2.SEGMENT1 as "C2"
       , c2.description as "管理分类"
       , it.ATTRIBUTE2 as "供应商货号" , it.ATTRIBUTE3 as "U9码", xiu_util_pkg.GET_FLEX_DESC('XIU_BRAND_CATERGORIES', it.ATTRIBUTE15 ) as "品牌"
   ,prl.cancel_flag
   ,PRL.CLOSED_CODE   
   ,prh.segment1 
   from oe_order_lines_all ol
      , mtl_system_items_b it
      , oe_order_headers_all oh
/*      , mtl_item_categories  ic1
      , mtl_categories_vl    c1*/
      , mtl_item_categories  ic2
      , mtl_categories_vl    c2
      , po_requisition_lines_all  prl
      ,po_requisition_headers_all prh
      , po_line_locations_all ploc
      , po_lines_all pol
      , po_headers_all poh
      ,ap_suppliers v  
      ,HR_LOCATIONS L   
     
   where ol.ordered_item = it.segment1
   and   ol.header_id = oh.header_id
   and  it.organization_id = 81
   and  it.item_type= 0
/*   and  ic1.inventory_item_id = it.inventory_item_id
   and  ic1.organization_id = it.organization_id
   and  ic1.category_set_id = 1
   and  c1.category_id = ic1.category_id*/
   and  ic2.inventory_item_id = it.inventory_item_id
   and  ic2.organization_id = it.organization_id
   and  ic2.category_set_id = 1100000041
   and  c2.category_id = ic2.category_id
   and prl.requisition_header_id=prh.requisition_header_id
   and prl.attribute2 =ol.line_id
   and prl.vendor_id=v.vendor_id(+)
   and PRL.DELIVER_TO_LOCATION_ID=L.location_id(+)
   and ol.flow_status_code not in('CANCELLED')
   and TO_NUMBER(prh.SEGMENT1)>12  
   and prl.cancel_flag  <>'Y' 
   and prl.line_location_id = ploc.line_location_id (+) 
   and ploc.po_line_id = pol.po_line_id (+) 
   and pol.po_header_id = poh.po_header_id (+)