SQL - Add SOP DropShip lines to the standard InventoryTransaction view

One of the standard views supplied with Dynamics is called 'InventoryTransactions'. This view does not natively show SOP DropShip items. A customer recently asked me to add them in, the resulting code is below.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

In order to achieve this, I needed to view the source of the view, but the source is compacted - all the spaces are removed. So, we need to 'prettify' it before working.

To get the source:

SP_HELPTEXT InventoryTransactions

Then run it through your favorite SQL prettifyier. We have several listed here:

http://dyndeveloper.com/menus/SQLGeneral.aspx

 

alter VIEW InventoryTransactions
AS
 
--select * from inventorytransactions
 
 
SELECT rtrim(['Inventory Transaction Amounts Work'].[IVDOCNBR]) AS 'Document Number'
    ,'Document Type' = dbo.DYN_FUNC_Document_Type_IV_Trx(['Inventory Transaction Amounts Work'].[IVDOCTYP])
    ,rtrim(['Inventory Transaction Amounts Work'].[ITEMNMBR]) AS 'Item Number'
    ,rtrim(['Inventory Transaction Amounts Work'].[UOFM]) AS 'U Of M'
    ,['Inventory Transaction Amounts Work'].[TRXQTY] AS 'TRX QTY'
    ,['Inventory Transaction Amounts Work'].[UNITCOST] AS 'Unit Cost'
    ,['Inventory Transaction Amounts Work'].[EXTDCOST] AS 'Extended Cost'
    ,rtrim(['Inventory Transaction Amounts Work'].[TRXLOCTN]) AS 'TRX Location'
    ,['Inventory Transaction Work'].[DOCDATE] AS 'Document Date'
    ,'Document Status' = dbo.DYN_FUNC_Document_Status_IV_Trx(1)
    ,'ABC Code' = dbo.DYN_FUNC_ABC_Code(['Item Master'].[ABCCODE])
    ,'Allow Back Orders' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[ALWBKORD])
    ,rtrim(['Item Master'].[ALTITEM1]) AS 'Alternate Item 1'
    ,rtrim(['Item Master'].[ALTITEM2]) AS 'Alternate Item 2'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[ASMVRIDX]
        ) AS 'Assembly Variance Account Number'
    ,rtrim(['Inventory Transaction Work'].[BACHNUMB]) AS 'Batch Number'
    ,rtrim(['Inventory Transaction Work'].[BCHSOURC]) AS 'Batch Source'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVCOGSIX]
        ) AS 'COGS Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSINFLX]
        ) AS 'COGS Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSMCIDX]
        ) AS 'COGS Monetary Correction Account Number'
    ,['Item Master'].[CREATDDT] AS 'Created Date'
    ,['Item Master'].[CURRCOST] AS 'Current Cost'
    ,NULL AS 'Customer Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVDMGIDX]
        ) AS 'Damaged Account Number'
    ,'Decimal Places Currency' = dbo.DYN_FUNC_Decimal_Places_Currency(['Item Master'].[DECPLCUR])
    ,'Decimal Places QTYS' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Item Master'].[DECPLQTY])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[DPSHPIDX]
        ) AS 'Drop Ship Account Number'
    ,['Inventory Transaction Work'].[GLPOSTDT] AS 'GL Posting Date'
    ,NULL AS 'History Module'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINSVIX]
        ) AS 'In Service Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] as ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINUSIX]
        ) AS 'In Use Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Inventory Transaction Amounts Work'].[IVIVINDX]
        ) AS 'Inventory Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVIVINDX]
        ) AS 'Inventory Account Number from Item Master'
    ,(
        select rtrim([ACTNUMST]) FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINFIDX]
        ) AS 'Inventory Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[INVMCIDX]
        ) AS 'Inventory Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Inventory Transaction Amounts Work'].[IVIVOFIX]
        ) AS 'Inventory Offset Account Number'
    ,NULL AS 'Inventory Offset Account Number from Item Master'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVRETIDX]
        ) AS 'Inventory Returns Account Number'
    ,rtrim(['Item Master'].[ITMCLSCD]) AS 'Item Class Code'
    ,rtrim(['Item Master'].[ITEMCODE]) AS 'Item Code'
    ,rtrim(['Item Master'].[ITEMDESC]) AS 'Item Description'
    ,rtrim(['Item Master'].[ITMGEDSC]) AS 'Item Generic Description'
    ,['Item Master'].[ITEMSHWT] / 100.00 AS 'Item Shipping Weight'
    ,rtrim(['Item Master'].[ITMSHNAM]) AS 'Item Short Name'
    ,rtrim(['Item Master'].[ITMTSHID]) AS 'Item Tax Schedule ID'
    ,'Item Tracking Option' = dbo.DYN_FUNC_Item_Tracking_Option(['Item Master'].[ITMTRKOP])
    ,'Item Type' = dbo.DYN_FUNC_Item_Type(['Item Master'].[ITEMTYPE])
    ,'Keep Calendar History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPCALHST])
    ,'Keep Distribution History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPDSTHST])
    ,'Keep Period History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPERHIST])
    ,'Keep Trx History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPTRXHST])
    ,'Kit COGS Account Source' = dbo.DYN_FUNC_Kit_COGS_Account_Source(['Item Master'].[KTACCTSR])
    ,rtrim(['Item Master'].[LASTGENSN]) AS 'Last Generated Serial Number'
    ,['Inventory Transaction Amounts Work'].[LNSEQNBR] AS 'Line SEQ Number'
    ,rtrim(['Item Master'].[LOCNCODE]) AS 'Location Code'
    ,rtrim(['Item Master'].[LOTTYPE]) AS 'Lot Type'
    ,['Item Master'].[MSTRCDTY] AS 'Master Record Type'
    ,['Item Master'].[MODIFDT] AS 'Modified Date'
    ,rtrim(['Inventory Transaction Work'].[MDFUSRID]) AS 'Modified User ID'
    ,['Inventory Transaction Work'].[NOTEINDX] AS 'Note Index'
    ,['Inventory Transaction Work'].[POSTEDDT] AS 'Posted Date'
    ,rtrim(['Inventory Transaction Work'].[PTDUSRID]) AS 'Posted User ID'
    ,['Inventory Transaction Work'].[PSTGSTUS] AS 'Posting Status'
    ,rtrim(['Item Master'].[PRCLEVEL]) AS 'PriceLevel'
    ,rtrim(['Item Master'].[PriceGroup]) AS 'Price Group'
    ,'Price Method' = dbo.DYN_FUNC_Price_Method(['Item Master'].[PRICMTHD])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PINFLIDX]
        ) AS 'Purch Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURMCIDX]
        ) AS 'Purch Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master'] where ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURPVIDX]
        ) AS 'Purchase Price Variance Account Number'
    ,rtrim(['Item Master'].[PRCHSUOM]) AS 'Purchasing U Of M'
    ,['Inventory Transaction Amounts Work'].[QTYBSUOM] AS 'QTY In Base U Of M'
    ,rtrim(['Inventory Transaction Work'].[RCDOCNUM]) AS 'Recurring Document Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLSIDX]
        ) AS 'Sales Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLDSIX]
        ) AS 'Sales Discounts Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLRNIX]
        ) AS 'Sales Returns Account Number'
    ,rtrim(['Item Master'].[SELNGUOM]) AS 'Selling U Of M'
    ,'Source Indicator' = dbo.DYN_FUNC_Source_Indicator(['Inventory Transaction Work'].[SOURCEINDICATOR])
    ,rtrim(['Inventory Transaction Work'].[SRCRFRNCNMBR]) AS 'Source Reference Number'
    ,['Item Master'].[STNDCOST] AS 'Standard Cost'
    ,['Inventory Transaction Work'].[TRXQTYTL] AS 'TRX QTY Total'
    ,NULL AS 'TRX Source'
    ,rtrim(['Item Master'].[TCC]) AS 'Tax Commodity Code'
    ,'Tax Options' = dbo.DYN_FUNC_Tax_Options(['Item Master'].[TAXOPTNS])
    ,'Transfer From QTY Type' = dbo.DYN_FUNC_Transfer_From_QTY_Type(['Inventory Transaction Amounts Work'].[TRFQTYTY])
    ,rtrim(['Inventory Transaction Amounts Work'].[TRNSTLOC]) AS 'Transfer To Location'
    ,'Transfer To QTY Type' = dbo.DYN_FUNC_Transfer_To_QTY_Type(['Inventory Transaction Amounts Work'].[TRTQTYTY])
    ,rtrim(['Item Master'].[UOMSCHDL]) AS 'U Of M Schedule'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[UPPVIDX]
        ) AS 'Unrealized Purchase Price Variance Account Number'
    ,['Inventory Transaction Amounts Work'].[USAGETYPE] AS 'Usage Type'
    ,rtrim(['Item Master'].[USCATVLS_1]) AS 'User Category Value 1'
    ,rtrim(['Item Master'].[USCATVLS_2]) AS 'User Category Value 2'
    ,rtrim(['Item Master'].[USCATVLS_3]) AS 'User Category Value 3'
    ,rtrim(['Item Master'].[USCATVLS_4]) AS 'User Category Value 4'
    ,rtrim(['Item Master'].[USCATVLS_5]) AS 'User Category Value 5'
    ,rtrim(['Item Master'].[USCATVLS_6]) AS 'User Category Value 6'
    ,'Valuation Method' = dbo.DYN_FUNC_Valuation_Method(['Item Master'].[VCTNMTHD])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVVARIDX]
        ) AS 'Variances Account Number'
    ,['Item Master'].[WRNTYDYS] AS 'Warranty Days'
    ,'Assembly Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[ASMVRIDX])
    ,'COGS Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVCOGSIX])
    ,'COGS Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[CGSINFLX])
    ,'COGS Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[CGSMCIDX])
    ,'Damaged Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVDMGIDX])
    ,'Document Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppItemTransactionNumber(1, ['Inventory Transaction Amounts Work'].[ITEMNMBR], ['Inventory Transaction Amounts Work'].[IVDOCTYP], ['Inventory Transaction Amounts Work'].[IVDOCNBR], 1)
    ,'Drop Ship Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[DPSHPIDX])
    ,'In Service Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINSVIX])
    ,'In Use Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINUSIX])
    ,'Inventory Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Inventory Transaction Amounts Work'].[IVIVINDX])
    ,'Inventory Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINFIDX])
    ,'Inventory Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[INVMCIDX])
    ,'Inventory Offset Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Inventory Transaction Amounts Work'].[IVIVOFIX])
    ,'Inventory Returns Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVRETIDX])
    ,'Item Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppItemID(1, ['Inventory Transaction Amounts Work'].[ITEMNMBR], ['Item Master'].[LOCNCODE])
    ,'Purch Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PINFLIDX])
    ,'Purch Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PURMCIDX])
    ,'Purchase Price Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PURPVIDX])
    ,'Sales Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLSIDX])
    ,'Sales Discounts Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLDSIX])
    ,'Sales Returns Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLRNIX])
    ,'Unrealized Purchase Price Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[UPPVIDX])
    ,'Variances Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVVARIDX])
FROM [IV10001] AS ['Inventory Transaction Amounts Work'] WITH (NOLOCK)
LEFT JOIN [IV10000] AS ['Inventory Transaction Work'] WITH (NOLOCK) ON ['Inventory Transaction Amounts Work'].[IVDOCTYP] = ['Inventory Transaction Work'].[IVDOCTYP]
    AND ['Inventory Transaction Amounts Work'].[IVDOCNBR] = ['Inventory Transaction Work'].[IVDOCNBR]
LEFT JOIN [IV00101] AS ['Item Master'] WITH (NOLOCK) ON ['Inventory Transaction Amounts Work'].[ITEMNMBR] = ['Item Master'].[ITEMNMBR]
 
UNION ALL
 
SELECT rtrim(['Inventory Transaction Amounts History'].[DOCNUMBR]) AS 'Document Number'
    ,'Document Type' = dbo.DYN_FUNC_Document_Type_IV_Trx(['Inventory Transaction Amounts History'].[DOCTYPE])
    ,rtrim(['Inventory Transaction Amounts History'].[ITEMNMBR]) AS 'Item Number'
    ,rtrim(['Inventory Transaction Amounts History'].[UOFM]) AS 'U Of M'
    ,['Inventory Transaction Amounts History'].[TRXQTY] AS 'TRX QTY'
    ,['Inventory Transaction Amounts History'].[UNITCOST] AS 'Unit Cost'
    ,['Inventory Transaction Amounts History'].[EXTDCOST] AS 'Extended Cost'
    ,rtrim(['Inventory Transaction Amounts History'].[TRXLOCTN]) AS 'TRX Location'
    ,['Inventory Transaction Amounts History'].[DOCDATE] AS 'Document Date'
    ,'Document Status' = dbo.DYN_FUNC_Document_Status_IV_Trx(2)
    ,'ABC Code' = dbo.DYN_FUNC_ABC_Code(['Item Master'].[ABCCODE])
    ,'Allow Back Orders' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[ALWBKORD])
    ,rtrim(['Item Master'].[ALTITEM1]) AS 'Alternate Item 1'
    ,rtrim(['Item Master'].[ALTITEM2]) AS 'Alternate Item 2'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[ASMVRIDX]
        ) AS 'Assembly Variance Account Number'
    ,rtrim(['Inventory Transaction History'].[BACHNUMB]) AS 'Batch Number'
    ,rtrim(['Inventory Transaction History'].[BCHSOURC]) AS 'Batch Source'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVCOGSIX]
        ) AS 'COGS Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSINFLX]
        ) AS 'COGS Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSMCIDX]
        ) AS 'COGS Monetary Correction Account Number'
    ,['Item Master'].[CREATDDT] AS 'Created Date'
    ,['Item Master'].[CURRCOST] AS 'Current Cost'
    ,NULL AS 'Customer Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVDMGIDX]
        ) AS 'Damaged Account Number'
    ,'Decimal Places Currency' = dbo.DYN_FUNC_Decimal_Places_Currency(['Item Master'].[DECPLCUR])
    ,'Decimal Places QTYS' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Item Master'].[DECPLQTY])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[DPSHPIDX]
        ) AS 'Drop Ship Account Number'
    ,['Inventory Transaction History'].[GLPOSTDT] AS 'GL Posting Date'
    ,rtrim(['Inventory Transaction Amounts History'].[HSTMODUL]) AS 'History Module'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINSVIX]
        ) AS 'In Service Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINUSIX]
        ) AS 'In Use Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Inventory Transaction Amounts History'].[IVIVINDX]
        ) AS 'Inventory Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVIVINDX]
        ) AS 'Inventory Account Number from Item Master'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINFIDX]) AS 'Inventory Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[INVMCIDX]) AS 'Inventory Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Inventory Transaction Amounts History'].[IVIVOFIX]) AS 'Inventory Offset Account Number'
    ,NULL AS 'Inventory Offset Account Number from Item Master'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVRETIDX]
        ) AS 'Inventory Returns Account Number'
    ,rtrim(['Item Master'].[ITMCLSCD]) AS 'Item Class Code'
    ,rtrim(['Item Master'].[ITEMCODE]) AS 'Item Code'
    ,rtrim(['Item Master'].[ITEMDESC]) AS 'Item Description'
    ,rtrim(['Item Master'].[ITMGEDSC]) AS 'Item Generic Description'
    ,['Item Master'].[ITEMSHWT] / 100.00 AS 'Item Shipping Weight'
    ,rtrim(['Item Master'].[ITMSHNAM]) AS 'Item Short Name'
    ,rtrim(['Item Master'].[ITMTSHID]) AS 'Item Tax Schedule ID'
    ,'Item Tracking Option' = dbo.DYN_FUNC_Item_Tracking_Option(['Item Master'].[ITMTRKOP])
    ,'Item Type' = dbo.DYN_FUNC_Item_Type(['Item Master'].[ITEMTYPE])
    ,'Keep Calendar History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPCALHST])
    ,'Keep Distribution History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPDSTHST])
    ,'Keep Period History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPERHIST])
    ,'Keep Trx History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPTRXHST])
    ,'Kit COGS Account Source' = dbo.DYN_FUNC_Kit_COGS_Account_Source(['Item Master'].[KTACCTSR])
    ,rtrim(['Item Master'].[LASTGENSN]) AS 'Last Generated Serial Number'
    ,['Inventory Transaction Amounts History'].[LNSEQNBR] AS 'Line SEQ Number'
    ,rtrim(['Item Master'].[LOCNCODE]) AS 'Location Code'
    ,rtrim(['Item Master'].[LOTTYPE]) AS 'Lot Type'
    ,['Item Master'].[MSTRCDTY] AS 'Master Record Type'
    ,['Item Master'].[MODIFDT] AS 'Modified Date'
    ,NULL AS 'Modified User ID'
    ,['Inventory Transaction History'].[NOTEINDX] AS 'Note Index'
    ,NULL AS 'Posted Date'
    ,NULL AS 'Posted User ID'
    ,NULL AS 'Posting Status'
    ,rtrim(['Item Master'].[PRCLEVEL]) AS 'PriceLevel'
    ,rtrim(['Item Master'].[PriceGroup]) AS 'Price Group'
    ,'Price Method' = dbo.DYN_FUNC_Price_Method(['Item Master'].[PRICMTHD])
    ,(
        SELECT rtrim([ACTNUMST]) from [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PINFLIDX]
        ) AS 'Purch Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURMCIDX]) AS 'Purch Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURPVIDX]
        ) AS 'Purchase Price Variance Account Number'
    ,rtrim(['Item Master'].[PRCHSUOM]) AS 'Purchasing U Of M'
    ,['Inventory Transaction Amounts History'].[QTYBSUOM] AS 'QTY In Base U Of M'
    ,NULL AS 'Recurring Document Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLSIDX]
        ) AS 'Sales Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLDSIX]
        ) AS 'Sales Discounts Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLRNIX]
        ) AS 'Sales Returns Account Number'
    ,rtrim(['Item Master'].[SELNGUOM]) AS 'Selling U Of M'
    ,'Source Indicator' = dbo.DYN_FUNC_Source_Indicator(['Inventory Transaction History'].[SOURCEINDICATOR])
    ,rtrim(['Inventory Transaction History'].[SRCRFRNCNMBR]) AS 'Source Reference Number'
    ,['Item Master'].[STNDCOST] AS 'Standard Cost'
    ,NULL AS 'TRX QTY Total'
    ,NULL AS 'TRX Source'
    ,rtrim(['Item Master'].[TCC]) AS 'Tax Commodity Code'
    ,'Tax Options' = dbo.DYN_FUNC_Tax_Options(['Item Master'].[TAXOPTNS])
    ,'Transfer From QTY Type' = dbo.DYN_FUNC_Transfer_From_QTY_Type(['Inventory Transaction Amounts History'].[TRFQTYTY])
    ,rtrim(['Inventory Transaction Amounts History'].[TRNSTLOC]) AS 'Transfer To Location'
    ,'Transfer To QTY Type' = dbo.DYN_FUNC_Transfer_To_QTY_Type(['Inventory Transaction Amounts History'].[TRTQTYTY])
    ,rtrim(['Item Master'].[UOMSCHDL]) AS 'U Of M Schedule'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[UPPVIDX]
        ) AS 'Unrealized Purchase Price Variance Account Number'
    ,NULL AS 'Usage Type'
    ,rtrim(['Item Master'].[USCATVLS_1]) AS 'User Category Value 1'
    ,rtrim(['Item Master'].[USCATVLS_2]) AS 'User Category Value 2'
    ,rtrim(['Item Master'].[USCATVLS_3]) AS 'User Category Value 3'
    ,rtrim(['Item Master'].[USCATVLS_4]) AS 'User Category Value 4'
    ,rtrim(['Item Master'].[USCATVLS_5]) AS 'User Category Value 5'
    ,rtrim(['Item Master'].[USCATVLS_6]) AS 'User Category Value 6'
    ,'Valuation Method' = dbo.DYN_FUNC_Valuation_Method(['Item Master'].[VCTNMTHD])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVVARIDX]
        ) AS 'Variances Account Number'
    ,['Item Master'].[WRNTYDYS] AS 'Warranty Days'
    ,'Assembly Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[ASMVRIDX])
    ,'COGS Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVCOGSIX])
    ,'COGS Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[CGSINFLX])
    ,'COGS Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[CGSMCIDX])
    ,'Damaged Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVDMGIDX])
    ,'Document Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppItemTransactionNumber(1, ['Inventory Transaction Amounts History'].[ITEMNMBR], ['Inventory Transaction Amounts History'].[DOCTYPE], ['Inventory Transaction Amounts History'].[DOCNUMBR], 3)
    ,'Drop Ship Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[DPSHPIDX])
    ,'In Service Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINSVIX])
    ,'In Use Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINUSIX])
    ,'Inventory Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Inventory Transaction Amounts History'].[IVIVINDX])
    ,'Inventory Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVINFIDX])
    ,'Inventory Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[INVMCIDX])
    ,'Inventory Offset Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Inventory Transaction Amounts History'].[IVIVOFIX])
    ,'Inventory Returns Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVRETIDX])
    ,'Item Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppItemID(1, ['Inventory Transaction Amounts History'].[ITEMNMBR], ['Item Master'].[LOCNCODE])
    ,'Purch Inflation Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PINFLIDX])
    ,'Purch Monetary Correction Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PURMCIDX])
    ,'Purchase Price Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[PURPVIDX])
    ,'Sales Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLSIDX])
    ,'Sales Discounts Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLDSIX])
    ,'Sales Returns Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVSLRNIX])
    ,'Unrealized Purchase Price Variance Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[UPPVIDX])
    ,'Variances Account Number For Drillback' = 'dgpp://DGPB/?Db=&Srv=VMACCOUNTING&Cmp=FP&Prod=0' + dbo.dgppAccountIndex(1, ['Item Master'].[IVVARIDX])
    FROM [IV30300] AS ['Inventory Transaction Amounts History'] WITH (NOLOCK)
        LEFT JOIN [IV30200] AS ['Inventory Transaction History'] WITH (NOLOCK) ON ['Inventory Transaction Amounts History'].[DOCTYPE] = ['Inventory Transaction History'].[IVDOCTYP] AND ['Inventory Transaction Amounts History'].[DOCNUMBR] = ['Inventory Transaction History'].[DOCNUMBR]
        LEFT JOIN [IV00101] AS ['Item Master'] WITH (NOLOCK) ON ['Inventory Transaction Amounts History'].[ITEMNMBR] = ['Item Master'].[ITEMNMBR]
union all
SELECT rtrim(h.sopnumbe) AS 'Document Number'
    ,'Document Type' = dbo.DYN_FUNC_SOP_Type(h.[SOPTYPE])
    ,rtrim(l.[ITEMNMBR]) AS 'Item Number'
    ,rtrim(l.[UOFM]) AS 'U Of M'
    ,l.[quantity] AS 'TRX QTY'
    ,l.[UNITCOST] AS 'Unit Cost'
    ,l.[EXTDCOST] AS 'Extended Cost'
    ,rtrim(l.locncode) AS 'TRX Location'
    ,h.[DOCDATE] AS 'Document Date'
    ,'Document Status' = dbo.DYN_FUNC_Document_Status_IV_Trx(2)
    ,'ABC Code' = dbo.DYN_FUNC_ABC_Code(['Item Master'].[ABCCODE])
    ,'Allow Back Orders' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[ALWBKORD])
    ,rtrim(['Item Master'].[ALTITEM1]) AS 'Alternate Item 1'
    ,rtrim(['Item Master'].[ALTITEM2]) AS 'Alternate Item 2'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[ASMVRIDX]
        ) AS 'Assembly Variance Account Number'
    ,rtrim(h.[BACHNUMB]) AS 'Batch Number'
    ,rtrim(h.[BCHSOURC]) AS 'Batch Source'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVCOGSIX]
        ) AS 'COGS Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSINFLX]
        ) AS 'COGS Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[CGSMCIDX]
        ) AS 'COGS Monetary Correction Account Number'
    ,['Item Master'].[CREATDDT] AS 'Created Date'
    ,['Item Master'].[CURRCOST] AS 'Current Cost'
    ,NULL AS 'Customer Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVDMGIDX]
        ) AS 'Damaged Account Number'
    ,'Decimal Places Currency' = dbo.DYN_FUNC_Decimal_Places_Currency(['Item Master'].[DECPLCUR])
    ,'Decimal Places QTYS' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Item Master'].[DECPLQTY])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[DPSHPIDX]
        ) AS 'Drop Ship Account Number'
    ,'1/1/1900' AS 'GL Posting Date'
    ,'' AS 'History Module'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINSVIX]
        ) AS 'In Service Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINUSIX]
        ) AS 'In Use Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] =l.INVINDX
        ) AS 'Inventory Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVIVINDX]
        ) AS 'Inventory Account Number from Item Master'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVINFIDX]) AS 'Inventory Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[INVMCIDX]) AS 'Inventory Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = l.INVINDX) AS 'Inventory Offset Account Number'
    ,NULL AS 'Inventory Offset Account Number from Item Master'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVRETIDX]
        ) AS 'Inventory Returns Account Number'
    ,rtrim(['Item Master'].[ITMCLSCD]) AS 'Item Class Code'
    ,rtrim(['Item Master'].[ITEMCODE]) AS 'Item Code'
    ,rtrim(['Item Master'].[ITEMDESC]) AS 'Item Description'
    ,rtrim(['Item Master'].[ITMGEDSC]) AS 'Item Generic Description'
    ,['Item Master'].[ITEMSHWT] / 100.00 AS 'Item Shipping Weight'
    ,rtrim(['Item Master'].[ITMSHNAM]) AS 'Item Short Name'
    ,rtrim(['Item Master'].[ITMTSHID]) AS 'Item Tax Schedule ID'
    ,'Item Tracking Option' = dbo.DYN_FUNC_Item_Tracking_Option(['Item Master'].[ITMTRKOP])
    ,'Item Type' = dbo.DYN_FUNC_Item_Type(['Item Master'].[ITEMTYPE])
    ,'Keep Calendar History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPCALHST])
    ,'Keep Distribution History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPDSTHST])
    ,'Keep Period History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPERHIST])
    ,'Keep Trx History' = dbo.DYN_FUNC_Boolean_All(['Item Master'].[KPTRXHST])
    ,'Kit COGS Account Source' = dbo.DYN_FUNC_Kit_COGS_Account_Source(['Item Master'].[KTACCTSR])
    ,rtrim(['Item Master'].[LASTGENSN]) AS 'Last Generated Serial Number'
    ,l.LNITMSEQ AS 'Line SEQ Number'
    ,rtrim(['Item Master'].[LOCNCODE]) AS 'Location Code'
    ,rtrim(['Item Master'].[LOTTYPE]) AS 'Lot Type'
    ,['Item Master'].[MSTRCDTY] AS 'Master Record Type'
    ,['Item Master'].[MODIFDT] AS 'Modified Date'
    ,NULL AS 'Modified User ID'
    ,0 AS 'Note Index'
    ,NULL AS 'Posted Date'
    ,NULL AS 'Posted User ID'
    ,NULL AS 'Posting Status'
    ,rtrim(['Item Master'].[PRCLEVEL]) AS 'PriceLevel'
    ,rtrim(['Item Master'].[PriceGroup]) AS 'Price Group'
    ,'Price Method' = dbo.DYN_FUNC_Price_Method(['Item Master'].[PRICMTHD])
    ,(
        SELECT rtrim([ACTNUMST]) from [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PINFLIDX]
        ) AS 'Purch Inflation Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURMCIDX]) AS 'Purch Monetary Correction Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[PURPVIDX]
        ) AS 'Purchase Price Variance Account Number'
    ,rtrim(l.QTYBSUOM) AS 'Purchasing U Of M'
    ,l.QTYBSUOM AS 'QTY In Base U Of M'
    ,NULL AS 'Recurring Document Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLSIDX]
        ) AS 'Sales Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLDSIX]
        ) AS 'Sales Discounts Account Number'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVSLRNIX]
        ) AS 'Sales Returns Account Number'
    ,rtrim(['Item Master'].[SELNGUOM]) AS 'Selling U Of M'
    ,'Source Indicator' =''
    ,'' AS 'Source Reference Number'
    ,['Item Master'].[STNDCOST] AS 'Standard Cost'
    ,NULL AS 'TRX QTY Total'
    ,NULL AS 'TRX Source'
    ,rtrim(['Item Master'].[TCC]) AS 'Tax Commodity Code'
    ,'Tax Options' = dbo.DYN_FUNC_Tax_Options(['Item Master'].[TAXOPTNS])
    ,'Transfer From QTY Type' = 0
    ,'' AS 'Transfer To Location'
    ,'Transfer To QTY Type' = 0
    ,rtrim(['item master'].UOMSCHDL) AS 'U Of M Schedule'
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[UPPVIDX]
        ) AS 'Unrealized Purchase Price Variance Account Number'
    ,NULL AS 'Usage Type'
    ,rtrim(['Item Master'].[USCATVLS_1]) AS 'User Category Value 1'
    ,rtrim(['Item Master'].[USCATVLS_2]) AS 'User Category Value 2'
    ,rtrim(['Item Master'].[USCATVLS_3]) AS 'User Category Value 3'
    ,rtrim(['Item Master'].[USCATVLS_4]) AS 'User Category Value 4'
    ,rtrim(['Item Master'].[USCATVLS_5]) AS 'User Category Value 5'
    ,rtrim(['Item Master'].[USCATVLS_6]) AS 'User Category Value 6'
    ,'Valuation Method' = dbo.DYN_FUNC_Valuation_Method(['Item Master'].[VCTNMTHD])
    ,(
        SELECT rtrim([ACTNUMST])
        FROM [GL00105] AS ['Account Index Master']
        WHERE ['Account Index Master'].[ACTINDX] = ['Item Master'].[IVVARIDX]
        ) AS 'Variances Account Number'
    ,['Item Master'].[WRNTYDYS] AS 'Warranty Days'
    ,'Assembly Variance Account Number For Drillback' = ''
    ,'COGS Account Number For Drillback' = ''
    ,'COGS Inflation Account Number For Drillback' =''
    ,'COGS Monetary Correction Account Number For Drillback' = ''
    ,'Damaged Account Number For Drillback' = ''
    ,'Document Number For Drillback' =''
    ,'Drop Ship Account Number For Drillback' =''
    ,'In Service Account Number For Drillback' =''
    ,'In Use Account Number For Drillback' = ''
    ,'Inventory Account Number For Drillback' = ''
    ,'Inventory Inflation Account Number For Drillback' = ''
    ,'Inventory Monetary Correction Account Number For Drillback' = ''
    ,'Inventory Offset Account Number For Drillback' = ''
    ,'Inventory Returns Account Number For Drillback' = ''
    ,'Item Number For Drillback' = ''
    ,'Purch Inflation Account Number For Drillback' = ''
    ,'Purch Monetary Correction Account Number For Drillback' = ''
    ,'Purchase Price Variance Account Number For Drillback' = ''
    ,'Sales Account Number For Drillback' =''
    ,'Sales Discounts Account Number For Drillback' = ''
    ,'Sales Returns Account Number For Drillback' = ''
    ,'Unrealized Purchase Price Variance Account Number For Drillback' = ''
    ,'Variances Account Number For Drillback' = ''
        from sop10100 h
            join sop10200 l on l.sopnumbe = h.sopnumbe and l.soptype = h.soptype
            LEFT JOIN [IV00101] AS ['Item Master'] WITH (NOLOCK) ON l.[ITEMNMBR] = ['Item Master'].[ITEMNMBR]
        where l.DROPSHIP = 1

 

http://dyndeveloper.com/menus/SQLGeneral.aspx

 

                       


RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences