[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.35.12020000.2 2012/07/24 15:13:40 vkinduri ship $ */
3
4
5 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('GMP_DEBUG_ENABLED'),'N'); -- BUG: 9366921
6
7 split_report VARCHAR2(1) := NVL(FND_PROFILE.VALUE('GMP_SPLIT_PDR_BY_ORGANIZATION'),'N'); -- Bug: 9458217 Vpedarla
8
9 scale_report VARCHAR2(1) ; -- Bug: 9265463 vpedarla
10
11 G_inst_id NUMBER;
12 G_org_id NUMBER;
13 -- G_plan_name VARCHAR2(10);
14 G_plan_id NUMBER;
15 G_plan_org NUMBER;
16 G_start_date DATE;
17 G_day_bucket NUMBER;
18 G_day_bckt_cutoff_dt DATE;
19 G_plan_day_bckt_cutoff_dt DATE;
20 G_week_bucket NUMBER;
21 G_week_bckt_cutoff_dt DATE;
22 G_plan_week_bckt_cutoff_dt DATE;
23 G_period_bucket NUMBER;
24 G_fsort NUMBER;
25 G_ssort NUMBER;
26 G_tsort NUMBER;
27 G_ex_typ NUMBER;
28 G_plnr_low VARCHAR2(10);
29 G_plnr_high VARCHAR2(10);
30 G_byr_low VARCHAR2(300);
31 G_byr_high VARCHAR2(300);
32 G_itm_low VARCHAR2(1000);
33 G_itm_high VARCHAR2(1000);
34 G_cat_set_id NUMBER;
35 G_category_low VARCHAR2(300);
36 G_category_high VARCHAR2(300);
37 G_abc_class_low VARCHAR2(50);
38 G_abc_class_high VARCHAR2(50);
39 G_cutoff_date DATE;
40 G_incl_items_no_activity NUMBER ; -- Bug: 8486531 Vpedarla
41 G_comb_pdr NUMBER;
42 G_comb_pdr_temp VARCHAR2(150);
43 G_comb_pdr_locale VARCHAR2(10);
44 G_horiz_pdr NUMBER;
45 G_horiz_pdr_temp VARCHAR2(150);
46 G_horiz_pdr_locale VARCHAR2(10);
47 G_vert_pdr NUMBER;
48 G_vert_pdr_temp VARCHAR2(150);
49 G_vert_pdr_locale VARCHAR2(10);
50 G_excep_pdr NUMBER;
51 G_excep_pdr_temp VARCHAR2(150);
52 G_excep_pdr_locale VARCHAR2(10);
53 G_act_pdr NUMBER;
54 G_act_pdr_temp VARCHAR2(150);
55 G_act_pdr_locale VARCHAR2(10);
56
57 G_horiz_plan_stmt VARCHAR2(32767);
58 G_vert_plan_stmt VARCHAR2(32767);
59 G_exc_plan_stmt VARCHAR2(32767);
60 G_act_plan_stmt VARCHAR2(32767);
61 G_common_pdr_stmt VARCHAR2(32767) := NULL;
62 G_horiz_pdr_stmt VARCHAR2(32767) := NULL;
63 G_vert_pdr_stmt VARCHAR2(32767) := NULL;
64 G_excep_pdr_stmt VARCHAR2(32767) := NULL;
65 G_act_pdr_stmt VARCHAR2(32767) := NULL;
66 G_header_stmt VARCHAR2(32767) := NULL;
67
68 invalid_parameter EXCEPTION;
69
70
71 -- vpedarla Bug: 8363786
72 v_min_cutoff_date DATE;
73 v_hour_cutoff_date DATE;
74 v_daily_cutoff_date DATE;
75 v_weekly_cutoff_date DATE;
76 v_period_cutoff_date DATE;
77 v_err_mesg VARCHAR2(32767);
78 v_weekly_start_date DATE ;
79 v_period_start_date DATE ;
80 -- vpedarla Bug: 8363786 end
81
82
83 -- Vpedarla bug 9366921 .
84 Items_count NUMBER;
85
86 Horiz_details_count NUMBER ; -- Bug: 9265463 Vpedarla
87
88 PROCEDURE create_pdr
89 (
90 errbuf OUT NOCOPY VARCHAR2,
91 retcode OUT NOCOPY VARCHAR2,
92 p_inst_id IN NUMBER,
93 p_org_id IN NUMBER,
94 p_plan_id IN NUMBER,
95 p_plan_org IN NUMBER,
96 p_start_date IN VARCHAR2,
97 p_day_bucket IN NUMBER,
98 -- p_day_bckt_cutoff_dt IN VARCHAR2,
99 p_plan_day_bckt_cutoff_dt IN VARCHAR2,
100 p_week_bucket IN NUMBER,
101 -- p_week_bckt_cutoff_dt IN VARCHAR2,
102 p_plan_week_bckt_cutoff_dt IN VARCHAR2,
103 p_period_bucket IN NUMBER,
104 p_fsort IN NUMBER,
105 p_ssort IN NUMBER,
106 p_tsort IN NUMBER,
107 p_ex_typ IN NUMBER,
108 p_plnr_low IN VARCHAR2,
109 p_plnr_high IN VARCHAR2,
110 p_byr_low IN VARCHAR2,
111 p_byr_high IN VARCHAR2,
112 p_itm_low IN VARCHAR2,
113 p_itm_high IN VARCHAR2,
114 p_cat_set_id IN NUMBER,
115 p_category_low IN VARCHAR2,
116 p_category_high IN VARCHAR2,
117 p_abc_class_low IN VARCHAR2,
118 p_abc_class_high IN VARCHAR2,
119 p_cutoff_date IN VARCHAR2,
120 p_incl_items_no_activity IN VARCHAR2, -- Bug: 8486531 Vpedarla
121 p_comb_pdr IN NUMBER,
122 p_comb_pdr_place IN VARCHAR2, -- Added
123 p_comb_comm_pdr IN VARCHAR2, -- Added
124 p_comb_pdr_temp IN VARCHAR2,
125 p_comb_pdr_locale IN VARCHAR2,
126 p_horiz_pdr IN NUMBER,
127 p_horiz_pdr_place IN VARCHAR2, -- Added
128 p_horiz_pdr_temp IN VARCHAR2,
129 p_horiz_pdr_locale IN VARCHAR2,
130 p_vert_pdr IN NUMBER,
131 p_vert_pdr_place IN VARCHAR2, -- Added
132 p_vert_pdr_temp IN VARCHAR2,
133 p_vert_pdr_locale IN VARCHAR2,
134 p_excep_pdr IN NUMBER,
135 p_excep_pdr_place IN VARCHAR2, -- Added
136 p_excep_pdr_temp IN VARCHAR2,
137 p_excep_pdr_locale IN VARCHAR2,
138 p_act_pdr IN NUMBER,
139 p_act_pdr_place IN VARCHAR2, -- Added
140 p_act_pdr_temp IN VARCHAR2,
141 p_act_pdr_locale IN VARCHAR2
142 ) IS
143
144
145 -- Bug: 9458217 Vpedarla Plan_id is used
146 CURSOR org_select(C_plan_id in NUMBER ,C_inst_id in NUMBER , C_plan_org in NUMBER ) is
147 SELECT organization_id FROM msc_plan_organizations
148 WHERE plan_id = C_plan_id
149 AND sr_instance_id = C_inst_id
150 AND (C_plan_org = -999 or C_plan_org = organization_id ) ;
151
152 BEGIN
153
154 gmp_debug_message(' Into GMP_PLNG_DTL_REPORT_PKG.create_pdr ');
155
156 split_report := NVL(FND_PROFILE.VALUE('GMP_SPLIT_PDR_BY_ORGANIZATION'),'N'); -- Bug: 9458217 Vpedarla
157
158 scale_report := NVL(FND_PROFILE.VALUE('GMP_SCALE_PDR'),'N'); -- Bug: 9265463 Vpedarla
159
160 Horiz_details_count := 0; -- Bug: 9265463
161
162 retcode := 0;
163 G_inst_id := p_inst_id ;
164 G_org_id := p_org_id ;
165 -- G_plan_name := P_plan_name ;
166 G_plan_id := p_plan_id ;
167 G_plan_org := p_plan_org ;
168 G_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS') ;
169 G_day_bucket := NVL(p_day_bucket,0) ; -- 7451619 Rajesh
170 -- G_day_bckt_cutoff_dt := TO_DATE(p_day_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
171 G_plan_day_bckt_cutoff_dt := TO_DATE(p_plan_day_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
172 G_week_bucket := NVL(p_week_bucket,0) ; -- 7451619 Rajesh
173 -- G_week_bckt_cutoff_dt := TO_DATE(p_week_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
174 G_plan_week_bckt_cutoff_dt := TO_DATE(p_plan_week_bckt_cutoff_dt, 'YYYY/MM/DD HH24:MI:SS') ;
175 G_period_bucket := NVL(p_period_bucket,0) ;
176 G_fsort := p_fsort ;
177 G_ssort := p_ssort ;
178 G_tsort := p_tsort ;
179 G_ex_typ := p_ex_typ ;
180 G_plnr_low := p_plnr_low ;
181 G_plnr_high := p_plnr_high ;
182 G_byr_low := p_byr_low ;
183 G_byr_high := p_byr_high ;
184 G_itm_low := p_itm_low ;
185 G_itm_high := p_itm_high ;
186 G_cat_set_id := nvl(p_cat_set_id,0) ;
187 G_category_low := p_category_low ;
188 G_category_high := p_category_high ;
189 G_abc_class_low := p_abc_class_low ;
190 G_abc_class_high := p_abc_class_high ;
191 G_cutoff_date := TO_DATE(p_cutoff_date, 'YYYY/MM/DD HH24:MI:SS') ;
192 g_incl_items_no_activity := NVL(to_number(p_incl_items_no_activity) , 1 );
193 G_comb_pdr := NVL(p_comb_pdr,2) ;
194 G_comb_pdr_temp := p_comb_pdr_temp ;
195 G_comb_pdr_locale := p_comb_pdr_locale ;
196 G_horiz_pdr := NVL(p_horiz_pdr,2) ;
197 G_horiz_pdr_temp := p_horiz_pdr_temp ;
198 G_horiz_pdr_locale := p_horiz_pdr_locale ;
199 G_vert_pdr := NVL(p_vert_pdr,2) ;
200 G_vert_pdr_temp := p_vert_pdr_temp ;
201 G_vert_pdr_locale := p_vert_pdr_locale ;
202 G_excep_pdr := NVL(p_excep_pdr,2) ;
203 G_excep_pdr_temp := p_excep_pdr_temp ;
204 G_excep_pdr_locale := p_excep_pdr_locale ;
205 G_act_pdr := NVL(p_act_pdr,2) ;
206 G_act_pdr_temp := p_act_pdr_temp ;
207 G_act_pdr_locale := p_act_pdr_locale ;
208
209
210 -- Bug: 8363786 Vpedarla
211 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Before msc_snapshot_pk.get_bucket_cutoff_dates ');
212
213 msc_snapshot_pk.get_bucket_cutoff_dates(
214 G_plan_id , -- p_plan_id IN NUMBER,
215 G_org_id , -- p_org_id IN NUMBER,
216 G_inst_id , --v_sr_instance_id, -- IN NUMBER,
217 G_start_date , --v_plan_start_date, -- IN DATE,
218 to_date(null), --p_plan_completion_date IN DATE,
219 0, --p_min_cutoff_bucket IN number,
220 0, --p_hour_cutoff_bucket IN number,
221 G_day_bucket , --v_daily_cutoff_bucket, -- IN number,
222 G_week_bucket , --v_weekly_cutoff_bucket, -- IN number,
223 G_period_bucket , --v_period_cutoff_bucket, -- IN number,
224 v_min_cutoff_date, -- OUT DATE,
225 v_hour_cutoff_date, -- OUT DATE,
226 v_daily_cutoff_date, -- OUT DATE,
227 v_weekly_cutoff_date, -- OUT DATE,
228 v_period_cutoff_date, -- OUT DATE,
229 v_err_mesg -- OUT VARCHAR2
230 ) ;
231
232 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After msc_snapshot_pk.get_bucket_cutoff_dates ');
233 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_min_cutoff_date: '||TO_CHAR(v_min_cutoff_date,'DD-MON-YYYY'));
234 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_hour_cutoff_date: '||TO_CHAR(v_hour_cutoff_date,'DD-MON-YYYY'));
235 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_daily_cutoff_date: '||TO_CHAR(v_daily_cutoff_date,'DD-MON-YYYY'));
236 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_weekly_cutoff_date: '||TO_CHAR(v_weekly_cutoff_date,'DD-MON-YYYY'));
237 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_period_cutoff_date: '||TO_CHAR(v_period_cutoff_date,'DD-MON-YYYY'));
238 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_err_mesg: '||v_err_mesg);
239
240 select min(cal.week_start_date)
241 into v_weekly_start_date
242 from msc_cal_week_start_dates cal , msc_trading_partners mtp
243 where cal.exception_set_id = mtp.calendar_exception_set_id
244 and cal.calendar_code = mtp.calendar_code
245 and cal.week_start_date >= trunc(v_daily_cutoff_date)
246 and cal.sr_instance_id = G_inst_id
247 and mtp.sr_tp_id = G_org_id
248 and mtp.partner_type = 3
249 and cal.sr_instance_id = mtp.sr_instance_id ;
250
251 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_weekly_start_date: '||TO_CHAR(v_weekly_start_date,'DD-MON-YYYY'));
252
253 select min(cal.period_start_date)
254 into v_period_start_date
255 from msc_period_start_dates cal , msc_trading_partners mtp
256 where cal.exception_set_id = mtp.calendar_exception_set_id
257 and cal.calendar_code = mtp.calendar_code
258 and cal.period_start_date >= nvl(trunc(v_weekly_cutoff_date),trunc(v_daily_cutoff_date))
259 and cal.sr_instance_id = G_inst_id
260 and mtp.sr_tp_id = G_org_id
261 and mtp.partner_type = 3
262 and cal.sr_instance_id = mtp.sr_instance_id ;
263
264 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' v_period_start_date: '||TO_CHAR(v_period_start_date,'DD-MON-YYYY'));
265
266 if G_week_bucket = 0 and G_period_bucket = 0 and G_day_bucket = 0 then
267 G_day_bckt_cutoff_dt := G_start_date ;
268 G_week_bckt_cutoff_dt := NULL;
269 elsif G_week_bucket = 0 and G_period_bucket = 0 then
270 G_day_bckt_cutoff_dt := v_daily_cutoff_date ;
271 G_week_bckt_cutoff_dt := NULL;
272 elsif G_day_bucket = 0 and G_week_bucket = 0 then
273 -- G_day_bckt_cutoff_dt := v_period_start_date; /* Bug: 8447261 Vpedarla */
274 G_day_bckt_cutoff_dt := G_start_date ;
275 G_week_bckt_cutoff_dt := v_period_start_date;
276 elsif G_day_bucket = 0 and G_period_bucket = 0 then
277 -- G_day_bckt_cutoff_dt := v_weekly_start_date; /* Bug: 8447261 Vpedarla */
278 G_day_bckt_cutoff_dt := G_start_date ;
279 G_week_bckt_cutoff_dt := v_weekly_cutoff_date + 1 ; -- bug: 8447261
280 else
281 /* Bug: 8447261 Vpedarla added the below if condition */
282 if G_day_bucket = 0 THEN
283 G_day_bckt_cutoff_dt := G_start_date ;
284 ELSE
285 G_day_bckt_cutoff_dt := v_weekly_start_date ;
286 END IF;
287 G_week_bckt_cutoff_dt := v_period_start_date ;
288 end if;
289 -- Bug: 8363786 Vpedarla end
290
291
292 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Calling GMP_PLNG_DTL_REPORT_PKG with values ');
293 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_inst_id '||to_char(G_inst_id));
294 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_org_id '||to_char(G_org_id));
295 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_name '||G_plan_name);
296 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_id '||to_char(G_plan_id));
297 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_org '||to_char(G_plan_org));
298 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_start_date '||TO_CHAR(G_start_date,'DD-MON-YYYY'));
299 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_day_bucket '||to_char(G_day_bucket));
300 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_day_bckt_cutoff_dt '||TO_CHAR(G_day_bckt_cutoff_dt,'DD-MON-YYYY'));
301 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_day_bckt_cutoff_dt '||TO_CHAR(G_plan_day_bckt_cutoff_dt,'DD-MON-YYYY'));
302 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_week_bucket '||to_char(G_week_bucket));
303 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_week_bckt_cutoff_dt '||TO_CHAR(G_week_bckt_cutoff_dt,'DD-MON-YYYY'));
304 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plan_week_bckt_cutoff_dt '||TO_CHAR(G_plan_week_bckt_cutoff_dt,'DD-MON-YYYY'));
305 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_period_bucket '||to_char(G_period_bucket));
306 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fsort '||to_char(G_fsort));
307 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ssort '||to_char(G_ssort));
308 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tsort '||to_char(G_tsort));
309 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ex_typ '||to_char(G_ex_typ));
310 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plnr_low '||G_plnr_low);
311 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_plnr_high '||G_plnr_high);
312 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_byr_low '||G_byr_low);
313 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_byr_high '||G_byr_high);
314 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_itm_low '||G_itm_low);
315 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_itm_high '||G_itm_high);
316 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_cat_set_id '||to_char(G_cat_set_id));
317 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_low '||G_category_low);
318 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_high '||G_category_high);
319 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_abc_class_low '||G_abc_class_low);
320 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_abc_class_high '||G_abc_class_high);
321 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_cutoff_date '||TO_CHAR(G_cutoff_date,'DD-MON-YYYY'));
322 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_incl_items_no_activity '||G_incl_items_no_activity);
323 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr '||to_char(G_comb_pdr));
324 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr_temp '||G_comb_pdr_temp);
325 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_comb_pdr_locale '||G_comb_pdr_locale);
326 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr '||to_char(G_vert_pdr));
327 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr_temp '||G_vert_pdr_temp);
328 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_vert_pdr_locale '||G_vert_pdr_locale);
329 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr '||to_char(G_horiz_pdr));
330 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr_temp '||G_horiz_pdr_temp);
331 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_horiz_pdr_locale '||G_horiz_pdr_locale);
332 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr '||to_char(G_excep_pdr));
333 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr_temp '||G_excep_pdr_temp);
334 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_excep_pdr_locale '||G_excep_pdr_locale);
335 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr '||to_char(G_act_pdr));
336 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr_temp '||G_act_pdr_temp);
337 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_act_pdr_locale '||G_act_pdr_locale);
338
339 /* Bug 5708728 with the new new parameters to the report when
340 combined pdr is submitted the four individual report indicators are either
341 NULL or 2 thereby not printing information
342 Changing them to 1 i.e. YES - this also means if combined PDR is
343 submitted you can NOT turn off printing of any of the individual
344 information */
345 IF G_comb_pdr = 1 THEN
346 G_horiz_pdr := 1 ;
347 G_vert_pdr := 1 ;
348 G_excep_pdr := 1 ;
349 G_act_pdr := 1 ;
350 END IF ;
351
352 validate_parameters;
353
354 IF split_report = 'Y' THEN
355 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Splitting the Planning detail report on Organization basis ');
356 END IF;
357
358 -- Bug: 9458217 Vpedarla setup
359 OPEN org_select(G_plan_id, G_inst_id, p_plan_org);
360 LOOP
361 FETCH org_select into G_plan_org ;
362 EXIT WHEN org_select%NOTFOUND ;
363
364 IF split_report <> 'Y' THEN
365 G_plan_org := p_plan_org ;
366 ELSE
367 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Generating report for Org id '||G_plan_org);
368 END IF;
369
370 insert_items;
371
372 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After insert_items Items_count: '||Items_count);
373
374 -- INSERT INTO pdr_items_gtmp SELECT * FROM gmp_pdr_items_gtmp;
375
376 -- Vpedarla bug: 9366921
377 If Items_count > 0 THEN
378
379 -- Bug 9338193 Vpedarla Added the below condition.
380 IF G_horiz_pdr = 1 THEN
381
382 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Calling gmp_horizontal_pdr_pkg.populate_horizontal_plan '||
383 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Calling populate horizontal plan '||
384 ' with parameters : G_inst_id - '||to_char(G_inst_id)||' G_org_id - '||to_char(G_org_id)||
385 ' G_plan_id - '|| to_char(G_plan_id) ||
386 ' G_day_bckt_cutoff_dt - '||to_char(G_day_bckt_cutoff_dt, 'MM-DD-YYYY HH:MI:SS')||
387 ' G_week_bckt_cutoff_dt - '||to_char(G_week_bckt_cutoff_dt, 'MM-DD-YYYY HH:MI:SS')||
388 ' G_period_bucket - '||to_char(G_period_bucket));
389
390 -- Bug: 8486531 Vpedarla added new parameter G_incl_items_no_activity to populate_horizontal_plan procedure call.
391 gmp_horizontal_pdr_pkg.populate_horizontal_plan (G_inst_id, G_org_id, G_plan_id,
392 G_day_bckt_cutoff_dt, G_week_bckt_cutoff_dt, G_period_bucket, G_incl_items_no_activity);
393
394 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After gmp_horizontal_pdr_pkg.populate_horizontal_plan ');
395 -- INSERT INTO horizontal_pdr_gtmp SELECT * FROM gmp_horizontal_pdr_gtmp;
396
397 SELECT count(*) into Horiz_details_count FROM gmp_horizontal_pdr_gtmp;
398 gmp_debug_message(' Horiz_details_count = '|| Horiz_details_count );
399
400 ELSE
401
402 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' Skipping populate horizontal plan ');
403
404 END IF;
405 -- end of Bug 9338193.
406
407 IF G_horiz_pdr = 1 THEN
408 horiz_plan_stmt;
409 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After horiz plan stmt ');
410 END IF;
411
412 IF G_vert_pdr = 1 THEN
413 vert_plan_stmt;
414 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After vert plan stmt ');
415 END IF;
416
417 IF G_excep_pdr = 1 THEN
418 item_exception_stmt;
419 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After item exception stmt ');
420 END IF ;
421
422 IF G_act_pdr = 1 THEN
423 item_action_stmt;
424 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After item action stmt ');
425 END IF ;
426
427 generate_xml;
428 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' After generate xml ');
429
430 ELSE
431
432 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' No Items are present for the given report parameters. ');
433
434 END IF ;
435 -- Vpedarla end of bug: 9366921
436
437 IF split_report <> 'Y' THEN
438 EXIT;
439 END IF;
440
441 END LOOP ;
442 Close org_select;
443 -- Bug: 9458217 Vpedarla end
444
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Error in package GMP_PLNG_DTL_REPORT_PKG.CREATE_PDR - '|| sqlerrm);
449 RAISE;
450 END create_pdr;
451
452 PROCEDURE validate_parameters IS
453 BEGIN
454 IF G_comb_pdr = 1 THEN
455 IF G_comb_pdr_temp IS NULL OR G_comb_pdr_locale IS NULL THEN
456 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Combined Planning Detail Report ');
457 RAISE invalid_parameter;
458 END IF;
459 ELSIF G_comb_pdr = 2 THEN
460 IF G_horiz_pdr = 1 THEN
461 IF G_horiz_pdr_temp IS NULL OR G_horiz_pdr_locale IS NULL THEN
462 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Horizontal Planning Detail Report ');
463 RAISE invalid_parameter;
464 END IF;
465 END IF;
466 IF G_vert_pdr = 1 THEN
467 IF G_vert_pdr_temp IS NULL OR G_vert_pdr_locale IS NULL THEN
468 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Vertical Planning Detail Report ');
469 RAISE invalid_parameter;
470 END IF;
471 END IF;
472 IF G_excep_pdr = 1 THEN
473 IF G_excep_pdr_temp IS NULL OR G_excep_pdr_locale IS NULL THEN
474 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Exception Planning Detail Report ');
475 RAISE invalid_parameter;
476 END IF;
477 END IF;
478 IF G_act_pdr = 1 THEN
479 IF G_act_pdr_temp IS NULL OR G_act_pdr_locale IS NULL THEN
480 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please specify the Template and Locale for Action Planning Detail Report ');
481 RAISE invalid_parameter;
482 END IF;
483 END IF;
484 END IF;
485 EXCEPTION
486 WHEN invalid_parameter THEN
487 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in validate_parameters: Invalid Parameters submitted ');
488 RAISE;
489 END validate_parameters;
490
491 PROCEDURE insert_items IS
492
493 x_select VARCHAR2(20000);
494 cur_item NUMBER;
495 X_row_count NUMBER;
496
497 BEGIN
498
499 x_select := ' INSERT INTO gmp_pdr_items_gtmp ( '||
500 ' organization_code, '||
501 ' item_name, '||
502 ' category_name, '||
503 ' planner_code, '||
504 ' buyer_name, '||
505 ' abc_class_name, '||
506 ' inventory_item_id, '||
507 ' organization_id, '||
508 ' base_item_id, '||
509 ' standard_cost, '||
510 ' calculate_atp, '||
511 ' wip_supply_type, '||
512 ' bom_item_type '||
513 ' ) '||
514 ' SELECT DISTINCT '||
515 ' msi.organization_code, '||
516 ' msi.item_name, '||
517 ' mic.category_name, '||
518 ' msi.planner_code, '||
519 ' msi.buyer_name, '||
520 ' msi.abc_class_name, '||
521 ' msi.inventory_item_id, '||
522 ' msi.organization_id, '||
523 ' msi.base_item_id, '||
524 ' msi.standard_cost, '||
525 ' msi.calculate_atp, '||
526 ' msi.wip_supply_type, '||
527 ' msi.bom_item_type '||
528 ' FROM '||
529 ' msc_system_items msi '||
530 ' , msc_item_categories mic ';
531 IF G_ex_typ IS NOT NULL THEN
532 x_select := x_select || ' , msc_item_exceptions mie';
533 END IF;
534
535 x_select := x_select || ' WHERE '||
536 ' msi.sr_instance_id = :inst_id '||
537 ' AND msi.plan_id = :plan_id '||
538 ' AND mic.inventory_item_id = msi.inventory_item_id '||
539 ' AND mic.organization_id = msi.organization_id '||
540 ' AND mic.sr_instance_id = msi.sr_instance_id '||
541 ' AND mic.category_set_id = :cat_set_id ' ;
542 IF G_plan_org <> -999 THEN
543 x_select := x_select || ' AND msi.organization_id = :plan_org ';
544 ELSE
545 x_select := x_select || ' AND msi.organization_id IN (SELECT organization_id FROM msc_plan_organizations '||
546 ' WHERE plan_id = :plan_id AND '||
547 ' sr_instance_id = :inst_id) ';
548 END IF;
549 IF G_category_low IS NOT NULL THEN
550 x_select := x_select || ' AND mic.category_name >= :category_low ';
551 END IF;
552 IF G_category_high IS NOT NULL THEN
553 x_select := x_select || ' AND mic.category_name <= :category_high ';
554 END IF;
555 IF G_ex_typ IS NOT NULL THEN
556 x_select := x_select || ' AND msi.inventory_item_id = mie.inventory_item_id '||
557 ' AND msi.organization_id = mie.organization_id '||
558 ' AND msi.sr_instance_id = mie.sr_instance_id '||
559 ' AND mie.exception_type = :exception_type ';
560 END IF;
561 IF G_plnr_low IS NOT NULL THEN
562 x_select := x_select || ' AND msi.planner_code >= :planner_low ';
563 END IF;
564 IF G_plnr_high IS NOT NULL THEN
565 x_select := x_select || ' AND msi.planner_code <= :planner_high ';
566 END IF;
567 IF G_byr_low IS NOT NULL THEN
568 x_select := x_select || ' AND msi.buyer_name >= :buyer_low ';
569 END IF;
570 IF G_byr_high IS NOT NULL THEN
571 x_select := x_select || ' AND msi.buyer_name <= :buyer_high ';
572 END IF;
573 IF G_abc_class_low IS NOT NULL THEN
574 x_select := x_select || ' AND msi.abc_class_name >= :abc_class_low ';
575 END IF;
576 IF G_abc_class_high IS NOT NULL THEN
577 x_select := x_select || ' AND msi.abc_class_name <= :abc_class_high ';
578 END IF;
579 IF G_itm_low IS NOT NULL THEN
580 x_select := x_select || ' AND msi.item_name >= :item_name_low ';
581 END IF;
582 IF G_itm_high IS NOT NULL THEN
583 x_select := x_select || ' AND msi.item_name <= :item_name_high ';
584 END IF;
585
586 /*
587 IF G_fsort IS NOT NULL THEN
588 x_select := x_select || ' ORDER BY :first_sort ';
589 END IF;
590 IF G_ssort IS NOT NULL THEN
591 x_select := x_select || ' , :second_sort ';
592 END IF;
593 IF G_tsort IS NOT NULL THEN
594 x_select := x_select || ' , :third_sort ';
595 END IF;
596 IF G_fsort IS NOT NULL AND G_ssort IS NOT NULL AND G_tsort IS NOT NULL THEN
597 x_select := x_select || ' ORDER BY msi.inventory_item_id, msi.organization_id ';
598 END IF;
599 */
600
601 cur_item := dbms_sql.open_cursor;
602 dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
603
604 dbms_sql.bind_variable(cur_item, ':inst_id', G_inst_id);
605 dbms_sql.bind_variable(cur_item, ':plan_id', G_plan_id);
606 -- dbms_sql.bind_variable(cur_item, ':plan_org', G_plan_org);
607 dbms_sql.bind_variable(cur_item, ':cat_set_id', G_cat_set_id);
608
609 IF G_plan_org <> -999 THEN
610 dbms_sql.bind_variable(cur_item, ':plan_org', G_plan_org);
611 END IF;
612
613 IF G_category_low IS NOT NULL THEN
614 dbms_sql.bind_variable(cur_item, ':category_low', G_category_low);
615 END IF;
616 IF G_category_high IS NOT NULL THEN
617 dbms_sql.bind_variable(cur_item, ':category_high', G_category_high);
618 END IF;
619
620 IF G_ex_typ IS NOT NULL THEN
621 dbms_sql.bind_variable(cur_item, ':exception_type', G_ex_typ);
622 END IF;
623
624 IF G_plnr_low IS NOT NULL THEN
625 dbms_sql.bind_variable(cur_item, ':planner_low', G_plnr_low);
626 END IF;
627 IF G_plnr_high IS NOT NULL THEN
628 dbms_sql.bind_variable(cur_item, ':planner_high', G_plnr_high);
629 END IF;
630
631 IF G_byr_low IS NOT NULL THEN
632 dbms_sql.bind_variable(cur_item, ':buyer_low', G_byr_low);
633 END IF;
634 IF G_byr_high IS NOT NULL THEN
635 dbms_sql.bind_variable(cur_item, ':buyer_high', G_byr_high);
636 END IF;
637
638 IF G_abc_class_low IS NOT NULL THEN
639 dbms_sql.bind_variable(cur_item, ':abc_class_low', G_abc_class_low);
640 END IF;
641 IF G_abc_class_high IS NOT NULL THEN
642 dbms_sql.bind_variable(cur_item, ':abc_class_high', G_abc_class_high);
643 END IF;
644
645 IF G_itm_low IS NOT NULL THEN
646 dbms_sql.bind_variable(cur_item, ':item_name_low', G_itm_low);
647 END IF;
648 IF G_itm_high IS NOT NULL THEN
649 dbms_sql.bind_variable(cur_item, ':item_name_high', G_itm_high);
650 END IF;
651 /*
652 IF G_fsort IS NOT NULL THEN
653 dbms_sql.bind_variable(cur_item, ':first_sort', G_fsort);
654 END IF;
655 IF G_ssort IS NOT NULL THEN
656 dbms_sql.bind_variable(cur_item, ':second_sort', G_ssort);
657 END IF;
658 IF G_tsort IS NOT NULL THEN
659 dbms_sql.bind_variable(cur_item, ':third_sort', G_tsort);
660 END IF;
661 */
662
663 FND_FILE.PUT_LINE(FND_FILE.LOG,' Item query - '||x_select );
664
665 X_row_count := dbms_sql.EXECUTE (cur_item);
666
667 -- Vpedarla bug: 9366921
668 Items_count := X_row_count ;
669
670 FND_FILE.PUT_LINE(FND_FILE.LOG,'Num of rows in gmp_pdr_items_gtmp '||to_char(X_row_count));
671
672 dbms_sql.close_cursor (cur_item);
673
674 EXCEPTION
675 WHEN OTHERS THEN
676 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in package GMP_PLNG_DTL_REPORT_PKG '||sqlerrm);
677 IF dbms_sql.is_open (cur_item) THEN
678 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION cur_item is Open');
679 dbms_sql.close_cursor (cur_item);
680 END IF;
681 END insert_items;
682
683
684 PROCEDURE horiz_plan_stmt IS
685
686 BEGIN
687
688 G_horiz_plan_stmt := ' SELECT '||
689 ' organization_id , '||
690 ' inventory_item_id , '||
691 ' bucket_date , '||
692 ' quantity1 , '||
693 ' quantity2 , '||
694 ' quantity3 , '||
695 ' quantity4 , '||
696 ' quantity5 , '||
697 ' quantity6 , '||
698 ' quantity7 , '||
699 ' quantity8 , '||
700 ' quantity9 , '||
701 ' quantity10 , '||
702 ' quantity11 , '||
703 ' quantity12 , '||
704 ' quantity13 , '||
705 ' quantity14 , '||
706 ' quantity15 , '||
707 ' quantity16 , '||
708 ' quantity17 , '||
709 ' quantity18 , '||
710 ' quantity19 , '||
711 -- ' quantity20 , '||
712 -- ' quantity21 , '||
713 ' quantity22 '||
714 -- ' quantity23 , '||
715 -- ' quantity24 , '||
716 -- ' quantity25 , '||
717 -- ' quantity26 , '||
718 -- ' quantity27 , '||
719 -- ' quantity28 , '||
720 -- ' quantity29 , '||
721 -- ' quantity30 , '||
722 -- ' quantity31 , '||
723 -- ' quantity32 , '||
724 -- ' quantity33 , '||
725 -- ' quantity34 , '||
726 -- ' quantity35 , '||
727 -- ' quantity36 , '||
728 -- ' quantity37 , '||
729 -- ' quantity38 , '||
730 -- ' quantity39 , '||
731 -- ' quantity40 , '||
732 -- ' quantity41 , '||
733 -- ' quantity42 , '||
734 -- ' quantity43 , '||
735 -- ' quantity44 , '||
736 -- ' quantity45 '||
737 ' FROM '||
738 ' ( SELECT '||
739 ' ghp.organization_id , '||
740 ' ghp.inventory_item_id , '||
741 ' ghp.bucket_date , '||
742 ' ghp.quantity1 , '||
743 ' ghp.quantity2 , '||
744 ' ghp.quantity3 , '||
745 ' ghp.quantity4 , '||
746 ' ghp.quantity5 , '||
747 ' ghp.quantity6 , '||
748 ' ghp.quantity7 , '||
749 ' ghp.quantity8 , '||
750 ' ghp.quantity9 , '||
751 ' ghp.quantity10 , '||
752 ' ghp.quantity11 , '||
753 ' ghp.quantity12 , '||
754 ' ghp.quantity13 , '||
755 ' ghp.quantity14 , '||
756 ' ghp.quantity15 , '||
757 ' ghp.quantity16 , '||
758 ' ghp.quantity17 , '||
759 ' ghp.quantity18 , '||
760 ' ghp.quantity19 , '||
761 -- ' ghp.quantity20 , '||
762 -- ' ghp.quantity21 , '||
763 ' ghp.quantity22 '||
764 -- ' ghp.quantity23 , '||
765 -- ' ghp.quantity24 , '||
766 -- ' ghp.quantity25 , '||
767 -- ' ghp.quantity26 , '||
768 -- ' ghp.quantity27 , '||
769 -- ' ghp.quantity28 , '||
770 -- ' ghp.quantity29 , '||
771 -- ' ghp.quantity30 , '||
772 -- ' ghp.quantity31 , '||
773 -- ' ghp.quantity32 , '||
774 -- ' ghp.quantity33 , '||
775 -- ' ghp.quantity34 , '||
776 -- ' ghp.quantity35 , '||
777 -- ' ghp.quantity36 , '||
778 -- ' ghp.quantity37 , '||
779 -- ' ghp.quantity38 , '||
780 -- ' ghp.quantity39 , '||
781 -- ' ghp.quantity40 , '||
782 -- ' ghp.quantity41 , '||
783 -- ' ghp.quantity42 , '||
784 -- ' ghp.quantity43 , '||
785 -- ' ghp.quantity44 , '||
786 -- ' ghp.quantity45 '||
787 ' FROM '||
788 ' gmp_horizontal_pdr_gtmp ghp ) ';
789
790 EXCEPTION
791 WHEN OTHERS THEN
792 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_exception_stmt '||sqlerrm);
793 END horiz_plan_stmt;
794
795 PROCEDURE vert_plan_stmt IS
796
797 BEGIN
798
799 G_vert_plan_stmt :=
800 ' SELECT /*+ INDEX(rec, MSC_SUPPLIES_N1) */ '||
801 ' organization_code, '|| /* header show */
802 ' item_name, '|| /* header show */
803 ' category_name, '|| /* header show */
804 ' planner_code, '|| /* header show */
805 ' buyer_name, '|| /* header show */
806 ' abc_class_name, '|| /* header show */
807 ' planning_group, '||
808 ' order_type, '||/* detail show */
809 ' order_number, '||/* detail show */
810 ' activity_date, '||/* detail show */
811 ' quantity_rate, '||/* detail show */
812 ' old_schd_date, '||
813 ' order_placement_date, '||
814 ' new_schedule_date, '||
815 ' new_doc_date, '||
816 ' new_wip_start_date, '||
817 ' implement_as_id, '||
818 ' firm_date, '||
819 ' firm_qty, '||
820 ' wip_qty, '||
821 ' compression_days, '||
822 ' using_assembly_item_name, '||
823 ' designator, '||
824 ' source_org, '||
825 ' supplier_name, '||
826 ' plan_name, '||
827 ' plan_id, '||
828 ' organization_id, '||
829 ' sr_instance_id, '||
830 ' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
831 ' inventory_item_id, '||
832 ' supplier_id, '||
833 ' implement_as, '||
834 ' implemented_qty '||
835 ' FROM '||
836 '( SELECT '||
837 ' gpi.organization_code organization_code, '|| /* header show */
838 ' gpi.item_name item_name, '|| /* header show */
839 ' gpi.category_name category_name, '|| /* header show */
840 ' gpi.planner_code planner_code, '|| /* header show */
841 ' gpi.buyer_name buyer_name, '|| /* header show */
842 ' gpi.abc_class_name abc_class_name, '|| /* header show */
843 ' sup.planning_group planning_group, '||
844 ' l1.meaning order_type, '||/* detail show */
845 ' DECODE (sup.order_type, '||
846 ' 5, TO_CHAR (sup.transaction_id), '||
847 ' sup.order_number '||
848 ' ) order_number, '||/* detail show */
849 ' cal.calendar_date activity_date, '||/* detail show */
850 ' NVL (sup.daily_rate, sup.new_order_quantity) quantity_rate, '||/* detail show */
851 ' sup.old_schedule_date old_schd_date, '||
852 ' sup.new_order_placement_date order_placement_date, '||
853 ' sup.new_schedule_date new_schedule_date, '||
854 ' sup.new_dock_date new_doc_date, '||
855 ' sup.new_wip_start_date new_wip_start_date, '||
856 ' sup.implement_as implement_as_id, '||
857 ' sup.firm_date firm_date, '||
858 ' sup.firm_quantity firm_qty, '||
859 ' NVL (sup.daily_rate, sup.new_order_quantity) - NVL (sup.quantity_in_process, 0) wip_qty, '||
860 ' sup.schedule_compress_days compression_days, '||
861 ' TO_CHAR (NULL) using_assembly_item_name, '||
862 ' msc_get_name.designator (sup.schedule_designator_id) designator, '||
863 -- ' msc_get_name.designator (sup.schedule_designator_id), /* Since this column is selected twice, so removing one instance. */
864 ' msc_get_name.org_code (sup.source_organization_id, '||
865 ' sup.source_sr_instance_id '||
866 ' ) source_org, '||
867 ' msc_get_name.supplier (DECODE (sup.plan_id, '||
868 ' -1, sup.supplier_id, '||
869 ' DECODE (sup.order_type, '||
870 ' 1, sup.supplier_id, '||
871 ' 2, sup.supplier_id, '||
872 ' sup.source_supplier_id '||
873 ' ) '||
874 ' ) '||
875 ' ) supplier_name, '||
876 ' mp.compile_designator plan_name, '||
877 ' sup.plan_id plan_id, '||
878 ' sup.organization_id organization_id, '||
879 ' sup.sr_instance_id sr_instance_id, '||
880 ' '||G_cat_set_id||' cat_set_id, ' ||-- mic.category_set_id, /* Global Var */
881 ' sup.inventory_item_id inventory_item_id, '||
882 ' sup.supplier_id supplier_id, '||
883 ' DECODE (sup.implement_as, '||
884 ' NULL, NULL, '||
885 ' msc_get_name.lookup_meaning ('||''''||'MRP_WORKBENCH_IMPLEMENT_AS'||''''||', '||
886 ' sup.implement_as '||
887 ' ) '||
888 ' ) implement_as, '||
889 ' DECODE (sup.disposition_status_type, '||
890 ' 2, 0.0, '||
891 ' NVL (sup.daily_rate, sup.new_order_quantity) '||
892 ' ) implemented_qty '||
893 ' FROM '||
894 -- msc_item_categories mic,
895 -- ' msc_system_items msi,
896 ' gmp_pdr_items_gtmp gpi, '||
897 ' msc_supplies sup, '||
898 ' msc_trading_partners mtp, '||
899 ' msc_calendar_dates cal, '||
900 ' mfg_lookups l1, '||
901 ' msc_plans mp '||
902 ' WHERE cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
903 ' AND NVL (TRUNC (sup.last_unit_completion_date), '||
904 ' TRUNC (sup.new_schedule_date) '||
905 ' ) '||
906 ' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
907 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
908 ' AND cal.calendar_code = mtp.calendar_code '||
909 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
910 ' AND mtp.sr_tp_id = sup.organization_id '||
911 ' AND mtp.sr_instance_id = sup.sr_instance_id '||
912 ' AND mtp.partner_type = 3 '||
913 ' AND sup.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
914 ' AND sup.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
915 ' AND sup.organization_id = gpi.organization_id '||
916 ' AND sup.inventory_item_id = gpi.inventory_item_id '||
917 ' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
918 ' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
919 ' AND l1.lookup_code = sup.order_type '||
920 ' AND mp.plan_id = sup.plan_id '||
921 ' 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))) '||
922 /* Global Var */
923 ' UNION ALL '||
924 ' SELECT '||
925 ' gpi.organization_code organization_code, '|| /* header show */
926 ' gpi.item_name item_name, '|| /* header show */
927 ' gpi.category_name category_name, '|| /* header show */
928 ' gpi.planner_code planner_code, '|| /* header show */
929 ' gpi.buyer_name buyer_name, '|| /* header show */
930 ' gpi.abc_class_name abc_class_name, '|| /* header show */
931 ' dem.planning_group planning_group, '||
932 ' l1.meaning order_type, '||/* detail show */
933 ' NVL (dem.order_number, '||
934 ' DECODE (dem.origination_type, '||
935 ' 29, msc_get_name.scenario_designator (dem.forecast_set_id, '||
936 ' dem.plan_id, '||
937 ' dem.organization_id, '||
938 ' dem.sr_instance_id '||
939 ' ), '||
940 ' msc_get_name.designator (dem.schedule_designator_id) '||
941 ' ) '||
942 ' ) order_number , '||/* detail show */
943 ' cal.calendar_date activity_date , '||/* detail show */
944 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) quantity_rate, '||/* detail show */
945 ' dem.old_demand_date old_schd_date, '||
946 ' TO_DATE (NULL) order_placement_date, '||
947 ' dem.using_assembly_demand_date new_schedule_date, '||
948 ' TO_DATE (NULL) new_doc_date, '||
949 ' TO_DATE (NULL) new_wip_start_date, '||
950 ' TO_NUMBER (NULL)implement_as_id, '||
951 ' dem.firm_date firm_date, '||
952 ' dem.firm_quantity firm_qty, '||
953 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) - TO_NUMBER (NULL) wip_qty, '||
954 ' TO_NUMBER (NULL) compression_days, '||
955 ' msc_get_name.item_name (dem.using_assembly_item_id, NULL, NULL, NULL) using_assembly_item_name, '||
956 ' DECODE (dem.schedule_designator_id, '||
957 ' NULL, NULL, '||
958 ' DECODE (dem.origination_type, '||
959 ' 29, msc_get_name.forecastsetname (dem.forecast_set_id, '||
960 ' dem.plan_id, '||
961 ' dem.organization_id, '||
962 ' dem.sr_instance_id '||
963 ' ), '||
964 ' msc_get_name.designator (dem.schedule_designator_id) '||
965 ' ) '||
966 ' ) designator, '||
967 /* DECODE (dem.schedule_designator_id,
968 NULL, NULL,
969 DECODE (dem.origination_type,
970 29, msc_get_name.forecastsetname (dem.forecast_set_id,
971 dem.plan_id,
972 dem.organization_id,
973 dem.sr_instance_id
974 ),
975 msc_get_name.designator (dem.schedule_designator_id)
976 )
977 ), */ /* Since this column is selected twice, so removing one instance. */
978 ' msc_get_name.org_code (dem.source_organization_id, '||
979 ' dem.source_org_instance_id '||
980 ' ) source_org, '||
981 ' NULL supplier_name, '||
982 ' mp.compile_designator plan_name, '||
983 ' dem.plan_id plan_id, '||
984 ' dem.organization_id organization_id, '||
985 ' dem.sr_instance_id sr_instance_id, '||
986 ' '||G_cat_set_id||' cat_set_id, '||-- mic.category_set_id, /* Global Var */
987 ' dem.inventory_item_id inventory_item_id, '||
988 ' TO_NUMBER (NULL) supplier_id, '||
989 ' TO_CHAR (NULL) implement_as, '||
990 ' -NVL (dem.daily_demand_rate, dem.using_requirement_quantity) implemented_qty '||
991 ' FROM '||
992 -- msc_item_categories mic,
993 -- ' msc_system_items msi,
994 ' gmp_pdr_items_gtmp gpi, '||
995 ' msc_demands dem, '||
996 ' msc_trading_partners mtp, '||
997 ' msc_calendar_dates cal, '||
998 ' mfg_lookups l1, '||
999 ' msc_plans mp '||
1000 ' WHERE cal.calendar_date BETWEEN TRUNC (dem.using_assembly_demand_date) '||
1001 ' AND NVL (TRUNC (dem.assembly_demand_comp_date), '||
1002 ' TRUNC (dem.using_assembly_demand_date) '||
1003 ' ) '||
1004 ' AND DECODE (dem.assembly_demand_comp_date, NULL, 1, cal.seq_num) IS NOT NULL '||
1005 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
1006 ' AND cal.calendar_code = mtp.calendar_code '||
1007 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
1008 ' AND mtp.sr_tp_id = dem.organization_id '||
1009 ' AND mtp.sr_instance_id = dem.sr_instance_id '||
1010 ' AND mtp.partner_type = 3 '||
1011 ' AND dem.plan_id = '||G_plan_id||-- msi.plan_id /* Global Var */
1012 ' AND dem.sr_instance_id = '||G_inst_id||-- msi.sr_instance_id /* Global Var */
1013 ' AND dem.organization_id = gpi.organization_id '||
1014 ' AND dem.inventory_item_id = gpi.inventory_item_id '||
1015 ' AND NVL (dem.daily_demand_rate, dem.using_requirement_quantity) <> 0 '||
1016 ' AND l1.lookup_type = '||''''||'MSC_DEMAND_ORIGINATION'||''''||
1017 ' AND l1.lookup_code = dem.origination_type '||
1018 ' AND mp.plan_id = dem.plan_id '||
1019 ' 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))) )';
1020 /* Global Var */
1021
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure vert_plan_stmt '||sqlerrm);
1025 END vert_plan_stmt;
1026
1027 PROCEDURE item_exception_stmt IS
1028
1029 BEGIN
1030
1031 G_exc_plan_stmt :=
1032 ' SELECT '||
1033 ' organization_code, '||
1034 ' item_name, '||
1035 ' category_name, '|| /* header show */
1036 ' planner_code, '|| /* header show */
1037 ' buyer_name, '|| /* header show */
1038 ' abc_class_name, '|| /* header show */
1039 ' exception_id, '||
1040 ' inventory_item_id, '||
1041 ' organization_id, '||
1042 ' exception_type exception_type_dtl , '|| -- Vpedarla bug:7408259 Modified the column alias since the header statement also has same column name
1043 ' exception_type_text, '||
1044 ' due_date, '||
1045 ' quantity, '||
1046 ' from_date, '||
1047 ' to_date, '||
1048 ' lot_number, '||
1049 ' department_line_code'||
1050 ' FROM '||
1051 '( SELECT '||
1052 ' med.organization_code organization_code, '||
1053 ' gpi.item_name item_name, '||
1054 ' gpi.category_name category_name, '|| /* header show */
1055 ' gpi.planner_code planner_code, '|| /* header show */
1056 ' gpi.buyer_name buyer_name, '|| /* header show */
1057 ' gpi.abc_class_name abc_class_name, '|| /* header show */
1058 ' med.exception_id exception_id, '||
1059 ' gpi.inventory_item_id inventory_item_id, '||
1060 ' gpi.organization_id organization_id, '||
1061 ' med.exception_type exception_type, '||
1062 ' med.exception_type_text exception_type_text, '||
1063 ' med.due_date due_date, '||
1064 ' med.quantity quantity, '||
1065 ' med.from_date from_date, '||
1066 ' med.to_date to_date, '||
1067 ' med.lot_number lot_number, '||
1068 ' med.department_line_code department_line_code'||
1069 ' FROM msc_exception_details_v med, '||
1070 ' gmp_pdr_items_gtmp gpi '||
1071 ' WHERE med.plan_id = '||G_plan_id||
1072 -- Bug: 7257708 Vpedarla changed the below two lines
1073 -- ' 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) '||
1074 -- ' 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))) '||
1075 ' 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)))) '||
1076 /* Global Var */
1077 ' AND med.sr_instance_id = '||G_inst_id||
1078 ' AND med.inventory_item_id = gpi.inventory_item_id '||
1079 ' AND nvl(med.category_set_id, '||G_cat_set_id ||') = '|| G_cat_set_id ||
1080 ' AND med.organization_id = gpi.organization_id )' ;
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_exception_stmt '||sqlerrm);
1084 END item_exception_stmt;
1085
1086 PROCEDURE item_action_stmt IS
1087
1088 BEGIN
1089
1090 G_act_plan_stmt :=
1091 ' SELECT '||
1092 ' inventory_item_id, '||
1093 ' organization_id, '||
1094 ' item_action, '||
1095 ' order_type, '||
1096 ' order_number, '||
1097 ' activity_date, '||
1098 ' old_schedule_date, '||
1099 ' new_order_placement_date, '||
1100 ' new_schedule_date, '||
1101 ' new_dock_date, '||
1102 ' new_wip_start_date, '||
1103 ' schedule_compress_days '||
1104 ' FROM '||
1105 ' ( SELECT '||
1106 ' gpi.inventory_item_id, '||
1107 ' gpi.organization_id, '||
1108 ' DECODE '||
1109 ' ( '||
1110 ' msc_get_name.action '||
1111 ' ( '||
1112 ' '||''''||'MSC_SUPPLIES'||''''||' , '||
1113 ' gpi.bom_item_type, '||
1114 ' gpi.base_item_id, '||
1115 ' gpi.wip_supply_type, '||
1116 ' sup.order_type, '||
1117 ' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
1118 ' sup.disposition_status_type, '||
1119 ' sup.new_schedule_date, '||
1120 ' sup.old_schedule_date, '||
1121 ' sup.implemented_quantity, '||
1122 ' sup.quantity_in_process, '||
1123 ' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
1124 ' ), '||
1125 ' '||''''||'None'||''''||' , DECODE '||
1126 ' ( '||
1127 ' SIGN (sup.new_schedule_date - sup.old_schedule_date), '||
1128 ' 1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,3), '||
1129 ' -1, msc_get_name.lookup_meaning ('||''''||'MRP_ACTIONS'||''''||' ,2), '||
1130 ' '||''''||'None'||''''||
1131 ' ), '||
1132 ' msc_get_name.action '||
1133 ' ( '||
1134 ' '||''''||'MSC_SUPPLIES'||''''||' , '||
1135 ' gpi.bom_item_type, '||
1136 ' gpi.base_item_id, '||
1137 ' gpi.wip_supply_type, '||
1138 ' sup.order_type, '||
1139 ' DECODE (sup.firm_planned_type,1, 1,sup.reschedule_flag), '||
1140 ' sup.disposition_status_type, '||
1141 ' sup.new_schedule_date, '||
1142 ' sup.old_schedule_date, '||
1143 ' sup.implemented_quantity, '||
1144 ' sup.quantity_in_process, '||
1145 ' DECODE (sup.new_order_quantity,0, sup.firm_quantity,sup.new_order_quantity) '||
1146 ' ) '||
1147 ' ) item_action, '||
1148 ' l1.meaning order_type, '||
1149 ' DECODE (sup.order_type,5, TO_CHAR (sup.transaction_id),sup.order_number) order_number, '||
1150 ' cal.calendar_date activity_date, '||
1151 ' sup.old_schedule_date old_schedule_date, '||
1152 ' sup.new_order_placement_date new_order_placement_date, '||
1153 ' sup.new_schedule_date new_schedule_date, '||
1154 ' sup.new_dock_date new_dock_date, '||
1155 ' sup.new_wip_start_date new_wip_start_date, '||
1156 ' sup.schedule_compress_days schedule_compress_days '||
1157 ' FROM '||
1158 ' gmp_pdr_items_gtmp gpi, '||
1159 ' msc_supplies sup, '||
1160 ' msc_calendar_dates cal, '||
1161 ' msc_trading_partners mtp, '||
1162 ' mfg_lookups l1 '||
1163 ' WHERE '||
1164 ' cal.calendar_date BETWEEN TRUNC (sup.new_schedule_date) '||
1165 ' AND NVL (TRUNC (sup.last_unit_completion_date), '||
1166 ' TRUNC (sup.new_schedule_date) '||
1167 ' ) '||
1168 ' AND DECODE (sup.last_unit_completion_date, NULL, 1, cal.seq_num) IS NOT NULL '||
1169 ' AND cal.exception_set_id = mtp.calendar_exception_set_id '||
1170 ' AND cal.calendar_code = mtp.calendar_code '||
1171 ' AND cal.sr_instance_id = mtp.sr_instance_id '||
1172 ' AND mtp.sr_tp_id = sup.organization_id '||
1173 ' AND mtp.sr_instance_id = sup.sr_instance_id '||
1174 ' AND mtp.partner_type = 3 '||
1175 ' AND sup.plan_id = '||G_plan_id|| -- gpi.plan_id /* Global Var */
1176 ' AND sup.sr_instance_id = '||G_inst_id|| -- gpi.sr_instance_id /* Global Var */
1177 ' AND sup.organization_id = gpi.organization_id '||
1178 ' AND sup.inventory_item_id = gpi.inventory_item_id '||
1179 ' AND NVL (sup.daily_rate, sup.new_order_quantity) <> 0 '||
1180 ' AND l1.lookup_type = '||''''||'MRP_ORDER_TYPE'||''''||
1181 ' AND l1.lookup_code = sup.order_type '||
1182 ' 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))) ) ';
1183
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure item_action_stmt '||sqlerrm);
1187 END item_action_stmt;
1188
1189 PROCEDURE generate_xml IS
1190
1191 qryCtx DBMS_XMLGEN.ctxHandle;
1192 result CLOB;
1193 seq_stmt VARCHAR2(100);
1194 x_seq_num NUMBER;
1195 l_encoding VARCHAR2(20); /* B7481907 */
1196 l_xml_header VARCHAR2(100); /* B7481907 */
1197 l_offset PLS_INTEGER; /* B7481907 */
1198 temp_clob CLOB; /* B7481907 */
1199 l_ref_cur SYS_REFCURSOR; /* B7481907 */
1200 len PLS_INTEGER; /* B7481907 */
1201
1202 BEGIN
1203
1204 seq_stmt := NULL;
1205 x_seq_num := 0;
1206
1207 -- B7481907 Rajesh Patangya starts
1208 -- The following line of code ensures that XML data
1209 -- generated here uses the right encoding
1210 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1211 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
1212 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_xml_header - '||l_xml_header);
1213 -- B7481907 Rajesh Patangya starts
1214
1215 G_header_stmt := ' SELECT ' ||
1216 ' msc_get_name.org_code ('||G_org_id||', '||G_inst_id||' ) master_org, '||
1217 ' msc_get_name.instance_code ('||G_inst_id||' ) instance_code, '||
1218 -- ''''||G_plan_name||''''||' plan_name, ' ||
1219 ' gmp_plng_dtl_report_pkg.plan_name plan_name, '||
1220 ' gmp_plng_dtl_report_pkg.plan_org ('||G_plan_org||' ) plan_org, '||
1221 G_day_bucket||' day_bucket, '||
1222 G_week_bucket||' week_bucket, '||
1223 G_period_bucket||' period_bucket, ';
1224
1225 IF G_fsort IS NOT NULL THEN
1226 G_header_stmt := G_header_stmt ||
1227 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_fsort||' ) first_sort, ';
1228 ELSE
1229 G_header_stmt := G_header_stmt ||
1230 ''''||G_fsort||''''||' first_sort, ';
1231 END IF;
1232
1233 IF G_ssort IS NOT NULL THEN
1234 G_header_stmt := G_header_stmt ||
1235 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_ssort||' ) second_sort, ';
1236 ELSE
1237 G_header_stmt := G_header_stmt ||
1238 ''''||G_ssort||''''||' second_sort, ';
1239 END IF;
1240
1241 IF G_tsort IS NOT NULL THEN
1242 G_header_stmt := G_header_stmt ||
1243 ' gmp_plng_dtl_report_pkg.lookup_meaning ('||''''||'GMP_DATA_SELECT'||''''||', '||G_tsort||' ) third_sort, ';
1244 ELSE
1245 G_header_stmt := G_header_stmt ||
1246 ''''||G_tsort||''''||' third_sort, ' ;
1247 END IF;
1248
1249 IF G_ex_typ IS NOT NULL THEN
1250 G_header_stmt := G_header_stmt ||
1251 -- Bug: 7257708 Vpedarla changed the below line.
1252 -- ' msc_get_name.lookup_meaning ('||''''||'MSC_X_EXCEPTION_TYPE'||''''||', '||G_ex_typ||' ) exception_type, ';
1253 ' msc_get_name.lookup_meaning ('||''''||'MRP_EXCEPTION_CODE_TYPE'||''''||', '||G_ex_typ||' ) exception_type, ';
1254 ELSE
1255 -- Vpedarla bug: 7408259 Modified the below code
1256 /* G_header_stmt := G_header_stmt ||
1257 ''''||G_ex_typ||''''||' exception_type, ' ; */
1258 G_header_stmt := G_header_stmt ||
1259 ''''||'ALL'||''''||' exception_type, ' ;
1260 END IF;
1261
1262 G_header_stmt := G_header_stmt ||''''||G_plnr_low||''''||' planner_low, ' ||
1263 ''''||G_plnr_high||''''||' planner_high, ' ||
1264 ''''||G_byr_low||''''||' buyer_low, ' ||
1265 ''''||G_byr_high||''''||' buyer_high, ' ||
1266 ''''||G_itm_low||''''||' item_low, ' ||
1267 ''''||G_itm_high||''''||' item_high, ' ||
1268 ' gmp_plng_dtl_report_pkg.category_set_name ('||G_cat_set_id||' ) category_set_name, '||
1269 /* ToDo : define the function category_set_name */
1270 ''''||G_category_low||''''||' category_low, ' ||
1271 ''''||G_category_high||''''||' category_high, ' ||
1272 ''''||G_abc_class_low||''''||' abc_class_low, ' ||
1273 ''''||G_abc_class_high||''''||' abc_class_high, ' ||
1274 ''''||G_cutoff_date||''''||' cutoff_date, ' ||
1275 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_incl_items_no_activity||' ) INCL_ITEMS_NO_ACTIVITY, '|| --8486531 Vpedarla
1276 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_comb_pdr||' ) comb_pdr, '||
1277 ''''||G_comb_pdr_temp||''''||' comb_pdr_temp, ' ||
1278 ''''||G_comb_pdr_locale||''''||' comb_pdr_locale, ' ||
1279 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_horiz_pdr||' ) horiz_pdr, '||
1280 ''''||G_horiz_pdr_temp||''''||' horiz_pdr_temp, ' ||
1281 ''''||G_horiz_pdr_locale||''''||' horiz_pdr_locale, ' ||
1282 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_vert_pdr||' ) vert_pdr, '||
1283 ''''||G_vert_pdr_temp||''''||' vert_pdr_temp, ' ||
1284 ''''||G_vert_pdr_locale||''''||' vert_pdr_locale, ' ||
1285 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_excep_pdr||' ) excep_pdr, '||
1286 ''''||G_excep_pdr_temp||''''||' excep_pdr_temp, ' ||
1287 ''''||G_excep_pdr_locale||''''||' excep_pdr_locale, ' ||
1288 ' msc_get_name.lookup_meaning ('||''''||'SYS_YES_NO'||''''||', '||G_act_pdr||' ) act_pdr, '||
1289 ''''||G_act_pdr_temp||''''||' act_pdr_temp, ' ||
1290 ''''||G_act_pdr_locale||''''||' act_pdr_locale ';
1291
1292 IF G_comb_pdr = 1 THEN /*combined pdr report */
1293
1294 G_common_pdr_stmt := NULL ;
1295
1296 -- INSERT INTO ns_debug (LONGVAL) values ('in combined PDR');
1297 FND_FILE.PUT_LINE(FND_FILE.LOG,'in combined PDR');
1298
1299 G_common_pdr_stmt := G_common_pdr_stmt || G_header_stmt;
1300
1301 -- Vpedarla bug: 8273098 modified the order of columns based on order by clause from parameters.
1302 G_common_pdr_stmt := G_common_pdr_stmt || ' , CURSOR ( SELECT gpi.organization_code organization_code, gpi.item_name item_name, '||
1303 ' gpi.category_name category_name, gpi.planner_code planner_code, gpi.buyer_name buyer_name, gpi.abc_class_name abc_class_name';
1304
1305 IF G_horiz_pdr = 1 THEN
1306 BEGIN
1307 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1308 ' ('||G_horiz_plan_stmt||' horiz '||
1309 ' WHERE gpi.inventory_item_id = horiz.inventory_item_id '||
1310 ' AND gpi.organization_id = horiz.organization_id ) horiz ';
1311 EXCEPTION
1312 when others then
1313 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in horiz cursor stmt '||sqlerrm);
1314 END ;
1315 END IF;
1316
1317 IF G_vert_pdr = 1 THEN
1318 BEGIN
1319 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1320 ' ('||G_vert_plan_stmt||' vert '||
1321 ' WHERE gpi.inventory_item_id = vert.inventory_item_id '||
1322 ' AND gpi.organization_id = vert.organization_id ) vert ';
1323 EXCEPTION
1324 when others then
1325 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in vert cursor stmt '||sqlerrm);
1326 END ;
1327 END IF;
1328
1329 IF G_excep_pdr = 1 THEN
1330 BEGIN
1331 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1332 ' ('||G_exc_plan_stmt||' exc '||
1333 ' WHERE gpi.inventory_item_id = exc.inventory_item_id '||
1334 ' AND gpi.organization_id = exc.organization_id ) exc ';
1335 EXCEPTION
1336 when others then
1337 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in exc cursor stmt '||sqlerrm);
1338 END ;
1339 END IF;
1340
1341 IF G_act_pdr = 1 THEN
1342 BEGIN
1343 G_common_pdr_stmt := G_common_pdr_stmt || ', CURSOR '||
1344 ' ('||G_act_plan_stmt||' act '||
1345 ' WHERE gpi.inventory_item_id = act.inventory_item_id '||
1346 ' AND gpi.organization_id = act.organization_id ) act ';
1347 EXCEPTION
1348 when others then
1349 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in act cursor stmt '||sqlerrm);
1350 END ;
1351 END IF;
1352
1353 G_common_pdr_stmt := G_common_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1354 IF G_fsort IS NOT NULL THEN
1355 G_common_pdr_stmt := G_common_pdr_stmt || ' ORDER BY '||G_fsort;
1356 ELSE
1357 G_common_pdr_stmt := G_common_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1358 END IF;
1359
1360 IF G_ssort IS NOT NULL THEN
1361 G_common_pdr_stmt := G_common_pdr_stmt || ', '||G_ssort;
1362 END IF;
1363
1364 IF G_tsort IS NOT NULL THEN
1365 G_common_pdr_stmt := G_common_pdr_stmt || ', '||G_tsort;
1366 END IF;
1367
1368 IF G_fsort IS NOT NULL THEN
1369 G_common_pdr_stmt := G_common_pdr_stmt || ' ) gpi ';
1370 END IF;
1371 G_common_pdr_stmt := G_common_pdr_stmt || ' FROM DUAL ';
1372
1373 -- INSERT INTO ns_debug (LONGVAL) values (G_common_pdr_stmt);
1374 gmp_debug_message('G_common_pdr_stmt ' );
1375
1376 DBMS_LOB.createtemporary(temp_clob, TRUE);
1377 DBMS_LOB.createtemporary(result, TRUE);
1378
1379 -- generate XML data
1380 temp_clob := DBMS_XMLQUERY.getXML (G_common_pdr_stmt ); -- Bug: 9265463
1381 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1382 pattern => '>',
1383 offset => 1,
1384 nth => 1);
1385 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_offset - '||l_offset);
1386
1387 -- Remove the header
1388 DBMS_LOB.erase (temp_clob, l_offset,1);
1389
1390 -- The following line of code ensures that XML data
1391 -- generated here uses the right encoding
1392 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1393
1394 -- Append the rest to xml output
1395 DBMS_LOB.append (result, temp_clob);
1396
1397 -- close context and free memory
1398 -- DBMS_XMLGEN.closeContext(qryctx);
1399 DBMS_LOB.FREETEMPORARY (temp_clob);
1400 -- B7481907 Rajesh Patangya Ends
1401
1402 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1403
1404 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1405 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,1,x_seq_num );
1406 DBMS_LOB.FREETEMPORARY (result);
1407 ps_generate_output(x_seq_num,1);
1408
1409 ELSE /* Not Combined PDR */
1410
1411 IF G_horiz_pdr = 1 THEN /* Horizontal xml */
1412
1413 G_horiz_pdr_stmt := NULL ;
1414
1415 G_horiz_pdr_stmt := G_horiz_pdr_stmt || G_header_stmt ;
1416
1417 -- Vpedarla bug: 8273098 modified the order of columns based on order by clause from parameters.
1418 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' , CURSOR ( SELECT gpi.organization_code organization_code, gpi.item_name item_name, '||
1419 ' gpi.category_name category_name, gpi.planner_code planner_code, gpi.buyer_name buyer_name, gpi.abc_class_name abc_class_name ';
1420
1421 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', CURSOR '||
1422 ' ('||G_horiz_plan_stmt||' horiz '||
1423 ' WHERE gpi.inventory_item_id = horiz.inventory_item_id '||
1424 ' AND gpi.organization_id = horiz.organization_id ) horiz ';
1425
1426 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1427
1428 -- Bug: 8486531
1429 IF (G_incl_items_no_activity = 2 )THEN
1430 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' , (select distinct inventory_item_id from gmp_horizontal_pdr_gtmp ) gtmp '||
1431 ' where gpi.inventory_item_id = gtmp.inventory_item_id ';
1432 END IF;
1433
1434 IF G_fsort IS NOT NULL THEN
1435 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ORDER BY '||G_fsort;
1436 ELSE
1437 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1438 END IF;
1439
1440 IF G_ssort IS NOT NULL THEN
1441 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', '||G_ssort;
1442 END IF;
1443
1444 IF G_tsort IS NOT NULL THEN
1445 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ', '||G_tsort;
1446 END IF;
1447
1448 IF G_fsort IS NOT NULL THEN
1449 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' ) gpi ';
1450 END IF;
1451 G_horiz_pdr_stmt := G_horiz_pdr_stmt || ' FROM DUAL ';
1452
1453 -- B7481907 Rajesh Patangya starts
1454 DBMS_LOB.createtemporary(temp_clob, TRUE);
1455 DBMS_LOB.createtemporary(result, TRUE);
1456
1457 -- generate XML data
1458 temp_clob := DBMS_XMLQUERY.getXML (G_horiz_pdr_stmt ); -- Bug: 9265463
1459 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1460 pattern => '>',
1461 offset => 1,
1462 nth => 1);
1463 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_offset - '||l_offset);
1464
1465 -- Remove the header
1466 DBMS_LOB.erase (temp_clob, l_offset,1);
1467
1468 -- The following line of code ensures that XML data
1469 -- generated here uses the right encoding
1470 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1471
1472 -- Append the rest to xml output
1473 DBMS_LOB.append (result, temp_clob);
1474
1475 -- close context and free memory
1476 -- DBMS_XMLGEN.closeContext(qryctx);
1477 DBMS_LOB.FREETEMPORARY (temp_clob);
1478 -- B7481907 Rajesh Patangya Ends
1479
1480 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1481
1482 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1483 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,2,x_seq_num );
1484 DBMS_LOB.FREETEMPORARY (result);
1485 ps_generate_output(x_seq_num,2);
1486
1487 -- INSERT INTO ns_debug (col3) values (G_horiz_pdr_stmt);
1488
1489 END IF;
1490
1491 IF G_vert_pdr = 1 THEN /* Vertical PDR */
1492
1493 G_vert_pdr_stmt := NULL ;
1494
1495 G_vert_pdr_stmt := G_vert_pdr_stmt || G_header_stmt ;
1496
1497 G_vert_pdr_stmt := G_vert_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1498 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1499
1500 G_vert_pdr_stmt := G_vert_pdr_stmt || ', CURSOR '||
1501 ' ('||G_vert_plan_stmt||' vert '||
1502 ' WHERE gpi.inventory_item_id = vert.inventory_item_id '||
1503 ' AND gpi.organization_id = vert.organization_id ) vert ';
1504
1505 G_vert_pdr_stmt := G_vert_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1506 IF G_fsort IS NOT NULL THEN
1507 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ORDER BY '||G_fsort;
1508 ELSE
1509 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1510 END IF;
1511
1512 IF G_ssort IS NOT NULL THEN
1513 G_vert_pdr_stmt := G_vert_pdr_stmt || ', '||G_ssort;
1514 END IF;
1515
1516 IF G_tsort IS NOT NULL THEN
1517 G_vert_pdr_stmt := G_vert_pdr_stmt || ', '||G_tsort;
1518 END IF;
1519
1520 IF G_fsort IS NOT NULL THEN
1521 G_vert_pdr_stmt := G_vert_pdr_stmt || ' ) gpi ';
1522 END IF;
1523 G_vert_pdr_stmt := G_vert_pdr_stmt || ' FROM DUAL ';
1524
1525 -- B7481907 Rajesh Patangya starts
1526 DBMS_LOB.createtemporary(temp_clob, TRUE);
1527 DBMS_LOB.createtemporary(result, TRUE);
1528
1529 -- generate XML data
1530 temp_clob := DBMS_XMLQUERY.getXML (G_vert_pdr_stmt ); -- Bug: 9265463
1531 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1532 pattern => '>',
1533 offset => 1,
1534 nth => 1);
1535 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_offset - '||l_offset);
1536
1537 -- Remove the header
1538 DBMS_LOB.erase (temp_clob, l_offset,1);
1539
1540 -- The following line of code ensures that XML data
1541 -- generated here uses the right encoding
1542 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1543
1544 -- Append the rest to xml output
1545 DBMS_LOB.append (result, temp_clob);
1546
1547 -- close context and free memory
1548 -- DBMS_XMLGEN.closeContext(qryctx);
1549 DBMS_LOB.FREETEMPORARY (temp_clob);
1550 -- B7481907 Rajesh Patangya Ends
1551
1552 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1553
1554 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1555 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,3,x_seq_num );
1556 DBMS_LOB.FREETEMPORARY (result);
1557 ps_generate_output(x_seq_num,3);
1558
1559 -- INSERT INTO ns_debug (col3) values (G_vert_pdr_stmt);
1560
1561 END IF;
1562
1563 IF G_excep_pdr = 1 THEN
1564
1565 G_excep_pdr_stmt := NULL ;
1566
1567 G_excep_pdr_stmt := G_excep_pdr_stmt || G_header_stmt ;
1568
1569 G_excep_pdr_stmt := G_excep_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1570 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1571
1572 G_excep_pdr_stmt := G_excep_pdr_stmt || ', CURSOR '||
1573 ' ('||G_exc_plan_stmt||' exc '||
1574 ' WHERE gpi.inventory_item_id = exc.inventory_item_id '||
1575 ' AND gpi.organization_id = exc.organization_id ) exc ';
1576
1577 G_excep_pdr_stmt := G_excep_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1578 IF G_fsort IS NOT NULL THEN
1579 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ORDER BY '||G_fsort;
1580 ELSE
1581 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1582 END IF;
1583
1584 IF G_ssort IS NOT NULL THEN
1585 G_excep_pdr_stmt := G_excep_pdr_stmt || ', '||G_ssort;
1586 END IF;
1587
1588 IF G_tsort IS NOT NULL THEN
1589 G_excep_pdr_stmt := G_excep_pdr_stmt || ', '||G_tsort;
1590 END IF;
1591
1592 IF G_fsort IS NOT NULL THEN
1593 G_excep_pdr_stmt := G_excep_pdr_stmt || ' ) gpi ';
1594 END IF;
1595 G_excep_pdr_stmt := G_excep_pdr_stmt || ' FROM DUAL ';
1596
1597 -- B7481907 Rajesh Patangya starts
1598 DBMS_LOB.createtemporary(temp_clob, TRUE);
1599 DBMS_LOB.createtemporary(result, TRUE);
1600
1601 -- generate XML data
1602 temp_clob := DBMS_XMLQUERY.getXML (G_excep_pdr_stmt ); -- Bug: 9265463
1603 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1604 pattern => '>',
1605 offset => 1,
1606 nth => 1);
1607 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_offset - '||l_offset);
1608
1609 -- Remove the header
1610 DBMS_LOB.erase (temp_clob, l_offset,1);
1611
1612 -- The following line of code ensures that XML data
1613 -- generated here uses the right encoding
1614 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1615
1616 -- Append the rest to xml output
1617 DBMS_LOB.append (result, temp_clob);
1618
1619 -- close context and free memory
1620 -- DBMS_XMLGEN.closeContext(qryctx);
1621 DBMS_LOB.FREETEMPORARY (temp_clob);
1622 -- B7481907 Rajesh Patangya Ends
1623
1624 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1625
1626 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1627 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,4,x_seq_num );
1628 -- INSERT INTO temp_r1(xml_file, file_type, pdr_xml_id) VALUES(result,4,x_seq_num );
1629 DBMS_LOB.FREETEMPORARY (result);
1630 ps_generate_output(x_seq_num,4);
1631
1632 -- INSERT INTO ns_debug (col3) values (G_excep_pdr_stmt);
1633
1634 END IF;
1635
1636 IF G_act_pdr = 1 THEN
1637
1638 G_act_pdr_stmt := NULL ;
1639
1640 G_act_pdr_stmt := G_act_pdr_stmt || G_header_stmt ;
1641
1642 G_act_pdr_stmt := G_act_pdr_stmt || ' , CURSOR ( SELECT gpi.item_name, gpi.organization_code, '||
1643 ' gpi.category_name, gpi.buyer_name, gpi.planner_code, gpi.abc_class_name ';
1644
1645 G_act_pdr_stmt := G_act_pdr_stmt || ', CURSOR '||
1646 ' ('||G_act_plan_stmt||' act '||
1647 ' WHERE gpi.inventory_item_id = act.inventory_item_id '||
1648 ' AND gpi.organization_id = act.organization_id ) act ';
1649
1650 G_act_pdr_stmt := G_act_pdr_stmt || ' FROM gmp_pdr_items_gtmp gpi ';
1651 IF G_fsort IS NOT NULL THEN
1652 G_act_pdr_stmt := G_act_pdr_stmt || ' ORDER BY '||G_fsort;
1653 ELSE
1654 G_act_pdr_stmt := G_act_pdr_stmt || ' ORDER BY 1, 2 ) gpi ';
1655 END IF;
1656
1657 IF G_ssort IS NOT NULL THEN
1658 G_act_pdr_stmt := G_act_pdr_stmt || ', '||G_ssort;
1659 END IF;
1660
1661 IF G_tsort IS NOT NULL THEN
1662 G_act_pdr_stmt := G_act_pdr_stmt || ', '||G_tsort;
1663 END IF;
1664
1665 IF G_fsort IS NOT NULL THEN
1666 G_act_pdr_stmt := G_act_pdr_stmt || ' ) gpi ';
1667 END IF;
1668 G_act_pdr_stmt := G_act_pdr_stmt || ' FROM DUAL ';
1669
1670 -- B7481907 Rajesh Patangya starts
1671 DBMS_LOB.createtemporary(temp_clob, TRUE);
1672 DBMS_LOB.createtemporary(result, TRUE);
1673
1674 -- generate XML data
1675 temp_clob := DBMS_XMLQUERY.getXML (G_act_pdr_stmt ); -- Bug: 9265463
1676 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1677 pattern => '>',
1678 offset => 1,
1679 nth => 1);
1680 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_offset - '||l_offset);
1681
1682 -- Remove the header
1683 DBMS_LOB.erase (temp_clob, l_offset,1);
1684
1685 -- The following line of code ensures that XML data
1686 -- generated here uses the right encoding
1687 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1688
1689 -- Append the rest to xml output
1690 DBMS_LOB.append (result, temp_clob);
1691
1692 -- close context and free memory
1693 -- DBMS_XMLGEN.closeContext(qryctx);
1694 DBMS_LOB.FREETEMPORARY (temp_clob);
1695 -- B7481907 Rajesh Patangya Ends
1696
1697 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1698
1699 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1700 INSERT INTO gmp_pdr_xml_temp(xml_file, file_type, pdr_xml_id) VALUES(result,5,x_seq_num );
1701 DBMS_LOB.FREETEMPORARY (result);
1702 ps_generate_output(x_seq_num,5);
1703
1704 -- INSERT INTO ns_debug (col3) values (G_act_pdr_stmt);
1705
1706 END IF;
1707
1708 END IF; /* Combined PDR */
1709
1710 EXCEPTION
1711 WHEN OTHERS THEN
1712 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in procedure generate_xml '||sqlerrm);
1713
1714 END generate_xml;
1715
1716 FUNCTION plan_name RETURN VARCHAR2 IS
1717 plan_name VARCHAR2(10);
1718 BEGIN
1719
1720 SELECT compile_designator
1721 INTO plan_name
1722 FROM msc_plans
1723 WHERE plan_id = G_plan_id;
1724
1725 RETURN plan_name;
1726
1727 EXCEPTION WHEN OTHERS THEN
1728 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function plan_name '||sqlerrm);
1729 END plan_name;
1730
1731 FUNCTION plan_org ( org_id IN NUMBER) RETURN VARCHAR2 IS
1732 org_code VARCHAR2(40);
1733 BEGIN
1734
1735 IF org_id = -999 THEN
1736
1737 SELECT organization_code
1738 INTO org_code
1739 FROM gmp_plan_organization_v
1740 WHERE organization_id = org_id;
1741
1742 RETURN org_code;
1743
1744 END IF;
1745
1746 SELECT organization_code
1747 INTO org_code
1748 FROM gmp_plan_organization_v
1749 WHERE organization_id = org_id
1750 AND plan_id = G_plan_id
1751 AND sr_instance_id = G_inst_id;
1752
1753 RETURN org_code;
1754
1755 EXCEPTION WHEN OTHERS THEN
1756 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function plan_org '||sqlerrm);
1757 END plan_org;
1758
1759 FUNCTION category_set_name ( cat_set_id IN NUMBER) RETURN VARCHAR2 IS
1760 cat_set_name VARCHAR2(35);
1761 BEGIN
1762
1763 SELECT category_set_name
1764 INTO cat_set_name
1765 FROM msc_category_sets
1766 WHERE category_set_id = cat_set_id
1767 AND sr_instance_id = G_inst_id;
1768
1769 RETURN cat_set_name;
1770
1771 EXCEPTION WHEN OTHERS THEN
1772 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in function category_set_name '||sqlerrm);
1773 END category_set_name;
1774
1775 FUNCTION lookup_meaning(l_lookup_type IN VARCHAR2,
1776 l_lookup_code IN NUMBER) RETURN VARCHAR2 IS
1777
1778 meaning_text VARCHAR2(80);
1779 BEGIN
1780
1781 IF l_lookup_code IS NULL THEN
1782 RETURN NULL;
1783 END IF;
1784
1785 SELECT meaning
1786 INTO meaning_text
1787 FROM fnd_lookup_values
1788 WHERE language = userenv('LANG')
1789 AND lookup_type = l_lookup_type
1790 AND TO_NUMBER(lookup_code) = l_lookup_code;
1791
1792 RETURN meaning_text;
1793
1794 EXCEPTION WHEN no_data_found THEN
1795 RETURN NULL;
1796 END lookup_meaning;
1797
1798 /* ***************************************************************
1799 * NAME
1800 * PROCEDURE - ps_generate_output
1801 * PARAMETERS
1802 * DESCRIPTION
1803 * Procedure used generate the final output.
1804 * HISTORY
1805 * Namit 31Mar05 - Initial Version
1806 *************************************************************** */
1807
1808 PROCEDURE ps_generate_output (
1809 p_sequence_num IN NUMBER,
1810 p_pdr_type IN NUMBER
1811 )
1812 IS
1813
1814 l_conc_id NUMBER;
1815 l_req_id NUMBER;
1816 l_phase VARCHAR2(20);
1817 l_status_code VARCHAR2(20);
1818 l_dev_phase VARCHAR2(20);
1819 l_dev_status VARCHAR2(20);
1820 l_message VARCHAR2(20);
1821 l_status BOOLEAN;
1822 l_log_text VARCHAR2(200);
1823 l_def_output_type VARCHAR2(20);
1824
1825
1826 BEGIN
1827
1828 BEGIN
1829
1830 SELECT default_output_type
1831 INTO l_def_output_type
1832 FROM xdo_templates_b
1833 WHERE template_code = 'GMPCMBDPDR';
1834
1835 gmp_debug_message(' Default output type-- '||l_def_output_type);
1836
1837
1838 EXCEPTION WHEN OTHERS THEN
1839 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in retrieving default output type '||sqlerrm);
1840 END;
1841
1842 gmp_debug_message(' ps_generate_output called with p_pdr_type '||p_pdr_type);
1843
1844 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPPDROP','', '',FALSE,
1845 p_sequence_num,chr(0),'','','','','','','','','','','',
1846 '','','','','','','','','','','','','','','',
1847 '','','','','','','','','','',
1848 '','','','','','','','','','',
1849 '','','','','','','','','','',
1850 '','','','','','','','','','',
1851 '','','','','','','','','','',
1852 '','','','','','','','','','',
1853 '','','','','','','','','','');
1854
1855 IF l_conc_id = 0 THEN
1856 l_log_text := FND_MESSAGE.GET;
1857 FND_FILE.PUT_LINE ( FND_FILE.LOG,l_log_text);
1858 ELSE
1859 COMMIT ;
1860 END IF;
1861
1862 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'l_conc_id : '||to_char(l_conc_id));
1863 IF l_conc_id <> 0 THEN
1864
1865 l_status := fnd_concurrent.WAIT_FOR_REQUEST
1866 (
1867 REQUEST_ID => l_conc_id,
1868 INTERVAL => 30,
1869 MAX_WAIT => 900,
1870 PHASE => l_phase,
1871 STATUS => l_status_code,
1872 DEV_PHASE => l_dev_phase,
1873 DEV_STATUS => l_dev_status,
1874 MESSAGE => l_message
1875 );
1876
1877 gmp_debug_message(' Wait completed for conc request '||to_char(l_conc_id));
1878
1879
1880 DELETE FROM gmp_pdr_xml_temp WHERE pdr_xml_id = p_sequence_num;
1881 COMMIT;
1882
1883
1884 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, 'p_pdr_type = '||to_char(p_pdr_type));
1885
1886 IF p_pdr_type = 1 THEN
1887
1888 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1889 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1890 l_conc_id,554,G_comb_pdr_temp,
1891 G_comb_pdr_locale,'Y','RTF',l_def_output_type,scale_report,'','','','','',
1892 '','','','','','','','','','','','','','','',
1893 '','','','','','','','','','',
1894 '','','','','','','','','','',
1895 '','','','','','','','','','',
1896 '','','','','','','','','','',
1897 '','','','','','','','','','',
1898 '','','','','','','','','','',
1899 '','','','','','','','','','');
1900 gmp_debug_message( 'Submitted combined PDR with request id '||to_char(l_req_id));
1901
1902 ELSIF p_pdr_type = 2 THEN
1903
1904 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1905 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1906 l_conc_id,554,G_horiz_pdr_temp,
1907 G_horiz_pdr_locale,'Y','RTF',l_def_output_type,scale_report,'','','','','',
1908 '','','','','','','','','','','','','','','',
1909 '','','','','','','','','','',
1910 '','','','','','','','','','',
1911 '','','','','','','','','','',
1912 '','','','','','','','','','',
1913 '','','','','','','','','','',
1914 '','','','','','','','','','',
1915 '','','','','','','','','','');
1916
1917 gmp_debug_message( 'Submitted Horizontal PDR with request id '||to_char(l_req_id));
1918
1919 ELSIF p_pdr_type = 3 THEN
1920
1921 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1922 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1923 l_conc_id,554,G_vert_pdr_temp,
1924 G_vert_pdr_locale,'Y','RTF',l_def_output_type,scale_report,'','','','','',
1925 '','','','','','','','','','','','','','','',
1926 '','','','','','','','','','',
1927 '','','','','','','','','','',
1928 '','','','','','','','','','',
1929 '','','','','','','','','','',
1930 '','','','','','','','','','',
1931 '','','','','','','','','','',
1932 '','','','','','','','','','');
1933
1934 gmp_debug_message('Submitted Vertical PDR with request id '||to_char(l_req_id));
1935
1936 ELSIF p_pdr_type = 4 THEN
1937
1938 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1939 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1940 l_conc_id,554,G_excep_pdr_temp,
1941 G_excep_pdr_locale,'N','RTF',l_def_output_type,scale_report,'','','','','',
1942 '','','','','','','','','','','','','','','',
1943 '','','','','','','','','','',
1944 '','','','','','','','','','',
1945 '','','','','','','','','','',
1946 '','','','','','','','','','',
1947 '','','','','','','','','','',
1948 '','','','','','','','','','',
1949 '','','','','','','','','','');
1950
1951 gmp_debug_message( 'Submitted Exception PDR with request id '||to_char(l_req_id));
1952
1953 ELSIF p_pdr_type = 5 THEN
1954
1955 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1956 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1957 l_conc_id,554,G_act_pdr_temp,
1958 G_act_pdr_locale,'N','RTF',l_def_output_type,scale_report,'','','','','',
1959 '','','','','','','','','','','','','','','',
1960 '','','','','','','','','','',
1961 '','','','','','','','','','',
1962 '','','','','','','','','','',
1963 '','','','','','','','','','',
1964 '','','','','','','','','','',
1965 '','','','','','','','','','',
1966 '','','','','','','','','','');
1967
1968 gmp_debug_message( 'Submitted Action PDR with request id '||to_char(l_req_id));
1969
1970 END IF;
1971 END IF;
1972
1973 EXCEPTION
1974 WHEN OTHERS THEN
1975 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_output '||SQLERRM);
1976 END ps_generate_output;
1977
1978 /* ***************************************************************
1979 * NAME
1980 * PROCEDURE - xml_transfer
1981 * PARAMETERS
1982 * DESCRIPTION
1983 * Procedure used provide the XML as output of the concurrent program.
1984 * HISTORY
1985 * Namit 31Mar05 - Initial Version
1986 *************************************************************** */
1987
1988 PROCEDURE xml_transfer (
1989 errbuf OUT NOCOPY VARCHAR2,
1990 retcode OUT NOCOPY VARCHAR2,
1991 p_sequence_num IN NUMBER
1992 )IS
1993
1994 l_file CLOB;
1995 file_varchar2 VARCHAR2(32767);
1996 m_file CLOB;
1997 l_len NUMBER;
1998 l_limit NUMBER;
1999
2000 BEGIN
2001
2002 gmp_debug_message(' xml_transfer started '|| to_char(sysdate, 'hh24:mi:ss'));
2003
2004 file_varchar2 := NULL ;
2005
2006 SELECT xml_file INTO l_file
2007 FROM gmp_pdr_xml_temp
2008 WHERE pdr_xml_id = p_sequence_num;
2009 l_limit:= 1;
2010 /* changed the Number from 10 to 15, becuase it is trimiing the First Standard Line */
2011 l_len := DBMS_LOB.GETLENGTH (l_file);
2012 gmp_debug_message('l_len :'||l_len );
2013
2014 LOOP
2015 IF l_len > l_limit THEN
2016 --BUG 6646373 DBMS_LOB.SUBSTR was failing for multi byte character as l_file being CLOB type variable.
2017 --Introduced another clob variable m_file and after trimming it assigned to the varchar type variable.
2018 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
2019 M_FILE := DBMS_LOB.SUBSTR (l_file,1024,l_limit);
2020 -- Vpedarla 8605434
2021 -- file_varchar2:=trim(M_FILE);
2022 file_varchar2:=M_FILE;
2023 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
2024 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
2025 If l_limit < 1026 THEN
2026 gmp_debug_message(' l_limit '||l_limit||'**' || file_varchar2 );
2027 END IF;
2028 file_varchar2 := NULL;
2029 m_file :=NULL;
2030 l_limit:= l_limit + 1024;
2031 ELSE
2032 -- file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
2033 M_FILE := DBMS_LOB.SUBSTR (l_file,1024,l_limit);
2034 -- Vpedarla 8605434
2035 -- file_varchar2:=trim(M_FILE);
2036 file_varchar2:=M_FILE;
2037 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
2038 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
2039 file_varchar2 := NULL;
2040 m_file :=NULL;
2041 EXIT;
2042 END IF;
2043 END LOOP;
2044 gmp_debug_message(' xml_transfer end of loop '|| to_char(sysdate, 'hh24:mi:ss'));
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure gmp_plng_dtl_report_pkg.xml_transfer '||SQLERRM);
2048 END;
2049
2050 /*
2051 REM+=========================================================================+
2052 REM| FUNCTION NAME |
2053 REM| gmp_debug_message |
2054 REM| DESCRIPTION |
2055 REM| This procedure is created to enable more debug messages |
2056 REM| HISTORY |
2057 REM| Vpedarla Bug: 9366921 created this procedure |
2058 REM+=========================================================================+
2059 */
2060 PROCEDURE gmp_debug_message(pBUFF IN VARCHAR2) IS
2061 BEGIN
2062 IF (l_debug = 'Y') then
2063 FND_FILE.PUT_LINE ( FND_FILE.LOG,pBUFF);
2064 END IF;
2065 END gmp_debug_message;
2066
2067 END GMP_PLNG_DTL_REPORT_PKG;
2068