1 PACKAGE BODY fem_undo_ui_pkg AS
2 /* $Header: FEMUNDOUIB.pls 120.0 2006/06/30 06:07:50 asadadek noship $ */
3
4 FUNCTION get_user_name(p_user_id NUMBER) RETURN VARCHAR2 AS
5 l_user_name VARCHAR2(100);
6 BEGIN
7
8 SELECT user_name INTO l_user_name
9 FROM fnd_user
10 WHERE user_id = p_user_id;
11
12 RETURN l_user_name;
13
14 EXCEPTION
15 WHEN no_data_found THEN
16 RETURN null;
17
18 END;
19
20
21 -- This function gets the undo status for a request and object combination.
22 -- This function simply returns the execution_status_code of the p_object_id
23 -- for p_request_id by ordering the status codes in a logical order such that
24 -- the UI will reflect the correct undo status.
25 -- The possible undo_status_codes are 'SUCCESS','RUNNING','ERROR_RERUN','ERROR_CANCELLED'.
26
27 FUNCTION get_undo_status(p_object_id NUMBER,p_request_id NUMBER) RETURN VARCHAR2 AS
28 l_undo_status VARCHAR2(100);
29 BEGIN
30
31 SELECT exec_status_code INTO l_undo_status
32 FROM (SELECT exec_status_code
33 FROM (SELECT exec_status_code
34 FROM fem_ud_list_candidates
35 WHERE object_id = p_object_id
36 AND request_id = p_request_id
37 UNION
38 SELECT exec_status_code
39 FROM fem_ud_list_dependents
40 WHERE dependent_object_id = p_object_id
41 AND dependent_request_id = p_request_id )
42 ORDER by exec_status_code desc)
43 WHERE rownum = 1;
44
45 RETURN l_undo_status;
46
47 EXCEPTION
48
49 WHEN no_data_found THEN
50 RETURN null;
51
52 END;
53
54
55
56 FUNCTION is_ledger_table(p_table_name varchar2) RETURN VARCHAR2 AS
57 l_ledger_table VARCHAR2(1) := 'N';
58 l_count NUMBER;
59 BEGIN
60
61
62 SELECT count(*) INTO l_count FROM
63 FEM_TABLE_CLASS_ASSIGNMT class
64 WHERE class.table_name= p_table_name
65 AND class.table_classification_code IN ('ABM_LEDGER','PFT_LEDGER');
66
67 IF l_count >= 1 THEN
68 L_LEDGER_TABLE := 'Y';
69 END IF;
70
71 RETURN l_ledger_table;
72
73 EXCEPTION
74 WHEN no_data_found THEN
75 RETURN l_ledger_table;
76
77 END;
78
79
80 -- This function checks if a particular Undo candidate and its
81 -- dependents are not undoable. Checks the validation_status_code column
82 -- in the preview tables , that is populated by the Undo engine.
83 -- Returns 'Y' if the candidate and all its dependents are undoable.
84 -- Returns 'N' otherwise.
85
86 FUNCTION is_undo_valid(p_ud_session_id NUMBER,
87 p_object_id NUMBER,
88 p_request_id NUMBER) RETURN VARCHAR2 AS
89 l_status_code VARCHAR2(40);
90 l_count NUMBER;
91 BEGIN
92
93 -- First find if the candidate is valid for Undo.
94 SELECT validation_status_code INTO l_status_code
95 FROM fem_ud_prview_candidates
96 WHERE ud_session_id = p_ud_session_id
97 AND object_id = p_object_id
98 AND request_id = p_request_id;
99
100 IF l_status_code <> 'FEM_UD_VALID_TXT' THEN
101 RETURN 'N';
102 END IF ;
103
104 --Now find if any of the dependents is invalid for Undo.
105 SELECT count(*) into l_count
106 FROM fem_ud_prview_dependents
107 WHERE validation_status_code <> 'FEM_UD_VALID_TXT'
108 AND request_id = p_request_id
109 AND object_id = p_object_id
110 AND ud_session_id = p_ud_session_id;
111
112 IF l_count > 0 THEN
113 RETURN 'N';
114 END IF;
115
116 RETURN 'Y';
117
118 EXCEPTION
119 WHEN no_data_found THEN
120 RETURN 'N';
121
122 END;
123
124 END fem_undo_ui_pkg;
[Home] [Help]