[Home] [Help]
PACKAGE BODY: APPS.PQH_AME_UTILS
Source
1 PACKAGE BODY pqh_ame_utils AS
2 /* $Header: pqameutl.pkb 120.0 2005/05/29 01:24:12 appldev noship $ */
3 --
4 --
5 --
6 g_package constant varchar2(30) := 'pqh_ame_utils.';
7 g_debug constant boolean := hr_utility.debug_enabled;
8
9 -- ---------------------------------------------------------------------------
10 -- ---------------<Populate_Txn_Details> -------------------------------------
11 -- ---------------------------------------------------------------------------
12 procedure populate_txn_details (
13 p_transaction_id in number) is
14
15 cursor csr_txn is
16 select item_type ,item_key, process_name, selected_person_id, creator_person_id
17 from hr_api_transactions
18 where transaction_id = p_transaction_id;
19 l_proc varchar2(72);
20 begin
21 l_proc := g_package||'populate_txn_details';
22 hr_utility.set_location('Entering:'||l_proc, 5);
23 if (g_transaction_id = p_transaction_id ) then
24 return;
25 else
26 g_transaction_id := p_transaction_id;
27 open csr_txn;
28 fetch csr_txn into g_item_type, g_item_key, g_process_name,
29 g_person_id, g_creator_person_id;
30 close csr_txn;
31 end if;
32 hr_utility.set_location(' Leaving:'||l_proc, 10);
33 Exception
34 WHEN others THEN
35 Raise;
36 end populate_txn_details;
37
38
39 -- ---------------------------------------------------------------------------
40 -- ---------------<Get_Item_Type> --------------------------------------------
41 -- ---------------------------------------------------------------------------
42 function get_item_type( p_transaction_id in varchar2) return varchar2 is
43 l_proc varchar2(72);
44 begin
45 l_proc := g_package||'get_item_type';
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 populate_txn_details(p_transaction_id);
48 hr_utility.set_location(' Leaving:'||l_proc, 10);
49 return g_item_type;
50 Exception
51 WHEN others THEN
52 wf_core.context(g_package,'.get_final_approver',g_item_type,g_item_key);
53 Raise;
54 end;
55 --
56 -- ---------------------------------------------------------------------------
57 -- ---------------<Get_Item_Key> ---------------------------------------------
58 -- ---------------------------------------------------------------------------
59 function get_item_key( p_transaction_id in varchar2) return varchar2 is
60 l_proc varchar2(72);
61 begin
62 l_proc := g_package||'get_item_key';
63 hr_utility.set_location('Entering:'||l_proc, 5);
64 populate_txn_details(p_transaction_id);
65 hr_utility.set_location(' Leaving:'||l_proc, 10);
66 return g_item_key;
67 Exception
68 WHEN others THEN
69 wf_core.context(g_package,'.get_final_approver',g_item_type,g_item_key);
70 Raise;
71 end;
72 --
73 -- ---------------------------------------------------------------------------
74 -- ---------------<Get_Process_Name> -----------------------------------------
75 -- ---------------------------------------------------------------------------
76 function get_process_name( p_transaction_id in varchar2) return varchar2 is
77 l_proc varchar2(72);
78 begin
79 l_proc := g_package||'get_process_name';
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 populate_txn_details(p_transaction_id);
82 hr_utility.set_location(' Leaving:'||l_proc, 10);
83 return g_process_name;
84
85 Exception
86 WHEN others THEN
87 wf_core.context(g_package,'.get_final_approver',g_item_type,g_item_key);
88 Raise;
89 end;
90 --
91 -- ---------------------------------------------------------------------------
92 -- ---------------<Get_Final_Approver> ---------------------------------------
93 -- ---------------------------------------------------------------------------
94 function get_final_approver( p_transaction_id in varchar2) return varchar2 is
95 approvers ame_util.approversTable;
96 l_txn_type varchar2(240);
97 l_txn_app_id number(18);
98 l_cnt number;
99
100 l_creator_person_id number(18);
101 l_final_approver_id number(18);
102 l_response varchar2(10);
103 l_proc varchar2(72);
104 begin
105 l_proc := g_package||'get_final_approver';
106 hr_utility.set_location('Entering:'||l_proc, 5);
107 populate_txn_details(p_transaction_id);
108
109 l_creator_person_id := g_person_id;
110 l_final_approver_id := g_person_id;
111
112 l_response := hr_approval_custom.Check_Final_approver(
113 p_forward_to_person_id => l_creator_person_id,
114 p_person_id => l_creator_person_id );
115
116 while l_response='N' loop
117
118 l_final_approver_id := hr_approval_custom.Get_Next_Approver(
119 p_person_id =>l_final_approver_id);
120
121 l_response := hr_approval_custom.Check_Final_approver(
122 p_forward_to_person_id => l_final_approver_id,
123 p_person_id => l_creator_person_id );
124
125 end loop;
126 hr_utility.set_location(' Leaving:'||l_proc, 10);
127
128 return l_final_approver_id;
129
130 Exception
131 WHEN others THEN
132 wf_core.context(g_package,'.get_final_approver',g_item_type,g_item_key);
133 Raise;
134 end;
135 --
136 -- ---------------------------------------------------------------------------
137 -- ---------------<Get_Requestor_Person_Id> ----------------------------------
138 -- ---------------------------------------------------------------------------
139 function get_requestor_person_id( p_transaction_id in varchar2) return varchar2 is
140 cursor csr_mgr (c_person_id in number) is
141 select supervisor_id
142 from per_all_assignments_f
143 where person_id = c_person_id
144 and sysdate between effective_start_date and effective_end_date;
145 --
146 l_supervisor_id number(18);
147 --
148 l_proc varchar2(72);
149 begin
150 l_proc := g_package||'get_requestor_person_id';
151 hr_utility.set_location('Entering:'||l_proc, 5);
152 populate_txn_details(p_transaction_id);
153 open csr_mgr(g_person_id);
154 fetch csr_mgr into l_supervisor_id;
155 close csr_mgr ;
156
157 hr_utility.set_location(' Leaving:'||l_proc, 10);
158 return g_person_id;
159
160 Exception
161 WHEN others THEN
162 wf_core.context(g_package,'.get_final_approver',g_item_type,g_item_key);
163 Raise;
164 end;
165
166
167 END; -- Package Body PQH_AME_UTILS