0 Replies Latest reply on Apr 12, 2013 8:16 AM by MichaelVoccola

    Cascading PO's and LineItem Pricing

    MichaelVoccola

      Title

      Cascading PO's and LineItem Pricing

      Post

           The solution in question is solving a problem of purchasing items from other companies in the system. Essentially there are currently three tables in the relationship:

             
      •           Vendor
      •      
      •           Job
      •      
      •           LineItems
      •      
      •           LineItems_Prices

            

           When a user creates a Job that user's ID is set as Job::DIV_OwnerID. The user can then set the client ID and begin adding lineitems etc... When a lineitem is added to the current job a script creates a new job naming the LineItem::VendorID as the new Job::DIV_OwnerID and the new Job::ClientID as the original job's Job::DIV_OwnerID. If there is already a job record, the script ignores this step.

           Each LineItem has two prices that each user will see: cost and sold. The trick here is that these actually need to be in the separate table "LineItems_Prices" which has the following fields:

             
      •           LineItem_RecordID (references the lineitem the price is for)
      •      
      •           Price
      •      
      •           GlobalJobID
      •      
      •           JobID

            

           Two TO's of LineItems_Prices will be needed to drive the LineItem's "Cost" and "Sold" prices

           LineItems contains the following fields:

             
      •           JobID
      •      
      •           GlobalJobID
      •      
      •           ItemID
      •      
      •           RecordID

            

      Goal:

           The end-goal is to have each respective DIV_OwnerID (user) see only the LineItems_Prices relating to them. For example, I am creating a job for a non-user client and add an item from CompanyA which triggers the previously mentioned script. The LineItem shows me my Cost and Sold prices. When CompanyA logs in, it shows them their Cost and Sold prices. CompanyA needs to add an item that I didn't put on the original PO that is going to be billed to my job, so they add a new item of their own as well as something they need to acquire from yet another vendor on the system and so on....

      Notes:

             
      •           "GlobalJobID" is not a global field, it is simply a tracking number that keeps all related items together. It helps all the vendors in the system stay on the same page when they are working together.
      •      
      •           When a user signs in, a script and self-join of users sets the global field CurrentUser::gDIV_OwnerID to the user's associated company.
      •      
      •           The solution to this problem needs to allow for a vendor creating a job to add items from their own inventory as well.

            

      Thanks!