IBY_TRANS_PCARD

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
TRANSACTIONID BEPKEY PAYEEID BEPID ECAPPID TRXNMID PAYERID PAYERINSTRID CHNAME PCNUMBER EXPIRYDATE SUBTYPE ENCRYPTED ACCTTYPE REQDATE REQTYPE STATUS SETTLEDATE UPDATEDATE TRXNTYPEID ERRORLOCATION BEPCODE BEPMESSAGE BATCHID NEEDSUPDT DESTURL PAYMENTMETHODNAME AMOUNT CURRENCYNAMECODE ACCTNO REFINFO MEMO TANGIBLEID AUTHCODE REFERENCECODE OPERATIONCODE INSTRNAME AUTHTYPE AVSCODE ACQUIRER AUXMSG PONUMBER TAXAMOUNT SHIPTOZIP SHIPFROMZIP ADDRESS1 ADDRESS2 ADDRESS3 CITY STATE ZIP COUNTY COUNTRY ORG_ID NLSLANG VOICEAUTHFLAG ORDER_MEDIUM EFT_AUTH_METHOD

Name

TRANSACTIONID

BEPKEY

PAYEEID

BEPID

ECAPPID

TRXNMID

PAYERID

PAYERINSTRID

CHNAME

PCNUMBER

EXPIRYDATE

SUBTYPE

ENCRYPTED

ACCTTYPE

REQDATE

REQTYPE

STATUS

SETTLEDATE

UPDATEDATE

TRXNTYPEID

ERRORLOCATION

BEPCODE

BEPMESSAGE

BATCHID

NEEDSUPDT

DESTURL

PAYMENTMETHODNAME

AMOUNT

CURRENCYNAMECODE

ACCTNO

REFINFO

MEMO

TANGIBLEID

AUTHCODE

REFERENCECODE

OPERATIONCODE

INSTRNAME

AUTHTYPE

AVSCODE

ACQUIRER

AUXMSG

PONUMBER

TAXAMOUNT

SHIPTOZIP

SHIPFROMZIP

ADDRESS1

ADDRESS2

ADDRESS3

CITY

STATE

ZIP

COUNTY

COUNTRY

ORG_ID

NLSLANG

VOICEAUTHFLAG

ORDER_MEDIUM

EFT_AUTH_METHOD

Query

SQL_Statement
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 = 'PURCHASECARD') 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 = 'PURCHASECARD'))

SQL_Statement

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 = 'PURCHASECARD') 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 = 'PURCHASECARD'))