VS Tools Forum » Dynamics GP » eConnect for Dynamics GP » Automating Assembly Transactions
Site Search:
Sign in | Join | Help

Automating Assembly Transactions

Last post 10-22-2008 12:38 PM by Joe Cogan. 4 replies.
Page 1 of 1 (5 items)
Sort Posts: Previous Next

  • 02-27-2008 8:29 PM

    Automating Assembly Transactions

    Reply Contact

    Hey Steve!

     I know that this is the eConnect forum but I thought you'd be interested in this particular challenge.

     My client had a need for an inventory importing tool (using SerialNums).  I used a combination of SSIS, .Net (wrapper) and eConnect.  It works great.  Well, now they want me to extend the tool to include assembly transactions.  Well, after I told them "sure", I found out that eConnect doesn't do Assembly Transactions!  So, I'm trying to build a SQL script to automate it myself.

    After using SQL Trace to see what happens during an assembly transaction insert, I've extracted over 700 lines of t-SQL.  (Dexterity does some pretty weird stuff...)  Have you had any experience with automating assembly transactions?  I'm hoping to boil the trx down to a handful of table inserts and/or updates.

     Let me know.  Thanks!

    Joe Cogan
    joecogan@adelphia.net
    • 76.189.252.140

  • 02-28-2008 8:21 AM In reply to

    Re: Automating Assembly Transactions

    Reply Contact

    Sorry, Joe, I'm not even sure what an Assembly Transaction is... I Googled it <smiles>, I came up with the IV Tables page in this fourm. What a hoot. Soulds like something that has to do with BOM?

    Steve Gray, MCDBA
    Technical Editor
    steve@VSToolsForum.com
    • 216.77.101.2

  • 02-28-2008 8:42 AM In reply to

    Re: Automating Assembly Transactions

    Reply Contact

    Yes, it is a part of Dynamics GP's Bill Of Materials module.  An assembly transaction reports the act of "building a subassembly" (from BOM) in production.  It's used in manufacturing to prepare subs before actually selling them.

     Well, I got a little farther last night on the SQL trace and I'm building the t-SQL script now.  Maybe I'll forward along the information to you for your blog (if it works of course)!

    Talk to you soon!
    • 76.189.252.140

  • 10-22-2008 11:31 AM In reply to

    Re: Automating Assembly Transactions

    Reply Contact

    Was there any progress on this? I also need to automate the process of "building" assemblies.

     I am using the Inventory -> Bill of Materials. I have a existing program that puts SOP Invoices into GP using eConnect, and would like it to be able to build assemblies as needed to support the invoices if QOH doesnt support them.

     Thanks,

    Doug
    • 71.16.189.84

  • 10-22-2008 12:38 PM In reply to

    Re: Automating Assembly Transactions

    Reply Contact

    Doug,

     I have good news and bad news...

    The good news is, yes, there was progress on this.  We successfully created assembly transactions outside of GP's UI.

     The bad news...  It's complicated.  We had to do it using SSIS and a custom SQL script.  (If I were to do it today, I'd probably do it in a .Net class instead of SSIS.  It's better than DTS but still quirky.)  Here's an overview of how we did it:

    1. Created a batch record in SY00500.
    2. Retrieved the Assembly Transaction ID and set the next number on the BM40100 table.
    3. Created an Assembly Header record (BM10200).  (One for each doc num.)
    4. Created the Assembly Component records (BM10300).  Our assembly was "known" by us.  (ie:  we didn't have to look up the components in the master BOM records.)  The first record we created was the "parent" record.  Then we created the two component lines.
    5. Since we had to track serial numbers, we created the one "parent" and two "component" records in the BM10400 table.
    6. Lastly, we cleaned up the BM40100 table by updating the next Assembly Transaction number.

    I would have loved to create a "generic" process for building the assembly transactions but we didn't have the time.  If I were to do it again, I would...

    1. Create the batch record (SY00500)
    2. Retrieve and set the next Assy Trx ID (BM40100)
    3. Read the BOM Master records to retrieve the component requirements.
    4. Create the parent assembly record (BM10300)
    5. Loop through and create the component records (BM10300)
    6. Create the parent serial num record (BM10400)
    7. Loop through and create the component serial num records (BM10400)
    8. Clean up the BM10400 record.

    Here's the assy transaction part of the script that we use.  Note that you'll have to use it a reference and build your own script.  Good luck!!!

     /* Assembly Transactions

    ---------------------

    1. Create a batch record (SY00500).

    2. Get the Assembly Transaction ID and set the next number on the BM40100 table.

    3. Create a Assembly Header record (BM10200). (One for each doc num)

    4. Create Assembly Component Records (BM10300):

    a. Insert Parent component record (SPOT-1).

    b. Insert two component lines (SPOT-UNIT, SPOT-PKG).

    5. Create "Parent" Serial Component Lines for SPOT-1 (BM10400).

    6. Create "Component" Serial Component Lines for SPOT-UNIT (BM10400).

    7. Clean up BM40100 table: Update the next Assembly Transaction number. */

    -- Get next note index for the assembly batch.

    Set @SQL = N'exec ' + @DynamicsDBName + '.' + @Schema + '.smGetNextNoteIndex 11, 32, @AssemblyBatchNoteIndex OUT'

    Set @Params = N'@AssemblyBatchNoteIndex numeric(19,5) OUTPUT'

    Exec sp_executesql @SQL, @Params, @AssemblyBatchNoteIndex OUTPUT

    -- Insert the Batch Header record for the assembly trx.

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[SY00500]([GLPOSTDT],[BCHSOURC],[BACHNUMB],[SERIES],[MKDTOPST],[NUMOFTRX],[RECPSTGS],[DELBACH]

    ,[MSCBDINC],[BACHFREQ],[RCLPSTDT],[NOFPSTGS],[BCHCOMNT],[BRKDNALL],[CHKSPRTD],[RVRSBACH],[USERID],[CHEKBKID]

    ,[BCHTOTAL],[BCHEMSG1],[BCHEMSG2],[BACHDATE],[BCHSTRG1],[BCHSTRG2],[POSTTOGL],[MODIFDT],[CREATDDT],[NOTEINDX]

    ,[CURNCYID],[BCHSTTUS],[CNTRLTRX],[CNTRLTOT],[PETRXCNT],[APPROVL],[APPRVLDT],[APRVLUSERID],[ORIGIN],[ERRSTATE]

    ,[GLBCHVAL],[Computer_Check_Doc_Date],[Sort_Checks_By],[SEPRMTNC],[REPRNTED],[CHKFRMTS],[TRXSORCE],[PmtMethod]

    ,[EFTFileFormat])

    VALUES (CONVERT(char(10),GetDate(),101),@AssemblyBatchSource,@BACHNUMB,5,0,@NumberOfTransactions,0,0,0,1,''1/1/1900'',0,@AssemblyBatchComment,0,0

    ,0,@MDFUSRID,@AssemblyCheckbookID,0,0x00000000,0x00000000,''1/1/1900'','''','''',1,CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101),@AssemblyBatchNoteIndex,''''

    ,0,0,0,0,0,''1/1/1900'','''',3,0,0x00000000,''1/1/1900'',0,0,0,0,'''',0,0)'

    Set @Params = N'@AssemblyBatchSource varchar(100), @BACHNUMB varchar(15), @NumberOfTransactions int, @AssemblyBatchComment varchar(100),

    @MDFUSRID varchar(15), @AssemblyCheckbookID varchar(100), @AssemblyBatchNoteIndex numeric(19,5)'

    Exec sp_executesql @SQL, @Params, @AssemblyBatchSource, @AssemblyBatchNumber, @NumberOfTransactions, @AssemblyBatchComment,

    @MDFUSRID, @AssemblyCheckbookID, @AssemblyBatchNoteIndex

    /*CHANGED*/

    -- Cursor for document numbers in the import file.

    Set @SQL = N'DECLARE ByDocumentNumber CURSOR READ_ONLY FOR

    Select DocumentNumber, count(SerialNumber)

    from '     + @IntegrationDBName + '.' + @Schema + '.' + @ImportTableName +

    ' group by DocumentNumber order by DocumentNumber'

    Exec sp_executesql @SQL

    /*END CHANGES*/

    OPEN ByDocumentNumber

    /*CHANGED*/

    FETCH NEXT FROM ByDocumentNumber INTO @ImportDocumentNumber, @TransactionQuantity

    /*END CHANGES*/

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    -- Get next note index for the assembly header.

    Set @SQL = N'exec ' + @DynamicsDBName + '.' + @Schema + '.smGetNextNoteIndex 11, 32, @AssemblyHeaderNoteIndex OUT'

    Set @Params = N'@AssemblyHeaderNoteIndex numeric(19,5) OUTPUT'

    Exec sp_executesql @SQL, @Params, @AssemblyHeaderNoteIndex OUTPUT

    -- Get the next Assembly Transaction ID.

    Set @SQL = N'Select @AssemblyTransactionID = TRX_ID from ' + @CompanyDBName + '.' +

    @Schema + '.BM40100 where SETUPKEY = 1'

    Set @Params = N'@AssemblyTransactionID varchar(100) OUTPUT'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID OUTPUT

    -- Set the next assembly number in the BM40100 table.

    Set @SQL = N'Update ' + @CompanyDBName + '.' + @Schema +

    '.BM40100 Set TRX_ID = left(TRX_ID,9) + RIGHT(''00000000'' + RTRIM(cast(right(TRX_ID,8) as int) + 1), 8) where SETUPKEY = 1'

    Exec sp_executesql @SQL

    -- Insert the Assembly Header records into the BM10200 table (one for each Document Number in the import file).

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[BM10200] ([TRX_ID],[BM_Trx_Status],[BCHSOURC],[BACHNUMB],[Batch_ID_Note_Index],[Completion_Date]

    ,[BM_Start_Date],[TRXDATE],[PSTGDATE],[REFRENCE],[Quantity_Shortage_Status],[BM_Trx_Errors],[BM_Component_Errors]

    ,[NOTEINDX],[MODIFDT],[USER2ENT],[CREATDDT],[PTDUSRID],[POSTEDDT],[TRXSORCE],[USERDEF1],[USERDEF2],[USRDEF03],[USRDEF04])

    VALUES (@AssemblyTransactionID,3,@AssemblyBatchSource,@AssemblyBatchNumber,@AssemblyBatchNoteIndex

    ,''1/1/1900'',CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101),CONVERT(char(10),GetDate(),101),'''',1,0x00000000,0x00000000,@AssemblyHeaderNoteIndex

    ,CONVERT(char(10),GetDate(),101),@MDFUSRID,CONVERT(char(10),GetDate(),101),'''',''1/1/1900'','''','''','''','''','''')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @AssemblyBatchSource varchar(100), @AssemblyBatchNumber varchar(100),

    @AssemblyBatchNoteIndex numeric(19,5), @AssemblyHeaderNoteIndex numeric(19,5), @MDFUSRID varchar(15)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID , @AssemblyBatchSource , @AssemblyBatchNumber ,

    @AssemblyBatchNoteIndex , @AssemblyHeaderNoteIndex , @MDFUSRID

    -- Create Assembly Component Record in the BM10300 table for the parent item number (SPOT-1):

    Set @SQL = N'exec ' + @DynamicsDBName + '.' + @Schema + '.smGetNextNoteIndex 11, 32, @NextNoteIndex OUT'

    Set @Params = N'@NextNoteIndex numeric(19,5) OUTPUT'

    Exec sp_executesql @SQL, @Params, @NextNoteIndex OUTPUT

    /* REPLACE ALL @NUMBEROFTRANSACTIONS WITH @TRANSACTIONQUANTITY STARTING HERE */

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[BM10300]([TRX_ID],[Component_ID],[Parent_Component_ID],[ITEMNMBR],[ITEMDESC]

    ,[UOFM],[LOCNCODE],[Location_Code_Note_Index],[Stock_Quantity],[Assemble_Quantity],[ATYALLOC]

    ,[BM_Stock_Method],[Cost_Type],[INVINDX],[Inventory_Account_Reference],[Inventory_Account_Note_I]

    ,[Variance_Index],[Variance_Account_Reference],[Variance_Account_Note_In],[Design_Quantity]

    ,[Scrap_Percentage],[STNDCOST],[Standard_Quantity],[Extended_Standard_Cost],[Extended_Standard_Quantity]

    ,[Ext_Assemble_Qty_Posting_Cost],[Ext_Stock_Receipt_Cost],[Ext_Stock_Qty_Posting_Cost],[Ext_Subasm_Posting_Cost]

    ,[Ext_Subasm_Receipt_Cost],[BM_Component_Type],[Lvl],[QTYBSUOM],[Assemble_Serial_Lot_Count],[Stock_Serial_Lot_Count]

    ,[Temp_Allocated_Quantity],[Temp_Assemble_Quantity],[Temp_Extended_Assemble_Q],[Temp_Location_Code]

    ,[Temp_Stock_Quantity],[Temp_Extended_Stock_Quan],[DECPLQTY],[DECPLCUR],[ITMTRKOP],[NOTEINDX],[BM_Component_Errors]

    ,[TRXSORCE],[EXTQTYAL],[EXTQTYSEL],[Temp_Extended_Standard_Q],[Temp_Existing_Quantity_A],[Incl],[BM_Component_Options],[BIN])

    VALUES(@AssemblyTransactionID,0,-1,@ParentItemNumber,@ParentDescription,@UOFM

    ,@TRXLOCTN,@LocationNoteIndex,0,@TransactionQuantity,0,3,1,@IVIVINDX,'''',@IVIVINDXNoteIndex,@IVIVOFIX,''''

    ,0,0,0,@ParentStandardCost,@TransactionQuantity,0,@TransactionQuantity,0,0,0,0,0,2,0,1,0,0,0,0,0,'''',0,0,1,3,2,@NextNoteIndex

    ,0x00000000,'''',0,0,0,0,0,0x91000000,'''')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @ParentItemNumber varchar(30), @ParentDescription varchar(101), @UOFM varchar(8),

    @TRXLOCTN varchar(10), @LocationNoteIndex int, @TransactionQuantity int, @IVIVINDX int, @IVIVINDXNoteIndex int,

    @IVIVOFIX int, @ParentStandardCost numeric(19,5), @NextNoteIndex numeric(19,5)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID, @ParentItemNumber, @ParentDescription, @UOFM,

    @TRXLOCTN, @LocationNoteIndex, @TransactionQuantity, @IVIVINDX, @IVIVINDXNoteIndex,

    @IVIVOFIX, @ParentStandardCost, @NextNoteIndex

    -- Create Assembly Component Record in the BM10300 table for the component item numbers (SPOT-UNIT, SPOT-PKG):

    Set @SQL = N'exec ' + @DynamicsDBName + '.' + @Schema + '.smGetNextNoteIndex 11, 32, @NextNoteIndex OUT'

    Set @Params = N'@NextNoteIndex numeric(19,5) OUTPUT'

    Exec sp_executesql @SQL, @Params, @NextNoteIndex OUTPUT

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[BM10300]([TRX_ID],[Component_ID],[Parent_Component_ID],[ITEMNMBR],[ITEMDESC]

    ,[UOFM],[LOCNCODE],[Location_Code_Note_Index],[Stock_Quantity],[Assemble_Quantity],[ATYALLOC]

    ,[BM_Stock_Method],[Cost_Type],[INVINDX],[Inventory_Account_Reference],[Inventory_Account_Note_I]

    ,[Variance_Index],[Variance_Account_Reference],[Variance_Account_Note_In],[Design_Quantity]

    ,[Scrap_Percentage],[STNDCOST],[Standard_Quantity],[Extended_Standard_Cost],[Extended_Standard_Quantity]

    ,[Ext_Assemble_Qty_Posting_Cost],[Ext_Stock_Receipt_Cost],[Ext_Stock_Qty_Posting_Cost],[Ext_Subasm_Posting_Cost]

    ,[Ext_Subasm_Receipt_Cost],[BM_Component_Type],[Lvl],[QTYBSUOM],[Assemble_Serial_Lot_Count],[Stock_Serial_Lot_Count]

    ,[Temp_Allocated_Quantity],[Temp_Assemble_Quantity],[Temp_Extended_Assemble_Q],[Temp_Location_Code]

    ,[Temp_Stock_Quantity],[Temp_Extended_Stock_Quan],[DECPLQTY],[DECPLCUR],[ITMTRKOP],[NOTEINDX],[BM_Component_Errors]

    ,[TRXSORCE],[EXTQTYAL],[EXTQTYSEL],[Temp_Extended_Standard_Q],[Temp_Existing_Quantity_A],[Incl],[BM_Component_Options],[BIN])

    VALUES(@AssemblyTransactionID,16384,0,@PackagingItemNumber,@PackagingDescription,@UOFM

    ,@TRXLOCTN,@LocationNoteIndex,@TransactionQuantity,0,@TransactionQuantity,2,1,@IVIVINDX,'''',@IVIVINDXNoteIndex,0,''''

    ,0,1,0,@PackagingStandardCost,1,(@PackagingStandardCost * @TransactionQuantity),@TransactionQuantity,0,0,0,0,0,1,1,1,0,0,0,0,0,'''',0,0,1,3,1,@NextNoteIndex

    ,0x00000000,'''',@TransactionQuantity,0,0,0,0,0x81000000,'''')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @PackagingItemNumber varchar(30), @PackagingDescription varchar(101), @UOFM varchar(8),

    @TRXLOCTN varchar(10), @LocationNoteIndex int, @TransactionQuantity int, @IVIVINDX int, @IVIVINDXNoteIndex int,

    @IVIVOFIX int, @PackagingStandardCost numeric(19,5), @NextNoteIndex numeric(19,5)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID, @PackagingItemNumber, @PackagingDescription, @UOFM,

    @TRXLOCTN, @LocationNoteIndex, @TransactionQuantity, @IVIVINDX, @IVIVINDXNoteIndex,

    @IVIVOFIX, @PackagingStandardCost, @NextNoteIndex

    Set @SQL = N'exec ' + @DynamicsDBName + '.' + @Schema + '.smGetNextNoteIndex 11, 32, @NextNoteIndex OUT'

    Set @Params = N'@NextNoteIndex numeric(19,5) OUTPUT'

    Exec sp_executesql @SQL, @Params, @NextNoteIndex OUTPUT

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[BM10300]([TRX_ID],[Component_ID],[Parent_Component_ID],[ITEMNMBR],[ITEMDESC]

    ,[UOFM],[LOCNCODE],[Location_Code_Note_Index],[Stock_Quantity],[Assemble_Quantity],[ATYALLOC]

    ,[BM_Stock_Method],[Cost_Type],[INVINDX],[Inventory_Account_Reference],[Inventory_Account_Note_I]

    ,[Variance_Index],[Variance_Account_Reference],[Variance_Account_Note_In],[Design_Quantity]

    ,[Scrap_Percentage],[STNDCOST],[Standard_Quantity],[Extended_Standard_Cost],[Extended_Standard_Quantity]

    ,[Ext_Assemble_Qty_Posting_Cost],[Ext_Stock_Receipt_Cost],[Ext_Stock_Qty_Posting_Cost],[Ext_Subasm_Posting_Cost]

    ,[Ext_Subasm_Receipt_Cost],[BM_Component_Type],[Lvl],[QTYBSUOM],[Assemble_Serial_Lot_Count],[Stock_Serial_Lot_Count]

    ,[Temp_Allocated_Quantity],[Temp_Assemble_Quantity],[Temp_Extended_Assemble_Q],[Temp_Location_Code]

    ,[Temp_Stock_Quantity],[Temp_Extended_Stock_Quan],[DECPLQTY],[DECPLCUR],[ITMTRKOP],[NOTEINDX],[BM_Component_Errors]

    ,[TRXSORCE],[EXTQTYAL],[EXTQTYSEL],[Temp_Extended_Standard_Q],[Temp_Existing_Quantity_A],[Incl],[BM_Component_Options],[BIN])

    VALUES(@AssemblyTransactionID,32768,0,@ITEMNMBR,@ItemDescription,@UOFM

    ,@TRXLOCTN,@LocationNoteIndex,@TransactionQuantity,0,0,2,1,@IVIVINDX,'''',@IVIVINDXNoteIndex,0,''''

    ,0,1,0,@ItemStandardCost,1,(@ItemStandardCost * @TransactionQuantity),@TransactionQuantity,0,0,0,0,0,1,1,1,0,0,0,0,0,'''',0,0,1,3,2,@NextNoteIndex

    ,0x00000000,'''',0,0,0,0,0,0x81000000,'''')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @ITEMNMBR varchar(30), @ItemDescription varchar(101), @UOFM varchar(8),

    @TRXLOCTN varchar(10), @LocationNoteIndex int, @TransactionQuantity int, @IVIVINDX int, @IVIVINDXNoteIndex int,

    @ItemStandardCost numeric(19,5), @NextNoteIndex numeric(19,5)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID, @ITEMNMBR, @ItemDescription, @UOFM,

    @TRXLOCTN, @LocationNoteIndex, @TransactionQuantity, @IVIVINDX, @IVIVINDXNoteIndex,

    @ItemStandardCost, @NextNoteIndex

    -- Update the Qty Allocated on the Location table for the Packaging Component (SPOT-PKG):

    Set @SQL = N'Update ' + @CompanyDBName + '.' + @Schema + '.IV00102 Set ATYALLOC = ATYALLOC + @TransactionQuantity

    where ITEMNMBR = @PackagingItemNumber and LOCNCODE = @TRXLOCTN and RCRDTYPE = 2'

    Set @Params = N'@TransactionQuantity int, @PackagingItemNumber varchar(30), @TRXLOCTN varchar(10)'

    Exec sp_executesql @SQL, @Params, @TransactionQuantity, @PackagingItemNumber, @TRXLOCTN

    Set @SQL = N'Update ' + @CompanyDBName + '.' + @Schema + '.IV00102 Set ATYALLOC = ATYALLOC + @TransactionQuantity

    where ITEMNMBR = @PackagingItemNumber and LOCNCODE = '''' and RCRDTYPE = 1'

    Set @Params = N'@TransactionQuantity int, @PackagingItemNumber varchar(30)'

    Exec sp_executesql @SQL, @Params, @TransactionQuantity, @PackagingItemNumber

    -- Update the Qty Allocated on the Location table for the Serialized Component (SPOT-UNIT):

    Set @SQL = N'Update ' + @CompanyDBName + '.' + @Schema + '.IV00102 Set ATYALLOC = ATYALLOC + @TransactionQuantity

    where ITEMNMBR = @ITEMNMBR and LOCNCODE = @TRXLOCTN and RCRDTYPE = 2'

    Set @Params = N'@TransactionQuantity int, @ITEMNMBR varchar(30), @TRXLOCTN varchar(10)'

    Exec sp_executesql @SQL, @Params, @TransactionQuantity, @ITEMNMBR, @TRXLOCTN

    Set @SQL = N'Update ' + @CompanyDBName + '.' + @Schema + '.IV00102 Set ATYALLOC = ATYALLOC + @TransactionQuantity

    where ITEMNMBR = @ITEMNMBR and LOCNCODE = '''' and RCRDTYPE = 1'

    Set @Params = N'@TransactionQuantity int, @ITEMNMBR varchar(30)'

    Exec sp_executesql @SQL, @Params, @TransactionQuantity, @ITEMNMBR

    /*END OF THE REPLACEMENT */

    -- Cursor for serial numbers within the current document number.

    Select @SequenceNumber = 0Set @SQL = N'DECLARE SerialsByDoc CURSOR READ_ONLY FOR

    Select SerialNumber, UnitCost, Date

    from ' + @IntegrationDBName + '.' + @Schema + '.' + @ImportTableName +

    ' where DocumentNumber = ''' + @ImportDocumentNumber + ''''

    Exec sp_executesql @SQL

    OPEN SerialsByDoc

    FETCH NEXT FROM SerialsByDoc INTO @ImportSerialNumber, @ImportUnitCost, @ImportDateReceived

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    Select @SequenceNumber = @SequenceNumber + 1

    -- Insert the parent items into the serial table (SPOT-1).

    Set @SQL = N'INSERT INTO ' + @CompanyDBName + '.' + @Schema + '.[BM10400]

    ([TRX_ID] ,[Component_ID],[SLTYPE],[SLTSQNUM],[SERLTNUM],[SERLTQTY],[ITEMNMBR],[QTYTYPE],[DATERECD]

    ,[DTSEQNUM],[OVRSERLT],[PARSLSEQN],[Parent_Component_ID],[ITMTRKOP],[TRXSORCE],[UNITCOST],[Status]

    ,[LOCNCODE],[BIN],[MFGDATE],[EXPNDATE])

    VALUES(@AssemblyTransactionID,0,2001,@SequenceNumber,@ImportSerialNumber,1,@ParentItemNumber,1,''1/1/1900'',0

    ,0,0,-1,2,'''',@ParentStandardCost,0,@TRXLOCTN,'''',''1/1/1900'',''1/1/1900'')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @SequenceNumber int, @ImportSerialNumber varchar(100), @ParentItemNumber varchar(30),

    @ParentStandardCost numeric(19,5), @TRXLOCTN varchar(10)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID, @SequenceNumber, @ImportSerialNumber, @ParentItemNumber,

    @ParentStandardCost, @TRXLOCTN

    -- Insert the component items into the serial table (SPOT-UNIT).

    Set @SQL = N'INSERT INTO [SPOT].[dbo].[BM10400]

    ([TRX_ID] ,[Component_ID],[SLTYPE],[SLTSQNUM],[SERLTNUM],[SERLTQTY],[ITEMNMBR],[QTYTYPE],[DATERECD]

    ,[DTSEQNUM],[OVRSERLT],[PARSLSEQN],[Parent_Component_ID],[ITMTRKOP],[TRXSORCE],[UNITCOST],[Status]

    ,[LOCNCODE],[BIN],[MFGDATE],[EXPNDATE])

    VALUES(@AssemblyTransactionID,32768,2002,@SequenceNumber,@ImportSerialNumber,1,@ITEMNMBR,1,@ImportDateReceived,@SequenceNumber

    ,0,0,0,2,'''',@ImportUnitCost,0,@TRXLOCTN,'''',''1/1/1900'',''1/1/1900'')'

    Set @Params = N'@AssemblyTransactionID varchar(100), @SequenceNumber int, @ImportSerialNumber varchar(100), @ITEMNMBR varchar(30),

    @ImportDateReceived datetime, @ImportUnitCost numeric(19,5), @TRXLOCTN varchar(10)'

    Exec sp_executesql @SQL, @Params, @AssemblyTransactionID, @SequenceNumber, @ImportSerialNumber, @ITEMNMBR,

    @ImportDateReceived, @ImportUnitCost, @TRXLOCTN

    END

    FETCH NEXT FROM SerialsByDoc INTO @ImportSerialNumber, @ImportUnitCost, @ImportDateReceived

    END

    CLOSE SerialsByDoc

    DEALLOCATE SerialsByDoc

    END

    FETCH NEXT FROM ByDocumentNumber INTO @ImportDocumentNumber, @TransactionQuantity

    END

    CLOSE ByDocumentNumber

    DEALLOCATE ByDocumentNumber

    -- Delete records in the ImportInventory table.

    Set @SQL = N'Delete from ' + @IntegrationDBName + '.' + @Schema + '.' + @ImportTableName

    Exec sp_executesql @SQL
    • 173.88.167.119
Page 1 of 1 (5 items)