4 /*===========================================================================
1 PACKAGE BODY GR_PROCESS_DOCUMENTS_INSERTS AS
2 /* $Header: GRPDOCIB.pls 115.2 2003/02/28 15:10:12 mgrosser noship $ */
3
5 -- PROCEDURE:
6 -- Worksheet_Insert_Row
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to identify missing data in the worksheet
10 -- and to insert data into gr_work_worksheets.
14 -- p_output_type IN VARCHAR2 - 'PDF','XML','HTML', etc
11 --
12 -- PARAMETERS:
13 -- p_line_number IN OUT NOCOPY NUMBER - Line number of inserted record
15 -- p_language_code IN VARCHAR2 - Language that worksheet is being printed in
16 -- p_session_id IN NUMBER - Session id for report
17 -- p_item_code IN VARCHAR2 - Item that document is being printed for
18 -- p_print_font IN VARCHAR2 - Type of font to print the text in
19 -- p_print_size IN NUMBER - Size of font to print the text in
20 -- p_text_line IN VARCHAR2 - Text to be inserted
21 -- p_line_type IN VARCHAR2 - Type of value being inserted
22 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
23 --
24 -- SYNOPSIS:
25 -- Worksheet_Insert_Row(g_line_number,g_output_type,g_language_code, g_session_id,l_item_code,
26 -- l_print_font,l_print_size,l_text_line,l_line_type,l_return_status);
27 --
28 -- HISTORY
29 -- M. Grosser 27-Feb-2003 BUG 2718956 - Put an IF statment around the selection of
30 -- organization data and the error message associated with it.
31 -- It is only applicable to organization data.
32 --=========================================================================== */
33 PROCEDURE Worksheet_Insert_Row
34 (p_line_number IN OUT NOCOPY NUMBER,
35 p_output_type IN VARCHAR2,
36 p_user_id IN NUMBER,
37 p_current_date IN DATE,
38 p_language_code IN VARCHAR2,
39 p_session_id IN NUMBER,
40 p_item_code IN VARCHAR2,
41 p_print_font IN VARCHAR2,
42 p_print_size IN NUMBER,
43 p_text_line IN VARCHAR2,
44 p_line_type IN VARCHAR2,
45 x_return_status OUT NOCOPY VARCHAR2)
46 IS
47
48 /* ------------- LOCAL VARIABLES ------------------- */
49 L_TEXT_LINE GR_WORK_WORKSHEETS.text_line%TYPE;
50 L_LABEL_CODE GR_LABELS_B.label_code%TYPE;
51 L_ADDR_LINE SY_ADDR_MST.addr1%TYPE;
52 L_DEFAULT_ORGN SY_ORGN_MST.orgn_code%TYPE;
53 L_TELEPHONE_NUMBER VARCHAR2(78);
54 L_LINE_LEN NUMBER;
55 L_LABEL_LEN NUMBER;
56 L_LANGUAGE_CODE VARCHAR(4);
57
58
59 /* ------------------ CURSORS ---------------------- */
60 /* Get the organization name, address and contact information */
61 CURSOR c_get_orgn_info IS
62 SELECT om.orgn_name,
63 oa.addr1,
64 oa.addr2,
65 oa.addr3,
66 oa.addr4,
67 oa.postal_code,
68 oa.state_code,
69 oa.country_code,
70 oc.daytime_contact_name,
71 oc.daytime_telephone,
72 oc.daytime_extension,
73 oc.daytime_area_code,
74 oc.evening_contact_name,
75 oc.evening_telephone,
76 oc.evening_extension,
77 oc.evening_area_code,
78 oc.daytime_fax_no,
79 oc.daytime_email,
80 oc.evening_fax_no,
81 oc.evening_email
82 FROM gr_organization_contacts oc,
83 sy_addr_mst oa,
84 sy_orgn_mst om
85 WHERE om.orgn_code = l_default_orgn
86 AND om.addr_id = oa.addr_id
87 AND oc.orgn_code = om.orgn_code;
88 LocalOrgnRecord c_get_orgn_info%ROWTYPE;
89
90 /* Get the label description and print information */
91 CURSOR c_get_label_info IS
92 SELECT lab.data_position_indicator,
93 lat.label_description
94 FROM gr_labels_tl lat,
95 gr_labels_b lab
96 WHERE lab.label_code = l_label_code
97 AND lat.label_code = lab.label_code
98 AND lat.language = l_language_code;
99 LocalLabelRecord c_get_label_info%ROWTYPE;
100
101
102 /*
103 ** Get the country description
104 */
105 CURSOR c_get_country_info (V_country_code VARCHAR2) IS
106 SELECT geog_desc
107 FROM sy_geog_mst
108 WHERE geog_type = 1
109 AND geog_code = V_country_code;
110 LocalCountryRecord c_get_country_info%ROWTYPE;
111
112
113 BEGIN
114 x_return_status := 'S';
115
116 IF p_line_number IS NULL THEN
117 p_line_number := 0;
118 END IF;
119
120 l_text_line := p_text_line;
121
122 l_default_orgn := FND_PROFILE.Value('GR_ORGN_DEFAULT');
123
124 IF p_language_code is NULL THEN
125 l_language_code := USERENV('LANG');
126 ELSE
127 l_language_code := p_language_code;
128 END IF;
129
130 /* M. Grosser 27-Feb-2003 BUG 2718956 - Put an IF statment around the selection of
131 organization data and the error message associated with it.
132 It is only applicable to organization data.
133 */
134 IF l_text_line in ('01100','01101','01102','01103','01104','01105','01106','01107','01008') THEN
135 OPEN c_get_orgn_info;
136 FETCH c_get_orgn_info INTO LocalOrgnRecord;
137 IF c_get_orgn_info%NOTFOUND THEN
138 FND_FILE.PUT(FND_FILE.LOG,l_default_orgn || ' orgn address/contact missing');
139 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
140 END IF;
141 CLOSE c_get_orgn_info;
142 END IF; /* If printing organization information */
143 /* M. Grosser 27-Feb-2003 BUG 2718956 - End of changes */
144
145 IF l_text_line = '01100' THEN
146 l_label_code := '01100';
147 OPEN c_get_label_info;
151 ** If no label info print the code and ????
148 FETCH c_get_label_info INTO LocalLabelRecord;
149
150 /*
152 */
153 IF c_get_label_info%NOTFOUND THEN
154 l_text_line := l_label_code || ' has no details';
155 FND_FILE.PUT(FND_FILE.LOG,l_text_line);
156 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
157 ELSE
158 l_text_line := LocalLabelRecord.label_description;
159 IF LocalLabelRecord.data_position_indicator = 'I' THEN
160 l_text_line := l_text_line || ' '||LocalOrgnRecord.orgn_name;
161 l_label_len := LENGTH(LocalLabelRecord.label_description) + 1;
162 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
163 l_text_line := RPAD(l_text_line,30)||' '||LocalOrgnRecord.orgn_name;
164 l_label_len := 31;
165 END IF;
166
167 /* Created new procedure to avoid redundant code */
168 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
169 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
170
171 IF LocalLabelRecord.data_position_indicator = 'N' THEN
172 l_text_line := LocalOrgnRecord.orgn_name;
173 l_label_len := 0;
174
175 /* Created new procedure to avoid redundant code */
176 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
177 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
178 END IF;
179
180 IF LocalOrgnRecord.addr1 IS NOT NULL THEN
181 l_addr_line := LocalOrgnRecord.addr1;
182 l_line_len := l_label_len + LENGTH(l_addr_line);
183 l_text_line := LPAD(l_addr_line,l_line_len,' ');
184
185
186 /* Created new procedure to avoid redundant code */
187 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
188 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
189 END IF;
190
191 IF LocalOrgnRecord.addr2 IS NOT NULL THEN
192 l_addr_line := LocalOrgnRecord.addr2;
193 l_line_len := l_label_len + LENGTH(l_addr_line);
194 l_text_line := LPAD(l_addr_line,l_line_len,' ');
195
196
197 /* Created new procedure to avoid redundant code */
198 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
199 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
200 END IF;
201
202 IF LocalOrgnRecord.addr3 IS NOT NULL THEN
203 l_addr_line := LocalOrgnRecord.addr3;
204 l_line_len := l_label_len + LENGTH(l_addr_line);
205 l_text_line := LPAD(l_addr_line,l_line_len,' ');
206
207 /* Created new procedure to avoid redundant code */
208 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
209 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
210 END IF;
211
212 IF (LocalOrgnRecord.addr4 IS NOT NULL) OR
213 (LocalOrgnRecord.state_code IS NOT NULL) OR
214 (LocalOrgnRecord.postal_code IS NOT NULL) THEN
215 l_addr_line := NULL;
216
217 IF LocalOrgnRecord.addr4 IS NOT NULL THEN
218 l_addr_line := LocalOrgnRecord.addr4;
219 END IF;
220
221 IF LocalOrgnRecord.state_code IS NOT NULL THEN
222 l_addr_line := l_addr_line ||' '||LocalOrgnRecord.state_code;
223 END IF;
224
225 IF LocalOrgnRecord.postal_code IS NOT NULL THEN
226 l_addr_line := l_addr_line||' '||LocalOrgnRecord.postal_code;
227 END IF;
228
229 l_line_len := l_label_len + LENGTH(l_addr_line);
230 l_text_line := LPAD(l_addr_line,l_line_len,' ');
231
232 /* Created new procedure to avoid redundant code */
233 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
234 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
235 END IF;
236
237 IF LocalOrgnRecord.country_code IS NOT NULL THEN
238 l_addr_line := LocalOrgnRecord.country_code;
239 OPEN c_get_country_info(l_addr_line);
240 FETCH c_get_country_info INTO LocalCountryRecord;
241
242 IF c_get_country_info%FOUND THEN
243 l_addr_line := LocalCountryRecord.geog_desc;
244 END IF;
245
246 CLOSE c_get_country_info;
247 l_line_len := l_label_len + LENGTH(l_addr_line);
248 l_text_line := LPAD(l_addr_line,l_line_len,' ');
249
250 /* Created new procedure to avoid redundant code */
251 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
252 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
253 END IF;
254
255 END IF;
256 CLOSE c_get_label_info;
257 l_text_line := NULL;
258
259
260 /*
261 ** Label code for printing the daytime contact name
262 */
263 ELSIF l_text_line = '01101' THEN
264 IF LocalOrgnRecord.daytime_contact_name IS NOT NULL THEN
265 l_label_code := '01101';
266 OPEN c_get_label_info;
267 FETCH c_get_label_info INTO LocalLabelRecord;
268
269 /*
270 ** If no label info print the label code and ??????
271 */
272 IF c_get_label_info%NOTFOUND THEN
273 l_text_line := l_label_code || '??????' || ' ';
274 l_text_line := l_text_line || LocalOrgnRecord.daytime_contact_name;
278 /*
275 ELSE
276 l_text_line := LocalLabelRecord.label_description;
277
279 ** Label info and print data on the same line
280 */
281 IF LocalLabelRecord.data_position_indicator = 'I' THEN
282 l_text_line := l_text_line || ' '||LocalOrgnRecord.daytime_contact_name;
283 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
284 l_text_line := RPAD(l_text_line,30)||' '||LocalOrgnRecord.daytime_contact_name;
285 /*
286 ** Label info and print data on the next line
287 */
288 ELSE
289 /* Created new procedure to avoid redundant code */
290 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
291 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
292 l_text_line := LocalOrgnRecord.daytime_contact_name;
293 END IF;
294 END IF;
295 CLOSE c_get_label_info;
296 END IF;
297
298 /*
299 ** Label code for printing the daytime contact number
300 */
301 ELSIF l_text_line = '01102' THEN
302 IF LocalOrgnRecord.daytime_telephone IS NOT NULL THEN
303 l_label_code := '01102';
304 l_telephone_number := LocalOrgnRecord.daytime_area_code;
305
306 IF l_telephone_number IS NOT NULL THEN
307 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.daytime_telephone;
308 ELSE
309 l_telephone_number := LocalOrgnRecord.daytime_telephone;
310 END IF;
311
312 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.daytime_extension;
313 OPEN c_get_label_info;
314 FETCH c_get_label_info INTO LocalLabelRecord;
315
316 /*
317 ** If no label info print the label code and ??????
318 */
319 IF c_get_label_info%NOTFOUND THEN
320 l_text_line := l_label_code || '??????' || ' ' || l_telephone_number;
321 ELSE
322 l_text_line := LocalLabelRecord.label_description;
323
324 /*
325 ** Label info and print data on the same line
326 */
327 IF LocalLabelRecord.data_position_indicator = 'I' THEN
328 l_text_line := l_text_line || ' '||l_telephone_number;
329 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
330 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')||' '||l_telephone_number;
331 /*
332 ** Label info and print data on the next line
333 */
334 ELSE
335 /* Created new procedure to avoid redundant code */
336 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
337 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
338 l_text_line := l_telephone_number;
339 END IF;
340 END IF;
341 CLOSE c_get_label_info;
342 END IF;
343
344 /*
345 ** Label code for printing the evening contact name
346 */
347 ELSIF l_text_line = '01103' THEN
348 IF LocalOrgnRecord.evening_contact_name IS NOT NULL THEN
349 l_label_code := '01103';
350 OPEN c_get_label_info;
351 FETCH c_get_label_info INTO LocalLabelRecord;
352
353 /*
354 ** If no label info print the label code and ??????
355 */
356 IF c_get_label_info%NOTFOUND THEN
357 l_text_line := l_label_code || '??????' || ' ';
358 l_text_line := l_text_line || LocalOrgnRecord.evening_contact_name;
359 ELSE
360 l_text_line := LocalLabelRecord.label_description;
361
362 /*
363 ** Label info and print data on the same line
364 */
365 IF LocalLabelRecord.data_position_indicator = 'I' THEN
366 l_text_line := l_text_line || ' '||LocalOrgnRecord.evening_contact_name;
367 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
368 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')|| ' '||LocalOrgnRecord.evening_contact_name;
369 /*
370 ** Label info and print data on the next line
371 */
372 ELSE
373 /* Created new procedure to avoid redundant code */
374 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
375 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
376 l_text_line := LocalOrgnRecord.evening_contact_name;
377 END IF;
378 END IF;
379 CLOSE c_get_label_info;
380 END IF;
381
382 /*
383 ** Label code for printing the evening contact number
384 */
385 ELSIF l_text_line = '01104' THEN
386 IF LocalOrgnRecord.evening_telephone IS NOT NULL THEN
387 l_label_code := '01104';
388 l_telephone_number := LocalOrgnRecord.evening_area_code;
389
390 IF l_telephone_number IS NOT NULL THEN
391 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.evening_telephone;
392 ELSE
393 l_telephone_number := LocalOrgnRecord.evening_telephone;
394 END IF;
395
396 l_telephone_number := l_telephone_number || ' ' || LocalOrgnRecord.evening_extension;
397 OPEN c_get_label_info;
398 FETCH c_get_label_info INTO LocalLabelRecord;
399
400 /*
401 ** If no label info print the label code and ??????
402 */
403 IF c_get_label_info%NOTFOUND THEN
407
404 l_text_line := l_label_code || '??????' || ' ' || l_telephone_number;
405 ELSE
406 l_text_line := LocalLabelRecord.label_description;
408 /*
409 ** Label info and print data on the same line
410 */
411 IF LocalLabelRecord.data_position_indicator = 'I' THEN
412 l_text_line := l_text_line || ' '||l_telephone_number;
413 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
414 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')||' '||l_telephone_number;
415 /*
416 ** Label info and print data on the next line
417 */
418 ELSE
419 /* Created new procedure to avoid redundant code */
420 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
421 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
422 l_text_line := l_telephone_number;
423 END IF;
424
425 END IF;
426 CLOSE c_get_label_info;
427 END IF;
428
429 ELSIF l_text_line = '01105' THEN
430 IF LocalOrgnRecord.daytime_fax_no IS NOT NULL THEN
431 l_label_code := '01105';
432 OPEN c_get_label_info;
433 FETCH c_get_label_info INTO LocalLabelRecord;
434
435 /*
436 ** If no label info print the label code and ??????
437 */
438 IF c_get_label_info%NOTFOUND THEN
439 l_text_line := l_label_code || '??????' || ' ';
440 l_text_line := l_text_line || LocalOrgnRecord.daytime_fax_no;
441 ELSE
442 l_text_line := LocalLabelRecord.label_description;
443
444 /*
445 ** Label info and print data on the same line
446 */
447 IF LocalLabelRecord.data_position_indicator = 'I' THEN
448 l_text_line := l_text_line || ' '||LocalOrgnRecord.daytime_fax_no;
449 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
450 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')|| ' '||LocalOrgnRecord.daytime_fax_no;
451 /*
452 ** Label info and print data on the next line
453 */
454 ELSE
455 /* Created new procedure to avoid redundant code */
456 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
457 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
458
459 l_text_line := LocalOrgnRecord.daytime_fax_no;
460 END IF;
461
462 END IF;
463 CLOSE c_get_label_info;
464 END IF;
465
466 ELSIF l_text_line = '01106' THEN
467 IF LocalOrgnRecord.daytime_email IS NOT NULL THEN
468 l_label_code := '01106';
469 OPEN c_get_label_info;
470 FETCH c_get_label_info INTO LocalLabelRecord;
471
472 /*
473 ** If no label info print the label code and ??????
474 */
475 IF c_get_label_info%NOTFOUND THEN
476 l_text_line := l_label_code || '??????' || ' ';
477 l_text_line := l_text_line || LocalOrgnRecord.daytime_email;
478 ELSE
479 l_text_line := LocalLabelRecord.label_description;
480
481 /*
482 ** Label info and print data on the same line
483 */
484 IF LocalLabelRecord.data_position_indicator = 'I' THEN
485 l_text_line := l_text_line || ' '||LocalOrgnRecord.daytime_email;
486 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
487 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')|| ' '||LocalOrgnRecord.daytime_email;
488 /*
489 ** Label info and print data on the next line
490 */
491 ELSE
492 /* Created new procedure to avoid redundant code */
493 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
494 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
495 l_text_line := LocalOrgnRecord.daytime_email;
496 END IF;
497
498 END IF;
499 CLOSE c_get_label_info;
500 END IF;
501
502 ELSIF l_text_line = '01107' THEN
503 IF LocalOrgnRecord.evening_fax_no IS NOT NULL THEN
504 l_label_code := '01107';
505 OPEN c_get_label_info;
506 FETCH c_get_label_info INTO LocalLabelRecord;
507
508 /*
509 ** If no label info print the label code and ??????
510 */
511 IF c_get_label_info%NOTFOUND THEN
512 l_text_line := l_label_code || '??????' || ' ';
513 l_text_line := l_text_line || LocalOrgnRecord.evening_fax_no;
514 ELSE
515 l_text_line := LocalLabelRecord.label_description;
516
517 /*
518 ** Label info and print data on the same line
519 */
520 IF LocalLabelRecord.data_position_indicator = 'I' THEN
521 l_text_line := l_text_line || ' '||LocalOrgnRecord.evening_fax_no;
522 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
523 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')|| ' '||LocalOrgnRecord.evening_fax_no;
524 /*
525 ** Label info and print data on the next line
526 */
527 ELSE
528 /* Created new procedure to avoid redundant code */
529 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
533
530 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
531 l_text_line := LocalOrgnRecord.evening_fax_no;
532 END IF;
534 END IF;
535 CLOSE c_get_label_info;
536 END IF;
537
538 ELSIF l_text_line = '01108' THEN
539 IF LocalOrgnRecord.evening_email IS NOT NULL THEN
540 l_label_code := '01108';
541 OPEN c_get_label_info;
542 FETCH c_get_label_info INTO LocalLabelRecord;
543
544 /*
545 ** If no label info print the label code and ??????
546 */
547 IF c_get_label_info%NOTFOUND THEN
548 l_text_line := l_label_code || '??????' || ' ';
549 l_text_line := l_text_line || LocalOrgnRecord.evening_email;
550 ELSE
551 l_text_line := LocalLabelRecord.label_description;
552
553 /*
554 ** Label info and print data on the same line
555 */
556 IF LocalLabelRecord.data_position_indicator = 'I' THEN
557 l_text_line := l_text_line || ' '||LocalOrgnRecord.evening_email;
558 ELSIF LocalLabelRecord.data_position_indicator IN ('C', 'R') THEN
559 l_text_line := RPAD(LocalLabelRecord.label_description,30,' ')|| ' '||LocalOrgnRecord.evening_email;
560 /*
561 ** Label info and print data on the next line
562 */
563 ELSE
564 /* Created new procedure to avoid redundant code */
565 Insert_Gr_Work_Worksheets (p_output_type,p_line_number,p_session_id,
566 p_item_code, p_print_font, p_print_size,l_text_line, p_line_type, x_return_status);
567 l_text_line := LocalOrgnRecord.evening_email;
568 END IF;
569
570 END IF;
571 CLOSE c_get_label_info;
572 END IF;
573
574 END IF;
575
576 IF NVL(l_label_code, '00000') <> '01100' THEN
577
578 /* Modified to allow proper formatting of XML*/
579 IF p_output_type in ('XML','HTML') THEN
580 IF p_line_type = 'LIN' THEN
581 p_line_number := trunc(p_line_number/1000) * 1000 +1000;
582 ELSIF p_line_type = 'COL' THEN
583 p_line_number := p_line_number + 1;
584 ELSE
585 p_line_number := trunc(p_line_number /10000) * 10000 + 10000;
586 END IF;
587 ELSE
588 p_line_number := p_line_number + 1;
589 END IF; /* If XML or HTML */
590
591 INSERT INTO gr_work_worksheets
592 (session_id,
593 text_line_number,
594 item_code,
595 print_font,
596 print_size,
597 text_line,
598 line_type)
599 VALUES
600 (p_session_id,
601 p_line_number,
602 p_item_code,
603 p_print_font,
604 p_print_size,
605 l_text_line,
606 p_line_type);
607 END IF;
608
609 EXCEPTION
610 WHEN OTHERS THEN
611 FND_FILE.PUT(FND_FILE.LOG,' WORKSHEET INSERT: '||sqlerrm);
612 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
614
615 END Worksheet_Insert_Row;
616
617
618
619
620 /*===========================================================================
621 -- PROCEDURE:
622 -- Document_Insert_Row
623 --
624 -- DESCRIPTION:
625 -- This PL/SQL procedure is used to insert a row into GR_DOCUMENT_DETAILS.
626 --
627 -- PARAMETERS:
628 -- p_line_number IN OUT NOCOPY NUMBER - Line number of inserted record
629 -- p_output_type IN VARCHAR2 - 'PDF','XML','HTML', etc
630 -- p_document_text_id IN VARCHAR2 - Language that worksheet is being printed in
631 -- p_user_id IN NUMBER - Id of user running the report
632 -- p_current_date IN DATE - Date that report is being printed
633 -- p_session_id IN NUMBER - Session id for report
634 -- p_item_code IN VARCHAR2 - Item that document is being printed for
635 -- p_print_font IN VARCHAR2 - Type of font to print the text in
636 -- p_print_size IN NUMBER - Size of font to print the text in
637 -- p_text_line IN VARCHAR2 - Text to be inserted
638 -- p_line_type IN VARCHAR2 - Type of value being inserted
639 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
640 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
641 --
642 -- SYNOPSIS:
643 -- Document_Insert_Row(g_line_number,g_document_text_id,g_user_id,g_current_date,
644 -- g_session_id,l_item_code,l_print_font,l_print_size,l_text_line,
645 -- l_line_type,l_return_status);
646 --
647 -- HISTORY
648 --=========================================================================== */
649 PROCEDURE Document_Insert_Row
650 (p_line_number IN OUT NOCOPY NUMBER,
651 p_output_type IN VARCHAR2,
652 p_document_text_id IN NUMBER,
653 p_user_id IN NUMBER,
654 p_current_date IN DATE,
655 p_session_id IN NUMBER,
656 p_item_code IN VARCHAR2,
657 p_print_font IN VARCHAR2,
658 p_print_size IN NUMBER,
659 p_text_line IN VARCHAR2,
660 p_line_type IN VARCHAR2,
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_data OUT NOCOPY VARCHAR2)
663
664
665 IS
666
670 L_CALLED_BY_FORM VARCHAR2(1) := 'F';
667 /* ------------- LOCAL VARIABLES ------------------- */
668 /* Alpha Variables */
669 L_COMMIT VARCHAR2(1) := 'F';
671 L_ROWID VARCHAR2(18);
672 L_RETURN_STATUS VARCHAR2(1) := 'S';
673 L_MSG_DATA VARCHAR2(2000);
674
675 /* Number variables */
676 L_ORACLE_ERROR NUMBER;
677
678
679 BEGIN
680 x_return_status := 'S';
681 l_return_status := 'S';
682
683 IF p_line_number IS NULL THEN
684 p_line_number := 0;
685 END IF;
686
687 /* Modified to allow proper formatting of XML*/
688 IF p_output_type in ('XML','HTML') THEN
689 IF p_line_type = 'LIN' THEN
690 p_line_number := trunc(p_line_number/1000) * 1000 +1000;
691 ELSIF p_line_type = 'COL' THEN
692 p_line_number := p_line_number + 1;
693 ELSE
694 p_line_number := trunc(p_line_number /10000) * 10000 + 10000;
695 END IF;
696 ELSE
697 p_line_number := p_line_number + 1;
698 END IF; /* If XML or HTML */
699
700 GR_DOCUMENT_DETAILS_PKG.Insert_Row
701 (l_commit,
702 l_called_by_form,
703 p_line_number,
704 p_document_text_id,
705 p_print_font,
706 p_print_size,
707 p_text_line,
708 p_line_type,
709 p_user_id,
710 p_current_date,
711 p_user_id,
712 p_current_date,
713 p_user_id,
714 l_rowid,
715 l_return_status,
716 l_oracle_error,
717 l_msg_data);
718
719 EXCEPTION
720 WHEN OTHERS THEN
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 x_msg_data := l_msg_data;
723 FND_FILE.PUT(FND_FILE.LOG,' others '||sqlerrm);
724 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
725 END Document_Insert_Row;
726
727
728
729
730
731 /*===========================================================================
732 -- PROCEDURE:
733 -- Insert_Work_Row
734 --
735 -- DESCRIPTION:
736 -- This PL/SQL procedure is used to insert a row into GR_WORK_BUILD_DOCS.
737 --
738 -- PARAMETERS:
739 -- p_session_id IN NUMBER - Session id for report
740 -- p_document_code IN VARCHAR2 - Document type being printed
741 -- p_main_heading_code IN VARCHAR2 - Main heading that this data is being printed under
742 -- p_main_display_order IN NUMBER - Sequence number of headng
743 -- p_sub_heading_code IN VARCHAR2 - Sub heading that this data is being printed under
744 -- p_sub_display_order IN NUMBER - Sequence number of subheading
745 -- p_record_type IN VARCHAR2 - Type of record being inserted
746 -- p_label_or_phrase_code IN VARCHAR2 - Field name or label code
747 -- p_concentration_percent IN NUMBER - Ingredient concentration percentage
748 -- p_label_class IN VARCHAR2 - Field name class
749 -- p_phrase_hierarchy IN NUMBER - Phrase heirarchy
750 -- p_phrase_type IN VARCHAR2 - Phrase type
751 -- p_print_flag IN VARCHAR2 - Print - yes or no
752 -- p_item_code IN VARCHAR2 - Item that document is being printed for
753 -- p_structure_display_order IN NUMBER - Display order of structure
754 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
755 -- x_msg_data OUT NOCOPY VARCHAR2 - If there is an error, send back the approriate message
756 --
757 -- SYNOPSIS:
758 -- Insert_Work_Row(g_session_id,g_document_code,l_main_heading_code,l_main_display_order,l_sub_heading_code,
759 -- l_sub_display_order,l_record_type,l_label_or_phrase_code,l_concentration_percent,l_label_class,
760 -- l_phrase_hierarchy,l_phrase_type,l_print_flag,l_source_itemcode,l_structure_display_order,
761 -- l_return_status,l_msg_data);
762 --
763 -- HISTORY
764 --=========================================================================== */
765 PROCEDURE Insert_Work_Row
766 (p_session_id IN NUMBER,
767 p_document_code IN VARCHAR2,
768 p_main_heading_code IN VARCHAR2,
769 p_main_display_order IN NUMBER,
770 p_sub_heading_code IN VARCHAR2,
771 p_sub_display_order IN NUMBER,
772 p_record_type IN VARCHAR2,
773 p_label_or_phrase_code IN VARCHAR2,
774 p_concentration_percent IN NUMBER,
775 p_label_class IN VARCHAR2,
776 p_phrase_hierarchy IN NUMBER,
777 p_phrase_type IN VARCHAR2,
778 p_print_flag IN VARCHAR2,
779 p_source_itemcode IN VARCHAR2,
780 p_structure_display_order IN NUMBER,
781 x_return_status OUT NOCOPY VARCHAR2,
782 x_msg_data OUT NOCOPY VARCHAR2)
783 IS
784
785
786 /* ------------- LOCAL VARIABLES ------------------- */
787 /* Alpha Variables */
788 L_CODE_BLOCK VARCHAR2(2000);
789 L_UPDATE_OK VARCHAR2(2);
790
791 /* Numeric Variables */
792 L_ORACLE_ERROR NUMBER;
793
794
795 /* ------------------ CURSORS ---------------------- */
796 /* Check to see if a phrase is already written for this
797 ** heading and subheading combination
798 */
799 CURSOR c_get_existing_phrase IS
800 SELECT wbd.print_flag
801 FROM gr_work_build_docs wbd
802 WHERE wbd.document_code = p_document_code
803 AND wbd.main_heading_code = p_main_heading_code
807 LocalPhraseRecord c_get_existing_phrase%ROWTYPE;
804 AND wbd.sub_heading_code = p_sub_heading_code
805 AND wbd.record_type = p_label_or_phrase_code
806 AND wbd.label_or_phrase_code = p_label_or_phrase_code;
808
809
810 BEGIN
811 SAVEPOINT Insert_Work_Row;
812 l_code_block := NULL;
813 x_return_status := FND_API.G_RET_STS_SUCCESS;
814
815 IF p_label_or_phrase_code = 'L' THEN
816 l_update_ok := 'YS';
817 ELSE
818 OPEN c_get_existing_phrase;
819 FETCH c_get_existing_phrase INTO LocalPhraseRecord;
820 IF c_get_existing_phrase%NOTFOUND THEN
821 l_update_ok := 'YS';
822 ELSE
823 l_update_ok := 'NO';
824 END IF;
825 CLOSE c_get_existing_phrase;
826 END IF;
827
828 IF l_update_ok = 'YS' THEN
829 INSERT INTO gr_work_build_docs
830 (session_id,
831 sequence_number,
832 document_code,
833 main_heading_code,
834 main_display_order,
835 sub_heading_code,
836 sub_display_order,
837 record_type,
838 label_or_phrase_code,
839 concentration_percent,
840 label_class,
841 phrase_hierarchy,
842 phrase_type,
843 print_flag,
844 source_itemcode,
845 structure_display_order)
846 VALUES
847 (p_session_id,
848 gr_work_build_docs_s.nextval,
849 p_document_code,
850 p_main_heading_code,
851 p_main_display_order,
852 p_sub_heading_code,
853 p_sub_display_order,
854 p_record_type,
855 p_label_or_phrase_code,
856 p_concentration_percent,
857 p_label_class,
858 p_phrase_hierarchy,
859 p_phrase_type,
860 p_print_flag,
861 p_source_itemcode,
862 p_structure_display_order);
863 END IF;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 ROLLBACK TO SAVEPOINT Insert_Work_Row;
868 l_oracle_error := SQLCODE;
869 l_code_block := SUBSTR(SQLERRM, 1, 200);
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871 FND_MESSAGE.SET_NAME('GR','GR_UNEXPECTED_ERROR');
872 FND_MESSAGE.SET_TOKEN('TEXT',l_code_block,FALSE);
873 x_msg_data := FND_MESSAGE.Get;
874
875 END Insert_Work_Row;
876
877
878
879
880
881
882 /*===========================================================================
883 -- PROCEDURE:
884 -- Insert_Data_Record
885 --
886 -- DESCRIPTION:
887 -- This PL/SQL procedure is used to write field name data for the report.
888 --
889 -- PARAMETERS:
890 -- p_session_id IN NUMBER - Session id for report
891 -- p_document_item IN VARCHAR2 - Item that document is being printed for
892 -- p_print_font IN VARCHAR2 - Type of font to print the text in
893 -- p_print_size IN NUMBER - Size of font to print the text in
894 -- p_text_line1 IN VARCHAR2 - Text to be inserted
895 -- p_text_line2 IN VARCHAR2 - Second text to be inserted
896 -- p_source_action IN VARCHAR2 - 'WORKSHEET' or 'DOCUMENT'
897 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
898 --
899 -- SYNOPSIS:
900 -- Insert_Data_Record(g_session_id,l_item_code,l_print_font,l_print_size,l_text_line,
901 -- p_source_action,l_return_status);
902 --
903 -- HISTORY
904 --=========================================================================== */
905 PROCEDURE Insert_Data_Record
906 (p_line_number IN OUT NOCOPY NUMBER,
907 p_line_type IN VARCHAR2,
908 p_output_type IN VARCHAR2,
909 p_user_id IN NUMBER,
910 p_current_date IN DATE,
911 p_language_code IN VARCHAR2,
912 p_document_text_id IN NUMBER,
913 p_session_id IN NUMBER,
914 p_document_item IN VARCHAR2,
915 p_print_font IN VARCHAR2,
916 p_print_size IN NUMBER,
917 p_text_line_1 IN VARCHAR2,
918 p_text_line_2 IN VARCHAR2,
919 p_source_action IN VARCHAR2,
920 x_return_status OUT NOCOPY VARCHAR2)
921 IS
922
923 /* ------------- LOCAL VARIABLES ------------------- */
924 L_RETURN_STATUS VARCHAR2(1);
925 L_MSG_DATA VARCHAR2(2000);
926 L_LINE_TYPE VARCHAR2(8);
927
928 /* ------------------ EXCEPTIONS ---------------------- */
929 WORKSHEET_INSERT_ERROR EXCEPTION;
930 DOCUMENT_INSERT_ERROR EXCEPTION;
931
932 BEGIN
933 x_return_status := 'S';
934 l_return_status := 'S';
935
936 /* If printing PDF */
937 IF p_output_type NOT IN ('HTML','XML') THEN
938 l_line_type := 'M';
939 ELSE
940 l_line_type := p_line_type;
941 END IF;
942
943 /* If printing a worksheet */
944 IF p_source_action = 'WORKSHEET' THEN
945 Worksheet_Insert_Row
946 (p_line_number,
947 p_output_type,
948 p_user_id,
949 p_current_date,
950 p_language_code,
951 p_session_id,
952 p_document_item,
956 l_line_type,
953 p_print_font,
954 p_print_size,
955 p_text_line_1,
957 l_return_status);
958
959 IF l_return_status <> 'S' THEN
960 RAISE Worksheet_Insert_Error;
961 END IF;
962
963 IF p_text_line_2 IS NOT NULL THEN
964 Worksheet_Insert_Row
965 (p_line_number,
966 p_output_type,
967 p_user_id,
968 p_current_date,
969 p_language_code,
970 p_session_id,
971 p_document_item,
972 p_print_font,
973 p_print_size,
974 p_text_line_2,
975 l_line_type,
976 l_return_status);
977
978 IF l_return_status <> 'S' THEN
979 RAISE Worksheet_Insert_Error;
980 END IF;
981 END IF; /* If p_text_line_2 is NOT NULL */
982
983 ELSE /* Printing a document */
984 Document_Insert_Row
985 (p_line_number,
986 p_output_type,
987 p_document_text_id,
988 p_user_id,
989 p_current_date,
990 p_session_id,
991 p_document_item,
992 p_print_font,
993 p_print_size,
994 p_text_line_1,
995 l_line_type,
996 l_return_status,
997 l_msg_data);
998
999 IF l_return_status <> 'S' THEN
1000 RAISE Worksheet_Insert_Error;
1001 END IF;
1002
1003 IF p_text_line_2 IS NOT NULL THEN
1004 Document_Insert_Row
1005 (p_line_number,
1006 p_output_type,
1007 p_document_text_id,
1008 p_user_id,
1009 p_current_date,
1010 p_session_id,
1011 p_document_item,
1012 p_print_font,
1013 p_print_size,
1014 p_text_line_2,
1015 l_line_type,
1016 l_return_status,
1017 l_msg_data);
1018
1019
1020 IF l_return_status <> 'S' THEN
1021 RAISE Worksheet_Insert_Error;
1022 END IF;
1023 END IF; /* If p_text_line_2 IS NOT NULL */
1024
1025 END IF; /* Worksheet or Document */
1026
1027
1028 EXCEPTION
1029 WHEN Document_Insert_Error THEN
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031
1032 WHEN Worksheet_Insert_Error THEN
1033 x_return_status := FND_API.G_RET_STS_ERROR;
1034
1035 WHEN OTHERS THEN
1036 x_return_status := FND_API.G_RET_STS_ERROR;
1037
1038 END Insert_Data_Record;
1039
1040
1041
1042
1043 /*===========================================================================
1044 -- PROCEDURE:
1045 -- Insert_Gr_Work_Worksheets
1046 --
1047 -- DESCRIPTION:
1048 -- This PL/SQL procedure is used to set the line number and insert data
1049 -- into gr_work_worksheets. The line numbers will be spaced out differently
1050 -- for XML and HTML in order to correctly associate the data.
1051 --
1052 -- PARAMETERS:
1053 -- p_output_type IN VARCHAR2 - 'PDF','XML','HTML', etc
1054 -- p_line_number IN OUT NOCOPY NUMBER - Line number of inserted record
1055 -- p_session_id IN NUMBER - Session id for report
1056 -- p_item_code IN VARCHAR2 - Item that document is being printed for
1057 -- p_print_font IN VARCHAR2 - Type of font to print the text in
1058 -- p_print_size IN NUMBER - Size of font to print the text in
1059 -- p_text_line IN VARCHAR2 - Text to be inserted
1060 -- p_line_type IN VARCHAR2 - Type of value being inserted
1061 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
1062 --
1063 -- SYNOPSIS:
1064 -- Insert_Gr_Work_Worksheets (g_output_type,g_line_number,g_session_id,l_item_code,
1065 -- l_print_font,l_print_size,l_text_line,l_line_type,l_return_status);
1066 --
1067 -- HISTORY
1068 --=========================================================================== */
1069 PROCEDURE Insert_Gr_Work_Worksheets
1070 (p_output_type IN VARCHAR2,
1071 p_line_number IN OUT NOCOPY NUMBER,
1072 p_session_id IN NUMBER,
1073 p_item_code IN VARCHAR2,
1074 p_print_font IN VARCHAR2,
1075 p_print_size IN NUMBER,
1076 p_text_line IN VARCHAR2,
1077 p_line_type IN VARCHAR2,
1078 x_return_status OUT NOCOPY VARCHAR2)
1079 IS
1080
1081 BEGIN
1082 x_return_status := 'S';
1083
1084 /* Printing XML or HTML */
1085 IF p_output_type in ('XML','HTML') THEN
1089 END IF;
1086 p_line_number := p_line_number + 10000;
1087 ELSE /* Printing pdf */
1088 p_line_number := p_line_number + 1;
1090
1091 INSERT INTO gr_work_worksheets
1092 (session_id,
1093 text_line_number,
1094 item_code,
1095 print_font,
1096 print_size,
1097 text_line,
1098 line_type)
1099 VALUES
1100 (p_session_id,
1101 p_line_number,
1102 p_item_code,
1103 p_print_font,
1104 p_print_size,
1105 p_text_line,
1106 p_line_type);
1107
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 FND_FILE.PUT(FND_FILE.LOG,' PROCEDURE WORKSHEET INSERT: '||sqlerrm);
1111 FND_FILE.NEW_LINE(FND_FILE.LOG,1);
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1113
1114 END Insert_Gr_Work_Worksheets;
1115
1116
1117
1118
1119 /*===========================================================================
1120 -- PROCEDURE:
1121 -- Insert_XML_Data
1122 --
1123 -- DESCRIPTION:
1124 -- This PL/SQL procedure is used to insert XML data for the document
1125 -- if the output type is XML or HTML.
1126 --
1127 -- PARAMETERS:
1128 -- p_output_type IN VARCHAR2 - 'PDF','XML','HTML', etc
1129 -- p_session_id IN NUMBER - Session id for report
1130 -- p_document_item IN VARCHAR2 - Item that document is being printed for
1131 -- p_print_font IN VARCHAR2 - Type of font to print the text in
1132 -- p_print_size IN NUMBER - Size of font to print the text in
1133 -- p_value IN VARCHAR2 - Text to be inserted
1134 -- p_line_type IN VARCHAR2 - Type of value being inserted
1135 -- p_source IN VARCHAR2 - 'WORKSHEET' or 'DOCUMENT'
1136 -- x_return_status OUT NOCOPY VARCHAR2 - 'S'uccess, 'E'rror, 'U'nexpected Error
1137 --
1138 -- SYNOPSIS:
1142 -- HISTORY
1139 -- Insert_XML_Data(g_output_type,g_session_id,'',6,RPAD(G_INGSAFETY_HDG,l_max_safe),
1140 -- 'COL',p_source_procedure,l_return_status);
1141 --
1143 --=========================================================================== */
1144 PROCEDURE Insert_XML_Data
1145 (p_line_number IN OUT NOCOPY NUMBER,
1146 p_output_type IN VARCHAR2,
1147 p_user_id IN NUMBER,
1148 p_current_date IN DATE,
1149 p_language_code IN VARCHAR2,
1150 p_document_text_id IN NUMBER,
1151 p_session_id IN NUMBER,
1152 p_document_item IN VARCHAR2,
1153 p_print_font IN VARCHAR2,
1154 p_print_size IN NUMBER,
1155 p_value IN VARCHAR2,
1156 p_line_type IN VARCHAR2,
1157 p_source IN VARCHAR2,
1158 x_return_status OUT NOCOPY VARCHAR2)
1159 IS
1160
1161 /* ------------- LOCAL VARIABLES ------------------- */
1162 L_RETURN_STATUS VARCHAR2(4);
1163 L_MSG_DATA VARCHAR2(2000);
1164
1165
1166 /* ------------------ EXCEPTIONS ---------------------- */
1167 WORKSHEET_INSERT_ERROR EXCEPTION;
1168
1169
1170 BEGIN
1171 x_return_status := 'S';
1172
1173 /* Only run through this code is printing XML or HTML */
1174 IF p_output_type in ('XML','HTML') THEN
1175
1176 Insert_Data_Record
1177 (p_line_number,
1178 p_line_type,
1179 p_output_type,
1180 p_user_id,
1181 p_current_date,
1182 p_language_code,
1183 p_document_text_id,
1184 p_session_id,
1185 p_document_item,
1186 p_print_font,
1187 p_print_size,
1188 p_value,
1189 NULL,
1190 p_source,
1191 l_return_status);
1192
1193 IF l_return_status <> 'S' THEN
1194 RAISE Worksheet_Insert_Error;
1195 END IF;
1196
1197 END IF; /* If printing XML or HTML */
1198
1199 EXCEPTION
1200 WHEN Worksheet_Insert_Error THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 WHEN OTHERS THEN
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204
1205 END Insert_XML_Data;
1206
1207
1208 END GR_PROCESS_DOCUMENTS_INSERTS;
1209
[Home] [Help]