hi ,
db 10g release 2 .
- "ITEMS" table is the look-up table for items .
- "UNITS" table is the look-up table for units of measure .
- "item_detail" table is the stock table .
- "item_units" table holds the units of measure for a specific item .
- each item has a unit of measure or more .
- each unit has a conversion factor to the smallest unit . -- if an item has 2 units (box , and packet ) and the factor for unit (box) i.e = 30 , that means "1 box" = "30 packet" .
- the stock of an item exists as qty of his smallest unit of measure .
- "Serial" column in the item_detail table is a unique identifier for same items which have different expiry dates .
i have the following tables and data
CREATE TABLE UNITS
(
UNIT_CODE NUMBER,
UNIT_NAME VARCHAR2(90 BYTE),
PARENT_UNIT NUMBER
);
ALTER TABLE UNITS ADD (
CONSTRAINT UNIT_CODE_PK
PRIMARY KEY
(UNIT_CODE));
------------------------------------------
Insert into UNITS
(UNIT_CODE, UNIT_NAME, PARENT_UNIT)
Values
(1, 'Box', NULL);
Insert into UNITS
(UNIT_CODE, UNIT_NAME, PARENT_UNIT)
Values
(2, 'Packet', NULL);
Insert into UNITS
(UNIT_CODE, UNIT_NAME, PARENT_UNIT)
Values
(3, 'Strips', NULL);
Insert into UNITS
(UNIT_CODE, UNIT_NAME, PARENT_UNIT)
Values
(4, 'Bills', NULL);
COMMIT;
------------------------------------------
--------------------------------------------------------
-- DDL for Table ITEMS
--------------------------------------------------------
CREATE TABLE "ITEMS" ("ITEM_ID" NUMBER, "ITEM_ENG_NAME" VARCHAR2(150)) ;
Insert into ITEMS (ITEM_ID,ITEM_ENG_NAME) values (4,'Juice');
Insert into ITEMS (ITEM_ID,ITEM_ENG_NAME) values (1,'Pepsi');
Insert into ITEMS (ITEM_ID,ITEM_ENG_NAME) values (2,'Rice');
Insert into ITEMS (ITEM_ID,ITEM_ENG_NAME) values (3,'CocaCola');
--------------------------------------------------------
-- Constraints for Table ITEMS
--------------------------------------------------------
ALTER TABLE "ITEMS" ADD CONSTRAINT "PRODUCTS_PK" PRIMARY KEY ("ITEM_ID") ENABLE;
--------------------------------------------------------
CREATE TABLE ITEM_UNITS
(
ITEM_ID NUMBER,
UNIT_ID NUMBER,
FACTOR NUMBER
);
ALTER TABLE ITEM_UNITS ADD (
CONSTRAINT ITEMS_UNITS_PK
PRIMARY KEY
(ITEM_ID, UNIT_ID));
ALTER TABLE ITEM_UNITS ADD (
CONSTRAINT ITEMUNITS_ITEMID_FK
FOREIGN KEY (ITEM_ID)
REFERENCES ITEMS (ITEM_ID)
ON DELETE CASCADE);
ALTER TABLE ITEM_UNITS ADD (
CONSTRAINT ITEMUNITS_UNITID_FK
FOREIGN KEY (UNIT_ID)
REFERENCES UNITS (UNIT_CODE));
-----------------------------------------------------------
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(1, 4, 20);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(1, 1, 1);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(4, 4, 20);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(4, 1, 1);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(3, 2, 1);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(3, 1, 30);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(2, 1, 30);
Insert into ITEM_UNITS
(ITEM_ID, UNIT_ID, FACTOR)
Values
(2, 2,1);
COMMIT;
-----------------------------------
CREATE TABLE ITEM_DETAIL
(
SERIAL NUMBER,
ITEM_ID NUMBER,
EXPIRY_DATE DATE,
QTY NUMBER NOT NULL
);
COMMENT ON COLUMN ITEM_DETAIL.QTY IS 'QTY_ON_HAND';
ALTER TABLE ITEM_DETAIL ADD (
CONSTRAINT ITEMDTL_QTY_GREATER_ZERO
CHECK (QTY >= 0));
ALTER TABLE ITEM_DETAIL ADD (
CONSTRAINT ITEMDETAIL_SERIAL_PK
PRIMARY KEY
(SERIAL));
-----------------------------------
Insert into ITEM_DETAIL
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY)
Values
(1141, 3, TO_DATE('10/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into ITEM_DETAIL
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY)
Values
(1142, 4, TO_DATE('10/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);
Insert into ITEM_DETAIL
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY)
Values
(1126, 2, TO_DATE('10/10/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into ITEM_DETAIL
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY)
Values
(11, 1, TO_DATE('10/10/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into ITEM_DETAIL
(SERIAL, ITEM_ID, EXPIRY_DATE, QTY)
Values
(1083, 1, TO_DATE('10/10/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 95);
COMMIT;
- i want to retrieve the quantity each item has ( separated in units ) ,
i.e , item 1's is mentioned in 2 records with a total qty of 195 ( of the smallest unit of measure ) , and he has 2 units of measures (Box , Bills ) which their factors are ( 20 and 1) respectively,
so i should get a message tells the user that item 1's qty is " 9 Box , 15 Bills " . that's because (195/20=9 and the mod=15 ) , of course the item could have more than 2 units or only 1 unit .
expected results :-
| ITEM_ID | ITEM_ENG_NAME | QTY |
|---|
| 1 | Pepsi | 9 Box , 15 Bills |
| 2 | Rice | 0 Box , 22 Packet |
| 3 | CocaCola | 0 Box , 5 Packet |
| 4 | Juice | 0 Box , 5 Bills |
- i have the following view which do what i want but with each serial not item_id , it does not group item_id's
CREATE OR REPLACE FORCE VIEW "POS"."ITEMS_QTY_VU" ("SERIAL", "ITEM_ID", "EXPIRY_DATE", "ITEM_ENG_NAME", "SALE_UNIT", "BONUS", "DISCOUNT", "UNIT_NAME", "QTY")
AS
WITH item_units_plus AS
(SELECT item_id,
unit_id,
factor,
LEAD (factor, 1, 1E99) OVER ( PARTITION BY item_id ORDER BY factor ) AS next_factor,
ROW_NUMBER () OVER ( PARTITION BY item_id ORDER BY factor DESC ) AS rnk
FROM item_units
)
SELECT id.serial,
id.item_id,
id.expiry_date,
i.item_eng_name,
i.sale_unit,
i.bonus,
i.discount,
su.unit_name,
SUBSTR ( SYS_CONNECT_BY_PATH ( TRUNC ( MOD ( id.qty, iup.next_factor ) / iup.factor )
|| ' '
|| u.unit_name, ', ' ), 3 ) AS qty
FROM item_detail id
JOIN items i
ON i.item_id = id.item_id
AND id.qty <> 0
JOIN item_units_plus iup
ON iup.item_id = id.item_id
JOIN units u
ON u.unit_code = iup.unit_id
LEFT JOIN units sua
ON su.unit_code = i.sale_unit -- ***** CHANGED *****
WHERE CONNECT_BY_ISLEAF = 1
AND id.expiry_date > TRUNC (SYSDATE)
START WITH iup.rnk = 1
CONNECT BY iup.rnk = PRIOR iup.rnk + 1
AND id.serial = PRIOR id.serial;
- i have tried to add "Sum" to this line
SUBSTR ( SYS_CONNECT_BY_PATH ( TRUNC ( MOD ( sum(id.qty), iup.next_factor ) / iup.factor )
and add a " Group by " clause at the end of the code ,
the view was created successfully , but the "QTY " column did not get the data , it was blank .