1 package body pay_kr_wf_submit_program_pkg AS
2 /* $Header: pykrwfsp.pkb 120.2 2006/02/17 01:14:25 mmark noship $ */
3
4 g_debug constant boolean := hr_utility.debug_enabled ;
5 procedure submit_program (
6 itemtype in varchar2, -- the name of the item type
7 itemkey in varchar2, -- the unique item key
8 actid in number, -- the activity id
9 funcmode in varchar2, -- mode
10 resultout in out nocopy varchar2 -- the output
11 ) is
12 --
13 -- Bug 4859652
14 cursor csr_payroll_action_id(
15 p_prepayments_req_id in number,
16 p_business_group_id in pay_payroll_actions.business_group_id%type,
17 p_payroll_id in pay_payroll_actions.payroll_id%type
18 ) is
19 select payroll_action_id
20 from pay_payroll_actions
21 where request_id = p_prepayments_req_id
22 and business_group_id = p_business_group_id
23 and payroll_id = p_payroll_id
24 and action_type = 'P' ;
25 -- End of 4859652
26 --
27 cursor csr_run_type_period_name(p_run_type_period in number) is
28 select run_type_name||'-'||ptp.period_name||'-'||ppa.payroll_action_id run_type_period_name
29 from pay_payroll_actions ppa,
30 pay_payroll_actions rppa,
31 pay_assignment_actions paa,
32 pay_assignment_actions rpaa,
33 pay_action_interlocks pai,
34 pay_run_types_f prt,
35 per_time_periods ptp
36 where ppa.payroll_action_id = p_run_type_period
37 and paa.payroll_action_id = ppa.payroll_action_id
38 and ppa.action_type in ('P','U')
39 and ppa.action_status = 'C'
40 and rppa.action_type in ('Q','R')
41 and rppa.action_status = 'C'
42 and rpaa.payroll_action_id = rppa.payroll_action_id
43 and rpaa.assignment_id = paa.assignment_id
44 and pai.locking_action_id = paa.assignment_action_id
45 and pai.locked_action_id = rpaa.assignment_action_id
46 and ppa.effective_date >= rppa.effective_date
47 and rpaa.run_type_id = prt.run_type_id
48 and rppa.time_period_id = ptp.time_period_id
49 and ppa.payroll_action_id = paa.payroll_action_id
50 and rpaa.assignment_action_id = (select max(paa_locked.assignment_action_id)
51 from pay_assignment_actions paa_locked,
52 pay_action_interlocks pai_locking
53 where pai_locking.locking_action_id =
54 (select max(paa1.assignment_action_id)
55 from pay_assignment_actions paa1
56 where paa1.payroll_action_id = ppa.payroll_action_Id)
57 and paa_locked.assignment_action_id = pai_locking.locked_action_id)
58 and ppa.effective_date between prt.effective_start_date and prt.effective_end_date
59 and rppa.effective_date between ptp.start_date and ptp.end_date;
60 --
61 l_prepayments_req_id pay_payroll_actions.request_id%TYPE ;
62 l_business_group_id pay_payroll_actions.business_group_id%type ; -- Bug 4859652
63 l_payroll_id pay_payroll_actions.payroll_id%type ; -- Bug 4859652
64 --
65 begin
66 --
67 if g_debug then
68 hr_utility.trace('Into submit_program.') ;
69 end if ;
70 --
71 if wf_engine.getActivityAttrText(itemtype, itemkey, actid, 'PROGRAM') = 'PAYKRSOE'
72 and wf_engine.getItemAttrText(itemtype, itemkey, 'TO_RUN_PREPAYMENTS') = 'Y'
73 and wf_engine.getItemAttrText(itemtype, itemkey, 'RUNTYPEPERIODID') is null
74 then
75 --
76 if g_debug then
77 hr_utility.trace('Getting payroll action id from PrePayments to run Payslip (Korea)');
78 end if ;
79 --
80 -- Get payroll action id from PrePayments to run Payslip (Korea)
81 --
82 l_prepayments_req_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'PREPAYMENTS_REQ_ID_NUM');
83 -- Bug 4859652
84 l_business_group_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'BUSINESS_GROUP_NUMBER') ;
85 l_payroll_id := wf_engine.getItemAttrNumber(itemtype, itemkey, 'PAYROLL_ID') ;
86 -- End of 4859652
87 --
88 if g_debug then
89 hr_utility.trace('Got PrePayments request id = ' || l_prepayments_req_id );
90 end if ;
91 --
92 for rec in csr_payroll_action_id(
93 p_prepayments_req_id => l_prepayments_req_id,
94 p_business_group_id => l_business_group_id, -- Bug 4859652
95 p_payroll_id => l_payroll_id -- Bug 4859652
96 ) loop
97 --
98 if g_debug then
99 hr_utility.trace('Set run type period id in workflow = payroll action id = ' || to_char(rec.payroll_action_id) ) ;
100 end if ;
101 --
102 -- set the payroll action id as the run type period in workflow
103 --
104 wf_engine.setItemAttrText(itemtype, itemkey, 'RUNTYPEPERIODID', to_char(rec.payroll_action_id));
105 --
106 for rec1 in csr_run_type_period_name(rec.payroll_action_id) loop
107 wf_engine.setItemAttrText(itemtype, itemkey, 'RUN_TYPE_PERIOD_NAME_DISP', rec1.run_type_period_name);
108 end loop;
109 end loop ;
110 --
111 end if ;
112 --
113 if g_debug then
114 hr_utility.trace('Now calling fnd_wf_standard.executeconcprogram for program ' || wf_engine.getActivityAttrText(itemtype, itemkey, actid, 'PROGRAM') ) ;
115 end if ;
116 --
117 fnd_wf_standard.executeconcprogram(itemtype, itemkey, actid, funcmode, resultout) ;
118 --
119 if g_debug then
120 hr_utility.trace('Called fnd_wf_standard.executeconcprogram. Quiting submit_program.') ;
121 end if ;
122 --
123 exception
124 when others then
125 if g_debug then
126 hr_utility.trace(sqlerrm);
127 end if ;
128 resultout := 'ERROR:';
129 end submit_program ;
130 ----------------------------------------------------------------------------------------------------------------------------------------
131 procedure check_run_flags
132 (
133 p_itemtype in varchar2,
134 p_itemkey in varchar2,
135 p_actid in number,
136 p_funcmode in varchar2,
137 p_result in out nocopy varchar2
138 )
139 is
140 l_user_id number ;
141 l_resp_id number ;
142 l_resp_appl_id number ;
143 l_org_id number ;
144 l_resultout varchar2(80) ;
145 l_security_group_id number ;
146 l_per_security_id number ;
147
148 begin
149 if g_debug then
150 hr_utility.trace('In check_run_flags') ;
151 end if ;
152
153 if (p_funcmode = 'RUN') then
154 wf_standard.compare(
155 p_itemtype,
156 p_itemkey,
157 p_actid,
158 p_funcmode,
159 l_resultout
160 ) ;
161
162 if (l_resultout = 'COMPLETE:EQ') then
163 p_result := 'COMPLETE:RUN';
164 elsif ((l_resultout = 'COMPLETE:LT') or
165 (l_resultout = 'COMPLETE:GT') or
166 (l_resultout = 'COMPLETE:NULL'))
167 then
168 p_result := 'COMPLETE:SKIP';
169 end if ;
170
171 return ;
172
173 else
174 p_result := 'COMPLETE:SKIP';
175 return ;
176 end if ;
177 exception
178 when others then
179 null ;
180 if g_debug then
181 wf_core.context('pay_kr_wf_submit_program_pkg', 'check_run_flags', p_itemtype, p_itemkey, p_actid, p_funcmode) ;
182 end if ;
183 p_result := 'ERROR:';
184 end check_run_flags ;
185 ----------------------------------------------------------------------------------------------------------------------------------------
186 procedure get_assignment_count(
187 p_itemtype in varchar2,
188 p_itemkey in varchar2,
189 p_actid in number,
190 p_funcmode in varchar2,
191 p_result in out nocopy varchar2
192 ) is
193 ln_req_id number(9) ;
194 ln_complete number(9);
195 ln_error number(9);
196 ln_unprocessed number(9);
197 lb_to_get_counts boolean ;
198 l_payroll_id pay_payroll_actions.payroll_id%type ; -- Bug 5042942
199 l_action_completed boolean;
200 --
201 cursor asg_info_cur
202 is
203 select count(paa.assignment_action_id) ASG_COUNT,
204 paa.action_status ASG_STATUS
205 from pay_assignment_actions paa,
206 pay_payroll_actions ppa,
207 per_business_groups pbg
208 where paa.payroll_action_id = ppa.payroll_action_id
209 and ppa.request_id = ln_req_id
210 and ppa.business_group_id = pbg.business_group_id
211 and ppa.payroll_id = l_payroll_id -- Bug 5042942
212 and ppa.effective_date between pbg.date_from and nvl(pbg.date_to, ppa.effective_date) -- Bug 5042942
213 and ppa.action_type = 'R' -- Bug 5042942
214 and paa.source_action_id is null
215 and paa.run_type_id is null
216 group by pbg.name, paa.action_status;
217 --
218
219 begin
220
221 /*
222 * Get the request ids if we are running the monthly or bonus payroll processes, and
223 * use the request ids to get the number of assignments processed
224 */
225 lb_to_get_counts := false ; -- initialize to false
226 if wf_engine.getActivityAttrText(p_itemtype, p_itemkey, p_actid, 'PROGRAM') = 'PAYKRMTH' then -- we're running monthly
227 ln_req_id := wf_engine.getItemAttrNumber(p_itemtype, p_itemkey, 'MONTHLY_PAYROLL_REQ_ID_NUM');
228 --
229 if g_debug then
230 hr_utility.trace('Got Monthly Payroll request id = ' || ln_req_id );
231 end if ;
232 --
233 lb_to_get_counts := true ; -- will need to get assignment count information
234 elsif wf_engine.getActivityAttrText(p_itemtype, p_itemkey, p_actid, 'PROGRAM') = 'PAYKRBON' then -- we're running bonus
235 ln_req_id := wf_engine.getItemAttrNumber(p_itemtype, p_itemkey, 'BONUS_PAYROLL_REQ_ID_NUM');
236 --
237 if g_debug then
238 hr_utility.trace('Got Bonus Payroll request id = ' || ln_req_id );
239 end if ;
240 --
241 lb_to_get_counts := true ; -- will need to get assignment count information
242
243 end if ;
244 -- now actually get the assignment counts, if running monthly/bonus
245 if lb_to_get_counts = true then
246 -- initialize counters
247 ln_complete := 0;
248 ln_error := 0;
249 ln_unprocessed := 0;
250 l_payroll_id := wf_engine.getItemAttrNumber(p_itemtype, p_itemkey, 'PAYROLL_ID') ; -- Bug 5042942
251 for asg_info_rec in asg_info_cur loop
252 if asg_info_rec.ASG_STATUS = 'C' then
253 ln_complete := asg_info_rec.ASG_COUNT;
254 elsif asg_info_rec.ASG_STATUS = 'E' then
255 ln_error := asg_info_rec.ASG_COUNT;
256 elsif asg_info_rec.ASG_STATUS = 'U' then
257 ln_unprocessed := asg_info_rec.ASG_COUNT;
258 end if;
259 end loop ;
260
261 -- set corresponding item attributes
262 wf_engine.setItemAttrNumber(p_itemtype, p_itemkey, 'SUCCESSFULLY_PROCESSED_COUNT', ln_complete) ;
263 wf_engine.setItemAttrNumber(p_itemtype, p_itemkey, 'ERRORED_PROCESSED_COUNT', ln_error) ;
264 wf_engine.setItemAttrNumber(p_itemtype, p_itemkey, 'UN_PROCESSED_COUNT', ln_unprocessed) ;
265 end if ;
266 --
267 p_result := 'COMPLETE' ;
268 --
269 exception
270 when others then
271 if g_debug then
272 hr_utility.trace(sqlerrm);
273 end if ;
274 p_result := 'ERROR:';
275 end get_assignment_count ;
276
277 end pay_kr_wf_submit_program_pkg ;
[Home] [Help]