MRP:监控计划转PR明细表--后续需求的更改

累计计划单需求

累计PR下单数量

累计结余

PR最早满足需求日期

延迟天数

1.累计计划单需求量(QTY3)与累计PR下单数量(QTY4)
两个栏位时间点为括运行报表时间之前而大于MRP运行完成时间数量之和;
2.PR最早满足需求日期：QTY4-QTY3>=0时最早下达PR的日期
3.延迟天数：PR最早满足需求日期-建议订购日期+1(除去非工作日),PR最早满足需求日期没有时，默认当天
4.开发中用到的累计函数先记录如下：

FUNCTION ITEM_MRP_PR_DATE(P_ORGANIZATION_ID IN NUMBER,P_INVENTORY_ITEM_ID IN NUMBER,P_MRP VARCHAR2,P_DATE DATE)
 RETURN DATE IS
 L_MRP_SUMQTY NUMBER;
 L_DATE DATE;
BEGIN
 L_MRP_SUMQTY:=ITEM_MRP_SUGGEST_SUMQTY(P_ORGANIZATION_ID,P_INVENTORY_ITEM_ID,P_MRP,P_DATE);
 
 SELECT TRUNC(PR.CREATION_DATE)
   INTO  L_DATE
   FROM (
   SELECT PRL.CREATION_DATE,
          SUM(NVL(PRL.QUANTITY,0)-NVL(PRL.QUANTITY_CANCELLED,0))
           OVER (PARTITION BY PRL.ITEM_ID  ORDER BY PRL.CREATION_DATE) QTY 
  FROM PO_REQUISITION_HEADERS_ALL PRH,
       PO_REQUISITION_LINES_ALL   PRL,
       MTL_SYSTEM_ITEMS_B         MSI
 WHERE PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
   AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID
   AND MSI.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID  
   AND PRH.INTERFACE_SOURCE_CODE = 'MRP'
   AND NVL(PRL.CANCEL_FLAG, 'N') = 'N'
   AND (PRL.CLOSED_CODE != 'FINALLY CLOSED' OR PRL.CLOSED_CODE IS NULL)
   AND MSI.ORGANIZATION_ID=P_ORGANIZATION_ID
   AND MSI.INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
   AND TRUNC(PRL.CREATION_DATE) <= TRUNC(P_DATE)
   AND EXISTS (SELECT 1
               FROM MRP_PLANS MP
               WHERE MP.PLAN_COMPLETION_DATE                 AND MP.COMPILE_DESIGNATOR=P_MRP
            )
       ) PR
     WHERE PR.QTY>=L_MRP_SUMQTY
      AND ROWNUM=1;
 
RETURN L_DATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41594/viewspace-620772/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/41594/viewspace-620772/