1 PACKAGE BODY WSH_DOC_SETS AS
2 /* $Header: WSHUSDSB.pls 115.4 99/07/16 08:23:54 porting ship $ */
3
4 -- Name
5 -- Print_Document_Sets
6 -- Purpose
7 -- Execute any Delivery-based Document Set by submitting each document
8 -- to the transaction mananger and printing each report on the pre-customized
9 -- printer
10 -- Arguments
11 -- many - all required parameters for all the documents in the set must be
12 -- supplied on calling the package (hence the long list). Any parameters that are
13 -- not supplied will default to the default value as defined in the concurrent
14 -- program. HOWEVER: if all mandatory parameters are not supplied (either directly
15 -- to this package, or as default values in the Conc Prog Defn) then the report
16 -- cannot be submitted.
17 -- THIS DOES NOT SUPPORT
18 -- parameter default values (ie those defined in the Con Prg Defn) with sql
19 -- statements which reference other flex fields or profile values. ie for sql
20 -- defined default values, this only supports standard sql. (because it takes
21 -- the sql strings and plugs it into dynamic sql).
22 -- Likewise, any translation to internal values through table validated value
23 -- sets must contain standard sql in the where clause of the value set.
24 -- Unsupported sql defaults will be ignored.
25 -- IT DOES SUPPORT default values which are constants, profiles or simple sql.
26 -- Notes
27 -- USER DEFINED REPORTS
28 -- if the user defines their own reports they should restrict parameter names
29 -- to those used in this package. Additional they may use P_TEXT1 - P_TEXT4.
30
31 -- use the following select to ensure all parameter assigned to reports are
32 -- included as a parameter to this package.
33 --
34 -- we are trying to phase out OEXSHSKI + OEXSHOBR parameters. Do not use these for new
35 -- reports. instead convert to the new style of parameter/token names
36 /*
37 select cp.concurrent_program_name, col.column_seq_num seq, col.srw_param token, col.required_flag
38 from fnd_concurrent_programs_vl cp, fnd_descr_flex_column_usages col
39 where col.application_id = 300 and cp.application_id = 300
40 and col.descriptive_flexfield_name = '$SRS$.'||cp.Concurrent_program_name
41 and cp.enabled_flag = 'Y'
42 and ( cp.concurrent_program_name ='WSHRDPIK')
46
43 and col.srw_param is not null
44 order by cp.concurrent_program_name,col.column_seq_num,col.application_column_name
45 */
47 /* Included P_PROG_REQUEST_ID as a fix for bug 859003 */
48
49 PROCEDURE Print_Document_Sets (X_report_set_id IN number,
50 P_BATCH_NAME in varchar2 DEFAULT NULL,
51 P_BATCH_ID in varchar2 DEFAULT NULL,
52 P_PROG_REQUEST_ID in varchar2 DEFAULT NULL,
53 P_CATEGORY_HIGH in varchar2 DEFAULT NULL, /* oexshski only */
54 P_CATEGORY_LOW in varchar2 DEFAULT NULL, /* oexshski only */
55 P_CUSTOMER_ITEMS in varchar2 DEFAULT NULL,
56 P_DELIVERY_ID in varchar2 DEFAULT NULL,
57 P_DEPARTURE_DATE_HI in varchar2 DEFAULT NULL,
58 P_DEPARTURE_DATE_LO in varchar2 DEFAULT NULL,
59 P_DEPARTURE_ID in varchar2 DEFAULT NULL,
60 P_FREIGHT_CARRIER in varchar2 DEFAULT NULL,
61 P_ITEM in varchar2 DEFAULT NULL,
62 P_ITEM_DISPLAY in varchar2 DEFAULT NULL,
63 P_ITEM_FLEX_CODE in varchar2 DEFAULT NULL,
64 P_LINE_FLAG in varchar2 DEFAULT NULL, /* oexshski only */
65 P_LOCATOR_FLEX_CODE in varchar2 DEFAULT NULL,
66 P_ORDER_CATEGORY in varchar2 DEFAULT NULL, /* oexshobr only */
67 P_ORDER_TYPE_HIGH in varchar2 DEFAULT NULL, /* oexshski only */
68 P_ORDER_TYPE_LOW in varchar2 DEFAULT NULL, /* oexshski only */
69 P_ORGANIZATION_ID in varchar2 DEFAULT NULL, /* oexshobr only */
70 P_PICK_SLIP_NUMBER_HIGH in varchar2 DEFAULT NULL, /* oexshobr only */
71 P_PICK_SLIP_NUMBER_LOW in varchar2 DEFAULT NULL, /* oexshobr only */
72 P_PRINT_DESCRIPTION in varchar2 DEFAULT NULL, /* oexshobr only */
73 P_RELEASE_DATE_HIGH in varchar2 DEFAULT NULL, /* oexshobr only */
74 P_RELEASE_DATE_LOW in varchar2 DEFAULT NULL, /* oexshobr only */
75 P_RESERVATIONS in varchar2 DEFAULT NULL, /* oexshobr only */
76 P_SHIP_DATE_HIGH in varchar2 DEFAULT NULL, /* oexshski only */
77 P_SHIP_DATE_LOW in varchar2 DEFAULT NULL, /* oexshski only */
78 P_SOB_ID in varchar2 DEFAULT NULL,
79 P_USE_FUNCTIONAL_CURRENCY in varchar2 DEFAULT NULL,
80 P_WAREHOUSE in varchar2 DEFAULT NULL, /* oexshobr only */
81 P_WAREHOUSE_HIGH in varchar2 DEFAULT NULL, /* oexshski only */
82 P_WAREHOUSE_ID in varchar2 DEFAULT NULL,
83 P_WAREHOUSE_LOW in varchar2 DEFAULT NULL, /* oexshski only */
84 P_TEXT1 in varchar2 default null,
85 P_TEXT2 in varchar2 default null,
86 P_TEXT3 in varchar2 default null,
87 P_TEXT4 in varchar2 default null,
88 message_string in out varchar2,
89 status in out boolean) is
90
91
92 BEGIN
93 declare
94 shipping_style VARCHAR2(15);
95 release_name VARCHAR2(15);
96 prod_version VARCHAR2(15);
97 x_application_id NUMBER;
98 x_concurrent_program_name VARCHAR(40);
99 x_concurrent_program_id NUMBER;
100 x_execution_method_code VARCHAR2(10);
101
102 arg_cnt NUMBER;
103 x_request_id NUMBER;
104 total_docs number:=0;
105 submitted_docs number:=0;
106
107 valid_params BOOLEAN := TRUE;
108 error_in_a_doc BOOLEAN := FALSE;
109
110 X_Cursor NUMBER;
111 X_Rows NUMBER;
112 sql_value Varchar2(100);
113 X_Stmt_Num NUMBER;
114
115 arg_value varchar2(240);
116 arg_name varchar2(30);
117 arg_required_flag varchar2(1);
118 arg_default_value varchar2(2000);
119 arg_default_type varchar2(1);
120 arg_value_set_id number;
121
122 TYPE arg_table IS TABLE OF VARCHAR(80) INDEX BY BINARY_INTEGER;
123 argument arg_table;
124
125 cursor DOCUMENT_SET is
126 select a.application_id,
127 a.application_short_name,
128 f.concurrent_program_id,
129 f.concurrent_program_name,
130 f.user_concurrent_program_name,
131 f.printer_name default_printer_name,
132 f.output_print_style,
133 f.save_output_flag,
134 f.print_flag,
135 f.execution_method_code
136 from fnd_concurrent_programs_vl f,
137 so_report_set_lines rs, fnd_application a
138 where rs.report_set_id = X_report_set_id
139 and rs.report_id = f.concurrent_program_id
140 and rs.application_id = f.application_id
141 and a.application_id = f.application_id
142 and f.enabled_flag = 'Y'
143 order by rs.report_sequence;
144
145 Cursor DOCUMENT_PARAMS is
146 select decode (upper(decode(x_execution_method_code,'P',srw_param,end_user_column_name)),
147 'P_BATCH_NAME',P_BATCH_NAME,
148 'P_BATCH_ID',P_BATCH_ID,
149 'P_PROG_REQUEST_ID',P_PROG_REQUEST_ID,
150 'P_CATEGORY_HIGH',P_CATEGORY_HIGH,
151 'P_CATEGORY_LOW',P_CATEGORY_LOW,
152 'P_CUSTOMER_ITEMS',P_CUSTOMER_ITEMS,
153 'P_DELIVERY_ID',P_DELIVERY_ID,
154 'P_DEPARTURE_DATE_HI',P_DEPARTURE_DATE_HI,
155 'P_DEPARTURE_DATE_LO',P_DEPARTURE_DATE_LO,
156 'P_DEPARTURE_ID',P_DEPARTURE_ID,
157 'P_FREIGHT_CARRIER',P_FREIGHT_CARRIER,
158 'P_ITEM',P_ITEM,
159 'P_ITEM_DISPLAY',P_ITEM_DISPLAY,
160 'P_ITEM_FLEX_CODE',P_ITEM_FLEX_CODE,
161 'P_LINE_FLAG',P_LINE_FLAG,
165 'P_ORDER_TYPE_LOW',P_ORDER_TYPE_LOW,
162 'P_LOCATOR_FLEX_CODE',P_LOCATOR_FLEX_CODE,
163 'P_ORDER_CATEGORY',P_ORDER_CATEGORY,
164 'P_ORDER_TYPE_HIGH',P_ORDER_TYPE_HIGH,
166 'P_ORGANIZATION_ID',P_ORGANIZATION_ID,
167 'P_PICK_SLIP_NUMBER_HIGH',P_PICK_SLIP_NUMBER_HIGH,
168 'P_PICK_SLIP_NUMBER_LOW',P_PICK_SLIP_NUMBER_LOW,
169 'P_PRINT_DESCRIPTION',P_PRINT_DESCRIPTION,
170 'P_RELEASE_DATE_HIGH',P_RELEASE_DATE_HIGH,
171 'P_RELEASE_DATE_LOW',P_RELEASE_DATE_LOW,
172 'P_RESERVATIONS',P_RESERVATIONS,
173 'P_SHIP_DATE_HIGH',P_SHIP_DATE_HIGH,
174 'P_SHIP_DATE_LOW',P_SHIP_DATE_LOW,
175 'P_SOB_ID',P_SOB_ID,
176 'P_USE_FUNCTIONAL_CURRENCY',P_USE_FUNCTIONAL_CURRENCY,
177 'P_WAREHOUSE',P_WAREHOUSE,
178 'P_WAREHOUSE_HIGH',P_WAREHOUSE_HIGH,
179 'P_WAREHOUSE_ID',P_WAREHOUSE_ID,
180 'P_WAREHOUSE_LOW',P_WAREHOUSE_LOW,
181 'P_TEXT1',P_TEXT1,
182 'P_TEXT2',P_TEXT2,
183 'P_TEXT3',P_TEXT3,
184 'P_TEXT4',P_TEXT4,
185 'UNSUPPORTED') arg_value,
186 end_user_column_name,
187 required_flag,
188 default_value,
189 default_type,
190 flex_value_set_id
191 from fnd_descr_flex_column_usages
192 where application_id = x_application_id
193 and descriptive_flexfield_name = '$SRS$.'||x_concurrent_program_name
194 and enabled_flag = 'Y'
195 order by column_seq_num;
196
197 Cursor value_set_cursor (X_value_set_id in number) is
198 select
199 'select '||ID_COLUMN_NAME||
200 ' from '||APPLICATION_TABLE_NAME,
201 ADDITIONAL_WHERE_CLAUSE,
202 ' and '|| VALUE_COLUMN_NAME||'=:value'||
203 ' and '|| ENABLED_COLUMN_NAME||'=''Y'''||
204 ' and nvl('|| START_DATE_COLUMN_NAME||',sysdate)<=sysdate'||
205 ' and nvl('|| END_DATE_COLUMN_NAME||',sysdate)>=sysdate'
206 from fnd_flex_validation_tables
207 where flex_value_set_id = X_value_set_id
208 and id_column_name is not null;
209
210 select_clause varchar2(250);
211 where_clause varchar2(2000);
212 additional_clause varchar2(250);
213 value_set_lookup varchar2(2000);
214
215
216 printer_setup boolean;
217 printer_name varchar2(30);
218 save_output boolean;
219 printer_level number;
220 cursor report_level (X_concurrent_program_id NUMBER, X_application_id NUMBER) is
221 SELECT MAX(LEVEL_TYPE_ID)
222 FROM SO_REPORT_PRINTERS
223 WHERE REPORT_ID = X_concurrent_program_id
224 AND APPLICATION_id = x_application_id
225 AND LEVEL_VALUE_ID = DECODE(LEVEL_TYPE_ID,
226 10001,0, 10002, FND_GLOBAL.RESP_APPL_ID,
227 10003,FND_GLOBAL.RESP_ID, 10004, FND_GLOBAL.USER_ID)
228 AND ENABLE_FLAG = 'Y';
229
230
231 cursor report_printer (X_concurrent_program_id NUMBER,
232 X_application_id NUMBER, X_printer_level NUMBER) is
233 SELECT NVL(PRINTER_NAME, 'No Printer')
234 FROM SO_REPORT_PRINTERS
235 WHERE REPORT_ID = X_concurrent_program_id
236 AND APPLICATION_id = X_application_id
237 AND LEVEL_TYPE_ID = X_printer_level
238 AND LEVEL_VALUE_ID = DECODE(X_printer_level,
239 10001,0, 10002, FND_GLOBAL.RESP_APPL_ID,
240 10003,FND_GLOBAL.RESP_ID, 10004, FND_GLOBAL.USER_ID)
241 AND ENABLE_FLAG = 'Y';
242
243 no_reportset_to_process EXCEPTION;
244
245
246
247
248 begin
249
250 if x_report_set_id is null then
251 raise no_reportset_to_process;
252 end if;
253
254 -- for each document in the document set, select its parameters
255 -- and then launch it.
256 FOR DOCUMENT IN DOCUMENT_SET LOOP
257
258 total_docs := total_docs +1;
259
260 x_concurrent_program_name := document.concurrent_program_name;
261 x_application_id := document.application_id;
262 x_execution_method_code := document.execution_method_code;
263
264 WSH_UTIL.WRITE_LOG('Processing document ' || x_concurrent_program_name, 3);
265
266 arg_cnt := 0;
267 valid_params := TRUE;
268
269 OPEN DOCUMENT_PARAMS;
270
271 -- fetch each parameter for the document
272 -- both the arg name, the value passed in to this package and any dflt value
273 LOOP
274 FETCH DOCUMENT_PARAMS
275 INTO arg_value,
276 arg_name,
277 arg_required_flag,
278 arg_default_value,
279 arg_default_type,
280 arg_value_set_id;
281
282 EXIT WHEN (document_params%notfound) or (NOT valid_params);
283
284 arg_cnt := arg_cnt + 1;
285
286 WSH_UTIL.WRITE_LOG('Argument Name ' || arg_name, 3);
287 WSH_UTIL.WRITE_LOG('Argument Value ' || arg_value, 3);
288 WSH_UTIL.WRITE_LOG('Argument Required ' || arg_required_flag, 3);
289
290 if arg_value <> 'UNSUPPORTED' then
291 argument(arg_cnt) := arg_value;
292 else
293 argument(arg_cnt) := null;
294 end if;
295
296 -- if the argument does not have a value, then check its default
297 -- as defined in the concurrent program definition
298 if argument(arg_cnt) is null then
299 -- only check for Constants or Profile values
300 if arg_default_type = 'C' then -- Constant
301 argument(arg_cnt) := arg_default_value;
305 -- use dynamic sql to get the default value.
302 elsif arg_default_type = 'P' then -- Profile
303 argument(arg_cnt) := fnd_profile.value(arg_default_value);
304 elsif arg_default_type = 'S' then -- Sql
306 -- NOTE not all values will be defined if this references another
307 -- flex field, this will cause an error in which case continue
308 begin
309 begin
310
311 X_Cursor := dbms_sql.open_cursor;
312 dbms_sql.parse(X_Cursor,arg_default_value,dbms_sql.v7);
313 DBMS_SQL.Define_Column(X_cursor, 1, sql_value, 100 );
314 X_Rows := dbms_sql.execute(X_Cursor);
315 X_Rows := dbms_sql.fetch_rows(X_Cursor);
316 DBMS_SQL.Column_Value(X_cursor, 1, sql_value);
317 IF dbms_sql.is_open(X_Cursor) THEN
318 dbms_sql.close_cursor(X_Cursor);
319 END IF;
320
321 -- dbms_output.put_line('Value for dynamic sql select is '||sql_value);
322 argument(arg_cnt) := sql_value;
323 end;
324 exception when others then null;
325 end;
326 end if;
327
328 -- we now have the default value. If this is validated against a table value set
329 -- which select an id_column, then we must convert the user-friendly default
330 -- value to its internal value using the value set.
331 if argument(arg_cnt) is not null then
332
333 open value_set_cursor(arg_value_set_id);
334 fetch value_set_cursor into select_clause, where_clause, additional_clause;
335 if (value_set_cursor%found) then
336 if substr(upper(where_clause),1,5) = 'WHERE' then
337 where_clause := ' and '||substr(where_clause,6);
338 end if;
339
340 -- always put where clause at end as it may include an ORDER_BY clause
341 value_set_lookup := select_clause||' where 1=1 ' || additional_clause ||' '|| where_clause;
342
343 -- dbms_output.put_line('Value for dynamic sql is: '||value_set_lookup);
344 -- dbms_output.put_line('Where value = '||sql_value);
345 begin
346 X_Cursor := dbms_sql.open_cursor;
347 dbms_sql.parse(X_Cursor,value_set_lookup,dbms_sql.v7);
348 DBMS_SQL.Bind_Variable(X_cursor,':value',argument(arg_cnt));
349 DBMS_SQL.Define_Column(X_cursor, 1, sql_value, 255 );
350 X_Rows := dbms_sql.execute(X_Cursor);
351 X_Rows := dbms_sql.fetch_rows(X_Cursor);
352 DBMS_SQL.Column_Value(X_cursor, 1, sql_value);
353 exception when others then
354 -- dbms_output.put_line('Error in dynamic sql; arg_value_set_id:'||to_char(arg_value_set_id));
355 -- dbms_output.put_line('Error in dynamic sql:'||value_set_lookup);
356 -- dbms_output.put_line('Where value = '||sql_value);
357 -- dont interupt: set this param to null. If its required, then user
358 -- will be informed there is a missing required flag.
359 sql_value := NULL;
360 end;
361
362 IF dbms_sql.is_open(X_Cursor) THEN
363 dbms_sql.close_cursor(X_Cursor);
364 END IF;
365 if sql_value is not null then
366 argument(arg_cnt) := sql_value;
367 end if;
368
369 end if;
370 close value_set_cursor;
371 end if;
372
373 end if;
374
375 -- if still null and its required then raise appropriate error
376 if (argument(arg_cnt) is null) and arg_required_flag = 'Y' then
377 if arg_value = 'UNSUPPORTED' then
378 FND_MESSAGE.Set_Name('OE','WSH_UNSUPPORTED_ARG');
379 FND_MESSAGE.Set_Token('ARGUMENT',arg_name);
380 FND_MESSAGE.Set_Token('DOCUMENT',
381 document.user_concurrent_program_name);
382 WSH_UTIL.Write_Log('WSH_UNSUPPORTED_ARG IN DOC '||
383 arg_name||' '||
384 document.user_concurrent_program_name);
385 else
386 FND_MESSAGE.Set_Name('OE','WSH_NULL_ARG_IN_DOC');
387 FND_MESSAGE.Set_Token('ARGUMENT',arg_name);
388 FND_MESSAGE.Set_Token('DOCUMENT',
389 document.user_concurrent_program_name);
390 WSH_UTIL.Write_Log('WSH_NULL_ARG IN DOC '||arg_name||' '||
391 x_concurrent_program_name);
392 end if;
393 -- set error_flags to stop processing this document
394 valid_params := FALSE;
395 error_in_a_doc := TRUE;
396 end if;
397
398 END LOOP;
399 CLOSE DOCUMENT_PARAMS;
400
401 if VALID_PARAMS then
402 -- loop through the rest of the arguments (upto 30) setting any
403 -- remaining ones to null for unassigned.
404 WHILE arg_cnt < 30 LOOP
405 arg_cnt := arg_cnt +1;
406 argument(arg_cnt) := '';
407 END LOOP;
408
409 -- set up the printer
410
411 if document.print_flag = 'Y' then
412
413 -- dbms_output.put_line(' applid='||to_char(FND_GLOBAL.RESP_APPL_ID)||
414 -- ' resp_id='||to_char(FND_GLOBAL.RESP_ID) ||
415 -- ' user_id='||to_char(FND_GLOBAL.USER_ID));
416
417 -- dbms_output.put_line(' prog_id='||(document.concurrent_program_id)||
418 -- ' app='|| to_char(document.application_id));
419
420 open report_level(document.concurrent_program_id, document.application_id);
421 fetch report_level into printer_level;
422 close report_level;
423
424 printer_name := null;
425 open report_printer(document.concurrent_program_id,
426 document.application_id, printer_level);
427 fetch report_printer into printer_name;
428 close report_printer;
429
430 if printer_name is null or printer_name = 'No Printer' then
431 printer_name := document.default_printer_name;
432 end if;
433
434 if document.save_output_flag = 'Y' then
435 save_output := TRUE;
436 else
437 save_output := FALSE;
438 end if;
439
440 if printer_name is not null then
441 printer_setup := FND_REQUEST.Set_print_options(
442 printer_name,
443 document.output_print_style,
444 1, save_output, 'N');
445 end if;
446 end if;
447 -- go ahead and submit this document as a request
448 x_request_id := FND_REQUEST.Submit_Request(
449 document.application_short_name,
450 document.concurrent_program_name,'','',FALSE,
451 argument(1), argument(2), argument(3), argument(4), argument(5),
452 argument(6), argument(7), argument(8), argument(9), argument(10),
453 argument(11),argument(12),argument(13),argument(14),argument(15),
454 argument(16),argument(17),argument(18),argument(19),argument(20),
455 argument(21),argument(22),argument(23),argument(24),argument(25),
456 argument(26),argument(27),argument(28),argument(29),argument(30),
457 '','','','','','','','','','',
458 '','','','','','','','','','',
459 '','','','','','','','','','',
460 '','','','','','','','','','',
461 '','','','','','','','','','',
462 '','','','','','','','','','',
463 '','','','','','','','','','');
464
465 -- increase the counter if successful
466 if x_request_id > 0 then
467 submitted_docs := submitted_docs +1;
468 WSH_UTIL.WRITE_LOG('Request ID ' || to_char(x_request_id), 3);
469 end if;
470
471 end if;
472 END LOOP;
473
474 if error_in_a_doc then
475 -- an error occured in at least one document submission
476 -- because of a missing required argument or an unsupported argument
477
478 status := FALSE;
479
480 elsif (total_docs = 0 ) then
481 -- successfully looped through all documents but didnt submit any
482 -- probably because there werent any in the set (but may have had problems
483 -- in fnd_request function
484
485 WSH_UTIL.Write_Log('no documents in document set');
486 FND_MESSAGE.Set_Name('OE','WSH_NO_DOCS');
487 status := FALSE;
488
489 else
490 -- everthing worked: any documents not submitted resulted
491 -- from problem in fnd_request
492 status := TRUE;
493 WSH_UTIL.Write_Log('Submitted '||to_char(submitted_docs)||
494 ' out of '||to_char(total_docs));
495
496 FND_MESSAGE.Set_Name('OE','WSH_DOCS_SUBMITTED');
497 FND_MESSAGE.Set_Token('SUBMITTED_DOCS',submitted_docs);
498 FND_MESSAGE.Set_Token('TOTAL_DOCS',total_docs);
499 end if;
500
501 message_string := FND_MESSAGE.get;
502
503
504 EXCEPTION
505 WHEN NO_REPORTSET_TO_PROCESS THEN
506 WSH_UTIL.Write_Log('No Reports to process');
507 null;
508 WHEN OTHERS THEN
509 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
510 FND_MESSAGE.Set_Token('PACKAGE','WSH_DOC_SETS.Print_document_sets');
511 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
512 FND_MESSAGE.Set_Token('ORA_TEXT','Unexpected exception');
513 message_string := FND_MESSAGE.get;
514 end;
515 END Print_Document_sets;
516
517 END WSH_DOC_SETS;
[Home] [Help]