I Want To Optimize This Store Procedure For One Use It Taking 71.942 Ms I Want To Reduce This Time

Question

1.00/5 (3 votes)

See more:

SQL

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;
	--iTotalBalance 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; -- Binaya Patel : June 21 2012 (eMB2.0.15)
	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; --[PHD] 
	
	
	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 'Statement Period: % to %', iledgerfrom, iledgerto;
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;
    --iTotalBalance = 0;
	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 := '';
		
	/************************************************************************
	TRANSACTION AMOUNT OF SELF PAY CLAIMS ARE TAKEN
	TERENCE SEP 12 2012 - SELF PAY CLAIMS FROM PHD ACCOUNTS (LEDGER TX HAVING PHD FLAG AS 1) 
	SHOULD BE EXCLUDED. THAT MEANS - ONLY TRANSACTION AMOUNT OF NON-PHD SELF PAY CLAIMS ARE TAKEN
	FOR PHD SELF PAY CLAIMS, THE PR AVAILABLE IN CLAIM SUBMISSION ENTRY IS TAKEN 
	- WHICH IS HANDLED SEPARATELY IN sQuery_PRI_SUBMISSION_PR_AMOUNT
	*************************************************************************/	
	
	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
	)';
	--RAISE NOTICE 'vsQuerysQuery: %', sQuery;
	
	/**********************************************
	INTRODUCED FOR HANDLING PHD CLAIMS - TERENCE SEP 12 2012
	Primary PR from the PHD primary claim submission and
	Secondary PR for the orphan secondary claim submission is taken here.
	Secondary PR for the primary claims are excluded
	THESE PR WILL NOT BE TAKEN IN (PRIMARY PR - SEC PAYMENT) CALUCLATION
	AS PATIENT IS SUPPOSED TO PAY FROM THEIR POCKET
	**********************************************/
	
	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 '; -- To make the default where condition. it should not affect the resultset as primarykey is greater than 0 always

    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; --[PHD] JOBIN (12-OCT-2012)
	
   
    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
		/*******************************************
		[V2.2.1] JOBIN JAN 15 2013
		ADDED PHD SFS ADJUSTMENT AMOUNT IN BELOW sQuery
		
		[eMB2.2.2] -  NOW WE WILL NOT CONSIDER PRIMARY PR OF CLAIMS WHERE THE CLAIM CARRIER ID IS SELF
		THIS IS TO AVOID TAKING PRIMARY PR AND SELF PAY CHARGES BOTH FOR THE SAME CLAIM
		HIS SCENARIO HAPPENS WHEN AN INSURANCE CLAIM IS RESUBMITTED AS SELF.
		********************************************/	
		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 '---------------------------------------------------------------';
	
	-- TERENCE APR 08 2013 - WE NEED TO UNDERSTAND WHAT THIS LOGIC IS
	
	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; -- CHANGING BACK TO DEFAULT VALUE
    -- IMPORTANT: WE NEED TO CHECK HOW THIS NEW LOGIC WILL ENSURE THAT ALL AGING AMOUNTS (0-30, 31-60 etc)
    -- WILL STILL ADD UP TO PATIENT BALANCE DUE TILL THE STATEMENT MONTH.
    
    while (v_current < p_end) loop --for Rec1 in select * from temp_table_rs
    	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;


--    RETURN pay_by_quarter;
end
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Posted 15-Oct-14 3:12am

Member 10501509

Updated 15-Oct-14 9:52am

Maciej Los

v3

Add a Solution

Comments

Member 10501509 15-Oct-14 9:15am

sample input for store procedure
select * from sp_ecp_ledger_patient_statement_aging('0074690845',null,6070, '2014-10-01 09:30:00', '2014-11-01 09:29:59', '2014-09-01 09:30:00' ,'2014-10-01 09:29:59',
'2014-08-01 09:30:00', '2014-09-01 09:29:59', '2014-07-01 09:30:00', '2014-08-01 09:29:59')

[no name] 15-Oct-14 9:23am

And what is it that you expect us to do with this code dump? Use a profiler to see what is taking so long.

Member 10501509 15-Oct-14 9:31am

i am using pgadmin

Member 10501509 15-Oct-14 9:31am

We dont have sqlprofiler

[no name] 15-Oct-14 9:43am

So go get one.

[no name] 15-Oct-14 15:59pm

I suggest to outsource this part of your app :-)

Maciej Los 15-Oct-14 16:01pm

Not a question at all! We can't see your screen and we can't read in your mind.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-15T10:36:00

Solution 1

I'd suggest to download a tool which functionality can help you to improve performance of above query.

For example:
Enterprise Postgres Query Analyser[^]
SO: is there a postgresql equivalent of sql server profiler?[^]

Here is interesting article: http://www.charlestonsw.com/sql-analyzing-queries-in-postgres/[^]

Posted 15-Oct-14 10:36am

Maciej Los

Comments

Member 10501509 17-Oct-14 3:21am

I am downloading pgbadger and epqa winrar file.what to do with winrar files.

Maciej Los 17-Oct-14 3:24am

Download WinRar or 7-zip software and unpack archive files.

Member 10501509 17-Oct-14 3:50am

I have winrar software in my computer