SMS

select GL.SMS_NO Mobile_No,'Dear Customer'||'  '||'"'||M.SLS_PM_SLCODE||'"'||'  '||'Your Cash Payment
of TK'||' '||'**'||M.SLS_PM_AMOUNT||'**'||' '||' '||'On'||' '||M.SLS_PM_MDATE||' '||
'has been received and Credited to your account. Thanking You, RAK Paints.' Message
from SLS_PAY_MASTER M, GL_SL_CODE GL
where M.SLS_PM_SLCODE=GL.SLCODE
AND SLS_PM_MDATE=:MDATE
and SLS_PM_TRCODE='CR'
AND SLS_PM_AMOUNT>0
UNION ALL
select GL.SMS_NO Mobile_No,'Dear Customer'||' '||'"'||M.SLS_PM_SLCODE||'"'||' '||'Thank you for your
cheque '||' '||'of TK'||' '||'**'||M.SLS_PM_AMOUNT||'** ,'||' '||
'the same will be credited to your account subject to realization. RAK Paints.' Message
from SLS_PAY_MASTER M, GL_SL_CODE GL
where M.SLS_PM_SLCODE=GL.SLCODE
AND M.SLS_PM_MDATE=:MDATE
and M.SLS_PM_TRCODE ='BR'
AND SLS_PM_AMOUNT>0
UNION ALL
select GL.SMS_NO Mobile_No,'Dear Customer'||' '||'"'||M.SLS_PM_SLCODE||'"'||' '||'Your cheque No'||'
'||M.SLS_PM_INSTRNO||' '||
'has been dishonored. Please pay the due amount of TK'||'**'|| SLS_PM_AMOUNT ||'**'||'immediately.
Thanking You. RAK Paints.' Message
from SLS_PAY_MASTER M, GL_SL_CODE GL
where M.SLS_PM_SLCODE=GL.SLCODE
AND M.SLS_PM_DISDT=:MDATE
and M.SLS_PM_DISTRCODE='DCH'
AND SLS_PM_TRCODE='BR'
AND M.SLS_PM_RLOC BETWEEN 6 AND 13
UNION ALL
select GL.SMS_NO Mobile_No,'Dear Customer'||' '||M.INV_ISM_SLCODE||' '||'Your Invoice no'||' '||
INV_ISM_TRCODE||'-'
||INV_ISM_SERIALNO||'/'||INV_ISM_FYEAR ||' '||'amount TK'||'**'||
NVL(INV_ISM_GROSS,0)||'**'||' '||'will be matured on'||' '||INV_ISM_DUEDT ||'. '||
'Please arrange to pay accordingly.If paid plz Ignore.'  Message
from INV_ISSUE_MASTER M, GL_SL_CODE GL
where M.INV_ISM_SLCODE=GL.SLCODE
AND GL.SLTYPE<>'PROJECT'
AND M.INV_ISM_DUEDT=TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YY'))+3
and GL.SMS_NO is not null
UNION ALL
select GL.SMS_NO Mobile_No,'You have to collect amount'||'**'||NVL(INV_ISM_GROSS,0)||'**'||' '||'from'
||' '||SUBSTR(GL_CUS.SLNAME,1,30)||' '||'against invoice no'||' '||INV_ISM_TRCODE||'-'
||INV_ISM_SERIALNO||'/'||INV_ISM_FYEAR ||' '||'will be matured on'||' '||INV_ISM_DUEDT||
'.If paid plz Ignore.'  Message
from INV_ISSUE_MASTER M, GL_SL_CODE GL_CUS, GL_SL_CODE GL
where M.INV_ISM_EMP=GL.SLCODE
AND M.INV_ISM_SLCODE=GL_CUS.SLCODE
AND M.INV_ISM_DUEDT=TO_DATE(TO_CHAR(SYSDATE,'DD-MON-YY'))+3
and GL.SMS_NO is not null



----------------------------EVERY 15 DAYS------------------------

select GL.SMS_NO Mobile_No,'Dear Customer'||' '||M.SLCD||'. '||
'Your total due' ||' '||'TK'||' '||'**'||DUEAMT||'**.'||' '|| 'Above 365 days TK'||' '
||'**'|| D_365||'**'||' '||'above 120 days TK'||' '||'**'||D_180||'**.'||'Please pay Overdue
Immediately.' Message
FROM(
SELECT SLCD, MAX(ABS(DUEAMT)) DUEAMT,SUM(D_120) D_180,SUM(D_365) D_365
FROM(
select SLCD,DUEAMT,AMT D_120, 0 AS D_365
from VW_CUS_AGEING
where NO_DAYS > 120 and NO_DAYS  <= 365
UNION ALL
select SLCD,DUEAMT,0 AS D_120, AMT D_365
from VW_CUS_AGEING
where  NO_DAYS > 365
)
GROUP BY SLCD) M, GL_SL_CODE GL
where M.SLCD=GL.SLCODE
and GL.SMS_NO is not null
and SLTYPE<>'PROJECT'