| Description |
* customer using i21 ap
* Vendors were converted and additional are setup
* We are trying to insert vendor locaitons from origin
*Steps:*
1. Use customer convCV database.
2. Run the query below to insert vendor locations from origin.
Query purpose: Origin did not have Vendor Locations. So each location is a vendor record in SSVNDMST. These are identified by "Ssvnd_pay_to" having a value different than the SSvnd_no, These records have to be converted as vendor locations in i21.
Example
|ssvnd_no|SSvnd_pay_to| | |
|abc123|abc| | |
|abc|abc| | |
| | | | |
| | | | |
Expected i21 results
|int vendor id|location id| |
|1|ABC| |
|1|ABC123| |
problem is - we can not insert ABC123 location on vendor ABC.
Requirements:
1. Data fix in 18.1 - Not requied, I can use the script below.
2. Create an usp and button for the insert in 19.1 in Purchasing section.
!pastedImage_d98646_0.png!
Query:
INSERT [dbo].[tblEMEntityLocation]
([intEntityId],
[strLocationName],
[strAddress],
[strCity],
[strCountry],
[strState],
[strZipCode],
[strNotes],
[intShipViaId],
[intTermsId],
[intWarehouseId],
[ysnDefaultLocation])
select
ENT.intEntityId,
RTRIM(ISNULL(CASE WHEN ssvnd_co_per_ind = 'C' THEN ssvnd_name
ELSE dbo.fnTrim(SUBSTRING(ssvnd_name, DATALENGTH([dbo].[fnGetVendorLastName](ssvnd_name)), DATALENGTH(ssvnd_name)))
+ ' ' + dbo.fnTrim([dbo].[fnGetVendorLastName](ssvnd_name))
END,'')) + '_' + CAST(A4GLIdentity AS NVARCHAR),
dbo.fnTrim(ISNULL(ssvnd_addr_1,'')) + CHAR(10) + dbo.fnTrim(ISNULL(ssvnd_addr_2,'')),
ssvnd_city,
'United States',
ssvnd_st,
dbo.fnTrim(ssvnd_zip),
NULL,
NULL,
CASE WHEN ssvnd_terms_disc_pct = 0 AND ssvnd_terms_due_day = 0
AND ssvnd_terms_disc_day = 0 AND ssvnd_terms_cutoff_day = 0 THEN (SELECT TOP 1 intTermID FROM tblSMTerm WHERE strTerm= 'Due on Receipt')
WHEN ssvnd_terms_type = 'D' THEN (SELECT TOP 1 intTermID FROM tblSMTerm
WHERE dblDiscountEP = ssvnd_terms_disc_pct
AND intBalanceDue = ssvnd_terms_due_day
AND intDiscountDay = ssvnd_terms_disc_day)
WHEN ssvnd_terms_type = 'P' THEN (SELECT TOP 1 intTermID FROM tblSMTerm
WHERE intBalanceDue = ssvnd_terms_due_day
AND intDiscountDay = ssvnd_terms_disc_day
AND intDayofMonthDue = ssvnd_terms_cutoff_day)
ELSE (SELECT TOP 1 intTermID FROM tblSMTerm WHERE strTerm= 'Due on Receipt') END,
NULL,
0
from ssvndmst
inner join tblEMEntity ENT on ENT.strEntityNo COLLATE SQL_Latin1_General_CP1_CS_AS = ssvnd_pay_to COLLATE SQL_Latin1_General_CP1_CS_AS
INNER JOIN tblEMEntityType ETYP ON ETYP.intEntityId = ENT.intEntityId
where ssvnd_pay_to is not null and ssvnd_vnd_no <> ssvnd_pay_to AND ETYP.strType = 'Vendor'
and (
RTRIM(ISNULL(CASE WHEN ssvnd_co_per_ind = 'C' THEN ssvnd_name
ELSE dbo.fnTrim(SUBSTRING(ssvnd_name, DATALENGTH([dbo].[fnGetVendorLastName](ssvnd_name)), DATALENGTH(ssvnd_name)))
+ ' ' + dbo.fnTrim([dbo].[fnGetVendorLastName](ssvnd_name))
END,'')) + '_' + CAST(A4GLIdentity AS NVARCHAR) not in (select strLocationName COLLATE Latin1_General_CI_AS from tblEMEntityLocation ))
|