Vendors
Vendor Import
Date: Mon, 8 Feb 1999 08:44:09 -0500 (EST)
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: Re: AP : Vendor Import
Hi,
Yes, there is no direct interface..
But, it is pretty easy.
Just map fields from legacy to the PO_VENDORS and PO_VENDOR_SITES
table and use SQL*LOADER to load the data.
Try the load for 10-20 vendors first, the really complicated ones,so
that your test time is considerably reduce. That way you can trap all
errors before the full load.
Need further help, lemme know.
Regards,
Raj
---senapathy srinivasan senapathy_s@hotmail.com wrote:
Hi,
We are on 10.7 GUI. Our group has about a thousand vendors. We are
using
multi sob and multi-org. A vendor may belong to more than one
company,
so we will have to create multiple sites, that is one site per
company.
We were exploring the possibility of importing vendor information
into
the Vendor Tables. When we contacted Oracle, we were informed that
there
is no direct interface for importing Vendors into Oracle on 10.7. Is
there any way out for this. Importing Vendors would enable us to
save a
lot of time in our implementation efforts.
Thanks for any inputs.
Senapathy
Date: Mon, 8 Feb 1999 08:46:18 -0500 (EST)
From: "Lakdawala, M" M_Lakdawala@csx.com
Subject: RE: AP : Vendor Import
You will have to write your own custom codes to populate the vendor tables.
However keep in mind you keep all integrity constrains and business rules
which have been implemented by Oracle on the front end.
Thanks & Regards
MUZAFFER LAKDAWALA
E-mail mailto:M_lakdawala@csx.com M_lakdawala@csx.com
Phone 904.366.4720
Date February 8, 1999 9:35 AM
-----Original Message-----
From: senapathy srinivasan
[mailto:senapathy_s@hotmail.com]
Sent: Monday, February 08, 1999 12:27 AM
Subject: AP : Vendor Import
Hi,
We are on 10.7 GUI. Our group has about a thousand vendors.
We are using
multi sob and multi-org. A vendor may belong to more than
one company,
so we will have to create multiple sites, that is one site
per company.
We were exploring the possibility of importing vendor
information into
the Vendor Tables. When we contacted Oracle, we were
informed that there
is no direct interface for importing Vendors into Oracle on
10.7. Is
there any way out for this. Importing Vendors would enable
us to save a
lot of time in our implementation efforts.
Thanks for any inputs.
Senapathy
Date: Mon, 8 Feb 1999 08:55:29 -0500 (EST)
From: Xavier Ghyssens xghyssens@twohs.com
Subject: RE: AP : Vendor Import
You can also use the useful tool SmartDB which provide also the template
to import the vendor data directly in the DB with all the constraint of
validations
Feel free to contct me if you need more explanation
Xavier ghyssens
At 08:46 AM 2/8/99 -0500, you wrote:
You will have to write your own custom codes to populate the vendor tables.
However keep in mind you keep all integrity constrains and business rules
which have been implemented by Oracle on the front end.
Thanks & Regards
MUZAFFER LAKDAWALA
E-mail mailto:M_lakdawala@csx.com M_lakdawala@csx.com
Phone 904.366.4720
Date February 8, 1999 9:35 AM
Date: Mon, 8 Feb 1999 12:27:45 -0500 (EST)
From: Ilya.Gandzeychuk@Kvazar-Micro.com
Subject: RE: AP : Vendor Import
Three tables store vendor information. If need you may use the table
PO_VENDOR_CONTACTS. This table stores information about contacts for a
vendor site.
Ilya Gandzeychuk
Oracle Application Consultant
Kvazar-Micro Corp.
Ukraine
Date: Mon, 8 Feb 1999 13:53:40 -0500 (EST)
From: "Farber, Andy" farban@consumer.org
Subject: RE: AP : Vendor Import
What about brute force?
If you have 1,000 vendors, if entering one vendor takes (as much as) 5
minutes, then you'll need 5,000 minutes, which is 83 hours, perhaps 2 temps
for a week of data entry...
Andrew R. Farber
Consumers Union of U.S., Inc.
914/378-2531
mailto:farban@consumer.org
Date: Mon, 8 Feb 1999 22:38:22 -0500 (EST)
From: "senapathy srinivasan" senapathy_s@hotmail.com
Hi folks,
Thank you all those who had replied to my queries on GL : Budget
Journals, AP : Vendor Import and many others. This is to you Andy.
We had decided yesterday afternoon to do exactly the same as you have
recommended below, down to getting 2 temps to enter the data. Your
calculations below have reinforced our approach. Some Vendors will be
also entered by end users to get them familiar with the screens.
Senapathy
Date: Mon, 8 Feb 1999 14:39:04 -0500
From: "Farber, Andy" farban@consumer.org
What about brute force?
If you have 1,000 vendors, if entering one vendor takes (as much as) 5
minutes, then you'll need 5,000 minutes, which is 83 hours, perhaps 2
temps
for a week of data entry...Andrew R. FarberConsumers Union of U.S., Inc.
914/378-2531mailto:farban@consumer.org
Date: Tue, 9 Feb 1999 08:08:57 -0500 (EST)
From: "Kalyansundaram.M" kalyan@wipsys.soft.net
Dear Senapathy, We had the same problem and even checked with Oracle and found there is no tool available with them.
We have done import of the Supplier data into the application with the help
of Excel. We did the same with the help of an excel macro which writes into
the supplier tables. The same was done for the employee master both as
employee as well as Suppliers. The Volume was quite high about 8000
masters. The Excel Macro writes the data into the application directly. I
hope it helps.
Thanks and Regards.
Kalyan
Vendor Numbering
Date: Fri, 19 Mar 1999 14:00:18 -0800 (PST)
Now, regarding vendor conversion, Oracle use a Table called
PO_UNIQUE_IDENTIFIER_CONTROL. When you insert a vendor from apps
screen, it calls one package which in turns execute this SQL script to
fetch new vendor number and inserts into SEGMENT1 column in PO_VENDORS.
SELECT current_max_unique_identifier
---Sean Mayhew
How does Oracle get the automatic vendor number for AP?
ie Table, Sequence, etc.
I'm writting a vendor import program, and need to access this info How to manage Duplicate Vendors
Date: Thu, 6 May 1999 07:25:13 -0700 (PDT)
Date: Thu, 06 May 1999 10:28:31 -0400
Date: Thu, 6 May 1999 07:36:29 -0700 (PDT)
Date: Thu, 06 May 1999 10:40:03 -0400
Date: Thu, 6 May 1999 10:40:47 -0400
Date: Thu, 6 May 1999 12:14:00 -0400
Date: Thu, 6 May 1999 12:08:06 -0700 (PDT)
Date: Thu, 6 May 1999 17:00:33 -0400
Date: Fri, 7 May 1999 15:30:47 +0100
Date: Fri, 7 May 1999 11:38:40 -0700 (PDT)
Date: Mon, 10 May 1999 09:46:22 +0100
Date: Wed, 12 May 1999 08:44:01 -0700 (PDT)
Date: Fri, 14 May 1999 14:35:34 +0100 Report for duplicate vendors
From: mrosm [mailto:mrosm@stratos.net]
Date: Thu, 15 Jul 1999 11:59:18 -0400 Track where No Inv/PO activity
Date: Tue, 20 Jul 1999 12:43:30 -0700
Date: Tue, 20 Jul 1999 16:09:59 -0400
Date: Tue, 20 Jul 1999 16:26:46 -0400 (EDT) How to merge suppliers when both have same invoice nos.
Date: Mon, 19 Jul 1999 16:35:56 -0600
Date: Mon, 26 Jul 1999 15:44:14 -0500
From: Sitikantha Panigrahi
Subject: Re: Auto Supplier / Vendor Numbering
Hi Sean,
Could please post my reply to the mailing list. All my mails bounce
back when I post it. But I can receive all mails.
FROM po_unique_identifier_control
WHERE table_name = 'PO_VENDORS';
But you can always use your own sequence to generate unique vendor
number.
Let me know if you want any more info.
Sitikantha Panigrahi
Regards
Sean Mayhew
smayhew35@hotmail.com
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: Duplicate Vendors - URGENT
Hello,
We are converting from JDEdwards.
Legacy allowed for having duplicate vendors.
Financials does not allow for duplicate vendors.Also, the vendor name
column in PO_VENDORS table has an unique index on it.
I need some input on how any one of you handled this problem.
Regards,
Raj Nagaraju
From: "Palad, Ed (FUSA)" EdPalad@firstusa.com
Subject: RE: Duplicate Vendors - URGENT
raj,
A duplicate vendor_name indicates just another site
(PO_VENDOR_SITES).
Write a PL/SQL, when you get a dup populate the table or view above.
ed
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: RE: Duplicate Vendors - URGENT
Ed,
Thanks for the input.
But our problem is these vendors have different Tax ID.
For example,
We have two McDonalds, totally different vendors but have the same
name. They are located at different addresses, taxid, etc.
But, they have the same name. So, basically two different vendors.
I am sorry I did not make it clear in my earlier message.
Regards,
Raj
From: "Palad, Ed (FUSA)" EdPalad@firstusa.com
Subject: RE: Duplicate Vendors - URGENT
Raj,
You have to append something, maybe the first four of TIN or the
last four,
whatever will make it unique and populate po_vendorss. Yes I agree
different TIN,
different vendor.
ed.
From: "Joe Maliszewski" jmaliszewski@csi.com
Subject: RE: Duplicate Vendors - URGENT
Then the legal name should be used. For Example: McDonalds # 22222
Washington.
Joe Maliszewski
Consultant
jmaliszewski@csi.com
From: Steve Bradley sbradley@sprynet.com
Subject: RE: Duplicate Vendors - URGENT
I agree with Joe, use the legal name. In the McDonalds case the legal name
is probably something like:
Davey Jone's Franchise, Inc.
D/b/a McDonalds of ......
It is possible to have two separate legal entities with the same legal name
if they are incorporated in two different states. Similar legal names are
not permitted if incorporating in the same state.
This doesn't help your situation since you are probably looking for a
method that permits an automated conversion of vendors. Using correct
legal names requires additional research prior to conversion.
One consideration is using the same vendor name for all McDonalds. There
is no law that says you can't do this if all of the sites are incorporated
(non-1099 vendors). If you are not required to send 1099's then you should
not be concerned about using the same vendor. If you can show that the
vendor led you to believe it is incorporated you are not required to send
1099's.
I would not use additional characters like "." at the end because it does
not help in the selection process unless you memorize which one is which.
This is not good for new employees. Definitely do not use a space at the
end. It really messes up screen queries.
Respectfully,
Steve Bradley, CPA
OASIS Consulting Group, Inc.
Office 404-352-8387
Fax 404-609-9856
Pager 888-912-2569
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: RE: Duplicate Vendors - Rephrasing question
Steve,
Thanks for the reply.
We have thousands of individuals who are vendors.
Your thoughts helps me on Corporate entities.
If the vendor is an individual,eg.,
John Doe.
The client does not want to put SSN anywhere on the vendor name.
I would appreciate thoughts on this.
Regards,
Raj
From: Steve Bradley sbradley@sprynet.com
Subject: RE: Duplicate Vendors - Rephrasing question
Are you saying there might be more than John Smith in the world? You do
have a problem.
I have a standard Oracle solution and a custom solution. The standard
would be to add the last 4 digits of their SSN to the vendor name. This
would require a compromise on the part of the client since part of the SSN
will print on individual's checks. The custom solution would be the same
except the check format program would be modified to strip the last 4
characters from the vendor name for vendors with a vendor type of
"SPECIAL".
Hope this helps.
Respectfully,
Steve Bradley, CPA
OASIS Consulting Group, Inc.
Office 404-352-8387
Fax 404-609-9856
Pager 888-912-2569
From: James.Walker@blackwell.co.uk
Subject: RE: Duplicate Vendors - URGENT
Drop the unique index PO_VENDORS_U2 that is on PO_VENDORS.VENDOR_NAME. The
unique constraint is a default, not an inherent design limitation. Raise a
TAR and as with any customisation make sure it checks out in test.
Prior to moving to this solution on 10.7 multi-org, our 10.6 solution was to
modify the Vendor Name (as already suggested) by appending a sequence
number. The 'real' name was held in ATTRIBUTE1 and that was used on the
cheque reports (front-end systems do the order entry). With multiple SOB on
multi-org sharing the same vendors, the later solution (stated above) avoids
inflating the number of vendor headers unnecessarily (a site being added
where needed for each SOB).
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: RE: Duplicate Vendors - Dropping Index
James,
That is a good suggestion that we are thinking about.
Thank you.
I wanted to know, if, you, or any other firm has actually DROPPED the
unique index PO_VENDORS_U2 that is on PO_VENDORS.VENDOR_NAME and has
encountered ABSOLUTELY NO problems in a production environment..
From: James.Walker@blackwell.co.uk
Subject: RE: Duplicate Vendors - Dropping Index
On our system the index was dropped prior to performing a data take-on into
10.7 (from 10.6). We have encountered no problems in a production
environment concerning this customisation, since going live on 10.7 in
September 1998. Fortunately for us Oracle Consulting Services were
responsible for this work. Bear in mind that our AP is driven by front end
feeder systems (that produce the invoices) so online searches are performed
using the supplier number generated on the feeder systems (and not the
name). We do not have OE or PA. This approach would need to be thoroughly
tested in your environment.
From: rajendra nagaraju rnagaraju@yahoo.com
Subject: RE: Duplicate Vendors - Dropping Index
Hello James,
I dropped the PO_VENDORS_U2 unique index on PO_VENDORS.VENDOR_NAME.
This did not help because Apps enforces unique vendor name at the form
level thro a WHEN-VALIDATE-ITEM trigger which uses
AP_VENDORS_PKG.check_unique_vendor_name procedure to do this.
You talked about Oracle Consulting doing some customisation towards
this end.
The form name that you use to enter vendors is APXVDMVD.
I assume they did customising on this form.
I would appreciate some details and/or documentation on this.
Thank you,
Regards,
Raj Nagaraju
From: James.Walker@blackwell.co.uk
Subject: RE: Duplicate Vendors - Dropping Index
our vendors aren't entered through that form, they are loaded via a
customised interface from the feeder systems. we only manually enter a
limited number of 'overhead' vendors which don't have duplicate names. is
the form the same on character?
when you say Apps enforces unique vendor name does it just warn and allow
you to proceed (as for customer names on ar)?
if not it doesn't sound like it would be too bad to modify the form but we
haven't had to do that.
Sent: Monday, July 12, 1999 7:18 PM
Subject: AP: Duplicate Vendors
Hi,
I would like to generate a report which identifies duplicate
vendors/suppliers based on duplicate vendor names and duplicate
tax_ids. If there are 2 vendors with the same name and the same
tax_id,
they are duplicates. Can anyone assist me in writing the
appropriate
query. Please send response to mrosm@stratos.net as well. I
appreciate
any assistance.
Thanks so much.
From: Ester Humphrey ehumphrey@gwu-financials.com
Subject: RE: Duplicate Vendors
Ron,
Run and evaluate the Supplier Merge Report which will identify all multiple
and duplicate suppliers.
Then execute the Supplier Audit Report to purge those suppliers no longer
needed.
Esther
From: "m. smith" mrsmith@hotbot.com
Subject: AP, PO: Duplicate Vendor-No PO Activity
Can someone please take a look at the script below and tell me why I am getting the error: ERROR at line 1:
ORA-01756: quoted string not properly terminated
Perhaps this can be used when the problem is resolved.
-- this sql script reports vendors with duplicate tax id and
-- whether there are invoice or po activities
declare
v_vendor_num varchar2(10);
v_vendor_name varchar2(35);
v_check_date varchar2(11);
rec1_data varchar2(200);
rec2_data varchar2(250);
rec3_data varchar2(250);
rec4_data varchar2(250);
inv_act varchar2(03);
po_act varchar2(03);
cnt07 number := 0;
cnt number := 0;
inv_cnt number := 0;
po_cnt number := 0;
cntc1 number := 0;
cntc2 number := 0;
cursor c1 is
select num_1099
from po.po_vendors
group by num_1099
having count(num_1099) > 1;
c1_rec c1%rowtype;
cursor c2 (curr_num_1099 varchar2) is
select vendor_name,
b.vendor_id,
b.address_line1,
b.address_line2,
num_1099,
a.segment1,
org_id,
vendor_site_code
from po.po_vendors a,
po.po_vendor_sites_all b
where num_1099 = curr_num_1099
and (num_1099 not like '0%' and
num_1099 not like '1%' and
num_1099 not like '2%' and
num_1099 not like '3%' and
num_1099 not like '4%' and
num_1099 not like '5%' and
num_1099 not like '6%' and
num_1099 not like '7%' and
num_1099 not like '8%')
and a.end_date_active is null
and a.vendor_id = b.vendor_id;
c2_rec c2%rowtype;
begin
open c1;
fetch c1 into c1_rec;
while c1%found loop
cntc1 := cntc1 + 1;
open c2 (c1_rec.num_1099);
fetch c2 into c2_rec;
while c2%found loop
select count(*)
into inv_cnt
from ap.ap_invoices_all
where vendor_id = c2_rec.vendor_id;
if inv_cnt> 0 then
inv_act := 'Yes';
else
inv_act := 'No';
end if;
select count(*)
into po_cnt
from po.po_headers_all
where vendor_id = c2_rec.vendor_id;
if po_cnt > 0 then
po_act := 'Yes';
else
po_act := 'No';
end if;
dbms_output.put_line((rpad(substr(c2_rec.num_1099,1,15),15,' ') || ' ' || rpad(substr(c2_rec.vendor_name,1,25),25,' ') || ' ' || rpad(nvl(substr(c2_rec.address_line1,1,20),' '),20,' ') || ' ' || rpad(nvl(substr(c2_rec.address_line2,1,20),' '),20,' ') || ' ' || rpad(c2_rec.segment1,8,' ') || ' ' || rpad(substr(c2_rec.org_id,1,4),4,' ') || ' ' || rpad(substr(c2_rec.vendor_site_code,1,15),15,' ') || ' ' || rpad(inv_act,3,' ') || ' ' || po_act));
-- dbms_output.put_line(rec1_data);
fetch c2 into c2_rec;
end loop;
close c2;
fetch c1 into c1_rec;
end loop;
close c1;
dbms_output.put_line('cntc1 ' || cntc1);
dbms_output.put_line('cnt ' || cnt07);
end;
/
spool off
From: "Mark R Erzen" merzen@darc.com
Subject: RE: AP, PO: Duplicate Vendor-No PO Activity
This may not be the answer, but I was wondering if PL/SQL understands
varchar2(03), as opposed to varchar2(30).
From: Balalji Sundaralingam CMCISSB@NJTRANSIT.STATE.NJ.US
Subject: Re: AP, PO: Duplicate Vendor-No PO Activity
Hi Smith,
Try this to see if it works.
dbms_output.put_line('cntc1 ' || to_char(cntc1));
dbms_output.put_line('cnt ' || to_char(cnt07));
Balaji
From: kbrown@es.com
Cc: jerror@es.com
Subject: AP: Cancelled Invoices and Supplier Merge on Same
AP Questions:
1) We have a supplier's invoice which was entered then cancelled under
one Supplier's (A) name. The same invoice was then re-entered under another
Supplier's (B) name, using the same invoice number, and paid under Supplier
(B). We're now trying to do a Supplier Merge on suppliers A and B and
cannot because the merge would create a duplicate invoice.
2) The other situation we have is similar to #1) with the exception
that the invoice to Supplier (A) was never cancelled. For example, we have
internal check requests which we number manually (CR071599: CR = check
request, 07 = month, 15 = day, 99 = year). We have entered a check request
#CR071599 under Supplier (A), paid it. We then entered another check
request #CR071599 under Supplier (B). They do happen to be two separate
check requests for reimbursement of unrelated items, only
* They're using the same invoice number
* They're entered against two separate suppliers which we now want to
merge.
Anyone experienced and resolved these problems
before?
Your help is appreciated.
Kristie
From: "Debbie Griffin" dgriffin@fnal.gov
Subject: AP: Cancelled Invoices and Supplier Merge on Same
I had logged a TAR with support on this problem in March of 1998. Their
solution was to send me a SQL script to update the invoice numbers so they
can be merged. We have since converted this script into a concurrent
program where I enter the paramaters of vendor number, existing invoice
number, and the new number that I want to change it to. I usually leave it
the same and add *SQL to it so it is obvious that it was changed via SQL.
Then I run the merge.
-- Debbie Griffin
Fermilab
dgriffin@fnal.gov
630-840-8754