Oracle DB 12.1.0.2
Hello Team,
kindly advise how to get the output of the procedure below on sqldeveloper?
create or replace PROCEDURE SPIO_UTS_BILL_INFO1 (
VI_Tel_Num IN Varchar2,
V_ACCOUNT_CODE OUT Number,
VO_Outstanding_Amt OUT Number,
VO_Waived_Amt OUT Number,
VO_Purchase_Amt OUT Number)
IS
/******************************************************************************
NAME: SPIO_UTS_BILL_INFO
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 04/02/2019 khoosunee 1. Created this procedure.
NOTES:
Object Name: SPIO_UTS_BILL_INFO
Sysdate: 04/02/2019
Description: Stored Procedure to retrieve Waived Amount, Service Outstanding Amount and Purchase (Devices & Accessories) Outstanding Amount based on a phone number
New version updated and optimised on 12/02/2019 -- Breaking the Main Select Statement into various 3 Select Statements avoid joins with more than 2 tables
******************************************************************************/
V_ACC_LINK_CODE NUMBER;
BEGIN
/* Retrieve ACCOUNT_LINK_CODE_N from CB_ACCOUNT_SERVICE_LIST using Phone Number */
SELECT ASL.ACCOUNT_CODE_N
INTO V_ACCOUNT_CODE
FROM CB_ACCOUNT_SERVICE_LIST@cbsstandby ASL
WHERE NVL(ASL.TO_DATE_D,SYSDATE) >= SYSDATE
AND ASL.SERVICE_INFO_V = VI_Tel_Num
AND ASL.TO_DATE_D = (select max(to_date_d) from cb_account_service_list@cbsstandby asl1
where asl1.service_info_v = asl.service_info_v);
/* Waived Amount */
SELECT SUM(NVL(SAP.OVERALL_AMT_N,0))/100
INTO VO_Waived_Amt
FROM CB_ACCOUNT_RECEIVE_PAY@cbsstandby ARP,
CB_SUB_AR_AP@cbsstandby SAP, CB_ACCOUNT_MASTER@cbsstandby CAM
WHERE SAP.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
AND ARP.ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
AND ARP.TRANS_NUM_V = SUBSTR(SAP.TRANS_NUM_V,2,14)
AND SUBSTR(SAP.TRANS_NUM_V,1,3) = '5CR'
AND ARP.USER_CODE_N NOT IN (1,2)
AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;
/* Outstanding Amount */
SELECT SUM(NVL(SAA.OVERALL_AMT_N,0) - NVL(SAA.CLEARED_AMT_N,0))/100
INTO VO_Outstanding_Amt
FROM CB_SUB_AR_AP@cbsstandby SAA , cb_account_master@cbsstandby CAM
WHERE SAA.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;
/* Purchase Outstanding Amount */
SELECT SUM(NVL(SAA.OVERALL_AMT_N,0) - NVL(SAA.CLEARED_AMT_N,0))/100
INTO VO_Purchase_Amt
FROM CB_POS_TRANSACTIONS@cbsstandby CPT ,CB_SUB_AR_AP@cbsstandby SAA, CB_ACCOUNT_MASTER@cbsstandby CAM
WHERE CPT.TRANSACTION_NUM_V = SAA.TRANS_NUM_V
AND SAA.MAIN_ACCOUNT_LINK_CODE_N = CAM.ACCOUNT_LINK_CODE_N
AND CPT.TYPE_OF_SALES_V = 'C'
AND CAM.ACCOUNT_CODE_N = V_ACCOUNT_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
--Consider logging the error and then re-raise
RAISE;
END SPIO_UTS_BILL_INFO1;
expected output should be recordset but only console below is displayed.
Thanks,
Roshan