jump to navigation

Sample Stock Report August 16, 2007

Posted by Muhammad Habib in Oracle Developer, Oracle General.
trackback

—————————————————————
SCRIPT TO CREATE TABLES (OPN, RCT, ISU)
—————————————————————
create table OPN
(
Q_ID NUMBER,
OPN_QTY NUMBER,
OPN_DT DATE
);

create table RCT
(
Q_ID NUMBER,
RCT_QTY NUMBER,
RCT_DT DATE
);

create table ISU
(
Q_ID NUMBER,
ISU_QTY NUMBER,
ISU_DT DATE
);
—————————————————————
SCRIPT TO CREATE VIEW STK_V
—————————————————————
CREATE OR REPLACE VIEW STK_V AS
(
SELECT Q_ID,
SUM(OPN_QTY) AS OPENING,
0 AS RECEIPT,
0 AS ISSUE,
OPN_DT AS T_DT
FROM OPN
GROUP BY Q_ID, OPN_DT
UNION
SELECT Q_ID,
0 AS OPENING,
SUM(RCT_QTY) AS RECEIPT,
0 AS ISSUE,
RCT_DT AS T_DT
FROM RCT
GROUP BY Q_ID, RCT_DT
UNION
SELECT Q_ID,
0 AS OPENING,
0 AS RECEIPT,
SUM(ISU_QTY) AS ISSUE,
ISU_DT AS T_DT
FROM ISU
GROUP BY Q_ID, ISU_DT
);

—————————————————————
REPORT QUERY
————————————————————— SELECT Q_ID,
SUM(OPENING) AS OPENING,
SUM(RECEIPT) AS RECEIPT,
SUM(ISSUE) AS ISSUE,
MAX(T_DT) as T_DT
FROM
(
SELECT Q_ID,
SUM(OPENING) AS OPENING,
SUM(RECEIPT) AS RECEIPT,
SUM(ISSUE) AS ISSUE,
T_DT
FROM STK_V
WHERE T_DT BETWEEN :FROM_DT AND :TO_DT
AND Q_ID = :QLTY_ID
GROUP BY Q_ID, T_DT
UNION ALL
SELECT Q_ID,
((SUM(OPENING)+SUM(RECEIPT))-SUM(ISSUANCE)) AS OPENING,
0 AS RECEIPT,
0 AS ISSUE,
T_DT
FROM STK_V
WHERE T_DT < :FROM_DT
AND Q_ID = :QLTY_ID
GROUP BY Q_ID, T_DT
)
GROUP BY Q_ID, T_DT
ORDER BY T_DT
—————————————————————
FORMULA COLUMN IN REPORT TO GET BALANCE
—————————————————————

function BALANCE return Number is
begin
RETURN :OPENING + :RECEIPT – :ISSUE;
end;
————————————————————————
MAKE A SUMMARY COLUMN ON BALANCE FORMULA AT SAME LEVEL
————————————————————————

Report columns are: Opening, Receipt, Issuance, Balance

Hope it will be helpful.

Advertisements

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: