gistfile1.txt

declare @assc int; set @assc = 2000;
declare @org int; set @org = 3000;
declare @orgkey varchar; select @orgkey = keychain from m3_unionfusion_smw28.dbo.CON_Organization where Id = @org;
declare @uc bigint; select @uc = systemuserid from m3_unionfusion_smw28.dbo.Cor_Association where Id = @assc

declare @minid bigint; select @minid = m3_unionfusion_smw28.dbo.MinLineItemId();

;
with x as 
(select max(invoicelineitemid) maxid from import_membership_buildingtrades.dbo.invoicelineitem group by invoiceid)
--insert into m3_unionfusion_smw28.dbo.fin_order (id, lineitemnumber, lineitemtype, w_netamount, w_netamountpaid, w_netamountpending, w_netfees, w_netdiscount, w_netrefund, w_netliabilities,
--	isinactive, invoicedate, committed, paid, duedate, uc, dc,
--	autocoupons, name, entityid, organizationid, associationid, importid, oldpk, classname)
select
	@minid-row_number() over (order by li.dc desc), '-' + right('00000000' + convert(varchar,  abs(@minid-(ROW_NUMBER() over (order by li.dc desc)))), 9),
	1 as type, 0 as amount, 0 as paid, 0 as pending, 0 as fees, 0 as discount, 0 as refund, 0 as liabilities,
	0 as inactive, li.invoicedate as invoice, li.committed as committed, null as paiddate, li.duedate as duedate, @uc as uc, li.dc as dc,
	0 as autocoupons, li.name as name, li.entityid as entity, 3000 as org, 2000 as assc, 3000 as import, i.invoiceid as pk, 'Drg.M3.Domain.Financial.Order' as classname
from import_membership_buildingtrades.dbo.invoice i
	join import_membership_buildingtrades..invoicelineitem firstLineItem on firstLineItem.InvoiceId = i.InvoiceId
	join m3_unionfusion_smw28.dbo.fin_lineitem li on li.oldpk = firstlineitem.invoicelineitemid and li.importid = 3000
	join x on x.maxId = firstLineItem.invoicelineitemid
where i.systemid = 13

--drop index fin_lineitem.idx_lineitem_oldpk_importid
--create index idx_lineitem_oldpk_importid on fin_lineitem (OldPk, ImportId) include (Name, Committed, DueDate, InvoiceDate, DC, EntityId)