1 PACKAGE BODY GMD_CoA_Data_New AS
2 /* $Header: GMDCOA2B.pls 115.2 2002/12/05 18:02:52 magupta noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_COA_DATA_NEW';
5 /* next variable is version for Look_for_CoC_Data */
6
7
8 /*#############################################################################
9 # Procedure Look_For_CoC_Specs
10 # The cursors used are similar to spec cursors in Details procedure.
11 # Cursors here do not take 'assay_code' as parameter and return info for
12 # header and detail tables. Also, cursors here will return records with
13 # NULL lots (specs only; results must have lots if item is lot cntrl'd).
14 # This procedure also used if sales/shipping information is given (so
15 # there is data in the header table), but no results were found in
16 # Populate_Details. Use cursors here to look for specs for CoC.
17 # P_init_msg_list should be false when called from Populate_CoA_Data
18 # 11feb2000 LRJ
19 # 31mar2000 LRJ Made this procedure public (added to package spec)
20 # 07jul2000 LRJ Add section for scenario where header data exists but no
21 # results were found. Make procedure private.
22 # Added cursors for global item/lot specs and global item
23 # no-lot spec
24 # 12jun2001 James Bernard Bug 1810652
25 # Modified the where clause of the select statement in the
26 # get_qc_cust_spec cursor definition to retrieve records when
27 # c_item_id is null.
28 # Modified the where clause of the select statement in the
29 # get_qc_global_cust_spec cursor definition to retrieve records when
30 # c_item_id is null.
31 # Modified the where clause of the select statement in the
32 # get_qc_item_spec cursor definition to retrieve records when
33 # c_item_id is null.
34 # In this procedure in the for cursors tbl.hdr.FIRST and tbl_hdr.LAST
35 # are replaced with NVL(tbl.hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
36 ############################################################################ */
37 PROCEDURE Look_For_CoC_Specs(
38 rec_param IN t_coa_parameters,
39 hdr_tbl_ndx OUT NOCOPY BINARY_INTEGER,
40 tbl_hdr IN OUT NOCOPY t_coa_header_tbl,
41 tbl_dtl IN OUT NOCOPY t_coa_detail_tbl)
42 IS
43 /* get cust spec else get global cust spec */
44 /* else get item spec else get global item spec */
45 /* BEGIN BUG#1810652 James Bernard */
46 /* Added 'c_item_id is NULL or' in the where clause */
47 CURSOR get_qc_cust_spec (c_cust_id NUMBER, c_item_id NUMBER,
48 c_orgn_code VARCHAR2)
49 IS
50 select gcs.orgn_code,
51 gcs.cust_id,
52 gsb.item_id,
53 null whse_code,
54 null lot_id,
55 gsb.spec_id qc_spec_id,
56 gt.test_code assay_code,
57 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
58 specification,
59 gst.test_uom uom,
60 gcs.text_code spec_text_code
61 from
62 gmd_specifications_b gsb, -- qc_spec_mst qsm
63 gmd_customer_spec_vrs gcs,
64 gmd_spec_tests_b gst,
65 gmd_qc_tests_b gt
66 where
67 gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id= c_cust_id)
68 and (c_item_id is NULL or gsb.item_id = c_item_id)
69 and gcs.orgn_code = c_orgn_code
70 and gsb.spec_id = gcs.spec_id
71 and gsb.spec_id = gst.spec_id
72 and gst.test_id = gt.test_id
73 and gsb.delete_mark = 0
74 ;
75
76 /* END BUG#1810652 */
77 /* BEGIN BUG#1810652 James Bernard */
78 /* Added 'c_item_id is NULL or ' in the where clause */
79 CURSOR get_qc_global_cust_spec (c_cust_id NUMBER, c_item_id NUMBER)
80 IS
81 select gcs.orgn_code,
82 gcs.cust_id,
83 gsb.item_id,
84 null whse_code,
85 null lot_id,
86 gsb.spec_id qc_spec_id,
87 gt.test_code assay_code,
88 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
89 specification,
90 gst.test_uom uom,
91 gcs.text_code spec_text_code
92 from
93 gmd_specifications_b gsb, -- qc_spec_mst qsm
94 gmd_customer_spec_vrs gcs,
95 gmd_spec_tests_b gst,
96 gmd_qc_tests_b gt
97 where
98 gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
99 and (c_item_id is NULL or gsb.item_id = c_item_id)
100 and gcs.orgn_code is null
101 and gsb.spec_id = gcs.spec_id
102 and gsb.spec_id = gst.spec_id
103 and gst.test_id = gt.test_id
104 and gsb.delete_mark = 0
105 ;
106 /* END BUG#1810652 */
107 /* instead of 8 cursors with the hierarchy for searching specs */
108 /* 1 cursor has parameters to look for NULLs or not */
109 /* Cursor should be called in this order: */
110 /* FALSE = 0 (off) TRUE = 1 (on) */
111 /* (1) whse/lot/orgn params FALSE, look for specific whse/lot/orgn */
112 /* (2) orgn param=TRUE, look for global + specific whse/lot */
113 /* (3) orgn param=FALSE, lot param=TRUE; local + specific whse + null lot */
114 /* (4) orgn param=TRUE, lot param=TRUE; global + specific whse + null lot */
115 /* (5) orgn param=FALSE, whse param=TRUE, lot param=FALSE; */
116 /* local + null whse + specific lot */
117 /* (6) orgn param=TRUE, whse param=TRUE, lot param=FALSE; */
118 /* global + null whse + specific lot */
119 /* (7) orgn param=FALSE, whse param=TRUE, lot param=TRUE; */
120 /* local + null whse + null lot */
121 /* (8) orgn param=TRUE, whse param=TRUE, lot param=TRUE; */
122 /* global + null whse + null lot */
123
124 /* BEGIN BUG#1810652 James Bernard */
125 /* Added the 'c_item_id is NULL or' in the where clause */
126 CURSOR get_qc_item_spec (c_item_id NUMBER, c_lot_id NUMBER,
127 c_whse_code VARCHAR2, c_orgn_code VARCHAR2,
128 l_chk_whse_null NUMBER,
129 l_chk_lot_null NUMBER,
130 l_chk_orgn_null NUMBER
131 )
132 IS
133 select gcs.orgn_code,
134 null cust_id,
135 gsb.item_id,
136 gcs.whse_code whse_code,
137 gcs.lot_id lot_id,
138 gsb.spec_id qc_spec_id,
139 gt.test_code assay_code,
140 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
141 specification,
142 gst.test_uom uom,
143 gcs.text_code spec_text_code
144 from
145 gmd_specifications_b gsb, -- qc_spec_mst qsm
146 gmd_inventory_spec_vrs gcs,
147 gmd_spec_tests_b gst,
148 gmd_qc_tests_b gt
149 where
150 ((l_chk_whse_null =1 and gcs.whse_code is NULL)
151 OR (l_chk_whse_null=0 and
152 (c_whse_code is NULL or gcs.whse_code = c_whse_code)
153 ) )
154 and ((l_chk_lot_null=1 and gcs.lot_id is NULL)
155 OR (l_chk_lot_null=0 and
156 (c_lot_id is NULL or gcs.lot_id = c_lot_id)
157 ) )
158 and (c_item_id is NULL or gsb.item_id = c_item_id)
159 and ((l_chk_orgn_null=1 and gcs.orgn_code is NULL)
160 OR (l_chk_orgn_null=0 and
161 (c_orgn_code is NULL or gcs.orgn_code = c_orgn_code)
162 ) )
163 and gsb.spec_id = gcs.spec_id
164 and gsb.spec_id = gst.spec_id
165 and gst.test_id = gt.test_id
166 and gsb.delete_mark = 0
167 ;
168
169 /* END BUG#1810652 */
170
171 dtl_tbl_ndx BINARY_INTEGER := 0;
172 v_gmd_coa_id BINARY_INTEGER := 1;
173 v_previous_header VARCHAR2(75); /* orgn_code+cust+item+whse+lot */
174 v_current_header VARCHAR2(75);
175 l_chk_whse_null NUMBER;
176 l_chk_lot_null NUMBER;
177 l_chk_orgn_null NUMBER;
178
179 /* ***********************************************************************
180 - Procedure assign_cursor_values_to_table
181 - If there is a way to send a cursor or a row from a cursor as a
182 - variable, change this code!
183 - ***********************************************************************/
184 PROCEDURE Assign_Cursor_Values_To_Table
185 (p_orgn_code IN op_ordr_hdr.orgn_code%TYPE,
186 p_cust_id IN op_cust_mst.cust_id%TYPE,
187 p_item_id IN ic_item_mst.item_id%TYPE,
188 p_whse_code IN ic_whse_mst.whse_code%TYPE,
189 p_lot_id IN ic_lots_mst.lot_id%TYPE,
190 p_qc_spec_id IN gmd_specifications_b.spec_id%TYPE,
191 p_assay_code IN gmd_qc_tests_b.test_code%TYPE,
192 p_specification IN gmd_spec_tests.target_value_char%TYPE,
193 p_UOM IN gmd_qc_tests_b.test_unit%TYPE,
194 p_spec_text_code IN gmd_specifications_b.text_code%TYPE)
195 IS
196 BEGIN
197 dtl_tbl_ndx := dtl_tbl_ndx + 1;
198
199 IF hdr_tbl_ndx = 0 THEN
200 tbl_dtl(dtl_tbl_ndx).gmd_coa_id :=1 ;
201 ELSE
202 tbl_dtl(dtl_tbl_ndx).gmd_coa_id := hdr_tbl_ndx;
203 END IF; /* -- end if hdr tbl ndx is 0 or greater than 0 */
204 tbl_dtl(dtl_tbl_ndx).qc_spec_id := p_qc_spec_id;
205 tbl_dtl(dtl_tbl_ndx).assay_code := p_assay_code;
206 tbl_dtl(dtl_tbl_ndx).specification := p_specification;
207
208 tbl_dtl(dtl_tbl_ndx).uom := p_uom;
209 tbl_dtl(dtl_tbl_ndx).spec_text_code := p_spec_text_code;
210
211 v_current_header := p_orgn_code || p_cust_id ||
212 p_item_id || p_whse_code ||
213 p_lot_id;
214
215 IF v_previous_header is not NULL THEN
216 IF v_previous_header <> v_current_header THEN
217 hdr_tbl_ndx := hdr_tbl_ndx + 1;
218 tbl_hdr(hdr_tbl_ndx).gmd_coa_id := hdr_tbl_ndx;
219 tbl_hdr(hdr_tbl_ndx).orgn_code := p_orgn_code;
220 tbl_hdr(hdr_tbl_ndx).cust_id := p_cust_id;
221 tbl_hdr(hdr_tbl_ndx).item_id := p_item_id;
222 tbl_hdr(hdr_tbl_ndx).whse_code := p_whse_code;
223 tbl_hdr(hdr_tbl_ndx).lot_id := p_lot_id;
224 tbl_hdr(hdr_tbl_ndx).report_title := v_report_title;
225 END IF;
226 v_previous_header := v_current_header;
227 ELSE /* -- else this is the very first record */
228 v_previous_header := p_orgn_code || p_cust_id ||
229 p_item_id || p_whse_code ||
230 p_lot_id;
231 hdr_tbl_ndx := hdr_tbl_ndx + 1;
232 tbl_hdr(hdr_tbl_ndx).gmd_coa_id := hdr_tbl_ndx;
233 tbl_hdr(hdr_tbl_ndx).orgn_code := p_orgn_code;
234 tbl_hdr(hdr_tbl_ndx).cust_id := p_cust_id;
235 tbl_hdr(hdr_tbl_ndx).item_id := p_item_id;
239 END IF; /* -- end if this is 1st record or not */
236 tbl_hdr(hdr_tbl_ndx).whse_code := p_whse_code;
237 tbl_hdr(hdr_tbl_ndx).lot_id := p_lot_id;
238 tbl_hdr(hdr_tbl_ndx).report_title := v_report_title;
240 END Assign_Cursor_Values_To_Table;
241
242 BEGIN
243 /* if customer parameter was given, look for customer-specific spec,
244 * then global spec
245 * if no customer results found, look for item/loc/lot-specific result
246 * Fill in whse, customer name, lot no at end of main code
247
248 * Parameters given would lead to either a cust spec or a global cust
249 * spec or an item spec (or no spec).
250 * Cursors have been designed to bring back all info needed for header
251 * and for detail. So that header table will not have duplicates,
252 * code will check header info against previous and only add to header
253 * table when header info changes. */
254
255 hdr_tbl_ndx := 0;
256 dtl_tbl_ndx := 0;
257 l_chk_whse_null := 0; /* -- 1st time thru, look for match */
258 l_chk_orgn_null := 0;
259 l_chk_lot_null := 0;
260
261 IF tbl_hdr.FIRST is NULL THEN
262 /* if no sales/ship data was given (item/lot only) and (therefore) */
263 /* there is no data in header table, use this code which puts data */
264 /* in both header and detail tables. */
265 IF rec_param.cust_id is not NULL THEN
266
267 FOR qc_cur_rec IN get_qc_cust_spec (rec_param.cust_id,
268 rec_param.item_id,
269 rec_param.orgn_code) LOOP
270 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
271 qc_cur_rec.cust_id,
272 qc_cur_rec.item_id,
273 qc_cur_rec.whse_code,
274 qc_cur_rec.lot_id,
275 qc_cur_rec.qc_spec_id,
276 qc_cur_rec.assay_code,
277 qc_cur_rec.specification,
278 qc_cur_rec.uom,
279 qc_cur_rec.spec_text_code );
280 END LOOP; /* -- end get CoC cust spec */
281 IF hdr_tbl_ndx = 0 THEN
282 /* -- try for global customer spec */
283 FOR qc_cur_rec IN get_qc_global_cust_spec
284 (rec_param.cust_id,
285 rec_param.item_id ) LOOP
286 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
287 qc_cur_rec.cust_id,
288 qc_cur_rec.item_id,
289 qc_cur_rec.whse_code,
290 qc_cur_rec.lot_id,
291 qc_cur_rec.qc_spec_id,
292 qc_cur_rec.assay_code,
293 qc_cur_rec.specification,
294 qc_cur_rec.uom,
295 qc_cur_rec.spec_text_code );
296 END LOOP;
297 /* -- end get global cust spec */
298 END IF;
299 /* -- end if no orgn/cust spec found, look for global spec */
300
301 END IF;
302 /* -- end if cust parameter has value, */
303 /* -- then look for cust-specific result */
304
305 IF hdr_tbl_ndx = 0 THEN
306 /* no customer specific spec found, or no customer id given as */
307 /* parameter, look for local item/lot/whse specs */
308 /* (1) */
309 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
310 rec_param.lot_id,
311 rec_param.whse_code,
312 rec_param.orgn_code,
316 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
313 l_chk_whse_null,
314 l_chk_lot_null,
315 l_chk_orgn_null ) LOOP
317 qc_cur_rec.cust_id,
318 qc_cur_rec.item_id,
319 qc_cur_rec.whse_code,
320 qc_cur_rec.lot_id,
321 qc_cur_rec.qc_spec_id,
322 qc_cur_rec.assay_code,
323 qc_cur_rec.specification,
324 qc_cur_rec.uom,
325 qc_cur_rec.spec_text_code );
326 END LOOP;
327 /* -- end get item from item/lot/loc spec */
328 END IF;
329 /* -- end if no cust-specific spec, then look for */
330 /* -- local item/loc/lot spec */
331
332 IF hdr_tbl_ndx = 0 THEN
333 /* no local item/lot/whse specs, look for global */
334 /* (2) */
335 l_chk_orgn_null := 1;
336
337 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
338 rec_param.lot_id,
339 rec_param.whse_code,
340 rec_param.orgn_code,
341 l_chk_whse_null,
342 l_chk_lot_null,
343 l_chk_orgn_null ) LOOP
344 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
345 qc_cur_rec.cust_id,
346 qc_cur_rec.item_id,
347 qc_cur_rec.whse_code,
348 qc_cur_rec.lot_id,
349 qc_cur_rec.qc_spec_id,
350 qc_cur_rec.assay_code,
351 qc_cur_rec.specification,
352 qc_cur_rec.uom,
353 qc_cur_rec.spec_text_code );
354 END LOOP;
355 /* -- end get item from item/lot/loc spec */
356 END IF;
357 /* -- end if no local item/lot/whse spec, then look for */
358 /* -- global item/loc/lot spec */
359
360 IF hdr_tbl_ndx = 0 THEN
361 /* no global item/lot/whse specs, look for local item/whse */
362 /* (3) */
363 l_chk_orgn_null := 0;
364 l_chk_lot_null := 1;
365
366 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
367 rec_param.lot_id,
368 rec_param.whse_code,
369 rec_param.orgn_code,
370 l_chk_whse_null,
371 l_chk_lot_null,
372 l_chk_orgn_null ) LOOP
373 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
374 qc_cur_rec.cust_id,
375 qc_cur_rec.item_id,
376 qc_cur_rec.whse_code,
377 qc_cur_rec.lot_id,
378 qc_cur_rec.qc_spec_id,
379 qc_cur_rec.assay_code,
380 qc_cur_rec.specification,
381 qc_cur_rec.uom,
382 qc_cur_rec.spec_text_code );
383 END LOOP;
384 /* end get item from item/lot/loc spec */
385 END IF;
389 IF hdr_tbl_ndx = 0 THEN
386 /* end if no global item/lot/whse spec, then look for */
387 /* local item/whse spec */
388
390 /* no local item/whse specs, look for global item/whse */
391 /* (4) */
392 l_chk_orgn_null := 1;
393 l_chk_lot_null := 1;
394
395 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
396 rec_param.lot_id,
397 rec_param.whse_code,
398 rec_param.orgn_code,
399 l_chk_whse_null,
400 l_chk_lot_null,
401 l_chk_orgn_null ) LOOP
402 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
403 qc_cur_rec.cust_id,
404 qc_cur_rec.item_id,
405 qc_cur_rec.whse_code,
406 qc_cur_rec.lot_id,
407 qc_cur_rec.qc_spec_id,
408 qc_cur_rec.assay_code,
409 qc_cur_rec.specification,
410 qc_cur_rec.uom,
411 qc_cur_rec.spec_text_code );
412 END LOOP;
413 /* end get item from item/lot/loc spec */
414 END IF;
415 /* end if no local item/whse spec, look for global */
416
417 IF hdr_tbl_ndx = 0 THEN
418 /* no global item/whse specs, look for local item/lot */
419 /* (5) */
420 l_chk_orgn_null := 0;
421 l_chk_lot_null := 0;
422 l_chk_whse_null := 1;
423
424 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
425 rec_param.lot_id,
426 rec_param.whse_code,
427 rec_param.orgn_code,
428 l_chk_whse_null,
429 l_chk_lot_null,
430 l_chk_orgn_null ) LOOP
431 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
432 qc_cur_rec.cust_id,
433 qc_cur_rec.item_id,
434 qc_cur_rec.whse_code,
435 qc_cur_rec.lot_id,
436 qc_cur_rec.qc_spec_id,
437 qc_cur_rec.assay_code,
438 qc_cur_rec.specification,
439 qc_cur_rec.uom,
440 qc_cur_rec.spec_text_code );
441 END LOOP;
442 /* end get item from item/lot/loc spec */
443 END IF;
444 /* end if no global item/lot/whse spec, then look for */
445 /* local item/lot spec */
446
447 IF hdr_tbl_ndx = 0 THEN
448 /* no local item/lot specs, look for global item/lot */
449 /* (6) */
450 l_chk_orgn_null := 1;
451 l_chk_lot_null := 0;
452 l_chk_whse_null := 1;
453
454 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
455 rec_param.lot_id,
456 rec_param.whse_code,
457 rec_param.orgn_code,
458 l_chk_whse_null,
459 l_chk_lot_null,
460 l_chk_orgn_null ) LOOP
461 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
462 qc_cur_rec.cust_id,
463 qc_cur_rec.item_id,
464 qc_cur_rec.whse_code,
465 qc_cur_rec.lot_id,
466 qc_cur_rec.qc_spec_id,
467 qc_cur_rec.assay_code,
468 qc_cur_rec.specification,
469 qc_cur_rec.uom,
470 qc_cur_rec.spec_text_code );
471 END LOOP;
472 /* end get item from item/lot/loc spec */
473 END IF;
474 /* end if no local item/lot spec, look for global */
475
476 IF hdr_tbl_ndx = 0 THEN
477 /* no global item/lot specs, look for local item only */
478 /* (7) */
479 l_chk_orgn_null := 0;
480 l_chk_lot_null := 1;
481 l_chk_whse_null := 1;
482
483 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
484 rec_param.lot_id,
485 rec_param.whse_code,
486 rec_param.orgn_code,
487 l_chk_whse_null,
488 l_chk_lot_null,
489 l_chk_orgn_null ) LOOP
490 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
491 qc_cur_rec.cust_id,
495 qc_cur_rec.qc_spec_id,
492 qc_cur_rec.item_id,
493 qc_cur_rec.whse_code,
494 qc_cur_rec.lot_id,
496 qc_cur_rec.assay_code,
497 qc_cur_rec.specification,
498 qc_cur_rec.uom,
499 qc_cur_rec.spec_text_code );
500 END LOOP;
501 /* end get item from item/lot/loc spec */
502 END IF;
503 /* end if no global item/lotspec, then look for */
504 /* local item-only spec */
505
506 IF hdr_tbl_ndx = 0 THEN
507 /* no local item-only specs, look for global item-only */
508 /* (8) */
509 l_chk_orgn_null := 1;
510 l_chk_lot_null := 1;
511 l_chk_whse_null := 1;
512
513 FOR qc_cur_rec IN get_qc_item_spec (rec_param.item_id,
514 rec_param.lot_id,
515 rec_param.whse_code,
516 rec_param.orgn_code,
517 l_chk_whse_null,
518 l_chk_lot_null,
519 l_chk_orgn_null ) LOOP
520 Assign_Cursor_Values_To_Table (qc_cur_rec.orgn_code,
521 qc_cur_rec.cust_id,
522 qc_cur_rec.item_id,
523 qc_cur_rec.whse_code,
524 qc_cur_rec.lot_id,
525 qc_cur_rec.qc_spec_id,
526 qc_cur_rec.assay_code,
527 qc_cur_rec.specification,
528 qc_cur_rec.uom,
529 qc_cur_rec.spec_text_code );
530 END LOOP;
531 /* end get item from item/lot/loc spec */
532 END IF;
533 /* end if no local item-only spec, look for global */
534
535 ELSE
536 /* sales/ship data was given, header records are already created. */
537 /* just look for specs and insert data into details table */
538
539 /*BEGIN BUG#1810652 James Bernard */
540 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
541 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
542 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
543 /*END BUG#1810652 */
544 dtl_tbl_ndx := 0;
545
546 IF tbl_hdr(loop_counter).cust_id is not NULL THEN
547 FOR qc_cur_rec IN get_qc_cust_spec
548 (tbl_hdr(loop_counter).cust_id,
549 tbl_hdr(loop_counter).item_id,
550 tbl_hdr(loop_counter).orgn_code) LOOP
551 dtl_tbl_ndx := dtl_tbl_ndx + 1;
552 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
553 := tbl_hdr(loop_counter).gmd_coa_id;
554 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
555 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
556 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
557 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
558 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
559 END LOOP;
560 /* end get CoC cust spec */
561
562 IF dtl_tbl_ndx = 0 THEN
563 /* try for global customer specification */
564 FOR qc_cur_rec IN get_qc_global_cust_spec
565 (tbl_hdr(loop_counter).cust_id,
566 tbl_hdr(loop_counter).item_id ) LOOP
567 dtl_tbl_ndx := dtl_tbl_ndx + 1;
568 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
569 := tbl_hdr(loop_counter).gmd_coa_id;
570 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
571 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
572 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
573 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
574 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
575 END LOOP;
576 /* end get global cust spec */
577 END IF;
578 /* end if no orgn/cust spec found, look for global spec */
579 END IF;
580 /* end if cust parameter has value, */
581 /* then look for cust-specific result */
582
583 IF dtl_tbl_ndx = 0 THEN
584 /* no customer specific spec found, or no customer id given as */
585 /* parameter, look for local item/lot/loc spec */
586 /* (1) */
587 FOR qc_cur_rec IN get_qc_item_spec
588 (tbl_hdr(loop_counter).item_id,
589 tbl_hdr(loop_counter).lot_id,
590 tbl_hdr(loop_counter).whse_code,
591 tbl_hdr(loop_counter).orgn_code,
592 l_chk_whse_null,
593 l_chk_lot_null,
594 l_chk_orgn_null
598 := tbl_hdr(loop_counter).gmd_coa_id;
595 ) LOOP
596 dtl_tbl_ndx := dtl_tbl_ndx + 1;
597 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
599 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
600 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
601 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
602 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
603 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
604 END LOOP;
605 /* end get item from local item/lot/loc spec */
606 END IF;
607 /* end if no cust-specific spec, then look for */
608 /* local item/loc/lot spec */
609
610 IF dtl_tbl_ndx = 0 THEN
611 /* no local item/lot/whse spec,look for global item specs */
612 /* (2) */
613 l_chk_orgn_null := 1;
614
615 FOR qc_cur_rec IN get_qc_item_spec
616 (tbl_hdr(loop_counter).item_id,
617 tbl_hdr(loop_counter).lot_id,
618 tbl_hdr(loop_counter).whse_code,
619 tbl_hdr(loop_counter).orgn_code,
620 l_chk_whse_null,
621 l_chk_lot_null,
622 l_chk_orgn_null
623 ) LOOP
624 dtl_tbl_ndx := dtl_tbl_ndx + 1;
625 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
626 := tbl_hdr(loop_counter).gmd_coa_id;
627 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
628 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
629 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
630 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
631 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
632 END LOOP;
633 /* end get item from item/lot/whse spec */
634 END IF;
635 /* end if no local item spec, look for global item spec */
636
637 IF dtl_tbl_ndx = 0 THEN
638 /* no global item/lot/whse specs, look for local item/whse */
639 /* (3) */
640 l_chk_orgn_null := 0;
641 l_chk_lot_null := 1;
642
643 FOR qc_cur_rec IN get_qc_item_spec
644 (tbl_hdr(loop_counter).item_id,
645 tbl_hdr(loop_counter).lot_id,
646 tbl_hdr(loop_counter).whse_code,
647 tbl_hdr(loop_counter).orgn_code,
648 l_chk_whse_null,
649 l_chk_lot_null,
650 l_chk_orgn_null
651 ) LOOP
652 dtl_tbl_ndx := dtl_tbl_ndx + 1;
653 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
654 := tbl_hdr(loop_counter).gmd_coa_id;
655 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
656 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
657 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
658 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
659 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
660 END LOOP;
661 /* end get item from item/lot/whse spec */
662 END IF;
663 /* end if no global item/whse/lot spec */
664
665 IF dtl_tbl_ndx = 0 THEN
666 /* no local item/whse spec,look for global item specs */
667 /* (4) */
668 l_chk_orgn_null := 1;
669 l_chk_lot_null := 1;
670
671 FOR qc_cur_rec IN get_qc_item_spec
672 (tbl_hdr(loop_counter).item_id,
673 tbl_hdr(loop_counter).lot_id,
674 tbl_hdr(loop_counter).whse_code,
675 tbl_hdr(loop_counter).orgn_code,
676 l_chk_whse_null,
677 l_chk_lot_null,
678 l_chk_orgn_null
679 ) LOOP
680 dtl_tbl_ndx := dtl_tbl_ndx + 1;
681 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
685 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
682 := tbl_hdr(loop_counter).gmd_coa_id;
683 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
684 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
686 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
687 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
688 END LOOP;
689 /* end get item from local item/lot/whse spec */
690 END IF;
691 /* end if no global/item/whse */
692
693 IF dtl_tbl_ndx = 0 THEN
694 /* no global item/whse specs, look for local item/lot */
695 /* (5) */
696 l_chk_orgn_null := 0;
697 l_chk_lot_null := 0;
698 l_chk_whse_null := 1;
699
700 FOR qc_cur_rec IN get_qc_item_spec
701 (tbl_hdr(loop_counter).item_id,
702 tbl_hdr(loop_counter).lot_id,
703 tbl_hdr(loop_counter).whse_code,
704 tbl_hdr(loop_counter).orgn_code,
705 l_chk_whse_null,
706 l_chk_lot_null,
707 l_chk_orgn_null
708 ) LOOP
709 dtl_tbl_ndx := dtl_tbl_ndx + 1;
710 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
711 := tbl_hdr(loop_counter).gmd_coa_id;
712 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
713 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
714 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
715 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
716 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
717 END LOOP;
718 /* end get item from item/lot/whse spec */
719 END IF;
720 /* end if local item/lot spec */
721
722 IF dtl_tbl_ndx = 0 THEN
723 /* no local item/lot spec,look for global item specs */
724 /* (6) */
725 l_chk_orgn_null := 1;
726 l_chk_lot_null := 0;
727 l_chk_whse_null := 1;
728
729 FOR qc_cur_rec IN get_qc_item_spec
730 (tbl_hdr(loop_counter).item_id,
731 tbl_hdr(loop_counter).lot_id,
732 tbl_hdr(loop_counter).whse_code,
733 tbl_hdr(loop_counter).orgn_code,
734 l_chk_whse_null,
735 l_chk_lot_null,
736 l_chk_orgn_null
737 ) LOOP
738 dtl_tbl_ndx := dtl_tbl_ndx + 1;
739 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
743 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
740 := tbl_hdr(loop_counter).gmd_coa_id;
741 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
742 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
744 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
745 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
746 END LOOP;
747 /* end get item from local item/lot/whse spec */
748 END IF;
749 /* end global item/lot spec */
750
751 IF dtl_tbl_ndx = 0 THEN
752 /* no global item/lot specs, look for local item-only */
753 /* (7) */
754 l_chk_orgn_null := 0;
755 l_chk_lot_null := 1;
756 l_chk_whse_null := 1;
757
758 FOR qc_cur_rec IN get_qc_item_spec
759 (tbl_hdr(loop_counter).item_id,
760 tbl_hdr(loop_counter).lot_id,
761 tbl_hdr(loop_counter).whse_code,
762 tbl_hdr(loop_counter).orgn_code,
763 l_chk_whse_null,
764 l_chk_lot_null,
765 l_chk_orgn_null
766 ) LOOP
767 dtl_tbl_ndx := dtl_tbl_ndx + 1;
768 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
769 := tbl_hdr(loop_counter).gmd_coa_id;
770 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
771 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
772 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
773 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
774 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
775 END LOOP;
776 /* end get item from item/lot/whse spec */
777 END IF;
778 /* end if local item-only spec */
779
780 IF dtl_tbl_ndx = 0 THEN
781 /* no local item-only spec,look for global item-only specs */
782 /* (8) */
783 l_chk_orgn_null := 1;
784 l_chk_lot_null := 1;
785 l_chk_whse_null := 1;
786
787 FOR qc_cur_rec IN get_qc_item_spec
788 (tbl_hdr(loop_counter).item_id,
789 tbl_hdr(loop_counter).lot_id,
790 tbl_hdr(loop_counter).whse_code,
791 tbl_hdr(loop_counter).orgn_code,
792 l_chk_whse_null,
793 l_chk_lot_null,
794 l_chk_orgn_null
795 ) LOOP
796 dtl_tbl_ndx := dtl_tbl_ndx + 1;
797 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
798 := tbl_hdr(loop_counter).gmd_coa_id;
799 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
800 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
801 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
802 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
803 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
804 END LOOP;
805 /* end get item from local item/lot/whse spec */
806 END IF;
807 /* end global item-only spec */
808
809 END LOOP;
810 /* end going through header records */
811 hdr_tbl_ndx := tbl_hdr.LAST;
812
813 END IF;
814 /* end if header records already exist */
815
816 EXCEPTION
817 WHEN NO_DATA_FOUND THEN
818 NULL;
819 /* no data found is not an error. Not all items will have specs */
820 WHEN OTHERS THEN
821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
822 /* exception defined in Populate_Coa_Data */
823
824 END Look_For_CoC_Specs;
825 /* end procedure */
826
827
828 /*###############################################################
829 # NAME
830 # Populate_Details
831 # SYNOPSIS
832 # proc Populate_Details
833 # parms header table IN
834 # details table IN OUT details will have data if this
835 # is non-sales data CoC
836 # DESCRIPTION
837 # get results info
838 #
839 # HISTORY
840 # 12jun2001 James Bernard Bug 1810652
841 # In the for cursors tbl_hdr.FIRST and tbl_hdr.LAST are
845 # definition.
842 # replaced with NVL(tbl_hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
843 # Added 'c_item_id is NULL or' condition in the where clause
844 # of the select statement in the get_cust_rslt_info cursor
846 # Added 'c_item_id is NULL or' and 'c_lot_id is NULL or'
847 # conditions in the where clause of the select statement
848 # in the get_item_rslt_info cursor definition.
849 ################################################################*/
850 PROCEDURE Populate_Details (tbl_hdr IN t_coa_header_tbl,
851 tbl_dtl IN OUT NOCOPY t_coa_detail_tbl) IS
852
853 /* BEGIN BUG#1810652 James Bernard */
854 /* Added 'c_item_id is NULL or' in the where clause */
855 CURSOR get_cust_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
856 c_item_id ic_item_mst.item_id%TYPE,
857 c_lot_id ic_lots_mst.lot_id%TYPE,
858 c_cust_id op_cust_mst.cust_id%TYPE) IS
859 select gr.result_id qc_result_id,
860 gst.spec_id qc_spec_id,
861 gt.test_code assay_code,
862 gr.result_date result_date,
863 decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
864 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
865 specification,
866 gst.test_uom uom,
867 gst.text_code spec_text_code,
868 gr.text_code rslt_text_code
869 from gmd_samples gs,
870 gmd_results gr,
871 gmd_qc_tests_b gt,
872 gmd_spec_results gsr,
873 gmd_spec_tests_b gst ,
874 gmd_sampling_events gse,
875 gmd_event_spec_disp ges,
876 gmd_specifications_b gsb,
877 gmd_sample_spec_disp gss
878
879 where gs.sample_id = gr.sample_id
880 and gse.sampling_event_id = gs.sampling_event_id
881 and gse.sampling_event_id = ges.sampling_event_id
882 and ges.spec_used_for_lot_attrib_ind ='Y'
883 and ges.spec_id(+) = gst.spec_id
884 and gst.test_id(+) = gt.test_id
885 and gs.sample_id = gr.sample_id
886 and gr.result_date is not null
887 and gr.result_id = gsr.result_id
888 and gr.test_id = gt.test_id
889 and ges.event_spec_disp_id = gsr.event_spec_disp_id
890 and ges.event_spec_disp_id = gss.event_spec_disp_id
891 and gsb.spec_id = ges.spec_id
892 and gss.disposition = '4A' -- ACCEPT
893 and nvl(gsr.evaluation_ind,'N') in ('0A')
894 and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
895 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
896 and gs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
897 and (c_lot_id is NULL or gs.lot_id = c_lot_id)
898 and (c_item_id is NULL or gs.item_id = c_item_id)
899 and gs.orgn_code = c_orgn_code
900 and gs.delete_mark = 0
901 ;
902 /*END BUG#1810652 */
903
904 /* use the next cursor if no rows returned from get_cust_rslt_info */
905 /* BEGIN BUG#1810652 James Bernard */
906 /* Added 'c_item_id is NULL or' and 'c_lot_id is NULL or' in the where clause */
907 CURSOR get_item_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
908 c_item_id ic_item_mst.item_id%TYPE,
909 c_lot_id ic_lots_mst.lot_id%TYPE) IS
910 select gr.result_id qc_result_id,
911 gst.spec_id qc_spec_id,
912 gt.test_code assay_code,
913 gr.result_date result_date,
914 decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
915 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
916 specification,
917 gst.test_uom uom,
918 gst.text_code spec_text_code,
919 gr.text_code rslt_text_code
920 from gmd_samples gs,
921 gmd_results gr,
922 gmd_qc_tests_b gt,
923 gmd_spec_results gsr,
924 gmd_spec_tests_b gst ,
925 gmd_sampling_events gse,
926 gmd_event_spec_disp ges,
927 gmd_specifications_b gsb,
928 gmd_sample_spec_disp gss
929
930 where gs.sample_id = gr.sample_id
931 and gse.sampling_event_id = gs.sampling_event_id
932 and gse.sampling_event_id = ges.sampling_event_id
933 and ges.spec_used_for_lot_attrib_ind ='Y'
934 and ges.spec_id(+) = gst.spec_id
935 and gst.test_id(+) = gt.test_id
936 and gs.sample_id = gr.sample_id
937 and gr.result_date is not null
938 and gr.result_id = gsr.result_id
939 and gr.test_id = gt.test_id
940 and ges.event_spec_disp_id = gsr.event_spec_disp_id
941 and ges.event_spec_disp_id = gss.event_spec_disp_id
942 and gsb.spec_id = ges.spec_id
943 and gss.disposition = '4A' -- ACCEPT
944 and nvl(gsr.evaluation_ind,'N') = '0A'
945 and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
946 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
947 and (c_lot_id is NULL or gs.lot_id = c_lot_id)
948 and (c_item_id is NULL or gs.item_id = c_item_id)
949 and gs.orgn_code = c_orgn_code
950 and gs.delete_mark = 0
951 and gs.cust_id is NULL
952 and gs.batch_id is NULL
956 and gs.supplier_id is NULL
953 and gs.formula_id is NULL
954 and gs.routing_id is NULL
955 and gs.oprn_id is NULL
957 ;
958
959
960
961
962 /*END BUG#1810652 */
963
964
965 CURSOR get_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
966 c_cust_id op_cust_mst.cust_id%TYPE,
967 c_orgn_code gmd_customer_spec_vrs.ORGN_CODE%TYPE,
968 c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
969 IS
970 select gsb.spec_id qc_spec_id,
971 decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
972 specification,
973 gcs.text_code spec_text_code
974 from gmd_specifications_b gsb,
975 gmd_customer_spec_vrs gcs,
976 gmd_spec_tests_b gst,
977 gmd_qc_tests_b gt
978 where gsb.spec_id = gcs.spec_id
979 and gsb.spec_id = gst.spec_id
980 and gst.test_id = gt.test_id
981 and gsb.item_id = c_item_id
982 and gt.test_code = c_assay_code
983 and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
984 and gcs.orgn_code = c_orgn_code
985 and gsb.delete_mark= 0;
986
987 CURSOR get_global_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
988 c_cust_id op_cust_mst.cust_id%TYPE,
989 c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
990 IS
991 select gsb.spec_id qc_spec_id,
992 decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
993 specification,
994 gcs.text_code spec_text_code
995 from gmd_specifications_b gsb,
996 gmd_customer_spec_vrs gcs,
997 gmd_spec_tests_b gst,
998 gmd_qc_tests_b gt
999 where gsb.spec_id = gcs.spec_id
1000 and gsb.spec_id = gst.spec_id
1001 and gst.test_id = gt.test_id
1002 and gsb.item_id = c_item_id
1003 and gt.test_code = c_assay_code
1004 and gcs.cust_id = (select of_cust_id from op_cust_mst where cust_id = c_cust_id)
1005 and gcs.orgn_code is NULL
1006 and gsb.delete_mark= 0;
1007
1008
1009 CURSOR get_assay_info (c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
1010 IS
1011 select gt.test_desc assay_desc
1012 from gmd_qc_tests gt
1013 where gt.test_code = c_assay_code
1014 and gt.delete_mark = 0;
1015
1016 tbl_ndx BINARY_INTEGER := 0;
1017 /* index for pl/sql table */
1018 v_gmd_coa_id BINARY_INTEGER;
1019 dtl_counter NUMBER := 0;
1020 l_dummy_param_rec t_coa_parameters;
1021 l_tmp_hdr_tbl t_coa_header_tbl;
1022 found_a_row BOOLEAN;
1023
1024 /* *********************************************************************
1025 * select from result table looking for customer
1026 * if no results, select from result table for item/loc result
1027 * if no item/loc result, then end procedure, else
1028 * look for a customer spec
1029 * if spec_id <> cust spec then look for global cust spec
1030 * get assay description
1031 * *********************************************************************/
1032
1033 BEGIN /* begin Populate_Details */
1034
1035 IF v_report_title = 'COA' THEN
1036
1037 /*BEGIN BUG#1810652 James Bernard */
1038 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
1039 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0). */
1040 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
1041 /*END BUG#1810652 */
1042
1043 found_a_row := FALSE;
1044
1045 begin
1046 /* look for customer-specific result */
1047 FOR cust_rslt_cur_rec IN get_cust_rslt_info
1048 (tbl_hdr(loop_counter).orgn_code,
1049 tbl_hdr(loop_counter).item_id,
1050 tbl_hdr(loop_counter).lot_id,
1051 tbl_hdr(loop_counter).cust_id) LOOP
1052 found_a_row := TRUE;
1053 tbl_ndx := tbl_ndx + 1;
1054 tbl_dtl(tbl_ndx).gmd_coa_id := tbl_hdr(loop_counter).gmd_coa_id;
1055 tbl_dtl(tbl_ndx).qc_result_id := cust_rslt_cur_rec.qc_result_id;
1056 tbl_dtl(tbl_ndx).result_date := cust_rslt_cur_rec.result_date;
1057 tbl_dtl(tbl_ndx).qc_spec_id := cust_rslt_cur_rec.qc_spec_id;
1058 tbl_dtl(tbl_ndx).assay_code := cust_rslt_cur_rec.assay_code;
1059 tbl_dtl(tbl_ndx).result := cust_rslt_cur_rec.result;
1060 tbl_dtl(tbl_ndx).specification := cust_rslt_cur_rec.specification;
1061 tbl_dtl(tbl_ndx).uom := cust_rslt_cur_rec.uom;
1062 tbl_dtl(tbl_ndx).rslt_text_code := cust_rslt_cur_rec.rslt_text_code;
1063 tbl_dtl(tbl_ndx).spec_text_code := cust_rslt_cur_rec.spec_text_code;
1064
1065 END LOOP ;
1066 EXCEPTION
1067 when NO_DATA_FOUND then
1068 NULL;
1069 end;
1070 /* end looking for customer-specific result */
1071
1072 IF NOT(found_a_row) THEN
1073 begin
1074 /* look for item/loc result */
1075 FOR item_rslt_cur_rec IN get_item_rslt_info
1076 (tbl_hdr(loop_counter).orgn_code,
1077 tbl_hdr(loop_counter).item_id,
1078 tbl_hdr(loop_counter).lot_id) LOOP
1079 found_a_row := TRUE;
1080 tbl_ndx := tbl_ndx + 1;
1081 tbl_dtl(tbl_ndx).gmd_coa_id := tbl_hdr(loop_counter).gmd_coa_id;
1085 tbl_dtl(tbl_ndx).assay_code := item_rslt_cur_rec.assay_code;
1082 tbl_dtl(tbl_ndx).qc_result_id := item_rslt_cur_rec.qc_result_id;
1083 tbl_dtl(tbl_ndx).result_date := item_rslt_cur_rec.result_date;
1084 tbl_dtl(tbl_ndx).qc_spec_id := item_rslt_cur_rec.qc_spec_id;
1086 tbl_dtl(tbl_ndx).result := item_rslt_cur_rec.result;
1087 tbl_dtl(tbl_ndx).specification := item_rslt_cur_rec.specification;
1088 tbl_dtl(tbl_ndx).uom := item_rslt_cur_rec.uom;
1089 tbl_dtl(tbl_ndx).rslt_text_code := item_rslt_cur_rec.rslt_text_code;
1090 tbl_dtl(tbl_ndx).spec_text_code := item_rslt_cur_rec.spec_text_code;
1091
1092 END LOOP ;
1093 EXCEPTION
1094 when NO_DATA_FOUND then
1095 NULL;
1096 end;
1097 /* end looking for item/loc-specific result */
1098 END IF;
1099 /* end if no cust results, look for item results */
1100 END LOOP;
1101 /* end looping through records in header table */
1102
1103 IF tbl_ndx = 0 THEN
1104 /* Look_For_CoC_Specs needs a header table which is IN/OUT. */
1105 /* But tbl_hdr was passed to Populate_Details as IN. */
1106 /* Look_For_Coc_Specs should not change hdr table with sales- */
1107 /* order-no-results scenario, so send a temporary,writable copy. */
1108 l_tmp_hdr_tbl := tbl_hdr;
1109 GMD_COA_DATA_NEW.Look_For_CoC_Specs
1110 (l_dummy_param_rec,
1111 tbl_ndx,
1112 l_tmp_hdr_tbl,
1113 tbl_dtl);
1114 IF tbl_dtl.FIRST is not NULL THEN
1115 v_report_title := 'COC';
1116 END IF;
1117 /* end if there are any rows in tbl_dtl */
1118 END IF;
1119 /* no results found, look for CoC */
1120 /* this IF and call to Look for CoC Specs should only */
1121 /* happen when sales/shipping info is given and no */
1122 /* results exists. */
1123 ELSE
1124 tbl_ndx := 1;
1125 /* if CoC, set flag = 1 so next section executes for CoC */
1126 END IF;
1127 /* end if this is CoA (not CoC) */
1128
1129
1130 /* *********************************************************************** --
1131 * now look for customer specs
1132 * if anything about the cust spec cursors or the detail table changes,
1133 * also modify CoC code in Look_For_CoC_Specs procedure */
1134
1135 IF tbl_ndx > 0 THEN
1136
1137 IF tbl_dtl.EXISTS(1) THEN
1138 /* if sales/shipping item has no specs, there could be rows in tbl_hdr, */
1139 /* but nothing to be found for details. If so, skip this section. */
1140 /* This section assumes results have been found. */
1141 /* Exception WHEN-NO-DATA-FOUND is not handling th is case. */
1142
1143 /*BEGIN BUG#1810652 James Bernard */
1144 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
1145 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0). */
1146 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
1147 /*END BUG#1810652 */
1148
1149 IF v_report_title = 'COA' THEN
1150 /* if CoC, need to loop thru tbl_dtl for assay desc, */
1151 /* but not look for specs again */
1152 /* use v_gmd_coa_id rather than tbl_dtl(loop_counter).gmd_coa_id as index */
1153 /* to header table. Hopefully makes codes more readable. */
1154 v_gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
1155
1156 FOR cust_spec_rec IN get_cust_spec (tbl_hdr(v_gmd_coa_id).item_id,
1157 tbl_hdr(v_gmd_coa_id).cust_id,
1158 tbl_hdr(v_gmd_coa_id).orgn_code,
1159 tbl_dtl(loop_counter).assay_code)
1160 LOOP
1161 dtl_counter := dtl_counter + 1;
1162 tbl_dtl(loop_counter).qc_spec_id := cust_spec_rec.qc_spec_id;
1163 tbl_dtl(loop_counter).specification := cust_spec_rec.specification;
1164 tbl_dtl(loop_counter).spec_text_code := cust_spec_rec.spec_text_code;
1165 END LOOP;
1166 IF dtl_counter = 0 THEN
1167 FOR cust_spec_rec IN get_global_cust_spec
1168 (tbl_hdr(v_gmd_coa_id).item_id,
1169 tbl_hdr(v_gmd_coa_id).cust_id,
1170 tbl_dtl(loop_counter).assay_code)
1171 LOOP
1172 dtl_counter := dtl_counter + 1;
1173 tbl_dtl(loop_counter).qc_spec_id := cust_spec_rec.qc_spec_id;
1174 tbl_dtl(loop_counter).specification := cust_spec_rec.specification;
1175 tbl_dtl(loop_counter).spec_text_code:= cust_spec_rec.spec_text_code;
1176 END LOOP;
1177 END IF;
1178 /* end if no cust spec found, look for global spec */
1179 /* if there is only an item specification, it should already have */
1180 /* been associated with result in result table (qrm.qc_spec_id). */
1181 /* That value, and specification and text code would have been */
1182 /* pulled in cursors get_cust_rslt_info or get_item_rslt_info. */
1183 END IF;
1184 /* end if this is CoA */
1185
1186 /* ****************************************************************** -- */
1187 /* now fill in lookup columns (assay_description) */
1188
1189 /* get assay description from qc_assy_typ */
1190
1191 IF tbl_dtl(loop_counter).assay_code is not NULL THEN
1192 FOR assay_cur_rec IN get_assay_info (tbl_dtl(loop_counter).assay_code)
1193 LOOP
1194 tbl_dtl(loop_counter).assay_desc := assay_cur_rec.assay_desc;
1195 END LOOP;
1196 /* end getting assay from qc_assy_typ */
1197 END IF;
1198 /* end checking if assay code has a value */
1199 END LOOP;
1200 /* end loop thru detail to get specs and/or fill in */
1201 /* lookup columns */
1202 END IF;
1203 /* end if there are rows in detail which need specs matched up */
1204 ELSE
1205 tbl_dtl := empty_detail;
1206 END IF;
1207 /* end if any result records found */
1208
1209
1210 EXCEPTION
1211 WHEN NO_DATA_FOUND THEN
1212 NULL;
1213
1214 WHEN OTHERS THEN
1215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1216 /* exception defined in Populate_Coa_Data */
1217
1218 END Populate_Details;
1219
1220
1221 /*###############################################################
1222 # NAME
1223 # Populate_Text
1224 # SYNOPSIS
1225 # proc Populate_Text
1226 # parms detail table IN text table OUT
1227 # DESCRIPTION
1231 # In the For Cursors tbl_dtl.FIRST and tbl_dtl.LAST is
1228 # get results text info
1229 # HISTORY
1230 # 12jun2001 James Bernard Bug 1810652
1232 # replaced by NVL(tbl_dtl.FIRST,0) and NVL(tbl_dtl.LAST,0)
1233 ################################################################*/
1234 PROCEDURE Populate_Text (tbl_dtl IN t_coa_detail_tbl,
1235 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
1236 tbl_rslt_text OUT NOCOPY t_coa_text_tbl) IS
1237
1238 CURSOR get_text_info (c_text_code qc_text_tbl.text_code%TYPE) IS
1239 select paragraph_code,
1240 line_no,
1241 text
1242 from qc_text_tbl
1243 where text_code = c_text_code
1244 and line_no > 0
1245 order by paragraph_code, line_no ;
1246
1247 tbl_ndx BINARY_INTEGER := 0;
1248 /* index for pl/sql table */
1249
1250 BEGIN
1251
1252 /* loop through detail records, get text for results and text for specs */
1253
1254 tbl_ndx := 0;
1255
1256 /*BEGIN BUG#1810652 James Bernard */
1257 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
1258 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
1259 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
1260 /*END BUG#1810652 */
1261 IF (tbl_dtl(loop_counter).spec_text_code) is not NULL THEN
1262 FOR text_cur_rec IN get_text_info (tbl_dtl(loop_counter).spec_text_code)
1263 LOOP
1264 tbl_ndx := tbl_ndx + 1;
1265
1266 tbl_spec_text(tbl_ndx).gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
1267 tbl_spec_text(tbl_ndx).text_code:=tbl_dtl(loop_counter).spec_text_code;
1268 tbl_spec_text(tbl_ndx).paragraph_code := text_cur_rec.paragraph_code;
1269 tbl_spec_text(tbl_ndx).line_no := text_cur_rec.line_no;
1270 tbl_spec_text(tbl_ndx).text := text_cur_rec.text;
1271 END LOOP;
1272 /* end cursor to get text */
1273 END IF;
1274 END LOOP;
1275 /* end looping through detail records */
1276
1277 tbl_ndx := 0;
1278
1279 /*BEGIN BUG#1810652 James Bernard */
1280 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
1281 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
1282 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
1283 /*END BUG#1810652 */
1284 IF (tbl_dtl(loop_counter).rslt_text_code) is not NULL THEN
1285 FOR text_cur_rec IN get_text_info (tbl_dtl(loop_counter).rslt_text_code)
1286 LOOP
1287 tbl_ndx := tbl_ndx + 1;
1288
1289 tbl_rslt_text(tbl_ndx).gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
1290 tbl_rslt_text(tbl_ndx).text_code:=tbl_dtl(loop_counter).rslt_text_code;
1291 tbl_rslt_text(tbl_ndx).paragraph_code := text_cur_rec.paragraph_code;
1292 tbl_rslt_text(tbl_ndx).line_no := text_cur_rec.line_no;
1293 tbl_rslt_text(tbl_ndx).text := text_cur_rec.text;
1294
1295 END LOOP;
1296 /* end cursor to get text */
1297 END IF;
1298 END LOOP;
1299 /* end looping through detail records */
1300
1301 EXCEPTION
1302 WHEN NO_DATA_FOUND THEN
1303 NULL;
1304 /* there may not be text information for this result rec */
1305
1306 WHEN OTHERS THEN
1307 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1308
1309 END Populate_Text;
1310
1311
1312 /*###############################################################
1313 # NAME
1314 # Populate_CoA_Data
1315 # SYNOPSIS
1316 # proc Populate_CoA_Data
1317 # parms event
1318 # There is no Orgn_Code in where clauses for cursors because
1319 # parameter is ID which identifies orgn code.
1320 # (ie would need orgn_code if we had sales_order_no, not id)
1321 # DESCRIPTION
1322 # populate gmd_coa_coa_hdr with records
1323 # HISTORY
1324 # 12jun2001 James Bernard Bug 1810652
1325 # Commented a line which was not properly commented in the
1326 # while <<order loop>>.
1327 # In the For cursors tbl_hdr.FIRST and tbl_hdr.LAST are
1328 # replaced with NVL(tbl_hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
1329 ################################################################*/
1330 PROCEDURE Populate_CoA_Data (
1331 p_api_version In NUMBER,
1332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1333 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1334 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1335 rec_param IN t_coa_parameters,
1336 x_return_status OUT NOCOPY VARCHAR2,
1337 x_msg_count OUT NOCOPY NUMBER,
1338 x_msg_data OUT NOCOPY VARCHAR2,
1339 tbl_hdr OUT NOCOPY t_coa_header_tbl,
1340 tbl_dtl OUT NOCOPY t_coa_detail_tbl,
1341 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
1342 tbl_rslt_text OUT NOCOPY t_coa_text_tbl) IS
1343
1344 CURSOR get_order_info
1345 (c_order_id NUMBER, c_from_shipdate DATE,
1346 c_to_shipdate DATE, c_cust_id NUMBER,
1347 c_bol_id NUMBER, c_item_id NUMBER,
1348 c_lot_id NUMBER, c_whse_code VARCHAR2)
1349 IS
1350 select ooh.order_id,
1351 ood.line_id,
1352 ooh.orgn_code,
1353 ooh.order_no,
1354 ooh.custpo_no,
1355 ood.sched_shipdate,
1356 ood.actual_shipdate,
1357 ood.shipcust_id,
1358 ood.bol_id,
1359 ood.item_id,
1360 ood.from_whse,
1361 ood.generic_id,
1362 ood.order_qty1,
1363 ood.order_qty2,
1364 ood.order_um1,
1365 ood.order_um2,
1366 ood.ship_qty1,
1367 ood.ship_qty2,
1368 ood.alloc_qty
1369 from
1370 op_ordr_hdr ooh,
1371 op_ordr_dtl ood
1372 where
1373 ooh.order_id = ood.order_id
1374 and (c_from_shipdate is NULL or
1375 ( (ood.sched_shipdate between c_from_shipdate and c_to_shipdate)
1376 OR
1377 (ood.actual_shipdate between c_from_shipdate and c_to_shipdate)
1378 ))
1379 and (c_cust_id IS NULL OR ood.shipcust_id = c_cust_id)
1380 and (c_order_id IS NULL OR ood.order_id = c_order_id)
1381 and (c_bol_id IS NULL OR ood.bol_id = c_bol_id)
1382 and (c_item_id IS NULL OR ood.item_id = c_item_id)
1383 and ood.ship_status <> -1
1384 and ood.delete_mark = 0
1385 ;
1386
1387
1388 CURSOR get_gnrc_info (c_generic_id op_gnrc_itm.generic_id%TYPE) IS
1389 SELECT generic_item,
1390 generic_desc
1391 FROM
1392 op_gnrc_itm
1393 WHERE generic_id = c_generic_id
1394 and delete_mark = 0
1395 ;
1396
1397 CURSOR get_bol_info (c_bol_id op_bill_lad.bol_no%TYPE) IS
1398 SELECT bol_no
1399 FROM
1400 op_bill_lad
1401 WHERE bol_id = c_bol_id
1402 and delete_mark = 0
1406 SELECT item_no,
1403 ;
1404
1405 CURSOR get_item_info (c_item_id ic_item_mst.item_id%TYPE) IS
1407 item_desc1
1408 FROM
1409 ic_item_mst
1410 WHERE item_id = c_item_id
1411 and delete_mark = 0
1412 ;
1413
1414 CURSOR get_cust_info (c_cust_id op_cust_mst.cust_id%TYPE) IS
1415 SELECT
1416 custsort_no cust_no,
1417 cust_name cust_name
1418 from op_cust_mst
1419 WHERE cust_id = c_cust_id
1420 AND delete_mark= 0;
1421
1422
1423 CURSOR get_whse_info (c_whse_code ic_whse_mst.WHSE_CODE%TYPE) IS
1424 SELECT whse_name
1425 FROM
1426 ic_whse_mst
1427 WHERE
1428 whse_code = c_whse_code
1429 and delete_mark = 0
1430 ;
1431
1432 CURSOR get_lot_tran (c_line_id ic_tran_pnd.line_id%TYPE) IS
1433 SELECT itp.lot_id, itp.whse_code, itp.location
1434 FROM
1435 ic_tran_pnd itp
1436 WHERE
1437 itp.doc_type = 'OPSO'
1438 AND itp.completed_ind <> -1
1439 AND itp.line_id = c_line_id
1440 and itp.delete_mark = 0
1441 ;
1442
1443 CURSOR get_lot_info (c_lot_id ic_lots_mst.lot_id%TYPE) IS
1444 SELECT ilm.lot_no,
1445 ilm.lot_desc,
1446 ilm.sublot_no
1447 FROM
1448 ic_lots_mst ilm
1449 WHERE
1450 ilm.lot_id = c_lot_id
1451 and ilm.delete_mark = 0
1452 ;
1453
1454 ord_cur_rec get_order_info % ROWTYPE;
1455
1456 tbl_ndx BINARY_INTEGER := 0;
1457 /* index for pl/sql table */
1458 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CoA_Data';
1459 l_api_version CONSTANT NUMBER := 1.6;
1460 /* **************************************************************
1461 * Procedure Look_For_Results
1462 * Used when there is NO shipping data (order, bol, ship num)
1463 * Cursor search for lot because non-lot controlled items only
1464 * return CoC (specs only).
1465 * HISTORY
1466 * 12jun2001 James Bernard Bug 1810652
1467 * Modified the where clause of the select statement in the
1468 * get_qc_cust_rslt cursor definition to retrieve records when
1469 * c_lot_id and c_item_id are null.
1470 * Modified the where clause of the select statement in the
1471 * get_qc_item_rslt cursor definition to retrieve records when
1472 * c_lot_id and c_item_id are null.
1473 * **************************************************************/
1474 PROCEDURE Look_For_Results (
1475 tbl_ndx OUT NOCOPY BINARY_INTEGER)
1476 IS
1477 /* BEGIN BUG#1810652 James Bernard */
1478 /* Added 'c_lot_id is NULL or' and 'c_item_id is NULL or' */
1479 /* conditions in the where clause */
1480 CURSOR get_qc_cust_rslt (c_cust_id NUMBER, c_item_id NUMBER,
1481 c_lot_id NUMBER, c_orgn_code VARCHAR2)
1482 IS
1483 select distinct gs.orgn_code,
1484 gs.cust_id,
1485 gs.item_id,
1486 gs.whse_code,
1487 gs.lot_id
1488 from
1489 gmd_samples gs,
1490 gmd_results gr,
1491 gmd_spec_results gsr,
1492 gmd_sampling_events gse,
1493 gmd_event_spec_disp ges,
1494 gmd_spec_tests_b gst,
1495 gmd_sample_spec_disp gss
1496 where gse.sampling_event_id = gs.sampling_event_id
1497 and gse.sampling_event_id = ges.sampling_event_id
1498 and ges.spec_used_for_lot_attrib_ind ='Y'
1499 and gs.sample_id = gr.sample_id
1500 and gr.result_id = gsr.result_id
1501 and ges.event_spec_disp_id = gsr.event_spec_disp_id
1502 and ges.event_spec_disp_id = gss.event_spec_disp_id(+)
1503 and ges.spec_id(+) = gst.spec_id
1504 and gst.test_id(+) = gr.test_id
1505 and gss.disposition = '4A' -- ACCEPT
1506 and nvl(gsr.evaluation_ind,'N') = '0A'
1507 and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
1508 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
1509 and (c_lot_id is NULL or gs.lot_id = c_lot_id)
1513 /* END BUG#1810652 */
1510 and (c_item_id is NULL or gs.item_id = c_item_id)
1511 and gs.orgn_code = c_orgn_code
1512 and gs.delete_mark = 0;
1514
1515 /* BEGIN BUG#1810652 James Bernard */
1516 /* Added 'c_lot_id is NULL or' and 'c_item_id is NULL or' */
1517 /* conditions in the where clause */
1518 CURSOR get_qc_item_rslt (c_item_id NUMBER, c_lot_id NUMBER,
1519 c_whse_code VARCHAR2, c_orgn_code VARCHAR2)
1520 IS
1521 select distinct gs.orgn_code,
1522 gs.cust_id,
1523 gs.item_id,
1524 gs.whse_code,
1525 gs.lot_id
1526 from
1527 gmd_samples gs,
1528 gmd_results gr,
1529 gmd_spec_results gsr,
1530 gmd_sampling_events gse,
1531 gmd_event_spec_disp ges,
1532 gmd_spec_tests_b gst,
1533 gmd_sample_spec_disp gss
1534 where
1535 gs.cust_id is NULL
1536 and gs.batch_id is NULL
1537 and gs.formula_id is NULL
1538 and gs.routing_id is NULL
1539 and gs.oprn_id is NULL
1540 and gs.supplier_id is NULL
1541 and gse.sampling_event_id = gs.sampling_event_id
1542 and gse.sampling_event_id = ges.sampling_event_id
1543 and ges.spec_used_for_lot_attrib_ind ='Y'
1544 and gs.sample_id = gr.sample_id
1545 and gr.result_id = gsr.result_id
1546 and ges.event_spec_disp_id = gsr.event_spec_disp_id
1547 and ges.event_spec_disp_id = gss.event_spec_disp_id(+)
1548 and ges.spec_id(+) = gst.spec_id
1549 and gst.test_id(+) = gr.test_id
1550 and gss.disposition = '4A' -- ACCEPT
1551 and nvl(gsr.evaluation_ind,'N') = '0A'
1552 and decode(nvl(gst.print_on_coa_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N',
1553 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','2R','Y','N'),'N') = 'Y'
1554 and (c_lot_id is NULL or gs.lot_id = c_lot_id)
1555 and (c_item_id is NULL or gs.item_id = c_item_id)
1556 and (c_whse_code is NULL OR gs.whse_code = c_whse_code)
1557 and gs.orgn_code = c_orgn_code
1558 and gs.delete_mark = 0
1559 ;
1560
1561 /* END BUG#1810652 */
1562 BEGIN
1563 /* if customer parameter was given, look for customer-specific result */
1564 /* if no customer results found, look for item/loc/lot-specific result */
1565
1566 tbl_ndx := 0;
1567
1568 IF rec_param.cust_id is not NULL THEN
1569 FOR qc_cur_rec IN get_qc_cust_rslt (rec_param.cust_id,
1570 rec_param.item_id,
1571 rec_param.lot_id,
1572 rec_param.orgn_code) LOOP
1573 tbl_ndx := tbl_ndx + 1;
1574 tbl_hdr(tbl_ndx).gmd_coa_id := tbl_ndx;
1575 tbl_hdr(tbl_ndx).orgn_code := qc_cur_rec.orgn_code;
1576 tbl_hdr(tbl_ndx).cust_id := qc_cur_rec.cust_id;
1577 tbl_hdr(tbl_ndx).item_id := qc_cur_rec.item_id;
1578 tbl_hdr(tbl_ndx).whse_code := qc_cur_rec.whse_code;
1579 tbl_hdr(tbl_ndx).lot_id := qc_cur_rec.lot_id;
1580 tbl_hdr(tbl_ndx).report_title := v_report_title;
1581
1582 /* fill in whse, customer name, lot no at end of main code */
1583
1584 END LOOP;
1585 /* end get item from cust rslt rather than sales order table */
1586 END IF;
1587 /* end if cust parameter has value, then look for */
1588 /* cust-specific result */
1592
1589 IF tbl_ndx = 0 THEN
1590 /* no customer specific rslt found, or no customer id given as */
1591 /* parameter, look for item/lot/loc rslt */
1593 FOR qc_cur_rec IN get_qc_item_rslt (rec_param.item_id,
1594 rec_param.lot_id,
1595 rec_param.whse_code,
1596 rec_param.orgn_code) LOOP
1597 tbl_ndx := tbl_ndx + 1;
1598 tbl_hdr(tbl_ndx).gmd_coa_id := tbl_ndx;
1599 tbl_hdr(tbl_ndx).orgn_code := qc_cur_rec.orgn_code;
1600 tbl_hdr(tbl_ndx).cust_id:=nvl(qc_cur_rec.cust_id, rec_param.CUST_ID);
1601 tbl_hdr(tbl_ndx).item_id := qc_cur_rec.item_id;
1602 tbl_hdr(tbl_ndx).whse_code := qc_cur_rec.whse_code;
1603 tbl_hdr(tbl_ndx).lot_id := qc_cur_rec.lot_id;
1604 tbl_hdr(tbl_ndx).report_title := v_report_title;
1605
1606 /* fill in whse, customer name, lot no at end of main code */
1607
1608 END LOOP;
1609 /* end get item from item/lot/loc rslt */
1610 END IF;
1611 /* end if no cust-specific result, then look for */
1612 /* item/loc/lot results */
1613 END Look_For_Results;
1614 /* end sub procedure */
1615
1616 /* **************************** main code **************************** */
1617 BEGIN
1618
1619 /* Do API standard code for savepoint, messages, initialize return status */
1620 SAVEPOINT Populate_CoA_Data_SAVE;
1621 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1622 l_api_name, G_PKG_NAME) THEN
1623 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1624 END IF;
1625 IF FND_API.to_Boolean (p_init_msg_list) THEN
1626 FND_MSG_PUB.initialize;
1627 END IF;
1628 x_return_status := FND_API.G_RET_STS_SUCCESS;
1629 v_report_title := 'COA';
1630
1631 IF rec_param.order_id is not NULL or rec_param.bol_id is not NULL
1632 or rec_param.from_shipdate is not NULL THEN
1633 /*- or rec_param.cust_po is not NULL */
1634 OPEN get_order_info (rec_param.order_id,
1635 rec_param.from_shipdate, rec_param.to_shipdate,
1636 rec_param.cust_id, rec_param.bol_id,
1637 rec_param.item_id, rec_param.lot_id,
1638 rec_param.whse_code);
1639 FETCH get_order_info INTO ord_cur_rec;
1640
1641 <<order_loop>>
1642 WHILE ( get_order_info % FOUND ) LOOP
1643 tbl_ndx := tbl_ndx + 1;
1644 /* BEGIN BUG#1810652 James Bernard */
1645 /* Properly commented the next line. Prior to the fix there */
1646 /* was only the opening slash and asterisk */
1647 /* replace with next_val.sequence */
1648 /* END BUG#1810652 */
1649 tbl_hdr(tbl_ndx).gmd_coa_id := tbl_ndx;
1650 tbl_hdr(tbl_ndx).order_id := ord_cur_rec.order_id;
1651 tbl_hdr(tbl_ndx).line_id := ord_cur_rec.line_id;
1652 tbl_hdr(tbl_ndx).orgn_code := ord_cur_rec.orgn_code;
1653 tbl_hdr(tbl_ndx).order_no := ord_cur_rec.order_no;
1654 tbl_hdr(tbl_ndx).custpo_no := ord_cur_rec.custpo_no;
1655 tbl_hdr(tbl_ndx).cust_id := ord_cur_rec.shipcust_id;
1656 tbl_hdr(tbl_ndx).bol_id := ord_cur_rec.bol_id;
1657 tbl_hdr(tbl_ndx).item_id := ord_cur_rec.item_id;
1658 tbl_hdr(tbl_ndx).whse_code := ord_cur_rec.from_whse;
1659 tbl_hdr(tbl_ndx).order_qty1 := ord_cur_rec.order_qty1;
1660 tbl_hdr(tbl_ndx).order_qty2 := ord_cur_rec.order_qty2;
1661 tbl_hdr(tbl_ndx).order_um1 := ord_cur_rec.order_um1;
1662 tbl_hdr(tbl_ndx).order_um2 := ord_cur_rec.order_um2;
1663 tbl_hdr(tbl_ndx).ship_qty1 := ord_cur_rec.ship_qty1;
1664 tbl_hdr(tbl_ndx).ship_qty2 := ord_cur_rec.ship_qty2;
1665
1666 /* if there is an actual date, use it; otherwise use scheduled date */
1667 IF ord_cur_rec.actual_shipdate is NULL THEN
1668 tbl_hdr(tbl_ndx).shipdate := ord_cur_rec.sched_shipdate;
1669 ELSE
1670 tbl_hdr(tbl_ndx).shipdate := ord_cur_rec.actual_shipdate;
1671 END IF;
1672
1673 /* if generic id exists in sales order record, get item no and desc */
1674 /* from op_gnrc_itm, else get item no and desc from ic_item_mst */
1675
1676 IF ord_cur_rec.generic_id is not NULL THEN
1677 FOR gnrc_cur_rec IN get_gnrc_info (ord_cur_rec.generic_id) LOOP
1678 tbl_hdr(tbl_ndx).item_no := gnrc_cur_rec.generic_item;
1679 tbl_hdr(tbl_ndx).item_desc := gnrc_cur_rec.generic_desc;
1680 END LOOP;
1681 /* end getting generic item info from table */
1682 ELSE
1683 FOR item_cur_rec IN get_item_info (ord_cur_rec.item_id) LOOP
1684 tbl_hdr(tbl_ndx).item_no := item_cur_rec.item_no;
1685 tbl_hdr(tbl_ndx).item_desc := item_cur_rec.item_desc1;
1689 /* end if generic_id has a value */
1686 END LOOP;
1687 /* end getting item info from ic_item_mst */
1688 END IF;
1690 /* fill in whse, customer name, bol no at end of code */
1691
1692 /* get lot id from ic_tran_pnd */
1693 /* get lot no and name and sublot no from ic_lot_mst at end of code */
1694 /* (2 steps so non-sales-order loop can also use get_lot_info) */
1695
1696 IF ord_cur_rec.alloc_qty > 0 THEN
1697 FOR lot_cur_rec IN get_lot_tran (ord_cur_rec.line_id) LOOP
1698 tbl_hdr(tbl_ndx).lot_id := lot_cur_rec.lot_id;
1699 END LOOP;
1700 /* end getting lot id from ic_tran_pnd */
1701 END IF;
1702 /* end checking if alloc qty > 0 */
1703 /* report title = COA or COC */
1704 tbl_hdr(tbl_ndx).report_title := v_report_title;
1705
1706 FETCH get_order_info INTO ord_cur_rec;
1707
1708 END LOOP order_loop;
1709
1710 CLOSE get_order_info;
1711
1712 /* ************************************************************************** */
1713 /* *** else no sales order info given, find item info from qc_rslt (CoA) or */
1714 /* *** qc_spec (CoC) */
1715 ELSE
1716
1717 Look_For_Results ( tbl_ndx);
1718
1719 IF tbl_ndx = 0 then
1720 /* this is a COC report; check for specs only */
1721 v_report_title := 'COC';
1722 GMD_COA_DATA_NEW.Look_For_CoC_Specs
1723 (rec_param,
1724 tbl_ndx,
1725 tbl_hdr,
1726 tbl_dtl);
1727
1728 IF tbl_ndx = 0 THEN
1729 v_report_title := 'BLK';
1730 END IF;
1731 /* if neither coa nor coc data found, change this */
1732 /* flag back to default for check in populate_details. */
1733 END IF;
1734 /* end if results records were found */
1735 END IF;
1736 /* if no sales order info given, else find item info from qc */
1737
1738 IF v_report_title <> 'BLK' THEN
1739
1740 GMD_COA_DATA_NEW.Populate_Details (tbl_hdr, tbl_dtl);
1741
1742 END IF;
1743
1744 IF tbl_dtl.EXISTS(1) THEN
1745 GMD_COA_DATA_NEW.Populate_Text (tbl_dtl, tbl_spec_text, tbl_rslt_text);
1746 ELSE
1747 tbl_hdr := empty_header;
1748 /* if there is nothing in details, empty headers */
1749 /* this is not an error. Report will be empty */
1750 tbl_ndx := 0;
1751 END IF;
1752
1753 /* *********************************************************************** -- */
1754 /* now fill in lookup columns (whse name, customer name, bol_no, lot_no) */
1755 /* and make sure report_title is accurate (gets a little messy if */
1756 /* sales/ship info given and no results (CoC) */
1757
1758 IF tbl_ndx > 0 THEN
1759
1760 /*BEGIN BUG#1810652 James Bernard */
1761 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
1762 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
1763 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
1764 /*END BUG#1810652 */
1765
1766 /* if sales/ship data given and no results, only specs exists, */
1767 /* reset report_title in tbl_hdr to v_report_title (tbl_hdr is IN */
1768 /* var to Populate_Details, so cannot change report_title there.) */
1769
1770 tbl_hdr(loop_counter).report_title := v_report_title;
1771
1772 /* get bol (shipping no) from op_bill_lad */
1773
1774 IF tbl_hdr(loop_counter).bol_id is not NULL THEN
1775 FOR bol_cur_rec IN get_bol_info (tbl_hdr(loop_counter).bol_id) LOOP
1776 tbl_hdr(loop_counter).bol_no := bol_cur_rec.bol_no;
1777 END LOOP;
1778 /* end getting bol (shipping) info from op_bill_lad */
1779 END IF;
1780 /* end checking if bol id has a value */
1781 /* get customer no and name from op_cust_mst tables */
1782
1783 IF tbl_hdr(loop_counter).cust_id is not NULL THEN
1784 FOR cust_cur_rec IN get_cust_info (tbl_hdr(loop_counter).cust_id) LOOP
1785 tbl_hdr(loop_counter).cust_no := cust_cur_rec.cust_no;
1786 tbl_hdr(loop_counter).cust_name := cust_cur_rec.cust_name;
1787 END LOOP;
1788 /* end getting cust info from hz tables */
1789 END IF;
1790 /* end checking if cust id has a value */
1791
1792 /* get item no and description from ic_item_mst if it was not */
1793 /* selected as part of generic item check. */
1794 /* It is correct to check item_NO, not item_ID! */
1795
1796 IF tbl_hdr(loop_counter).item_no is NULL THEN
1797 FOR item_cur_rec IN get_item_info (tbl_hdr(loop_counter).item_id) LOOP
1798 tbl_hdr(loop_counter).item_no := item_cur_rec.item_no;
1799 tbl_hdr(loop_counter).item_desc := item_cur_rec.item_desc1;
1800 END LOOP;
1801 /* end getting item info from ic_item_mst */
1802 END IF;
1803 /* end checking if item id has a value */
1804
1805 /* get warehouse no and name from ic_whse_mst */
1806
1807 IF tbl_hdr(loop_counter).whse_code is not NULL THEN
1808 FOR whse_cur_rec IN get_whse_info (tbl_hdr(loop_counter).whse_code) LOOP
1809 tbl_hdr(loop_counter).whse_name := whse_cur_rec.whse_name;
1810 END LOOP;
1811 /* end getting warehouse info from ic_whse_mst */
1812 END IF;
1816 /* if pkg called from report, lot id must have value */
1813 /* end checking if whse has a value */
1814
1815 /* get lot no, sublot no and lot desc from ic_lots_mst */
1817 /* else if called from opm portal, lot may be null */
1818 IF tbl_hdr(loop_counter).lot_id is not NULL THEN
1819 FOR lot_cur_rec IN get_lot_info (tbl_hdr(loop_counter).lot_id) LOOP
1820 tbl_hdr(loop_counter).lot_no := lot_cur_rec.lot_no;
1821 tbl_hdr(loop_counter).lot_desc := lot_cur_rec.lot_desc;
1822 tbl_hdr(loop_counter).sublot_no := lot_cur_rec.sublot_no;
1823 END LOOP;
1824 /* end getting lot info from ic_lot_mst */
1825 END IF;
1826 /* end checking if lot id has a value */
1827 END LOOP;
1828 /* end loop to fill in lookup columns */
1829 ELSE /* no header records found */
1830 tbl_hdr := empty_header;
1831 /* this is not an error. Report will be empty */
1832 END IF;
1833 /* end if there are any records in header table */
1834
1835 /* -- standard check of p_commit */
1836 IF FND_API.To_Boolean(p_commit) THEN
1837 COMMIT WORK;
1838 END IF;
1839 /* -- standard call to get message count and if count is 1, get message info */
1840 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1841
1842 EXCEPTION
1843 WHEN FND_API.G_EXC_ERROR THEN
1844 ROLLBACK TO Populate_CoA_Data_Save;
1845 x_return_status := FND_API.G_RET_STS_ERROR;
1846 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1847
1848 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1849 ROLLBACK TO Populate_CoA_Data_Save;
1850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1852
1853 WHEN OTHERS THEN
1854 ROLLBACK TO Populate_CoA_Data_Save;
1855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1856 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1857 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1858
1859 END Populate_CoA_Data;
1860
1861
1862 /*###############################################################
1863 # NAME
1864 # dump_to_db_tables
1865 # SYNOPSIS
1866 # proc dump_to_db_tables
1867 # parms event
1868 # This is a debug procedure to put data from plsql tables into
1869 # physical database tables.
1870 # p_commit should always be TRUE
1871 # DESCRIPTION
1872 # HISTORY
1873 # 12jun2001 James Bernard Bug 1810652
1874 # In the For cursors tbl_hdr.FIRST, tbl_hdr.LAST ,tbl_spec_text.FIRST,
1875 # tbl_spec_text.LAST,tbl_rslt_text.FIRST and tbl_rslt_text.LAST
1876 # are replaced with NVL(tbl_hdr.FIRST,0).
1877 # NVL(tbl_hdr.LAST,0),NVL(tbl_spec_text.FIRST,0)
1878 # NVL(tbl_spec_text.LAST,0),NVL(tbl_rslt_text.FIRST,0) and
1879 # NVL(tbl_rslt_text.LAST,0) respectively.
1880 ################################################################*/
1881
1882
1883 PROCEDURE Dump_To_Db_Tables (
1884 p_api_version In NUMBER,
1885 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1886 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1887 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1888 tbl_hdr IN t_coa_header_tbl,
1889 tbl_dtl IN t_coa_detail_tbl,
1890 tbl_spec_text IN t_coa_text_tbl,
1891 tbl_rslt_text IN t_coa_text_tbl,
1892 x_return_status OUT NOCOPY VARCHAR2,
1893 x_msg_count OUT NOCOPY NUMBER,
1894 x_msg_data OUT NOCOPY VARCHAR2) IS
1895
1896 X_user_id NUMBER;
1897 X_login_id NUMBER;
1898 X_date DATE;
1899 l_api_name CONSTANT VARCHAR2(30) := 'Dump_To_Db_Tables';
1900 l_api_version CONSTANT NUMBER := 1.6;
1901
1902 begin
1903
1904 IF FND_API.to_Boolean (p_init_msg_list) THEN
1905 FND_MSG_PUB.initialize;
1906 END IF;
1907 x_return_status := FND_API.G_RET_STS_SUCCESS;
1908
1909 /* ******* if db table is necessary and for debugging ***** -- */
1910
1911 delete from gmd_coa_headers;
1912 delete from gmd_coa_details;
1913 delete from gmd_coa_spec_text;
1914 delete from gmd_coa_rslt_text;
1915
1916 /* -- Do API standard code for savepoint, messages, initialize return status */
1917 SAVEPOINT Dump_To_Db_Tables_SAVE;
1918 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1919 l_api_name, G_PKG_NAME) THEN
1920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1921 END IF;
1922
1923 IF tbl_hdr.FIRST is not NULL THEN
1924 X_user_id := FND_GLOBAL.USER_ID;
1925 X_login_id := FND_GLOBAL.LOGIN_ID;
1926
1927 /*BEGIN BUG#1810652 James Bernard */
1928 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
1929 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
1930 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
1931 /*END BUG#1810652 */
1932 INSERT into gmd_coa_headers (gmd_coa_id, order_id, line_id, orgn_code,
1933 order_no,
1934 custpo_no,
1935 shipdate, cust_id, cust_no, cust_name,
1936 bol_id, bol_no, item_id,
1937 item_no, item_desc1,
1938 whse_code, whse_name,
1942 report_title,
1939 lot_id, lot_no, lot_desc, sublot_no,
1940 order_qty1, order_um1, order_qty2,
1941 order_um2, ship_qty1, ship_qty2,
1943 created_by, creation_date, last_update_date,
1944 last_updated_by, last_update_login)
1945 VALUES (tbl_hdr(loop_counter).gmd_coa_id,
1946 tbl_hdr(loop_counter).order_id,
1947 tbl_hdr(loop_counter).line_id,
1948 tbl_hdr(loop_counter).orgn_code,
1949 tbl_hdr(loop_counter).order_no,
1950 tbl_hdr(loop_counter).custpo_no,
1951 tbl_hdr(loop_counter).shipdate,
1952 tbl_hdr(loop_counter).cust_id,
1953 tbl_hdr(loop_counter).cust_no,
1954 tbl_hdr(loop_counter).cust_name,
1955 tbl_hdr(loop_counter).bol_id,
1956 tbl_hdr(loop_counter).bol_no,
1957 tbl_hdr(loop_counter).item_id,
1958 tbl_hdr(loop_counter).item_no,
1959 tbl_hdr(loop_counter).item_desc,
1960 tbl_hdr(loop_counter).whse_code,
1961 tbl_hdr(loop_counter).whse_name,
1962 tbl_hdr(loop_counter).lot_id,
1963 tbl_hdr(loop_counter).lot_no,
1964 tbl_hdr(loop_counter).lot_desc,
1965 tbl_hdr(loop_counter).sublot_no,
1966 tbl_hdr(loop_counter).order_qty1,
1967 tbl_hdr(loop_counter).order_um1,
1968 tbl_hdr(loop_counter).order_qty2,
1969 tbl_hdr(loop_counter).order_um2,
1970 tbl_hdr(loop_counter).ship_qty1,
1971 tbl_hdr(loop_counter).ship_qty2,
1972 tbl_hdr(loop_counter).report_title,
1973 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
1974 );
1975 END LOOP;
1976
1977 IF tbl_dtl.FIRST is not NULL THEN
1978 /* -- ******* if db table is necessary and for debugging ***** -- */
1979 /*BEGIN BUG#1810652 James Bernard */
1980 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
1981 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
1982 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
1983 /*END BUG#1810652 */
1984 INSERT into gmd_coa_details (gmd_coa_id, qc_result_id, result_date,
1985 qc_spec_id, assay_code, assay_desc,
1986 result, specification, uom,
1987 rslt_text_code, spec_text_code,
1988 created_by, creation_date,
1989 last_update_date,
1990 last_updated_by, last_update_login
1991 )
1992 VALUES (tbl_dtl(loop_counter).gmd_coa_id,
1993 tbl_dtl(loop_counter).qc_result_id,
1994 tbl_dtl(loop_counter).result_date,
1995 tbl_dtl(loop_counter).qc_spec_id,
1996 tbl_dtl(loop_counter).assay_code,
1997 tbl_dtl(loop_counter).assay_desc,
1998 tbl_dtl(loop_counter).result,
1999 tbl_dtl(loop_counter).specification,
2000 tbl_dtl(loop_counter).uom,
2001 tbl_dtl(loop_counter).rslt_text_code,
2002 tbl_dtl(loop_counter).spec_text_code,
2003 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
2004 );
2005 END LOOP;
2006 IF tbl_spec_text.FIRST is not NULL THEN
2007 /*BEGIN BUG#1810652 James Bernard */
2008 /*Changed tbl_spec_text.FIRST to NVL(tbl_spec_text.FIRST,0) */
2009 /* and tbl_spec_text.LAST to NVL(tbl_spec_text.LAST,0) */
2010 FOR loop_counter IN NVL(tbl_spec_text.FIRST,0) .. NVL(tbl_spec_text.LAST,0) LOOP
2011 /*END BUG#1810652 */
2012 INSERT into gmd_coa_spec_text (gmd_coa_id, text_code,
2013 paragraph_code, line_no, text)
2014 VALUES (tbl_spec_text(loop_counter).gmd_coa_id,
2015 tbl_spec_text(loop_counter).text_code,
2016 tbl_spec_text(loop_counter).paragraph_code,
2017 tbl_spec_text(loop_counter).line_no,
2018 tbl_spec_text(loop_counter).text);
2019 END LOOP;
2020 END IF;
2021 /* if there is any spec text */
2022 IF tbl_rslt_text.FIRST is not NULL THEN
2023 /*BEGIN BUG#1810652 James Bernard */
2024 /*Changed tbl_rslt_text.FIRST to NVL(tbl_rslt_text.FIRST,0) and */
2025 /*tbl_rslt_text.LAST to NVL(tbl_rslt_text.LAST,0) */
2026 FOR loop_counter IN NVL(tbl_rslt_text.FIRST,0) .. NVL(tbl_rslt_text.LAST,0) LOOP
2027 /*END BUG#1810652 */
2028 INSERT into gmd_coa_rslt_text (gmd_coa_id, text_code,
2029 paragraph_code, line_no, text)
2030 VALUES (tbl_rslt_text(loop_counter).gmd_coa_id,
2031 tbl_rslt_text(loop_counter).text_code,
2032 tbl_rslt_text(loop_counter).paragraph_code,
2033 tbl_rslt_text(loop_counter).line_no,
2034 tbl_rslt_text(loop_counter).text);
2035 END LOOP;
2036 END IF;
2037 /* if there is any results text */
2038 ELSE
2039 NULL;
2040 /* empty table is not an error */
2041 END if;
2042 /* if there is something in details table */
2043 ELSE
2044 NULL;
2045 /* empty table is not an error */
2046 END IF;
2050 IF FND_API.To_Boolean(p_commit) THEN
2047 /* if there is something in header table */
2048
2049 /* -- standard check of p_commit */
2051 COMMIT WORK;
2052 END IF;
2053
2054 /* -- standard call to get message count and if count is 1, get message info */
2055 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2056
2057 EXCEPTION
2058 WHEN FND_API.G_EXC_ERROR THEN
2059 ROLLBACK TO Dump_To_Db_Tables_Save;
2060 x_return_status := FND_API.G_RET_STS_ERROR;
2061 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2062
2063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2064 ROLLBACK TO Dump_To_Db_Tables_Save;
2065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2067
2068 WHEN OTHERS THEN
2069 ROLLBACK TO Dump_To_Db_Tables_Save;
2070 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2071 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2072 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
2073
2074 END Dump_To_Db_Tables;
2075
2076 END GMD_CoA_Data_NEW;
[Home] [Help]