hey i'm always getting the next message:
Warning: compiled but with compilation errors
can someone help me,please.
This is de package i created/:
CREATE OR REPLACE PACKAGE BODY P_RTB_SECACC_PREDICTIONS AS
/************************************************** ****************************
NAME: P_RTB_SECACC_PREDICTIONS
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
************************************************** ****************************/
PROCEDURE INSERT_RTB_CONTRACT_PRED (IN_JOB_RUN IN NUMBER) IS
LV_FRST_DAY_MTH DATE := "ETL_LIB_RTB"."GET_FIRST_DAY_OF_MONTH"("ETL_LIB_DT WH"."GET_RTB2_MTH_BUS_DAT"()) ;
LV_LST_DAY_MTH DATE := "ETL_LIB_RTB"."GET_LAST_DAY_OF_MONTH" ("ETL_LIB_DTWH"."GET_RTB2_MTH_BUS_DAT"()) ;
LV_LST_DAY_PREV_YEAR DATE := LAST_DAY(ADD_MONTHS(TRUNC(ADD_MONTHS(SYSDATE, -12),'SYYYY'), 11)) ;
LV_END_DAT DATE := ADD_MONTHS("ETL_LIB_DTWH"."GET_RTB2_MTH_BUS_DAT"() , +11);
LV_CNTRCT_ID th_deposit_contract_movements.contract_id%TYPE;
LV_TOT_SUM_MOV th_deposit_contract_movements.movement_fc_amt%TYPE ;
LV_NMBR_SECACC NUMBER(10);
LV_SPLIT_SUM NUMBER(10);
LV_DB_SRC VARCHAR2(10) := "ETL_LIB_RTB_SECACC"."GET_DB_SOURCE_SECACC"();
CURSOR CURS_MOVEMENT IS
SELECT contract_id, sum(movement_fc_amt) as TOTAL_FEE_AMT
FROM th_deposit_contract_movements
where ACCOUNTING_DAT between LV_FRST_DAY_MTH and LV_LST_DAY_MTH
and STANDARD_TEXT_COD = 'CT07'
and ACCOUNTING_TYPE = '01'
group by contract_id;
CURSOR CURS_COUNT_SECACC IS
SELECT COUNT(*) as NUMBER_OF_SECURITY_ACCOUNT
FROM th_deposit_contract_dim TDCD,
th_deposit_balance_month TDBM
WHERE TDCD.ORI_SEC_CAP_PRODUCT = '00'
AND TDCD.LINK_CONTRACT_ID is not null
AND tdcd.STATUS_DESCR = 'OPEN'
AND TDCD.BLOCKADE_COD = '00'
AND TDCD.DEPO_CONTRACT_DIM_IDN = TDBM.DEPO_CONTRACT_DIM_IDN
AND TDBM.BALANCE_DAT = LV_LST_DAY_PREV_YEAR
AND TDBM.BALANCE_LC_AMT > 0
AND TDCD.LINK_CONTRACT_ID in (LV_CNTRCT_ID)
group by TDCD.CONTRACT_ID;
CURSOR CURS_SPLIT_AMT IS
SELECT TDCD.CONTRACT_ID as SECURITY_ACCOUNT
FROM TH_DEPOSIT_CONTRACT_DIM TDCD,
TH_DEPOSIT_BALANCE_MONTH TDBM
WHERE TDCD.ORI_SEC_CAP_PRODUCT = '00'
AND TDCD.LINK_CONTRACT_ID is not null
AND tdcd.STATUS_DESCR = 'OPEN'
AND TDCD.BLOCKADE_COD = '00'
AND TDCD.DEPO_CONTRACT_DIM_IDN = TDBM.DEPO_CONTRACT_DIM_IDN
AND TDBM.BALANCE_DAT = LV_LST_DAY_PREV_YEAR
AND TDBM.BALANCE_LC_AMT > 0
AND TDCD.LINK_CONTRACT_ID in (LV_CNTRCT_ID);
REC_DATA CURS_SPLIT_AMT%ROWTYPE;
BEGIN
OPEN CURS_MOVEMENT;
LOOP
FETCH CURS_MOVEMENT INTO LV_CNTRCT_ID, LV_TOT_SUM_MOV ;
EXIT WHEN CURS_MOVEMENT%NOTFOUND;
OPEN CURS_COUNT_SECACC;
LOOP
FETCH CURS_COUNT_SECACC INTO LV_NMBR_SECACC ;
EXIT WHEN CURS_MOVEMENT%NOTFOUND;
LV_SPLIT_SUM := LV_TOT_SUM_MOV/LV_NMBR_SECACC;
OPEN CURS_SPLIT_AMT;
LOOP
FETCH CURS_SPLIT_AMT INTO REC_DATA;
EXIT WHEN CURS_MOVEMENT%NOTFOUND;
BEGIN
INSERT INTO RTB_CONTRACT_PREDICTION (
CONTRACT_ID,
COMPONENT_COD,
START_DAT,
END_DAT,
AMOUNT,
SPLITTED_FLG,
FACTOR_PCT,
DB_SOURCE,
DURATION_MONTH,
STEP,
LAST_UPDATE_USER,
LAST_UPDATE_DAT)
VALUES( REC_DATA.SECURITY_ACCOUNT,
'FEE_MFR',
LV_FRST_DAY_MTH ,
LV_LST_DAY_PREV_YEAR ,
LV_SPLIT_SUM ,
'Y',
1,
LV_DB_SRC ,
12,
1,
USER ,
SYSDATE);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
END LOOP;
CLOSE CURS_SPLIT_AMT;
END LOOP;
CLOSE CURS_COUNT_SECACC;
END LOOP;
CLOSE CURS_MOVEMENT;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END INSERT_RTB_CONTRACT_PRED;
END P_RTB_SECACC_PREDICTIONS;
/