sql2——函数

create or replace function FIND_PROC_USE_NUM(X_TASK_NO IN VARCHAR2,
X_ITEM_CODE IN VARCHAR2,
X_QUANTITY_ISSUED IN NUMBER,
X_ORG_ID IN NUMBER)
/*========================================================+
版权信息：版权所有(c) 2012，中兴通讯股份有限公司
创建者： 夏琼华
创建日期：2013-09-05
内容摘要：查询领用数量
+=========================================================*/
return number is
V_SUM NUMBER; --已制单数量之合
V_ISSUE_SUM NUMBER; --已发料数量之合
V_MAKE_SUM NUMBER; --已制单未发料数量之合
V_ISSUE_QTY NUMBER; --已发料数量
V_MAKE_QTY NUMBER; --已制单未发料数量
V_COUNT NUMBER;
V_SQL VARCHAR2(2000);
begin
V_SUM := 0;
V_ISSUE_SUM := 0;
V_MAKE_SUM := 0;
--循环任务号下的单据明细
FOR J IN (SELECT DISTINCT PPM.RECORD_ID,
PPM.BILL_NUMBER,
PPM.PICK_TYPE,
PPD.ITEM_CODE
FROM WMES.PROC_PICKLIST_MAIN PPM
JOIN WMES.PROC_PICKLIST_DETAIL PPD ON PPD.HEADER_ID =
PPM.RECORD_ID
AND PPD.ENABLED_FLAG = 'Y'
WHERE PPM.TASK_NO = X_TASK_NO
AND PPD.ITEM_CODE = X_ITEM_CODE
AND PPM.ORGANIZATION_ID = X_ORG_ID
AND PPM.ENABLED_FLAG = 'Y') LOOP

V_ISSUE_QTY := 0;
V_MAKE_QTY := 0;

IF (J.PICK_TYPE = '配送库') THEN
V_SQL := 'SELECT COUNT(*)
FROM(SELECT IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU
FROM KXSTEPIII.EDI_SO_S@step IESS
JOIN KXSTEPIII.EDI_BILLSTATUSINFO_V@STEP WBSI
ON WBSI.EXTERNALORDERKEY2 = IESS.EXTERNALORDERKEY2
AND WBSI.ORDERKEY = IESS.ORDERKEY
WHERE IESS.EXTERNALORDERKEY2 = ''' || J.BILL_NUMBER || '''
AND IESS.SKU IN (SELECT NVL(PPD.REPLACE_ITEM_CODE,PPD.ITEM_CODE)
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID=' || J.RECORD_ID || '
AND PPD.ITEM_CODE=''' || J.ITEM_CODE || ''')
AND WBSI.STATUS = ''出货全部完成''
GROUP BY IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU)';
EXECUTE IMMEDIATE V_SQL
INTO V_COUNT;

IF (V_COUNT > 0) THEN
V_SQL := 'SELECT NVL(SUM(IESS.SHIPPEDQTY),0)
FROM KXSTEPIII.EDI_SO_S@step IESS
JOIN KXSTEPIII.EDI_BILLSTATUSINFO_V@STEP WBSI
ON WBSI.EXTERNALORDERKEY2 = IESS.EXTERNALORDERKEY2
AND WBSI.ORDERKEY = IESS.ORDERKEY
WHERE IESS.EXTERNALORDERKEY2 = ''' || J.BILL_NUMBER || '''
AND IESS.SKU IN (SELECT NVL(PPD.REPLACE_ITEM_CODE,PPD.ITEM_CODE)
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID=' || J.RECORD_ID || '
AND PPD.ITEM_CODE= ''' || J.ITEM_CODE || ''')
AND WBSI.STATUS = ''出货全部完成''
GROUP BY IESS.EXTERNORDERKEY,
IESS.EXTERNALORDERKEY2,
IESS.SKU';

EXECUTE IMMEDIATE V_SQL
INTO V_ISSUE_QTY;
ELSE
SELECT NVL(SUM(PPD.USE_NUM), 0)
INTO V_MAKE_QTY
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID = J.RECORD_ID
AND PPD.ITEM_CODE = J.ITEM_CODE;

END IF;

/*ELSIF (J.PICK_TYPE = '车间库') THEN
SELECT NVL(SUM(PPD.USE_NUM), 0)
INTO V_QTY
FROM WMES.PROC_PICKLIST_DETAIL PPD
WHERE PPD.HEADER_ID = J.RECORD_ID
AND PPD.ITEM_CODE = J.ITEM_CODE;*/
END IF;

V_ISSUE_SUM := V_ISSUE_SUM + V_ISSUE_QTY;
V_MAKE_SUM := V_MAKE_SUM + V_MAKE_QTY;

END LOOP;

IF (V_ISSUE_SUM < X_QUANTITY_ISSUED) THEN
V_ISSUE_SUM := X_QUANTITY_ISSUED;
END IF;

V_SUM := V_ISSUE_SUM + V_MAKE_SUM;
return(V_SUM);

EXCEPTION
WHEN OTHERS THEN
RETURN 0;
end FIND_PROC_USE_NUM;