CUPOA (customer payment on acccount) search

I sort of answered my own question: OK, so I found a long way around by running a receivables analysis which I can dump into Excel, sort on prepayment field.  This gives no detail, except the amount, but it is a start.  I am not confident this provides all outstanding CUPOA’s, but, it allows me to group the overview of what is out there.  I will need to drill down on my own to gather detail.

Apps9 has “Mixed Payment/Cash Box Ledger Transaction Analysis” view which has a ‘Series ID’ field.  Not sure if this exists in Apps10?

Linda

Hi - thanks for the input.  This however only returns what CUPOA’s have been used in payments - not ones that have been created.

Voucher Row query where “Reference Series” = CUPOA?  

Thanks again for your help and insight, but, no return on that query.  I am thinking that since the system does not generate the CUPOA # until after the voucher is approved - that that is why in the voucher queries, the system would not recognize CUPOA as a series ID.  Since, until approval/posting, the series ID field is blank.

If you have access you could try an sql query 

select * from LEDGER_TRANSACTION where LEDGER_ITEM_series_id = 'CUPOA'

HI - where would I be getting the data to dump into Access?  is there an analysis which provides the headers you refer to?

There is a screen called “SQL Query Tool”.  If you run the above query (in the SQL statement box) it returns rows of data which can be RMB/Output to Excel.  

Apologies, I was referring to “Permissions” to the SQL Query Tool rather than the Microsoft programme when I said “Access”.

You or your admin could also create a Quick Report from the SQL statement

Hi Linda - I do have access to SQL and used the query you stated with no return.  I actually am getting an error on the expression.  I tried to look around to see if any of the other expressions work and have not found one yet.

When  looking at my past mixed payment vouchers, there is no indication there that an item is a CUPOA, the invoice ID field is blank.  I do see that the voucher does refer to the payment as “Payment in Advance” and to view the CUPOA # assigned, I have to go to the customer Open Items Ledger or customer Statement.

If there are any other suggestions as to what other statements I can use in the SQL, that would be great.  In the meantime, I will keep trying when time permits.

Patty :)

You didn’t say what the error was..?

Does the following error?

select * from LEDGER_TRANSACTION 

If the transaction is a Payment in Advance, there may be a different series (not CUPOA) but I’m not sure what that is (CUPIA?)

If the above query runs OK, try running it for a payment_id that is a known payment transaction you’re searching for

select * from LEDGER_TRANSACTION where payment_id = ‘XX’ 

Linda

Here is what I am seeing:

Did you try this?

select * from LEDGER_TRANSACTION

Are you running in something like PL/SQL developer or the IFS SQL Query Tool?

i am running SQL Query Tool.  It seems when I plug the word CUPOA in to the statement, i receive an error: regarding using “CUPOA” as a key word - it does not recognize.  

(running the ledger trans query gives way too much detail not relating to AR to sift through, therefore not a good way for me to go)

So, taking a step back as to remove additional statement detail  - I ran the query :  select * from IFSAPP.cust_open_item_snapshot 

This also gives a lot of erroneous detail, but, at least it is all AR.  From this, I am able to dump into Excel, which I can sort.  this does supply all open CUPOA’s that are currently on the books.  (It would be grand if there was a way to run a report or query to result in all CUPOA,s that are created, with creation and use history.  What I have will do for now.)

It is still along way around to where I want to go, but, I arrive fairly close to where I need to be. :)

I am seeing that even when running a mixed payment transaction report, the activity does not include CUPOA’s.  -not even as blank inv ID transactions - very strange.  its like they are in limbo till they post to the cust ledger.  even after mixed payment is approved and posted to the GL, and I go back and look at a previous voucher, there is no detail.   I need to dig more when time allows.

Thanks for your help and assistance.

When I run the query we only have CUPAY transactions.  If you run the following (which excludes CUPAY) you may identify the Series ID you are looking for and then be able to query for that transactions only.

select * from LEDGER_TRANSACTION where party_type = 'Customer' and series_id not in ('CUPAY')

Linda

Hi Adam - The “Re-post Customer Ledger Item” is a brilliant place to retrieve all open CUPOA’s - very simple/concise useful list. Thank you.  Although, it only returns Open CUPOA’s.  The “Bad Debt Management” seems to only show posted invoices.

Thanks a bunch,

Patty

Yes,  “Re-post Customer Ledger Item” is showing open items only. I do not know a window in IFS showing all payments on account in overview mode. I was always querying view IFSAPP.LEDGER_TRANSACTION_CU_QRY (or IFSAPP.LEDGER_TRANSACTION_SU_QRY for accounts payable) in SQL.