Calculation Example :
Production Opening =
(Fixed Opening/Previous Closing + Total Production) - Total Delivery
Current Closing/Next Month Opening =
(Fixed Opening/Previous Closing + Total Production) - Total Delivery
Production Pending =
(Total Production + Opening ) - Total Target
STEP : 1
======
Create product-wise first Opening Balance Entry table. this is one time entry table only developers not only any user.
CREATE TABLE PR_DAILY_PRODUCTION_OPENING
(
ID NUMBER(20),
OP_DATE DATE,
OP_ITEMCD VARCHAR2(16 BYTE),
OP_QTY NUMBER(20),
ENTRY_DATE DATE DEFAULT SYSDATE,
ENTRY_BY VARCHAR2(30 BYTE)
)
*** All data entry 31-dec-23 That means my opening Balance start 01-jan-24
ID OP_DATE OP_ITEMCD OP_QTY ENTRY_DATE
== ========== ========== ====== ==========
1 12/31/2023 F101V101BOX 1345 3/25/2024 10:38:26 AM
2 12/31/2023 F101R101BOX 57 3/25/2024 10:38:26 AM
3 12/31/2023 F101WS01BOX 1 3/25/2024 10:38:26 AM
4 12/31/2023 F101B201BOX 12 3/25/2024 10:38:26 AM
5 12/31/2023 F101B130BOX 0 3/25/2024 10:38:26 AM
6 12/31/2023 F101BL250ML 2 3/25/2024 10:38:26 AM
7 12/31/2023 F101BL750ML 18 3/25/2024 10:38:26 AM
8 12/31/2023 F101BL450ML 2111 3/25/2024 10:38:26 AM
9 12/31/2023 F101WH500GM -7 3/25/2024 10:38:26 AM
10 12/31/2023 F101WH30KG 0 3/25/2024 10:38:26 AM
STEP 2 :
======
Create a Daily Production Entry Table.
CREATE TABLE PR_DAILY_PRODUCTION
(
ID NUMBER,
PRODUCTION_DATE DATE,
ITEM_CD VARCHAR2(20 BYTE),
QTY NUMBER,
ENTRY_DATE DATE DEFAULT SYSDATE,
ENTRY_BY VARCHAR2(30 BYTE),
UPDATE_DATE DATE,
UPDATE_BY VARCHAR2(30 BYTE)
)
*** All data entry Start Date 01-jan-24.
ID PRODUCTION_DATE ITEM_CD QTY ENTRY_DATE ENTRY_BY
== ========== ========== ====== ========== ===========
1 1/2/2024 F101V101BOX 1085 4/2/2024 12:15:18 PM 00136
2 1/2/2024 F101B201BOX 1064 4/2/2024 12:15:18 PM 00136
3 1/3/2024 F101V101BOX 1015 4/2/2024 12:16:31 PM 00136
4 1/3/2024 F101B201BOX 1120 4/2/2024 12:16:31 PM 00136
5 1/4/2024 F101V101BOX 945 4/2/2024 12:17:36 PM 00136
6 1/4/2024 F101B201BOX 1064 4/2/2024 12:17:36 PM 00136
7 1/5/2024 F101V101BOX 805 4/2/2024 12:18:19 PM 00136
8 1/5/2024 F101B201BOX 952 4/2/2024 12:18:19 PM 00136
9 1/6/2024 F101V101BOX 35 4/2/2024 12:18:44 PM 00136
10 1/6/2024 F101B201BOX 350 4/2/2024 12:18:44 PM 00136
STEP 3 :
=====
Create a Daily Delivery entry Table.
CREATE TABLE PR_DAILY_DELIVERY
(
ID NUMBER,
DELIVERY_DATE DATE,
ITEM_CD VARCHAR2(20 BYTE),
QTY NUMBER,
ENTRY_DATE DATE DEFAULT SYSDATE,
ENTRY_BY VARCHAR2(30 BYTE),
UPDATE_DATE DATE,
UPDATE_BY VARCHAR2(30 BYTE)
)
*** All data entry Start Date 01-jan-24.
ID DELIVERY_DATE ITEM_CD QTY ENTRY_DATE ENTRY_BY
== ============= ========== ====== ========== ===========
1 1/2/2024 F101V101BOX 1000 4/2/202412:15:18PM 00136
2 1/2/2024 F101B201BOX 500 4/2/2024 12:15:18 PM 00136
3 1/3/2024 F101V101BOX 300 4/2/2024 12:16:31 PM 00136
STEP 4 :
=====
Create function for Production Opening Show.
CREATE OR REPLACE FUNCTION GET_PRODUCTION_OPENING_BAL ( P_SLCODE IN VARCHAR2, P_MDATE IN DATE)
RETURN NUMBER IS
V_QTY NUMBER;
BEGIN
SELECT SUM(NVL(QTY,0)) OPENING
INTO V_QTY
FROM
(
------- DAILY_PRODUCTION
select TO_CHAR(PRODUCTION_DATE,'DD-MON-RR') MDATE, 'Production' ptype, A.ITEM_CD , FUNC_ITEM_NAME (A.ITEM_CD) ITEM_NAME, NVL(A.QTY,0) QTY
from PR_DAILY_PRODUCTION a
where PRODUCTION_DATE < P_MDATE
AND A.ITEM_CD = P_SLCODE ---'F101V101BOX'
AND PRODUCTION_DATE >'01-JAN-24'
UNION ALL ------Daily Delivery query.
select TO_CHAR(INV_ISD_MDATE,'DD-MON-RR') MDATE, 'Delivery' ptype, A.INV_ISD_ITEMCODE ITEM_CD , FUNC_ITEM_NAME (A.INV_ISD_ITEMCODE) ITEM_NAME,
-sum((NVL(A.INV_ISD_QTY,0) + NVL(A.INV_ISSUE_QTY_B,0) )/ NVL(b.CTN_SIZE,0) ) QTY
from PR_DAILY_DELIVERY a, item b
where a.INV_ISD_ITEMCODE=b.item_cd
AND INV_ISD_MDATE < P_MDATE
AND B.ITM_SUBGRCD = 'F101'
AND B.STATUS = 'Y'
AND INV_ISD_MDATE >= '01-JAN-24'
and A.INV_ISD_ITEMCODE= P_SLCODE ---'F101V101BOX'
group by INV_ISD_MDATE, A.INV_ISD_ITEMCODE , FUNC_ITEM_NAME (A.INV_ISD_ITEMCODE)
UNION ALL ----- PRODUCTION_OPENING
SELECT TO_CHAR(OP_DATE,'DD-MON-RR') MDATE,'OPENING' ptype, OP_ITEMCD ITEM_CD ,FUNC_ITEM_NAME (OP_ITEMCD) ITEM_NAME,
SUM(NVL(OP_QTY,0)) OP
FROM PR_DAILY_PRODUCTION_OPENING
WHERE OP_ITEMCD= P_SLCODE
AND OP_DATE< P_MDATE
AND OP_DATE>= '31-DEC-23'
GROUP BY OP_DATE,OP_ITEMCD
);
RETURN V_QTY;
END;
/
STEP 5 :
=====
SQL Query :
select SL, PDATE, MDATE, ptype,
sum(Mega_Coil_Qty) Mega_Coil, SUM(Red_Coil_Qty) Red_Coil, SUM(Magic_Coil_Qty) Magic_Coil
--- TOTAL
sum(Mega_Coil_Qty) + SUM(Red_Coil_Qty) + SUM(Magic_Coil_Qty) TOTAL
from
(
--------------------------------------------------------------------------------------
---- PRODUCTION_OPENING
--------------------------------------------------------------------------------------
SELECT '1' SL, NULL PDATE, 'Production Opening' MDATE , 'Production Opening' ptype,
SUM (Mega_Coil_Qty) Mega_Coil_Qty,
SUM (Red_Coil_Qty) Red_Coil_Qty,
SUM (Magic_Coil_Qty) Magic_Coil_Qty
FROM (
---Mega_Coil_Qty
SELECT DISTINCT
GET_PRODUCTION_OPENING_BAL ('F101V101BOX', :P_FROM_DATE) Mega_Coil_Qty,
0 Red_Coil_Qty,
0 Magic_Coil_Qty
FROM DUAL
UNION ALL ---- Red_Coil_Qty
.
.
.
.
FROM DUAL)
------------------------------------------------------------------------------------------ Daily Production
--------------------------------------------------------------------------------------
UNION ALL
SELECT '2' SL, TO_CHAR(PRODUCTION_DATE,'DD-MON-RR') PDATE, TO_CHAR(PRODUCTION_DATE,'DD-MON-RR') MDATE, 'Production' ptype,
Mega_Coil_Qty, Red_Coil_Qty, Magic_Coil_Qty
from ALL_ITEM_PRODUCTION_QTY_VIEW
where PRODUCTION_DATE between :P_FROM_DATE and :P_TO_DATE
-------------------------------------------------------------------------------------
---- Total Production
--------------------------------------------------------------------------------------
UNION ALL
SELECT '3' SL, NULL PDATE, 'Total MTD Production' MDATE , 'Total Production' ptype,
SUM(MEGA_COIL_QTY) Mega_Coil_Qty, SUM(RED_COIL_QTY) Red_Coil_Qty, SUM(MAGIC_COIL_QTY) Magic_Coil_Qty
FROM ALL_ITEM_PRODUCTION_QTY_VIEW
where PRODUCTION_DATE between :P_FROM_DATE and :P_TO_DATE
GROUP BY TO_CHAR(PRODUCTION_DATE,'MONTH-RR')
-------------------------------------------------------------------------------------
----- Closing Balance = (Fixed Opening/Previous Closing + Total Production) - Total Delivery
-------------------------------------------------------------------------------------
UNION ALL
SELECT '4' SL, NULL PDATE, 'Closing Balance' MDATE , 'Closing Balance' ptype, NULL ITEM_CD , NULL ITEM_NAME,
NVL(PP.Mega_Coil_Qty,0) - NVL(TD.Mega_Coil_Qty,0) Mega_Coil_Qty,
NVL(PP.Red_Coil_Qty,0) - NVL(TD.Red_Coil_Qty,0) Red_Coil_Qty,
NVL(PP.Magic_Coil_Qty,0) - NVL(TD.Magic_Coil_Qty,0) Red_Coil_Qty
FROM
(
SELECT --- (PRODUCTION + PRODUCTION OPENING)
NVL(PR.Mega_Coil_Qty,0) + NVL(PO.Mega_Coil_Qty,0) Mega_Coil_Qty,
NVL(PR.Red_Coil_Qty,0) + NVL(PO.Red_Coil_Qty,0) Red_Coil_Qty,
NVL(PR.Magic_Coil_Qty,0) + NVL(PO.Magic_Coil_Qty,0) Magic_Coil_Qty
FROM
(
---- PRODUCTION
SELECT ---TO_CHAR(PRODUCTION_DATE,'MONTH-RR') MDATE , 'Total Production' ptype,
SUM(MEGA_COIL_QTY) Mega_Coil_Qty, SUM(RED_COIL_QTY) Red_Coil_Qty, SUM(MAGIC_COIL_QTY) Magic_Coil_Qty
FROM ALL_ITEM_PRODUCTION_QTY_VIEW
where PRODUCTION_DATE between :P_FROM_DATE and :P_TO_DATE
GROUP BY TO_CHAR(PRODUCTION_DATE,'MONTH-RR')
) PR,
(
---- PRODUCTION_OPENING
SELECT ---'1' SL, NULL PDATE, 'Production Opening' MDATE , 'Production Opening' ptype, NULL ITEM_CD , NULL ITEM_NAME,
SUM (Mega_Coil_Qty) Mega_Coil_Qty,
SUM (Red_Coil_Qty) Red_Coil_Qty,
SUM (Magic_Coil_Qty) Magic_Coil_Qty
FROM PRODUCTION_OPENING_VIEW) PO
WHERE ROWNUM=ROWNUM
)PP,
-----Total Delivery
(
SELECT SUM(MEGA_COIL_QTY) MEGA_COIL_QTY, SUM(RED_COIL_QTY) RED_COIL_QTY, SUM(MAGIC_COIL_QTY) MAGIC_COIL_QTY
FROM PRODUCT_DELIVERY_VIEW
WHERE MDATE between :P_FROM_DATE and :P_TO_DATE
GROUP BY TO_CHAR(MDATE,'MONTH-RR')
) TD
WHERE ROWNUM=ROWNUM
------------------------------------------------------------------------------------
----- Total Target
--------------------------------------------------------------------------------------
UNION ALL
SELECT '5' SL, NULL PDATE, 'Total Target' MDATE , 'Total Target' ptype, NULL ITEM_CD , NULL ITEM_NAME,
SUM(MEGA_COIL_QTY) Mega_Coil_Qty, SUM(RED_COIL_QTY) Red_Coil_Qty, SUM(MAGIC_COIL_QTY) Magic_Coil_Qty
FROM PRODUCTION_TARGET_QTY_VIEW
where TARGET_MONTH between :P_FROM_DATE and :P_TO_DATE
--------------------------------------------------------------------------------------
----- Production Pending
--------------------------------------------------------------------------------------
UNION ALL
SELECT '6' SL, NULL PDATE, 'Production Pending' MDATE , 'Production Pending' ptype,
NVL(TD.Mega_Coil_Qty,0) -NVL(PP.Mega_Coil_Qty,0) Mega_Coil_Qty,
NVL(TD.Red_Coil_Qty,0)-NVL(PP.Red_Coil_Qty,0) Red_Coil_Qty,
NVL(TD.Magic_Coil_Qty,0)-NVL(PP.Magic_Coil_Qty,0) Red_Coil_Qty
FROM
(
SELECT NVL(PR.Mega_Coil_Qty,0)+NVL(PO.Mega_Coil_Qty,0) Mega_Coil_Qty,
NVL(PR.Red_Coil_Qty,0)+NVL(PO.Red_Coil_Qty,0) Red_Coil_Qty,
NVL(PR.Magic_Coil_Qty,0)+NVL(PO.Magic_Coil_Qty,0) Magic_Coil_Qty
FROM
(
---- PRODUCTION
SELECT ---TO_CHAR(PRODUCTION_DATE,'MONTH-RR') MDATE , 'Total Production' ptype, NULL ITEM_CD , NULL ITEM_NAME,
SUM(MEGA_COIL_QTY) Mega_Coil_Qty, SUM(RED_COIL_QTY) Red_Coil_Qty, SUM(MAGIC_COIL_QTY) Magic_Coil_Qty
FROM ALL_ITEM_PRODUCTION_QTY_VIEW
where PRODUCTION_DATE between :P_FROM_DATE and :P_TO_DATE
GROUP BY TO_CHAR(PRODUCTION_DATE,'MONTH-RR')
) PR,
(
---- PRODUCTION_OPENING
SELECT ---'1' SL, NULL PDATE, 'Production Opening' MDATE , 'Production Opening' ptype, NULL ITEM_CD , NULL ITEM_NAME,
SUM (Mega_Coil_Qty) Mega_Coil_Qty,
SUM (Red_Coil_Qty) Red_Coil_Qty,
SUM (Magic_Coil_Qty) Magic_Coil_Qty
FROM PRODUCTION_OPENING_VIEW) PO
WHERE ROWNUM=ROWNUM
)PO
WHERE ROWNUM=ROWNUM
)PP,
-----Total Target
(
SELECT '5' SL, NULL PDATE, 'Total Target' MDATE , ' Total Target' ptype, NULL ITEM_CD , NULL ITEM_NAME,
SUM(MEGA_COIL_QTY) Mega_Coil_Qty, SUM(RED_COIL_QTY) Red_Coil_Qty, SUM(MAGIC_COIL_QTY) Magic_Coil_Qty
FROM PRODUCTION_TARGET_QTY_VIEW
where TARGET_MONTH between :P_FROM_DATE and :P_TO_DATE
) TD
WHERE ROWNUM=ROWNUM
-------------------------------------------------------------------------------------
-----Total Delivery
--------------------------------------------------------------------------------------
UNION ALL
SELECT '7' SL, NULL PDATE, 'Monthly Total Delivery' MDATE , 'Monthly Total Delivery' ptype,
SUM(MEGA_COIL_QTY) MEGA_COIL_QTY, SUM(RED_COIL_QTY) RED_COIL_QTY, SUM(MAGIC_COIL_QTY) MAGIC_COIL_QTY
FROM PRODUCT_DELIVERY_VIEW
WHERE MDATE between :P_FROM_DATE and :P_TO_DATE
GROUP BY TO_CHAR(MDATE,'MONTH-RR')
)
group by SL, PDATE, MDATE, ptype
ORDER BY SL, MDATE