設一ALERT,抓取SHIPPED後在ITEM DEMAND SUPPLY中還有記錄的

最近各廠都啟用了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=====