Mark Invoices Collected

Purpose
Marking invoices collected based upon the cash receipts that come in is mainly for the purpose of accurately accounting for the DA’s monthly payments.

DAs are paid a percentage of the royalties collected from the stores in the territory. The payment is based on a percentage of the gross collections of those stores minus the amount collected that is applied to Invoices that reduce the DA’s check. These invoices are identified by what DA Payment Group type is assigned to the Invoice Item/Account Type. These numbers calculated in this process give more detail on the Continuing Fees report and as a result informs the DA of the total amount of collections received from the store as well as the portion that the DA receives based on how the payment was applied.

There are two procedures that do the bulk of the work for marking invoices as collect proc_Subway_Invoices_MarkCollected and proc_Subway_Invoice_UpdateCollectedAmount

proc_Subway_Invoices_MarkCollected
This procedure is in charge of:
 * locking and Unlocking the objects to prevent posting
 * gathering all the cash receipts to be collected
 * marking the Country/Bank Type as collected in the EftPostingDates table—this is used to let On Hold invoices know that collection was made

One significant changed made was that this stored procedure does not create a transaction. While each individual cash receipt item is handled in a transaction, the over arching procedure is not. This still allows the procedure to be re-runnable from the point of failure while not having to start from square one.

proc_Subway_Invoice_UpdateCollectedAmount
This procedure takes in one CashReceiptItemId number to process. The details of the cash receipt come from the table CashRecieptsWaitingCollection. The details are stored in variables, EntityType, EntityId, AssociatedBalanceId, CurrencyId, PostingDate, ConversionRate, BalanceId and Amount.

If the BalanceId is not Royalty the program retrieves the AdjustmentType for the given entity

The Invoices to be collected are stored in a temp table in the proper order.

The table is populated with only those Invoices where the running sum of the previous invoices is less than the Cash Receipt amount. This ensures that only the records that could be updated are pull from the database.

All records except the last record in the temp table are updated to the full collection amount. The logic is if you are not the last record in the temp table then the value must be fully collected otherwise you would have been the last record in the table.

For the last record in the table the logic to determine the amount is least of the total amount of the Cash Receipt minus any money that has been applied or the item amount left to be collected on the invoice. This is because we can have more cash then we do invoices. This value is added to the current value of the Collected Amount. If the last Invoice does not cover the amount of the cash that is left, then the record in CashRecieptsWaitingCollection is updated with the amount left over. This will be applied on the next days run against any new Invoices created.

Note that if the currencies between the Invoice and Cash are different then the Cash is converted to the Invoice amount using the formula

If the currencies are the same no conversion is attempted even though the Cash and Invoice may have different conversion rates because they were created in two different months.

The values from the Temp table are then placed in a Log Table InvoiceCollectionLog