0040613: Volume Discount don not create invoice discount

Description

The Volume Discount asociated at a Business Partner 'x' does not create a discount sales invoice when another Volume Discount that this may be or not asociated at another Business Partner 'y' which has a Product Category or Product restriction.

There is a problem in the 'm_rappel_invoice_create' function on the db.

1- Does not checks if the product category is active or not.
2- It does not checks that the product category nor the product is defined on the same rappel(Volume discount) that is being used for the creation of the invoice.

This can be seen in this piece of code from the 'm_rappel_invoice_create':

AND((COALESCE(R.INCLUDE_PRODUCT_CATEGORY, 'Y')='N' -- Just selected

     AND EXISTS
       (SELECT 1
        FROM M_RAPPEL_PRODUCTCATEGORY RC, M_PRODUCT PROD
        WHERE PROD.M_PRODUCT_ID=IL.M_PRODUCT_ID
          AND RC.M_PRODUCT_CATEGORY_ID=PROD.M_PRODUCT_CATEGORY_ID ))
    OR (COALESCE(R.INCLUDE_PRODUCT_CATEGORY, 'Y')='Y' -- Everyone except selected

        AND NOT EXISTS
          (SELECT 1
           FROM M_RAPPEL_PRODUCTCATEGORY RC, M_PRODUCT PROD
           WHERE PROD.M_PRODUCT_ID=IL.M_PRODUCT_ID
             AND RC.M_PRODUCT_CATEGORY_ID=PROD.M_PRODUCT_CATEGORY_ID )))
AND((COALESCE(R.INCLUDE_PRODUCT, 'Y')='N' -- Just selected

     AND EXISTS
       (SELECT 1
        FROM M_RAPPEL_PRODUCT RP
        WHERE RP.M_PRODUCT_ID=IL.M_PRODUCT_ID
          AND RP.ISACTIVE='Y'))
    OR (COALESCE(R.INCLUDE_PRODUCT, 'Y')='Y' -- Everyone except selected

        AND NOT EXISTS
          (SELECT 1
           FROM M_RAPPEL_PRODUCT RP
           WHERE RP.M_PRODUCT_ID=IL.M_PRODUCT_ID
             AND RP.ISACTIVE='Y')))
AND RP.ISSOTRX=I.ISSOTRX
AND RP.M_RAPPEL_BPARTNER_ID=v_Record_ID;

Steps To Reproduce

Steps to reproduce this:

1- First of all, the rappels should be 'activated'. So, as System Administrator, go to the window 'Windows, Tabs, and fields' and search for the business partner window. Then search for the 'Volume Discount' tab and activate it. Then you will have to do a compile.complete.
2- Create a volume discount with the 'All excluding defined' on the 'Included product' and 'Included product category' fields.
3- Set the volume discount for a business partner 'x'
4- On the Scale, set a range of values, for example 1% discount for products between $1 and $1000.
5- Create a sales invoice and add a product.
6- On the business partner window, go to the volume discount tab and create a new one, selecting the volume discount created before.
7- Click on the 'Generate Invoice' button and set the date that is equals than the invoice created.
8- A new invoice will be generated, and it will be okay. But the problem gets when you create another volume discount, so:
9- Create a new volume discount for ANOTHER Business partner (let's call it 'y'), using the 'Only those defined' on the 'Included product' and 'Included product category' fields.
10- On the product category of this new volume discount use, for example, water product category.
11- Create a new invoice, for the first business partner ('x'), with a product that has the 'Water' product category.
12- Try the 'Create invoice' from the rappel again, and you can see that it will not find any invoice.

You can see an example attached.

Proposed Solution

Change the xml of PL M_RAPPEL_INVOICE_CREATE.xml as:

...
      AND((COALESCE(R.INCLUDE_PRODUCT_CATEGORY, 'Y')='N' -- Just selected
      AND EXISTS
      (SELECT 1
      FROM M_RAPPEL_PRODUCTCATEGORY RC,
        M_PRODUCT PROD
      WHERE PROD.M_PRODUCT_ID=IL.M_PRODUCT_ID
        AND RC.M_PRODUCT_CATEGORY_ID=PROD.M_PRODUCT_CATEGORY_ID
        AND RC.ISACTIVE='Y' AND R.M_RAPPEL_ID=RC.M_RAPPEL_ID
      ))
      OR (COALESCE(R.INCLUDE_PRODUCT_CATEGORY, 'Y')='Y' -- Everyone except selected
      AND NOT EXISTS
      (SELECT 1
      FROM M_RAPPEL_PRODUCTCATEGORY RC,
        M_PRODUCT PROD
      WHERE PROD.M_PRODUCT_ID=IL.M_PRODUCT_ID
        AND RC.M_PRODUCT_CATEGORY_ID=PROD.M_PRODUCT_CATEGORY_ID
        AND RC.ISACTIVE='Y' AND R.M_RAPPEL_ID=RC.M_RAPPEL_ID
      )))
      AND((COALESCE(R.INCLUDE_PRODUCT, 'Y')='N' -- Just selected
      AND EXISTS
      (SELECT 1 FROM M_RAPPEL_PRODUCT RP WHERE RP.M_PRODUCT_ID=IL.M_PRODUCT_ID AND RP.ISACTIVE='Y'
       AND R.M_RAPPEL_ID=RP.M_RAPPEL_ID))
      OR (COALESCE(R.INCLUDE_PRODUCT, 'Y')='Y' -- Everyone except selected
      AND NOT EXISTS
      (SELECT 1 FROM M_RAPPEL_PRODUCT RP WHERE RP.M_PRODUCT_ID=IL.M_PRODUCT_ID AND RP.ISACTIVE='Y'
       AND R.M_RAPPEL_ID=RP.M_RAPPEL_ID)))
      AND RP.ISSOTRX=I.ISSOTRX
      AND RP.M_RAPPEL_BPARTNER_ID=v_Record_ID;
...

To add the current rappel selected.

Issue History
Date Modified	Username	Field	Change
2019-04-13 00:32	matias-bernal	New Issue
2019-04-13 00:32	matias-bernal	Assigned To	=> Triage Finance
2019-04-13 00:32	matias-bernal	File Added: 9107.zip
2019-04-13 00:32	matias-bernal	Modules	=> Core
2019-04-13 00:32	matias-bernal	Triggers an Emergency Pack	=> No
2019-08-14 22:05	collazoandy4	Assigned To	Triage Finance => collazoandy4
2019-08-14 22:05	collazoandy4	Status	new => scheduled

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.