My Oracle Support Banner

AP: In the Voucher build process, APVEDTVLDT is not using effdt logic for Vendor (Doc ID 649880.1)

Last updated on MAY 18, 2021

Applies to:

PeopleSoft Enterprise FIN Payables - Version 8.8 and later
Information in this document applies to any platform.

Information in this document applies to any platform.



This document was previously published as Customer Connection Solution 201021121

Symptoms

8.8 SP1: The sql in APVEDTVLDT.VLDTpym.Step03, does not incorporate effective dated logic when choosing the vendor address which causes duplicate inserts.

Sql in step:
INSERT INTO %Table(payment_tbl_vw) (bank_setid
 , bank_cd
 , bank_acct_key
 , pymnt_id
 , pymnt_id_ref
 , bank_account_num
 , remit_setid
 , remit_vendor
 , name1
 , name2
 , address1
 , address2
 , address3
 , address4
 , city
 , in_city_limit
 , county
 , state
 , geo_code
 , postal
 , country
 , pymnt_dt
 , creation_dt
 , oprid
 , pymnt_amt
 , currency_pymnt
 , pymnt_method
 , pymnt_handling_cd
 , pymnt_status
 , cancel_action
 , accounting_dt
 , post_status_ap
 , process_instance
 , pay_cycle
 , pay_cycle_seq_num
 , recon_type
 , recon_cycle_nbr
 , eft_pymnt_fmt_cd
 , eft_trans_handling
 , dfi_id_qual
 , dfi_id_num
 , rep_rtng_num
 , remit_bank_account
 , form_nbr_confirmed
 , in_process_flg
 , positive_pay_stat
 , micr_line
 , emplid
 , reconcile_oprid
 , recon_status
 , bnk_id_nbr
 , branch_id
 , check_digit
 , remit_branch_id
 , remit_check_digit
 , cash_cleared_flg
 , pymnt_type
 , num1
 , num2
 , house_type
 , addr_field1
 , addr_field2
 , addr_field3
 , vndr_loc
 , preferred_language
 , doc_type
 , doc_seq_nbr
 , doc_seq_status
 , dft_status
 , source_txn
 , branch_name
 , lc_id
 , business_unit_gl
 , dft_master_id
 , dft_control_id
 , dft_stmp_tax_amt
 , dft_drawee
 , dft_wo_acct
 , dft_opt_flg
 , dft_approve_flag
 , dft_type
 , value_dt
 , user_pymnt_char1
 , user_pymnt_char2
 , user_pymnt_dec
 , user_pymnt_num
 , schedule_id
 , dft_wo_altacct
 , cancel_post_status
 , treasury_pmt_nbr
 , single_pymnt_flg
 , ipac_pymnt_flg
 , recon_run_id
 , bank_acct_rvl_amt)
 SELECT %Bind(bank_setid)
 ,%Bind(bank_cd)
 ,%Bind(bank_acct_key)
 ,%Bind(pymnt_id)
 ,%Bind(pymnt_id_ref)
 ,%Bind(bank_account_num)
 ,XREF.remit_setid
 ,XREF.remit_vendor
 ,VND.name1
 ,VND.name2
 ,ADDR.address1
 ,ADDR.address2
 ,ADDR.address3
 ,ADDR.address4
 ,ADDR.city
 ,ADDR.in_city_limit
 ,ADDR.county
 ,ADDR.state
 ,ADDR.geo_code
 ,ADDR.postal
 ,ADDR.country
 ,VCHR.INVOICE_DT
 ,%CurrentDateIn
 ,VCHR.OPRID
 ,XREF.PYMNT_GROSS_AMT
 ,XREF.currency_pymnt
 ,XREF.pymnt_method
 ,XREF.pymnt_handling_cd
 ,'P'
 ,'N'
 ,VCHR.ACCOUNTING_DT
 ,'U'
 ,%ProcessInstance
 ,' '
 ,0
 ,'U'
 ,0
 ,' '
 ,' '
 ,' '
 ,' '
 ,' '
 ,' '
 ,'Y'
 ,'N'
 ,'N'
 ,%Bind(micr_line)
 ,' '
 ,' '
 ,'UNR'
 ,%Bind(bnk_id_nbr)
 ,%Bind(branch_id)
 ,%Bind(check_digit)
 ,' '
 ,' '
 ,'N'
 ,'M'
 ,ADDR.num1
 ,ADDR.num2
 ,ADDR.house_type
 ,ADDR.addr_field1
 ,ADDR.addr_field2
 ,ADDR.addr_field3
 ,XREF.vndr_loc
 ,LOC.PREFERRED_LANGUAGE
 ,' '
 ,' '
 ,' '
 ,'N'
 ,'VCHR'
 ,%Bind(branch_name)
 ,' '
 ,%Bind(business_unit_gl)
 ,' '
 ,' '
 ,0
 ,' '
 ,' '
 ,'N'
 ,'N'
 ,' '
 ,VCHR.INVOICE_DT
 ,' '
 ,' '
 ,0
 ,0
 ,' '
 ,' '
 ,'N'
 ,0
 ,'N'
 ,'N'
 , ' '
 , 0
  FROM %Table(VED_PVX_TAO) XREF
  , %Table(VED_VCH_TAO) VCHR
  , %Table(VENDOR) VND
  , %Table(VENDOR_ADDR) ADDR
  , %Table(VENDOR_LOC) LOC
 WHERE XREF.PROCESS_INSTANCE = %ProcessInstance
   AND XREF.BUSINESS_UNIT = %Bind(BUSINESS_UNIT)
   AND XREF.VOUCHER_ID = %Bind(VOUCHER_ID)
   AND XREF.PYMNT_CNT = %Bind(PYMNT_CNT)
   AND VCHR.PROCESS_INSTANCE = %ProcessInstance
   AND VCHR.BUSINESS_UNIT = XREF.BUSINESS_UNIT
   AND VCHR.VOUCHER_ID = XREF.VOUCHER_ID
   AND VND.SETID = XREF.REMIT_SETID
   AND VND.VENDOR_ID = XREF.REMIT_VENDOR
   AND ADDR.SETID = VND.SETID
   AND ADDR.VENDOR_ID = VND.VENDOR_ID
   AND ADDR.ADDRESS_SEQ_NUM = XREF.REMIT_ADDR_SEQ_NUM
   AND LOC.SETID = VND.SETID
   AND LOC.VENDOR_ID = VND.VENDOR_ID
   AND LOC.VNDR_LOC = XREF.VNDR_LOC
   AND LOC.EFFDT = (
 SELECT MAX(LOC2.EFFDT)
  FROM %Table(VENDOR_LOC) LOC2
 WHERE LOC2.SETID = LOC.SETID
   AND LOC2.VENDOR_ID = LOC.VENDOR_ID
   AND LOC2.VNDR_LOC = LOC.VNDR_LOC
   AND LOC2.EFFDT <= %CurrentDateIn)
   AND LOC.EFF_STATUS = 'A'

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.