1 PACKAGE CSM_UTIL_PKG AS
2 /* $Header: csmeutls.pls 120.12 2008/02/21 09:02:12 trajasek ship $ */
3
4 /***
5 Debug levels:
6 0 = No debug
7 1 = Log errors
8 2 = Log errors and functional messages
9 3 = Log errors, functional messages and SQL statements
10 4 = Full Debug
11 ***/
12 g_debug_level_none CONSTANT NUMBER := 0;
13 g_debug_level_error CONSTANT NUMBER := 1;
14 g_debug_level_medium CONSTANT NUMBER := 2;
15 g_debug_level_sql CONSTANT NUMBER := 3;
16 g_debug_level_full CONSTANT NUMBER := 4;
17 --g_flow_type varchar2(20) := 'NORMAL' ;
18
19 FUNCTION Get_Debug_Level
20 RETURN NUMBER;
21
22 --Contains information needed to refresh/sync an acc table with backend
23 TYPE Acc_Refresh_Desc_Rec_Type IS RECORD
24 (
25 --backend table name that provides the primary key
26 BACKEND_TABLE_NAME VARCHAR2(30),
27 --name of the primary key column
28 PRIMARY_KEY_COLUMN VARCHAR2(30),
29 --name of the acc table to be updated
30 ACC_TABLE_NAME VARCHAR2(30),
31 --name of the acc sequence
32 ACC_SEQUENCE_NAME VARCHAR2(50),
33 --TL table name, if involved
34 TL_TABLE_NAME VARCHAR2(30),
35 --name of the publication item on PDA
36 PUBLICATION_ITEM_NAME VARCHAR2(30),
37 --determines which entries user have access to
38 --must select the primary_key_column
39 --e.g. select task_status_id from jtf_task_statuses_b
40 ACCESS_QUERY VARCHAR2(2048)
41 );
42
43 TYPE Acc_Refresh_Desc_Tbl_Type IS VARRAY(20) OF Acc_Refresh_Desc_Rec_Type;
44
45 TYPE Changed_Records_Cur_Type IS REF CURSOR;
46
47 -- Commented out as olite does not support large numbers
48 --Function generate_NumPK_FromStr(strPK varchar2 ) return number ;
49
50 Function GetLocalTime(p_server_time date, p_userid number) return date;
51
52 Function Get_Responsibility_ID(p_userid in number) RETURN NUMBER;
53
54 FUNCTION get_user_name(p_user_id IN number) RETURN varchar2;
55
56 Function MakeDirtyForUser ( p_publication_item in varchar2,
57 p_accessList in number,
58 p_resourceList in number,
59 p_dmlList in char,
60 p_timestamp in date) return boolean;
61
62 Function MakeDirtyForUser ( p_publication_item in varchar2,
63 p_accessList in asg_download.access_list,
64 p_resourceList in asg_download.user_list,
65 p_dmlList in asg_download.dml_list,
66 p_timestamp in date) return boolean;
67
68 Function MakeDirtyForUser ( p_publication_item in varchar2,
69 p_accessList in asg_download.access_list,
70 p_resourceList in asg_download.user_list,
71 p_dmlList in char,
72 p_timestamp in date) return boolean;
73
74 FUNCTION MakeDirtyForUser(p_publication_item in varchar2,
75 p_accessList in number,
76 p_resourceList in number,
77 p_dmlList in char,
78 p_timestamp in date,
79 p_pkvalueslist IN asg_download.pk_list) RETURN BOOLEAN;
80
81 FUNCTION GetAsgDmlConstant( p_dml in char) return char;
82
83 function get_tl_omfs_palm_resources(p_language varchar2)
84 return asg_download.user_list;
85
86 function get_tl_omfs_palm_users(p_language varchar2)
87 return asg_download.user_list;
88
89 FUNCTION is_palm_resource(p_resource_id IN number)
90 RETURN boolean;
91
92 FUNCTION is_palm_user(p_user_id IN number)
93 RETURN boolean;
94
95 function get_all_omfs_palm_res_list return asg_download.user_list;
96
97 function get_all_omfs_palm_user_list return asg_download.user_list;
98
99 FUNCTION get_user_language(p_user_id IN NUMBER)
100 return VARCHAR2;
101
102 PROCEDURE refresh_all_app_level_acc (p_status OUT NOCOPY VARCHAR2,
103 p_message OUT NOCOPY VARCHAR2);
104
105 Function MakeDirtyForResource ( p_publication_item in varchar2,
106 p_accessList in asg_download.access_list,
107 p_resourceList in asg_download.user_list,
108 p_dmlList in char,
109 p_timestamp in date)
110 return boolean;
111 Function MakeDirtyForResource ( p_publication_item in varchar2,
112 p_accessList in number,
113 p_resourceList in number,
114 p_dmlList in char,
115 p_timestamp in date)
116 return boolean;
117
118 FUNCTION MakeDirtyForResource(p_publication_item in varchar2,
119 p_accessList in number,
120 p_resourceList in number,
121 p_dmlList in char,
122 p_timestamp in date,
123 p_pkvalueslist IN asg_download.pk_list)
124 RETURN BOOLEAN;
125
126 -- procedure log (mesg varchar2);
127
128 /*--------------------------------
129 This function returns a translated error message string. If p_api_error is FALSE, it gets
130 message with MESSAGE_NAME = p_message from FND_NEW_MESSAGES and replaces any tokens with
131 the supplied token values. If p_api_error is TRUE, it just returns the api error in the
132 FND_MSG_PUB message stack.
133 --------------------------------*/
134 FUNCTION GET_ERROR_MESSAGE_TEXT(
135 p_api_error IN BOOLEAN DEFAULT FALSE
136 , p_message IN FND_NEW_MESSAGES.MESSAGE_NAME%TYPE DEFAULT NULL
137 , p_token_name1 IN VARCHAR2 DEFAULT NULL
138 , p_token_value1 IN VARCHAR2 DEFAULT NULL
139 , p_token_name2 IN VARCHAR2 DEFAULT NULL
140 , p_token_value2 IN VARCHAR2 DEFAULT NULL
141 , p_token_name3 IN VARCHAR2 DEFAULT NULL
142 , p_token_value3 IN VARCHAR2 DEFAULT NULL
143 )
144 RETURN VARCHAR2;
145
146 /*------------------------------------------------
147 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure when a record was successfully
148 applied and needs to be deleted from the in-queue.
149 ------------------------------------------------*/
150 PROCEDURE DELETE_RECORD
151 (
152 p_user_name IN VARCHAR2,
153 p_tranid IN NUMBER,
154 p_seqno IN NUMBER,
155 p_pk IN VARCHAR2,
156 p_object_name IN VARCHAR2,
157 p_pub_name IN VARCHAR2,
158 p_error_msg OUT NOCOPY VARCHAR2,
159 x_return_status IN OUT NOCOPY VARCHAR2
160 );
161
162 /*------------------------------------------------
163 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
164 when a record failed to be processed and needs to be deferred and rejected from mobile.
165 ------------------------------------------------*/
166 PROCEDURE DEFER_RECORD
167 (
168 p_user_name IN VARCHAR2,
169 p_tranid IN NUMBER,
170 p_seqno IN NUMBER,
171 p_pk IN VARCHAR2,
172 p_object_name IN VARCHAR2,
173 p_pub_name IN VARCHAR2,
174 p_error_msg IN VARCHAR2,
175 x_return_status IN OUT NOCOPY VARCHAR2,
176 p_dml_type IN VARCHAR2
177 );
178
179 /***
180 This procedure is called by APPLY_CLIENT_CHANGES wrapper procedure
181 when the PK of the inserted record is created in the API.
182 We need to remove the local PK from local
183 ***/
184 PROCEDURE REJECT_RECORD
185 (
186 p_user_name IN VARCHAR2,
187 p_tranid IN NUMBER,
188 p_seqno IN NUMBER,
189 p_pk IN VARCHAR2,
190 p_object_name IN VARCHAR2,
191 p_pub_name IN VARCHAR2,
192 p_error_msg IN VARCHAR2,
193 x_return_status IN OUT NOCOPY VARCHAR2
194 );
195
196 FUNCTION GET_TASK_ESC_LEVEL( p_task_id IN NUMBER) RETURN VARCHAR2;
197
198 /* Two functions to check if field service palm is enabled. */
199 FUNCTION IS_FIELD_SERVICE_PALM_ENABLED RETURN BOOLEAN;
200
201 /* logs messages using the JTT framework */
202 PROCEDURE log(message IN VARCHAR2,
203 module IN VARCHAR2 DEFAULT 'CSM',
204 log_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT);
205
206 procedure pvt_log (mesg varchar2);
207 Function GetServerTime(p_client_time date, p_user_name varchar2)
208 return date;
209
210 FUNCTION item_name(p_item_name IN varchar2) RETURN varchar2;
211
212 FUNCTION is_flow_history(p_flowtype IN VARCHAR2) RETURN BOOLEAN;
213
214 /*R12-Function is called in CSF_M_DEBRIEF_EXPENSES_V to make debrief_header_id column nullable*/
215 FUNCTION get_debrief_header_id(p_debrief_header_id in CSF_DEBRIEF_HEADERS.DEBRIEF_HEADER_ID%TYPE)
216 RETURN NUMBER;
217
218 /*R12-Function to return nullable number type for not null numbers*/
219 FUNCTION get_number(p_number IN NUMBER) RETURN NUMBER;
220
221 /*R12-Function to return nullable varchar type for not null varchar*/
222 FUNCTION get_varchar(p_varchar IN VARCHAR2) RETURN VARCHAR2;
223
224 /*R12-Function to return nullable date type for not null date*/
225 FUNCTION get_date(p_date IN DATE) RETURN DATE;
226
227 /*R12-Function to get owner's full/group name*/
228 FUNCTION get_owner_name(p_owner_type_code IN VARCHAR2,p_owner_id IN NUMBER,p_language IN VARCHAR2)
229 RETURN VARCHAR2;
230
231 FUNCTION get_wf_attrText(p_notification_id IN NUMBER,p_attribute IN VARCHAR2)
232 RETURN VARCHAR2;
233
234 /*------------------------------------------------
235 This Function is used to find the difference between dates and convert it
236 to the required UOM given
237 ------------------------------------------------*/
238 FUNCTION Get_Datediff_For_Req_UOM
239 ( p_start_date IN DATE,
240 p_end_date IN DATE,
241 p_class IN MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE,
242 p_to_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE,
243 p_min_uom IN CSM_UNIT_OF_MEASURE_TL_ACC.UOM_CODE%TYPE
244 ) RETURN NUMBER;
245
246 --12.1
247 FUNCTION is_mfs_group(p_group_id NUMBER) RETURN BOOLEAN;
248 --12.1
249 FUNCTION get_group_owner(p_group_id NUMBER) RETURN NUMBER;
250
251 --12.1
252 FUNCTION from_same_group(p_member1_resource_id NUMBER,p_member2_resource_id NUMBER) RETURN BOOLEAN;
253 --12.1 gets the owner id of the given user
254 FUNCTION get_owner(p_user_id NUMBER) RETURN NUMBER;
255
256 --12.1 gets the Group Name for a given Group
257 FUNCTION get_group_name(p_group_id NUMBER, p_language VARCHAR2) RETURN VARCHAR2;
258
259 END CSM_UTIL_PKG; -- Package spec