[Home] [Help]
5: --
6: g_package varchar2(33) := 'hr_transaction_ss.';
7: g_update_object_version varchar2(30) := 'update_object_version';
8:
9: g_debug boolean := hr_utility.debug_enabled;
10:
11: --- -------------------------------------------------------------------------
12: --- -------------setRespondedUserCtx-----------------------------------
13: --- -------------------------------------------------------------------------
30: cursor user_role_name is
31: select name from wf_roles where UPPER(EMAIL_ADDRESS) = UPPER(substr (contextUser, 7)) and ORIG_SYSTEM = 'PER' and STATUS = 'ACTIVE';
32: begin
33: if g_debug then
34: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
35: hr_utility.set_location('p_item_type:'||p_item_type,2);
36: hr_utility.set_location('p_item_key:'||p_item_key,3);
37: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
38: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
31: select name from wf_roles where UPPER(EMAIL_ADDRESS) = UPPER(substr (contextUser, 7)) and ORIG_SYSTEM = 'PER' and STATUS = 'ACTIVE';
32: begin
33: if g_debug then
34: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
35: hr_utility.set_location('p_item_type:'||p_item_type,2);
36: hr_utility.set_location('p_item_key:'||p_item_key,3);
37: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
38: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
39: end if;
32: begin
33: if g_debug then
34: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
35: hr_utility.set_location('p_item_type:'||p_item_type,2);
36: hr_utility.set_location('p_item_key:'||p_item_key,3);
37: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
38: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
39: end if;
40:
33: if g_debug then
34: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
35: hr_utility.set_location('p_item_type:'||p_item_type,2);
36: hr_utility.set_location('p_item_key:'||p_item_key,3);
37: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
38: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
39: end if;
40:
41:
34: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
35: hr_utility.set_location('p_item_type:'||p_item_type,2);
36: hr_utility.set_location('p_item_key:'||p_item_key,3);
37: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,4);
38: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,5);
39: end if;
40:
41:
42: -- check if we have the context set by the Approver Notification
45: contextUser := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CONTEXT_USER_ATTR',true);
46: contextProxyUser := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CONTEXT_PROXY_ATTR',true);
47:
48: if g_debug then
49: hr_utility.set_location('contextUser value :'|| contextUser,6);
50: hr_utility.set_location('contextProxyUser value :'|| contextProxyUser,6);
51: end if;
52: /* possible values
53: Case 1: Null => Call back function does not exists or context attribute
46: contextProxyUser := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CONTEXT_PROXY_ATTR',true);
47:
48: if g_debug then
49: hr_utility.set_location('contextUser value :'|| contextUser,6);
50: hr_utility.set_location('contextProxyUser value :'|| contextProxyUser,6);
51: end if;
52: /* possible values
53: Case 1: Null => Call back function does not exists or context attribute
54: never set
76: if(nvl(contextProxyUser,contextUser)<>fnd_global.user_name) then
77:
78: -- check if the role is a valid FND user
79: if g_debug then
80: hr_utility.set_location('contextUser and contextProxyUser does not match fnd_global.user_name',7);
81: end if;
82: open username;
83: fetch username into userId;
84: if username%found then
82: open username;
83: fetch username into userId;
84: if username%found then
85: if g_debug then
86: hr_utility.set_location('found a valid fnd user corresponding to context user',8);
87: hr_utility.set_location('calling fnd_global.apps_initialize for userId:'||userId,9);
88: end if;
89: -- call the apps intialization
90: fnd_global.apps_initialize(userId,null,null,null,null);
83: fetch username into userId;
84: if username%found then
85: if g_debug then
86: hr_utility.set_location('found a valid fnd user corresponding to context user',8);
87: hr_utility.set_location('calling fnd_global.apps_initialize for userId:'||userId,9);
88: end if;
89: -- call the apps intialization
90: fnd_global.apps_initialize(userId,null,null,null,null);
91: if g_debug then
88: end if;
89: -- call the apps intialization
90: fnd_global.apps_initialize(userId,null,null,null,null);
91: if g_debug then
92: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,10);
93: hr_utility.set_location('fnd_global.user_name after reset:'||fnd_global.user_name,11);
94: hr_utility.set_location('fnd_global.user_id after reset:'||fnd_global.user_id,12);
95: end if;
96: end if;
89: -- call the apps intialization
90: fnd_global.apps_initialize(userId,null,null,null,null);
91: if g_debug then
92: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,10);
93: hr_utility.set_location('fnd_global.user_name after reset:'||fnd_global.user_name,11);
94: hr_utility.set_location('fnd_global.user_id after reset:'||fnd_global.user_id,12);
95: end if;
96: end if;
97: close username;
90: fnd_global.apps_initialize(userId,null,null,null,null);
91: if g_debug then
92: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,10);
93: hr_utility.set_location('fnd_global.user_name after reset:'||fnd_global.user_name,11);
94: hr_utility.set_location('fnd_global.user_id after reset:'||fnd_global.user_id,12);
95: end if;
96: end if;
97: close username;
98: end if;
108: null;
109: end;
110: if (l_user_role_count > 1) OR ((l_user_role_count = 0))then
111: if g_debug and l_user_role_count > 1 then
112: hr_utility.set_location('more than one PER Role is attched with email: ' || substr (contextUser, 7),13);
113: hr_utility.set_location('So this is setup issue.',13);
114: end if;
115: if g_debug and l_user_role_count = 0 then
116: hr_utility.set_location('no valid PER Role attached with email: ' || substr (contextUser, 7),13);
109: end;
110: if (l_user_role_count > 1) OR ((l_user_role_count = 0))then
111: if g_debug and l_user_role_count > 1 then
112: hr_utility.set_location('more than one PER Role is attched with email: ' || substr (contextUser, 7),13);
113: hr_utility.set_location('So this is setup issue.',13);
114: end if;
115: if g_debug and l_user_role_count = 0 then
116: hr_utility.set_location('no valid PER Role attached with email: ' || substr (contextUser, 7),13);
117: hr_utility.set_location('So this is setup issue.',13);
112: hr_utility.set_location('more than one PER Role is attched with email: ' || substr (contextUser, 7),13);
113: hr_utility.set_location('So this is setup issue.',13);
114: end if;
115: if g_debug and l_user_role_count = 0 then
116: hr_utility.set_location('no valid PER Role attached with email: ' || substr (contextUser, 7),13);
117: hr_utility.set_location('So this is setup issue.',13);
118: end if;
119: return;
120: end if;
113: hr_utility.set_location('So this is setup issue.',13);
114: end if;
115: if g_debug and l_user_role_count = 0 then
116: hr_utility.set_location('no valid PER Role attached with email: ' || substr (contextUser, 7),13);
117: hr_utility.set_location('So this is setup issue.',13);
118: end if;
119: return;
120: end if;
121: -- we have valid wf role
121: -- we have valid wf role
122: -- check if the role is same as fnd_global.user_name
123: open user_role_name;
124: fetch user_role_name into c_user_name;
125: hr_utility.set_location('c_user_name ' || c_user_name,13);
126: if user_role_name%found then
127:
128: if(c_user_name<>fnd_global.user_name) then
129: -- check if the role is a valid FND user
127:
128: if(c_user_name<>fnd_global.user_name) then
129: -- check if the role is a valid FND user
130: if g_debug then
131: hr_utility.set_location('contextUser and contextProxyUser does not match fnd_global.user_name for email',13);
132: end if;
133: contextUser := c_user_name;
134: open username;
135: fetch username into userId;
134: open username;
135: fetch username into userId;
136: if username%found then
137: if g_debug then
138: hr_utility.set_location('found a valid fnd user corresponding to context user',14);
139: hr_utility.set_location('calling fnd_global.apps_initialize for userId:'||userId,15);
140: end if;
141: -- call the apps intialization
142: fnd_global.apps_initialize(userId,null,null,null,null);
135: fetch username into userId;
136: if username%found then
137: if g_debug then
138: hr_utility.set_location('found a valid fnd user corresponding to context user',14);
139: hr_utility.set_location('calling fnd_global.apps_initialize for userId:'||userId,15);
140: end if;
141: -- call the apps intialization
142: fnd_global.apps_initialize(userId,null,null,null,null);
143: if g_debug then
140: end if;
141: -- call the apps intialization
142: fnd_global.apps_initialize(userId,null,null,null,null);
143: if g_debug then
144: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,16);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
141: -- call the apps intialization
142: fnd_global.apps_initialize(userId,null,null,null,null);
143: if g_debug then
144: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,16);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
142: fnd_global.apps_initialize(userId,null,null,null,null);
143: if g_debug then
144: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,16);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
143: if g_debug then
144: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,16);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
144: hr_utility.set_location('returned from calling fnd_global.apps_initialize for userId:'||userId,16);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
145: hr_utility.set_location('fnd_global.user_name:'||fnd_global.user_name,17);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
153: hr_utility.set_location('fnd_global.per_security_profile_id:'||fnd_global.per_security_profile_id,25);
146: hr_utility.set_location('fnd_global.user_id:'||fnd_global.user_id,18);
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
153: hr_utility.set_location('fnd_global.per_security_profile_id:'||fnd_global.per_security_profile_id,25);
154: end if;
147: hr_utility.set_location('fnd_profile.value(PER_SECURITY_PROFILE_ID):'||fnd_profile.value('PER_SECURITY_PROFILE_ID'),19);
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
153: hr_utility.set_location('fnd_global.per_security_profile_id:'||fnd_global.per_security_profile_id,25);
154: end if;
155: end if; --end of if username%found then
148: hr_utility.set_location('fnd_profile.value(PER_BUSINESS_GROUP_ID):'||fnd_profile.value('PER_BUSINESS_GROUP_ID'),20);
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
153: hr_utility.set_location('fnd_global.per_security_profile_id:'||fnd_global.per_security_profile_id,25);
154: end if;
155: end if; --end of if username%found then
156: close username;
149: hr_utility.set_location('fnd_global.resp_id :'||fnd_global.resp_id,21);
150: hr_utility.set_location('fnd_global.resp_appl_id :'||fnd_global.resp_appl_id,22);
151: hr_utility.set_location('fnd_global.per_business_group_id :'||fnd_global.per_business_group_id,23);
152: hr_utility.set_location('fnd_global.security_group_id:'||fnd_global.security_group_id,24);
153: hr_utility.set_location('fnd_global.per_security_profile_id:'||fnd_global.per_security_profile_id,25);
154: end if;
155: end if; --end of if username%found then
156: close username;
157: end if; -- end of if(c_user_name<>fnd_global.user_name) then
159: close user_role_name;
160: end if; --end of if(contextUser is not null and substr (contextUser, 1, 6) = 'email:') then
161:
162: if (g_debug ) then
163: hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 26);
164: end if;
165:
166: exception
167: when others then
168: if ( username%isopen ) then
169: close username;
170: end if;
171: if g_debug then
172: hr_utility.set_location('Error in setRespondedUserCtx SQLERRM' ||' '||to_char(SQLCODE),30);
173: end if;
174: raise;
175: end setRespondedUserCtx;
176:
204: l_proc constant varchar2(100) := g_package || ' reset_ame_approval_status';
205: --ns end
206:
207: BEGIN
208: hr_utility.set_location('Entering: '|| l_proc,5);
209: g_debug := hr_utility.debug_enabled;
210:
211: IF g_debug THEN
212: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
205: --ns end
206:
207: BEGIN
208: hr_utility.set_location('Entering: '|| l_proc,5);
209: g_debug := hr_utility.debug_enabled;
210:
211: IF g_debug THEN
212: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
213: END IF;
208: hr_utility.set_location('Entering: '|| l_proc,5);
209: g_debug := hr_utility.debug_enabled;
210:
211: IF g_debug THEN
212: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
213: END IF;
214:
215:
216: -- get AME related WF attribute values
218: (p_item_type => p_item_type
219: ,p_item_key => p_item_key
220: ,p_name => 'HR_AME_APP_ID_ATTR') then
221: -- get the attribute value
222: hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
223: c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
224: itemkey => p_item_key,
225: aname => 'HR_AME_APP_ID_ATTR');
226:
224: itemkey => p_item_key,
225: aname => 'HR_AME_APP_ID_ATTR');
226:
227: else
228: hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
229: c_application_id := null;
230: end if;
231:
232: c_application_id := nvl(c_application_id,800);
235: (p_item_type => p_item_type
236: ,p_item_key => p_item_key
237: ,p_name => 'TRANSACTION_ID') then
238: -- get the attribute value
239: hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
240: c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
241: itemkey => p_item_key,
242: aname => 'TRANSACTION_ID');
243: else
240: c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
241: itemkey => p_item_key,
242: aname => 'TRANSACTION_ID');
243: else
244: hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
245: c_transaction_id := null;
246: end if;
247:
248:
250: (p_item_type => p_item_type
251: ,p_item_key => p_item_key
252: ,p_name => 'HR_AME_TRAN_TYPE_ATTR') then
253: -- get the attribute value
254: hr_utility.trace('In : (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
255: c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
256: itemkey => p_item_key,
257: aname => 'HR_AME_TRAN_TYPE_ATTR');
258: else
255: c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
256: itemkey => p_item_key,
257: aname => 'HR_AME_TRAN_TYPE_ATTR');
258: else
259: hr_utility.trace('In : else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
260: c_transaction_type := null;
261:
262: end if;
263:
268: itemkey => p_item_key,
269: aname => 'HR_AME_TRAN_TYPE_ATTR');
270:
271: if(c_transaction_type is not null) then
272: hr_utility.trace('In : (if(c_transaction_type is not null) ) '|| l_proc);
273: IF g_debug THEN
274: hr_utility.trace('calling ame_api.getAllApprovers ');
275: END IF;
276:
270:
271: if(c_transaction_type is not null) then
272: hr_utility.trace('In : (if(c_transaction_type is not null) ) '|| l_proc);
273: IF g_debug THEN
274: hr_utility.trace('calling ame_api.getAllApprovers ');
275: END IF;
276:
277: ame_api.getAllApprovers(applicationIdIn =>c_application_id,
278: transactionIdIn=>c_transaction_id,
288: itemkey => p_item_key,
289: aname => 'RETURN_TO_USERNAME');
290: Exception
291: WHEN others then
292: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
293: null; -- Bug 3050544
294: End;
295: IF ( c_return_user IS NULL OR c_creator_user = c_return_user ) THEN
296: c_rfc_initiator := 'Y';
302:
303: for i in 1..c_all_approvers.count loop
304:
305: IF g_debug THEN
306: hr_utility.trace('calling ame_api..updateApprovalStatus2 ');
307: END IF;
308:
309: -- call AME update approval status as null
310: --ns comment start
333: end if;
334:
335:
336: IF g_debug THEN
337: hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 35);
338: END IF;
339:
340: hr_utility.set_location('Leaving: '|| l_proc,40);
341: EXCEPTION
336: IF g_debug THEN
337: hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 35);
338: END IF;
339:
340: hr_utility.set_location('Leaving: '|| l_proc,40);
341: EXCEPTION
342: when others then
343: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
344: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
339:
340: hr_utility.set_location('Leaving: '|| l_proc,40);
341: EXCEPTION
342: when others then
343: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
344: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
345: Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
346: raise;
347:
340: hr_utility.set_location('Leaving: '|| l_proc,40);
341: EXCEPTION
342: when others then
343: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
344: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
345: Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
346: raise;
347:
348: END reset_ame_approval_status;
360: c_proc varchar2(30) default 'reset_approval_status';
361: l_approvalProcessVersion varchar2(10);
362: l_proc constant varchar2(100) := g_package || ' reset_approval_status';
363: BEGIN
364: hr_utility.set_location('Entering: '|| l_proc,5);
365: g_debug := hr_utility.debug_enabled;
366:
367: IF g_debug THEN
368: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
361: l_approvalProcessVersion varchar2(10);
362: l_proc constant varchar2(100) := g_package || ' reset_approval_status';
363: BEGIN
364: hr_utility.set_location('Entering: '|| l_proc,5);
365: g_debug := hr_utility.debug_enabled;
366:
367: IF g_debug THEN
368: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
369: END IF;
364: hr_utility.set_location('Entering: '|| l_proc,5);
365: g_debug := hr_utility.debug_enabled;
366:
367: IF g_debug THEN
368: hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
369: END IF;
370:
371: -- need to reset all the Workflow item attributes used in the approval process
372:
449: itemkey => p_item_Key,
450: aname => 'HR_APPROVAL_PRC_VERSION');
451: Exception
452: when others then
453: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
454: null;
455:
456: End;
457:
459: wf_engine.GetItemAttrText(
460: itemtype => p_item_Type, itemkey => p_item_Key,
461: aname => 'HR_AME_TRAN_TYPE_ATTR') IS NOT NULL) THEN
462: -- CURRENT_APPROVER_INDEX
463: hr_utility.trace('In ( IF ( NVL(l_approvalProcessversion,X) <> V5 OR
464: wf_engine.GetItemAttrText(..,..,..,)IS NOT NULL '|| l_proc);
465: if hr_workflow_utility.item_attribute_exists
466: (p_item_type => p_item_type
467: ,p_item_key => p_item_key
500:
501:
502:
503: IF g_debug THEN
504: hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 20);
505: END IF;
506:
507: hr_utility.set_location('Leaving: '|| l_proc,25);
508: EXCEPTION
503: IF g_debug THEN
504: hr_utility.set_location('Leaving:'||g_package||'.'|| c_proc, 20);
505: END IF;
506:
507: hr_utility.set_location('Leaving: '|| l_proc,25);
508: EXCEPTION
509: when others then
510: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
511: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
506:
507: hr_utility.set_location('Leaving: '|| l_proc,25);
508: EXCEPTION
509: when others then
510: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
511: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
512: Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
513: raise;
514:
507: hr_utility.set_location('Leaving: '|| l_proc,25);
508: EXCEPTION
509: when others then
510: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
511: hr_utility.trace(' exception in '||c_proc||' : ' || sqlerrm);
512: Wf_Core.Context(g_package, c_proc, p_item_type, p_item_key);
513: raise;
514:
515: END reset_approval_status;
531: l_item_key hr_api_transaction_steps.item_key%type;
532: l_effective_date varchar2(100);
533: l_proc constant varchar2(100) := g_package || ' get_wf_effective_date';
534: BEGIN
535: hr_utility.set_location('Entering: '|| l_proc,5);
536: open csr_item_type_key;
537: fetch csr_item_type_key into l_item_type,l_item_key;
538: close csr_item_type_key;
539: if l_item_type is not null and l_item_key is not null then
543: ,aname => 'P_EFFECTIVE_DATE');
544: else
545: l_effective_date := null;
546: end if;
547: hr_utility.set_location('Leaving: '|| l_proc,10);
548: return l_effective_date;
549:
550: EXCEPTION
551: when others then
549:
550: EXCEPTION
551: when others then
552:
553: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
554: return null;
555:
556: END get_wf_effective_date;
557:
569: ,p_status in varchar2
570: ,p_transaction_id in number default null) is
571: l_proc constant varchar2(100) := g_package || ' set_save_for_later_status';
572: begin
573: hr_utility.set_location('Entering: '|| l_proc,5);
574: If (p_item_type is not null and
575: p_item_key is not null )
576: then
577: wf_engine.SetItemAttrText(itemtype => p_item_type,
585: hr_transaction_api.update_transaction(p_transaction_id => p_transaction_id,
586: p_status => p_status );
587: end If;
588:
589: hr_utility.set_location('Leaving: '|| l_proc,10);
590: end set_save_for_later_status;
591:
592: procedure set_initial_save_for_later
593: (itemtype in varchar2,
596: funmode in varchar2,
597: result out nocopy varchar2 ) is
598: l_proc constant varchar2(100) := g_package || ' set_initial_save_for_later';
599: begin
600: hr_utility.set_location('Entering: '|| l_proc,5);
601: if ( funmode = 'RUN' ) then
602: set_save_for_later_status
603: (p_item_type => itemtype,
604: p_item_key => itemkey,
609: null;
610: --
611: --
612: end if;
613: hr_utility.set_location('Leaving: '|| l_proc,10);
614:
615: end set_initial_save_for_later;
616:
617: procedure set_delete_save_for_later
622: result out nocopy varchar2 ) is
623: ln_transaction_id hr_api_transactions.transaction_id%TYPE;
624: l_proc constant varchar2(100) := g_package || ' set_delete_save_for_later';
625: begin
626: hr_utility.set_location('Entering: '|| l_proc,5);
627: if ( funmode = 'RUN' ) then
628: ln_transaction_id := get_transaction_id(itemtype, itemkey);
629: set_save_for_later_status
630: (p_item_type => itemtype,
637: null;
638: --
639: --
640: end if;
641: hr_utility.set_location('Leaving: '|| l_proc,10);
642: end set_delete_save_for_later;
643:
644:
645: procedure set_save_for_later
649: funmode in varchar2,
650: result out nocopy varchar2 ) is
651: l_proc constant varchar2(100) := g_package || ' set_save_for_later';
652: begin
653: hr_utility.set_location('Entering: '|| l_proc,5);
654: if ( funmode = 'RUN' ) then
655: set_save_for_later_status
656: (p_item_type => itemtype,
657: p_item_key => itemkey,
662: null;
663: --
664: --
665: end if;
666: hr_utility.set_location('Leaving: '|| l_proc,10);
667: end set_save_for_later;
668:
669:
670: procedure set_return_for_correction
677: l_result varchar2(2000); --ns
678: l_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
679: l_proc constant varchar2(100) := g_package || ' set_return_for_correction';
680: begin
681: hr_utility.set_location('Entering: '|| l_proc,5);
682: if ( funmode = 'RUN' ) then
683: hr_utility.trace('In (: if ( funmode = RUN )'|| l_proc);
684: --ns commented
685: --ns this procedure is replace by the one below to set the return for correction status appropriately.
679: l_proc constant varchar2(100) := g_package || ' set_return_for_correction';
680: begin
681: hr_utility.set_location('Entering: '|| l_proc,5);
682: if ( funmode = 'RUN' ) then
683: hr_utility.trace('In (: if ( funmode = RUN )'|| l_proc);
684: --ns commented
685: --ns this procedure is replace by the one below to set the return for correction status appropriately.
686: /*
687: ln_transaction_id := get_transaction_id(itemtype, itemkey);
729: end if;
730:
731: exception
732: when others then
733: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
734: raise;
735: end;
736: result := 'COMPLETE:SUCCESS';
737: elsif ( funmode = 'CANCEL' ) then
734: raise;
735: end;
736: result := 'COMPLETE:SUCCESS';
737: elsif ( funmode = 'CANCEL' ) then
738: hr_utility.trace('In (: if ( funmode = CANCEL )'|| l_proc);
739: --
740: null;
741: --
742: --
740: null;
741: --
742: --
743: end if;
744: hr_utility.set_location('Leaving: '|| l_proc,20);
745:
746: end set_return_for_correction;
747:
748:
754: result out nocopy varchar2 ) is
755: ln_transaction_id hr_api_transactions.transaction_id%TYPE;
756: l_proc constant varchar2(100) := g_package || ' set_submit_for_approval';
757: begin
758: hr_utility.set_location('Entering: '|| l_proc,5);
759:
760: if ( funmode = 'RUN' ) then
761: ln_transaction_id := get_transaction_id(itemtype, itemkey);
762: set_save_for_later_status
770: null;
771: --
772: --
773: end if;
774: hr_utility.set_location('Leaving: '|| l_proc,10);
775: end set_submit_for_approval;
776:
777:
778: -- ----------------------------------------------------------------------------
784: l_proc constant varchar2(100) := g_package || ' get_transaction_id';
785: --
786: l_transaction_id number;
787: begin
788: hr_utility.set_location('Entering: '|| l_proc,5);
789: hr_utility.set_location('Leaving: '|| l_proc,10);
790: return(wf_engine.getitemattrnumber
791: (itemtype => p_item_type
792: ,itemkey => p_item_key
785: --
786: l_transaction_id number;
787: begin
788: hr_utility.set_location('Entering: '|| l_proc,5);
789: hr_utility.set_location('Leaving: '|| l_proc,10);
790: return(wf_engine.getitemattrnumber
791: (itemtype => p_item_type
792: ,itemkey => p_item_key
793: ,aname => 'TRANSACTION_ID'));
792: ,itemkey => p_item_key
793: ,aname => 'TRANSACTION_ID'));
794: exception
795: when others then
796: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
797: -- the TRANSACTION_ID doesn't exist as an item so return null
798: select transaction_id into l_transaction_id from hr_api_transactions where item_type = p_item_type and item_key = p_item_key;
799: hr_utility.set_location('l_transaction_id: '|| l_transaction_id,10);
800: return(l_transaction_id);
795: when others then
796: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
797: -- the TRANSACTION_ID doesn't exist as an item so return null
798: select transaction_id into l_transaction_id from hr_api_transactions where item_type = p_item_type and item_key = p_item_key;
799: hr_utility.set_location('l_transaction_id: '|| l_transaction_id,10);
800: return(l_transaction_id);
801:
802: end get_transaction_id;
803:
834:
835: l_function_name fnd_form_functions_vl.function_name%TYPE default null;
836: l_proc constant varchar2(100) := g_package || ' populate_null_values';
837: BEGIN
838: hr_utility.set_location('Entering: '|| l_proc,5);
839: If p_function_id is null then
840: hr_utility.trace('In(If p_function_id is null)'|| l_proc);
841: If p_item_type is not null and p_item_key is not null then
842: hr_utility.trace('In(p_item_type is not null and p_item_key is not null)'|| l_proc);
836: l_proc constant varchar2(100) := g_package || ' populate_null_values';
837: BEGIN
838: hr_utility.set_location('Entering: '|| l_proc,5);
839: If p_function_id is null then
840: hr_utility.trace('In(If p_function_id is null)'|| l_proc);
841: If p_item_type is not null and p_item_key is not null then
842: hr_utility.trace('In(p_item_type is not null and p_item_key is not null)'|| l_proc);
843: OPEN get_function_info(p_item_type => p_item_type,
844: p_item_key => p_item_key);
838: hr_utility.set_location('Entering: '|| l_proc,5);
839: If p_function_id is null then
840: hr_utility.trace('In(If p_function_id is null)'|| l_proc);
841: If p_item_type is not null and p_item_key is not null then
842: hr_utility.trace('In(p_item_type is not null and p_item_key is not null)'|| l_proc);
843: OPEN get_function_info(p_item_type => p_item_type,
844: p_item_key => p_item_key);
845:
846: FETCH get_function_info into l_function_id, l_function_name;
850: END if;*/
851: close get_function_info;
852: end if;
853: else
854: hr_utility.trace('In else of (If p_function_id is null)'|| l_proc);
855: l_function_id := p_function_id;
856: end if;
857:
858: If p_selected_person_id is null then
921: else
922: lv_transaction_type := nvl(p_transaction_type,'NWF');
923: end if;
924:
925: hr_utility.set_location('Leaving: '|| l_proc,20);
926: END populate_null_values;
927:
928: -- ----------------------------------------------------------------------------
929: -- |----------------------------< start_transaction >-------------------------|
967: lv_transaction_type hr_api_transactions.transaction_type%TYPE;
968: l_proc constant varchar2(100) := g_package || ' start_transaction';
969: --
970: begin
971: hr_utility.set_location('Entering: '|| l_proc,5);
972: g_debug := hr_utility.debug_enabled;
973:
974: IF g_debug THEN
975: hr_utility.trace('In (IF g_debug )'|| l_proc);
968: l_proc constant varchar2(100) := g_package || ' start_transaction';
969: --
970: begin
971: hr_utility.set_location('Entering: '|| l_proc,5);
972: g_debug := hr_utility.debug_enabled;
973:
974: IF g_debug THEN
975: hr_utility.trace('In (IF g_debug )'|| l_proc);
976: END IF;
971: hr_utility.set_location('Entering: '|| l_proc,5);
972: g_debug := hr_utility.debug_enabled;
973:
974: IF g_debug THEN
975: hr_utility.trace('In (IF g_debug )'|| l_proc);
976: END IF;
977:
978: populate_null_values
979: (itemtype
998:
999:
1000:
1001: if funmode = 'RUN' then
1002: hr_utility.trace('In (if funmode = RUN) '|| l_proc);
1003: savepoint start_transaction;
1004: -- check to see if the TRANSACTION_ID attribute has been created
1005: if hr_workflow_utility.item_attribute_exists
1006: (p_item_type => itemtype
1005: if hr_workflow_utility.item_attribute_exists
1006: (p_item_type => itemtype
1007: ,p_item_key => itemkey
1008: ,p_name => 'TRANSACTION_ID') then
1009: hr_utility.trace('In (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
1010: -- the TRANSACTION_ID exists so ensure that it is null
1011: if get_transaction_id
1012: (p_item_type => itemtype
1013: ,p_item_key => itemkey) is not null then
1011: if get_transaction_id
1012: (p_item_type => itemtype
1013: ,p_item_key => itemkey) is not null then
1014: -- a current transaction is in progress we cannot overwrite it
1015: -- hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
1016: -- hr_utility.raise_error;
1017: result := 'SUCCESS';
1018: hr_utility.set_location('Leaving: '|| l_proc,25);
1019: return;
1012: (p_item_type => itemtype
1013: ,p_item_key => itemkey) is not null then
1014: -- a current transaction is in progress we cannot overwrite it
1015: -- hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
1016: -- hr_utility.raise_error;
1017: result := 'SUCCESS';
1018: hr_utility.set_location('Leaving: '|| l_proc,25);
1019: return;
1020: end if;
1014: -- a current transaction is in progress we cannot overwrite it
1015: -- hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
1016: -- hr_utility.raise_error;
1017: result := 'SUCCESS';
1018: hr_utility.set_location('Leaving: '|| l_proc,25);
1019: return;
1020: end if;
1021: else
1022: hr_utility.trace('In else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
1018: hr_utility.set_location('Leaving: '|| l_proc,25);
1019: return;
1020: end if;
1021: else
1022: hr_utility.trace('In else of (if hr_workflow_utility.item_attribute_exists) '|| l_proc);
1023: -- the TRANSACTION_ID does not exist so create it
1024: wf_engine.additemattr
1025: (itemtype => itemtype
1026: ,itemkey => itemkey
1096: -- transaction has been successfully created so commit and return success
1097: -- commit;
1098: result := 'SUCCESS';
1099: elsif funmode = 'CANCEL' then
1100: hr_utility.trace('In ( elsif funmode = CANCEL) '|| l_proc);
1101: null;
1102: end if;
1103:
1104: IF g_debug THEN
1101: null;
1102: end if;
1103:
1104: IF g_debug THEN
1105: hr_utility.set_location(' Leaving:'||l_proc, 30);
1106: END IF;
1107: hr_utility.set_location('Leaving: '|| l_proc,35);
1108: exception
1109: when hr_util_web.g_error_handled then
1103:
1104: IF g_debug THEN
1105: hr_utility.set_location(' Leaving:'||l_proc, 30);
1106: END IF;
1107: hr_utility.set_location('Leaving: '|| l_proc,35);
1108: exception
1109: when hr_util_web.g_error_handled then
1110: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1111: -- error from validating the login
1106: END IF;
1107: hr_utility.set_location('Leaving: '|| l_proc,35);
1108: exception
1109: when hr_util_web.g_error_handled then
1110: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1111: -- error from validating the login
1112: rollback to start_transaction;
1113: when others then
1114: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1110: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1111: -- error from validating the login
1112: rollback to start_transaction;
1113: when others then
1114: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1115: -- rollback any work
1116: rollback to start_transaction;
1117: raise;
1118: --
1143: l_allow_delete VARCHAR2(1) := 'Y';
1144: --- End of Code added for bug10407680
1145: l_proc constant varchar2(100) := g_package || ' rollback_transaction';
1146: begin
1147: hr_utility.set_location('Entering: '|| l_proc,5);
1148: if funmode = 'RUN' then
1149: hr_utility.trace('In ( if funmode = RUN ) '|| l_proc);
1150: -- Get the TRANSACTION_ID exists so ensure that it is null
1151: if get_transaction_id
1145: l_proc constant varchar2(100) := g_package || ' rollback_transaction';
1146: begin
1147: hr_utility.set_location('Entering: '|| l_proc,5);
1148: if funmode = 'RUN' then
1149: hr_utility.trace('In ( if funmode = RUN ) '|| l_proc);
1150: -- Get the TRANSACTION_ID exists so ensure that it is null
1151: if get_transaction_id
1152: (p_item_type => itemtype
1153: ,p_item_key => itemkey) is not null then
1150: -- Get the TRANSACTION_ID exists so ensure that it is null
1151: if get_transaction_id
1152: (p_item_type => itemtype
1153: ,p_item_key => itemkey) is not null then
1154: hr_utility.trace('In ( if get_transaction_id(..,.., ) '|| l_proc);
1155: savepoint rollback_transaction;
1156: -- check if this is appraisal transaction
1157: -- we need to call the custom call to update the appraisal status
1158: begin
1178: end if;
1179: */
1180: exception
1181: when others then
1182: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1183: hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'||
1184: 'rollback_transaction'||' : ' || sqlerrm);
1185: -- just log the message no need to raise it
1186: end;
1179: */
1180: exception
1181: when others then
1182: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1183: hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'||
1184: 'rollback_transaction'||' : ' || sqlerrm);
1185: -- just log the message no need to raise it
1186: end;
1187: --- Added If Condition for bug#10407680
1194: end if;
1195: --commit;
1196: result := 'SUCCESS';
1197: elsif funmode = 'CANCEL' then
1198: hr_utility.trace('In ( if funmode = CANCEL ) '|| l_proc);
1199: null;
1200: end if;
1201: hr_utility.set_location('Leaving: '|| l_proc,20);
1202: exception
1197: elsif funmode = 'CANCEL' then
1198: hr_utility.trace('In ( if funmode = CANCEL ) '|| l_proc);
1199: null;
1200: end if;
1201: hr_utility.set_location('Leaving: '|| l_proc,20);
1202: exception
1203: when others then
1204: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1205: -- rollback any work
1200: end if;
1201: hr_utility.set_location('Leaving: '|| l_proc,20);
1202: exception
1203: when others then
1204: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1205: -- rollback any work
1206: rollback to rollback_transaction;
1207: -- raise a system error
1208: raise;
1284: end if;
1285: end loop;
1286: */
1287:
1288: hr_utility.set_location('Entering: '|| l_proc,5);
1289: l_process_api := true;
1290: --
1291: if l_process_api then
1292: hr_utility.trace('In ( if l_process_api): '|| l_proc);
1288: hr_utility.set_location('Entering: '|| l_proc,5);
1289: l_process_api := true;
1290: --
1291: if l_process_api then
1292: hr_utility.trace('In ( if l_process_api): '|| l_proc);
1293: --
1294: -- issue a savepoint if operating in validation only mode.
1295: --
1296: if p_validate then
1333: if p_validate then
1334: raise hr_api.validate_enabled;
1335: end if;
1336: end if;
1337: hr_utility.set_location('Leaving: '|| l_proc,15);
1338: exception
1339: when hr_api.validate_enabled then
1340: --
1341: -- As the Validate_Enabled exception has been raised
1340: --
1341: -- As the Validate_Enabled exception has been raised
1342: -- we must rollback to the savepoint
1343: --
1344: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1345: rollback to process_web_api_call;
1346: when others then
1347: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1348:
1343: --
1344: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1345: rollback to process_web_api_call;
1346: when others then
1347: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1348:
1349: --close the cursor
1350: dbms_sql.close_cursor(l_cursor);
1351: raise;
1429: --
1430: l_parameter fnd_form_functions.parameters%TYPE;
1431: l_effectiveDate boolean := FALSE;
1432: begin
1433: hr_utility.set_location('Entering: '|| l_proc,5);
1434: -- set the ignore warnings flag
1435: if upper(p_ignore_warnings) = 'Y' then
1436: l_ignore_warnings := true;
1437: else
1442: (p_item_type => p_item_type
1443: ,p_item_key => p_item_key);
1444: if l_transaction_id is null then
1445: fnd_message.set_name('PER','l_transaction_id');
1446: hr_utility.raise_error;
1447: end if;
1448: -- set the Profiles before starting to process any step.
1449: hr_utility.set_location('Call Set_Transaction_Context: '|| l_proc, 10);
1450: -- Call Set_Transaction_Context
1445: fnd_message.set_name('PER','l_transaction_id');
1446: hr_utility.raise_error;
1447: end if;
1448: -- set the Profiles before starting to process any step.
1449: hr_utility.set_location('Call Set_Transaction_Context: '|| l_proc, 10);
1450: -- Call Set_Transaction_Context
1451: hr_transaction_swi.set_transaction_context(l_transaction_id);
1452:
1453: -- If p_effective_date is not NULL then set it on the g_txn_ctx.EFFECTIVE_DATE
1458: hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE := trunc(fnd_date.chardate_to_date(p_effective_date));
1459: END;
1460: end if;
1461:
1462: hr_utility.set_location('Call Set_Person_Context: '|| l_proc, 15);
1463: -- Call Set_Person_Context
1464:
1465: hr_transaction_swi.set_person_context(
1466: p_selected_person_id => hr_transaction_swi.g_txn_ctx.SELECTED_PERSON_ID,
1468: p_effective_date => hr_transaction_swi.g_txn_ctx.EFFECTIVE_DATE
1469: );
1470:
1471: -- select each transaction steps to process
1472: hr_utility.set_location('select each transaction steps to process: '|| l_proc, 20);
1473: open cur_fn;
1474: fetch cur_fn INTO l_parameter;
1475: close cur_fn;
1476: --
1483: hr_process_person_ss.g_person_id := null;
1484: hr_process_person_ss.g_assignment_id := null;
1485: hr_process_person_ss.g_session_id := null;
1486: hr_new_user_reg_ss.g_ignore_emp_generation := 'NO';
1487: hr_utility.trace('In (for I in csr_trs loop) '|| l_proc);
1488: for I in csr_trs loop
1489: begin
1490: -- call the API for the transaction step
1491: if p_update_object_version = 'Y' then
1540: l_warning_error := true;
1541: end if;
1542: end if;
1543: exception
1544: when hr_utility.hr_error then
1545: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1546: -- an application error has been raised. set the error flag
1547: -- to indicate an application error
1548: -- the error message should of been set already
1541: end if;
1542: end if;
1543: exception
1544: when hr_utility.hr_error then
1545: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1546: -- an application error has been raised. set the error flag
1547: -- to indicate an application error
1548: -- the error message should of been set already
1549: hr_message.provide_error;
1590: -- 05/09/2002 Bug 2356339 Fix Ends
1591:
1592: end if;
1593: when others then
1594: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1595: -- a system error has occurred so raise it to stop
1596: -- processing of the transaction steps
1597: raise;
1598: end;
1599: end loop;
1600: -- check to see if any application errors where raised
1601: if l_obj_fatal_error then
1602: fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
1603: raise hr_utility.hr_error;
1604: elsif l_object_version_error then
1605: fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
1606: raise hr_utility.hr_error;
1607: elsif l_application_error or l_warning_error then
1602: fnd_message.set_name('PER','HR_FATAL_OBJECT_ERROR');
1603: raise hr_utility.hr_error;
1604: elsif l_object_version_error then
1605: fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
1606: raise hr_utility.hr_error;
1607: elsif l_application_error or l_warning_error then
1608: raise hr_utility.hr_error;
1609: end if;
1610: hr_utility.set_location('Leaving: '|| l_proc,15);
1604: elsif l_object_version_error then
1605: fnd_message.set_name('PER','HR_7155_OBJECT_INVALID');
1606: raise hr_utility.hr_error;
1607: elsif l_application_error or l_warning_error then
1608: raise hr_utility.hr_error;
1609: end if;
1610: hr_utility.set_location('Leaving: '|| l_proc,15);
1611:
1612: exception
1606: raise hr_utility.hr_error;
1607: elsif l_application_error or l_warning_error then
1608: raise hr_utility.hr_error;
1609: end if;
1610: hr_utility.set_location('Leaving: '|| l_proc,15);
1611:
1612: exception
1613: when others then
1614: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1610: hr_utility.set_location('Leaving: '|| l_proc,15);
1611:
1612: exception
1613: when others then
1614: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1615: -- an application error, warning or system error was raised so
1616: -- keep raising it so the calling process must handle it
1617: raise;
1618: end process_transaction;
1628: ,p_update_object_version in varchar2 default 'N'
1629: ,p_result out nocopy varchar2) is
1630: l_proc constant varchar2(100) := g_package || ' validate_transaction';
1631: begin
1632: hr_utility.set_location('Entering: '|| l_proc,5);
1633: validate_transaction (
1634: p_item_type => p_item_type
1635: ,p_item_key => p_item_key
1636: ,p_effective_date => p_effective_date
1637: ,p_update_object_version => p_update_object_version
1638: ,p_ignore_warnings => 'N'
1639: ,p_result => p_result );
1640:
1641: hr_utility.set_location('Leaving: '|| l_proc,10);
1642: end;
1643:
1644:
1645: -- ns 11/06/2003: Bug 3223682: Overloaded validate_transaction with additional
1658: ,p_ignore_warnings in varchar2 default 'N'
1659: ,p_result out nocopy varchar2) is
1660: l_proc constant varchar2(100) := g_package || ' validate_transaction';
1661: begin
1662: hr_utility.set_location('Entering: '|| l_proc,5);
1663: p_result := 'N';
1664: savepoint VALIDATE_TRANSACTION;
1665: begin
1666: -- process the transaction reporting warnings if they exist
1673: ,p_validate => false);
1674: rollback to VALIDATE_TRANSACTION;
1675: p_result := null;
1676: exception
1677: when hr_utility.hr_error then
1678: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1679: hr_message.provide_error;
1680: --result := fnd_message.get;
1681: p_result := hr_message.last_message_name;
1674: rollback to VALIDATE_TRANSACTION;
1675: p_result := null;
1676: exception
1677: when hr_utility.hr_error then
1678: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1679: hr_message.provide_error;
1680: --result := fnd_message.get;
1681: p_result := hr_message.last_message_name;
1682: if p_result <> 'HR_7155_OBJECT_INVALID' and
1684: p_result := hr_message.get_message_text;
1685: end if;
1686: rollback to VALIDATE_TRANSACTION;
1687: when others then
1688: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1689: p_result := sqlerrm;
1690: rollback to VALIDATE_TRANSACTION;
1691: end;
1692: hr_utility.set_location('Leaving: '|| l_proc,10);
1688: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1689: p_result := sqlerrm;
1690: rollback to VALIDATE_TRANSACTION;
1691: end;
1692: hr_utility.set_location('Leaving: '|| l_proc,10);
1693: end validate_transaction;
1694: -- ----------------------------------------------------------------------------
1695: -- |----------------------------< commit_transaction >------------------------|
1696: -- ----------------------------------------------------------------------------
1710: l_return_status varchar2(10);
1711: l_txn_id number;
1712: p_data VARCHAR2(4000);
1713: begin
1714: hr_utility.set_location('Entering: '|| l_proc,5);
1715:
1716: if funmode = 'RUN' then
1717: hr_utility.trace('In(if funmode = RUN) '|| l_proc);
1718:
1713: begin
1714: hr_utility.set_location('Entering: '|| l_proc,5);
1715:
1716: if funmode = 'RUN' then
1717: hr_utility.trace('In(if funmode = RUN) '|| l_proc);
1718:
1719:
1720: -- new call to reset the apps context, if does not match
1721: -- the approving user context
1719:
1720: -- new call to reset the apps context, if does not match
1721: -- the approving user context
1722: if g_debug then
1723: hr_utility.set_location('calling setRespondedUserCtx',10);
1724: hr_utility.set_location('itemtype:'||itemtype,11);
1725: hr_utility.set_location('itemkey:'||itemkey,12);
1726: end if;
1727: begin
1720: -- new call to reset the apps context, if does not match
1721: -- the approving user context
1722: if g_debug then
1723: hr_utility.set_location('calling setRespondedUserCtx',10);
1724: hr_utility.set_location('itemtype:'||itemtype,11);
1725: hr_utility.set_location('itemkey:'||itemkey,12);
1726: end if;
1727: begin
1728: setRespondedUserCtx(itemtype,itemkey);
1721: -- the approving user context
1722: if g_debug then
1723: hr_utility.set_location('calling setRespondedUserCtx',10);
1724: hr_utility.set_location('itemtype:'||itemtype,11);
1725: hr_utility.set_location('itemkey:'||itemkey,12);
1726: end if;
1727: begin
1728: setRespondedUserCtx(itemtype,itemkey);
1729: exception
1729: exception
1730: when others then
1731: -- do nothing ??
1732: if g_debug then
1733: hr_utility.set_location('Error calling setRespondedUserCtx SQLERRM' ||' '||to_char(SQLCODE),20);
1734: end if;
1735: null;
1736: end;
1737:
1778: end if;
1779:
1780: exception
1781: when l_commit_error then
1782: hr_utility.set_location('EXCEPTION: SWI COMMIT ERROR '|| l_proc,555);
1783: l_sqlerrm := sqlerrm;
1784: l_error_text := hr_utility.get_message;
1785: if l_error_text is null then
1786: l_error_text := fnd_message.get;
1780: exception
1781: when l_commit_error then
1782: hr_utility.set_location('EXCEPTION: SWI COMMIT ERROR '|| l_proc,555);
1783: l_sqlerrm := sqlerrm;
1784: l_error_text := hr_utility.get_message;
1785: if l_error_text is null then
1786: l_error_text := fnd_message.get;
1787: end if;
1788: wf_engine.setitemattrtext
1798: ,avalue => 'E');
1799: raise;
1800:
1801: when l_merge_attachment_error then
1802: hr_utility.set_location('EXCEPTION: MERGE ATTCHMENT ERROR '|| l_proc,555);
1803: l_sqlerrm := sqlerrm;
1804: l_error_text := hr_utility.get_message;
1805: if l_error_text is null then
1806: l_error_text := fnd_message.get;
1800:
1801: when l_merge_attachment_error then
1802: hr_utility.set_location('EXCEPTION: MERGE ATTCHMENT ERROR '|| l_proc,555);
1803: l_sqlerrm := sqlerrm;
1804: l_error_text := hr_utility.get_message;
1805: if l_error_text is null then
1806: l_error_text := fnd_message.get;
1807: end if;
1808: wf_engine.setitemattrtext
1819: raise;
1820:
1821:
1822: when others then
1823: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1824: l_sqlerrm := sqlerrm;
1825: raise;
1826: end;
1827: -- transition workflow with SUCCESS
1826: end;
1827: -- transition workflow with SUCCESS
1828: result := 'COMPLETE:SUCCESS';
1829: elsif funmode = 'CANCEL' then
1830: hr_utility.trace('In(if funmode = CANCEL) '|| l_proc);
1831: result := 'COMPLETE:';
1832: end if;
1833: hr_utility.set_location('Leaving: '|| l_proc,15);
1834: exception
1829: elsif funmode = 'CANCEL' then
1830: hr_utility.trace('In(if funmode = CANCEL) '|| l_proc);
1831: result := 'COMPLETE:';
1832: end if;
1833: hr_utility.set_location('Leaving: '|| l_proc,15);
1834: exception
1835: when hr_utility.hr_error then
1836: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1837: -- rollback any work
1831: result := 'COMPLETE:';
1832: end if;
1833: hr_utility.set_location('Leaving: '|| l_proc,15);
1834: exception
1835: when hr_utility.hr_error then
1836: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1837: -- rollback any work
1838: rollback to commit_transaction;
1839: --
1832: end if;
1833: hr_utility.set_location('Leaving: '|| l_proc,15);
1834: exception
1835: when hr_utility.hr_error then
1836: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1837: -- rollback any work
1838: rollback to commit_transaction;
1839: --
1840: l_error_text := hr_utility.get_message;
1836: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1837: -- rollback any work
1838: rollback to commit_transaction;
1839: --
1840: l_error_text := hr_utility.get_message;
1841: if l_error_text is null then
1842: l_error_text := fnd_message.get;
1843: end if;
1844: -- 1903606
1864:
1865: -- an application error or warning has been set
1866: result := 'COMPLETE:APPLICATION_ERROR';
1867: when others then
1868: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1869:
1870: if(itemkey IS NOT NULL) THEN
1871: hr_utility.set_location('PDATA11 : ' || p_data, 20);
1872: p_data := nvl(wf_engine.GetItemAttrText
1867: when others then
1868: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
1869:
1870: if(itemkey IS NOT NULL) THEN
1871: hr_utility.set_location('PDATA11 : ' || p_data, 20);
1872: p_data := nvl(wf_engine.GetItemAttrText
1873: (itemtype => itemtype
1874: ,itemkey => itemkey
1875: ,aname => 'ERROR_STACK'),' ') || p_data;
1872: p_data := nvl(wf_engine.GetItemAttrText
1873: (itemtype => itemtype
1874: ,itemkey => itemkey
1875: ,aname => 'ERROR_STACK'),' ') || p_data;
1876: hr_utility.set_location('PDATA21 : ' || p_data, 20);
1877: end if;
1878:
1879:
1880: -- rollback any work
1880: -- rollback any work
1881: rollback to commit_transaction;
1882:
1883: if(itemkey IS NOT NULL) THEN
1884: hr_utility.set_location('PDATA25 : ' || p_data, 20);
1885: hr_utility.set_location('ITEM_KEY : ' || itemkey, 20);
1886: wf_engine.setitemattrtext
1887: (itemtype => itemtype
1888: ,itemkey => itemkey
1881: rollback to commit_transaction;
1882:
1883: if(itemkey IS NOT NULL) THEN
1884: hr_utility.set_location('PDATA25 : ' || p_data, 20);
1885: hr_utility.set_location('ITEM_KEY : ' || itemkey, 20);
1886: wf_engine.setitemattrtext
1887: (itemtype => itemtype
1888: ,itemkey => itemkey
1889: ,aname => 'ERROR_STACK'
1887: (itemtype => itemtype
1888: ,itemkey => itemkey
1889: ,aname => 'ERROR_STACK'
1890: ,avalue => p_data);
1891: hr_utility.set_location('PDATA25 : ' || p_data, 20);
1892: end if;
1893:
1894: -- 1903606
1895: wf_engine.setitemattrtext
1924: ,funmode in varchar2
1925: ,result out nocopy varchar2) is
1926: l_proc constant varchar2(100) := g_package || ' commit_approval_transaction';
1927: begin
1928: hr_utility.set_location('Entering: '|| l_proc,5);
1929: if funmode = 'RUN' then
1930: savepoint commit_approval_transaction;
1931: -- process the transaction reporting warnings if they exist
1932: process_transaction
1937: result := 'SUCCESS';
1938: elsif funmode = 'CANCEL' then
1939: null;
1940: end if;
1941: hr_utility.set_location('Leaving: '|| l_proc,10);
1942: exception
1943: when others then
1944: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1945: -- rollback any work
1940: end if;
1941: hr_utility.set_location('Leaving: '|| l_proc,10);
1942: exception
1943: when others then
1944: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1945: -- rollback any work
1946: rollback to commit_approval_transaction;
1947: -- system error
1948: raise;
1956: ,p_actid in number) is
1957: l_proc constant varchar2(100) := g_package || ' retry_transaction';
1958:
1959: begin
1960: hr_utility.set_location('Entering: '|| l_proc,5);
1961: savepoint retry_transaction;
1962: -- process the transaction ignoring warnings if they exist
1963: process_transaction
1964: (p_item_type => p_item_type
1976: -- ,activity => '#'||p_actid
1977: -- ,result => 'SUCCESS');
1978: -- commit the transaction
1979: -- commit;
1980: hr_utility.set_location('Leaving: '|| l_proc,10);
1981: exception
1982: when hr_utility.hr_error then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984: -- rollback any work
1978: -- commit the transaction
1979: -- commit;
1980: hr_utility.set_location('Leaving: '|| l_proc,10);
1981: exception
1982: when hr_utility.hr_error then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984: -- rollback any work
1985: rollback to retry_transaction;
1986: -- an application error has been set
1979: -- commit;
1980: hr_utility.set_location('Leaving: '|| l_proc,10);
1981: exception
1982: when hr_utility.hr_error then
1983: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1984: -- rollback any work
1985: rollback to retry_transaction;
1986: -- an application error has been set
1987: -- redisplay the errors
1986: -- an application error has been set
1987: -- redisplay the errors
1988: raise;
1989: when others then
1990: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
1991: -- rollback any work
1992: rollback to retry_transaction;
1993: -- system error
1994: raise;
2057: ln_transaction_id hr_api_transaction_steps.transaction_id%TYPE;
2058:
2059: BEGIN
2060:
2061: hr_utility.set_location('Entering: '|| l_proc,5);
2062: -- populate the values in case of null before passing it to API.
2063: -- CompWorkBench
2064:
2065: populate_null_values
2131: END IF;
2132:
2133: hr_transaction_api.g_update_flag := 'N';
2134: l_count := p_transaction_data.COUNT;
2135: hr_utility.trace('In(for I in csr_trs loop)'||l_proc);
2136: FOR i IN 1..l_count LOOP
2137: IF p_transaction_data(i).param_data_type = 'DATE' THEN
2138: --ensure that the effective date is in the correct format
2139: BEGIN
2142: l_original_date := trunc(to_date(p_transaction_data(i).param_original_value,
2143: g_date_format)); --ns
2144: EXCEPTION
2145: WHEN OTHERS THEN
2146: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2147: -- the date check failed therefore we must report the error
2148: -- and reset to the original value
2149: l_value_error := true;
2150: fnd_message.set_name('PER', 'HR_51778_WEB_KIOSK_INV_EDATE');
2157: ,p_value => l_date
2158: ,p_original_value => l_original_date);
2159:
2160: IF l_value_error THEN
2161: RAISE hr_utility.hr_error;
2162: END IF;
2163: ELSIF p_transaction_data(i).param_data_type = 'NUMBER' THEN
2164: BEGIN
2165: --ns start
2191: ,p_original_value => l_original_number); --ns
2192: END IF;
2193: exception
2194: when others then
2195: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
2196: --SQLERRM
2197: RAISE hr_utility.hr_error;
2198: END;
2199: ELSIF p_transaction_data(i).param_data_type = 'VARCHAR2' THEN
2193: exception
2194: when others then
2195: hr_utility.set_location('EXCEPTION: '|| l_proc,560);
2196: --SQLERRM
2197: RAISE hr_utility.hr_error;
2198: END;
2199: ELSIF p_transaction_data(i).param_data_type = 'VARCHAR2' THEN
2200: BEGIN
2201: hr_transaction_api.set_varchar2_value
2205: ,p_value => p_transaction_data(i).param_value
2206: ,p_original_value => p_transaction_data(i).param_original_value); --ns
2207: exception
2208: when others then
2209: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
2210: --SQLERRM
2211: RAISE hr_utility.hr_error;
2212: END;
2213: END IF;
2207: exception
2208: when others then
2209: hr_utility.set_location('EXCEPTION: '|| l_proc,565);
2210: --SQLERRM
2211: RAISE hr_utility.hr_error;
2212: END;
2213: END IF;
2214: END LOOP;
2215:
2224: -- --------------------------------------------------------------------
2225: -- Find out how many variables we have to set
2226:
2227: IF p_transaction_step_id IS NOT NULL THEN
2228: hr_utility.trace('In ( IF p_transaction_step_id IS NOT NULL ) '|| l_proc);
2229:
2230: OPEN get_trans_id(p_transaction_step_id);
2231: FETCH get_trans_id into ln_transaction_id;
2232: CLOSE get_trans_id;
2231: FETCH get_trans_id into ln_transaction_id;
2232: CLOSE get_trans_id;
2233:
2234: IF ln_transaction_id IS NOT NULL THEN
2235: hr_utility.trace('In ( I IF ln_transaction_id IS NOT NULL ) '|| l_proc);
2236: Begin
2237: if ( wf_engine.GetItemAttrText(p_item_type ,p_item_key ,'HR_APPROVAL_PRC_VERSION') = 'V5' ) then
2238: lv_status := hr_api.g_varchar2; -- so that the original value is picked from txn table;
2239: end if;
2238: lv_status := hr_api.g_varchar2; -- so that the original value is picked from txn table;
2239: end if;
2240: Exception
2241: when Others then -- wf attribute not found
2242: hr_utility.set_location('EXCEPTION: '|| l_proc,570);
2243: null;
2244: End;
2245:
2246: hr_transaction_api.update_transaction
2251: );
2252: END IF;
2253: END IF;
2254:
2255: hr_utility.set_location('Leaving: '|| l_proc,25);
2256: EXCEPTION
2257: WHEN OTHERS THEN
2258: hr_utility.set_location('EXCEPTION: '|| l_proc,575);
2259: --SQLERRM
2254:
2255: hr_utility.set_location('Leaving: '|| l_proc,25);
2256: EXCEPTION
2257: WHEN OTHERS THEN
2258: hr_utility.set_location('EXCEPTION: '|| l_proc,575);
2259: --SQLERRM
2260: raise;
2261: END save_transaction_step;
2262:
2278: li_step_count INTEGER;
2279: l_proc constant varchar2(100) := g_package || ' get_activity_trans_step_id';
2280: BEGIN
2281:
2282: hr_utility.set_location('Entering: '|| l_proc,5);
2283: li_step_count := p_trans_step_id_tbl.COUNT;
2284: hr_utility.trace('Going to ( FOR i IN 0..li_step_count LOOP): '|| l_proc);
2285: FOR i IN 0..li_step_count LOOP
2286: IF p_activity_name =
2280: BEGIN
2281:
2282: hr_utility.set_location('Entering: '|| l_proc,5);
2283: li_step_count := p_trans_step_id_tbl.COUNT;
2284: hr_utility.trace('Going to ( FOR i IN 0..li_step_count LOOP): '|| l_proc);
2285: FOR i IN 0..li_step_count LOOP
2286: IF p_activity_name =
2287: hr_transaction_api.get_varchar2_value
2288: (p_transaction_step_id => p_trans_step_id_tbl(i)
2290: THEN
2291: RETURN p_trans_step_id_tbl(i);
2292: END IF;
2293: END LOOP;
2294: hr_utility.set_location('Leaving: '|| l_proc,15);
2295:
2296:
2297: RETURN NULL;
2298:
2298:
2299:
2300: EXCEPTION
2301: WHEN OTHERS THEN
2302: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2303: --SQLERRM
2304: raise;
2305: END get_activity_trans_step_id;
2306:
2319: ltt_trans_obj_vers_num hr_util_web.g_varchar2_tab_type;
2320: ln_trans_step_rows number ;
2321: l_proc constant varchar2(100) := g_package || ' check_txn_step_exists';
2322: BEGIN
2323: hr_utility.set_location('Entering: '|| l_proc,5);
2324: ln_transaction_id := get_transaction_id
2325: (p_Item_Type => p_item_type
2326: , p_Item_Key => p_item_key);
2327:
2326: , p_Item_Key => p_item_key);
2327:
2328: IF ln_transaction_id IS NOT NULL
2329: THEN
2330: hr_utility.trace(' In(IF ln_transaction_id IS NOT NULL)'|| l_proc);
2331: hr_transaction_api.get_transaction_step_info
2332: (p_Item_Type => p_item_type,
2333: p_Item_Key => p_item_key,
2334: p_activity_id =>p_actid,
2338:
2339: -- if no transaction steps are found , return
2340: IF ln_trans_step_rows >= 1
2341: THEN
2342: hr_utility.trace(' In( IF ln_trans_step_rows >= 1)'|| l_proc);
2343: hr_utility.set_location('Leaving: '|| l_proc,20);
2344: return TRUE ;
2345: ELSE
2346: hr_utility.set_location('Leaving: '|| l_proc,20);
2339: -- if no transaction steps are found , return
2340: IF ln_trans_step_rows >= 1
2341: THEN
2342: hr_utility.trace(' In( IF ln_trans_step_rows >= 1)'|| l_proc);
2343: hr_utility.set_location('Leaving: '|| l_proc,20);
2344: return TRUE ;
2345: ELSE
2346: hr_utility.set_location('Leaving: '|| l_proc,20);
2347: return FALSE ;
2342: hr_utility.trace(' In( IF ln_trans_step_rows >= 1)'|| l_proc);
2343: hr_utility.set_location('Leaving: '|| l_proc,20);
2344: return TRUE ;
2345: ELSE
2346: hr_utility.set_location('Leaving: '|| l_proc,20);
2347: return FALSE ;
2348: END IF ;
2349:
2350: END IF ;
2347: return FALSE ;
2348: END IF ;
2349:
2350: END IF ;
2351: hr_utility.set_location('Leaving: '|| l_proc,20);
2352: return FALSE ;
2353: END ;
2354:
2355:
2372: ln_trans_step_rows NUMBER ;
2373: ln_value_id NUMBER ;
2374: l_proc constant varchar2(100) := g_package || ' delete_trn_step_by_act_name';
2375: BEGIN
2376: hr_utility.set_location('Entering: '|| l_proc,5);
2377: IF p_activity_name is not null THEN
2378: hr_utility.trace('In(IF p_activity_name is not null) '|| l_proc);
2379:
2380: ln_transaction_id := get_transaction_id
2374: l_proc constant varchar2(100) := g_package || ' delete_trn_step_by_act_name';
2375: BEGIN
2376: hr_utility.set_location('Entering: '|| l_proc,5);
2377: IF p_activity_name is not null THEN
2378: hr_utility.trace('In(IF p_activity_name is not null) '|| l_proc);
2379:
2380: ln_transaction_id := get_transaction_id
2381: (p_Item_Type => p_item_type,
2382: p_Item_Key => p_item_key);
2381: (p_Item_Type => p_item_type,
2382: p_Item_Key => p_item_key);
2383: IF ln_transaction_id IS NOT NULL
2384: THEN
2385: hr_utility.trace('In( IF ln_transaction_id IS NOT NULL) '|| l_proc);
2386: hr_transaction_api.get_transaction_step_info
2387: (p_Item_Type => p_item_type,
2388: p_Item_Key => p_item_key,
2389: p_activity_id =>p_actid,
2394:
2395: -- if no transaction steps are found , return
2396: IF ln_trans_step_rows > 0
2397: THEN
2398: hr_utility.trace('In( IF ln_trans_step_rows > 0) '|| l_proc);
2399: ln_transaction_step_id :=
2400: hr_transaction_ss.get_activity_trans_step_id
2401: (p_activity_name =>p_activity_name,
2402: p_trans_step_id_tbl => ltt_trans_step_ids);
2405: p_login_person_id => p_login_person_id);
2406: END IF ;
2407: END IF;
2408: END IF;
2409: hr_utility.set_location('Leaving: '|| l_proc,25);
2410:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2409: hr_utility.set_location('Leaving: '|| l_proc,25);
2410:
2411: EXCEPTION
2412: WHEN OTHERS THEN
2413: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2414: raise;
2415: END delete_trn_step_by_act_name;
2416:
2417: -- -------------------------------------------------------------------------
2430: l_trans_step_rows NUMBER;
2431: l_proc constant varchar2(100) := g_package || ' delete_transaction_steps';
2432:
2433: BEGIN
2434: hr_utility.set_location('Entering: '|| l_proc,5);
2435: if p_actid IS NULL then
2436: hr_transaction_api.get_transaction_step_info (
2437: p_Item_Type => p_item_type,
2438: p_Item_Key => p_item_key,
2449: p_object_version_number => l_trans_obj_vers_num,
2450: p_rows => l_trans_step_rows
2451: );
2452: end if;
2453: hr_utility.trace('Going to ( FOR i IN 0..(l_trans_step_rows - 1) LOOP) '|| l_proc);
2454: FOR i IN 0..(l_trans_step_rows - 1) LOOP
2455: delete_transaction_step
2456: (p_transaction_step_id => l_trans_step_ids(i)
2457: ,p_object_version_number => l_trans_obj_vers_num(i)
2457: ,p_object_version_number => l_trans_obj_vers_num(i)
2458: ,p_login_person_id => p_login_person_id);
2459: END LOOP;
2460:
2461: --hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
2462: --hr_utility.raise_error;
2463: hr_utility.set_location('Leaving: '|| l_proc,15);
2464:
2465: EXCEPTION
2458: ,p_login_person_id => p_login_person_id);
2459: END LOOP;
2460:
2461: --hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
2462: --hr_utility.raise_error;
2463: hr_utility.set_location('Leaving: '|| l_proc,15);
2464:
2465: EXCEPTION
2466: WHEN OTHERS THEN
2459: END LOOP;
2460:
2461: --hr_utility.set_message(801, 'HR_51750_WEB_TRANSAC_STARTED');
2462: --hr_utility.raise_error;
2463: hr_utility.set_location('Leaving: '|| l_proc,15);
2464:
2465: EXCEPTION
2466: WHEN OTHERS THEN
2467: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2463: hr_utility.set_location('Leaving: '|| l_proc,15);
2464:
2465: EXCEPTION
2466: WHEN OTHERS THEN
2467: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2468: raise;
2469:
2470: END delete_transaction_steps;
2471:
2489: ----- bug 5102128
2490:
2491:
2492: BEGIN
2493: hr_utility.set_location('Entering: '|| l_proc,5);
2494: if p_transaction_step_id is not null then
2495: hr_utility.trace('In (if p_transaction_step_id is not null ) '|| l_proc);
2496: if p_object_version_number is null then
2497: hr_utility.trace('In (if p_object_version_number is null ) '|| l_proc);
2491:
2492: BEGIN
2493: hr_utility.set_location('Entering: '|| l_proc,5);
2494: if p_transaction_step_id is not null then
2495: hr_utility.trace('In (if p_transaction_step_id is not null ) '|| l_proc);
2496: if p_object_version_number is null then
2497: hr_utility.trace('In (if p_object_version_number is null ) '|| l_proc);
2498: l_object_version_number :=
2499: get_transaction_step_ovn(to_number(p_transaction_step_id));
2493: hr_utility.set_location('Entering: '|| l_proc,5);
2494: if p_transaction_step_id is not null then
2495: hr_utility.trace('In (if p_transaction_step_id is not null ) '|| l_proc);
2496: if p_object_version_number is null then
2497: hr_utility.trace('In (if p_object_version_number is null ) '|| l_proc);
2498: l_object_version_number :=
2499: get_transaction_step_ovn(to_number(p_transaction_step_id));
2500: else
2501: hr_utility.trace('In else of (if p_object_version_number is null ) '|| l_proc);
2497: hr_utility.trace('In (if p_object_version_number is null ) '|| l_proc);
2498: l_object_version_number :=
2499: get_transaction_step_ovn(to_number(p_transaction_step_id));
2500: else
2501: hr_utility.trace('In else of (if p_object_version_number is null ) '|| l_proc);
2502: l_object_version_number := to_number(p_object_version_number);
2503: end if;
2504:
2505: ----- bug 5102128
2526: ----- bug 5102128
2527: end if;
2528: ----- bug 5102128
2529: end if;
2530: hr_utility.set_location('Leaving: '|| l_proc,20);
2531:
2532: EXCEPTION
2533: WHEN OTHERS THEN
2534: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2530: hr_utility.set_location('Leaving: '|| l_proc,20);
2531:
2532: EXCEPTION
2533: WHEN OTHERS THEN
2534: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2535: raise;
2536:
2537: END delete_transaction_step;
2538:
2547:
2548: l_ovb number;
2549: l_proc constant varchar2(100) := g_package || ' get_transaction_step_ovn';
2550: begin
2551: hr_utility.set_location('Entering: '|| l_proc,5);
2552: open csr_hats;
2553: fetch csr_hats into l_ovb;
2554: if csr_hats%notfound then
2555: l_ovb := null;
2554: if csr_hats%notfound then
2555: l_ovb := null;
2556: end if;
2557: close csr_hats;
2558: hr_utility.set_location('Leaving: '|| l_proc,10);
2559: return l_ovb;
2560:
2561: EXCEPTION
2562: WHEN OTHERS THEN
2559: return l_ovb;
2560:
2561: EXCEPTION
2562: WHEN OTHERS THEN
2563: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2564: raise;
2565:
2566: end get_transaction_step_ovn;
2567:
2576: l_number_value number;
2577: l_date date;
2578: l_proc constant varchar2(100) := g_package || ' set_transaction_value';
2579: begin
2580: hr_utility.set_location('Entering: '|| l_proc,5);
2581: if (p_datatype = 'VARCHAR2') then
2582: hr_utility.trace('In (if (p_datatype = VARCHAR2)) '|| l_proc);
2583: hr_transaction_api.set_varchar2_value
2584: (p_transaction_step_id => p_transaction_step_id
2578: l_proc constant varchar2(100) := g_package || ' set_transaction_value';
2579: begin
2580: hr_utility.set_location('Entering: '|| l_proc,5);
2581: if (p_datatype = 'VARCHAR2') then
2582: hr_utility.trace('In (if (p_datatype = VARCHAR2)) '|| l_proc);
2583: hr_transaction_api.set_varchar2_value
2584: (p_transaction_step_id => p_transaction_step_id
2585: ,p_person_id => p_login_person_id
2586: ,p_name => p_name
2585: ,p_person_id => p_login_person_id
2586: ,p_name => p_name
2587: ,p_value => p_value);
2588: elsif p_datatype = 'NUMBER' then
2589: hr_utility.trace('In ( elsif p_datatype = NUMBER) '|| l_proc);
2590: hr_transaction_api.set_number_value
2591: (p_transaction_step_id => p_transaction_step_id
2592: ,p_person_id => p_login_person_id
2593: ,p_name => p_name
2592: ,p_person_id => p_login_person_id
2593: ,p_name => p_name
2594: ,p_value => to_number(p_value));
2595: elsif p_datatype = 'DATE' then
2596: hr_utility.trace('In ( elsif p_datatype = DATE) '|| l_proc);
2597: hr_transaction_api.set_date_value
2598: (p_transaction_step_id => p_transaction_step_id
2599: ,p_person_id => p_login_person_id
2600: ,p_name => p_name
2599: ,p_person_id => p_login_person_id
2600: ,p_name => p_name
2601: ,p_value => to_date(p_value, g_date_format));
2602: else
2603: hr_utility.trace('In else of (if (p_datatype = VARCHAR2)) '|| l_proc);
2604: --raise datetype error;
2605: null;
2606: end if;
2607: hr_utility.set_location('Leaving: '|| l_proc,15);
2603: hr_utility.trace('In else of (if (p_datatype = VARCHAR2)) '|| l_proc);
2604: --raise datetype error;
2605: null;
2606: end if;
2607: hr_utility.set_location('Leaving: '|| l_proc,15);
2608: EXCEPTION
2609: WHEN OTHERS THEN
2610:
2611: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2607: hr_utility.set_location('Leaving: '|| l_proc,15);
2608: EXCEPTION
2609: WHEN OTHERS THEN
2610:
2611: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2612: raise;
2613: end set_transaction_value;
2614:
2615: procedure create_transaction_step
2626: l_trns_object_version_number number;
2627: l_proc constant varchar2(100) := g_package || ' create_transaction_step';
2628:
2629: begin
2630: hr_utility.set_location('Entering: '|| l_proc,5);
2631: l_transaction_id := get_transaction_id
2632: (p_item_type => p_item_type
2633: ,p_item_key => p_item_key);
2634: if l_transaction_id is null then
2654: ,p_item_key => p_item_key
2655: ,p_activity_id => to_number(p_actid)
2656: ,p_transaction_step_id => p_transaction_step_id
2657: ,p_object_version_number => l_trns_object_version_number);
2658: hr_utility.set_location('Leaving: '|| l_proc,10);
2659: end create_transaction_step;
2660:
2661: --
2662: -- --------------------
2718: and name = p_name
2719: and varchar2_value Is Not Null;
2720:
2721: begin
2722: hr_utility.set_location('Entering: '|| l_proc,5);
2723: -- to hold error message if raised;
2724: p_status := 'SUCCESS';
2725:
2726: select count(transaction_step_id)
2729: where item_key = p_item_key
2730: and item_type = p_item_type;
2731:
2732: if l_tx_step_count <> 0 then -- user has changed data in one of the previous pages
2733: hr_utility.trace('In ( if l_tx_step_count <> 0 ) '|| l_proc);
2734:
2735: -- Initialize Table index to 0
2736: l_index := 0;
2737: hr_utility.trace('Going to ( for I in csr_hatv ( p_item_key => p_item_key ) '|| l_proc);
2733: hr_utility.trace('In ( if l_tx_step_count <> 0 ) '|| l_proc);
2734:
2735: -- Initialize Table index to 0
2736: l_index := 0;
2737: hr_utility.trace('Going to ( for I in csr_hatv ( p_item_key => p_item_key ) '|| l_proc);
2738: for I in csr_hatv ( p_item_key => p_item_key
2739: ,p_item_type => p_item_type
2740: ,p_name => 'P_REVIEW_PROC_CALL'
2741: ) loop
2810: else
2811: p_review_regions := 'NO_CHANGES';
2812: end if;
2813:
2814: hr_utility.set_location('Leaving: '|| l_proc,20);
2815:
2816: --
2817: EXCEPTION WHEN OTHERS THEN
2818: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2814: hr_utility.set_location('Leaving: '|| l_proc,20);
2815:
2816: --
2817: EXCEPTION WHEN OTHERS THEN
2818: hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2819: p_status := g_package||'get_review_regions Error :'||substr(SQLERRM,1,1000);
2820: -- handle this exception and raise it to jdbc call
2821: end; -- get_review_regions
2822:
2837: i integer := 0;
2838: l_proc constant varchar2(100) := g_package || ' get_transaction_data';
2839:
2840: begin
2841: hr_utility.set_location('Entering: '|| l_proc,5);
2842: p_transaction_data := null;
2843: open transaction_data_row;
2844: if g_oracle_db_version >= 9 then
2845: hr_utility.trace('In(if g_oracle_db_version >= 9 ): '|| l_proc);
2841: hr_utility.set_location('Entering: '|| l_proc,5);
2842: p_transaction_data := null;
2843: open transaction_data_row;
2844: if g_oracle_db_version >= 9 then
2845: hr_utility.trace('In(if g_oracle_db_version >= 9 ): '|| l_proc);
2846: loop
2847: fetch transaction_data_row bulk collect
2848: into p_transaction_data.name,
2849: p_transaction_data.VARCHAR2_VALUE,
2852: limit p_bulk_fetch_limit;
2853: exit when transaction_data_row%notfound;
2854: end loop;
2855: else
2856: hr_utility.trace('In else of (if g_oracle_db_version >= 9 ): '|| l_proc);
2857: loop
2858: i := i + 1;
2859: fetch transaction_data_row
2860: into p_transaction_data.name(i),
2864: exit when transaction_data_row%notfound;
2865: end loop;
2866: end if;
2867: close transaction_data_row;
2868: hr_utility.set_location('Leaving: '|| l_proc,15);
2869: end get_transaction_data;
2870:
2871: procedure set_transaction_approved
2872: (itemtype in varchar2,
2876: result out nocopy varchar2 ) is
2877: ln_transaction_id hr_api_transactions.transaction_id%TYPE;
2878: l_proc constant varchar2(100) := g_package || ' set_transaction_approved';
2879: begin
2880: hr_utility.set_location('Entering: '|| l_proc,5);
2881:
2882: if ( funmode = 'RUN' ) then
2883: ln_transaction_id := get_transaction_id(itemtype, itemkey);
2884: set_save_for_later_status
2892: null;
2893: --
2894: --
2895: end if;
2896: hr_utility.set_location('Leaving: '|| l_proc,10);
2897: end set_transaction_approved;
2898:
2899:
2900: end hr_transaction_ss;