select MAX(batchId) as batchId from tbl_EAI_inboundBatches with (nolock) where ownerId = and ownerType = and queueStatus = 300 and fileformat = order by batchId desc select top 1 batchId, fileName from tbl_EAI_inboundBatches with (nolock) where ownerId = and ownerType = and batchId = and queueStatus = 300 and fileformat = order by batchId desc select distinct fseFiscalYear, fseFiscalMonth, fseFiscalWeek from tbl_IMPORT_600_Invoice_Header with (nolock) where eai_batchId = and customer_number <> '' order by fseFiscalYear, fseFiscalMonth, fseFiscalWeek

Creating #qmd_fiscalMonthsInBatch.recordCount# Child Batch(es)

select * from tbl_IMPORT_610_Invoice_Detail with (nolock) where eai_batchId = and eai_importId = 0 insert into tbl_IMPORT_610_Invoice_Detail ( eai_batchId, #_childBatchColumns# , fseHeaderBatchId ) select #_childBatchId# eai_batchId , d.#_childBatchColumn# ,#_headerBatchId# as fseHeaderBatchId from tbl_IMPORT_610_invoice_detail d with (nolock) inner join tbl_IMPORT_600_invoice_header h with (nolock) on h.eai_batchId = and h.ownerId = d.ownerId and h.ownerType = d.ownerType and h.fseFiscalYear = and h.fseFiscalMonth = and h.fseFiscalWeek = and h.invoice_number = d.invoice_number where d.ownerId = and d.ownerType = and d.eai_batchId = and d.invoice_number <> '' and h.customer_number <> '' and d.invoice_line_type <> '' and d.invoice_line_type in ( 'N', 'K' ) update tbl_EAI_inboundBatches set lineCount = , message = 'Child Batch', queueStatus = 0, status = 'PENDING' where batchId =

Child Batch ###_childBatchId# created for FY#fseFiscalYear# P#fseFiscalMonth# W#fseFiscalWeek# (#_cbInsert.recordCount# lines)

delete tbl_EAI_inboundBatches where batchId = delete tbl_EAI_childBatches where childBatchId = and batchId = #qmd_batchWithResults.resultsText#

Child Batches Completed

update tbl_IMPORT_610_Invoice_Detail set fseHeaderBatchId = where eai_batchId = select h.customer_number, h.invoice_number, format( h.fseInvoiceDate, 'MM/dd/yyyy' ) as invoice_date, h.fseForecastYear, h.fseForecastMonth, h.fseFiscalMonth, h.fseFiscalYear, CONVERT(VARCHAR(20), d.item_number) as sku, h.reverseInvoice, sum(d.extended_sales_dollars ) extended_price, case when coalesce(nullif( d.uom, '' ), 'CS' ) = 'CS' then sum( convert( int, d.invoiced_volume )) else case when coalesce( pkg.master_net_weight, '' ) = '' then 0 else sum( convert( decimal, d.invoiced_volume ))/ convert( decimal, (coalesce ( pkg.master_net_weight, '' )) ) end end case_Qty , case when coalesce(nullif( d.uom, '' ), 'CS' ) <> 'CS' then sum( convert( decimal, d.invoiced_volume )) else 0 end lb_qty, CONVERT(VARCHAR(5), coalesce(nullif( d.uom, '' ), 'CS' )) as unit_of_sale from tbl_IMPORT_610_Invoice_Detail d with (nolock) inner join tbl_IMPORT_600_Invoice_Header h with (nolock) on h.eai_batchId = and h.ownerId = d.ownerId and h.ownerType = d.ownerType and h.invoice_number = d.invoice_number and h.eai_batchId = d.fseHeaderBatchId left outer join tbl_PRD_Skus s with (nolock) on s.sku = d.item_number and s.ownerId = d.ownerId and s.fsl_tableCode = d.ownerType left outer join tbl_PRD_Packaging pkg with (nolock) on pkg.LinkID = s.skuId and pkg.LinkType = 'SKU' and pkg.ownerId = s.ownerId and pkg.fsl_tablecode = s.fsl_tablecode and ( pkg.master_net_weight_type = 'NET' or pkg.master_net_weight_type = 'AVG' ) and pkg.MASTER_NET_WEIGHT_UNIT = 'LB' and ISNUMERIC( pkg.master_net_weight ) = 1 where d.eai_batchId = and d.ownerId = and d.ownerType = and h.fseInvoiceDate <> '' and h.order_type <> '' group by h.customer_number, h.invoice_number, h.fseInvoiceDate, h.fseForecastYear, h.fseForecastMonth, h.fseFiscalMonth, h.fseFiscalYear, d.item_number, h.reverseInvoice, d.uom, pkg.master_net_weight select h.customer_number, h.invoice_number, format( h.fseInvoiceDate, 'MM/dd/yyyy' ) as invoice_date, h.fseForecastYear, h.fseForecastMonth, h.fseFiscalMonth, h.fseFiscalYear, CONVERT(VARCHAR(20), d.item_number) as sku, h.reverseInvoice, sum(d.extended_sales_dollars ) extended_price, case when coalesce(nullif( d.uom, '' ), 'CS' ) = 'CS' then sum( convert( int, d.invoiced_volume )) else 0 end case_Qty , case when coalesce(nullif( d.uom, '' ), 'CS' ) <> 'CS' then sum( convert( decimal, d.invoiced_volume )) else 0 end lb_qty, CONVERT(VARCHAR(5), coalesce(nullif( d.uom, '' ), 'CS' )) as unit_of_sale from tbl_IMPORT_610_Invoice_Detail d with (nolock) inner join tbl_IMPORT_600_Invoice_Header h with (nolock) on h.eai_batchId = and h.ownerId = d.ownerId and h.ownerType = d.ownerType and h.invoice_number = d.invoice_number and h.eai_batchId = d.fseHeaderBatchId where d.eai_batchId = and d.ownerId = and d.ownerType = and h.fseInvoiceDate <> '' and h.order_type not in ('X', 'K') and d.invoice_line_type <> '' group by h.customer_number, h.invoice_number, h.fseInvoiceDate, h.fseForecastYear, h.fseForecastMonth, h.fseFiscalMonth, h.fseFiscalYear, d.item_number, h.reverseInvoice, d.uom select customer_number, invoice_number, invoice_date,fseForecastYear,fseForecastMonth, fseFiscalMonth, fseFiscalYear, sku,reverseInvoice,extended_price, case_Qty, lb_qty, unit_of_sale from qmd_invoiceHistory

No Invoice Records to process

select formatid, formatName from tbl_eai_inboundFIleformats with (nolock) where ownerid = and ownerType = and formatname = 'BOSTON_INVOICE_HISTORY_TXT' select formatid, formatName from tbl_eai_inboundFIleformats with (nolock) where ownerid = 0 and ownerType = 'MFR' and formatname = 'BOSTON_INVOICE_HISTORY_TXT'

Invoices after combining Details & Header using BOSTON_INVOICE_HISTORY_TXT

Created batch #_loadCombinedInvoicesResult.batchId#Not Loaded

Invoices loaded : #numberformat(qmd_invoiceHistory.recordCount)# records

#_combinedInvoices# delete from tbl_EAI_inboundBatches where batchId = or filename like