AP Payment Card Transactions

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Payment Card Transactions Report
Short Name: APTRXFEE_XML
DB package:
select ibc.masked_card_number  CARD_NUMBER, 
       apc.check_id PAYMENT_NUMBER, 
       apc.check_date PAYMENT_DATE, 
       apc.vendor_name SUPPLIER_NAME, 
       apc.amount PAYMENT_AMOUNT, 
       decode(apline.FEE_AMOUNT,null,0, apline.fee_amount) FEE, 
       apc.currency_code CURRENCY, 
       iby_creditcard_pkg.Mask_Card_Number(apc.PAYCARD_AUTHORIZATION_NUMBER) AUTHORIZATION_CODE    
from 
  ( 
  (ap_paycard_trx_fee_header aphead inner join ap_paycard_trx_fee_lines apline on aphead.trx_fee_header_id = apline.trx_fee_header_id) 
  right outer join 
  (
	(
		(
			(ap_card_programs_all approg inner join ap_card_profiles_all approf on approg.CARD_TYPE_LOOKUP_CODE ='PAYMENTCARD' and approg.card_program_id = approf.card_program_id ) 
			inner join  
			ap_cards_all apcard on apcard.card_program_id = approg.card_program_id and apcard.profile_id = approf.profile_id 
		) inner join 
   		ap_checks_all apc on apcard.paycard_reference_id = apc.paycard_reference_id 
  	) inner join 
	IBY_PAYMENTCARD ibc on  apcard.paycard_reference_id = ibc.instrid 
   ) 
   on apc.check_date  between apline.from_date and apline.to_date 
   and aphead.org_id = approg.org_id 
   and aphead.card_issuer_id = approg.vendor_id 
   and aphead.card_issuer_site_id = approg.vendor_site_id 
   and apc.currency_code = aphead.currency_code 
 ) where apc.check_date between :P_PERIOD_FROM and nvl(:P_PERIOD_TO,to_date(to_char('31-DEC-2949'),'dd-mon-rrrr')) 
   and apc.paycard_reference_id = nvl(:P_ACCOUNT_NUMBER, apc.paycard_reference_id ) and apc.void_date is null
and apc.org_id = nvl(:P_SECURITY_ID_INT_1,apc.org_id)
order by ibc.masked_card_number, apc.currency_code, apc.check_date asc
Parameter Name SQL text Validation
Payment Card Number
 
LOV Oracle
P_PERIOD_TO
 
Date
P_PERIOD_FROM
 
Date
P_LEDGER_SET
 
LOV Oracle
P_SECURITY_ID_INT_1
 
LOV Oracle