working with steve - 4/29/19 - problem with work in progress (transitional invoices) - working with scott jolley - if it is not closed, it is good to go (still in transitional) Asset In Transit on Work in Process - if fully closed and going back, it is over stating the asset inventory - maybe check the main invoice type and the date, ideally, we could just tweak the if statement for showing the in transit inventory (asset) - scenario - say we have a WIP (work in progress) invoice with the following details: start with $300 in inventory and oweing $300 as a payable (a/p) total invoice is for $250, Cost of goods sold is $150, profit is $100, an initial payment of $50, and an outstanding $200 still owed. the wip invoice started on 4/11/19 and went through 4/30/19. On 5/1/19, the invoice became real. where are the pieces and numbers at the different stages? 4/11/19 4/30/19 5/1/19 Revenue 0 0 250 COGS 0 0 150 GP 0 0 100 Expenses 0 0 0 NP 0 0 100 Assets - payments in bank 50 50 50 - wip inventory 150 150 0 - future revenue 250 250 0 - normal inventory 300 (- wip inventory) 300 (- wip inventory) 150 Liabilities - owe on inventory 300 300 300 - pre payments 50 50 0 Equity - future profit 100 100 0 /////////////////////////////////////////////////////////// as of 5/22/19 - this is a quick overview of what we do for QTI invoices on the balance sheet. - verbage for QTI (wip) stuff: (in the top_secret/cfc/search_10.cfc page) the QTI stuff only runs if running on normal dates (dateSwitch = 2) (line 196) the top query is called "qryGetQTIInvoices" - just invoice numbers from the transition_invoices table (line 200) - as a note, we may need to figure out which ones are still QTI's and which ones are finished. currently, it grabs both and treats them the same. the problem may be with the inventory date. ones still in QTI status will have the start transition date where as the closed ones will have the actual booked (last of three) dates. that may be a problem. SELECT invoice_number FROM transition_invoices WHERE corp_id = #Trim(arguments.corpId)# AND ((qti_start_date #useLessThanValue# #CreateODBCDate(arguments.useBalanceDate)# AND qti_finished_flag = 0) OR (qti_start_date #useLessThanValue# #CreateODBCDate(arguments.useBalanceDate)# AND qti_end_date #useGreaterThanValue# #CreateODBCDate(arguments.useBalanceDate)# AND qti_finished_flag = 1)) AND ((qti_start_date <= 6/12/19 AND qti_finished_flag = 0) OR (qti_start_date <= 6/12/19 AND qti_end_date >= 6/12/19 AND qti_finished_flag = 1)) GROUP BY invoice_number ORDER BY invoice_number there is a list called "qtiInvoice_list" with all QTI invoices (as a group) (line 214) there is a query called "qryGetQTILineItems" that pulls from the po_invoice_lines_53 table based off of the qtiInvoice_list (line 216) - contains extended line cost, extended line price, invoice number, and po/inv line id's there is a query called "qryGetQTITotals" that sums up things from the qryGetQTILineItems query (line 242) - total qti cost, total qti price the qtiInvoice_list gets re-filtered based off of the line items (line 247) there is a query called "qryGetDepositedQTIPayments" that pulls in data from the invoice_payments_53 table based off of the qtiInvoice_list (line 249) - total paid, pmt count -- going down a bit... assets - the general parts inventory just pulls based off of date, no tie-in to QTI invoices (line 839) - that may be a problem... assets - there is a new if statement that was added for in transit inventory on QTI invoices (line 911) - see notes above on the general inventory reports... there may be a date range difference here - **** on 6/25/19 - we may need to modify this... we need to have two different categories for still in progress and done - think of the bubble analogy to help record this assets - accounts receivable - for QTI invoices - total qti price from qryGetQTITotals (line 976) - **** on 6/25/19 - there is a tax problem here... the main qti totals just grab straight line items - we really need automate the full taxes piece - we need to move the tax pieces back and forth... we may need to skip this for now until we get the full thing automated. assets - normal a/r's don't include the QTI invoices - not in qtiInvoice_list (line 993) liabilities - both daily and monthly tax liabilities do not include QTI invoices (line 1051) - **** on 6/25/19 we may need another section that shows taxes on qti invoices - we have some questions here... make sure and check the math liabilities - a/p's - payments made on QTI invoices (line 1224) liabilities - COGS on QTI invoices (line 1775) - **** on 6/25/19 - maybe look at this... if we use in transit invnetory, we may not need this. - we may need a couple different scenarios based on open still or closed equity - net profit - future potential from QTI invoices (line 1799) ////////////////////////////////////////////////////////////////// on 6/11/19 - looking at data... invoice # 36 in corp 53 created on 6/3/10 all dates held the 6/3/10 value while in invoice type id 8 (transition or QTI) invoice_date, line_item_date, qti_start_date, qti_end_date once flipped to a normal customer invoice... the dates are changed. invoice_date and line_item_dates are updated. qti_start_date and qti_end_date are set accordingly (with a range, a day different than the main invoice date) this happens around line 740 ish on top_secret/cfc/assets_10.cfc in the "updateTransitionInvoice" method. ////////////////////////////////////////////////////////////////// on 6/12/19 - different scenarios qti invoice for: invoice # 132 for $89.71 (total invoice with tax) inventory/cost of goods $40 profit $43.92 revenue only (no taxes) $83.92 tax $5.79 payments made $0 ////////////////////////////////////////////////////////////// on 6/25/19 working with Steve - we may need to go to a line item level on the dates (po/invoice line items)... currently, we try really hard to match the main with the line items. There is some built-in code to help with this process. - we may need to pull some data... and see what to do with it - you stand in this line, you stand in this line. (analogy between parallel lines and diverging lines and the bubble analgoy - small flex zones or small flex bubbles) - we would love to see the ice-down date, aggregated data, watchers, feeders, tiggers, 3D calendars, taking things clear out to minutes and seconds vs just 24hour dates. - Steve and I were talking about display only pieces on the balance sheet... the future potential from qti invoices may be a display piece, not a real book piece. Just like the liabiltlty for loyalty points. Just ideas. - Small talks about posting (old school accounting and posting to differnet accounts) vs a computer just running logic to figure out where things were at - a computer can figure it out and repeat those actions every time so that it just flows through vs having to post and post and repost. Basically, make it show up for roll call vs doing different journal entries every time. - to make the current qti process work, we skipped the tax stuff until the very end... we also need to track both those lists of invoices that are still in transition and those that are done. This is really true if you are going back in time. - We briefly talked about using aggregated data to show daily break-downs in the full P&L and balance sheet levels - 3D calendars - different categories go down the left side. - dates go across the top. - totals show up below (per column per date), they also show up per section (far right per row or per record). - it would be so cool if we could put all of the known P&L and balance sheets categories on a single report and then pull from special daily totals (aggregated data) to populate thos values. - see this element of time in the adilas university site for some sketches of a similar idea https://www.adilas.biz/top_secret/time_web_gallery.cfm?corp=371&id=1797 - What has been changed and what has been iced down - as we move across time on a report and/or calendar? - What about using ajax (asynchronous calls to get the data) to figure out the aggregated totals as they show up. - It would be really cool to see the known issues and be able to drill-down to those values easily... even through the aggregated totals.