[Home] [Help]
PACKAGE BODY: APPS.POS_ASN_REVIEW_PKG
Source
1 PACKAGE BODY pos_asn_review_pkg AS
2 /* $Header: POSASNRB.pls 115.1 99/10/14 16:18:39 porting shi $ */
3
4 g_header_results_table ak_query_pkg.results_table_type;
5 g_header_items_table ak_query_pkg.items_table_type;
6
7 g_inv_hdr_results_table ak_query_pkg.results_table_type;
8 g_inv_hdr_items_table ak_query_pkg.items_table_type;
9
10 g_lines_results_table ak_query_pkg.results_table_type;
11 g_lines_items_table ak_query_pkg.items_table_type;
12
13 g_seq_results_table ak_query_pkg.results_table_type;
14 g_seq_items_table ak_query_pkg.items_table_type;
15
16 g_ship_results_table ak_query_pkg.results_table_type;
17 g_ship_items_table ak_query_pkg.items_table_type;
18
19 g_inv_dtl_results_table ak_query_pkg.results_table_type;
20 g_inv_dtl_items_table ak_query_pkg.items_table_type;
21
22 PROCEDURE header_section;
23 PROCEDURE lines_section;
24
25 PROCEDURE PrintResult(p_result_index IN NUMBER,
26 p_results_table IN ak_query_pkg.results_table_type,
27 p_items_table IN ak_query_pkg.items_table_type);
28
29 PROCEDURE DupResultTable(p_source in ak_query_pkg.results_table_type,
30 p_dest in out ak_query_pkg.results_table_type);
31
32 PROCEDURE DupItemTable(p_source in ak_query_pkg.items_table_type,
33 p_dest in out ak_query_pkg.items_table_type);
34
35 function get_result_value(p_table in ak_query_pkg.results_table_type,
36 p_index in number,
37 p_col in number) return varchar2;
38
39 function valid_invoice_num(p_session_id in number) return boolean;
40
41 -- Body
42
43 PROCEDURE review_page(p_submit IN VARCHAR2 DEFAULT 'N') IS
44
45 l_language VARCHAR2(5);
46 l_script_name VARCHAR2(240);
47 l_org_id NUMBER;
48 l_user_id NUMBER;
49 l_session_id NUMBER;
50 l_responsibility_id NUMBER;
51
52 l_where_clause VARCHAR2(2000);
53 l_total_rows NUMBER := 0;
54 l_result_index NUMBER;
55
56 BEGIN
57
58 IF NOT icx_sec.validatesession THEN
59 RETURN;
60 END IF;
61
62 l_org_id := icx_sec.getID(icx_sec.PV_ORG_ID);
63 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
64 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
65 l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
66 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
67 l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
68
69 fnd_global.apps_initialize(l_user_id, l_responsibility_id, 178);
70
71 htp.htmlOpen;
72
73 htp.headOpen;
74 icx_util.copyright;
75 htp.linkRel('STYLESHEET', '/OA_HTML/' || l_language || '/POSSTYLE.css');
76 htp.p('<script src="/OA_HTML/POSASNED.js" language="JavaScript"></script>');
77 htp.headClose;
78
79 if p_submit = 'Y' then
80
81 pos_asn.submit;
82
83 htp.p('<body onLoad="javascript:LoadPage(' ||
84 '''' || pos_asn.sub_state || '''' || ',' ||
85 '''' || pos_asn.error_message || '''' || ',' ||
86 '''' || pos_asn.but1 || '''' || ',' ||
87 '''' || pos_asn.but2 || '''' || ',' ||
88 '''' || pos_asn.but3 || '''' ||
89 ')">');
90 else
91
92 htp.p('<body>');
93
94 end if;
95
96 htp.p('<FORM NAME="POS_ASN_REVIEW" ACTION="'||l_script_name||'/POS_ASN_REVIEW_PKG.review_page" METHOD="GET">');
97
98 htp.p('<INPUT NAME="p_submit" TYPE="HIDDEN" VALUE="Y">');
99
100 htp.p('<table align=center width=98% cellpadding=2 cellspacing=0 border=0>');
101
102 header_section;
103
104 htp.p('<TR><TD colspan=3> <TD></TR>');
105
106 lines_section;
107
108 htp.p('</FORM>');
109
110 htp.p('</TABLE>');
111
112 htp.bodyClose;
113 htp.htmlClose;
114
115 END review_page;
116
117
118 PROCEDURE header_section IS
119
120 l_language VARCHAR2(5);
121 l_script_name VARCHAR2(240);
122 l_org_id NUMBER;
123 l_user_id NUMBER;
124 l_session_id NUMBER;
125
126 l_where_clause VARCHAR2(2000);
127 l_total_rows NUMBER := 0;
128
129 BEGIN
130
131 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
132
133 l_where_clause := 'SESSION_ID = ' || to_char(l_session_id);
134
135 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
136 p_parent_region_code=>'POS_ASN_REVIEW_HEADERS_R',
137 p_where_clause=>l_where_clause,
138 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
139 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
140 p_return_parents=>'T',
141 p_return_children=>'F');
142
143 DupResultTable(ak_query_pkg.g_results_table, g_header_results_table);
144 DupItemTable(ak_query_pkg.g_items_table, g_header_items_table);
145
146 IF FND_FUNCTION.TEST('ICX_DISABLE_ASBN') and valid_invoice_num(l_session_id) THEN
147
148 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
149 p_parent_region_code=>'POS_ASN_REVIEW_INV_HDR_R',
150 p_where_clause=>l_where_clause,
151 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
152 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
153 p_return_parents=>'T',
154 p_return_children=>'F');
155
156 DupResultTable(ak_query_pkg.g_results_table, g_inv_hdr_results_table);
157 DupItemTable(ak_query_pkg.g_items_table, g_inv_hdr_items_table);
158
159 END IF;
160
161 l_total_rows := g_header_results_table.count;
162
163 IF l_total_rows = 1 THEN
164
165 PrintResult(g_header_results_table.FIRST, g_header_results_table, g_header_items_table);
166
167 IF FND_FUNCTION.TEST('ICX_DISABLE_ASBN') and valid_invoice_num(l_session_id) THEN
168
169 htp.p('<TR><TD colspan=3> <TD></TR>');
170
171 PrintResult(g_inv_hdr_results_table.FIRST, g_inv_hdr_results_table, g_inv_hdr_items_table);
172
173 END IF;
174
175 END IF;
176
177 END header_section;
178
179 PROCEDURE lines_section IS
180
181 l_language VARCHAR2(5);
182 l_script_name VARCHAR2(240);
183 l_org_id NUMBER;
184 l_user_id NUMBER;
185 l_session_id NUMBER;
186
187 l_where_clause VARCHAR2(2000);
188 l_total_rows NUMBER := 0;
189 l_result_index NUMBER;
190
191 BEGIN
192
193 l_session_id := icx_sec.getID(icx_sec.PV_SESSION_ID);
194
195 l_where_clause := 'SESSION_ID = ' || to_char(l_session_id);
196
197 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
198 p_parent_region_code=>'POS_ASN_REVIEW_DETAILS_SEQ_R',
199 p_where_clause=>l_where_clause,
200 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
201 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
202 p_return_parents=>'T',
203 p_return_children=>'F');
204
205 DupResultTable(ak_query_pkg.g_results_table, g_seq_results_table);
206 DupItemTable(ak_query_pkg.g_items_table, g_seq_items_table);
207
208 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
209 p_parent_region_code=>'POS_ASN_REVIEW_PO_DETAILS_R',
210 p_where_clause=>l_where_clause,
211 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
212 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
213 p_return_parents=>'T',
214 p_return_children=>'F');
215
216 DupResultTable(ak_query_pkg.g_results_table, g_lines_results_table);
217 DupItemTable(ak_query_pkg.g_items_table, g_lines_items_table);
218
219 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
220 p_parent_region_code=>'POS_ASN_REVIEW_SHIP_DETAILS_R',
221 p_where_clause=>l_where_clause,
222 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
223 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
224 p_return_parents=>'T',
225 p_return_children=>'F');
226
227 DupResultTable(ak_query_pkg.g_results_table, g_ship_results_table);
228 DupItemTable(ak_query_pkg.g_items_table, g_ship_items_table);
229
230 IF FND_FUNCTION.TEST('ICX_DISABLE_ASBN') and valid_invoice_num(l_session_id) THEN
231
232 ak_query_pkg.exec_query(p_parent_region_appl_id=>178,
233 p_parent_region_code=>'POS_ASN_REVIEW_INV_DETAILS_R',
234 p_where_clause=>l_where_clause,
235 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
236 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
237 p_return_parents=>'T',
238 p_return_children=>'F');
239
240 DupResultTable(ak_query_pkg.g_results_table, g_inv_dtl_results_table);
241 DupItemTable(ak_query_pkg.g_items_table, g_inv_dtl_items_table);
242
243 END IF;
244
245 l_total_rows := g_lines_results_table.count;
246
247 l_result_index := g_lines_results_table.FIRST;
248
249 WHILE (l_result_index IS NOT NULL) LOOP
250
251 htp.p('<TR><TD colspan=3><FONT class=headingblack>');
252
253 htp.p(g_seq_items_table(g_seq_items_table.FIRST).attribute_label_long || ' ' ||
254 get_result_value(g_seq_results_table, l_result_index, 1));
255
256 htp.p('</FONT></TD></TR><TR><TD colspan=3>');
257
258 htp.p('<table align=center width=100% cellpadding=0 cellspacing=0 border=0>');
259
260 htp.p('<tr><td height=5><img src=/OA_MEDIA/FNDPXG5.gif></td></tr>');
261 htp.p('<tr><td height=1 bgcolor=black><img src=/OA_MEDIA/FNDPX1.gif></td></tr>');
262 htp.p('<tr><td height=3><img src=/OA_MEDIA/FNDPXG5.gif></td></tr>');
263
264 htp.p('</table></TD></TR>');
265
266 PrintResult(l_result_index, g_lines_results_table, g_lines_items_table);
267
268 htp.p('<TR><TD colspan=3> </TD></TR>');
269 htp.p('<TR><TD colspan=3><FONT class=headingblack>');
270
271 htp.p(fnd_message.get_string('ICX','ICX_POS_SHIPMENT_DETAILS'));
272
273 htp.p('</FONT><TD><TR>');
274
275 PrintResult(l_result_index, g_ship_results_table, g_ship_items_table);
276
277 IF FND_FUNCTION.TEST('ICX_DISABLE_ASBN') and valid_invoice_num(l_session_id) THEN
278
279 htp.p('<TR><TD colspan=3> </TD></TR>');
280 htp.p('<TR><TD colspan=3><FONT class=headingblack>');
281
282 htp.p(fnd_message.get_string('ICX','ICX_POS_INVOICE_DETAILS'));
283
284 htp.p('</FONT><TD><TR>');
285
286 PrintResult(l_result_index, g_inv_dtl_results_table, g_inv_dtl_items_table);
287
288 END IF;
289
290 l_result_index := g_lines_results_table.NEXT(l_result_index);
291
292 htp.p('<TR><TD colspan=3> <TD></TR>');
293
294 END LOOP;
295
296 END lines_section;
297
298 PROCEDURE PrintResult(p_result_index IN NUMBER,
299 p_results_table IN ak_query_pkg.results_table_type,
300 p_items_table IN ak_query_pkg.items_table_type) IS
301
302 l_attribute_index NUMBER := p_items_table.FIRST;
303 l_current_col NUMBER := 0;
304
305 l_counter NUMBER := 0;
306 l_width VARCHAR2(50);
307
308 BEGIN
309
310 WHILE (l_attribute_index IS NOT NULL) LOOP
311
312 if (p_items_table(l_attribute_index).node_query_flag = 'Y') then
313
314 l_current_col := l_current_col + 1;
315
316 end if;
317
318 IF (p_items_table(l_attribute_index).node_display_flag = 'Y') AND
319 (p_items_table(l_attribute_index).secured_column = 'F') THEN
320
321
322 IF (p_items_table(l_attribute_index).item_style = 'TEXT') THEN
323
324 if (l_counter mod 3) = 0 then
325
326 htp.p('<TR>');
327
328 elsif (p_items_table(l_attribute_index).display_value_length > 20) then
329
330 htp.p('<TR>');
331 l_counter := 2;
332
333 end if;
334
335 if (p_items_table(l_attribute_index).display_value_length > 20) then
336
337 l_width := '100% colspan=3';
338
339 else
340
341 l_width := '33%';
342
343 end if;
344
345 htp.p('<TD width=' || l_width || '><font class=datablack>' ||
346 p_items_table(l_attribute_index).attribute_label_long || ': ' ||
347 '</font><font class=tabledata>' ||
348 nvl(get_result_value(p_results_table, p_result_index, l_current_col), fnd_message.get_string('ICX','ICX_POS_NA')) || '</font></TD>');
349
350 if (l_counter mod 3) = 2 then
351 htp.p('</TR>');
352 end if;
353
354 l_counter := l_counter + 1;
355
356 END IF;
357
358 END IF;
359
360 l_attribute_index := p_items_table.NEXT(l_attribute_index);
361
362 END LOOP;
363
364
365 END PrintResult;
366
367 PROCEDURE DupResultTable(p_source in ak_query_pkg.results_table_type,
368 p_dest in out ak_query_pkg.results_table_type) IS
369
370 v_index NUMBER;
371
372 BEGIN
373
374 v_index := p_source.FIRST;
375
376 if v_index is not null then
377
378 LOOP
379
380 p_dest(v_index) := p_source(v_index);
381
382 EXIT WHEN v_index = p_source.LAST;
383
384 v_index := p_source.NEXT(v_index);
385
386 END LOOP;
387
388 end if;
389
390 END DupResultTable;
391
392 PROCEDURE DupItemTable(p_source in ak_query_pkg.items_table_type,
393 p_dest in out ak_query_pkg.items_table_type) IS
394
395 v_index NUMBER;
396
397 BEGIN
398
399 v_index := p_source.FIRST;
400
401 if v_index is not null then
402
403 LOOP
404
405 p_dest(v_index) := p_source(v_index);
406
407 EXIT WHEN v_index = p_source.LAST;
408
409 v_index := p_source.NEXT(v_index);
410
411 END LOOP;
412
413 end if;
414
415 END DupItemTable;
416
417 function get_result_value(p_table in ak_query_pkg.results_table_type,
418 p_index in number,
419 p_col in number) return varchar2 is
420
421 sql_statement VARCHAR2(300);
422 l_cursor NUMBER;
423 l_execute NUMBER;
424 l_result VARCHAR2(2000);
425
426 BEGIN
427
428 IF p_table.count > 0 THEN
429
430 pos_asn_review_pkg.g_temp_table := p_table;
431
432 sql_statement := 'begin ' ||
433 ':l_result := pos_asn_review_pkg.g_temp_table(:p_index).value' ||
434 to_char(p_col) || '; ' ||
435 ' end;';
436
437 l_cursor := dbms_sql.open_cursor;
438 dbms_sql.parse(l_cursor, sql_statement, dbms_sql.v7);
439 dbms_sql.bind_variable(l_cursor, 'l_result', l_result, 2000);
440 dbms_sql.bind_variable(l_cursor, 'p_index', p_index);
441
442 l_execute := dbms_sql.execute(l_cursor);
443 dbms_sql.variable_value(l_cursor, 'l_result', l_result);
444 dbms_sql.close_cursor(l_cursor);
445 return l_result;
446
447 ELSE
448
449 return null;
450
451 END IF;
452
453 END get_result_value;
454
455 function valid_invoice_num(p_session_id in number) return boolean is
456
457 l_num varchar2(30);
458
459 begin
460
461 select invoice_num
462 into l_num
463 from pos_asn_shop_cart_headers
464 where session_id = p_session_id;
465
466 if l_num is not null then
467 return true;
468 else
469 return false;
470 end if;
471
472 exception
473 when others then
474 return false;
475
476 end valid_invoice_num;
477
478 END pos_asn_review_pkg;