http://in.compucrete.com/index.php?action=history&feed=atom&title=Employee_Loans_Export_QueryEmployee Loans Export Query - Revision history2026-05-15T17:02:59ZRevision history for this page on the wikiMediaWiki 1.40.0http://in.compucrete.com/index.php?title=Employee_Loans_Export_Query&diff=194&oldid=prevWikiAdmin: 1 revision imported2023-07-14T12:58:49Z<p>1 revision imported</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<tr class="diff-title" lang="en">
<td colspan="1" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="1" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 12:58, 14 July 2023</td>
</tr><tr><td colspan="2" class="diff-notice" lang="en"><div class="mw-diff-empty">(No difference)</div>
</td></tr></table>WikiAdminhttp://in.compucrete.com/index.php?title=Employee_Loans_Export_Query&diff=193&oldid=prevIntra>User: 1 revision imported2017-01-10T00:01:39Z<p>1 revision imported</p>
<p><b>New page</b></p><div><PRE><br />
select EMP_ID,(SELECT DISPLAY_NAME FROM PRTEMPLOYEE EE WHERE EE.EMP_ID=EH.EMP_ID) "Name",<br />
'CUR' "TYPE",TRX_DATE ,TRX_TYPE,DEF_ID,<br />
CAST ( (SELECT FACT_VALUE FROM PRTEMPFACT WHERE EMP_ID=EH.EMP_ID AND DEF_ID=EH.DEF_ID AND FACTOR_ID='PAYMENT') AS CHAR(20) )"Ded Amt",<br />
CAST(0 AS NUMERIC (15,2)) "Beg Bal",CAST(DEF_VALUE AS NUMERIC (15,2)) "New Loans",CAST(0 AS NUMERIC (15,2)) "Deductions"<br />
from prtempdefhst EH<br />
where EH.def_id IN ( :DED_1 , :DED_2)<br />
AND EH.TRX_DATE BETWEEN :BEG_DATE AND :END_DATE AND EH.TRX_TYPE='B'<br />
UNION<br />
select EMP_ID,(SELECT DISPLAY_NAME FROM PRTEMPLOYEE EE WHERE EE.EMP_ID=EH.EMP_ID) "Name",<br />
'CUR' "TYPE",TRX_DATE ,TRX_TYPE,DEF_ID,<br />
CAST ( (SELECT FACT_VALUE FROM PRTEMPFACT WHERE EMP_ID=EH.EMP_ID AND DEF_ID=EH.DEF_ID AND FACTOR_ID='PAYMENT') AS CHAR(20) )"Ded Amt",<br />
CAST(0 AS NUMERIC (15,2)) "Beg Bal",CAST(0 AS NUMERIC (15,2)) "New Loans",CAST(DEF_VALUE AS NUMERIC (15,2)) "Deductions"<br />
from prtempdefhst EH<br />
where EH.def_id IN ( :DED_1 , :DED_2)<br />
AND EH.TRX_DATE BETWEEN :BEG_DATE AND :END_DATE AND EH.TRX_TYPE='S'<br />
UNION<br />
select EMP_ID,(SELECT MAX(DISPLAY_NAME) FROM PRTEMPLOYEE EE WHERE EE.EMP_ID=E.EMP_ID) "Name",<br />
'BEG' "TYPE",<br />
MAX(LH.TRX_DATE),'A' ,DEF_ID,<br />
CAST ( (SELECT MAX(FACT_VALUE) FROM PRTEMPFACT EF WHERE EF.EMP_ID=E.EMP_ID AND EF.DEF_ID=E.DEF_ID AND EF.FACTOR_ID='PAYMENT') AS CHAR(20) ) "Ded Amt",<br />
CAST(SUM(LH.DEF_VALUE) AS NUMERIC (15,2)) "Beg Bal",<br />
CAST(0 AS NUMERIC (15,2)) "New Loans",CAST(0 AS NUMERIC (15,2)) "Deductions"<br />
from prtempdef E<br />
LEFT OUTER JOIN PRPEMPDEF_LOANHISTORY( :BEG_DATE , :BEG_DATE ,E.EMP_ID,E.DEF_ID) LH ON 1=1<br />
where E.def_id IN ( :DED_1 , :DED_2)<br />
AND LH.TRXTYPE='A'<br />
AND EXISTS (SELECT EMP_ID FROM PRTEMPDEFHST EDH WHERE EDH.DEF_ID=E.DEF_ID AND EDH.EMP_ID=E.EMP_ID)<br />
GROUP BY EMP_ID,DEF_ID<br />
</PRE></div>Intra>User