We are trying to write a query or stored procedure to identify the cash receipts for each of our franchises (warehouses) but ran into some trouble with consolidated billing. Since payments aren't applied to the line items we need to create a lookup table to use against AR_OpenInvoice. The problem I cannot wrap my head around involves looping over each invoiceno, headerseqno, warehouse, and extensionamt in the AR_InvoiceHistoryDetail to get the resulting table for each invoice with % owed to each franchise (example):

InvoiceNo | HeaderSeqNo | WarehouseCode | % owed to Warehouse

000111        00000               99                          45%

000111        00000               108                        55%

000112        00000               99                          100%

000113        00000               15                          33.33%

000113        00000               20                          33.33%

000113        00000               19                          33.33%

Should I sum the extension amount on each invoice first then look at the extension amount for each detailseqno on that invoice? I cant seem to get total sales on the invoice as a percent owed to each franchise.

Any additional help or suggestions are appreciated. I tried to just scheduled an rrd and parse the crystal result however it is slow and ugly.

Example of the raw output without doing any formatting: