VIEW: APPS.IBY_TRANS_CORE_V

Source

View Text - Preformatted

SELECT b.TransactionId, b.bepkey, b.PayeeId, b.BEPID, b.ECAppID, b.trxnMID, b.payerid, b.payerinstrid, b.process_profile_code, cr.chname, cr.masked_cc_number ccnumber, cr.expirydate, NVL(cr.encrypted,'N'), y.accttype, b.ReqDate, b.ReqType, b.Status, b.SettleDate, b.UpdateDate, b.TrxnTYpeid, b.ErrorLocation, b.BEPCode, b.BEPMessage, b.BatchID , b.NeedsUpdt, b.destURL, b.PaymentMethodName, round(b.amount, 2) amount, b.currencynamecode, t.acctno, t.refinfo, t.memo, b.TangibleId, c.AuthCode, c.ReferenceCode, c.OperationCode, c.InstrName, c.AuthType, c.AVScode, c.Acquirer, c.Auxmsg, NVL(ul.address1,l.address1), NVL(ul.address2,l.address2), NVL(ul.address3,l.address3), NVL(ul.city,l.city), NVL(ul.state,l.state), NVL(ul.postal_code,l.postal_code) zip, NVL(ul.county,l.county), NVL(ul.county,l.country), b.org_id, b.nlslang, c.voiceauthflag, c.debit_network_code, c.surcharge_amount, c.proc_tracenumber, t.order_medium, t.eft_auth_method FROM HZ_LOCATIONS L,HZ_PARTY_SITES PS,HZ_PARTY_SITE_USES SU,IBY_ACCTTYPE Y,IBY_CREDITCARD CR,IBY_TANGIBLE T,IBY_TRXN_CORE C,IBY_TRXN_SUMMARIES_ALL B,HZ_LOCATIONS UL WHERE (b.trxnMID = c.trxnMID) AND (t.mtangibleid = b.mtangibleid) AND (b.payerinstrid = cr.instrid) AND (cr.accttypeid = y.accttypeid(+)) AND (cr.upgrade_addressid = l.location_id(+)) AND (cr.addressid = su.party_site_use_id(+)) AND (su.party_site_id = ps.party_site_id(+)) AND (ps.location_id = ul.location_id(+)) AND (b.bepid in (select distinct bepid from iby_pmtschemes where pmtschemename in ( 'PURCHASECARD' , 'SSL')))

View Text - HTML Formatted

SELECT B.TRANSACTIONID
, B.BEPKEY
, B.PAYEEID
, B.BEPID
, B.ECAPPID
, B.TRXNMID
, B.PAYERID
, B.PAYERINSTRID
, B.PROCESS_PROFILE_CODE
, CR.CHNAME
, CR.MASKED_CC_NUMBER CCNUMBER
, CR.EXPIRYDATE
, NVL(CR.ENCRYPTED
, 'N')
, Y.ACCTTYPE
, B.REQDATE
, B.REQTYPE
, B.STATUS
, B.SETTLEDATE
, B.UPDATEDATE
, B.TRXNTYPEID
, B.ERRORLOCATION
, B.BEPCODE
, B.BEPMESSAGE
, B.BATCHID
, B.NEEDSUPDT
, B.DESTURL
, B.PAYMENTMETHODNAME
, ROUND(B.AMOUNT
, 2) AMOUNT
, B.CURRENCYNAMECODE
, T.ACCTNO
, T.REFINFO
, T.MEMO
, B.TANGIBLEID
, C.AUTHCODE
, C.REFERENCECODE
, C.OPERATIONCODE
, C.INSTRNAME
, C.AUTHTYPE
, C.AVSCODE
, C.ACQUIRER
, C.AUXMSG
, NVL(UL.ADDRESS1
, L.ADDRESS1)
, NVL(UL.ADDRESS2
, L.ADDRESS2)
, NVL(UL.ADDRESS3
, L.ADDRESS3)
, NVL(UL.CITY
, L.CITY)
, NVL(UL.STATE
, L.STATE)
, NVL(UL.POSTAL_CODE
, L.POSTAL_CODE) ZIP
, NVL(UL.COUNTY
, L.COUNTY)
, NVL(UL.COUNTY
, L.COUNTRY)
, B.ORG_ID
, B.NLSLANG
, C.VOICEAUTHFLAG
, C.DEBIT_NETWORK_CODE
, C.SURCHARGE_AMOUNT
, C.PROC_TRACENUMBER
, T.ORDER_MEDIUM
, T.EFT_AUTH_METHOD
FROM HZ_LOCATIONS L
, HZ_PARTY_SITES PS
, HZ_PARTY_SITE_USES SU
, IBY_ACCTTYPE Y
, IBY_CREDITCARD CR
, IBY_TANGIBLE T
, IBY_TRXN_CORE C
, IBY_TRXN_SUMMARIES_ALL B
, HZ_LOCATIONS UL
WHERE (B.TRXNMID = C.TRXNMID)
AND (T.MTANGIBLEID = B.MTANGIBLEID)
AND (B.PAYERINSTRID = CR.INSTRID)
AND (CR.ACCTTYPEID = Y.ACCTTYPEID(+))
AND (CR.UPGRADE_ADDRESSID = L.LOCATION_ID(+))
AND (CR.ADDRESSID = SU.PARTY_SITE_USE_ID(+))
AND (SU.PARTY_SITE_ID = PS.PARTY_SITE_ID(+))
AND (PS.LOCATION_ID = UL.LOCATION_ID(+))
AND (B.BEPID IN (SELECT DISTINCT BEPID
FROM IBY_PMTSCHEMES
WHERE PMTSCHEMENAME IN ( 'PURCHASECARD'
, 'SSL')))