[Home] [Help]
PACKAGE BODY: APPS.FLM_LINEARITY_REPORT
Source
1 PACKAGE BODY flm_linearity_report AS
2 /* $Header: FLMFLINB.pls 115.3 2002/11/27 11:19:25 nrajpal ship $ */
3
4 PROCEDURE populate_flow_summary (
5 x_return_status OUT NOCOPY VARCHAR2,
6 p_line_from IN VARCHAR2,
7 p_line_to IN VARCHAR2,
8 p_sch_group IN VARCHAR2,
9 p_org_id IN NUMBER,
10 p_begin_date IN DATE,
11 p_last_date IN DATE,
12 p_query_id IN NUMBER
13 ) IS
14
15 CURSOR flow_schedule_cursor(l_week_start_date DATE) IS
16 SELECT wl.line_code,
17 wsg.schedule_group_name schedule_group,
18 NVL(wfs.schedule_group_id,-1),
19 wfs.primary_item_id,
20 trunc(wfs.scheduled_completion_date),
21 sum(nvl(wfs.planned_quantity,0)),
22 sum(nvl(wfs.quantity_completed,0))
23 FROM wip_flow_schedules wfs, wip_lines wl, wip_schedule_groups wsg
24 WHERE wfs.organization_id = p_org_id
25 AND trunc(wfs.scheduled_completion_date) between trunc(l_week_start_date) and trunc(l_week_start_date+6)
26 AND wl.line_id = wfs.line_id
27 AND wl.organization_id = wfs.organization_id
28 AND (p_line_from IS NULL or (wl.line_code >= p_line_from AND wl.line_code <= p_line_to))
29 AND wsg.schedule_group_id(+) = wfs.schedule_group_id
30 AND wsg.organization_id(+) = wfs.organization_id
31 AND (p_sch_group IS NULL or p_sch_group = wsg.schedule_group_name)
32 GROUP BY wl.line_code, wsg.schedule_group_name, wfs.schedule_group_id, wfs.primary_item_id,
33 trunc(wfs.scheduled_completion_date);
34
35 TYPE flow_schedule_type IS RECORD
36 ( line_code VARCHAR2(10),
37 schedule_group VARCHAR2(150),
38 schedule_group_id NUMBER,
39 item_id NUMBER,
40 completion_date DATE,
41 planned_qty NUMBER,
42 actual_qty NUMBER );
43
44 TYPE daily_qty_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
45 TYPE daily_date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
46
47 TYPE weekly_flow_schedule_type IS RECORD
48 ( week_start_date DATE,
49 line_code VARCHAR2(10),
50 schedule_group VARCHAR2(150),
51 item_id NUMBER,
52 planned_qty daily_qty_table,
53 actual_qty daily_qty_table );
54
55 flow_schedule flow_schedule_type;
56 weekly_flow_schedule weekly_flow_schedule_type;
57 l_week_start_date DATE;
58 l_last_line_code VARCHAR2(10);
59 l_last_schedule_group_id NUMBER;
60 l_last_item_id NUMBER;
61
62 PROCEDURE clean_record IS
63 BEGIN
64 weekly_flow_schedule.item_id := NULL;
65 weekly_flow_schedule.week_start_date := NULL;
66 weekly_flow_schedule.line_code := NULL;
67 weekly_flow_schedule.schedule_group := NULL;
68 weekly_flow_schedule.planned_qty(1) := NULL;
69 weekly_flow_schedule.planned_qty(2) := NULL;
70 weekly_flow_schedule.planned_qty(3) := NULL;
71 weekly_flow_schedule.planned_qty(4) := NULL;
72 weekly_flow_schedule.planned_qty(5) := NULL;
73 weekly_flow_schedule.planned_qty(6) := NULL;
74 weekly_flow_schedule.planned_qty(7) := NULL;
75 weekly_flow_schedule.actual_qty(1) := NULL;
76 weekly_flow_schedule.actual_qty(2) := NULL;
77 weekly_flow_schedule.actual_qty(3) := NULL;
78 weekly_flow_schedule.actual_qty(4) := NULL;
79 weekly_flow_schedule.actual_qty(5) := NULL;
80 weekly_flow_schedule.actual_qty(6) := NULL;
81 weekly_flow_schedule.actual_qty(7) := NULL;
82 END;
83
84 PROCEDURE flush_record IS
85 BEGIN
86
87 INSERT INTO mrp_form_query(
88 query_id,
89 last_update_date,
90 last_updated_by,
91 creation_date,
92 created_by,
93 number15,
94 date1,
95 char1,
96 char2,
97 number1,
98 number2,
99 number3,
100 number4,
101 number5,
102 number6,
103 number7,
104 number8,
105 number9,
106 number10,
107 number11,
108 number12,
109 number13,
110 number14 )
111 VALUES (
112 p_query_id,
113 sysdate,
114 1,
115 sysdate,
116 1,
117 weekly_flow_schedule.item_id,
118 weekly_flow_schedule.week_start_date,
119 weekly_flow_schedule.line_code,
120 weekly_flow_schedule.schedule_group,
121 weekly_flow_schedule.planned_qty(1),
122 weekly_flow_schedule.planned_qty(2),
123 weekly_flow_schedule.planned_qty(3),
124 weekly_flow_schedule.planned_qty(4),
125 weekly_flow_schedule.planned_qty(5),
126 weekly_flow_schedule.planned_qty(6),
127 weekly_flow_schedule.planned_qty(7),
128 weekly_flow_schedule.actual_qty(1),
129 weekly_flow_schedule.actual_qty(2),
130 weekly_flow_schedule.actual_qty(3),
131 weekly_flow_schedule.actual_qty(4),
132 weekly_flow_schedule.actual_qty(5),
133 weekly_flow_schedule.actual_qty(6),
134 weekly_flow_schedule.actual_qty(7)
135 );
136
137 END flush_record;
138
139 BEGIN
140 clean_record;
141 l_week_start_date := p_begin_date;
142
143 WHILE (l_week_start_date < p_last_date) LOOP
144
145 OPEN flow_schedule_cursor(l_week_start_date);
146 FETCH flow_schedule_cursor INTO flow_schedule;
147
148 IF (flow_schedule_cursor%FOUND) THEN
149
150 l_last_schedule_group_id := flow_schedule.schedule_group_id;
151 l_last_line_code := flow_schedule.line_code;
152 l_last_item_id := flow_schedule.item_id;
153 LOOP
154
155 IF ((l_last_schedule_group_id<>flow_schedule.schedule_group_id) OR
156 (l_last_line_code<>flow_schedule.line_code) OR (l_last_item_id<>flow_schedule.item_id)) THEN
157 flush_record;
158 clean_record;
159 END IF;
160
161 weekly_flow_schedule.week_start_date := l_week_start_date;
162 weekly_flow_schedule.line_code := flow_schedule.line_code;
163 weekly_flow_schedule.schedule_group := flow_schedule.schedule_group;
164 weekly_flow_schedule.item_id := flow_schedule.item_id;
165 weekly_flow_schedule.planned_qty(flow_schedule.completion_date-l_week_start_date+1)
166 := flow_schedule.planned_qty;
167 weekly_flow_schedule.actual_qty(flow_schedule.completion_date-l_week_start_date+1)
168 := flow_schedule.actual_qty;
169
170 l_last_schedule_group_id := flow_schedule.schedule_group_id;
171 l_last_line_code := flow_schedule.line_code;
172 l_last_item_id := flow_schedule.item_id;
173
174 FETCH flow_schedule_cursor INTO flow_schedule;
175 IF (flow_schedule_cursor%NOTFOUND) THEN
176 flush_record;
177 clean_record;
178 EXIT;
179 END IF;
180
181 END LOOP;
182
183 END IF;
184 l_week_start_date := l_week_start_date + 7;
185 CLOSE flow_schedule_cursor;
186 END LOOP;
187
188 END Populate_Flow_Summary;
189
190 END flm_linearity_report;