CREATE OR REPLACE
PROCEDURE "SP_STOCK_STATISEICE_TONOW_RDC"
/***********************************************************
模塊名稱：RDC當前庫存統計報表
模塊功能：
創 建 人：吳超
創建時間：2011-5-9
引數說明：
測驗用例：
修改歷史：= = = = = = = = = = = = = = = = = = = = = = = = = =
修 改 人：黃廷輝
修改日期：2014年6月10日 14:07:39
修改說明：更改為有索引查詢提高效率
= = = = = = = = = = = = = = = = = = = = = = = = = = =
翻譯歷史：= = = = = = = = = = = = = = = = = = = = = = = = =
翻 譯 人：
翻譯日期：
翻譯說明：支持ORACLE版
= = = = = = = = = = = = = = = = = = = = = = = = = = =
************************************************************/
(V_PROJECT_NAME IN VARCHAR2, --所屬專案
V_EXT_CUST_NAME IN VARCHAR2, --專案歸屬
V_SIGN_NO IN VARCHAR2, --簽收單號
V_FOREIGHT_NO IN VARCHAR2, --貨運單號
V_CONTRACT IN VARCHAR2, --合同號
V_WAREHOUSE_NAME IN VARCHAR2, --RDC
V_OFFICE_ID IN VARCHAR2, --組織
OUTCURSOR OUT PKG_CURSOR.T_CURSOR --回傳資料
) AS
V_SQL VARCHAR2(30000);
V_INSERT_SQL VARCHAR2(30000);
V_SERIAL_NO LONG;
BEGIN
/* EXECUTE IMMEDIATE 'TRUNCATE TABLE TMP_RPT_GOODS_RDC_NEW';
COMMIT;*/
V_SERIAL_NO := SEQ_SEARCH_MIT_BATCH_NO.NEXTVAL;
V_INSERT_SQL := 'INSERT INTO TMP_RPT_GOODS_RDC_NEW(TMP_RPT_GOODS_RDC_ID,';
V_INSERT_SQL := V_INSERT_SQL ||
'OMS_ORDER_ID,PRICE,VOLUME,WEIGHT,STATUS_VALUE,';
V_INSERT_SQL := V_INSERT_SQL ||
'SUB_ORDER_TYPE,ACTION_TYPE,BATCH_NO)SELECT SYS_GUID(),';
V_INSERT_SQL := V_INSERT_SQL ||
'OO.OMS_ORDER_ID,OSODS.ACTUAL_SKU_QTY,OSODS.ACTUAL_SKU_VOLUME,';
V_INSERT_SQL := V_INSERT_SQL ||
' OSODS.ACTUAL_SKU_WEIGHT,OSODS.STATUS_VALUE_ID,OSODS.SUB_ORDER_TYPE_ID, ';
V_INSERT_SQL := V_INSERT_SQL ||
'CASE WHEN OSODS.SUB_ORDER_TYPE_ID = ''d1df8a30-09b2-4be1-a265-27165a38b687'' THEN ';
V_INSERT_SQL := V_INSERT_SQL ||
'0 ELSE 1 END,'||'''' || V_SERIAL_NO || ''''|| ' FROM OMS_ORDER OO, OMS_SUB_ORD_DTL_STATUS OSODS ';
V_INSERT_SQL := V_INSERT_SQL ||
' WHERE OO.OMS_ORDER_ID = OSODS.OMS_ORDER_ID ';
V_INSERT_SQL := V_INSERT_SQL ||
' AND OO.ACTIVE = 1 AND OSODS.IS_COMPLETED = 1 ';
V_INSERT_SQL := V_INSERT_SQL ||
' AND OO.BUSINESS_STATUS_ID IN (''ddfc129b-fd6e-46af-b20c-ecea2ccaee18'',';
V_INSERT_SQL := V_INSERT_SQL ||
'''8cac03f4-49be-478d-be9b-ada88b232be9'', ''57129ea5-6906-4a0e-b66d-147cc4f81b80'', ''11f58dca-9011-4cea-8e93-a94472391812'') ';
V_INSERT_SQL := V_INSERT_SQL ||
'AND OSODS.SUB_ORDER_TYPE_ID IN (''d1df8a30-09b2-4be1-a265-27165a38b687'', ''9748f0cc-aec7-498f-bce5-f16f78921dfc'') ';
V_INSERT_SQL := V_INSERT_SQL ||
' AND OSODS.STATUS_VALUE_ID IN (''02375a2c-686e-4173-90d0-121145aa737f'',''1f6aa98a-a56f-4bb0-afac-667c9b76355e'') ';
IF V_PROJECT_NAME IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL || ' AND (OO.PROJECT_ID = ''' ||
V_PROJECT_NAME || ''')';
END IF;
IF V_EXT_CUST_NAME IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL || ' AND OO.EXT_CUSTMER_NAME = ''' ||
V_EXT_CUST_NAME || '''';
END IF;
IF V_SIGN_NO IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL ||
'AND OO.SIGN_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_SIGN_NO || ''', ''''))))';
END IF;
IF V_FOREIGHT_NO IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL ||
'AND OO.FOREIGHT_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_FOREIGHT_NO || ''', ''''))))';
END IF;
IF V_CONTRACT IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL ||
'AND OO.COTRACT_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_CONTRACT || ''', ''''))))';
END IF;
IF V_WAREHOUSE_NAME IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL || 'AND OO.WAREHOUSE_ID = ''' ||
V_WAREHOUSE_NAME || ''' ';
END IF;
IF V_OFFICE_ID IS NOT NULL THEN
V_INSERT_SQL := V_INSERT_SQL ||
'AND OO.OFFICE_ID IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_OFFICE_ID || ''', ''''))))';
END IF;
-- insert into testsql (sql) values (V_INSERT_SQL);
--DBMS_OUTPUT.PUT_LINE(V_INSERT_SQL);
EXECUTE IMMEDIATE V_INSERT_SQL;
-- COMMIT;
V_SQL := 'SELECT * FROM (SELECT OO.OMS_ORDER_ID, OMS.SERVICE_NAME AS ORDERTYPE,';
V_SQL := V_SQL ||
'OO.PROJECT_NAME AS PROCECTNAME, OO.FOREIGHT_NO AS FOREIGHTNO,';
V_SQL := V_SQL ||
'OO.OFFICE_NAME AS OFFICENAME, OO.EXT_CUSTMER_NAME AS EXTCUSTMERNAME,';
V_SQL := V_SQL ||
'OO.SIGN_NO AS SIGNNO, OO.DESTINATION_PROVINCE_NAME AS PROVINCENAME,';
V_SQL := V_SQL ||
'OO.DESTINATION_NAME AS DESTINATIONNAME,OO.WAREHOUSE_NAME AS WAREHOUSENAME,';
V_SQL := V_SQL ||
'OO.ASD AS ASD,OO.CRD AS CRD,OO.EXT_COTRACT_NO AS COTRACTNO,';
V_SQL := V_SQL ||
'OO.ACTUAL_TOTAL_PIECES AS TOTALPIECES,OMS.FACT_START_DATE AS MINDATE,';
V_SQL := V_SQL ||
'OMS.FACT_END_DATE AS MAXDATE,OO.ACTUAL_TOTAL_WEIGHT AS TOTALVOLUME,';
V_SQL := V_SQL ||
'OO.ACTUAL_TOTAL_VOLUME AS TOTALWEIGHT,OO.BUSINESS_STATUS_NAME AS STATUSNAME,';
V_SQL := V_SQL ||
' NVL(TRUNC((SYSDATE - OMS.FACT_START_DATE) * 24, 2), 0) AS WRIMTIME,';
V_SQL := V_SQL ||
'(NVL(OMS.PLAN_QTY, 0) - NVL(OMS.ACTUAL_QTY, 0)) AS UNACTIONQTY,';
V_SQL := V_SQL ||
'((SELECT COUNT(1)FROM TMP_RPT_GOODS_RDC_NEW TMP WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.BATCH_NO='''|| V_SERIAL_NO ||'''';
V_SQL := V_SQL ||
' AND TMP.ACTION_TYPE = 0) -(SELECT COUNT(1) FROM TMP_RPT_GOODS_RDC_NEW TMP ';
V_SQL := V_SQL ||
' WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.ACTION_TYPE = 1 AND TMP.BATCH_NO='''|| V_SERIAL_NO ||''')) AS ACTIONQTY,';
V_SQL := V_SQL ||
'((SELECT NVL(SUM(TMP.WEIGHT), 0) FROM TMP_RPT_GOODS_RDC_NEW TMP ';
V_SQL := V_SQL ||
' WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.ACTION_TYPE = 0 AND TMP.BATCH_NO='''|| V_SERIAL_NO ||''') -';
V_SQL := V_SQL ||
'(SELECT NVL(SUM(TMP.WEIGHT), 0) FROM TMP_RPT_GOODS_RDC_NEW TMP ';
V_SQL := V_SQL ||
' WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.ACTION_TYPE = 1 AND TMP.BATCH_NO='''|| V_SERIAL_NO ||''')) AS GROSSWEIGHT,';
V_SQL := V_SQL ||
'((SELECT NVL(SUM(TMP.VOLUME), 0) FROM TMP_RPT_GOODS_RDC_NEW TMP ';
V_SQL := V_SQL ||
' WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.ACTION_TYPE = 0 AND TMP.BATCH_NO='''|| V_SERIAL_NO ||''') -';
V_SQL := V_SQL ||
' (SELECT NVL(SUM(TMP.VOLUME), 0) FROM TMP_RPT_GOODS_RDC_NEW TMP ';
V_SQL := V_SQL ||
' WHERE TMP.OMS_ORDER_ID = OO.OMS_ORDER_ID AND TMP.ACTION_TYPE = 1 AND TMP.BATCH_NO='''|| V_SERIAL_NO ||''')) AS VOLUME ';
V_SQL := V_SQL ||
' FROM OMS_ORDER OO, OMS_MILESTONE_STATUS OMS WHERE OO.OMS_ORDER_ID = OMS.OMS_ORDER_ID';
V_SQL := V_SQL ||
' AND OO.ACTIVE = 1 AND OO.BUSINESS_STATUS_ID IN (''ddfc129b-fd6e-46af-b20c-ecea2ccaee18'',';
V_SQL := V_SQL ||
' ''8cac03f4-49be-478d-be9b-ada88b232be9'', ''57129ea5-6906-4a0e-b66d-147cc4f81b80'',';
V_SQL := V_SQL ||
'''11f58dca-9011-4cea-8e93-a94472391812'') AND OMS.SERVICE_ID = ''d1df8a30-09b2-4be1-a265-27165a38b687''';
V_SQL := V_SQL || ' AND OMS.STATUS_TYPE = ''CUST OBJECT'' ';
IF V_PROJECT_NAME IS NOT NULL THEN
V_SQL := V_SQL || ' AND (OO.PROJECT_ID = ''' || V_PROJECT_NAME || ''')';
END IF;
IF V_EXT_CUST_NAME IS NOT NULL THEN
V_SQL := V_SQL || ' AND OO.EXT_CUSTMER_NAME = ''' || V_EXT_CUST_NAME || '''';
END IF;
IF V_SIGN_NO IS NOT NULL THEN
V_SQL := V_SQL ||
'AND OO.SIGN_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_SIGN_NO || ''', ''''))))';
END IF;
IF V_FOREIGHT_NO IS NOT NULL THEN
V_SQL := V_SQL ||
'AND OO.FOREIGHT_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_FOREIGHT_NO || ''', ''''))))';
END IF;
IF V_CONTRACT IS NOT NULL THEN
V_SQL := V_SQL ||
'AND OO.COTRACT_NO IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_CONTRACT || ''', ''''))))';
END IF;
IF V_WAREHOUSE_NAME IS NOT NULL THEN
V_SQL := V_SQL || 'AND OO.WAREHOUSE_ID = ''' || V_WAREHOUSE_NAME ||
''' ';
END IF;
IF V_OFFICE_ID IS NOT NULL THEN
V_SQL := V_SQL ||
'AND OO.OFFICE_ID IN (SELECT STR FROM TABLE(STR2LIST(REPLACE(''' ||
V_OFFICE_ID || ''', ''''))))';
END IF;
V_SQL := V_SQL || ')WHERE ACTIONQTY <> 0 ORDER BY WRIMTIME DESC ';
--insert into testsql (sql) values (v_sql);
OPEN OUTCURSOR FOR V_SQL;
DELETE TMP_RPT_GOODS_RDC_NEW A WHERE A.BATCH_NO = V_SERIAL_NO;
COMMIT;
--DBMS_OUTPUT.PUT_LINE(v_sql);
END SP_STOCK_STATISEICE_TONOW_RDC;
uj5u.com熱心網友回復：
看了一下，然后呢？uj5u.com熱心網友回復：
查詢的時候是這個樣子是什么問題Procedure execution failed
ORA-06550: line 1, column 38:
PLS-00905: object OMSTEST.SP_STOCK_STATISEICE_TONOW_RDC is invalid
ORA-06550: line 1, column 28:
PL/SQL: Statement ignored
uj5u.com熱心網友回復：
OMSTEST.SP_STOCK_STATISEICE_TONOW_RDC失效了，重新編譯一下轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99326.html
標籤：開發