1 PACKAGE BODY GMD_CoA_Data_OM AS
2 /* $Header: GMDCOA3B.pls 115.13 2004/02/18 16:52:36 magupta noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMD_COA_DATA_OM';
5 v_stage NUMBER; /* Variable for debugging. */
6 p_msg VARCHAR2(4000);
7 l_debug_enabled VARCHAR2(1):='Y';
8 l_utl_file_dir VARCHAR2(1000);
9 /* next variable is version for Look_for_CoC_Data */
10
11
12 PROCEDURE Trace(p_msg IN VARCHAR2)
13 IS
14 BEGIN
15 /*gmd_debug.put_line(p_msg); */
16 IF (l_debug_enabled = 'Y') THEN
17 FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg);
18 END IF;
19 END Trace;
20 FUNCTION IsEmpty(p_str IN VARCHAR2) RETURN BOOLEAN IS
21 BEGIN
22 IF ( p_str IS NULL OR RTRIM(p_str) IS NULL )
23 THEN
24 RETURN(TRUE);
25 ELSE
26 RETURN (FALSE);
27 END IF;
28 END;
29
30 PROCEDURE WriteLog(p_msg_code IN VARCHAR2,
31 p_appl IN VARCHAR2,
32 p_col1 IN VARCHAR2,
33 p_token1 IN VARCHAR2,
34 p_col2 IN VARCHAR2,
35 p_token2 IN VARCHAR2,
36 p_col3 IN VARCHAR2,
37 p_token3 IN VARCHAR2,
38 p_col4 IN VARCHAR2,
39 p_token4 IN VARCHAR2,
40 p_col5 IN VARCHAR2,
41 p_token5 IN VARCHAR2,
42 p_file_typ IN VARCHAR2 )
43 IS
44 BEGIN
45 v_stage:=1;
46 FND_MESSAGE.SET_NAME(p_appl,p_msg_code);
47 IF (NOT IsEmpty(p_col1))
48 THEN
49 FND_MESSAGE.SET_TOKEN(p_token1,p_col1);
50 END IF;
51
52 IF (NOT IsEmpty(p_col2))
53 THEN
54 FND_MESSAGE.SET_TOKEN(p_token2,p_col2);
55 END IF;
56
57 IF (NOT IsEmpty(p_col3))
58 THEN
59 FND_MESSAGE.SET_TOKEN(p_token3,p_col3);
60 END IF;
61
62 IF (NOT IsEmpty(p_col4))
63 THEN
64 FND_MESSAGE.SET_TOKEN(p_token4,p_col4);
65 END IF;
66
67 IF (NOT IsEmpty(p_col5))
68 THEN
69 FND_MESSAGE.SET_TOKEN(p_token5,p_col5);
70 END IF;
71
72 IF (p_file_typ='OUTPUT')
73 THEN
74 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,FND_MESSAGE.GET);
75 ELSE
76 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET);
77 END IF;
78 END WriteLog;
79
80 /*#############################################################################
81 # Procedure Look_For_CoC_Specs
82 # The cursors used are similar to spec cursors in Details procedure.
83 # Cursors here do not take 'assay_code' as parameter and return info for
84 # header and detail tables. Also, cursors here will return records with
85 # NULL lots (specs only; results must have lots if item is lot cntrl'd).
86 # This procedure also used if sales/shipping information is given (so
87 # there is data in the header table), but no results were found in
88 # Populate_Details. Use cursors here to look for specs for CoC.
89 # P_init_msg_list should be false when called from Populate_CoA_Data
90 # 11feb2000 LRJ
91 # 31mar2000 LRJ Made this procedure public (added to package spec)
92 # 07jul2000 LRJ Add section for scenario where header data exists but no
93 # results were found. Make procedure private.
94 # Added cursors for global item/lot specs and global item
95 # no-lot spec
96 # 12jun2001 James Bernard Bug 1810652
97 # Modified the where clause of the select statement in the
98 # get_qc_cust_spec cursor definition to retrieve records when
99 # c_item_id is null.
100 # Modified the where clause of the select statement in the
101 # get_qc_global_cust_spec cursor definition to retrieve records when
102 # c_item_id is null.
103 # Modified the where clause of the select statement in the
104 # get_qc_item_spec cursor definition to retrieve records when
105 # c_item_id is null.
106 # In this procedure in the for cursors tbl.hdr.FIRST and tbl_hdr.LAST
107 # are replaced with NVL(tbl.hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
108 ############################################################################ */
109 PROCEDURE Look_For_CoC_Specs(
110 rec_param IN t_coa_parameters,
111 hdr_tbl_ndx OUT NOCOPY BINARY_INTEGER,
112 tbl_hdr IN OUT NOCOPY t_coa_header_tbl,
113 tbl_dtl IN OUT NOCOPY t_coa_detail_tbl)
114 IS
115 CURSOR get_spec_details (c_spec_id NUMBER)
116 IS
117 select gsb.item_id,
118 gsb.spec_id qc_spec_id,
119 gt.test_code assay_code,
120 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
121 specification,
122 gt.test_unit uom,
123 gst.text_code spec_text_code
124 from
125 gmd_specifications_b gsb,
126 gmd_spec_tests_b gst,
127 gmd_qc_tests_b gt
128 where
129 gsb.spec_id = c_spec_id
130 and gsb.spec_id = gst.spec_id
131 and gst.test_id = gt.test_id
132 and nvl(gst.print_spec_ind,'N') = 'Y'
133 and gsb.delete_mark = 0
134 ;
135
136
137 dtl_tbl_ndx BINARY_INTEGER := 0;
138 v_gmd_coa_id BINARY_INTEGER := 1;
139 v_previous_header VARCHAR2(75); /* orgn_code+cust+item+whse+lot */
140 v_current_header VARCHAR2(75);
141 l_chk_whse_null NUMBER;
142 l_chk_lot_null NUMBER;
143 l_chk_orgn_null NUMBER;
144 p_customer_spec_rec GMD_SPEC_MATCH_GRP.customer_spec_rec_type;
145 p_inventory_spec_rec GMD_SPEC_MATCH_GRP.inventory_spec_rec_type;
146 x_return_flag BOOLEAN;
147 x_spec_id NUMBER;
148 x_spec_vr_id NUMBER;
149 x_return_status VARCHAR2(1000);
150 x_message_data VARCHAR2(1000);
151
152
153
154 BEGIN
155
156 hdr_tbl_ndx := 0;
157 dtl_tbl_ndx := 0;
158 l_chk_whse_null := 0; /* -- 1st time thru, look for match */
159 l_chk_orgn_null := 0;
160 l_chk_lot_null := 0;
161
162
163 /*BEGIN BUG#1810652 James Bernard */
164 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
165 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
166 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
167 /*END BUG#1810652 */
168 dtl_tbl_ndx := 0;
169
170
171 /*-----------------------------------------------------------------
172 Call Customer specific spec matching API,
173 If the spec is found then open the cursor get_spec_details
174 above to get the details about that spec, else try Inventory
175 spec.
176 -------------------------------------------------------------------*/
177 p_customer_spec_rec.item_id := tbl_hdr(loop_counter).item_id;
178 p_customer_spec_rec.cust_id := tbl_hdr(loop_counter).cust_id;
179 p_customer_spec_rec.date_effective := SYSDATE;
180 p_customer_spec_rec.whse_code := tbl_hdr(loop_counter).whse_code;
181 p_customer_spec_rec.org_id := tbl_hdr(loop_counter).org_id;
182 p_customer_spec_rec.order_id := tbl_hdr(loop_counter).order_id;
183
184 x_return_flag := FALSE;
185 x_return_flag := GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC(p_customer_spec_rec,
186 x_spec_id,
187 x_spec_vr_id,
188 x_return_status,
189 x_message_data);
190 trace('Look_For_CoC_Specs, GMD_SPEC_MATCH_GRP.FIND_CUSTOMER_SPEC , Spec_id '||x_spec_id);
191 IF (x_return_flag = FALSE) THEN
192
193 p_inventory_spec_rec.item_id := tbl_hdr(loop_counter).item_id;
194 p_inventory_spec_rec.lot_id := tbl_hdr(loop_counter).lot_id;
195 p_inventory_spec_rec.date_effective := SYSDATE;
196 p_inventory_spec_rec.whse_code := tbl_hdr(loop_counter).whse_code;
197 -- p_inventory_spec_rec.org_id := tbl_hdr(loop_counter).org_id;
198
199 x_return_flag := GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC(p_inventory_spec_rec ,
200 x_spec_id ,
201 x_spec_vr_id,
202 x_return_status,
203 x_message_data);
204 trace('Look_For_CoC_Specs, GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC , Spec_id '||x_spec_id);
205 END IF;
206
207 FOR qc_cur_rec IN get_spec_details
208 (x_spec_id) LOOP
209 dtl_tbl_ndx := dtl_tbl_ndx + 1;
210 tbl_dtl(dtl_tbl_ndx).gmd_coa_id
211 := tbl_hdr(loop_counter).gmd_coa_id;
212 tbl_dtl(dtl_tbl_ndx).qc_spec_id := qc_cur_rec.qc_spec_id;
213 tbl_dtl(dtl_tbl_ndx).assay_code := qc_cur_rec.assay_code;
214 tbl_dtl(dtl_tbl_ndx).specification := qc_cur_rec.specification;
215 tbl_dtl(dtl_tbl_ndx).uom := qc_cur_rec.uom;
216 tbl_dtl(dtl_tbl_ndx).spec_text_code := qc_cur_rec.spec_text_code;
217 trace('Look_For_CoC_Specs , get_spec_details '||tbl_dtl(dtl_tbl_ndx).qc_spec_id||' '||
218 'assay_code '||tbl_dtl(dtl_tbl_ndx).assay_code||' '||
219 'specification '||tbl_dtl(dtl_tbl_ndx).specification||' '||
220 'uom '||tbl_dtl(dtl_tbl_ndx).uom||' '||
221 'spec_text_code '||tbl_dtl(dtl_tbl_ndx).spec_text_code);
222
223 END LOOP;
224
225
226
227 END LOOP;
228 /* end going through header records */
229 hdr_tbl_ndx := tbl_hdr.LAST;
230
231
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN
234 NULL;
235 /* no data found is not an error. Not all items will have specs */
236 WHEN OTHERS THEN
237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
238 /* exception defined in Populate_Coa_Data */
239
240 END Look_For_CoC_Specs;
241 /* end procedure */
242
243
244 /*###############################################################
245 # NAME
246 # Populate_Details
247 # SYNOPSIS
248 # proc Populate_Details
249 # parms header table IN
250 # details table IN OUT details will have data if this
251 # is non-sales data CoC
252 # DESCRIPTION
253 # get results info
254 #
255 # HISTORY
256 # 12jun2001 James Bernard Bug 1810652
257 # In the for cursors tbl_hdr.FIRST and tbl_hdr.LAST are
258 # replaced with NVL(tbl_hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
259 # Added 'c_item_id is NULL or' condition in the where clause
260 # of the select statement in the get_cust_rslt_info cursor
261 # definition.
262 # Added 'c_item_id is NULL or' and 'c_lot_id is NULL or'
263 # conditions in the where clause of the select statement
264 # in the get_item_rslt_info cursor definition.
265 # 26Sep2001 Manish Gupta New Quality
266 # Changed the cursors for getting data from New Quality tables.
267 ################################################################*/
268 PROCEDURE Populate_Details (tbl_hdr IN t_coa_header_tbl,
269 tbl_dtl IN OUT NOCOPY t_coa_detail_tbl) IS
270
271 /* BEGIN BUG#1810652 James Bernard */
272 /* Added 'c_item_id is NULL or' in the where clause */
273 CURSOR get_cust_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
274 c_item_id ic_item_mst.item_id%TYPE,
275 c_lot_id ic_lots_mst.lot_id%TYPE,
276 c_lot_no ic_lots_mst.lot_no%TYPE,
277 c_cust_id hz_cust_accounts.cust_account_id%TYPE) IS
278 select gr.result_id qc_result_id,
279 gst.spec_id qc_spec_id,
280 gt.test_code assay_code,
281 gr.result_date result_date,
282 decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
283 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
284 specification,
285 gt.test_unit uom,
286 gst.text_code spec_text_code,
287 gr.text_code rslt_text_code
288 from gmd_samples gs,
289 gmd_results gr,
290 gmd_qc_tests_b gt,
291 gmd_spec_results gsr,
292 gmd_spec_tests_b gst ,
293 gmd_sampling_events gse,
294 gmd_event_spec_disp ges,
295 gmd_specifications_b gsb,
296 gmd_sample_spec_disp gss
297 where gs.sample_id = gr.sample_id
298 and gse.sampling_event_id = gs.sampling_event_id
299 and gse.sampling_event_id = ges.sampling_event_id
300 and ges.spec_used_for_lot_attrib_ind ='Y'
301 and ges.spec_id(+) = gst.spec_id
302 and gst.test_id(+) = gt.test_id
303 and gs.sample_id = gr.sample_id
304 and gr.result_date is not null
305 and gr.result_id = gsr.result_id
306 and gr.test_id = gt.test_id
307 and ges.event_spec_disp_id = gsr.event_spec_disp_id
308 and ges.event_spec_disp_id = gss.event_spec_disp_id
309 and gsb.spec_id = ges.spec_id
310 and gss.disposition in ('4A','5AV') -- ACCEPT
311 and nvl(gsr.evaluation_ind,'N') in ('0A','1V')
315 and ( c_lot_id is NULL or decode(nvl(gs.lot_id,0),0,gs.lot_no, gs.lot_id) = decode(nvl(gs.lot_id,0),0, c_lot_no,c_lot_id))
312 and decode(nvl(gst.print_result_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N',
313 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N') = 'Y'
314 and gs.cust_id = c_cust_id
316 -- and (c_lot_id is NULL or gs.lot_id = c_lot_id)
317 and (c_item_id is NULL or gs.item_id = c_item_id)
318 --and gs.orgn_code = c_orgn_code
319 and gs.delete_mark = 0
320 ;
321 /*END BUG#1810652 */
322
323 /* use the next cursor if no rows returned from get_cust_rslt_info */
324 /* BEGIN BUG#1810652 James Bernard */
325 /* Added 'c_item_id is NULL or' and 'c_lot_id is NULL or' in the where clause */
326 CURSOR get_item_rslt_info (c_orgn_code gmd_samples.orgn_code%TYPE,
327 c_item_id ic_item_mst.item_id%TYPE,
328 c_lot_no ic_lots_mst.lot_no%TYPE,
329 c_lot_id ic_lots_mst.lot_id%TYPE) IS
330 select gr.result_id qc_result_id,
331 gst.spec_id qc_spec_id,
332 gt.test_code assay_code,
333 gr.result_date result_date,
334 decode(gr.result_value_char, null, to_char(gr.result_value_num), gr.result_value_char) result,
335 decode (gst.target_value_char, null, to_char(gst.target_value_num), gst.target_value_char)
336 specification,
337 gt.test_unit uom,
338 gst.text_code spec_text_code,
339 gr.text_code rslt_text_code
340 from gmd_samples gs,
341 gmd_results gr,
342 gmd_qc_tests_b gt,
343 gmd_spec_results gsr,
344 gmd_spec_tests_b gst ,
345 gmd_sampling_events gse,
346 gmd_event_spec_disp ges,
347 gmd_specifications_b gsb,
348 gmd_sample_spec_disp gss
349 where gs.sample_id = gr.sample_id
350 and gse.sampling_event_id = gs.sampling_event_id
351 and gse.sampling_event_id = ges.sampling_event_id
352 and ges.spec_used_for_lot_attrib_ind ='Y'
353 and ges.spec_id(+) = gst.spec_id
354 and gst.test_id(+) = gt.test_id
355 and gs.sample_id = gr.sample_id
356 and gr.result_date is not null
357 and gr.result_id = gsr.result_id
358 and gr.test_id = gt.test_id
359 and ges.event_spec_disp_id = gsr.event_spec_disp_id
360 and ges.event_spec_disp_id = gss.event_spec_disp_id
361 and gsb.spec_id = ges.spec_id
362 and gss.disposition in ('4A','5AV') -- ACCEPT
363 --and gss.disposition = '4A' -- ACCEPT
364 and nvl(gsr.evaluation_ind,'N') in ( '0A','1V')
365 and decode(nvl(gst.print_result_ind,'N'),'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N',
366 gr.ad_hoc_print_on_coa_ind, 'Y', decode( gsr.evaluation_ind,'0A','Y','1V','Y','N'),'N') = 'Y'
367 -- and (c_lot_id is NULL or gs.lot_id = c_lot_id)
368 and ( c_lot_id is NULL or decode(nvl(gs.lot_id,0),0,gs.lot_no, gs.lot_id) = decode(nvl(gs.lot_id,0),0, c_lot_no,c_lot_id))
369 and (c_item_id is NULL or gs.item_id = c_item_id)
370 --and gs.orgn_code = c_orgn_code
371 and gs.delete_mark = 0
372 and gs.cust_id is NULL
373 and gs.batch_id is NULL
374 and gs.formula_id is NULL
375 and gs.routing_id is NULL
376 and gs.oprn_id is NULL
377 and gs.supplier_id is NULL
378 ;
379
380
381
382
383 /*END BUG#1810652 */
384
385
386 CURSOR get_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
387 c_cust_id hz_cust_accounts.cust_account_id%TYPE,
388 c_orgn_code gmd_customer_spec_vrs.ORGN_CODE%TYPE,
392 decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
389 c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
390 IS
391 select gsb.spec_id qc_spec_id,
393 specification,
394 gcs.text_code spec_text_code
395 from gmd_specifications_b gsb,
396 gmd_customer_spec_vrs gcs,
397 gmd_spec_tests_b gst,
398 gmd_qc_tests_b gt
399 where gsb.spec_id = gcs.spec_id
400 and gsb.spec_id = gst.spec_id
401 and gst.test_id = gt.test_id
402 and nvl(gst.print_result_ind, 'N') = 'Y'
403 and gsb.item_id = c_item_id
404 and gt.test_code = c_assay_code
405 and gcs.cust_id = c_cust_id
406 and gcs.orgn_code = c_orgn_code
407 and gsb.delete_mark= 0;
408
409 CURSOR get_global_cust_spec (c_item_id ic_item_mst.item_id%TYPE,
410 c_cust_id hz_cust_accounts.cust_account_id%TYPE,
411 c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
412 IS
413 select gsb.spec_id qc_spec_id,
414 decode(gst.target_value_char, null, to_char(gst.target_value_num),gst.target_value_char)
415 specification,
416 gcs.text_code spec_text_code
417 from gmd_specifications_b gsb,
418 gmd_customer_spec_vrs gcs,
419 gmd_spec_tests_b gst,
420 gmd_qc_tests_b gt
421 where gsb.spec_id = gcs.spec_id
422 and gsb.spec_id = gst.spec_id
423 and gst.test_id = gt.test_id
424 and nvl(gst.print_result_ind, 'N') = 'Y'
425 and gsb.item_id = c_item_id
426 and gt.test_code = c_assay_code
427 and gcs.cust_id = c_cust_id
428 and gcs.orgn_code is NULL
429 and gsb.delete_mark= 0;
430
431
432 CURSOR get_assay_info (c_assay_code gmd_qc_tests_b.TEST_CODE%TYPE)
433 IS
434 select gt.test_desc assay_desc
435 from gmd_qc_tests gt
436 where gt.test_code = c_assay_code
437 and gt.delete_mark = 0;
438
439 tbl_ndx BINARY_INTEGER := 0;
440 /* index for pl/sql table */
441 v_gmd_coa_id BINARY_INTEGER;
442 dtl_counter NUMBER := 0;
443 l_dummy_param_rec t_coa_parameters;
444 l_tmp_hdr_tbl t_coa_header_tbl;
445 found_a_row BOOLEAN;
446
447 /* *********************************************************************
448 * select from result table looking for customer
449 * if no results, select from result table for item/loc result
450 * if no item/loc result, then end procedure, else
451 * look for a customer spec
452 * if spec_id <> cust spec then look for global cust spec
453 * get assay description
454 * *********************************************************************/
455
456 BEGIN /* begin Populate_Details */
457
458 IF v_report_title = 'COA' THEN
459
460 Trace('Populate_Details, in v_report_title = ''COA''');
461
462 /*BEGIN BUG#1810652 James Bernard */
463 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
464 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0). */
465 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
466 /*END BUG#1810652 */
467
468 found_a_row := FALSE;
469
470 begin
471 /* look for customer-specific result */
472 FOR cust_rslt_cur_rec IN get_cust_rslt_info
473 (tbl_hdr(loop_counter).orgn_code,
474 tbl_hdr(loop_counter).item_id,
475 tbl_hdr(loop_counter).lot_id,
476 tbl_hdr(loop_counter).lot_no,
477 tbl_hdr(loop_counter).cust_id) LOOP
478 found_a_row := TRUE;
479 tbl_ndx := tbl_ndx + 1;
480 tbl_dtl(tbl_ndx).gmd_coa_id := tbl_hdr(loop_counter).gmd_coa_id;
481 tbl_dtl(tbl_ndx).qc_result_id := cust_rslt_cur_rec.qc_result_id;
482 tbl_dtl(tbl_ndx).result_date := cust_rslt_cur_rec.result_date;
483 tbl_dtl(tbl_ndx).qc_spec_id := cust_rslt_cur_rec.qc_spec_id;
484 tbl_dtl(tbl_ndx).assay_code := cust_rslt_cur_rec.assay_code;
485 tbl_dtl(tbl_ndx).result := cust_rslt_cur_rec.result;
486 tbl_dtl(tbl_ndx).specification := cust_rslt_cur_rec.specification;
487 tbl_dtl(tbl_ndx).uom := cust_rslt_cur_rec.uom;
488 tbl_dtl(tbl_ndx).rslt_text_code := cust_rslt_cur_rec.rslt_text_code;
489 tbl_dtl(tbl_ndx).spec_text_code := cust_rslt_cur_rec.spec_text_code;
490
491 p_msg := 'Populate_Details get_cust_rslt_info gmd_coa_id '||tbl_hdr(loop_counter).gmd_coa_id||' '||
492 'qc_result_id '||tbl_dtl(tbl_ndx).qc_result_id||' '||
493 'result_date '||tbl_dtl(tbl_ndx).result_date||' '||
494 'qc_spec_id '||tbl_dtl(tbl_ndx).qc_spec_id||' '||
498 'uom '||tbl_dtl(tbl_ndx).uom||' '||
495 'assay_code '||tbl_dtl(tbl_ndx).assay_code||' '||
496 'result '||tbl_dtl(tbl_ndx).result||' '||
497 'specification '||tbl_dtl(tbl_ndx).specification||' '||
499 'rslt_text_code '||tbl_dtl(tbl_ndx).rslt_text_code||' '||
500 'spec_text_code '||tbl_dtl(tbl_ndx).spec_text_code;
501 trace(p_msg);
502
503
504 END LOOP ;
505 EXCEPTION
506 when NO_DATA_FOUND then
507 NULL;
508 end;
509 /* end looking for customer-specific result */
510
511 IF NOT(found_a_row) THEN
512 begin
513 /* look for item/loc result */
514 FOR item_rslt_cur_rec IN get_item_rslt_info
515 (tbl_hdr(loop_counter).orgn_code,
516 tbl_hdr(loop_counter).item_id,
517 tbl_hdr(loop_counter).lot_no,
518 tbl_hdr(loop_counter).lot_id) LOOP
519 found_a_row := TRUE;
520 tbl_ndx := tbl_ndx + 1;
521 tbl_dtl(tbl_ndx).gmd_coa_id := tbl_hdr(loop_counter).gmd_coa_id;
522 tbl_dtl(tbl_ndx).qc_result_id := item_rslt_cur_rec.qc_result_id;
523 tbl_dtl(tbl_ndx).result_date := item_rslt_cur_rec.result_date;
524 tbl_dtl(tbl_ndx).qc_spec_id := item_rslt_cur_rec.qc_spec_id;
525 tbl_dtl(tbl_ndx).assay_code := item_rslt_cur_rec.assay_code;
526 tbl_dtl(tbl_ndx).result := item_rslt_cur_rec.result;
527 tbl_dtl(tbl_ndx).specification := item_rslt_cur_rec.specification;
528 tbl_dtl(tbl_ndx).uom := item_rslt_cur_rec.uom;
529 tbl_dtl(tbl_ndx).rslt_text_code := item_rslt_cur_rec.rslt_text_code;
530 tbl_dtl(tbl_ndx).spec_text_code := item_rslt_cur_rec.spec_text_code;
531
532 p_msg := 'Populate_Details get_item_rslt_info gmd_coa_id '||tbl_hdr(loop_counter).gmd_coa_id||' '||
533 'qc_result_id '||tbl_dtl(tbl_ndx).qc_result_id||' '||
534 'result_date '||tbl_dtl(tbl_ndx).result_date||' '||
535 'qc_spec_id '||tbl_dtl(tbl_ndx).qc_spec_id||' '||
536 'assay_code '||tbl_dtl(tbl_ndx).assay_code||' '||
537 'result '||tbl_dtl(tbl_ndx).result||' '||
538 'specification '||tbl_dtl(tbl_ndx).specification||' '||
539 'uom '||tbl_dtl(tbl_ndx).uom||' '||
540 'rslt_text_code '||tbl_dtl(tbl_ndx).rslt_text_code||' '||
541 'spec_text_code '||tbl_dtl(tbl_ndx).spec_text_code;
542 trace(p_msg);
543
544 END LOOP ;
545 EXCEPTION
546 when NO_DATA_FOUND then
547 NULL;
548 end;
549 /* end looking for item/loc-specific result */
550 END IF;
551 /* end if no cust results, look for item results */
552 END LOOP;
553 /* end looping through records in header table */
554
555 IF tbl_ndx = 0 THEN
556 /* Look_For_CoC_Specs needs a header table which is IN/OUT. */
557 /* But tbl_hdr was passed to Populate_Details as IN. */
558 /* Look_For_Coc_Specs should not change hdr table with sales- */
559 /* order-no-results scenario, so send a temporary,writable copy. */
560 l_tmp_hdr_tbl := tbl_hdr;
561 trace('Populate Details Going to GMD_COA_DATA_OM.Look_For_CoC_Specs');
562 GMD_COA_DATA_OM.Look_For_CoC_Specs
563 (l_dummy_param_rec,
564 tbl_ndx,
565 l_tmp_hdr_tbl,
566 tbl_dtl);
567 trace('Populate Details coming back from GMD_COA_DATA_OM.Look_For_CoC_Specs');
568 IF tbl_dtl.FIRST is not NULL THEN
569 v_report_title := 'COC';
570 END IF;
571 /* end if there are any rows in tbl_dtl */
572 END IF;
573 /* no results found, look for CoC */
574 /* this IF and call to Look for CoC Specs should only */
575 /* happen when sales/shipping info is given and no */
576 /* results exists. */
577 ELSE
578 tbl_ndx := 1;
579 /* if CoC, set flag = 1 so next section executes for CoC */
583
580 END IF;
581 /* end if this is CoA (not CoC) */
582
584 /* *********************************************************************** --
585 * now look for customer specs
586 * if anything about the cust spec cursors or the detail table changes,
587 * also modify CoC code in Look_For_CoC_Specs procedure */
588
589 IF tbl_ndx > 0 THEN
590
591 IF tbl_dtl.EXISTS(1) THEN
592 /* if sales/shipping item has no specs, there could be rows in tbl_hdr, */
593 /* but nothing to be found for details. If so, skip this section. */
594 /* This section assumes results have been found. */
595 /* Exception WHEN-NO-DATA-FOUND is not handling th is case. */
596
597 /*BEGIN BUG#1810652 James Bernard */
598 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
599 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0). */
600 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
601 /*END BUG#1810652 */
602
603 IF v_report_title = 'COA' THEN
604 /* if CoC, need to loop thru tbl_dtl for assay desc, */
605 /* but not look for specs again */
606 /* use v_gmd_coa_id rather than tbl_dtl(loop_counter).gmd_coa_id as index */
607 /* to header table. Hopefully makes codes more readable. */
608 v_gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
609
610 FOR cust_spec_rec IN get_cust_spec (tbl_hdr(v_gmd_coa_id).item_id,
611 tbl_hdr(v_gmd_coa_id).cust_id,
612 tbl_hdr(v_gmd_coa_id).orgn_code,
613 tbl_dtl(loop_counter).assay_code)
614 LOOP
615 dtl_counter := dtl_counter + 1;
616 tbl_dtl(loop_counter).qc_spec_id := cust_spec_rec.qc_spec_id;
617 tbl_dtl(loop_counter).specification := cust_spec_rec.specification;
618 tbl_dtl(loop_counter).spec_text_code := cust_spec_rec.spec_text_code;
619 END LOOP;
620 IF dtl_counter = 0 THEN
621 FOR cust_spec_rec IN get_global_cust_spec
622 (tbl_hdr(v_gmd_coa_id).item_id,
623 tbl_hdr(v_gmd_coa_id).cust_id,
624 tbl_dtl(loop_counter).assay_code)
625 LOOP
626 dtl_counter := dtl_counter + 1;
627 tbl_dtl(loop_counter).qc_spec_id := cust_spec_rec.qc_spec_id;
628 tbl_dtl(loop_counter).specification := cust_spec_rec.specification;
629 tbl_dtl(loop_counter).spec_text_code:= cust_spec_rec.spec_text_code;
630 END LOOP;
631 END IF;
632 /* end if no cust spec found, look for global spec */
633 /* if there is only an item specification, it should already have */
634 /* been associated with result in result table (qrm.qc_spec_id). */
635 /* That value, and specification and text code would have been */
636 /* pulled in cursors get_cust_rslt_info or get_item_rslt_info. */
637 END IF;
638 /* end if this is CoA */
639
640 /* ****************************************************************** -- */
641 /* now fill in lookup columns (assay_description) */
642
643 /* get assay description from qc_assy_typ */
644
645 IF tbl_dtl(loop_counter).assay_code is not NULL THEN
646 FOR assay_cur_rec IN get_assay_info (tbl_dtl(loop_counter).assay_code)
647 LOOP
648 tbl_dtl(loop_counter).assay_desc := assay_cur_rec.assay_desc;
649 END LOOP;
650 /* end getting assay from qc_assy_typ */
651 END IF;
652 /* end checking if assay code has a value */
653 END LOOP;
654 /* end loop thru detail to get specs and/or fill in */
655 /* lookup columns */
656 END IF;
657 /* end if there are rows in detail which need specs matched up */
658 ELSE
659 tbl_dtl := empty_detail;
660 END IF;
664 EXCEPTION
661 /* end if any result records found */
662
663
665 WHEN NO_DATA_FOUND THEN
666 NULL;
667
668 WHEN OTHERS THEN
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
670 /* exception defined in Populate_Coa_Data */
671
672 END Populate_Details;
673
674
675 /*###############################################################
676 # NAME
677 # Populate_Text
678 # SYNOPSIS
679 # proc Populate_Text
680 # parms detail table IN text table OUT
681 # DESCRIPTION
682 # get results text info
683 # HISTORY
684 # 12jun2001 James Bernard Bug 1810652
685 # In the For Cursors tbl_dtl.FIRST and tbl_dtl.LAST is
686 # replaced by NVL(tbl_dtl.FIRST,0) and NVL(tbl_dtl.LAST,0)
687 ################################################################*/
688 PROCEDURE Populate_Text (tbl_dtl IN t_coa_detail_tbl,
689 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
690 tbl_rslt_text OUT NOCOPY t_coa_text_tbl) IS
691
692 CURSOR get_text_info (c_text_code qc_text_tbl.text_code%TYPE) IS
693 select paragraph_code,
694 line_no,
695 text
696 from qc_text_tbl
697 where text_code = c_text_code
698 and line_no > 0
699 order by paragraph_code, line_no ;
700
701 tbl_ndx BINARY_INTEGER := 0;
702 /* index for pl/sql table */
703
704 BEGIN
705
706 /* loop through detail records, get text for results and text for specs */
707
708 tbl_ndx := 0;
709
710 /*BEGIN BUG#1810652 James Bernard */
711 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
712 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
713 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
714 /*END BUG#1810652 */
715 IF (tbl_dtl(loop_counter).spec_text_code) is not NULL THEN
716 FOR text_cur_rec IN get_text_info (tbl_dtl(loop_counter).spec_text_code)
717 LOOP
718 tbl_ndx := tbl_ndx + 1;
719
720 tbl_spec_text(tbl_ndx).gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
721 tbl_spec_text(tbl_ndx).text_code:=tbl_dtl(loop_counter).spec_text_code;
722 tbl_spec_text(tbl_ndx).paragraph_code := text_cur_rec.paragraph_code;
723 tbl_spec_text(tbl_ndx).line_no := text_cur_rec.line_no;
724 tbl_spec_text(tbl_ndx).text := text_cur_rec.text;
725 END LOOP;
726 /* end cursor to get text */
727 END IF;
728 END LOOP;
729 /* end looping through detail records */
730
731 tbl_ndx := 0;
732
733 /*BEGIN BUG#1810652 James Bernard */
734 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
735 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
736 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
737 /*END BUG#1810652 */
738 IF (tbl_dtl(loop_counter).rslt_text_code) is not NULL THEN
739 FOR text_cur_rec IN get_text_info (tbl_dtl(loop_counter).rslt_text_code)
740 LOOP
741 tbl_ndx := tbl_ndx + 1;
742
743 tbl_rslt_text(tbl_ndx).gmd_coa_id := tbl_dtl(loop_counter).gmd_coa_id;
744 tbl_rslt_text(tbl_ndx).text_code:=tbl_dtl(loop_counter).rslt_text_code;
745 tbl_rslt_text(tbl_ndx).paragraph_code := text_cur_rec.paragraph_code;
746 tbl_rslt_text(tbl_ndx).line_no := text_cur_rec.line_no;
747 tbl_rslt_text(tbl_ndx).text := text_cur_rec.text;
748
749 END LOOP;
750 /* end cursor to get text */
751 END IF;
752 END LOOP;
753 /* end looping through detail records */
754
755 EXCEPTION
756 WHEN NO_DATA_FOUND THEN
757 NULL;
758 /* there may not be text information for this result rec */
759
760 WHEN OTHERS THEN
761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
762
763 END Populate_Text;
764
765
766 /*###############################################################
767 # NAME
768 # Populate_CoA_Data
769 # SYNOPSIS
770 # proc Populate_CoA_Data
771 # parms event
772 # There is no Orgn_Code in where clauses for cursors because
773 # parameter is ID which identifies orgn code.
774 # (ie would need orgn_code if we had sales_order_no, not id)
775 # DESCRIPTION
776 # populate gmd_coa_coa_hdr with records
777 # HISTORY
778 # 12jun2001 James Bernard Bug 1810652
779 # Commented a line which was not properly commented in the
780 # while <<order loop>>.
781 # In the For cursors tbl_hdr.FIRST and tbl_hdr.LAST are
782 # replaced with NVL(tbl_hdr.FIRST,0) and NVL(tbl_hdr.LAST,0).
783 ################################################################*/
784 PROCEDURE Populate_CoA_Data (
785 p_api_version In NUMBER,
786 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
787 p_commit IN VARCHAR2 := FND_API.G_FALSE,
788 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
789 rec_param IN t_coa_parameters,
790 x_return_status OUT NOCOPY VARCHAR2,
794 tbl_dtl OUT NOCOPY t_coa_detail_tbl,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2,
793 tbl_hdr OUT NOCOPY t_coa_header_tbl,
795 tbl_spec_text OUT NOCOPY t_coa_text_tbl,
796 tbl_rslt_text OUT NOCOPY t_coa_text_tbl) IS
797 CURSOR get_order_info
798 (c_order_id NUMBER,
799 c_bol_id NUMBER,
800 c_shipment_no VARCHAR2,
801 c_org_id NUMBER,
802 c_cust_id NUMBER)
803 IS
804 select l.header_id order_id,
805 l.line_id line_id,
806 wdd.delivery_detail_id,
807 null orgn_code,
808 l.org_id,
809 h.order_number order_no,
810 h.cust_po_number custpo_no,
811 l.schedule_ship_date sched_shipdate,
812 l.actual_shipment_date actual_shipdate,
813 l.ship_to_org_id ,
814 wnd.delivery_id bol_id,
815 wnd.name bol_no,
816 l.inventory_item_id discrete_item_id,
817 ic.item_id item_id,
818 ic.item_no,
819 ic.item_desc1 item_desc1,
820 decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) ship_from_org_id,
821 ship_from_org.organization_code from_whse,
822 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity, l.ordered_quantity ) order_qty1,
823 decode(l.line_category_code,'RETURN',(-1)*l.ordered_quantity2,l.ordered_quantity2) order_qty2,
824 l.order_quantity_uom order_um1,
825 l.ordered_quantity_UOM2 order_um2,
826 wdd.shipped_quantity ship_qty1,
827 wdd.shipped_quantity2 ship_qty2,
828 C.cust_account_id shipcust_id,
829 C.account_number cust_no,
830 pr.party_name cust_name ,
831 0 alloc_qty
832 FROM
833 oe_order_headers_all h,
834 oe_order_lines_all l,
835 wsh_delivery_details wdd,
836 wsh_new_deliveries wnd,
837 wsh_delivery_assignments wda,
838 mtl_parameters ship_from_org,
839 mtl_system_items msi,
840 ic_item_mst ic,
841 hz_cust_accounts c,
842 hz_cust_site_uses_all s,
843 hz_cust_acct_sites_all a,
844 hz_parties pr
845 where h.header_id = l.header_id
846 and l.header_id = wdd.source_header_id
847 and l.line_id = wdd.source_line_id
848 and wnd.delivery_id= wda.delivery_id
849 and wda.delivery_detail_id = wdd.delivery_detail_id
850 and (c_order_id IS NULL OR h.header_id = c_order_id)
851 and (c_bol_id IS NULL OR wnd.delivery_id= c_bol_id)
852 and (c_shipment_no IS NULL OR wnd.name= c_shipment_no)
853 and (c_org_id IS NULL OR a.org_id = c_org_id)
854 and (c_cust_id IS NULL OR l.sold_to_org_id = c_cust_id)
855 --and wnd.name = 'passedname'
856 and wdd.source_code ='OE'
857 and l.ship_from_org_id = ship_from_org.organization_id(+)
858 and ship_from_org.process_enabled_flag(+)='Y'
859 and msi.organization_id=decode(l.ship_from_org_id, null , h.ship_from_org_id, l.ship_from_org_id) -- oe_sys_parameters.value('MASTER_ORGANIZATION_ID')
860 and msi.inventory_item_id = l.inventory_item_id
861 and msi.segment1 = ic.item_no
862 and l.ship_to_org_id = s.site_use_id(+)
863 and s.site_use_code(+) ='SHIP_TO'
864 and s.org_id = a.org_id(+)
865 and s.cust_acct_site_id = a.cust_acct_site_id(+)
866 and a.cust_account_id = c.cust_account_id(+)
867 and c.party_id = pr.party_id(+)
868 order by l.header_id
869 ;
870 /*CURSOR get_order_info
871 (c_order_id NUMBER, c_from_shipdate DATE,
872 c_to_shipdate DATE, c_cust_id NUMBER,
873 c_bol_id NUMBER, c_item_id NUMBER,
874 c_lot_id NUMBER, c_whse_code VARCHAR2)
875 IS
876 select ooh.order_id,
877 ood.line_id,
878 ooh.orgn_code,
879 ooh.order_no,
880 ooh.custpo_no,
881 ood.sched_shipdate,
882 ood.actual_shipdate,
883 ood.shipcust_id,
884 ood.bol_id,
885 ood.item_id,
886 ood.from_whse,
887 ood.generic_id,
888 ood.order_qty1,
889 ood.order_qty2,
890 ood.order_um1,
891 ood.order_um2,
892 ood.ship_qty1,
893 ood.ship_qty2,
894 ood.alloc_qty
895 from
896 op_ordr_hdr ooh,
897 op_ordr_dtl ood
898 where
899 ooh.order_id = ood.order_id
900 and (c_from_shipdate is NULL or
901 ( (ood.sched_shipdate between c_from_shipdate and c_to_shipdate)
902 OR
903 (ood.actual_shipdate between c_from_shipdate and c_to_shipdate)
904 ))
905 and (c_cust_id IS NULL OR ood.shipcust_id = c_cust_id)
906 and (c_order_id IS NULL OR ood.order_id = c_order_id)
907 and (c_bol_id IS NULL OR ood.bol_id = c_bol_id)
908 and (c_item_id IS NULL OR ood.item_id = c_item_id)
909 and ood.ship_status <> -1
910 and ood.delete_mark = 0
911 ; */
912
913
914
915 /*CURSOR get_gnrc_info (c_generic_id op_gnrc_itm.generic_id%TYPE) IS
919 op_gnrc_itm
916 SELECT generic_item,
917 generic_desc
918 FROM
920 WHERE generic_id = c_generic_id
921 and delete_mark = 0
922 ;*/
923
924 /* CURSOR get_bol_info (c_bol_id op_bill_lad.bol_no%TYPE) IS
925 SELECT bol_no
926 FROM
927 op_bill_lad
928 WHERE bol_id = c_bol_id
929 and delete_mark = 0
930 ;*/
931
932 /* CURSOR get_item_info (c_item_id ic_item_mst.item_id%TYPE) IS
933 SELECT item_no,
934 item_desc1
935 FROM
936 ic_item_mst
937 WHERE item_id = c_item_id
938 and delete_mark = 0
939 ;*/
940
941 /*CURSOR get_cust_info (c_cust_id hz_cust_accounts.cust_account_id%TYPE) IS
942 SELECT
943 custsort_no cust_no,
944 cust_name cust_name
945 from op_cust_mst
946 WHERE cust_id = c_cust_id
947 AND delete_mark= 0; */
948
949 /* CURSOR get_cust_info (c_ship_to_org_id OE_ORDER_LINES.SHIP_TO_ORG_ID%TYPE) IS
950 SELECT
951 A.CUST_ACCT_SITE_ID cust_id,
952 C.ACCOUNT_NUMBER cust_no,
953 PR.PARTY_NAME cust_name
954 from
955 HZ_CUST_ACCOUNTS C,
956 HZ_CUST_SITE_USES_ALL S,
957 HZ_CUST_ACCT_SITES_ALL A,
958 HZ_PARTIES PR
959 where C.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
960 AND S.CUST_ACCT_SITE_ID = A.CUST_ACCT_SITE_ID
961 AND S.SITE_USE_CODE IN ('SHIP_TO')
962 AND A.ORG_ID = S.ORG_ID
963 AND C.PARTY_ID = PR.PARTY_ID
964 AND S.SITE_USE_ID = c_ship_to_org_id
965 ;*/
966
967
968 /*CURSOR get_whse_info (c_whse_code ic_whse_mst.WHSE_CODE%TYPE) IS
969 SELECT whse_name
970 FROM
971 ic_whse_mst
972 WHERE
973 whse_code = c_whse_code
974 and delete_mark = 0
975 ;*/
976 CURSOR get_whse_info (c_ship_from_org_id oe_order_lines.ship_from_org_id%TYPE) IS
977 SELECT s.orgn_code,
978 w.whse_code,
979 w.whse_name
980 FROM mtl_parameters p,
981 ic_whse_mst w,
982 sy_orgn_mst s
983 WHERE
984 w.mtl_organization_id = c_ship_from_org_id
985 AND p.ORGANIZATION_ID = c_ship_from_org_id
986 AND s.orgn_code = w.orgn_code
987 AND s.orgn_code = p.process_orgn_code
988 AND p.process_enabled_flag ='Y'
989 AND s.delete_mark = 0
990 AND w.delete_mark = 0
991 ;
992
993
994 CURSOR get_lot_tran (c_line_id ic_tran_pnd.line_id%TYPE) IS
995 SELECT itp.lot_id, itp.whse_code, itp.location
996 FROM
997 ic_tran_pnd itp
998 WHERE
999 itp.doc_type = 'OMSO'
1000 AND itp.completed_ind <> -1
1001 AND itp.line_detail_id = c_line_id
1002 and itp.delete_mark = 0
1003 ;
1004
1005 CURSOR get_lot_info (c_lot_id ic_lots_mst.lot_id%TYPE) IS
1006 SELECT ilm.lot_no,
1007 ilm.lot_desc,
1008 ilm.sublot_no
1009 FROM
1010 ic_lots_mst ilm
1011 WHERE
1012 ilm.lot_id = c_lot_id
1013 and ilm.delete_mark = 0
1014 ;
1015
1016 ord_cur_rec get_order_info % ROWTYPE;
1017
1018 tbl_ndx BINARY_INTEGER := 0;
1019 /* index for pl/sql table */
1020 l_api_name CONSTANT VARCHAR2(30) := 'Populate_CoA_Data';
1021 l_api_version CONSTANT NUMBER := 1.6;
1022
1023
1024 /* **************************** main code **************************** */
1025 BEGIN
1026 /* select NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'Y')
1027 INTO l_debug_enabled FROM sys.DUAL; */
1028 --Bug 3222090, magupta removed call to FND_PROFILE.VALUE('AFLOG_ENABLED')
1029 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1030 l_debug_enabled := 'Y';
1031 end if;
1032
1033
1034 /* Do API standard code for savepoint, messages, initialize return status */
1035 SAVEPOINT Populate_CoA_Data_SAVE;
1036 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1037 l_api_name, G_PKG_NAME) THEN
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END IF;
1040 IF FND_API.to_Boolean (p_init_msg_list) THEN
1041 FND_MSG_PUB.initialize;
1042 END IF;
1043 x_return_status := FND_API.G_RET_STS_SUCCESS;
1044 v_report_title := 'COA';
1045 /*IF (l_debug_enabled = 'Y') THEN
1046 select value into l_utl_file_dir from v$parameter where name like 'utl_file_dir';
1047 fnd_file.put_names('coacoc.log','coacoc.out',l_utl_file_dir);
1048 Trace('Log file is '||l_utl_file_dir||'coacoc.log');
1049 END IF; */
1050 /*fnd_file.put_names(test.log,test.out,/home/magupta/work/qc/); */
1051 --gmd_debug.Log_Initialize('coacocpackage');
1052 trace('Populate_CoA_Data :- delivery_id '||rec_param.bol_id);
1053 trace('The value of master organization id is '||oe_sys_parameters.value('MASTER_ORGANIZATION_ID'));
1054 IF rec_param.shipment_no is not NULL or rec_param.order_id is not NULL or rec_param.bol_id is not NULL
1058 rec_param.bol_id,
1055 or rec_param.from_shipdate is not NULL or rec_param.org_id IS NOT NULL or rec_param.item_id IS NOT NULL THEN
1056 /*- or rec_param.cust_po is not NULL */
1057 OPEN get_order_info (rec_param.order_id,
1059 rec_param.shipment_no,
1060 rec_param.org_id,
1061 rec_param.cust_id
1062 );
1063 FETCH get_order_info INTO ord_cur_rec;
1064
1065 <<order_loop>>
1066 WHILE ( get_order_info % FOUND ) LOOP
1067 tbl_ndx := tbl_ndx + 1;
1068 /* BEGIN BUG#1810652 James Bernard */
1069 /* Properly commented the next line. Prior to the fix there */
1070 /* was only the opening slash and asterisk */
1071 /* replace with next_val.sequence */
1072 /* END BUG#1810652 */
1073 tbl_hdr(tbl_ndx).gmd_coa_id := tbl_ndx;
1074 tbl_hdr(tbl_ndx).org_id := ord_cur_rec.org_id;
1075 tbl_hdr(tbl_ndx).order_id := ord_cur_rec.order_id;
1076 tbl_hdr(tbl_ndx).line_id := ord_cur_rec.line_id;
1077 tbl_hdr(tbl_ndx).delivery_detail_id := ord_cur_rec.delivery_detail_id;
1078 -- tbl_hdr(tbl_ndx).orgn_code := ord_cur_rec.orgn_code;
1079 tbl_hdr(tbl_ndx).order_no := ord_cur_rec.order_no;
1080 tbl_hdr(tbl_ndx).custpo_no := ord_cur_rec.custpo_no;
1081 tbl_hdr(tbl_ndx).cust_id := ord_cur_rec.shipcust_id;
1082 tbl_hdr(tbl_ndx).bol_id := ord_cur_rec.bol_id;
1083 tbl_hdr(tbl_ndx).item_id := ord_cur_rec.item_id;
1084 -- tbl_hdr(tbl_ndx).whse_code := ord_cur_rec.from_whse;
1085 tbl_hdr(tbl_ndx).order_qty1 := ord_cur_rec.order_qty1;
1086 tbl_hdr(tbl_ndx).order_qty2 := ord_cur_rec.order_qty2;
1087 tbl_hdr(tbl_ndx).order_um1 := ord_cur_rec.order_um1;
1088 tbl_hdr(tbl_ndx).order_um2 := ord_cur_rec.order_um2;
1089 tbl_hdr(tbl_ndx).ship_qty1 := ord_cur_rec.ship_qty1;
1090 tbl_hdr(tbl_ndx).ship_qty2 := ord_cur_rec.ship_qty2;
1091 tbl_hdr(tbl_ndx).cust_no := ord_cur_rec.cust_no;
1092 tbl_hdr(tbl_ndx).cust_name := ord_cur_rec.cust_name;
1093 tbl_hdr(tbl_ndx).bol_no := ord_cur_rec.bol_no;
1094 tbl_hdr(tbl_ndx).item_no := ord_cur_rec.item_no;
1095 tbl_hdr(tbl_ndx).item_desc := ord_cur_rec.item_desc1;
1096 tbl_hdr(tbl_ndx).ship_from_org_id := ord_cur_rec.ship_from_org_id;
1097 /* if there is an actual date, use it; otherwise use scheduled date */
1098
1099
1100
1101 IF ord_cur_rec.actual_shipdate is NULL THEN
1102 tbl_hdr(tbl_ndx).shipdate := ord_cur_rec.sched_shipdate;
1103 ELSE
1104 tbl_hdr(tbl_ndx).shipdate := ord_cur_rec.actual_shipdate;
1105 END IF;
1106 IF tbl_hdr(tbl_ndx).ship_from_org_id is not NULL THEN
1107 FOR whse_cur_rec IN get_whse_info (tbl_hdr(tbl_ndx).ship_from_org_id) LOOP
1108 tbl_hdr(tbl_ndx).whse_code := whse_cur_rec.whse_code;
1109 tbl_hdr(tbl_ndx).whse_name := whse_cur_rec.whse_name;
1110 tbl_hdr(tbl_ndx).orgn_code := whse_cur_rec.orgn_code;
1111 END LOOP;
1112 /* end getting warehouse info from ic_whse_mst */
1113 END IF;
1114
1115
1116
1117 /* if generic id exists in sales order record, get item no and desc */
1118 /* from op_gnrc_itm, else get item no and desc from ic_item_mst */
1119
1120 -- IF ord_cur_rec.generic_id is not NULL THEN
1121 -- FOR gnrc_cur_rec IN get_gnrc_info (ord_cur_rec.generic_id) LOOP
1122 -- tbl_hdr(tbl_ndx).item_no := gnrc_cur_rec.generic_item;
1123 -- tbl_hdr(tbl_ndx).item_desc := gnrc_cur_rec.generic_desc;
1124 -- END LOOP;
1125 -- /* end getting generic item info from table */
1126 -- ELSE
1127 -- FOR item_cur_rec IN get_item_info (ord_cur_rec.item_id) LOOP
1128 -- tbl_hdr(tbl_ndx).item_no := item_cur_rec.item_no;
1129 -- tbl_hdr(tbl_ndx).item_desc := item_cur_rec.item_desc1;
1130 -- END LOOP;
1131 /* end getting item info from ic_item_mst */
1132 -- END IF;
1133 /* end if generic_id has a value */
1134 /* fill in whse, customer name, bol no at end of code */
1135
1136 /* get lot id from ic_tran_pnd */
1137 /* get lot no and name and sublot no from ic_lot_mst at end of code */
1138 /* (2 steps so non-sales-order loop can also use get_lot_info) */
1139
1140 -- IF ord_cur_rec.alloc_qty > 0 THEN
1141 FOR lot_cur_rec IN get_lot_tran (ord_cur_rec.delivery_detail_id) LOOP
1142 tbl_hdr(tbl_ndx).lot_id := lot_cur_rec.lot_id;
1143 END LOOP;
1144 /* end getting lot id from ic_tran_pnd */
1145 IF tbl_hdr(tbl_ndx).lot_id is not NULL THEN
1146 FOR lot_cur_rec IN get_lot_info (tbl_hdr(tbl_ndx).lot_id) LOOP
1147 tbl_hdr(tbl_ndx).lot_no := lot_cur_rec.lot_no;
1148 tbl_hdr(tbl_ndx).lot_desc := lot_cur_rec.lot_desc;
1149 tbl_hdr(tbl_ndx).sublot_no := lot_cur_rec.sublot_no;
1150 END LOOP;
1151 /* end getting lot info from ic_lot_mst */
1152 END IF;
1153 /* end checking if lot id has a value */
1154
1155 -- END IF;
1159 p_msg := 'Populate_CoA_Data get_order_info gmd_coa_id '||tbl_hdr(tbl_ndx).gmd_coa_id||' '||
1156 /* end checking if alloc qty > 0 */
1157 /* report title = COA or COC */
1158 tbl_hdr(tbl_ndx).report_title := v_report_title;
1160 'org_id '||tbl_hdr(tbl_ndx).org_id||' '||
1161 'order_id '||tbl_hdr(tbl_ndx).order_id||' '||
1162 'line_id '||tbl_hdr(tbl_ndx).line_id||' '||
1163 'order_no '||tbl_hdr(tbl_ndx).order_no||' '||
1164 'custpo_no '||tbl_hdr(tbl_ndx).custpo_no||' '||
1165 'cust_id '||tbl_hdr(tbl_ndx).cust_id||' '||
1166 'bol_id '||tbl_hdr(tbl_ndx).bol_id||' '||
1167 'item_id '||tbl_hdr(tbl_ndx).item_id||' '||
1168 'order_id '||tbl_hdr(tbl_ndx).order_id||' '||
1169 'whse_code '||tbl_hdr(tbl_ndx).whse_code||' '||
1170 'order_qty1 '||tbl_hdr(tbl_ndx).order_qty1||' '||
1171 'order_qty2 '||tbl_hdr(tbl_ndx).order_qty2||' '||
1172 'order_um1 '||tbl_hdr(tbl_ndx).order_um1||' '||
1173 'ship_qty1 '||tbl_hdr(tbl_ndx).ship_qty1||' '||
1174 'ship_qty2 '||tbl_hdr(tbl_ndx).ship_qty2||' '||
1175 'cust_no '||tbl_hdr(tbl_ndx).cust_no||' '||
1176 'cust_name '||tbl_hdr(tbl_ndx).cust_name||' '||
1177 'bol_no '||tbl_hdr(tbl_ndx).bol_no||' '||
1178 'item_no '||tbl_hdr(tbl_ndx).item_no||' '||
1179 'lot_id '||tbl_hdr(tbl_ndx).lot_id||' '||
1180 'lot_no '||tbl_hdr(tbl_ndx).lot_no||' '||
1181 'whse_code '||tbl_hdr(tbl_ndx).whse_code||' '||
1182 'orgn_code '||tbl_hdr(tbl_ndx).orgn_code||' '||
1183 'item_desc '||tbl_hdr(tbl_ndx).item_desc;
1184
1185 trace(p_msg);
1186
1187
1188 FETCH get_order_info INTO ord_cur_rec;
1189
1190 END LOOP order_loop;
1191
1192 CLOSE get_order_info;
1193
1194 END IF;
1195
1196
1197
1198 IF v_report_title <> 'BLK' THEN
1199 trace('Populate_CoA_Data :- Going to GMD_COA_DATA_OM.Populate_Details');
1200 GMD_COA_DATA_OM.Populate_Details (tbl_hdr, tbl_dtl);
1201 trace('Populate_CoA_Data :- Came Back from GMD_COA_DATA_OM.Populate_Details');
1202
1203 END IF;
1204
1205 IF tbl_dtl.EXISTS(1) THEN
1206 GMD_COA_DATA_OM.Populate_Text (tbl_dtl, tbl_spec_text, tbl_rslt_text);
1207 ELSE
1208 tbl_hdr := empty_header;
1209 /* if there is nothing in details, empty headers */
1210 /* this is not an error. Report will be empty */
1211 tbl_ndx := 0;
1212 END IF;
1213
1214 /* *********************************************************************** -- */
1215 /* now fill in lookup columns (whse name, customer name, bol_no, lot_no) */
1219 IF tbl_ndx > 0 THEN
1216 /* and make sure report_title is accurate (gets a little messy if */
1217 /* sales/ship info given and no results (CoC) */
1218
1220
1221 /*BEGIN BUG#1810652 James Bernard */
1222 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
1223 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
1224 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
1225 /*END BUG#1810652 */
1226
1227 /* if sales/ship data given and no results, only specs exists, */
1228 /* reset report_title in tbl_hdr to v_report_title (tbl_hdr is IN */
1229 /* var to Populate_Details, so cannot change report_title there.) */
1230
1231 tbl_hdr(loop_counter).report_title := v_report_title;
1232
1233 /* get bol (shipping no) from op_bill_lad */
1234
1235 -- IF tbl_hdr(loop_counter).bol_id is not NULL THEN
1236 -- FOR bol_cur_rec IN get_bol_info (tbl_hdr(loop_counter).bol_id) LOOP
1237 -- tbl_hdr(loop_counter).bol_no := bol_cur_rec.bol_no;
1238 -- END LOOP;
1239 /* end getting bol (shipping) info from op_bill_lad */
1240 -- END IF;
1241 /* end checking if bol id has a value */
1242 /* get customer no and name from op_cust_mst tables */
1243
1244 -- IF tbl_hdr(loop_counter).cust_id is not NULL THEN
1245 -- FOR cust_cur_rec IN get_cust_info (tbl_hdr(loop_counter).cust_id) LOOP
1246 -- tbl_hdr(loop_counter).cust_no := cust_cur_rec.cust_no;
1247 -- tbl_hdr(loop_counter).cust_name := cust_cur_rec.cust_name;
1248 -- END LOOP;
1249 /* end getting cust info from hz tables */
1250 -- END IF;
1251 /* end checking if cust id has a value */
1252
1253 /* get item no and description from ic_item_mst if it was not */
1254 /* selected as part of generic item check. */
1255 /* It is correct to check item_NO, not item_ID! */
1256
1257 -- IF tbl_hdr(loop_counter).item_no is NULL THEN
1258 -- FOR item_cur_rec IN get_item_info (tbl_hdr(loop_counter).item_id) LOOP
1259 -- tbl_hdr(loop_counter).item_no := item_cur_rec.item_no;
1260 -- tbl_hdr(loop_counter).item_desc := item_cur_rec.item_desc1;
1261 -- END LOOP;
1262 /* end getting item info from ic_item_mst */
1263 -- END IF;
1264 /* end checking if item id has a value */
1265
1266
1267 /* get lot no, sublot no and lot desc from ic_lots_mst */
1268 /* if pkg called from report, lot id must have value */
1269 /* else if called from opm portal, lot may be null */
1270 -- IF tbl_hdr(loop_counter).lot_id is not NULL THEN
1271 -- FOR lot_cur_rec IN get_lot_info (tbl_hdr(loop_counter).lot_id) LOOP
1272 -- tbl_hdr(loop_counter).lot_no := lot_cur_rec.lot_no;
1273 -- tbl_hdr(loop_counter).lot_desc := lot_cur_rec.lot_desc;
1274 -- tbl_hdr(loop_counter).sublot_no := lot_cur_rec.sublot_no;
1275 -- END LOOP;
1276 /* end getting lot info from ic_lot_mst */
1277 -- END IF;
1278 /* end checking if lot id has a value */
1279 END LOOP;
1280 /* end loop to fill in lookup columns */
1281 ELSE /* no header records found */
1282 tbl_hdr := empty_header;
1283 /* this is not an error. Report will be empty */
1284 END IF;
1288 IF FND_API.To_Boolean(p_commit) THEN
1285 /* end if there are any records in header table */
1286
1287 /* -- standard check of p_commit */
1289 COMMIT WORK;
1290 END IF;
1291 /* -- standard call to get message count and if count is 1, get message info */
1292 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1293 --fnd_file.close;
1294 EXCEPTION
1295 WHEN FND_API.G_EXC_ERROR THEN
1296 ROLLBACK TO Populate_CoA_Data_Save;
1297 x_return_status := FND_API.G_RET_STS_ERROR;
1298 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1299
1300 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1301 ROLLBACK TO Populate_CoA_Data_Save;
1302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1303 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1304
1305 WHEN OTHERS THEN
1306 ROLLBACK TO Populate_CoA_Data_Save;
1307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1308 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1309 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1310
1311 END Populate_CoA_Data;
1312
1313
1314 /*###############################################################
1315 # NAME
1316 # dump_to_db_tables
1317 # SYNOPSIS
1318 # proc dump_to_db_tables
1319 # parms event
1320 # This is a debug procedure to put data from plsql tables into
1321 # physical database tables.
1322 # p_commit should always be TRUE
1323 # DESCRIPTION
1324 # HISTORY
1325 # 12jun2001 James Bernard Bug 1810652
1326 # In the For cursors tbl_hdr.FIRST, tbl_hdr.LAST ,tbl_spec_text.FIRST,
1327 # tbl_spec_text.LAST,tbl_rslt_text.FIRST and tbl_rslt_text.LAST
1328 # are replaced with NVL(tbl_hdr.FIRST,0).
1329 # NVL(tbl_hdr.LAST,0),NVL(tbl_spec_text.FIRST,0)
1330 # NVL(tbl_spec_text.LAST,0),NVL(tbl_rslt_text.FIRST,0) and
1331 # NVL(tbl_rslt_text.LAST,0) respectively.
1332 ################################################################*/
1333
1334
1335 PROCEDURE Dump_To_Db_Tables (
1336 p_api_version In NUMBER,
1337 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1338 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1339 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1340 tbl_hdr IN t_coa_header_tbl,
1341 tbl_dtl IN t_coa_detail_tbl,
1342 tbl_spec_text IN t_coa_text_tbl,
1343 tbl_rslt_text IN t_coa_text_tbl,
1344 x_return_status OUT NOCOPY VARCHAR2,
1345 x_msg_count OUT NOCOPY NUMBER,
1346 x_msg_data OUT NOCOPY VARCHAR2) IS
1347
1348 X_user_id NUMBER;
1349 X_login_id NUMBER;
1350 X_date DATE;
1351 l_api_name CONSTANT VARCHAR2(30) := 'Dump_To_Db_Tables';
1352 l_api_version CONSTANT NUMBER := 1.6;
1353
1354 begin
1355
1356 IF FND_API.to_Boolean (p_init_msg_list) THEN
1357 FND_MSG_PUB.initialize;
1358 END IF;
1359 x_return_status := FND_API.G_RET_STS_SUCCESS;
1360
1361 /* ******* if db table is necessary and for debugging ***** -- */
1362
1363 delete from gmd_coa_headers;
1364 delete from gmd_coa_details;
1365 delete from gmd_coa_spec_text;
1366 delete from gmd_coa_rslt_text;
1367
1368 /* -- Do API standard code for savepoint, messages, initialize return status */
1369 SAVEPOINT Dump_To_Db_Tables_SAVE;
1370 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1371 l_api_name, G_PKG_NAME) THEN
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1373 END IF;
1374
1375 IF tbl_hdr.FIRST is not NULL THEN
1376 X_user_id := FND_GLOBAL.USER_ID;
1377 X_login_id := FND_GLOBAL.LOGIN_ID;
1378
1379 /*BEGIN BUG#1810652 James Bernard */
1380 /*Changed tbl_hdr.FIRST to NVL(tbl_hdr.FIRST,0) and */
1381 /*tbl_hdr.LAST to NVL(tbl_hdr.LAST,0) */
1382 FOR loop_counter IN NVL(tbl_hdr.FIRST,0) .. NVL(tbl_hdr.LAST,0) LOOP
1383 /*END BUG#1810652 */
1384 INSERT into gmd_coa_headers (gmd_coa_id, order_id, line_id, orgn_code,
1385 order_no,
1386 custpo_no,
1387 shipdate, cust_id, cust_no, cust_name,
1388 bol_id, bol_no, item_id,
1389 item_no, item_desc1,
1390 whse_code, whse_name,
1391 lot_id, lot_no, lot_desc, sublot_no,
1392 order_qty1, order_um1, order_qty2,
1393 order_um2, ship_qty1, ship_qty2,
1394 report_title,
1395 created_by, creation_date, last_update_date,
1396 last_updated_by, last_update_login)
1397 VALUES (tbl_hdr(loop_counter).gmd_coa_id,
1398 tbl_hdr(loop_counter).order_id,
1399 tbl_hdr(loop_counter).line_id,
1400 tbl_hdr(loop_counter).orgn_code,
1401 tbl_hdr(loop_counter).order_no,
1402 tbl_hdr(loop_counter).custpo_no,
1403 tbl_hdr(loop_counter).shipdate,
1404 tbl_hdr(loop_counter).cust_id,
1405 tbl_hdr(loop_counter).cust_no,
1406 tbl_hdr(loop_counter).cust_name,
1407 tbl_hdr(loop_counter).bol_id,
1408 tbl_hdr(loop_counter).bol_no,
1409 tbl_hdr(loop_counter).item_id,
1413 tbl_hdr(loop_counter).whse_name,
1410 tbl_hdr(loop_counter).item_no,
1411 tbl_hdr(loop_counter).item_desc,
1412 tbl_hdr(loop_counter).whse_code,
1414 tbl_hdr(loop_counter).lot_id,
1415 tbl_hdr(loop_counter).lot_no,
1416 tbl_hdr(loop_counter).lot_desc,
1417 tbl_hdr(loop_counter).sublot_no,
1418 tbl_hdr(loop_counter).order_qty1,
1419 tbl_hdr(loop_counter).order_um1,
1420 tbl_hdr(loop_counter).order_qty2,
1421 tbl_hdr(loop_counter).order_um2,
1422 tbl_hdr(loop_counter).ship_qty1,
1423 tbl_hdr(loop_counter).ship_qty2,
1424 tbl_hdr(loop_counter).report_title,
1425 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
1426 );
1427 END LOOP;
1428
1429 IF tbl_dtl.FIRST is not NULL THEN
1430 /* -- ******* if db table is necessary and for debugging ***** -- */
1431 /*BEGIN BUG#1810652 James Bernard */
1432 /*Changed tbl_dtl.FIRST to NVL(tbl_dtl.FIRST,0) and */
1433 /*tbl_dtl.LAST to NVL(tbl_dtl.LAST,0) */
1434 FOR loop_counter IN NVL(tbl_dtl.FIRST,0) .. NVL(tbl_dtl.LAST,0) LOOP
1435 /*END BUG#1810652 */
1436 INSERT into gmd_coa_details (gmd_coa_id, qc_result_id, result_date,
1437 qc_spec_id, assay_code, assay_desc,
1438 result, specification, uom,
1439 rslt_text_code, spec_text_code,
1440 created_by, creation_date,
1441 last_update_date,
1442 last_updated_by, last_update_login
1443 )
1444 VALUES (tbl_dtl(loop_counter).gmd_coa_id,
1445 tbl_dtl(loop_counter).qc_result_id,
1446 tbl_dtl(loop_counter).result_date,
1447 tbl_dtl(loop_counter).qc_spec_id,
1448 tbl_dtl(loop_counter).assay_code,
1449 tbl_dtl(loop_counter).assay_desc,
1450 tbl_dtl(loop_counter).result,
1451 tbl_dtl(loop_counter).specification,
1452 tbl_dtl(loop_counter).uom,
1453 tbl_dtl(loop_counter).rslt_text_code,
1454 tbl_dtl(loop_counter).spec_text_code,
1455 X_user_id, SYSDATE, SYSDATE, X_user_id, X_login_id
1456 );
1457 END LOOP;
1458 IF tbl_spec_text.FIRST is not NULL THEN
1459 /*BEGIN BUG#1810652 James Bernard */
1460 /*Changed tbl_spec_text.FIRST to NVL(tbl_spec_text.FIRST,0) */
1461 /* and tbl_spec_text.LAST to NVL(tbl_spec_text.LAST,0) */
1462 FOR loop_counter IN NVL(tbl_spec_text.FIRST,0) .. NVL(tbl_spec_text.LAST,0) LOOP
1463 /*END BUG#1810652 */
1464 INSERT into gmd_coa_spec_text (gmd_coa_id, text_code,
1465 paragraph_code, line_no, text)
1466 VALUES (tbl_spec_text(loop_counter).gmd_coa_id,
1467 tbl_spec_text(loop_counter).text_code,
1468 tbl_spec_text(loop_counter).paragraph_code,
1469 tbl_spec_text(loop_counter).line_no,
1470 tbl_spec_text(loop_counter).text);
1471 END LOOP;
1472 END IF;
1473 /* if there is any spec text */
1474 IF tbl_rslt_text.FIRST is not NULL THEN
1475 /*BEGIN BUG#1810652 James Bernard */
1476 /*Changed tbl_rslt_text.FIRST to NVL(tbl_rslt_text.FIRST,0) and */
1477 /*tbl_rslt_text.LAST to NVL(tbl_rslt_text.LAST,0) */
1478 FOR loop_counter IN NVL(tbl_rslt_text.FIRST,0) .. NVL(tbl_rslt_text.LAST,0) LOOP
1479 /*END BUG#1810652 */
1480 INSERT into gmd_coa_rslt_text (gmd_coa_id, text_code,
1481 paragraph_code, line_no, text)
1482 VALUES (tbl_rslt_text(loop_counter).gmd_coa_id,
1483 tbl_rslt_text(loop_counter).text_code,
1484 tbl_rslt_text(loop_counter).paragraph_code,
1485 tbl_rslt_text(loop_counter).line_no,
1486 tbl_rslt_text(loop_counter).text);
1487 END LOOP;
1488 END IF;
1489 /* if there is any results text */
1490 ELSE
1491 NULL;
1492 /* empty table is not an error */
1493 END if;
1494 /* if there is something in details table */
1495 ELSE
1496 NULL;
1497 /* empty table is not an error */
1498 END IF;
1499 /* if there is something in header table */
1500
1501 /* -- standard check of p_commit */
1502 IF FND_API.To_Boolean(p_commit) THEN
1503 COMMIT WORK;
1504 END IF;
1505
1506 /* -- standard call to get message count and if count is 1, get message info */
1507 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1508
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 ROLLBACK TO Dump_To_Db_Tables_Save;
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1514
1515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1516 ROLLBACK TO Dump_To_Db_Tables_Save;
1517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1518 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1519
1520 WHEN OTHERS THEN
1521 ROLLBACK TO Dump_To_Db_Tables_Save;
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 END Dump_To_Db_Tables;
1523 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1524 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
1525
1527
1528
1529
1530
1531
1532
1533
1534
1535 PROCEDURE run_coa_coc (
1536 errbuf OUT NOCOPY VARCHAR2,
1537 retcode OUT NOCOPY NUMBER,
1538 p_delivery_id number) IS
1539 --errbuf VARCHAR2(200);
1540 --retcode NUMBER;
1541
1542 -- If no spec data is found, the gmd_coa_headers table will be empty
1543
1544 CURSOR get_report_title IS
1545 select c.report_title, meaning
1546 from gem_lookups l, gmd_coa_headers c
1547 where l.lookup_type = 'GMD_COA_REPORT_TITLE'
1548 and l.lookup_code = c.report_title;
1549
1550 CURSOR get_report_title_blank IS
1551 select meaning
1552 from gem_lookups l
1553 where l.lookup_type = 'GMD_COA_REPORT_TITLE'
1554 and l.lookup_code = 'BLK';
1555
1556
1557 param_rec gmd_coa_data_om.t_coa_parameters;
1558 header_table gmd_coa_data_om.t_coa_header_tbl;
1559 detail_table gmd_coa_data_om.t_coa_detail_tbl;
1560 spec_text_table gmd_coa_data_om.t_coa_text_tbl;
1561 rslt_text_table gmd_coa_data_om.t_coa_text_tbl;
1562
1563 X_status BOOLEAN;
1564 X_conc_id NUMBER;
1565 X_which_report VARCHAR2(80);
1566 X_report_title VARCHAR2(30);
1567
1568 x_return_status VARCHAR2(1);
1569 x_msg_count NUMBER;
1570 x_msg_data VARCHAR2(2000);
1571
1572 p_init_msg_list VARCHAR2(1);
1573 p_commit VARCHAR2(1);
1574 p_validation_level NUMBER;
1575 p_api_version_populate CONSTANT NUMBER := 1.5;
1576 p_api_version_dump CONSTANT NUMBER := 1.5;
1577 rqid NUMBER:=0;
1578 rdata VARCHAR2(10);
1579 l_i NUMBER;
1580
1581 begin
1582
1583 param_rec.order_id := null; --:qcrcoa01.order_id;
1584 param_rec.orgn_code := null;--:qcrcoa01.orgn_code;
1585 param_rec.from_shipdate := null; --:qcrcoa01.from_shipdate;
1586 param_rec.to_shipdate :=null; -- :qcrcoa01.to_shipdate;
1587 param_rec.cust_id :=null; -- :qcrcoa01.shipcust_id;
1588 param_rec.bol_id :=p_delivery_id; -- :qcrcoa01.bol_id;
1589 param_rec.item_id :=null; -- :qcrcoa01.item_id;
1590 param_rec.whse_code :=null; -- :qcrcoa01.whse_code;
1591 param_rec.lot_id :=null; -- :qcrcoa01.lot_id;
1592 param_rec.shipment_no := null;
1593
1594 p_init_msg_list := 'T';
1595 p_commit := 'T';
1596 p_validation_level := 0;
1597 Trace('run_coa_coc : Going to gmd_coa_data_om.populate_coa_data, Shipment No '|| param_rec.shipment_no);
1598 gmd_coa_data_om.populate_coa_data (
1599 p_api_version_populate,
1600 p_init_msg_list,
1601 p_commit,
1602 p_validation_level,
1603 param_rec,
1604 x_return_status, x_msg_count, x_msg_data,
1605 header_table, detail_table,
1606 spec_text_table, rslt_text_table);
1607
1608 Trace('run_coa_coc : Coming from gmd_coa_data_om.populate_coa_data, x_return_status'||x_return_status);
1609 IF x_return_status = 'S' THEN -- #1
1610
1611 -- check if plsql tables have any data.
1612 -- If so, do dump to db tables and call concurrent manager for report.
1613 -- else give user 'blank report' message.
1614
1615 p_init_msg_list := 'F';
1616 Trace('run_coa_coc : calling gmd_coa_data_om.dump_to_db_tables ');
1617 gmd_coa_data_om.dump_to_db_tables (
1618 p_api_version_dump,
1619 p_init_msg_list,
1620 p_commit,
1621 p_validation_level,
1622 header_table, detail_table,
1623 spec_text_table, rslt_text_table,
1624 x_return_status, x_msg_count, x_msg_data);
1625 Trace('run_coa_coc : returning from gmd_coa_data_om.dump_to_db_tables, x_return_status'||x_return_status);
1626
1627
1628 IF x_return_status = 'S' THEN -- #2
1629 OPEN get_report_title;
1630 FETCH get_report_title into x_report_title, x_which_report;
1631 CLOSE get_report_title;
1632
1633 IF x_report_title IS NOT NULL THEN -- #3
1634
1635 rqid := FND_GLOBAL.CONC_REQUEST_ID;
1636 rdata := fnd_conc_global.request_data;
1637 if (rdata is not null) then
1638 errbuf := 'Done';
1639 retcode := 0 ;
1640 return;
1641 else
1642 l_i := 1;
1643 end if;
1644
1645 X_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMD','QCRCOA02','', '',TRUE,
1646 to_char(0),
1647 '','','','','','','','','',
1648 '','','','','','','','','','',
1649 '','','','','','','','','','',
1650 '','','','','','','','','','',
1651 '','','','','','','','','','',
1652 '','','','','','','','','','',
1653 '','','','','','','','','','',
1654 '','','','','','','','','','',
1655 '','','','','','','','','','',
1656 '','','','','','','','','','');
1657 IF X_conc_id = 0 THEN -- #4
1658 null;
1659 errbuf := 'Unable to submit concurrent request for QCRCOA02 ...';
1660 retcode := -1;
1661 ELSE
1662 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1663 request_data => l_i);
1664 errbuf := 'Sub Requests Submitted.';
1665 retcode := 0;
1666
1667
1668 END IF; -- end if #4 concurrent manager returned conc_id or not
1669 ELSE -- x_report_title IS NULL #3
1670 OPEN get_report_title_blank;
1671 FETCH get_report_title_blank into x_which_report;
1672 CLOSE get_report_title_blank;
1673 END IF; -- end if #3 plsql table has rows (data found)
1674 END IF; -- end if #2 stored procedure dump-to-db ended with success
1675
1676 -- coa table will have 3-char code for if stored procedure found COA
1677 -- data or COC data. Pass lookup value for that code to message to
1678 -- be displayed so user knows which one is being run.
1679 /*writelog('GMD_COA_REPORT_SUBMITTED','GMD' , 'COA_REPORT_TITLE', x_which_report); */
1680 END IF; -- end if (#1) 1st api call retured success
1681
1682 IF x_return_status <> 'S' THEN
1683
1684 errbuf := 'Error';
1685 retcode :=-1;
1686
1687 END IF; -- end if api returned Success or not
1688
1689 END run_coa_coc;
1690
1691
1692
1693 END GMD_CoA_Data_OM;
[Home] [Help]