[Home] [Help]
PACKAGE BODY: APPS.GMP_PLNG_DTL_REPORT_PKG
Source
1 PACKAGE BODY GMP_PLNG_DTL_REPORT_PKG AS
2 /* $Header: GMPPLDRB.pls 120.13.12010000.2 2008/11/01 21:31:25 rpatangy ship $ */
3
4 G_inst_id NUMBER;
5 G_org_id NUMBER;
6 -- G_plan_name VARCHAR2(10);
7 G_plan_id NUMBER;
8 G_plan_org NUMBER;
9 G_start_date DATE;
10 G_day_bucket NUMBER;
11 G_day_bckt_cutoff_dt DATE;
12 G_plan_day_bckt_cutoff_dt DATE;
13 G_week_bucket NUMBER;
14 G_week_bckt_cutoff_dt DATE;
15 G_plan_week_bckt_cutoff_dt DATE;
16 G_period_bucket NUMBER;
17 G_fsort NUMBER;
18 G_ssort NUMBER;
19 G_tsort NUMBER;
20 G_ex_typ NUMBER;
21 G_plnr_low VARCHAR2(10);
22 G_plnr_high VARCHAR2(10);
23 G_byr_low VARCHAR2(300);
24 G_byr_high VARCHAR2(300);
25 G_itm_low VARCHAR2(1000);
26 G_itm_high VARCHAR2(1000);
27 G_cat_set_id NUMBER;
28 G_category_low VARCHAR2(300);
29 G_category_high VARCHAR2(300);
30 G_abc_class_low VARCHAR2(50);
31 G_abc_class_high VARCHAR2(50);
32 G_cutoff_date DATE;
33 G_comb_pdr NUMBER;
34 G_comb_pdr_temp VARCHAR2(150);
35 G_comb_pdr_locale VARCHAR2(10);
36 G_horiz_pdr NUMBER;
37 G_horiz_pdr_temp VARCHAR2(150);
38 G_horiz_pdr_locale VARCHAR2(10);
39 G_vert_pdr NUMBER;
40 G_vert_pdr_temp VARCHAR2(150);
41 G_vert_pdr_locale VARCHAR2(10);
42 G_excep_pdr NUMBER;
43 G_excep_pdr_temp VARCHAR2(150);
44 G_excep_pdr_locale VARCHAR2(10);
45 G_act_pdr NUMBER;
46 G_act_pdr_temp VARCHAR2(150);
47 G_act_pdr_locale VARCHAR2(10);
48
49 G_horiz_plan_stmt VARCHAR2(10000);
50 G_vert_plan_stmt VARCHAR2(15000);
51 G_exc_plan_stmt VARCHAR2(10000);
52 G_act_plan_stmt VARCHAR2(10000);
53 G_common_pdr_stmt VARCHAR2(20000) := NULL;
54 G_horiz_pdr_stmt VARCHAR2(10000) := NULL;
55 G_vert_pdr_stmt VARCHAR2(10000) := NULL;
56 G_excep_pdr_stmt VARCHAR2(10000) := NULL;
57 G_act_pdr_stmt VARCHAR2(10000) := NULL;
58 G_header_stmt VARCHAR2(10000) := NULL;
59
60 invalid_parameter EXCEPTION;
61
62
63 PROCEDURE create_pdr
64 (
65 errbuf OUT NOCOPY VARCHAR2,
66 retcode OUT NOCOPY VARCHAR2,
67 p_inst_id IN NUMBER,
68 p_org_id IN NUMBER,
69 p_plan_id IN NUMBER,
70 p_plan_org IN NUMBER,
71 p_start_date IN VARCHAR2,
72 p_day_bucket IN NUMBER,
73 p_day_bckt_cutoff_dt IN VARCHAR2,
74 p_plan_day_bckt_cutoff_dt IN VARCHAR2,
75 p_week_bucket IN NUMBER,
76 p_week_bckt_cutoff_dt IN VARCHAR2,
77 p_plan_week_bckt_cutoff_dt IN VARCHAR2,
78 p_period_bucket IN NUMBER,
79 p_fsort IN NUMBER,
80 p_ssort IN NUMBER,
81 p_tsort IN NUMBER,
82 p_ex_typ IN NUMBER,
83 p_plnr_low IN VARCHAR2,
84 p_plnr_high IN VARCHAR2,
85 p_byr_low IN VARCHAR2,
86 p_byr_high IN VARCHAR2,
87 p_itm_low IN VARCHAR2,
88 p_itm_high IN VARCHAR2,
89 p_cat_set_id IN NUMBER,
90 p_category_low IN VARCHAR2,
91 p_category_high IN VARCHAR2,
92 p_abc_class_low IN VARCHAR2,
93 p_abc_class_high IN VARCHAR2,
94 p_cutoff_date IN VARCHAR2,
95 p_comb_pdr IN NUMBER,
96 p_comb_pdr_place IN VARCHAR2, -- Added
97 p_comb_comm_pdr IN VARCHAR2, -- Added
98 p_comb_pdr_temp IN VARCHAR2,
99 p_comb_pdr_locale IN VARCHAR2,
100 p_horiz_pdr IN NUMBER,
101 p_horiz_pdr_place IN VARCHAR2, -- Added
102 p_horiz_pdr_temp IN VARCHAR2,
103 p_horiz_pdr_locale IN VARCHAR2,
104 p_vert_pdr IN NUMBER,
105 p_vert_pdr_place IN VARCHAR2, -- Added
106 p_vert_pdr_temp IN VARCHAR2,
107 p_vert_pdr_locale IN VARCHAR2,
108 p_excep_pdr IN NUMBER,
109 p_excep_pdr_place IN VARCHAR2, -- Added
110 p_excep_pdr_temp IN VARCHAR2,
111 p_excep_pdr_locale IN VARCHAR2,
112 p_act_pdr IN NUMBER,
113 p_act_pdr_place IN VARCHAR2, -- Added
114 p_act_pdr_temp IN VARCHAR2,
115 p_act_pdr_locale IN VARCHAR2
116 ) IS
117 BEGIN
118
119 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'In GMP_PLNG_DTL_REPORT_PKG ');
120
121 retcode := 0;
122 G_inst_id := p_inst_id ;
123 G_org_id := p_org_id ;
124 -- G_plan_name := P_plan_name ;
125 G_plan_id := p_plan_id ;
126 G_plan_org := p_plan_org ;
127 G_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS') ;
128 G_day_bucket := NVL(p_day_bucket,0) ; -- 7451619 Rajesh
129 G_day_bckt_cutoff_dt := TO_DATE(p_day_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
130 G_plan_day_bckt_cutoff_dt := TO_DATE(p_plan_day_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
131 G_week_bucket := NVL(p_week_bucket,0) ; -- 7451619 Rajesh
132 G_week_bckt_cutoff_dt := TO_DATE(p_week_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
133 G_plan_week_bckt_cutoff_dt := TO_DATE(p_plan_week_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
134 G_period_bucket := NVL(p_period_bucket,0) ;
135 G_fsort := p_fsort ;
136 G_ssort := p_ssort ;
137 G_tsort := p_tsort ;
138 G_ex_typ := p_ex_typ ;
139 G_plnr_low := p_plnr_low ;
140 G_plnr_high := p_plnr_high ;
141 G_byr_low := p_byr_low ;
142 G_byr_high := p_byr_high ;
143 G_itm_low := p_itm_low ;
144 G_itm_high := p_itm_high ;
145 G_cat_set_id := nvl(p_cat_set_id,0) ;
146 G_category_low := p_category_low ;
147 G_category_high := p_category_high ;
148 G_abc_class_low := p_abc_class_low ;
149 G_abc_class_high := p_abc_class_high ;
150 G_cutoff_date := TO_DATE(p_cutoff_date, 'YYYY/MM/DD HH24:MI:SS') ;
151 G_comb_pdr := NVL(p_comb_pdr,2) ;
152 G_comb_pdr_temp := p_comb_pdr_temp ;
153 G_comb_pdr_locale := p_comb_pdr_locale ;
154 G_horiz_pdr := NVL(p_horiz_pdr,2) ;
155 G_horiz_pdr_temp := p_horiz_pdr_temp ;
156 G_horiz_pdr_locale := p_horiz_pdr_locale ;
157 G_vert_pdr := NVL(p_vert_pdr,2) ;
158 G_vert_pdr_temp := p_vert_pdr_temp ;
159 G_vert_pdr_locale := p_vert_pdr_locale ;
160 G_excep_pdr := NVL(p_excep_pdr,2) ;
161 G_excep_pdr_temp := p_excep_pdr_temp ;
162 G_excep_pdr_locale := p_excep_pdr_locale ;
163 G_act_pdr := NVL(p_act_pdr,2) ;
164 G_act_pdr_temp := p_act_pdr_temp ;
165 G_act_pdr_locale := p_act_pdr_locale ;
166
167 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Calling GMP_PLNG_DTL_REPORT_PKG with values ');
168 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_inst_id '||to_char(G_inst_id));
169 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_org_id '||to_char(G_org_id));
170 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_name '||G_plan_name);
171 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_id '||to_char(G_plan_id));
172 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_org '||to_char(G_plan_org));
173 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_start_date '||TO_CHAR(G_start_date,'DD-MON-YYYY'));
174 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_day_bucket '||to_char(G_day_bucket));
175 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_day_bckt_cutoff_dt '||TO_CHAR(G_day_bckt_cutoff_dt,'DD-MON-YYYY'));
176 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_day_bckt_cutoff_dt '||TO_CHAR(G_plan_day_bckt_cutoff_dt,'DD-MON-YYYY'));
177 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_week_bucket '||to_char(G_week_bucket));
178 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_week_bckt_cutoff_dt '||TO_CHAR(G_week_bckt_cutoff_dt,'DD-MON-YYYY'));
179 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_week_bckt_cutoff_dt '||TO_CHAR(G_plan_week_bckt_cutoff_dt,'DD-MON-YYYY'));
180 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_period_bucket '||to_char(G_period_bucket));
181 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fsort '||to_char(G_fsort));
182 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ssort '||to_char(G_ssort));
183 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tsort '||to_char(G_tsort));
184 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ex_typ '||to_char(G_ex_typ));
185 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plnr_low '||G_plnr_low);
186 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plnr_high '||G_plnr_high);
187 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_byr_low '||G_byr_low);
188 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_byr_high '||G_byr_high);
189 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_itm_low '||G_itm_low);
190 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_itm_high '||G_itm_high);
191 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_cat_set_id '||to_char(G_cat_set_id));
192 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_low '||G_category_low);
193 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_high '||G_category_high);
194 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_abc_class_low '||G_abc_class_low);
195 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_abc_class_high '||G_abc_class_high);
196 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_cutoff_date '||TO_CHAR(G_cutoff_date,'DD-MON-YYYY'));
197 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr '||to_char(G_comb_pdr));
198 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr_temp '||G_comb_pdr_temp);
199 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr_locale '||G_comb_pdr_locale);
200 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr '||to_char(G_vert_pdr));
201 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr_temp '||G_vert_pdr_temp);
202 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr_locale '||G_vert_pdr_locale);
203 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr '||to_char(G_horiz_pdr));
204 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr_temp '||G_horiz_pdr_temp);
205 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr_locale '||G_horiz_pdr_locale);
206 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr '||to_char(G_excep_pdr));
207 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr_temp '||G_excep_pdr_temp);
208 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr_locale '||G_excep_pdr_locale);
209 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr '||to_char(G_act_pdr));
210 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr_temp '||G_act_pdr_temp);
211 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr_locale '||G_act_pdr_locale);
212
213 /* Bug 5708728 with the new new parameters to the report when
214 combined pdr is submitted the four individual report indicators are either
215 NULL or 2 thereby not printing information
216 Changing them to 1 i.e. YES - this also means if combined PDR is
217 submitted you can NOT turn off printing of any of the individual
218 information */
219 IF G_comb_pdr = 1 THEN
220 G_horiz_pdr := 1 ;
221 G_vert_pdr := 1 ;
222 G_excep_pdr := 1 ;
223 G_act_pdr := 1 ;
224 END IF ;
225
226 validate_parameters;
227
228 insert_items;
229
230 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After insert_items ');
231
232 -- INSERT INTO temp_gmp_pdr_items_gtmp SELECT * FROM gmp_pdr_items_gtmp;
233
234 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Calling gmp_horizontal_pdr_pkg.populate_horizontal_plan '||
235 ' with parameters : G_inst_id - '||to_char(G_inst_id)||' G_org_id - '||to_char(G_org_id)||
236 ' G_plan_id - '|| to_char(G_plan_id) ||
237 ' G_day_bckt_cutoff_dt - '||to_char(G_day_bckt_cutoff_dt, 'MM-DD-YYYY HH:MI:SS')||
238 ' G_week_bckt_cutoff_dt - '||to_char(G_week_bckt_cutoff_dt, 'MM-DD-YYYY HH:MI:SS')||
239 ' G_period_bucket - '||to_char(G_period_bucket));
240
241 gmp_horizontal_pdr_pkg.populate_horizontal_plan (G_inst_id, G_org_id, G_plan_id,
242 G_day_bckt_cutoff_dt, G_week_bckt_cutoff_dt, G_period_bucket);
243
244 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After gmp_horizontal_pdr_pkg.populate_horizontal_plan ');
245
246 -- INSERT INTO temp_gmp_horizontal_pdr_gtmp SELECT * FROM gmp_horizontal_pdr_gtmp;
247
248 horiz_plan_stmt;
249
250 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After horiz_plan_stmt ');
251
252 vert_plan_stmt;
253 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After vert_plan_stmt ');
254
255 item_exception_stmt;
256 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After item_exception_stmt ');
257
258 item_action_stmt;
259 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After item_action_stmt ');
260
261 generate_xml;
262 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After generate_xml ');
263 EXCEPTION
264 WHEN OTHERS THEN
265 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Error in package GMP_PLNG_DTL_REPORT_PKG.CREATE_PDR - '|| sqlerrm);
266 RAISE;
267 END create_pdr;
268
269 PROCEDURE validate_parameters IS
270 BEGIN
271 IF G_comb_pdr = 1 THEN
272 IF G_comb_pdr_temp IS NULL OR G_comb_pdr_locale IS NULL THEN
273 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Combined Planning Detail Report ');
274 RAISE invalid_parameter;
275 END IF;
279 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Horizontal Planning Detail Report ');
276 ELSIF G_comb_pdr = 2 THEN
277 IF G_horiz_pdr = 1 THEN
278 IF G_horiz_pdr_temp IS NULL OR G_horiz_pdr_locale IS NULL THEN
280 RAISE invalid_parameter;
281 END IF;
282 END IF;
283 IF G_vert_pdr = 1 THEN
284 IF G_vert_pdr_temp IS NULL OR G_vert_pdr_locale IS NULL THEN
285 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Vertical Planning Detail Report ');
286 RAISE invalid_parameter;
287 END IF;
288 END IF;
289 IF G_excep_pdr = 1 THEN
290 IF G_excep_pdr_temp IS NULL OR G_excep_pdr_locale IS NULL THEN
291 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Exception Planning Detail Report ');
292 RAISE invalid_parameter;
293 END IF;
294 END IF;
295 IF G_act_pdr = 1 THEN
296 IF G_act_pdr_temp IS NULL OR G_act_pdr_locale IS NULL THEN
297 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Action Planning Detail Report ');
298 RAISE invalid_parameter;
299 END IF;
300 END IF;
301 END IF;
302 END validate_parameters;
303
304 PROCEDURE insert_items IS
305
306 x_select VARCHAR2(2000);
307 cur_item NUMBER;
308 X_row_count NUMBER;
309
310 BEGIN
311
312 x_select := ' INSERT INTO gmp_pdr_items_gtmp ( '||
313 ' organization_code, '||
314 ' item_name, '||
315 ' category_name, '||
316 ' planner_code, '||
317 ' buyer_name, '||
318 ' abc_class_name, '||
319 ' inventory_item_id, '||
320 ' organization_id, '||
321 ' base_item_id, '||
322 ' standard_cost, '||
323 ' calculate_atp, '||
324 ' wip_supply_type, '||
325 ' bom_item_type '||
326 ' ) '||
327 ' SELECT DISTINCT '||
328 ' msi.organization_code, '||
329 ' msi.item_name, '||
330 ' mic.category_name, '||
331 ' msi.planner_code, '||
332 ' msi.buyer_name, '||
333 ' msi.abc_class_name, '||
334 ' msi.inventory_item_id, '||
335 ' msi.organization_id, '||
336 ' msi.base_item_id, '||
337 ' msi.standard_cost, '||
338 ' msi.calculate_atp, '||
339 ' msi.wip_supply_type, '||
340 ' msi.bom_item_type '||
341 ' FROM '||
342 ' msc_system_items msi '||
343 ' , msc_item_categories mic ';
344 IF G_ex_typ IS NOT NULL THEN
345 x_select := x_select || ' , msc_item_exceptions mie';
346 END IF;
347
348 x_select := x_select || ' WHERE '||
349 ' msi.sr_instance_id = :inst_id '||
350 ' AND msi.plan_id = :plan_id '||
351 ' AND mic.inventory_item_id = msi.inventory_item_id '||
352 ' AND mic.organization_id = msi.organization_id '||
353 ' AND mic.sr_instance_id = msi.sr_instance_id '||
354 ' AND mic.category_set_id = :cat_set_id ' ;
355 IF G_plan_org <> -999 THEN
356 x_select := x_select || ' AND msi.organization_id = :plan_org ';
357 ELSE
358 x_select := x_select || ' AND msi.organization_id IN (SELECT organization_id FROM msc_plan_organizations '||
359 ' WHERE plan_id = :plan_id AND '||
360 ' sr_instance_id = :inst_id) ';
361 END IF;
362 IF G_category_low IS NOT NULL THEN
363 x_select := x_select || ' AND mic.category_name >= :category_low ';
364 END IF;
365 IF G_category_high IS NOT NULL THEN
366 x_select := x_select || ' AND mic.category_name <= :category_high ';
367 END IF;
368 IF G_ex_typ IS NOT NULL THEN
369 x_select := x_select || ' AND msi.inventory_item_id = mie.inventory_item_id '||
370 ' AND msi.organization_id = mie.organization_id '||
371 ' AND msi.sr_instance_id = mie.sr_instance_id '||
372 ' AND mie.exception_type = :exception_type ';
373 END IF;
374 IF G_plnr_low IS NOT NULL THEN
375 x_select := x_select || ' AND msi.planner_code >= :planner_low ';
376 END IF;
377 IF G_plnr_high IS NOT NULL THEN
378 x_select := x_select || ' AND msi.planner_code <= :planner_high ';
379 END IF;
380 IF G_byr_low IS NOT NULL THEN
381 x_select := x_select || ' AND msi.buyer_name >= :buyer_low ';
382 END IF;
383 IF G_byr_high IS NOT NULL THEN
384 x_select := x_select || ' AND msi.buyer_name <= :buyer_high ';
385 END IF;
386 IF G_abc_class_low IS NOT NULL THEN
387 x_select := x_select || ' AND msi.abc_class_name >= :abc_class_low ';
388 END IF;
389 IF G_abc_class_high IS NOT NULL THEN
390 x_select := x_select || ' AND msi.abc_class_name <= :abc_class_high ';
391 END IF;
392 IF G_itm_low IS NOT NULL THEN
393 x_select := x_select || ' AND msi.item_name >= :item_name_low ';
394 END IF;
395 IF G_itm_high IS NOT NULL THEN
396 x_select := x_select || ' AND msi.item_name <= :item_name_high ';
400 IF G_fsort IS NOT NULL THEN
397 END IF;
398
399 /*
401 x_select := x_select || ' ORDER BY :first_sort ';
402 END IF;
403 IF G_ssort IS NOT NULL THEN
404 x_select := x_select || ' , :second_sort ';
405 END IF;
406 IF G_tsort IS NOT NULL THEN
407 x_select := x_select || ' , :third_sort ';
408 END IF;
409 IF G_fsort IS NOT NULL AND G_ssort IS NOT NULL AND G_tsort IS NOT NULL THEN
410 x_select := x_select || ' ORDER BY msi.inventory_item_id, msi.organization_id ';
411 END IF;
412 */
413
414 cur_item := dbms_sql.open_cursor;
415 dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
416
417 dbms_sql.bind_variable(cur_item, ':inst_id', G_inst_id);
418 dbms_sql.bind_variable(cur_item, ':plan_id', G_plan_id);
419 -- dbms_sql.bind_variable(cur_item, ':plan_org', G_plan_org);
420 dbms_sql.bind_variable(cur_item, ':cat_set_id', G_cat_set_id);
421
422 IF G_plan_org <> -999 THEN
423 dbms_sql.bind_variable(cur_item, ':plan_org', G_plan_org);
424 END IF;
425
426 IF G_category_low IS NOT NULL THEN
427 dbms_sql.bind_variable(cur_item, ':category_low', G_category_low);
428 END IF;
429 IF G_category_high IS NOT NULL THEN
430 dbms_sql.bind_variable(cur_item, ':category_high', G_category_high);
431 END IF;
432
433 IF G_ex_typ IS NOT NULL THEN
434 dbms_sql.bind_variable(cur_item, ':exception_type', G_ex_typ);
435 END IF;
436
437 IF G_plnr_low IS NOT NULL THEN
438 dbms_sql.bind_variable(cur_item, ':planner_low', G_plnr_low);
439 END IF;
440 IF G_plnr_high IS NOT NULL THEN
441 dbms_sql.bind_variable(cur_item, ':planner_high', G_plnr_high);
442 END IF;
443
444 IF G_byr_low IS NOT NULL THEN
445 dbms_sql.bind_variable(cur_item, ':buyer_low', G_byr_low);
446 END IF;
447 IF G_byr_high IS NOT NULL THEN
448 dbms_sql.bind_variable(cur_item, ':buyer_high', G_byr_high);
449 END IF;
450
451 IF G_abc_class_low IS NOT NULL THEN
452 dbms_sql.bind_variable(cur_item, ':abc_class_low', G_abc_class_low);
453 END IF;
454 IF G_abc_class_high IS NOT NULL THEN
455 dbms_sql.bind_variable(cur_item, ':abc_class_high', G_abc_class_high);
456 END IF;
457
458 IF G_itm_low IS NOT NULL THEN
459 dbms_sql.bind_variable(cur_item, ':item_name_low', G_itm_low);
460 END IF;
461 IF G_itm_high IS NOT NULL THEN
462 dbms_sql.bind_variable(cur_item, ':item_name_high', G_itm_high);
463 END IF;
464 /*
465 IF G_fsort IS NOT NULL THEN
466 dbms_sql.bind_variable(cur_item, ':first_sort', G_fsort);
467 END IF;
468 IF G_ssort IS NOT NULL THEN
469 dbms_sql.bind_variable(cur_item, ':second_sort', G_ssort);
470 END IF;
471 IF G_tsort IS NOT NULL THEN
472 dbms_sql.bind_variable(cur_item, ':third_sort', G_tsort);
473 END IF;
474 */
475 X_row_count := dbms_sql.EXECUTE (cur_item);
476
477 FND_FILE.PUT_LINE(FND_FILE.LOG,'Num of rows in gmp_pdr_items_gtmp '||to_char(X_row_count));
478
479 dbms_sql.close_cursor (cur_item);
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in package GMP_PLNG_DTL_REPORT_PKG '||sqlerrm);
484 IF dbms_sql.is_open (cur_item) THEN
485 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION cur_item is Open');
486 dbms_sql.close_cursor (cur_item);
487 END IF;
488 END insert_items;
489
490
491 PROCEDURE horiz_plan_stmt IS
492
493 -- x_select VARCHAR2(5000);
494
495 BEGIN
496
497 G_horiz_plan_stmt := ' SELECT '||
498 ' organization_id , '||
499 ' inventory_item_id , '||
500 ' bucket_date , '||
501 ' quantity1 , '||
502 ' quantity2 , '||
503 ' quantity3 , '||
504 ' quantity4 , '||
505 ' quantity5 , '||
506 ' quantity6 , '||
507 ' quantity7 , '||
508 ' quantity8 , '||
509 ' quantity9 , '||
510 ' quantity10 , '||
511 ' quantity11 , '||
512 ' quantity12 , '||
513 ' quantity13 , '||
514 ' quantity14 , '||
515 ' quantity15 , '||
516 ' quantity16 , '||
517 ' quantity17 , '||
518 ' quantity18 , '||
519 ' quantity19 , '||
520 ' quantity20 , '||
521 ' quantity21 , '||
522 ' quantity22 , '||
523 ' quantity23 , '||
524 ' quantity24 , '||
525 ' quantity25 , '||
526 ' quantity26 , '||
527 ' quantity27 , '||
528 ' quantity28 , '||
529 ' quantity29 , '||
530 ' quantity30 , '||
531 ' quantity31 , '||
532 ' quantity32 , '||
533 ' quantity33 , '||
534 ' quantity34 , '||
535 ' quantity35 , '||
536 ' quantity36 , '||
537 ' quantity37 , '||
538 ' quantity38 , '||
539 ' quantity39 , '||
540 ' quantity40 , '||
541 ' quantity41 , '||
542 ' quantity42 , '||
543 ' quantity43 , '||
544 ' quantity44 , '||
545 ' quantity45 '||
546 ' FROM '||
547 ' ( SELECT '||
548 ' ghp.organization_id , '||
552 ' ghp.quantity2 , '||
549 ' ghp.inventory_item_id , '||
550 ' ghp.bucket_date , '||
551 ' ghp.quantity1 , '||
553 ' ghp.quantity3 , '||
554 ' ghp.quantity4 , '||
555 ' ghp.quantity5 , '||
556 ' ghp.quantity6 , '||
557 ' ghp.quantity7 , '||
558 ' ghp.quantity8 , '||
559 ' ghp.quantity9 , '||
560 ' ghp.quantity10 , '||
561 ' ghp.quantity11 , '||
562 ' ghp.quantity12 , '||
563 ' ghp.quantity13 , '||
564 ' ghp.quantity14 , '||
565 ' ghp.quantity15 , '||
566 ' ghp.quantity16 , '||
567 ' ghp.quantity17 , '||
568 ' ghp.quantity18 , '||
569 ' ghp.quantity19 , '||
570 ' ghp.quantity20 , '||
571 ' ghp.quantity21 , '||
572 ' ghp.quantity22 , '||
573 ' ghp.quantity23 , '||
574 ' ghp.quantity24 , '||
575 ' ghp.quantity25 , '||
576 ' ghp.quantity26 , '||
577 ' ghp.quantity27 , '||
578 ' ghp.quantity28 , '||
579 ' ghp.quantity29 , '||
580 ' ghp.quantity30 , '||
581 ' ghp.quantity31 , '||
582 ' ghp.quantity32 , '||
583 ' ghp.quantity33 , '||
584 ' ghp.quantity34 , '||
585 ' ghp.quantity35 , '||
586 ' ghp.quantity36 , '||
587 ' ghp.quantity37 , '||
588 ' ghp.quantity38 , '||
589 ' ghp.quantity39 , '||
590 ' ghp.quantity40 , '||
591 ' ghp.quantity41 , '||
592 ' ghp.quantity42 , '||
593 ' ghp.quantity43 , '||
594 ' ghp.quantity44 , '||
595 ' ghp.quantity45 '||
596 ' FROM '||
597 ' gmp_horizontal_pdr_gtmp ghp ) ';
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_exception_stmt '||sqlerrm);
602 END horiz_plan_stmt;
603
604 PROCEDURE vert_plan_stmt IS
605
606 -- x_select VARCHAR2(5000);
607
608 BEGIN
609
610 G_vert_plan_stmt :=
611 ' SELECT '||
612 ' organization_code, '|| /* header show */
613 ' item_name, '|| /* header show */
614 ' category_name, '|| /* header show */
615 ' planner_code, '|| /* header show */
616 ' buyer_name, '|| /* header show */
617 ' abc_class_name, '|| /* header show */
618 ' planning_group, '||
619 ' order_type, '||/* detail show */
620 ' order_number, '||/* detail show */
621 ' activity_date, '||/* detail show */
622 ' quantity_rate, '||/* detail show */
623 ' old_schd_date, '||
624 ' order_placement_date, '||
625 ' new_schedule_date, '||
626 ' new_doc_date, '||
627 ' new_wip_start_date, '||
628 ' implement_as_id, '||
629 ' firm_date, '||
630 ' firm_qty, '||
631 ' wip_qty, '||
632 ' compression_days, '||
633 ' using_assembly_item_name, '||
634 ' designator, '||
635 ' source_org, '||
636 ' supplier_name, '||
637 ' plan_name, '||
638 ' plan_id, '||
639 ' organization_id, '||
640 ' sr_instance_id, '||
641 ' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
642 ' inventory_item_id, '||
643 ' supplier_id, '||
644 ' implement_as, '||
645 ' implemented_qty '||
646 ' FROM '||
647 '( SELECT '||
648 ' gpi.organization_code organization_code, '|| /* header show */
649 ' gpi.item_name item_name, '|| /* header show */
650 ' gpi.category_name category_name, '|| /* header show */
651 ' gpi.planner_code planner_code, '|| /* header show */
652 ' gpi.buyer_name buyer_name, '|| /* header show */
653 ' gpi.abc_class_name abc_class_name, '|| /* header show */
654 ' sup.planning_group planning_group, '||
655 ' l1.meaning order_type, '||/* detail show */
656 ' DECODE (sup.order_type, '||
657 ' 5, TO_CHAR (sup.transaction_id), '||
658 ' sup.order_number '||
659 ' ) order_number, '||/* detail show */
660 ' cal.calendar_date activity_date, '||/* detail show */
661 ' NVL (sup.daily_rate, sup.new_order_quantity) quantity_rate, '||/* detail show */
662 ' sup.old_schedule_date old_schd_date, '||
663 ' sup.new_order_placement_date order_placement_date, '||
664 ' sup.new_schedule_date new_schedule_date, '||
665 ' sup.new_dock_date new_doc_date, '||
666 ' sup.new_wip_start_date new_wip_start_date, '||
667 ' sup.implement_as implement_as_id, '||
668 ' sup.firm_date firm_date, '||
669 ' sup.firm_quantity firm_qty, '||
670 ' NVL (sup.daily_rate, sup.new_order_quantity) - NVL (sup.quantity_in_process, 0) wip_qty, '||
671 ' sup.schedule_compress_days compression_days, '||
672 ' TO_CHAR (NULL) using_assembly_item_name, '||
673 ' msc_get_name.designator (sup.schedule_designator_id) designator, '||
677 ' ) source_org, '||
674 -- ' msc_get_name.designator (sup.schedule_designator_id), /* Since this column is selected twice, so removing one instance. */
675 ' msc_get_name.org_code (sup.source_organization_id, '||
676 ' sup.source_sr_instance_id '||
678 ' msc_get_name.supplier (DECODE (sup.plan_id, '||
679 ' -1, sup.supplier_id, '||
680 ' DECODE (sup.order_type, '||
681 ' 1, sup.supplier_id, '||
682 ' 2, sup.supplier_id, '||
683 ' sup.source_supplier_id '||
684 ' ) '||
685 ' ) '||
686 ' ) supplier_name, '||
687 ' mp.compile_designator plan_name, '||
688 ' sup.plan_id plan_id, '||
689 ' sup.organization_id organization_id, '||
690 ' sup.sr_instance_id sr_instance_id, '||
691 ' '||G_cat_set_id||' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
692 ' sup.inventory_item_id inventory_item_id, '||
693 ' sup.supplier_id supplier_id, '||
694 ' DECODE (sup.implement_as, '||
695 ' NULL, NULL, '||
696 ' msc_get_name.lookup_meaning ('||''''||'MRP_WORKBENCH_IMPLEMENT_AS'||''''||', '||
697 ' sup.implement_as '||
698 ' ) '||
699 ' ) implement_as, '||
700 ' DECODE (sup.disposition_status_type, '||
701 ' 2, 0.0, '||
702 ' NVL (sup.daily_rate, sup.new_order_quantity) '||
703 ' ) implemented_qty '||
704 ' FROM '||
705 -- msc_item_categories mic,
706 -- ' msc_system_items msi,
707 ' gmp_pdr_items_gtmp gpi, '||
708 ' msc_supplies sup, '||
709 ' msc_trading_partners mtp, '||
710 ' msc_calendar_dates cal, '||
711 ' mfg_lookups l1, '||
712 ' msc_plans mp '||
713 ' WHERE cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
714 ' AND NVL (TRUNC (sup.last_unit_completion_date), '||
715 ' TRUNC (sup.new_schedule_date) '||
716 ' ) '||
717 ' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
718 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
719 ' AND cal.calendar_code = mtp.calendar_code '||
720 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
721 ' AND mtp.sr_tp_id = sup.organization_id '||
722 ' AND mtp.sr_instance_id = sup.sr_instance_id '||
723 ' AND mtp.partner_type = 3 '||
724 ' AND sup.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
725 ' AND sup.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
726 ' AND sup.organization_id = gpi.organization_id '||
727 ' AND sup.inventory_item_id = gpi.inventory_item_id '||
728 ' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
729 ' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
730 ' AND l1.lookup_code = sup.order_type '||
731 ' AND mp.plan_id = sup.plan_id '||
732 ' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) '||
733 /* Global Var */
734 ' UNION ALL '||
735 ' SELECT '||
736 ' gpi.organization_code organization_code, '|| /* header show */
737 ' gpi.item_name item_name, '|| /* header show */
738 ' gpi.category_name category_name, '|| /* header show */
739 ' gpi.planner_code planner_code, '|| /* header show */
740 ' gpi.buyer_name buyer_name, '|| /* header show */
741 ' gpi.abc_class_name abc_class_name, '|| /* header show */
742 ' dem.planning_group planning_group, '||
743 ' l1.meaning order_type, '||/* detail show */
744 ' NVL (dem.order_number, '||
745 ' DECODE (dem.origination_type, '||
746 ' 29, msc_get_name.scenario_designator (dem.forecast_set_id, '||
747 ' dem.plan_id, '||
748 ' dem.organization_id, '||
749 ' dem.sr_instance_id '||
750 ' ), '||
751 ' msc_get_name.designator (dem.schedule_designator_id) '||
752 ' ) '||
753 ' ) order_number , '||/* detail show */
754 ' cal.calendar_date activity_date , '||/* detail show */
755 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) quantity_rate, '||/* detail show */
756 ' dem.old_demand_date old_schd_date, '||
757 ' TO_DATE (NULL) order_placement_date, '||
758 ' dem.using_assembly_demand_date new_schedule_date, '||
759 ' TO_DATE (NULL) new_doc_date, '||
760 ' TO_DATE (NULL) new_wip_start_date, '||
761 ' TO_NUMBER (NULL)implement_as_id, '||
765 ' TO_NUMBER (NULL) compression_days, '||
762 ' dem.firm_date firm_date, '||
763 ' dem.firm_quantity firm_qty, '||
764 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) - TO_NUMBER (NULL) wip_qty, '||
766 ' msc_get_name.item_name (dem.using_assembly_item_id, NULL, NULL, NULL) using_assembly_item_name, '||
767 ' DECODE (dem.schedule_designator_id, '||
768 ' NULL, NULL, '||
769 ' DECODE (dem.origination_type, '||
770 ' 29, msc_get_name.forecastsetname (dem.forecast_set_id, '||
771 ' dem.plan_id, '||
772 ' dem.organization_id, '||
773 ' dem.sr_instance_id '||
774 ' ), '||
775 ' msc_get_name.designator (dem.schedule_designator_id) '||
776 ' ) '||
777 ' ) designator, '||
778 /* DECODE (dem.schedule_designator_id,
779 NULL, NULL,
780 DECODE (dem.origination_type,
781 29, msc_get_name.forecastsetname (dem.forecast_set_id,
782 dem.plan_id,
783 dem.organization_id,
784 dem.sr_instance_id
785 ),
786 msc_get_name.designator (dem.schedule_designator_id)
787 )
788 ), */ /* Since this column is selected twice, so removing one instance. */
789 ' msc_get_name.org_code (dem.source_organization_id, '||
790 ' dem.source_org_instance_id '||
791 ' ) source_org, '||
792 ' NULL supplier_name, '||
793 ' mp.compile_designator plan_name, '||
794 ' dem.plan_id plan_id, '||
795 ' dem.organization_id organization_id, '||
796 ' dem.sr_instance_id sr_instance_id, '||
797 ' '||G_cat_set_id||' cat_set_id, '||-- mic.category_set_id, /* Global Var */
798 ' dem.inventory_item_id inventory_item_id, '||
799 ' TO_NUMBER (NULL) supplier_id, '||
800 ' TO_CHAR (NULL) implement_as, '||
801 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) implemented_qty '||
802 ' FROM '||
803 -- msc_item_categories mic,
804 -- ' msc_system_items msi,
805 ' gmp_pdr_items_gtmp gpi, '||
806 ' msc_demands dem, '||
807 ' msc_trading_partners mtp, '||
808 ' msc_calendar_dates cal, '||
809 ' mfg_lookups l1, '||
810 ' msc_plans mp '||
811 ' WHERE cal.calendar_date BETWEEN TRUNC (dem.using_assembly_demand_date) '||
812 ' AND NVL (TRUNC (dem.assembly_demand_comp_date), '||
813 ' TRUNC (dem.using_assembly_demand_date) '||
814 ' ) '||
815 ' AND DECODE (dem.assembly_demand_comp_date, NULL, 1, cal.seq_num) IS NOT NULL '||
816 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
817 ' AND cal.calendar_code = mtp.calendar_code '||
818 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
819 ' AND mtp.sr_tp_id = dem.organization_id '||
820 ' AND mtp.sr_instance_id = dem.sr_instance_id '||
821 ' AND mtp.partner_type = 3 '||
822 ' AND dem.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
823 ' AND dem.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
824 ' AND dem.organization_id = gpi.organization_id '||
825 ' AND dem.inventory_item_id = gpi.inventory_item_id '||
826 ' AND NVL (dem.daily_demand_rate, dem.using_requirement_quantity) <> 0 '||
827 ' AND l1.lookup_type = '||''''||'MSC_DEMAND_ORIGINATION'||''''||
828 ' AND l1.lookup_code = dem.origination_type '||
829 ' AND mp.plan_id = dem.plan_id '||
830 ' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) )';
831 /* Global Var */
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure vert_plan_stmt '||sqlerrm);
836 END vert_plan_stmt;
837
838 PROCEDURE item_exception_stmt IS
839
840 -- x_select VARCHAR2(5000);
841
842 BEGIN
843
844 G_exc_plan_stmt :=
845 ' SELECT '||
846 ' organization_code, '||
847 ' item_name, '||
848 ' category_name, '|| /* header show */
849 ' planner_code, '|| /* header show */
850 ' buyer_name, '|| /* header show */
851 ' abc_class_name, '|| /* header show */
852 ' exception_id, '||
853 ' inventory_item_id, '||
854 ' organization_id, '||
855 ' exception_type, '||
856 ' exception_type_text, '||
857 ' due_date, '||
858 ' quantity, '||
859 ' from_date, '||
860 ' to_date, '||
861 ' lot_number, '||
862 ' department_line_code'||
863 ' FROM '||
864 '( SELECT '||
865 ' med.organization_code organization_code, '||
869 ' gpi.buyer_name buyer_name, '|| /* header show */
866 ' gpi.item_name item_name, '||
867 ' gpi.category_name category_name, '|| /* header show */
868 ' gpi.planner_code planner_code, '|| /* header show */
870 ' gpi.abc_class_name abc_class_name, '|| /* header show */
871 ' med.exception_id exception_id, '||
872 ' gpi.inventory_item_id inventory_item_id, '||
873 ' gpi.organization_id organization_id, '||
874 ' med.exception_type exception_type, '||
875 ' med.exception_type_text exception_type_text, '||
876 ' med.due_date due_date, '||
877 ' med.quantity quantity, '||
878 ' med.from_date from_date, '||
879 ' med.to_date to_date, '||
880 ' med.lot_number lot_number, '||
881 ' med.department_line_code department_line_code'||
882 ' FROM msc_exception_details_v med, '||
883 ' gmp_pdr_items_gtmp gpi '||
884 ' WHERE med.plan_id = '||G_plan_id||
885 -- Bug: 7257708 Vpedarla changed the below two lines
886 -- ' WHERE med.exception_type IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,20,24,26,30) '||
887 -- ' AND TRUNC (med.to_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (med.to_date))) '||
888 ' AND TRUNC (nvl(med.to_date,sysdate)) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (nvl(med.to_date,sysdate)))) '||
889 /* Global Var */
890 ' AND med.sr_instance_id = '||G_inst_id||
891 ' AND med.inventory_item_id = gpi.inventory_item_id '||
892 ' AND nvl(med.category_set_id, '||G_cat_set_id ||') = '|| G_cat_set_id ||
893 ' AND med.organization_id = gpi.organization_id )' ;
894 EXCEPTION
895 WHEN OTHERS THEN
896 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_exception_stmt '||sqlerrm);
897 END item_exception_stmt;
898
899 PROCEDURE item_action_stmt IS
900
901 BEGIN
902
903 G_act_plan_stmt :=
904 ' SELECT '||
905 ' inventory_item_id, '||
906 ' organization_id, '||
907 ' item_action, '||
908 ' order_type, '||
909 ' order_number, '||
910 ' activity_date, '||
911 ' old_schedule_date, '||
912 ' new_order_placement_date, '||
913 ' new_schedule_date, '||
914 ' new_dock_date, '||
915 ' new_wip_start_date, '||
916 ' schedule_compress_days '||
917 ' FROM '||
918 ' ( SELECT '||
919 ' gpi.inventory_item_id, '||
920 ' gpi.organization_id, '||
921 ' DECODE '||
922 ' ( '||
923 ' msc_get_name.action '||
924 ' ( '||
925 ' '||''''||'MSC_SUPPLIES'||''''||' , '||
926 ' gpi.bom_item_type, '||
927 ' gpi.base_item_id, '||
928 ' gpi.wip_supply_type, '||
929 ' sup.order_type, '||
930 ' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
931 ' sup.disposition_status_type, '||
932 ' sup.new_schedule_date, '||
933 ' sup.old_schedule_date, '||
934 ' sup.implemented_quantity, '||
935 ' sup.quantity_in_process, '||
936 ' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
937 ' ), '||
938 ' '||''''||'None'||''''||' , DECODE '||
939 ' ( '||
940 ' SIGN (sup.new_schedule_date - sup.old_schedule_date), '||
941 ' 1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,3), '||
942 ' -1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,2), '||
943 ' '||''''||'None'||''''||
944 ' ), '||
945 ' msc_get_name.action '||
946 ' ( '||
947 ' '||''''||'MSC_SUPPLIES'||''''||' , '||
948 ' gpi.bom_item_type, '||
949 ' gpi.base_item_id, '||
950 ' gpi.wip_supply_type, '||
951 ' sup.order_type, '||
952 ' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
953 ' sup.disposition_status_type, '||
954 ' sup.new_schedule_date, '||
955 ' sup.old_schedule_date, '||
956 ' sup.implemented_quantity, '||
957 ' sup.quantity_in_process, '||
961 ' l1.meaning order_type, '||
958 ' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
959 ' ) '||
960 ' ) item_action, '||
962 ' DECODE (sup.order_type,5, TO_CHAR (sup.transaction_id),sup.order_number) order_number, '||
963 ' cal.calendar_date activity_date, '||
964 ' sup.old_schedule_date old_schedule_date, '||
965 ' sup.new_order_placement_date new_order_placement_date, '||
966 ' sup.new_schedule_date new_schedule_date, '||
967 ' sup.new_dock_date new_dock_date, '||
968 ' sup.new_wip_start_date new_wip_start_date, '||
969 ' sup.schedule_compress_days schedule_compress_days '||
970 ' FROM '||
971 ' gmp_pdr_items_gtmp gpi, '||
972 ' msc_supplies sup, '||
973 ' msc_calendar_dates cal, '||
974 ' msc_trading_partners mtp, '||
975 ' mfg_lookups l1 '||
976 ' WHERE '||
977 ' cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
978 ' AND NVL (TRUNC (sup.last_unit_completion_date), '||
979 ' TRUNC (sup.new_schedule_date) '||
980 ' ) '||
981 ' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
982 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
983 ' AND cal.calendar_code = mtp.calendar_code '||
984 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
985 ' AND mtp.sr_tp_id = sup.organization_id '||
986 ' AND mtp.sr_instance_id = sup.sr_instance_id '||
987 ' AND mtp.partner_type = 3 '||
988 ' AND sup.plan_id = '||G_plan_id|| -- gpi.plan_id /* Global Var */
989 ' AND sup.sr_instance_id = '||G_inst_id|| -- gpi.sr_instance_id /* Global Var */
990 ' AND sup.organization_id = gpi.organization_id '||
991 ' AND sup.inventory_item_id = gpi.inventory_item_id '||
992 ' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
993 ' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
994 ' AND l1.lookup_code = sup.order_type '||
995 ' AND TRUNC (cal.calendar_date) <= (NVL (TRUNC(TO_DATE('||''''||TO_CHAR(G_cutoff_date, 'YYYY/MM/DD HH24:MI:SS')||''''||', '||''''||'YYYY/MM/DD HH24:MI:SS'||''''||')), TRUNC (cal.calendar_date))) ) ';
996
997 EXCEPTION
998 WHEN OTHERS THEN
999 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_action_stmt '||sqlerrm);
1000 END item_action_stmt;
1001
1002 PROCEDURE generate_xml IS
1003
1004 qryCtx DBMS_XMLGEN.ctxHandle;
1005 result CLOB;
1006 seq_stmt VARCHAR2(100);
1007 x_seq_num NUMBER;
1008
1009 BEGIN
1010
1011 seq_stmt := NULL;
1012 x_seq_num := 0;
1013
1014 G_header_stmt := ' SELECT ' ||
1015 ' msc_get_name.org_code ('||G_org_id||', '||G_inst_id||' ) master_org, '||
1016 ' msc_get_name.instance_code ('||G_inst_id||' ) instance_code, '||
1017 -- ''''||G_plan_name||''''||' plan_name, ' ||
1018 ' gmp_plng_dtl_report_pkg.plan_name plan_name, '||
1019 ' gmp_plng_dtl_report_pkg.plan_org ('||G_plan_org||' ) plan_org, '||
1020 G_day_bucket||' day_bucket, '||
1021 G_week_bucket||' week_bucket, '||
1022 G_period_bucket||' period_bucket, ';
1023
1024 IF G_fsort IS NOT NULL THEN
1025 G_header_stmt := G_header_stmt ||
1026 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_fsort||' ) first_sort, ';
1027 ELSE
1028 G_header_stmt := G_header_stmt ||
1029 ''''||G_fsort||''''||' first_sort, ';
1030 END IF;
1031
1032 IF G_ssort IS NOT NULL THEN
1033 G_header_stmt := G_header_stmt ||
1034 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_ssort||' ) second_sort, ';
1035 ELSE
1036 G_header_stmt := G_header_stmt ||
1037 ''''||G_ssort||''''||' second_sort, ';
1038 END IF;
1039
1040 IF G_tsort IS NOT NULL THEN
1041 G_header_stmt := G_header_stmt ||
1042 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_tsort||' ) third_sort, ';
1043 ELSE
1044 G_header_stmt := G_header_stmt ||
1045 ''''||G_tsort||''''||' third_sort, ' ;
1046 END IF;
1047
1048 IF G_ex_typ IS NOT NULL THEN
1049 G_header_stmt := G_header_stmt ||
1050 -- Bug: 7257708 Vpedarla changed the below line.
1051 -- ' msc_get_name.lookup_meaning ('||''''||'MSC_X_EXCEPTION_TYPE'||''''||', '||G_ex_typ||' ) exception_type, ';
1052 ' msc_get_name.lookup_meaning ('||''''||'MRP_EXCEPTION_CODE_TYPE'||''''||', '||G_ex_typ||' ) exception_type, ';
1053 ELSE
1054 G_header_stmt := G_header_stmt ||
1055 ''''||G_ex_typ||''''||' exception_type, ' ;
1056 END IF;
1057
1058 G_header_stmt := G_header_stmt ||''''||G_plnr_low||''''||' planner_low, ' ||
1059 ''''||G_plnr_high||''''||' planner_high, ' ||
1060 ''''||G_byr_low||''''||' buyer_low, ' ||
1061 ''''||G_byr_high||''''||' buyer_high, ' ||
1062 ''''||G_itm_low||''''||' item_low, ' ||
1063 ''''||G_itm_high||''''||' item_high, ' ||
1064 ' gmp_plng_dtl_report_pkg.category_set_name ('||G_cat_set_id||' ) category_set_name, '||
1068 ''''||G_abc_class_low||''''||' abc_class_low, ' ||
1065 /* ToDo : define the function category_set_name */
1066 ''''||G_category_low||''''||' category_low, ' ||
1067 ''''||G_category_high||''''||' category_high, ' ||
1069 ''''||G_abc_class_high||''''||' abc_class_high, ' ||
1070 ''''||G_cutoff_date||''''||' cutoff_date, ' ||
1071 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_comb_pdr||' ) comb_pdr, '||
1072 ''''||G_comb_pdr_temp||''''||' comb_pdr_temp, ' ||
1073 ''''||G_comb_pdr_locale||''''||' comb_pdr_locale, ' ||
1074 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_horiz_pdr||' ) horiz_pdr, '||
1075 ''''||G_horiz_pdr_temp||''''||' horiz_pdr_temp, ' ||
1076 ''''||G_horiz_pdr_locale||''''||' horiz_pdr_locale, ' ||
1077 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_vert_pdr||' ) vert_pdr, '||
1078 ''''||G_vert_pdr_temp||''''||' vert_pdr_temp, ' ||
1079 ''''||G_vert_pdr_locale||''''||' vert_pdr_locale, ' ||
1080 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_excep_pdr||' ) excep_pdr, '||
1081 ''''||G_excep_pdr_temp||''''||' excep_pdr_temp, ' ||
1082 ''''||G_excep_pdr_locale||''''||' excep_pdr_locale, ' ||
1083 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_act_pdr||' ) act_pdr, '||
1084 ''''||G_act_pdr_temp||''''||' act_pdr_temp, ' ||
1085 ''''||G_act_pdr_locale||''''||' act_pdr_locale ';
1086
1087 IF G_comb_pdr = 1 THEN /*combined pdr report */
1088
1089 -- INSERT INTO ns_debug (LONGVAL) values ('in combined PDR');
1090 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'in combined PDR');
1091
1092 G_common_pdr_stmt := G_common_pdr_stmt || G_header_stmt;
1093
1094 G_common_pdr_stmt := G_common_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name item_name, gpi.organization_code organization_code, '||
1095 ' gpi.category_name category_name, gpi.buyer_name buyer_name, gpi.planner_code planner_code, gpi.abc_class_name abc_class_name';
1096
1097 IF G_horiz_pdr = 1 THEN
1098 BEGIN
1099 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1100 ' ('||G_horiz_plan_stmt||' horiz '||
1101 ' WHERE gpi.inventory_item_id = horiz.inventory_item_id '||
1102 ' AND gpi.organization_id = horiz.organization_id ) horiz ';
1103 EXCEPTION
1104 when others then
1105 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in horiz cursor stmt '||sqlerrm);
1106 END ;
1107 END IF;
1108
1109 IF G_vert_pdr = 1 THEN
1110 BEGIN
1111 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1112 ' ('||G_vert_plan_stmt||' vert '||
1113 ' WHERE gpi.inventory_item_id = vert.inventory_item_id '||
1114 ' AND gpi.organization_id = vert.organization_id ) vert ';
1115 EXCEPTION
1116 when others then
1117 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in vert cursor stmt '||sqlerrm);
1118 END ;
1119 END IF;
1120
1121 IF G_excep_pdr = 1 THEN
1122 BEGIN
1123 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1124 ' ('||G_exc_plan_stmt||' exc '||
1125 ' WHERE gpi.inventory_item_id = exc.inventory_item_id '||
1126 ' AND gpi.organization_id = exc.organization_id ) exc ';
1127 EXCEPTION
1128 when others then
1129 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in exc cursor stmt '||sqlerrm);
1130 END ;
1131 END IF;
1132
1133 IF G_act_pdr = 1 THEN
1134 BEGIN
1135 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1136 ' ('||G_act_plan_stmt||' act '||
1137 ' WHERE gpi.inventory_item_id = act.inventory_item_id '||
1138 ' AND gpi.organization_id = act.organization_id ) act ';
1139 EXCEPTION
1140 when others then
1141 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in act cursor stmt '||sqlerrm);
1142 END ;
1143 END IF;
1144
1145 G_common_pdr_stmt := G_common_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1146 IF G_fsort IS NOT NULL THEN
1147 G_common_pdr_stmt := G_common_pdr_stmt || ' ORDER BY '||G_fsort;
1148 ELSE
1149 G_common_pdr_stmt := G_common_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1150 END IF;
1151
1152 IF G_ssort IS NOT NULL THEN
1153 G_common_pdr_stmt := G_common_pdr_stmt || ', '||G_ssort;
1154 END IF;
1155
1156 IF G_tsort IS NOT NULL THEN
1157 G_common_pdr_stmt := G_common_pdr_stmt || ', '||G_tsort;
1158 END IF;
1159
1160 IF G_fsort IS NOT NULL THEN
1161 G_common_pdr_stmt := G_common_pdr_stmt || ' ) gpi ';
1162 END IF;
1163 G_common_pdr_stmt := G_common_pdr_stmt || ' FROM DUAL ';
1164
1165 -- INSERT INTO ns_debug (LONGVAL) values (G_common_pdr_stmt);
1166 -- FND_FILE.PUT_LINE(FND_FILE.LOG,G_common_pdr_stmt);
1167
1168 qryctx := dbms_xmlgen.newcontext(G_common_pdr_stmt);
1169 result := DBMS_XMLGEN.getXML(qryCtx);
1170 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1171
1172 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1176
1173 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,1,x_seq_num );
1174 DBMS_XMLGEN.closeContext(qryCtx);
1175 ps_generate_output(x_seq_num,1);
1177 ELSE /* Not Combined PDR */
1178
1179 IF G_horiz_pdr = 1 THEN /* Horizontal xml */
1180
1181 G_horiz_pdr_stmt := G_horiz_pdr_stmt || G_header_stmt ;
1182
1183 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name item_name, gpi.organization_code organization_code, '||
1184 ' gpi.category_name category_name, gpi.buyer_name buyer_name, gpi.planner_code planner_code, gpi.abc_class_name abc_class_name ';
1185
1186 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', CURSOR '||
1187 ' ('||G_horiz_plan_stmt||' horiz '||
1188 ' WHERE gpi.inventory_item_id = horiz.inventory_item_id '||
1189 ' AND gpi.organization_id = horiz.organization_id ) horiz ';
1190
1191 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1192 IF G_fsort IS NOT NULL THEN
1193 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ORDER BY '||G_fsort;
1194 ELSE
1195 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1196 END IF;
1197
1198 IF G_ssort IS NOT NULL THEN
1199 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', '||G_ssort;
1200 END IF;
1201
1202 IF G_tsort IS NOT NULL THEN
1203 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', '||G_tsort;
1204 END IF;
1205
1206 IF G_fsort IS NOT NULL THEN
1207 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ) gpi ';
1208 END IF;
1209 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' FROM DUAL ';
1210
1211 qryctx := dbms_xmlgen.newcontext(G_horiz_pdr_stmt);
1212 result := DBMS_XMLGEN.getXML(qryCtx);
1213 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1214
1215 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1216 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,2,x_seq_num );
1217 DBMS_XMLGEN.closeContext(qryCtx);
1218 ps_generate_output(x_seq_num,2);
1219
1220 -- INSERT INTO ns_debug (col3) values (G_horiz_pdr_stmt);
1221
1222 END IF;
1223
1224 IF G_vert_pdr = 1 THEN /* Vertical PDR */
1225
1226 G_vert_pdr_stmt := G_vert_pdr_stmt || G_header_stmt ;
1227
1228 G_vert_pdr_stmt := G_vert_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1229 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1230
1231 G_vert_pdr_stmt := G_vert_pdr_stmt || ', CURSOR '||
1232 ' ('||G_vert_plan_stmt||' vert '||
1233 ' WHERE gpi.inventory_item_id = vert.inventory_item_id '||
1234 ' AND gpi.organization_id = vert.organization_id ) vert ';
1235
1236 G_vert_pdr_stmt := G_vert_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1237 IF G_fsort IS NOT NULL THEN
1238 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ORDER BY '||G_fsort;
1239 ELSE
1240 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1241 END IF;
1242
1243 IF G_ssort IS NOT NULL THEN
1244 G_vert_pdr_stmt := G_vert_pdr_stmt || ', '||G_ssort;
1245 END IF;
1246
1247 IF G_tsort IS NOT NULL THEN
1248 G_vert_pdr_stmt := G_vert_pdr_stmt || ', '||G_tsort;
1249 END IF;
1250
1251 IF G_fsort IS NOT NULL THEN
1252 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ) gpi ';
1253 END IF;
1254 G_vert_pdr_stmt := G_vert_pdr_stmt || ' FROM DUAL ';
1255
1256 qryctx := dbms_xmlgen.newcontext(G_vert_pdr_stmt);
1257 result := DBMS_XMLGEN.getXML(qryCtx);
1258 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1259
1260 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1261 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,3,x_seq_num );
1262 DBMS_XMLGEN.closeContext(qryCtx);
1263 ps_generate_output(x_seq_num,3);
1264
1265 -- INSERT INTO ns_debug (col3) values (G_vert_pdr_stmt);
1266
1267 END IF;
1268
1269 IF G_excep_pdr = 1 THEN
1270
1271 G_excep_pdr_stmt := G_excep_pdr_stmt || G_header_stmt ;
1272
1273 G_excep_pdr_stmt := G_excep_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1274 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1275
1276 G_excep_pdr_stmt := G_excep_pdr_stmt || ', CURSOR '||
1277 ' ('||G_exc_plan_stmt||' exc '||
1278 ' WHERE gpi.inventory_item_id = exc.inventory_item_id '||
1279 ' AND gpi.organization_id = exc.organization_id ) exc ';
1280
1281 G_excep_pdr_stmt := G_excep_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1282 IF G_fsort IS NOT NULL THEN
1283 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ORDER BY '||G_fsort;
1284 ELSE
1285 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1286 END IF;
1287
1288 IF G_ssort IS NOT NULL THEN
1289 G_excep_pdr_stmt := G_excep_pdr_stmt || ', '||G_ssort;
1290 END IF;
1291
1292 IF G_tsort IS NOT NULL THEN
1296 IF G_fsort IS NOT NULL THEN
1293 G_excep_pdr_stmt := G_excep_pdr_stmt || ', '||G_tsort;
1294 END IF;
1295
1297 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ) gpi ';
1298 END IF;
1299 G_excep_pdr_stmt := G_excep_pdr_stmt || ' FROM DUAL ';
1300
1301 qryctx := dbms_xmlgen.newcontext(G_excep_pdr_stmt);
1302 result := DBMS_XMLGEN.getXML(qryCtx);
1303 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1304
1305 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1306 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,4,x_seq_num );
1307 DBMS_XMLGEN.closeContext(qryCtx);
1308 ps_generate_output(x_seq_num,4);
1309
1310 -- INSERT INTO ns_debug (col3) values (G_excep_pdr_stmt);
1311
1312 END IF;
1313
1314 IF G_act_pdr = 1 THEN
1315
1316 G_act_pdr_stmt := G_act_pdr_stmt || G_header_stmt ;
1317
1318 G_act_pdr_stmt := G_act_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1319 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1320
1321 G_act_pdr_stmt := G_act_pdr_stmt || ', CURSOR '||
1322 ' ('||G_act_plan_stmt||' act '||
1323 ' WHERE gpi.inventory_item_id = act.inventory_item_id '||
1324 ' AND gpi.organization_id = act.organization_id ) act ';
1325
1326 G_act_pdr_stmt := G_act_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1327 IF G_fsort IS NOT NULL THEN
1328 G_act_pdr_stmt := G_act_pdr_stmt || ' ORDER BY '||G_fsort;
1329 ELSE
1330 G_act_pdr_stmt := G_act_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1331 END IF;
1332
1333 IF G_ssort IS NOT NULL THEN
1334 G_act_pdr_stmt := G_act_pdr_stmt || ', '||G_ssort;
1335 END IF;
1336
1337 IF G_tsort IS NOT NULL THEN
1338 G_act_pdr_stmt := G_act_pdr_stmt || ', '||G_tsort;
1339 END IF;
1340
1341 IF G_fsort IS NOT NULL THEN
1342 G_act_pdr_stmt := G_act_pdr_stmt || ' ) gpi ';
1343 END IF;
1344 G_act_pdr_stmt := G_act_pdr_stmt || ' FROM DUAL ';
1345
1346 qryctx := dbms_xmlgen.newcontext(G_act_pdr_stmt);
1347 result := DBMS_XMLGEN.getXML(qryCtx);
1348 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1349
1350 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1351 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,5,x_seq_num );
1352 DBMS_XMLGEN.closeContext(qryCtx);
1353 ps_generate_output(x_seq_num,5);
1354
1355 -- INSERT INTO ns_debug (col3) values (G_act_pdr_stmt);
1356
1357 END IF;
1358
1359 END IF; /* Combined PDR */
1360
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure generate_xml '||sqlerrm);
1364
1365 END generate_xml;
1366
1367 FUNCTION plan_name RETURN VARCHAR2 IS
1368 plan_name VARCHAR2(10);
1369 BEGIN
1370
1371 SELECT compile_designator
1372 INTO plan_name
1373 FROM msc_plans
1374 WHERE plan_id = G_plan_id;
1375
1376 RETURN plan_name;
1377
1378 EXCEPTION WHEN OTHERS THEN
1379 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function plan_name '||sqlerrm);
1380 END plan_name;
1381
1382 FUNCTION plan_org ( org_id IN NUMBER) RETURN VARCHAR2 IS
1383 org_code VARCHAR2(7);
1384 BEGIN
1385
1386 IF org_id = -999 THEN
1387
1388 SELECT organization_code
1389 INTO org_code
1390 FROM gmp_plan_organization_v
1391 WHERE organization_id = org_id;
1392
1393 RETURN org_code;
1394
1395 END IF;
1396
1397 SELECT organization_code
1398 INTO org_code
1399 FROM gmp_plan_organization_v
1400 WHERE organization_id = org_id
1401 AND plan_id = G_plan_id
1402 AND sr_instance_id = G_inst_id;
1403
1404 RETURN org_code;
1405
1406 EXCEPTION WHEN OTHERS THEN
1407 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function plan_org '||sqlerrm);
1408 END plan_org;
1409
1410 FUNCTION category_set_name ( cat_set_id IN NUMBER) RETURN VARCHAR2 IS
1411 cat_set_name VARCHAR2(30);
1412 BEGIN
1413
1414 SELECT category_set_name
1415 INTO cat_set_name
1416 FROM msc_category_sets
1417 WHERE category_set_id = cat_set_id
1418 AND sr_instance_id = G_inst_id;
1419
1420 RETURN cat_set_name;
1421
1422 EXCEPTION WHEN OTHERS THEN
1423 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function category_set_name '||sqlerrm);
1424 END category_set_name;
1425
1426 FUNCTION lookup_meaning(l_lookup_type IN VARCHAR2,
1427 l_lookup_code IN NUMBER) RETURN VARCHAR2 IS
1428
1429 meaning_text VARCHAR2(80);
1430 BEGIN
1431
1432 IF l_lookup_code IS NULL THEN
1433 RETURN NULL;
1434 END IF;
1435
1436 SELECT meaning
1437 INTO meaning_text
1438 FROM fnd_lookup_values
1439 WHERE language = userenv('LANG')
1440 AND lookup_type = l_lookup_type
1441 AND TO_NUMBER(lookup_code) = l_lookup_code;
1442
1443 RETURN meaning_text;
1444
1445 EXCEPTION WHEN no_data_found THEN
1446 RETURN NULL;
1450 * NAME
1447 END lookup_meaning;
1448
1449 /* ***************************************************************
1451 * PROCEDURE - ps_generate_output
1452 * PARAMETERS
1453 * DESCRIPTION
1454 * Procedure used generate the final output.
1455 * HISTORY
1456 * Namit 31Mar05 - Initial Version
1457 *************************************************************** */
1458
1459 PROCEDURE ps_generate_output (
1460 p_sequence_num IN NUMBER,
1461 p_pdr_type IN NUMBER
1462 )
1463 IS
1464
1465 l_conc_id NUMBER;
1466 l_req_id NUMBER;
1467 l_phase VARCHAR2(20);
1468 l_status_code VARCHAR2(20);
1469 l_dev_phase VARCHAR2(20);
1470 l_dev_status VARCHAR2(20);
1471 l_message VARCHAR2(20);
1472 l_status BOOLEAN;
1473 l_log_text VARCHAR2(2000);
1474
1475
1476 BEGIN
1477
1478 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPPDROP','', '',FALSE,
1479 p_sequence_num,chr(0),'','','','','','','','','','','',
1480 '','','','','','','','','','','','','','','',
1481 '','','','','','','','','','',
1482 '','','','','','','','','','',
1483 '','','','','','','','','','',
1484 '','','','','','','','','','',
1485 '','','','','','','','','','',
1486 '','','','','','','','','','',
1487 '','','','','','','','','','');
1488
1489 IF l_conc_id = 0 THEN
1490 l_log_text := FND_MESSAGE.GET;
1491 FND_FILE.PUT_LINE ( FND_FILE.LOG,l_log_text);
1492 ELSE
1493 COMMIT ;
1494 END IF;
1495
1496 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'l_conc_id : '||to_char(l_conc_id));
1497 IF l_conc_id <> 0 THEN
1498
1499 l_status := fnd_concurrent.WAIT_FOR_REQUEST
1500 (
1501 REQUEST_ID => l_conc_id,
1502 INTERVAL => 30,
1503 MAX_WAIT => 900,
1504 PHASE => l_phase,
1505 STATUS => l_status_code,
1506 DEV_PHASE => l_dev_phase,
1507 DEV_STATUS => l_dev_status,
1508 MESSAGE => l_message
1509 );
1510
1511 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Wait completed for conc request '||to_char(l_conc_id));
1512
1513 DELETE FROM gmp_pdr_xml_temp WHERE pdr_xml_id = p_sequence_num;
1514 COMMIT;
1515
1516 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'p_pdr_type = '||to_char(p_pdr_type));
1517
1518 IF p_pdr_type = 1 THEN
1519
1520 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1521 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1522 l_conc_id,554,G_comb_pdr_temp,
1523 G_comb_pdr_locale,'N','','','','','','','','',
1524 '','','','','','','','','','','','','','','',
1525 '','','','','','','','','','',
1526 '','','','','','','','','','',
1527 '','','','','','','','','','',
1528 '','','','','','','','','','',
1529 '','','','','','','','','','',
1530 '','','','','','','','','','',
1531 '','','','','','','','','','');
1532 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Submitted combined PDR with request id '||to_char(l_req_id));
1533
1534 ELSIF p_pdr_type = 2 THEN
1535
1536 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1537 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1538 l_conc_id,554,G_horiz_pdr_temp,
1539 G_horiz_pdr_locale,'N','','','','','','','','',
1540 '','','','','','','','','','','','','','','',
1541 '','','','','','','','','','',
1542 '','','','','','','','','','',
1543 '','','','','','','','','','',
1544 '','','','','','','','','','',
1545 '','','','','','','','','','',
1546 '','','','','','','','','','',
1547 '','','','','','','','','','');
1548
1549 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Submitted Horizontal PDR with request id '||to_char(l_req_id));
1550
1551 ELSIF p_pdr_type = 3 THEN
1552
1553 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1554 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1555 l_conc_id,554,G_vert_pdr_temp,
1556 G_vert_pdr_locale,'N','','','','','','','','',
1557 '','','','','','','','','','','','','','','',
1558 '','','','','','','','','','',
1559 '','','','','','','','','','',
1560 '','','','','','','','','','',
1561 '','','','','','','','','','',
1562 '','','','','','','','','','',
1563 '','','','','','','','','','',
1564 '','','','','','','','','','');
1565
1566 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Submitted Vertical PDR with request id '||to_char(l_req_id));
1567
1568 ELSIF p_pdr_type = 4 THEN
1569
1570 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1571 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1572 l_conc_id,554,G_excep_pdr_temp,
1573 G_excep_pdr_locale,'N','','','','','','','','',
1574 '','','','','','','','','','','','','','','',
1575 '','','','','','','','','','',
1576 '','','','','','','','','','',
1577 '','','','','','','','','','',
1578 '','','','','','','','','','',
1579 '','','','','','','','','','',
1580 '','','','','','','','','','',
1581 '','','','','','','','','','');
1582
1583 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Submitted Exception PDR with request id '||to_char(l_req_id));
1584
1585 ELSIF p_pdr_type = 5 THEN
1586
1587 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1588 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1589 l_conc_id,554,G_act_pdr_temp,
1590 G_act_pdr_locale,'N','','','','','','','','',
1591 '','','','','','','','','','','','','','','',
1592 '','','','','','','','','','',
1593 '','','','','','','','','','',
1594 '','','','','','','','','','',
1595 '','','','','','','','','','',
1596 '','','','','','','','','','',
1597 '','','','','','','','','','',
1598 '','','','','','','','','','');
1599
1600 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Submitted Action PDR with request id '||to_char(l_req_id));
1601
1602 END IF;
1603 END IF;
1604
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_output '||SQLERRM);
1608 END ps_generate_output;
1609
1610 /* ***************************************************************
1611 * NAME
1612 * PROCEDURE - xml_transfer
1613 * PARAMETERS
1614 * DESCRIPTION
1615 * Procedure used provide the XML as output of the concurrent program.
1616 * HISTORY
1617 * Namit 31Mar05 - Initial Version
1618 *************************************************************** */
1619
1620 PROCEDURE xml_transfer (
1621 errbuf OUT NOCOPY VARCHAR2,
1622 retcode OUT NOCOPY VARCHAR2,
1623 p_sequence_num IN NUMBER
1624 )IS
1625
1626 l_file CLOB;
1627 file_varchar2 VARCHAR2(32767);
1628 m_file CLOB;
1629 l_len NUMBER;
1630 l_limit NUMBER;
1631
1632 BEGIN
1633
1634 SELECT xml_file INTO l_file
1635 FROM gmp_pdr_xml_temp
1636 WHERE pdr_xml_id = p_sequence_num;
1637 l_limit:= 1;
1638
1639 l_len := DBMS_LOB.GETLENGTH (l_file);
1640 LOOP
1641 IF l_len > l_limit THEN
1642 --BUG 6646373 DBMS_LOB.SUBSTR was failing for multi byte character as l_file being CLOB type variable.
1643 --Introduced another clob variable m_file and after trimming it assigned to the varchar type variable.
1644 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1645 M_FILE := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1646 file_varchar2:=trim(M_FILE);
1647 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
1648 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
1649 file_varchar2 := NULL;
1650 m_file :=NULL;
1651 l_limit:= l_limit + 10;
1652 ELSE
1653 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1654 M_FILE := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1655 file_varchar2:=trim(M_FILE);
1656 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
1657 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
1658 file_varchar2 := NULL;
1659 m_file :=NULL;
1660 EXIT;
1661 END IF;
1662 END LOOP;
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure gmp_plng_dtl_report_pkg.xml_transfer '||SQLERRM);
1666 END;
1667
1668
1669 END GMP_PLNG_DTL_REPORT_PKG;