SELECT h.customer_po_number --客户PO ,h.order_number --订单编号 ,h.order_type_name --订单类型 ,h.suit_count --套数 ,h.order_status_name --状态 ,to_char(h.booked_date,'yyyy-mm-dd') booked_date--订单受理日期 ,to_char(h.delivery_date,'yyyy-mm-dd') delivery_date --订单发货日期 ,to_char(h.creation_date,'yyyy-mm-dd') creation_date --订单创建日期 ,to_char(h.complete_date,'yyyy-mm-dd') complete_date --订单完工日期 ,h.zq --战区 ,h.sq --省区 ,h.pq --片区 ,h.city --城市 /* ,h.segment1 ,h.segment2 ,h.segment3*/ ,h.account_number --客户编码 ,h.account_short_name --客户简称 ,h.promotion_name --活动 ,h.style_series --风格系列 ,ooh.order_progress as 进展 --,hfv.confirmed_name as 核价员 ,oe.design_method as 设计方式 ,oe.end_cust_name -- 终端客户地址 ,oe.end_cust_phone -- 终端客户电话 ,oe.end_cust_address -- 终端客户地址 ,SUM(decode(l.item_class,'MM',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS mm --木门 ,SUM(decode(l.item_class,'LKM',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS LKM--铝框门款 ,SUM(decode(l.item_class,'QB',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS QB --墙板款 ,SUM(decode(l.item_class,'MMYB',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS MMYB --岩板款 ,SUM(decode(l.item_class,'PTCP',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS PTCP-- 配套款 ,SUM(decode(l.item_class,'WJPJ',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS WJPJ-- 五金配件款 ,SUM(decode(l.item_class,'CPJJ',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS CPJJ-- 成品家居款6
, SUM(decode(l.item_class,'YYWPM',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS YYWPM-- 运动款 ,SUM(decode(l.item_class,'MMSP',nvl(l.yg_order_amount ,0) - nvl(l.charge_amount ,0),0)) AS MMSP -- 饰品款 ,SUM(CASE WHEN l.item_class NOT IN ('MM','LKM','QB', 'MMYB','PTCP','WJPJ','CPJJ', 'YYWPM','MMSP' ) THEN nvl(l.yg_order_amount, 0) - nvl(l.charge_amount, 0)
ELSE 0 END ) AS qy
FROM apps.cux_om_order_headers_v h ,apps.cux_om_order_lines_v l ,mdm_cust_finerpt_user_access fu ,oms_oe_order_types ooot ,oms_oe_order_headers ooh --添加进展 --,APPS.OMS_OE_ORDER_HEADER_FINE_V hfv--添加核价员 ,oms_oe_order_header_atts oe --添加终端 WHERE h.header_id = l.header_id and l.header_id=ooh.header_id
and ooh.header_id=oe.header_id --and oe.header_id =hfv.header_id AND h.order_type_id = ooot.order_type_id and ooot.order_type_id=ooh.order_type_id (+)
--AND ooot.LEFT_FLAG = 'N' --是否遗留单:Y:是，N:否 AND ooot.CHANGE_FLAG = 'N'--是否更改单:Y:是，N:否 AND h.unit_code =161 AND FU.ORG_ID=H.unit_code ${if(len(BookDates)==0,""," and h.booked_date >='"+BookDates+"'")} ${if(len(BookDatee)==0,""," and h.booked_date ${if(len(BookDatee_c)==0,""," and h.creation_date ${if(len(bookdatee)=0,"","and h.booked_date ${if(len(bookdatee_c)=0,"","and h.creation_date --${if(len(BookDatee_c)==0,""," and h.creation_date ${if(len(bookdatee)=0,"","and l.cancelled_date ${if(len(bookdatee_c)=0,"","and h.creation_date ${if(len(bookdatee)=0,"","and l.cancelled_date ${if(len(bookdatee_c)=0,"","and h.creation_date<to_date('"+bookdatee_C+"','yyyy-mm-dd')+1")} ${if(len(sq)==0,"", "and h.sq in ('" + replace(sq,",","','") + "')")} ${if(len(po) == 0,"","and h.customer_po_number like '%"+po+"%'")} --添加订单类型过滤 ${if(len(订单类型)==0,"","and h.order_type_name like '%"+订单类型+"%'")} --添加客户简称过滤 ${if(len(客户简称)==0,"","and h.account_short_name like '%"+客户简称+"%'")} --添加订单编号过滤 ${if(len(订单编号)==0,"","and h.order_number like '%"+订单编号+"%'")} AND h.order_type_name not like '%遗留%' AND h.order_type_name not like '%厂部%' AND h.order_type_name not like '%运营物品%' AND h.header_id = l.header_id
--AND (fu.is_qd='N' OR fu.is_qd='A' OR h.site_code='1' ) GROUP BY h.customer_po_number --客户PO ,h.order_number --订单编号 ,h.order_type_name --订单类型 ,h.suit_count --套数 ,h.order_status_name --状态 ,to_char(h.creation_date,'yyyy-mm-dd') --订单创建日期 ,to_char(h.booked_date,'yyyy-mm-dd') --订单受理日期 ,to_char(h.complete_date,'yyyy-mm-dd') --订单完工日期 ,to_char(h.delivery_date,'yyyy-mm-dd') --订单发货日期 ,h.zq --战区 ,h.sq --省区 ,h.pq --片区 ,h.city --城市 ,h.account_number --客户编码 ,h.account_short_name --客户简称 ,h.promotion_name --活动 ,h.style_series --风格系列 ,ooh.order_progress --进展 --,hfv.confirmed_name -- 核价员 ,oe.design_method --设计方式 ,oe.end_cust_name -- 终端客户地址 ,oe.end_cust_phone -- 终端客户电话 ,oe.end_cust_address -- 终端客户地址
|
1min目标场景问卷
立即参与
