eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PQH_BDGT_REALLOC_UTILITY

Source


1 Package body pqh_bdgt_realloc_utility as
2 /* $Header: pqbreutl.pkb 120.2 2006/02/03 14:18:03 deenath noship $ */
3 
4 g_package  Varchar2(30) := ' pqh_bdgt_realloc_utility.';
5 g_warning  Varchar2(30);
6 --
7 ---------------------------get_entity_name-----------------------------
8 --
9 
10 FUNCTION get_entity_name
11 (
12  p_entity_id              IN    pqh_bdgt_pool_realloctions.entity_id%TYPE,
13  p_entity_type            IN    pqh_budget_pools.entity_type%TYPE
14 )
15 RETURN  VARCHAR IS
16 /*
17   This function will return the Entity Name.
18 */
19 
20 BEGIN
21 
22    If p_entity_type = 'POSITION' then
23       return(hr_general. DECODE_POSITION_LATEST_NAME (p_entity_id ));
24    elsif p_entity_type = 'JOB' then
25      return(hr_general.DECODE_JOB(p_entity_id));
26    elsif p_entity_type = 'ORGANIZATION' then
27      return(hr_general.DECODE_ORGANIZATION(p_entity_id ));
28    elsif p_entity_type = 'GRADE' then
29      return(hr_general.DECODE_GRADE(p_entity_id ));
30    end if;
31 END get_entity_name;
32 --
33 ---------------------------GET_PRD_REALLOC_RESERVED_AMT-----------------------------
34 --
35 
36 FUNCTION GET_PRD_REALLOC_RESERVED_AMT
37 (
38  p_budget_period_id       IN    pqh_budget_periods.budget_period_id%TYPE,
39  p_budget_unit_id         IN    pqh_budgets.budget_unit1_id%TYPE,
40  p_transaction_type       IN   pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD',
41  p_approval_status        IN    varchar2,
42  p_amount_type            IN    varchar2 ,
43  p_entity_type            IN   varchar2,
44  p_entity_id              IN   NUMBER,
45  p_start_date             IN   DATE,
46  p_end_date               IN   DATE
47 )
48 RETURN  NUMBER IS
49 /*
50 
51   This function will return Already Donated or Unapproved Donations amount for
52   Or Received amount a given budged period.
53   For Already Donated : Approval Type is 'A'
54   For Unapproved Donations  : Approval Type is 'P'
55   Default for Approval type is 'P'
56 Transaction Type :
57 ==================
58 DD - Donor Details
59 RD - Receiver Details
60 Default for Transaction Type is 'DD'
61 
62 Amount Type :
63 ==============
64 R - Reallocation Amount
65 RV - Reserved Amount
66 */
67 l_realloc_amt NUMBER := 0;
68 l_reserved_amt NUMBER := 0;
69 /*for donors amount to be picked based on budget period_id */
70 CURSOR csr_donor_amt(p_approval_status IN Varchar2) IS
71   SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
72   FROM   pqh_budget_pools fld,
73          pqh_budget_pools trnx,
74          pqh_bdgt_pool_realloctions trnxdtl,
75          pqh_bdgt_pool_realloctions trnxamt
76   WHERE  fld.pool_id = trnx.parent_pool_id
77    AND   fld.approval_status=p_approval_status
78    AND   fld.budget_unit_id = p_budget_unit_id
79    AND   fld.entity_type = p_entity_type
80    AND   trnx.pool_id = trnxdtl.pool_id
81    AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
82    AND   trnxamt.budget_period_id = p_budget_period_id
83    AND   trnxamt.transaction_type = p_transaction_type;
84 
85 /* for receivers the amount to be picked up based on entity/start date/end date */
86 CURSOR csr_receiver_amt(p_approval_status IN varchar2) IS
87   SELECT NVL(sum(trnxamt.reallocation_amt),0), NVL(sum(trnxamt.reserved_amt),0)
88   FROM   pqh_budget_pools fld,
89          pqh_budget_pools trnx,
90          pqh_bdgt_pool_realloctions trnxdtl,
91          pqh_bdgt_pool_realloctions trnxamt
92   WHERE  fld.pool_id = trnx.parent_pool_id
93    AND   fld.budget_unit_id = p_budget_unit_id
94    AND   fld.entity_type = p_entity_type
95    AND   trnx.pool_id = trnxdtl.pool_id
96    AND   fld.approval_status = p_approval_status
97    AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
98    AND   trnxamt.transaction_type = p_transaction_type
99    AND   trnxamt.entity_id = p_entity_id
100    AND   NVL(trnxamt.start_date,to_date('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date
101    AND   NVL(trnxamt.end_date,to_date('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date;
102 
103 BEGIN
104   -- get unit amt
105   IF p_amount_type = 'RV' THEN
106     IF p_transaction_type = 'DD' THEN
107       OPEN csr_donor_amt('A');
108       FETCH csr_donor_amt INTO l_realloc_amt, l_reserved_amt;
109       CLOSE csr_donor_amt;
110     ELSIF p_transaction_type = 'RD' THEN
111       OPEN csr_receiver_amt('A');
112       FETCH csr_receiver_amt INTO l_realloc_amt, l_reserved_amt;
113       CLOSE csr_receiver_amt;
114     END IF;
115   ELSIF p_amount_type = 'R' THEN
116     IF p_transaction_type = 'DD' THEN
117       OPEN csr_donor_amt(p_approval_status);
118       FETCH csr_donor_amt INTO l_realloc_amt, l_reserved_amt;
119       CLOSE csr_donor_amt;
120     ELSIF p_transaction_type = 'RD' THEN
121       OPEN csr_receiver_amt(p_approval_status);
122       FETCH csr_receiver_amt INTO l_realloc_amt, l_reserved_amt;
123       CLOSE csr_receiver_amt;
124     END IF;
125   END IF;
126   If p_amount_type = 'R' then
127      RETURN l_realloc_amt;
128   Elsif p_amount_type = 'RV' then
129      RETURN l_reserved_amt;
130   End if;
131 
132 EXCEPTION
133   WHEN OTHERS THEN
134     l_realloc_amt := 0;
135     l_reserved_amt := 0;
136     return(0);
137 END GET_PRD_REALLOC_RESERVED_AMT;
138 
139 --
140 ---------------------------GET_DTL_REALLOC_RESERVED_AMT-----------------------------
141 --
142 
143 FUNCTION GET_DTL_REALLOC_RESERVED_AMT
144 (
145  p_budget_detail_id       IN    pqh_budget_details.budget_detail_id%TYPE,
146  p_budget_unit_id         IN    pqh_budgets.budget_unit1_id%TYPE,
147  p_transaction_type       IN   pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD',
148  p_approval_status        IN    varchar2,
149  p_amount_type            IN    varchar2,
150  p_entity_type            IN    varchar2,
151  p_entity_id              IN    number,
152  p_start_date             IN    date,
153  p_end_date               IN    date
154 )
155 RETURN  NUMBER IS
156 /*
157 
158   This function will return Already Donated or Unapproved Donations amount for
159   Or Received amount of a given budget detail.
160   For Already Donated : Approval Type is 'A'
161   For Unapproved Donations  : Approval Type is 'P'
162   Default for Approval type is 'P'
163 Transaction Type :
164 ==================
165 DD - Donor Details
166 RD - Receiver Details
167 Default for Transaction Type is 'DD'
168 
169 Amount Type :
170 ==============
171 R - Reallocation Amount
172 RV - Reserved Amount
173 */
174 l_period_id NUMBER;
175 
176 l_budget_unit_id NUMBER := p_budget_unit_id;
177 l_transaction_type VARCHAR2(2) := p_transaction_type;
178 l_approval_status VARCHAR2(1) := p_approval_status;
179 l_amount_type VARCHAR2(2) := p_amount_type;
180 
181 
182 CURSOR csr_donor_amt(p_approval_status in varchar2) IS
183   SELECT  NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
184   FROM    pqh_budget_pools fld,
185           pqh_budget_pools trnx,
186           pqh_bdgt_pool_realloctions trnxdtl,
187           pqh_bdgt_pool_realloctions trnxamt
188   WHERE   fld.budget_unit_id = p_budget_unit_id
189     AND   fld.entity_type = p_entity_type
190     AND   fld.approval_status = p_approval_status
191     AND   fld.pool_id = trnx.parent_pool_id
192     AND   trnx.pool_id = trnxdtl.pool_id
193     AND   trnxdtl.budget_detail_id = p_budget_detail_id
194     AND   trnxamt.transaction_type = 'DD'
195     AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id;
196 
197 CURSOR csr_receiver_amt(p_approval_status in varchar2) IS
198   SELECT  NVL(SUM(trnxamt.reallocation_amt),0), NVL(SUM(trnxamt.reserved_amt),0)
199   FROM    pqh_budget_pools fld,
200           pqh_budget_pools trnx,
201           pqh_bdgt_pool_realloctions trnxdtl,
202           pqh_bdgt_pool_realloctions trnxamt
203   WHERE   fld.budget_unit_id = p_budget_unit_id
204     AND   fld.entity_type = p_entity_type
205     AND   fld.approval_status = p_approval_status
206     AND   fld.pool_id = trnx.parent_pool_id
207     AND   trnx.pool_id = trnxdtl.pool_id
208     AND   trnxdtl.reallocation_id = trnxamt.txn_detail_id
209     AND   trnxdtl.entity_id = p_entity_id
210     AND   trnxamt.transaction_type = 'RD'
211     AND   NVL(trnxamt.start_date,TO_DATE('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date
212     AND   NVL(trnxamt.end_date,TO_DATE('31/12/4712','dd/mm/RRRR')) BETWEEN p_start_date and p_end_date;
213  l_reserved_amt NUMBER := 0;
214  l_realloc_amt  NUMBER := 0;
215 BEGIN
216  IF p_transaction_type = 'DD' THEN
217     IF p_amount_type = 'RV' THEN
218        OPEN csr_donor_amt('A');
219        FETCH csr_donor_amt INTO l_realloc_amt,l_reserved_amt;
220        CLOSE csr_donor_amt;
221     ELSE
222        OPEN csr_donor_amt(p_approval_status);
223        FETCH csr_donor_amt INTO l_realloc_amt,l_reserved_amt;
224        CLOSE csr_donor_amt;
225     END IF;
226  ELSIF p_transaction_type = 'RD' THEN
227    IF p_amount_type = 'RV' THEN
228      OPEN csr_receiver_amt('A');
229      FETCH csr_receiver_amt INTO l_realloc_amt, l_reserved_amt;
230      CLOSE csr_receiver_amt;
231    ELSE
232      OPEN csr_receiver_amt(p_approval_status);
233      FETCH csr_receiver_amt INTO l_realloc_amt, l_reserved_amt;
234      CLOSE csr_receiver_amt;
235    END IF;
236  END IF;
237   If p_amount_type = 'R' then
238      RETURN l_realloc_amt;
239   Elsif p_amount_type = 'RV' then
240      RETURN l_reserved_amt;
241   End if;
242 EXCEPTION
243  WHEN OTHERS THEN
244 
245    return (0);
246 
247 end GET_DTL_REALLOC_RESERVED_AMT;
248 
249 --
250 ---------------------------GET_TRNX_LEVEL_TRANS_AMT-----------------------------
251 --
252 
253 
254 FUNCTION GET_TRNX_LEVEL_TRANS_AMT
255 (
256  p_transaction_id         IN    pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
257  p_txn_amt_balance_flag    IN    varchar2
258 ) RETURN  NUMBER IS
259 
260 /*
261 a) Txn Aount  is donor amount or receiver amount whichever is higher. for a balanced txn it will be same.
262 b) Txn Balancce is donor amt - rcvr amt
263 
264 This function returns Transaction/Reallocation Amount and Transaction Balance Amount for a given Transation Id.
265 
266 
267 Txn Amt Balance_flag :
268 ==========================
269 TA - Transaction Amount
270 TB - Transaction Balance
271 
272 
273 */
274 CURSOR csr_tranaction_amt (p_transaction_type in varchar)
275 IS
276 Select nvl(sum(trnxamt.reallocation_amt),0)
277 From pqh_bdgt_pool_realloctions trnxamt,
278 pqh_bdgt_pool_realloctions trnxdtl
279 where trnxamt.txn_detail_id = trnxdtl.reallocation_id
280 and  trnxdtl.pool_id= p_transaction_id
281 and  trnxamt.transaction_type = p_transaction_type;
282 
283 l_donor_txn_amt number;
284 l_receiver_txn_amt number;
285 l_txn_amt number;
286 
287 begin
288 
289 Open csr_tranaction_amt(p_transaction_type => 'DD');
290 fetch csr_tranaction_amt into l_donor_txn_amt;
291 close csr_tranaction_amt;
292 
293 Open csr_tranaction_amt(p_transaction_type => 'RD');
294 fetch csr_tranaction_amt into l_receiver_txn_amt;
295 close csr_tranaction_amt;
296 
297 If p_txn_amt_balance_flag = 'TA' then
298    if l_donor_txn_amt > l_receiver_txn_amt then
299       return (l_donor_txn_amt);
300    else
301       return (l_receiver_txn_amt);
302    end if;
303 End If;
304 
305 If p_txn_amt_balance_flag = 'TB' then
306          return ((l_donor_txn_amt)-(l_receiver_txn_amt));
307 End If;
308 
309 EXCEPTION
310   WHEN OTHERS THEN
311     l_receiver_txn_amt := 0;
312     l_donor_txn_amt := 0;
313     return l_receiver_txn_amt;
314 End GET_TRNX_LEVEL_TRANS_AMT;
315 --
316 ---------------------------GET_FOLDER_LEVEL_TRANS_AMT-----------------------------
317 --
318 
319 FUNCTION GET_FOLDER_LEVEL_TRANS_AMT
320 (
321  p_folder_id             IN    pqh_budget_pools.pool_id%TYPE
322 ) RETURN  NUMBER IS
323 
324 /* This function returns Transaction Amount for a given Folder Id */
325 
326 CURSOR csr_transaction_ids
327 IS
328 Select pool_id
329 From   pqh_budget_pools
330 Where  parent_pool_id= p_folder_id;
331 
332 l_transaction_id number;
333 l_trans_amt number :=0;
334 l_folder_amt number:=0;
335 
336 Begin
337 
338 open csr_transaction_ids;
339 loop
340   fetch csr_transaction_ids into l_transaction_id;
341   exit when csr_transaction_ids%notfound;
342   if l_transaction_id is not null then
343     l_trans_amt := pqh_bdgt_realloc_utility.GET_TRNX_LEVEL_TRANS_AMT
344                      (p_transaction_id => l_transaction_id
345                      ,p_txn_amt_balance_flag => 'TA');
346     l_folder_amt := l_folder_amt + l_trans_amt;
347 
348   end if;
349  end loop;
350  close csr_transaction_ids;
351  return l_folder_amt;
352 
353 EXCEPTION
354   WHEN OTHERS THEN
355     l_folder_amt := 0;
356     return(l_folder_amt);
357 
358 End GET_FOLDER_LEVEL_TRANS_AMT;
359 
360 FUNCTION GET_TRNX_DNR_REVR_COUNT
361 (
362  p_transaction_id             IN    pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
363  p_transaction_type       IN    pqh_bdgt_pool_realloctions.transaction_type%TYPE
364 ) RETURN  NUMBER IS
365 
366 /*
367 This function retuns the number of Donors/Receivers for a given Transaction
368 Transaction Type :
369 ====================
370 D - Donor
371 R - Receiver
372 
373 */
374 
375 CURSOR csr_dnr_revr_count
376 IS
377 Select count(*)
378 From   pqh_bdgt_pool_realloctions
379 Where  pool_id = p_transaction_id
380 And    transaction_type = p_transaction_type;
381 
382 l_count number;
383 
384 Begin
385 
386 Open csr_dnr_revr_count;
387 fetch csr_dnr_revr_count into l_count;
388 close csr_dnr_revr_count;
389 
390 return (nvl(l_count,0) );
391 
392 EXCEPTION
393   WHEN OTHERS THEN
394     l_count := 0;
395     return l_count;
396 
397 End GET_TRNX_DNR_REVR_COUNT;
398 --
399 ---------------------------CHK_RECV_EXISTS-----------------------------
400 --
401 PROCEDURE CHK_RECV_EXISTS
402 (
403 	p_trans_id	IN	pqh_bdgt_pool_realloctions.pool_id%TYPE,
404 	p_entity_id	IN	pqh_bdgt_pool_realloctions.entity_id%TYPE,
405 	p_detail_id	OUT NOCOPY	pqh_bdgt_pool_realloctions.reallocation_id%TYPE
406 )
407 /*
408 This function checks whether a receiver exists in a Transaction or not
409 If it exists then it returns the reallocation_id else returns -1
410 
411 */
412 IS
413 CURSOR csr_trnx_det
414 IS
415 select transaction_id,entity_id,txn_detail_id
416 from pqh_realloc_txn_details_v
417 where transaction_id = p_trans_id
418 and entity_id =p_entity_id
419 and transaction_type = 'R';
420 csr_trnx_det_rec csr_trnx_det%rowtype;
421 
422 BEGIN
423 
424 OPEN csr_trnx_det;
425 
426 FETCH csr_trnx_det into csr_trnx_det_rec;
427 IF(csr_trnx_det%rowcount=0) THEN
428 p_detail_id := -1;
429 ELSE
430 p_detail_id := csr_trnx_det_rec.txn_detail_id;
431 END IF;
432 CLOSE csr_trnx_det;
433 
434 EXCEPTION
435   WHEN OTHERS THEN
436 p_detail_id := 0;
437 
438 End CHK_RECV_EXISTS;
439 --
440 
441 FUNCTION CHK_APPROVED_FOLDER
442 (
443  p_budget_version_id      IN    pqh_budget_pools.budget_version_id%TYPE,
444  p_budget_unit_id         IN    pqh_budget_pools.budget_unit_id%TYPE,
445  p_entity_type            IN    pqh_budget_pools.entity_type%TYPE,
446  p_approval_status        IN    pqh_budget_pools.approval_status%Type) RETURN  NUMBER IS
447 
448 /*
449 Approval Status :
450 ================
451 A - Approved
452 P - Pending
453 
454 This function returns the Budget Folder is Approved or not for a given
455 Budget Version Id, Budget Unit Id, Entity Type ,Approval Status*/
456 
457 CURSOR csr_approved_folder
458 IS
459 Select count(pool_id)
460 From   pqh_budget_pools
461 Where  budget_version_id = p_budget_version_id
462 And    budget_unit_id = p_budget_unit_id
463 And    entity_type = p_entity_type
464 And    approval_status  = p_approval_status;
465 
466 l_count number;
467 
468 Begin
469    Open csr_approved_folder;
470    Fetch csr_approved_folder into l_count;
471    Close csr_approved_folder;
472 
473    If l_count <> 0  Then
474         return 1;
475    Else
476         return 0;
477    End If;
478 
479 EXCEPTION
480   WHEN OTHERS THEN
481      return 0;
482 End CHK_APPROVED_FOLDER;
483 ---------------------------GET_TRNX_LEVEL_RESERVED_AMT-----------------------------
484 --
485 FUNCTION GET_TRNX_LEVEL_RESERVED_AMT
486 (
487  p_transaction_id         IN    pqh_bdgt_pool_realloctions.reallocation_id%TYPE,
488  p_transaction_type       IN    pqh_bdgt_pool_realloctions.transaction_type%TYPE DEFAULT 'DD'
489 ) RETURN  NUMBER IS
490 
491 /*
492 This function returns Transaction/Reallocation Amount for a given Transation Id.
493 Transaction Type :
494 ====================
495 DD - Donor Details
496 RD - Receiver Details
497 
498 Default is DD
499 */
500 CURSOR csr_tranaction_amt
501 IS
502 Select nvl(sum(trnxamt.reserved_amt),0)
503 From pqh_bdgt_pool_realloctions trnxamt,
504 pqh_bdgt_pool_realloctions trnxdtl
505 where trnxamt.txn_detail_id = trnxdtl.reallocation_id
506 and  trnxdtl.pool_id= p_transaction_id
507 and  trnxamt.transaction_type = p_transaction_type;
508 
509 l_trans_amt number;
510 
511 begin
512 
513 Open csr_tranaction_amt;
514 fetch csr_tranaction_amt into l_trans_amt;
515 close csr_tranaction_amt;
516 
517 return (l_trans_amt );
518 
519 EXCEPTION
520   WHEN OTHERS THEN
521     l_trans_amt := 0;
522     return l_trans_amt;
523 End GET_TRNX_LEVEL_RESERVED_AMT;
524 --
525 FUNCTION GET_LOCATION_CODE
526 (
527  p_entity_code		IN    pqh_budgets.budgeted_entity_cd%TYPE,
528  p_organization_id	IN    pqh_budget_details.organization_id%TYPE,
529  p_business_group_id    IN    hr_organization_units_v.business_group_id%TYPE
530 ) RETURN  VARCHAR
531 IS
532 l_location_code VARCHAR2(30);
533 BEGIN
534  IF(p_entity_code = 'POSITION' OR p_entity_code = 'ORGANIZATION') then
535 	select location_code
536 	into l_location_code
537 	from hr_organization_units_v
538 	where organization_id = p_organization_id;
539   ELSE
540        select location_code
541        into l_location_code
542        from hr_organization_units_v
543        where organization_id= p_business_group_id;
544   END IF;
545   RETURN l_location_code;
546 EXCEPTION
547 WHEN OTHERS THEN
548  l_location_code := null;
549  RETURN l_location_code;
550 END GET_LOCATION_CODE;
551 
552 --
553 PROCEDURE APP_NEXT_USER
554 (p_trans_id              in pqh_routing_history.transaction_id%type,
555 p_tran_cat_id           in pqh_transaction_categories.transaction_category_id%type,
556 p_cur_user_id           in out nocopy fnd_user.user_id%type,
557 p_cur_user_name         in out nocopy fnd_user.user_name%type,
558 p_user_active_role_id   in out nocopy pqh_roles.role_id%type,
559 p_user_active_role_name in out nocopy pqh_roles.role_name%type,
560 p_routing_category_id      out nocopy pqh_routing_categories.routing_category_id%type,
561 p_member_cd                out nocopy pqh_transaction_categories.member_cd%type,
562 p_routing_list_id          out nocopy pqh_routing_lists.routing_list_id%type,
563 p_member_role_id           out nocopy pqh_roles.role_id%type,
564 p_member_user_id           out nocopy fnd_user.user_id%type,
565 p_person_id                out nocopy fnd_user.employee_id%type,
566 p_member_id                out nocopy pqh_routing_list_members.routing_list_member_id%type,
567 p_position_id              out nocopy pqh_position_transactions.position_id%type,
568 p_cur_person_id            out nocopy fnd_user.employee_id%type,
569 p_cur_member_id            out nocopy pqh_routing_list_members.routing_list_member_id%type,
570 p_cur_position_id          out nocopy pqh_position_transactions.position_id%type,
571 p_pos_str_ver_id           out nocopy pqh_routing_history.pos_structure_version_id%type,
572 p_assignment_id            out nocopy per_assignments_f.assignment_id%type,
573 p_cur_assignment_id        out nocopy per_assignments_f.assignment_id%type,
574 p_next_user                out nocopy varchar2,
575 p_next_user_display        out nocopy varchar2,
576 p_status_flag              out nocopy number,
577 p_can_approve              out nocopy number)
578 IS
579 l_can_approve         	BOOLEAN;
580 l_old_member_cd		varchar2(30);
581 l_routing_history_id	NUMBER;
582 l_member_id		NUMBER;
583 l_person_id		NUMBER;
584 l_old_member_id		NUMBER;
585 l_old_routing_list_id	NUMBER;
586 l_position_id		NUMBER;
587 l_old_position_id	NUMBER;
588 l_pos_str_id		NUMBER;
589 l_old_pos_str_id	NUMBER;
590 l_old_pos_str_ver_id	NUMBER;
591 l_assignment_id		NUMBER;
592 l_old_assignment_id	NUMBER;
593 l_history_flag		BOOLEAN;
594 l_range_name		VARCHAR2(80);
595 l_proc varchar2(61) := 'app_next_user';
596 BEGIN
597 hr_utility.set_location('inside'||l_proc,10);
598 pqh_workflow.applicable_next_user
599              (p_trans_id               => p_trans_id
600               ,p_tran_cat_id           => p_tran_cat_id
601               ,p_cur_user_id           => p_cur_user_id
602               ,p_cur_user_name         => p_cur_user_name
603               ,p_user_active_role_id   => p_user_active_role_id
604               ,p_user_active_role_name => p_user_active_role_name
605               ,p_routing_category_id   => p_routing_category_id
606               ,p_member_cd             => p_member_cd
607               ,p_old_member_cd         => l_old_member_cd
608               ,p_routing_history_id    => l_routing_history_id
609               ,p_member_id             => p_member_id
610               ,p_person_id             => p_person_id
611               ,p_old_member_id         => l_old_member_id
612               ,p_routing_list_id       => p_routing_list_id
613               ,p_old_routing_list_id   => l_old_routing_list_id
614               ,p_member_role_id        => p_member_role_id
615               ,p_member_user_id        => p_member_user_id
616               ,p_cur_person_id         => p_cur_person_id
617               ,p_cur_member_id         => p_cur_member_id
618               ,p_position_id           => p_position_id
619               ,p_old_position_id       => l_old_position_id
620               ,p_cur_position_id       => p_cur_position_id
621               ,p_pos_str_id            => l_pos_str_id
622               ,p_old_pos_str_id        => l_old_pos_str_id
623               ,p_pos_str_ver_id        => p_pos_str_ver_id
624               ,p_old_pos_str_ver_id    => l_old_pos_str_ver_id
625               ,p_assignment_id         => p_assignment_id
626               ,p_cur_assignment_id     => p_cur_assignment_id
627               ,p_old_assignment_id     => l_old_assignment_id
628               ,p_status_flag           => p_status_flag
629               ,p_history_flag          => l_history_flag
630               ,p_range_name            => l_range_name
631               ,p_can_approve           => l_can_approve);
632    hr_utility.set_location('status returned is'||p_status_flag,20);
633    if nvl(p_status_flag,0) <> 0 then
634       hr_utility.set_location('error returned',25);
635    else
636       hr_utility.set_location('got the next user ',30);
637       if(l_can_approve) then
638          p_can_approve:=0;
639          hr_utility.set_location('approver yes ',35);
640       else
641          p_can_approve:=1;
642          hr_utility.set_location('approver no',40);
643       end if;
644       hr_utility.set_location('getting user name',50);
645       hr_utility.set_location('member_cd is '||p_member_cd,60);
646       hr_utility.set_location('position_id is '||p_position_id,62);
647       hr_utility.set_location('assignment_id is '||p_assignment_id,64);
648       hr_utility.set_location('user_id is '||p_member_user_id,66);
649       hr_utility.set_location('role_id is '||p_member_role_id,68);
650       FND_NEXT_USER(p_member_cd         => p_member_cd,
651                     p_position_id       => p_position_id,
652                     p_assignment_id     => p_assignment_id,
653                     p_member_role_id    => p_member_role_id,
654                     p_member_user_id    => p_member_user_id,
655                     p_next_name_display => p_next_user_display,
656                     p_next_name	        => p_next_user);
657       hr_utility.set_location('user_name is '||p_next_user,70);
658       hr_utility.set_location('display_name is '||p_next_user_display,75);
659    end if;
660 END APP_NEXT_USER;
661 
662 procedure get_next_user(p_member_cd           in pqh_transaction_categories.member_cd%type,
663 			p_routing_category_id in pqh_routing_categories.routing_category_id%type,
664                         p_tran_cat_id         in pqh_transaction_categories.transaction_category_id%type,
665 			p_trans_id            in pqh_routing_history.transaction_id%type,
666 			p_cur_assignment_id   in per_assignments_f.assignment_id%type,
667 			p_cur_member_id       in pqh_routing_list_members.routing_list_member_id%type,
668 			p_routing_list_id     in pqh_routing_categories.routing_list_id%type,
669 			p_cur_position_id     in pqh_position_transactions.position_id%type,
670 			p_pos_str_ver_id      in per_pos_structure_elements.pos_structure_version_id%type,
671 			p_next_position_id       out nocopy pqh_position_transactions.position_id%type,
672 			p_next_member_id         out nocopy pqh_routing_list_members.routing_list_member_id%type,
673                         p_next_role_id           out nocopy number,
674                         p_next_user_id           out nocopy number,
675 			p_next_assignment_id     out nocopy per_assignments_f.assignment_id%type,
676 			p_status_flag            out nocopy number,
677                         p_next_user              out nocopy varchar2,
678                         p_next_user_display      out nocopy varchar2) is
679 begin
680    pqh_workflow.next_applicable
681                   (p_member_cd           => p_member_cd,
682 	  	   p_routing_category_id => p_routing_category_id,
683                    p_tran_cat_id         => p_tran_cat_id,
684 		   p_trans_id            => p_trans_id,
685 		   p_cur_assignment_id   => p_cur_assignment_id,
686 		   p_cur_member_id       => p_cur_member_id,
687 		   p_routing_list_id     => p_routing_list_id,
688 		   p_cur_position_id     => p_cur_position_id,
689 		   p_pos_str_ver_id      => p_pos_str_ver_id,
690 		   p_next_position_id    => p_next_position_id,
691 		   p_next_member_id      => p_next_member_id,
692                    p_next_role_id        => p_next_role_id,
693                    p_next_user_id        => p_next_user_id,
694 		   p_next_assignment_id  => p_next_assignment_id,
695 		   p_status_flag         => p_status_flag);
696    hr_utility.set_location('getting user name',50);
697    hr_utility.set_location('member_cd is '||p_member_cd,60);
698    hr_utility.set_location('position_id is '||p_next_position_id,62);
699    hr_utility.set_location('assignment_id is '||p_next_assignment_id,64);
700    hr_utility.set_location('user_id is '||p_next_user_id,66);
701    hr_utility.set_location('role_id is '||p_next_role_id,68);
702    FND_NEXT_USER(p_member_cd         => p_member_cd,
703                  p_position_id       => p_next_position_id,
704                  p_assignment_id     => p_next_assignment_id,
705                  p_member_role_id    => p_next_role_id,
706                  p_member_user_id    => p_next_user_id,
707                  p_next_name_display => p_next_user_display,
708                  p_next_name	     => p_next_user);
709    hr_utility.set_location('user_name is '||p_next_user,70);
710 end get_next_user;
711 
712 PROCEDURE FND_NEXT_USER(p_member_cd         IN pqh_transaction_categories.member_cd%type,
713                         p_position_id       IN pqh_position_transactions.position_id%type,
714                         p_assignment_id     IN per_assignments_f.assignment_id%type,
715                         p_member_role_id    IN pqh_roles.role_id%type,
716                         p_member_user_id    IN fnd_user.user_id%type,
717                         p_next_name         OUT NOCOPY VARCHAR,
718                         p_next_name_display OUT NOCOPY VARCHAR) is
719    l_next_name VARCHAR2(240);
720    l_person_id NUMBER;
721 begin
722 IF(p_member_cd = 'R') THEN
723    IF(p_member_user_id is NOT null) THEN
724       select user_name
725       into p_next_name
726       from fnd_user
727       where user_id=p_member_user_id;
728       hr_utility.set_location('user_name is '||p_next_name,10);
729       l_next_name := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
730                                               p_lookup_code => 'USER');
731       hr_utility.set_location('prefix is '||l_next_name,20);
732       p_next_name_display := l_next_name ||':'||p_next_name ;
733       hr_utility.set_location('name is '||p_next_name_display,30);
734    ELSIF p_member_role_id is not null then
735       select role_name
736       into p_next_name
737       from pqh_roles
738       where role_id= p_member_role_id;
739       hr_utility.set_location('user_name is '||p_next_name,40);
740       l_next_name := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
741                                               p_lookup_code => 'ROLE');
742       hr_utility.set_location('prefix is '||l_next_name,50);
743       p_next_name_display := l_next_name ||':'||p_next_name ;
744       p_next_name         := 'PQH_ROLE:'||p_member_role_id;
745       hr_utility.set_location('name is '||p_next_name_display,60);
746    else
747       hr_utility.set_location('member_cd R but null values',70);
748       p_next_name := null;
749       p_next_name_display := null;
750    END IF;
751 ELSIF (p_member_cd = 'P') THEN
752    select hr_general.DECODE_POSITION_LATEST_NAME (p_position_id)
753    into p_next_name from dual;
754    hr_utility.set_location('user_name is '||p_next_name,80);
755    l_next_name := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
756                                            p_lookup_code => 'POSITION');
757    hr_utility.set_location('prefix is '||l_next_name,90);
758    p_next_name_display := l_next_name ||':'||p_next_name ;
759    p_next_name := 'POS:'||p_position_id;
760    hr_utility.set_location('name is '||p_next_name_display,100);
761 ELSIF (p_member_cd = 'S') THEN
762    select person_id into l_person_id
763    from per_assignments
764    where assignment_id  = p_assignment_id;
765    hr_utility.set_location('person_id is '||l_person_id,110);
766    select user_name
767    into p_next_name
768    from fnd_user
769    where employee_id=l_person_id;
770    hr_utility.set_location('user_name is '||p_next_name,120);
771    l_next_name := hr_general.decode_lookup(p_lookup_type => 'PQH_BPR_ROUTING',
772                                            p_lookup_code => 'USER');
773    hr_utility.set_location('prefix is '||l_next_name,130);
774    p_next_name_display := l_next_name ||':'||p_next_name ;
775    hr_utility.set_location('name is '||p_next_name_display,140);
776 ELSE
777    hr_utility.set_location('invalid member_cd',150);
778    p_next_name := null;
779    p_next_name_display := null;
780 END IF;
781 EXCEPTION
782    WHEN OTHERS THEN
783       hr_utility.set_location('data issues',420);
784       p_next_name := null;
785       p_next_name_display := null;
786 END FND_NEXT_USER;
787 
788 -- ----------------------------------------------------------------------------
789 -- |------------------------< apply_transaction >------------------------|
790 -- ----------------------------------------------------------------------------
791 
792 function apply_transaction
793 (  p_transaction_id    in  NUMBER,
794    p_validate_only              in varchar2 default 'NO'
795 ) return varchar2 is
796 
797  l_proc                      varchar2(72) := g_package||'apply_transaction';
798  l_status   varchar2(30);
799  l_return   varchar2(30);
800  l_transaction_category_id NUMBER;
801 
802  Cursor csr_pool_dtls
803  IS  SELECT *
804      FROM   pqh_budget_pools
805      WHERE  pool_id = p_transaction_id;
806  l_pool_rec  csr_pool_dtls%ROWTYPE;
807 BEGIN
808    hr_utility.set_location('Entering '||l_proc,10);
809 --calling routine for enabling multi-message detection --kgowripe
810    hr_multi_message.enable_message_list;
811 --
812    OPEN csr_pool_dtls;
813    FETCH csr_pool_dtls INTO  l_pool_rec;
814    CLOSE csr_pool_dtls;
815    -- Added by krmahade for Bug#3036405
816    l_transaction_category_id := l_pool_rec.wf_transaction_category_id;
817    -- End krmahade
818    hr_utility.set_location('txn_cat is '||l_transaction_category_id||l_proc,12);
819    --Before Applying a Budget Reallocation Transaction, apply all the Business Rules
820    pqh_cbr_engine.apply_rules(p_transaction_type => 'REALLOCATION'
821                              ,p_transaction_id => p_transaction_id
822                              ,p_business_group_id => l_pool_rec.business_group_id
823                              ,p_effective_date => hr_general.effective_date
824                              ,p_status_flag => l_status);
825    hr_utility.set_location('status returned is '||l_status||l_proc,20);
826 
827    IF NVL(l_status,'S') <> 'E'and p_validate_only = 'NO' THEN
828       hr_utility.set_location('for folder approval '||l_proc,22);
829       pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
830                                                      ,p_folder_id => p_transaction_id
831                                                      ,p_object_version_number=>l_pool_rec.object_version_number
832                                                      ,p_business_group_id => l_pool_rec.business_group_id
833                                                      ,p_approval_status=> 'A'
834                                                      ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id );
835       hr_utility.set_location('folder approved '||l_proc,26);
836   ELSIF l_status = 'E' THEN
837       hr_utility.set_location('error encountered '||l_proc,28);
838       l_return := 'FAILURE';
839       fnd_message.set_name(8302,'PQH_CBR_FAILED_ERROR');
840       fnd_message.raise_error;
841   END IF;
842   -- Addd by KGOWRIPE for fixing bug# 2896852
843   IF NVL(l_status,'S') = 'W'  THEN
844       l_return := 'WARNING';
845       hr_utility.set_location('Warning at '||l_proc,29);
846       /* commented by mvankada for fixing the bug : 293577
847 
848       pqh_wf.set_apply_error(p_transaction_category_id => l_transaction_category_id,
849                              p_transaction_id          => p_transaction_id,
850                              p_apply_error_mesg        => 'PQH_BPR_WARNING',
851                              p_apply_error_num         => 0);
852        */
853        g_warning := 'TRUE';
854   ELSE
855       l_return := 'SUCCESS';
856   END IF;
857 -- end fix for 2896852
858   hr_utility.set_location('Leaving '||l_proc,30);
859   --
860   RETURN l_return;
861   Exception
862 --added by kgowripe
863   when hr_multi_message.error_message_exist then
864     Null;
865 
866 When Others THEN
867 
868    IF SQLERRM IS NOT NULL THEN
869      hr_utility.set_location('setting wf error '||l_proc,40);
870      pqh_wf.set_apply_error(p_transaction_category_id => l_transaction_category_id,
871                             p_transaction_id          => p_transaction_id,
872                             p_apply_error_mesg        => SQLERRM,
873                             p_apply_error_num         => SQLCODE);
874      l_return := 'FAILURE';
875    END IF;
876         --
877    hr_utility.set_location('Leaving '||l_proc,30);
878    return l_return;
879         --
880 End apply_transaction;
881 
882 FUNCTION entity_id
883 ( p_budget_detail_id IN pqh_budget_details.budget_detail_id%TYPE,
884 p_entity_type    IN pqh_budgets.budgeted_entity_cd%TYPE
885 )RETURN NUMBER
886 IS
887 l_entity_id NUMBER :=0;
888 CURSOR csr_details
889 IS
890 select job_id,position_id,grade_id,organization_id
891 from pqh_budget_details
892 where budget_detail_id = p_budget_detail_id;
893 rec_details csr_details%ROWTYPE;
894 BEGIN
895 OPEN csr_details;
896 FETCH csr_details into rec_details;
897    If p_entity_type = 'POSITION' then
898 	l_entity_id := rec_details.position_id;
899    elsif p_entity_type = 'JOB' then
900      l_entity_id := rec_details.job_id;
901    elsif p_entity_type = 'ORGANIZATION' then
902      l_entity_id := rec_details.organization_id;
903    elsif p_entity_type = 'GRADE' then
904      l_entity_id := rec_details.grade_id;
905    else
906      l_entity_id :=0;
907    end if;
908 CLOSE csr_details;
909 	return l_entity_id;
910 EXCEPTION
911   WHEN OTHERS THEN
912   l_entity_id :=0;
913   return l_entity_id;
914 END entity_id;
915 
916 FUNCTION respond_notification (p_transaction_id in number) RETURN varchar2
917 is
918   l_document          varchar2(4000);
919   l_proc              varchar2(61) := g_package||'respond_notification' ;
920   l_folder_name       varchar2(2000);
921   l_unit       varchar2(80);
922   l_entity_desc varchar2(80);
923   l_transaction_status  varchar2(100);
924   cursor csr_pool_dtls is select name,
925                                  hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
926                       hr_general.decode_shared_type(budget_unit_id),
927                       hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
928                from pqh_budget_pools
929                where pool_id = p_transaction_id;
930 BEGIN
931   hr_utility.set_location('inside respond notification'||l_proc,10);
932   open csr_pool_dtls;
933   fetch csr_pool_dtls into l_folder_name,l_entity_desc,l_unit,l_transaction_status;
934   close csr_pool_dtls;
935   hr_utility.set_location('pool dtls  fetched   '||l_proc,20);
936   --
937   hr_utility.set_message(8302,'PQH_BPL_WF_RESPOND_NOTICE');
938   hr_utility.set_message_token('FOLDER_NAME',l_folder_name);
939   hr_utility.set_message_token('ENTITY',l_entity_desc);
940   hr_utility.set_message_token('UNIT',l_unit);
941   hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
942   l_document := hr_utility.get_message;
943   return l_document;
944 exception
945   when others then
946      hr_utility.set_message(8302,'PQH_BPL_WF_RESPOND_FAIL');
947      hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
948      l_document := hr_utility.get_message;
949      return l_document;
950 END respond_notification;
951 
952 FUNCTION url_builder(p_transaction_id  in number) Return VARCHAR2 IS
953  l_url   varchar2(1000);
954  l_status  varchar2(30) := 'P';
955  Cursor csr_folder_status IS
956    SELECT business_group_id, NVL(approval_status,'P')
957    FROM   pqh_budget_pools
958   WHERE   pool_id = p_transaction_id;
959  l_business_group_id Number(15);
960  l_proc  varchar2(80) := g_package||'url_builder';
961 BEGIN
962   hr_utility.set_location('Entering '||l_proc,10);
963   OPEN csr_folder_status;
964   FETCH csr_folder_status INTO l_business_group_id,l_status;
965   CLOSE csr_folder_status;
966   l_url := 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/pqh/budgetreallocation/webui/BprReallocPG'||'&'||'retainAM=Y'||'&'||'P_FolderId='||p_transaction_id||'&'||'fromNotify=Y'||'&'||'approvalStatusFlag='||l_status||'&'||'P_BGId='||l_business_group_id;
967   hr_utility.set_location('URL '||substr(l_url,1,50),15);
968   hr_utility.set_location('URL2 '||substr(l_url,51,100),15);
969   hr_utility.set_location('Leaving '||l_proc,20);
970   RETURN l_url;
971 END url_builder;
972 FUNCTION warning_notification (p_transaction_id in number) RETURN varchar2
973 is
974   l_document varchar2(4000);
975   l_proc     varchar2(61) := g_package||'warning_notification' ;
976   l_folder_name       varchar2(2000);
977   l_unit       varchar2(80);
978   l_entity_desc varchar2(80);
979   l_transaction_status  varchar2(100);
980   cursor csr_pool_dtls is select name,
981                                  hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
982                       hr_general.decode_shared_type(budget_unit_id),
983                       hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
984                from pqh_budget_pools
985                where pool_id = p_transaction_id;
986 BEGIN
987   hr_utility.set_location('inside warning notification'||l_proc,10);
988   open csr_pool_dtls;
989   fetch csr_pool_dtls into l_folder_name,l_entity_desc,l_unit,l_transaction_status;
990   close csr_pool_dtls;
991   hr_utility.set_location('pool dtls  fetched   '||l_proc,20);
992   --
993   hr_utility.set_message(8302,'PQH_BPL_WF_WARN_NOTICE');
994   hr_utility.set_message_token('FOLDER_NAME',l_folder_name);
995   hr_utility.set_message_token('ENTITY',l_entity_desc);
996   hr_utility.set_message_token('UNIT',l_unit);
997   hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
998   l_document := hr_utility.get_message;
999   return l_document;
1000 exception
1001   when others then
1002      hr_utility.set_message(8302,'PQH_BPL_WF_WARN_FAIL');
1003      hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
1004      l_document := hr_utility.get_message;
1005      return l_document;
1006 END warning_notification;
1007 FUNCTION reject_notification (p_transaction_id in number) RETURN varchar2
1008 is
1009   l_document varchar2(4000);
1010   l_proc     varchar2(61) := g_package||'reject_notification' ;
1011   l_folder_name       varchar2(2000);
1012   l_unit       varchar2(80);
1013   l_entity_desc varchar2(80);
1014   l_transaction_status  varchar2(100);
1015   cursor csr_pool_dtls is select name,
1016                                  hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
1017                       hr_general.decode_shared_type(budget_unit_id),
1018                       hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
1019                from pqh_budget_pools
1020                where pool_id = p_transaction_id;
1021 BEGIN
1022   hr_utility.set_location('Entering'||l_proc,10);
1023   open csr_pool_dtls;
1024   fetch csr_pool_dtls into l_folder_name,l_entity_desc,l_unit,l_transaction_status;
1025   close csr_pool_dtls;
1026   hr_utility.set_location('pool dtls  fetched   '||l_proc,20);
1027   --
1028   hr_utility.set_message(8302,'PQH_BPL_WF_REJECT_NOTICE');
1029   hr_utility.set_message_token('FOLDER_NAME',l_folder_name);
1030   hr_utility.set_message_token('ENTITY',l_entity_desc);
1031   hr_utility.set_message_token('UNIT',l_unit);
1032   hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
1033   l_document := hr_utility.get_message;
1034   hr_utility.set_location('Leaving'||l_proc,10);
1035   return l_document;
1036 exception
1037   when others then
1038      hr_utility.set_message(8302,'PQH_BPL_WF_REJECT_FAIL');
1039      hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
1040      l_document := hr_utility.get_message;
1041 END reject_notification;
1042 FUNCTION apply_notification (p_transaction_id in number) RETURN varchar2
1043 is
1044   l_document varchar2(4000);
1045   l_proc     varchar2(61) := g_package||'apply_notification' ;
1046   l_folder_name       varchar2(2000);
1047   l_unit       varchar2(80);
1048   l_entity_desc varchar2(80);
1049   l_transaction_status  varchar2(100);
1050   cursor csr_pool_dtls is select name,
1051                                  hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
1052                       hr_general.decode_shared_type(budget_unit_id),
1053                       hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
1054                from pqh_budget_pools
1055                where pool_id = p_transaction_id;
1056 BEGIN
1057   hr_utility.set_location('Entering'||l_proc,10);
1058   open csr_pool_dtls;
1059   fetch csr_pool_dtls into l_folder_name,l_entity_desc,l_unit,l_transaction_status;
1060   close csr_pool_dtls;
1061   hr_utility.set_location('pool dtls  fetched   '||l_proc,20);
1062   --
1063   hr_utility.set_message(8302,'PQH_BPL_WF_APPLY_NOTICE');
1064   hr_utility.set_message_token('FOLDER_NAME',l_folder_name);
1065   hr_utility.set_message_token('ENTITY',l_entity_desc);
1066   hr_utility.set_message_token('UNIT',l_unit);
1067   hr_utility.set_message_token('TRANSACTION_STATUS',l_transaction_status);
1068   l_document := hr_utility.get_message;
1069   hr_utility.set_location('Leaving'||l_proc,10);
1070   return l_document;
1071 exception
1072   when others then
1073      hr_utility.set_message(8302,'PQH_BPL_WF_APPLY_FAIL');
1074      hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
1075      l_document := hr_utility.get_message;
1076 END apply_notification;
1077 
1078 function reject_transaction
1079 (  p_transaction_id    in  NUMBER,
1080    p_validate_only              in varchar2 default 'NO'
1081 ) return varchar2 is
1082 
1083  l_proc                      varchar2(72) := g_package||'reject_transaction';
1084  l_status   varchar2(30);
1085  l_return   varchar2(30);
1086  l_transaction_category_id NUMBER;
1087  l_validate boolean :=false;
1088 --
1089  Cursor csr_pool_dtls
1090  IS  SELECT *
1091      FROM   pqh_budget_pools
1092      WHERE  pool_id = p_transaction_id;
1093  l_pool_rec  csr_pool_dtls%ROWTYPE;
1094 BEGIN
1095    hr_utility.set_location('Entering '||l_proc,10);
1096 --added by kgowripe
1097 hr_multi_message.enable_message_list;
1098 --end changes by kgowripe
1099    OPEN csr_pool_dtls;
1100    FETCH csr_pool_dtls INTO  l_pool_rec;
1101    CLOSE csr_pool_dtls;
1102 
1103    if (p_validate_only = 'YES') then
1104      l_validate := true;
1105    end if;
1106   --
1107   pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
1108                                                  ,p_folder_id => p_transaction_id
1109                                                  ,p_object_version_number=>l_pool_rec.object_version_number
1110                                                  ,p_business_group_id => l_pool_rec.business_group_id
1111                                                  ,p_approval_status=> 'R'
1112                                                  ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id
1113 ,p_validate => l_validate );
1114 
1115   l_return := 'SUCESS';
1116   hr_utility.set_location('Leaving '||l_proc,30);
1117   --
1118   RETURN l_return;
1119   Exception
1120 --added by kgowripe
1121   when hr_multi_message.error_message_exist then
1122     Null;
1123 
1124   When Others THEN
1125 
1126    IF SQLERRM IS NOT NULL THEN
1127      pqh_wf.set_apply_error(p_transaction_category_id
1128                                                  => l_transaction_category_id,
1129                             p_transaction_id     => p_transaction_id,
1130                             p_apply_error_mesg   => SQLERRM,
1131                             p_apply_error_num    => SQLCODE);
1132      l_return := 'FAILURE';
1133    END IF;
1134         --
1135    hr_utility.set_location('Leaving '||l_proc,30);
1136    return l_return;
1137         --
1138 End reject_transaction;
1139 
1140 
1141 
1142 PROCEDURE notify_bgt_manager_users
1143 (
1144  p_transaction_id number,
1145  p_transaction_name varchar2
1146 ) IS
1147 
1148 /*
1149 This procedure sends notifiction to all users having 'Budget Manager' as role type
1150 */
1151 -- Added by mvankada
1152 -- Bug : 2883516
1153 -- added business_group_id is null condition
1154 CURSOR csr_bgt_manager_rls(l_business_group_id IN number)
1155 IS
1156 Select  role_id,role_name
1157 From pqh_roles
1158 Where role_type_cd = 'BUDGET'
1159 And enable_flag = 'Y'
1160 And(  business_group_id = l_business_group_id
1161 OR business_group_id IS NULL );
1162 
1163 CURSOR csr_bg_id
1164 IS
1165 Select  business_group_id
1166 From pqh_budget_pools
1167 Where pool_id = p_transaction_id;
1168 
1169 Cursor csr_seq_no (l_transaction_category_id IN number)  IS
1170 Select max(substr(item_key,(length(l_transaction_category_id||'-'||p_transaction_id))+2,6) )
1171 From wf_items
1172 Where item_type='PQHGEN'
1173 And item_key like l_transaction_category_id||'-'||p_transaction_id||'%';
1174 
1175 l_proc                      varchar2(72) := g_package||'notify_bgt_manager_users';
1176 l_role_id number(15);
1177 l_role_name varchar2(30);
1178 l_business_group_id number(15);
1179 l_transaction_category_id number(15);
1180 l_apply_error_mesg varchar2(4000);
1181 l_apply_error_num number;
1182 l_max_seq_no number(15);
1183 
1184 
1185 Begin
1186 hr_utility.set_location('Entering '||l_proc,10);
1187 Open csr_bg_id;
1188 Fetch csr_bg_id into l_business_group_id;
1189 Close csr_bg_id;
1190 hr_utility.set_location('Business Group id '||l_business_group_id||l_proc,12);
1191 l_transaction_category_id := pqh_workflow.get_txn_cat
1192                               (p_short_name => 'PQH_BPR'
1193                               ,p_business_group_id => l_business_group_id);
1194 hr_utility.set_location('transaction category id '||l_transaction_category_id||l_proc,15);
1195 
1196 Open csr_bgt_manager_rls(l_business_group_id);
1197 loop
1198   fetch csr_bgt_manager_rls into l_role_id,l_role_name;
1199   exit when csr_bgt_manager_rls%notfound;
1200   hr_utility.set_location('Transaction id   '||p_transaction_id||l_proc,20);
1201   hr_utility.set_location('Transaction name  '||p_transaction_name||l_proc,25);
1202   hr_utility.set_location('Role Id  '||l_role_id||l_proc,30);
1203   hr_utility.set_location('Role Name  '||l_role_name||l_proc,35);
1204 
1205   -- Get the maximun Sequence number
1206   Open csr_seq_no(l_transaction_category_id);
1207   Fetch csr_seq_no into l_max_seq_no;
1208   Close csr_seq_no;
1209   if l_max_seq_no is null then
1210      l_max_seq_no := 1;
1211   else
1212      l_max_seq_no := l_max_seq_no + 1;
1213   end if;
1214   hr_utility.set_location('Max Seq No   '||l_max_seq_no||l_proc,37);
1215    pqh_wf.process_user_action( P_TRANSACTION_CATEGORY_ID => l_transaction_category_id,
1216                                P_TRANSACTION_ID => p_transaction_id,
1217                                P_USER_ACTION_CD => 'PQH_BPR',
1218                                p_workflow_seq_no => l_max_seq_no,
1219                                P_FORWARDED_TO_ROLE_ID => l_role_id,
1220                                P_ROUTE_TO_USER  => 'PQH_ROLE:'||l_role_id,
1221                                P_TRANSACTION_NAME => p_transaction_name,
1222                                P_APPLY_ERROR_MESG => l_apply_error_mesg,
1223                                P_APPLY_ERROR_NUM  => l_apply_error_num);
1224   If (l_apply_error_num <> 0) then
1225       hr_utility.set_location('error encountered '||l_proc,40);
1226       fnd_message.set_name(8302,'PQH_BPR_PROCESS_LOG_ERROR');
1227       fnd_message.raise_error;
1228   End if;
1229 
1230 End loop;
1231 Close csr_bgt_manager_rls;
1232 hr_utility.set_location('Leaving '||l_proc,50);
1233 
1234 End notify_bgt_manager_users;
1235 
1236 FUNCTION fyi_notification (p_transaction_id in number) RETURN varchar2
1237 is
1238   l_document varchar2(4000);
1239   l_proc     varchar2(61) := g_package||'fyi_notification' ;
1240   l_folder_name     varchar2(2000);
1241   l_entity_type     varchar2(80);
1242   l_budget_unit     varchar2(80);
1243   l_approval_status  varchar2(100);
1244   cursor c0 is select  name,
1245                        hr_general.decode_lookup('PQH_BUDGET_ENTITY',ENTITY_TYPE),
1246                        hr_general.decode_shared_type(budget_unit_id),
1247                        hr_general.decode_lookup('PQH_REALLOC_TXN_STATUS',approval_status)
1248                from pqh_budget_pools
1249                where pool_id = p_transaction_id;
1250 BEGIN
1251   hr_utility.set_location('inside fyi notification'||l_proc,10);
1252   open c0;
1253   fetch c0 into l_folder_name,l_entity_type, l_budget_unit, l_approval_status;
1254   close c0;
1255   hr_utility.set_location('Folder name, Approval Status fetched   '||l_proc,20);
1256   --
1257   hr_utility.set_message(8302,'PQH_BPR_WF_FYI_NOTICE');
1258   hr_utility.set_message_token('FOLDER_NAME',l_folder_name);
1259   hr_utility.set_message_token('ENTITY_TYPE',l_entity_type);
1260   hr_utility.set_message_token('BUDGET_UNIT',l_budget_unit);
1261   hr_utility.set_message_token('APPROVAL_STATUS',l_approval_status);
1262   l_document := hr_utility.get_message;
1263   return l_document;
1264   exception
1265   when others then
1266      hr_utility.set_message(8302,'PQH_BPR_WF_FYI_FAIL');
1267      hr_utility.set_message_token('TRANSACTION_ID',p_transaction_id);
1268      l_document := hr_utility.get_message;
1269      return l_document;
1270 END fyi_notification;
1271 
1272 PROCEDURE update_folder_approval_status (p_transaction_id in number, p_action_flag in varchar2)
1273 IS
1274 /* This procedure updates the folder approval status as 'P' (Pending)
1275    if the action flag is I (InBox) , F (Forward)
1276  */
1277 
1278 Cursor csr_pool_dtls IS
1279 Select *
1280 From pqh_budget_pools
1281 Where pool_id = p_transaction_id;
1282 
1283 l_pool_rec  csr_pool_dtls%ROWTYPE;
1284 l_proc   varchar2(72) := g_package||'update_folder_approval_status';
1285 l_transaction_category_id number;
1286 
1287 BEGIN
1288    hr_utility.set_location('Entering '||l_proc,10);
1289 --added by kgowripe
1290 hr_multi_message.enable_message_list;
1291 --end changes by kgowripe
1292    OPEN csr_pool_dtls;
1293    FETCH csr_pool_dtls INTO  l_pool_rec;
1294    CLOSE csr_pool_dtls;
1295    hr_utility.set_location('Txn Cat Id  '||l_transaction_category_id ,15);
1296 
1297    if ( l_pool_rec.approval_status = 'T' and (p_action_flag = 'I'  OR
1298                                               p_action_flag = 'F' ))
1299    then
1300          pqh_budget_pools_api.update_reallocation_folder(p_effective_date=> hr_general.effective_date
1301                                                  ,p_folder_id => p_transaction_id
1302                                                  ,p_object_version_number=>l_pool_rec.object_version_number
1303                                                  ,p_business_group_id => l_pool_rec.business_group_id
1304                                                  ,p_approval_status=> 'P'
1305                                                  ,p_wf_transaction_category_id => l_pool_rec.wf_transaction_category_id);
1306   end if;
1307 Exception
1308 --added by kgowripe
1309   when hr_multi_message.error_message_exist then
1310     Null;
1311 End update_folder_approval_status;
1312 PROCEDURE bgt_dummy_folder_delete(p_business_group_id IN number)
1313 is
1314 
1315 /*
1316 This procedure deletes all folders whose approval_status is T (created)
1317 i.e the folders which are created but not routed.
1318 */
1319 Cursor csr_approval_status_T_folders IS
1320 Select pool_id
1321 From pqh_budget_pools
1322 Where approval_status = 'T'
1323 And business_group_id = p_business_group_id
1324 AND creation_date < sysdate - 2;
1325 
1326 l_folder_id pqh_budget_pools.pool_id%type;
1327 l_proc    varchar2(72) := g_package ||'bgt_dummy_folder_delete';
1328 
1329 BEGIN
1330 
1331 hr_utility.set_location(' Entering:' || l_proc,10);
1332 --added by kgowripe
1333 hr_multi_message.enable_message_list;
1334 --end changes by kgowripe
1335 Open  csr_approval_status_T_folders;
1336 loop
1337      hr_utility.set_location('Folder Id :' || l_folder_id ,20);
1338      fetch csr_approval_status_T_folders into l_folder_id;
1339      exit when csr_approval_status_T_folders%NOTFOUND;
1340      If l_folder_id is Not Null Then
1341         /* In the procedure pqh_budget_pools_swi.bgt_realloc_delete
1342            node_type T is marked as Transaction.
1343            So here passed TCF (Tempory Created Folder) as node type
1344            to distinguish with Transaction
1345          */
1346        BEGIN
1347          pqh_budget_pools_swi.bgt_realloc_delete(p_node_type => 'TCF'
1348                                             ,p_node_id   =>l_folder_id);
1349        EXCEPTION
1350          When Others Then
1351               Null;
1352        END;
1353      End If;
1354 end loop;
1355 close csr_approval_status_T_folders;
1356 hr_utility.set_location(' Leaving:' || l_proc,30);
1357 Exception
1358   When others Then
1359      Null;
1360 END bgt_dummy_folder_delete;
1361 
1362 PROCEDURE chk_bpr_route_catg_exist(p_business_group_id IN Number,
1363                                    p_status out nocopy varchar2) IS
1364 
1365  l_txn_catg_id Number;
1366  l_route_catg_id Number;
1367 CURSOR csr_rout_catg(p_txn_catg_id IN Number) IS
1368    SELECT routing_category_id
1369    FROM   pqh_routing_categories
1370    WHERE  transaction_category_id = p_txn_catg_id;
1371 BEGIN
1372   l_txn_catg_id := pqh_workflow.get_txn_cat('PQH_BPR',p_business_group_id);
1373   OPEN csr_rout_catg(l_txn_catg_id);
1374   FETCH csr_rout_catg INTO l_route_catg_id;
1375   IF csr_rout_catg%NOTFOUND THEN
1376     p_status := 'FALSE';
1377   ELSE
1378     p_status := 'TRUE';
1379   END IF;
1380   CLOSE csr_rout_catg;
1381 END chk_bpr_route_catg_exist;
1382 
1383 -- Added procedure by mvanakda
1384 PROCEDURE bpr_process_user_action(
1385              	  p_transaction_id                IN  NUMBER
1386 	         ,p_transaction_category_id       IN  NUMBER
1387 	         ,p_route_to_user                 IN  VARCHAR2
1388 	         ,p_routing_category_id           IN  NUMBER
1389 	         ,p_pos_structure_version_id      IN  NUMBER
1390 	         ,p_user_action_cd                IN  VARCHAR2
1391 	         ,p_forwarded_to_user_id          IN  NUMBER
1392 	         ,p_forwarded_to_role_id          IN  NUMBER
1393 	         ,p_forwarded_to_position_id      IN  NUMBER
1394 	         ,p_forwarded_to_assignment_id    IN  NUMBER
1395 	         ,p_forwarded_to_member_id        IN  NUMBER
1396 	         ,p_forwarded_by_user_id          IN  NUMBER
1397 	         ,p_forwarded_by_role_id          IN  NUMBER
1398 	         ,p_forwarded_by_position_id      IN  NUMBER
1399 	         ,p_forwarded_by_assignment_id    IN  NUMBER
1400 	         ,p_forwarded_by_member_id        IN  NUMBER
1401 	         ,p_effective_date                IN  DATE
1402 	         ,p_approval_cd                   IN  VARCHAR2
1403 	         ,p_member_cd                     In  VARCHAR2
1404 	         ,p_transaction_name              IN  VARCHAR2
1405 	         ,p_apply_error_mesg              OUT NOCOPY VARCHAR2
1406        		 ,p_apply_error_num               OUT NOCOPY NUMBER
1407        		 ,p_warning_mesg                  OUT NOCOPY VARCHAR2
1408        		 ) IS
1409 l_proc    varchar2(72) := g_package ||'bpr_process_user_action';
1410 l_return   varchar2(30);
1411 
1412 --Added cursor by krmahade
1413 CURSOR csr_get_bdgt_start_dt IS
1414     select bgt.budget_start_date
1415     from   pqh_budgets bgt,
1416            pqh_budget_versions bvr,
1417            pqh_budget_pools fld
1418     where  fld.pool_id = p_transaction_id
1419     and    fld.budget_version_id = bvr.budget_version_id
1420     and    bvr.budget_id = bgt.budget_id;
1421 
1422     l_effective_date date;
1423 --end krmahade
1424 BEGIN
1425   hr_utility.set_location(' Entering:' || l_proc,10);
1426 
1427   --Added by krmahade
1428   OPEN csr_get_bdgt_start_dt;
1429   FETCH  csr_get_bdgt_start_dt into l_effective_date;
1430   CLOSE csr_get_bdgt_start_dt;
1431 
1432   --End krmahade
1433 
1434   g_warning := 'FALSE';
1435 
1436   -- Svorugan : for bug fix : 2864971
1437         if (p_user_action_cd = 'REJECT') then
1438          --
1439            l_return   :=  pqh_bdgt_realloc_utility.reject_transaction(p_transaction_id);
1440            --
1441            hr_utility.set_location(' Action Rejection Status :' || l_return,26);
1442          --
1443         end if;
1444   -- End Svorugan
1445 
1446   hr_utility.set_location(' before call warning :' || g_warning,15);
1447 
1448   pqh_wf.process_user_action(
1449               	 p_transaction_id                 =>  p_transaction_id
1450   	         ,p_transaction_category_id        =>  p_transaction_category_id
1451   	         ,p_route_to_user                  =>  p_route_to_user
1452   	         ,p_routing_category_id            =>  p_routing_category_id
1453   	         ,p_pos_structure_version_id       =>  p_pos_structure_version_id
1454   	         ,p_user_action_cd                 =>  p_user_action_cd
1455   	         ,p_forwarded_to_user_id           =>  p_forwarded_to_user_id
1456   	         ,p_forwarded_to_role_id           =>  p_forwarded_to_role_id
1457   	         ,p_forwarded_to_position_id       =>  p_forwarded_to_position_id
1458   	         ,p_forwarded_to_assignment_id     =>  p_forwarded_to_assignment_id
1459   	         ,p_forwarded_to_member_id         =>  p_forwarded_to_member_id
1460   	         ,p_forwarded_by_user_id           =>  p_forwarded_by_user_id
1461   	         ,p_forwarded_by_role_id           =>  p_forwarded_by_role_id
1462   	         ,p_forwarded_by_position_id       =>  p_forwarded_by_position_id
1463   	         ,p_forwarded_by_assignment_id     =>  p_forwarded_by_assignment_id
1464   	         ,p_forwarded_by_member_id         =>  p_forwarded_by_member_id
1465   	         ,p_effective_date                 =>  NVL(l_effective_date,p_effective_date)
1466   	         ,p_approval_cd                    =>  p_approval_cd
1467   	         ,p_member_cd                      =>  p_member_cd
1468   	         ,p_transaction_name               =>  p_transaction_name
1469   	         ,p_apply_error_mesg               =>  p_apply_error_mesg
1470                  ,p_apply_error_num           	   =>  p_apply_error_num
1471        		 );
1472   hr_utility.set_location(' after call warning :' || g_warning,25);
1473   p_warning_mesg := g_warning;
1474 hr_utility.set_location(' leaving:' || l_proc,40);
1475 END bpr_process_user_action;
1476 
1477 -- ----------------------------------------------------------------------------
1478 -- |----------------------< check_approver_skip >-------------------------|
1479 -- ----------------------------------------------------------------------------
1480 FUNCTION check_approver_skip(p_transaction_category_id IN NUMBER)
1481 RETURN VARCHAR2
1482 IS
1483 l_status VARCHAR2(10) := 'Y';
1484 BEGIN
1485 SELECT prevent_approver_skip
1486 into l_status
1487 from pqh_transaction_categories
1488 where transaction_category_id = p_transaction_category_id;
1489 IF l_status is null OR l_status = 'N' THEN
1490 RETURN 'N';
1491 ELSE
1492 RETURN 'Y';
1493 END IF;
1494 END CHECK_APPROVER_SKIP;
1495 
1496 
1497 -- ----------------------------------------------------------------------------
1498 -- |----------------------< valid_user_opening >-------------------------------|
1499 -- | Wrapper on top of pqh_workflow.valid_user_openingto allow multi messaging |
1500 -- ----------------------------------------------------------------------------
1501 procedure valid_user_opening(p_business_group_id           in number    default null,
1502                              p_short_name                  in varchar2  ,
1503                              p_transaction_id              in number    default null,
1504                              p_routing_history_id          in number    default null,
1505                              p_wf_transaction_category_id     out nocopy number,
1506                              p_glb_transaction_category_id    out nocopy number,
1507                              p_role_id                        out nocopy number,
1508                              p_role_template_id               out nocopy number,
1509                              p_status_flag                    out nocopy varchar2) is
1510 l_result varchar2(100);
1511 BEGIN
1512     --
1513     -- Enable multi-messaging
1514     hr_multi_message.enable_message_list;
1515     --
1516      pqh_workflow.valid_user_opening(
1517                      p_business_group_id           => p_business_group_id,
1518                      p_short_name                  => p_short_name,
1519                      p_transaction_id              => p_transaction_id,
1520                      p_routing_history_id          => p_routing_history_id,
1521                      p_wf_transaction_category_id  => p_wf_transaction_category_id,
1522                      p_glb_transaction_category_id => p_glb_transaction_category_id,
1523                      p_role_id                     => p_role_id,
1524                      p_role_template_id            => p_role_template_id,
1525                      p_status_flag                 => p_status_flag );
1526 
1527        if (p_status_flag <> 0 and hr_multi_message.exception_add ) then
1528             hr_utility.raise_error;
1529        end if;
1530 
1531     -- Get the return status and disable multi-messaging
1532     l_result := hr_multi_message.get_return_status_disable;
1533 
1534 
1535 exception
1536     when hr_multi_message.error_message_exist then
1537        l_result := hr_multi_message.get_return_status_disable;
1538     when others then
1539        raise;
1540 
1541 END valid_user_opening;
1542 
1543 -- ----------------------------------------------------------------------------
1544 -- |------------------------< get_folder_unit >-------------------------------|
1545 -- | Function to return Folder Unit Desciption for Bdgt_Unit_Id. Bug #3027076.|
1546 -- ----------------------------------------------------------------------------
1547 FUNCTION get_folder_unit (p_budget_unit_id IN NUMBER)
1548 RETURN  VARCHAR2 IS
1549 --Cursor to fetch Folder Unit Desc
1550   CURSOR csr_folder_unit_desc IS
1551   SELECT stt.shared_type_name
1552     FROM per_shared_types st, per_shared_types_tl stt
1553    WHERE st.lookup_type       = 'BUDGET_MEASUREMENT_TYPE'
1554      AND(st.business_group_id = HR_GENERAL.get_business_group_id OR
1555          st.business_group_id IS NULL)
1556      AND st.shared_type_id    = p_budget_unit_id
1557      AND stt.shared_type_id   = st.shared_type_id
1558      AND stt.language = USERENV('LANG');
1559 --Local Variables
1560   l_folder_unit_desc VARCHAR2(80);
1561 BEGIN
1562   l_folder_unit_desc := NULL;
1563   OPEN csr_folder_unit_desc;
1564   FETCH csr_folder_unit_desc INTO l_folder_unit_desc;
1565   CLOSE csr_folder_unit_desc;
1566   RETURN l_folder_unit_desc;
1567 EXCEPTION
1568   WHEN OTHERS THEN
1569     l_folder_unit_desc := NULL;
1570     RETURN l_folder_unit_desc;
1571 END get_folder_unit;
1572 
1573 End pqh_bdgt_realloc_utility;