The CREATE OR REPLACE
PROCEDURE "SP_STOCK_STATISEICE_TONOW_RDC
"/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Module name: RDC current inventory statistics
Function modules:
Create a person: Wu Chao
Create time: 2011-5-9
Parameter description:
Test case:
Change history:==========================
Desired change: ting-hui huang
Modification date: June 10, 2014 14:07:39
Changes: changes to indexed query efficiency
===========================
Translation history:=========================
Translation:
The translation date:
Translation note: support for ORACLE version


===========================
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
(V_PROJECT_NAME IN VARCHAR2, project - belong to
V_EXT_CUST_NAME IN VARCHAR2, - project ownership
V_SIGN_NO IN VARCHAR2, - receipt number
V_FOREIGHT_NO IN VARCHAR2, - odd Numbers
V_CONTRACT VARCHAR2, IN - contract no.
V_WAREHOUSE_NAME IN VARCHAR2, - RDC
V_OFFICE_ID IN VARCHAR2, - organizations
OUTCURSOR OUT PKG_CURSOR. T_CURSOR -- -- return data
) AS

V_SQL VARCHAR2 (30000);
V_INSERT_SQL VARCHAR2 (30000);
V_SERIAL_NO LONG;

The BEGIN
/* the 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 be1 - a265-27165-09 b2-4 a38b687' '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 - 46 af - b20c - ecea2ccaee18', ';
V_INSERT_SQL:=V_INSERT_SQL | |
"' 8 cac03f4 - be 49-478 - d - be9b - ada88b232be9 ' ', ' '57129 a0e ea5-6906-4 - cc4f81b80 b66d - 147', ' ' '11 f58dca cea - 9011-4-8 e93 - a94472391812' ');
V_INSERT_SQL:=V_INSERT_SQL | |
'AND OSODS. SUB_ORDER_TYPE_ID IN (' d1df8a30 be1 - a265-27165-09 b2-4 a38b687', '9748 f0cc - aec7-498 - f - bce5 - f16f78921dfc') '.
V_INSERT_SQL:=V_INSERT_SQL | |
'AND OSODS. STATUS_VALUE_ID IN (' 02375 a2c d0-686 - e - 4173-90-121145 aa737f', '1 f6aa98a - bb0 a56f - 4 - c9b76355e afac - 667') '.

IF V_PROJECT_NAME IS NOT NULL THEN
V_INSERT_SQL:=V_INSERT_SQL | | 'AND (OO) PROJECT_ID="' | |
V_PROJECT_NAME | | ' ' ');
END the 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 the 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 the 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 the 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 the IF;
IF V_WAREHOUSE_NAME IS NOT NULL THEN
V_INSERT_SQL:=V_INSERT_SQL | | 'AND OO.. WAREHOUSE_ID=' '| |
V_WAREHOUSE_NAME | | '"';
END the 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 the IF;
- insert into testsql (SQL) values (V_INSERT_SQL);

-- DBMS_OUTPUT. PUT_LINE (V_INSERT_SQL);
The EXECUTE IMMEDIATE V_INSERT_SQL;
- COMMIT;

V_SQL:='SELECT * FROM (the SELECT OO OMS_ORDER_ID, OMS. The 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 | |
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull