[Home] [Help]
PACKAGE BODY: APPS.MSD_SOP_FACT_DATA
Source
1 PACKAGE BODY MSD_SOP_FACT_DATA AS
2 /* $Header: msdsfdcb.pls 120.3 2005/12/08 22:27:44 sjagathe noship $ */
3
4 /* Constants added for Bug# 4308790--------*/
5 C_FROM_DATE constant varchar2(100) := '1000/01/01 00:00:00';
6 C_TO_DATE constant varchar2(100) := '4000/12/31 00:00:00';
7
8 SYS_YES CONSTANT NUMBER := 1; /* Bug# 4615390 ISO */
9 SYS_NO CONSTANT NUMBER := 2; /* Bug# 4615390 ISO */
10
11 PROCEDURE sop_fact_data_collect( errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY VARCHAR2,
13 p_instance_id IN NUMBER,
14 p_date_from IN VARCHAR2,
15 p_date_to IN VARCHAR2,
16 /* p_booking_data IN NUMBER,
17 p_shipment_data IN NUMBER, Bug# 4867205*/
18 p_total_backlog IN NUMBER,
19 p_pastdue_backlog IN NUMBER,
20 p_onhand_inventory IN NUMBER,
21 p_production_plan IN NUMBER,
22 p_actual_production IN NUMBER
23 ) is
24
25
26
27 cursor get_cs_defn_id_c1(l_cs_name IN Varchar2) IS
28 select cs_definition_id
29 from msd_cs_definitions
30 where name = l_cs_name;
31
32 l_cs_name cs_name_list;
33 l_date_from varchar2(30);
34 l_date_to varchar2(30);
35 l_onhand_date_to varchar2(30);
36
37 l_cs_definition_id number;
38 l_cs_name_desc varchar2(80);
39
40 i number;
41 l_req_num number;
42 l_request_id number;
43 x_date_from varchar2(100);
44 x_date_to varchar2(100);
45
46 BEGIN
47 fnd_file.put_line(fnd_file.log, 'Launching SOP Fact Data Collect');
48
49 /* Bug # 4308790 ---- Always populate date range even though the range is null.
50 In case of null, we will use extremely small and large date
51 for the from date and to date ........... Amitku*/
52
53 x_date_from := nvl(p_date_from, C_FROM_DATE);
54 x_date_to := nvl(p_date_to, C_TO_DATE);
55
56 l_onhand_date_to := null;
57
58 /* Create list of SOP streams need to be colelcted */
59
60 /* Bug# 4867205 - Booking and Shipment Data will be collected separately
61 if p_booking_data = 1 then
62 l_request_id := 0;
63 l_request_id:= FND_REQUEST.SUBMIT_REQUEST(
64 'MSD',
65 'MSDCBD', -- Booking Data collect program called
66 NULL, -- description
67 NULL, -- start date
68 FALSE, -- TRUE,
69 p_instance_id, -- Instance Id
70 x_date_from, -- start date
71 x_date_to, -- End Date
72 SYS_NO -- Bug# 4615390: Do not collect ISOs
73 );
74
75 COMMIT;
76
77 IF l_request_id = 0 THEN
78 fnd_file.put_line(fnd_file.log, 'Booking Data Collect Launch Failed');
79 ELSE
80 fnd_file.put_line(fnd_file.log, 'Booking Data collect Request Id: '||l_request_id);
81 END IF;
82
83 end if;
84
85
86 if p_shipment_data = 1 then
87
88 l_request_id := 0;
89 l_request_id:= FND_REQUEST.SUBMIT_REQUEST(
90 'MSD',
91 'MSDCSD', -- Shipment Data collect program called
92 NULL, -- description
93 NULL, -- start date
94 FALSE, -- TRUE,
95 p_instance_id, -- Instance Id
96 x_date_from, -- start date
97 x_date_to, -- End Date
98 SYS_NO -- Bug# 4615390: Do not collect ISOs
99 );
100
101 COMMIT;
102
103 IF l_request_id = 0 THEN
104 fnd_file.put_line(fnd_file.log, 'Shipment Data Collect Launch Failed');
105 ELSE
106 fnd_file.put_line(fnd_file.log, 'Shipment Data collect Request Id: '||l_request_id);
107 END IF;
108
109 end if;
110
111 */
112
113 i := 0;
114 if p_total_backlog = 1 then
115
116 i:= i+1;
117 l_cs_name(i).cs_name := 'MSD_TOTAL_BACKLOG';
118
119 end if;
120
121 if p_pastdue_backlog = 1 then
122
123 i:= i+1;
124 l_cs_name(i).cs_name := 'MSD_PASTDUE_BACKLOG';
125
126 end if;
127
128 if p_production_plan = 1 then
129
130 i:= i+1;
131 l_cs_name(i).cs_name := 'MSD_PRODUCTION_PLAN';
132
133 end if;
134
135 if p_actual_production = 1 then
136
137 i:= i+1;
138 l_cs_name(i).cs_name := 'MSD_ACTUAL_PRODUCTION';
139
140 end if;
141
142 if p_onhand_inventory = 1 then
143
144 i:= i+1;
145 l_cs_name(i).cs_name := 'MSD_ONHAND_INVENTORY';
146
147 if to_date(x_date_to, 'YYYY/MM/DD HH24:MI:SS') >= sysdate then
148
149 l_onhand_date_to := to_char(trunc(sysdate -1), 'YYYYMMDD');
150
151 end if;
152
153 end if;
154
155 l_date_from := to_char(trunc(to_date(x_date_from, 'YYYY/MM/DD HH24:MI:SS')), 'YYYYMMDD');
156 l_date_to := to_char(trunc(to_date(x_date_to, 'YYYY/MM/DD HH24:MI:SS')), 'YYYYMMDD');
157
158
159 /* Check If any SOP streams need to be collected */
160
161 IF l_cs_name.exists(1) THEN
162
163 FOR j IN l_cs_name.FIRST..l_cs_name.LAST LOOP
164
165 /* Get SOP stream defintion id */
166 l_cs_definition_id := null;
167
168 open get_cs_defn_id_c1(l_cs_name(j).cs_name);
169 fetch get_cs_defn_id_c1 into l_cs_definition_id;
170 close get_cs_defn_id_c1;
171
172 if l_cs_definition_id is not null then
173
174 l_cs_name_desc := null;
175
176 select description
177 into l_cs_name_desc
178 from msd_cs_definitions
179 where cs_definition_id = l_cs_definition_id;
180
181 /* Get Collection request id */
182 l_req_num := 0;
183
184 select MSD_CS_COLL_REQUEST_S.nextval
185 into l_req_num
186 from dual;
187
188 /* Call DP CS Collection program */
189
190 l_request_id := 0;
191 if l_onhand_date_to is not null then
192
193 l_request_id := fnd_request.submit_request('MSD', 'MSDCSCL', NULL, NULL,FALSE,
194 'C', 'Y',
195 l_cs_definition_id,
196 NULL,
197 'Y',
198 p_instance_id,
199 l_date_from,
200 l_onhand_date_to,
201 NULL,
202 NULL,
203 NULL,
204 NULL,
205 NULL,
206 NULL,
207 NULL,
208 NULL,
209 l_req_num );
210
211 l_onhand_date_to := null;
212
213 else
214
215 l_request_id := fnd_request.submit_request('MSD', 'MSDCSCL', NULL, NULL,FALSE,
216 'C', 'Y',
217 l_cs_definition_id,
218 NULL,
219 'Y',
220 p_instance_id,
221 l_date_from,
222 l_date_to,
223 NULL,
224 NULL,
225 NULL,
226 NULL,
227 NULL,
228 NULL,
229 NULL,
230 NULL,
231 l_req_num );
232
233 end if;
234
235 COMMIT;
236
237 IF l_request_id = 0 THEN
238 fnd_file.put_line(fnd_file.log, l_cs_name_desc||' Data Collect Launch Failed');
239 ELSE
240 fnd_file.put_line(fnd_file.log, l_cs_name_desc||' Data collect Request Id: '||l_request_id);
241 END IF;
242
243 end if;
244 end loop;
245 end if;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 fnd_file.put_line(fnd_file.log, 'Error in SOP Fact Data Collect Launch');
250 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
251 retcode := -1;
252 raise;
253 end ;
254
255 PROCEDURE sop_fact_data_pull(errbuf OUT NOCOPY VARCHAR2,
256 retcode OUT NOCOPY VARCHAR2
257 ) is
258
259 cursor get_cs_defn_id_c2 IS
260 select cs_definition_id
261 from msd_cs_definitions
262 where name in ('MSD_TOTAL_BACKLOG', 'MSD_PASTDUE_BACKLOG', 'MSD_PRODUCTION_PLAN',
263 'MSD_ACTUAL_PRODUCTION', 'MSD_ONHAND_INVENTORY');
264
265 cursor get_cs_name_c2 (l_cs_definition_id IN number) IS
266 select name
267 from msd_cs_definitions
268 where cs_definition_id = l_cs_definition_id;
269
270 l_cs_id cs_id_list;
271 l_req_num number;
272 l_cs_name varchar(30);
273 l_cs_name_desc varchar(80);
274 l_request_id number;
275
276 BEGIN
277 /* Pull SOP Fact data from staging table to fact table */
278 fnd_file.put_line(fnd_file.log, 'Launching SOP Fact Data Pull');
279
280 /* Bug# 4867205 - Booking and Shipment Data will be collected separately
281
282 -- Launch Booking Data Pull Process
283 l_request_id := 0;
284 l_request_id:= FND_REQUEST.SUBMIT_REQUEST(
285 'MSD',
286 'MSDPBD', -- Booking Data Pull program called
287 NULL, -- description
288 NULL, -- start date
289 FALSE, -- TRUE
290 SYS_NO -- Bug# 4615390: Do not collect ISOs
291 );
292 COMMIT;
293
294 IF l_request_id = 0 THEN
295 fnd_file.put_line(fnd_file.log, 'Booking Data Pull Launch Failed');
296 ELSE
297 fnd_file.put_line(fnd_file.log, 'Booking Data Pull Request Id: '||l_request_id);
298 END IF;
299
300 -- Launch Shipment Data Pull Process
301 l_request_id := 0;
302 l_request_id:= FND_REQUEST.SUBMIT_REQUEST(
303 'MSD',
304 'MSDPSD', -- Shipment Data Pull program called
305 NULL, -- description
306 NULL, -- start date
307 FALSE, -- TRUE
308 SYS_NO -- Bug# 4615390: Do not collect ISOs
309 );
310
311 COMMIT;
312
313 IF l_request_id = 0 THEN
314 fnd_file.put_line(fnd_file.log, 'Shipment Data Pull Launch Failed');
315 ELSE
316 fnd_file.put_line(fnd_file.log, 'Shipment Data Pull Request Id: '||l_request_id);
317 END IF;
318
319 */
320
321 open get_cs_defn_id_c2;
322 fetch get_cs_defn_id_c2 bulk collect into l_cs_id;
323 close get_cs_defn_id_c2;
324
325 IF l_cs_id.exists(1) THEN
326
327 FOR j IN l_cs_id.FIRST..l_cs_id.LAST LOOP
328
329 /* Get CS Name */
330 l_cs_name := null;
331
332 open get_cs_name_c2 (l_cs_id(j).cs_definition_id);
333 fetch get_cs_name_c2 into l_cs_name;
334 close get_cs_name_c2;
335
336 l_cs_name_desc := null;
337
338 select description
339 into l_cs_name_desc
340 from msd_cs_definitions
341 where cs_definition_id = l_cs_id(j).cs_definition_id;
342
343 -- fnd_file.put_line(fnd_file.log, 'CS Name: '||l_cs_name);
344 -- fnd_file.put_line(fnd_file.log, 'CS Id: '||l_cs_id(j).cs_definition_id);
345
346 select MSD_CS_COLL_REQUEST_S.nextval
347 into l_req_num
348 from dual;
349
350 /* Call CS Data Pull program */
351
352 l_request_id := 0;
353 l_request_id := fnd_request.submit_request('MSD', 'MSDCSCL', NULL, NULL,FALSE,
354 'P',
355 'Y',
356 l_cs_id(j).cs_definition_id,
357 null,
358 'Y',
359 null,
360 NULL,
361 NULL,
362 NULL,
363 NULL,
364 NULL,
365 NULL,
366 NULL,
367 NULL,
368 NULL,
369 NULL,
370 l_req_num );
371
372 IF l_request_id = 0 THEN
373 fnd_file.put_line(fnd_file.log, l_cs_name_desc||' Data Pull Launch Failed');
374 ELSE
375 fnd_file.put_line(fnd_file.log, l_cs_name_desc||' Data Pull Request Id: '||l_request_id);
376 END IF;
377
378 COMMIT;
379
380 end loop;
381
382 end if;
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 fnd_file.put_line(fnd_file.log, 'Error in SOP Fact Data Pull Launch');
387 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
388 retcode := -1;
389 raise;
390 end ;
391
392 END MSD_SOP_FACT_DATA;