Production Ticket(ST) #11549
YAKULT - SFA Implementation for Yakult
1) Massage the excel provided.
2) Create script to patch the data.
Root cause: Client request.
None
Description
Dear Rashdan,
I need your assistance to patch Yakult Address Information and Billing Address Information into Datanory Trial (Yakult Organization). In the attachment “(Address (Bill To)”, please refer to column D (Bill-to Street) to update the Billing Address. However, some of data in column D is blank, which means the BP Billing Address is the same as Shipping Address.
Additionally, you may refer to Yakult Shipping Address in the “BP Listing (Full) with pricelist” attachment. In the attachment, you’ll also find Branch (Code) in column AE which referring to Yakult’s BP Branch Code. For Example, 99 Speedmart have many outlets, but Yakult only invoice to HQ Address. Yakult requires at the end of the HQ Address in Invoice to show the Branch Code in (). Please patch these Branch (Code) in Datanory Trial (Yakult Organization) in “Shipping Address Information – Shipping Address 1”. Thus, BP actual address will start in “Shipping Address Information – Shipping Address 2”.
Please let me know if you have any questions.
Hi Rashdan,
Just to update you, as agreed yesterday please patch the Branch (Code) in Datanory Trial (Yakult Organization) in “Shipping Address Information – Shipping Address 5”. Since not all the Yakult BP have the Branch (Code) while the “Shipping Address Information – Shipping Address 1” is a mandatory field.
Thanks.
History
#1 Updated by Rashdan Hussin 5 months ago
- Status changed from Assigned To to Support In Progress
--CREATE TABLE #TempAddressTable (
-- customer_code VARCHAR,
-- customer_name VARCHAR,
-- customer_address1 VARCHAR,
-- customer_address2 VARCHAR,
-- address_state VARCHAR,
-- address_postcode VARCHAR
--)
--select * into _mst_customer_contact_address_20240426 from mst_customer_contact_address
--update ww set
ww.address1=IsNull(a.customer_address1, ''),-- AS Address1,
ww.address2=IsNull(a.customer_address2, ''),-- AS Address2,
ww.address3=IsNull('', ''),-- AS Address3,
ww.address4=IsNull('', ''),-- AS Address4,
ww.address5=IsNull('', ''),-- AS Address5,
ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
ww.postcode=IsNull(a.address_postcode, ''),-- AS PostCode,
ww.updated_Date=getdate(),-- AS UpdatedDate,
ww.updated_by='Support'-- AS UpdatedBt,
FROM mst_customer_contact_address ww
inner join customer wer on ww.customer_id=wer.customer_id
inner join #TempAddressTable a on a.customer_code = wer.customer_code
inner join state s on a.address_state=s.state_code and s.org_id = 'A000002'
where ww.org_id = 'A000002'
and ww.address_type = 'Billing'
--INSERT into mst_customer_contact_address ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
SELECT newid() AS CustomerAddressId,
IsNull(wert.customer_id, '') AS CustomerId,
IsNull(a.customer_address1, '') AS Address1,
IsNull(a.customer_address2, '') AS Address2,
'' AS Address3,
'' AS Address4,
'' AS Address5,
IsNull(s.state_id, '') AS StateId,
IsNull(a.address_postcode, '') AS PostCode,
'C8BD9127-6E5A-45F3-A65E-A6F200C395DC' AS CountryId,
'Billing' AS AddressType,
'Active' AS Status,
'' AS TelNo,
'' AS AdditionalTelNo,
getdate() AS CreatedDate,
'Support' AS CreatedBy,
getdate() AS UpdatedDate,
'Support' AS UpdatedBt,
'A000002' AS OrgId,
'A000002' AS TenantId,
'1' AS Latitude,
'1' AS Longitude,
'1' AS Radius,
IsNull('', '') AS Contact,
IsNull('', '') AS SoldToAddr3Loc,
'C8BD9127-6E5A-45F3-A65E-A6F200C395DC' AS region_id
FROM #TempAddressTable a
left join state s on a.address_state=s.state_code and s.org_id = 'A000002'
inner join customer wert on a.customer_code=wert.customer_code
WHERE NOT EXISTS
and a.customer_address1 <> ''
--INSERT into mst_customer_contact_address ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
SELECT newid() AS CustomerAddressId,
IsNull(b.customer_id, '') AS CustomerId,
IsNull(b.address1, '') AS Address1,
IsNull(b.address2, '') AS Address2,
IsNull(b.address3, '') AS Address3,
IsNull(b.address4, '') AS Address4,
IsNull(b.address5, '') AS Address5,
IsNull(b.state_id, '') AS StateId,
IsNull(b.postcode, '') AS PostCode,
IsNull(b.country_id, '') AS CountryId,
'Billing' AS AddressType,
IsNull(b.status, '') AS Status,
IsNull(b.tel_no, '') AS TelNo,
IsNull(b.additional_tel_no, '') AS AdditionalTelNo,
getdate() AS CreatedDate,
'Support' AS CreatedBy,
getdate() AS UpdatedDate,
'Support' AS UpdatedBt,
IsNull(b.org_id, '') AS OrgId,
IsNull(b.tenant_id, '') AS TenantId,
IsNull(b.latitude, '') AS Latitude,
IsNull(b.longitude, '') AS Longitude,
IsNull(b.radius, '') AS Radius,
IsNull('', '') AS Contact,
IsNull('', '') AS SoldToAddr3Loc,
IsNull(b.region_id, '') AS region_id
FROM #TempAddressTable a
left join state s on a.address_state=s.state_code and s.org_id = 'A000002'
inner join customer wert on a.customer_code=wert.customer_code
inner join mst_customer_contact_address b on b.customer_id = wert.customer_id and b.address_type = 'Contact'
WHERE NOT EXISTS
and a.customer_address1 = ''
--These billing address need to patch again by follow the shipping address
select * from mst_customer_contact_address where org_id = 'A000002' and customer_id in
(select customer_id from customer where org_id = 'A000002')
and updated_date = '2024-04-27 00:45:46.653'
order by updated_date desc
#2 Updated by Rashdan Hussin 5 months ago
- Status changed from Support In Progress to Ticket Resolved
- % Done changed from 0 to 90
- Actual Initial Response set to 24/04/2024
- Resolution updated (diff)
--UPDATE billing
SET
address1 = shipping.address1,
address2 = shipping.address2,
address3 = shipping.address3,
address4 = shipping.address4,
address5 = shipping.address5,
state_id = shipping.state_id,
postcode = shipping.postcode
FROM
mst_customer_contact_address AS billing
INNER JOIN
mst_customer_contact_address AS shipping ON billing.customer_id = shipping.customer_id and shipping.org_id ='A000002'
WHERE
billing.address_type = 'billing'
AND shipping.address_type = 'shipping'
AND billing.org_id ='A000002'
AND billing.updated_date = '2024-04-27 00:45:46.653'
select
billing.customer_id,
billing.address1 as 'before 1',
shipping.address1 as 'after 1',
billing.address2 as 'before 2',
shipping.address2 as 'after 2',
billing.address3 as 'before 3',
shipping.address3 as 'after 3',
billing.address4 as 'before 4',
shipping.address4 as 'after 4',
billing.address5 as 'before 5',
shipping.address5 as 'after 5',
billing.state_id as 'before state',
shipping.state_id as 'after state',
billing.postcode as 'before postcode',
shipping.postcode as 'after postcode'
FROM
mst_customer_contact_address AS billing
INNER JOIN
mst_customer_contact_address AS shipping ON billing.customer_id = shipping.customer_id and shipping.org_id ='A000002'
WHERE
billing.address_type = 'billing'
AND shipping.address_type = 'shipping'
AND billing.org_id ='A000002'
AND billing.updated_date = '2024-04-27 00:45:46.653'
--and billing.customer_id = 'd187ae2e-dc2d-411b-93f4-177557019017'
#3 Updated by Rashdan Hussin 5 months ago
- % Done changed from 90 to 100