Datanory Production » Datanory - OROMEO

select * from #TempTable
select * from customer where org_id = 'OROMEO' order by updated_date desc --0

select * from #TempTable where customer_code not in
(select customer_code from customer where org_id = 'OROMEO') --420

select * from mst_branch where org_id = 'OROMEO' order by updated_date desc
select * from #TempTable where branch_code not in
(select branch_code from mst_branch where org_id = 'OROMEO')
--INSERT into mst_branch (branch_id, branch_code, branch_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
SELECT NEWID, x.branch_code, x.branch_code, 'Active', GETDATE, 'Support', GETDATE, 'Support', 'OROMEO', 'OROMEO'
from
(
select di.branch_code from #TempTable di
--where di.process_id = @ProcessId
group by di.branch_code
) x
where NOT EXISTS
--and x.process_id = @ProcessId
--and x.org_id = @OrganizationID

select * from mst_area where org_id = 'OROMEO' order by updated_date desc
select * from #TempTable where area_code not in
(select area_code from mst_area where org_id = 'OROMEO')
--INSERT into mst_area (area_id, area_code, area_description, status, branch_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
SELECT NEWID, x.area_code, x.area_code, 'Active', b.branch_id, GETDATE, 'Support', GETDATE, 'Support', 'OROMEO', 'OROMEO'
FROM
(
select di.area_code, di.branch_code from #TempTable di
--where di.process_id = @ProcessId
group by di.area_code, di.branch_code
) x
inner join mst_branch b on b.branch_code = x.branch_code and b.org_id = 'OROMEO'
where NOT EXISTS
--and x.process_id = @ProcessId
--and x.org_id = @OrganizationID --31

select a.area_code, a.branch_id, c.branch_id, * from mst_area a
inner join #TempTable b on b.area_code = a.area_code and a.org_id = 'OROMEO'
inner join mst_branch c on c.branch_code = b.branch_code and c.org_id = 'OROMEO'
where a.branch_id <> c.branch_id --45

--select * into DMS_Ecosystem_Backup_Table.dbo._mst_area_20240703 from DMS_EcoSystem.dbo.mst_area

--update a set a.branch_id = c.branch_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from mst_area a
--inner join #TempTable b on b.area_code = a.area_code and a.org_id = 'OROMEO'
--inner join mst_branch c on c.branch_code = b.branch_code and c.org_id = 'OROMEO'
--where a.branch_id <> c.branch_id

select a.branch_id, c.branch_id, a.area_id, d.area_id, * from customer a
inner join #TempTable b on b.customer_code = a.customer_code and a.org_id = 'OROMEO'
inner join mst_branch c on c.branch_code = b.branch_code and c.org_id = 'OROMEO'
inner join mst_area d on d.area_code = b.area_code and d.branch_id = c.branch_id and d.org_id = 'OROMEO'
WHERE EXISTS

--select * into DMS_Ecosystem_Backup_Table.dbo._customer_20240703 from DMS_EcoSystem.dbo.customer

--update a set a.branch_id = c.branch_id, a.area = d.area_code, a.area_id = d.area_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from customer a
--inner join #TempTable b on b.customer_code = a.customer_code and a.org_id = 'OROMEO'
--inner join mst_branch c on c.branch_code = b.branch_code and c.org_id = 'OROMEO'
--inner join mst_area d on d.area_code = b.area_code and d.branch_id = c.branch_id and d.org_id = 'OROMEO'
--WHERE EXISTS

select a.branch_id, b.branch_id, * from mst_customer_branch a
inner join customer b on b.customer_id = a.customer_id and b.org_id = 'OROMEO'
inner join #TempTable c on c.customer_code = b.customer_code and b.org_id = 'OROMEO'
inner join mst_branch d on d.branch_code = c.branch_code and d.org_id = 'OROMEO'
WHERE EXISTS (SELECT 1 FROM mst_customer_branch st WHERE st.customer_id = b.customer_id and st.org_id = 'OROMEO')

--select * into DMS_Ecosystem_Backup_Table.dbo._mst_customer_branch_20240703 from DMS_EcoSystem.dbo.mst_customer_branch

--update a set a.branch_id = d.branch_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from mst_customer_branch a
--inner join customer b on b.customer_id = a.customer_id and b.org_id = 'OROMEO'
--inner join #TempTable c on c.customer_code = b.customer_code and b.org_id = 'OROMEO'
--inner join mst_branch d on d.branch_code = c.branch_code and d.org_id = 'OROMEO'
--WHERE EXISTS (SELECT 1 FROM mst_customer_branch st WHERE st.customer_id = b.customer_id and st.org_id = 'OROMEO')

--delete from mst_branch where org_id = 'OROMEO' and branch_id not in
--(select branch_id from customer where org_id = 'OROMEO')
--and branch_code <> 'HQ'

select * from customer where org_id = 'OROMEO' and area_id not in
(select area_id from mst_area where org_id = 'OROMEO')

select a.branch_id, c.branch_id, * from customer a
inner join mst_area c on c.area_id = a.area_id and c.org_id = 'OROMEO'
where a.org_id = 'OROMEO'
and a.branch_id <> c.branch_id

--update a set a.branch_id = c.branch_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from customer a
--inner join mst_area c on c.area_id = a.area_id and c.org_id = 'OROMEO'
--where a.org_id = 'OROMEO'
--and a.branch_id <> c.branch_id

select a.branch_id, b.branch_id, * from mst_customer_branch a
inner join customer b on b.customer_id = a.customer_id and b.org_id = 'OROMEO'
where a.org_id = 'OROMEO'
and a.branch_id <> b.branch_id

--update a set a.branch_id = b.branch_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from mst_customer_branch a
--inner join customer b on b.customer_id = a.customer_id and b.org_id = 'OROMEO'
--where a.org_id = 'OROMEO'
--and a.branch_id <> b.branch_id

--update a set a.area_id = c.area_id, a.updated_date = getdate(), a.updated_by = 'Support'
--from customer a
----inner join mst_branch b on b.branch_id = a.branch_id and b.org_id = 'OROMEO'
--inner join mst_area c on c.area_code = a.area and c.org_id = 'OROMEO'
--where a.org_id = 'OROMEO'
--and a.area_id <> c.area_id