Table No.1
CREATE TABLE GRN_MASTER
(
GRN_NUM NUMBER,
GRN_DATE DATE,
SUPPLIER_ID NUMBER,
GATE_PASSS_ID NUMBER,
STORE_ID NUMBER,
APPROVED_ID NUMBER,
CONSTRAINTS PK_GRN_NUM PRIMARY KEY (GRN_NUM)
);
Table No.2
CREATE TABLE GRN_DETAIL
(
GRN_NUM NUMBER,
SR_NUM NUMBER,
ITEM_CODE NUMBER,
ITEM_UOM NUMBER,
ITEM_RATE NUMBER,
ITEM_VALUE NUMBER,
CONSTRAINT FK_GRN_NUM_DTL FOREIGN KEY (GRN_NUM) REFERENCES GRN_MASTER(GRN_NUM)
);
Now Creating View:
-CREATE OR REPLACE VIEW GRN_MASTER_DETAIL_VW
(
GRN_NUM ,
GRN_DATE ,
SUPPLIER_ID ,
GATE_PASSS_ID ,
STORE_ID ,
APPROVED_ID ,
SR_NUM ,
ITEM_CODE ,
ITEM_UOM ,
ITEM_RATE ,
ITEM_VALUE
)
AS SELECT
GM.GRN_NUM ,
GM.GRN_DATE ,
GM.SUPPLIER_ID ,
GM.GATE_PASSS_ID ,
GM.STORE_ID ,
GM.APPROVED_ID ,
GD.SR_NUM ,
GD.ITEM_CODE ,
GD.ITEM_UOM ,
GD.ITEM_RATE ,
GD.ITEM_VALUE
FROM GRN_MASTER GM, GRN_DETAIL GD
WHERE GM.GRN_NUM = GD.GRN_NUM;
No comments:
Post a Comment
Thank You For Comment