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:
InvoiceNo | Extension | WarehouseCode (Franchise) | HeaderSeq | DetailSeq |
3293277 | 120 | 25 | 0 | 1 |
3293277 | 75 | 25 | 0 | 2 |
3293277 | 60 | 41 | 0 | 3 |
3293277 | 120 | 44 | 0 | 4 |
3293277 | 60 | 69 | 0 | 5 |
3293277 | 60 | 69 | 0 | 6 |
3293277 | 75 | 69 | 0 | 7 |
3293277 | 60 | 83 | 0 | 8 |
3293277 | 60 | 123 | 0 | 9 |
3293277 | 60 | 123 | 0 | 10 |
3293277 | 75 | 123 | 0 | 11 |
3293277 | 75 | 125 | 0 | 12 |
3293277 | 120 | 196 | 0 | 13 |
3293277 | 60 | 196 | 0 | 14 |
3293277 | 75 | 125 | 0 | 15 |