--UNCLEARED RECEIPT
SELECT EFFECTIVE_DATE,
SUM(NVL(ENTERED_DR, 0)) - SUM(NVL(ENTERED_CR, 0)) DRCR,
FP.BANK_ACCOUNT_ID,
fp.bank_account_name
FROM GL.GL_JE_LINES L,
GL.GL_JE_HEADERS H,
(SELECT * FROM ce.CE_BANK_ACCOUNTS --AP.AP_BANK_ACCOUNTS_ALL
) FP
WHERE H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = FP.ASSET_CODE_COMBINATION_ID
--AND FP.BANK_ACCOUNT_ID in
/* (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000) */-- :BANK_ACCOUNT_ID1
AND CODE_COMBINATION_ID IN
(SELECT Q1.ASSET_CODE_COMBINATION_ID
FROM ce.CE_BANK_ACCOUNTS Q1 --AP.AP_BANK_ACCOUNTS_ALL Q1
-- WHERE Q1.BANK_ACCOUNT_ID in
)
AND H.DEFAULT_EFFECTIVE_DATE <= '30-SEP-2013' --:MONTH1
AND H.LEDGER_ID in (1001,1002,2004) --:SET_BOOK_ID
AND (H.JE_SOURCE LIKE 'Manual' OR H.JE_SOURCE LIKE 'Spreadsheet')
AND (L.JE_HEADER_ID || L.JE_LINE_NUM NOT IN
(SELECT DISTINCT NVL(JE_HEADER_ID, 0) || REFERENCE_ID
FROM APPS.CE_STATEMENT_RECONCILS_ALL A, APPS.CE_STATEMENT_LINES B
WHERE A.STATEMENT_LINE_ID(+) = B.STATEMENT_LINE_ID
AND B.TRX_DATE <= '30-SEP-2013' --:MONTH1
AND A.STATUS_FLAG(+) = 'M'
AND A.CURRENT_RECORD_FLAG(+) = 'Y'
AND A.REFERENCE_TYPE = 'JE_LINE'))
GROUP BY EFFECTIVE_DATE, FP.BANK_ACCOUNT_ID, FP.BANK_ACCOUNT_NAME
UNION
SELECT DISTINCT E.EFFECTIVE_DATE,
SUM(NVL(ENTERED_DR, 0)) - SUM(NVL(ENTERED_CR, 0)) DRCR,
FP.BANK_ACCOUNT_ID,
fp.bank_account_name
FROM APPS.CE_STATEMENT_LINES A,
APPS.CE_STATEMENT_HEADERS B,
APPS.CE_STATEMENT_RECONCILS_ALL C,
GL.GL_JE_HEADERS D,
GL.GL_JE_LINES E,
(SELECT * FROM ce.CE_BANK_ACCOUNTS --AP.AP_BANK_ACCOUNTS_ALL
) FP
WHERE A.STATEMENT_HEADER_ID(+) = B.STATEMENT_HEADER_ID
AND C.JE_HEADER_ID = D.JE_HEADER_ID
AND C.STATEMENT_LINE_ID = A.STATEMENT_LINE_ID
AND E.JE_HEADER_ID = D.JE_HEADER_ID
AND E.JE_HEADER_ID = C.JE_HEADER_ID(+)
AND A.BANK_TRX_NUMBER = D.DESCRIPTION
AND A.BANK_TRX_NUMBER = E.DESCRIPTION
AND E.CODE_COMBINATION_ID = FP.ASSET_CODE_COMBINATION_ID
/* --AND FP.BANK_ACCOUNT_ID in (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000) */--:BANK_ACCOUNT_ID1
AND CODE_COMBINATION_ID in
(SELECT Q2.ASSET_CODE_COMBINATION_ID
FROM ce.CE_BANK_ACCOUNTS Q2 )--AP.AP_BANK_ACCOUNTS_ALL Q2
--WHERE Q2.BANK_ACCOUNT_ID in
/* (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000) --:BANK_ACCOUNT_ID1
)
--AND B.BANK_ACCOUNT_ID in (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000)*/ --:BANK_ACCOUNT_ID1
AND TO_DATE(TO_CHAR(D.DEFAULT_EFFECTIVE_DATE, 'DD-MON-RRRR')) <=
'31-AUG-2013' --:MONTH1
AND TO_DATE(TO_CHAR(B.STATEMENT_DATE, 'DD-MON-RRRR')) > '30-SEP-2013' --:MONTH1
AND D.LEDGER_ID in (1001,1002,2004) --:SET_BOOK_ID
GROUP BY E.EFFECTIVE_DATE, FP.BANK_ACCOUNT_ID,fp.bank_account_name
UNION
SELECT EFFECTIVE_DATE, DRCR, BANK_ACCOUNT_ID, to_char(null) bank_account_name
FROM (SELECT B.EFFECTIVE_DATE,
H.BANK_ACCOUNT_ID,
SUM (DECODE(B.TRX_TYPE, 'CREDIT', NVL(A.AMOUNT, 0), 0)) -
SUM(DECODE(B.TRX_TYPE, 'DEBIT', NVL(A.AMOUNT, 0), 0)) DRCR
FROM APPS.CE_STATEMENT_RECONCILS_ALL A,
APPS.CE_STATEMENT_LINES B,
CE.CE_STATEMENT_HEADERS H
WHERE A.STATEMENT_LINE_ID(+) = B.STATEMENT_LINE_ID
AND H.STATEMENT_HEADER_ID = B.STATEMENT_HEADER_ID
/* --AND H.BANK_ACCOUNT_ID in (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000)*/ --:BANK_ACCOUNT_ID1
AND B.TRX_DATE <= '30-SEP-2013' --:MONTH1
AND A.STATUS_FLAG(+) = 'M'
AND A.CURRENT_RECORD_FLAG(+) = 'Y'
AND A.REFERENCE_TYPE = 'JE_LINE'
AND B.STATUS = 'RECONCILED'
AND TRUNC(A.CREATION_DATE) > '30-SEP-2013' --MONTH1
AND NVL(JE_HEADER_ID, 0) || REFERENCE_ID IN
(SELECT J.JE_HEADER_ID || J.JE_LINE_NUM
FROM GL.GL_JE_LINES J,
GL.GL_JE_HEADERS K,
GL.GL_CODE_COMBINATIONS M
WHERE J.JE_HEADER_ID = K.JE_HEADER_ID
AND J.CODE_COMBINATION_ID = M.CODE_COMBINATION_ID
AND M.CODE_COMBINATION_ID in
(SELECT Q3.ASSET_CODE_COMBINATION_ID
FROM ce.CE_BANK_ACCOUNTS Q3 )--AP.AP_BANK_ACCOUNTS_ALL Q3
--WHERE Q3.BANK_ACCOUNT_ID in
/* (10065,10146,10006,10013,10008,10018,12001,10236,10022,10021,10410,13017,10010,10219,10043,13000,10406,10389,10088,10386,10385,10037,
13003,10409,10091,10099,10100,10115,10137,13004,10408,10012,10123,10054,10145,10009,10039,10144,10402,10403,10400,10401,10397,10396,
10395,10399,10398,10199,10014,13009,10414,11000,10036,10048,10025,10125,10157,10140,10094,10067,10064,10038,10053,10121,10114,10030,
13015,10405,13014,10423,10143,10011,10001,10256,12000,10420,10000) --BANK_ACCOUNT_ID1
AND Q3.ASSET_CODE_COMBINATION_ID IS NOT NULL)*/
AND (K.JE_SOURCE = 'Manual' OR K.JE_SOURCE = 'Spreadsheet')
AND J.EFFECTIVE_DATE <= '30-SEP-2013' --MONTH1
AND J.STATUS = 'P'
AND K.ACTUAL_FLAG = 'A'
AND K.LEDGER_ID in (1001,1002,2004) --SET_BOOK_ID
)
GROUP BY B.EFFECTIVE_DATE, H.BANK_ACCOUNT_ID)
--UNPOSTED PAYMENTS
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4), SUBSTR('30-SEP-2013'--:MONTH1
, 4), 'C', 'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
TO_DATE(NULL) RE_TRX_DATE,
TO_DATE(NULL) RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
--AP.AP_BANK_ACCOUNTS_ALL A,
ce.CE_BANK_ACCOUNTS A,
CE.CE_STATEMENT_HEADERS C
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'UNRECONCILED' AND b.trx_date <= '30-SEP-2013'--:MONTH1
)
--AND a.bank_account_id = 10386--:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '30-SEP-2013'--:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book_id
UNION
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4),
SUBSTR('30-SEP-2013' --:MONTH1
,
4),
'C',
'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
T.trx_date RE_TRX_DATE,
T.gl_date RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
-- AP.AP_BANK_ACCOUNTS_ALL A,
ce.CE_BANK_ACCOUNTS A,
CE.CE_STATEMENT_HEADERS C,
APPS.CE_RECONCILED_TRANSACTIONS_V t
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND t.bank_account_id = a.bank_account_id
AND b.statement_line_id = t.statement_line_id
AND c.statement_header_id = t.statement_header_id
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'RECONCILED' AND B.TRX_DATE <= '30-SEP-2013' --:MONTH1
AND t.gl_date > '30-SEP-2013' --:MONTH1
AND T.TRX_DATE > '30-SEP-2013' --:MONTH1
)
--AND a.bank_account_id = 10386 --:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '30-SEP-2013' --:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book_id
UNION
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4),
SUBSTR('30-SEP-2013' --:MONTH1
,
4),
'C',
'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
T.trx_date RE_TRX_DATE,
T.gl_date RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
-- AP.AP_BANK_ACCOUNTS_ALL A,
ce.CE_BANK_ACCOUNTS A,
CE.CE_STATEMENT_HEADERS C,
APPS.CE_RECONCILED_TRANSACTIONS_V t
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND t.bank_account_id = a.bank_account_id
AND b.statement_line_id = t.statement_line_id
AND c.statement_header_id = t.statement_header_id
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'RECONCILED' AND B.TRX_DATE <= '30-SEP-2013' --:MONTH1
AND t.gl_date <= '30-SEP-2013' --:MONTH1
AND T.TRX_DATE <= '30-SEP-2013' --:MONTH1
AND TRUNC(T.CREATION_DATE) > '30-SEP-2013' --:MONTH1
)
--AND a.bank_account_id = 10386 --:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '30-SEP-2013' --:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book
--UNPOSTED RECEIPT
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4),
SUBSTR('31-JAN-2013' --:MONTH1
,
4),
'C',
'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
TO_DATE(NULL) RE_TRX_DATE,
TO_DATE(NULL) RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
--AP.AP_BANK_ACCOUNTS_ALL A,
ce.ce_bank_accounts A,
CE.CE_STATEMENT_HEADERS C
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'UNRECONCILED' AND b.trx_date <= '28-FEB-2013' --:MONTH1
)
--AND A.BANK_ACCOUNT_ID = 10386 --:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '28-FEB-2013' --:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book_id
UNION
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4),
SUBSTR('28-FEB-2013' --:MONTH1
,
4),
'C',
'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
T.trx_date RE_TRX_DATE,
T.gl_date RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
-- AP.AP_BANK_ACCOUNTS_ALL A,
ce.CE_BANK_ACCOUNTS A,
CE.CE_STATEMENT_HEADERS C,
APPS.CE_RECONCILED_TRANSACTIONS_V t
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND t.bank_account_id = a.bank_account_id
AND b.statement_line_id = t.statement_line_id
AND c.statement_header_id = t.statement_header_id
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'RECONCILED' AND B.TRX_DATE <= '28-FEB-2013' --:MONTH1
AND t.gl_date > '28-FEB-2013' --:MONTH1
AND T.TRX_DATE > '28-FEB-2013' --:MONTH1
)
--AND A.BANK_ACCOUNT_ID = 10386 --:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '28-FEB-2013' --:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book_id
UNION
SELECT A.BANK_ACCOUNT_NAME BANK,
a.bank_account_id,
DECODE(SUBSTR(C.STATEMENT_DATE, 4),
SUBSTR('28-FEB-2013' --:MONTH1
,
4),
'C',
'P') INDICATOR,
C.STATEMENT_DATE,
B.TRX_DATE,
T.trx_date RE_TRX_DATE,
T.gl_date RE_GL_DATE,
B.CREATION_DATE,
TO_DATE('') RA_CREATION,
BANK_TRX_NUMBER,
B.STATUS,
TRX_CODE_ID,
DECODE(B.TRX_TYPE, 'DEBIT', B.AMOUNT, NULL) DR,
DECODE(B.TRX_TYPE, 'CREDIT', B.AMOUNT, NULL) CR,
B.LINE_NUMBER
FROM CE.CE_STATEMENT_LINES B,
-- AP.AP_BANK_ACCOUNTS_ALL A,
ce.CE_BANK_ACCOUNTS A,
CE.CE_STATEMENT_HEADERS C,
APPS.CE_RECONCILED_TRANSACTIONS_V t
WHERE A.BANK_ACCOUNT_ID = C.BANK_ACCOUNT_ID
AND t.bank_account_id = a.bank_account_id
AND b.statement_line_id = t.statement_line_id
AND c.statement_header_id = t.statement_header_id
AND B.STATEMENT_HEADER_ID = C.STATEMENT_HEADER_ID
AND (B.STATUS = 'RECONCILED' AND B.TRX_DATE <= '28-FEB-2013' --:MONTH1
AND t.gl_date <= '28-FEB-2013' --:MONTH1
AND T.TRX_DATE <= '28-FEB-2013' --:MONTH1
AND TRUNC(T.CREATION_DATE) > '28-FEB-2013' -- :MONTH1
)
--AND A.BANK_ACCOUNT_ID = 10386 --:BANK_ACCOUNT_ID1
AND C.STATEMENT_DATE <= '28-FEB-2013' --:MONTH1
-- AND A.SET_OF_BOOKS_ID = :set_book_id
-- unpresented cheques
SELECT DISTINCT 'Unprsented_cheques' cate,
nvl(a.bank_account_id, a.ce_bank_acct_use_id) bank_account_id,
a.bank_account_name,
A.CHECK_DATE P_DATE,
A.AMOUNT CR
FROM ap.AP_CHECKS_all A, AP.AP_INVOICES_ALL B, ap.AP_SUPPLIERS C
WHERE A.VENDOR_ID = C.VENDOR_ID
AND B.VENDOR_ID = C.VENDOR_ID
AND A.AMOUNT <> 0
AND (A.STATUS_LOOKUP_CODE LIKE 'N%' OR A.CLEARED_DATE > '30-SEP-2013' --:MONTH1
OR (A.STATUS_LOOKUP_CODE LIKE 'V%' AND
A.CHECK_ID IN (SELECT CHECK_ID
FROM APPS.T4
WHERE PDATE <= '30-SEP-2013' --:MONTH1
AND VDATE > '30-SEP-2013' -- :MONTH1
)))
AND A.CHECK_DATE <= '30-SEP-2013' --:MONTH1
--AND (a.bank_account_id = 10386 or a.ce_bank_acct_use_id = 10386 /*:bank_account_id1*/)
AND B.SET_OF_BOOKS_ID = 1001 --:set_book_id