VIEW: APPS.IBY_TRANS_PCARD_V

Source

View Text - Preformatted

SELECT b.TransactionId, b.bepkey, b.PayeeId, b.BEPID, b.ECAppID, b.trxnMID, b.payerid, b.payerinstrid, pr.chname, pr.masked_cc_number pcnumber, pr.expirydate, pr.subtype, NVL(pr.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(t. amount, 2) amount, t.currencynamecode, t.acctno, t.refinfo, t.memo, t.TangibleId, c.AuthCode, c.ReferenceCode, c.OperationCode, c.InstrName, c.AuthType, c.AVScode, c.Acquirer, c.Auxmsg, c.ponumber, c.taxamount, c.shiptozip, c.shipfromzip, 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, 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 PR,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 = pr.instrid) AND(pr.accttypeid = y.accttypeid(+)) AND(b.instrtype = 'CREDITCARD') AND (NVL(pr.purchasecard_flag,'N') = 'Y') AND(pr.upgrade_addressid = l.location_id(+) ) AND(pr.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' ) ))

View Text - HTML Formatted

SELECT B.TRANSACTIONID
, B.BEPKEY
, B.PAYEEID
, B.BEPID
, B.ECAPPID
, B.TRXNMID
, B.PAYERID
, B.PAYERINSTRID
, PR.CHNAME
, PR.MASKED_CC_NUMBER PCNUMBER
, PR.EXPIRYDATE
, PR.SUBTYPE
, NVL(PR.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(T. AMOUNT
, 2) AMOUNT
, T.CURRENCYNAMECODE
, T.ACCTNO
, T.REFINFO
, T.MEMO
, T.TANGIBLEID
, C.AUTHCODE
, C.REFERENCECODE
, C.OPERATIONCODE
, C.INSTRNAME
, C.AUTHTYPE
, C.AVSCODE
, C.ACQUIRER
, C.AUXMSG
, C.PONUMBER
, C.TAXAMOUNT
, C.SHIPTOZIP
, C.SHIPFROMZIP
, 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
, 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 PR
, 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 = PR.INSTRID) AND(PR.ACCTTYPEID = Y.ACCTTYPEID(+)) AND(B.INSTRTYPE = 'CREDITCARD')
AND (NVL(PR.PURCHASECARD_FLAG
, 'N') = 'Y') AND(PR.UPGRADE_ADDRESSID = L.LOCATION_ID(+) ) AND(PR.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' ) ))