[Home] [Help]
PACKAGE BODY: APPS.INV_LABEL_PVT16
Source
1 PACKAGE BODY inv_label_pvt16 AS
2 /* $Header: INVLA16B.pls 120.2 2007/12/18 19:11:43 hjogleka noship $ */
3 label_b CONSTANT VARCHAR2(50) := '<label';
4 label_e CONSTANT VARCHAR2(50) := '</label>' || fnd_global.local_chr(10);
5 variable_b CONSTANT VARCHAR2(50) := '<variable name= "';
6 variable_e CONSTANT VARCHAR2(50) := '</variable>' || fnd_global.local_chr(10);
7 tag_e CONSTANT VARCHAR2(50) := '>' || fnd_global.local_chr(10);
8 l_debug NUMBER;
9 -- Bug 2795525 : This mask is used to mask all date fields.
10 g_date_format_mask VARCHAR2(100) := inv_label.g_date_format_mask;
11 g_header_printed BOOLEAN := FALSE;
12 g_user_name fnd_user.user_name%TYPE := fnd_global.user_name;
13
14 PROCEDURE TRACE(p_message VARCHAR2) IS
15 BEGIN
16 IF (g_header_printed = FALSE) THEN
17 inv_label.TRACE('$Header: INVLA16B.pls 120.2 2007/12/18 19:11:43 hjogleka noship $', g_pkg_name || ' - ' || 'LABEL_WIP_MOVE_CONT');
18 g_header_printed := TRUE;
19 END IF;
20
21 inv_label.TRACE(g_user_name || ': ' || p_message, 'LABEL_WIP_MOVE_CONT');
22 END TRACE;
23
24 PROCEDURE get_variable_data(
25 x_variable_content OUT NOCOPY inv_label.label_tbl_type
26 , x_msg_count OUT NOCOPY NUMBER
27 , x_msg_data OUT NOCOPY VARCHAR2
28 , x_return_status OUT NOCOPY VARCHAR2
29 , p_label_type_info IN inv_label.label_type_rec
30 , p_transaction_id IN NUMBER
31 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
32 , p_transaction_identifier IN NUMBER
33 ) IS
34 l_api_name VARCHAR2(20) := 'get_variable_data';
35
36 ---------------------------------------------------------------------------------------------
37 -- Project: 'Custom Labels' (A 11i10+ Project) |
38 -- Author: Dinesh ([email protected]) |
39 -- Change Description: |
40 -- Following variables were added (as a part of 11i10+ 'Custom Labels' Project) |
41 -- to retrieve and hold the SQL Statement and it's result. |
42 ---------------------------------------------------------------------------------------------
43 l_sql_stmt VARCHAR2(4000);
44 l_sql_stmt_result VARCHAR2(4000) := NULL;
45 TYPE sql_stmt IS REF CURSOR;
46 c_sql_stmt sql_stmt;
47 l_custom_sql_ret_status VARCHAR2(1);
48 l_custom_sql_ret_msg VARCHAR2(2000);
49
50 -- Fix for bug: 4179593 Start
51 l_CustSqlWarnFlagSet BOOLEAN;
52 l_CustSqlErrFlagSet BOOLEAN;
53 l_CustSqlWarnMsg VARCHAR2(2000);
54 l_CustSqlErrMsg VARCHAR2(2000);
55 -- Fix for bug: 4179593 End
56
57 ------------------------End of this change for Custom Labels project code--------------------
58
59
60 CURSOR wip_move_lines_cur IS
61 SELECT WMT.ACCT_PERIOD_ID move_acct_period
62 , WMT.ATTRIBUTE_CATEGORY move_attribute_catagory
63 , WMT.ATTRIBUTE1 move_attribute1
64 , WMT.ATTRIBUTE2 move_attribute2
65 , WMT.ATTRIBUTE3 move_attribute3
66 , WMT.ATTRIBUTE4 move_attribute4
67 , WMT.ATTRIBUTE5 move_attribute5
68 , WMT.ATTRIBUTE6 move_attribute6
69 , WMT.ATTRIBUTE7 move_attribute7
70 , WMT.ATTRIBUTE8 move_attribute8
71 , WMT.ATTRIBUTE9 move_attribute9
72 , WMT.ATTRIBUTE10 move_attribute10
73 , WMT.ATTRIBUTE11 move_attribute11
74 , WMT.ATTRIBUTE12 move_attribute12
75 , WMT.ATTRIBUTE13 move_attribute13
76 , WMT.ATTRIBUTE14 move_attribute14
77 , WMT.ATTRIBUTE15 move_attribute15
78 , WMT.CREATED_BY move_created_by
79 , WMT.CREATION_DATE move_creation_date
80 -- , WMT.EMPLOYEE_ID move_employee_id
81 , MEV.FULL_NAME move_employee_id
82 , WMT.FM_OPERATION_SEQ_NUM move_fm_operation_seq_num
83 , WMT.FM_OPERATION_CODE move_fm_operation_code
84 , BD1.DEPARTMENT_CODE move_fm_department
85 , ML1.MEANING move_fm_intraoperation_step
86 , WMT.JOB_QUANTITY_SNAPSHOT move_job_quantity_snapshot
87 , WMT.LAST_UPDATED_BY move_last_updated_by
88 , WMT.LAST_UPDATE_DATE move_last_update_date
89 , WMT.LAST_UPDATE_LOGIN move_last_update_login
90 , WL.LINE_CODE move_line
91 , WMT.ORGANIZATION_ID move_organization_id
92 , MP.ORGANIZATION_CODE move_organization_code
93 , HAOU.NAME move_organization_name
94 , WMT.OVERCOMPLETION_PRIMARY_QTY move_overcomp_primary_quantity
95 , WMT.OVERCOMPLETION_TRANSACTION_QTY move_overcomp_txn_quantity
96 , WE.primary_item_id move_primary_item_id
97 , msik3.CONCATENATED_SEGMENTS move_primary_item
98 , WMT.PRIMARY_QUANTITY move_primary_quantity
99 , WMT.PRIMARY_UOM move_primary_uom
100 , WMT.QA_COLLECTION_ID move_qa_collection_id
101 , MTR.REASON_NAME move_reason
102 , WMT.REFERENCE move_reference
103 -- , WMT.SCRAP_ACCOUNT_ID move_scrap_account
104 , glcc.concatenated_segments move_scrap_account
105 , WMT.TO_OPERATION_SEQ_NUM move_to_operation_seq_num
106 , WMT.TO_OPERATION_CODE move_to_operation_code
107 , BD2.DEPARTMENT_CODE move_to_department
108 , ML2.MEANING move_to_intraoperation_step
109 , TO_CHAR(WMT.TRANSACTION_DATE, g_date_format_mask) move_transaction_date
110 , WMT.TRANSACTION_ID move_transaction_id
111 , WMT.TRANSACTION_QUANTITY move_transaction_quantity
112 , WMT.TRANSACTION_UOM move_transaction_uom
113 , WMT.WIP_ENTITY_ID wip_entity_id
114 , WE.WIP_ENTITY_NAME wip_job_name
115 , mfglkp2.meaning wip_job_type
116 , wipdj.start_quantity wip_job_start_quantity
117 , TO_CHAR(wipdj.scheduled_start_date, g_date_format_mask) wip_job_start_date
118 , TO_CHAR(wipdj.scheduled_completion_date, g_date_format_mask) wip_job_completion_date
119 , mmt.subinventory_code job_completion_subinventory
120 , inv_project.get_locsegs(mmt.locator_id, mmt.organization_id) job_completion_locator
121 FROM MFG_LOOKUPS ML1
122 , MFG_LOOKUPS ML2
123 , MTL_TRANSACTION_REASONS MTR
124 , BOM_DEPARTMENTS BD1
125 , BOM_DEPARTMENTS BD2
126 , WIP_ENTITIES WE
127 , WIP_LINES WL
128 , WIP_MOVE_TRANSACTIONS WMT
129 , WIP_DISCRETE_JOBS wipdj
130 , MTL_PARAMETERS MP
131 , HR_ALL_ORGANIZATION_UNITS HAOU
132 , mtl_system_items_vl msik3
133 , MFG_LOOKUPS mfglkp2
134 , GL_CODE_COMBINATIONS_KFV glcc
135 , MTL_EMPLOYEES_VIEW MEV
136 , MTL_MATERIAL_TRANSACTIONS mmt
137 WHERE WMT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
138 AND WMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
139 AND WMT.LINE_ID = WL.LINE_ID (+)
140 AND WMT.ORGANIZATION_ID = WL.ORGANIZATION_ID (+)
141 AND WMT.FM_DEPARTMENT_ID = BD1.DEPARTMENT_ID
142 AND ML1.LOOKUP_CODE = WMT.FM_INTRAOPERATION_STEP_TYPE
143 AND ML1.LOOKUP_TYPE = 'WIP_INTRAOPERATION_STEP'
144 AND WMT.TO_DEPARTMENT_ID = BD2.DEPARTMENT_ID
145 AND ML2.LOOKUP_CODE = WMT.TO_INTRAOPERATION_STEP_TYPE
146 AND ML2.LOOKUP_TYPE = 'WIP_INTRAOPERATION_STEP'
147 AND WMT.REASON_ID = MTR.REASON_ID (+)
148 AND WMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
149 AND WMT.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
150 AND msik3.inventory_item_id(+) = WE.primary_item_id
151 AND msik3.organization_id(+) = WE.organization_id
152 AND wipdj.wip_entity_id(+) = WE.wip_entity_id
153 AND wipdj.organization_id(+) = WE.organization_id
154 AND mfglkp2.lookup_code(+) = wipdj.job_type
155 AND mfglkp2.lookup_type(+) = 'WIP_DISCRETE_JOB'
156 AND glcc.code_combination_id(+) = WMT.SCRAP_ACCOUNT_ID
157 AND MEV.ORGANIZATION_ID(+) = WMT.ORGANIZATION_ID
158 AND MEV.EMPLOYEE_ID(+) = WMT.EMPLOYEE_ID
159 AND mmt.MOVE_TRANSACTION_ID(+) = wmt.TRANSACTION_ID
160 AND mmt.INVENTORY_ITEM_ID(+) = wmt.PRIMARY_ITEM_ID
161 AND mmt.ORGANIZATION_ID(+) = wmt.ORGANIZATION_ID
162 AND mmt.TRANSACTION_SOURCE_ID(+) = wmt.WIP_ENTITY_ID
163 AND WMT.TRANSACTION_ID = p_transaction_id;
164
165 l_selected_fields inv_label.label_field_variable_tbl_type;
166 l_selected_fields_count NUMBER;
167 l_label_format_id NUMBER := 0;
168 l_label_format VARCHAR2(100);
169 l_printer VARCHAR2(30);
170 l_content_item_data LONG;
171 l_content_rec_index NUMBER := 0;
172 l_return_status VARCHAR2(240);
173 l_error_message VARCHAR2(240);
174 l_msg_count NUMBER;
175 l_api_status VARCHAR2(240);
176 l_msg_data VARCHAR2(240);
177 i NUMBER;
178 l_id NUMBER;
179 l_label_index NUMBER;
180 l_label_request_id NUMBER;
181 --I cleanup, use l_prev_format_id to record the previous label format
182 l_prev_format_id NUMBER;
183 -- I cleanup, user l_prev_sub to record the previous subinventory
184 --so that get_printer is not called if the subinventory is the same
185 l_subinventory VARCHAR2(30);
186 l_organization_id NUMBER;
187 l_prev_sub VARCHAR2(30);
188 -- a list of columns that are selected for format
189 l_column_name_list LONG;
190 v_wip_move_lines_content wip_move_lines_cur%ROWTYPE;
191 BEGIN
192 l_debug := inv_label.l_debug;
193 -- Initialize return status as success
194 x_return_status := fnd_api.g_ret_sts_success;
195
196 IF (l_debug = 1) THEN
197 TRACE('**In PVT16: Wip Move Content label**');
198 TRACE(' Business_flow: ' || p_label_type_info.business_flow_code);
199 TRACE(' Transaction ID:' || p_transaction_id);
200 END IF;
201
202 -- Get
203 IF p_transaction_id IS NOT NULL THEN
204 -- txn driven
205 IF p_label_type_info.business_flow_code IN (41) THEN
206 -- WIP Move Transaction
207 OPEN wip_move_lines_cur;
208
209 FETCH wip_move_lines_cur
210 INTO v_wip_move_lines_content;
211
212 IF wip_move_lines_cur%NOTFOUND THEN
213 IF (l_debug = 1) THEN
214 TRACE(' No record found for Wip Move Transaction, no WIP Move content label print');
215 END IF;
216
217 CLOSE wip_move_lines_cur;
218
219 RETURN;
220 END IF;
221 ELSE
222 IF (l_debug = 1) THEN
223 TRACE(' Invalid business flow code ' || p_label_type_info.business_flow_code);
224 END IF;
225
226 RETURN;
227 END IF;
228 ELSE
229 -- On demand, get information from input_param
230 IF (l_debug = 1) THEN
231 TRACE('Manual mode, not available yet');
232 END IF;
233 END IF;
234
235 IF (l_debug = 1) THEN
236 TRACE(' Getting selected fields ');
237 END IF;
238
239 inv_label.get_variables_for_format(x_variables => l_selected_fields, x_variables_count => l_selected_fields_count
240 , p_format_id => p_label_type_info.default_format_id);
241
242 IF (l_selected_fields_count = 0)
243 OR(l_selected_fields.COUNT = 0) THEN
244 IF (l_debug = 1) THEN
245 TRACE('no fields defined for this format: ' || p_label_type_info.default_format_id || ',' || p_label_type_info.default_format_name);
246 END IF;
247 END IF;
248
249 IF (l_debug = 1) THEN
250 TRACE(' Found variable defined for this format, count = ' || l_selected_fields_count);
251 END IF;
252
253 l_content_rec_index := 0;
254
255 IF (l_debug = 1) THEN
256 TRACE('** in PVT16.get_variable_data ** , start ');
257 END IF;
258
259 l_printer := p_label_type_info.default_printer;
260 l_label_index := 1;
261 l_prev_format_id := p_label_type_info.default_format_id;
262 l_prev_sub := '####';
263
264 l_content_item_data := '';
265
266 IF (l_debug = 1) THEN
267 TRACE(' New WIP Move Content label ');
268 END IF;
269
270 l_content_rec_index := l_content_rec_index + 1;
271
272 IF (l_debug = 1) THEN
273 TRACE(' New Label ' || l_content_rec_index);
274 END IF;
275
276
277 --R12 : RFID compliance project
278 --Calling rules engine before calling to get printer
279
280 IF (l_debug = 1) THEN
281 TRACE(
282 'Apply Rules engine for format'
283 || ',manual_format_id='
284 || p_label_type_info.manual_format_id
285 || ',manual_format_name='
286 || p_label_type_info.manual_format_name
287 );
288 END IF;
289
290 /* insert a record into wms_label_requests entity to
291 call the label rules engine to get appropriate label */
292
293 inv_label.get_format_with_rule(
294 p_document_id => p_label_type_info.label_type_id
295 , p_label_format_id => p_label_type_info.manual_format_id
296 , p_organization_id => v_wip_move_lines_content.move_organization_id
297 , p_inventory_item_id => v_wip_move_lines_content.move_primary_item_id
298 , p_lpn_id => NULL -- Bug 5509692.
299 , p_last_update_date => SYSDATE
300 , p_last_updated_by => fnd_global.user_id
301 , p_creation_date => SYSDATE
302 , p_created_by => fnd_global.user_id
303 --, p_printer_name => l_printer-- Removed in R12: 4396558
304 , p_business_flow_code => p_label_type_info.business_flow_code
305 , x_return_status => l_return_status
306 , x_label_format_id => l_label_format_id
307 , x_label_format => l_label_format
308 , x_label_request_id => l_label_request_id
309 );
310
311 IF l_return_status <> 'S' THEN
312 fnd_message.set_name('WMS', 'WMS_LABL_RULE_ENGINE_FAILED');
313 fnd_msg_pub.ADD;
314 l_label_format := p_label_type_info.default_format_id;
315 l_label_format_id := p_label_type_info.default_format_name;
316 END IF;
317
318 IF (l_debug = 1) THEN
319 TRACE('did apply label ' || l_label_format || ',' || l_label_format_id || ',req_id ' || l_label_request_id);
320 END IF;
321
322 IF p_label_type_info.manual_format_id IS NOT NULL THEN
323 l_label_format_id := p_label_type_info.manual_format_id;
324 l_label_format := p_label_type_info.manual_format_name;
325
326 IF (l_debug = 1) THEN
327 TRACE('Manual format passed in:' || l_label_format_id || ',' || l_label_format);
328 END IF;
329 END IF;
330
331
332 IF (l_debug = 1) THEN
333 TRACE(
334 ' Getting printer, manual_printer='
335 || p_label_type_info.manual_printer
336 || ',sub='
337 || l_subinventory
338 || ',default printer='
339 || p_label_type_info.default_printer
340 );
341 END IF;
342
343 -- IF clause Added for Add format/printer for manual request
344 IF p_label_type_info.manual_printer IS NULL THEN
345 IF (l_subinventory IS NOT NULL)
346 AND(l_subinventory <> l_prev_sub) THEN
347 IF (l_debug = 1) THEN
348 TRACE('getting printer with org, sub' || l_organization_id || ',' || l_subinventory);
349 END IF;
350
351 BEGIN
352 wsh_report_printers_pvt.get_printer(
353 p_concurrent_program_id => p_label_type_info.label_type_id
354 , p_user_id => fnd_global.user_id
355 , p_responsibility_id => fnd_global.resp_id
356 , p_application_id => fnd_global.resp_appl_id
357 , p_organization_id => l_organization_id
358 , p_format_id =>l_label_format_id --added in r12 RFID 4396558
359 , p_zone => l_subinventory
360 , x_printer => l_printer
361 , x_api_status => l_api_status
362 , x_error_message => l_error_message
363 );
364
365 IF l_api_status <> 'S' THEN
366 IF (l_debug = 1) THEN
367 TRACE('Error in calling get_printer, set printer as default printer, err_msg:' || l_error_message);
368 END IF;
369
370 l_printer := p_label_type_info.default_printer;
371 END IF;
372 EXCEPTION
373 WHEN OTHERS THEN
374 l_printer := p_label_type_info.default_printer;
375 END;
376
377 l_prev_sub := l_subinventory;
378
379 END IF;
380 ELSE
381 IF (l_debug = 1) THEN
382 TRACE('Set printer as Manual Printer passed in:' || p_label_type_info.manual_printer);
383 END IF;
384
385 l_printer := p_label_type_info.manual_printer;
386 END IF;
387
388
389
390
391 IF (l_label_format_id IS NOT NULL) THEN
392 -- Derive the fields for the format either passed in or derived via the rules engine.
393 IF l_label_format_id <> NVL(l_prev_format_id, -999) THEN
394 IF (l_debug = 1) THEN
395 TRACE(' Getting variables for new format ' || l_label_format);
396 END IF;
397
398 inv_label.get_variables_for_format(x_variables => l_selected_fields, x_variables_count => l_selected_fields_count
399 , p_format_id => l_label_format_id);
400 l_prev_format_id := l_label_format_id;
401
402 IF (l_selected_fields_count = 0)
403 OR(l_selected_fields.COUNT = 0) THEN
404 IF (l_debug = 1) THEN
405 TRACE('no fields defined for this format: ' || l_label_format || ',' || l_label_format_id);
406 END IF;
407
408 GOTO nextlabel;
409 END IF;
410
411 IF (l_debug = 1) THEN
412 TRACE(' Found selected_fields for format ' || l_label_format || ', num=' || l_selected_fields_count);
413 END IF;
414 END IF;
415 ELSE
416 IF (l_debug = 1) THEN
417 TRACE('No format exists for this label, goto nextlabel');
418 END IF;
419
420 GOTO nextlabel;
421 END IF;
422
423 /* variable header */
424 l_content_item_data := l_content_item_data || label_b;
425
426 IF l_label_format <> NVL(p_label_type_info.default_format_name, '@@@') THEN
427 l_content_item_data := l_content_item_data || ' _FORMAT="' || NVL(p_label_type_info.manual_format_name, l_label_format) || '"';
428 END IF;
429
430 IF (l_printer IS NOT NULL)
431 AND(l_printer <> NVL(p_label_type_info.default_printer, '###')) THEN
432 l_content_item_data := l_content_item_data || ' _PRINTERNAME="' || l_printer || '"';
433 END IF;
434
435 l_content_item_data := l_content_item_data || tag_e;
436
437 IF (l_debug = 1) THEN
438 TRACE('Starting assign variables, ');
439 END IF;
440
441 l_column_name_list := 'Set variables for ';
442
443 /* Modified for Bug 4072474 -start*/
444 l_custom_sql_ret_status := FND_API.G_RET_STS_SUCCESS;
445 /* Modified for Bug 4072474 -End*/
446
447 -- Fix for bug: 4179593 Start
448 l_CustSqlWarnFlagSet := FALSE;
449 l_CustSqlErrFlagSet := FALSE;
450 l_CustSqlWarnMsg := NULL;
451 l_CustSqlErrMsg := NULL;
452 -- Fix for bug: 4179593 End
453
454 /* Loop for each selected fields, find the columns and write into the XML_content*/
455 FOR i IN 1 .. l_selected_fields.COUNT LOOP
456 IF (l_debug = 1) THEN
457 l_column_name_list := l_column_name_list || ',' || l_selected_fields(i).column_name;
458 END IF;
459
460 ---------------------------------------------------------------------------------------------
461 -- Project: 'Custom Labels' (A 11i10+ Project) |
462 -- Author: Dinesh ([email protected]) |
463 -- Change Description: |
464 -- The check (SQL_STMT <> NULL and COLUMN_NAME = NULL) implies that the field is a |
465 -- Custom SQL based field. Handle it appropriately. |
466 ---------------------------------------------------------------------------------------------
467 IF (l_selected_fields(i).SQL_STMT IS NOT NULL AND l_selected_fields(i).column_name = 'sql_stmt') THEN
468 IF (l_debug = 1) THEN
469 trace('Custom Labels Trace [INVLA16B.pls]: ------------------------- REPORT BEGIN-------------------------------------');
470 trace('Custom Labels Trace [INVLA16B.pls]: LABEL_FIELD_ID : ' || l_selected_fields(i).label_field_id);
471 trace('Custom Labels Trace [INVLA16B.pls]: FIELD_VARIABLE_NAME : ' || l_selected_fields(i).variable_name);
472 trace('Custom Labels Trace [INVLA16B.pls]: COLUMN_NAME : ' || l_selected_fields(i).column_name);
473 trace('Custom Labels Trace [INVLA16B.pls]: SQL_STMT : ' || l_selected_fields(i).sql_stmt);
474 END IF;
475 l_sql_stmt := l_selected_fields(i).sql_stmt;
476 IF (l_debug = 1) THEN
477 trace('Custom Labels Trace [INVLA16B.pls]: l_sql_stmt BEFORE REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
478 END IF;
479 l_sql_stmt := l_sql_stmt || ' AND WLR.LABEL_REQUEST_ID = :REQUEST_ID';
480 IF (l_debug = 1) THEN
481 trace('Custom Labels Trace [INVLA16B.pls]: l_sql_stmt AFTER REQUEST_ID Filter Concatenation: ' || l_sql_stmt);
482
483 END IF;
484 BEGIN
485 IF (l_debug = 1) THEN
486 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 1');
487 trace('Custom Labels Trace [INVLA16B.pls]: LABEL_REQUEST_ID : ' || l_label_request_id);
488 END IF;
489 OPEN c_sql_stmt FOR l_sql_stmt using l_label_request_id;
490 LOOP
491 FETCH c_sql_stmt INTO l_sql_stmt_result;
492 EXIT WHEN c_sql_stmt%notfound OR c_sql_stmt%rowcount >=2;
493 END LOOP;
494
495 IF (c_sql_stmt%rowcount=1 AND l_sql_stmt_result IS NULL) THEN
496 x_return_status := FND_API.G_RET_STS_SUCCESS;
497 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
498
499 fnd_message.set_name('WMS','WMS_CS_NULL_VALUE_RETURNED');
500 fnd_msg_pub.ADD;
501 -- Fix for bug: 4179593 Start
502 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
503 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
504 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
505 l_CustSqlWarnFlagSet := TRUE;
506 -- Fix for bug: 4179593 End
507 IF (l_debug = 1) THEN
508 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 2');
509 trace('Custom Labels Trace [INVLA16B.pls]: l_sql_stmt_result is: ' || l_sql_stmt_result);
510 trace('Custom Labels Trace [INVLA16B.pls]: WARNING: NULL value returned by the custom SQL Query.');
511 trace('Custom Labels Trace [INVLA16B.pls]: l_custom_sql_ret_status is set to : ' || l_custom_sql_ret_status );
512 END IF;
513 ELSIF c_sql_stmt%rowcount=0 THEN
514 IF (l_debug = 1) THEN
515 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 3');
516 trace('Custom Labels Trace [INVLA16B.pls]: WARNING: No row returned by the Custom SQL query');
517 END IF;
518 x_return_status := FND_API.G_RET_STS_SUCCESS;
519 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
520
521 fnd_message.set_name('WMS','WMS_CS_NO_DATA_FOUND');
522 fnd_msg_pub.ADD;
523 /* Replaced following statement for Bug 4207625: Anupam Jain*/
524 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
525 -- Fix for bug: 4179593 Start
526 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
527 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
528 l_CustSqlWarnMsg := l_custom_sql_ret_msg;
529 l_CustSqlWarnFlagSet := TRUE;
530 -- Fix for bug: 4179593 End
531 ELSIF c_sql_stmt%rowcount>=2 THEN
532 IF (l_debug = 1) THEN
533 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 4');
534 trace('Custom Labels Trace [INVLA16B.pls]: ERROR: Multiple values returned by the Custom SQL query');
535 END IF;
536 l_sql_stmt_result := NULL;
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
539 fnd_message.set_name('WMS','WMS_CS_MULTIPLE_VALUES_RETURN');
540 fnd_msg_pub.ADD;
541 /* Replaced following statement for Bug 4207625: Anupam Jain*/
542 /*fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_status);*/
543 -- Fix for bug: 4179593 Start
544 --fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => l_custom_sql_ret_msg);
545 l_custom_sql_ret_msg := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
546 l_CustSqlErrMsg := l_custom_sql_ret_msg;
547 l_CustSqlErrFlagSet := TRUE;
548 -- Fix for bug: 4179593 End
549 END IF;
550 IF (c_sql_stmt%ISOPEN) THEN
551 CLOSE c_sql_stmt;
552 END IF;
553 EXCEPTION
554 WHEN OTHERS THEN
555 IF (c_sql_stmt%ISOPEN) THEN
556 CLOSE c_sql_stmt;
557 END IF;
558 IF (l_debug = 1) THEN
559 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 5');
560 trace('Custom Labels Trace [INVLA16B.pls]: Unexpected Error has occured in GET_VARIABLES_DATA');
561 END IF;
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 fnd_message.set_name('WMS','WMS_CS_WRONG_SQL_CONSTRUCT');
564 fnd_msg_pub.ADD;
565 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567 END;
568 IF (l_debug = 1) THEN
569 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 6');
570 trace('Custom Labels Trace [INVLA16B.pls]: Before assigning it to l_content_item_data');
571 END IF;
572 l_content_item_data := l_content_item_data
573 || variable_b
574 || l_selected_fields(i).variable_name
575 || '">'
576 || l_sql_stmt_result
577 || variable_e;
578 l_sql_stmt_result := NULL;
579 l_sql_stmt := NULL;
580 IF (l_debug = 1) THEN
581 trace('Custom Labels Trace [INVLA16B.pls]: At Breadcrumb 7');
582 trace('Custom Labels Trace [INVLA16B.pls]: After assigning it to l_content_item_data');
583 trace('Custom Labels Trace [INVLA16B.pls]: --------------------------REPORT END-------------------------------------');
584 END IF;
585 --------------------End of this change for Custom Labels project code--------------------
586 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_acct_period' THEN
587 l_content_item_data :=
588 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_acct_period
589 || variable_e;
590 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute_catagory' THEN
591 l_content_item_data :=
592 l_content_item_data
593 || variable_b
594 || l_selected_fields(i).variable_name
595 || '">'
596 || v_wip_move_lines_content.move_attribute_catagory
597 || variable_e;
598 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute1' THEN
599 l_content_item_data :=
600 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute1
601 || variable_e;
602 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute2' THEN
603 l_content_item_data :=
604 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute2
605 || variable_e;
606 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute3' THEN
607 l_content_item_data :=
608 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute3
609 || variable_e;
610 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute4' THEN
611 l_content_item_data :=
612 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute4
613 || variable_e;
614 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute5' THEN
615 l_content_item_data :=
616 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute5
617 || variable_e;
618 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute6' THEN
619 l_content_item_data :=
620 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute6
621 || variable_e;
622 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute7' THEN
623 l_content_item_data :=
624 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute7
625 || variable_e;
626 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute8' THEN
627 l_content_item_data :=
628 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute8
629 || variable_e;
630 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute9' THEN
631 l_content_item_data :=
632 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute9
633 || variable_e;
634 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute10' THEN
635 l_content_item_data :=
636 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute10
637 || variable_e;
638 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute11' THEN
639 l_content_item_data :=
640 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute11
641 || variable_e;
642 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute12' THEN
643 l_content_item_data :=
644 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute12
645 || variable_e;
646 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute13' THEN
647 l_content_item_data :=
648 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute13
649 || variable_e;
650 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute14' THEN
651 l_content_item_data :=
652 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute14
653 || variable_e;
654 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_attribute15' THEN
655 l_content_item_data :=
656 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_attribute15
657 || variable_e;
658 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_created_by' THEN
659 l_content_item_data :=
660 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_created_by
661 || variable_e;
662 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_creation_date' THEN
663 l_content_item_data :=
664 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_creation_date
665 || variable_e;
666 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_employee' THEN
667 l_content_item_data :=
668 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_employee_id
669 || variable_e;
670 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_fm_operation_seq_num' THEN
671 l_content_item_data :=
672 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_fm_operation_seq_num
673 || variable_e;
674 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_fm_operation_code' THEN
675 l_content_item_data :=
676 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_fm_operation_code
677 || variable_e;
678 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_fm_department' THEN
679 l_content_item_data :=
680 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_fm_department
681 || variable_e;
682 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_fm_intraoperation_step' THEN
686 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_job_quantity_snapshot' THEN
683 l_content_item_data :=
684 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_fm_intraoperation_step
685 || variable_e;
687 l_content_item_data :=
688 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_job_quantity_snapshot
689 || variable_e;
690 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_last_updated_by' THEN
691 l_content_item_data :=
692 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_last_updated_by
693 || variable_e;
694 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_last_update_date' THEN
695 l_content_item_data :=
696 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_last_update_date
697 || variable_e;
698 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_last_update_login' THEN
699 l_content_item_data :=
700 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_last_update_login
701 || variable_e;
702 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_line' THEN
703 l_content_item_data :=
704 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_line
705 || variable_e;
706 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_organization_code' THEN
707 l_content_item_data :=
708 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_organization_code
709 || variable_e;
710 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_organization_name' THEN
711 l_content_item_data :=
712 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_organization_name
713 || variable_e;
714 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_overcomp_primary_quantity' THEN
715 l_content_item_data :=
716 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_overcomp_primary_quantity
717 || variable_e;
718 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_overcomp_txn_quantity' THEN
719 l_content_item_data :=
720 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_overcomp_txn_quantity
721 || variable_e;
722 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_primary_item' THEN
723 l_content_item_data :=
724 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_primary_item
725 || variable_e;
726 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_primary_quantity' THEN
727 l_content_item_data :=
728 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_primary_quantity
729 || variable_e;
730 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_primary_uom' THEN
731 l_content_item_data :=
732 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_primary_uom
733 || variable_e;
734 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_qa_collection_id' THEN
735 l_content_item_data :=
736 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_qa_collection_id
737 || variable_e;
738 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_reason' THEN
739 l_content_item_data :=
740 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_reason
741 || variable_e;
742 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_reference' THEN
743 l_content_item_data :=
744 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_reference
745 || variable_e;
746 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_scrap_account' THEN
747 l_content_item_data :=
748 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_scrap_account
749 || variable_e;
750 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_to_operation_seq_num' THEN
751 l_content_item_data :=
752 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_to_operation_seq_num
753 || variable_e;
754 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_to_operation_code' THEN
755 l_content_item_data :=
756 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_to_operation_code
757 || variable_e;
758 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_to_department' THEN
759 l_content_item_data :=
760 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_to_department
761 || variable_e;
762 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_to_intraoperation_step' THEN
763 l_content_item_data :=
764 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_to_intraoperation_step
765 || variable_e;
766 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_transaction_date' THEN
767 l_content_item_data :=
771 l_content_item_data :=
768 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_transaction_date
769 || variable_e;
770 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_transaction_id' THEN
772 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_transaction_id
773 || variable_e;
774 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_transaction_quantity' THEN
775 l_content_item_data :=
776 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_transaction_quantity
777 || variable_e;
778 ELSIF LOWER(l_selected_fields(i).column_name) = 'move_transaction_uom' THEN
779 l_content_item_data :=
780 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.move_transaction_uom
781 || variable_e;
782 ELSIF LOWER(l_selected_fields(i).column_name) = 'wip_entity_id' THEN
783 l_content_item_data :=
784 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_entity_id
785 || variable_e;
786 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_name' THEN
787 l_content_item_data :=
788 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_job_name
789 || variable_e;
790 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_type' THEN
791 l_content_item_data :=
792 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_job_type
793 || variable_e;
794 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_qty' THEN
795 l_content_item_data :=
796 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_job_start_quantity
797 || variable_e;
798 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_scheduled_start_date' THEN
799 l_content_item_data :=
800 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_job_start_date
801 || variable_e;
802 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_scheduled_completion_date' THEN
803 l_content_item_data :=
804 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.wip_job_completion_date
805 || variable_e;
806 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_completion_subinventory' THEN
807 l_content_item_data :=
808 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.job_completion_subinventory
809 || variable_e;
810 ELSIF LOWER(l_selected_fields(i).column_name) = 'job_completion_locator' THEN
811 l_content_item_data :=
812 l_content_item_data || variable_b || l_selected_fields(i).variable_name || '">' || v_wip_move_lines_content.job_completion_locator
813 || variable_e;
814 END IF;
815 END LOOP;
816
817 l_content_item_data := l_content_item_data || label_e;
818 x_variable_content(l_label_index).label_content := l_content_item_data;
819 x_variable_content(l_label_index).label_request_id := l_label_request_id;
820
821 --------------------Start of changes for Custom Labels project code------------------
822 -- Fix for bug: 4179593 Start
823 IF (l_CustSqlWarnFlagSet) THEN
824 l_custom_sql_ret_status := INV_LABEL.G_WARNING;
825 l_custom_sql_ret_msg := l_CustSqlWarnMsg;
826 END IF;
827
828 IF (l_CustSqlErrFlagSet) THEN
829 l_custom_sql_ret_status := FND_API.G_RET_STS_ERROR;
830 l_custom_sql_ret_msg := l_CustSqlErrMsg;
831 END IF;
832 -- Fix for bug: 4179593 End
833
834 x_variable_content(l_label_index).label_status := l_custom_sql_ret_status ;
835 x_variable_content(l_label_index).error_message := l_custom_sql_ret_msg;
836
837 --------------------End of this changes for Custom Labels project code---------------
838
839 l_label_index := l_label_index + 1;
840
841 <<nextlabel>>
842 l_content_item_data := '';
843 l_custom_sql_ret_status := NULL;
844 l_custom_sql_ret_msg := NULL;
845
846 IF (l_debug = 1) THEN
847 TRACE(l_column_name_list);
848 TRACE(' Finished writing variables ');
849 END IF;
850
851 CLOSE wip_move_lines_cur;
852
853 --x_variable_content := x_variable_content || l_content_item_data ;
854
855
856 END get_variable_data;
857
858 PROCEDURE get_variable_data(
859 x_variable_content OUT NOCOPY LONG
860 , x_msg_count OUT NOCOPY NUMBER
861 , x_msg_data OUT NOCOPY VARCHAR2
862 , x_return_status OUT NOCOPY VARCHAR2
863 , p_label_type_info IN inv_label.label_type_rec
864 , p_transaction_id IN NUMBER
865 , p_input_param IN mtl_material_transactions_temp%ROWTYPE
866 , p_transaction_identifier IN NUMBER
867 ) IS
868 l_variable_data_tbl inv_label.label_tbl_type;
869 BEGIN
870 get_variable_data(
871 x_variable_content => l_variable_data_tbl
872 , x_msg_count => x_msg_count
873 , x_msg_data => x_msg_data
874 , x_return_status => x_return_status
875 , p_label_type_info => p_label_type_info
876 , p_transaction_id => p_transaction_id
877 , p_input_param => p_input_param
878 , p_transaction_identifier => p_transaction_identifier
879 );
880 x_variable_content := '';
881
882 FOR i IN 1 .. l_variable_data_tbl.COUNT() LOOP
883 x_variable_content := x_variable_content || l_variable_data_tbl(i).label_content;
884 END LOOP;
885 END get_variable_data;
886 END inv_label_pvt16;