How to add a parameter when calling a function from the trigger section
I have a parameter that I pass directly to the data model - RDATE, and this parameter is automatically added to the triggers section.
I get an error when calling the function in the usual way - PKG_REPORT.CHECK_RESULT_FROM_CALC_REAL_CASH_EXPIRE(SYSDATE)
"ORA-06550: line 2, column 18: PLS-00302: component 'RDATE' must be declared ORA-06550: line 2, column 1: PL/SQL: Statement ignored"
There is a parameter declaration in the function call
Tagged:
0
Answers
-
The package in the database has to be updated, add the parameter.
Rgds,
Kevin
0 -
Kevin M-Oracle declaring a parameter didn't help.
I have a script in the data model where the :RDATE parameter is used, and parameters are declared in the function I call. If, for example, my parameter in the data model is changed to TRUNC(SYSDATE), and the value is passed to the TRUNC(SYSDATE) function, then everything will work normally.
Іn this case I have everything declared, the problem occurs when I add a variable in the script
0 -
If you add a variable (parameter) in a script, you have to declare it in the database package.
Rgds,
Kevin
0 -
this is my code that I run, all variables are declared, but the problem still occurs
create PACKAGE PKG_VKASIAN_TEST AS FUNCTION CALC_REAL_CASH_EXPIRE_TEST (
R_DATE DATE
)RETURN NUMBER;
PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
R_DATE DATE,
P_UPDATED_ROW_COUNT OUT NUMBER
);
FUNCTION CHECK_RESULT_TEST(
R_DATE DATE
) RETURN BOOLEAN; END PKG_VKASIAN_TEST;
/ create PACKAGE BODY PKG_VKASIAN_TEST AS FUNCTION CHECK_RESULT_TEST(
R_DATE DATE
) RETURN BOOLEAN
IS
V_ROW_COUNT NUMBER;
V_DATE DATE := R_DATE; -- Added variable to accept the date
PRAGMA AUTONOMOUS_TRANSACTION; -- Add this line
BEGIN
V_ROW_COUNT := 0;
DWH.PKG_VKASIAN_TEST.CALC_REAL_CASH_EXPIRE_TEST_2(R_DATE, V_ROW_COUNT);
COMMIT; -- Consider committing the autonomous transaction if needed
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK; -- Roll back any changes if necessary
RETURN FALSE;
END;
FUNCTION CALC_REAL_CASH_EXPIRE_TEST(
R_DATE DATE
)
RETURN NUMBER
IS
ROWS_UPDATED NUMBER;
BEGIN
MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
USING (
WITH
WCONTRACT AS (
SELECT
R_DATE AS FIN_DATE,
CON.CUSTOMER_ID AS CUSTOMER_ID,
CUS.SHORT_NAME AS CUSTOMER_NAME,
CON.ID AS CONTRACT_ID,
CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
FROM SR_BANK.CUSTOMER@PRODDB CUS
JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
and CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
),
WCONTRACT_ACCOUNT AS (
SELECT
CON.FIN_DATE,
CON.CUSTOMER_ID,
CON.CUSTOMER_NAME,
CON.CONTRACT_ID, -- CON.CONTRACT_NO,
CON.CONTRACT_BEGIN_DATE,
CA.ACCOUNT_ID AS ACCOUNT_ID,
ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
BAL.CODE AS ACCOUNT_MASK,
GAM.SYS_NAME AS MACRO_CODE,
ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
FROM WCONTRACT CON
JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
AND BAL.CODE IN ('2909','3578','2203')
JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
),
WEXPIRED_COMMISSION AS (
SELECT
TT.FIN_DATE,
'COMMIS' AS RTYPE,
TT.CONTRACT_ID,
SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WCONTRACT_ACCOUNT TT
WHERE TT.ACCOUNT_MASK = '3578'
GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
),
WCONTRACT_ACCOUNT_PIVOT AS (
SELECT
FIN_DATE,
CONTRACT_ID,
CONTRACT_BODY_ACCOUNT_ID,
EXPIRE_BODY_ACCOUNT_ID,
DEBTS_ACCOUNT_ID
FROM (
SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
FROM WCONTRACT_ACCOUNT CON
)
PIVOT (
MAX(ACCOUNT_ID) AS ACCOUNT_ID
FOR MACRO_CODE IN (
'CREDIT_REST' AS CONTRACT_BODY,
'CREDIT_EXPIRE' AS EXPIRE_BODY,
'CREDIT_ALL_DEBTS' AS DEBTS
)
)
),
WPAYMENT AS (
SELECT
CAP.FIN_DATE,
CAP.CONTRACT_ID,
'BODY' AS PAYMENT_TYPE,
DOC.POST_DATE AS PAYMENT_DATE,
DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
-- DOC.DESCRIPTION
FROM WCONTRACT_ACCOUNT_PIVOT CAP
JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
AND DOC.ACCOUNT_CREDIT_ID = ANY(
CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
)
),
WSCHEDULE AS (
SELECT
FIN_DATE,
CONTRACT_ID AS CONTRACT_ID,
IS_CLOSED,
POST_DATE AS SCHEDULE_DATE,
TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
SCHEDULE_TYPE AS SCHEDULE_TYPE,
AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
FROM (
SELECT
CON.FIN_DATE,
CASE
WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
THEN 1
ELSE 0
END AS IS_CLOSED,
PAY.*
FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
) TT
UNPIVOT INCLUDE NULLS(
AMOUNT_UAH FOR SCHEDULE_TYPE IN (
ANN_AMOUNT_REST AS 'BODY'
)
)
),
WMUST_DATA AS (
SELECT
SCH.FIN_DATE AS FIN_DATE,
SCH.SCHEDULE_TYPE AS RTYPE,
SCH.CONTRACT_ID AS CONTRACT_ID,
SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
FROM WSCHEDULE SCH
WHERE
(
SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
OR
IS_CLOSED = 1
)
GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
),
WDONE_DATA AS (
SELECT
PAY.FIN_DATE AS FIN_DATE,
PAY.PAYMENT_TYPE AS RTYPE,
PAY.CONTRACT_ID AS CONTRACT_ID,
SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WPAYMENT PAY
WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
),
WFINAL_CONTAINER AS (
SELECT
RTY.RTYPE,
CON.*
FROM WCONTRACT CON,
(
SELECT 'BODY' AS RTYPE FROM DUAL
UNION ALL
SELECT 'COMMIS' AS RTYPE FROM DUAL
) RTY
)
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,COALESCE(MST.AMOUNT_UAH,0) -
COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
AND CON.RTYPE = MST.RTYPE
LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
AND CON.RTYPE = DON.RTYPE
WHERE CON.RTYPE = 'BODY'
UNION ALL
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,ECM.AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
AND CON.RTYPE = ECM.RTYPE
WHERE CON.RTYPE = 'COMMIS'
) M2
ON (
M1.RDATE = M2.FIN_DATE
AND
M1.RTYPE = M2.RTYPE
AND
M1.CONTRACT_ID = M2.CONTRACT_ID
)
WHEN MATCHED THEN
UPDATE SET
M1.RVALUE = M2.AMOUNT_UAH
WHEN NOT MATCHED THEN
INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
;
ROWS_UPDATED := SQL%ROWCOUNT;
-- MICRO.SP_LOG_OK('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SQL%ROWCOUNT||' rows was affected');
COMMIT; IF ROWS_UPDATED > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RETURN 0;
END; -- EXCEPTION
-- WHEN OTHERS
-- THEN MICRO.SP_LOG_ERROR('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 900));
-- THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
-- RAISE;
PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
R_DATE DATE,
P_UPDATED_ROW_COUNT OUT NUMBER -- Added parameter to return row count
)
IS
l_row_count NUMBER; -- Variable to store the number of affected rows
BEGIN
-- Perform the MERGE operation
MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
USING (
WITH
WCONTRACT AS (
SELECT
R_DATE AS FIN_DATE,
CON.CUSTOMER_ID AS CUSTOMER_ID,
CUS.SHORT_NAME AS CUSTOMER_NAME,
CON.ID AS CONTRACT_ID,
CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
FROM SR_BANK.CUSTOMER@PRODDB CUS
JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
AND CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
),
WCONTRACT_ACCOUNT AS (
SELECT
CON.FIN_DATE,
CON.CUSTOMER_ID,
CON.CUSTOMER_NAME,
CON.CONTRACT_ID,
-- CON.CONTRACT_NO,
CON.CONTRACT_BEGIN_DATE,
CA.ACCOUNT_ID AS ACCOUNT_ID,
ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
BAL.CODE AS ACCOUNT_MASK,
GAM.SYS_NAME AS MACRO_CODE,
ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
FROM WCONTRACT CON
JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
AND BAL.CODE IN ('2909','3578','2203')
JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
),
WEXPIRED_COMMISSION AS (
SELECT
TT.FIN_DATE,
'COMMIS' AS RTYPE,
TT.CONTRACT_ID,
SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WCONTRACT_ACCOUNT TT
WHERE TT.ACCOUNT_MASK = '3578'
GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
),
WCONTRACT_ACCOUNT_PIVOT AS (
SELECT
FIN_DATE,
CONTRACT_ID,
CONTRACT_BODY_ACCOUNT_ID,
EXPIRE_BODY_ACCOUNT_ID,
DEBTS_ACCOUNT_ID
FROM (
SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
FROM WCONTRACT_ACCOUNT CON
)
PIVOT (
MAX(ACCOUNT_ID) AS ACCOUNT_ID
FOR MACRO_CODE IN (
'CREDIT_REST' AS CONTRACT_BODY,
'CREDIT_EXPIRE' AS EXPIRE_BODY,
'CREDIT_ALL_DEBTS' AS DEBTS
)
)
),
WPAYMENT AS (
SELECT
CAP.FIN_DATE,
CAP.CONTRACT_ID,
'BODY' AS PAYMENT_TYPE,
DOC.POST_DATE AS PAYMENT_DATE,
DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
-- DOC.DESCRIPTION
FROM WCONTRACT_ACCOUNT_PIVOT CAP
JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
AND DOC.ACCOUNT_CREDIT_ID = ANY(
CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
)
),
WSCHEDULE AS (
SELECT
FIN_DATE,
CONTRACT_ID AS CONTRACT_ID,
IS_CLOSED,
POST_DATE AS SCHEDULE_DATE,
TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
SCHEDULE_TYPE AS SCHEDULE_TYPE,
AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
FROM (
SELECT
CON.FIN_DATE,
CASE
WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
THEN 1
ELSE 0
END AS IS_CLOSED,
PAY.*
FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
) TT
UNPIVOT INCLUDE NULLS(
AMOUNT_UAH FOR SCHEDULE_TYPE IN (
ANN_AMOUNT_REST AS 'BODY'
)
)
),
WMUST_DATA AS (
SELECT
SCH.FIN_DATE AS FIN_DATE,
SCH.SCHEDULE_TYPE AS RTYPE,
SCH.CONTRACT_ID AS CONTRACT_ID,
SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
FROM WSCHEDULE SCH
WHERE
(
SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
OR
IS_CLOSED = 1
)
GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
),
WDONE_DATA AS (
SELECT
PAY.FIN_DATE AS FIN_DATE,
PAY.PAYMENT_TYPE AS RTYPE,
PAY.CONTRACT_ID AS CONTRACT_ID,
SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WPAYMENT PAY
WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
),
WFINAL_CONTAINER AS (
SELECT
RTY.RTYPE,
CON.*
FROM WCONTRACT CON,
(
SELECT 'BODY' AS RTYPE FROM DUAL
UNION ALL
SELECT 'COMMIS' AS RTYPE FROM DUAL
) RTY
)
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,COALESCE(MST.AMOUNT_UAH,0) -
COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
AND CON.RTYPE = MST.RTYPE
LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
AND CON.RTYPE = DON.RTYPE
WHERE CON.RTYPE = 'BODY'
UNION ALL
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,ECM.AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
AND CON.RTYPE = ECM.RTYPE
WHERE CON.RTYPE = 'COMMIS'
) M2
ON (
M1.RDATE = M2.FIN_DATE
AND
M1.RTYPE = M2.RTYPE
AND
M1.CONTRACT_ID = M2.CONTRACT_ID
)
WHEN MATCHED THEN
UPDATE SET
M1.RVALUE = M2.AMOUNT_UAH
WHEN NOT MATCHED THEN
INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
; -- Get the number of rows affected
l_row_count := SQL%ROWCOUNT;
-- Commit the transaction
COMMIT;
-- Return the number of updated rows
P_UPDATED_ROW_COUNT := l_row_count;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;
END; END PKG_VKASIAN_TEST;
/0 -
@Kevin M-Oracle hi
this is my code that I run, all variables are declared, but the problem still occurs
```sql create PACKAGE PKG_VKASIAN_TEST AS FUNCTION CALC_REAL_CASH_EXPIRE_TEST (
R_DATE DATE
)RETURN NUMBER;
PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
R_DATE DATE,
P_UPDATED_ROW_COUNT OUT NUMBER
);
FUNCTION CHECK_RESULT_TEST(
R_DATE DATE
) RETURN BOOLEAN; END PKG_VKASIAN_TEST;
/ create PACKAGE BODY PKG_VKASIAN_TEST AS FUNCTION CHECK_RESULT_TEST(
R_DATE DATE
) RETURN BOOLEAN
IS
V_ROW_COUNT NUMBER;
V_DATE DATE := R_DATE; -- Added variable to accept the date
PRAGMA AUTONOMOUS_TRANSACTION; -- Add this line
BEGIN
V_ROW_COUNT := 0;
DWH.PKG_VKASIAN_TEST.CALC_REAL_CASH_EXPIRE_TEST_2(R_DATE, V_ROW_COUNT);
COMMIT; -- Consider committing the autonomous transaction if needed
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
ROLLBACK; -- Roll back any changes if necessary
RETURN FALSE;
END;
FUNCTION CALC_REAL_CASH_EXPIRE_TEST(
R_DATE DATE
)
RETURN NUMBER
IS
ROWS_UPDATED NUMBER;
BEGIN
MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
USING (
WITH
WCONTRACT AS (
SELECT
R_DATE AS FIN_DATE,
CON.CUSTOMER_ID AS CUSTOMER_ID,
CUS.SHORT_NAME AS CUSTOMER_NAME,
CON.ID AS CONTRACT_ID,
CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
FROM SR_BANK.CUSTOMER@PRODDB CUS
JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
and CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
),
WCONTRACT_ACCOUNT AS (
SELECT
CON.FIN_DATE,
CON.CUSTOMER_ID,
CON.CUSTOMER_NAME,
CON.CONTRACT_ID, -- CON.CONTRACT_NO,
CON.CONTRACT_BEGIN_DATE,
CA.ACCOUNT_ID AS ACCOUNT_ID,
ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
BAL.CODE AS ACCOUNT_MASK,
GAM.SYS_NAME AS MACRO_CODE,
ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
FROM WCONTRACT CON
JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
AND BAL.CODE IN ('2909','3578','2203')
JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
),
WEXPIRED_COMMISSION AS (
SELECT
TT.FIN_DATE,
'COMMIS' AS RTYPE,
TT.CONTRACT_ID,
SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WCONTRACT_ACCOUNT TT
WHERE TT.ACCOUNT_MASK = '3578'
GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
),
WCONTRACT_ACCOUNT_PIVOT AS (
SELECT
FIN_DATE,
CONTRACT_ID,
CONTRACT_BODY_ACCOUNT_ID,
EXPIRE_BODY_ACCOUNT_ID,
DEBTS_ACCOUNT_ID
FROM (
SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
FROM WCONTRACT_ACCOUNT CON
)
PIVOT (
MAX(ACCOUNT_ID) AS ACCOUNT_ID
FOR MACRO_CODE IN (
'CREDIT_REST' AS CONTRACT_BODY,
'CREDIT_EXPIRE' AS EXPIRE_BODY,
'CREDIT_ALL_DEBTS' AS DEBTS
)
)
),
WPAYMENT AS (
SELECT
CAP.FIN_DATE,
CAP.CONTRACT_ID,
'BODY' AS PAYMENT_TYPE,
DOC.POST_DATE AS PAYMENT_DATE,
DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
-- DOC.DESCRIPTION
FROM WCONTRACT_ACCOUNT_PIVOT CAP
JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
AND DOC.ACCOUNT_CREDIT_ID = ANY(
CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
)
),
WSCHEDULE AS (
SELECT
FIN_DATE,
CONTRACT_ID AS CONTRACT_ID,
IS_CLOSED,
POST_DATE AS SCHEDULE_DATE,
TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
SCHEDULE_TYPE AS SCHEDULE_TYPE,
AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
FROM (
SELECT
CON.FIN_DATE,
CASE
WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
THEN 1
ELSE 0
END AS IS_CLOSED,
PAY.*
FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
) TT
UNPIVOT INCLUDE NULLS(
AMOUNT_UAH FOR SCHEDULE_TYPE IN (
ANN_AMOUNT_REST AS 'BODY'
)
)
),
WMUST_DATA AS (
SELECT
SCH.FIN_DATE AS FIN_DATE,
SCH.SCHEDULE_TYPE AS RTYPE,
SCH.CONTRACT_ID AS CONTRACT_ID,
SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
FROM WSCHEDULE SCH
WHERE
(
SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
OR
IS_CLOSED = 1
)
GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
),
WDONE_DATA AS (
SELECT
PAY.FIN_DATE AS FIN_DATE,
PAY.PAYMENT_TYPE AS RTYPE,
PAY.CONTRACT_ID AS CONTRACT_ID,
SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WPAYMENT PAY
WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
),
WFINAL_CONTAINER AS (
SELECT
RTY.RTYPE,
CON.*
FROM WCONTRACT CON,
(
SELECT 'BODY' AS RTYPE FROM DUAL
UNION ALL
SELECT 'COMMIS' AS RTYPE FROM DUAL
) RTY
)
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,COALESCE(MST.AMOUNT_UAH,0) -
COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
AND CON.RTYPE = MST.RTYPE
LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
AND CON.RTYPE = DON.RTYPE
WHERE CON.RTYPE = 'BODY'
UNION ALL
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,ECM.AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
AND CON.RTYPE = ECM.RTYPE
WHERE CON.RTYPE = 'COMMIS'
) M2
ON (
M1.RDATE = M2.FIN_DATE
AND
M1.RTYPE = M2.RTYPE
AND
M1.CONTRACT_ID = M2.CONTRACT_ID
)
WHEN MATCHED THEN
UPDATE SET
M1.RVALUE = M2.AMOUNT_UAH
WHEN NOT MATCHED THEN
INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
;
ROWS_UPDATED := SQL%ROWCOUNT;
-- MICRO.SP_LOG_OK('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SQL%ROWCOUNT||' rows was affected');
COMMIT; IF ROWS_UPDATED > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error Message: ' || SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RETURN 0;
END; -- EXCEPTION
-- WHEN OTHERS
-- THEN MICRO.SP_LOG_ERROR('CALC_CASH_EXPIRED', TO_CHAR(R_DATE,'DD.MM.YYYY')||' - '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 900));
-- THEN DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
-- RAISE;
PROCEDURE CALC_REAL_CASH_EXPIRE_TEST_2(
R_DATE DATE,
P_UPDATED_ROW_COUNT OUT NUMBER -- Added parameter to return row count
)
IS
l_row_count NUMBER; -- Variable to store the number of affected rows
BEGIN
-- Perform the MERGE operation
MERGE INTO DWH.AD_CASH_FACT_EXPIRED_DATA M1
USING (
WITH
WCONTRACT AS (
SELECT
R_DATE AS FIN_DATE,
CON.CUSTOMER_ID AS CUSTOMER_ID,
CUS.SHORT_NAME AS CUSTOMER_NAME,
CON.ID AS CONTRACT_ID,
CON.BEGIN_BASE_AMOUNT AS CONTRACT_BEGIN_AMOUNT_UAH,
CON.BEGIN_DATE AS CONTRACT_BEGIN_DATE,
CON.CLOSE_DATE AS CONTRACT_CLOSE_DATE
FROM SR_BANK.CUSTOMER@PRODDB CUS
JOIN SR_BANK.CONTRACT@PRODDB CON ON CUS.ID = CON.CUSTOMER_ID
JOIN SR_BANK.CONTRACT_TYPE@PRODDB COT ON CON.TYPE_ID = COT.ID
AND COT.SYS_NAME = 'I_POTR_GNV' -- тільки кеш кредити
AND R_DATE BETWEEN CON.BEGIN_DATE AND COALESCE(CON.CLOSE_DATE,TRUNC(SYSDATE+10))
AND CON.BEGIN_DATE < TO_DATE('01.10.2022','DD.MM.YYYY') -- треба брати тільки кредити, що були відкриті до жовтня 2022
),
WCONTRACT_ACCOUNT AS (
SELECT
CON.FIN_DATE,
CON.CUSTOMER_ID,
CON.CUSTOMER_NAME,
CON.CONTRACT_ID,
-- CON.CONTRACT_NO,
CON.CONTRACT_BEGIN_DATE,
CA.ACCOUNT_ID AS ACCOUNT_ID,
ACC.ACCOUNT_NUMBER AS ACCOUNT_NO,
BAL.CODE AS ACCOUNT_MASK,
GAM.SYS_NAME AS MACRO_CODE,
ASN.BASE_AMOUNT AS ACCOUNT_AMOUNT_UAH
FROM WCONTRACT CON
JOIN SR_BANK.COMPOUND_DOCUMENT@PRODDB COD ON CON.CONTRACT_ID = COD.ID
JOIN SR_BANK.CONTRACT_ACCOUNT@PRODDB CA ON CON.CONTRACT_ID = CA.CONTRACT_ID
JOIN SR_BANK.CONTRACT_ACC_MACRO@PRODDB CAM ON CA.MACRO_ID = CAM.ID
JOIN SR_BANK.GENERATOR_ACC_MACRO@PRODDB GAM ON CAM.MACRO_ID = GAM.ID
JOIN SR_BANK.ACCOUNT@PRODDB ACC ON CA.ACCOUNT_ID = ACC.ID
JOIN SR_BANK.BALANCE_ACCOUNT@PRODDB BAL ON ACC.BALANCE_ID = BAL.ID
AND BAL.CODE IN ('2909','3578','2203')
JOIN SR_BANK.ACCOUNT_SNAPSHOT@PRODDB ASN ON CA.ACCOUNT_ID = ASN.ACCOUNT_ID
AND ASN.SNAPSHOT_DATE = CON.FIN_DATE
),
WEXPIRED_COMMISSION AS (
SELECT
TT.FIN_DATE,
'COMMIS' AS RTYPE,
TT.CONTRACT_ID,
SUM(TT.ACCOUNT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WCONTRACT_ACCOUNT TT
WHERE TT.ACCOUNT_MASK = '3578'
GROUP BY TT.FIN_DATE, TT.CONTRACT_ID
),
WCONTRACT_ACCOUNT_PIVOT AS (
SELECT
FIN_DATE,
CONTRACT_ID,
CONTRACT_BODY_ACCOUNT_ID,
EXPIRE_BODY_ACCOUNT_ID,
DEBTS_ACCOUNT_ID
FROM (
SELECT CON.CONTRACT_ID, ACCOUNT_ID, MACRO_CODE, FIN_DATE
FROM WCONTRACT_ACCOUNT CON
)
PIVOT (
MAX(ACCOUNT_ID) AS ACCOUNT_ID
FOR MACRO_CODE IN (
'CREDIT_REST' AS CONTRACT_BODY,
'CREDIT_EXPIRE' AS EXPIRE_BODY,
'CREDIT_ALL_DEBTS' AS DEBTS
)
)
),
WPAYMENT AS (
SELECT
CAP.FIN_DATE,
CAP.CONTRACT_ID,
'BODY' AS PAYMENT_TYPE,
DOC.POST_DATE AS PAYMENT_DATE,
DOC.BASE_AMOUNT AS PAYMENT_AMOUNT_UAH
-- DOC.DESCRIPTION
FROM WCONTRACT_ACCOUNT_PIVOT CAP
JOIN SR_BANK.COMPOUND_ACTION@PRODDB COA ON CAP.CONTRACT_ID = COA.COMPOUND_DOCUMENT_ID
JOIN SR_BANK.DOCUMENT@PRODDB DOC ON COA.ACTION_ID = DOC.ACTION_ID
AND DOC.ACCOUNT_DEBIT_ID = CAP.DEBTS_ACCOUNT_ID
AND DOC.ACCOUNT_CREDIT_ID = ANY(
CAP.CONTRACT_BODY_ACCOUNT_ID, CAP.EXPIRE_BODY_ACCOUNT_ID
)
),
WSCHEDULE AS (
SELECT
FIN_DATE,
CONTRACT_ID AS CONTRACT_ID,
IS_CLOSED,
POST_DATE AS SCHEDULE_DATE,
TRUNC(POST_DATE,'MM') AS SCHEDULE_MONTH,
SCHEDULE_TYPE AS SCHEDULE_TYPE,
AMOUNT_UAH AS SCHEDULE_AMOUNT_UAH
FROM (
SELECT
CON.FIN_DATE,
CASE
WHEN CON.CONTRACT_CLOSE_DATE <= CON.FIN_DATE
THEN 1
ELSE 0
END AS IS_CLOSED,
PAY.*
FROM SR_BANK.PAYMENT_SCHEDULE@PRODDB PAY
JOIN WCONTRACT CON ON PAY.CONTRACT_ID = CON.CONTRACT_ID
) TT
UNPIVOT INCLUDE NULLS(
AMOUNT_UAH FOR SCHEDULE_TYPE IN (
ANN_AMOUNT_REST AS 'BODY'
)
)
),
WMUST_DATA AS (
SELECT
SCH.FIN_DATE AS FIN_DATE,
SCH.SCHEDULE_TYPE AS RTYPE,
SCH.CONTRACT_ID AS CONTRACT_ID,
SUM(SCH.SCHEDULE_AMOUNT_UAH) AS AMOUNT_UAH
FROM WSCHEDULE SCH
WHERE
(
SCHEDULE_DATE <= R_DATE AND IS_CLOSED = 0
OR
IS_CLOSED = 1
)
GROUP BY SCH.FIN_DATE, SCH.SCHEDULE_TYPE, SCH.CONTRACT_ID
),
WDONE_DATA AS (
SELECT
PAY.FIN_DATE AS FIN_DATE,
PAY.PAYMENT_TYPE AS RTYPE,
PAY.CONTRACT_ID AS CONTRACT_ID,
SUM(PAY.PAYMENT_AMOUNT_UAH) AS AMOUNT_UAH
FROM WPAYMENT PAY
WHERE PAY.PAYMENT_DATE <= PAY.FIN_DATE
GROUP BY PAY.FIN_DATE, PAY.PAYMENT_TYPE, PAY.CONTRACT_ID
),
WFINAL_CONTAINER AS (
SELECT
RTY.RTYPE,
CON.*
FROM WCONTRACT CON,
(
SELECT 'BODY' AS RTYPE FROM DUAL
UNION ALL
SELECT 'COMMIS' AS RTYPE FROM DUAL
) RTY
)
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,COALESCE(MST.AMOUNT_UAH,0) -
COALESCE(DON.AMOUNT_UAH,0) AS AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WMUST_DATA MST ON CON.CONTRACT_ID = MST.CONTRACT_ID
AND CON.RTYPE = MST.RTYPE
LEFT JOIN WDONE_DATA DON ON CON.CONTRACT_ID = DON.CONTRACT_ID
AND CON.RTYPE = DON.RTYPE
WHERE CON.RTYPE = 'BODY'
UNION ALL
SELECT
CON.FIN_DATE, CUSTOMER_ID, CUSTOMER_NAME, CON.CONTRACT_ID, -- CONTRACT_NO,
CONTRACT_BEGIN_AMOUNT_UAH, CONTRACT_BEGIN_DATE, CONTRACT_CLOSE_DATE
,CON.RTYPE
,ECM.AMOUNT_UAH
FROM WFINAL_CONTAINER CON
LEFT JOIN WEXPIRED_COMMISSION ECM ON CON.CONTRACT_ID = ECM.CONTRACT_ID
AND CON.RTYPE = ECM.RTYPE
WHERE CON.RTYPE = 'COMMIS'
) M2
ON (
M1.RDATE = M2.FIN_DATE
AND
M1.RTYPE = M2.RTYPE
AND
M1.CONTRACT_ID = M2.CONTRACT_ID
)
WHEN MATCHED THEN
UPDATE SET
M1.RVALUE = M2.AMOUNT_UAH
WHEN NOT MATCHED THEN
INSERT (M1.RDATE, M1.CUSTOMER_ID, M1.CONTRACT_ID, M1.RTYPE, M1.RVALUE)
VALUES (M2.FIN_DATE, M2.CUSTOMER_ID, M2.CONTRACT_ID, M2.RTYPE, M2.AMOUNT_UAH)
; -- Get the number of rows affected
l_row_count := SQL%ROWCOUNT;
-- Commit the transaction
COMMIT;
-- Return the number of updated rows
P_UPDATED_ROW_COUNT := l_row_count;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
RAISE;
END; END PKG_VKASIAN_TEST;
/ ```0 -
Any parameter has to be declared in your package and defined in the data model.
What error is being returned.
Rgds,
Kevin
0