CREATE OR REPLACE PACKAGE provider_connect_claims_pkg AS
/********************************************************************************************************************
** NAME : provider_connect_claims_pkg **
** **
** VERSION : 1.0 **
** **
** DESCRIPTION : Process to search claims and display claim details for Provider portal UI Screen **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 04/08/2024 **
**-----------------------------------------------------------------------------------------------------------------**
** CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
** ---------------| ------------ | -----------| -------------------------------------------------------------------**
** 1.0 | 04/08/2024 | Cognizant | Initial Version **
********************************************************************************************************************/
/********************************************************************************************************************
* Declaration and Initializing Procedures **
********************************************************************************************************************/
PROCEDURE provider_connect_claim_search_prc (v_jsonIn IN CLOB,v_response OUT CLOB); -- Procedure to search claim details based on user search
PROCEDURE provider_connect_claim_summary_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB); -- Procedure to get claims details
PROCEDURE provider_connect_payment_details_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB); -- Procedure to get claims details
PROCEDURE provider_connect_claim_line_details_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB); -- Procedure to get claim line details
PROCEDURE provider_connect_claim_download_prc (
v_clm_number IN CLOB,
v_begin_date IN VARCHAR2,
v_end_date IN VARCHAR2,
v_mem_member_id IN VARCHAR2,
v_mem_first_name IN VARCHAR2,
v_mem_last_name IN VARCHAR2,
v_mem_dob IN VARCHAR2,
v_patient_ssn IN VARCHAR2,
v_mem_medicaid_id IN VARCHAR2,
v_check_number IN VARCHAR2,
v_prov_tin IN VARCHAR2,
v_prov_npi IN VARCHAR2,
v_xmlout OUT CLOB
); -- Procedure to download report for claim page details
FUNCTION get_claim_lists (v_claim_id VARCHAR2) RETURN VARCHAR2; -- Function to get claim list when multiple claims are entered into search
END provider_connect_claims_pkg ;
/
CREATE OR REPLACE PACKAGE BODY provider_connect_claims_pkg AS
------------------------------------------------------------------------------------------
-- Global variables declaration
------------------------------------------------------------------------------------------
v_return_code VARCHAR2(10);
v_program_error EXCEPTION;
v_query CLOB;
/********************************************************************************************************************
** NAME : provider_connect_claim_search_prc **
** **
** VERSION : 1.4 **
** **
** DESCRIPTION : To get claim details for claims search criteria for claims UI screen **
** **
** PARAMETERS : **
** INPUT : JSONIN CLOB **
** OUTPUT : Response with Data base result-set **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 05/09/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 04/08/2024 | Cognizant | Initial Version **
* 1.1 | 04/10/2024 | Cognizant | Pagination changes **
* 1.2 | 05/06/2024 | Cognizant | Fetch claims within user entered date range **
* 1.3 | 05/08/2024 | Cognizant | Added source condition to remove duplicate patient information under**
* the same claim numbers **
* 1.4 | 05/09/2024 | Cognizant | Logic to include pended claims **
********************************************************************************************************************/
PROCEDURE provider_connect_claim_search_prc (v_jsonIn IN CLOB,v_response OUT CLOB)
AS
/***********************
{
"UserProfile":
{
},
"Filter":
{
},
"Offset":"",
"Limit":""
}
************************/
------------------------------------------------------------------------------------------
-- Initializing variables for json objects / arrays
------------------------------------------------------------------------------------------
v_jsonObj JSON_OBJECT_T := JSON_OBJECT_T.parse(v_jsonIn);
v_claims_search_obj JSON_OBJECT_T;
v_claims_search_ary JSON_ARRAY_T := JSON_ARRAY_T ();
v_response_obj JSON_OBJECT_T := JSON_OBJECT_T();
------------------------------------------------------------------------------------------
-- Initializing variables for input parameters
------------------------------------------------------------------------------------------
v_claim_number CLOB := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Number'));
v_begin_date VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Begin_Date'));
v_end_date VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('End_Date'));
v_mem_member_id VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Member_Id'));
v_mem_first_name VARCHAR2(100) := TRIM(LOWER(v_jsonObj.get_Object('Filter').get_String('First_Name')));
v_mem_last_name VARCHAR2(100) := TRIM(LOWER(v_jsonObj.get_Object('Filter').get_String('Last_Name')));
v_mem_dob VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Dob'));
v_patient_ssn VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Patient_Ssn'));
v_mem_medicaid_id VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Medicaid_Id'));
v_check_number VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Check_Number'));
v_prov_tin VARCHAR2(30) := TRIM(v_jsonObj.get_Object('Filter').get_String('Prov_Tin'));
v_prov_npi VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Prov_Npi'));
-- V 1.1 Changes Begin
v_rows_perpage CLOB := TRIM(v_jsonObj.get_Object('Filter').get_String('Rows_Per_Page'));
v_page_num CLOB := TRIM(v_jsonObj.get_Object('Filter').get_String('Page_Number'));
-- V 1.1 Changes End
------------------------------------------------------------------------------------------
-- Initializing variables for user profile
------------------------------------------------------------------------------------------
v_user_id CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Id'));
v_user_name CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Name'));
v_user_role CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Role'));
v_user_tin CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Tin'));
--v_user_npi CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Npi'));
v_usage_page CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page'));
v_usage_page_clr_Code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page_Colour_Code'));
v_usage_section CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section'));
v_usage_sec_clr_code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section_Colour_Code'));
v_usage_action CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action'));
v_usage_action_criteria CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action_Criteria'));
v_usage_api CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Api'));
------------------------------------------------------------------------------------------
-- Local variables declaration
------------------------------------------------------------------------------------------
v_get_claim_nos CLOB;
v_claim_id CLOB;
v_days NUMBER;
v_date VARCHAR2(10);
v_ind VARCHAR2(5) := 'N';
-- V 1.1 Changes Begin
v_limit NUMBER := TO_NUMBER(v_rows_perpage);
v_offset NUMBER := v_limit * (TO_NUMBER(v_page_num)-1);
v_rec_count NUMBER;
-- V 1.1 Changes End
v_query1 CLOB; -- V 1.4 Changes
------------------------------------------------------------------------------------------
-- Type declaration for search details
------------------------------------------------------------------------------------------
TYPE clm_search_rec IS RECORD
(
claim_number CLOB,
claim_fact_key CLOB,
member_id CLOB,
member_first_name CLOB,
member_last_name CLOB,
member_dob CLOB,
patient_ssn CLOB,
check_number CLOB,
medicaid_id CLOB,
patient_name CLOB,
claim_status CLOB,
paid_to CLOB,
claim_data_updated CLOB,
date_of_service CLOB,
claim_status_php CLOB,
total_amount_values CLOB
);
TYPE clm_search_rec_type IS TABLE OF clm_search_rec INDEX BY PLS_INTEGER;
v_clm_search_rec_type clm_search_rec_type;
BEGIN
------------------------------------------------------------------------------------------
-- Insert usage details into usage table : provider_connect_usage_tbl
------------------------------------------------------------------------------------------
IF v_user_id IS NOT NULL THEN
v_return_code := provider_connect_usage_pkg.provider_connect_usage_internal_insert_prc(v_user_id,
v_user_name,
v_user_role,
v_user_tin,
--v_user_npi,
v_usage_page,
v_usage_page_clr_Code,
v_usage_section,
v_usage_sec_clr_code,
v_usage_action,
v_usage_action_criteria,
v_usage_api
);
END IF;
IF v_return_code <> 0 THEN
RAISE v_program_error;
END IF;
------------------------------------------------------------------------------------------
-- To get number of days
------------------------------------------------------------------------------------------
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) THEN
SELECT TO_DATE(v_end_date, 'YYYYMMDD') - TO_DATE(v_begin_date, 'YYYYMMDD') INTO v_days FROM DUAL;
END IF;
------------------------------------------------------------------------------------------
-- To check null input parameters
------------------------------------------------------------------------------------------
IF ( v_claim_number IS NULL AND
v_begin_date IS NULL AND
v_end_date IS NULL AND
v_mem_member_id IS NULL AND
v_mem_first_name IS NULL AND
v_mem_last_name IS NULL AND
v_mem_dob IS NULL AND
v_patient_ssn IS NULL AND
v_mem_medicaid_id IS NULL AND
v_check_number IS NULL AND
v_prov_npi IS NULL
) THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'System could not find valid details .Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSIF ((v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date > v_end_date)) OR (v_days > 93) THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'You can select maximum 3 months of data and End date should be greater than begin date.Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSIF (v_claim_number IS NOT NULL OR
v_check_number IS NOT NULL) OR
((v_mem_member_id IS NOT NULL OR
v_patient_ssn IS NOT NULL OR
v_mem_medicaid_id IS NOT NULL) AND
(v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL)) OR
((v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL) AND
(v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL)) OR
(v_prov_npi IS NOT NULL AND
v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL) THEN
-- V 1.4 Changes Begin
------------------------------------------------------------------------------------------
-- Split claim numbers
------------------------------------------------------------------------------------------
IF v_claim_number IS NOT NULL THEN
v_get_claim_nos := get_claim_lists(v_claim_number);
v_claim_number := v_get_claim_nos;
END IF;
------------------------------------------------------------------------------------------
-- Assigning select statement into variable to fetch total count of records
------------------------------------------------------------------------------------------
v_query1 := 'SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key
FROM claims_hub.hub_claims_medical clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
IF v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
IF v_claim_number IS NOT NULL OR v_check_number IS NOT NULL THEN
IF v_claim_number IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
ELSE
v_query1 := v_query1 || 'WHERE (1=1 OR clm.claim_hcc_id IS NULL) ';
END IF;
IF v_check_number IS NOT NULL THEN
v_query1 := v_query1 || 'AND clm.check_number = '''|| v_check_number || ''' ';
END IF;
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query1 := v_query1 || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query1 := v_query1 || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query1 := v_query1 || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query1 := v_query1 || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL ) THEN
v_query1 := v_query1 || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query1 := v_query1 || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query1 := v_query1 || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
v_query1 := v_query1 || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query1 := v_query1 || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
END IF;
v_query1 := v_query1 || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query1 := v_query1 || ' AND clm.claim_line_status_code <> ''r'' ';
v_query1 := v_query1 || ' AND ees.source = SUBSTR(clm.source,1,3) ';
IF v_check_number IS NULL THEN
v_query1 := v_query1 || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key
FROM claims_hub.hub_claims_medical_othsts clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
IF v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
IF v_claim_number IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query1 := v_query1 || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query1 := v_query1 || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query1 := v_query1 || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query1 := v_query1 || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL ) THEN
v_query1 := v_query1 || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query1 := v_query1 || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query1 := v_query1 || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query1 := v_query1 || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
v_query1 := v_query1 || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query1 := v_query1 || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
END IF;
v_query1 := v_query1 || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query1 := v_query1 || ' AND clm.claim_line_status_code <> ''r'' ';
v_query1 := v_query1 || ' AND ees.source = SUBSTR(clm.source,1,3) ';
--v_query1 := v_query1 || ' AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ';
END IF;
------------------------------------------------------------------------------------------
-- Select statement execution for total number of records and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ( ' || v_query1 || ' )' INTO v_rec_count;
IF v_rec_count > 0 THEN
-- V 1.4 Changes End
------------------------------------------------------------------------------------------
-- Assigning select statement into variable to fetch paginated data
------------------------------------------------------------------------------------------
-- V 1.4 Changes Begin
v_query := 'SELECT *
FROM (
-- V 1.4 Changes End
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_first_name) AS member_first_name,
INITCAP(ees.meme_last_name) AS member_last_name,
TO_CHAR(ees.meme_birth_dt,''MM/DD/YYYY'') AS member_dob,
ees.meme_ssn AS patient_ssn,
clm.check_number AS check_number,
ees.meme_medcd_no AS medicaid_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
clm.claim_status AS claim_status,
( SELECT INITCAP(prv1.provider_name)
FROM claims_hub.hub_claims_to_provider prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
AND prv1.provider_type = ''SUPPLIER''
) AS paid_to,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)) AS date_of_service,
clm.claim_status_php AS claim_status_php,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charge'' VALUE SUM(clm2.billed_amount),
KEY ''Paid_Amount'' VALUE SUM(clm2.paid_amount)
)
FROM claims_hub.hub_claims_medical clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND (clm2.billed_amount IS NOT NULL OR clm2.paid_amount IS NOT NULL)
AND clm2.claim_line_status_code <> ''r''
) AS total_amount_values
FROM claims_hub.hub_claims_medical clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
-- V 1.4 Changes Begin
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'LEFT OUTER JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
-- V 1.4 Changes End
-- V 1.2 Changes Begin
/*v_begin_date := SUBSTR(v_begin_date,1,6);
v_end_date := SUBSTR(v_end_date,1,6);*/
-- V 1.2 Changes End
IF v_claim_number IS NOT NULL OR v_check_number IS NOT NULL THEN
IF v_claim_number IS NOT NULL THEN
-- V 1.4 Changes Begin
/*v_get_claim_nos := get_claim_lists(v_claim_number);
v_claim_number := v_get_claim_nos;*/
-- V 1.4 Changes End
v_query := v_query || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
ELSE
v_query := v_query || 'WHERE (1=1 OR clm.claim_hcc_id IS NULL) ';
END IF;
IF v_check_number IS NOT NULL THEN
v_query := v_query || 'AND clm.check_number = '''|| v_check_number || ''' ';
END IF;
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query := v_query || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query := v_query || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query := v_query || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL ) THEN
v_query := v_query || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query := v_query || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query := v_query || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query := v_query || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
-- V 1.2 Changes Begin
--v_query := v_query || 'AND clm.svc_dt_beg_yyyymm BETWEEN ''' || v_begin_date || ''' AND '''|| v_end_date ||''' ';
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
-- V 1.2 Changes End
END IF;
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) '; -- V 1.3 Changes
-- V 1.4 Changes Begin
--v_query := v_query || ' ORDER BY clm.claim_hcc_id , clm.claim_fact_key ';
IF v_check_number IS NULL THEN
v_query := v_query || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_first_name) AS member_first_name,
INITCAP(ees.meme_last_name) AS member_last_name,
TO_CHAR(ees.meme_birth_dt,''MM/DD/YYYY'') AS member_dob,
ees.meme_ssn AS patient_ssn,
TO_NUMBER('''') AS check_number,
ees.meme_medcd_no AS medicaid_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
clm.claim_status AS claim_status,
(SELECT INITCAP(prv1.supplier_name)
FROM claims_hub.hub_claims_to_supplier_othsts prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
) AS paid_to,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical_othsts clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)) AS date_of_service,
clm.claim_status_php AS claim_status_php,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charge'' VALUE SUM(clm2.billed_amount),
KEY ''Paid_Amount'' VALUE SUM(clm2.paid_amount)
)
FROM claims_hub.hub_claims_medical_othsts clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND (clm2.billed_amount IS NOT NULL OR clm2.paid_amount IS NOT NULL)
AND clm2.claim_line_status_code <> ''r''
) AS total_amount_values
FROM claims_hub.hub_claims_medical_othsts clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
-- V 1.4 Changes Begin
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'LEFT OUTER JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
-- V 1.2 Changes End
IF v_claim_number IS NOT NULL THEN
v_query := v_query || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query := v_query || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query := v_query || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query := v_query || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL ) THEN
v_query := v_query || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query := v_query || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query := v_query || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query := v_query || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
END IF;
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) ';
--v_query := v_query || ' AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ';
END IF;
v_query := v_query || ' )ORDER BY claim_number,claim_fact_key ';
-- V 1.4 Changes End
-- V 1.1 Changes Begin
-- V 1.4 Changes Begin
/*------------------------------------------------------------------------------------------
-- Select statement execution for total number of records and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ( ' || v_query || ' )' INTO v_rec_count;*/
-- V 1.4 Changes End
------------------------------------------------------------------------------------------
-- Pagination logic
------------------------------------------------------------------------------------------
IF (v_offset IS NULL OR v_limit IS NULL) THEN
v_query := v_query || ' OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ';
ELSE
v_query := v_query || ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || v_limit || ' ROWS ONLY ';
END IF;
-- V 1.1 Changes End
------------------------------------------------------------------------------------------
-- Select statement execution and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_clm_search_rec_type;
------------------------------------------------------------------------------------------
-- For loop to read values from variable and save into object
------------------------------------------------------------------------------------------
FOR v_search_dtl IN 1 .. v_clm_search_rec_type.COUNT
LOOP
v_claims_search_obj := JSON_OBJECT_T();
v_claims_search_obj.put('Claim_Number' , v_clm_search_rec_type (v_search_dtl).claim_number );
v_claims_search_obj.put('Claim_Fact_Key' , v_clm_search_rec_type (v_search_dtl).claim_fact_key );
v_claims_search_obj.put('Member_Id' , v_clm_search_rec_type (v_search_dtl).member_id );
v_claims_search_obj.put('Member_First_Name' , v_clm_search_rec_type (v_search_dtl).member_first_name );
v_claims_search_obj.put('Member_Last_Name' , v_clm_search_rec_type (v_search_dtl).member_last_name );
v_claims_search_obj.put('Member_Dob' , v_clm_search_rec_type (v_search_dtl).member_dob );
v_claims_search_obj.put('Patient_Ssn' , v_clm_search_rec_type (v_search_dtl).patient_ssn );
v_claims_search_obj.put('Check_Number' , v_clm_search_rec_type (v_search_dtl).check_number );
v_claims_search_obj.put('Medicaid_Id' , v_clm_search_rec_type (v_search_dtl).medicaid_id );
v_claims_search_obj.put('Patient_Name' , v_clm_search_rec_type (v_search_dtl).patient_name );
v_claims_search_obj.put('Claim_Status' , v_clm_search_rec_type (v_search_dtl).claim_status );
v_claims_search_obj.put('Paid_To' , v_clm_search_rec_type (v_search_dtl).paid_to );
v_claims_search_obj.put('Claim_Data_Updated' , v_clm_search_rec_type (v_search_dtl).claim_data_updated );
v_claims_search_obj.put('Date_Of_Service' , v_clm_search_rec_type (v_search_dtl).date_of_service );
v_claims_search_obj.put('Claim_Status_Php' , v_clm_search_rec_type (v_search_dtl).claim_status_php );
v_claims_search_obj.put('Total_Amount_Values' , v_clm_search_rec_type (v_search_dtl).total_amount_values );
v_claims_search_ary.append(v_claims_search_obj);
v_ind := 'Y';
END LOOP;
-- V 1.4 Changes Begin
ELSE
v_ind := 'N';
END IF;
-- V 1.4 Changes End
------------------------------------------------------------------------------------------
-- Save values into response object and assign into response variable
------------------------------------------------------------------------------------------
IF v_ind = 'Y' THEN
v_response_obj.put('Claims_Search_Details', v_claims_search_ary);
-- V 1.1 Changes Begin
v_response_obj.put('Total_Count', v_rec_count);
v_response_obj.put('Page_Number', v_page_num);
-- V 1.1 Changes End
v_response_obj.put('System_Code', '200');
v_response_obj.put('System_Message', 'Claim(s) found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
v_response_obj.put('Claims_Search_Details', v_claims_search_ary);
v_response_obj.put('System_Code', '204');
v_response_obj.put('System_Message', 'Claim(s) not found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
END IF;
ELSE
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'System could not find valid details .Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
END IF;
------------------------------------------------------------------------------------------
-- Exception Handling
------------------------------------------------------------------------------------------
EXCEPTION
WHEN v_program_error THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
WHEN OTHERS THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
COMMIT;
END provider_connect_claim_search_prc;
/*******************************************************************************************************************/
/********************************************************************************************************************
** NAME : provider_connect_claim_summary_get_prc **
** **
** VERSION : 1.2 **
** **
** DESCRIPTION : To get claim summary details for claims search **
** **
** PARAMETERS : **
** INPUT : JSONIN CLOB **
** OUTPUT : Response with Data base result-set **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 05/09/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 04/08/2024 | Cognizant | Initial Version **
* 1.1 | 05/08/2024 | Cognizant | Added source condition to remove duplicate patient information under**
* the same claim numbers **
* 1.2 | 05/09/2024 | Cognizant | Logic to include pended claims **
********************************************************************************************************************/
PROCEDURE provider_connect_claim_summary_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB)
AS
/***********************
{
"UserProfile":
{
},
"Filter":
{
},
"Offset":"",
"Limit":""
}
************************/
------------------------------------------------------------------------------------------
-- Initializing variables for json objects / arrays
------------------------------------------------------------------------------------------
v_jsonObj JSON_OBJECT_T := JSON_OBJECT_T.parse(v_jsonIn);
v_claims_sum_obj JSON_OBJECT_T;
v_claims_sum_ary JSON_ARRAY_T := JSON_ARRAY_T ();
v_response_obj JSON_OBJECT_T := JSON_OBJECT_T();
------------------------------------------------------------------------------------------
-- Initializing variables for input parameters
------------------------------------------------------------------------------------------
v_claim_number VARCHAR2(30) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Number'));
v_claim_fact_key VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Fact_Key'));
------------------------------------------------------------------------------------------
-- Initializing variables for user profile
------------------------------------------------------------------------------------------
v_user_id CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Id'));
v_user_name CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Name'));
v_user_role CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Role'));
v_user_tin CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Tin'));
--v_user_npi CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Npi'));
v_usage_page CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page'));
v_usage_page_clr_Code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page_Colour_Code'));
v_usage_section CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section'));
v_usage_sec_clr_code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section_Colour_Code'));
v_usage_action CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action'));
v_usage_action_criteria CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action_Criteria'));
v_usage_api CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Api'));
------------------------------------------------------------------------------------------
-- Local variables declaration
------------------------------------------------------------------------------------------
v_ind VARCHAR2(5) := 'N';
------------------------------------------------------------------------------------------
-- Type declaration for claim summary details
------------------------------------------------------------------------------------------
TYPE clm_sum_rec IS RECORD
(
claim_number CLOB,
member_id CLOB,
patient_name CLOB,
claim_status CLOB,
group_id CLOB,
group_name CLOB,
subscriber_name CLOB,
patient_account_number CLOB,
servicing_provider CLOB,
date_claim_was_received CLOB,
claim_data_updated CLOB,
date_of_service CLOB
);
TYPE clm_sum_rec_type IS TABLE OF clm_sum_rec INDEX BY PLS_INTEGER;
v_clm_sum_rec_type clm_sum_rec_type;
BEGIN
------------------------------------------------------------------------------------------
-- Insert usage details into usage table : provider_connect_usage_tbl
------------------------------------------------------------------------------------------
IF v_user_id IS NOT NULL THEN
v_return_code := provider_connect_usage_pkg.provider_connect_usage_internal_insert_prc(v_user_id,
v_user_name,
v_user_role,
v_user_tin,
--v_user_npi,
v_usage_page,
v_usage_page_clr_Code,
v_usage_section,
v_usage_sec_clr_code,
v_usage_action,
v_usage_action_criteria,
v_usage_api
);
END IF;
IF v_return_code <> 0 THEN
RAISE v_program_error;
END IF;
------------------------------------------------------------------------------------------
-- To check null input parameters
------------------------------------------------------------------------------------------
IF ( v_claim_number IS NULL OR v_claim_fact_key IS NULL ) THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'System could not find valid details .Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
------------------------------------------------------------------------------------------
-- Assigning select statement into variable
------------------------------------------------------------------------------------------
-- V 1.2 Changes Begin
v_query := 'SELECT *
FROM (
-- V 1.2 Changes End
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
clm.claim_status AS claim_status,
ees.grgr_id AS group_id,
INITCAP(ees.grgr_name) AS group_name,
INITCAP(ees.sbsb_last_name || '' '' || ees.sbsb_first_name
|| '' '' || ees.sbsb_mid_init) AS subscriber_name,
clm.patient_account_number AS patient_account_number,
(SELECT INITCAP(prv2.provider_name)
FROM claims_hub.hub_claims_to_provider prv2
WHERE prv2.claim_fact_key = clm.claim_fact_key
AND prv2.provider_type = ''RENDERING PROVIDER HEADER''
) AS servicing_provider,
TO_CHAR(clm.receipt_dt,''MM/DD/YYYY'') AS date_claim_was_received,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)) AS date_of_service
FROM claims_hub.hub_claims_medical clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
v_query := v_query || ' WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key || ''' ';
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) '; -- V 1.1 Changes
-- V 1.2 Changes Begin
--v_query := v_query || ' ORDER BY clm.claim_hcc_id ';
v_query := v_query || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
clm.claim_status AS claim_status,
ees.grgr_id AS group_id,
INITCAP(ees.grgr_name) AS group_name,
INITCAP(ees.sbsb_last_name || '' '' || ees.sbsb_first_name
|| '' '' || ees.sbsb_mid_init) AS subscriber_name,
clm.patient_account_number AS patient_account_number,
(SELECT INITCAP(prv2.rendering_last_name
|| '' '' || prv2.rendering_first_name
|| '' '' || prv2. rendering_mid_name)
FROM claims_hub.hub_claims_to_practitioner_othsts prv2
WHERE prv2.claim_fact_key = clm.claim_fact_key
) AS servicing_provider,
TO_CHAR(clm.receipt_dt,''MM/DD/YYYY'') AS date_claim_was_received,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical_othsts clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)) AS date_of_service
FROM claims_hub.hub_claims_medical_othsts clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
v_query := v_query || ' WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key || ''' ';
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) ';
--v_query := v_query || ' AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ';
v_query := v_query || ' )ORDER BY claim_number ';
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Select statement execution and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_clm_sum_rec_type;
------------------------------------------------------------------------------------------
-- For loop to read values from variable and save into object
------------------------------------------------------------------------------------------
FOR v_clm_dtl IN 1 .. v_clm_sum_rec_type.COUNT
LOOP
v_claims_sum_obj := JSON_OBJECT_T();
v_claims_sum_obj.put('Claim_Number' , v_clm_sum_rec_type (v_clm_dtl).claim_number );
v_claims_sum_obj.put('Member_Id' , v_clm_sum_rec_type (v_clm_dtl).member_id );
v_claims_sum_obj.put('Patient_Name' , v_clm_sum_rec_type (v_clm_dtl).patient_name );
v_claims_sum_obj.put('Claim_Status' , v_clm_sum_rec_type (v_clm_dtl).claim_status );
v_claims_sum_obj.put('Group_Id' , v_clm_sum_rec_type (v_clm_dtl).group_id );
v_claims_sum_obj.put('Group_Name' , v_clm_sum_rec_type (v_clm_dtl).group_name );
v_claims_sum_obj.put('Subscriber_Name' , v_clm_sum_rec_type (v_clm_dtl).subscriber_name );
v_claims_sum_obj.put('Patient_Account_Number' , v_clm_sum_rec_type (v_clm_dtl).patient_account_number );
v_claims_sum_obj.put('Servicing_Provider' , v_clm_sum_rec_type (v_clm_dtl).servicing_provider );
v_claims_sum_obj.put('Date_Claim_Was_Received', v_clm_sum_rec_type (v_clm_dtl).date_claim_was_received);
v_claims_sum_obj.put('Claim_Data_Updated' , v_clm_sum_rec_type (v_clm_dtl).claim_data_updated );
v_claims_sum_obj.put('Date_Of_Service' , v_clm_sum_rec_type (v_clm_dtl).date_of_service );
v_claims_sum_ary.append(v_claims_sum_obj);
v_ind := 'Y';
END LOOP;
------------------------------------------------------------------------------------------
-- Save values into response object and assign into response variable
------------------------------------------------------------------------------------------
IF v_ind = 'Y' THEN
v_response_obj.put('Claims_Summary_Details', v_claims_sum_ary);
v_response_obj.put('System_Code', '200');
v_response_obj.put('System_Message', 'Claim summary details found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
v_response_obj.put('Summary', v_claims_sum_ary);
v_response_obj.put('System_Code', '204');
v_response_obj.put('System_Message', 'Claim summary details not found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
END IF;
END IF;
------------------------------------------------------------------------------------------
-- Exception Handling
------------------------------------------------------------------------------------------
EXCEPTION
WHEN v_program_error THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
WHEN OTHERS THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
COMMIT;
END provider_connect_claim_summary_get_prc;
/*******************************************************************************************************************/
/********************************************************************************************************************
** NAME : provider_connect_payment_details_get_prc **
** **
** VERSION : 1.1 **
** **
** DESCRIPTION : To get claim payment details for claims search **
** **
** PARAMETERS : **
** INPUT : JSONIN CLOB **
** OUTPUT : Response with Data base result-set **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 05/07/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 04/08/2024 | Cognizant | Initial Version **
* 1.1 | 05/07/2024 | Cognizant | Logic to include pended claims **
********************************************************************************************************************/
PROCEDURE provider_connect_payment_details_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB)
AS
/***********************
{
"UserProfile":
{
},
"Filter":
{
},
"Offset":"",
"Limit":""
}
************************/
------------------------------------------------------------------------------------------
-- Initializing variables for json objects / arrays
------------------------------------------------------------------------------------------
v_jsonObj JSON_OBJECT_T := JSON_OBJECT_T.parse(v_jsonIn);
v_payment_dtl_obj JSON_OBJECT_T;
v_payment_dtl_ary JSON_ARRAY_T := JSON_ARRAY_T ();
v_response_obj JSON_OBJECT_T := JSON_OBJECT_T();
------------------------------------------------------------------------------------------
-- Initializing variables for input parameters
------------------------------------------------------------------------------------------
v_claim_number VARCHAR2(30) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Number'));
v_claim_fact_key VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Fact_Key'));
------------------------------------------------------------------------------------------
-- Initializing variables for user profile
------------------------------------------------------------------------------------------
v_user_id CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Id'));
v_user_name CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Name'));
v_user_role CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Role'));
v_user_tin CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Tin'));
--v_user_npi CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Npi'));
v_usage_page CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page'));
v_usage_page_clr_Code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page_Colour_Code'));
v_usage_section CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section'));
v_usage_sec_clr_code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section_Colour_Code'));
v_usage_action CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action'));
v_usage_action_criteria CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action_Criteria'));
v_usage_api CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Api'));
------------------------------------------------------------------------------------------
-- Local variables declaration
------------------------------------------------------------------------------------------
v_ind VARCHAR2(5) := 'N';
------------------------------------------------------------------------------------------
-- Type declaration for payment details
------------------------------------------------------------------------------------------
TYPE payment_dtl_rec IS RECORD
(
claim_number CLOB,
check_number CLOB,
date_claim_was_paid_on CLOB,
claims_payment_made_to CLOB,
total_amount_values CLOB,
total_check_amount CLOB,
payment_status_desc CLOB
);
TYPE payment_dtl_rec_type IS TABLE OF payment_dtl_rec INDEX BY PLS_INTEGER;
v_payment_dtl_rec_type payment_dtl_rec_type;
BEGIN
------------------------------------------------------------------------------------------
-- Insert usage details into usage table : provider_connect_usage_tbl
------------------------------------------------------------------------------------------
IF v_user_id IS NOT NULL THEN
v_return_code := provider_connect_usage_pkg.provider_connect_usage_internal_insert_prc(v_user_id,
v_user_name,
v_user_role,
v_user_tin,
--v_user_npi,
v_usage_page,
v_usage_page_clr_Code,
v_usage_section,
v_usage_sec_clr_code,
v_usage_action,
v_usage_action_criteria,
v_usage_api
);
END IF;
IF v_return_code <> 0 THEN
RAISE v_program_error;
END IF;
------------------------------------------------------------------------------------------
-- To check null input parameters
------------------------------------------------------------------------------------------
IF ( v_claim_number IS NULL OR v_claim_fact_key IS NULL ) THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'System could not find valid details .Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
------------------------------------------------------------------------------------------
-- Assigning select statement into variable
------------------------------------------------------------------------------------------
-- V 1.1 Changes Begin
v_query := 'SELECT *
FROM(
-- V 1.1 Changes End
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.check_number AS check_number,
TO_CHAR(clm.paid_dt,''MM/DD/YYYY'') AS date_claim_was_paid_on,
(SELECT INITCAP(prv1.provider_name)
FROM claims_hub.hub_claims_to_provider prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
AND prv1.provider_type = ''SUPPLIER''
) AS claims_payment_made_to,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charge'' VALUE SUM(clm2.billed_amount),
KEY ''Paid_Amount'' VALUE SUM(clm2.paid_amount)
)
FROM claims_hub.hub_claims_medical clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND (clm2.billed_amount IS NOT NULL OR clm2.paid_amount IS NOT NULL)
AND clm2.claim_line_status_code <> ''r''
) AS total_amount_values,
CASE WHEN clm.total_check_amount IS NOT NULL THEN
''$'' || clm.total_check_amount
END AS total_check_amount,
clm.payment_status_desc AS payment_status_desc
FROM claims_hub.hub_claims_medical clm ';
v_query := v_query || ' WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key || ''' ';
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
-- V 1.1 Changes Begin
--v_query := v_query || ' ORDER BY clm.claim_hcc_id ';
v_query := v_query || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
TO_NUMBER('''') AS check_number,
TO_CHAR(clm.paid_dt,''MM/DD/YYYY'') AS date_claim_was_paid_on,
(SELECT INITCAP(prv1.supplier_name)
FROM claims_hub.hub_claims_to_supplier_othsts prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
) AS claims_payment_made_to,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charge'' VALUE SUM(clm2.billed_amount),
KEY ''Paid_Amount'' VALUE SUM(clm2.paid_amount)
)
FROM claims_hub.hub_claims_medical_othsts clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND (clm2.billed_amount IS NOT NULL OR clm2.paid_amount IS NOT NULL)
AND clm2.claim_line_status_code <> ''r''
) AS total_amount_values,
'''' AS total_check_amount,
'''' AS payment_status_desc
FROM claims_hub.hub_claims_medical_othsts clm ';
v_query := v_query || ' WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key || ''' ';
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
--v_query := v_query || ' AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ';
v_query := v_query || ' )ORDER BY claim_number ';
-- V 1.1 Changes End
------------------------------------------------------------------------------------------
-- Select statement execution and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_payment_dtl_rec_type;
------------------------------------------------------------------------------------------
-- For loop to read values from variable and save into object
------------------------------------------------------------------------------------------
FOR v_payment_dtl IN 1 .. v_payment_dtl_rec_type.COUNT
LOOP
v_payment_dtl_obj := JSON_OBJECT_T();
v_payment_dtl_obj.put('Claim_Number' , v_payment_dtl_rec_type (v_payment_dtl).claim_number );
v_payment_dtl_obj.put('Check_Number' , v_payment_dtl_rec_type (v_payment_dtl).check_number );
v_payment_dtl_obj.put('Date_Claim_Was_Paid_On' , v_payment_dtl_rec_type (v_payment_dtl).date_claim_was_paid_on );
v_payment_dtl_obj.put('Claims_Payment_Made_To' , v_payment_dtl_rec_type (v_payment_dtl).claims_payment_made_to );
v_payment_dtl_obj.put('Total_Amount_Values' , v_payment_dtl_rec_type (v_payment_dtl).total_amount_values );
v_payment_dtl_obj.put('Total_Check_Amount' , v_payment_dtl_rec_type (v_payment_dtl).total_check_amount );
v_payment_dtl_obj.put('Payment_Status_Desc' , v_payment_dtl_rec_type (v_payment_dtl).payment_status_desc );
v_payment_dtl_ary.append(v_payment_dtl_obj);
v_ind := 'Y';
END LOOP;
------------------------------------------------------------------------------------------
-- Save values into response object and assign into response variable
------------------------------------------------------------------------------------------
IF v_ind = 'Y' THEN
v_response_obj.put('Payment_Details', v_payment_dtl_ary);
v_response_obj.put('System_Code', '200');
v_response_obj.put('System_Message', 'Claim Payment details found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
v_response_obj.put('Payment_Details', v_payment_dtl_ary);
v_response_obj.put('System_Code', '204');
v_response_obj.put('System_Message', 'Claim Payment details not found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
END IF;
END IF;
------------------------------------------------------------------------------------------
-- Exception Handling
------------------------------------------------------------------------------------------
EXCEPTION
WHEN v_program_error THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
WHEN OTHERS THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
COMMIT;
END provider_connect_payment_details_get_prc;
/*******************************************************************************************************************/
/********************************************************************************************************************
** NAME : provider_connect_claim_line_details_get_prc **
** **
** VERSION : 1.2 **
** **
** DESCRIPTION : To get claim line details for claims search details for claims summary UI screen **
** **
** PARAMETERS : **
** INPUT : JSONIN CLOB **
** OUTPUT : Response with Data base result-set **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 05/10/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 04/08/2024 | Cognizant | Initial Version **
* 1.1 | 05/07/2024 | Cognizant | Logic to include pended claims **
* 1.2 | 05/10/2024 | Cognizant | Pagination changes for claim lines **
********************************************************************************************************************/
PROCEDURE provider_connect_claim_line_details_get_prc (v_jsonIn IN CLOB,v_response OUT CLOB)
AS
/***********************
{
"UserProfile":
{
},
"Filter":
{
},
"Offset":"",
"Limit":""
}
************************/
------------------------------------------------------------------------------------------
-- Initializing variables for json objects / arrays
------------------------------------------------------------------------------------------
v_jsonObj JSON_OBJECT_T := JSON_OBJECT_T.parse(v_jsonIn);
v_claim_line_obj JSON_OBJECT_T;
v_claim_line_ary JSON_ARRAY_T := JSON_ARRAY_T ();
v_response_obj JSON_OBJECT_T := JSON_OBJECT_T();
------------------------------------------------------------------------------------------
-- Initializing variables for input parameters
------------------------------------------------------------------------------------------
v_claim_number VARCHAR2(30) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Number'));
v_claim_fact_key VARCHAR2(50) := TRIM(v_jsonObj.get_Object('Filter').get_String('Claim_Fact_Key'));
-- V 1.2 Changes Begin
v_rows_perpage CLOB := TRIM(v_jsonObj.get_Object('Filter').get_String('Rows_Per_Page'));
v_page_num CLOB := TRIM(v_jsonObj.get_Object('Filter').get_String('Page_Number'));
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Initializing variables for user profile
------------------------------------------------------------------------------------------
v_user_id CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Id'));
v_user_name CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Name'));
v_user_role CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Role'));
v_user_tin CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Tin'));
--v_user_npi CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('User_Npi'));
v_usage_page CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page'));
v_usage_page_clr_Code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Page_Colour_Code'));
v_usage_section CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section'));
v_usage_sec_clr_code CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Section_Colour_Code'));
v_usage_action CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action'));
v_usage_action_criteria CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Action_Criteria'));
v_usage_api CLOB := TRIM(v_jsonObj.get_Object('UserProfile').get_String('Usage_Api'));
------------------------------------------------------------------------------------------
-- Local variables declaration
------------------------------------------------------------------------------------------
v_ind VARCHAR2(5) := 'N';
-- V 1.2 Changes Begin
v_query1 CLOB;
v_limit NUMBER := TO_NUMBER(v_rows_perpage);
v_offset NUMBER := v_limit * (TO_NUMBER(v_page_num)-1);
v_rec_count NUMBER;
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Type declaration for claim line details
------------------------------------------------------------------------------------------
TYPE claim_line_rec IS RECORD
(
claim_number CLOB,
claim_fact_key CLOB,
claim_line_fact_key CLOB,
item_number CLOB,
procedure_code CLOB,
diagnosis_code CLOB,
claim_code CLOB,
units CLOB,
charges CLOB,
allowed CLOB,
deductible CLOB,
coinsurance CLOB,
plan_paid CLOB,
network CLOB,
cap_no_cap CLOB,
total CLOB,
rarc_carc CLOB,
copay CLOB
);
TYPE claim_line_rec_type IS TABLE OF claim_line_rec INDEX BY PLS_INTEGER;
v_claim_line_rec_type claim_line_rec_type;
BEGIN
------------------------------------------------------------------------------------------
-- Insert usage details into usage table : provider_connect_usage_tbl
------------------------------------------------------------------------------------------
IF v_user_id IS NOT NULL THEN
v_return_code := provider_connect_usage_pkg.provider_connect_usage_internal_insert_prc(v_user_id,
v_user_name,
v_user_role,
v_user_tin,
--v_user_npi,
v_usage_page,
v_usage_page_clr_Code,
v_usage_section,
v_usage_sec_clr_code,
v_usage_action,
v_usage_action_criteria,
v_usage_api
);
END IF;
IF v_return_code <> 0 THEN
RAISE v_program_error;
END IF;
------------------------------------------------------------------------------------------
-- To check null input parameters
------------------------------------------------------------------------------------------
IF ( v_claim_number IS NULL OR v_claim_fact_key IS NULL ) THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', 'System could not find valid details .Please try again.');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
-- V 1.2 Changes Begin
------------------------------------------------------------------------------------------
-- Assigning select statement into variable to fetch count of total records
------------------------------------------------------------------------------------------
v_query1 := 'SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
clm.claim_line_fact_key AS claim_line_fact_key,
TO_NUMBER(clm.original_line_number) AS original_line_number
FROM claims_hub.hub_claims_medical clm ';
v_query1 := v_query1 || 'WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key ||''' ';
v_query1 := v_query1 || 'AND clm.claim_status_php <> ''Reversed'' ';
v_query1 := v_query1 || 'AND clm.claim_line_status_code <> ''r'' ';
v_query1 := v_query1 || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
clm.claim_line_fact_key AS claim_line_fact_key,
TO_NUMBER(clm.original_line_number) AS orig_line_number
FROM claims_hub.hub_claims_medical_othsts clm ';
v_query1 := v_query1 || 'WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key ||''' ';
v_query1 := v_query1 || 'AND clm.claim_status_php <> ''Reversed'' ';
v_query1 := v_query1 || 'AND clm.claim_line_status_code <> ''r'' ';
--v_query1 := v_query1 || 'AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ';
------------------------------------------------------------------------------------------
-- Select statement execution for total number of records and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ( ' || v_query1 || ' )' INTO v_rec_count;
IF v_rec_count > 0 THEN
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Assigning select statement into variable to fetch paginated records
------------------------------------------------------------------------------------------
-- V 1.1 Changes Begin
v_query := 'SELECT *
FROM (
-- V 1.1 Changes End
SELECT Claim_Number AS claim_number,
Claim_fact_key AS claim_fact_key,
Claim_line_fact_key AS claim_line_fact_key,
ROW_NUMBER() OVER (PARTITION BY claim_fact_key
ORDER BY TO_NUMBER(original_line_number),
TO_NUMBER(claim_line_fact_key) ASC) AS item_number,
Procedure_Code AS procedure_code,
Diagnosis_Code AS diagnosis_code,
Claim_Code AS claim_code,
Units AS units,
Charges AS charges,
Allowed AS allowed,
Deductible AS deductible,
Coinsurance AS coinsurance,
Plan_Paid AS plan_paid,
Network AS network,
Cap_No_Cap AS cap_no_cap,
Total AS total,
Rarc_Carc AS rarc_carc,
Copay AS copay
FROM (
SELECT DISTINCT
clm.claim_hcc_id AS Claim_Number,
clm.claim_fact_key AS Claim_fact_key,
clm.claim_line_fact_key AS Claim_line_fact_key,
clm.claim_line_hcc_id AS Item_Number,
clm.original_line_number AS Original_line_number,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Procedure_Code'' VALUE TRIM(clm.service_code),
KEY ''Description_of_Code'' VALUE INITCAP(TRIM(clm.service_short_desc))
)
)
FROM claims_hub.hub_claims_medical clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_line_fact_key = clm.claim_line_fact_key
) AS Procedure_Code,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Diagnosis_Code'' VALUE REPLACE(TRIM(diag.dx_1),''.''),
KEY ''Description_of_Code'' VALUE NVL(INITCAP(TRIM(diag.dx_long_desc_1)) ,'''')
)
)
FROM claims_hub.hub_claim_line_to_diagnosis diag
WHERE diag.claim_fact_key = clm.claim_fact_key
AND diag.claim_line_fact_key = clm.claim_line_fact_key
) AS Diagnosis_Code,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Claim_Code'' VALUE code,
KEY ''Description_of_Code'' VALUE msg_desc
)
)
FROM (SELECT DISTINCT
TRIM(msgc.code_1) AS code,
TRIM(msgc.desc_1) AS msg_desc
FROM claims_hub.hub_claims_to_msg_code msgc
WHERE msgc.claim_fact_key = clm.claim_fact_key
AND msgc.claim_line_fact_key = clm.claim_line_fact_key
)
) AS Claim_Code,
clm.unit_count AS Units,
CASE WHEN clm.billed_amount IS NOT NULL
THEN ''$'' || clm.billed_amount
END AS Charges,
CASE WHEN clm.base_allowed_amount IS NOT NULL
THEN ''$'' || clm.base_allowed_amount
END AS Allowed,
CASE WHEN clm.base_deductible_amount IS NOT NULL
THEN ''$'' || clm.base_deductible_amount
END AS Deductible,
CASE WHEN clm.base_coinsurance_amount IS NOT NULL
THEN clm.base_coinsurance_amount
END AS Coinsurance,
CASE WHEN clm.base_paid_amount IS NOT NULL
THEN ''$'' || clm.base_paid_amount
END AS Plan_Paid,
clm.benefit_tier_type AS Network,
clm.is_capitated AS Cap_No_Cap,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charges'' VALUE TO_CHAR(SUM(clm1.billed_amount)),
KEY ''Total_Allowed'' VALUE TO_CHAR(SUM(clm1.base_allowed_amount)),
KEY ''Total_Deductible'' VALUE TO_CHAR(SUM(clm1.base_deductible_amount)),
KEY ''Total_Coinsurance'' VALUE TO_CHAR(SUM(clm1.base_coinsurance_amount)),
KEY ''Total_Plan_Paid'' VALUE TO_CHAR(SUM(clm1.base_paid_amount)),
KEY ''Total_Units'' VALUE TO_CHAR(SUM(clm1.unit_count)),
KEY ''Total_Copay'' VALUE TO_CHAR(SUM(clm1.base_copay_amount))
)
FROM claims_hub.hub_claims_medical clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
AND (clm1.billed_amount IS NOT NULL OR
clm1.base_allowed_amount IS NOT NULL OR
clm1.base_deductible_amount IS NOT NULL OR
clm1.base_coinsurance_amount IS NOT NULL OR
clm1.base_paid_amount IS NOT NULL OR
clm1.unit_count IS NOT NULL OR
clm1.base_copay_amount IS NOT NULL
)
) AS Total,
clm.claim_adjustment_reason_code AS Rarc_Carc,
CASE WHEN clm.base_copay_amount IS NOT NULL
THEN ''$'' || clm.base_copay_amount
END AS Copay
FROM claims_hub.hub_claims_medical clm ';
v_query := v_query || 'WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key ||''' ';
v_query := v_query || 'AND clm.claim_status_php <> ''Reversed'' ';
v_query := v_query || 'AND clm.claim_line_status_code <> ''r'' ) ';
-- V 1.1 Changes Begin
--v_query := v_query || ' ORDER BY Item_Number ) ';
v_query := v_query || 'UNION
SELECT Claim_Number AS claim_number,
Claim_fact_key AS claim_fact_key,
Claim_line_fact_key AS claim_line_fact_key,
ROW_NUMBER() OVER (PARTITION BY claim_fact_key
ORDER BY TO_NUMBER(original_line_number),
TO_NUMBER(claim_line_fact_key) ASC) AS item_number,
Procedure_Code AS procedure_code,
Diagnosis_Code AS diagnosis_code,
Claim_Code AS claim_code,
Units AS units,
Charges AS charges,
Allowed AS allowed,
Deductible AS deductible,
Coinsurance AS coinsurance,
Plan_Paid AS plan_paid,
Network AS network,
Cap_No_Cap AS cap_no_cap,
Total AS total,
Rarc_Carc AS rarc_carc,
Copay AS copay
FROM (
SELECT DISTINCT
clm.claim_hcc_id AS Claim_Number,
clm.claim_fact_key AS Claim_fact_key,
clm.claim_line_fact_key AS Claim_line_fact_key,
clm.claim_line_hcc_id AS Item_Number,
clm.original_line_number AS Original_line_number,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Procedure_Code'' VALUE TRIM(clm.service_code),
KEY ''Description_of_Code'' VALUE INITCAP(TRIM(clm.service_short_desc))
)
)
FROM claims_hub.hub_claims_medical_othsts clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_line_fact_key = clm.claim_line_fact_key
) AS Procedure_Code,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Diagnosis_Code'' VALUE REPLACE(TRIM(diag.dx_1),''.''),
KEY ''Description_of_Code'' VALUE NVL(INITCAP(TRIM(diag.dx_long_desc_1)) ,'''')
)
)
FROM claims_hub.hub_claim_line_to_diagnosis_othsts diag
WHERE diag.claim_fact_key = clm.claim_fact_key
AND diag.claim_line_fact_key = clm.claim_line_fact_key
) AS Diagnosis_Code,
(SELECT JSON_ARRAYAGG
(
JSON_OBJECT (KEY ''Claim_Code'' VALUE code,
KEY ''Description_of_Code'' VALUE msg_desc
)
)
FROM (SELECT DISTINCT
TRIM(msgc.code_1) AS code,
TRIM(msgc.desc_1) AS msg_desc
FROM claims_hub.hub_claims_to_msg_info_othsts msgc
WHERE msgc.claim_fact_key = clm.claim_fact_key
AND msgc.claim_line_fact_key = clm.claim_line_fact_key
)
) AS Claim_Code,
clm.unit_count AS Units,
CASE WHEN clm.billed_amount IS NOT NULL
THEN ''$'' || clm.billed_amount
END AS Charges,
CASE WHEN clm.base_allowed_amount IS NOT NULL
THEN ''$'' || clm.base_allowed_amount
END AS Allowed,
CASE WHEN clm.base_deductible_amount IS NOT NULL
THEN ''$'' || clm.base_deductible_amount
END AS Deductible,
CASE WHEN clm.base_coinsurance_amount IS NOT NULL
THEN clm.base_coinsurance_amount
END AS Coinsurance,
CASE WHEN clm.base_paid_amount IS NOT NULL
THEN ''$'' || clm.base_paid_amount
END AS Plan_Paid,
clm.benefit_tier_type AS Network,
clm.is_capitated AS Cap_No_Cap,
(SELECT DISTINCT JSON_OBJECT (
KEY ''Total_Charges'' VALUE TO_CHAR(SUM(clm1.billed_amount)),
KEY ''Total_Allowed'' VALUE TO_CHAR(SUM(clm1.base_allowed_amount)),
KEY ''Total_Deductible'' VALUE TO_CHAR(SUM(clm1.base_deductible_amount)),
KEY ''Total_Coinsurance'' VALUE TO_CHAR(SUM(clm1.base_coinsurance_amount)),
KEY ''Total_Plan_Paid'' VALUE TO_CHAR(SUM(clm1.base_paid_amount)),
KEY ''Total_Units'' VALUE TO_CHAR(SUM(clm1.unit_count)),
KEY ''Total_Copay'' VALUE TO_CHAR(SUM(clm1.base_copay_amount))
)
FROM claims_hub.hub_claims_medical_othsts clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
AND (clm1.billed_amount IS NOT NULL OR
clm1.base_allowed_amount IS NOT NULL OR
clm1.base_deductible_amount IS NOT NULL OR
clm1.base_coinsurance_amount IS NOT NULL OR
clm1.base_paid_amount IS NOT NULL OR
clm1.unit_count IS NOT NULL OR
clm1.base_copay_amount IS NOT NULL
)
) AS Total,
'''' AS Rarc_Carc,
CASE WHEN clm.base_copay_amount IS NOT NULL
THEN ''$'' || clm.base_copay_amount
END AS Copay
FROM claims_hub.hub_claims_medical_othsts clm ';
v_query := v_query || 'WHERE clm.claim_hcc_id = ''' || v_claim_number || ''' AND clm.claim_fact_key = ''' || v_claim_fact_key ||''' ';
v_query := v_query || 'AND clm.claim_status_php <> ''Reversed'' ';
v_query := v_query || 'AND clm.claim_line_status_code <> ''r'' )';
--v_query := v_query || 'AND clm.claim_status IN (''Needs Repair'',''Rejected'',''Needs Review'',''Needs Repricing'') ) ';
v_query := v_query || ')ORDER BY Item_Number ';
-- V 1.1 Changes End
-- V 1.2 Changes Begin
------------------------------------------------------------------------------------------
-- Pagination logic
------------------------------------------------------------------------------------------
IF (v_offset IS NULL OR v_limit IS NULL) THEN
v_query := v_query || ' OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ';
ELSE
v_query := v_query || ' OFFSET ' || v_offset || ' ROWS FETCH NEXT ' || v_limit || ' ROWS ONLY ';
END IF;
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Select statement execution and assign values into variable
------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE v_query BULK COLLECT INTO v_claim_line_rec_type;
------------------------------------------------------------------------------------------
-- For loop to read values from variable and save into object
------------------------------------------------------------------------------------------
FOR v_claim_line_dtl IN 1 .. v_claim_line_rec_type.COUNT
LOOP
v_claim_line_obj:= JSON_OBJECT_T();
v_claim_line_obj.put('Claim_Number' , v_claim_line_rec_type (v_claim_line_dtl).claim_number );
v_claim_line_obj.put('Claim_Fact_Key' , v_claim_line_rec_type (v_claim_line_dtl).claim_fact_key );
v_claim_line_obj.put('Claim_Line_Fact_Key', v_claim_line_rec_type (v_claim_line_dtl).claim_line_fact_key);
v_claim_line_obj.put('Item_Number' , v_claim_line_rec_type (v_claim_line_dtl).item_number );
v_claim_line_obj.put('Procedure_Code' , v_claim_line_rec_type (v_claim_line_dtl).procedure_code );
v_claim_line_obj.put('Diagnosis_Code' , v_claim_line_rec_type (v_claim_line_dtl).diagnosis_code );
v_claim_line_obj.put('Claim_Code' , v_claim_line_rec_type (v_claim_line_dtl).claim_code );
v_claim_line_obj.put('Units' , v_claim_line_rec_type (v_claim_line_dtl).units );
v_claim_line_obj.put('Charges' , v_claim_line_rec_type (v_claim_line_dtl).charges );
v_claim_line_obj.put('Allowed' , v_claim_line_rec_type (v_claim_line_dtl).allowed );
v_claim_line_obj.put('Deductible' , v_claim_line_rec_type (v_claim_line_dtl).deductible );
v_claim_line_obj.put('Coinsurance' , v_claim_line_rec_type (v_claim_line_dtl).coinsurance );
v_claim_line_obj.put('Plan_Paid' , v_claim_line_rec_type (v_claim_line_dtl).plan_paid );
v_claim_line_obj.put('Network' , v_claim_line_rec_type (v_claim_line_dtl).network );
v_claim_line_obj.put('Cap_No_Cap' , v_claim_line_rec_type (v_claim_line_dtl).cap_no_cap );
v_claim_line_obj.put('Total' , v_claim_line_rec_type (v_claim_line_dtl).total );
v_claim_line_obj.put('Rarc_Carc' , v_claim_line_rec_type (v_claim_line_dtl).rarc_carc );
v_claim_line_obj.put('Copay' , v_claim_line_rec_type (v_claim_line_dtl).copay );
v_claim_line_ary.append(v_claim_line_obj);
v_ind := 'Y';
END LOOP;
-- V 1.2 Changes Begin
ELSE
v_ind := 'N';
END IF;
-- V 1.2 Changes End
------------------------------------------------------------------------------------------
-- Save values into response object and assign into response variable
------------------------------------------------------------------------------------------
IF v_ind = 'Y' THEN
v_response_obj.put('Claim_Line_Details', v_claim_line_ary);
-- V 1.2 Changes Begin
v_response_obj.put('Total_Count', v_rec_count);
v_response_obj.put('Page_Number', v_page_num);
-- V 1.2 Changes End
v_response_obj.put('System_Code', '200');
v_response_obj.put('System_Message', 'Claim line details found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
ELSE
v_response_obj.put('Claim_Line_Details', v_claim_line_ary);
v_response_obj.put('System_Code', '204');
v_response_obj.put('System_Message', 'Claim line details not found');
v_response_obj.put('Status', 'Success');
v_response := v_response_obj.to_clob;
END IF;
END IF;
------------------------------------------------------------------------------------------
-- Exception Handling
------------------------------------------------------------------------------------------
EXCEPTION
WHEN v_program_error THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
WHEN OTHERS THEN
v_response_obj.put('System_Code', '400');
v_response_obj.put('System_Message', SQLCODE ||' : '|| SQLERRM);
v_response_obj.put('Status', 'Fail');
v_response := v_response_obj.to_String;
COMMIT;
END provider_connect_claim_line_details_get_prc;
/*******************************************************************************************************************/
/********************************************************************************************************************
** NAME : PROVIDER_CONNECT_CLAIM_DOWNLOAD_PRC **
** **
** VERSION : 1.0 **
** **
** DESCRIPTION : To download report for claim details **
** **
** PARAMETERS : **
** INPUT : Claims, member details, Begin date and end date **
** OUTPUT : Response with XML data base result-set **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 05/20/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 05/20/2024 | Cognizant | Initial Version **
********************************************************************************************************************/
PROCEDURE provider_connect_claim_download_prc(
v_clm_number IN CLOB,
v_begin_date IN VARCHAR2,
v_end_date IN VARCHAR2,
v_mem_member_id IN VARCHAR2,
v_mem_first_name IN VARCHAR2,
v_mem_last_name IN VARCHAR2,
v_mem_dob IN VARCHAR2,
v_patient_ssn IN VARCHAR2,
v_mem_medicaid_id IN VARCHAR2,
v_check_number IN VARCHAR2,
v_prov_tin IN VARCHAR2,
v_prov_npi IN VARCHAR2,
v_xmlout OUT CLOB
)
AS
v_xmltype XMLTYPE;
v_xml_dom_out dbms_xmldom.domdocument;
v_root_node dbms_xmldom.domnode;
v_pc_claim_element dbms_xmldom.domelement;
v_pc_claim_node dbms_xmldom.domnode;
v_download_element dbms_xmldom.domelement;
v_download_node dbms_xmldom.domnode;
v_download_text dbms_xmldom.domtext;
v_download_textnode dbms_xmldom.domnode;
v_header_element dbms_xmldom.domelement;
v_header_node dbms_xmldom.domnode;
v_header_text dbms_xmldom.domtext;
v_header_textnode dbms_xmldom.domnode;
v_pc_success_element dbms_xmldom.domelement;
v_pc_success_node dbms_xmldom.domnode;
v_pc_success_text dbms_xmldom.domtext;
v_pc_success_textnode dbms_xmldom.domnode;
v_claim_download_rec_type claim_download_rec_table:= claim_download_rec_table();
v_download_result CLOB;
v_query CLOB;
v_header_query CLOB;
v_base_query CLOB;
v_have_data BOOLEAN := FALSE;
------------------------------------------------------------------------------------------
-- Local variables declaration
------------------------------------------------------------------------------------------
v_get_claim_nos CLOB;
v_claim_number CLOB := v_clm_number;
v_days NUMBER;
v_date VARCHAR2(10);
BEGIN
v_xml_dom_out := xmldom.newdomdocument;
v_root_node := xmldom.makenode(v_xml_dom_out);
v_pc_claim_element := xmldom.createelement(v_xml_dom_out, 'PC_Claims_Download');
v_pc_claim_node := xmldom.appendchild(v_root_node, xmldom.makenode(v_pc_claim_element));
v_base_query := 'SELECT claim_download_rec(csv_row) FROM ( ';
v_header_query := 'claim_number,claim_fact_key,member_id,patient_name,member_dob,patient_ssn,medicaid_id,group_id,group_name,subscriber_name,patient_account_number,date_of_service,claim_status,claim_status_php,total_charge,paid_to,paid_amount,date_claim_was_received,date_claim_was_paid_on,total_check_amount,payment_status_desc,check_number,claim_data_updated';
------------------------------------------------------------------------------------------
-- To get number of days
------------------------------------------------------------------------------------------
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) THEN
SELECT TO_DATE(v_end_date, 'YYYYMMDD') - TO_DATE(v_begin_date, 'YYYYMMDD') INTO v_days FROM DUAL;
END IF;
------------------------------------------------------------------------------------------
-- To check null input parameters
------------------------------------------------------------------------------------------
IF ( v_claim_number IS NULL AND
v_begin_date IS NULL AND
v_end_date IS NULL AND
v_mem_member_id IS NULL AND
v_mem_first_name IS NULL AND
v_mem_last_name IS NULL AND
v_mem_dob IS NULL AND
v_patient_ssn IS NULL AND
v_mem_medicaid_id IS NULL AND
v_check_number IS NULL AND
v_prov_npi IS NULL
) THEN
v_pc_success_element := dbms_xmldom.createelement(v_xml_dom_out, 'status');
v_pc_success_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_pc_success_element));
v_pc_success_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'success');
v_pc_success_textnode := dbms_xmldom.appendchild(v_pc_success_node, dbms_xmldom.makenode(v_pc_success_text));
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'System could not find valid details .Please try again.');
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
ELSIF ((v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date > v_end_date)) OR (v_days > 93) THEN
v_pc_success_element := dbms_xmldom.createelement(v_xml_dom_out, 'status');
v_pc_success_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_pc_success_element));
v_pc_success_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'success');
v_pc_success_textnode := dbms_xmldom.appendchild(v_pc_success_node, dbms_xmldom.makenode(v_pc_success_text));
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'You can select maximum 3 months of data and End date should be greater than begin date.Please try again.');
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
ELSIF (v_claim_number IS NOT NULL OR
v_check_number IS NOT NULL) OR
((v_mem_member_id IS NOT NULL OR
v_patient_ssn IS NOT NULL OR
v_mem_medicaid_id IS NOT NULL) AND
(v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL)) OR
((v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL) AND
(v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL)) OR
(v_prov_npi IS NOT NULL AND
v_begin_date IS NOT NULL AND
v_end_date IS NOT NULL)
THEN
------------------------------------------------------------------------------------------
-- Split claim numbers
------------------------------------------------------------------------------------------
IF v_claim_number IS NOT NULL THEN
v_get_claim_nos := get_claim_lists(v_claim_number);
v_claim_number := v_get_claim_nos;
END IF;
v_query := 'SELECT claim_number'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' claim_fact_key'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' member_id'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' patient_name'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' member_dob'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' patient_ssn'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' medicaid_id'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' group_id'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' group_name'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' subscriber_name'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' patient_account_number'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' date_of_service'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' claim_status'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' claim_status_php'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' total_charge'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' paid_to'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' paid_amount'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' date_claim_was_received'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' date_claim_was_paid_on'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' total_check_amount'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' payment_status_desc'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' check_number'|| ' || ' || '''' || '|' || '''' || ' || ' ||
' claim_data_updated AS csv_row
FROM (
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_first_name) AS member_first_name,
INITCAP(ees.meme_last_name) AS member_last_name,
TO_CHAR(ees.meme_birth_dt,''MM/DD/YYYY'') AS member_dob,
ees.meme_ssn AS patient_ssn,
clm.check_number AS check_number,
ees.meme_medcd_no AS medicaid_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
ees.grgr_id AS group_id,
INITCAP(ees.grgr_name) AS group_name,
INITCAP(ees.sbsb_last_name || '' '' || ees.sbsb_first_name
|| '' '' || ees.sbsb_mid_init) AS subscriber_name,
clm.patient_account_number AS patient_account_number,
clm.claim_status AS claim_status,
( SELECT INITCAP(prv1.provider_name)
FROM claims_hub.hub_claims_to_provider prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
AND prv1.provider_type = ''SUPPLIER''
) AS paid_to,
TO_CHAR(clm.receipt_dt,''MM/DD/YYYY'') AS date_claim_was_received,
TO_CHAR(clm.paid_dt,''MM/DD/YYYY'') AS date_claim_was_paid_on,
CASE WHEN clm.total_check_amount IS NOT NULL THEN
''$'' || clm.total_check_amount
END AS total_check_amount,
clm.payment_status_desc AS payment_status_desc,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)) AS date_of_service,
clm.claim_status_php AS claim_status_php,
(SELECT SUM(clm2.billed_amount)
FROM claims_hub.hub_claims_medical clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND clm2.billed_amount IS NOT NULL
AND clm2.claim_line_status_code <> ''r''
) AS total_charge,
(SELECT SUM(clm3.paid_amount)
FROM claims_hub.hub_claims_medical clm3
WHERE clm3.claim_hcc_id = clm.claim_hcc_id
AND clm3.claim_fact_key = clm.claim_fact_key
AND clm3.claim_status_php <> ''Reversed''
AND clm3.paid_amount IS NOT NULL
AND clm3.claim_line_status_code <> ''r''
) AS paid_amount
FROM claims_hub.hub_claims_medical clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'LEFT OUTER JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
IF v_claim_number IS NOT NULL OR v_check_number IS NOT NULL THEN
IF v_claim_number IS NOT NULL THEN
v_query := v_query || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
ELSE
v_query := v_query || 'WHERE (1=1 OR clm.claim_hcc_id IS NULL) ';
END IF;
IF v_check_number IS NOT NULL THEN
v_query := v_query || 'AND clm.check_number = '''|| v_check_number || ''' ';
END IF;
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query := v_query || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query := v_query || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query := v_query || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND v_mem_last_name IS NOT NULL AND v_mem_dob IS NOT NULL ) THEN
v_query := v_query || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query := v_query || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query := v_query || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query := v_query || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
END IF;
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) ';
IF v_check_number IS NULL THEN
v_query := v_query || 'UNION
SELECT DISTINCT
clm.claim_hcc_id AS claim_number,
clm.claim_fact_key AS claim_fact_key,
ees.sbsb_id || ees.meme_sfx AS member_id,
INITCAP(ees.meme_first_name) AS member_first_name,
INITCAP(ees.meme_last_name) AS member_last_name,
TO_CHAR(ees.meme_birth_dt,''MM/DD/YYYY'') AS member_dob,
ees.meme_ssn AS patient_ssn,
TO_NUMBER('''') AS check_number,
ees.meme_medcd_no AS medicaid_id,
INITCAP(ees.meme_last_name || '' '' || ees.meme_first_name
|| '' '' || ees.meme_mid_init) AS patient_name,
ees.grgr_id AS group_id,
INITCAP(ees.grgr_name) AS group_name,
INITCAP(ees.sbsb_last_name || '' '' || ees.sbsb_first_name
|| '' '' || ees.sbsb_mid_init) AS subscriber_name,
clm.patient_account_number AS patient_account_number,
clm.claim_status AS claim_status,
(SELECT INITCAP(prv1.supplier_name)
FROM claims_hub.hub_claims_to_supplier_othsts prv1
WHERE prv1.claim_fact_key = clm.claim_fact_key
) AS paid_to,
TO_CHAR(clm.receipt_dt,''MM/DD/YYYY'') AS date_claim_was_received,
TO_CHAR(clm.paid_dt,''MM/DD/YYYY'') AS date_claim_was_paid_on,
CASE WHEN clm.total_check_amount IS NOT NULL THEN
''$'' || clm.total_check_amount
END AS total_check_amount,
clm.payment_status_desc AS payment_status_desc,
TO_CHAR(most_recent_process_time,''MM/DD/YYYY'') AS claim_data_updated,
(SELECT CASE WHEN (svc_dt_beg IS NOT NULL AND svc_dt_end IS NOT NULL) THEN
TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
WHEN svc_dt_beg IS NOT NULL THEN TO_CHAR(svc_dt_beg,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_beg,''MM/DD/YYYY'')
WHEN svc_dt_end IS NOT NULL THEN TO_CHAR(svc_dt_end,''MM/DD/YYYY'') || '' to '' || TO_CHAR(svc_dt_end,''MM/DD/YYYY'')
END FROM (SELECT MIN(clm1.svc_dt_beg) AS svc_dt_beg,
MAX(clm1.svc_dt_end) AS svc_dt_end
FROM claims_hub.hub_claims_medical_othsts clm1
WHERE clm1.claim_hcc_id = clm.claim_hcc_id
AND clm1.claim_fact_key = clm.claim_fact_key
AND clm1.claim_status_php <> ''Reversed''
AND clm1.claim_line_status_code <> ''r''
)
) AS date_of_service,
clm.claim_status_php AS claim_status_php,
(SELECT SUM(clm2.billed_amount)
FROM claims_hub.hub_claims_medical clm2
WHERE clm2.claim_hcc_id = clm.claim_hcc_id
AND clm2.claim_fact_key = clm.claim_fact_key
AND clm2.claim_status_php <> ''Reversed''
AND clm2.billed_amount IS NOT NULL
AND clm2.claim_line_status_code <> ''r''
) AS total_charge,
(SELECT SUM(clm3.paid_amount)
FROM claims_hub.hub_claims_medical clm3
WHERE clm3.claim_hcc_id = clm.claim_hcc_id
AND clm3.claim_fact_key = clm.claim_fact_key
AND clm3.claim_status_php <> ''Reversed''
AND clm3.paid_amount IS NOT NULL
AND clm3.claim_line_status_code <> ''r''
) AS paid_amount
FROM claims_hub.hub_claims_medical_othsts clm
JOIN payors_hub.php_ees_master_tb ees
ON NVL(ees.member_key,ees.meme_ck) = clm.member_key ';
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'LEFT OUTER JOIN claims_hub.hub_claims_to_provider prv
ON prv.claim_fact_key = clm.claim_fact_key ';
END IF;
IF v_claim_number IS NOT NULL THEN
v_query := v_query || 'WHERE (clm.claim_hcc_id) IN (' || v_claim_number || ') ';
IF TO_CHAR(SYSDATE,'MMDD') = '0229' THEN
SELECT TO_CHAR(SYSDATE-1 - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
ELSE
SELECT TO_CHAR(SYSDATE - INTERVAL '2' YEAR,'YYYYMM') INTO v_date FROM DUAL;
END IF;
v_query := v_query || ' AND clm.svc_dt_beg_yyyymm >= ''' || v_date || ''' ';
ELSIF v_mem_member_id IS NOT NULL OR v_patient_ssn IS NOT NULL OR v_mem_medicaid_id IS NOT NULL THEN
IF v_mem_member_id IS NOT NULL THEN
v_query := v_query || 'WHERE ees.sbsb_id||ees.meme_sfx = ''' ||v_mem_member_id || ''' ';
ELSE
v_query := v_query || 'WHERE (1=1 OR ees.sbsb_id||ees.meme_sfx IS NULL) ';
END IF;
IF v_patient_ssn IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_ssn = '''|| v_patient_ssn || ''' ';
END IF;
IF v_mem_medicaid_id IS NOT NULL THEN
v_query := v_query || 'AND ees.meme_medcd_no = '''|| v_mem_medicaid_id || ''' ';
END IF;
ELSIF (v_mem_first_name IS NOT NULL AND
v_mem_last_name IS NOT NULL AND
v_mem_dob IS NOT NULL ) THEN
v_query := v_query || 'WHERE LOWER(ees.meme_first_name) = ''' || v_mem_first_name || ''' ';
v_query := v_query || 'AND LOWER(ees.meme_last_Name) = ''' || v_mem_last_name || ''' ';
v_query := v_query || 'AND TO_CHAR(ees.meme_birth_dt,''YYYYMMDD'') = '''|| v_mem_dob || ''' ';
ELSIF v_prov_npi IS NOT NULL AND v_prov_tin IS NOT NULL THEN
v_query := v_query || 'WHERE prv.provider_npi = '''|| v_prov_npi || ''' ';
END IF;
IF v_prov_tin IS NOT NULL THEN
v_query := v_query || 'AND prv.tax_id = '''|| v_prov_tin || ''' ';
END IF;
IF (v_begin_date IS NOT NULL AND v_end_date IS NOT NULL) AND (v_begin_date <= v_end_date) AND v_days <= 93 THEN
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_beg,''YYYYMMDD'') >= ''' || v_begin_date || ''' ';
v_query := v_query || 'AND TO_CHAR(clm.svc_dt_end,''YYYYMMDD'') <= ''' || v_end_date || ''' ';
END IF;
v_query := v_query || ' AND clm.claim_status_php <> ''Reversed'' AND ees.cspd_cat = ''M'' ';
v_query := v_query || ' AND clm.claim_line_status_code <> ''r'' ';
v_query := v_query || ' AND ees.source = SUBSTR(clm.source,1,3) ';
END IF;
v_query := v_query || ' )ORDER BY claim_number,claim_fact_key ';
EXECUTE IMMEDIATE v_base_query || v_query BULK COLLECT INTO v_claim_download_rec_type;
IF v_claim_download_rec_type.COUNT > 0 THEN
v_have_data := TRUE;
v_pc_success_element := dbms_xmldom.createelement(v_xml_dom_out, 'status');
v_pc_success_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_pc_success_element));
v_pc_success_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'success');
v_pc_success_textnode := dbms_xmldom.appendchild(v_pc_success_node, dbms_xmldom.makenode(v_pc_success_text));
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out,v_header_query);
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
END IF;
FOR idx in 1.. v_claim_download_rec_type.COUNT
LOOP
v_have_data := TRUE;
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out,v_claim_download_rec_type(idx).csv_row);
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
END LOOP;
IF NOT v_have_data THEN
v_pc_success_element := dbms_xmldom.createelement(v_xml_dom_out, 'status');
v_pc_success_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_pc_success_element));
v_pc_success_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'success');
v_pc_success_textnode := dbms_xmldom.appendchild(v_pc_success_node, dbms_xmldom.makenode(v_pc_success_text));
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'No Data found');
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
END IF;
ELSE
v_pc_success_element := dbms_xmldom.createelement(v_xml_dom_out, 'status');
v_pc_success_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_pc_success_element));
v_pc_success_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'success');
v_pc_success_textnode := dbms_xmldom.appendchild(v_pc_success_node, dbms_xmldom.makenode(v_pc_success_text));
v_download_element := dbms_xmldom.createelement(v_xml_dom_out, 'download');
v_download_node := dbms_xmldom.appendchild(v_pc_claim_node, dbms_xmldom.makenode(v_download_element));
v_download_text := dbms_xmldom.createtextnode(v_xml_dom_out, 'System could not find valid details .Please try again.');
v_download_textnode := dbms_xmldom.appendchild(v_download_node, dbms_xmldom.makenode(v_download_text));
END IF;
v_xmltype := dbms_xmldom.getxmltype(v_xml_dom_out);
v_xmlout := v_xmltype.getclobval();
END provider_connect_claim_download_prc;
/********************************************************************************************************************/
/********************************************************************************************************************
** NAME : GET_CLAIM_LISTS **
** **
** VERSION : 1.0 **
** **
** DESCRIPTION : To split comma delimited values when user entered multiple claim numbers with comma **
** separated **
** PARAMETERS : **
** INPUT : claim ids with comma separated **
** OUTPUT : claim ids with out comma separated **
** **
** AUTHOR : COGNIZANT **
** **
** DATE : 04/08/2024 **
*------------------------------------------------------------------------------------------------------------------**
* CHANGE VERSION | HISTORY DATE | AUTHOR | CHANGE DESCRIPTION **
* ---------------| ------------ | -----------| --------------------------------------------------------------------**
* 1.0 | 04/08/2024 | Cognizant | Initial Version **
********************************************************************************************************************/
FUNCTION get_claim_lists (v_claim_id VARCHAR2) RETURN VARCHAR2
AS
------------------------------------------------------------------------------------------------
-- Initializing variables
------------------------------------------------------------------------------------------------
v_clm_id CLOB;
v_get_claim_nos CLOB;
BEGIN
------------------------------------------------------------------------------------------------
-- To split comma delimited values for multiple claim numbers
------------------------------------------------------------------------------------------------
FOR v_clm_id IN (SELECT TRIM(REGEXP_SUBSTR(v_claim_id, '[^,]+', 1, LEVEL)) l FROM DUAL CONNECT BY LEVEL <= regexp_count(v_claim_id, ',')+1)
LOOP
v_get_claim_nos := v_get_claim_nos || '''' || v_clm_id.l || '''' || ',';
END LOOP;
RETURN RTRIM(v_get_claim_nos,',');
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN 0;
END get_claim_lists;
/*******************************************************************************************************************/
END provider_connect_claims_pkg ;
/
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.
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.
Following is the syntax structure for the PL/SQL code blocks
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
DECLARE
message varchar2(100):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
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;
/
BEGIN
DBMS_OUTPUT.put_line (1/0);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error is: ' || SQLERRM);
END;