oracle_script.sql

CREATE SEQUENCE "SALES_ORDER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 161 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "PURCHASES_ORDER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 61 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "SALE_RETURNS_ORDER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "PURCHASE_RETURNS_ORDER_ID" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "POS_SEQUENCE" MINVALUE 1000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1466 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "POS_DETAILS_SEQUENCE" MINVALUE 1000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1020 CACHE 20 NOORDER NOCYCLE
CREATE SEQUENCE "SEQ_PURCHASE" MINVALUE 1000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1000 CACHE 20 NOORDER NOCYCLE

CREATE TABLE "CUSTOMERS" ("CUSTOMER_ID" NUMBER, "NAME" VARCHAR2(100), "MODIFIED" TIMESTAMP (6), "CREATED" TIMESTAMP (6))  ;

CREATE TABLE "ITEMS" ("ITEM_ID" NUMBER, "CODE" VARCHAR2(60), "NAME" VARCHAR2(100), "PACK_UNIT_ID" NUMBER, "PACK_SIZE" NUMBER(8, 0), "STANDARD_UNIT_ID" NUMBER, "CREATED" TIMESTAMP (6), "MODIFIED" TIMESTAMP (6));

CREATE TABLE "ITEM_PRICES" ("ITEM_PRICE_ID" NUMBER, "ITEM_ID" NUMBER, "SALE_PRICE" NUMBER, "EFFECTIVE_FROM" DATE, "EFFECTIVE_TO" DATE, "STATUS" NUMBER(1, 0));


CREATE TABLE "PURCHASES" ("ORDER_ID" NUMBER, "INVOICE_DATE" DATE, "PARTY_ID" NUMBER, "PARTY_NAME" VARCHAR2(120), "PARTY_CONTACT" VARCHAR2(40), "TOTAL_QUANTITY" NUMBER, "TOTAL_AMOUNT" NUMBER, "OTHER_AMOUNT" NUMBER, "TOTAL_PAYBLE_AMOUNT" NUMBER, "TOTAL_PAID_AMOUNT" NUMBER, "TOTAL_DUE_AMOUNT" NUMBER, "REMARKS" VARCHAR2(1200), "CREATED_ON" DATE);

CREATE TABLE "PURCHASE_DETAILS" ("ORDER_ID" NUMBER, "ITEM_ID" NUMBER, "ITEM_NAME" VARCHAR2(160), "LOCATION" VARCHAR2(40), "UOM" VARCHAR2(20), "QUANTITY" NUMBER, "PRICE" NUMBER, "AMOUNT" NUMBER) ;

CREATE TABLE "PURCHASE_RETURNS" ("ORDER_ID" NUMBER, "INVOICE_DATE" DATE, "PARTY_ID" NUMBER, "PARTY_NAME" VARCHAR2(120), "PARTY_CONTACT" VARCHAR2(40), "TOTAL_QUANTITY" NUMBER, "TOTAL_AMOUNT" NUMBER, "OTHER_AMOUNT" NUMBER, "TOTAL_PAYBLE_AMOUNT" NUMBER, "TOTAL_PAID_AMOUNT" NUMBER, "TOTAL_DUE_AMOUNT" NUMBER, "REMARKS" VARCHAR2(1200), "CREATED_ON" DATE);

CREATE TABLE "PURCHASE_RETURN_DETAILS" ("ORDER_ID" NUMBER, "ITEM_ID" NUMBER, "ITEM_NAME" VARCHAR2(160), "LOCATION" VARCHAR2(40), "UOM" VARCHAR2(20), "QUANTITY" NUMBER, "PRICE" NUMBER, "AMOUNT" NUMBER);

CREATE TABLE "SALES" ("ORDER_ID" NUMBER, "INVOICE_DATE" DATE, "PARTY_ID" NUMBER, "PARTY_NAME" VARCHAR2(120), "PARTY_CONTACT" VARCHAR2(40), "TOTAL_QUANTITY" NUMBER, "TOTAL_AMOUNT" NUMBER, "OTHER_AMOUNT" NUMBER, "TOTAL_PAYBLE_AMOUNT" NUMBER, "TOTAL_PAID_AMOUNT" NUMBER, "TOTAL_DUE_AMOUNT" NUMBER, "REMARKS" VARCHAR2(1200), "CREATED_ON" DATE, "CURRENCY" VARCHAR2(10), "TAUX" NUMBER(15, 2));

CREATE TABLE "SALE_DETAILS" ("ORDER_ID" NUMBER, "ITEM_ID" NUMBER, "ITEM_NAME" VARCHAR2(160), "LOCATION" VARCHAR2(40), "UOM" VARCHAR2(20), "QUANTITY" NUMBER, "PRICE" NUMBER, "AMOUNT" NUMBER) ;

CREATE TABLE "SALE_RETURNS" ("ORDER_ID" NUMBER, "INVOICE_DATE" DATE, "PARTY_ID" NUMBER, "PARTY_NAME" VARCHAR2(120), "PARTY_CONTACT" VARCHAR2(40), "TOTAL_QUANTITY" NUMBER, "TOTAL_AMOUNT" NUMBER, "OTHER_AMOUNT" NUMBER, "TOTAL_PAYBLE_AMOUNT" NUMBER, "TOTAL_PAID_AMOUNT" NUMBER, "TOTAL_DUE_AMOUNT" NUMBER, "REMARKS" VARCHAR2(1200), "CREATED_ON" DATE);

CREATE TABLE "SALE_RETURN_DETAILS" ("ORDER_ID" NUMBER, "ITEM_ID" NUMBER, "ITEM_NAME" VARCHAR2(160), "LOCATION" VARCHAR2(40), "UOM" VARCHAR2(20), "QUANTITY" NUMBER, "PRICE" NUMBER, "AMOUNT" NUMBER) ;

CREATE TABLE "UOMS" ("UOM_ID" NUMBER, "NAME" VARCHAR2(60), "CREATED" TIMESTAMP (6), "MODIFIED" TIMESTAMP (6));


SELECT * FROM POS.PURCHASES p ORDER BY ORDER_ID DESC 
SELECT * FROM POS.PURCHASE_DETAILS pd ORDER BY ORDER_ID DESC 



CREATE TABLE "USERS"
(
  FIRST_NAME  VARCHAR2(25 BYTE)                 NOT NULL,
  LAST_NAME   VARCHAR2(25 BYTE)                 NOT NULL,
  EMAIL       VARCHAR2(45 BYTE)                 NOT NULL,
  USER_NAME   VARCHAR2(25 BYTE)                 NOT NULL,
  PASSWORD    VARCHAR2(64 BYTE)                 NOT NULL
)

ALTER USER POS IDENTIFIED BY POS

INSERT INTO USERS (FIRST_NAME, LAST_NAME, EMAIL, USER_NAME, PASSWORD)
VALUES ('John', 'Doe', 'john.doe@example.com', 'john', '123');
COMMIT;

INSERT INTO SALES (ORDER_ID, INVOICE_DATE, PARTY_ID, PARTY_NAME, PARTY_CONTACT, TOTAL_QUANTITY, TOTAL_AMOUNT, OTHER_AMOUNT, TOTAL_PAYBLE_AMOUNT, TOTAL_PAID_AMOUNT, TOTAL_DUE_AMOUNT, REMARKS, CREATED_ON, CURRENCY, TAUX)
VALUES (1, TO_DATE('2024-03-22', 'YYYY-MM-DD'), 1001, 'ABC Company', '123-456-7890', 10, 1000, 50, 1050, 500, 550, 'First sale of the month', SYSDATE, 'USD', 1.25);
COMMIT

SELECT * FROM POS.USERS u  


-- Inserting data into SALE_DETAILS table
INSERT INTO SALE_DETAILS (ORDER_ID, ITEM_ID, ITEM_NAME, LOCATION, UOM, QUANTITY, PRICE, AMOUNT)
VALUES (1, 101, 'Product A', 'Warehouse A', 'Piece', 10, 50, 500);
COMMIT


INSERT INTO SALE_RETURNS (ORDER_ID, INVOICE_DATE, PARTY_ID, PARTY_NAME, PARTY_CONTACT, TOTAL_QUANTITY, TOTAL_AMOUNT, OTHER_AMOUNT, TOTAL_PAYBLE_AMOUNT, TOTAL_PAID_AMOUNT, TOTAL_DUE_AMOUNT, REMARKS, CREATED_ON)
VALUES (1, TO_DATE('2024-03-22', 'YYYY-MM-DD'), 1001, 'ABC Company', '123-456-7890', 5, 250, 10, 260, 260, 0, 'Return of damaged items', SYSDATE)

-- Inserting data into SALE_RETURN_DETAILS table
INSERT INTO SALE_RETURN_DETAILS (ORDER_ID, ITEM_ID, ITEM_NAME, LOCATION, UOM, QUANTITY, PRICE, AMOUNT)
VALUES (1, 101, 'Product A', 'Warehouse A', 'Piece', 5, 50, 250);

-- Inserting data into UOMS table
INSERT INTO UOMS (UOM_ID, NAME, CREATED, MODIFIED)
VALUES (1, 'Piece', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Inserting data into CUSTOMERS table
INSERT INTO CUSTOMERS (CUSTOMER_ID, NAME, MODIFIED, CREATED)
VALUES (1001, 'ABC Company', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- Inserting data into ITEMS table
INSERT INTO ITEMS (ITEM_ID, CODE, NAME, PACK_UNIT_ID, PACK_SIZE, STANDARD_UNIT_ID, CREATED, MODIFIED)
VALUES (101, 'P101', 'Product A', 1, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
COMMIT
-- Inserting data into ITEM_PRICES table
INSERT INTO ITEM_PRICES (ITEM_PRICE_ID, ITEM_ID, SALE_PRICE, EFFECTIVE_FROM, EFFECTIVE_TO, STATUS)
VALUES (1, 101, 50, TO_DATE('2024-01-01', 'YYYY-MM-DD'), TO_DATE('2024-12-31', 'YYYY-MM-DD'), 1);
COMMIT
-- Inserting data into PURCHASES table
INSERT INTO PURCHASES (ORDER_ID, INVOICE_DATE, PARTY_ID, PARTY_NAME, PARTY_CONTACT, TOTAL_QUANTITY, TOTAL_AMOUNT, OTHER_AMOUNT, TOTAL_PAYBLE_AMOUNT, TOTAL_PAID_AMOUNT, TOTAL_DUE_AMOUNT, REMARKS, CREATED_ON)
VALUES (1, TO_DATE('2024-03-20', 'YYYY-MM-DD'), 2001, 'XYZ Supplier', '987-654-3210', 20, 2000, 100, 2100, 2000, 100, 'Initial purchase', SYSDATE);
COMMIT
-- Inserting data into PURCHASE_DETAILS table
INSERT INTO PURCHASE_DETAILS (ORDER_ID, ITEM_ID, ITEM_NAME, LOCATION, UOM, QUANTITY, PRICE, AMOUNT)
VALUES (1, 201, 'Raw Material X', 'Warehouse B', 'Kg', 50, 30, 1500);
COMMIT
-- Inserting data into PURCHASE_RETURNS table
INSERT INTO PURCHASE_RETURNS (ORDER_ID, INVOICE_DATE, PARTY_ID, PARTY_NAME, PARTY_CONTACT, TOTAL_QUANTITY, TOTAL_AMOUNT, OTHER_AMOUNT, TOTAL_PAYBLE_AMOUNT, TOTAL_PAID_AMOUNT, TOTAL_DUE_AMOUNT, REMARKS, CREATED_ON)
VALUES (1, TO_DATE('2024-03-25', 'YYYY-MM-DD'), 2001, 'XYZ Supplier', '987-654-3210', 5, 500, 0, 500, 0, 500, 'Return due to quality issues', SYSDATE);
COMMIT
-- Inserting data into PURCHASE_RETURN_DETAILS table
INSERT INTO PURCHASE_RETURN_DETAILS (ORDER_ID, ITEM_ID, ITEM_NAME, LOCATION, UOM, QUANTITY, PRICE, AMOUNT)
VALUES (1, 201, 'Raw Material X', 'Warehouse B', 'Kg', 5, 30, 150);
COMMIT

SELECT NAME FROM V$SERVICES

UPDATE USERS SET user_name = 'johndoe', password = 'john@123'
COMMIT 

SELECT * FROM PURCHASE_DETAILS pd 
SELECT * FROM ITEM_PRICES ip 


INSERT INTO UOMS (UOM_ID, NAME, CREATED, MODIFIED) 
VALUES 
(1, 'Meter', TO_TIMESTAMP('2024-03-23 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), TO_TIMESTAMP('2024-03-23 12:00:00', 'YYYY-MM-DD HH24:MI:SS'));

ALTER TABLE ITEMS ADD qoh NUMBER



CREATE OR REPLACE TRIGGER update_qty
AFTER INSERT OR UPDATE OF quantity ON purchase_details 
FOR EACH ROW
BEGIN
    UPDATE ITEMS 
    SET qoh = NVL(qoh,0) + :NEW.quantity
    WHERE item_id = :NEW.item_id;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
 END;

SELECT * FROM ITEMS i 

UPDATE ITEMS  SET qoh = 50 WHERE ITEM_ID  = 101;

COMMIT;


CREATE OR REPLACE TRIGGER reduce_qty
AFTER INSERT OR UPDATE OF quantity ON sale_details 
FOR EACH ROW
BEGIN
    UPDATE ITEMS  
    SET qoh = NVL(qoh,0) - :NEW.quantity
    WHERE item_id = :NEW.item_id;
EXCEPTION
    WHEN OTHERS THEN
        NULL;
 END;
SELECT * FROM pos.ITEM_PRICES ip 
SELECT * FROM pos.ITEMS i 



UPDATE pos.ITEMS i SET qoh = 100 WHERE item_id = 101;

INSERT INTO POS.ITEMS (item_id, code, name, pack_unit_id, pack_size, standard_unit_id, created, modified) 
select 91, 'P102', 'shampoo', 1, 1, 1, to_date('2024-03-27','yyyy-mm-dd'), to_date('2024-03-27','yyyy-mm-dd') FROM dual
union all 
select 91, 'P103', 'biscuit', 1, 1, 1, to_date('2024-03-27','yyyy-mm-dd'), to_date('2024-03-27','yyyy-mm-dd')
FROM dual
ROLLBACK

INSERT INTO POS.ITEMS  (item_id, code, name, pack_unit_id, pack_size, standard_unit_id, created, modified) 
select 93, 'P102', 'a1', 1, 1, 1, to_date('2024-03-27','yyyy-mm-dd'), to_date('2024-03-27','yyyy-mm-dd') 
from dual  
union all 
select 93, 'P103', 'bcd', 1, 1, 1, to_date('2024-03-27','yyyy-mm-dd'), to_date('2024-03-27','yyyy-mm-dd') 
from dual 

COMMIT

insert into pos.item_prices (item_price_id,item_id,sale_price,EFFECTIVE_FROM,EFFECTIVE_TO,status) 
select 96, 'P104', '1.0', 'to_date('2024-03-27','yyyy-mm-dd'), to_date('2024-03-27','yyyy-mm-dd'),1
from dual

SELECT * FROM POS.ITEMS i 

select a.item_id, a.name item_name, b.name as pack_unit, a.pack_size, c.name as standard_unit,p.sale_price 
from pos.items a, pos.uoms b, pos.uoms c,
    (select item_id, sale_price 
from pos.item_prices
--    where date'2024-03-27' between sysdate and sysdate
) p
      where a.name='abc' and a.pack_unit_id = b.UOM_ID and qoh>0 and a.standard_unit_id = c.uom_id
    and a.item_id = p.item_id
      order by 2

select TO_CHAR(A.INVOICE_DATE,'dd') day, sum(b.price)
 from pos.SALES A, pos.SALE_DETAILs B WHERE B.ORDER_ID = A.ORDER_ID
group by TO_CHAR(A.INVOICE_DATE,'dd')
order by 1


select TO_CHAR(A.INVOICE_DATE,'dd') day, sum(b.amount)
 from pos.SALE_RETURns A, pos.SALE_RETURN_DETAILS B WHERE B.ORDER_ID = A.ORDER_ID
group by TO_CHAR(A.INVOICE_DATE,'dd')
order by 1

select * from pos.SALE_RETURN_DETAILS
select * from pos.SALE_RETURns

select * from pos.items;

update pos.item_prices set effective_to = sysdate+200 where item_price_id = 99;

commit
rollback

select * from pos.uoms;
update pos.uoms set name = 'Nos.'

commit