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