nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to add a parameter when calling a function from the trigger section

Vitalii_Alliance_dgtl

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

Find more posts tagged with

BI Publisher Data Models

Accepted answers

All comments

Kevin M-Oracle

The package in the database has to be updated, add the parameter.

Rgds,

Kevin

Vitalii_Alliance_dgtl

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

Kevin M-Oracle

If you add a variable (parameter) in a script, you have to declare it in the database package.

Rgds,

Kevin

Vitalii_Alliance_dgtl

@Kevin M-Oracle

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;

/

Vitalii_Alliance_dgtl

@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;

/
```

Kevin M-Oracle

Any parameter has to be declared in your package and defined in the data model.

What error is being returned.

Rgds,

Kevin

Vitalii_Alliance_dgtl

@Kevin M-Oracle

I don't understand where else I need to add the parameter. I have specified the declaration both in the interface and in the code - in the body of the package procedure and function.

I provided the code above, as well as the first screenshot of the parameter declaration in the BI.

Kevin M-Oracle

In the trigger form simply call the procedure, do not try to declare the parameter.

Rgds,

Kevin