HI,
Database 12c.
Below are the script based on discount policy which is maintained in a table. Now there two way to define discount either it can be category level or item level. For example, beneath item wise discount has been entered which is first priority, if in case not found then system will see at category level.
Create table INV_ITEM_DISCOUNT
(
CATEGORY_ID NUMBER(3),
ITEM_ID NUMBER,
DIS_RATE NUMBER(11,4),
FROM_DATE DATE,
TO_DATE DATE,
ACTIVE VARCHAR2(1)
)
INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,GST_RATE,FROM_DATE,TO_DATE,ACTIVE)
VALUES (1,1,.10,SYSDATE,NULL,'A')
INSERT INTO INV_ITEM_DISCOUNT(CATEGORY_ID,ITEM_ID,GST_RATE,FROM_DATE,TO_DATE,ACTIVE)
VALUES (1,null,.10,SYSDATE,NULL,'A')