Bottle Returns POS

The migration of Adempiere modules to iDempiere

Bottle Returns POS

Postby red1 » Thu Apr 23, 2015 9:49 pm

As described in https://groups.google.com/forum/#!topic ... 9iW8LjghJw
In a juice distributor scenario, a distributor sells crates of bottled juice drinks to retailers and then only invoice them after they return the bottles to give a net sale value of just the drinks. How best to handle this?

My suggestion and at the end some suggested enhancements as well as best practice question:
1. Sales Order for a BOM of product - children been juice (90cts) and empty bottle (10cts) = $1.00
2. Generate Shipment from Order, pick from inventory, send to retailier.
3. Retailer returns the bottles, record them in:
a. RMA Customer with Shipment ID linked.
b. Customer Returns based on 3a.
c. Shipment to Locator "Empty Bottles" based on 3b
d. Invoice Credit Memo based on 3c.
4. Issue Sales Invoice with Credit Memo for retailer to pay the difference.

In the end all accounts and inventory locators reflect everything.

Enhancements:
1. Same like POS Sales, i like to create a RMA POS which does steps 3.a,b,c,d one shot during RMA Customer
2. During 4, i create a JasperReport for Customer Invoice Print Format to reflect both Invoice and Credit Memo.

Now i have made enhancement (1) as shown in the following Customer RMA screenshot at the end of the plugin process where a Sale of 10 units is then returned 7 units.

Screen Shot 2015-04-23 at 3.38.39 PM.png
Screen Shot 2015-04-23 at 3.38.39 PM.png (92.01 KiB) Viewed 9905 times

The inventory is separated automatically where HQ Transit stores the 'empty bottles'

Screen Shot 2015-04-23 at 3.38.06 PM.png
Screen Shot 2015-04-23 at 3.38.06 PM.png (74.15 KiB) Viewed 9905 times

The accounts reflecting an Invoice of 10 with a Credit Memo of 7:

Screen Shot 2015-04-23 at 3.47.48 PM.png
Screen Shot 2015-04-23 at 3.47.48 PM.png (32.94 KiB) Viewed 9905 times

Payment window will then be able to allocate both invoices visibly. Next will be to make a JasperReport for the Sales Invoice to incorporate the Credit Memo to advice the business the whole affair on a single page for the customer's easy reference.

Source is at https://bitbucket.org/red1/org.business.bottlerma
Plugin is at https://sourceforge.net/projects/red1/files/Business/
You should:
1) Uncheck the MM Customer Returns DocType confirmation box
2) You may make a new RMA Type 'Empty Bottle' for fun
3) You should have a client warehouse with SearchValue = 'HQT' and name can be 'Empty Bottle'.
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Bottle Returns POS

Postby red1 » Fri Apr 24, 2015 1:50 pm

BeforePatch.png
BeforePatch.png (36.61 KiB) Viewed 9887 times
Now the juice is flowing. During Customer RMA, the pop up selection does not show the balance from previous RMA and does not capture this qty during RMA Line creation. It even gives an error pop show stopper. Now it is solved with a nice short but non-impact patch. https://idempiere.atlassian.net/browse/IDEMPIERE-2594

StartBalance.png
StartBalance.png (30.47 KiB) Viewed 9887 times
Balance3.png
Balance3.png (29.07 KiB) Viewed 9887 times

The first screen showing at the start of the return and the second when the customer returns again the balance. There is a slight hitch detected during testing where the zero line still passes but will not pass thru saving due to more than shipped total which means it does not impact the law.

The only code needed to change is the SQL to extract a previous RMA record of the same shipment:

Screen Shot 2015-04-24 at 7.46.47 AM.png
Screen Shot 2015-04-24 at 7.46.47 AM.png (56.04 KiB) Viewed 9887 times

The above failed when i tried multiple returns onto single Shipment. So easily solved by making it as a SUM(rmal.qty):
Code: Select all
diff --git a/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java b/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java
--- a/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java
+++ b/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java
@@ -73,7 +73,7 @@
         sqlStmt.append("SELECT iol.M_InOutLine_ID, iol.Line, ");
         sqlStmt.append("COALESCE(p.Name, c.Name) AS ProductName, ");
         sqlStmt.append("iol.QtyEntered, ");
-        sqlStmt.append("iol.movementQty, ");
+        sqlStmt.append("iol.movementQty-(SELECT COALESCE((SELECT SUM(rmal.qty) FROM M_RMALine rmal INNER JOIN M_InOutLine iol ON rmal.M_InOutLine_ID=iol.M_InOutLine_ID AND iol.M_InOut_ID = ?),0)) AS MovementQty, ");
         sqlStmt.append("CASE WHEN iol.M_AttributeSetInstance_ID IS NOT NULL THEN (SELECT SerNo FROM M_AttributeSetInstance asi WHERE asi.M_AttributeSetInstance_ID=iol.M_AttributeSetInstance_ID) END as ASI ");
         sqlStmt.append("FROM M_InOutLine iol ");
         sqlStmt.append("LEFT JOIN M_Product p ON p.M_Product_ID = iol.M_Product_ID ");
@@ -87,7 +87,8 @@
         {
             pstmt = DB.prepareStatement(sqlStmt.toString(), null);
             pstmt.setInt(1, M_InOut_ID);
-            pstmt.setInt(2, M_RMA_ID);
+            pstmt.setInt(2, M_InOut_ID);
+            pstmt.setInt(3, M_RMA_ID);

Now we await the peer review and maintainer's decision to admit it to core code.
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Bottle Returns POS

Postby red1 » Fri Apr 24, 2015 4:19 pm

I updated a more aesthetic finishing as the box allows a zero to pass thru as a full qty (!) but of course stops it later as checkQty fails it as over shipped qty. I use the patch here:
Code: Select all
@@ -160,6 +161,8 @@
                 BigDecimal d = (BigDecimal)miniTable.getValueAt(i, 5);              //  5-Movement Qty
                 KeyNamePair pp = (KeyNamePair)miniTable.getValueAt(i, 1);   //  1-Line
                 
+                if (d.compareTo(Env.ZERO)==0) //red1 - IDEMPIERE-2594, stopping zero from passing thru, trigger desired qtyCheck error
+                   d=Env.ONE;

which gives the result as desired, fooling it not to proceed as the checkQty kicks in first before saving.

ZeroCaseTrigger.png
ZeroCaseTrigger.png (37.64 KiB) Viewed 9884 times
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Bottle Returns POS

Postby red1 » Sat Apr 25, 2015 1:08 am

Testing with multi lines with incremental returns gave erroneous and wrong results but finally the inner SQL construct solves it:
Code: Select all
SELECT SUM(rmal.qty) FROM M_RMALine rmal WHERE rmal.M_InOutLine_ID=iol.M_InOutLine_ID

Screen Shot 2015-04-24 at 7.04.04 PM.png
Screen Shot 2015-04-24 at 7.04.04 PM.png (33.63 KiB) Viewed 9877 times

Patch is updated in the JIRA.
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Bottle Returns POS

Postby red1 » Sat Apr 25, 2015 3:03 am

Carlos Ruiz gave a hand here with just a single word,

Code: Select all
diff -r 1861093d9de8 org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java
--- a/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java Fri Apr 24 12:08:56 2015 -0500
+++ b/org.adempiere.ui/src/org/compiere/grid/CreateFromRMA.java Fri Apr 24 12:32:57 2015 -0500
@@ -136,7 +136,7 @@
                miniTable.setColumnClass(2, String.class, true);        //  2-Product
                miniTable.setColumnClass(3, String.class, true);        //  3-ASI
                miniTable.setColumnClass(4, BigDecimal.class, true);        //  4-Qty
-               miniTable.setColumnClass(5, BigDecimal.class, true);        //  5-Delivered Qty
+               miniTable.setColumnClass(5, BigDecimal.class, false);        //  5-Delivered Qty

resulting in no need to go to the detail row level to edit any value:

Screen Shot 2015-04-24 at 8.59.56 PM.png
Screen Shot 2015-04-24 at 8.59.56 PM.png (36.25 KiB) Viewed 9874 times
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Re: Bottle Returns POS

Postby red1 » Wed May 20, 2015 6:05 pm

I made an instant BOM Drop within the Order Lines without doing a BOM Drop! It is just a matter of using the plugin, enter some BOM parent, and Complete or Prepare your Order. The plugin merely expands the BOM children (all levels!) into your Order as new OrderLines and set the parent BOM line to zero Qty so as not to duplicate the items.

Screen Shot 2015-05-20 at 12.14.02 PM.png
Screen Shot 2015-05-20 at 12.14.02 PM.png (96.12 KiB) Viewed 9748 times

*(For newbies, a BOM Drop is a conversion method where a Parent is exchanged with its Children into the Order).
*(For idiots, a BOM is a build of materials usually used during Production where BOM children are transformed to a BOM Parent with inventory and accounting consequences).

Below is a two level BOM been fully exploded or dropped from a BOMParent that was set at Qty 2. Notice that parents (within the hierarchy) are zeroised to take the final children prices. (Now this therefore is the hard assumption that you allow this to be so, i.e. any parent pricing will not be valid.) Also note that the getPrice (database function to roll up BOM) now works fully due to a fix i did some days ago. Note that children are multiplied accordingly (i.e. grandchildren doubled). Source is here.

Screen Shot 2015-05-22 at 10.42.04 AM.png
Screen Shot 2015-05-22 at 10.42.04 AM.png (68.99 KiB) Viewed 9641 times

I have provided a Sample 2 Level BOM. sql migration script that has 2 children where one of them has 2 more children. It is in the http://sourceforge.net/projects/red1/files/Business/ folder.
red1
Site Admin
 
Posts: 2760
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia


Return to iDempiere

Who is online

Users browsing this forum: No registered users and 2 guests

cron