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.OE_OE_FOLDER_EXT

Source


1 PACKAGE BODY Oe_Oe_Folder_Ext AS
2 /* $Header: OEXFEXTB.pls 120.1.12010000.1 2008/07/25 07:47:56 appldev ship $ */
3 
4 PROCEDURE Get_Customized_Buttons
5                (
6                p_folder_id        IN  Number
7              , x_custom_buttons_tbl OUT NOCOPY /* file.sql.39 change */ Oe_Oe_Folder_Ext.Config_Buttons_Tbl
8              , x_default_buttons_tbl OUT NOCOPY /* file.sql.39 change */ Oe_Oe_Folder_Ext.Config_Buttons_Tbl
9                 )
10    IS
11    /*Commented for Bug 6829128
12    CURSOR C1(l_folder_id Number) IS
13    SELECT action_id,object,action_name,user_entered_prompt,
14          folder_id,width,access_key,DISPLAY_AS_BUTTON_FLAG
15    FROM  OE_Custom_Actions
16    WHERE DISPLAY_AS_BUTTON_FLAG='Y' AND
17          folder_id=l_folder_id;*/
18 
19    --Modified for Bug#6829128
20    CURSOR C1(l_folder_id Number) is
21    SELECT *
22    FROM
23      (SELECT action_id,
24         object,
25         action_name,
26         user_entered_prompt,
27         folder_id,
28         width,
29         access_key,
30         display_as_button_flag,
31         nvl(user_entered_prompt,    default_prompt) PROMPT
32      FROM oe_custom_actions
33       WHERE display_as_button_flag = 'Y'
34       AND folder_id = l_folder_id
35       UNION
36       SELECT action_id,
37         object,
38         action_name,
39         NULL,
40         NULL,
41         width,
42         NULL,
43         display_as_button_flag,
44         decode(action_name,    'RITEMS',    'Related Items',    'BOOK_ORDER',    'Book Order',    initcap(action_name)) PROMPT
45      FROM oe_default_actions od
46       WHERE display_as_button_flag = 'Y'
47       AND NOT EXISTS
48        (SELECT action_id
49         FROM oe_custom_actions oc
50         WHERE oc.action_id = od.action_id
51         AND folder_id = l_folder_id)
52      )
53    ORDER BY PROMPT;
54    --End of Bug#6829128
55 
56    CURSOR C2(l_folder_id Number) IS
57    SELECT action_id,object,action_name,
58          width,DISPLAY_AS_BUTTON_FLAG
59    FROM  OE_Default_Actions  OD
60    WHERE DISPLAY_AS_BUTTON_FLAG='Y' AND
61    NOT EXISTS
62   (SELECT action_id FROM
63    OE_CUSTOM_ACTIONS OC
64    WHERE OC.action_id=OD.action_id
65    AND Folder_Id=l_folder_id);
66 
67    l_count Number:=1;
68 BEGIN
69 
70 
71 
72      FOR BUTTONS IN C1(p_folder_id)
73      LOOP
74        x_custom_buttons_tbl(l_count).Action_Id:=BUTTONS.Action_id;
75        x_custom_buttons_tbl(l_count).Action_Name:=BUTTONS.Action_Name;
76        x_custom_buttons_tbl(l_count).User_Entered_Prompt:=BUTTONS.User_Entered_Prompt;
77        x_custom_buttons_tbl(l_count).Folder_Id:=BUTTONS.Folder_Id;
78        x_custom_buttons_tbl(l_count).Width:=BUTTONS.Width;
79        x_custom_buttons_tbl(l_count).access_key:=BUTTONS.access_key;
80        x_custom_buttons_tbl(l_count).display_as_button:=
81                BUTTONS.display_as_button_flag;
82        l_count:=l_count+1;
83      END LOOP;
84 
85     l_count:=1;
86      FOR BUTTONS IN C2(p_folder_id)
87      LOOP
88        x_default_buttons_tbl(l_count).Action_Id:=BUTTONS.Action_id;
89        x_default_buttons_tbl(l_count).Action_Name:=BUTTONS.Action_Name;
90        x_default_buttons_tbl(l_count).Width:=BUTTONS.Width;
91        x_default_buttons_tbl(l_count).display_as_button:=
92                     BUTTONS.display_as_button_flag;
93        l_count:=l_count+1;
94      END LOOP;
95 
96    EXCEPTION
97    WHEN NO_DATA_FOUND THEN
98     Null;
99    WHEN TOO_MANY_ROWS THEN
100     Null;
101    WHEN OTHERS THEN
102     Null;
103 END Get_Customized_Buttons;
104 
105 
106 PROCEDURE Get_Buttons_List
107                (
108                p_folder_id        IN  Number
109              , p_displayed_buttons IN Oe_Oe_Folder_Ext.Config_Buttons_Tbl
110              , x_buttons_tbl OUT NOCOPY /* file.sql.39 change */ Oe_Oe_Folder_Ext.Config_Buttons_Tbl
111                 )
112    IS
113    CURSOR C1(l_folder_id Number) IS
114    SELECT action_id,object,action_name,user_entered_prompt,
115          folder_id,width,access_key,display_as_button_flag,default_prompt
116    FROM  OE_Custom_Actions
117    WHERE folder_id=l_folder_id;
118 
119    CURSOR C2(l_folder_id Number) IS
120    SELECT action_id,object,action_name,
121          width,display_as_button_flag
122    FROM  OE_Default_Actions  OD
123    WHERE  NOT EXISTS
124   (SELECT action_id FROM
125    OE_CUSTOM_ACTIONS OC
126    WHERE OC.action_id=OD.action_id
127    AND Folder_Id=l_folder_id);
128 
129 
130    l_count Number:=1;
131 BEGIN
132 
133 
134      FOR BUTTONS IN C1(p_folder_id)
135      LOOP
136        x_buttons_tbl(l_count).Action_Id:=BUTTONS.Action_id;
137        x_buttons_tbl(l_count).Action_Name:=BUTTONS.Action_Name;
138        x_buttons_tbl(l_count).User_Entered_Prompt:=BUTTONS.User_Entered_Prompt;
139        x_buttons_tbl(l_count).access_key:=BUTTONS.access_key;
140        x_buttons_tbl(l_count).default_prompt:=BUTTONS.default_prompt;
141        x_buttons_tbl(l_count).display_as_button:=BUTTONS.display_as_button_flag;
142        x_buttons_tbl(l_count).Folder_Id:=BUTTONS.Folder_Id;
143        x_buttons_tbl(l_count).Width:=BUTTONS.Width;
144             BEGIN
145              SELECT default_prompt into
146                     x_buttons_tbl(l_count).default_prompt
147              FROM  oe_custom_actions
148              WHERE action_name=BUTTONS.Action_Name
149              AND default_prompt IS NOT NULL
150              AND ROWNUM=1;
151              EXCEPTION
152              WHEN NO_DATA_FOUND THEN
153               Null;
154              WHEN TOO_MANY_ROWS THEN
155               Null;
156              WHEN OTHERS THEN
157               Null;
158             END;
159        l_count:=l_count+1;
160      END LOOP;
161 
162      FOR BUTTONS IN C2(p_folder_id)
163      LOOP
164        x_buttons_tbl(l_count).Action_Id:=BUTTONS.Action_id;
165        x_buttons_tbl(l_count).Action_Name:=BUTTONS.Action_Name;
166        x_buttons_tbl(l_count).Width:=BUTTONS.Width;
167        x_buttons_tbl(l_count).display_as_button:=BUTTONS.display_as_button_flag;
168             BEGIN
169              SELECT default_prompt into
170                     x_buttons_tbl(l_count).default_prompt
171              FROM  oe_custom_actions
172              WHERE action_name=BUTTONS.Action_Name
173              AND default_prompt IS NOT NULL
174              AND ROWNUM=1;
175              EXCEPTION
176              WHEN NO_DATA_FOUND THEN
177               Null;
178              WHEN TOO_MANY_ROWS THEN
179               Null;
180              WHEN OTHERS THEN
181               Null;
182             END;
183        l_count:=l_count+1;
184      END LOOP;
185 
186    EXCEPTION
187    WHEN NO_DATA_FOUND THEN
188     Null;
189    WHEN TOO_MANY_ROWS THEN
190     Null;
191    WHEN OTHERS THEN
192     Null;
193 END Get_Buttons_List;
194 
195 PROCEDURE Store_Custom_Buttons
196             (
197              p_folder_id        IN  Number
198            , p_config_buttons_tbl IN Oe_Oe_Folder_Ext.Config_Buttons_Tbl
199            , l_return_status     OUT NOCOPY /* file.sql.39 change */ Varchar2
200            , x_custom_buttons_tbl OUT NOCOPY /* file.sql.39 change */ Oe_Oe_Folder_Ext.Config_Buttons_Tbl
201            , x_default_buttons_tbl OUT NOCOPY /* file.sql.39 change */ Oe_Oe_Folder_Ext.Config_Buttons_Tbl
202             )
203  IS PRAGMA AUTONOMOUS_TRANSACTION;
204  l_action_id Number;
205 BEGIN
206 
207   IF p_config_buttons_tbl.count>0 THEN
208     FOR i in p_config_buttons_tbl.first ..p_config_buttons_tbl.last LOOP
209      BEGIN
210       SELECT ACTION_ID
211       INTO l_action_id
212       FROM OE_Custom_Actions
213       WHERE action_id=p_config_buttons_tbl(i).action_id
214       AND folder_id=p_folder_id;
215 
216       UPDATE OE_Custom_Actions
217       SET action_name=p_config_buttons_tbl(i).action_name,
218           width=p_config_buttons_tbl(i).width,
219           user_entered_prompt=p_config_buttons_tbl(i).user_entered_prompt,
220           access_key=p_config_buttons_tbl(i).access_key,
221           display_as_button_flag=p_config_buttons_tbl(i).display_as_button
222       WHERE action_id=p_config_buttons_tbl(i).action_id
223       AND folder_id=p_folder_id;
224     EXCEPTION
225     WHEN NO_DATA_FOUND THEN
226 
227       INSERT INTO OE_Custom_Actions
228       (
229       action_name,
230       width,
231       folder_id,
232       user_entered_prompt,
233       access_key,
234       display_as_button_flag,
235       default_prompt,
236       action_id,
237       CREATED_BY,
238       CREATION_DATE,
239       LAST_UPDATE_DATE,
240       LAST_UPDATED_BY,
241       LAST_UPDATE_LOGIN
242 
243        )
244        Values(
245          p_config_buttons_tbl(i).action_name ,
246          p_config_buttons_tbl(i).width,
247          p_folder_id,
248          p_config_buttons_tbl(i).user_entered_prompt,
249          p_config_buttons_tbl(i).access_key,
250          p_config_buttons_tbl(i).display_as_button,
251          p_config_buttons_tbl(i).default_prompt,
252          p_config_buttons_tbl(i).action_id,
253          fnd_profile.value('USER_ID'),
254          sysdate,
255          sysdate,
256          fnd_profile.value('USER_ID'),
257          fnd_profile.value('USER_ID')
258         );
259     END;
260 
261     END LOOP;
262   END IF;
263   COMMIT;
264     Get_Customized_Buttons
265                (
266                p_folder_id=>p_folder_id
267              , x_custom_buttons_tbl=>x_custom_buttons_tbl
268              , x_default_buttons_tbl=>x_default_buttons_tbl
269                 );
270    EXCEPTION
271    WHEN NO_DATA_FOUND THEN
272     Null;
273    WHEN TOO_MANY_ROWS THEN
274     Null;
275    WHEN OTHERS THEN
276     Null;
277 END Store_Custom_Buttons;
278 
279 
280 PROCEDURE INSERT_FOLDER(p_folder_extension_id IN Number,
281                        p_object IN Varchar2,
282                        p_user_id IN Number,
283                        p_folder_id IN Number,
284                        p_pricing_tab IN Varchar2 Default Null,
285                        p_service_tab IN Varchar2 Default Null,
286                        p_others_tab IN Varchar2 Default Null,
287                        p_addresses_tab IN varchar2 Default Null,
288                        p_returns_tab  IN Varchar2 Default Null,
289                        p_shipping_tab IN Varchar2 Default Null,
290                        p_headers_others_tab IN Varchar2 Default Null,
291                        p_options_details IN Varchar2 Default Null,
292                        p_services_details IN Varchar2 Default Null,
293                        p_adjustment_details IN Varchar2 Default Null,
294                        p_related_item_details IN Varchar2 Default Null,
295                        p_pricing_ava_details IN Varchar2 Default Null,
296                        p_default_line_region IN Varchar2 Default Null
297                      )
298 IS PRAGMA AUTONOMOUS_TRANSACTION;
299  l_folder_extension_id Number;
300 BEGIN
301   SELECT FOLDER_EXTENSION_ID
302   INTO l_folder_extension_id
303   FROM oe_folder_extensions
304   WHERE  folder_id=p_folder_id;
305 
306   UPDATE oe_folder_extensions
307   SET DISPLAY_LINE_OTHERS=p_others_tab,
308      DISPLAY_LINE_PRICING=p_pricing_tab,
309      DISPLAY_LINE_SERVICES=p_service_tab,
310      DISPLAY_LINE_ADDRESSES=p_addresses_tab,
311      DISPLAY_LINE_RETURNS=p_returns_tab,
312      DISPLAY_LINE_SHIPPING=p_shipping_tab,
313      DEFAULT_LINE_REGION=p_default_line_region,
314      DISPLAY_ORDER_OTHERS=p_headers_others_tab,
315      DISPLAY_OPTIONS_DETAILS=p_options_details,
316      DISPLAY_SERVICES_DETAILS=p_services_details,
317      DISPLAY_ADJUSTMENT_DETAILS=p_adjustment_details,
318      DISPLAY_RELATED_ITEMS_DETAILS=p_related_item_details,
319      DISPLAY_PRICING_AVA_DETAILS= p_pricing_ava_details
320   WHERE FOLDER_ID=p_folder_id;
321    Commit;
322 
323   EXCEPTION
324   WHEN NO_DATA_FOUND THEN
325   INSERT INTO oe_folder_extensions
326    (FOLDER_EXTENSION_ID,
327     OBJECT,
328     USER_ID,
329     FOLDER_ID,
330     APPLICATION_ID,
331     DISPLAY_LINE_OTHERS ,
332     DISPLAY_LINE_PRICING,
333     DISPLAY_LINE_SERVICES,
334     DISPLAY_LINE_ADDRESSES,
335     DISPLAY_LINE_RETURNS,
336     DISPLAY_LINE_SHIPPING,
337     DISPLAY_ORDER_OTHERS,
338     DISPLAY_OPTIONS_DETAILS,
339     DISPLAY_SERVICES_DETAILS,
340     DISPLAY_ADJUSTMENT_DETAILS,
341     DISPLAY_RELATED_ITEMS_DETAILS,
342     DISPLAY_PRICING_AVA_DETAILS,
343     DEFAULT_LINE_REGION,
344       CREATED_BY,
345       CREATION_DATE,
346       LAST_UPDATE_DATE,
347       LAST_UPDATED_BY,
348       LAST_UPDATE_LOGIN
349    ) VALUES
350   (
351    oe_folder_extensions_s.nextval,
352    p_object,
353    p_user_id,
354    p_folder_id,
355    fnd_profile.value('RESP_APPL_ID'),
356    p_others_tab,
357    p_pricing_tab,
358    p_service_tab,
359    p_addresses_tab,
360    p_returns_tab,
361    p_shipping_tab,
362    p_headers_others_tab,
363    p_options_details,
364    p_services_details,
365    p_adjustment_details,
366    p_related_item_details,
367    p_pricing_ava_details ,
368    p_default_line_region,
369    1,
370    sysdate,
371    sysdate,
372    1,
373    1
374    );
375    Commit;
376 
377 
378    WHEN TOO_MANY_ROWS THEN
379     Null;
380    WHEN OTHERS THEN
381     Null;
382 
383 END INSERT_FOLDER;
384 
385 PROCEDURE FOLDER_ACTIONS_INIT( x_others_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
386                                x_pricing_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
387                                x_addresses_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
388                                x_services_flag  OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
389                                x_shipping_flag  OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
390                                x_returns_flag  OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
391                                x_header_others_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
392                                x_options_details    OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
393                                x_services_details   OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
394                                x_adjustment_details OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
395                                x_related_item_details OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
396                                x_pricing_ava_details  OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
397                                x_default_line_region  OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
398                                x_custom_buttons_tbl OUT NOCOPY /* file.sql.39 change */
399                                Oe_Oe_Folder_Ext.Config_Buttons_Tbl,
400                                x_default_buttons_tbl OUT NOCOPY /* file.sql.39 change */
401                                Oe_Oe_Folder_Ext.Config_Buttons_Tbl,
402                                p_order_folder_object IN Varchar DEFAULT 'OE_ORDERS_TELESALES',
403 			       p_line_folder_object IN Varchar DEFAULT 'OE_LINE_TELESALES'
404 
405                                 ) IS
406    l_folder_header_id Number;
407    l_folder_line_id Number;
408    l_folder_extension_id Number;
409    l_user_id  Number;
410    l_responsibility_id  Number;
411    l_folder_app_id  Number;
412    l_lang VARCHAR2(50);
413 
414     CURSOR C1 IS
415     SELECT FDF.Folder_Id
416     FROM FND_DEFAULT_FOLDERS FDF, FND_FOLDERS FF
417     WHERE
418     FDF.OBJECT=p_order_folder_object
419     AND (FDF.USER_ID=l_user_id OR (FDF.APPLICATION_ID=l_folder_app_id
420     AND FDF.USER_ID=l_responsibility_id))
421     AND FDF.FOLDER_ID=FF.FOLDER_ID
422     AND FF.LANGUAGE=l_lang
423     ORDER BY FDF.USER_ID DESC;
424 
425     CURSOR C2 IS
426     SELECT FDF.Folder_Id
427     FROM FND_DEFAULT_FOLDERS FDF, FND_FOLDERS FF
428     WHERE
429     FDF.OBJECT=p_line_folder_object
430     AND (FDF.USER_ID=l_user_id OR (FDF.APPLICATION_ID=l_folder_app_id
431     AND FDF.USER_ID=l_responsibility_id))
432     AND FDF.FOLDER_ID=FF.FOLDER_ID
433     AND FF.LANGUAGE=l_lang
434     ORDER BY FDF.USER_ID DESC;
435   BEGIN
436    l_user_id:=fnd_profile.value('USER_ID');
437    l_responsibility_id := -1 * to_number(fnd_profile.value('RESP_ID'));
438    l_folder_app_id:=fnd_profile.value('RESP_APPL_ID');
439    l_lang:=userenv('LANG');
440    BEGIN
441     OPEN C1;
442     FETCH C1 INTO l_folder_header_id;
443     CLOSE C1;
444 
445     SELECT FOLDER_EXTENSION_ID,
446           DISPLAY_ORDER_OTHERS
447     INTO   l_folder_extension_id,
448           x_header_others_flag
449     FROM  OE_FOLDER_EXTENSIONS
450     WHERE folder_id=l_folder_header_id ;
451 
452    EXCEPTION
453    WHEN NO_DATA_FOUND THEN
454     Null;
455    WHEN TOO_MANY_ROWS THEN
456     Null;
457    WHEN OTHERS THEN
458     Null;
459    END ;
460 
461    BEGIN
462     OPEN C2;
463     FETCH C2 INTO l_folder_line_id;
464     CLOSE C2;
465 
466     SELECT FOLDER_EXTENSION_ID,
467           DISPLAY_LINE_OTHERS,
468           DISPLAY_LINE_ADDRESSES,
469           DISPLAY_LINE_PRICING,
470           DISPLAY_LINE_SERVICES,
471           DISPLAY_LINE_RETURNS,
472           DISPLAY_LINE_SHIPPING,
473           DISPLAY_OPTIONS_DETAILS,
474           DISPLAY_SERVICES_DETAILS,
475           DISPLAY_ADJUSTMENT_DETAILS,
476           DISPLAY_RELATED_ITEMS_DETAILS,
477           DISPLAY_PRICING_AVA_DETAILS,
478           DEFAULT_LINE_REGION
479     INTO   l_folder_extension_id,
480           x_others_flag,
481           x_addresses_flag,
482           x_pricing_flag,
483           x_services_flag,
484           x_returns_flag,
485           x_shipping_flag,
486           x_options_details ,
487           x_services_details,
488           x_adjustment_details,
489           x_related_item_details,
490           x_pricing_ava_details ,
491           x_default_line_region
492     FROM  OE_FOLDER_EXTENSIONS
493     WHERE folder_id=l_folder_line_id ;
494 
495 
496    EXCEPTION
497    WHEN NO_DATA_FOUND THEN
498     Null;
499    WHEN TOO_MANY_ROWS THEN
500     Null;
501    WHEN OTHERS THEN
502     Null;
503    END ;
504 
505     Get_Customized_Buttons
506                (
507                p_folder_id=>l_folder_header_id
508              , x_custom_buttons_tbl=>x_custom_buttons_tbl
509              , x_default_buttons_tbl=>x_default_buttons_tbl
510                 );
511 
512  EXCEPTION
513  WHEN NO_DATA_FOUND THEN
514    Null;
515  WHEN TOO_MANY_ROWS THEN
516    Null;
517  WHEN OTHERS THEN
518    Null;
519 END FOLDER_Actions_Init;
520 
521 PROCEDURE DELETE_FOLDER(p_folder_extension_id IN Number Default Null ,
522                        p_folder_id IN Number
523                      )
524 IS PRAGMA AUTONOMOUS_TRANSACTION;
525  l_folder_extension_id Number;
526 BEGIN
527 
528    DELETE FROM OE_CUSTOM_ACTIONS
529    WHERE folder_id=p_folder_id;
530 
531    DELETE FROM oe_folder_extensions
532    WHERE  folder_id=p_folder_id;
533 
534    Commit;
535 
536  EXCEPTION
537  WHEN NO_DATA_FOUND THEN
538     Null;
539  WHEN TOO_MANY_ROWS THEN
540     Null;
541  WHEN OTHERS THEN
542     Null;
543 END DELETE_FOLDER;
544 
545 PROCEDURE Defer_Pricing(p_mode In Varchar2)
546 IS
547 
548 BEGIN
549 
550  IF p_mode='Y' THEN
551   OE_GLOBALS.G_DEFER_PRICING:='Y';
552  ELSIF p_mode='N' THEN
553   OE_GLOBALS.G_DEFER_PRICING:='N';
554  END IF;
555 
556 END Defer_Pricing;
557 
558 END Oe_Oe_Folder_Ext;