By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE blu_adj_statistics (
system_date DATE,
document_category VARCHAR2(50),
document_source VARCHAR2(50),
claim_type VARCHAR2(50),
claim_iteration NUMBER,
hsa_indicator NUMBER,
edi_app_source VARCHAR2(50)
)
INSERT INTO blu_adj_statistics VALUES (SYSDATE, 'A', 'B', 'C', 1, NULL, 'D');
1 rows affected
CREATE TYPE type_trdelclvr_QADJ as object(
TOTAL_CLAIMS INTEGER,
calculated_year INTEGER,
MTH varchar2(500),
CLAIM INTEGER,
CIW INTEGER,
PAPER INTEGER,
MBRECLAIM INTEGER,
PRVRDIALUP INTEGER,
PRVRIP INTEGER,
ELECTRONIC INTEGER
);
CREATE TYPE table_trdelclvr_QADJ is table of type_trdelclvr_QADJ;
CREATE OR REPLACE PACKAGE P_trdelclvr01 AS
FUNCTION fn_trdelclvr_QADJ(
P_CLAIM varchar2,
P_CIW varchar2,
P_PP varchar2,
P_PAPER varchar2,
P_MBR varchar2,
P_EDI varchar2,
P_MBR_NatApp varchar2,
P_CDAnet_DialUp varchar2,
P_CDAnet_IP varchar2,
P_CDAnet_ICA varchar2,
P_START_DATE date,
P_END_DATE date,
P_DENTAL varchar2,
P_IVR varchar2
) return table_trdelclvr_QADJ;
END P_trdelclvr01;
/
CREATE OR REPLACE PACKAGE BODY P_trdelclvr01 AS
FUNCTION fn_trdelclvr_QADJ(
P_CLAIM varchar2,
P_CIW varchar2,
P_PP varchar2,
P_PAPER varchar2,
P_MBR varchar2,
P_EDI varchar2,
P_MBR_NatApp varchar2,
P_CDAnet_DialUp varchar2,
P_CDAnet_IP varchar2,
P_CDAnet_ICA varchar2,
P_START_DATE date,
P_END_DATE date,
P_DENTAL varchar2,
P_IVR varchar2
) RETURN table_trdelclvr_QADJ
AS
test_type table_trdelclvr_QADJ;
BEGIN
SELECT type_trdelclvr_QADJ(
count(1),
TO_CHAR(TRUNC(system_date, 'MM'),'YYYY'),
TO_CHAR(TRUNC(system_date, 'MM'),'YYYY-MM'),
sum(decode(document_category, P_CLAIM ,1,0)),
sum(decode(document_category, P_CIW ,1, P_PP ,1,0)),
sum(decode(document_source,P_PAPER,1,0)),
sum(decode(document_source, P_MBR,decode(edi_app_source,P_MBR,1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_MBR_NatApp, 1,0),0)),
sum(decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_DialUp, 1,0),0)),
sum(decode(document_source, P_EDI,decode(edi_app_source, P_CDAnet_IP, 1,0),0)
+ decode(document_source, P_EDI, decode(edi_app_source, P_CDAnet_ICA, 1,0),0)),
sum(decode(document_source, P_EDI, 1,0))
)
BULK COLLECT INTO test_type
FROM blu_adj_statistics adj
SELECT * FROM USER_ERRORS;
DECLARE
v_data table_trdelclvr_QADJ;
BEGIN
v_data := P_trdelclvr01.fn_trdelclvr_QADJ(
P_CLAIM => 'A',
P_CIW => 'A',
P_PP => 'X',
P_PAPER => 'B',
P_MBR => 'B',
P_EDI => 'D',
P_MBR_NatApp => 'D',
P_CDAnet_DialUp => 'D',
P_CDAnet_IP => 'D',
P_CDAnet_ICA => 'D',
P_START_DATE => TRUNC(SYSDATE),
P_END_DATE => TRUNC(SYSDATE),
P_DENTAL => 'C',
P_IVR => 'A'
);
FOR i IN 1 .. v_data.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(
v_data(i).TOTAL_CLAIMS
||', '||v_data(i).calculated_year
||', '||v_data(i).MTH
||', '||v_data(i).CLAIM
||', '||v_data(i).CIW
||', '||v_data(i).PAPER
||', '||v_data(i).MBRECLAIM
||', '||v_data(i).PRVRDIALUP
||', '||v_data(i).PRVRIP
||', '||v_data(i).ELECTRONIC
);
END LOOP;
END;
/
1 rows affected
dbms_output:
1, 2024, 2024-08, 1, 1, 1, 0, 0, 0, 0