最近各廠都啟用了MRP方式產生需求,因而對ITEM DEMAND SUPPLY中的資料有了嚴格的要求,否則MRP會不準確.
因而要對DEMAND SUPPLY中的異常要及時發現與處理
[@more@]這是抓出SALES ORDER做SHIPPING CONFIRM後還有ITEM DEMAND SUPPLY及保留記錄的.
定義一個ＡＬＥＲＴ
Application:Alert
Name:Demand and Supply are still after shipped
Description:AR
Enabled:V
Periodic頁簽:
Frequency:Every N Calendar Days
Days:1
Start Time:15:00:00
End Time:
Keep 0 Days End Date:
Select Statement:
SELECT ol.ship_from_org_id||' '||lpad(oh.order_number,12,' ')||' '
||lpad(ol.line_number,3,' ')||'.'||rpad(ol.shipment_number,3,' ')
||rpad(mi.segment1,20,' ') item,--mso.SALES_ORDER_ID,
lpad(md.LINE_ITEM_QUANTITY,9,' ') mdqty,lpad(abs(mmt.TRANSACTION_QUANTITY),9,' ') mmtqty,lpad(ol.SHIPPED_QUANTITY,9,' ') shipqty, lpad(ol.INVENTORY_ITEM_ID,9,' ') itemid,
mmt.TRANSACTION_DATE mmtdate,lpad(mmt.TRANSACTION_ID,10,' ') mmtid,
--md.PARENT_DEMAND_ID pdemandid,
lpad(md.DEMAND_ID,9,' ') demand_id,lpad(mr.RESERVATION_ID,9,' ') reservidinto &pidata,&mdqty,&mmtqty,&shipqty,&itemid,&mmtdate,&mmtid,&demandid,&reservid
FROM mtl_demand md,mtl_reservations mr,
mtl_sales_orders mso,
oe_order_headers_all oh,oe_order_lines_all ol,
MTL_MATERIAL_TRANSACTIONS mmt,
mtl_system_items_b mi
WHERE 1=1
and mi.inventory_item_id=ol.inventory_item_id
and mi.organization_id=ol.ship_from_org_id
AND md.DEMAND_ID=mr.N_COLUMN1(+)
--and mr.DEMAND_SOURCE_TYPE_ID=8
and md.COMPLETED_QUANTITY<>md.line_item_quantity
AND md.ORGANIZATION_ID=ol.ship_from_org_id
AND md.INVENTORY_ITEM_ID=ol.inventory_item_id
AND md.DEMAND_SOURCE_HEADER_ID=mso.SALES_ORDER_ID and md.demand_source_line=ol.line_id
AND mso.SEGMENT1=oh.order_number
and ol.header_id=oh.header_idand oh.org_id in (543,801)
and ol.shipped_quantity>0
and mmt.transaction_source_id=md.demand_source_header_id
and mmt.transaction_reference=ol.header_id
and mmt.source_code='ORDER ENTRY'
and mmt.source_line_id=ol.line_id
order by ol.ship_from_org_id,oh.order_number,ol.line_number,ol.shipment_number
通過VERIFY與RUN後,點ACTIONS
Action Name:Details
Description:
Action Level:Summary
點Action Details
Action Type:Message
List: Reply To:
TO:longwsheng@gmail.com
Subject:Demand and Supply are still after shipped
CC:
BCC:
Print For User: Printer:
Response Set: Response Days:
Text(D):
訂單與機型 需求數量 庫存異動 銷貨量 機型ID 扣帳日期 交易ID 需求ID 保留ID
--------------------------------------------------------------------------------------------------------------------------------
=**= Enter summary template below this line =**=
&pidata &mdqty &mmtqty &shipqty &itemid &mmtdate &mmtid &demandid &reservid
=**= Enter summary template above this line =**=
=**= Demand and Supply are still after shipped=**=
Column Overflow:Truncate Max Width:180
Action Sets
Seq:1 Action Set Name: Details Description:
Suppress Duplicates: Enabled:V End date:
Action Sets Details
Members:
seq:1 Action:Details
Type:Action:Message
Summary Threshold:
Action:Abort
Seq:
Enabled:V
End Date
=====save=====