eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ICX_REQ_TEMPLATES

Source


1 PACKAGE BODY ICX_REQ_TEMPLATES as
2 /* $Header: ICXRQTMB.pls 115.3 99/07/17 03:23:36 porting sh $ */
3 
4 --**********************************************************
5 -- LOCAL PROCEDURES NOT DECLARED IN SPEC
6 --**********************************************************
7 
8 ------------------------------------------------------------
9 procedure createDummyPage(p_where number, nodeId varchar2, nodeIndex varchar2, v_string long,v_first_time_flag varchar2) is
10 ------------------------------------------------------------
11 begin
12    -- open html
13    htp.htmlOpen;
14    htp.headOpen;
15    htp.headClose;
16 
17    if (v_first_time_flag = 'Y') then
18       htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.openTemplate(''' || icx_util.replace_quotes(nodeId) ||''',document.GetChildren.nodeId,document.GetChildren.nodeIndex)"');
19    else
20       htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.addChildren(''' || icx_util.replace_quotes(nodeId) || ''',document.GetChildren.nodeId,document.GetChildren.nodeIndex)"');
21    end if;
22 
23        htp.formOpen(curl        =>'ICX_REQ_TEMPLATES.GetTemplateChildren',
24                     cmethod     => 'POST',
25                     cattributes => 'name=''GetChildren'''
26                    );
27 
28        htp.formHidden('nodeId',  cvalue => v_string);
29        htp.formHidden('p_where', cvalue => p_where);
30        htp.formHidden('nodeIndex', cvalue => nodeIndex);
31 
32        htp.formClose;
33 
34 
35    htp.bodyClose;
36    htp.htmlClose;
37 
38 end createDummyPage;
39 
40 
41 --**********************************************************
42 -- LOCAL PROCEDURES NOT DECLARED IN SPEC
43 --**********************************************************
44 
45 
46 ------------------------------------------------------------
47 function get_default_template( v_emergency varchar2 )
48         return varchar2 is
49 ------------------------------------------------------------
50 
51 v_return_template  varchar2(25);
52 v_test             number;
53 
54 begin
55 
56     -- get default template
57     v_return_template := icx_sec.getID(icx_sec.PV_USER_REQ_TEMPLATE);
58 
59     v_test := 0;
60 
61     if v_emergency = 'YES' then
62            select count(-1) into v_test
63            from   po_reqexpress_headers
64            where  express_name = v_return_template
65            and    (reserve_po_number = 'YES' OR reserve_po_number = 'OPTIONAL');
66     else
67            select count(-1) into v_test
68            from   po_reqexpress_headers
69            where  express_name = v_return_template
70            and    (reserve_po_number = 'NO' OR reserve_po_number = 'OPTIONAL' OR reserve_po_number is null);
71     end if;
72 
73     if v_test = 0 then
74           v_return_template := null;
75     end if;
76 
77     return v_return_template;
78 
79 end get_default_template;
80 
81 ------------------------------------------------------------
82 procedure GetTemplateTop(v_org_id number,
83                          v_emergency varchar2 default NULL ) is
84 ------------------------------------------------------------
85 
86 -- (MC) remove local variables v_regions_table, v_items_table, and v_results_table
87 
88 y_table            icx_util.char240_table;
89 /* Change wrto Bug Fix to implement the Bind Vars **/
90   where_clause_binds      ak_query_pkg.bind_tab;
91   where_clause_binds_empty     ak_query_pkg.bind_tab;
92   where_clause       varchar2(2000);
93 
94   v_index             number;
95 v_node_id          varchar2(240);
96 v_name             varchar2(240);
97 v_no_of_children   number;
98 v_children_loaded  varchar2(100);
99 p_where            varchar2(240);
100 counter            number;
101 i                  number;
102 v_dcdName          varchar2(240) := owa_util.get_cgi_env('SCRIPT_NAME');
103 v_default_template  varchar2(25);
104 v_def_is_top        varchar2(1);
105 
106 begin
107 
108 
109     v_index := 1;
110 
111 
112    /* desmond for beta 1 move default template to top for beta no emergency default */
113 --   v_default_template := get_default_template('NO');
114    /* for ups, support emergency po defaulting 5/5/97 */
115    v_default_template := get_default_template(v_emergency);
116    v_def_is_top := 'N';
117 
118 /* Change wrto Bug Fix to implement the Bind Vars **/
119 --  where_clause := 'relationship_type = ''TOP'' ';
120     where_clause := 'relationship_type = :rel_type_bin ';
121   where_clause_binds(v_index).name := 'rel_type_bin';
122   where_clause_binds(v_index).value := 'TOP';
123   v_index := v_index + 1;
124 
125 
126    if v_emergency = 'YES' then
127 --            where_clause :=  where_clause  ||  ' AND ( RESERVE_PO_NUMBER = ''YES'' OR RESERVE_PO_NUMBER = ''OPTIONAL'' )';
128             where_clause :=  where_clause  ||  ' AND ( RESERVE_PO_NUMBER = :reserve_po_num_bin OR RESERVE_PO_NUMBER = :reserve_po_num1_bin )';
129   where_clause_binds(v_index).name := 'reserve_po_num_bin';
130   where_clause_binds(v_index).value := 'YES';
131   v_index := v_index + 1;
132   where_clause_binds(v_index).name := 'reserve_po_num1_bin';
133   where_clause_binds(v_index).value := 'OPTIONAL';
134   v_index := v_index + 1;
135    else
136 --            where_clause :=  where_clause  ||  ' AND (RESERVE_PO_NUMBER = ''NO'' OR RESERVE_PO_NUMBER = ''OPTIONAL'' OR RESERVE_PO_NUMBER is  NULL )';
137             where_clause :=  where_clause  ||  ' AND (RESERVE_PO_NUMBER = :reserve_po_num2_bin OR RESERVE_PO_NUMBER = :reserve_po_num3_bin OR RESERVE_PO_NUMBER is  NULL )';
138   where_clause_binds(v_index).name := 'reserve_po_num2_bin';
139   where_clause_binds(v_index).value := 'NO';
140   v_index := v_index + 1;
141   where_clause_binds(v_index).name := 'reserve_po_num3_bin';
142   where_clause_binds(v_index).value := 'OPTIONAL';
143   v_index := v_index + 1;
144    end if;
145 
146    htp.comment(where_clause);
147 
148    ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
149                               P_PARENT_REGION_CODE    => 'ICX_RELATED_TEMPLATES_DISPLAY',
150                               P_RESPONSIBILITY_ID     => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
151                               P_USER_ID         => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
152                               P_WHERE_CLAUSE 		=> where_clause,
153                               P_RETURN_PARENTS        => 'T',
154                               P_RETURN_CHILDREN       => 'F',
155                               p_WHERE_BINDS      => where_clause_binds );
156 
157    counter := 1;
158 
159    where_clause_binds := where_clause_binds_empty;
160 
161    v_index := 1;
162 
163 
164 
165 if ak_query_pkg.g_results_table.count > 0 then
166 	htp.p('TOP_TEMPLATES = new MakeArray(' || ak_query_pkg.g_results_table.count || ');');
167       for i in ak_query_pkg.g_results_table.first .. ak_query_pkg.g_results_table.last loop
168 
169 /* desmond */
170 --         v_node_id := ak_query_pkg.g_results_table(i).value2;  -- Template id
171 --         v_name    := ak_query_pkg.g_results_table(i).value4;  -- Template name
172 
173          v_node_id := ak_query_pkg.g_results_table(i).value1;
174          v_name := ak_query_pkg.g_results_table(i).value1;
175 
176          if v_default_template is not NULL then
177             if v_node_id = v_default_template then
178                v_def_is_top := 'Y';
179             end if;
180          end if;
181 
182          p_where := icx_call.encrypt2(v_node_id || '*' || v_org_id || '**]');
183 
184          select count(-1) into v_no_of_children
185          from   icx_related_templates_val_v
186          where  express_name = v_node_id
187          and    RELATIONSHIP_TYPE <> 'TOP';
188 
189          if v_no_of_children > 0 then
190                 v_children_loaded := 'false';
191          else
192                 v_children_loaded := 'true';
193          end if;
194 
195          htp.p('TOP_TEMPLATES[' || counter || ']= new node("' || v_node_id || '","' ||
196                                 v_name || '",' || v_children_loaded || ',"' ||
197                                 v_dcdName || '/ICX_REQ_TEMPLATES.template_items?p_where=' || p_where  -- Node Link
198                                 || '","' || p_where || '");');
199          counter := counter + 1;
200 
201       end loop; -- end i
202 
203    else -- No hierchy setup use regular templates
204 
205       if v_emergency = 'YES' then
206   --          where_clause := '(RESERVE_PO_NUMBER = ''YES'' OR RESERVE_PO_NUMBER = ''OPTIONAL'' )';
207             where_clause := '(RESERVE_PO_NUMBER = :reserve_po_num4_bin OR RESERVE_PO_NUMBER = :reserve_po_num5_bin )';
208   where_clause_binds(v_index).name := 'reserve_po_num4_bin';
209   where_clause_binds(v_index).value := 'YES';
210   v_index := v_index + 1;
211   where_clause_binds(v_index).name := 'reserve_po_num5_bin';
212   where_clause_binds(v_index).value := 'OPTIONAL';
213   v_index := v_index + 1;
214       else
215 --            where_clause := '(RESERVE_PO_NUMBER = ''NO'' OR RESERVE_PO_NUMBER = ''OPTIONAL'' OR RESERVE_PO_NUMBER is NULL )';
216       where_clause := '(RESERVE_PO_NUMBER = :reserve_po_num6_bin OR RESERVE_PO_NUMBER = :reserve_po_num7_bin OR RESERVE_PO_NUMBER is NULL )';
217   where_clause_binds(v_index).name := 'reserve_po_num6_bin';
218   where_clause_binds(v_index).value := 'NO';
219   v_index := v_index + 1;
220   where_clause_binds(v_index).name := 'reserve_po_num7_bin';
221   where_clause_binds(v_index).value := 'OPTIONAL';
222   v_index := v_index + 1;
223 
224       end if;
225 
226       htp.comment(where_clause);
227 
228       ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
229                              P_PARENT_REGION_CODE    => 'ICX_REQ_TEMPLATES',
230                              P_RESPONSIBILITY_ID     => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
231                              P_USER_ID               => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
232                              P_WHERE_CLAUSE          => where_clause,
233                              P_RETURN_PARENTS        => 'T',
234                              P_RETURN_CHILDREN       => 'F',
235                              P_WHERE_BINDS      =>     where_clause_binds);
236 
237       if ak_query_pkg.g_results_table.count > 0 then
238          htp.p('TOP_TEMPLATES = new MakeArray(' || ak_query_pkg.g_results_table.count  || ');');
239          for i in ak_query_pkg.g_results_table.first .. ak_query_pkg.g_results_table.last loop
240 
241 
242             v_node_id := ak_query_pkg.g_results_table(i).value1;  -- template id
243             v_name    := ak_query_pkg.g_results_table(i).value1;  -- template id
244 
245             if v_default_template is not NULL then
246                if v_node_id = v_default_template then
247                    v_def_is_top := 'Y';
248                end if;
249             end if;
250 
251             p_where := icx_call.encrypt2(v_node_id || '*' || v_org_id || '**]');
252 
253             v_children_loaded := 'true';
254             htp.p('TOP_TEMPLATES[' || counter || ']= new node("' || v_node_id || '","' ||
255                                   v_name || '",' || v_children_loaded || ',"' ||
256                                   v_dcdName || '/ICX_REQ_TEMPLATES.template_items?p_where=' || p_where  -- Node Link
257                                 || '","' || p_where || '");');
258             counter := counter + 1;
259 
260          end loop;
261 
262 
263       else  -- No TEmplates
264 --        if v_default_template is not NULL then
265 --            htp.p('TOP_TEMPLATES = new MakeArray(1);');
266 --        else
267             htp.p('TOP_TEMPLATES = new MakeArray(0);');
268 --        end if;
269       end if;
270 
271    end if; -- No hierchy setup
272 
273 
274    /* desmond -- move default template to top is not already at top */
275    /* allow it to be at children level and dispaly as such in Beta 2 */
276    if v_def_is_top = 'N'  AND
277       v_default_template is not NULL then
278 
279       p_where := icx_call.encrypt2(v_default_template || '*' || v_org_id || '**]');
280 
281       select count(-1) into v_no_of_children
282       from   icx_related_templates_val_v
283       where  express_name = v_default_template
284       and    RELATIONSHIP_TYPE <> 'TOP';
285 
286       if v_no_of_children > 0 then
287          v_children_loaded := 'false';
288       else
289          v_children_loaded := 'true';
290       end if;
291 
292       htp.p('new_TOP_TEMPLATES = new MakeArray(' || counter || ');');
293       htp.p('for (var i = 1; i <= ' || to_char(counter - 1) || '; i ++) {'
294              || 'new_TOP_TEMPLATES[i] = TOP_TEMPLATES[i]; }');
295       htp.p('new_TOP_TEMPLATES[' || counter || ']= new node("' || v_default_template || '","' ||
296                                 v_default_template || '",' || v_children_loaded || ',"' ||
297                                 v_dcdName || '/ICX_REQ_TEMPLATES.template_items?p_where=' || p_where  -- Node Link
298                                 || '","' || p_where || '");');
299       htp.p('TOP_TEMPLATES = new MakeArray(' || counter || ');');
300       htp.p('for (var i = 1; i <= ' || to_char(counter) || '; i ++) {'
301              || 'TOP_TEMPLATES[i] = new_TOP_TEMPLATES[i];}');
302 
303    end if;
304 
305 
306 
307 end GetTemplateTop;
308 
309 
310 
311 ------------------------------------------------------------
312 procedure templates(start_row in number default 1,
313                     c_end_row in number default null,
314                     p_where   in number) is
315 ------------------------------------------------------------
316 v_lang           varchar2(5);
317 v_dcdName        varchar2(1000);
318 v_frame_location varchar2(1024);
319 n_temp           number;
320 
321 begin
322 
323 
324     -- get lang code
325     v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
326 
327     --get dcd name
328     v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
329 
330     htp.htmlOpen;
331        htp.headOpen;
332        htp.headClose;
333        htp.framesetOpen('','250,*','BORDER=5');
334            htp.framesetOpen('*,0','','BORDER=0');
335            htp.frame('/OA_HTML/' || v_lang || '/ICXTEMH.htm', 'left_frame', '0','0', cscrolling=>'auto',cnoresize => '');
336                   htp.frame(csrc  =>  v_dcdName || '/ICX_REQ_TEMPLATES.GetTemplateChildren?p_where=' || p_where,  -- URL
337                             cname =>  'dummy', 	   --Window Name
338                             cmarginwidth   => '0', --    Value in pixels
339                             cmarginheight  => '0', --    Value in pixels
340 --                            cscrolling     => 'NO',--    yes | no | auto
341                             cattributes    => 'FRAMEBORDER=NO');
342            htp.framesetClose;
343 
344            v_frame_location := v_dcdName || '/ICX_REQ_TEMPLATES.template_items?';
345 
346            if c_end_row is not null then
347                v_frame_location := v_frame_location || 'p_start_row=' || start_row  ||'&p_end_row=' || c_end_row || '&p_where=' || p_where;
348            else
349                v_frame_location := v_frame_location || 'p_where=' || p_where;
350            end if;
351            htp.frame( v_frame_location, 'right_frame', '0','0', cscrolling=>'auto');
352     htp.framesetClose;
353     htp.htmlClose;
354 
355 
356 end templates;
357 
358 
359 ------------------------------------------------------------
360 procedure GetTemplateChildren( p_where in number,
361                                nodeId  in varchar2  default null,
362                                nodeIndex in varchar2 default null) is
363 ------------------------------------------------------------
364 
365 -- (MC) remove local variables v_regions_table, v_items_table, and v_results_table
366 
367 y_table            icx_util.char240_table;
368 where_clause_binds      ak_query_pkg.bind_tab;
369 where_clause_binds_empty      ak_query_pkg.bind_tab;
370 where_clause            varchar2(2000);
371 
372 v_index            number;
373 
374 v_p_where          number;
375 -- childrenString     varchar2(2000);
376 -- Fix for bug 517695
377 childrenString     long;
378 
379 v_node_id          varchar2(240);
380 v_name             varchar2(240);
381 v_no_of_children   number;
382 v_org              number;
383 params             icx_on_utilities.v80_table;
384 
385 v_dcdName          varchar2(240) := owa_util.get_cgi_env('SCRIPT_NAME');
386 
387 v_emergency varchar2(10);
388 v_reserve_po_number varchar2(10);
389 v_default_template_id varchar2(240);
390 v_default_top varchar2(240);
391 v_cont    varchar2(1);
392 d_nodeid  varchar2(240);
393 d_relation varchar2(80);
394 d_inter_nodes varchar2(2000);
395 v_first_time_flag varchar2(1);
396 
397   cursor getTopNode_emg(childnodeId varchar2) is
398      select express_name,relationship_type
399      from icx_related_templates_val_v
400      where related_express_name = childnodeId
401      and (reserve_po_number = 'YES'
402       or  reserve_po_number = 'OPTIONAL');
403 
404   cursor getTopNode(childnodeId varchar2) is
405      select express_name,relationship_type
406      from icx_related_templates_val_v
407      where related_express_name = childnodeId
408      and (reserve_po_number = 'NO'
409       or  reserve_po_number = 'OPTIONAL'
410       or  reserve_po_number is NULL);
411 
412   cursor getAnyTop is
413      select express_name,relationship_type
414      from icx_related_templates_val_v
415      where relationship_type = 'TOP'
416      order by express_name;
417 
418 begin
419 
420 
421 -- Check if session is valid
422 if (icx_sec.validatesession()) then
423 
424     v_index := 1;
425 
426   --decrypt2 p_where
427 
428   if p_where is not null then
429         icx_on_utilities.unpack_parameters(icx_call.decrypt2(p_where), params);
430         --template_id := params(1);
431 
432 --        if nodeId is null then
433           v_default_template_id := params(1);
434 
435           -- determine top of the default template name and use it to
436           -- drill down the default template tree
437 
438           if v_default_template_id is not NULL and
439              length(v_default_template_id) > 5 then
440              if substr(v_default_template_id,1,5) = '(NEW)' and
441                 v_default_template_id <> '(NEW)none' then
442 
443                 v_default_top := substr(v_default_template_id,6,length(v_default_template_id) - 5);
444 
445                 -- v_cont := 'Y';
446                 --     if v_emergency is not NULL AND v_emergency = 'YES' then
447                 --          while v_cont = 'Y' loop
448                 --            open getTopNode_emg(v_default_template_id);
449                 --            fetch getTopNode_emg into d_nodeid,d_relation;
450                 --            if getTopNode_emg%NOTFOUND OR
451                 --               d_relation = 'TOP' OR
452                 --               v_default_template_id = d_nodeid  then
453                 --               v_cont := 'N';
454                 --            end if;
455                 --            close getTopNode_emg;
456                 --            v_default_template_id := d_nodeid;
457                 --          end loop;
458                 --     else
459 
460                 v_cont := 'Y';
461                 d_inter_nodes := v_default_top;
462                 while v_cont = 'Y' loop
463                    open getTopNode(v_default_top);
464                    fetch getTopNode into d_nodeid,d_relation;
465                    if getTopNode%NOTFOUND OR
466                       d_relation = 'TOP' OR
467                       v_default_top = d_nodeid  then
468                          v_cont := 'N';
469                    end if;
470                    close getTopNode;
471                    if d_nodeid is not NULL then
472                       v_default_top := d_nodeid;
473                       d_inter_nodes := d_nodeid || '~~' || d_inter_nodes;  -- build hier tree
474                    end if;
475                 end loop;
476                 if d_inter_nodes = v_default_top then   -- just format it to what showTree js script expects
477                    d_inter_nodes := d_inter_nodes || '~~' || d_inter_nodes;
478                 end if;
479 --     end if;
480 
481 --          v_emergency := params(3);
482 --        else
483 --          v_default_template_id := NULL;
484 --          v_emergency := NULL;
485 --        end if;
486 
487            v_default_template_id := substr(v_default_template_id,6,length(v_default_template_id) - 5);
488         else
489            v_default_template_id := NULL;
490            open getAnyTop;
491            fetch getAnyTop into d_nodeid,d_relation;
492            if getAnyTop%NOTFOUND then
493                v_default_top := NULL;
494            end if;
495            close getAnyTop;
496            if d_nodeid is not NULL then
497               v_default_top := d_nodeid;
498            end if;
499         end if;
500 
501       end if;
502 
503 
504         v_org := params(2);
505   end if;
506 
507    if nodeId is not null then
508 --       where_clause := 'express_name = ''' || nodeId || ''' AND ';
509          where_clause := 'express_name = :express_name1_bin  AND ';
510   where_clause_binds(v_index).name := 'express_name1_bin';
511   where_clause_binds(v_index).value := nodeId;
512   v_index := v_index + 1;
513 
514    else
515        if v_default_top is not null then
516 --          where_clause := 'express_name = ''' || v_default_top || ''' AND ';
517           where_clause := 'express_name = :express_name2_bin  AND ';
518   where_clause_binds(v_index).name := 'express_name2_bin';
519   where_clause_binds(v_index).value := v_default_top;
520   v_index := v_index + 1;
521        end if;
522    end if;
523 
524 --   where_clause := where_clause || ' relationship_type = ''CHILD''';
525    where_clause := where_clause || ' relationship_type = :rel_type_bin ';
526   where_clause_binds(v_index).name := 'rel_type_bin';
527   where_clause_binds(v_index).value := 'CHILD';
528   v_index := v_index + 1;
529    -- Query childrens.
530    ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
531                               P_PARENT_REGION_CODE    => 'ICX_RELATED_TEMPLATES_DISPLAY',
532                               P_RESPONSIBILITY_ID     => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
533                               P_USER_ID               => icx_sec.getID(icx_sec.PV_WEB_USER_ID),
534                               P_WHERE_CLAUSE 		=> where_clause,
535                               P_RETURN_PARENTS        => 'T',
536                               P_RETURN_CHILDREN       => 'F',
537                               p_WHERE_BINDS      => where_clause_binds);
538 
539    if ak_query_pkg.g_results_table.count > 0 then
540    for i in ak_query_pkg.g_results_table.first .. ak_query_pkg.g_results_table.last loop
541 
542 --         v_node_id := ak_query_pkg.g_results_table(i).value3;  -- Related template id
543 --         v_namet    := ak_query_pkg.g_results_table(i).value4;  -- Related template name
544        v_node_id := ak_query_pkg.g_results_table(i).value2;
545        v_name := ak_query_pkg.g_results_table(i).value2;
546 
547 
548          v_p_where := icx_call.encrypt2(v_node_id || '*' || v_org || '**]');
549 
550          select count(-1) into v_no_of_children
551          from   icx_related_templates_val_v
552          where  express_name = v_node_id
553          and    RELATIONSHIP_TYPE <> 'TOP';
554 
555 
556 /* desmond -- for beta 1 show default template as a leaf displaying from parent tree and no emergency po case */
557 --         if v_node_id = get_default_template('NO') then
558 --            childrenString := childrenString || v_node_id || '~~' || v_name || '~~' || '0' || '~~' || v_dcdName || '/ICX_REQ_TEMPLATES.template_items?p_where=' || v_p_where || '~~' || v_p_where || '~~';
559 --         else
560 
561            childrenString := childrenString || v_node_id || '~~' || v_name || '~~' || v_no_of_children || '~~'||  v_dcdName || '/ICX_REQ_TEMPLATES.template_items?p_where=' || v_p_where  -- Node Link
562                                           ||  '~~' || v_p_where || '~~' ;
563 --         end if;
564 
565 
566    end loop; -- end i
567    end if;
568 
569 
570 /* desmond */
571   /* In beta2,  explode the template at the child level if default  template is a child */
572   /* so get the top node template id, and drill down from there */
573    if nodeid is NULL AND
574       v_default_template_id is not NULL AND
575       v_default_template_id <> 'none' then
576 
577       if v_default_top is not NULL then
578            d_nodeid := v_default_top;
579       else
580            d_nodeid := v_default_template_id;
581       end if;
582       v_first_time_flag := 'Y';
583     else
584       d_nodeid := nodeId;
585       v_first_time_flag := 'N';
586     end if;
587 
588    /* desmond -- for beta 1, since we have moved default temp */
589    /* on top, so show it as a leaf, and do not explode it any more */
590    createDummyPage(v_p_where,d_nodeid,nodeIndex,childrenString,v_first_time_flag);
591 
592 
593 
594 --   createDummyPage(v_p_where, nodeId, childrenString);
595 
596 end if;
597 
598 
599 end GetTemplateChildren;
600 
601 
602 
603 
604 ------------------------------------------------------------
605 procedure template_items(p_start_row in number default 1,
606                          p_end_row in number default null,
607 				 p_where in varchar2) is
608 ------------------------------------------------------------
609 v_dcdName            varchar2(1000);
610 v_lang		     varchar2(5);
611 
612 begin
613 
614    v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
615 
616     -- get lang code
617     v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
618 
619 
620    -- We need to split into 2 frames
621 
622    js.scriptOpen;
623    htp.p('function openButWin(start_row, end_row, total_row, where) {
624 
625          var result = "' || v_dcdName ||
626 	              '/ICX_REQ_TEMPLATES.template_items_buttons?p_start_row=" +
627 		      start_row + "&p_end_row=" + end_row + "&p_total_rows=" +
628 		      total_row + "&p_where=" + where;
629 	    open(result, ''k_buttons'');
630 }
631   ');
632 
633    js.scriptClose;
634    htp.p('<FRAMESET ROWS="*,40" BORDER=0>');
635    htp.p('<FRAME SRC="' || v_dcdName ||
636 	 '/ICX_REQ_TEMPLATES.template_items_display?p_start_row=' ||
637 	 p_start_row || '&p_end_row=' || p_end_row || '&p_where=' ||
638 	p_where||
639 	 '"  NAME="data" FRAMEBORDER=NO MARGINWIDTH=0 MARGINHEIGHT=0 NORESIZE>');
640 
641    htp.p('<FRAME NAME="k_buttons" SRC="/OA_HTML/' ||
642 	 v_lang ||
643 	 '/ICXPINK.htm" MARGINWIDTH=0 MARGINHEIGHT=0 FRAMEBORDER=NO NORESIZE SCROLLING="NO">');
644 
645    htp.p('</FRAMESET>');
646 
647 end;
648 
649 ------------------------------------------------------------
650 procedure template_items_buttons(p_start_row in number default 1,
651                                  p_end_row in number default null,
652 				 p_total_rows in number,
653 				 p_where in number) is
654 ------------------------------------------------------------
655 
656 v_lang              varchar2(30);
657 c_query_size        number;
658 
659 begin
660 
661 IF icx_sec.validateSession THEN
662 
663    SELECT QUERY_SET INTO c_query_size FROM ICX_PARAMETERS;
664 
665    v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
666      htp.p('<BODY BGCOLOR="#FFCCCC">');
667 
668      htp.p('<TABLE BORDER=0>');
669      htp.p('<TD>');
670    icx_on_utilities2.displaySetIcons(p_language_code   => v_lang,
671                                      p_packproc        => 'ICX_REQ_TEMPLATES.template_items',
672                                      p_start_row       => p_start_row,
673                                      p_stop_row        => p_end_row,
674  				     p_encrypted_where => p_where,
675                                      p_query_set       => c_query_size,
676 				     p_target          => 'parent',
677                                      p_row_count       => p_total_rows);
678      htp.p('</TD>');
679      htp.p('<TD width=1000></TD><TD>');
680      FND_MESSAGE.SET_NAME('ICX','ICX_ADD_TO_ORDER');
681      icx_util.DynamicButton(P_ButtonText      => FND_MESSAGE.GET,
682                             P_ImageFileName   => 'FNDBNEW.gif',
683                             P_OnMouseOverText => FND_MESSAGE.GET,
684                             P_HyperTextCall   => 'javascript:parent.frames[0].submit()',
685                             P_LanguageCode    => v_lang,
686                             P_JavaScriptFlag  => FALSE);
687 
688      htp.p('</TD></TABLE>');
689      htp.p('</BODY>');
690 
691 end if;
692 
693 end;
694 
695 
696 ------------------------------------------------------------
697 procedure template_items_display(p_start_row in number default 1,
698                          p_end_row in number default null,
699                                  p_where in varchar2) is
700 ------------------------------------------------------------
701 
702 sess_web_user       number(15);
703 c_title             varchar2(80) := '';
704 c_prompts           icx_util.g_prompts_table;
705 v_lang              varchar2(30);
706 where_clause_binds      ak_query_pkg.bind_tab;
707 where_clause        varchar2(2000);
708 v_index   NUMBER;
709 
710 
711 total_rows          number;
712 end_row             number;
713 display_text        varchar2(5000);
714 -- temp_table          icx_admin_sig.pp_table;
715 temp_table          varchar2(5000);
716 c_query_size        number;
717 v_supplier_url      varchar2(150);
718 v_supplier_item_url varchar2(150);
719 v_item_url          varchar2(150);
720 v_line_id	        varchar2(65);
721 i                   number := 0;
722 j                   number := 0;
723 
724 y_table             icx_util.char240_table;
725 
726 v_express_name      varchar2(240) := NULL;
727 v_org               number;
728 params              icx_on_utilities.v80_table;
729 
730 counter             number := 0;
731 V_QUANTITY_LENGTH   NUMBER :=10;
732 
733 c_currency            varchar2(15);
734 c_money_precision     number;
735 c_money_fmt_mask      varchar2(32);
736 l_encrypted_where     number;
737 v_dcdName            varchar2(1000);
738 
739 v_line_id_ind       number;
740 v_supplier_url_ind  number;
741 v_item_url_ind      number;
742 v_supplier_item_url_ind number;
743 g_reg_ind           number;
744 l_pos              number := 0;
745 l_spin_pos         number := 0;
746 
747 
748 BEGIN
749 
750 
751  IF icx_sec.validateSession() THEN
752 
753    v_index := 1;
754 
755    sess_web_user := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
756    v_lang := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
757    v_dcdName := owa_util.get_cgi_env('SCRIPT_NAME');
758 
759 
760    -- icx_util.getPrompts(178,'ICX_ITEMS_TEMPLATE',c_title,c_prompts);
761    icx_util.getPrompts(601,'ICX_PO_TEMPLATE_ITEMS_R',c_title,c_prompts);
762    icx_util.error_page_setup;
763 
764    l_encrypted_where := p_where;
765 
766     IF p_where IS NOT NULL THEN
767         icx_on_utilities.unpack_parameters(icx_call.decrypt2(p_where), params);
768         v_express_name := params(1);
769 
770         if length(v_express_name) > 5 then
771            if substr(v_express_name,1,5) = '(NEW)' then
772               v_express_name := substr(v_express_name,6,length(v_express_name) - 5);
773            end if;
774         end if;
775         v_org := params(2);
776     END IF;
777 
778  -- If no template is selected then display a blank right frame
779 
780    IF (v_express_name is NULL or v_express_name = 'none') THEN
781       htp.htmlOpen;
782         htp.headOpen;
783           icx_util.copyright;
784           htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.winOpen(''nav'', ''template'')"');
785           htp.bodyClose;
786         htp.headClose;
787       htp.htmlClose;
788       return;
789    END IF;
790 
791    ICX_REQ_NAVIGATION.get_currency(v_org, c_currency, c_money_precision,
792                                    c_money_fmt_mask);
793 
794    --  Query against ICX_PO_REQ_TEMPLATE_ITEMS_R and
795    --- display only items in this template
796 
797    -- where_clause := 'organization_id = ' || v_org || ' and EXPRESS_NAME = ' || '''' || replace(v_express_name,'''','''''') || '''';
798 --   where_clause := '(organization_id is NULL OR organization_id = ' || v_org || ')' || ' and EXPRESS_NAME = ' || '''' || replace(v_express_name,'''','''''') || '''';
799    where_clause := '(organization_id is NULL OR organization_id = :org_id_bin ) and EXPRESS_NAME = :express_name_bin';
800 
801   where_clause_binds(v_index).name := 'org_id_bin';
802   where_clause_binds(v_index).value := v_org;
803   v_index := v_index + 1;
804   where_clause_binds(v_index).name := 'express_name_bin';
805   where_clause_binds(v_index).value := v_express_name;
806   v_index := v_index + 1;
807 
808    -- get number of rows to display
809    SELECT QUERY_SET INTO c_query_size FROM ICX_PARAMETERS;
810 
811    -- set up end rows to display
812 
813    IF p_end_row IS NULL THEN
814       end_row := c_query_size;
815    ELSE
816       end_row := p_end_row;
817    END IF;
818 
819    ak_query_pkg.exec_query(P_PARENT_REGION_APPL_ID => 601,
820                               P_PARENT_REGION_CODE    => 'ICX_PO_TEMPLATE_ITEMS_R',
821   			      P_WHERE_CLAUSE          => where_clause,
822                               p_WHERE_BINDS      => where_clause_binds,
823                               P_RESPONSIBILITY_ID     => icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
824                               P_USER_ID               => sess_web_user,
825                               P_RETURN_PARENTS        => 'T',
826                               P_RETURN_CHILDREN       => 'F',
827                               P_RANGE_LOW             => p_start_row,
828                               P_RANGE_HIGH            => end_row);
829    -- Get number of rows to display
830     g_reg_ind := ak_query_pkg.g_regions_table.FIRST;
831 
832     total_rows := ak_query_pkg.g_regions_table(g_reg_ind).total_result_count;
833 
834     IF end_row > total_rows THEN
835        end_row := total_rows;
836     END IF;
837 
838 
839   IF ak_query_pkg.g_results_table.COUNT = 0 then
840       htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.winOpen(''nav'', ''template'')"');
841       fnd_message.set_name('EC','ICX_NO_RECORDS_FOUND');
842       fnd_message.set_token('NAME_OF_REGION_TOKEN',c_title);
843       htp.p('<H3>' || fnd_message.get || '</H3>');
844       htp.bodyClose;
845       return;
846    END IF;
847 
848    --- Display the template
849    htp.htmlOpen;
850    htp.headOpen;
851    icx_util.copyright;
852    js.scriptOpen;
853 
854    htp.p('function submit() {
855              document.template_items.cartId.value = parent.parent.parent.cartId;
856 	     document.template_items.p_emergency.value = parent.parent.parent.emergency;
857              document.template_items.submit();
858           }');
859 
860    htp.p('function get_parent_values(cartId,emerg) {
861 	     cartId.value = parent.parent.parent.cartId;
862              emerg.value = parent.parent.parent.emergency;
863            }');
864 
865    js.scriptClose;
866    htp.title(c_title);
867    htp.headClose;
868 
869 
870    htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.winOpen(''nav'', ''template''); top.lastTemplate.start_row='|| p_start_row ||
871                         ';top.lastTemplate.end_row='|| end_row ||
872 		        ';parent.openButWin(' || p_start_row || ',' ||
873 		        end_row || ',' || total_rows || ',' || p_where || ')"');
874    htp.br;
875 
876 
877 
878    htp.p('<FORM ACTION="' || v_dcdName || '/ICX_REQ_TEMPLATES.submit_items" METHOD="POST" TARGET="_parent" NAME="template_items" onSubmit="return(false)">');
879 
880      htp.formHidden('cartId','');
881      htp.formHidden('p_emergency','');
882      js.scriptOpen;
883 
884      htp.p('get_parent_values(document.template_items.cartId,document.template_items.p_emergency);');
885      js.scriptClose;
886 
887 
888 
889      htp.formHidden('p_start_row', p_start_row);
890      htp.formHidden('p_end_row', p_end_row);
891      htp.formHidden('p_where', p_where);
892      /* expressName required to identify a cart line uniquely */
893      htp.formHidden('v_express_name', v_express_name);
894      -- htp.formHidden('p_emergency','');
895      htp.formHidden('end_row',end_row,'cols="60" rows ="10"');
896      htp.formHidden('p_query_set',c_query_size,'cols="60" rows = "10"');
897      htp.formHidden('p_row_count',total_rows,'cols="60" rows="10"');
898 
899 
900      l_pos := l_pos + 9;
901 
902      -- Print the table column headings
903      htp.tableOpen('border=5','','','','bgcolor=#' || icx_util.get_color('TABLE_
904 DATA_MULTIROW') );
905 
906      htp.p('<TR BGColor="#'||icx_util.get_color('TABLE_HEADER_TABS')||'">');
907 
908      FOR i IN ak_query_pkg.g_items_table.FIRST .. ak_query_pkg.g_items_table.LAST LOOP
909         IF (ak_query_pkg.g_items_table(i).value_id IS NOT NULL
910                    AND ak_query_pkg.g_items_table(i).item_style <> 'hidden'
911                    AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
912                    AND ak_query_pkg.g_items_table(i).secured_column <> 'T') or
913                    ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' THEN
914 
915                IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' THEN
916                       --print quantity heading WITH COLSPAN=2
917                   htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long,'CENTER','','','','2');
918                ELSIF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_UNIT_PRICE' THEN
919                   htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long || ' (' || c_currency || ')', 'CENTER','','','','','width=80');
920                ELSE
921                   htp.tableData(ak_query_pkg.g_items_table(i).attribute_label_long, 'CENTER');
922                END IF;
923         END IF;
924 
925         -- Find line id, urls value id
926         if ak_query_pkg.g_items_table(i).value_id is not null then
927 
928            --need line_id to call javascript function down() and up()
929            if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_LINE_ID') then
930               v_line_id_ind := ak_query_pkg.g_items_table(i).value_id;
931            end if;
932            -- find item_url and supplier_item_url
933            if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_ITEM_URL') then
934               v_item_url_ind := ak_query_pkg.g_items_table(i).value_id;
935            end if;
936            if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUPPLIER_URL') then
937               v_supplier_url_ind := ak_query_pkg.g_items_table(i).value_id;
938            end if;
939            if (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUPPLIER_ITEM_URL') then
940               v_supplier_item_url_ind := ak_query_pkg.g_items_table(i).value_id;
941            end if;
942         end if;
943 
944      END LOOP;
945 
946 
947      htp.tableRowClose;
948 
949      counter := 0;
950      -- Print the table data
951 
952      -- FOR j IN p_start_row - 1 .. end_row - 1 LOOP
953      FOR j IN ak_query_pkg.g_results_table.FIRST .. ak_query_pkg.g_results_table.LAST LOOP
954 
955        temp_table := '<TR BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW') || '">';
956 
957        icx_util.transfer_Row_To_Column( ak_query_pkg.g_results_table(j), y_table) ;
958 
959        FOR i in ak_query_pkg.g_items_table.FIRST .. ak_query_pkg.g_items_table.LAST LOOP
960 
961          -- Print quantity input text box and up button
962              IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_QTY' THEN
963                 v_line_id := y_table(v_line_id_ind);
964                 display_text := '<TD ROWSPAN=2><CENTER> <INPUT TYPE=''text'' NAME=''Quantity'' '
965 || ' SIZE=' || to_char(V_QUANTITY_LENGTH) || ' onChange=''if(!parent.parent.parent.checkNumber(this)){this.focus();this.value="";}''></CENTER></TD>';
966 
967                  l_spin_pos := l_pos;
968 
969      		 display_text := display_text
970 		   || '<TD width=18 valign=bottom> <a href="javascript:parent.parent.parent.up(document.template_items.elements['
971 		   || l_spin_pos
972 		   || '])" onMouseOver="window.status=''Add Quantity'';return true"><IMG SRC=/OA_MEDIA/'
973 		   || v_lang
974 		   || '/FNDISPNU.gif border=0></a></TD>';
975 
976                  l_pos := l_pos + 1;
977 
978                  temp_table := temp_table ||  display_text;
979              END IF;
980 
981              /* Sequence number is one of the required attribute to
982                 to identify a row in the cart line */
983              IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SEQUENCE' THEN
984                    display_text := '<INPUT TYPE="HIDDEN" NAME="v_sequence_num" VALUE =' || y_table(ak_query_pkg.g_items_table(i).value_id) || '>';
985                    l_pos := l_pos + 1;
986                    temp_table := temp_table || display_text;
987              END IF;
988 
989              IF ak_query_pkg.g_items_table(i).value_id IS NOT NULL -- not including ICX_QTY
990                   AND ak_query_pkg.g_items_table(i).node_display_flag = 'Y'
991                   AND ak_query_pkg.g_items_table(i).secured_column <> 'T'
992                   AND ak_query_pkg.g_items_table(i).item_style <> 'HIDDEN' THEN
993 
994 /* Ref Bug #640289 : Changed By Suri. The Standard Requisitions/Emergency Requisitions Unit Price  field should allow more than two decimal places. ***/
995 
996                      IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_UNIT_PRICE' THEN
997 --                        display_text := to_char(to_number(y_table(ak_query_pkg.g_items_table(i).value_id)), c_money_fmt_mask);
998                           display_text := to_char(to_number(y_table(ak_query_pkg.g_items_table(i).value_id)));
999 /* End Change Bug #640289 By Suri ***/
1000                      ELSE
1001                         display_text := y_table(ak_query_pkg.g_items_table(i).value_id);
1002                      END IF;
1003 
1004                      IF display_text is null THEN
1005                         display_text := htf.br;
1006                      END IF;
1007                      IF display_text = '-' THEN
1008                         display_text := htf.br;
1009                      END IF;
1010                      IF display_text = 'null' THEN
1011                         display_text := htf.br;
1012                      END IF;
1013                      IF display_text = '-1' THEN
1014                         display_text := htf.br;
1015                      END IF;
1016 
1017 
1018                      -- Display item_description as a link and a tabledata
1019                      IF (ak_query_pkg.g_items_table(i).attribute_code = 'ICX_ITEM_DESCRIPTION') THEN
1020                         v_item_url := y_table(v_item_url_ind);
1021                         display_text := ICX_REQ_NAVIGATION.addURL(v_item_url, display_text);
1022                      END IF;
1023 
1024                       -- Display source_name as a link
1025                       IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_NAME' THEN
1026                          v_supplier_url := y_table(v_supplier_url_ind);
1027                          display_text := ICX_REQ_NAVIGATION.addURL(v_supplier_url, display_text);
1028                       END IF;
1029 
1030                       -- Display supplier item number as a link
1031                       IF ak_query_pkg.g_items_table(i).attribute_code = 'ICX_SUGGESTED_VENDOR_ITEM_NUM' THEN
1032                          v_supplier_item_url := y_table(v_supplier_item_url_ind);
1033                          display_text := ICX_REQ_NAVIGATION.addURL(v_supplier_item_url, display_text);
1034                       END IF;
1035 
1036                       -- Bold
1037                       IF ak_query_pkg.g_items_table(i).bold = 'Y' THEN
1038            	             display_text := htf.bold(display_text);
1039                       END IF;
1040 
1041                       -- Italics
1042                       IF ak_query_pkg.g_items_table(i).italic = 'Y' THEN
1043      	                   display_text := htf.italic(display_text);
1044            	      END IF;
1045 
1046                       temp_table := temp_table ||
1047                                        htf.tableData( cvalue   => display_text,
1048                                                      calign   => ak_query_pkg.g_items_table(i).horizontal_alignment,
1049                                                      crowspan => '2',
1050                                                      cattributes => ' VALIGN=' || ak_query_pkg.g_items_table(i).vertical_alignment
1051                                                    );
1052 
1053              END IF; /* if value_id is not null */
1054        END LOOP;  -- for i in 1 .. ak_query_pkg.g_items_table.first loop
1055 
1056        temp_table := temp_table || htf.tableRowClose;
1057 
1058        --print the down button
1059        display_text := htf.tableRowOpen( cattributes => 'BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW')||'"');
1060 
1061        display_text := htf.tableRowOpen( cattributes => 'BGColor="#'||icx_util.get_color('TABLE_DATA_MULTIROW')||'"');
1062 
1063 
1064        display_text := display_text
1065 	 || '<TD WIDTH=18 valign=top><a href="javascript:parent.parent.parent.down(document.template_items.elements['
1066 	 || l_spin_pos
1067 	 || '])" onMouseOver="window.status=''Reduce Quantity'';return true"><IMG SRC=/OA_MEDIA/'
1068 	 || v_lang || '/FNDISPND.gif  BORDER=0></a>';
1069 
1070        display_text := display_text || '</TD>';
1071        display_text := display_text || htf.tableRowClose;
1072        temp_table := temp_table ||  display_text;
1073 
1074        htp.p(temp_table);
1075 
1076        counter := counter + 1;
1077 
1078      END LOOP;  -- for j in 1 .. g_results_table.COUNT loop
1079 
1080      htp.tableClose;
1081 
1082 
1083 
1084 
1085      htp.p('</FORM>');
1086      htp.bodyClose;
1087      htp.htmlClose;
1088 
1089 END IF; /* validate session */
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092    icx_util.add_error(substr(SQLERRM, 12, 512));
1093    icx_util.error_page_print;
1094 
1095 END template_items_display;
1096 
1097 
1098 PROCEDURE submit_items ( cartId IN NUMBER,
1099                         p_start_row      IN NUMBER DEFAULT 1,
1100 		        p_end_row        IN NUMBER DEFAULT NULL,
1101 		        p_where          IN VARCHAR2,
1102                         v_express_name   IN VARCHAR2,
1103 	                p_emergency          IN NUMBER DEFAULT NULL,
1104                         end_row          IN NUMBER DEFAULT NULL,
1105                         p_query_set      IN NUMBER DEFAULT NULL,
1106                         p_row_count      IN NUMBER DEFAULT NULL,
1107                         Quantity         IN ICX_OWA_PARMS.ARRAY DEFAULT ICX_OWA_PARMS.empty,
1108                         v_sequence_num   IN ICX_OWA_PARMS.ARRAY DEFAULT ICX_OWA_PARMS.empty) IS
1109 
1110   l_cart_id NUMBER := NULL;
1111   l_emergency varchar2(10);
1112   l_line_id NUMBER;
1113   l_num_rows NUMBER;
1114   l_cart_line_id number;
1115   l_shopper_id number;
1116   l_org_id number;
1117   l_qty number;
1118   v_org  number;
1119   params icx_on_utilities.v80_table;
1120   l_pad number;
1121 
1122   -- temp variables used to query deliver-to-requestor-info
1123   v_requestor_id number;
1124   v_requestor_name VARCHAR2(80);
1125   d_location_id NUMBER;
1126   d_location_code VARCHAR2(40);
1127   d_org_id NUMBER;
1128   d_org_code VARCHAR2(40);
1129 
1130 
1131   CURSOR check_cart_line_exists(v_cart_id number,v_sequence_num number,
1132                                 v_org_id number, v_express_name varchar2) IS
1133   SELECT cart_line_id
1134   FROM  icx_shopping_cart_lines
1135   WHERE cart_id = v_cart_id
1136   AND   line_id = v_sequence_num
1137   AND   express_name = v_express_name
1138   AND   NVL(org_id, -9999) = NVL(v_org_id,-9999);
1139 
1140   CURSOR get_cart_header_info(v_cart_id number) IS
1141   SELECT need_by_date,
1142          deliver_to_requestor_id,
1143          deliver_to_location_id,
1144          destination_organization_id,
1145          deliver_to_location,
1146          created_by,
1147          org_id
1148   FROM  icx_shopping_carts
1149   WHERE cart_id = v_cart_id
1150   FOR UPDATE;
1151 
1152   l_need_by_date date;
1153   l_deliver_to_location_id number;
1154   l_dest_org_id number;
1155   l_rows_added NUMBER := 0;
1156   l_qty_added NUMBER := 0;
1157   l_qty_updated NUMBER := 0;
1158   l_rows_updated NUMBER := 0;
1159   l_deliver_to_location VARCHAR2(240);
1160   l_created_by NUMBER := NULL;
1161   l_order_total NUMBER := 0;
1162   v_cart_line_number NUMBER := NULL;
1163 
1164   l_emp_id number;
1165   l_account_id NUMBER := NULL;
1166   l_account_num VARCHAR2(2000) := NULL;
1167   l_segments fnd_flex_ext.SegmentArray;
1168 
1169 BEGIN
1170 
1171   IF icx_sec.validateSession THEN
1172 
1173     icx_util.error_page_setup;
1174 
1175     l_cart_id := to_number(icx_call.decrypt2(cartId));
1176     l_emergency := icx_call.decrypt2(p_emergency);
1177     l_rows_added := 0;
1178     l_rows_updated := 0;
1179     l_num_rows := Quantity.COUNT;
1180     l_shopper_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
1181     -- l_org_id := icx_sec.getId(icx_sec.PV_ORG_ID);
1182 
1183     IF p_where IS NOT NULL THEN
1184         icx_on_utilities.unpack_parameters(icx_call.decrypt2(p_where), params);
1185         v_org := params(2);
1186     END IF;
1187 
1188     IF cartId IS NOT NULL THEN
1189        OPEN get_cart_header_info(l_cart_id);
1190        FETCH get_cart_header_info INTO l_need_by_date, l_emp_id,
1191                                        l_deliver_to_location_id,
1192                                        l_dest_org_id, l_deliver_to_location,
1193                                        l_created_by, l_org_id;
1194        CLOSE get_cart_header_info;
1195     END IF;
1196 
1197     /* Select the max of the cart_line_number for ordering */
1198     SELECT max(cart_line_number) + 1 into v_cart_line_number
1199     FROM icx_shopping_cart_lines
1200     WHERE cart_id = l_cart_id;
1201 
1202     IF v_cart_line_number IS NULL THEN
1203      /* This is the first one  */
1204      v_cart_line_number := 1;
1205     END IF;
1206 
1207     FOR i IN 1 .. l_num_rows LOOP
1208 
1209     l_pad := instr(Quantity(i),'.',1,2);
1210     if (l_pad > 2) then
1211        l_qty := substr(Quantity(i),1,l_pad - 1);
1212     elsif (l_pad > 0) then
1213        l_qty := 0;
1214     else
1215        l_qty := Quantity(i);
1216     end if;
1217 
1218       IF l_qty IS NOT NULL AND l_qty > 0 THEN
1219 
1220         l_cart_line_id := NULL;
1221         OPEN check_cart_line_exists(l_cart_id, v_sequence_num(i), l_org_id,
1222                                     v_express_name);
1223         FETCH check_cart_line_exists into l_cart_line_id;
1224         CLOSE check_cart_line_exists;
1225 
1226 
1227         IF l_cart_line_id IS NULL THEN
1228 
1229           l_line_id := v_sequence_num(i);
1230 
1231 --changed by alex for attachment
1232 --          select icx_shopping_cart_lines_s.nextval into l_cart_line_id
1233 --          from dual;
1234 --new code:
1235           select PO_REQUISITION_LINES_S.nextval into l_cart_line_id
1236 	    from dual;
1237 	  /* get contact id for deliver_to_requestor */
1238 	  v_requestor_id := icx_sec.getID(icx_sec.PV_INT_CONTACT_ID);
1239 
1240 	  ICX_REQ_NAVIGATION.shopper_info(v_requestor_id,
1241 					  v_requestor_name,
1242 					  d_location_id,
1243 					  d_location_code,
1244 					  d_org_id,
1245 					  d_org_code);
1246 
1247           INSERT INTO icx_shopping_cart_lines
1248 	    (cart_line_id,
1249 	     cart_id,
1250 	     creation_date,
1251 	     created_by,
1252 	     quantity,
1253 	     line_id,
1254 	     item_id,
1255 	     item_revision,
1256 	     unit_of_measure,
1257 	     unit_price,
1258 	     category_id,
1259 	     line_type_id,
1260 	     item_description,
1261 	     destination_organization_id,
1262 	     deliver_to_location_id,
1263 	     suggested_buyer_id,
1264 	     suggested_vendor_name,
1265 	     suggested_vendor_site,
1266 	     need_by_date,
1267 	     suggested_vendor_contact,
1268 	     suggested_vendor_phone,
1269 	     suggested_vendor_item_num,
1270 	     -- supplier_item_num, Obselate?
1271 	     last_update_date,
1272 	     last_updated_by,
1273 	     org_id,
1274 	     express_name,
1275 	     item_number,
1276 	     deliver_to_location,
1277 	     custom_defaulted,
1278 	     cart_line_number,
1279 	     autosource_doc_header_id,
1280 	     autosource_doc_line_num
1281 	     --	     ,deliver_to_requestor,
1282 	     --	     deliver_to_requestor_id
1283 	     )
1284 	    SELECT /* into icx_shopping_cart_lines */
1285 	    l_cart_line_id,
1286 	    l_cart_id,
1287 	    sysdate,
1288 	    l_shopper_id,
1289 	    l_qty,
1290 	    l_line_id,
1291 	    prl.item_id,
1292 	    prl.item_revision,
1293 	    prl.unit_meas_lookup_code,
1294 	    DECODE(ROUND(NVL(pl.unit_price, 0)*NVL(ph.rate,1),5),0,
1295 		   NVL(prl.unit_price, 0), ROUND(NVL(pl.unit_price,0)*
1296 						 NVL(ph.rate,1), 5)),
1297 	    prl.category_id,
1298 	    prl.line_type_id,
1299 	    prl.item_description,
1300 	    l_dest_org_id,
1301 	    l_deliver_to_location_id,
1302 	    prl.suggested_buyer_id,
1303 	    pv.vendor_name,
1304 	    pvs.vendor_site_code,
1305 	    l_need_by_date,
1306 	    DECODE(prl.suggested_vendor_contact_id, NULL, NULL,
1307 		   pvc.last_name ||',' ||pvc.first_name),
1308 	    pvc.phone,
1309 	    prl.suggested_vendor_product_code,
1310 	    -- supplier item num ?
1311 	    sysdate,
1312 	    l_shopper_id,
1313 	    l_org_id,
1314 	    v_express_name,
1315 	    msi.concatenated_segments,
1316 	    l_deliver_to_location,
1317 	    'N',
1318 	    v_cart_line_number,
1319 	    prl.po_header_id,
1320 	    pl.line_num
1321 --	    ,v_requestor_name
1322 --	    ,v_requestor_id
1323 	    FROM po_reqexpress_headers prh,
1324 	    po_reqexpress_lines prl,
1325 	    mtl_system_items_kfv msi,
1326 	    po_vendor_contacts pvc,
1327 	    po_vendor_sites pvs,
1328 	    po_vendors pv,
1329 	    po_headers ph,
1330 	    po_lines pl
1331 	    WHERE prh.express_name = prl.express_name
1332 	    AND   prl.suggested_vendor_id = pv.vendor_id(+)
1333 	    AND   prl.suggested_vendor_site_id = pvs.vendor_site_id(+)
1334 	    AND   prl.suggested_vendor_contact_id = pvc.vendor_contact_id(+)
1335 	    AND   prl.po_header_id = ph.po_header_id(+)
1336 	    AND   nvl(ph.po_header_id, -1) = nvl(pl.po_header_id, -1)
1337 	    AND   prl.po_line_id = pl.po_line_id(+)
1338 	    AND   prl.source_type_code = 'VENDOR'
1339 	    AND   prl.item_id is not null
1340 	      AND   prl.item_id = msi.inventory_item_id
1341 	      AND   msi.purchasing_enabled_flag = 'Y'
1342 	      AND   prl.express_name = v_express_name
1343 	      AND   prl.sequence_num = to_number(l_line_id)
1344 	      AND   msi.organization_id = v_org
1345 	      UNION
1346 	      SELECT
1347 	      l_cart_line_id,
1348 	      l_cart_id,
1349 	      sysdate,
1350 	      l_shopper_id,
1351 	      l_qty,
1352 	      l_line_id,
1353 	      prl.item_id,
1354 	      prl.item_revision,
1355 	      prl.unit_meas_lookup_code,
1356 	      DECODE(ROUND(NVL(pl.unit_price, 0)*NVL(ph.rate,1),5),0,
1357 		     NVL(prl.unit_price, 0), ROUND(NVL(pl.unit_price,0)*
1358 						   NVL(ph.rate,1), 5)),
1359 	      prl.category_id,
1360 	      prl.line_type_id,
1361 	      prl.item_description,
1362 	      l_dest_org_id,
1363 	      l_deliver_to_location_id,
1364 	      prl.suggested_buyer_id,
1365 	      pv.vendor_name,
1366 	      pvs.vendor_site_code,
1367 	      l_need_by_date,
1368 	      DECODE(prl.suggested_vendor_contact_id, NULL, NULL,
1369 		     pvc.last_name ||',' ||pvc.first_name),
1370 	      pvc.phone,
1371 	      prl.suggested_vendor_product_code,
1372 	      -- supplier item num ?
1373 	      sysdate,
1374 	      l_shopper_id,
1375 	      l_org_id,
1376 	      v_express_name,
1377 	      NULL,
1378 	      l_deliver_to_location,
1379 	      'N',
1380 	      v_cart_line_number,
1381 	      prl.po_header_id,
1382 	      pl.line_num
1383 --	      ,v_requestor_name
1384 --	      ,v_requestor_id
1385 	      FROM po_reqexpress_headers prh,
1386 	      po_reqexpress_lines prl,
1387 	      po_vendor_contacts pvc,
1388 	      po_vendor_sites pvs,
1389 	      po_vendors pv,
1390 	      po_headers ph,
1391 	      po_lines pl
1392 	      WHERE prh.express_name = prl.express_name
1393 	      AND   prl.suggested_vendor_id = pv.vendor_id(+)
1394 	      AND   prl.suggested_vendor_site_id = pvs.vendor_site_id(+)
1395 	      AND   prl.suggested_vendor_contact_id = pvc.vendor_contact_id(+)
1396 	      AND   prl.po_header_id = ph.po_header_id(+)
1397 	      AND   nvl(ph.po_header_id, -1) = nvl(pl.po_header_id, -1)
1398 	      AND   prl.po_line_id = pl.po_line_id(+)
1399 	      AND   prl.source_type_code = 'VENDOR'
1400 	      AND   prl.item_id is null
1401 		AND   prl.express_name = v_express_name
1402 		AND   prl.sequence_num = to_number(l_line_id);
1403 	      /* end of insert into icx_shopping_cart_lines */
1404 
1405           -- Get the default accounts and update distributions
1406           icx_req_acct2.get_default_account(l_cart_id,l_cart_line_id,
1407                         l_emp_id,l_org_id,l_account_id,l_account_num);
1408 
1409           l_rows_added := l_rows_added + 1;
1410           l_qty_added  := l_qty_added + l_qty;
1411           v_cart_line_number := v_cart_line_number + 1;
1412 
1413          ELSE
1414 
1415            UPDATE icx_shopping_cart_lines
1416            SET quantity = quantity + l_qty,
1417                last_update_date = sysdate,
1418                last_updated_by   = l_shopper_id
1419            WHERE cart_id = l_cart_id
1420            AND   cart_line_id = l_cart_line_id;
1421 
1422            l_rows_updated := l_rows_updated + 1;
1423            l_qty_updated  := l_qty_updated + l_qty;
1424          END IF;
1425 
1426         END IF;
1427 
1428     END LOOP;
1429 
1430     COMMIT;
1431 
1432     /* Call Custom defaults */
1433     if l_emergency is not NULL and l_emergency = 'YES' then
1434        icx_req_custom.reqs_default_lines('YES', l_cart_id);
1435     else
1436        icx_req_custom.reqs_default_lines('NO', l_cart_id);
1437     end if;
1438 
1439     /* get the order total; do this after custom defaults as it clould
1440        modify the price or quantity */
1441     SELECT SUM(quantity * unit_price) INTO l_order_total
1442     FROM  icx_shopping_cart_lines
1443     WHERE cart_id = l_cart_id;
1444 
1445     total_page(l_rows_added,l_rows_updated, l_qty_added, l_qty_updated,
1446                 l_order_total, l_dest_org_id, v_express_name,
1447                 p_start_row, p_end_row, p_where,
1448                 end_row, p_query_set, p_row_count);
1449 
1450   END IF; /* validate session */
1451 
1452 EXCEPTION
1453 
1454  WHEN OTHERS THEN
1455     -- htp.p('Error in Submit Items.');
1456     -- htp.br;
1457     -- htp.p(substr(SQLERRM, 1, 512));
1458     -- htp.br;
1459     icx_util.add_error(substr(SQLERRM, 12, 512));
1460     icx_util.error_page_print;
1461 
1462 END submit_items;
1463 
1464 PROCEDURE total_page(l_rows_added number default 0,
1465                      l_rows_updated number default 0,
1466                      l_qty_added number default 0,
1467                      l_qty_updated number default 0,
1468                      l_order_total number default 0,
1469                      l_dest_org_id number,
1470                      v_express_name VARCHAR2 default null,
1471                      p_start_row NUMBER DEFAULT 1,
1472                      p_end_row NUMBER DEFAULT NULL,
1473                      p_where   VARCHAR2,
1474                      end_row NUMBER DEFAULT NULL,
1475                      p_query_set NUMBER DEFAULT NULL,
1476                      p_row_count NUMBER DEFAULT NULL) IS
1477 
1478  l_add_message    varchar2(500) := '';
1479  l_update_message varchar2(500) := '';
1480  l_print_message  varchar2(1100) := '';
1481  l_order_total_message  varchar2(1100) := '';
1482 
1483  l_template_selected_message  varchar2(200) := '';
1484  l_return_to_current_message  varchar2(200) := '';
1485  l_return_to_next_message  varchar2(200) := '';
1486  v_icx_template VARCHAR2(200):= NULL;
1487  l_navigation_message  varchar2(2000) := '';
1488 
1489  v_order_total    varchar2(30) := '';
1490 
1491  l_currency       VARCHAR2(15);
1492  l_precision      NUMBER(1);
1493  l_fmt_mask       VARCHAR2(32);
1494  v_dcd_name       VARCHAR2(1000) := owa_util.get_cgi_env('SCRIPT_NAME');
1495  next_start_row   NUMBER := NULL;
1496  next_end_row     NUMBER := NULL;
1497 
1498 BEGIN
1499 
1500    /* get the currency code */
1501    icx_req_navigation.get_currency(l_dest_org_id, l_currency,
1502                                    l_precision, l_fmt_mask);
1503 
1504    FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_NEW');
1505    FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY', l_rows_added);
1506    l_add_message := FND_MESSAGE.GET;
1507    l_print_message := l_add_message;
1508 
1509    IF l_rows_updated > 0 THEN
1510       FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_UPDATE');
1511       FND_MESSAGE.SET_TOKEN('ITEM_QUANTITY', l_rows_updated);
1512       l_update_message := FND_MESSAGE.GET;
1513       IF l_rows_added > 0 THEN
1514          l_print_message := l_add_message || '<BR>' || l_update_message;
1515       ELSE
1516          l_print_message := l_update_message;
1517       END IF;
1518    END IF;
1519 
1520    /* Build the new order total message */
1521    FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_TOTAL');
1522    FND_MESSAGE.SET_TOKEN('CURRENCY_CODE', l_currency);
1523    v_order_total := to_char(to_number(l_order_total), fnd_currency.get_format_mask(l_currency, 30));
1524    FND_MESSAGE.SET_TOKEN('REQUISITION_TOTAL', v_order_total);
1525    l_order_total_message := FND_MESSAGE.GET;
1526 
1527    /* 'Seleted from template' message */
1528    FND_MESSAGE.SET_NAME('ICX', 'ICX_TEMPLATE');
1529    v_icx_template := FND_MESSAGE.GET;
1530    FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_SOURCE');
1531    FND_MESSAGE.SET_TOKEN('SOURCE_NAME', v_icx_template || ' ' || v_express_name);
1532    l_template_selected_message := FND_MESSAGE.GET;
1533 
1534    /* 'Return to current set' message */
1535    FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_RETURN_CURRENT');
1536    l_return_to_current_message := FND_MESSAGE.GET;
1537 
1538    /* 'Return to next set' message */
1539    FND_MESSAGE.SET_NAME('ICX','ICX_ITEM_ADD_RETURN_NEXT');
1540    l_return_to_next_message := FND_MESSAGE.GET;
1541 
1542 
1543    htp.bodyOpen('','BGCOLOR="#FFCCCC" onLoad="top.winOpen(''nav'', ''template'')"');
1544 
1545    htp.p('<H3>');
1546    htp.p(l_print_message);
1547    htp.br;
1548    htp.br; -- add line between update and total message
1549    htp.p(l_order_total_message);
1550    htp.br;
1551    htp.br; -- leave two line between the messages
1552    htp.br;
1553    htp.p(l_template_selected_message);
1554    l_navigation_message := '<TABLE BORDER=0><TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="' || v_dcd_name ||
1555 '/ICX_REQ_TEMPLATES.template_items?p_start_row=' || p_start_row || '&p_end_row=' || p_end_row || '&p_where=' || p_where || '">' ||  l_return_to_current_message || '</A></B></TD></TR>';
1556 /*
1557    htp.p('<DL>');
1558    htp.p('<DT>' || l_template_selected_message);
1559    htp.p('<DL>');
1560    htp.p('<DT>' || '<A HREF="' || v_dcd_name || '/ICX_REQ_TEMPLATES.template_items?p_start_row=' || p_start_row || '&p_end_row='|| p_end_row || '&p_where=' || p_where ||  '">'  || l_return_to_current_message || '</A>');
1561    htp.p('</DL>');
1562 */
1563 
1564    /* find next set start row and next set end row */
1565       if end_row < p_row_count
1566          and p_query_set is not NULL then
1567 
1568          next_start_row := end_row+1;
1569          if end_row+p_query_set > p_row_count then
1570              next_end_row := p_row_count;
1571          else
1572              next_end_row := end_row+p_query_set;
1573          end if;
1574          l_navigation_message := l_navigation_message || '<TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="' || v_dcd_name ||
1575 '/ICX_REQ_TEMPLATES.template_items?p_start_row=' || next_start_row || '&p_end_row=' || next_end_row || '&p_where=' || p_where || '">' || l_return_to_next_message || '</A></B></TD></TR>';
1576 
1577 
1578 /*
1579          htp.p('<DL>');
1580          htp.p('<DT>' || '<A HREF="' || v_dcd_name || '/ICX_REQ_TEMPLATES.template_items?p_start_row=' || next_start_row || '&p_end_row='|| next_end_row || '&p_where=' || p_where ||  '">'  || l_return_to_next_message || '</A>');
1581 
1582    -- htp.p('<DT>' || l_return_to_next_message);
1583          htp.p('</DL>');
1584 */
1585 
1586       end if;
1587 
1588       -- MESSAGE NEEDS TO BE SWITCHED TO REVIEW MY ORDER
1589       FND_MESSAGE.SET_NAME('ICX','ICX_REVIEW_ORDER');
1590       l_return_to_next_message := FND_MESSAGE.GET;
1591       l_navigation_message := l_navigation_message || '<TR><TD><BR></TD><TD><BR></TD><TD><BR></TD><TD NOWRAP>' || '<B><A HREF="javascript:parent.parent.parent.switchFrames(''my_order'')">' || l_return_to_next_message || '</A></B></TD></TR>';
1592 
1593 
1594       l_navigation_message := l_navigation_message || '</TABLE>';
1595 
1596 
1597    -- htp.p('</DL>');
1598    htp.p(l_navigation_message);
1599    htp.p('</H3>');
1600 
1601    htp.bodyClose;
1602 
1603 
1604 EXCEPTION
1605 
1606  WHEN OTHERS THEN
1607     -- htp.p('Error in Total print message page.');
1608     -- htp.br;
1609     -- htp.p(substr(SQLERRM, 1, 512));
1610     -- htp.br;
1611     icx_util.add_error(substr(SQLERRM, 12, 512));
1612     icx_util.error_page_print;
1613 END total_page;
1614 
1615 end ICX_REQ_TEMPLATES;