Datanory Production

Remove customer Lotus”s double quotes and replace with 1 quotes only.

select *
from customer
where org_id = 'oromeo' and customer_name like 'LOTUS''''S%'
--where org_id = 'oromeo' and chain_id = 'D3D10ED2-B4B6-4C3B-9BBE-E780FDB0ED3B'

select *
into _customer_240202
from customer

UPDATE customer
SET customer_short_name = REPLACE
WHERE org_id = 'oromeo' and customer_name like 'LOTUS''S%'

select REPLACE from customer where org_id = 'oromeo' and customer_name like 'LOTUS''S%'

Remove user

select *
from member
where org_id = 'oromeo' and member_cd like '2%'

select *
into _member_240202
from member

delete from member
where org_id = 'oromeo' and member_cd like '2%'

User Template

select
member_cd as [member_code],
first_name as [first_name],
isnull(last_name,'') as [last_name],
gender as [gender],
isnull(email,'') as [email],
login_id as [login_id],
password as [password],
isnull(c.title_cd,'') as [title],
isnull(b.designation_cd,'') as [designation],
isnull(d.tel_no,'') as [tel_no],
isnull(d.fax_no,'') as [fax_no],
isnull(e.access_control_cd,'') as [mobile_access],
sales_type as [sales_type],
isnull(mt.member_type_code,'') as [member_type],
f.ug_cd as [usergroup_code],
STRING_AGG (h.branch_code,',') WITHIN GROUP (ORDER BY h.branch_code) as [branch_code],
isnull(i.country_code,'') as [country],
isnull(j.state_code,'') as [state],
isnull(d.address1,'') as [address_1],
isnull(d.address2,'') as [address_2],
isnull(d.address3,'') as [address_3],
isnull(d.postcode,'') as [postcode]
from member (nolock) a
left join member_contact_address (nolock) d on a.member_id = d.member_id
left join mobile_access_control (nolock) e on a.mobile_access_control_id = e.access_control_id
left join member_type (nolock) mt on a.member_type_id = mt.member_type_id
left join user_group (nolock) f on a.ug_id = f.ug_id
left join member_branch (nolock) g on a.member_id = g.member_id
left join mst_branch (nolock) h on g.branch_id = h.branch_id
left join country (nolock) i on d.country_id = i.country_id
left join state (nolock) j on d.state_id = j.state_id
left join title (nolock) c on a.title_id = c.title_id
left join designation (nolock) b on a.designation_id = b.designation_id
where a.org_id ='oromeo'
GROUP BY
a.member_cd, a.first_name, a.last_name, a.gender, a.email, a.dob, a.login_id,
a.password, c.title_cd, b.designation_cd, d.tel_no, d.fax_no, e.access_control_cd,
a.sales_type, mt.member_type_code, f.ug_cd, i.country_code, j.state_code,
d.address1, d.address2, d.address3, d.postcode
order by member_cd

Customer Template

select
a.customer_code as [customer_code],
a.customer_name as [customer_name],
a.customer_short_name as [customer_short_name],
b.branch_code as [branch_code],
STRING_AGG (ltrim(rtrim(isnull(m.first_name,'')+' '+isnull(m.last_name,''))),',') WITHIN GROUP (ORDER BY ltrim(rtrim(isnull(m.first_name,'')+' '+isnull(m.last_name,'')))) as [person_in_charge],
--a.person_in_charge as [person_in_charge],
ct.terms_code as [terms_code],
a.store_number as [store_number],
pc.price_code as [price_code],
mid.invoice_discount_code as [invoice_discount_code],
ma.area_code as [area_code],
a.special_instruction as [special_instruction],
a.contact_no as [tel_no],
a.pic as [pic],
mca.additional_tel_no as [additional_tel_no],
a.customer_on_hold as [customer_on_hold],
c.chain_code as [chain_code],
ch.channel_code as [channel_code],
cl.cluster_code as [cluster_code],
a.status as [status],
mca.address1 as [address1],
mca.address2 as [address2],
mca.address3 as [address3],
mca.address4 as [address4],
mca.address5 as [address5],
cy.country_code as [country_code],
s.state_code as [state_code],
mca.postcode as [postcode],
a.latitude as [latitude],
a.longitude as [longitude],
mca.radius as [radius],
r.region_code as [region_code]
from Customer (nolock) a
inner join mst_branch (nolock) b on a.branch_id=b.branch_id and b.org_id= a.org_id
inner join state (nolock) s on a.state_id=s.state_id and s.org_id = a.org_id
inner join chain (nolock) c on a.chain_id=c.chain_id and c.org_id= a.org_id
inner join channel (nolock) ch on a.channel_id=ch.channel_id and ch.org_id= a.org_id
inner join cluster (nolock) cl on a.cluster_id=cl.cluster_id and cl.org_id= a.org_id and cl.channel_id = ch.channel_id
inner join country (nolock) cy on a.country_id=cy.country_id and cy.org_id= a.org_id
inner join mst_area (nolock) ma on a.area_id=ma.area_id and ma.org_id= a.org_id and b.branch_id = ma.branch_id
inner join mst_terms_code (nolock) ct on a.terms_id=ct.terms_id and ct.org_id= a.org_id
inner join mst_price_code (nolock) pc on a.price_id=pc.price_id and pc.org_id= a.org_id
inner join mst_invoice_discount (nolock) mid on a.invoice_discount_id= mid.invoice_discount_id and mid.org_id= a.org_id
inner join mst_customer_contact_address (nolock) mca on mca.customer_id=a.customer_id and mca.org_id=c.org_id and mca.address_type='Contact'
inner join mst_region (nolock) r on r.region_id = mca.region_id and r.org_id = a.org_id
left join customer_to_user_relation (nolock) cur on cur.customer_id=a.customer_id and cur.org_id=c.org_id
left join member (nolock) m on m.member_id=cur.user_id and m.org_id=c.org_id
where a.org_id='oromeo'
GROUP BY
a.customer_code, a.customer_name, a.customer_short_name, b.branch_code,
ct.terms_code, a.store_number, pc.price_code, mid.invoice_discount_code,
ma.area_code, a.special_instruction, a.contact_no, a.pic, mca.additional_tel_no,
a.customer_on_hold, c.chain_code, ch.channel_code, cl.cluster_code, a.status,
mca.address1, mca.address2, mca.address3, mca.address4, mca.address5,
cy.country_code, s.state_code, mca.postcode, a.latitude, a.longitude, mca.radius, r.region_code
order by a.customer_code

SKU_Info Template

select *
from product_item
where org_id = 'oromeo'

select *
from product_group
where org_id = 'oromeo'

select
a.item_code as [SKU Code],
a.item_name as [SKU Name],
a.item_short_name as [SKU Short Name],
a.item_desc as [SKU Description],
mst5.group_code as [Product Group Code],
mst3.sub_group_code as [Item Group Code],
mst2.package_group_code as [Packaging Group Code],
mst_uom_code.product_uom_code as [Stock UOM],
mst_cf_alt_uom.product_uom_code as [Alternate Uom],
mst_oth_uom_code.product_uom_code as [Other Uom],
a.cf_alt_uom as [Conv Fact Alt Uom],
a.cf_oth_uom as [Conv Fact Oth Uom],
mst4.product_class_code as [Product Class],
mst.list_price_code as [List Price Code],
a.status as [Status],
a.barcode as [Barcode]
from product_item (nolock) a
inner join product_group (nolock) mst5 on a.group_id = mst5.group_id and mst5.org_id = a.org_id
inner join product_sub_group (nolock) mst3 on a.sub_group_id = mst3.sub_group_id and mst3.org_id = a.org_id and mst5.group_id = mst3.group_id
inner join mst_package_group (nolock)mst2 on a.package_group_id = mst2.package_group_id and mst2.org_id = a.org_id
inner join mst_product_uom (nolock) mst_uom_code on a.stock_uom_id = mst_uom_code.product_uom_id and mst_uom_code.org_id = a.org_id
inner join mst_product_uom (nolock) mst_cf_alt_uom on a.alt_uom_id = mst_cf_alt_uom.product_uom_id and mst_cf_alt_uom.org_id = a.org_id
inner join mst_product_uom (nolock) mst_oth_uom_code on a.oth_uom_id =mst_oth_uom_code.product_uom_id and mst_oth_uom_code.org_id = a.org_id
inner join mst_product_class (nolock) mst4 on a.product_class_id = mst4.product_class_id and mst4.org_id = a.org_id
inner join mst_list_price_code mst on a.list_price_code_id = mst.list_price_code_id and mst.org_id = a.org_id
where a.org_id='oromeo'
order by a.item_code

SKU_Price Template

select *
from product_item
where org_id = 'oromeo'

select *
from mst_product_price
where org_id = 'oromeo'

select *
from mst_price_code
where org_id = 'oromeo'

select p.item_code as [SKU Code], p.item_desc as [SKU Description], pc.price_code as [Price Code], mp.selling_price as [Selling Price], mp.price_basis as [Price Basis], p.Status
from product_item p inner join mst_product_price mp on p.item_id = mp.item_id inner join mst_price_code pc on mp.price_code_id = pc.price_id
where p.org_id = 'oromeo'