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