CREATE OR REPLACE FUNCTION sp_ecp_ledger_patient_statement_aging
(
iaccountkey varchar,
irenderingprovider integer,
ipatient integer,
iledgerfrom varchar,
iledgerto varchar,
p31To60DaysFromDate varchar,
p31To60DaysToDate varchar,
p61To90DaysFromDate varchar,
p61To90DaysToDate varchar,
p91To120DaysFromDate varchar,
p91To120DaysToDate varchar
) RETURNS SETOF type_sp_ecp_ledger_patient_statement_aging AS
$body$
DECLARE sType type_SP_eCP_LEDGER_PATIENT_STATEMENT_AGING%ROWTYPE;
SQL varchar(8000);
iAccountKey varchar(20);
iRenderingProvider INTEGER;
ipatient INTEGER;
iledgerfrom VARCHAR;
iledgerto VARCHAR;
s31To60DaysFromDate VARCHAR;
s31To60DaysToDate VARCHAR;
s61To90DaysFromDate VARCHAR;
s61To90DaysToDate VARCHAR;
s91To120DaysFromDate VARCHAR;
s91To120DaysToDate VARCHAR;
Rec RECORD;
Rec1 RECORD;
sQuery VARCHAR;
sLWhere VARCHAR;
sLWhereBalance VARCHAR;
sLWhereTotal1 VARCHAR;
sLWhereTotal2 VARCHAR;
sLWhereTotal3 VARCHAR;
sLWhereTotal4 VARCHAR;
sLWhereTotal5 VARCHAR;
sLWhereSecTriFilter VARCHAR;
sLWhereSecTriFilterTxn VARCHAR;
sLWhereSecFilter VARCHAR;
sLOrder VARCHAR;
iClaimStatusAvailable VARCHAR;
sRenderingName VARCHAR;
nTypeCount int;
SELF_PAY_AMOUNT Numeric;
iFormatNumer Numeric;
v_current int;
p_end int;
pay_by_quarter Numeric[4];
descCharacter int;
iCurrentValue Numeric;
temTableTotal VARCHAR;
temTableRS VARCHAR;
tempData VARCHAR;
PATIENT_STATEMENT_TX_TYPE VARCHAR;
CLOSED_CLAIM_TX_TYPE INTEGER;
sQuerySelfPay VARCHAR;
sQuerySelfPayTemp VARCHAR;
PRI_SUBMISSION_PR_AMOUNT Numeric;
sQuery_PRI_SUBMISSION_PR_AMOUNT VARCHAR;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP VARCHAR;
SQL_EXCLUDE_CONFIDENTIAL_TRANSACTION VARCHAR;
sLWhereTotalN VARCHAR;
SQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH VARCHAR;
PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH NUMERIC;
BALANCE_PAYMENT_AMT NUMERIC;
BEGIN
iAccountKey := $1;
iRenderingProvider := $2;
ipatient := $3;
iledgerfrom := $4;
iledgerto := $5;
s31To60DaysFromDate := $6;
s31To60DaysToDate := $7;
s61To90DaysFromDate := $8;
s61To90DaysToDate := $9;
s91To120DaysFromDate := $10;
s91To120DaysToDate := $11;
RAISE NOTICE '------------------- PATIENT STATEMENT AGING -------------------';
RAISE NOTICE 'Account Key: %', iAccountKey;
RAISE NOTICE 'Patient Key: %', ipatient;
RAISE NOTICE 'iledgerfrom: %', iledgerfrom;
RAISE NOTICE 'iledgerto: %', iledgerto;
RAISE NOTICE 's31To60DaysFromDate: %', s31To60DaysFromDate;
RAISE NOTICE 's31To60DaysToDate: %', s31To60DaysToDate;
RAISE NOTICE 's61To90DaysFromDate: %', s61To90DaysFromDate;
RAISE NOTICE 's61To90DaysToDate: %', s61To90DaysToDate;
RAISE NOTICE 's91To120DaysFromDate: %', s91To120DaysFromDate;
RAISE NOTICE 's91To120DaysToDate: %', s91To120DaysToDate;
iFormatNumer := '9999999999.99';
descCharacter := 25;
v_current := 1;
p_end := 5;
iCurrentValue = 0;
SELF_PAY_AMOUNT := '0.00';
PRI_SUBMISSION_PR_AMOUNT := '0.00';
sQuery:='';
sQuery_PRI_SUBMISSION_PR_AMOUNT :='';
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP :='';
sLWhere:='';
sLWhereTotal1:='';
sLWhereTotal2:='';
sLWhereTotal3:='';
sLWhereTotal4:='';
sLWhereTotal5:='';
temTableRS = 'temp_table_rs';
temTableTotal = 'temp_table_total';
tempData = 'tempdata';
PATIENT_STATEMENT_TX_TYPE := '1014';
CLOSED_CLAIM_TX_TYPE := 3000;
SQL_EXCLUDE_CONFIDENTIAL_TRANSACTION := ' AND eCPLedger_Master_SFS_Confidentiality_Flag = 0 ';
sLWhereTotalN := '';
sQuerySelfPay = 'SELECT COALESCE(SUM(LM.eCPLedger_Master_Transaction_Amount),0) AS TransactionAmount FROM tbl_eCPLedger_Master LM
WHERE LM.eCPAccount_Key = ' || quote_literal(iAccountKey) || ' AND
LM.eCPPatientDMG_Key = ' || ipatient || ' AND
LM.eCPLedger_Master_Transaction_Type IN (1001) AND
LM.eCPLedger_Master_PHD_Flag = 0 AND
LM.eCPLedger_Payer_Key IN
(SELECT eCPLedger_Payer_Key FROM tbl_eCPLedger_Payer WHERE UPPER(eCPLedger_Payer_ID) = ''SELF'')
AND
LM.eCPClaim_Key NOT IN
(SELECT eCPClaim_Primary_Secondary_Relation_Secondary_Claim_Key
FROM tbl_eCPClaim_Primary_Secondary_Relation
WHERE eCPClaim_Primary_Secondary_Relation_Secondary_Claim_Key = LM.eCPClaim_key
)';
sQuery_PRI_SUBMISSION_PR_AMOUNT := 'SELECT
COALESCE(SUM(LM.eCPLedger_Master_Transaction_PR),0.00)
AS PRAmount
FROM
tbl_eCPLedger_Master LM
WHERE LM.eCPAccount_Key = ' || quote_literal(iAccountKey) || ' AND
LM.eCPPatientDMG_Key = ' || ipatient || ' AND
LM.eCPLedger_Master_Transaction_Type IN (1001,1002) AND
LM.eCPLedger_Master_PHD_Flag = 1 ' || SQL_EXCLUDE_CONFIDENTIAL_TRANSACTION || ' AND
LM.eCPClaim_Key NOT IN
(SELECT eCPClaim_Primary_Secondary_Relation_Secondary_Claim_Key
FROM tbl_eCPClaim_Primary_Secondary_Relation
WHERE eCPClaim_Primary_Secondary_Relation_Secondary_Claim_Key = LM.eCPClaim_key)';
sLWhereSecTriFilter:=' and ecpclaim_key not in (select ecpclaim_primary_secondary_relation_secondary_claim_key from
tbl_ecpclaim_primary_secondary_relation where ecpclaim_primary_secondary_relation_secondary_claim_key = ecplm.ecpclaim_key
and ecplm.ecpledger_master_transaction_type = ''1002'')
and ecpclaim_key not in (select ecpclaim_primary_secondary_relation_tertiary_claim_key from tbl_ecpclaim_primary_secondary_relation where
ecpclaim_primary_secondary_relation_tertiary_claim_key = ecplm.ecpclaim_key and ecplm.ecpledger_master_transaction_type = ''1003'') ';
sLWhereSecFilter:=' and ecpclaim_key in (select ecpclaim_primary_secondary_relation_secondary_claim_key from
tbl_ecpclaim_primary_secondary_relation where ecpclaim_primary_secondary_relation_secondary_claim_key = ecplm.ecpclaim_key) ';
sLWhereSecTriFilterTxn:=' and ecpclaim_key not in (select ecpclaim_primary_secondary_relation_secondary_claim_key from
tbl_ecpclaim_primary_secondary_relation where ecpclaim_primary_secondary_relation_secondary_claim_key = ecplm.ecpclaim_key)
and ecpclaim_key not in (select ecpclaim_primary_secondary_relation_tertiary_claim_key from
tbl_ecpclaim_primary_secondary_relation where ecpclaim_primary_secondary_relation_tertiary_claim_key = ecplm.ecpclaim_key) ';
sLWhere := ' where ecpledger_master_key > 0 ';
IF((iAccountKey IS NOT NULL) AND (iAccountKey != 'All')) then
sLWhere:= sLWhere || ' and eCPAccount_Key = ' || quote_literal(iAccountKey) || ' ';
END IF;
IF(ipatient IS NOT NULL) then
sLWhere:= sLWhere || ' and eCPPatientDMG_Key =' || ipatient || ' ';
end if;
sLWhere := sLWhere || ' AND ecpLM.eCPLedger_Master_Transaction_Type <> ' || PATIENT_STATEMENT_TX_TYPE || ' ';
sLWhere := sLWhere || ' AND ecpLM.eCPLedger_Master_Transaction_Type <> '|| CLOSED_CLAIM_TX_TYPE;
sLWhere := sLWhere || SQL_EXCLUDE_CONFIDENTIAL_TRANSACTION;
if((iledgerfrom IS NOT NULL AND iledgerfrom != '') AND (iledgerto IS NOT NULL AND iledgerto != '')) then
sLWhereTotal5 := ' and eCPLedger_Master_Transaction_Date BETWEEN ' || quote_literal(iledgerfrom) || ' AND ' || quote_literal(iledgerto) || ' ';
sLWhereTotal4 := ' and eCPLedger_Master_Transaction_Date BETWEEN ' || quote_literal(s31To60DaysFromDate) || ' AND ' || quote_literal(s31To60DaysToDate) || ' ';
sLWhereTotal3 := ' and eCPLedger_Master_Transaction_Date BETWEEN ' || quote_literal(s61To90DaysFromDate) || ' AND ' || quote_literal(s61To90DaysToDate) || ' ';
sLWhereTotal2 := ' and eCPLedger_Master_Transaction_Date BETWEEN ' || quote_literal(s91To120DaysFromDate) || ' AND ' || quote_literal(s91To120DaysToDate) || ' ';
sLWhereTotal1 := ' and eCPLedger_Master_Transaction_Date < ' || quote_literal(s91To120DaysFromDate) || ' ';
sLWhereTotalN := ' and eCPLedger_Master_Transaction_Date <= ' || quote_literal(iledgerto) || ' ';
end if;
execute 'create temporary table '|| temTableRS || ' (genCol VARCHAR, transDate timestamp, serviceDate date, description VARCHAR(200), copay DOUBLE PRECISION, totalAmt DOUBLE PRECISION, totalPymt DOUBLE PRECISION, totalAdj DOUBLE PRECISION, agingCharges DOUBLE PRECISION)';
RAISE NOTICE '---------------------------------------------------------------';
RAISE NOTICE 'Aging Charges';
WHILE (v_current <= p_end) loop
if (v_current = 1) then
sQuery = 'select
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_PR),0) AS PRAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal1 || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1004,1005,1007,1008) AND ecplm.eCPClaim_Key IN(SELECT eCPClaim_Key FROM tbl_eCPClaim WHERE UPPER(eCPClaim_Carrier_ID) <> ''SELF'' and eCPClaim_Key=ecplm.eCPClaim_Key)) -
0 -
0)) +
((SELECT COALESCE(SUM(ecpledger_master_transaction_amount),0) AS TransactionAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal1 || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN(1000,1016)) -
SUM(0) -
0),0)
as AGING_CHARGES from tbl_ecpledger_master ecplm ';
sQuery = sQuery || sLWhere || sLWhereTotal1;
sQuerySelfPayTemp = sQuerySelfPay || sLWhereTotal1;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP = sQuery_PRI_SUBMISSION_PR_AMOUNT || sLWhereTotal1;
RAISE NOTICE '>120 Days';
elsif (v_current = 2) then
sQuery = 'select
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_PR),0) AS PRAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal2 || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1004,1005,1007,1008) AND ecplm.eCPClaim_Key IN(SELECT eCPClaim_Key FROM tbl_eCPClaim WHERE UPPER(eCPClaim_Carrier_ID) <> ''SELF'' and eCPClaim_Key=ecplm.eCPClaim_Key)) -
0 -
0)) +
((SELECT COALESCE(SUM(ecpledger_master_transaction_amount),0) AS TransactionAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal2 || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN(1000,1016)) -
SUM(0) -
0),0)
as AGING_CHARGES from tbl_ecpledger_master ecplm ';
sQuery = sQuery || sLWhere || sLWhereTotal2;
sQuerySelfPayTemp = sQuerySelfPay || sLWhereTotal2;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP = sQuery_PRI_SUBMISSION_PR_AMOUNT || sLWhereTotal2;
RAISE NOTICE '91-120 Days';
elsif (v_current = 3) then
sQuery = 'select
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_PR),0) AS PRAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal3 || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1004,1005,1007,1008) AND ecplm.eCPClaim_Key IN(SELECT eCPClaim_Key FROM tbl_eCPClaim WHERE UPPER(eCPClaim_Carrier_ID) <> ''SELF'' and eCPClaim_Key=ecplm.eCPClaim_Key)) -
0 -
0)) +
((SELECT COALESCE(SUM(ecpledger_master_transaction_amount),0) AS TransactionAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal3 || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN(1000,1016)) -
SUM(0) -
0),0)
as AGING_CHARGES from tbl_ecpledger_master ecplm ';
sQuery = sQuery || sLWhere || sLWhereTotal3;
sQuerySelfPayTemp = sQuerySelfPay || sLWhereTotal3;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP = sQuery_PRI_SUBMISSION_PR_AMOUNT || sLWhereTotal3;
RAISE NOTICE '61-90 Days';
elsif (v_current = 4) then
sQuery = 'select
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_PR),0) AS PRAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal4 || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1004,1005,1007,1008) AND ecplm.eCPClaim_Key IN(SELECT eCPClaim_Key FROM tbl_eCPClaim WHERE UPPER(eCPClaim_Carrier_ID) <> ''SELF'' and eCPClaim_Key=ecplm.eCPClaim_Key)) -
0 -
0)) +
((SELECT COALESCE(SUM(ecpledger_master_transaction_amount),0) AS TransactionAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal4 || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN(1000,1016)) -
SUM(0) -
0),0)
as AGING_CHARGES from tbl_ecpledger_master ecplm ';
sQuery = sQuery || sLWhere || sLWhereTotal4;
sQuerySelfPayTemp = sQuerySelfPay || sLWhereTotal4;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP = sQuery_PRI_SUBMISSION_PR_AMOUNT || sLWhereTotal4;
RAISE NOTICE '31-60 Days';
elsif (v_current = 5) then
sQuery = 'select
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_PR),0) AS PRAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal5 || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1004,1005,1007,1008) AND ecplm.eCPClaim_Key IN(SELECT eCPClaim_Key FROM tbl_eCPClaim WHERE UPPER(eCPClaim_Carrier_ID) <> ''SELF'' and eCPClaim_Key=ecplm.eCPClaim_Key)) -
0 -
0)) +
((SELECT COALESCE(SUM(ecpledger_master_transaction_amount),0) AS TransactionAmount FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotal5 || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN(1000,1016)) -
SUM(0) -
0),0)
as AGING_CHARGES from tbl_ecpledger_master ecplm ';
sQuery = sQuery || sLWhere || sLWhereTotal5;
sQuerySelfPayTemp = sQuerySelfPay || sLWhereTotal5;
sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP = sQuery_PRI_SUBMISSION_PR_AMOUNT || sLWhereTotal5;
RAISE NOTICE '0-30 Days';
end if;
execute 'create temporary table '|| temTableTotal || ' as (' || sQuery || ')';
execute sQuerySelfPayTemp INTO SELF_PAY_AMOUNT;
execute sQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP INTO PRI_SUBMISSION_PR_AMOUNT;
execute 'UPDATE '|| temTableTotal || ' SET AGING_CHARGES = AGING_CHARGES + ' || SELF_PAY_AMOUNT + PRI_SUBMISSION_PR_AMOUNT;
sQuery = 'select * from '|| temTableTotal;
for Rec1 in execute sQuery
loop
execute ('insert into '|| temTableRS || ' (genCol, transDate, serviceDate, description, copay, totalAmt, totalPymt, totalAdj, agingCharges) VALUES (''A'', NULL, NULL, ''Aging'', 0.00, 0.00, 0.00, 0.00, ' || Rec1.AGING_CHARGES || ') ');
pay_by_quarter[v_current-1] = Rec1.AGING_CHARGES;
RAISE NOTICE '%', Rec1.AGING_CHARGES;
v_current = v_current + 1;
end loop;
execute 'drop table if exists '|| temTableTotal;
END loop;
RAISE NOTICE '---------------------------------------------------------------';
v_current = 0;
execute 'drop table if exists '|| temTableRS || '';
execute 'create TEMPORARY table '||tempData ||' (balance double PRECISION)';
SQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH := 'SELECT
COALESCE((
((SELECT COALESCE(SUM(eCPLedger_Master_Transaction_Payment),0) AS SecPayment FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotalN || ' ' || sLWhereSecFilter || ' AND eCPLedger_Master_Transaction_Type IN (1005,1008)) +
(SELECT COALESCE(SUM(eCPLedger_Master_Transaction_Adjustment),0) AS SFS_ADJUSTMENT_AMOUNT FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotalN || ' ' || sLWhereSecTriFilterTxn || ' AND eCPLedger_Master_Transaction_Type IN (1100) AND eCPLedger_Master_PHD_Flag = 1))) +
(SUM(ecpledger_master_transaction_copay) +
(SELECT COALESCE(SUM(ecpledger_master_transaction_payment),0) AS Payment FROM tbl_ecpledger_master ecplm ' || sLWhere || sLWhereTotalN || ' AND eCPLedger_Master_Transaction_Type IN(1000,1010,1011,1012,1013,1015,1016,1017,1101,1102))),0)
as PAYMENT from tbl_ecpledger_master ecplm ';
SQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH := SQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH || sLWhere || sLWhereTotalN;
EXECUTE SQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH INTO PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH;
RAISE NOTICE 'Payment amt. till end of pat. stmt. month: %', PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH;
RAISE NOTICE '---------------------------------------------------------------';
v_current := 0;
IF(PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH <> 0) THEN
RAISE NOTICE 'Aging Charges - Payment amt. till end of pat. stmt. month';
BALANCE_PAYMENT_AMT := PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH;
WHILE (v_current < p_end) LOOP
IF(BALANCE_PAYMENT_AMT > 0) THEN
IF(pay_by_quarter[v_current] >= BALANCE_PAYMENT_AMT) THEN
pay_by_quarter[v_current] := pay_by_quarter[v_current] - BALANCE_PAYMENT_AMT;
BALANCE_PAYMENT_AMT := 0;
ELSIF(pay_by_quarter[v_current] < BALANCE_PAYMENT_AMT) THEN
BALANCE_PAYMENT_AMT := BALANCE_PAYMENT_AMT - pay_by_quarter[v_current];
pay_by_quarter[v_current] := 0;
END IF;
END IF;
RAISE NOTICE '%', pay_by_quarter[v_current];
v_current := v_current + 1;
END LOOP;
RAISE NOTICE '---------------------------------------------------------------';
END IF;
v_current := 0;
while (v_current < p_end) loop
execute 'insert into '||tempData ||' values(' || pay_by_quarter[v_current] || ')';
v_current = v_current + 1;
end loop;
sQuery = 'select * from '||tempData;
for Rec in execute sQuery
loop
RETURN NEXT Rec;
end loop;
execute 'drop table if exists ' || tempData;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;