Support #7473
[SCP ID :##3861##] : SQL query for Internet Transaction Report
| Status: | Closed - End of life cycle | Start date: | May 24, 2017 | |
|---|---|---|---|---|
| Priority: | Normal | Due date: | ||
| Assignee: |  Zahir Abd Latif | % Done: | 100% | |
| Category: | MY RIB | Spent time: | - | |
| Target version: | - |
Description
Hi,
Kindly attend below request.
There is a request to extract 6 month data for the Internet Transaction Report for CIB and BIB. As the report generated daily, it is cumbersome for the user to retrive for 6 months.
Appreciate your assistance to provide us the query so we can retrieve from DB and present to user.
History
#1
Updated by Zahir Abd Latif about 7 years ago
- Status changed from New - Begin Life Cycle to Closed - End of life cycle
- Assignee changed from Hafizudin MD to Zahir Abd Latif
- % Done changed from 0 to 100
Jeng Wei, May 25, 2017 11:36 AM:-
Please execute sql below with starting and ending date.
select UPDATED_DATE,UPDATED_BY,TRNX_ID,
TRNX_TYPE,FROM_ACCOUNT_NO,FROM_CURRENCY,
TO_ACCOUNT_NO,TO_CURRENCY,AMOUNT,CHARGES,
GST_CHARGES,GST_GL_ACCOUNT,GST_ABSORB_AMOUNT,
TRNX_STATUS,EFFECTIVE_DATE,REMOTE_IP,PHX_REF_NO,
EPAY_REF_NO,PHX_REV_REF_NO,PHX_CHAR_REF_NO,PHX_CHARREV_REF_NO,
DETAILS,IB_SESSION_ID,RECEIPT_INDI,BILL_ACCT_ID,BILL_REF_ID,RIM_NO,
TRNX_CODE,ACTION_STATUS,TRNX_REASON,INITIATED_BY,VERIFIED_BY,VERIFIED_DATE,
VERIFIER_REMARKS,FROM_ACC_CRT_DT,CREATION_DATE,TO_ACC_CRT_DT
from BIB_COMPANY_TRANSACTION where TRNX_STATUS in ('Accepted', 'Successful', 'Unsuccessful')
and TRNX_CODE not in('billadd','billedit','billdelete','fttadd','fttedit','fttdelete','interadd','interedit','interdelete','intraadd','intraedit','intradelete')
and TRNX_TYPE not like('SI%') and UPDATED_DATE between to_date ('20170507','yyyymmdd') and to_date ('20170508','yyyymmdd' )
union all SELECT si.TRNX_DATE as UPDATED_DATE,ct.UPDATED_BY,si.TRNX_ID,ct.TRNX_TYPE,si.FROM_ACCOUNT_NO,
si.FROM_CURRENCY,si.TO_ACCOUNT_NO,si.TO_CURRENCY,decode(si.FOREIGN_AMOUNT, 0, si.LOCAL_AMOUNT, si.FOREIGN_AMOUNT)
as AMOUNT,' ' CHARGES,' ' GST_CHARGES,' ' GST_GL_ACCOUNT,' ' GST_ABSORB_AMOUNT,si.TRNX_STATUS,ct.EFFECTIVE_DATE,
' ' REMOTE_IP,si.PHX_REF_NO,ct.EPAY_REF_NO,si.PHX_CHARREV_REF_NO as PHX_REV_REF_NO,si.PHX_CHAR_REF_NO,
' ' PHX_CHARREV_REF_NO,ct.DETAILS,' ' IB_SESSION_ID,si.RECEIPT_INDI,ct.BILL_ACCT_ID,ct.BILL_REF_ID,ct.RIM_NO,si.TRNX_CODE,
' ' ACTION_STATUS,si.TRNX_REASON,ct.INITIATED_BY,ct.VERIFIED_BY,ct.VERIFIED_DATE,ct.VERIFIER_REMARKS,ct.FROM_ACC_CRT_DT,
ct.CREATION_DATE,ct.TO_ACC_CRT_DT FROM BIB_SI_HISTORY si LEFT JOIN BIB_COMPANY_TRANSACTION ct on si.SI_TRNX_ID = ct.TRNX_ID
where TRNX_DATE between to_date ('20170507','yyyymmdd') and to_date ('20170508','yyyymmdd' ) order by UPDATED_DATE