[Home] [Help]
PACKAGE BODY: APPS.WSH_DOCUMENT_SETS
Source
1 PACKAGE BODY WSH_DOCUMENT_SETS as
2 /* $Header: WSHDSPRB.pls 120.9 2008/01/23 13:18:53 jnpinto 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 --
12 -- Either the p_report_set_id and one of p_trip_ids, p_stop_ids, and
13 -- p_delivery_ids in parameters should be used or the
14 -- p_document_param_info. The former method is primarily for the
15 -- Transactions form.
16 --
17 -- many - all required parameters for all the documents in the set must be
18 -- supplied on calling the package (hence the long list). Any parameters that are
19 -- not supplied will default to the default value as defined in the concurrent
20 -- program. HOWEVER: if all mandatory parameters are not supplied (either directly
21 -- to this package, or as default values in the Conc Prog Defn) then the report
22 -- cannot be submitted.
23 -- THIS DOES NOT SUPPORT
24 -- parameter default values (ie those defined in the Con Prg Defn) with sql
25 -- statements which reference other flex fields or profile values. ie for sql
26 -- defined default values, this only supports standard sql. (because it takes
27 -- the sql strings and plugs it into dynamic sql).
28 -- Likewise, any translation to internal values through table validated value
29 -- sets must contain standard sql in the where clause of the value set.
30 -- Unsupported sql defaults will be ignored.
31 -- IT DOES SUPPORT default values which are constants, profiles or simple sql.
32 -- Notes
33 -- USER DEFINED REPORTS
34 -- if the user defines their own reports they should restrict parameter names
35 -- to those used in this package. Additional they may use P_TEXT1 - P_TEXT4.
36
37 --
38 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DOCUMENT_SETS';
39 --
40 PROCEDURE Print_Document_Sets
41 (p_report_set_id IN NUMBER,
42 p_organization_id IN NUMBER,
43 p_trip_ids IN WSH_UTIL_CORE.Id_Tab_Type,
44 p_stop_ids IN WSH_UTIL_CORE.Id_Tab_Type,
45 p_delivery_ids IN WSH_UTIL_CORE.Id_Tab_Type,
46 p_document_param_info IN WSH_DOCUMENT_SETS.DOCUMENT_SET_TAB_TYPE,
47 x_return_status IN OUT NOCOPY VARCHAR2)
48 IS
49
50 --
51 l_debug_on BOOLEAN;
52 --
53 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PRINT_DOCUMENT_SETS';
54 --
55
56 BEGIN
57 --
58 -- Debug Statements
59 --
60 --
61 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
62 --
63 IF l_debug_on IS NULL
64 THEN
65 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
66 END IF;
67 --
68 IF l_debug_on THEN
69 WSH_DEBUG_SV.push(l_module_name);
70 --
71 WSH_DEBUG_SV.log(l_module_name,'P_REPORT_SET_ID',P_REPORT_SET_ID);
72 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
73 WSH_DEBUG_SV.log(l_module_name,'P_TRIP_IDS.COUNT',P_TRIP_IDS.COUNT);
74 WSH_DEBUG_SV.log(l_module_name,'P_STOP_IDS.COUNT',P_STOP_IDS.COUNT);
75 WSH_DEBUG_SV.log(l_module_name,'P_DELIVERY_IDS.COUNT',P_DELIVERY_IDS.COUNT);
76
77 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.COUNT',P_DOCUMENT_PARAM_INFO.COUNT);
78 /*
79 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.report_set_id',P_DOCUMENT_PARAM_INFO.p_report_set_id);
80 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.request_id',P_DOCUMENT_PARAM_INFO.p_request_id);
81 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.customer_id',P_DOCUMENT_PARAM_INFO.p_customer_id);
82 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.item_id',P_DOCUMENT_PARAM_INFO.p_item_id);
83 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.item_cate_set_id',P_DOCUMENT_PARAM_INFO.p_item_cate_set_id);
84 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.item_category_id',P_DOCUMENT_PARAM_INFO.p_item_category_id);
85 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.transaction_type_id',P_DOCUMENT_PARAM_INFO.p_transaction_type_id);
86 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.header_id_low',P_DOCUMENT_PARAM_INFO.p_header_id_high);
87 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.salesrep_id',P_DOCUMENT_PARAM_INFO.p_salesrep_id);
88 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.user_id',P_DOCUMENT_PARAM_INFO.p_user_id);
89 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.territory_name',P_DOCUMENT_PARAM_INFO.p_territory_name);
90 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.organization_id',P_DOCUMENT_PARAM_INFO.p_organization_id);
91 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.trip_id',P_DOCUMENT_PARAM_INFO.p_trip_id);
92 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.dleg_id',P_DOCUMENT_PARAM_INFO.p_delivery_leg_id);
93 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.bol_num',P_DOCUMENT_PARAM_INFO.p_bill_of_lading_number);
94 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.trip_stop_id',P_DOCUMENT_PARAM_INFO.p_trip_stop_id);
95 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.delivery_id',P_DOCUMENT_PARAM_INFO.p_delivery_id);
96 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.Order_Num_Lo',P_DOCUMENT_PARAM_INFO.p_order_num_l);
97 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.Order_Num_Hi',P_DOCUMENT_PARAM_INFO.p_order_num_h);
98 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.Move_Order_Num_Lo',P_DOCUMENT_PARAM_INFO.p_move_order_l);
99 WSH_DEBUG_SV.log(l_module_name,'P_DOCUMENT_PARAM_INFO.Move_Order_Num_Hi',P_DOCUMENT_PARAM_INFO.p_move_order_h);
100 */
101
102 WSH_DEBUG_SV.log(l_module_name,'X_RETURN_STATUS',X_RETURN_STATUS);
103 END IF;
104 --
105 DECLARE
106 l_shipping_style VARCHAR2(15);
107 l_release_name VARCHAR2(15);
108 l_prod_version VARCHAR2(15);
109 l_application_id NUMBER;
110 l_concurrent_program_name VARCHAR(40);
111 l_concurrent_program_id NUMBER;
112 l_execution_method_code VARCHAR2(10);
113 --l_user_concurrent_program_name VARCHAR2(200); --Bug 1633386
114 l_user_concurrent_program_name fnd_concurrent_programs_vl.user_concurrent_program_name%TYPE;
115
116 l_arg_cnt NUMBER;
117 l_request_id NUMBER;
118 l_total_docs NUMBER :=0;
119 l_submitted_docs NUMBER :=0;
120
121 l_valid_params BOOLEAN := TRUE;
122 l_non_default_params BOOLEAN := FALSE;
123 l_error_in_a_doc BOOLEAN := FALSE;
124
125 l_cursor NUMBER;
126 l_rows NUMBER;
127 l_sql_value Varchar2(32767);
128 l_stmt_num NUMBER;
129 l_status Varchar2(100);
130 l_error_message Varchar2(4000); -- bug 2548069 (frontported): resolve ORA-6502
131
132 l_arg_value VARCHAR2(240);
133 l_arg_name fnd_descr_flex_column_usages.end_user_column_name%type;
134 l_arg_required_flag fnd_descr_flex_column_usages.required_flag%type;
135 l_arg_default_value fnd_descr_flex_column_usages.default_value%type;
136 l_arg_default_type fnd_descr_flex_column_usages.default_type%type;
137 l_arg_value_set_id fnd_descr_flex_column_usages.flex_value_set_id%type;
138 l_parameter_name VARCHAR2(32767);
139
140 No_Org_For_Entity EXCEPTION;
141
142 l_req_id_str VARCHAR2(2000);
143 l_buffer_fill VARCHAR2(1):='N';
144
145 l_main_conc_request_id NUMBER;
146
147 --- BugFix 3274604 - Start
148
149 cnt NUMBER;
150 l_ledger_id NUMBER; --LE Uptake
151 x_return_status VARCHAR2(1);
152 x_msg_count NUMBER;
153 x_msg_data VARCHAR2(255);
154 p_location_id1 NUMBER;
155 x_document_number VARCHAR2(255);
156 l_return_status VARCHAR2(1);
157 l_num_warning NUMBER;
158 l_num_errors NUMBER;
159 l_delivery_id NUMBER;
160 l_delivery_leg_id NUMBER;
161 l_ship_method_code VARCHAR2(30);
162 l_pickup_location_id NUMBER;
163 l_trip_name VARCHAR2(30);
164 l_bol_count NUMBER;
165 l_document_number VARCHAR2(255);
166 l_msg_count NUMBER;
167 l_msg_data VARCHAR2(255);
168 wsh_create_document_error EXCEPTION;
169
170
171 CURSOR c_get_delivery_info(l_delivery_id IN NUMBER) IS
172 SELECT del.delivery_id,
173 dlg.delivery_leg_id,
174 wt.ship_method_code,
175 del.initial_pickup_location_id,
176 wt.name
177 FROM wsh_new_deliveries del,
178 wsh_delivery_legs dlg,
179 wsh_trip_stops st,
180 wsh_trips wt
181 WHERE del.delivery_id = dlg.delivery_id
182 AND dlg.pick_up_stop_id = st.stop_id
183 AND st.trip_id = wt.trip_id
184 AND del.initial_pickup_location_id = st.stop_location_id
185 AND del.delivery_id = l_delivery_id;
186 --LE Uptake
187 CURSOR c_get_ledger_id(p_delivery_id IN NUMBER) IS
188 SELECT ood.set_of_books_id
189 FROM org_organization_definitions ood,
190 wsh_new_deliveries del
191 WHERE ood.organization_id = del.organization_id
192 AND del.delivery_id = p_delivery_id;
193
194 CURSOR m_get_ledger_id(p_delivery_id IN NUMBER) IS
195 SELECT hoi.org_information1
196 FROM hr_organization_information hoi,
197 wsh_new_deliveries wnd
198 WHERE wnd.delivery_id = p_delivery_id
199 AND hoi.organization_id = wnd.organization_id
200 AND hoi.org_information_context = 'Accounting Information';
201
202 CURSOR c_get_init_pickup_loc_id(p_delivery_id IN NUMBER) IS
203 SELECT initial_pickup_location_id
204 FROM WSH_NEW_DELIVERIES
205 WHERE delivery_id = p_delivery_id;
206
207 --- BugFix 3274604 - End
208
209 TYPE arg_table IS TABLE OF VARCHAR(240) INDEX BY BINARY_INTEGER;
210 l_argument arg_table;
211 l_argument_name arg_table;
212 l_printer_pos NUMBER ;
213
214 CURSOR c_document_set(p_report_set_id NUMBER) IS
215 SELECT a.application_id,
216 a.application_short_name,
217 f.concurrent_program_id,
218 f.concurrent_program_name,
219 f.user_concurrent_program_name,
220 f.printer_name default_printer_name,
221 f.output_print_style,
222 f.save_output_flag,
223 f.print_flag,
224 f.execution_method_code ,
225 nvl( rs.number_of_copies, 0 ) number_of_copies,
226 f.mls_executable_id,
227 f.output_file_type,
228 nvl(rs.template_code,f.template_code) template_code,
229 f.nls_compliant
230 FROM fnd_concurrent_programs_vl f,
231 wsh_report_set_lines rs, fnd_application a
232 WHERE rs.report_set_id = p_report_set_id
233 AND rs.concurrent_program_id = f.concurrent_program_id
234 AND rs.application_id = f.application_id
235 AND a.application_id = f.application_id
236 AND f.enabled_flag = 'Y'
237 ORDER BY rs.program_sequence;
238
239 l_doc_set_params wsh_document_sets.document_set_tab_type;
240
241 CURSOR c_document_params(i NUMBER) IS
242 SELECT
243 decode(
244 lower(decode(l_execution_method_code, 'P', srw_param, 'K',srw_param, end_user_column_name)),
245 --Bug 6766880 added l_execution_method_code K
246 'p_request_id', to_char(l_doc_set_params(i).p_request_id), -- bug 1589045
247 'p_customer_id', to_char(l_doc_set_params(i).p_customer_id),
248 'p_item_id', to_char(l_doc_set_params(i).p_item_id),
249 'p_item_cate_set_id', to_char(l_doc_set_params(i).p_item_cate_set_id),
250 'p_item_category_id', to_char(l_doc_set_params(i).p_item_category_id),
251 'p_transaction_type_id', to_char(l_doc_set_params(i).p_transaction_type_id),
252 'p_header_id_low', to_char(l_doc_set_params(i).p_header_id_low),
253 'p_header_id_high', to_char(l_doc_set_params(i).p_header_id_high),
254 'p_salesrep_id', to_char(l_doc_set_params(i).p_salesrep_id),
255 'p_user_id', to_char(l_doc_set_params(i).p_user_id),
256 'p_territory_name', l_doc_set_params(i).p_territory_name,
257 'p_item_display', l_doc_set_params(i).p_item_display,
258 'p_item_flex_code', l_doc_set_params(i).p_item_flex_code,
259 'p_organization_id', to_char(l_doc_set_params(i).p_organization_id),
260 'p_org_id', to_char(l_doc_set_params(i).p_organization_id),
261 'p_sort_by', to_char(l_doc_set_params(i).p_sort_by),
262 'p_show_functional_currency', l_doc_set_params(i).p_show_functional_currency,
263 'p_ledger_id', to_char(l_doc_set_params(i).p_ledger_id), -- LE Uptake
264 'p_order_date_low', to_char(l_doc_set_params(i).p_order_date_low),
265 'p_order_date_high', to_char(l_doc_set_params(i).p_order_date_high),
266 'p_delivery_date_low', to_char(l_doc_set_params(i).p_delivery_date_low),
267 'p_delivery_date_high', to_char(l_doc_set_params(i).p_delivery_date_high),
268 'p_freight_code', l_doc_set_params(i).p_freight_code,
269 'p_delivery_id', to_char(l_doc_set_params(i).p_delivery_id),
270 'p_delivery_id_high', to_char(l_doc_set_params(i).p_delivery_id_high),
271 'p_delivery_id_low', to_char(l_doc_set_params(i).p_delivery_id_low),
272 'p_trip_id', to_char(l_doc_set_params(i).p_trip_id),
273 'p_trip_id_high', to_char(l_doc_set_params(i).p_trip_id_high),
274 'p_trip_id_low', to_char(l_doc_set_params(i).p_trip_id_low),
275 -- 'p_delivery_leg_id', to_char(l_doc_set_params(i).p_delivery_leg_id),
276 'p_bill_of_lading_number', to_char(l_doc_set_params(i).p_bill_of_lading_number),
277 'p_trip_stop_id', to_char(l_doc_set_params(i).p_trip_stop_id),
278 'p_departure_date_low', to_char(l_doc_set_params(i).p_departure_date_low),
279 'p_departure_date_high', to_char(l_doc_set_params(i).p_departure_date_high),
280 'p_container_id', to_char(l_doc_set_params(i).p_container_id),
281 'p_print_cust_item', l_doc_set_params(i).p_print_cust_item,
282 'p_print_mode', l_doc_set_params(i).p_print_mode,
283 'p_print_all', l_doc_set_params(i).p_print_all,
284 'p_sort', l_doc_set_params(i).p_sort,
285 'p_delivery_date_lo', to_char(l_doc_set_params(i).p_delivery_date_lo),
286 'p_delivery_date_hi', to_char(l_doc_set_params(i).p_delivery_date_hi),
287 'p_freight_carrier', l_doc_set_params(i).p_freight_carrier,
288 'p_quantity_precision', l_doc_set_params(i).p_quantity_precision,
289 'p_locator_flex_code', l_doc_set_params(i).p_locator_flex_code,
290 'p_warehouse_id', to_char(l_doc_set_params(i).p_warehouse_id),
291 'p_pick_slip_num_low', to_char(l_doc_set_params(i).pick_slip_num_l),
292 'p_pick_slip_num_high', to_char(l_doc_set_params(i).pick_slip_num_h),
293 'p_order_type_id', to_char(l_doc_set_params(i).p_order_type_id), --Bugfix 3604021
294 'p_order_num_l', to_char(l_doc_set_params(i).p_order_num_l),
295 'p_order_num_h', to_char(l_doc_set_params(i).p_order_num_h),
296 'p_order_num_low', to_char(l_doc_set_params(i).p_order_num_low),
297 'p_order_num_high', to_char(l_doc_set_params(i).p_order_num_high),
298 --Bug#1577520
299 --'p_move_order_low', to_char(l_doc_set_params(i).p_move_order_l),
300 --'p_move_order_high', to_char(l_doc_set_params(i).p_move_order_h),
301 'p_move_order_low', l_doc_set_params(i).p_move_order_l,
302 'p_move_order_high', l_doc_set_params(i).p_move_order_h,
303 'p_ship_method_code', l_doc_set_params(i).p_ship_method_code,
304 'p_customer_name', l_doc_set_params(i).p_customer_name,
305 'p_pick_status', l_doc_set_params(i).p_pick_status,
306 'p_detail_date_low', to_char(l_doc_set_params(i).p_detail_date_l),
307 'p_detail_date_high', to_char(l_doc_set_params(i).p_detail_date_h),
308 'p_exception_name', l_doc_set_params(i).p_exception_name,
309 'p_logging_entity', l_doc_set_params(i).p_logging_entity,
310 'p_location_id', to_char(l_doc_set_params(i).p_location_id),
311 'p_creation_date_from', to_char(l_doc_set_params(i).p_creation_date_from),
312 'p_creation_date_to', to_char(l_doc_set_params(i).p_creation_date_to),
313 'p_last_update_date_from', to_char(l_doc_set_params(i).p_last_update_date_from),
314 'p_last_update_date_to', to_char(l_doc_set_params(i).p_last_update_date_to),
315 'p_severity', l_doc_set_params(i).p_severity,
316 'p_status', l_doc_set_params(i).p_status,
317 'p_text1', l_doc_set_params(i).p_text1,
318 'p_text2', l_doc_set_params(i).p_text2,
322 'p_printer_name', l_doc_set_params(i).p_printer_name,
319 'p_text3', l_doc_set_params(i).p_text3,
320 'p_text4', l_doc_set_params(i).p_text4,
321 'p_currency_code', l_doc_set_params(i).p_currency_code,
323 'UNSUPPORTED')
324 arg_value,
325 end_user_column_name,
326 required_flag,
327 default_value,
328 default_type,
329 flex_value_set_id,
330 lower(decode(l_execution_method_code, 'P', srw_param,'K',srw_param, end_user_column_name)) parameter_name
331 --Bug 6766880 added l_execution_method_code K
332 FROM
333 fnd_descr_flex_column_usages
334 WHERE application_id = l_application_id
335 AND descriptive_flexfield_name = '$SRS$.'||l_concurrent_program_name
336 AND enabled_flag = 'Y'
337 ORDER BY
338 column_seq_num;
339
340 CURSOR c_value_set_cursor(p_value_set_id IN NUMBER)
341 IS
342 SELECT
343 'select ' || id_column_name ||
344 ' from ' || application_table_name,
345 additional_where_clause,
346 ' and ' || value_column_name || '=:value' ||
347 ' and ' || enabled_column_name || '=''Y''' ||
348 ' and nvl(' || start_date_column_name || ',sysdate)<=sysdate' ||
349 ' and nvl(' || end_date_column_name || ',sysdate)>=sysdate'
350 FROM fnd_flex_validation_tables
351 WHERE flex_value_set_id = p_value_set_id
352 AND id_column_name IS NOT NULL;
353
354
355 CURSOR c_stop_trip_id_cursor (t_stop_id NUMBER )
356 IS
357 select distinct trip_id from wsh_trip_stops
358 where stop_id=t_stop_id;
359 -- bug 1633386
360
361 l_select_clause VARCHAR2(250);
362 l_where_clause VARCHAR2(2000);
363 l_additional_clause VARCHAR2(250);
364 l_value_set_lookup VARCHAR2(2000);
365
366 l_printer_setup BOOLEAN;
367 l_printer_name VARCHAR2(32767);
368 l_organization_id number ; -- Bug 3534965(3510460 Frontport)
369 l_save_output BOOLEAN;
370 l_printer_level NUMBER;
371
372 CURSOR c_report_level(p_concurrent_program_id NUMBER, p_application_id NUMBER)
373 IS
374 SELECT MAX(level_type_id)
375 FROM wsh_report_printers
376 WHERE concurrent_program_id = p_concurrent_program_id
377 AND application_id = p_application_id
378 AND level_value_id =
379 Decode(level_type_id,
380 10001, 0,
381 10002, fnd_global.resp_appl_id,
382 10003, fnd_global.resp_id,
383 10004, fnd_global.user_id)
384 AND enabled_flag = 'Y';
385
386 CURSOR c_report_printer
387 (p_concurrent_program_id NUMBER,
388 p_application_id NUMBER,
389 p_printer_level NUMBER)
390 IS
391 SELECT Nvl(l_printer_name, 'No Printer')
392 FROM wsh_report_printers
393 WHERE concurrent_program_id = p_concurrent_program_id
394 AND application_id = p_application_id
395 AND level_type_id = p_printer_level
396 AND level_value_id =
397 Decode(p_printer_level,
398 10001, 0,
399 10002, fnd_global.resp_appl_id,
400 10003, fnd_global.resp_id,
401 10004, fnd_global.user_id)
402 AND enabled_flag = 'Y';
403
404 no_reportset_to_process EXCEPTION;
405
406 entity_count number := 0;
407 entity_type varchar2(20);
408 entity_name varchar2(100);
409
410 CURSOR Get_Del_Org (v_del_id NUMBER) IS
411 SELECT organization_id
412 FROM WSH_NEW_DELIVERIES
413 WHERE delivery_id = v_del_id;
414
415 l_copies NUMBER := 0;
416
417 --bug 1633386
418
419 l_stop_trip_id_tmp NUMBER :=0;
420 l_delv_trip_id_tmp NUMBER :=0;
421
422 --bug 1633386
423
424 l_lang_doc_params_info WSH_DOCUMENT_SETS.document_set_rec_type;
425 l_nls_lang WSH_EXTREPS_MLS_LANG.lang_tab_type;
426 l_submitted BOOLEAN;
427 l_nls_count NUMBER;
428
429 l_template_code VARCHAR2(80);
430 l_appl_short_name VARCHAR2(50);
431 l_output_file_type VARCHAR2(4);
432 l_language VARCHAR2(2);
433 l_territory VARCHAR2(2);
434 l_ret_status BOOLEAN;
435 l_print_pdf VARCHAR2(1);
436
437
438 BEGIN
439
440 if (p_trip_ids.count <> 0) then
441 entity_count := p_trip_ids.count;
442 entity_type := 'Trip';
443 elsif (p_stop_ids.count <> 0) then
444 entity_count := p_stop_ids.count;
445 entity_type := 'Stop';
446 elsif (p_delivery_ids.count <> 0) then
447 entity_count := p_delivery_ids.count;
448 entity_type := 'Delivery';
449
450 -- Fix for Bug:2283001
451 l_doc_set_params := p_document_param_info;
452 else
453 l_doc_set_params := p_document_param_info;
454
455 IF l_doc_set_params(1).p_organization_id IS NULL THEN
456
457 IF p_organization_id IS NULL THEN
458 RAISE No_Org_For_Entity;
459 ELSE
460 l_doc_set_params(1).p_organization_id := p_organization_id;
461 l_doc_set_params(1).p_warehouse_id := l_doc_set_params(1).p_organization_id;
465 IF l_doc_set_params(1).p_report_set_id IS NULL THEN
462 END IF;
463 END IF;
464
466 IF p_report_set_id IS NULL THEN
467 RAISE No_Reportset_TO_Process;
468 ELSE
469 l_doc_set_params(1).p_report_set_id := p_report_set_id;
470 END IF;
471 END IF;
472
473 end if;
474
475 -- bug 1589045
476 l_main_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
477 if l_main_conc_request_id = -1 then
478 l_main_conc_request_id := null;
479 end if;
480
481 IF l_debug_on THEN
482 WSH_DEBUG_SV.logmsg(l_module_name,'entity_count is ' || entity_count );
483 END IF;
484 --
485 if (entity_count > 0) then --{ populate l_doc_set_params with entity information
486
487 for i in 1..entity_count loop
488
489 l_doc_set_params(i).p_report_set_id := p_report_set_id;
490
491 if (p_trip_ids.count <> 0) then -- { populate current entity's info
492 l_doc_set_params(i).p_trip_id := p_trip_ids(i);
493 l_doc_set_params(i).p_trip_id_high := p_trip_ids(i);
494 l_doc_set_params(i).p_trip_id_low := p_trip_ids(i);
495
496 -- setting org to dummy value because the report should be
497 -- org independant for trips and stops.
498
499 l_doc_set_params(i).p_warehouse_id := -1;
500 l_doc_set_params(i).p_organization_id := -1;
501
502
503 elsif (p_stop_ids.count <> 0) then
504
505 l_doc_set_params(i).p_trip_stop_id := p_stop_ids(i);
506
507 -- setting org to dummy value because the report should be
508 -- org independent for trips and stops.
509
510 l_doc_set_params(i).p_warehouse_id := -1;
511 l_doc_set_params(i).p_organization_id := -1;
512
513 else
514
515 l_doc_set_params(i).p_delivery_id := p_delivery_ids(i);
516 l_doc_set_params(i).p_delivery_id_high := p_delivery_ids(i);
517 l_doc_set_params(i).p_delivery_id_low := p_delivery_ids(i);
518
519 OPEN Get_Del_Org (p_delivery_ids(i));
520
521 FETCH Get_Del_Org INTO l_doc_set_params(i).p_organization_id;
522
523 IF Get_Del_Org%NOTFOUND THEN
524 CLOSE Get_Del_Org;
525 RAISE No_Org_For_Entity;
526 END IF;
527
528 l_doc_set_params(i).p_warehouse_id := l_doc_set_params(i).p_organization_id;
529
530 IF Get_Del_Org%ISOPEN THEN
531 CLOSE Get_Del_Org;
532 END IF;
533
534 end if; --{ populate current entity's info
535
536 end loop;
537 end if; --{ finished populating l_doc_set_params with entity information
538
539 IF l_debug_on THEN
540 WSH_DEBUG_SV.logmsg(l_module_name,'doc set param counts is ' || l_doc_set_params.COUNT);
541 END IF;
542 --
543 FOR i IN 1..l_doc_set_params.COUNT LOOP
544
545 -- bug 1589045
546 l_doc_set_params(i).p_request_id := l_main_conc_request_id;
547
548 if (p_trip_ids.count <> 0) then
549 --
550 -- Debug Statements
551 --
552 IF l_debug_on THEN
553 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
554 END IF;
555 --
556 entity_name := WSH_TRIPS_PVT.Get_Name(p_trip_ids(i));
557 elsif (p_stop_ids.count <> 0) then
558 --
559 -- Debug Statements
560 --
561 IF l_debug_on THEN
562 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
563 END IF;
564 --
565 entity_name := WSH_TRIP_STOPS_PVT.Get_Name(p_stop_ids(i));
566
567 --bug 1633386
568 OPEN c_stop_trip_id_cursor( p_stop_ids(i));
569 LOOP
570 FETCH c_stop_trip_id_cursor INTO l_stop_trip_id_tmp;
571 EXIT WHEN c_stop_trip_id_cursor%NOTFOUND ;
572 l_doc_set_params(i).p_trip_id := l_stop_trip_id_tmp;
573 END LOOP;
574 CLOSE c_stop_trip_id_cursor;
575
576 elsif (p_delivery_ids.count <> 0) then
577 --
578 -- Debug Statements
579 --
580 IF l_debug_on THEN
581 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.GET_NAME',WSH_DEBUG_SV.C_PROC_LEVEL);
582 END IF;
583 --
584 entity_name := WSH_NEW_DELIVERIES_PVT.Get_Name(p_delivery_ids(i));
585
586 else
587 entity_name := 'LINE ';
588 end if;
589
590 IF l_doc_set_params(i).p_report_set_id IS NULL THEN
591 RAISE no_reportset_to_process;
592 END IF;
593
594
595 FOR document IN c_document_set(l_doc_set_params(i).p_report_set_id) LOOP
599 l_organization_id := l_doc_set_params(i).p_organization_id ; -- Bug 3534965(3510460 Frontport)
596
597 -- Bug 3320252 : initialize l_printer_name everytime in this loop. -- jckwok
598 l_printer_name := l_doc_set_params(i).p_printer_name ; -- Choose Printer project
600
601 l_total_docs := l_total_docs + 1;
602
603 l_user_concurrent_program_name := document.user_concurrent_program_name; --Bug 1633386
604 l_concurrent_program_name := document.concurrent_program_name;
605 l_application_id := document.application_id;
606 l_execution_method_code := document.execution_method_code;
607 l_template_code := document.template_code;
608 l_appl_short_name := document.application_short_name;
609 l_output_file_type := document.output_file_type;
610 l_concurrent_program_id := document.concurrent_program_id;
611
612
613
614
615 if ( document.number_of_copies = 0 ) then
616 l_copies := to_number(NVL(FND_PROFILE.VALUE('CONC_COPIES'),'1')) ;
617 else
618 l_copies := document.number_of_copies ;
619 end if ;
620
621 --
622 IF l_debug_on THEN
623 WSH_DEBUG_SV.logmsg(l_module_name, 'Report Set Id is :'||l_doc_set_params(i).p_report_set_id );
624 WSH_DEBUG_SV.logmsg(l_module_name, 'CONC PROGRAM IS :'||l_concurrent_program_name );
625 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF COPIES for current doc :'||L_COPIES );
626 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF COPIES from fnd profile :'|| to_number(NVL(FND_PROFILE.VALUE('CONC_COPIES'),'0')) );
627 END IF;
628
629 -- Debug Statements
630 --
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.logmsg(l_module_name, 'NUMBER OF COPIES TO BE PRINTED FOR EACH REPORT '||L_COPIES );
633 END IF;
634 --
635
636 --
637 -- Debug Statements
638 --
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.logmsg(l_module_name, 'PROCESSING DOCUMENT ' || L_CONCURRENT_PROGRAM_NAME );
641 END IF;
642 --
643
644 -- Assigning current doc set params to language parameters
645 l_lang_doc_params_info := l_doc_set_params(i);
646
647 l_arg_cnt := 0;
648 l_valid_params := TRUE;
649 l_non_default_params := FALSE;
650
651 OPEN c_document_params(i);
652
653 LOOP
654
655 FETCH c_document_params INTO
656 l_arg_value,
657 l_arg_name,
658 l_arg_required_flag,
659 l_arg_default_value,
660 l_arg_default_type,
661 l_arg_value_set_id,
662 l_parameter_name;
663
664 EXIT WHEN (c_document_params%notfound) OR (NOT l_valid_params);
665
666 l_arg_cnt := l_arg_cnt + 1;
667
668 --Bug 6137146 added condition for Shipping Exception Report
669 IF l_concurrent_program_name = 'WSHRDXCP' AND L_ARG_NAME = 'Request Id'
670 AND l_doc_set_params(i).p_request_id IS NULL THEN
671 L_ARG_VALUE := -99 ;
672 END IF ;
673
674 --
675 -- Debug Statements
676 --
677 IF l_debug_on THEN
678 WSH_DEBUG_SV.logmsg(l_module_name, 'ARGUMENT NAME ' || L_ARG_NAME );
679 END IF;
680 --
681 --
682 -- Debug Statements
683 --
684 IF l_debug_on THEN
685 WSH_DEBUG_SV.logmsg(l_module_name, 'ARGUMENT VALUE ' || L_ARG_VALUE );
686 END IF;
687 --
688 --
689 -- Debug Statements
690 --
691 IF l_debug_on THEN
692 WSH_DEBUG_SV.logmsg(l_module_name, 'ARGUMENT REQUIRED ' || L_ARG_REQUIRED_FLAG );
693 END IF;
694 --
695
696 l_argument_name ( l_arg_cnt ) := l_arg_name ;
697
698 IF l_arg_value <> 'UNSUPPORTED' THEN
699 l_argument(l_arg_cnt) := l_arg_value;
700 ELSE
701 l_argument(l_arg_cnt) := NULL;
702 END IF;
703 --
704 -- Debug Statements
705 --
706 IF l_debug_on THEN
707 WSH_DEBUG_SV.logmsg(l_module_name, 'ARGUMENT NAME ' || L_ARG_NAME||' VALUE:'||TO_CHAR ( L_ARG_CNT ) ||':'||L_ARGUMENT ( L_ARG_CNT ) );
708 END IF;
709 --
710
711 IF l_argument(l_arg_cnt) IS NULL THEN
712 --
713 -- Debug Statements
714 --
715 IF l_debug_on THEN
716 WSH_DEBUG_SV.logmsg(l_module_name, 'ARG DEFAULT TYPE ' || L_ARG_DEFAULT_TYPE );
717 END IF;
718 --
719 IF l_arg_default_type = 'C' THEN -- Constant
720 l_argument(l_arg_cnt) := l_arg_default_value;
721 ELSIF l_arg_default_type = 'P' THEN -- Profile
722 l_argument(l_arg_cnt) := fnd_profile.value(l_arg_default_value);
726 -- flex field, this will cause an error in which case continue
723 ELSIF l_arg_default_type = 'S' THEN -- Sql
724 -- use dynamic sql to get the default value.
725 -- NOTE not all values will be defined if this references another
727 BEGIN
728 BEGIN
729 l_cursor := dbms_sql.open_cursor;
730 dbms_sql.parse(l_cursor, l_arg_default_value, dbms_sql.v7);
731 dbms_sql.define_column(l_cursor, 1, l_sql_value, 100 );
732 l_rows := dbms_sql.execute(l_cursor);
733 l_rows := dbms_sql.fetch_rows(l_cursor);
734 dbms_sql.column_value(l_cursor, 1, l_sql_value);
735 IF dbms_sql.is_open(l_cursor) THEN
736 dbms_sql.close_cursor(l_cursor);
737 END IF;
738
739 l_argument(l_arg_cnt) := l_sql_value;
740 END;
741
742 EXCEPTION
743 WHEN OTHERS THEN
744 --NULL;
745 -- Bug 3596524
746 IF dbms_sql.is_open(l_cursor) THEN
747 dbms_sql.close_cursor(l_cursor);
748 END IF;
749 END;
750 END IF;
751
752 -- we now have the default value. If this is validated against a table value set
753 -- which select an id_column, then we must convert the user-friendly default
754 -- value to its internal value using the value set.
755 IF l_argument(l_arg_cnt) IS NOT NULL THEN
756
757 OPEN c_value_set_cursor(l_arg_value_set_id);
758 FETCH c_value_set_cursor INTO l_select_clause, l_where_clause, l_additional_clause;
759 IF (c_value_set_cursor%found) THEN
760 IF Substr(Upper(l_where_clause), 1, 5) = 'WHERE' THEN
761 l_where_clause := ' and ' || Substr(l_where_clause, 6);
762 END IF;
763
764 -- always put where clause at end as it may include an ORDER_BY clause
765 l_value_set_lookup :=
766 l_select_clause || ' where 1=1 ' || l_additional_clause
767 || ' ' || l_where_clause;
768
769 BEGIN
770 l_cursor := dbms_sql.open_cursor;
771 dbms_sql.parse(l_cursor, l_value_set_lookup, dbms_sql.v7);
772 dbms_sql.bind_variable(l_cursor, ':value', l_argument(l_arg_cnt));
773 dbms_sql.define_column(l_cursor, 1, l_sql_value, 255 );
774 l_rows := dbms_sql.execute(l_cursor);
775 l_rows := dbms_sql.fetch_rows(l_cursor);
776 dbms_sql.column_value(l_cursor, 1, l_sql_value);
777
778 EXCEPTION
779 WHEN OTHERS THEN
780 l_sql_value := NULL;
781 END;
782
783 IF dbms_sql.is_open(l_cursor) THEN
784 dbms_sql.close_cursor(l_cursor);
785 END IF;
786
787 IF l_sql_value IS NOT NULL THEN
788 l_argument(l_arg_cnt) := l_sql_value;
789 END IF;
790
791 END IF;
792
793 CLOSE c_value_set_cursor;
794 END IF;
795 ELSE -- if argument value is not null,
796 --
797 -- Debug Statements
798 --
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.logmsg(l_module_name, 'L_PARAMETER_NAME:' || L_PARAMETER_NAME );
801 END IF;
802 --
803 IF l_parameter_name NOT IN
804 (
805 'p_organization_id',
806 'p_org_id',
807 'p_warehouse_id'
808 )
809 THEN
810 l_non_default_params := TRUE;
811 END IF;
812 END IF;
813
814 --
815 -- Debug Statements
816 --
817 IF l_debug_on THEN
818 WSH_DEBUG_SV.logmsg(l_module_name, ' CHECK ARG NAME ' || L_ARG_NAME||' VALUE:'||TO_CHAR ( L_ARG_CNT ) ||':'||L_ARGUMENT ( L_ARG_CNT ) );
819 END IF;
820 --
821 -- if still null and its required then raise appropriate error
822 IF (l_argument(l_arg_cnt) IS NULL) AND l_arg_required_flag = 'Y' THEN
823 IF l_arg_value = 'UNSUPPORTED' THEN
824 x_return_status := fnd_api.g_ret_sts_error;
825 fnd_message.set_name('WSH', 'WSH_UNSUPPORTED_ARG');
826 -- bug 2389744
827 fnd_message.set_token('DOCUMENT',l_user_concurrent_program_name);
828 fnd_message.set_token('ARGUMENT',l_arg_name);
829 -- bug 2389744
830 wsh_util_core.add_message(x_return_status);
831
835 --bug 1633386
832 ELSE
833 x_return_status := fnd_api.g_ret_sts_error;
834 fnd_message.set_name('WSH', 'WSH_NULL_ARG_IN_DOC');
836 fnd_message.set_token('ARGUMENT',l_arg_name);
837 fnd_message.set_token('DOCUMENT',l_user_concurrent_program_name);
838 --bug 1633386
839 wsh_util_core.add_message(x_return_status);
840
841 END IF;
842 -- set error_flags to stop processing this document
843 l_valid_params := FALSE;
844 l_error_in_a_doc := TRUE;
845 wsh_util_core.PrintMsg('The document ' || l_concurrent_program_name || ' cannot be generated' ||
846 ' because argument ' || l_arg_name || ' is not supported or has null value');
847 END IF;
848
849 IF l_parameter_name IN
850 (
851 'p_organization_id',
852 'p_org_id'
853 )
854 AND l_argument(l_arg_cnt) = -1
855 THEN
856 l_argument(l_arg_cnt) := NULL;
857 END IF;
858
859 END LOOP; -- c_document_params loop
860 CLOSE c_document_params;
861
862 if ( l_valid_params ) then
863 --
864 -- Debug Statements
865 --
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.logmsg(l_module_name, 'L_VALID_PARAMS IS TRUE' );
868 END IF;
869 --
870 else
871 --
872 -- Debug Statements
873 --
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name, 'L_VALID_PARAMS IS FALSE' );
876 END IF;
877 --
878 end if;
879
880 IF l_valid_params
881 THEN
882 --
883 -- If not a single parameter was specified explicitly
884 -- or in other words, if all parameters were defaulted
885 -- do not submit the document
886 --
887 if ( l_non_default_params ) then
888 --
889 -- Debug Statements
890 --
891 IF l_debug_on THEN
892 WSH_DEBUG_SV.logmsg(l_module_name, 'L_NON_DEFAULT_PARAMS IS TRUE' );
893 END IF;
894 --
895 else
896 --
897 -- Debug Statements
898 --
899 IF l_debug_on THEN
900 WSH_DEBUG_SV.logmsg(l_module_name, 'L_NON_DEFAULT_PARAMS IS FALSE' );
901 END IF;
902 --
903 end if;
904 IF NOT(l_non_default_params)
905 THEN
906 --
907 -- Debug Statements
908 --
909 IF l_debug_on THEN
910 WSH_DEBUG_SV.logmsg(l_module_name, 'THE DOCUMENT ' || L_CONCURRENT_PROGRAM_NAME || ' DOES NOT HAVE A PARAM SPECIFIED.' );
911 END IF;
912 --
913 l_valid_params := FALSE;
914 x_return_status := wsh_util_core.g_ret_sts_warning;
915 fnd_message.set_name('WSH', 'WSH_NO_CRITERIA_FOR_DOC');
916 fnd_message.set_token('DOCUMENT_NAME',l_user_concurrent_program_name);
917 wsh_util_core.add_message(x_return_status);
918 END IF;
919 END IF;
920
921 IF l_valid_params THEN --{
922 -- As per Concurrent Processing reqts, the first unused argument (if < 30)
923 -- should be set as chr(0) and remaining as null when submitting PL/SQL programs
924 -- as part of Document Sets
925 IF l_arg_cnt < 30 THEN
926 l_arg_cnt := l_arg_cnt + 1;
927 l_argument(l_arg_cnt) := chr(0);
928 END IF;
929 -- loop through the rest of the arguments (upto 30) setting any
930 -- remaining ones to null for unassigned.
931 WHILE l_arg_cnt < 30 LOOP
932 l_arg_cnt := l_arg_cnt + 1;
933 l_argument(l_arg_cnt) := '';
934 END LOOP;
935
936 -- set up the printer
937
938 --
939 -- Debug Statements
940 --
941 IF l_debug_on THEN --{
942 WSH_DEBUG_SV.logmsg(l_module_name, 'THE DOCUMENT ' || L_CONCURRENT_PROGRAM_NAME || ' HAVE A PARAM SPECIFIED.' );
943 END IF; --}
944 --
945
946 IF document.print_flag = 'Y' THEN --{
947
948 --
949 -- Debug Statements
950 --
951 IF l_debug_on THEN --{
952 WSH_DEBUG_SV.logmsg(l_module_name, 'APPLICATION ID: ' || TO_CHAR ( l_application_id ) || ' RESPONSIBILITY ID: ' || TO_CHAR ( FND_GLOBAL.RESP_ID ) || ' USER ID: ' || TO_CHAR ( FND_GLOBAL.USER_ID ) );
956 if ( l_printer_name IS NULL or l_printer_name = '-1' ) then --{
953 END IF; --}
954 --
955
957 --
958 -- Debug Statements
959 --
960 IF l_debug_on THEN --{
961 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REPORT_PRINTERS_PVT.GET_PRINTER',WSH_DEBUG_SV.C_PROC_LEVEL);
962 END IF; --}
963 --
964 WSH_REPORT_PRINTERS_PVT.Get_Printer(p_concurrent_program_id => document.concurrent_program_id,
965 p_organization_id => l_organization_id , -- Bug 3534965(3510460 Frontport)
966 p_equipment_type_id => null,
967 p_equipment_instance => null,
968 p_user_id => fnd_global.user_id,
969 p_zone => null,
970 p_department_id => null,
971 p_responsibility_id => fnd_global.resp_id,
972 p_application_id => l_application_id ,
973 p_site_id => 0,
974 x_printer => l_printer_name,
975 x_api_status => l_status,
976 x_error_message => l_error_message);
977
978
979 IF l_error_message IS NOT NULL THEN --{
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.logmsg(l_module_name, 'GET_PRINTER: ' || l_error_message);
982 END IF;
983 END IF; --}
984
985 IF l_printer_name IS NULL OR l_printer_name = 'No Printer' THEN --{
986 l_printer_name := document.default_printer_name;
987 --
988 -- Debug Statements
989 --
990 IF l_debug_on THEN --{
991 WSH_DEBUG_SV.logmsg(l_module_name, 'PRINTER NAME IS NULL AND THE DEFAULT PRINTER IS '||L_PRINTER_NAME );
992 END IF; --}
993 --
994 END IF; --}
995
996
997 IF document.save_output_flag = 'Y' THEN --{
998 l_save_output := TRUE;
999 ELSE
1000 l_save_output := FALSE;
1001 END IF; --}
1002
1003 END IF ; --} if l_printer_name is null
1004
1005 IF ( document.CONCURRENT_PROGRAM_NAME <> 'WSHRDPIK' or WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count = 0 ) then --{
1006 IF l_debug_on THEN --{
1007 WSH_DEBUG_SV.logmsg(l_module_name, DOCUMENT.CONCURRENT_PROGRAM_NAME || ' and no printers setup ' );
1008 END IF; --}
1009 --
1010 IF l_debug_on THEN --{
1011 WSH_DEBUG_SV.logmsg(l_module_name, l_copies || ' copies of ' || DOCUMENT.CONCURRENT_PROGRAM_NAME||' WILL BE PRINTED ON PRINTER '||L_PRINTER_NAME );
1012 END IF; --}
1013 --
1014 l_printer_setup :=
1015 fnd_request.set_print_options
1016 (l_printer_name, -- This could be null here.
1017 document.output_print_style,
1018 l_copies,
1019 l_save_output,
1020 'N');
1021 if ( NOT l_printer_setup and l_debug_on ) then --{
1022 WSH_DEBUG_SV.logmsg(l_module_name, ' Set_Print_Options Returned False !!!!');
1023 end if ; --}
1024 END IF ; --}
1025 END IF; --} If document.print_flag = 'Y'
1026
1027 -- go ahead and submit this document as a request
1028
1029
1030 IF ( document.CONCURRENT_PROGRAM_NAME <> 'WSHRDPIK' or WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count = 0 ) then --{
1031
1032 -- Getting language if document has MLS function associated with it
1033 IF document.mls_executable_id IS NOT NULL THEN
1034 l_nls_lang.delete;
1035 IF l_debug_on THEN --{
1036 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_EXTREPS_MLS_LANG.Get_NLS_Lang ' );
1037 END IF; --}
1038 WSH_EXTREPS_MLS_LANG.Get_NLS_Lang (
1039 p_prog_name => document.concurrent_program_name,
1040 p_doc_param_info => l_lang_doc_params_info,
1041 p_nls_comp => document.nls_compliant,
1042 x_nls_lang => l_nls_lang,
1043 x_return_status => x_return_status
1044 );
1045 IF x_return_status IN ( fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error ) THEN
1046 IF l_debug_on THEN --{
1047 WSH_DEBUG_SV.logmsg(l_module_name, 'Error returning from Get_NLS_Lang :'||x_return_status );
1048 END IF; --}
1049 wsh_util_core.add_message(x_return_status);
1050 END IF;
1051 IF l_debug_on THEN --{
1052 WSH_DEBUG_SV.logmsg(l_module_name, 'Number of NLS languages :'||l_nls_lang.COUNT );
1053 END IF; --}
1054 END IF;
1055
1059 WHILE NOT (l_submitted)
1056 l_submitted := FALSE;
1057 l_nls_count := 1;
1058
1060 LOOP
1061 IF l_nls_lang.COUNT <> 0 THEN
1062 IF l_nls_count <= l_nls_lang.COUNT THEN
1063 /* set the language and territory for this request
1064 all individual requests are protected against updates */
1065 IF l_debug_on THEN --{
1066 WSH_DEBUG_SV.logmsg(l_module_name, 'Setting option for language : '||l_nls_lang(l_nls_count).nls_language);
1067 END IF; --}
1068 if ( not fnd_request.set_options(
1069 implicit => 'NO',
1070 protected => 'YES',
1071 language => l_nls_lang(l_nls_count).nls_language,
1072 territory => l_nls_lang(l_nls_count).nls_territory)) then
1073 IF l_debug_on THEN --{
1074 WSH_DEBUG_SV.logmsg(l_module_name, 'fnd_request.set_options returned false');
1075 END IF; --}
1076 wsh_util_core.add_message(x_return_status);
1077 raise no_data_found ;
1078 end if;
1079 ELSE
1080 -- Exit loop since all nls requests are submitted
1081 EXIT;
1082 END IF;
1083 ELSE
1084 l_submitted := TRUE;
1085 END IF;
1086
1087 --- BugFix 3274604 - Start
1088 -- Following code is added to make sure that packing slip, BOL amd MBOL
1089 -- document number are generated before submitting request
1090
1091 IF document.concurrent_program_name = 'WSHRDMBL' THEN
1092 IF l_debug_on THEN
1093 WSH_DEBUG_SV.logmsg(l_module_name, 'p_trip_id for Master BOL = ' ||l_argument(1));
1094 END IF;
1095 WSH_MBOLS_PVT.Generate_MBOL(
1096 p_trip_id => l_argument(1),
1097 x_sequence_number => l_document_number,
1098 x_return_status => l_return_status );
1099
1100 WSH_UTIL_CORE.api_post_call(
1101 p_return_status => l_return_status,
1102 x_num_warnings => l_num_warning,
1103 x_num_errors => l_num_errors,
1104 p_raise_error_flag => FALSE );
1105
1106 --Bug 3685366 : Added conditon to check bol_error_flag.
1107 ELSIF document.concurrent_program_name = 'WSHRDBOL'
1108 AND ( nvl(l_doc_set_params(i).bol_error_flag,'N') = 'N') THEN
1109
1110 IF l_argument(6) is not null THEN
1111 IF l_debug_on THEN
1112 WSH_DEBUG_SV.logmsg(l_module_name, 'p_trip_id for BOL = ' ||l_argument(6));
1113 END IF;
1114 WSH_MBOLS_PVT.Generate_BOLs(
1115 p_trip_id => l_argument(6),
1116 x_return_status => l_return_status );
1117
1118 WSH_UTIL_CORE.api_post_call(
1119 p_return_status => l_return_status,
1120 x_num_warnings => l_num_warning,
1121 x_num_errors => l_num_errors,
1122 p_raise_error_flag => FALSE );
1123 ELSIF l_argument(5) is not null THEN
1124
1125 OPEN c_get_delivery_info(l_argument(5));
1126 LOOP
1127 FETCH c_get_delivery_info INTO l_delivery_id,
1128 l_delivery_leg_id,
1129 l_ship_method_code,
1130 l_pickup_location_id,
1131 l_trip_name;
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.logmsg(l_module_name, 'l_delivery_id for BOL = ' ||l_delivery_id);
1134 WSH_DEBUG_SV.logmsg(l_module_name, 'l_delivery_leg_id for BOL = ' ||l_delivery_leg_id);
1135 WSH_DEBUG_SV.logmsg(l_module_name, 'l_ship_method_code for BOL = ' ||l_ship_method_code);
1136 WSH_DEBUG_SV.logmsg(l_module_name, 'l_pickup_location_id for BOL = ' ||l_pickup_location_id);
1137 WSH_DEBUG_SV.logmsg(l_module_name, 'l_trip_name for BOL = ' ||l_trip_name);
1138 END IF;
1139 EXIT WHEN c_get_delivery_info%NOTFOUND;
1140
1141 SELECT count(*)
1142 INTO l_bol_count
1143 FROM wsh_document_instances
1144 WHERE entity_name = 'WSH_DELIVERY_LEGS'
1145 AND entity_id = l_delivery_leg_id
1146 AND status <> 'CANCELLED';
1147
1148 IF l_bol_count = 0 THEN
1149
1150 IF l_ship_method_code IS NULL THEN
1151 FND_MESSAGE.SET_NAME('WSH','WSH_BOL_NULL_SHIP_METHOD_ERROR');
1152 FND_MESSAGE.SET_TOKEN('TRIP_NAME', l_trip_name);
1153 x_return_status := wsh_util_core.g_ret_sts_error;
1154 wsh_util_core.add_message(x_return_status);
1155 IF l_debug_on THEN
1156 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status for BOL = '||x_return_status);
1157 END IF;
1158 IF c_get_delivery_info%ISOPEN THEN
1159 CLOSE c_get_delivery_info;
1160 END IF;
1161 RAISE wsh_create_document_error;
1162 END IF;
1163 -- LE Uptake
1164 OPEN c_get_ledger_id(l_delivery_id);
1165 FETCH c_get_ledger_id INTO l_ledger_id;
1166 IF c_get_ledger_id%NOTFOUND THEN
1167 FND_MESSAGE.SET_NAME('WSH','WSH_LEDGER_ID_NOT_FOUND');
1171 CLOSE c_get_delivery_info;
1168 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1169 wsh_util_core.add_message(x_return_status);
1170 IF c_get_delivery_info%ISOPEN THEN
1172 END IF;
1173 RAISE wsh_create_document_error;
1174 END IF;
1175 IF c_get_ledger_id%ISOPEN THEN
1176 CLOSE c_get_ledger_id;
1177 END IF;
1178
1179 WSH_DOCUMENT_PVT.Create_Document
1180 ( p_api_version => 1.0
1181 , p_init_msg_list => 'F'
1182 , p_commit => NULL
1183 , p_validation_level => NULL
1184 , x_return_status => l_return_status
1185 , x_msg_count => l_msg_count
1186 , x_msg_data => l_msg_data
1187 , p_entity_name => 'WSH_DELIVERY_LEGS'
1188 , p_entity_id => l_delivery_leg_id
1189 , p_application_id => 665
1190 , p_location_id => l_pickup_location_id
1191 , p_document_type => 'BOL'
1192 , p_document_sub_type => l_ship_method_code
1193 , p_ledger_id => l_ledger_id -- LE Uptake
1194 , p_consolidate_option => 'BOTH'
1195 , p_manual_sequence_number => 200
1196 , x_document_number => l_document_number);
1197
1198 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1199 IF c_get_delivery_info%ISOPEN THEN
1200 CLOSE c_get_delivery_info;
1201 END IF;
1202 RAISE wsh_create_document_error;
1203 END IF;
1204 --
1205 END IF;
1206 END LOOP;
1207 IF c_get_delivery_info%ISOPEN THEN
1208 CLOSE c_get_delivery_info;
1209 END IF;
1210 END IF;
1211
1212 ELSIF document.concurrent_program_name = 'WSHRDPAK' THEN
1213
1214 SELECT count(*)
1215 into cnt
1216 from wsh_document_instances
1217 WHERE entity_name = 'WSH_NEW_DELIVERIES'
1218 AND entity_id = l_argument(2)
1219 AND document_type = 'PACK_TYPE';
1220
1221 IF cnt = 0 then
1222
1223 open c_get_init_pickup_loc_id(l_argument(2));
1224 fetch c_get_init_pickup_loc_id into p_location_id1;
1225 IF c_get_init_pickup_loc_id%ISOPEN THEN
1226 close c_get_init_pickup_loc_id;
1227 END IF;
1228 -- LE Uptake
1229 open m_get_ledger_id(l_argument(2));
1230 fetch m_get_ledger_id INTO l_ledger_id;
1231 IF l_debug_on THEN
1232 WSH_DEBUG_SV.logmsg(l_module_name,'l_ledger_id = '||l_ledger_id);
1233 END IF;
1234
1235 IF m_get_ledger_id%NOTFOUND THEN
1236 FND_MESSAGE.SET_NAME('WSH','WSH_LEDGER_ID_NOT_FOUND');
1237 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1238 wsh_util_core.add_message(x_return_status);
1239 RAISE wsh_create_document_error;
1240 END IF;
1241
1242 IF m_get_ledger_id%ISOPEN THEN
1243 close m_get_ledger_id;
1244 END IF;
1245
1246 wsh_document_pvt.create_document (
1247 p_api_version => 1.0
1248 , p_init_msg_list => FND_API.G_FALSE -- Bug 5614459
1249 , p_commit => NULL
1250 , p_validation_level => NULL
1251 , x_return_status => l_return_status
1252 , x_msg_count => l_msg_count
1253 , x_msg_data => l_msg_data
1254 , p_entity_name => 'WSH_NEW_DELIVERIES'
1255 , p_entity_id => l_argument(2)
1256 , p_application_id => 665
1257 , p_location_id => p_location_id1
1258 , p_document_type => 'PACK_TYPE'
1259 , p_document_sub_type => 'SALES_ORDER'
1260 , p_ledger_id => l_ledger_id -- LE Uptake
1261 , p_consolidate_option => 'BOTH'
1262 , p_manual_sequence_number => 200
1263 , x_document_number => l_document_number);
1264
1265 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1266 RAISE wsh_create_document_error;
1267 END IF;
1268
1269 END IF;
1270 END IF;
1271
1272 --- BugFix 3274604 - End
1273
1274 -- Begin of XDO Integration Changes
1275
1276
1277 l_print_pdf := 'N';
1278 IF(l_output_file_type = 'XML' ) THEN
1279 --{
1280 IF l_debug_on THEN
1281 WSH_DEBUG_SV.logmsg(l_module_name, 'template' || l_template_code);
1282 END IF;
1283 --
1284 IF (l_template_code is not NULL) then
1285 --{
1286 IF l_nls_lang.COUNT <> 0 THEN
1287 --bugfix 6717642 added function lower
1288 select lower(iso_language),iso_territory into l_language, l_territory
1289 from fnd_languages
1290 where language_code = l_nls_lang(l_nls_count).lang_code;
1291 ELSE
1292 select lower(iso_language),iso_territory into l_language, l_territory
1293 from fnd_languages
1294 where language_code = userenv('LANG');
1295 END IF;
1296 --
1297 IF l_debug_on THEN
1298 WSH_DEBUG_SV.logmsg(l_module_name, ' language ' || l_language );
1299 WSH_DEBUG_SV.logmsg(l_module_name, 'territory ' || l_territory);
1300 END IF;
1304 l_language,
1301 --
1302 l_ret_status :=fnd_request.add_layout(l_appl_short_name,
1303 l_template_code,
1305 l_territory,
1306 'PDF');
1307 IF l_ret_status THEN
1308 l_print_pdf := 'Y';
1309 ELSE
1310 IF l_debug_on THEN
1311 WSH_DEBUG_SV.logmsg(l_module_name, 'Error returning from fnd_request.add_layout :'||x_return_status);
1312 END IF;
1313 wsh_util_core.add_message(x_return_status);
1314 END IF;
1315 --}
1316 ELSE
1317 --{
1318 IF l_debug_on THEN
1319 WSH_DEBUG_SV.logmsg(l_module_name, 'No template was specified for this report. Hence could not generate the pdf output' );
1320 END IF;
1321 --
1322 fnd_message.set_name('WSH', 'WSH_NO_DEFAULT_TEMPLATE');
1323 fnd_message.set_token('CONC_PROG_NAME', document.user_concurrent_program_name);
1324 x_return_status := wsh_util_core.g_ret_sts_error;
1325 wsh_util_core.add_message(x_return_status);
1326 END IF; --} If template_code is not null
1327 END IF; --} If l_output_file_type = 'XML'
1328
1329 -- End of XDO Integration Changes
1330 IF ((l_output_file_type <> 'XML') OR
1331 (l_output_file_type = 'XML' and l_print_pdf = 'Y')) THEN
1332 l_request_id := fnd_request.submit_request
1333 (document.application_short_name,
1334 document.concurrent_program_name,
1335 '',
1336 '',
1337 FALSE,
1338 l_argument(1), l_argument(2), l_argument(3), l_argument(4), l_argument(5),
1339 l_argument(6), l_argument(7), l_argument(8), l_argument(9), l_argument(10),
1340 l_argument(11),l_argument(12),l_argument(13),l_argument(14),l_argument(15),
1341 l_argument(16),l_argument(17),l_argument(18),l_argument(19),l_argument(20),
1342 l_argument(21),l_argument(22),l_argument(23),l_argument(24),l_argument(25),
1343 l_argument(26),l_argument(27),l_argument(28),l_argument(29),l_argument(30),
1344 '','','','','','','','','','',
1345 '','','','','','','','','','',
1346 '','','','','','','','','','',
1347 '','','','','','','','','','',
1348 '','','','','','','','','','',
1349 '','','','','','','','','','',
1350 '','','','','','','','','','');
1351
1352
1353 --
1354 -- Debug Statements
1355 --
1356 IF l_debug_on THEN --{
1357 WSH_DEBUG_SV.logmsg(l_module_name, 'SUBMITTED '|| DOCUMENT.CONCURRENT_PROGRAM_NAME||' WITH REQUEST_ID:'||TO_CHAR ( L_REQUEST_ID ) );
1358 END IF; --}
1359 --
1360 -- increase the counter if successful
1361 IF l_request_id > 0 THEN --{
1362
1363 l_submitted_docs := l_submitted_docs + 1;
1364
1365 --
1366 -- Debug Statements
1367 --
1368 IF l_debug_on THEN --{
1369 WSH_DEBUG_SV.logmsg(l_module_name, 'REQUEST ID ' || TO_CHAR ( L_REQUEST_ID ) );
1370 END IF; --}
1371 --
1372
1373 if (entity_count > 0) then --{
1374
1375 IF l_submitted_docs = 1 THEN --{
1376 l_req_id_str := to_char(l_request_id);
1377 ELSE
1378 --Bug#5188945: Restricting the string of request ids to 1975 characters length only
1379 IF (l_buffer_fill = 'N' ) THEN
1380 --{
1381 l_req_id_str := l_req_id_str || ', ' || to_char(l_request_id);
1382 IF LENGTH(l_req_id_str) > 1975 THEN
1383 --{
1384 l_req_id_str := SUBSTR(l_req_id_str,1,INSTR(l_req_id_str,',',-1,1)) || '...';
1385 l_buffer_fill := 'Y';
1386 --}
1387 END IF;
1388 --}
1389 END IF;
1390 --}
1391 END IF; --}
1392
1393 end if; --}
1394
1395 ELSE
1396
1397 if (entity_count > 0) then --{
1398
1399 declare
1400 msg_buffer varchar2(2000);
1401 begin
1402
1403 msg_buffer := fnd_message.get;
1404
1405 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINT_DOC_SET_FAILED');
1406 FND_MESSAGE.SET_TOKEN('RELEASE_TYPE', entity_type);
1407 FND_MESSAGE.SET_TOKEN('NAME', entity_name);
1408 FND_MESSAGE.SET_TOKEN('UNDERLYING_ERROR', msg_buffer);
1409
1410 wsh_util_core.add_message(x_return_status);
1411
1412 end;
1413 end if;--}
1414
1418 l_nls_count := l_nls_count + 1;
1415 END IF; --} if request_id > 0
1416
1417 END IF ; --} if outfile_file_type <> 'XML' or l_print_pdf = 'Y'
1419
1420 END LOOP; -- end of l_submitted loop
1421
1422 ELSE -- It is WSHRDPIK and G_PRINTERTAB has been populated
1423 IF l_debug_on THEN --{
1424 WSH_DEBUG_SV.logmsg(l_module_name, 'WSHRDPIK and number of printers is ' || WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count );
1425 END IF; --}
1426 --
1427 -- Find position of 'Printer Name' in the arguments
1428
1429 l_printer_pos := -1 ;
1430 for j in 1..l_argument_name.count loop
1431 IF l_debug_on THEN --{
1432 WSH_DEBUG_SV.logmsg(l_module_name, 'Argument name ' || l_argument_name (j) );
1433 END IF; --}
1434 --
1435 if l_argument_name(j) = 'Printer Name' then
1436 l_printer_pos := j;
1437 IF l_debug_on THEN --{
1438 WSH_DEBUG_SV.logmsg(l_module_name, 'Found printer name at pos ' || j );
1439 END IF; --}
1440 --
1441 exit ;
1442 end if ;
1443 end loop ;
1444
1445
1446 -- FOR i in 1..WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.count LOOP
1447
1448 -- l_argument(l_printer_pos) := WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i);
1449 l_argument(l_printer_pos) := p_document_param_info(1).p_printer_name;
1450
1451 IF l_debug_on THEN --{
1452 WSH_DEBUG_SV.logmsg(l_module_name, 'Setting '|| document.concurrent_program_name || ' with ' || l_copies || ' copies on printer ' || p_document_param_info(1).p_printer_name );
1453 --
1454 END IF; --}
1455
1456 l_printer_setup :=
1457 fnd_request.set_print_options
1458 (printer => p_document_param_info(1).p_printer_name,
1459 style => document.output_print_style,
1460 copies => l_copies,
1461 save_output => l_save_output,
1462 print_together => 'N');
1463
1464 if ( NOT l_printer_setup and l_debug_on ) then --{
1465 WSH_DEBUG_SV.logmsg(l_module_name, 'For Printer ' || p_document_param_info(1).p_printer_name || ', Set_Print_Options Returned False for WSHRDPIK!!!!');
1466 end if ; --}
1467
1468 -- Getting language if document has MLS function associated with it
1469 IF document.mls_executable_id IS NOT NULL THEN
1470 l_nls_lang.delete;
1471 IF l_debug_on THEN --{
1472 WSH_DEBUG_SV.logmsg(l_module_name, 'Calling WSH_EXTREPS_MLS_LANG.Get_NLS_Lang ' );
1473 END IF; --}
1474 WSH_EXTREPS_MLS_LANG.Get_NLS_Lang (
1475 p_prog_name => document.concurrent_program_name,
1476 p_doc_param_info => l_lang_doc_params_info,
1477 p_nls_comp => document.nls_compliant,
1478 x_nls_lang => l_nls_lang,
1479 x_return_status => x_return_status
1480 );
1481 IF x_return_status IN ( fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error ) THEN
1482 IF l_debug_on THEN --{
1483 WSH_DEBUG_SV.logmsg(l_module_name, 'Error returning from Get_NLS_Lang :'||x_return_status
1484 );
1485 END IF; --}
1486 wsh_util_core.add_message(x_return_status);
1487 END IF;
1488 IF l_debug_on THEN --{
1489 WSH_DEBUG_SV.logmsg(l_module_name, 'Number of NLS languages :'||l_nls_lang.COUNT );
1490 END IF; --}
1491 END IF;
1492
1493 l_submitted := FALSE;
1494 l_nls_count := 1;
1495
1496
1497
1498 l_submitted := FALSE;
1499 l_nls_count := 1;
1500
1501 WHILE NOT (l_submitted)
1502 LOOP
1503 IF l_nls_lang.COUNT <> 0 THEN
1504 IF l_nls_count <= l_nls_lang.COUNT THEN
1505 /* set the language and territory for this request
1506 all individual requests are protected against updates */
1507 IF l_debug_on THEN --{
1508 WSH_DEBUG_SV.logmsg(l_module_name, 'Setting option for language : '||l_nls_lang(l_nls_count).nls_language);
1509 END IF; --}
1510 if ( not fnd_request.set_options(
1511 implicit => 'NO',
1512 protected => 'YES',
1513 language => l_nls_lang(l_nls_count).nls_language,
1514 territory => l_nls_lang(l_nls_count).nls_territory)) then
1515 IF l_debug_on THEN --{
1519 raise no_data_found ;
1516 WSH_DEBUG_SV.logmsg(l_module_name, 'fnd_request.set_options returned false');
1517 END IF; --}
1518 wsh_util_core.add_message(x_return_status);
1520 end if;
1521 ELSE
1522 -- Exit loop since all nls requests are submitted
1523 EXIT;
1524 END IF;
1525 ELSE
1526 l_submitted := TRUE;
1527 END IF;
1528
1529 -- Begin of XDO Integration Changes
1530
1531 l_print_pdf := 'N';
1532 IF (l_output_file_type = 'XML' ) then
1533 --{
1534 IF l_debug_on THEN
1535 WSH_DEBUG_SV.logmsg(l_module_name, 'template' || l_template_code);
1536 END IF;
1537 --
1538 IF (l_template_code is not NULL) then
1539 --{
1540 IF l_nls_lang.COUNT <> 0 THEN
1541 --bugfix 6717642 added function lower
1542 select lower(iso_language),iso_territory into l_language, l_territory
1543 from fnd_languages
1544 where language_code = l_nls_lang(l_nls_count).lang_code;
1545 ELSE
1546 select lower(iso_language),iso_territory into l_language, l_territory
1547 from fnd_languages
1548 where language_code = userenv('LANG');
1549 END IF;
1550 --
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.logmsg(l_module_name, ' language' || l_language );
1553 WSH_DEBUG_SV.logmsg(l_module_name, 'territory' || l_territory);
1554 END IF;
1555 --
1556 l_ret_status :=fnd_request.add_layout
1557 (l_appl_short_name,
1558 l_template_code,
1559 l_language,
1560 l_territory,
1561 'PDF');
1562 IF l_ret_status THEN
1563 l_print_pdf := 'Y';
1564 ELSE
1565 IF l_debug_on THEN
1566 WSH_DEBUG_SV.logmsg(l_module_name, 'Error returning from fnd_request.add_layout :'||x_return_status);
1567 END IF;
1568 wsh_util_core.add_message(x_return_status);
1569 END IF;
1570 --}
1571 ELSE
1572 IF l_debug_on THEN
1573 WSH_DEBUG_SV.logmsg(l_module_name, 'No template was specified for this report. Hence could not generate the pdf output' );
1574 END IF;
1575 fnd_message.set_name('WSH', 'WSH_NO_DEFAULT_TEMPLATE');
1576 fnd_message.set_token('CONC_PROG_NAME', document.concurrent_program_name);
1577 x_return_status := wsh_util_core.g_ret_sts_error;
1578 wsh_util_core.add_message(x_return_status);
1579 END IF; --} l_template_code is not null
1580 END IF; --} l_output_file_type = 'XML'
1581
1582 -- End of XDO Integration Changes
1583
1584 IF ((l_output_file_type <> 'XML') OR
1585 (l_output_file_type = 'XML' and l_print_pdf = 'Y')) THEN
1586 l_request_id := fnd_request.submit_request
1587 (document.application_short_name,
1588 document.concurrent_program_name,
1589 '',
1590 '',
1591 FALSE,
1592 l_argument(1), l_argument(2), l_argument(3), l_argument(4), l_argument(5),
1593 l_argument(6), l_argument(7), l_argument(8), l_argument(9), l_argument(10),
1594 l_argument(11),l_argument(12),l_argument(13),l_argument(14),l_argument(15),
1595 l_argument(16),l_argument(17),l_argument(18),l_argument(19),l_argument(20),
1596 l_argument(21),l_argument(22),l_argument(23),l_argument(24),l_argument(25),
1597 l_argument(26),l_argument(27),l_argument(28),l_argument(29),l_argument(30),
1598 '','','','','','','','','','',
1599 '','','','','','','','','','',
1600 '','','','','','','','','','',
1601 '','','','','','','','','','',
1602 '','','','','','','','','','',
1603 '','','','','','','','','','',
1604 '','','','','','','','','','');
1605
1606 --
1607 -- Debug Statements
1608 --
1609 IF l_debug_on THEN --{
1610 WSH_DEBUG_SV.logmsg(l_module_name, 'SUBMITTED '|| DOCUMENT.CONCURRENT_PROGRAM_NAME||' WITH
1611 REQUEST_ID:'||TO_CHAR ( L_REQUEST_ID ) );
1612 END IF; --}
1613 --
1614 -- increase the counter if successful
1615 IF l_request_id > 0 THEN --{
1616
1617 l_submitted_docs := l_submitted_docs + 1;
1618
1619 --
1620 -- Debug Statements
1621 --
1622 IF l_debug_on THEN --{
1623 WSH_DEBUG_SV.logmsg(l_module_name, 'REQUEST ID ' || TO_CHAR ( L_REQUEST_ID ) );
1624 END IF; --}
1625 --
1626
1627 if (entity_count > 0) then --{
1628
1632 --Bug#5188945: Restricting the string of request ids to 1975 characters length only
1629 IF l_submitted_docs = 1 THEN --{
1630 l_req_id_str := to_char(l_request_id);
1631 ELSE
1633 IF (l_buffer_fill = 'N' ) THEN
1634 --{
1635 l_req_id_str := l_req_id_str || ', ' || to_char(l_request_id);
1636 IF LENGTH(l_req_id_str) >1975 THEN
1637 --{
1638 l_req_id_str := SUBSTR(l_req_id_str,1,INSTR(l_req_id_str,',',-1,1)) || '...';
1639 l_buffer_fill := 'Y';
1640 --}
1641 END IF;
1642 --}
1643 END IF;
1644 --}
1645
1646 END IF; --}
1647
1648 end if; --}
1649
1650 ELSE
1651
1652 if (entity_count > 0) then --{
1653
1654 declare
1655 msg_buffer varchar2(2000);
1656 begin
1657
1658 msg_buffer := fnd_message.get;
1659
1660 FND_MESSAGE.SET_NAME('WSH', 'WSH_PRINT_DOC_SET_FAILED');
1661 FND_MESSAGE.SET_TOKEN('RELEASE_TYPE', entity_type);
1662 FND_MESSAGE.SET_TOKEN('NAME', entity_name);
1663 FND_MESSAGE.SET_TOKEN('UNDERLYING_ERROR', msg_buffer);
1664
1665 wsh_util_core.add_message(x_return_status);
1666
1667 end;
1668 end if;--}
1669
1670 END IF; --} if request_id > 0
1671 END IF; --} if output_file_type <> 'XML' or l_print_pdf = 'Y'
1672
1673 l_nls_count := l_nls_count + 1;
1674
1675 END LOOP; -- end of l_submitted loop
1676
1677 -- END LOOP ; -- Loop on G_PRINTERTAB
1678
1679 END IF; --} if condition on WSHRDPIK and WSH_INV_INTEGRATION_GRP.G_PRINTERTAB
1680
1681 END IF; --} If valid_params
1682
1683
1684 END LOOP; -- c_document_set loop
1685
1686
1687 IF ( l_total_docs = 0 ) THEN --{
1688 -- successfully looped through all documents but didnt submit any
1689 -- probably because there werent any in the set (but may have had problems
1690 -- in fnd_request function
1691
1692 x_return_status := fnd_api.g_ret_sts_error;
1693 fnd_message.set_name('WSH','WSH_NO_DOCS');
1694 wsh_util_core.add_message(x_return_status);
1695
1696 ELSE
1697 -- everthing worked: any documents not submitted resulted
1698 -- from problem in fnd_request
1699 x_return_status := fnd_api.g_ret_sts_success;
1700 --
1701 -- Debug Statements
1702 --
1703 IF l_debug_on THEN
1704 WSH_DEBUG_SV.logmsg(l_module_name, 'SUBMITTED ' || TO_CHAR ( L_SUBMITTED_DOCS ) || ' OUT OF ' || TO_CHAR ( L_TOTAL_DOCS ) );
1705 END IF;
1706 --
1707
1708 fnd_message.set_name('WSH', 'WSH_DOCS_SUBMITTED');
1709 fnd_message.set_token('SUBMITTED_DOCS', l_submitted_docs);
1710 fnd_message.set_token('TOTAL_DOCS', l_total_docs);
1711 fnd_message.set_token('REQ_IDS', l_req_id_str);
1712 wsh_util_core.add_message(x_return_status);
1713 END IF; --}
1714
1715 END LOOP; -- for loop
1716
1717 EXCEPTION
1718 WHEN no_reportset_to_process THEN
1719 --
1720 -- Debug Statements
1721 --
1722 IF l_debug_on THEN
1723 WSH_DEBUG_SV.logmsg(l_module_name, 'NO REPORTS TO PROCESS' );
1724 END IF;
1725 --
1726 x_return_status := fnd_api.g_ret_sts_error;
1727 fnd_message.set_name('WSH','WSH_NO_REPORT_TO_PROCESS');
1728 wsh_util_core.add_message(x_return_status);
1729
1730 WHEN No_Org_For_Entity THEN
1731 --
1732 -- Debug Statements
1733 --
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.logmsg(l_module_name, 'NO ORGANIZATION FOUND FOR ENTITY' );
1736 END IF;
1737 --
1738 x_return_status := wsh_util_core.g_ret_sts_error;
1739 fnd_message.set_name('WSH','WSH_NO_ENTITY_ORG');
1740 wsh_util_core.add_message(x_return_status);
1741
1742 WHEN wsh_create_document_error THEN
1743 x_return_status := FND_API.G_RET_STS_ERROR;
1744 -- FND_MESSAGE.SET_NAME('WSH', 'WSH_BOL_CREATE_DOCUMENT_ERROR');
1745 -- WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1746
1747 --
1748 -- Debug Statements
1749 --
1750 IF l_debug_on THEN
1751 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_CREATE_DOCUMENT_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1752 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_CREATE_DOCUMENT_ERROR');
1753 END IF;
1754 WHEN OTHERS THEN
1755 wsh_util_core.default_handler('WSH_UTIL_CORE.PRINT_DOCUMENT_SETS');
1756 x_return_status := fnd_api.g_ret_sts_unexp_error;
1757 END;
1758 --
1759 -- Debug Statements
1760 --
1761 IF l_debug_on THEN
1762 WSH_DEBUG_SV.pop(l_module_name);
1763 END IF;
1764 --
1765 END print_document_sets;
1766
1767 END WSH_DOCUMENT_SETS;
1768