CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTE

 
COLUMN SRBRNCHCD HEADING 'SR Creator|Domicile|Branch|Code' FORMAT A30
COLUMN SR_NUM HEADING 'SR #|||' FORMAT A20
COLUMN TABLE2_ID HEADING 'Sales TABLE2 Id|||' FORMAT A20 
COLUMN CASH_AMNT HEADING '    Cash Amount ($)     |||' FORMAT $999,999,999,999,990.99
COLUMN CPF_AMNT HEADING '     CPF Amount ($)     |||' FORMAT $999,999,999,999,990.99
COLUMN CIF_NO HEADING 'CIF #|||' FORMAT A20
COLUMN CUST_NAME HEADING 'Customer Name|||' FORMAT A50
COLUMN SALES_PERSON HEADING 'Salesperson Name|||' FORMAT A50
COLUMN BRANCH_DESCRIPTION HEADING 'Domicile Branch|Description||' FORMAT A50 WORD_WRAPPED
COLUMN SR_STAT HEADING 'SR Status|||' FORMAT A25
COLUMN DUMMYSPACE2 HEADING '' FORMAT A6
COLUMN DUMMYSPACE HEADING '' FORMAT A2
COLUMN CASH_AMNT2 HEADING '     CPF Amount ($)     |||' FORMAT A24
COLUMN CPF_AMNT2 HEADING '     CPF Amount ($)     |||' FORMAT A24

SET NEWPAGE 0
SET TERMOUT OFF
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET NUMWIDTH 14
SET PAGESIZE 55
SET LINESIZE 343
SET UNDERLINE _
SET SCAN ON
SET ESCAPE \
SET VERIFY OFF
SET COLSEP ''

COLUMN TODAY NOPRINT NEW_VALUE DATEVAR
COLUMN RPT_GEN NOPRINT NEW_VALUE RPTGENVAL
COLUMN CASH_SUM NOPRINT NEW_VALUE CASH_TOTAL 
COLUMN CPF_SUM NOPRINT NEW_VALUE CPF_TOTAL  
COLUMN REC_CNT NOPRINT NEW_VALUE RECTOTAL 

DEFINE RPT_TITLE1 = "PARTIAL LOAN PREPAYMENTT MONTHLY SUMMARY REPORT" (CHAR)
DEFINE RPT_TITLE2 = "Report ID: XYZR2W26" (CHAR)
DEFINE RPT_TITLE3 = "For the month of " (CHAR)
DEFINE RPT_PAGING = "Page" (CHAR)
DEFINE RPT_LINE = "________________________________________________________________________________________________________________________________________________________________________________________________" (CHAR)
DEFINE RPT_DATE = "Report Date: " (CHAR)
DEFINE RPT_FTR1 = "Grand Amount" 
DEFINE RPT_FTR2 = "Grand Total # of records " 

TTITLE CENTER RPT_TITLE1 -
LEFT RPT_TITLE2 - 
RIGHT RPT_DATE  DATEVAR SKIP 1 -
CENTER RPT_TITLE3 RPTGENVAL SKIP 2 -
RPT_LINE  - 
RPT_LINE  SKIP 1 
BTITLE RPT_LINE  -
RPT_LINE  SKIP 1 -
RIGHT RPT_PAGING FORMAT 999 SQL.PNO SKIP 2 
REPFOOTER SKIP 1  RPT_FTR2 COL 129 RECTOTAL SKIP 1 -
RPT_FTR1 COL 77 CASH_TOTAL COL 103 CPF_TOTAL

WHENEVER OSERROR EXIT SQL.OSCODE
SPOOL /prodlib/XYZ/batch/working/ABCSRPARTIALLOANMTHLYRPT.txt
 
WHENEVER SQLERROR EXIT SQL.SQLCODE
SELECT 
LPAD(COUNT(*),6,' ') as REC_CNT,
TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS TODAY,
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'Month') AS RPT_GEN
FROM
S_TABLE1 A, S_TABLE2 B, S_TABLE3 C, S_TABLE4 D, S_TABLE5 E, S_TABLE4 F, S_TABLE5 G, S_TABLE3 H
WHERE
B.PR_TABLE3_ID = C.ROW_ID AND C.PR_EMP_ID = D.PAR_ROW_ID AND A.ROW_ID = B.X_ABC_ATTRIB_VAR07
AND A.OWNER_OU_ID = E.PAR_ROW_ID 
AND A.CST_CON_ID = F.PAR_ROW_ID
AND B.PR_TABLE3_ID = H.ROW_ID AND H.OU_ID = G.PAR_ROW_ID
AND TO_CHAR(A.CREATED, 'MMYYYY') =  TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MMYYYY')
AND B.CHANNEL_TYPE_CD = 'Service Request'
AND A.SR_AREA = 'Partial Loan Prepayment';

WHENEVER SQLERROR EXIT SQL.SQLCODE
SELECT LPAD(TO_CHAR(SUM (CASH_AMNT),'$999,999,999,999,990.99'),24,' ') AS CASH_SUM ,
SRAREA, LPAD(TO_CHAR(SUM(CPF_AMNT),'$999,999,999,999,990.99'),24,' ') AS CPF_SUM FROM 
(SELECT
E.DEPT_NUM AS SRBRNCHCD,
A.SR_NUM AS SR_NUM,
B.ROW_ID AS TABLE2_ID,
A.X_ABC_ATTRIB_NUM01 AS CASH_AMNT,
A.X_ABC_ATTRIB_NUM02 AS CPF_AMNT,
F.PERSON_UID AS CIF_NO,
F.LAST_NAME AS CUST_NAME,
D.LAST_NAME AS SALES_PERSON,
G.NAME AS BRANCH_DESCRIPTION,
A.SR_STAT_ID AS SR_STAT,
A.SR_AREA AS SRAREA
FROM
S_TABLE1 A, S_TABLE2 B, S_TABLE3 C, S_TABLE4 D, S_TABLE5 E, S_TABLE4 F, S_TABLE5 G, S_TABLE3 H
WHERE
B.PR_TABLE3_ID = C.ROW_ID AND C.PR_EMP_ID = D.PAR_ROW_ID AND A.ROW_ID = B.X_ABC_ATTRIB_VAR07
AND A.OWNER_OU_ID = E.PAR_ROW_ID 
AND A.CST_CON_ID = F.PAR_ROW_ID
AND B.PR_TABLE3_ID = H.ROW_ID AND H.OU_ID = G.PAR_ROW_ID
AND TO_CHAR(A.CREATED, 'MMYYYY') =  TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MMYYYY')
AND B.CHANNEL_TYPE_CD = 'Service Request'
AND A.SR_AREA = 'Partial Loan Prepayment')
GROUP BY SRAREA;

WHENEVER OSERROR EXIT SQL.OSCODE
SPOOL /prodlib/XYZ/batch/working/ABCSRPARTIALLOANMTHLYRPT.txt


BREAK ON SRBRNCHCD DUP SKIP 1
COMPUTE SUM LABEL 'Total amount' OF CASH_AMNT CPF_AMNT ON SRBRNCHCD 
COMPUTE NUMBER LABEL 'Total # of records: ' OF  DUMMYSPACE2 ON SRBRNCHCD
--COMPUTE SUM LABEL 'Grand Amount' OF  CASH_AMNT CPF_AMNT ON REPORT
--COMPUTE NUMBER LABEL 'Grand Total # of records' OF DUMMYSPACE2 ON REPORT

WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT
E.DEPT_NUM AS SRBRNCHCD,
''  DUMMYSPACE,
A.SR_NUM AS SR_NUM,
'' DUMMYSPACE,
B.ROW_ID AS TABLE2_ID,
'' DUMMYSPACE,
A.X_ABC_ATTRIB_NUM01 AS CASH_AMNT,
'' DUMMYSPACE,
A.X_ABC_ATTRIB_NUM02 AS CPF_AMNT,
'' DUMMYSPACE,
'' DUMMYSPACE2,
'' DUMMYSPACE,
F.PERSON_UID AS CIF_NO,
'' DUMMYSPACE,
F.LAST_NAME AS CUST_NAME, 
'' DUMMYSPACE,
D.LAST_NAME AS SALES_PERSON,
''  DUMMYSPACE,
G.NAME AS BRANCH_DESCRIPTION,
''  DUMMYSPACE, 
A.SR_STAT_ID AS SR_STAT,
'' DUMMYSPACE 
FROM
S_TABLE1 A, S_TABLE2 B, S_TABLE3 C, S_TABLE4 D, S_TABLE5 E, S_TABLE4 F, S_TABLE5 G, S_TABLE3 H
WHERE
B.PR_TABLE3_ID = C.ROW_ID AND C.PR_EMP_ID = D.PAR_ROW_ID AND A.ROW_ID = B.X_ABC_ATTRIB_VAR07
AND A.OWNER_OU_ID = E.PAR_ROW_ID 
AND A.CST_CON_ID = F.PAR_ROW_ID
AND B.PR_TABLE3_ID = H.ROW_ID AND H.OU_ID = G.PAR_ROW_ID
AND TO_CHAR(A.CREATED, 'MMYYYY') =  TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MMYYYY')
AND B.CHANNEL_TYPE_CD = 'Service Request'
AND A.SR_AREA = 'Partial Loan Prepayment'
ORDER BY SRBRNCHCD, SR_NUM, TABLE2_ID ASC;

CLEAR BREAK
CLEAR COMPUTE
REPFOOTER OFF

WHENEVER SQLERROR EXIT SQL.SQLCODE

SELECT
TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS TODAY,
TO_CHAR(ADD_MONTHS(SYSDATE,-1),'Month') AS RPT_GEN,
'' AS SRBRNCHCD,
''  AS DUMMYSPACE,
'' AS  DUMMYSPACE,
'' AS SR_NUM,
'' AS DUMMYSPACE,
'' AS TABLE2_ID,
'' AS DUMMYSPACE,
'' AS CASH_AMNT2,
'' AS DUMMYSPACE,
'' AS DUMMYSPACE,
(CHR(10)||CHR(10)||'No records extracted') AS CPF_AMNT2,
'' AS CIF_NO,
''  AS DUMMYSPACE,
'' AS CUST_NAME,
''  AS DUMMYSPACE,
''  AS DUMMYSPACE,
'' AS SALES_PERSON,
''  AS DUMMYSPACE,
''  AS DUMMYSPACE,
'' AS BRANCH_DESCRIPTION,
''  AS DUMMYSPACE,
''  AS DUMMYSPACE,
'' AS SR_STAT,
''  AS DUMMYSPACE,
''  AS DUMMYSPACE
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM S_TABLE1 A, S_TABLE2 B, S_TABLE3 C, S_TABLE4 D, S_TABLE5 E, 
S_TABLE4 F, S_TABLE5 G, S_TABLE3 H
WHERE
B.PR_TABLE3_ID = C.ROW_ID 
AND C.PR_EMP_ID = D.PAR_ROW_ID 
AND A.ROW_ID = B.X_ABC_ATTRIB_VAR07
AND A.OWNER_OU_ID = E.PAR_ROW_ID 
AND A.CST_CON_ID = F.PAR_ROW_ID
AND B.PR_TABLE3_ID = H.ROW_ID 
AND H.OU_ID = G.PAR_ROW_ID
AND TO_CHAR(A.CREATED, 'MMYYYY') =  TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MMYYYY')
AND B.CHANNEL_TYPE_CD = 'Service Request'
AND A.SR_AREA = 'Partial Loan Prepayment');


CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTE

UNDEFINE RPT_TITLE1 
UNDEFINE RPT_TITLE2 
UNDEFINE RPT_TITLE3 
UNDEFINE RPT_PAGING 
UNDEFINE RPT_LINE
UNDEFINE RPT_FTR1
UNDEFINE RPT_FTR2
UNDEFINE RTR_DATE

SET TRIMSPOOL OFF
SET SCAN OFF
SPOOL OFF

EXIT;