Code:
CREATE OR REPLACE FUNCTION "FNC_ZUP22221" (
p_ImportType IN NUMBER
)
RETURN NUMBER IS
/*
* The possible return values are the following:
* 1 = Insert and Delete executed successfully
* 2 = One or more rows in TBL_TEMP_ZUP22221 exist in TBL_VEHICLE
* 3 = p_ImportType is neither 1, 2 nor 3
* 4 = An unhandled exception is thrown
*/
v_Result NUMBER;
v_RowCount NUMBER;
BEGIN
SAVEPOINT SP;
IF p_ImportType = 1 THEN
SELECT COUNT(A.PART_NUMBER)
INTO v_RowCount
FROM TBL_TEMP_ZUP22221 A
INNER JOIN TBL_VEHICLE B
ON A.PART_NUMBER = B.PART_NUMBER
AND A.PS = B.PS
AND A.START_DATE = B.START_DATE
AND A.END_DATE = B.END_DATE
AND A.NAMC = B.NAMC;
IF v_RowCount > 0 THEN
v_Result := 2;
ELSE
INSERT INTO TBL_PLAN_STORE(
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
ORDER_TYPE)
SELECT
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
'V'
FROM TBL_TEMP_ZUP22221;
v_Result := 1;
END IF;
ELSIF p_ImportType = 2 THEN
INSERT INTO TBL_PLAN_STORE(
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
ORDER_TYPE)
SELECT
A.PART_NUMBER,
A.PS,
A.START_DATE,
A.END_DATE,
A.WRKG_DAYS,
A.QTY_PER_CONT,
A.TQN,
A.NAMC,
A.COC,
A.C_OR_D,
A.TP,
A.CUST_ORDER_QTY,
A.DOCK,
A.KANBAN,
A.PART_DESCRIPTION,
A.STORE_ADDRESS,
A.HMM,
A.SUPPLIER_NAME,
A.MAIN_ROUTE_NO,
A.VALID,
A.NAME_OF_TMM,
A.TEL,
A.CROSS_D1,
A.EDI,
A.UPD_TIMESTAMP,
A.USER_NAME,
'V'
FROM TBL_TEMP_ZUP22221 A
INNER JOIN TBL_VEHICLE B
ON A.PART_NUMBER != B.PART_NUMBER
AND A.PS != B.PS
AND A.START_DATE != B.START_DATE
AND A.END_DATE != B.END_DATE
AND A.NAMC != B.NAMC;
INSERT INTO TBL_PLAN_STORE(
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
ORDER_TYPE)
SELECT
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
'V'
FROM TBL_VEHICLE;
v_Result := 1;
ELSIF p_ImportType = 3 THEN
INSERT INTO TBL_PLAN_STORE(
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
ORDER_TYPE)
SELECT
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
'V'
FROM TBL_TEMP_ZUP22221;
INSERT INTO TBL_PLAN_STORE(
PART_NUMBER,
PS,
START_DATE,
END_DATE,
WRKG_DAYS,
QTY_PER_CONT,
TQN,
NAMC,
COC,
C_OR_D,
TP,
CUST_ORDER_QTY,
DOCK,
KANBAN,
PART_DESCRIPTION,
STORE_ADDRESS,
HMM,
SUPPLIER_NAME,
MAIN_ROUTE_NO,
VALID,
NAME_OF_TMM,
TEL,
CROSS_D1,
EDI,
UPD_TIMESTAMP,
USER_NAME,
ORDER_TYPE)
SELECT
A.PART_NUMBER,
A.PS,
A.START_DATE,
A.END_DATE,
A.WRKG_DAYS,
A.QTY_PER_CONT,
A.TQN,
A.NAMC,
A.COC,
A.C_OR_D,
A.TP,
A.CUST_ORDER_QTY,
A.DOCK,
A.KANBAN,
A.PART_DESCRIPTION,
A.STORE_ADDRESS,
A.HMM,
A.SUPPLIER_NAME,
A.MAIN_ROUTE_NO,
A.VALID,
A.NAME_OF_TMM,
A.TEL,
A.CROSS_D1,
A.EDI,
A.UPD_TIMESTAMP,
A.USER_NAME,
'V'
FROM TBL_VEHICLE A
INNER JOIN TBL_TEMP_ZUP22221 B
ON A.PART_NUMBER != B.PART_NUMBER
AND A.PS != B.PS
AND A.START_DATE != B.START_DATE
AND A.END_DATE != B.END_DATE
AND A.NAMC != B.NAMC;
v_Result := 1;
ELSE
v_Result := 3;
END IF;
IF v_Result = 1 THEN
DELETE FROM TBL_TEMP_ZUP22221;
DELETE FROM TBL_VEHICLE;
END IF;
COMMIT;
RETURN v_Result;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO SP;
RETURN 4;
END "FNC_ZUP22221";
/
So calling it exec FNC_ZUP22220(1); is valid? Or am I missing something here? I don't need to edit that procedure, it's from someone I don't know.
Reply With Quote