[Home] [Help]
PACKAGE BODY: APPS.GMD_COA_DATA_OM_NEW
Source
1 PACKAGE BODY GMD_COA_DATA_OM_NEW AS
2 /* $Header: GMDGCOAB.pls 120.9.12000000.3 2007/10/17 22:02:58 uphadtar ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDGCOAB.pls |
11 --| Package Name : GMD_COA_DATA_OM_NEW |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer API for Population Of COA DATA |
16 --| |
17 --| HISTORY |
18 --| Manish Gupta 20-June-2003 Created. |
19 --| Anoop Baddam 11-MAR-2004 BUG#3482676 Modified the procedure |
20 --| getLatestSample to check the specified lot no. |
21 --| Vipul Vaish 30-APR-2004 BUG#3588346 Modified the cursor |
22 --| c_get_test in procedure populate_spec such that tests |
23 --| which have no Evaluation would not be shown on COA |
24 --| report. |
25 --| Rameshwar 07-MAY-2004 BUG#3615409 |
26 --| Initilized the lot_no and moved the call_spec_match |
27 --| within the loop in the procedure get_order_params |
28 --| Sulipta 23-SEP-2004 BUG#3710191 |
29 --| Added code to populate data into ship_uom1 , ship_uom2 |
30 --| Sulipta 18-APR-2005 BUG#4260445 |
31 --| Added code to get the value of text_code from gmd_spe- |
32 --| -cifications table and inserted that value to gmd_coa_ |
33 --| headers table corresponding to the spec_id.Added the |
34 --| procedure populate_hdr_text to insert values into gmd_ |
35 --| coa_spec_hdr_text table from qc_text_tbl. |
36 --| Saikiran 27-Sep-2005 Made Inventory Convergence changes |
37 --| Saikiran 03-Nov-2005 Bug# 4662469 |
38 --| RLNAGARA 21-Feb-2006 Bug 4916856 Modified the cursors in the proc |
39 --| get_order_params by replacing the decode statement by OR . |
40 --| RAGSRIVA 02-Aug-2006 Bug 5399406 Modified the procedure |
41 --| populate_coa_data to assign param_rec.ship_to_site_id |
42 --| to hdr_rec.ship_to_site_id. |
43 --| RAGSRIVA 06-Nov-2006 Bug 5629675 Modified the cursor get_lot_tran in |
44 --| procedure get_order_params to select the lot number. |
45 --| srakrish 11-Jan-2006 Bug 5747932: Modified populate_results procedure |
46 --| such that non validated tests results are displayed |
47 --| Uday Phadtare 16-OCT-2007 Bug 6485606. Changed the column from inv_uom |
48 --| to uom while inserting test_unit into gmd_coa_details table. |
49 --+==========================================================================+
50 -- End of comments
51
52 /* Global variables */
53 COA_ID NUMBER:=0;
54 G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_COA_DATA_OM_NEW';
55 G_tmp BOOLEAN := FND_MSG_PUB.Check_Msg_Level(0) ; -- temp call to initialize the
56 -- msg level threshhold gobal
57 -- variable.
58 G_debug_level NUMBER(2) := FND_MSG_PUB.G_Msg_Level_Threshold; -- Use this variable everywhere
59 -- to decide to log a debug msg.
60
61
62 PROCEDURE PUT_SPEC_IN_LOG(p_spec_id IN NUMBER,
63 x_return_status OUT NOCOPY VARCHAR2);
64 PROCEDURE log_msg(p_msg_text IN VARCHAR2);
65
66 -- Only called when the spec_id is returned by spec match
67 PROCEDURE insert_hdr_rec (p_hdr_rec t_coa_hdr_rec,
68 x_return_status OUT NOCOPY VARCHAR2) is
69 X_user_id NUMBER:= FND_GLOBAL.USER_ID;
70 X_login_id NUMBER:= FND_GLOBAL.LOGIN_ID;
71 BEGIN
72
73 PrintLn('Begin procedure insert_hdr_rec');
74 PrintLn('Inserting into gmd_coa_headers table');
75 INSERT INTO gmd_coa_headers (gmd_coa_id,
76 order_id,
77 line_id,
78 organization_id, --INVCONV
79 organization_code, --INVCONV
80 order_no,
81 custpo_no,
82 shipdate,
83 cust_id,
84 cust_no,
85 cust_name,
86 bol_id,
87 bol_no,
88 inventory_item_id, --INVCONV
89 item_number, --INVCONV
90 item_description,
91 revision, --Bug# 4662469
92 subinventory, --INVCONV
93 lot_number, --INVCONV
94 lot_description, --INVCONV
95 order_qty1,
96 order_uom1, --INVCONV
97 order_qty2,
98 order_uom2, --INVCONV
99 ship_qty1,
100 ship_qty2,
101 ship_qty_uom1, -- Bug # 3710191 Added ship_uom1 and ship_uom2
102 ship_qty_uom2, --INVCONV
103 report_title,
104 spec_hdr_text_code, -- Bug # 4260445
105 created_by, creation_date, last_update_date,
106 last_updated_by, last_update_login)
107 VALUES (p_hdr_rec.gmd_coa_id,
108 p_hdr_rec.order_id,
109 p_hdr_rec.line_id,
110 p_hdr_rec.organization_id,
111 p_hdr_rec.organization_code,
112 p_hdr_rec.order_no,
113 p_hdr_rec.custpo_no,
114 p_hdr_rec.shipdate,
115 p_hdr_rec.cust_id,
116 p_hdr_rec.cust_no,
117 p_hdr_rec.cust_name,
118 p_hdr_rec.bol_id,
119 p_hdr_rec.bol_no,
120 p_hdr_rec.inventory_item_id,
121 p_hdr_rec.item_number,
122 p_hdr_rec.item_description,
123 p_hdr_rec.revision, --bug# 4662469
124 p_hdr_rec.subinventory,
125 p_hdr_rec.lot_number,
126 p_hdr_rec.lot_description,
127 p_hdr_rec.order_qty1,
128 p_hdr_rec.order_uom1,
129 p_hdr_rec.order_qty2,
130 p_hdr_rec.order_uom2,
131 p_hdr_rec.ship_qty1,
132 p_hdr_rec.ship_qty2,
133 p_hdr_rec.ship_qty_uom1, -- Bug # 3710191 Added these two lines.
134 p_hdr_rec.ship_qty_uom2,
135 p_hdr_rec.report_title,
136 p_hdr_rec.spec_hdr_text_code, -- Bug # 4260445
137 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
138 );
139 --log_msg('Message level is ...'||FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
140 --log_msg('debug level is ...'||G_debug_level);
141 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
142 log_msg('procedure insert_hdr_rec, inserted into hdr table...');
143 END IF;
144 PrintLn('End procedure insert_hdr_rec');
145 PrintLn('Calling procedure Populate_hdr_Text'); -- Bug # 4260445
146 populate_hdr_text(p_hdr_rec,x_return_status); --Bug # 4260445 Calling populate_hdr_text
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 PrintLn('When Others in GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
151 log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
153 END insert_hdr_rec;
154
155 -- Bug # 4260445 Added this procedure to populate data into gmd_coa_spec_hdr_text
156
157 PROCEDURE populate_hdr_text (tbl_hdr IN t_coa_hdr_rec,
158 x_return_status OUT NOCOPY VARCHAR2) IS
159 CURSOR get_text_info (c_text_code qc_text_tbl.text_code%TYPE) IS
160 select paragraph_code,
161 line_no,
162 text
163 from qc_text_tbl
164 where text_code = c_text_code
165 and line_no > 0
166 order by paragraph_code, line_no ;
167
168 BEGIN
169
170 PrintLn('Begin procedure populate_hdr_text');
171
172 FOR text_cur_rec IN get_text_info (tbl_hdr.spec_hdr_text_code)
173 LOOP
174 PrintLn('In gmd_coa_spec_hdr_text LOOP');
175 PrintLn('Inserting into gmd_coa_spec_hdr_text');
176 INSERT into gmd_coa_spec_hdr_text (gmd_coa_id, text_code,
177 paragraph_code, line_no, text)
178 VALUES (tbl_hdr.gmd_coa_id,
179 tbl_hdr.spec_hdr_text_code,
180 text_cur_rec.paragraph_code,
181 text_cur_rec.line_no,
182 text_cur_rec.text);
183 END LOOP;
184 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
185 log_msg('inserted into populate_hdr_text...');
186 END IF;
187
188 PrintLn('End procedure populate_hdr_text');
189 EXCEPTION
190 WHEN OTHERS THEN
191 PrintLn('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
192 log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
194
195 END populate_hdr_text; -- Bug # 4260445
196
197
198
199 PROCEDURE populate_text (tbl_dtl IN t_coa_dtl_rec,
200 x_return_status OUT NOCOPY VARCHAR2) IS
201
202 CURSOR get_text_info (c_text_code qc_text_tbl.text_code%TYPE) IS
203 select paragraph_code,
204 line_no,
205 text
206 from qc_text_tbl
207 where text_code = c_text_code
208 and line_no > 0
209 order by paragraph_code, line_no ;
210
211
212 BEGIN
213
214 PrintLn('Begin procedure populate_text');
215
216 FOR text_cur_rec IN get_text_info (tbl_dtl.spec_text_code)
217 LOOP
218 PrintLn('In gmd_coa_spec_text LOOP');
219 PrintLn('Inserting into gmd_coa_spec_text');
220 INSERT into gmd_coa_spec_text (gmd_coa_id, text_code,
221 paragraph_code, line_no, text)
222 VALUES (tbl_dtl.gmd_coa_id,
223 tbl_dtl.spec_text_code,
224 text_cur_rec.paragraph_code,
225 text_cur_rec.line_no,
226 text_cur_rec.text);
227 END LOOP;
228
229
230 FOR text_cur_rec IN get_text_info (tbl_dtl.rslt_text_code)
231 LOOP
232 PrintLn('In gmd_coa_rslt_text LOOP');
233 PrintLn('Inserting into gmd_coa_rslt_text');
234 INSERT into gmd_coa_rslt_text (gmd_coa_id, text_code,
235 paragraph_code, line_no, text)
236 VALUES (tbl_dtl.gmd_coa_id,
237 tbl_dtl.rslt_text_code,
238 text_cur_rec.paragraph_code,
239 text_cur_rec.line_no,
240 text_cur_rec.text);
241 END LOOP;
242 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
243 log_msg('inserted into populate_text...');
244 END IF;
245
246 PrintLn('End procedure populate_text');
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 PrintLn('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
251 log_msg('GMD_COA_DATA.INSERT_HDR_REC '|| SUBSTR(SQLERRM,1,100));
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
253 END populate_text;
254
255
256
257 PROCEDURE insert_dtl_rec(p_dtl_rec t_coa_dtl_rec,
258 x_return_status OUT NOCOPY VARCHAR2) IS
259 X_user_id NUMBER:= FND_GLOBAL.USER_ID;
260 X_login_id NUMBER:= FND_GLOBAL.LOGIN_ID;
261 BEGIN
262 PrintLn('Begin procedure insert_dtl_rec');
263 PrintLn('Inserting into gmd_coa_details');
264 INSERT into gmd_coa_details (gmd_coa_id,
265 qc_result_id,
266 result_date,
267 qc_spec_id,
268 assay_code,
269 assay_desc,
270 result,
271 specification,
272 uom, --Bug 6485606. Changed inv_uom to uom
273 rslt_text_code,
274 spec_text_code,
275 min_spec,max_spec,
276 test_method,
277 created_by, creation_date,
278 last_update_date,
279 last_updated_by, last_update_login
280 )
281 VALUES (p_dtl_rec.gmd_coa_id,
282 p_dtl_rec.result_id,
283 p_dtl_rec.result_date,
284 p_dtl_rec.spec_id,
285 p_dtl_rec.test_code,
286 p_dtl_rec.test_display,
287 p_dtl_rec.result,
288 p_dtl_rec.specification,
289 p_dtl_rec.test_unit,
290 p_dtl_rec.rslt_text_code,
291 p_dtl_rec.spec_text_code,
292 p_dtl_rec.min_spec,
293 p_dtl_rec.max_spec,
294 p_dtl_rec.test_method,
295 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
296 );
297
298 PrintLn('End procedure insert_dtl_rec');
299 PrintLn('Calling procedure Populate_Text');
300 Populate_Text(p_dtl_rec,x_return_status);
301
302 EXCEPTION
303 WHEN OTHERS THEN
307
304 PrintLn('GMD_COA_DATA.INSERT_DTL_REC '|| SUBSTR(SQLERRM,1,100));
305 log_msg('GMD_COA_DATA.INSERT_DTL_REC '|| SUBSTR(SQLERRM,1,100));
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
308 END insert_dtl_rec;
309
310 PROCEDURE populate_result(p_detail_rec IN OUT NOCOPY t_coa_dtl_rec,
311 p_sample_id NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2) IS
313 --For sample having single result(for printing COA)
314
315 CURSOR get_results(p_sample_id NUMBER,
316 p_test_id NUMBER) IS
317 select r.result_id,
318 r.test_id,
319 ges.spec_id,
320 nvl(r.result_value_char,result_value_num) result,
321 r.result_value_char,
322 r.result_value_num,
323 r.result_date,
324 r.text_code
325 from gmd_results r,
326 gmd_spec_results sr,
327 gmd_event_spec_disp ges
328 where r.sample_id = p_sample_id
329 and r.result_id = sr.result_id
330 and nvl(sr.evaluation_ind,'N') in ('0A','1V','2R','N') -- srakrish bug 5747932: To fetch results for non validated tests.
331 and sr.event_spec_disp_id = ges.event_spec_disp_id
332 and ges.spec_used_for_lot_attrib_ind ='Y'
333 and r.test_id = p_test_id
334 order by r.result_date, r.seq desc;
335
336 CURSOR c_get_range_with_disp(p_test_id NUMBER,
337 p_result NUMBER) IS
338 SELECT display_label_numeric_range
339 FROM gmd_qc_test_values
340 WHERE p_result between min_num and max_num
341 AND test_id = p_test_id;
342
343 BEGIN
344 PrintLn('Begin procedure populate_result');
345 FOR c_results IN get_results(p_sample_id,p_detail_rec.test_id) LOOP
346 PrintLn('In get_results LOOP');
347 p_detail_rec.gmd_coa_id := coa_id;
348 p_detail_rec.result_id := c_results.result_id;
349 p_detail_rec.result_date := c_results.result_date;
350 p_detail_rec.result := c_results.result;
351 p_detail_rec.result_value_num := c_results.result_value_num;
352 p_detail_rec.result_value_char := c_results.result_value_char;
353 p_detail_rec.rslt_text_code := c_results.text_code;
354
355 PrintLn('p_detail_rec.gmd_coa_id = '||p_detail_rec.gmd_coa_id);
356 PrintLn('p_detail_rec.result_id = '||p_detail_rec.result_id);
357 PrintLn('p_detail_rec.result_date = '||p_detail_rec.result_date);
358 PrintLn('p_detail_rec.result = '||p_detail_rec.result);
359 PrintLn('p_detail_rec.result_value_num = '||p_detail_rec.result_value_num);
360 PrintLn('p_detail_rec.result_value_char = '||p_detail_rec.result_value_char);
361 PrintLn('p_detail_rec.rslt_text_code = '||p_detail_rec.rslt_text_code);
362 PrintLn('p_detail_rec.test_type = '||p_detail_rec.test_type);
363
364 IF (p_detail_rec.test_type in ('N','E')) THEN
365 p_detail_rec.result := getprecision(c_results.result,p_detail_rec.report_precision);
366 END IF;
367 IF (p_detail_rec.test_type in ('L')) THEN
368 p_detail_rec.result := get_text_for_range(p_detail_rec.test_id,p_detail_rec.result_value_num);
369 END IF;
370
371 exit;
372 END LOOP;
373 PrintLn('End procedure populate_result');
374 EXCEPTION
375 WHEN OTHERS THEN
376 PrintLn('GMD_COA_DATA.POPULATE_RESULT '|| SUBSTR(SQLERRM,1,100));
377 log_msg('GMD_COA_DATA.POPULATE_RESULT '|| SUBSTR(SQLERRM,1,100));
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
379 END populate_result;
380
381 PROCEDURE populate_composite_results(p_detail_rec IN OUT NOCOPY t_coa_dtl_rec ,
382 p_event_spec_disp_id IN NUMBER,
383 x_return_status OUT NOCOPY VARCHAR2) IS
384 --there is no result date in composite results??
385 CURSOR c_composite_results(p_event_spec_disp_id IN NUMBER,
386 p_test_id IN NUMBER) IS
387 select r.composite_result_id,
388 r.test_id,
389 --nvl(r.mean,r.mode_char) result,
390 r.mean,
391 r.mode_num,
392 r.mode_char,
393 r.low_num,
394 r.high_num,
395 r.range,
396 r.non_validated_result,
397 r.standard_deviation , ges.spec_id
398 from gmd_composite_results r,
399 gmd_composite_spec_disp sd,
400 gmd_event_spec_disp ges
401 where r.composite_spec_disp_id =sd.composite_spec_disp_id
402 and sd.event_spec_disp_id = p_event_spec_disp_id
403 and sd.event_spec_disp_id = ges.event_spec_disp_id
404 and nvl(ges.spec_used_for_lot_attrib_ind,'N') = 'Y'
405 and nvl(sd.latest_ind,'N') = 'Y'
406 and r.test_id = p_test_id;
407
408 BEGIN
409 PrintLn('Begin procedure populate_composite_results');
410 FOR l_results IN c_composite_results(p_event_spec_disp_id,
411 p_detail_rec.test_id) LOOP
412 PrintLn('In c_composite_results LOOP');
413 p_detail_rec.gmd_coa_id := coa_id;
414 p_detail_rec.result_id := l_results.composite_result_id;
415
416 -- In composite result for numeric values is mean
417 -- for char results is mode_char
418 p_detail_rec.result_value_num := l_results.mean;
419 p_detail_rec.result_value_char := l_results.mode_char;
420
421 PrintLn('p_detail_rec.gmd_coa_id = '||p_detail_rec.gmd_coa_id);
422 PrintLn('p_detail_rec.result_id = '||p_detail_rec.result_id);
423 PrintLn('p_detail_rec.result_value_num = '||p_detail_rec.result_value_num);
424 PrintLn('p_detail_rec.result_value_char = '||p_detail_rec.result_value_char);
428 p_detail_rec.result := getprecision(l_results.mean,p_detail_rec.report_precision);
425 PrintLn('p_detail_rec.test_type = '||p_detail_rec.test_type);
426
427 IF (p_detail_rec.test_type in ('N','E')) THEN
429 ELSIF (p_detail_rec.test_type in ('L')) THEN
430 p_detail_rec.result := get_text_for_range(p_detail_rec.test_id,p_detail_rec.result_value_num);
431 ELSE
432 p_detail_rec.result := nvl(l_results.mode_char,l_results.non_validated_result);
433 END IF;
434 EXIT;
435 END LOOP;
436 PrintLn('End procedure populate_composite_results');
437 EXCEPTION
438 WHEN OTHERS THEN
439 PrintLn('GMD_COA_DATA.POPULATE_COMPOSITE_RESULTS '|| SUBSTR(SQLERRM,1,100));
440 log_msg('GMD_COA_DATA.POPULATE_COMPOSITE_RESULTS '|| SUBSTR(SQLERRM,1,100));
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
442 END populate_composite_results;
443
444 PROCEDURE populate_spec(p_spec_id IN NUMBER,
445 p_cust_id IN NUMBER,
446 p_report_type IN VARCHAR2,
447 x_return_status OUT NOCOPY VARCHAR2,
448 p_event_spec_disp_id IN NUMBER DEFAULT NULL,
449 p_sample_id IN NUMBER DEFAULT NULL) IS
450 --Input cust_id, spec_id, result
451 -- output result with report precision, test_display, result_display(for 'L' Only)
452 --Make sure that COA or COC report is determined before this procedure is called.
453 CURSOR c_get_cust_test(p_cust_id NUMBER,
454 p_test_id NUMBER) IS
455 SELECT cust_test_display,
456 report_precision
457 FROM gmd_customer_tests
458 WHERE cust_id = p_cust_id
459 AND test_id = p_test_id;
460
461
462 CURSOR c_get_test( p_spec_id NUMBER ) IS
463 SELECT nvl(s.test_display,t.test_desc) display,
464 s.report_precision,t.test_type,
465 s.target_value_num, s.min_value_num, s.max_value_num,
466 s.target_value_char, s.min_value_char, s.max_value_char,
467 s.text_code spec_text_code,t.test_code,t.test_id,t.test_unit,
468 decode(m.test_method_code,'DEFAULT',NULL, m.test_method_code) test_method_code
469 FROM gmd_spec_tests s,
470 gmd_qc_tests t,
471 gmd_test_methods_b m
472 --Bug 3785184 backing out fix 3588346
473 -- gmd_results r, --BUG#3588346
474 -- gmd_spec_results sr --BUG#3588346
475 WHERE s.test_id= t.test_id
476 AND s.spec_id= p_spec_id
477 AND ((p_report_type = 'COC' and nvl(s.print_spec_ind,'N') ='Y') OR
478 (p_report_type = 'COA' and nvl(s.print_result_ind,'N') ='Y'))
479 -- Bug# 5223677. Pick all tests. Commented following AND condition.
480 -- AND nvl(s.optional_ind,'N') = 'N'
481 AND s.test_method_id = m.test_method_id
482 --Bug 3785184 backing out fix 3588346
483 --BEGIN BUG#3588346
484 -- AND r.sample_id = p_sample_id
485 -- AND s.test_id = r.test_id
486 -- AND r.result_id = sr.result_id
487 -- AND sr.evaluation_ind IS NOT NULL
488 --END BUG#3588346
489 ORDER BY s.seq;
490
491 CURSOR c_get_range_with_disp(p_test_id NUMBER,
492 p_result NUMBER) IS
493 SELECT display_label_numeric_range
494 FROM gmd_qc_test_values
495 WHERE p_result between min_num and max_num
496 AND test_id = p_test_id;
497
498 l_get_cust_test c_get_cust_test%ROWTYPE;
499
500 l_target VARCHAR2(240);
501 l_detail_rec t_coa_dtl_rec;
502 l_detail_rec_blank t_coa_dtl_rec;
503
504
505 BEGIN
506 PrintLn('Begin procedure populate_spec');
507 PrintLn('p_spec_id = '||p_spec_id);
508 PrintLn('p_cust_id = '||p_cust_id);
509 PrintLn('p_report_type = '||p_report_type);
510 PrintLn('p_event_spec_disp_id = '||p_event_spec_disp_id);
511 PrintLn('p_sample_id = '||p_sample_id);
512
513 FOR c_spec_rec IN c_get_test(p_spec_id) LOOP
514 PrintLn('In c_get_test LOOP');
515 --Bug 3785184 Start with blank l_detail_rec
516 l_detail_rec := l_detail_rec_blank;
517
518 l_detail_rec.gmd_coa_id := coa_id;
519 l_detail_rec.test_id := c_spec_rec.test_id;
520 l_detail_rec.test_code := c_spec_rec.test_code;
521 l_detail_rec.test_unit := c_spec_rec.test_unit;
522 l_detail_rec.spec_text_code := c_spec_rec.spec_text_code;
523 l_detail_rec.test_type := c_spec_rec.test_type;
524 l_detail_rec.test_method := c_spec_rec.test_method_code;
525
526
527 l_get_cust_test.cust_test_display := NULL;
528 l_get_cust_test.report_precision := NULL;
529
530 IF (p_cust_id IS NOT NULL) THEN
531 OPEN c_get_cust_test(p_cust_id,c_spec_rec.test_id);
532 FETCH c_get_cust_test INTO l_get_cust_test;
533 CLOSE c_get_cust_test;
534 ELSE
535 l_get_cust_test.cust_test_display := NULL;
536 l_get_cust_test.report_precision := NULL;
537 END IF;
538
539 IF (l_get_cust_test.cust_test_display IS NOT NULL) THEN
540 l_detail_rec.test_display := l_get_cust_test.cust_test_display;
541 ELSE
542 l_detail_rec.test_display := c_spec_rec.display;
543 END IF;
544
545
546 IF (l_get_cust_test.report_precision IS NOT NULL) THEN
547 l_detail_rec.report_precision := l_get_cust_test.report_precision;
548 ELSE
549 l_detail_rec.report_precision := c_spec_rec.report_precision;
550 END IF;
554
551 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
552 log_msg('populate_spec, report preicion is ...'||l_detail_rec.report_precision);
553 END IF;
555 PrintLn('c_spec_rec.test_type = '||c_spec_rec.test_type);
556 IF (c_spec_rec.test_type in ('L')) THEN
557 -- when numeric with display text display only the text
558 l_detail_rec.min_spec := get_text_for_range(l_detail_rec.test_id,c_spec_rec.min_value_num);
559 l_detail_rec.max_spec := get_text_for_range(l_detail_rec.test_id,c_spec_rec.max_value_num);
560 ELSIF (c_spec_rec.test_type in ('N','E')) THEN
561 l_detail_rec.min_spec := getprecision(c_spec_rec.min_value_num,l_detail_rec.report_precision);
562 l_detail_rec.max_spec := getprecision(c_spec_rec.max_value_num,l_detail_rec.report_precision);
563 ELSIF (c_spec_rec.test_type in ('T')) THEN
564 l_detail_rec.min_spec := c_spec_rec.min_value_char;
565 l_detail_rec.max_spec := c_spec_rec.max_value_char;
566 ELSE
567 l_detail_rec.specification := c_spec_rec.target_value_char;
568 l_detail_rec.min_spec := c_spec_rec.target_value_char;
569 l_detail_rec.max_spec := null;
570 END IF;
571
572 PrintLn('l_detail_rec.gmd_coa_id = '||l_detail_rec.gmd_coa_id);
573 PrintLn('l_detail_rec.test_id = '||l_detail_rec.test_id);
574 PrintLn('l_detail_rec.test_code = '||l_detail_rec.test_code);
575 PrintLn('l_detail_rec.test_unit = '||l_detail_rec.test_unit);
576 PrintLn('l_detail_rec.spec_text_code = '||l_detail_rec.spec_text_code);
577 PrintLn('l_detail_rec.test_type = '||l_detail_rec.test_type);
578 PrintLn('l_detail_rec.test_method = '||l_detail_rec.test_method);
579 PrintLn('l_detail_rec.report_precision = '||l_detail_rec.report_precision);
580 PrintLn('l_detail_rec.min_spec = '||l_detail_rec.min_spec);
581 PrintLn('l_detail_rec.max_spec = '||l_detail_rec.max_spec);
582
583 --Bug 3785184 Populate result only for COA report
584 IF (p_report_type = 'COA') THEN
585 IF (p_sample_id IS NOT NULL) THEN
586 PrintLn('Calling procedure populate_result ');
587 populate_result(l_detail_rec,p_sample_id,x_return_status);
588 ELSIF (p_event_spec_disp_id IS NOT NULL) THEN
589 PrintLn('Calling procedure populate_composite_results');
590 populate_composite_results(l_detail_rec,p_event_spec_disp_id,x_return_status);
591 END IF;
592 END IF;
593
594 PrintLn('Calling procedure insert_dtl_rec');
595 insert_dtl_rec(l_detail_rec,x_return_status);
596
597 END LOOP;
598 PrintLn('End procedure populate_spec');
599 EXCEPTION
600 WHEN OTHERS THEN
601 PrintLn('GMD_COA_DATA.POPULATE_SPEC '|| SUBSTR(SQLERRM,1,100));
602 log_msg('GMD_COA_DATA.POPULATE_SPEC '|| SUBSTR(SQLERRM,1,100));
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
604 END populate_spec;
605
606
607
608
609 FUNCTION get_text_for_range(p_test_id NUMBER,
610 p_value NUMBER) RETURN VARCHAR2 IS
611
612 CURSOR c_get_range_with_disp(p_test_id NUMBER,
613 p_value NUMBER) IS
614 SELECT display_label_numeric_range
615 FROM gmd_qc_test_values
616 WHERE p_value between min_num and max_num
617 AND test_id = p_test_id;
618
619 l_text VARCHAR2(240);
620 BEGIN
621 PrintLn('Begin procedure get_text_for_range');
622 PrintLn('p_test_id = '||p_test_id);
623 OPEN c_get_range_with_disp(p_test_id,p_value);
624 FETCH c_get_range_with_disp INTO l_text;
625 CLOSE c_get_range_with_disp;
626 PrintLn('End procedure get_text_for_range');
627 RETURN l_text;
628
629 END get_text_for_range;
630
631
632
633
634 PROCEDURE get_foreign_keys(p_hdr_rec IN OUT NOCOPY t_coa_hdr_rec,
635 x_return_status OUT NOCOPY VARCHAR2) IS
636
637 /*CURSOR c_get_item_no(p_item_id IN NUMBER) IS
638 select item_no, item_desc1
639 from ic_item_mst
640 where item_id = p_item_id;*/
641
642 --INVCONV
643 CURSOR c_get_item_no (p_inventory_item_id IN NUMBER, p_organization_id IN NUMBER) IS
644 SELECT concatenated_segments item_number,
645 description item_description
646 FROM mtl_system_items_b_kfv
647 WHERE inventory_item_id = p_inventory_item_id
648 AND organization_id = p_organization_id;
649
650
651 CURSOR c_get_cust(p_cust_id IN NUMBER) IS
652 select a.account_number cust_no,
653 b.party_name cust_name
654 from hz_cust_accounts a,
655 hz_parties b
656 where a.party_id = b.party_id
657 and a.cust_account_id = p_cust_id;
658
659 --INVCONV
660 /*CURSOR c_get_lot(p_lot_id IN NUMBER,
661 p_lot_no in VARCHAR2) IS
662 select a.lot_no,a.lot_desc,
663 a.sublot_no
664 from ic_lots_mst a
665 where (p_lot_id is NULL OR lot_id = p_lot_id)
666 and (p_lot_no IS NULL or lot_no = p_lot_no);*/
667
668 BEGIN
669 PrintLn('Begin procedure get_foreign_keys');
670 PrintLn('p_hdr_rec.cust_id = '||p_hdr_rec.cust_id);
671 PrintLn('p_hdr_rec.lot_no = '||p_hdr_rec.lot_number);
672 OPEN c_get_item_no(p_hdr_rec.inventory_item_id, p_hdr_rec.organization_id);
673 FETCH c_get_item_no INTO p_hdr_rec.item_number, p_hdr_rec.item_description;
674 CLOSE c_get_item_no;
675
676 IF (p_hdr_rec.cust_id IS NOT NULL) THEN
677 OPEN c_get_cust(p_hdr_rec.cust_id);
681
678 FETCH c_get_cust INTO p_hdr_rec.cust_no, p_hdr_rec.cust_name;
679 CLOSE c_get_cust;
680 END IF;
682 PrintLn('End procedure get_foreign_keys');
683 EXCEPTION
684 WHEN OTHERS THEN
685 PrintLn('GMD_COA_DATA.GET_FOREIGN_KEYS '|| SUBSTR(SQLERRM,1,100));
686 log_msg('GMD_COA_DATA.GET_FOREIGN_KEYS '|| SUBSTR(SQLERRM,1,100));
687 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
688
689 END get_foreign_keys;
690
691
692
693 FUNCTION getprecision (p_value IN NUMBER,
694 p_report_precision IN NUMBER) return VARCHAR2 IS
695 L_local NUMBER;
696 l_format VARCHAR2(50);
697
698 begin
699 PrintLn('Begin Function getprecision');
700 PrintLn('p_value = '||p_value);
701 PrintLn('p_report_precision = '||p_report_precision);
702
703 -- Bug 3970286
704 -- For 9.97,1 as input parameters, the l_local should be 99 and not 9,
705 -- to accomodate any rounding UP. Added 1 to the length.
706 l_local := POWER(10,LENGTH(TRUNC(p_value))+1) - 1;
707 PrintLn('l_local is '||l_local);
708
709 IF (p_report_precision > 0) THEN
710 l_format := to_char(l_local)||'D'||to_char(power(10,p_report_precision) -1);
711 ELSE
712 l_format := to_char(l_local);
713 END IF;
714 PrintLn('Format string is '||l_format);
715
716 PrintLn('End Function getprecision');
717 return rtrim(ltrim(to_char(p_value,l_format)));
718 EXCEPTION
719 WHEN OTHERS THEN
720 PrintLn('GMD_COA_DATA.GETPRECISION '|| SUBSTR(SQLERRM,1,100));
721 log_msg('GMD_COA_DATA.GETPRECISION '|| SUBSTR(SQLERRM,1,100));
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
723 END getprecision;
724
725
726 PROCEDURE log_msg(p_msg_text IN VARCHAR2) IS
727 BEGIN
728 FND_MESSAGE.SET_NAME('GMI','GMI_DEBUG_API');
729 FND_MESSAGE.SET_TOKEN('MSG',p_msg_text);
730 FND_MSG_PUB.Add;
731 END log_msg ;
732
733 PROCEDURE put_spec_in_log(p_spec_id IN NUMBER,
734 x_return_status OUT NOCOPY VARCHAR2) IS
735 CURSOR c_get_spec(p_spec_id NUMBER) IS
736 SELECT spec_name,spec_vers
737 FROM gmd_specifications_b
738 WHERE spec_id = p_spec_id;
739
740 l_spec_name gmd_specifications.spec_name%TYPE;
741 l_spec_vers gmd_specifications.spec_vers%TYPE;
742
743 BEGIN
744 PrintLn('Begin Procedure put_spec_in_log');
745 OPEN c_get_spec(p_spec_id);
746 FETCH c_get_spec INTO l_spec_name,l_spec_vers;
747 CLOSE c_get_spec;
748 PrintLn('l_spec_name = '||l_spec_name);
749 PrintLn('l_spec_vers = '||l_spec_vers);
750 PrintLn('GMD_QC_SPEC_NAME');
751 FND_MESSAGE.SET_NAME('GMD','GMD_QC_SPEC_NAME');
752 FND_MESSAGE.SET_TOKEN('SPEC_NAME' ,l_spec_name);
753 FND_MESSAGE.SET_TOKEN('SPEC_VERSION',l_spec_vers);
754 FND_MSG_PUB.Add;
755 PrintLn('End Procedure put_spec_in_log');
756 EXCEPTION
757 WHEN OTHERS THEN
758 PrintLn('GMD_COA_DATA.PUT_SPEC_IN_LOG '|| SUBSTR(SQLERRM,1,100));
759 log_msg('GMD_COA_DATA.PUT_SPEC_IN_LOG '|| SUBSTR(SQLERRM,1,100));
760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
761 END put_spec_in_log;
762
763 PROCEDURE getLatestSample(p_spec_id IN NUMBER,
764 p_lot_number IN VARCHAR2,
765 p_event_spec_disp_id OUT NOCOPY NUMBER,
766 p_sample_id OUT NOCOPY NUMBER) IS
767
768 --BUG#3482676 Anoop.
769 --Added condition in where clause to check the lot no specified.
770 CURSOR c_sampling_event(p_spec_id IN NUMBER) IS
771 select nvl(a.SAMPLE_ACTIVE_CNT,0) sample_active_cnt,
772 b.event_spec_disp_id,
773 b.sampling_event_id
774 from gmd_sampling_events
775 a,gmd_event_spec_disp b
776 where b.spec_id =p_spec_id
777 and (a.lot_number = p_lot_number or p_lot_number is null)
778 and b.disposition in ('4A','5AV','5RJ')
779 and b.spec_used_for_lot_attrib_ind ='Y'
780 and a.sampling_event_id= b.sampling_event_id
781 and nvl (a.sample_active_cnt,0) >= 1
782 order by a.creation_date desc ;
783
784 CURSOR c_simple_result(p_sampling_event_id IN NUMBER) IS
785 select sample_id
786 from gmd_samples
787 where sampling_event_id = p_sampling_event_id;
788
789 BEGIN
790 PrintLn('Begin Procedure getLatestSample');
791 PrintLn('p_spec_id = '||p_spec_id);
792 PrintLn('p_lot_number = '||p_lot_number);
793 FOR c_sampling_event_rec IN c_sampling_event(p_spec_id) LOOP
794 PrintLn('In c_sampling_event LOOP');
795 IF (c_sampling_event_rec.SAMPLE_ACTIVE_CNT=1) THEN
796 for c_simple_result_rec in c_simple_result(c_sampling_event_rec.sampling_event_id) loop
797 PrintLn('In c_simple_result LOOP');
798 p_sample_id := c_simple_result_rec.sample_id;
799 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
800 log_msg('getLatestSample sample for spec_id '|| p_sample_id);
801 END IF;
802 PrintLn('getLatestSample sample for spec_id '|| p_sample_id);
803 PrintLn('RETURN End Procedure getLatestSample');
804 RETURN;
805 END LOOP;
806 ELSE
807 p_event_spec_disp_id := c_sampling_event_rec.event_spec_disp_id;
808 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
809 log_msg('getLatestSample p_event_spec_disp_id for spec_id '|| p_event_spec_disp_id);
810 END IF;
814 END IF;
811 PrintLn('getLatestSample p_event_spec_disp_id for spec_id '|| p_event_spec_disp_id);
812 PrintLn('RETURN End Procedure getLatestSample');
813 RETURN;
815 END LOOP;
816 PrintLn('End Procedure getLatestSample');
817 END getLatestSample;
818
819 --Start of comments
820 --+=====================================================================================================+
821 --| API Name : get_result_match_for_spec |
822 --| TYPE : Group |
823 --| Notes : |
824 --| Parameters : item_id - IN PARAMETER item_id of the order line |
825 --| lot_id - IN PARAMETER lot_id |
826 --| whse_code - IN PARAMETER warehouse |
827 --| location - IN PARAMETER location |
828 --| result_type - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
829 --| result_type will have 2 values - 'I' for Individual Result, |
830 --| 'C' - for Composite Result |
831 --| sample_id - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
832 --| - This will be used to navigate to the Result form. |
833 --| spec_match_type - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
834 --| It can have 3 values. |
835 --| - NULL If no sample is found, OR no results can be found for this lot, |
836 --| - 'U' for Unaccepted. If the latest accepted final result is not |
837 --| within the spec. test range. |
838 --| - 'A' for Accepted.All the test results for the customer spec are |
839 --| within the spec. test range |
840 --| event_spec_disp_id - OUT parameter ( will be SET BY THE API get_result_match_for_spec) |
841 --| - This will be used to navigate to the composite results form. |
842 --| |
843 --| Calling Program : - Order Management(Pick lots form) |
844 --| HISTORY |
845 --| Mahesh Chandak 1-sep-2002 Created. |
846 --| Anoop Baddam 11-MAR-2004 Modified the call to procedure getLatestSample. |
847 --+=====================================================================================================+
848 -- End of comments
849
850
851
852 PROCEDURE get_result_match_for_spec
853 ( p_spec_id IN NUMBER
854 , p_lots IN OUT NOCOPY result_lot_match_tbl
855 , x_return_status OUT NOCOPY VARCHAR2
856 , x_message_data OUT NOCOPY VARCHAR2 ) IS
857
858 l_position VARCHAR2(3);
859 --l_lot_no VARCHAR2(32); --INVCONV
860 --l_sublot_no VARCHAR2(32); --INVCONV
861 --l_whse_code VARCHAR2(4); --INVCONV
862 --l_location VARCHAR2(16); --INVCONV
863 --l_item_id NUMBER; --INVCONV
864 l_lot_number VARCHAR2(80); --INVCONV
865 l_subinventory VARCHAR2(10); --INVCONV
866 l_locator_id NUMBER; --INVCONV
867 l_inventory_item_id NUMBER; --INVCONV
868 l_revision NUMBER; --BUG# 4662469
869 l_old_event_spec_disp_id NUMBER;
870 l_cust_id NUMBER;
871
872 -- pick up only required test
873 --RLNAGARA Bug # 4916856
874 --Bug# 5223677. Pick all tests. Commented optional_ind condition.
875 CURSOR cr_get_req_spec_tests IS
876 SELECT gst.test_id
877 FROM GMD_SPEC_TESTS_B gst
878 WHERE gst.spec_id = p_spec_id;
879 -- AND gst.optional_ind is NULL ;
880
881 CURSOR cr_get_sample_for_lot IS
882 SELECT gs.lot_number,gs.cust_id,gs.creation_date,/*gs.location,*/gs.subinventory,
883 gr.sample_id sample_id,ges.event_spec_disp_id,
884 gr.test_id,
885 gr.result_value_num,
886 gr.result_value_char,'SAMPLE'
887 FROM GMD_SAMPLING_EVENTS gs ,
888 GMD_EVENT_SPEC_DISP ges,
889 GMD_RESULTS gr,
890 GMD_SPEC_RESULTS sp
891 WHERE
892 gs.inventory_item_id = l_inventory_item_id
893 AND (gs.revision = l_revision OR gs.revision IS NULL )
894 AND (gs.lot_number = l_lot_number OR gs.lot_number IS NULL)
895 AND (gs.subinventory = l_subinventory OR gs.subinventory IS NULL)
896 AND (gs.locator_id = l_locator_id OR gs.locator_id IS NULL )
897 AND gr.delete_mark = 0
898 AND (gs.cust_id = l_cust_id OR gs.cust_id IS NULL)
899 AND (gr.result_value_num IS NOT NULL or gr.result_value_char IS NOT NULL)
900 AND gs.sample_active_cnt = 1
901 and ges.disposition in ('4A','5AV','5RJ')
902 and ges.spec_used_for_lot_attrib_ind ='Y'
903 and gs.sampling_event_id = ges.sampling_event_id
907 SELECT gs.lot_number,gs.cust_id,gs.creation_date ,/*gs.location,*/gs.subinventory,
904 and ges.event_spec_disp_id = sp.event_spec_disp_id
905 and sp.result_id = gr.result_id
906 UNION
908 null sample_id,ges.event_spec_disp_id,
909 gr.test_id,
910 gr.mean result_value_num,
911 gr.mode_char result_value_char,'EVENT_SPEC_DISP'
912 FROM GMD_SAMPLING_EVENTS gs ,
913 GMD_EVENT_SPEC_DISP sd,
914 GMD_COMPOSITE_RESULTS gr,
915 GMD_COMPOSITE_SPEC_DISP ges
916 WHERE
917 gs.inventory_item_id = l_inventory_item_id
918 AND (gs.revision = l_revision OR gs.revision IS NULL)
919 AND (gs.lot_number = l_lot_number OR gs.lot_number IS NULL)
920 AND (gs.subinventory = l_subinventory OR gs.subinventory IS NULL)
921 AND (gs.locator_id = l_locator_id OR gs.locator_id IS NULL )
922 AND (gs.cust_id = l_cust_id OR gs.cust_id IS NULL)
923 AND gr.delete_mark = 0
924 AND (gr.mean IS NOT NULL or gr.mode_char IS NOT NULL)
925 AND gs.sample_active_cnt > 1
926 AND gs.sampling_event_id = sd.sampling_event_id
927 and sd.event_spec_disp_id = ges.event_spec_disp_id
928 and ges.composite_spec_disp_id = gr.composite_spec_disp_id
929 and ges.disposition in ('4A','5AV','5RJ')
930 and nvl(ges.latest_ind,'N') = 'Y'
931 ORDER BY 1 ,2,3 desc,4,5,6 ;
932 -- 2651353 changed the order by clause. sample date takes preference over sub lot no.
933 -- looks for a sample within a lot_no with latest sample date.
934
935 l_lot_counter BINARY_INTEGER;
936 l_spec_test_counter BINARY_INTEGER;
937 REQ_FIELDS_MISSING EXCEPTION;
938 INVALID_LOT EXCEPTION;
939 l_sample_rec cr_get_sample_for_lot%ROWTYPE;
940
941 TYPE spec_test_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
942
943 spec_test_list spec_test_tab ;
944
945 TYPE result_test_tab IS TABLE OF cr_get_sample_for_lot%ROWTYPE INDEX BY BINARY_INTEGER;
946
947 result_test_list result_test_tab ;
948 l_spec_tests_exist_in_sample BOOLEAN := FALSE;
949 l_result_in_spec BOOLEAN := TRUE;
950 l_in_spec VARCHAR2(1); -- returned by the results API
951 BEGIN
952 PrintLn('Begin Procedure get_result_match_for_spec');
953 PrintLn('p_spec_id = '||p_spec_id);
954 x_return_status := FND_API.G_RET_STS_SUCCESS;
955 --FND_MSG_PUB.initialize;
956
957 l_position := '010';
958 PrintLn('l_position = '||l_position);
959 IF p_spec_id IS NULL THEN
960 PrintLn('RETURN End Procedure get_result_match_for_spec');
961 RETURN;
962 END IF;
963
964 FOR spec_test_row IN cr_get_req_spec_tests LOOP
965 spec_test_list(spec_test_row.test_id) := spec_test_row.test_id;
966 END LOOP;
967
968 l_position := '020';
969 PrintLn('l_position = '||l_position);
970 l_lot_counter := p_lots.FIRST;
971 WHILE l_lot_counter IS NOT NULL
972 LOOP
973 --BUG#3482676 Anoop.
974 --Modified the call to procedure getLatestSample by passing lot no.
975 PrintLn('In WHILE l_lot_counter LOOP');
976 PrintLn('Calling Procedure getLatestSample');
977 getLatestSample(p_spec_id,
978 p_lots(l_lot_counter).lot_number,
979 p_lots(l_lot_counter).event_spec_disp_id,
980 p_lots(l_lot_counter).sample_id);
981 PrintLn('The value of event_spec_disp_id after latest sample is '||p_lots(l_lot_counter).event_spec_disp_id);
982 PrintLn('The value of sample_id after latest sample is '||p_lots(l_lot_counter).sample_id);
983 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
984 log_msg('The value of event_spec_disp_id after latest sample is '||p_lots(l_lot_counter).event_spec_disp_id);
985 log_msg('The value of sample_id after latest sample is '||p_lots(l_lot_counter).sample_id);
986 END IF;
987
988 IF (p_lots(l_lot_counter).event_spec_disp_id IS NULL AND
989 p_lots(l_lot_counter).sample_id IS NULL) THEN
990 /** Mahesh, Can we remove this check for OM too */
991 PrintLn('p_lots(l_lot_counter).called_from = '||p_lots(l_lot_counter).called_from);
992 IF (p_lots(l_lot_counter).called_from = 'OM') THEN
993 IF p_lots(l_lot_counter).inventory_item_id IS NULL OR p_lots(l_lot_counter).lot_number IS NULL OR
994 p_lots(l_lot_counter).subinventory IS NULL THEN
995 RAISE REQ_FIELDS_MISSING;
996 END IF;
997 END IF;
998 PrintLn('p_lots(l_lot_counter).lot_number = '||p_lots(l_lot_counter).lot_number);
999
1000
1001 l_lot_number := p_lots(l_lot_counter).lot_number;
1002
1003 l_inventory_item_id := p_lots(l_lot_counter).inventory_item_id;
1004 l_revision := p_lots(l_lot_counter).revision; --bug# 4662469
1005 l_subinventory := p_lots(l_lot_counter).subinventory;
1006 l_locator_id := p_lots(l_lot_counter).locator_id;
1007
1008 l_old_event_spec_disp_id := null;
1009 l_spec_tests_exist_in_sample := FALSE;
1010 l_result_in_spec := TRUE;
1011 result_test_list.DELETE;
1012
1013 l_position := '030';
1014
1015 PrintLn('l_position = '||l_position);
1016 PrintLn('l_inventory_item_id = '||l_inventory_item_id);
1017 PrintLn('l_revision = '||l_revision);
1018 PrintLn('l_lot_number = '||l_lot_number);
1022
1019 PrintLn('l_subinventory = '||l_subinventory);
1020 PrintLn('l_locator_id = '||l_locator_id);
1021 PrintLn('l_cust_id = '||l_cust_id);
1023 OPEN cr_get_sample_for_lot ;
1024 LOOP
1025 PrintLn('In cr_get_sample_for_lot LOOP');
1026 FETCH cr_get_sample_for_lot INTO l_sample_rec;
1027 IF cr_get_sample_for_lot%NOTFOUND THEN
1028 PrintLn('cr_get_sample_for_lot%NOTFOUND EXIT cr_get_sample_for_lot LOOP');
1029 EXIT ;
1030 END IF;
1031
1032 PrintLn('l_sample_rec.event_spec_disp_id = '||l_sample_rec.event_spec_disp_id);
1033 PrintLn('l_old_event_spec_disp_id = '||l_old_event_spec_disp_id);
1034 -- sample changed.check for tests against each sample.
1035 IF l_old_event_spec_disp_id IS NULL OR l_sample_rec.event_spec_disp_id <> l_old_event_spec_disp_id THEN
1036 l_old_event_spec_disp_id := l_sample_rec.event_spec_disp_id ;
1037
1038 PrintLn('1 result_test_list.COUNT = '||result_test_list.COUNT);
1039 PrintLn('1 spec_test_list.COUNT = '||spec_test_list.COUNT);
1040 IF result_test_list.COUNT = spec_test_list.COUNT THEN
1041 l_spec_tests_exist_in_sample := TRUE;
1042 PrintLn('EXIT cr_get_sample_for_lot LOOP');
1043 EXIT; -- once a matching sample with all the reqd spec test is found,then do not continue further.
1044 END IF;
1045 result_test_list.DELETE;
1046
1047 -- If the current test is not in the spec, ignore it.
1048 -- If the test is already in the result test list, skip this row.
1049 END IF;
1050
1051 PrintLn('2 result_test_list.COUNT = '||result_test_list.COUNT);
1052 PrintLn('2 spec_test_list.COUNT = '||spec_test_list.COUNT);
1053 PrintLn('l_sample_rec.test_id = '||l_sample_rec.test_id);
1054
1055 IF spec_test_list.EXISTS(l_sample_rec.test_id) AND
1056 NOT (result_test_list.EXISTS(l_sample_rec.test_id)) THEN
1057 result_test_list(l_sample_rec.test_id) := l_sample_rec;
1058 END IF;
1059
1060 END LOOP;
1061 CLOSE cr_get_sample_for_lot;
1062 -- do check again since the last sample won't go through the first test.
1063 -- Bug 3854427 result_test_list.COUNT and spec_test_list.COUNT need not necessarily be the same.
1064 --IF result_test_list.COUNT = spec_test_list.COUNT THEN
1065 IF result_test_list.COUNT <> 0 THEN
1066 l_spec_tests_exist_in_sample := TRUE;
1067 END IF;
1068
1069 l_position := '040';
1070 PrintLn('l_position = '||l_position);
1071 IF l_spec_tests_exist_in_sample THEN
1072 PrintLn('l_spec_tests_exist_in_sample TRUE');
1073 -- check test results against the selected sample are in range as per the given specification
1074 l_spec_test_counter := spec_test_list.FIRST;
1075 IF (p_lots(l_lot_counter).called_from = 'COA') THEN
1076 p_lots(l_lot_counter).sample_id := result_test_list(result_test_list.FIRST).sample_id;
1077 IF (result_test_list(result_test_list.FIRST).sample_id IS NULL) THEN
1078 p_lots(l_lot_counter).event_spec_disp_id := result_test_list(result_test_list.FIRST).event_spec_disp_id;
1079 END IF;
1080 PrintLn('RETURN End Procedure get_result_match_for_spec');
1081 RETURN;
1082 END IF;
1083
1084
1085 WHILE l_spec_test_counter IS NOT NULL
1086 LOOP
1087 PrintLn('In WHILE l_spec_test_counter LOOP');
1088 l_in_spec := GMD_RESULTS_GRP.rslt_is_in_spec(
1089 p_spec_id => p_spec_id
1090 , p_test_id => spec_test_list(l_spec_test_counter)
1091 , p_rslt_value_num => result_test_list(l_spec_test_counter).result_value_num
1092 , p_rslt_value_char => result_test_list(l_spec_test_counter).result_value_char ) ;
1093
1094 PrintLn('l_in_spec = '||l_in_spec);
1095 IF l_in_spec IS NULL THEN
1096 l_result_in_spec := FALSE;
1097 EXIT;
1098 END IF;
1099 l_spec_test_counter := spec_test_list.NEXT(l_spec_test_counter);
1100 END LOOP ;
1101 l_position := '050';
1102 PrintLn('l_position = '||l_position);
1103 IF l_result_in_spec THEN
1104 PrintLn('l_result_in_spec TRUE');
1105 p_lots(l_lot_counter).sample_id := result_test_list(result_test_list.FIRST).sample_id;
1106 IF (result_test_list(result_test_list.FIRST).sample_id IS NULL) THEN
1107 p_lots(l_lot_counter).event_spec_disp_id := result_test_list(result_test_list.FIRST).event_spec_disp_id;
1108 END IF;
1109 p_lots(l_lot_counter).spec_match_type := 'A';
1110 p_lots(l_lot_counter).result_type := 'I' ;
1111 ELSE
1112 PrintLn('l_result_in_spec FALSE');
1113 p_lots(l_lot_counter).sample_id := result_test_list(result_test_list.FIRST).sample_id;
1114 IF (result_test_list(result_test_list.FIRST).sample_id IS NULL) THEN
1115 p_lots(l_lot_counter).event_spec_disp_id := result_test_list(result_test_list.FIRST).event_spec_disp_id;
1116 END IF;
1117 p_lots(l_lot_counter).spec_match_type := 'U';
1118 p_lots(l_lot_counter).result_type := 'I' ;
1122 END IF; -- IF l_spec_tests_exist_in_sample
1119 END IF;
1120 ELSE
1121 p_lots(l_lot_counter).spec_match_type := null;
1123 END IF; -- IF (p_lots(l_lot_counter).event_spec_disp_id IS NULL check for getLatestSample
1124 l_lot_counter := p_lots.NEXT(l_lot_counter);
1125 END LOOP; -- WHILE l_lot_counter IS NOT NULL
1126 PrintLn('End Procedure get_result_match_for_spec');
1127
1128 EXCEPTION
1129 WHEN REQ_FIELDS_MISSING THEN
1130 PrintLn('GMD_REQ_FIELD_MIS , PACKAGE , GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC');
1131 gmd_api_pub.log_message('GMD_REQ_FIELD_MIS','PACKAGE','GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC');
1132 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1133 x_return_status := FND_API.G_RET_STS_ERROR ;
1134 WHEN INVALID_LOT THEN
1135 PrintLn('GMD_INVALID_LOT , LOT_NUMBER = '||p_lots(l_lot_counter).lot_number);
1136 gmd_api_pub.log_message('GMD_INVALID_LOT','LOT',to_char(p_lots(l_lot_counter).lot_number));
1137 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1138 x_return_status := FND_API.G_RET_STS_ERROR ;
1139 WHEN OTHERS THEN
1140 PrintLn('GMD_API_ERROR , PACKAGE , GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC , ERROR '||SUBSTR(SQLERRM,1,100)||' l_position = '||l_position);
1141 gmd_api_pub.log_message('GMD_API_ERROR','PACKAGE','GMD_SPEC_MATCH_GRP.GET_RESULT_MATCH_FOR_SPEC','ERROR', SUBSTR(SQLERRM,1,100),'POSITION',l_position);
1142 x_message_data := FND_MSG_PUB.GET(FND_MSG_PUB.G_LAST,FND_API.G_FALSE);
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1144 END get_result_match_for_spec ;
1145
1146
1147
1148 PROCEDURE add_sample_to_log(p_sample_id IN NUMBER,
1149 p_event_spec_disp_id IN NUMBER) IS
1150
1151 CURSOR c_sample IS
1152 select a.organization_id, b.organization_code, a.sample_no
1153 from gmd_samples a, mtl_parameters b
1154 where sample_id = p_sample_id
1155 and a.organization_id = b.organization_id;
1156
1157
1158 CURSOR c_samples IS
1159 select a.organization_id, c.organization_code, a.sample_no
1160 from gmd_samples a,
1161 gmd_event_spec_disp b,
1162 mtl_parameters c
1163 where a.sampling_event_id = b.sampling_event_id
1164 and b.event_spec_disp_id = p_event_spec_disp_id
1165 and a.organization_id = c.organization_id;
1166
1167 BEGIN
1168 PrintLn('Begin Procedure add_sample_to_log');
1169 PrintLn('p_sample_id = '||p_sample_id);
1170 PrintLn('p_event_spec_disp_id = '||p_event_spec_disp_id);
1171 IF (p_sample_id IS NOT NULL) THEN
1172 FOR l_sample_rec in c_sample LOOP
1173 PrintLn('In c_sample1 LOOP');
1174 PrintLn('GMD_QC_SAMPLE_NO');
1175 FND_MESSAGE.SET_NAME('GMD','GMD_QC_SAMPLE_NO');
1176 FND_MESSAGE.SET_TOKEN('ORGN_CODE' ,l_sample_rec.organization_code);
1177 FND_MESSAGE.SET_TOKEN('SAMPLE_NO',l_sample_rec.sample_no);
1178 FND_MSG_PUB.Add;
1179 END LOOP;
1180 ELSE
1181 FOR l_sample_rec in c_samples LOOP
1182 PrintLn('In c_samples2 LOOP');
1183 PrintLn('GMD_QC_SAMPLE_NO');
1184 FND_MESSAGE.SET_NAME('GMD','GMD_QC_SAMPLE_NO');
1185 FND_MESSAGE.SET_TOKEN('ORGN_CODE' ,l_sample_rec.organization_code);
1186 FND_MESSAGE.SET_TOKEN('SAMPLE_NO',l_sample_rec.sample_no);
1187 FND_MSG_PUB.Add;
1188 END LOOP;
1189 END IF;
1190 PrintLn('End Procedure add_sample_to_log');
1191 END add_sample_to_log;
1192
1193 PROCEDURE call_spec_match(p_hdr_rec IN OUT NOCOPY t_coa_hdr_rec,
1194 x_return_status OUT NOCOPY VARCHAR2) IS
1195
1196 --RLNAGARA Bug # 4916856 Replaced gmd_all_spec_vrs with gmd_com_spec_vrs_vl
1197 CURSOR c_coa_type(p_spec_vr_id IN NUMBER) IS
1198 select nvl(coa_type,'A') coa_type
1199 from gmd_com_spec_vrs_vl
1200 where spec_vr_id = p_spec_vr_id;
1201
1202 CURSOR cur_spec_hdr_text(l_spec_id IN NUMBER) IS -- Bug # 4260445 Declared Cursor to get the value of text_code
1203 SELECT text_code
1204 FROM gmd_specifications
1205 WHERE spec_id = l_spec_id;
1206
1207 p_customer_spec_rec GMD_SPEC_MATCH_GRP.customer_spec_rec_type;
1208 p_inventory_spec_rec GMD_SPEC_MATCH_GRP.inventory_spec_rec_type;
1209 x_return_flag BOOLEAN;
1210 x_spec_id NUMBER;
1211 x_spec_vr_id NUMBER;
1212 l_return_status VARCHAR2(1000);
1213 x_message_data VARCHAR2(1000);
1214 x_sample_id NUMBER;
1215 x_event_spec_disp_id NUMBER;
1216 l_lot_tbl result_lot_match_tbl;
1217 l_coa_type VARCHAR2(1);
1218 l_lot_ctl NUMBER; -- Bug# 5010385
1219
1220 BEGIN
1221 PrintLn('Begin Procedure call_spec_match');
1222 /*-----------------------------------------------------------------
1223 Call customer spec match and inventory spec match
1224 -------------------------------------------------------------------*/
1225 p_customer_spec_rec.inventory_item_id := p_hdr_rec.inventory_item_id; --INVCONV
1226 p_customer_spec_rec.revision := p_hdr_rec.revision; --Bug# 4662469
1227 p_customer_spec_rec.cust_id := p_hdr_rec.cust_id;
1228 p_customer_spec_rec.date_effective := SYSDATE;
1229 p_customer_spec_rec.subinventory := p_hdr_rec.subinventory; --INVCONV
1230 p_customer_spec_rec.org_id := p_hdr_rec.org_id;
1231 p_customer_spec_rec.order_id := p_hdr_rec.order_id;
1232 p_customer_spec_rec.organization_id := nvl(p_hdr_rec.organization_id,0); --INVCONV
1233 p_customer_spec_rec.look_in_other_orgn := 'Y';
1234 p_customer_spec_rec.ship_to_site_id := p_hdr_rec.ship_to_site_id; --Bug 4166529 added.
1235
1236 PrintLn('p_customer_spec_rec.inventory_item_id = '||p_customer_spec_rec.inventory_item_id); --INVCONV
1237 PrintLn('p_customer_spec_rec.revision = '||p_customer_spec_rec.revision); --Bug# 4662469
1238 PrintLn('p_customer_spec_rec.cust_id = '||p_customer_spec_rec.cust_id);
1239 PrintLn('p_customer_spec_rec.date_effective = '||to_char(p_customer_spec_rec.date_effective));
1240 PrintLn('p_customer_spec_rec.subinventory = '||p_customer_spec_rec.subinventory); --INVCONV
1241 PrintLn('p_customer_spec_rec.org_id = '||p_customer_spec_rec.org_id);
1242 PrintLn('p_customer_spec_rec.order_id = '||p_customer_spec_rec.order_id);
1243 PrintLn('p_customer_spec_rec.organization_id = '||p_customer_spec_rec.organization_id); --INVCONV
1244 PrintLn('p_customer_spec_rec.look_in_other_orgn = '||p_customer_spec_rec.look_in_other_orgn);
1245 PrintLn('p_customer_spec_rec.ship_to_site_id = '||p_customer_spec_rec.ship_to_site_id); --Bug 4166529 added.
1246
1247 PrintLn('Calling GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC');
1248 x_return_flag := FALSE;
1249 x_return_flag := GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC(p_customer_spec_rec,
1250 x_spec_id,
1251 x_spec_vr_id,
1252 l_return_status,
1253 x_message_data);
1254 IF x_return_flag THEN -- cust spec found
1255 PrintLn('CUSTOMER SPEC FOUND');
1256 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC , Spec_id '||x_spec_id);
1257 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC , x_spec_vr_id '||x_spec_vr_id);
1258 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC , l_return_status '||l_return_status);
1259 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC , x_message_data '||x_message_data);
1260 END IF;
1261
1262 IF (x_return_flag = FALSE) THEN
1263 PrintLn('CUSTOMER SPEC NOT FOUND');
1264 p_inventory_spec_rec.inventory_item_id := p_hdr_rec.inventory_item_id; --INVCONV
1265 p_inventory_spec_rec.revision := p_hdr_rec.revision; --Bug# 4662469
1266 p_inventory_spec_rec.lot_number := p_hdr_rec.lot_number; --INVCONV
1267 p_inventory_spec_rec.organization_id := p_hdr_rec.organization_id; --INVCONV
1268 p_inventory_spec_rec.date_effective := SYSDATE;
1269 p_inventory_spec_rec.subinventory := p_hdr_rec.subinventory; --INVCONV
1270
1271 PrintLn('p_inventory_spec_rec.inventory_item_id = '||p_inventory_spec_rec.inventory_item_id); --INVCONV
1272 PrintLn('p_inventory_spec_rec.revision = '||p_inventory_spec_rec.revision); --INVCONV
1273 PrintLn('p_inventory_spec_rec.organization_id = '||p_inventory_spec_rec.organization_id); --INVCONV
1274 PrintLn('p_inventory_spec_rec.parent_lot_number = '||p_inventory_spec_rec.parent_lot_number); --INVCONV
1275 PrintLn('p_inventory_spec_rec.lot_number = '||p_inventory_spec_rec.lot_number); --INVCONV
1276 PrintLn('p_inventory_spec_rec.date_effective = '||to_char(p_inventory_spec_rec.date_effective));
1277 PrintLn('p_inventory_spec_rec.subinventory = '||p_inventory_spec_rec.subinventory); --INVCONV
1278 PrintLn('p_inventory_spec_rec.locator_id = '||p_inventory_spec_rec.locator_id); --INVCONV
1279 PrintLn('p_inventory_spec_rec.grade_code = '||p_inventory_spec_rec.grade_code); --INVCONV
1280 PrintLn('p_inventory_spec_rec.exact_match = '||p_inventory_spec_rec.exact_match); --INVCONV
1281 PrintLn('Calling GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC');
1282
1283 x_return_flag := GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC(p_inventory_spec_rec ,
1284 x_spec_id ,
1285 x_spec_vr_id,
1286 l_return_status,
1287 x_message_data);
1288 IF x_return_flag THEN
1289 PrintLn('INVENTORY SPEC FOUND');
1290 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , Spec_id '||x_spec_id);
1291 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , x_spec_vr_id '||x_spec_vr_id);
1295
1292 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , l_return_status '||l_return_status);
1293 PrintLn('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , x_message_data '||x_message_data);
1294 END IF;
1296 IF FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW >= G_Debug_Level THEN
1297 log_msg('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , Spec_id '||x_spec_id);
1298 log_msg('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , x_message_data '||x_message_data);
1299 log_msg('call_spec_match, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , l_return_status '||l_return_status);
1300 log_msg('The parameters for inventory=>inventory_item_id, lot_number, subinventory '||
1301 p_inventory_spec_rec.inventory_item_id||' '||
1302 p_inventory_spec_rec.lot_number||' '||p_inventory_spec_rec.subinventory);
1303 END IF;
1304
1305 END IF;
1306 IF (x_return_flag = FALSE) THEN
1307 PrintLn('GMD_QC_NO_SPEC_FOUND');
1308 FND_MESSAGE.SET_NAME('GMD','GMD_QC_NO_SPEC_FOUND');
1309 FND_MSG_PUB.Add;
1310 PrintLn('RETURN End Procedure call_spec_match');
1311 RETURN;
1312 ELSE
1313 coa_id := coa_id + 1;
1314 p_hdr_rec.gmd_coa_id := coa_id;
1315
1316 PrintLn('p_hdr_rec.gmd_coa_id = '||p_hdr_rec.gmd_coa_id);
1317 PrintLn('p_hdr_rec.item_number = '||p_hdr_rec.item_number);
1318 PrintLn('p_hdr_rec.lot_number = '||p_hdr_rec.lot_number);
1319
1320 IF (p_hdr_rec.item_number IS NULL or p_hdr_rec.lot_number IS NULL) THEN
1321 PrintLn('Calling Procedure get_foreign_keys');
1322 get_foreign_keys(p_hdr_rec,x_return_status);
1323 END IF;
1324
1325 PrintLn('Calling Procedure put_spec_in_log');
1326 -- to put the information in log.
1327 put_spec_in_log(x_spec_id,x_return_status);
1328
1329 END IF;
1330
1331 -- check if coa can be printed for the spec_vr_id
1332 OPEN c_coa_type(x_spec_vr_id);
1333 FETCH c_coa_type INTO l_coa_type;
1334 CLOSE c_coa_type;
1335
1336 OPEN cur_spec_hdr_text(x_spec_id); -- Bug # 4260445
1337 FETCH cur_spec_hdr_text INTO p_hdr_rec.spec_hdr_text_code;
1338 CLOSE cur_spec_hdr_text;
1339
1340
1341 PrintLn('p_hdr_rec.lot_number = '||p_hdr_rec.lot_number);
1342 PrintLn('l_coa_type = '||l_coa_type);
1343
1344 -- Bug# 5010385
1345 SELECT lot_control_code INTO l_lot_ctl
1346 FROM mtl_system_items
1347 WHERE organization_id = p_hdr_rec.organization_id
1348 AND inventory_item_id = p_hdr_rec.inventory_item_id;
1349
1350 PrintLn('Item lot_ctl = '||l_lot_ctl);
1351
1352 -- Bug 5010385 Added the above select and modified the following IF condition.
1353 -- IF (p_hdr_rec.lot_number IS NULL OR l_coa_type='C') THEN
1354 IF (l_coa_type='C') OR (l_lot_ctl = 2 AND p_hdr_rec.lot_number IS NULL) THEN
1355 PrintLn('GMD_QC_COA_NO_LOT');
1356 FND_MESSAGE.SET_NAME('GMD','GMD_QC_COA_NO_LOT');
1357 FND_MSG_PUB.Add;
1358 p_hdr_rec.report_title := 'COC';
1359
1360 PrintLn('p_hdr_rec.report_title = '||p_hdr_rec.report_title);
1361 PrintLn('Calling Procedure insert_hdr_rec');
1362 insert_hdr_rec(p_hdr_rec,x_return_status);
1366 --call_mahesh_api for matchih spec
1363 PrintLn('Calling Procedure populate_spec');
1364 populate_spec(x_spec_id,p_hdr_rec.cust_id,'COC',x_return_status);
1365 ELSE
1367 l_lot_tbl(1).inventory_item_id := p_hdr_rec.inventory_item_id; --INVCONV
1368 l_lot_tbl(1).revision := p_hdr_rec.revision; --Bug# 4662469
1369 l_lot_tbl(1).lot_number := p_hdr_rec.lot_number; --INVCONV
1370 l_lot_tbl(1).subinventory := p_hdr_rec.subinventory; --INVCONV
1371 l_lot_tbl(1).cust_id := p_hdr_rec.cust_id;
1372 l_lot_tbl(1).called_from := 'COA';
1373
1374 PrintLn('Calling Procedure get_result_match_for_spec');
1375 PrintLn('l_lot_tbl(1).inventory_item_id = '||l_lot_tbl(1).inventory_item_id); --INVCONV
1376 PrintLn('l_lot_tbl(1).revision = '||l_lot_tbl(1).revision); --Bug# 4662469
1377 PrintLn('l_lot_tbl(1).lot_number = '||l_lot_tbl(1).lot_number); --INVCONV
1378 PrintLn('l_lot_tbl(1).subinventory = '||l_lot_tbl(1).subinventory); --INVCONV
1379 PrintLn('l_lot_tbl(1).cust_id = '||l_lot_tbl(1).cust_id);
1380 PrintLn('l_lot_tbl(1).called_from = '||l_lot_tbl(1).called_from);
1381
1382 get_result_match_for_spec( x_spec_id
1383 , l_lot_tbl
1384 , x_return_status
1385 , x_message_data);
1386
1387 IF (x_return_status <> 'S') THEN
1388 PrintLn('Error occurred in execution of gmd_coa_data.get_result_match_for_spec...');
1389 log_msg('Error occurred in execution of gmd_coa_data.get_result_match_for_spec...');
1390 END IF;
1391
1392 PrintLn('l_lot_tbl(1).sample_id = '||l_lot_tbl(1).sample_id);
1393 PrintLn('l_lot_tbl(1).event_spec_disp_id = '||l_lot_tbl(1).event_spec_disp_id);
1394 PrintLn('x_spec_id' || x_spec_id);
1395
1396
1397 PrintLn('p_hdr_rec.spec_hdr_text_code ' || p_hdr_rec.spec_hdr_text_code ); --Bug # 4260445
1398
1399 IF (l_lot_tbl(1).sample_id IS NOT NULL OR
1400 l_lot_tbl(1).event_spec_disp_id IS NOT NULL) THEN
1401 PrintLn('Calling Procedure add_sample_to_log');
1402 add_sample_to_log(l_lot_tbl(1).sample_id, l_lot_tbl(1).event_spec_disp_id );
1403
1404 p_hdr_rec.report_title := 'COA';
1405 PrintLn('p_hdr_rec.report_title = '||p_hdr_rec.report_title);
1406 PrintLn('Calling Procedure insert_hdr_rec');
1407 insert_hdr_rec(p_hdr_rec,x_return_status);
1408 PrintLn('Calling Procedure populate_spec');
1409 populate_spec(x_spec_id,
1410 p_hdr_rec.cust_id,
1411 'COA',
1412 x_return_status,
1413 l_lot_tbl(1).event_spec_disp_id,
1414 l_lot_tbl(1).sample_id);
1415 ELSE
1416 PrintLn('GMD_QC_COA_NO_ACCEPTED_SAMPLE');
1417 FND_MESSAGE.SET_NAME('GMD','GMD_QC_COA_NO_ACCEPTED_SAMPLE');
1418 FND_MSG_PUB.Add;
1419
1420 p_hdr_rec.report_title := 'COC';
1421
1422 PrintLn('p_hdr_rec.report_title = '||p_hdr_rec.report_title);
1423 PrintLn('Calling Procedure insert_hdr_rec');
1424 insert_hdr_rec(p_hdr_rec,x_return_status);
1425 PrintLn('Calling Procedure populate_spec');
1426 populate_spec(x_spec_id,p_hdr_rec.cust_id,'COC',x_return_status);
1427 END IF;
1428 END IF;
1429 PrintLn('End Procedure call_spec_match');
1430 EXCEPTION
1431 WHEN OTHERS THEN
1432 PrintLn('GMD_COA_DATA.CALL_SPEC_MATCH '|| SUBSTR(SQLERRM,1,100));
1433 log_msg('GMD_COA_DATA.CALL_SPEC_MATCH '|| SUBSTR(SQLERRM,1,100));
1434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1435 END call_spec_match;
1436
1437 PROCEDURE get_order_params ( p_param_rec t_coa_parameters,
1438 x_return_status OUT NOCOPY VARCHAR2) IS
1439 --get info call spec match if row returned by spec match insert into header
1440 --RLNAGARA Bug 4916856
1441 CURSOR c_order_delivery(p_delivery_id NUMBER,
1442 p_order_id NUMBER,
1443 --p_item_id NUMBER, --INVCONV
1444 p_inventory_item_id NUMBER, --INVCONV
1445 p_org_id NUMBER,
1446 p_cust_id NUMBER) IS
1447 select l.header_id order_id,
1448 l.line_id line_id,
1449 wdd.delivery_detail_id,
1450 null organization_code, --INVCONV
1451 l.org_id,
1452 h.order_number order_no,
1453 h.cust_po_number custpo_no,
1454 nvl(l.actual_shipment_date,l.schedule_ship_date) actual_shipdate,
1455 l.ship_to_org_id ,
1456 wnd.delivery_id bol_id,
1457 wnd.name bol_no,
1458 l.inventory_item_id inventory_item_id, --INVCONV
1459 msi.concatenated_segments item_number, --INVCONV
1460 msi.description item_description, --INVCONV
1461 l.item_revision, --Bug# 4662469
1462 decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
1466 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity, l.ordered_quantity ) order_qty1,
1463 l.ship_to_org_id ship_to_site_id, --Bug 4166529 added.
1464 ship_from_org.organization_code from_whse,
1465 h.sold_to_org_id cust_id,
1467 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity2,l.ordered_quantity2) order_qty2,
1468 l.order_quantity_uom order_uom1, --INVCONV
1469 l.ordered_quantity_UOM2 order_uom2, --INVCONV
1470 wdd.shipped_quantity ship_qty1,
1471 wdd.shipped_quantity2 ship_qty2,
1472 l.shipping_quantity_uom ship_qty_uom1, --INVCONV -- Bug # 3710191 Added these two lines.
1473 l.shipping_quantity_uom2 ship_qty_uom2, --INVCONV
1474 C.cust_account_id shipcust_id,
1475 C.account_number cust_no,
1476 pr.party_name cust_name ,
1477 0 alloc_qty
1478 FROM
1479 oe_order_headers_all h,
1480 oe_order_lines_all l,
1481 wsh_delivery_details wdd,
1482 wsh_new_deliveries wnd,
1483 wsh_delivery_assignments wda,
1484 mtl_parameters ship_from_org,
1485 mtl_system_items_b_kfv msi, --INVCONV
1486 hz_cust_accounts c,
1487 hz_cust_site_uses_all s,
1488 hz_cust_acct_sites_all a,
1489 hz_parties pr
1490 where h.header_id = l.header_id
1491 and l.header_id = wdd.source_header_id
1492 and l.line_id = wdd.source_line_id
1493 and wnd.delivery_id = wda.delivery_id
1494 and wda.delivery_detail_id = wdd.delivery_detail_id
1495 and (p_order_id IS NULL OR h.header_id = p_order_id)
1496 and (p_delivery_id IS NULL OR wnd.delivery_id = p_delivery_id)
1497 and (p_org_id IS NULL OR a.org_id = p_org_id)
1498 and (p_cust_id IS NULL OR l.sold_to_org_id = p_cust_id)
1499 and (p_inventory_item_id IS NULL OR msi.inventory_item_id = p_inventory_item_id) --INVCONV
1500 and wdd.source_code ='OE'
1501 and l.ship_from_org_id = ship_from_org.organization_id(+)
1502 and ship_from_org.process_enabled_flag(+) ='Y'
1503 and ((l.ship_from_org_id IS NOT NULL AND msi.organization_id = l.ship_from_org_id ) OR (l.ship_from_org_id IS NULL AND msi.organization_id = h.ship_from_org_id ))
1504 and msi.inventory_item_id = l.inventory_item_id
1505 and l.ship_to_org_id = s.site_use_id(+)
1506 and s.site_use_code(+) = 'SHIP_TO'
1507 and s.org_id = a.org_id(+)
1508 and s.cust_acct_site_id = a.cust_acct_site_id(+)
1509 and a.cust_account_id = c.cust_account_id(+)
1510 and c.party_id = pr.party_id(+)
1511 order by l.header_id;
1512
1513 --RLNAGARA Bug 4916856
1514 CURSOR c_order ( p_order_id NUMBER,
1515 --p_item_id NUMBER, --INVCONV
1516 p_inventory_item_id NUMBER, --INVCONV
1517 p_org_id NUMBER,
1518 p_cust_id NUMBER) IS
1519 select l.header_id order_id,
1520 l.line_id line_id,
1521 l.org_id,
1522 h.order_number order_no,
1523 h.cust_po_number custpo_no,
1524 nvl(l.actual_shipment_date,l.schedule_ship_date) actual_shipdate,
1525 l.ship_to_org_id ,
1526 l.inventory_item_id inventory_item_id, --INVCONV
1527 msi.concatenated_segments item_number, --INVCONV
1528 msi.description item_description, --INVCONV
1529 l.item_revision, --Bug# 4662469
1530 decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
1531 l.ship_to_org_id ship_to_site_id, --Bug 4166529 added.
1532 ship_from_org.organization_code from_whse,
1533 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity, l.ordered_quantity ) order_qty1,
1534 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity2,l.ordered_quantity2) order_qty2,
1535 l.order_quantity_uom order_uom1, --INVCONV
1536 l.ordered_quantity_UOM2 order_uom2, --INVCONV
1537 C.cust_account_id shipcust_id,
1538 C.account_number cust_no,
1539 pr.party_name cust_name ,
1540 0 alloc_qty
1541
1542 FROM
1543 oe_order_headers_all h,
1544 oe_order_lines_all l,
1545 mtl_parameters ship_from_org,
1546 mtl_system_items_b_kfv msi, --INVCONV
1547 hz_cust_accounts c,
1548 hz_cust_site_uses_all s,
1549 hz_cust_acct_sites_all a,
1550 hz_parties pr
1551 where h.header_id = l.header_id
1552 and (p_order_id IS NULL OR h.header_id = p_order_id)
1553 and (p_org_id IS NULL OR a.org_id = p_org_id)
1554 and (p_cust_id IS NULL OR l.sold_to_org_id = p_cust_id)
1555 and (p_inventory_item_id IS NULL OR msi.inventory_item_id = p_inventory_item_id)
1556 and l.ship_from_org_id = ship_from_org.organization_id(+)
1557 and ship_from_org.process_enabled_flag(+) = 'Y'
1558 and ((l.ship_from_org_id IS NOT NULL AND msi.organization_id = l.ship_from_org_id ) OR (l.ship_from_org_id IS NULL AND msi.organization_id = h.ship_from_org_id ))
1559 and msi.inventory_item_id = l.inventory_item_id
1563 and s.cust_acct_site_id = a.cust_acct_site_id(+)
1560 and l.ship_to_org_id = s.site_use_id(+)
1561 and s.site_use_code(+) = 'SHIP_TO'
1562 and s.org_id = a.org_id(+)
1564 and a.cust_account_id = c.cust_account_id(+)
1565 and c.party_id = pr.party_id(+)
1566 order by l.header_id;
1567
1568 --INVCONV
1569 /*CURSOR get_whse_info (c_ship_from_org_id oe_order_lines.ship_from_org_id%TYPE) IS
1570 SELECT s.orgn_code,
1571 w.whse_code
1572 FROM mtl_parameters p,
1573 ic_whse_mst w,
1574 sy_orgn_mst s
1575 WHERE w.mtl_organization_id = c_ship_from_org_id
1576 AND p.ORGANIZATION_ID = c_ship_from_org_id
1577 AND s.orgn_code = w.orgn_code
1578 AND s.orgn_code = p.process_orgn_code
1579 AND p.process_enabled_flag ='Y'
1580 AND s.delete_mark = 0
1581 AND w.delete_mark = 0
1582 ;*/
1583
1584 --INVCONV
1585 /*CURSOR get_lot_tran (c_line_id ic_tran_pnd.line_id%TYPE) IS
1586 SELECT itp.lot_id, itp.whse_code, itp.location
1587 FROM ic_tran_pnd itp
1588 WHERE itp.doc_type = 'OMSO'
1589 AND itp.completed_ind <> -1
1590 AND itp.line_detail_id = c_line_id
1591 and itp.delete_mark = 0;*/
1592
1593 --INVCONV
1594 --RLNAGARA Bug # 4916856
1595 -- Bug# 5629675 Added distinct in the first select and added union.
1596 CURSOR get_lot_tran(p_order_line_id oe_order_lines_all.line_id%TYPE) IS
1597 SELECT distinct MTLT.LOT_NUMBER
1598 FROM MTL_TRANSACTION_LOTS_TEMP MTLT,
1599 MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
1600 MTL_TXN_REQUEST_LINES_V MTRL
1601 WHERE MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
1602 AND MMTT.MOVE_ORDER_LINE_ID = MTRL.LINE_ID
1603 AND MTRL.TXN_SOURCE_LINE_ID = p_order_line_id
1604 AND MTRL.TRANSACTION_TYPE_ID = 52
1605 AND MTRL.TRANSACTION_ACTION_ID = 28
1606 AND MTRL.TRANSACTION_SOURCE_TYPE_ID = 2
1607 UNION
1608 SELECT distinct MTLN.LOT_NUMBER
1609 FROM MTL_TRANSACTION_LOT_NUMBERS MTLN,
1610 MTL_MATERIAL_TRANSACTIONS MMT
1611 WHERE MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID
1612 AND MMT.TRX_SOURCE_LINE_ID = p_order_line_id
1613 AND MMT.TRANSACTION_TYPE_ID = 52
1614 AND MMT.TRANSACTION_ACTION_ID = 28
1615 AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2;
1616
1617 hdr_rec t_coa_hdr_rec;
1618 l_del_found boolean:=FALSE;
1619 BEGIN
1620 PrintLn('Begin Procedure get_order_params');
1621
1622 FOR c_order_rec IN c_order_delivery(p_param_rec.delivery_id,
1623 p_param_rec.order_id,
1624 --p_param_rec.item_id,
1625 p_param_rec.inventory_item_id,
1626 p_param_rec.org_id,
1627 p_param_rec.cust_id) LOOP
1628
1629 PrintLn('In c_order_delivery LOOP');
1630 l_del_found := TRUE;
1631
1632 hdr_rec.org_id := c_order_rec.org_id;
1633 hdr_rec.order_id := c_order_rec.order_id;
1634 hdr_rec.line_id := c_order_rec.line_id;
1635 hdr_rec.delivery_detail_id := c_order_rec.delivery_detail_id;
1636 hdr_rec.order_no := c_order_rec.order_no;
1637 hdr_rec.custpo_no := c_order_rec.custpo_no;
1638 hdr_rec.cust_id := c_order_rec.shipcust_id;
1639 hdr_rec.bol_id := c_order_rec.bol_id;
1640 --hdr_rec.item_id := c_order_rec.item_id; --INVCONV
1641 hdr_rec.inventory_item_id := c_order_rec.inventory_item_id; --INVCONV
1642 hdr_rec.revision := c_order_rec.item_revision; --Bug# 4662469
1643 hdr_rec.order_qty1 := c_order_rec.order_qty1;
1644 hdr_rec.order_qty2 := c_order_rec.order_qty2;
1645 --hdr_rec.order_um1 := c_order_rec.order_um1; --INVCONV
1646 --hdr_rec.order_um2 := c_order_rec.order_um2; --INVCONV
1647 hdr_rec.order_uom1 := c_order_rec.order_uom1; --INVCONV
1648 hdr_rec.order_uom2 := c_order_rec.order_uom2; --INVCONV
1649 hdr_rec.ship_qty1 := c_order_rec.ship_qty1;
1650 hdr_rec.ship_qty2 := c_order_rec.ship_qty2;
1651 --hdr_rec.ship_uom1 := c_order_rec.ship_uom1; --INVCONV -- Bug # 3710191 added these two lines.
1652 --hdr_rec.ship_uom2 := c_order_rec.ship_uom2; --INVCONV
1653 hdr_rec.ship_qty_uom1 := c_order_rec.ship_qty_uom1; --INVCONV
1654 hdr_rec.ship_qty_uom2 := c_order_rec.ship_qty_uom2; --INVCONV
1655 hdr_rec.cust_no := c_order_rec.cust_no;
1656 hdr_rec.cust_name := c_order_rec.cust_name;
1657 hdr_rec.bol_no := c_order_rec.bol_no;
1658 --hdr_rec.item_no := c_order_rec.item_no; --INVCONV
1659 --hdr_rec.item_desc := c_order_rec.item_desc1; --INVCONV
1660 hdr_rec.item_number := c_order_rec.item_number; --INVCONV
1661 hdr_rec.item_description := c_order_rec.item_description; --INVCONV
1662 hdr_rec.ship_from_org_id := c_order_rec.ship_from_org_id;
1663 hdr_rec.shipdate := c_order_rec.actual_shipdate;
1664 hdr_rec.ship_to_site_id := c_order_rec.ship_to_site_id; --Bug 4166529 added.
1665
1666 PrintLn('hdr_rec.org_id = '||hdr_rec.org_id);
1667 PrintLn('hdr_rec.order_id = '||hdr_rec.order_id);
1671 PrintLn('hdr_rec.custpo_no = '||hdr_rec.custpo_no);
1668 PrintLn('hdr_rec.line_id = '||hdr_rec.line_id);
1669 PrintLn('hdr_rec.delivery_detail_id = '||hdr_rec.delivery_detail_id);
1670 PrintLn('hdr_rec.order_no = '||hdr_rec.order_no);
1672 PrintLn('hdr_rec.cust_id = '||hdr_rec.cust_id);
1673 PrintLn('hdr_rec.bol_id = '||hdr_rec.bol_id);
1674 --PrintLn('hdr_rec.item_id = '||hdr_rec.item_id); --INVCONV
1675 PrintLn('hdr_rec.inventory_item_id = '||hdr_rec.inventory_item_id); --INVCONV
1676 PrintLn('hdr_rec.revsion ='||hdr_rec.revision); --Bug# 4662469
1677 PrintLn('hdr_rec.order_qty1 = '||hdr_rec.order_qty1);
1678 PrintLn('hdr_rec.order_qty2 = '||hdr_rec.order_qty2);
1679 --PrintLn('hdr_rec.order_um1 = '||hdr_rec.order_um1); --INVCONV
1680 --PrintLn('hdr_rec.order_um2 = '||hdr_rec.order_um2); --INVCONV
1681 PrintLn('hdr_rec.order_uom1 = '||hdr_rec.order_uom1); --INVCONV
1682 PrintLn('hdr_rec.order_uom2 = '||hdr_rec.order_uom2); --INVCONV
1683 PrintLn('hdr_rec.ship_qty1 = '||hdr_rec.ship_qty1);
1684 PrintLn('hdr_rec.ship_qty2 = '||hdr_rec.ship_qty2);
1685 --PrintLn('hdr_rec.ship_uom1 = '||hdr_rec.ship_uom1); --INVCONV -- Bug # 3710191 Added these two lines
1686 --PrintLn('hdr_rec.ship_uom2 = '||hdr_rec.ship_uom2);
1687 PrintLn('hdr_rec.ship_qty_uom1 = '||hdr_rec.ship_qty_uom1); --INVCONV
1688 PrintLn('hdr_rec.ship_qty_uom2 = '||hdr_rec.ship_qty_uom2); --INVCONV
1689 PrintLn('hdr_rec.cust_no = '||hdr_rec.cust_no);
1690 PrintLn('hdr_rec.cust_name = '||hdr_rec.cust_name);
1691 PrintLn('hdr_rec.bol_no = '||hdr_rec.bol_no);
1692 --PrintLn('hdr_rec.item_no = '||hdr_rec.item_no); --INVCONV
1693 --PrintLn('hdr_rec.item_desc = '||hdr_rec.item_desc); --INVCONV
1694 PrintLn('hdr_rec.item_number = '||hdr_rec.item_number); --INVCONV
1695 PrintLn('hdr_rec.item_description = '||hdr_rec.item_description); --INVCONV
1696 PrintLn('hdr_rec.ship_from_org_id = '||hdr_rec.ship_from_org_id);
1697 PrintLn('hdr_rec.shipdate = '||hdr_rec.shipdate);
1698 PrintLn('hdr_rec.ship_to_site_id = '||hdr_rec.ship_to_site_id); --Bug 4166529 added.
1699
1700 --INVCONV
1701 /*OPEN get_whse_info(c_order_rec.ship_from_org_id);
1702 FETCH get_whse_info INTO hdr_rec.orgn_code,hdr_rec.whse_code;
1703 CLOSE get_whse_info;*/
1704
1705 hdr_rec.organization_id := c_order_rec.ship_from_org_id; --INVCONV
1706 PrintLn('hdr_rec.organization_id = '||hdr_rec.organization_id); --INVCONV
1707
1708 --INVCONV
1709 /*FOR c_lot_tran in get_lot_tran(c_order_rec.delivery_detail_id) LOOP
1710 PrintLn('In get_lot_tran LOOP');
1711 hdr_rec.lot_id := c_lot_tran.lot_id;
1712 PrintLn('hdr_rec.lot_id(:= c_lot_tran.lot_id) = '||hdr_rec.lot_id);
1713 --BEGIN BUG#3615409
1714 --Initialized the lot_no
1715 --Moved the call_spec_match within the loop to fetch lot_no correctly.
1716 hdr_rec.lot_no := NULL;
1717 IF (hdr_rec.lot_id IS NULL) THEN
1718 hdr_rec.lot_id := p_param_rec.lot_id;
1719 hdr_rec.lot_no := p_param_rec.lot_no;
1720 END IF;
1721 PrintLn('hdr_rec.lot_id = '||hdr_rec.lot_id);
1722 PrintLn('hdr_rec.lot_no = '||hdr_rec.lot_no);
1723 PrintLn('Calling Procedure call_spec_match');
1724 call_spec_match(hdr_rec,
1725 x_return_status);
1726 --END BUG#3615409
1727 END LOOP;*/
1728
1729 --INVCONV
1730 FOR c_lot_tran in get_lot_tran(c_order_rec.line_id) LOOP
1731 PrintLn('In get_lot_tran LOOP');
1732 hdr_rec.lot_number := c_lot_tran.lot_number;
1733 PrintLn('hdr_rec.lot_number(:= c_lot_tran.lot_number) = '||hdr_rec.lot_number);
1734 call_spec_match(hdr_rec,x_return_status);
1735 END LOOP;
1736 END LOOP;
1737
1738 --If the delivery_id is passed or the previous cursor returned rows.
1739 --BEGIN BUG#3615409
1740 IF (p_param_rec.delivery_id IS NOT NULL OR l_del_found) THEN
1741 --END BUG#3615409
1742 PrintLn('RETURN End Procedure get_order_params');
1743 RETURN;
1744 END IF;
1745
1746 FOR c_order_rec IN c_order(p_param_rec.order_id,
1747 --p_param_rec.item_id, --INVCONV
1748 p_param_rec.inventory_item_id, --INVCONV
1749 p_param_rec.org_id,
1750 p_param_rec.cust_id) LOOP
1751 PrintLn('In c_order LOOP');
1752
1753 hdr_rec.org_id := c_order_rec.org_id;
1754 hdr_rec.order_id := c_order_rec.order_id;
1755 hdr_rec.line_id := c_order_rec.line_id;
1756 hdr_rec.order_no := c_order_rec.order_no;
1757 hdr_rec.custpo_no := c_order_rec.custpo_no;
1758 hdr_rec.cust_id := c_order_rec.shipcust_id;
1759 --hdr_rec.item_id := c_order_rec.item_id; --INVCONV
1760 hdr_rec.inventory_item_id := c_order_rec.inventory_item_id; --INVCONV
1761 hdr_rec.revision := c_order_rec.item_revision; --Bug# 4662469
1762 hdr_rec.order_qty1 := c_order_rec.order_qty1;
1763 hdr_rec.order_qty2 := c_order_rec.order_qty2;
1764 --hdr_rec.order_um1 := c_order_rec.order_um1; --INVCONV
1765 --hdr_rec.order_um2 := c_order_rec.order_um2; --INVCONV
1766 hdr_rec.order_uom1 := c_order_rec.order_uom1; --INVCONV
1770 --hdr_rec.item_no := c_order_rec.item_no; --INVCONV
1767 hdr_rec.order_uom2 := c_order_rec.order_uom2; --INVCONV
1768 hdr_rec.cust_no := c_order_rec.cust_no;
1769 hdr_rec.cust_name := c_order_rec.cust_name;
1771 --hdr_rec.item_desc := c_order_rec.item_desc1; --INVCONV
1772 hdr_rec.item_number := c_order_rec.item_number; --INVCONV
1773 hdr_rec.item_description := c_order_rec.item_description; --INVCONV
1774 hdr_rec.ship_from_org_id := c_order_rec.ship_from_org_id;
1775 hdr_rec.shipdate := c_order_rec.actual_shipdate;
1776 --hdr_rec.lot_id := p_param_rec.lot_id; --INVCONV
1777 --hdr_rec.lot_no := p_param_rec.lot_no; --INVCONV
1778 hdr_rec.lot_number := p_param_rec.lot_number; --INVCONV
1779 hdr_rec.ship_to_site_id := c_order_rec.ship_to_site_id; --Bug 4166529 added.
1780
1781
1782 PrintLn('hdr_rec.org_id = '||hdr_rec.org_id);
1783 PrintLn('hdr_rec.order_id = '||hdr_rec.order_id);
1784 PrintLn('hdr_rec.line_id = '||hdr_rec.line_id);
1785 PrintLn('hdr_rec.order_no = '||hdr_rec.order_no);
1786 PrintLn('hdr_rec.custpo_no = '||hdr_rec.custpo_no);
1787 PrintLn('hdr_rec.cust_id = '||hdr_rec.cust_id);
1788 --PrintLn('hdr_rec.item_id = '||hdr_rec.item_id); --INVCONV
1789 PrintLn('hdr_rec.inventory_item_id = '||hdr_rec.inventory_item_id); --INVCONV
1790 PrintLn('hdr_rec.revsion ='||hdr_rec.revision); --Bug# 4662469
1791 PrintLn('hdr_rec.order_qty1 = '||hdr_rec.order_qty1);
1792 PrintLn('hdr_rec.order_qty2 = '||hdr_rec.order_qty2);
1793 --PrintLn('hdr_rec.order_um1 = '||hdr_rec.order_um1); --INVCONV
1794 --PrintLn('hdr_rec.order_um2 = '||hdr_rec.order_um2); --INVCONV
1795 PrintLn('hdr_rec.order_uom1 = '||hdr_rec.order_uom1); --INVCONV
1796 PrintLn('hdr_rec.order_uom2 = '||hdr_rec.order_uom2); --INVCONV
1797 PrintLn('hdr_rec.cust_no = '||hdr_rec.cust_no);
1798 PrintLn('hdr_rec.cust_name = '||hdr_rec.cust_name);
1799 --PrintLn('hdr_rec.item_no = '||hdr_rec.item_no); --INVCONV
1800 --PrintLn('hdr_rec.item_desc = '||hdr_rec.item_desc); --INVCONV
1801 PrintLn('hdr_rec.item_number = '||hdr_rec.item_number); --INVCONV
1802 PrintLn('hdr_rec.item_description = '||hdr_rec.item_description); --INVCONV
1803 PrintLn('hdr_rec.ship_from_org_id = '||hdr_rec.ship_from_org_id);
1804 PrintLn('hdr_rec.shipdate = '||hdr_rec.shipdate);
1805 --PrintLn('hdr_rec.lot_id = '||hdr_rec.lot_id); --INVCONV
1806 --PrintLn('hdr_rec.lot_no = '||hdr_rec.lot_no); --INVCONV
1807 PrintLn('hdr_rec.lot_number = '||hdr_rec.lot_number); --INVCONV
1808 PrintLn('hdr_rec.ship_to_site_id = '||hdr_rec.ship_to_site_id); --Bug 4166529 added.
1809
1810 --INVCONV
1811 /*OPEN get_whse_info(c_order_rec.ship_from_org_id);
1812 FETCH get_whse_info INTO hdr_rec.orgn_code,hdr_rec.whse_code;
1813 CLOSE get_whse_info;
1814 PrintLn('hdr_rec.orgn_code = '||hdr_rec.orgn_code);
1815 PrintLn('hdr_rec.whse_code = '||hdr_rec.whse_code);*/
1816
1817 hdr_rec.organization_id := c_order_rec.ship_from_org_id; --INVCONV
1818 PrintLn('hdr_rec.organization_id = '||hdr_rec.organization_id); --INVCONV
1819
1820 --INVCONV
1821 /* hdr_rec.lot_no := NULL;
1822 IF (hdr_rec.lot_id IS NULL) THEN
1823 hdr_rec.lot_id := p_param_rec.lot_id;
1824 hdr_rec.lot_no := p_param_rec.lot_no;
1825 END IF;
1826 PrintLn('hdr_rec.lot_id = '||hdr_rec.lot_id);
1827 PrintLn('hdr_rec.lot_no = '||hdr_rec.lot_no);*/
1828 PrintLn('Calling Procedure call_spec_match');
1829 call_spec_match(hdr_rec,
1830 x_return_status);
1831
1832
1833
1834
1835 END LOOP;
1836 PrintLn('End Procedure get_order_params');
1837 EXCEPTION
1838
1839 WHEN OTHERS THEN
1840 PrintLn('GMD_COA_DATA.GET_ORDER_PARAMS '|| SUBSTR(SQLERRM,1,100));
1841 log_msg('GMD_COA_DATA.GET_ORDER_PARAMS '|| SUBSTR(SQLERRM,1,100));
1842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1843
1844 END get_order_params;
1845
1846 PROCEDURE populate_coa_data(
1847 p_api_version IN NUMBER
1848 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1849 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1850 , p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
1851 , x_return_status OUT NOCOPY VARCHAR2
1852 , x_msg_count OUT NOCOPY NUMBER
1853 , x_msg_data OUT NOCOPY VARCHAR2
1854 , param_rec IN t_coa_parameters) IS
1855 hdr_rec t_coa_hdr_rec;
1856 l_api_name CONSTANT VARCHAR2(30) := 'populate_coa_data' ;
1857 l_api_version CONSTANT NUMBER := 1.0 ;
1858
1859 BEGIN
1860
1861 Log_Initialize; -- Initialize Debug Log File
1862
1863 PrintLn('Begin Procedure populate_coa_data');
1864 PrintLn('param_rec.order_id = '||param_rec.order_id);
1865 --PrintLn('param_rec.orgn_code = '||param_rec.orgn_code); --INVCONV
1866 PrintLn('param_rec.organization_id = '||param_rec.organization_id); --INVCONV
1867 PrintLn('param_rec.cust_id = '||param_rec.cust_id);
1868 PrintLn('param_rec.delivery_id = '||param_rec.delivery_id);
1869 --PrintLn('param_rec.item_id = '||param_rec.item_id); --INVCONV
1870 PrintLn('param_rec.inventory_item_id = '||param_rec.inventory_item_id); --INVCONV
1874 --PrintLn('param_rec.location = '||param_rec.location); --INVCONV
1871 PrintLn('param_rec.revision = '||param_rec.revision); --Bug# 4662469
1872 --PrintLn('param_rec.whse_code = '||param_rec.whse_code); --INVCONV
1873 PrintLn('param_rec.subinventory = '||param_rec.subinventory); --INVCONV
1875 PrintLn('param_rec.locator_id = '||param_rec.locator_id); --INVCONV
1876 --PrintLn('param_rec.lot_id = '||param_rec.lot_id); --INVCONV
1877 --PrintLn('param_rec.lot_no = '||param_rec.lot_no); --INVCONV
1878 PrintLn('param_rec.lot_number = '||param_rec.lot_number); --INVCONV
1879 PrintLn('param_rec.org_id = '||param_rec.org_id);
1880 PrintLn('param_rec.sampling_event_id = '||param_rec.sampling_event_id);
1881 PrintLn('param_rec.spec_id = '||param_rec.spec_id);
1882 PrintLn('param_rec.ship_to_site_id = '||param_rec.ship_to_site_id); -- Bug# 5399406
1883
1884 IF FND_API.to_boolean(p_init_msg_list) THEN
1885 FND_MSG_PUB.Initialize;
1886 END IF;
1887
1888 -- Standard call to check for call compatibility.
1889 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
1890 p_api_version ,
1891 l_api_name ,
1892 G_PKG_NAME ) THEN
1893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1894 END IF;
1895
1896 x_return_status :=FND_API.G_RET_STS_SUCCESS;
1897
1898 IF (param_rec.delivery_id IS NOT NULL or param_rec.order_id IS NOT NULL) THEN
1899 PrintLn('Calling Procedure get_order_params');
1900 get_order_params(param_rec,
1901 x_return_status);
1902 ELSE
1903 hdr_rec.cust_id := param_rec.cust_id;
1904 hdr_rec.order_id := param_rec.order_id;
1905 --hdr_rec.item_id :=param_rec.item_id; --INVCONV
1906 hdr_rec.inventory_item_id :=param_rec.inventory_item_id; --INVCONV
1907 hdr_rec.revision := param_rec.revision; --Bug# 4662469
1908 --hdr_rec.whse_code := param_rec.whse_code; --INVCONV
1909 hdr_rec.subinventory := param_rec.subinventory; --INVCONV
1910 --hdr_rec.lot_id := param_rec.lot_id; --INVCONV
1911 --hdr_rec.lot_no := param_rec.lot_no; --INVCONV
1912 hdr_rec.lot_number := param_rec.lot_number; --INVCONV
1913 hdr_rec.org_id := param_rec.org_id;
1914 --hdr_rec.orgn_code := param_rec.orgn_code; --INVCONV
1915 hdr_rec.organization_id := param_rec.organization_id; --INVCONV
1916 hdr_rec.ship_to_site_id := param_rec.ship_to_site_id; --Bug# 5399406
1917
1918 -- these two parameters are for short circuiting
1919 --hdr_rec.sampling_event_id
1920 --hdr_rec.spec_id
1921
1922 PrintLn('Calling Procedure call_spec_match');
1923 call_spec_match(hdr_rec,
1924 x_return_status);
1925 END IF;
1926 FND_MSG_PUB.Count_AND_GET
1927 (p_count => x_msg_count, p_data => x_msg_data);
1928 PrintLn('End Procedure populate_coa_data');
1929 EXCEPTION
1930
1931 WHEN OTHERS THEN
1932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1933 PrintLn('GMD_COA_DATA.POPULATE_COA_DATA '|| SUBSTR(SQLERRM,1,100));
1934 log_msg('GMD_COA_DATA.POPULATE_COA_DATA '|| SUBSTR(SQLERRM,1,100));
1935 FND_MSG_PUB.Count_AND_GET
1936 (p_count => x_msg_count, p_data => x_msg_data);
1937 END populate_coa_data;
1938 -- /*************************************************************************
1939 -- # PROC
1940 -- # Log_Initialize
1941 -- #
1942 -- # INPUT PARAMETERS
1943 -- # filename
1944 -- # DESCRIPTION
1945 -- # Procedure to initialize the debug log.
1946 -- #
1947 -- #
1948 -- #**************************************************************************/
1949 PROCEDURE Log_Initialize
1950 IS
1951
1952 LOG UTL_FILE.FILE_TYPE;
1953 l_file_name VARCHAR2(10) := 'GMDLOG.txt';
1954
1955 CURSOR c_get_1st_location IS
1956 SELECT NVL( SUBSTR( value, 1, INSTR( value, ',')-1), value)
1957 FROM v$parameter
1958 WHERE name = 'utl_file_dir';
1959
1960 BEGIN
1961
1962 OPEN c_get_1st_location;
1963 FETCH c_get_1st_location
1964 INTO g_gmdlog_location;
1965 CLOSE c_get_1st_location;
1966
1967 LOG := UTL_FILE.fopen(g_gmdlog_location, l_file_name, 'w');
1968 UTL_FILE.put_line(LOG, 'Log file opened: '||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1969 UTL_FILE.fflush(LOG);
1970 UTL_FILE.fclose(LOG);
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 NULL;
1975
1976 END Log_Initialize;
1977
1978 -- /*************************************************************************
1979 -- # PROC
1980 -- # PrintLine
1981 -- #
1982 -- # INPUT PARAMETERS
1983 -- # string
1984 -- # DESCRIPTION
1985 -- # Procedure to write the debug log.
1986 -- #
1987 -- #
1988 -- #**************************************************************************/
1989 PROCEDURE PrintLn( p_msg IN VARCHAR2 ) IS
1990
1991 CURSOR get_log_file_location IS
1992 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
1993 FROM v$parameter
1994 WHERE name = 'utl_file_dir';
1995
1996
1997 l_log UTL_FILE.file_type;
1998 l_file_name VARCHAR2(80) := 'GMDLOG.txt';
1999
2000 BEGIN
2001
2002 IF g_gmdlog_location is NULL THEN
2003 OPEN get_log_file_location;
2004 FETCH get_log_file_location into gmd_coa_data_om_new.g_gmdlog_location;
2005 CLOSE get_log_file_location;
2006 END IF;
2007
2008 l_log := UTL_FILE.fopen(g_gmdlog_location, l_file_name, 'a');
2009 IF UTL_FILE.IS_OPEN(l_log) THEN
2010 UTL_FILE.put_line(l_log, p_msg);
2011 UTL_FILE.fflush(l_log);
2012 UTL_FILE.fclose(l_log);
2013 END IF;
2014
2015 EXCEPTION
2016
2017 WHEN OTHERS THEN
2018 NULL;
2019
2020 END PrintLn;
2021
2022 END gmd_coa_data_om_new;