我想优化这个商店程序一次使用它采取71.942女士我想减少这个时间 [英] I Want To Optimize This Store Procedure For One Use It Taking 71.942 Ms I Want To Reduce This Time
本文介绍了我想优化这个商店程序一次使用它采取71.942女士我想减少这个时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
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;
推荐答案
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;
\t
\ts31To60DaysFromDate VARCHAR;
\ts31To60DaysToDate VARCHAR;
\ts61To90DaysFromDate VARCHAR;
\ts61To90DaysToDate VARCHAR;
\ts91To120DaysFromDate VARCHAR;
\ts91To120DaysToDate VARCHAR;
Rec RECORD;
Rec1 RECORD;
sQuery VARCHAR;
sLWhere VARCHAR;
sLWhereBalance VARCHAR;
sLWhereTotal1 VARCHAR;
sLWhereTotal2 VARCHAR;
sLWhereTotal3 VARCHAR;
sLWhereTotal4 VARCHAR;
sLWhereTotal5 VARCHAR;
\tsLWhereSecTriFilter VARCHAR;
\tsLWhereSecTriFilterTxn VARCHAR;
\tsLWhereSecFilter VARCHAR;
sLOrder VARCHAR;
iClaimStatusAvailable VARCHAR;
sRenderingName VARCHAR;
nTypeCount int;
\tSELF_PAY_AMOUNT Numeric;
\t--iTotalBalance Numeric;
\tiFormatNumer Numeric;
\tv_current int;
\tp_end int;
\tpay_by_quarter Numeric[4];
\tdescCharacter int;
\tiCurrentValue Numeric;
\ttemTableTotal VARCHAR;
\ttemTableRS VARCHAR;
\ttempData VARCHAR;
\tPATIENT_STATEMENT_TX_TYPE VARCHAR;
\tCLOSED_CLAIM_TX_TYPE \tINTEGER; -- Binaya Patel : June 21 2012 (eMB2.0.15)
\tsQuerySelfPay VARCHAR;
\tsQuerySelfPayTemp VARCHAR;
\t
\tPRI_SUBMISSION_PR_AMOUNT Numeric;
\tsQuery_PRI_SUBMISSION_PR_AMOUNT VARCHAR;
\tsQuery_PRI_SUBMISSION_PR_AMOUNT_TEMP VARCHAR;
\t
\tSQL_EXCLUDE_CONFIDENTIAL_TRANSACTION VARCHAR; --[PHD]
\t
\t
\tsLWhereTotalN VARCHAR;
\tSQL_PAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH VARCHAR;
\tPAYMENT_AMT_TILL_END_OF_PAT_STMT_MONTH NUMERIC;
\tBALANCE_PAYMENT_AMT NUMERIC;
\t
BEGIN
\tiAccountKey \t\t\t:=
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 \t\t:=
1; iRenderingProvider :=
这篇关于我想优化这个商店程序一次使用它采取71.942女士我想减少这个时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文