Hi I have three tables :
NSK_FCST
NSK_OUTPUT
NSK_LEADTIME
Data of NSK_OUTPUT:
Data of NSK_FCST
Data of NSK_LEADTIME
Expected Output:
| ITEM1 | LOC1 | FCST1 | FCST2 | FCST3 | FCST4 | FCST5 |
| i1 | l1 | 1 | 2 | 3 | 4 | 5 |
| i2 | l2 | 7 | 8 | 9 | 10 | 600 |
| i3 | l3 | 13 | 14 | 15 | 6000 | 7000 |
Offsetlt will be in multiples of 7 and should be divided by 7 before processing it
Logic For i1:
For i1, leadtime in nsk_offsetlt is 7 so remainder is zero. so the record should be output as is from nsk_output table
Logic For i2:
For i2, leadtime in nsk_offsetlt is 14 so remainder is two. So the columns in nsk_output should be move one week i.e.,
NSK_OUTPUT.FCST1 = NSK_OUTPUT.FCST2
NSK_OUTPUT.FCST2 = NSK_OUTPUT.FCST3
NSK_OUTPUT.FCST3 = NSK_OUTPUT.FCST4
NSK_OUTPUT.FCST4 = NSK_OUTPUT.FCST5
NSK_OUTPUT.FCST5 = NSK_FCST.FCST6 [as there is no column Fcst6 in nsk_output table, so now we should query nsk_fcst table for item i2 get the value NSK_FCST.FCST6 and put value in NSK_OUTPUT.FCST5]
Logic for i3:
For i3, leadtime in nsk_offsetlt is 21 so remainder is three. So the columns in nsk_output should be move two weeks i.e.,
NSK_OUTPUT.FCST1 = NSK_OUTPUT.FCST3
NSK_OUTPUT.FCST2 = NSK_OUTPUT.FCST4
NSK_OUTPUT.FCST3 = NSK_OUTPUT.FCST5
NSK_OUTPUT.FCST4 = NSK_FCST.FCST6[as there is no column Fcst6 in nsk_output table, so now we should query nsk_fcst table for item i3 get the value NSK_FCST.FCST6 and put value in NSK_OUTPUT.FCST4]
NSK_OUTPUT.FCST5 = NSK_FCST.FCST7 [as there is no column Fcst6/Fcst7 in nsk_output table, so now we should query nsk_fcst table for item i3 get the value NSK_FCST.FCST7 and put value in NSK_OUTPUT.FCST5]
NSK_OFFSETLT value will be dynamic and will be based on the table, however it will be interms of multiples of 7 only.
thanks for your pointers in advance.
CREATE TABLE NSK_FCST
(
ITEM1 VARCHAR2 (100),
LOC1 VARCHAR2 (100),
FCST1 NUMBER,
FCST2 NUMBER,
FCST3 NUMBER,
FCST4 NUMBER,
FCST5 NUMBER,
FCST6 NUMBER,
FCST7 NUMBER,
FCST8 NUMBER
);
CREATE TABLE NSK_LEADTIME
(
ITEM1 VARCHAR2 (100),
LEADTIME NUMBER
);
CREATE TABLE NSK_OUTPUT
(
ITEM1 VARCHAR2 (100),
LOC1 VARCHAR2 (100),
FCST1 NUMBER,
FCST2 NUMBER,
FCST3 NUMBER,
FCST4 NUMBER,
FCST5 NUMBER
);
INSERT INTO NSK_FCST
VALUES ('i1',
'l1',
10,
20,
30,
40,
50,
60,
70,
80);
INSERT INTO NSK_FCST
VALUES ('i2',
'l2',
100,
200,
300,
400,
500,
600,
700,
800);
INSERT INTO NSK_FCST
VALUES ('i3',
'l3',
1000,
2000,
3000,
4000,
5000,
6000,
7000,
8000);
INSERT INTO NSK_OUTPUT
VALUES ('i1',
'l1',
1,
2,
3,
4,
5);
INSERT INTO NSK_OUTPUT
VALUES ('i2',
'l2',
6,
7,
8,
9,
10);
INSERT INTO NSK_OUTPUT
VALUES ('i3',
'l3',
11,
12,
13,
14,
15);
INSERT INTO NSK_LEADTIME
VALUES ('i1', 0);
INSERT INTO NSK_LEADTIME
VALUES ('i2', 7);
INSERT INTO NSK_LEADTIME
VALUES ('i3', 14);