EAP: Archive Selection SQL for Voucher is using double quotes

(Doc ID 2179913.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

PeopleSoft Enterprise FIN Payables - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On the Archive Data To History > View Details page, the Archive Selection SQL for VOUCHER appears to be coded incorrectly for the following criteria:

  OR ( C.VAT_ENTITY <> ''' '''
  AND C.VAT_DISTRIB_STATUS = '''U''')
  OR ( C.ASSET_FLG = '''Y'''
  AND C.AM_DISTRIB_STATUS IN ('N','M'))
  OR ( C.PROJECT_ID <> ''' '''

See double quotes around the single quotes. As a result, no data will be returned.

STEPS
1. On the Archive Data To History page, select the following criteria:

Archive Template = AP_VCHR
Selective Query = AP_ARCHIVE_VOUCHER_ALL

2. Click on the Define Binds hyperlink
3. Specify the current Date
4. Click on the View Details hyperlink
5. On the Archive Run Control Details page, click on the View SQL hyperlink for Archiving Record = VOUCHER
6. Below is the Archive Selection SQL for VOUCHER:

%InsertSelectWithLongs(AP_ARC_VOUCH_H, VOUCHER A, AP_ARCH_VCH_VW M, PSARCH_ID
= 'AP_VCHR', PSARCH_BATCHNUM = 0) FROM PS_VOUCHER A, PS_AP_ARCH_VCH_VW M
  WHERE ( A.BUSINESS_UNIT = M.BUSINESS_UNIT
  AND A.VOUCHER_ID = M.VOUCHER_ID
  AND A.INVOICE_DT < ÚTEIN('2016-06-01')
  AND A.IN_PROCESS_FLG = 'N'
  AND ( A.ENTRY_STATUS = 'X'
  OR ( A.MATCH_STATUS_VCHR IN ('M','O','C','N')
  AND ( A.VOUCHER_ID_RELATED = ' '
  OR EXISTS (SELECT 'X'
  FROM PS_VOUCHER B
  WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT
  AND B.VOUCHER_ID = A.VOUCHER_ID_RELATED
  AND B.IN_PROCESS_FLG = 'N'
  AND B.INVOICE_DT < ÚTEIN('2016-06-01')
  AND (( B.ENTRY_STATUS = 'D')
  OR ( B.MATCH_STATUS_VCHR IN ('M','O','C','N')
  AND B.POST_STATUS_AP = 'P'))))
  AND NOT EXISTS (SELECT 'X'
  FROM PS_VOUCHER L
  WHERE L.BUSINESS_UNIT = A.BUSINESS_UNIT
  AND L.VOUCHER_ID = A.VOUCHER_ID_RELATED
  AND ( L.IN_PROCESS_FLG = 'Y'
  OR L.INVOICE_DT > ÚTEIN('2016-06-01')
  OR L.MATCH_STATUS_VCHR NOT IN ('C','M','N','O')
  OR L.POST_STATUS_AP <> 'P'))
  AND NOT EXISTS (SELECT 'X'
  FROM PS_VCHR_ACCTG_LINE C
  WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND (( A.VOUCHER_ID = C.VOUCHER_ID)
  OR ( A.VOUCHER_ID_RELATED = C.VOUCHER_ID))
  AND (( C.JOURNAL_DATE > ÚTEIN('2016-06-01'))
  OR ( C.GL_DISTRIB_STATUS NOT IN ('D','I'))
  OR ( C.VAT_ENTITY <> ''' '''
  AND C.VAT_DISTRIB_STATUS = '''U''')
  OR ( C.ASSET_FLG = '''Y'''
  AND C.AM_DISTRIB_STATUS IN ('N','M'))
  OR ( C.PROJECT_ID <> ''' '''
  AND C.PC_DISTRIB_STATUS NOT IN ('D','I')))))))

7. See double quotes in the section below

  OR ( C.VAT_ENTITY <> ''' '''
  AND C.VAT_DISTRIB_STATUS = '''U''')
  OR ( C.ASSET_FLG = '''Y'''
  AND C.AM_DISTRIB_STATUS IN ('N','M'))
  OR ( C.PROJECT_ID <> ''' '''

Changes

 

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms