| Description |
After running the conversion for Import Special Prices. I have to run the following scripts to remove the locations and then to delete duplicate records.
---Remove customer location in special prices
UPDATE tblARCustomerSpecialPrice
SET intCustomerLocationId = NULL
WHERE
intSpecialPriceId IN (
SELECT intSpecialPriceId FROM
(
select
intSpecialPriceId,
intCustomerLocationId,
strCustomerNumber, intEntityVendorId, intItemId, strClass, strPriceBasis, dblDeviation, strLineNote, dtmBeginDate,
dtmEndDate, intRackVendorId, intRackItemId, intEntityLocationId, intRackLocationId, strInvoiceType, intCategoryId,
tblARCustomerSpecialPrice.intCurrencyId, intProgramId, strProgramType
from tblARCustomerSpecialPrice
inner join tblARCustomer on tblARCustomerSpecialPrice.intEntityCustomerId = tblARCustomer.intEntityId
and intCustomerLocationId is not Null
group by
intCustomerLocationId,
strCustomerNumber, intEntityVendorId, intItemId, strClass, strPriceBasis, dblDeviation, strLineNote, dtmBeginDate,
dtmEndDate, intRackVendorId, intRackItemId, intEntityLocationId, intRackLocationId, strInvoiceType, intCategoryId,
tblARCustomerSpecialPrice.intCurrencyId, intProgramId, strProgramType,intSpecialPriceId
) tblA
)
---- Remove duplicate special prices after import.
BEGIN TRANSACTION
BEGIN
WITH CTE AS(
SELECT intSpecialPriceId,strCustomerNumber,tblARCustomerSpecialPrice.intEntityCustomerId, intCustomerLocationId, intEntityVendorId, intItemId, strClass, strPriceBasis, dblDeviation, strLineNote, dtmBeginDate,
dtmEndDate, intRackVendorId, intRackItemId, intEntityLocationId, intRackLocationId, strInvoiceType, intCategoryId,
tblARCustomerSpecialPrice.intCurrencyId, intProgramId, strProgramType
,RN = ROW_NUMBER()OVER(PARTITION BY
strCustomerNumber,tblARCustomerSpecialPrice.intEntityCustomerId, intCustomerLocationId, intEntityVendorId, intItemId, strClass, strPriceBasis, dblDeviation, strLineNote, dtmBeginDate,
dtmEndDate, intRackVendorId, intRackItemId, intEntityLocationId, intRackLocationId, strInvoiceType, intCategoryId,
tblARCustomerSpecialPrice.intCurrencyId, intProgramId, strProgramType ORDER BY strCustomerNumber)
from tblARCustomerSpecialPrice
inner join tblARCustomer on tblARCustomerSpecialPrice.intEntityCustomerId = tblARCustomer.intEntityId
)
DELETE tblARCustomerSpecialPrice WHERE intSpecialPriceId IN (SELECT intSpecialPriceId FROM CTE WHERE RN > 1)
SELECT * FROM tblARCustomerSpecialPrice
END
commit TRANSACTION
These are the queries I run to validate the count and differences:
This is final balancing for special prices, only works after cleanup above!
---checks for different special price basis being used
select distinct(ptpdv_basis_ind), COUNT(\*) from ptpdvmst
group by ptpdv_basis_ind
---checks for special price count
select COUNT(\*) from ptpdvmst
select COUNT(\*) from tblARCustomerSpecialPrice
Verification before conversion:
---total ptpdvmst count 1646
---total bill to join count after join 1644 - this is the correct join that matches i21.
---total ship to join count after join 1646
---total converted special price count 1644
select ptcus_bill_to, ptpdv_vnd_no, ptpdv_itm_no, ptpdv_class, ptpdv_basis_ind, COUNT(\*) from ptpdvmst
inner join ptcusmst on ptpdvmst.ptpdv_cus_no = ptcusmst.ptcus_cus_no
group by ptcus_bill_to, ptpdv_vnd_no, ptpdv_itm_no, ptpdv_class, ptpdv_basis_ind
---total count 1644
--This checks between origin and i21 non-matching count of special price per bill to. If there is non-matching, need to find why one has more than the other. Create 2 temp tables and compare the two to check for non-matching special price count. This wold be done after the location cleanup for duplicates (axel query).
select strCustomerNumber, COUNT(\*) as count into #tmp1 from tblARCustomerSpecialPrice
inner join tblARCustomer on tblARCustomerSpecialPrice.intEntityCustomerId = tblARCustomer.intEntityId
left join tblICItem on tblARCustomerSpecialPrice.intItemId = tblICItem.intItemId
group by strCustomerNumber
select ptcus_bill_to, COUNT(\*)as ptpdvcount into #tmp2 from ptpdvmst
inner join ptcusmst on ptpdvmst.ptpdv_cus_no = ptcusmst.ptcus_cus_no
group by ptcus_bill_to
select ptcus_bill_to, #tmp1.count, #tmp2.ptpdvcount from #tmp1
inner join #tmp2 on #tmp2.ptcus_bill_to collate Latin1_General_CI_AS = #tmp1.strCustomerNumber
where count<>ptpdvcount
---this checks the detail in non-match
select * from tblARCustomerSpecialPrice
inner join tblARCustomer on tblARCustomerSpecialPrice.intEntityCustomerId = tblARCustomer.intEntityId
where strCustomerNumber = 'OLDCAST '
select ptpdv_basis_ind,ptpdv_factor, * from ptcusmst
inner join ptpdvmst on ptcusmst.ptcus_cus_no = ptpdvmst.ptpdv_cus_no
where ptcus_bill_to = 'OLDCAST '
There should be no different in count.
|