[Home] [Help]
PACKAGE BODY: APPS.CN_WF_PMT_PKG
Source
1 PACKAGE BODY CN_WF_PMT_PKG as
2 -- $Header: cnwfpmtb.pls 120.4 2006/02/10 00:06:42 fmburu ship $ --CREATE OR REPLACE
3 --
4 -- Procedure
5 -- StartProcess
6 --
7 -- Description
8
9 -- Initiate workflow
10 -- IN
11 -- RequestorUsername -Requestor Username from callling application
12 -- ProcessOwner - Process Owner Username from calling application
13 -- Workflowprocess - Workflow process to run.
14 --
15 G_last_updated_by NUMBER := fnd_global.user_id;
16 G_last_update_login NUMBER := fnd_global.login_id;
17 procedure startprocess
18 ( p_posting_detail_id in number,
19 p_RequestorUsername in varchar2,
20 p_ProcessOwner in varchar2,
21 p_WorkflowProcess in varchar2,
22 p_Item_Type in varchar2 ) is
23 --+
24 --+
25 ItemKey varchar2(240) := p_posting_detail_id;
26 ItemUserKey varchar2(80) := p_posting_detail_id;
27 --+
28
29
30 -- changes for bug#2568937
31 CURSOR get_details IS
32 SELECT cnpt.commission_header_id commission_header_id, cnpt.quota_id quota_id, pr.payrun_mode
33 FROM cn_payment_transactions_all cnpt, cn_payruns_all pr
34 WHERE cnpt.payment_transaction_id = p_posting_detail_id
35 AND cnpt.payrun_id = pr.payrun_id ;
36
37 -- added new cursor for for bug#2568937 start
38 CURSOR get_revenue_class(l_commission_header_id NUMBER) IS
39 SELECT revenue_class_id revenue_class_id
40 FROM cn_commission_headers_all cnch
41 WHERE cnch.commission_header_id = l_commission_header_id;
42 -- added new cursor for for bug#2568937 end
43
44 CURSOR get_accgen_type IS
45 SELECT nvl (payables_ccid_level, 'CUSTOM')
46 FROM cn_repositories_all rp, cn_payment_transactions_all trxn
47 WHERE trxn.org_id = rp.org_id
48 AND trxn.payment_transaction_id = p_posting_detail_id ;
49
50
51 l_accgen_type cn_repositories.payables_ccid_level%type;
52
53 l_revenue_class_id cn_commission_headers.revenue_class_id%type;
54 l_profile_value VArchar2(01);
55
56 begin
57 --+
58 -- Start Process :
59 --+
60 --Changed from 'dd-mon-yyyy hh24:mi:ss' from 'dd-mon-yyyy hh24:mi:sssss' to have more control.
61 SELECT p_posting_detail_id ||'--'|| to_char(sysdate, 'dd-mon-yyyy hh24:mi:sssss')
62 INTO itemkey
63 FROM dual;
64
65 open get_accgen_type;
66 fetch get_accgen_type into l_accgen_type;
67 close get_accgen_type;
68
69 wf_engine.CreateProcess( ItemType => p_Item_Type,
70 ItemKey => ItemKey,
71 process => p_WorkflowProcess );
72
73 wf_engine.SetItemUserKey ( ItemType => p_Item_Type,
74 ItemKey => ItemKey,
75 UserKey => ItemUserKey);
76
77 FOR i IN get_details LOOP
78
79 l_profile_value := i.payrun_mode ;
80
81 -- changes for bug#2568937
82 IF l_profile_value = 'Y' THEN
83 OPEN get_revenue_class(i.commission_header_id);
84 FETCH get_revenue_class into l_revenue_class_id;
85 CLOSE get_revenue_class;
86 END IF;
87
88
89
90
91 wf_engine.SetItemAttrNumber ( itemtype => p_item_type,
92 itemkey => itemkey,
93 aname => 'POSTING_DETAIL_ID',
94 avalue => p_posting_detail_id);
95
96 --+
97 wf_engine.SetItemAttrText ( itemtype => p_item_type,
98 itemkey => itemkey,
99 aname => 'QUOTA_ID',
100 avalue => i.quota_id);
101
102 --+
103 -- changes for bug#2568937
104 IF l_profile_value = 'Y' THEN
105
106 wf_engine.SetItemAttrText ( itemtype => p_item_type,
107 itemkey => itemkey,
108 aname => 'REVENUE_CLASS_ID',
109 avalue => l_revenue_class_id);
110
111 END IF;
112 --+
113 wf_engine.SetItemAttrText ( itemtype => p_item_type,
114 itemkey => itemkey,
115 aname => 'ACCGEN_TYPE',
116 avalue => l_accgen_type);
117 -- changes for bug#2568937
118 IF l_profile_value = 'Y' THEN
119
120 -- Added KS
121 wf_engine.SetItemAttrText ( itemtype => p_item_type,
122 itemkey => itemkey,
123 aname => 'COMMISSION_HEADER_ID',
124 avalue => i.commission_header_id);
125 END IF;
126
127 END LOOP;
128
129
130 wf_engine.SetItemOwner ( itemtype => p_item_type,
131 itemkey => itemkey,
132 owner => p_ProcessOwner );
133
134 --+
135
136 wf_engine.StartProcess( itemtype => p_item_type,
137 itemkey => itemkey );
138
139 --+
140 exception
141 when others then
142 --+
143 wf_core.context('CNACCGEN','CNACCGEN',p_posting_detail_id);
144 raise;
145 --+
146 end StartProcess;
147 --
148 -- Procedure
149 -- selector
150 --
151 -- Description
152 -- Determine which process to run
153 -- IN
154 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
155 -- itemkey - A string generated from the application object's primary key.
156 -- actid - The function activity(instance id).
157 -- funcmode - Run/Cancel/Timeout
158 -- OUT
159 -- resultout - Name of workflow process to run
160 --
161 procedure selector
162 ( itemtype in varchar2,
163 itemkey in varchar2,
164 actid in number,
165 funcmode in varchar2,
166 resultout out nocopy varchar2 ) is
167 --+
168 begin
169 --+
170 -- RUN mode - normal process execution
171 --+
172 if (funcmode = 'RUN') then
173 --+
174 -- Return process to run
175 --+
176 resultout := 'CNACCGEN';
177 return;
178 end if;
179
180 --+
181 exception
182 when others then
183 wf_core.context('CNACCGEN','Selector',itemtype,itemkey,actid,funcmode);
184 raise;
185 end selector;
186
187 -- update_trx_ccid
188 -- Update the transaction with the ccid
189 -- IN
190 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
191 -- itemkey - A string generated from the application object's primary key.
192 -- actid - The function activity(instance id).
193 -- funcmode - Run/Cancel
194 -- OUT
195 -- Resultout - 'COMPLETE'
196 --
197
198
199 procedure update_trx_ccid
200 ( itemtype in varchar2,
201 itemkey in varchar2,
202 actid in number,
203 funcmode in varchar2,
204 resultout out nocopy varchar2 ) is
205
206 l_return_status VARCHAR2(1);
207 l_msg_count NUMBER;
208 l_msg_data VARCHAR2(2000);
209 l_expense_ccid cn_payment_transactions.expense_ccid%type;
210 l_liability_ccid cn_payment_transactions.liability_ccid%type;
211 l_posting_detail_id cn_payment_transactions.payment_transaction_id%type;
212
213 --+
214 --+
215 begin
216 --+
217 -- RUN mode - normal process execution
218 --+
219 l_expense_ccid := wf_engine.GetItemAttrNumber( itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'EXPENSE_CCID');
222 l_liability_ccid := wf_engine.GetItemAttrNumber( itemtype => itemtype,
223 itemkey => itemkey,
224 aname => 'LIABILITY_CCID');
225 l_posting_detail_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
226 itemkey => itemkey,
227 aname => 'POSTING_DETAIL_ID');
228 if (funcmode = 'RUN') then
229 update cn_payment_transactions_all
230 set expense_ccid = l_expense_ccid,
231 liability_ccid = l_liability_ccid,
232 --Update WHO columns for bug 3866105 (the same as 11.5.8 bug 3854249, 11.5.10 3866113) by Julia Huang on 9/1/04.
233 LAST_UPDATE_DATE = SYSDATE,
234 LAST_UPDATED_BY = G_LAST_UPDATED_BY,
235 LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN
236 where payment_transaction_id = l_posting_detail_id;
237 resultout := 'COMPLETE:T';
238 end if;
239 --+
240 -- CANCEL mode - activity 'compensation'
241 --+
242 if (funcmode = 'CANCEL') then
243 --+
244 resultout := 'COMPLETE:';
245 return;
246 --+
247 end if;
248 --+
249 -- TIMEOUT mode
250 --+
251 if (funcmode = 'TIMEOUT') then
252 resultout := 'COMPLETE:';
253 return;
254 end if;
255
256 exception
257 when FND_API.G_EXC_ERROR THEN
258 wf_core.context('CNACCGEN','RECORDNTF',itemtype,itemkey,actid,funcmode);
259 raise;
260 when others then
261 wf_core.context('CNACCGEN','RECORDNTF',itemtype,itemkey,actid,funcmode);
262 raise;
263 end;
264
265 procedure get_ccid
266 ( itemtype in varchar2,
267 itemkey in varchar2,
268 actid in number,
269 funcmode in varchar2,
270 resultout out nocopy varchar2 )
271 is
272
273 l_accgen_type varchar2(30);
274 l_expense_ccid number;
275
276 l_liability_ccid number;
277 l_ccid_identifier number;
278
279 l_cached_org_id integer;
280 l_cached_org_append varchar2(100);
281
282 l_rule_id NUMBER;
283
284 cursor ruleset_cur ( p_commission_header_id number ) is
285 select ruleset_id
286 from cn_rulesets_all rs, cn_commission_headers_all ch
287 where processed_date between start_date and end_date
288 and commission_header_id = p_commission_header_id
289 and rs.org_id = ch.org_id
290 and module_type = 'ACCGEN' ;
291
292 l_ruleset_id cn_rulesets.ruleset_id%type;
293
294 l_stmt varchar2(4000);
295 l_profile_value varchar2(01);
296 l_payment_transaction_id cn_payment_transactions.payment_transaction_id%TYPE;
297 l_itc cn_payment_transactions.incentive_type_code%TYPE;
298
299 begin
300 l_accgen_type := wf_engine.GetItemAttrText( itemtype => itemtype,
301 itemkey => itemkey,
302 aname => 'ACCGEN_TYPE');
303
304 --Bug 3866105 (the same as 11.5.8 bug 3854249, 11.5.10 3866113) by Julia Huang on 9/1/04.
305 l_payment_transaction_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'POSTING_DETAIL_ID');
308
309 SELECT pr.payrun_mode, pr.org_id , cnpt.incentive_type_code
310 INTO l_profile_value, l_cached_org_id, l_itc
311 FROM cn_payment_transactions_all cnpt, cn_payruns_all pr
312 WHERE cnpt.payment_transaction_id = l_payment_transaction_id
313 AND cnpt.payrun_id = pr.payrun_id ;
314
315
316 -- changes for bug#2568937
317 --Bug 3866105 (the same as 11.5.8 bug 3854249, 11.5.10 3866113) by Julia Huang on 9/1/04.
318 IF l_accgen_type = 'REVCLS' AND l_profile_value = 'Y' AND l_itc IN ('COMMISSION','BONUS') THEN
319
320 l_ccid_identifier := wf_engine.GetItemAttrNumber( itemtype => itemtype,itemkey => itemkey,aname => 'REVENUE_CLASS_ID');
321 SELECT expense_account_id, liability_account_id
322 INTO l_expense_ccid, l_liability_ccid
323 FROM cn_revenue_classes_all
324 WHERE revenue_class_id = l_ccid_identifier;
325
326 ELSIF l_accgen_type = 'PLANELEM' THEN
327 l_ccid_identifier := wf_engine.GetItemAttrNumber( itemtype => itemtype,
328 itemkey => itemkey,
329 aname => 'QUOTA_ID');
330 SELECT expense_account_id, liability_account_id
331 INTO l_expense_ccid, l_liability_ccid
332 FROM cn_quotas_all
333 WHERE quota_id = l_ccid_identifier;
334
335 -- changes for bug#2568937
336 --Bug 3866105 (the same as 11.5.8 bug 3854249, 11.5.10 3866113) by Julia Huang on 9/1/04.
337 ELSIF l_accgen_type = 'CLASSIFICATION' AND l_profile_value = 'Y' AND l_itc IN ('COMMISSION','BONUS') THEN
338 l_ccid_identifier := wf_engine.getitemattrnumber
339 ( itemtype => itemtype,
340 itemkey => itemkey,
341 aname => 'COMMISSION_HEADER_ID');
342 IF l_cached_org_id = -99 then
343 l_cached_org_append := '_MINUS99';
344 ELSE
345 l_cached_org_append := '_' || l_cached_org_id;
346 END IF;
347
348 open ruleset_cur (l_ccid_identifier);
349 fetch ruleset_cur into l_ruleset_id;
350 close ruleset_cur;
351
352 l_stmt := 'BEGIN ' ||
353 ' cn_clsfn_' || To_char(Abs(l_ruleset_id))
354 || l_cached_org_append || '.classify_'
355 || To_char(Abs(l_ruleset_id)) ||
356 '( :commission_header_id, :expense_ccid, :liability_ccid ); '
357 || 'END;';
358
359 EXECUTE IMMEDIATE l_stmt
360 USING l_ccid_identifier,
361 OUT l_expense_ccid, OUT l_liability_ccid;
362
363 --Bug 3866105 (the same as 11.5.8 bug 3854249, 11.5.10 3866113) by Julia Huang on 9/1/04.
364 ELSIF l_accgen_type IN ('CLASSIFICATION','REVCLS') AND l_profile_value = 'Y'
365 AND l_itc IN ('MANUAL_PAY_ADJ','PMTPLN','PMTPLN_REC')
366 THEN
367 l_ccid_identifier := wf_engine.GetItemAttrNumber( itemtype => itemtype,itemkey => itemkey,aname => 'QUOTA_ID');
368
369 SELECT expense_account_id, liability_account_id
370 INTO l_expense_ccid, l_liability_ccid
371 FROM cn_quotas_all
372 WHERE quota_id = l_ccid_identifier;
373
374 END IF;
375
376 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
377 itemkey => itemkey,
378 aname => 'EXPENSE_CCID',
379 avalue => l_expense_ccid);
380 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
381 itemkey => itemkey,
382 aname => 'LIABILITY_CCID',
383 avalue => l_liability_ccid);
384
385 end;
386
387 -- Procedure
388 -- Get_acc_gen_type
389 --
390 -- Description dummy procedure
391 --
392 -- IN
393 -- itemkey
394 --
395 procedure get_acc_gen_type( itemtype in varchar2,
396 itemkey in varchar2,
397 actid in number,
398 funcmode in varchar2,
399 resultout out nocopy varchar2 ) is
400
401 begin
402 resultout := wf_engine.GetItemAttrText( itemtype => itemtype,
403 itemkey => itemkey,
404 aname => 'ACCGEN_TYPE');
405 null;
406 end;
407
408
409
410 end CN_WF_PMT_PKG;