Import Invoice and applied payments without Autolockbox
Date: Tue, 13 Jul 1999 08:57:57 -0700 (PDT)
From: Siddharth Shah shahsd@yahoo.com
Subject: AR Experts!
Hi All,
I am facing an issue at a client site. My client is in
Middle East (Dubai) and they do not use the Autolock
box feature.
We are trying to bring invoices and their applied
payments from third party system into Oracle AR. What
is the best workaround for this, keeping in mind that
they do not use the lockbox feature. Can this be done
through front end or does it seem to be a data
conversion issue.
Thanks in advance,
Siddharth Shah
Date: Tue, 13 Jul 1999 09:19:53 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: AR Experts!
Why can't you use the auto-lockbox feature? It is really just
an interface table like all of the other interfaces. The data
that goes into the interface table doesn't have to be from a
"lockbox". You can write your own program to load data
from the third party system into the ar_payments_interface_all
table and then run the autolockbox concurrant program to
load the data. There is nothing magical about autolockbox
other than it is an interface table.
Date: Tue, 13 Jul 1999 09:42:30 -0700 (PDT)
From: Andy Schindler aschindl@yahoo.com
Subject: Re: AR Experts!
Siddharth, just because your client doesn't use an
actual lockbox (defined as having all checks sent to a
bank for data entry and immediate deposit) does not
mean that you can't use Oracle's lock box interface
for your project. Lockbox is Oracle's supported
method of importing payments.
Since you are exporting "associated" payments from a
legacy system, your biggest hurdle with lockbox is
handled. You know what invoice to apply the payment
to.
If this helps with discussions with your clients, to
import AP invoices you must populate the XpenseXpress
tables and run the invoice import. This does not mean
that the client must use XpenseXpress for manual entry
of expense reports. Again, it is Oracle's supported
method of importing AP invoices.
Good luck.
Andy Schindler
NRC, Inc
Importing AR invoices and check payments applied
Date: Mon, 12 Jul 1999 08:57:34 -0700 (PDT)
From: Siddharth Shah shahsd@yahoo.com
To: oraapps-l@cpa.qc.ca
Subject: AR Experts!
Hi AR Experts!
I am facing an issue at a client site, we are trying
to import invoices from third party system into Oracle
AR thru AutoInvoice. I am wondering if this import
also brings along all the check payments that were
applied against the invoices that are being imported.
If not, what is a good workaround for this kind of
situation?
Thanks in advance,
Siddharth Shah
Date: Mon, 12 Jul 1999 09:07:32 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: AR Experts!
You can bring in the check payments via the AutoLockBox
interface.
Date: Mon, 12 Jul 1999 09:16:32 -0700 (PDT)
From: Siddharth Shah shahsd@yahoo.com
Subject: RE: AR Experts!
Thanks you Orth for your prompt reply.
Siddharth
Lockbox not reading file
Date: Tue, 13 Jul 1999 13:21:06 PDT
From: "jyothi satish" jyothikir@hotmail.com
Subject: lock box process URGENT
hi
i am running lock box process with orcle's std control file and
transmission format CONVERT
no matter what dat file/control file i give in the request form
i am getting the same log and rep
it just shows what ever i am entering and its not reading the files at all
had anybody encountered this type of problem
pl mail me back with some suggestions
do i have to apply any patch?
Thanks in advance
satish
Date: Tue, 13 Jul 1999 21:59:01 -0400
From: "Chadda, Alok" achadda@kpmg.com
To: "'jyothikir@hotmail.com'" jyothikir@hotmail.com
Cc: "'oraapps-l@cpa.qc.ca'" oraapps-l@cpa.qc.ca
Subject: RE:lock box process URGENT
Satish,
Are you getting any erorrs in the log or lockbox run report? Is interface
program reading your data? Even though it is same data again and again.
Where are you storing the data and control file?
Alok
Date: Wed, 14 Jul 1999 19:14:07 PDT
From: "jyothi satish" jyothikir@hotmail.com
Subject: RE:lock box process URGENT
hi alok
thanks for ur reply
now i could load the data into the ar_payments_interface
but when i run the remaining two programs.. submit validation
and post cash .. it is giving errors
in the log file it says 'no item number on PAYMENT record type'
I have given item number as one of the fields and i am polpulating it
hope to hear from u
thanks
SQL error 2112
Date: Fri, 16 Jul 1999 10:25:48 -0400
From: "Kelly.Newman" Kelly.Newman@plpit.fishersci.com
Subject: Lockbox SQL error 2112
We're getting a SQL -2112 error out of our lockbox process. Select..INTO
returns too many rows. We hit this once before at implementation time, and
seem to recall it having something to do with banks. Does anyone know what
this is & how to fix it.
Date: Fri, 16 Jul 1999 11:41:09 -0400
From: "Veronica Glanton/MIS/HQ/KEMET/US" VeronicaGlanton@kemet.com
Subject: Re: Lockbox SQL error 2112
I don't know if this could be your problem or not. But, I think when we
received this error before
we had more than one detail record with the same overflow sequence number.
Try looking at that.
Hope this helps!
Veronica Glanton
Lockbox - Receipt Conversion
Date: Wed, 28 Jul 1999 16:23:02 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: AR: Lockbox for converting data
Hello lockbox experts.
I know people have used autolockbox to convert receipts in the past.
I'm trying to bring in rel 10.7 receipts into a rel 11.02 app through
autolockbox.
The strategy is to pull data from the rel 10.7 application tables and
populate
an ASCII file and allow autolockbox to use sqlloader to suck the file into
the interface table AR_PAYMENTS_INTERFACE_ALL. This part works fine.
I'm getting numerous errors, most of which are clearly innacurate (like
currency
is not active, and it's USD (US dollars). So I must be doing something very
wrong.
What should I be doing in order to get these receipts in? How should the
records be formatted? What info do I need to include?
I took the Convert transmission format and added lockbox_number (because
it is required by validation), and invoice1,2,3, etcetera for receipts that
were
used to pay multiple invoices in Release 10.7 from where I am converting.
If
anybody wants to take a stab at helping me, I can send the execution report
etc. and give more detail.
Thanks in advance,
Tom-
Date: Thu, 29 Jul 1999 12:24:46 -0500
From: Seshadri Bala SBala@corpacctpo.hyatt.com
Cc: torth@sequent.com
Subject: Re : AR Lockbox for converting data
Hi
Can u send me the execution report. ( Including the Import Section ,
Validation Section ).
TIA
Balaguru
Date: Thu, 29 Jul 1999 16:27:36 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Re : AR Lockbox for converting data
Thanks Praveen and Balaguru for your help.
I'm now converting receipts successfully. There were some
setup issues that were my primary problem. But the key is
also to use the overflow records. Things were easier that
way. Also, convert your amounts to pennies (cents) for
loading into ar_payments_interface_all. Or multiply by
one hundred for whatever your currency is, then round with
0 decimal places.
Those are the oddities I noticed
Thanks,
Tom-
Autolockbox - Value for Item_number
Date: 3 Aug 99 10:38:05 MDT
From: vv vv venvivek@usa.net
Subject: Autolockbox : Value for Item_Number
Hi Guys.
In Autolockbox there is a field called Item_number in ar_payments_interface,
What is the value the item_number is goin to carry ie whether we have to pass
the Transaction number or its a receipt number or its a Inventory Item number.
Any help will be appriciated.
With regards.
Vivek.
Date: Tue, 3 Aug 1999 09:47:53 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Autolockbox : Value for Item_Number
Hello Vivek,
This is just a unique number in the interface table. It isn't related to
anything in the application. In my procedure, I just do the following
sql statement up front, then increment by one each time through the
loop for each record that I write to the data file to be loaded into
ar_payments_interface_all.
select nvl(max(item_number),0)
into m_item_number
from ar_payments_interface_all;
Date: Tue, 3 Aug 1999 12:12:01 -0700 (PDT)
From: Leonard Leung leonardleung@yahoo.com
Subject: Autolockbox : Value for Item_Number
Vivek-
The item number is only used for the sequential processing of each
record in the interface table. The item number will not be carried
over to a field on the receipt or application. If you would like this
field to be visable in Oracle, you will have to use a flexfield for it.
After defining a flexfield on the receipt and on the your loader file
and transmission format this number will come through.
As for how it is populated in the firt place. If your bank doesnot
give it to you in the first place you must generate this number from
some sort of customized data manipulation.
Lenny
Date: Tue, 3 Aug 1999 15:30:52 -0400
From: phendre@csc.com
Subject: RE: Autolockbox : Value for Item_Number
Using rownum is one of the other options.
Praveen.
On-account vs Unapplied
Date: Wed, 04 Aug 1999 10:17:11 -0700
From: Nitya Ramakrishnan nramakri@cisco.com
Subject: Autolockbox Receipt Application
Hi All,
We are on 10.7 character and use Autolockbox to create receipts and apply
cash.
Currently, if the receipt cannot apply it defaults to the 'Unapplied'
status. However, Oracle manuals tell us that we could set it up so it
defaults to either 'On-account' or 'Unapplied' based on our setup. How do
we make it default to 'On-account'?
One option was to set up an Autocash rule and and assign it at the system
Options level where a rule is specified and the 'Remaining Amount' is set
to 'On-account'. This works, but only when that specific rule applies. The
rest, it continues to put as 'Unapplied'. We would like ALL receipts to go
'On-account' instead of 'Unapplied'.
Anyone done this?
Thanks,
Nitya
Date: Thu, 05 Aug 1999 05:48:22 GMT
From: "Sunny Sunny" sunn999@hotmail.com
Subject: Re: Autolockbox Receipt Application
Hi,
I suppose when u use Lock boxes u use Auto Cash rule sets, in the
Auto cash rule set there's a field for remaining amount,set it to
Onaccount.This way all the remaining amount of the receipts shall be set to
Onaccount.
Hope this helps.
Sunil Wahi
Date: Fri, 06 Aug 1999 10:15:56 -0700
From: Nitya Ramakrishnan nramakri@cisco.com
Subject: Re: Autolockbox Receipt Application
We are doing this set up in the Auto Cash Rules. However, if the receipt is
matched to a certain rule and some application happens (even $0.00 for an
invoice that is found, but no amount is open) the remaining amount on the
receipt goes 'Unapplied'. If the rule does not apply at all, then the
entire amount goes 'On-Account'.
Eg: Rule specified - Match Invoice.
Receipt comes in with invoice number 12334 to apply $100 to. However,
Invoice is already fully applied. So, $0.00 from this receipt gets applied
to the invoice and the remaining amount viz $100.00 goes 'Unapplied'.
If however the Receipt comes in with No invoice information, the Match
Invoice rule does not apply so everything goes "On-Account".
Any ideas?
Thanks,
Nitya
Date: Fri, 6 Aug 1999 16:02:53 -0400
From: "Veronica Glanton/MIS/HQ/KEMET/US" VeronicaGlanton@kemet.com
Subject: Re: Autolockbox Receipt Application
Nitya,
In either case, do you pass customer information through the lockbox
so that the lockbox process knows whose remittance it is?
Veronica
Date: Mon, 09 Aug 1999 10:30:07 -0700
From: Nitya Ramakrishnan nramakri@cisco.com
Subject: Re: Autolockbox Receipt Application
Veronica,
Yes. In both cases, the customer information is passed.
Thanks,
Date: Tue, 10 Aug 1999 23:25:50 -0400
From: "Chadda, Alok" achadda@kpmg.com
To: "'nramakri@cisco.com'" nramakri@cisco.com
Subject: RE:Autolockbox Receipt Application
Nitya -
Navigate to Setup-Receipt-AutoCash Rule Sets and select the one
you are using. Change the default value of Unapplied to On
Account for Remaining Amount field. You need to the above for all the
Autocash
Rules you are using.
...
HTH
Alok
Autolockbox - Processing Batch of Receipts with a few invalid invoice nos.
Date: 6 Aug 99 14:46:06 PDT
From: HEMANT FULAMBARKAR fhemant@usa.net
Subject: Autolockbox - Experts
Hi,
We are on R11.0.3 using Autolockbox. My scripts and programs are working fine.
But if in a batch of receipts, if one of the Invoices is wrong, then whole
receipt is rejected. How do I process such records where the Invoice Number is
invalid and process such amounts as 'Unapplied' in a batch of invoices? Has
anybody any clue?
Thanks in advance.
- Hemant Fulambarkar
Millennia Vision Corporation
(650) 232-6300 Ext.229 (Voice)
hfulambarkar@mvsn.com
Date: Fri, 6 Aug 1999 15:11:18 -0700
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Autolockbox - Experts
One idea would be to write a store procedure that uses utl_file
to read in the file, validate the invoices, then right out another
file without the invalid invoice lines in it and use the file produced
for autolockbox instead of the original file. Or some variation
on this theme.
Autolockbox - errors in output file AND Autocash rule being ignored
Date: 4 Aug 99 11:57:22 MDT
From: vv vv venvivek@usa.net
Subject: AR: Autolockbox Help needed.
Hi All.
Thanx for Ur response. I gettin some problems while i am tryin to do
Autolockbox. Here by i am attaching the log,output file, ctl file and the
data
fild. Any suggestion will be appriciated.
Thanx in advance.
With Regards.
VV.
Date: Thu, 05 Aug 1999 08:44:42 -0400
Date: 5 Aug 99 11:11:48 MDT
phendre@csc.com wrote:
Date: 5 Aug 99 11:43:35 MDT
Orth, Tom (torth)" torth@sequent.com wrote:
Date: 5 Aug 99 11:48:31 MDT
"Orth, Tom (torth)" torth@sequent.com wrote:
Date: 5 Aug 99 12:12:12 MDT
phendre@csc.com wrote:
Date: 5 Aug 99 16:07:13 MDT
Date: Thu, 05 Aug 1999 17:26:16 -0500
Date: 5 Aug 99 16:47:15 MDT Running Autolockbox after manually loading interface tables
Date: Fri, 06 Aug 1999 15:45:57 BST
Date: Fri, 6 Aug 1999 08:26:41 -0700 Lockbox for MultiOrg - V 11
Date: Fri, 13 Aug 1999 12:41:52 +1000
Date: Fri, 13 Aug 1999 07:05:02 -0700 (PDT)
Date: 13 Aug 99 12:39:16 MDT
From: "Mujahid Siddeeque" MSiddeeq@smtp-gw.lsuc2.lsuc.on.ca
To:
Subject: Re: AR: Autolockbox Help needed.
Hi VV,
Could you send me all three files ( with attachment), so that i can
review and let see how can i help you, as i worked on Autolockbox in
11.0.2 Apps.
Regards
Mujahid siddeeque
System Analyst
Law Society Of Upper Canada
(416) 947 - 4065
From: vv vv venvivek@usa.net
Subject: Re: [Re: AR: Autolockbox Help needed.]
HI
Thanx for the response. Now i can able to create a receipt with on-account for
the respective customer account. My concern is
1) Y there is a list of errors in my output file of the autolockbox.
2) Even i set my autocashrule of that customer to clear the past invoicedue,
the autolockbox simply puts the entire amount as an on-account on the
respective customer account.
Thanx in Advance.
With regards.
Vivek.
In your data.dat you have only receipt records (type 6). There are no
overflow
receipt records ( type 4). Overflow receipt records correspond to your
transaction. If you have overflow receipt records, your receipt gets
applied
to these transaction.
Praveen.
From: vv vv venvivek@usa.net
Subject: Re: [AR: Autolockbox Help needed.]
Hi Praveen.
U mean to say i have to have two different *.ctl file one for type 6 and
the
other for type 4.
1) In type 4 *.ctl it has been restricted upto applyin for four invoice,
what
if the case if i want to apply more than four invoices.
2) If there is goin to be two different *.ctl file which is goin to insert a
seprate row to the
Thanx.
With regards.
Vivek.
This list of errors threw me off too. It believe it is a list of all
possible errors.
Not the errors that you received. Those should be on a line by line basis.
From: vv vv venvivek@usa.net
Subject: Re: [RE: [Re: AR: Autolockbox Help needed.]]
Hi Tom.
So u mean to say that these errors are not a major concern. Then what it
actually means. If ur goin to have errors based upon our uploadin of data
the
how can we distinguish between those form this list.
Thanx.
Vivek.
The actual errors that you receive will be given with a particular
line in your file. The list of errors at the end of the output is for
you to refer to from the error number given at the line level.
I'm not sure why Oracle did it this way. Nothing they do seems
very consistent!
From: vv vv venvivek@usa.net
Subject: Re: [RE: [RE: [Re: AR: Autolockbox Help needed.]]]
Hi Tom
I got it on the error list.
the other problem i have is after setting the autocashrule of respective
customer to clear the past invoice due,the autolockbox simply puts the entire
amount as an on-account on therespective customer account.
Did u faced any of these problems got any suggestions.
Thanx
Vivek.
To my knowledge:
You can put both the receipt and overflow receipt records in the same file.
You can use the same *.ctl file to do this. They are differenciated by
record identifier.
The receipt and overflow receipt records are tied by the item_number
For the same receipt records, you can have multiple overflow receipt
records. I think you use overflow sequence for this.
Thanks,
Praveen.
From: vv vv venvivek@usa.net
Subject: Re: [AR: Autolockbox Help needed.]
Hi Praveen.
Thanx for the suggestion. Got any model scripts like that. It will be very
helpful.
With regards.
Vivek.
From: Seshadri Bala SBala@corpacctpo.hyatt.com
Cc: venvivek@usa.net
Subject: Re: AR: Autolockbox Help needed
Hi
Even though u have the autocashrule set defined , make sure the
following is setup properly.
Your Autocashrule is assigned to the customer profile
Your Autocashrule is assigned in the systems options window.
Any questions , welcome
Thanx
Balaguru Seshadri
From: vv vv venvivek@usa.net
Subject: Re: [Re: AR: Autolockbox Help needed]
Hi Bala.
The Both the setup r been done. Still I am having the problem.
Got any more suggestions.
With Regards.
Vivek.
From: "x cbl" xcbl@hotmail.com
Subject: AR: Applying receipts
Has anyone set up LOCKBOX to use data other than a bank file.
I am attempting to run lockbox after manually loading the
AR_PAYMENTS_INTERFACE table and the AR_TRANSMISSIONS table in order to
mimic the SQL*LOADER part of LOCKBOX. I am NOT getting any data applied and
have no real errors.
If anyone has done this, can they please supply examples??
thanks
robert
From: "Orth, Tom (torth)" torth@sequent.com
Subject: RE: Applying receipts
I tried this too and ultimately wrote out a file using the utl_file package.
Admittedly, I didn't try very hard to get it to work by writing directly to
the table. I decided to go the route of writing the file simply because
the lockbox application seemed rather tightly bound with the lockbox
procedure.
From: Sheila Cruickshank scruickshank@assist.com.au
Subject: Lockbox for R11 Multi Org
How has Lockbox changed to cater for Multi Org in R11?
Does the .ctl program need to populate an ORG_ID default to
AR_PAYMENTS_INTERFACE table?
Any help appreciated if anyone has implemented for Multi-org,
thanks
Sheila
From: Murali Avanamuthu mavanamuthu@yahoo.com
Cc: scruickshank@assist.com.au
Subject: Re: AR: Lockbox for R11 Multi Org
Hi,
Yes. AR_PAYMENTS_INTERFACE_ALL has org_id and hence
Lockbox supports Multi-org. You can set-up your own
lockbox, receipt class, source etc. for each operating
unit. Only the transmission format is global.
Hope this helps,
Murali
From: vv vv venvivek@usa.net
Subject: Re: [Lockbox for R11 Multi Org]
Yep Ur right. There is a field called org_id in AR_PAYMENTS_INTEFACE which
helps in implementing the multi-org facility.
With regards.
Vivek.