last_trn_time  timestamp;
prev_trn_time   timestamp;

BEGIN
begin
select max(start_date_time) into last_trn_time from  DIGX_CZ_INTERFACE_LOGGER  WHERE DETERMINANT_VALUE = 'OBDX_TZ'
and trunc(start_date_time)=trunc(sysdate);  --and rownum<2;

 select st_dt_time into prev_trn_time from etz_start_date_time where rid=(
  select max(rid) from etz_start_date_time);
BEGIN
if last_trn_time > prev_trn_time then
insert into etz_start_date_time(rid, st_dt_time) values (etz_rtsis_seq.nextval,last_trn_time);
commit;
END IF;
END;
end;

begin
if last_trn_time > prev_trn_time then
insert into  internetbanking (
SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.SOURCEACCOUNT accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    '' beneficiaryName,
    'TANZANIA' beneficiaryCountry,
    '' benBankOrWalletCode,
    a.BEN_PHONE_NO benAccountOrMobileNumber,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    '' serviceStatus,
    a.REFERENCENO transactionRef,
    a.CCY currency,
    a.AMOUNT orgAmount,
    CASE
        WHEN a.CCY = 'USD' THEN TO_NUMBER(a.AMOUNT) -- Convert USD to TZS
        WHEN A.CCY IN ('EUR','GBP') THEN
            ROUND(TO_NUMBER(a.AMOUNT) / GET_EXCHANGE_RATE(F.BRANCH_CODE, a.CCY, 'USD'),2)
        ELSE ROUND(TO_NUMBER(a.AMOUNT) / GET_EXCHANGE_RATE(F.BRANCH_CODE, 'USD', a.CCY),2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.CCY = 'TZS' THEN TO_NUMBER(a.AMOUNT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.AMOUNT) * GET_EXCHANGE_RATE(F.BRANCH_CODE, A.CCY, 'TZS'),2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_BILLS a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F 
    ON a.SOURCEACCOUNT = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.SERVICE_NAME = 'BILL_PAY'
UNION ALL

SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.DEBIT_ACC accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    a.ULTBEN2 beneficiaryName,
    a.CRACCDESC beneficiaryCountry,
    a.AWI benBankOrWalletCode,
    a.CREDIT_ACC benAccountOrMobileNumber,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    'International' serviceStatus,
    a.REFERENCENO transactionRef,
    a.CREDIT_CURRENCY currency,
    a.CREDIT_AMT orgAmount,
    CASE
        WHEN a.CREDIT_CURRENCY = 'USD' THEN TO_NUMBER(a.CREDIT_AMT) -- Convert USD to TZS
        WHEN a.CREDIT_CURRENCY IN ('EUR','GBP') THEN ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE, a.CREDIT_CURRENCY, 'USD'), 2)
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE, 'USD', a.CREDIT_CURRENCY), 2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.CREDIT_CURRENCY = 'TZS' THEN TO_NUMBER(a.CREDIT_AMT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) * get_exchange_rate(F.BRANCH_CODE, a.CREDIT_CURRENCY, 'TZS'), 2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_INTERNATIONAL a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN
    FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F ON a.DEBIT_ACC = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.STATUS = 'SUCCESS'
UNION ALL

SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.DBTACC accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    a.ULTBEN1 beneficiaryName,
    'TANZANIA' beneficiaryCountry,
    'ECOCTZTZXXX' benBankOrWalletCode,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    '' serviceStatus,
    a.REFERENCENO transactionRef,
    a.TXNCCY currency,
    a.ACTAMT orgAmount,
    CASE
        WHEN a.TXNCCY = 'USD' THEN TO_NUMBER(a.ACTAMT) -- Convert USD to TZS
        WHEN a.TXNCCY IN ('EUR','GBP') THEN ROUND(TO_NUMBER(a.ACTAMT) / get_exchange_rate(F.BRANCH_CODE, a.TXNCCY, 'USD'), 2)
        ELSE ROUND(TO_NUMBER(a.ACTAMT) / get_exchange_rate(F.BRANCH_CODE, 'USD', a.TXNCCY), 2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.TXNCCY = 'TZS' THEN TO_NUMBER(a.ACTAMT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.ACTAMT) * get_exchange_rate(F.BRANCH_CODE, a.TXNCCY, 'TZS'), 2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_INTERNAL_TRANSFERS a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN
    FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F ON a.DBTACC = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.STATUS = 'SUCCESS'

   UNION ALL

SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.ACCOUNTNO accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    a.BENEFICIARYFIRSTNAME beneficiaryName,
    a.DESTCOUNTRY beneficiaryCountry,
    a.DESTBANKCODE benBankOrWalletCode,
    a.BENEFICIARYACCOUNTNO benAccountOrMobileNumber,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    '' serviceStatus,
    a.REFERENCENO transactionRef,
    a.RECEIVINGCCYCODE currency,
    a.RECEIVEAMOUNT orgAmount,
    CASE
        WHEN a.RECEIVINGCCYCODE = 'USD' THEN TO_NUMBER(a.RECEIVEAMOUNT) -- Convert USD to TZS
        WHEN a.RECEIVINGCCYCODE IN ('EUR','GBP') THEN ROUND(TO_NUMBER(a.RECEIVEAMOUNT) / get_exchange_rate(F.BRANCH_CODE, a.RECEIVINGCCYCODE, 'USD'), 2)
        ELSE ROUND(TO_NUMBER(a.RECEIVEAMOUNT) / get_exchange_rate(F.BRANCH_CODE, 'USD', a.RECEIVINGCCYCODE), 2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.RECEIVINGCCYCODE = 'TZS' THEN TO_NUMBER(a.RECEIVEAMOUNT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.RECEIVEAMOUNT) * get_exchange_rate(F.BRANCH_CODE, a.RECEIVINGCCYCODE, 'TZS'), 2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_RT_SENDMONEY a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN
    FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F ON a.ACCOUNTNO = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.RESPONSEMESSAGE = 'SUCCESS'
    
   UNION ALL

SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.DEBIT_ACC accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    a.ULTBEN1 beneficiaryName,
    'TANZANIA' beneficiaryCountry,
    a.AWI benBankOrWalletCode,
    a.CREDIT_ACC benAccountOrMobileNumber,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    '' serviceStatus,
    a.REFERENCENO transactionRef,
    a.CREDIT_CURRENCY currency,
    a.CREDIT_AMT orgAmount,
    CASE
        WHEN a.CREDIT_CURRENCY = 'USD' THEN TO_NUMBER(a.CREDIT_AMT) -- Convert USD to TZS
        WHEN a.CREDIT_CURRENCY IN ('EUR','GBP') THEN ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE, a.CREDIT_CURRENCY, 'USD'), 2)
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE, 'USD', a.CREDIT_CURRENCY), 2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.CREDIT_CURRENCY = 'TZS' THEN TO_NUMBER(a.CREDIT_AMT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) * get_exchange_rate(F.BRANCH_CODE, a.CREDIT_CURRENCY, 'TZS'), 2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_DOMESTIC_TRANSFER_1 a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN
    FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F ON a.DEBIT_ACC = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.STATUS = 'SUCCESS'

   UNION ALL

SELECT '' SEQNO,
    TO_CHAR(SYSDATE, 'DDMMYYYYHHMM') reportingDate,
    TO_CHAR(a.START_DATE_TIME, 'DDMMYYYYHHMM') transactionDate,
    a.DEBIT_ACC accountNumber,
    c.PARTY_ID customerIdentificationNumber,
    a.BEN1_REC beneficiaryName,
    'TANZANIA' beneficiaryCountry,
    'ECOCTZTZXXX' benBankOrWalletCode,
    'Internet Banking' serviceCategory,
    'Transfer' subServiceCategory,
    '' serviceStatus,
    a.REFERENCENO transactionRef,
    a.CREDIT_CCY currency,
    a.CREDIT_AMT orgAmount,
    CASE
        WHEN a.CREDIT_CCY = 'USD' THEN TO_NUMBER(a.CREDIT_AMT) -- Convert USD to TZS
        WHEN a.CREDIT_CCY IN ('EUR','GBP') THEN ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE, a.CREDIT_CCY,'USD'),2)
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) / get_exchange_rate(F.BRANCH_CODE,'USD', a.CREDIT_CCY),2)
    END usdAmount, -- Convert orgAmount to USD
    CASE
        WHEN a.CREDIT_CCY = 'TZS' THEN TO_NUMBER(a.CREDIT_AMT)  -- Convert TZS to USD
        ELSE ROUND(TO_NUMBER(a.CREDIT_AMT) * get_exchange_rate(F.BRANCH_CODE,a.CREDIT_CCY, 'TZS'),2)
    END tzsAmount, -- Convert orgAmount to TZS
    0 valueAddedTaxAmount,
    0 exciseDutyAmount,
    0 electronicLevyAmount,
    '' spool_flag
FROM
    OBDX_VW_SELF_TRANSFER_1 a
JOIN
    DIGX_UM_USERPROFILE b ON a.USERID = b.U_NAME
JOIN
    DIGX_UM_USERPARTY_RELATION c ON b.U_NAME = c.USER_ID AND a.USERID = c.USER_ID
JOIN
    FCC_STTMS_CUST_ACCOUNT@RTSIS_INTERNETBANKING_2.DEVDB.DEVWORKLOADLHR.ORACLEVCN.COM F ON a.DEBIT_ACC = F.CUST_AC_NO
WHERE
    c.DETERMINANT_VALUE = 'OBDX_TZ'
    AND TRUNC(a.START_DATE_TIME) = TRUNC(SYSDATE)
    AND (a.START_DATE_TIME > prev_trn_time AND a.START_DATE_TIME <= last_trn_time)
    AND a.STATUS = 'SUCCESS');
 commit;
 END IF;
 END;
END;
/
by

PL/SQL Online Compiler

Write, Run & Share PL/SQL code online using OneCompiler's Oracle PL/SQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for Oracle PL/SQL running on latest version 23c (23.3.0.0). Getting started with the OneCompiler's Oracle PL/SQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'PL/SQL' and start writing code to learn and test online without worrying about tedious process of installation.

About PL/SQL

PL/SQL is procedural extension for SQL created by Oracle. It is by default embedded into the Oracle Database. PL/SQL program units are compiled and stored inside the Oracle Database which results in optimal execution times as the PL/SQL and SQL run within the same server process.

Syntax help

Following is the syntax structure for the PL/SQL code blocks

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Example

DECLARE 
   message  varchar2(100):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Named procedures

CREATE OR REPLACE FUNCTION 
hello_user
   (user_name IN VARCHAR2) 
    RETURN VARCHAR2
IS
BEGIN
   RETURN 'Hello ' || user_name;
END hello_user;
/

BEGIN
   dbms_output.put_line(hello_user('Peter'));
END;
/

Exception handling

BEGIN
  DBMS_OUTPUT.put_line (1/0);
EXCEPTION
  WHEN OTHERS
  THEN
    DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;