BEGIN TRAN 
CREATE TABLE #EntityIds(
EntityId BIGINT
)
DECLARE @EntityId INT
DECLARE @SalesOrderToCloneId INT
DECLARE @SupplyOrderToCloneId INT 
DECLARE @Verbose BIT = 0
DECLARE @InsertCount INT = 0

--DELETE ORIGINAL ORDER
DECLARE @DeleteOriginalOrder BIT = 0

--IF TRUE ALL STORES WILL GET A COPY OF THE CLONED ORDER, IF FALSE ENTITYIDS MUST BE SPECIFIED
DECLARE @UpdateAllEntities BIT = 0

-- SUPPY ORDER ID TO CLONE
SET @SupplyOrderToCloneId = 2049982
SELECT @SalesOrderToCloneId = TransactionSalesOrderId FROM tbTransactionSupplyOrder where iTransaction_num = @SupplyOrderToCloneId

IF(@UpdateAllEntities = 0)
BEGIN
INSERT INTO #EntityIds
SELECT EntityId FROM tbEntity
--WHERE EntityID in (3928) -- USE COMMA SEPARATED VALUES WITH ,
WHERE EntityTypeID = 4 and Entity between 'InForm Training 00550%' and 'InForm Training 00601%'
END
ELSE
BEGIN

--IF @UpdateAllEntities is set to true, all entities will be inserted into the entityids table
INSERT INTO #EntityIds
SELECT EntityId FROM tbEntity
WHERE EntityTypeID = 4 and Entityid not in (1920,3928) order by 1 asc

END

DECLARE EntityCursor CURSOR FOR
SELECT EntityId FROM #EntityIds

OPEN EntityCursor

FETCH NEXT FROM EntityCursor INTO
@EntityId

WHILE @@FETCH_STATUS = 0
BEGIN

SET @InsertCount = @InsertCount + 1

DECLARE @NewSalesOrderId INT 
DECLARE @NewSupplyOrderId INT 
DECLARE @AuthorizationId VARCHAR(50) 
DECLARE @Now DATETIME = Getdate()
DECLARE @StoreUser VARCHAR(50)
SET @AuthorizationId = 'AUTO-RECEIVED' 

SELECT @StoreUser = UserName FROM tbUser WHERE EntityID = @EntityId AND FirstName = 'InFORM'

IF (@Verbose = 1)
BEGIN
SELECT 'CLONING SALES ORDER = ' + CONVERT(VARCHAR(50),@SalesOrderToCloneId) + ' TO ENTITY ' + CONVERT(VARCHAR(50),@EntityId)
END

-- CLONE SALES ORDER 
INSERT INTO tbtransactionsalesorder 
            ([itransaction_typ], 
             [iquote_num], 
             [iorder_typ], 
             [istatus_typ], 
             [ientry_dt], 
             [iapply_dt], 
             [irequired_dt], 
             [ireceived_dt], 
             [idue_dt], 
             [stax_cd], 
             [iterms_cd], 
             [sposting_cd], 
             [ssalesrep_cd], 
             [sdunn_cd], 
             [sprice_cd], 
             [mtaxable_amt], 
             [mnontaxable_amt], 
             [mtax_amt], 
             [mfreight_amt], 
             [mtotal_amt], 
             [mpaid_amt], 
             [mdue_amt], 
             [mblanket_amt], 
             [mshipped_amt], 
             [mdiscount_amt], 
             [ftax_pc], 
             [scustomer_cd], 
             [scustomer_nm], 
             [scustomerattn], 
             [scustomeraddress1], 
             [scustomeraddress2], 
             [scustomeraddress3], 
             [sshipto_cd], 
             [sshiptoattn], 
             [sshipto_nm], 
             [sshiptoaddress1], 
             [sshiptoaddress2], 
             [sshiptoaddress3], 
             [syourreference], 
             [scomment], 
             [svia_cd], 
             [sfob_cd], 
             [slocation_cd], 
             [itoapprove_num], 
             [inextline_id], 
             [sreference], 
             [suserapproved], 
             [irecurring_typ], 
             [itorecur_num], 
             [itorecur_dt], 
             [isplitcomm_fl], 
             [iprinted_num], 
             [itobeinvoiced_num], 
             [iinvoiced_num], 
             [isource_typ], 
             [isource_num], 
             [dtpost_dt], 
             [dtlastupdate_dt], 
             [entityid], 
             [scommentfororder], 
             [sstatus], 
             [ishipmentorder], 
             [sordernote], 
             [distributionauthorised], 
             [distributionauthorisedate], 
             [distributionauthorisedby], 
             [distributionsupplyordercreated], 
             [distributionsupplyordercreatedate], 
             [distributionsupplyorderauthorisedby], 
             [distributionprintinvoice], 
             [distributionprintinvoicedate], 
             [distributionprintinvoiceby], 
             [distributionpayment], 
             [distributionpaymentdate], 
             [distributionpaymentby], 
             [slastupdate_id], 
             [vendorid], 
             [vendorname], 
             [blanketorderno], 
             [overwritetax], 
             [freighttaxable], 
             [mfreighttax_amt], 
             [orderclassid], 
             [daystocover], 
             [customsortbyfield], 
             [customersortdirection], 
             [controlordertotal], 
             [createdby], 
             [coveruntildate]) 
SELECT [itransaction_typ], 
       [iquote_num], 
       [iorder_typ], 
       [istatus_typ], 
       [ientry_dt], 
       [iapply_dt], 
       [irequired_dt], 
       [ireceived_dt], 
       [idue_dt], 
       [stax_cd], 
       [iterms_cd], 
       [sposting_cd], 
       [ssalesrep_cd], 
       [sdunn_cd], 
       [sprice_cd], 
       [mtaxable_amt], 
       [mnontaxable_amt], 
       [mtax_amt], 
       [mfreight_amt], 
       [mtotal_amt], 
       [mpaid_amt], 
       [mdue_amt], 
       [mblanket_amt], 
       [mshipped_amt], 
       [mdiscount_amt], 
       [ftax_pc], 
       [scustomer_cd], 
       [scustomer_nm], 
       [scustomerattn], 
       [scustomeraddress1], 
       [scustomeraddress2], 
       [scustomeraddress3], 
       [sshipto_cd], 
       [sshiptoattn], 
       entity.Entity, 
       entity.MailAddress1, 
       entity.MailAddress2, 
       entity.MailState, 
       [syourreference], 
       [scomment], 
       [svia_cd], 
       [sfob_cd], 
       [slocation_cd], 
       [itoapprove_num], 
       [inextline_id], 
       [sreference], 
       [suserapproved], 
       [irecurring_typ], 
       [itorecur_num], 
       [itorecur_dt], 
       [isplitcomm_fl], 
       [iprinted_num], 
       [itobeinvoiced_num], 
       [iinvoiced_num], 
       [isource_typ], 
       [isource_num], 
       [dtpost_dt], 
       [dtlastupdate_dt], 
       @EntityId, 
       [scommentfororder], 
       [sstatus], 
       [ishipmentorder], 
       [sordernote], 
       [distributionauthorised], 
       [distributionauthorisedate], 
       @StoreUser, 
       [distributionsupplyordercreated], 
       [distributionsupplyordercreatedate], 
       @StoreUser, 
       [distributionprintinvoice], 
       [distributionprintinvoicedate], 
       @StoreUser, 
       [distributionpayment], 
       [distributionpaymentdate], 
       @StoreUser, 
       @StoreUser, 
       [vendorid], 
       [vendorname], 
       [blanketorderno], 
       [overwritetax], 
       [freighttaxable], 
       [mfreighttax_amt], 
       [orderclassid], 
       [daystocover], 
       [customsortbyfield], 
       [customersortdirection], 
       [controlordertotal], 
       @StoreUser, 
       [coveruntildate] 
FROM   [tbtransactionsalesorder] 
JOIN tbEntity entity ON entity.EntityID = @EntityId
WHERE  itransaction_num = @SalesOrderToCloneId 

SELECT @NewSalesOrderId = Scope_identity() 

IF (@Verbose = 1)
BEGIN
SELECT 'tbTransactionSalesOrder' 

-- COMPARISON SELECTS 
SELECT * 
FROM   [tbtransactionsalesorder] 
WHERE  itransaction_num = @NewSalesOrderId 

SELECT * 
FROM   [tbtransactionsalesorder] 
WHERE  itransaction_num = @SalesOrderToCloneId 

END

INSERT INTO tbtransactionsalesorderdetail 
            ([itransaction_typ], 
             [itransaction_num], 
             [idetail_num], 
             [istatus_typ], 
             [sitem_cd], 
             [slocation_cd], 
             [iapply_dt], 
             [ientry_dt], 
             [sdescription], 
             [fordered_qty], 
             [fshipped_qty], 
             [fbackorder_qty], 
             [freturned_qty], 
             [ssellunit_cd], 
             [msellunitprice_amt], 
             [msellunitextended_amt], 
             [sunit_cd], 
             [munitprice_amt], 
             [mextended_amt], 
             [stax_cd], 
             [sjob_cd], 
             [srevenueacct_cd], 
             [mcost_amt], 
             [mtax_amt], 
             [ftax_pc], 
             [mdiscount_amt], 
             [fdiscount_pc], 
             [sivunit_cd], 
             [finivunit_qty], 
             [iitem_typ], 
             [fconversionrate], 
             [iline_id], 
             [ikit_id], 
             [isourcedetail_num], 
             [entityid], 
             [itemid], 
             [salesorderdate], 
             [purchaseunit_cd], 
             [purchaseunit_qty], 
             [purchaseunit_conversionrate], 
             [itaxableflag], 
             [vendorid], 
             [sortorder], 
             [originalorderqty], 
             [minorderqty], 
             [maxorderqty], 
             [buildtolevelqty], 
             [usage], 
             [safetystock]) 
SELECT [itransaction_typ], 
       @NewSalesOrderId, 
       [idetail_num], 
       [istatus_typ], 
       [sitem_cd], 
       [slocation_cd], 
       [iapply_dt], 
       [ientry_dt], 
       [sdescription], 
       [fordered_qty], 
       [fshipped_qty], 
       [fbackorder_qty], 
       [freturned_qty], 
       [ssellunit_cd], 
       [msellunitprice_amt], 
       [msellunitextended_amt], 
       [sunit_cd], 
       [munitprice_amt], 
       [mextended_amt], 
       [stax_cd], 
       [sjob_cd], 
       [srevenueacct_cd], 
       [mcost_amt], 
       [mtax_amt], 
       [ftax_pc], 
       [mdiscount_amt], 
       [fdiscount_pc], 
       [sivunit_cd], 
       [finivunit_qty], 
       [iitem_typ], 
       [fconversionrate], 
       [iline_id], 
       [ikit_id], 
       [isourcedetail_num], 
       @EntityId, 
       [itemid], 
       [salesorderdate], 
       [purchaseunit_cd], 
       [purchaseunit_qty], 
       [purchaseunit_conversionrate], 
       [itaxableflag], 
       [vendorid], 
       [sortorder], 
       [originalorderqty], 
       [minorderqty], 
       [maxorderqty], 
       [buildtolevelqty], 
       [usage], 
       [safetystock] 
FROM   tbtransactionsalesorderdetail 
WHERE  itransaction_num = @SalesOrderToCloneId 

IF(@Verbose = 1)
BEGIN
SELECT 'tbTransactionSalesOrderDetail' 

-- COMPARISON SELECTS 
SELECT * 
FROM   tbtransactionsalesorderdetail 
WHERE  itransaction_num = @NewSalesOrderId 

SELECT * 
FROM   tbtransactionsalesorderdetail 
WHERE  itransaction_num = @SalesOrderToCloneId 

END

select top 1 * from tbtransactionsalesorderdetailitemquantity

INSERT INTO tbtransactionsalesorderdetailitemquantity(
	TransactionSalesOrderDetailId,
	OnHandQty,
	OnOrderQty,
	SuggestedQty,
	IsSuggestedItem,OriginalSuggestedQty,
	HasBeenManuallyEdited,
	InsideSalesUsage,
	CateringPickupUsage,
	OriginalOnHandQty)
SELECT new.transactionsalesorderdetailid, 
       itemquantitygolden.onhandqty, 
       itemquantitygolden.onorderqty, 
       itemquantitygolden.suggestedqty, 
       itemquantitygolden.issuggesteditem, 
       itemquantitygolden.originalsuggestedqty, 
       itemquantitygolden.hasbeenmanuallyedited, 
       itemquantitygolden.insidesalesusage, 
       itemquantitygolden.cateringpickupusage, 
       itemquantitygolden.originalonhandqty 
	   --NULL THERE IS AN EXTRA COLUMN IN JAT 
FROM   tbtransactionsalesorderdetail golden 
       JOIN tbtransactionsalesorderdetail new 
         ON new.sdescription = golden.sdescription 
            AND new.itransaction_num = @NewSalesOrderId 
       JOIN tbtransactionsalesorderdetailitemquantity itemquantitygolden 
         ON itemquantitygolden.transactionsalesorderdetailid = 
            golden.transactionsalesorderdetailid 
WHERE  golden.itransaction_num = @SalesOrderToCloneId 

IF(@Verbose = 1)
BEGIN
SELECT 'tbTransactionSalesOrderDetailItemQuantity' 

-- COMPARISON SELECTS 
SELECT * 
FROM   tbtransactionsalesorderdetailitemquantity 
WHERE  transactionsalesorderdetailid IN (SELECT transactionsalesorderdetailid 
                                         FROM   tbtransactionsalesorderdetail 
                                         WHERE itransaction_num = @NewSalesOrderId) 

SELECT * 
FROM   tbtransactionsalesorderdetailitemquantity 
WHERE  transactionsalesorderdetailid IN (SELECT transactionsalesorderdetailid 
                                         FROM   tbtransactionsalesorderdetail 
                                         WHERE itransaction_num = @SalesOrderToCloneId) 

END										 
---------------------------------------------------------------------------------------------------------------------------------------------- 
-- CLONE SALES ORDER 


-- CLONE SUPPLY ORDER 
INSERT INTO tbtransactionsupplyorder 
            ([itransaction_typ], 
             [iquote_num], 
             [iorder_typ], 
             [istatus_typ], 
             [ientry_dt], 
             [iapply_dt], 
             [irequired_dt], 
             [ireceived_dt], 
             [idue_dt], 
             [stax_cd], 
             [iterms_cd], 
             [sposting_cd], 
             [ssalesrep_cd], 
             [sdunn_cd], 
             [sprice_cd], 
             [mtaxable_amt], 
             [mnontaxable_amt], 
             [mtax_amt], 
             [mfreight_amt], 
             [mtotal_amt], 
             [mpaid_amt], 
             [mdue_amt], 
             [mblanket_amt], 
             [mshipped_amt], 
             [mdiscount_amt], 
             [ftax_pc], 
             [scustomer_cd], 
             [scustomer_nm], 
             [scustomerattn], 
             [scustomeraddress1], 
             [scustomeraddress2], 
             [scustomeraddress3], 
             [sshipto_cd], 
             [sshiptoattn], 
             [sshipto_nm], 
             [sshiptoaddress1], 
             [sshiptoaddress2], 
             [sshiptoaddress3], 
             [syourreference], 
             [scomment], 
             [svia_cd], 
             [sfob_cd], 
             [slocation_cd], 
             [itoapprove_num], 
             [inextline_id], 
             [sreference], 
             [suserapproved], 
             [irecurring_typ], 
             [itorecur_num], 
             [itorecur_dt], 
             [isplitcomm_fl], 
             [iprinted_num], 
             [itobeinvoiced_num], 
             [iinvoiced_num], 
             [isource_typ], 
             [isource_num], 
             [dtpost_dt], 
             [dtlastupdate_dt], 
             [entityid], 
             [scommentfororder], 
             [sstatus], 
             [ishipmentorder], 
             [sordernote], 
             [svendor_cd], 
             [svendor_nm], 
             [svendorattn], 
             [svendoraddress1], 
             [svendoraddress2], 
             [svendoraddress3], 
             [distributionprintorder], 
             [distributionprintorderdate], 
             [distributionprintorderby], 
             [distributionvendoracknowledge], 
             [distributionvendoracknowledgedate], 
             [distributionvendoracknowledgeby], 
             [distributiongoodsdispatched], 
             [distributiongoodsdispatcheddate], 
             [distributiongoodsdispatchedby], 
             [distributiongoodsreceived], 
             [distributiongoodsreceiveddate], 
             [distributiongoodsreceivedby], 
             [transactionsalesorderid], 
             [distributionprintinvoice], 
             [distributionprintinvoicedate], 
             [distributionprintinvoiceby], 
             [distributionauthorised], 
             [distributionauthorisedby], 
             [distributionauthorisedate], 
             [slastupdate_id], 
             [distributionpayment], 
             [distributionpaymentby], 
             [distributionpaymentdate], 
             [blanketorderno], 
             [createdfromorder], 
             [consolidatedinvoicenum], 
             [approvedforpayment], 
             [invoicereconcomment], 
             [invoiceamount], 
             [overwritetax], 
             [freighttaxable], 
             [mfreighttax_amt], 
             [orderclassid], 
             [extendschedulereceivedatebyhour], 
             [receivingcomments], 
             [invoicetaxamount], 
             [splitorder], 
             [parentorderid], 
             [autostatus], 
             [originalapplydate], 
             [receivedontime], 
             [publishdate], 
             [receivedshippingnotification], 
             [deliverycheckid], 
             [pushedtotomorrowdate]) 
SELECT [itransaction_typ], 
       [iquote_num], 
       [iorder_typ], 
       [istatus_typ], 
       [ientry_dt], 
       [iapply_dt], 
       [irequired_dt], 
       [ireceived_dt], 
       [idue_dt], 
       [stax_cd], 
       [iterms_cd], 
       [sposting_cd], 
       [ssalesrep_cd], 
       [sdunn_cd], 
       [sprice_cd], 
       [mtaxable_amt], 
       [mnontaxable_amt], 
       [mtax_amt], 
       [mfreight_amt], 
       [mtotal_amt], 
       [mpaid_amt], 
       [mdue_amt], 
       [mblanket_amt], 
       [mshipped_amt], 
       [mdiscount_amt], 
       [ftax_pc], 
       [scustomer_cd], 
       [scustomer_nm], 
       [scustomerattn], 
       [scustomeraddress1], 
       [scustomeraddress2], 
       [scustomeraddress3], 
       [sshipto_cd], 
       [sshiptoattn], 
       entity.Entity, 
       [sshiptoaddress1], 
       [sshiptoaddress2], 
       [sshiptoaddress3], 
       [syourreference], 
       [scomment], 
       [svia_cd], 
       [sfob_cd], 
       [slocation_cd], 
       [itoapprove_num], 
       [inextline_id], 
       [sreference], 
       [suserapproved], 
       [irecurring_typ], 
       [itorecur_num], 
       [itorecur_dt], 
       [isplitcomm_fl], 
       [iprinted_num], 
       [itobeinvoiced_num], 
       [iinvoiced_num], 
       [isource_typ], 
       [isource_num], 
       [dtpost_dt], 
       [dtlastupdate_dt], 
       @EntityId, 
       [scommentfororder], 
       [sstatus], 
       [ishipmentorder], 
       [sordernote], 
       [svendor_cd], 
       [svendor_nm], 
       [svendorattn], 
       [svendoraddress1], 
       [svendoraddress2], 
       [svendoraddress3], 
       1, 
       @Now, 
       @StoreUser + ' - ' + StoreNumber,
       1, 
       @Now, 
       'System', 
       1, 
       @Now, 
       'System', 
       1, 
       [distributiongoodsreceiveddate], 
       @AuthorizationId, 
       @NewSalesOrderId, 
       [distributionprintinvoice], 
       [distributionprintinvoicedate], 
       [distributionprintinvoiceby], 
       [distributionauthorised], 
       @StoreUser, 
       [distributionauthorisedate], 
       @AuthorizationId, 
       [distributionpayment], 
       [distributionpaymentby], 
       [distributionpaymentdate], 
       [blanketorderno], 
       [createdfromorder], 
       [consolidatedinvoicenum], 
       [approvedforpayment], 
       [invoicereconcomment], 
       [invoiceamount], 
       [overwritetax], 
       [freighttaxable], 
       [mfreighttax_amt], 
       [orderclassid], 
       0, 
       [receivingcomments], 
       [invoicetaxamount], 
       [splitorder], 
       [parentorderid], 
       [autostatus], 
       [originalapplydate], 
       [receivedontime], 
       [publishdate], 
       [ReceivedShippingNotification], 
       [deliverycheckid], 
       [pushedtotomorrowdate] 
FROM   tbtransactionsupplyorder 
JOIN tbEntity entity ON entity.EntityID = @EntityId
WHERE  transactionsalesorderid = @SalesOrderToCloneId 

SELECT @NewSupplyOrderId = Scope_identity() 

IF(@Verbose = 1)
BEGIN

SELECT 'tbTransactionSupplyOrder' 

-- COMPARISON SELECTS 
SELECT * 
FROM   tbtransactionsupplyorder 
WHERE  itransaction_num = @NewSupplyOrderId 

SELECT * 
FROM   tbtransactionsupplyorder 
WHERE  itransaction_num = @SupplyOrderToCloneId 

END

INSERT INTO tbtransactionsupplyorderdetail 
            ([itransaction_typ], 
             [itransaction_num], 
             [idetail_num], 
             [istatus_typ], 
             [sitem_cd], 
             [slocation_cd], 
             [iapply_dt], 
             [ientry_dt], 
             [sdescription], 
             [fordered_qty], 
             [fshipped_qty], 
             [fbackorder_qty], 
             [freturned_qty], 
             [ssellunit_cd], 
             [msellunitprice_amt], 
             [msellunitextended_amt], 
             [sunit_cd], 
             [munitprice_amt], 
             [mextended_amt], 
             [stax_cd], 
             [sjob_cd], 
             [srevenueacct_cd], 
             [mcost_amt], 
             [mtax_amt], 
             [ftax_pc], 
             [mdiscount_amt], 
             [fdiscount_pc], 
             [sivunit_cd], 
             [finivunit_qty], 
             [iitem_typ], 
             [fconversionrate], 
             [iline_id], 
             [ikit_id], 
             [isourcedetail_num], 
             [entityid], 
             [itemid], 
             [supplyorderdate], 
             [itaxableflag], 
             [sortorder], 
             [lotnumber], 
             [vendorshippedqty], 
             [authorisedorderqty], 
             [overreceive], 
             [short], 
             [damaged], 
             [lowquality], 
             [overshelf], 
             [comments], 
             [addedonreceive], 
             [capturedreceiveqty], 
             [capturedreturnqty], 
             [receiveunit], 
             [itemtaxrate], 
             [originalreceivedqty]) 
SELECT [itransaction_typ], 
       @NewSupplyOrderId, 
       [idetail_num], 
       [istatus_typ], 
       [sitem_cd], 
       [slocation_cd], 
       [iapply_dt], 
       [ientry_dt], 
       [sdescription], 
       [fordered_qty], 
       [fshipped_qty], 
       [fbackorder_qty], 
       [freturned_qty], 
       [ssellunit_cd], 
       [msellunitprice_amt], 
       [msellunitextended_amt], 
       [sunit_cd], 
       [munitprice_amt], 
       [mextended_amt], 
       [stax_cd], 
       [sjob_cd], 
       [srevenueacct_cd], 
       [mcost_amt], 
       [mtax_amt], 
       [ftax_pc], 
       [mdiscount_amt], 
       [fdiscount_pc], 
       [sivunit_cd], 
       [finivunit_qty], 
       [iitem_typ], 
       [fconversionrate], 
       [iline_id], 
       [ikit_id], 
       [isourcedetail_num], 
       @EntityId, 
       [itemid], 
       [supplyorderdate], 
       [itaxableflag], 
       [sortorder], 
       [lotnumber], 
       [vendorshippedqty], 
       [authorisedorderqty], 
       [overreceive], 
       [short], 
       [damaged], 
       [lowquality], 
       [overshelf], 
       [comments], 
       [addedonreceive], 
       [capturedreceiveqty], 
       [capturedreturnqty], 
       [receiveunit], 
       [itemtaxrate], 
       [originalreceivedqty] 
FROM   tbtransactionsupplyorderdetail 
WHERE  itransaction_num = @SupplyOrderToCloneId 

IF(@Verbose = 1)
BEGIN

SELECT 'tbTransactionSupplyOrderDetail' 

-- COMPARISON SELECTS 
SELECT * 
FROM   tbtransactionsupplyorderdetail 
WHERE  itransaction_num = @NewSupplyOrderId 

SELECT * 
FROM   tbtransactionsupplyorderdetail 
WHERE  itransaction_num = @SupplyOrderToCloneId 

END
----------------------------------------------------------------------------------------------------------------------------- 
-- AUTO RECEIVE 
EXEC Qrymmstransactionsupplyorderdetail_deletezeroqty_num 
  @Tx_num = @NewSupplyOrderId 

--ADD ITEM QUANTITIES 
DECLARE @SQL VARCHAR(max) = '' 

SELECT @SQL = @SQL 
              + 'EXEC qryMMSItemQty_Insert @ItemId = ' 
              + CONVERT(VARCHAR(max), detail.itemid) 
              + ', @EntityId = ' 
              + CONVERT(VARCHAR(max), @EntityId) + ';' 
FROM   tbtransactionsupplyorderdetail detail 
       LEFT JOIN tbitemqty itemQty 
              ON itemQty.entityid = detail.entityid 
                 AND itemQty.itemid = detail.itemid 
WHERE  itransaction_num = @NewSupplyOrderId 
       AND itemQty.itemid IS NULL 

EXEC(@SQL) 

IF(@Verbose = 1)
BEGIN

SELECT 'tbItemQty' 

-- COMPARISON SELECTS 
SELECT * 
FROM   tbitemqty quantity 
       JOIN tbtransactionsupplyorderdetail detail 
         ON detail.entityid = quantity.entityid 
            AND detail.itransaction_num = @NewSupplyOrderId 
            AND detail.itemid = quantity.itemid 
WHERE  quantity.entityid = @EntityId 

END

--ADD ITEM QUANTITIES 
--AUTO RECEIVE DETAILS 
EXEC Qrymmstransactionsupplyorderdetail_autoreceive 
  @ID = @NewSupplyOrderId 

DECLARE @ReceiveDate DATETIME 

SELECT @ReceiveDate = Isnull(Dateadd(mi, autoreceivedelayminutes, @Now), Dateadd(mi, 360, @Now)) 
FROM   tbvendorentity 

EXEC Qrymmstransactionsupplyorder_autoreceive 
  @Id = @NewSupplyOrderId, 
  @AuthorisedBy = @AuthorizationId, 
  @Date = @ReceiveDate, 
  @OriginalApplyDate = @Now, 
  @AutoStatus = 8 -- AUTO RECEIVE 


IF(@Verbose = 1)
BEGIN

SELECT 'tbTransactionSupplyOrder' 

SELECT * 
FROM   tbtransactionsupplyorder 
WHERE  itransaction_num = @NewSupplyOrderId 

SELECT * 
FROM   tbtransactionsupplyorder 
WHERE  itransaction_num = @SupplyOrderToCloneId 

END

DECLARE @PeriodInfo TABLE 
  ( 
     fiscalyear INT, 
     startdate  DATETIME, 
     enddate    DATETIME, 
     [now]      DATETIME, 
     [period]   INT 
  ) 
DECLARE @UnitConversion TABLE 
  ( 
     transactionsupplyorderdetailid INT, 
     entityid                       INT, 
     itemid                         INT, 
     conversion                     FLOAT 
  ) 
----------------------- 
DECLARE @SellUnit_Cd                    VARCHAR(50), 
        @sIVUnit_Cd                     VARCHAR(50), 
        @TransactionSupplyOrderDetailId INT, 
        @ItemId                         INT 
DECLARE detailscursor CURSOR FOR 
  SELECT ssellunit_cd, 
         sivunit_cd, 
         transactionsupplyorderdetailid, 
         itemid 
  FROM   tbtransactionsupplyorderdetail 
  WHERE  itransaction_num = @NewSupplyOrderId 

OPEN detailscursor 

FETCH next FROM detailscursor INTO @SellUnit_Cd, @sIVUnit_Cd, 
@TransactionSupplyOrderDetailId, @ItemId 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      DECLARE @ConversionTable TABLE 
        ( 
           conversion FLOAT 
        ) 

      INSERT INTO @ConversionTable 
      EXEC Qrymmsunit_getconversion 
        @FromUnit = @SellUnit_Cd, 
        @ToUnit = @sIVUnit_Cd 

      INSERT INTO @UnitConversion 
      SELECT @TransactionSupplyOrderDetailId, 
             @EntityId, 
             @ItemId, 
             conversion 
      FROM   @ConversionTable 

      DELETE FROM @ConversionTable 

      FETCH next FROM detailscursor INTO @SellUnit_Cd, @sIVUnit_Cd, 
      @TransactionSupplyOrderDetailId, @ItemId 
  END 

CLOSE detailscursor; 

DEALLOCATE detailscursor; 

----------------------- 
INSERT INTO @PeriodInfo 
SELECT sfiscalyear, 
       iperiodbegin_dt, 
       iperiodend_dt, 
       @Now, 
       periodno 
FROM   tbperioddetail 
WHERE  Getdate() >= iperiodbegin_dt 
       AND Getdate() < iperiodend_dt 

INSERT INTO tbtransactionreceivelog 
            ([fiscalyear], 
             [startdate], 
             [enddate], 
             [periodno], 
             [transactionsupplyorderdetailid], 
             [itransaction_num], 
             [itemid], 
             [applydate], 
             [entityid], 
             [itaxableflag], 
             [purchaseunit], 
             [inventoryunit], 
             [fconversionrate], 
             [purchaseunitcost], 
             [inventoryunitcost], 
             [mtax_amt], 
             [ftax_pc], 
             [totalpurchasecost], 
             [totalinventorycost], 
             [purchaseamt], 
             [inventoryamt], 
             [importtopurchasejournal], 
             [received_dt], 
             [businessday]) 
SELECT PeriodInfo.fiscalyear, 
       PeriodInfo.startdate, 
       PeriodInfo.enddate, 
       PeriodInfo.[period], 
       detail.transactionsupplyorderdetailid, 
       itransaction_num, 
       detail.itemid, 
       @Now, 
       @EntityId, 
       itaxableflag, 
       sunit_cd, 
       sivunit_cd, 
       fconversionrate, 
       munitprice_amt, 
       munitprice_amt / fconversionrate, 
       ( ftax_pc / 100 ) * fshipped_qty, 
       ftax_pc, 
       munitprice_amt * fshipped_qty, 
       munitprice_amt * fshipped_qty, 
       fshipped_qty, 
       fshipped_qty * unitConversion.conversion, 
       0, 
       @Now, 
       CONVERT(DATETIME, Datediff(day, 0, @Now)) 
FROM   tbtransactionsupplyorderdetail detail 
       JOIN @UnitConversion unitConversion 
         ON unitConversion.entityid = @EntityId 
            AND unitConversion.transactionsupplyorderdetailid = 
                detail.transactionsupplyorderdetailid 
            AND unitConversion.itemid = detail.itemid 
       JOIN @PeriodInfo PeriodInfo 
         ON PeriodInfo.[now] = @Now 
WHERE  itransaction_num = @NewSupplyOrderId 

IF(@Verbose = 1)
BEGIN

SELECT 'tbTransactionReceiveLog' 

SELECT * 
FROM   tbtransactionreceivelog 
WHERE  itransaction_num = @NewSupplyOrderId 

END

--UPDATING SHIPPING RECEIVEDSHIPPINGNOTIFICATION
UPDATE tbtransactionsupplyorder SET ReceivedShippingNotification = 1
WHERE iTransaction_num = @NewSupplyOrderId

UPDATE tbTransactionSupplyOrderDetail SET fShipped_qty = fOrdered_qty, VendorShippedQty = fOrdered_qty
WHERE iTransaction_num = @NewSupplyOrderId

FETCH NEXT FROM EntityCursor INTO 
@EntityId

END

CLOSE EntityCursor;
DEALLOCATE EntityCursor;

-- DELETE ORIGINAL ORDER
IF (@DeleteOriginalOrder = 1)
BEGIN

DELETE FROM tbTransactionReceiveLog WHERE iTransaction_num = @SupplyOrderToCloneId

DELETE quantity
FROM   tbitemqty quantity 
       JOIN tbtransactionsupplyorderdetail detail 
         ON detail.entityid = quantity.entityid 
            AND detail.itransaction_num = @SupplyOrderToCloneId 
            AND detail.itemid = quantity.itemid 

DELETE FROM tbTransactionSupplyOrderDetail WHERE iTransaction_num = @SupplyOrderToCloneId
DELETE FROM tbTransactionSupplyOrder WHERE iTransaction_num = @SupplyOrderToCloneId 

DELETE FROM tbTransactionSalesOrderDetailItemQuantity WHERE TransactionSalesOrderDetailId 
IN (SELECT TransactionSalesOrderDetailId FROM tbTransactionSalesOrderDetail WHERE iTransaction_num = @SalesOrderToCloneId)

DELETE FROM tbTransactionSalesOrderDetail where iTransaction_num = @SalesOrderToCloneId
DELETE FROM tbTransactionSalesOrder WHERE iTransaction_num = @SalesOrderToCloneId

IF(@Verbose = 1)

BEGIN
SELECT 'ORIGINAL ORDER DELETED, SALES ORDER = ' + CONVERT(VARCHAR(50),@SalesOrderToCloneId) + ', SUPPLY ORDER = ' + CONVERT(VARCHAR(50),@SupplyOrderToCloneId)
END


END

SELECT 'Created ' + CONVERT(VARCHAR(MAX),@InsertCount) + ' Orders'
------------------------------------------------------------------------------------------------------------------------------------------------------- 

DROP TABLE #EntityIds

--COMMIT TRAN 
--ROLLBACK TRAN