[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_PREP_APPRV_PKG
Source
1 PACKAGE BODY IGI_ITR_PREP_APPRV_PKG as
2 -- $Header: igiitrub.pls 120.6.12020000.1 2012/06/27 10:58:36 appldev ship $
3 --
4
5
6 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 l_state_level number := FND_LOG.LEVEL_STATEMENT;
8 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
9 l_event_level number := FND_LOG.LEVEL_EVENT;
10 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
11 l_error_level number := FND_LOG.LEVEL_ERROR;
12 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
13 l_path VARCHAR2(50):= 'IGI.PLSQL.igiitrub.IGI_ITR_PREP_APPRV_PKG.';
14
15 -- ****************************************************************************
16 -- Private procedure: Display diagnostic message
17 -- ****************************************************************************
18 PROCEDURE diagn_msg (p_level IN NUMBER, p_path IN VARCHAR2, p_mesg IN VARCHAR2 ) IS
19 BEGIN
20 IF (p_level >= l_debug_level ) THEN
21 FND_LOG.STRING (p_level , l_path || p_path , p_mesg );
22 END IF;
23 END ;
24
25
26
27 --
28 -- ****************************************************************************
29 -- Private function: Get authorization limit..--
30 --****************************************************************************
31 FUNCTION get_authorization_limit (p_employee_id NUMBER,
32 p_set_of_books_id NUMBER) RETURN NUMBER IS
33 l_limit NUMBER;
34 BEGIN
35
36 SELECT nvl(authorization_limit, 0)
37 INTO l_limit
38 FROM GL_AUTHORIZATION_LIMITS
39 WHERE employee_id = p_employee_id
40 AND ledger_id = p_set_of_books_id;
41
42 return (l_limit);
43
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 l_limit := 0;
47 return (l_limit);
48 WHEN OTHERS
49 THEN
50 IF ( l_unexp_level >= l_debug_level) THEN
51 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
52 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
53 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
54 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrub.IGI_ITR_PREP_APPRV_PKG.get_authorization_limit',TRUE);
55 END IF;
56 raise_application_error (-20001,'IGI_ITR_PREP_APPRV_PKG.get_authorization_limit'||SQLERRM);
57
58 END get_authorization_limit;
59
60
61 --
62 -- ********************************************************************
63 -- can_preparer_approve
64 -- *********************************************************************
65 --
66 -- finds out whether preparer is authorised to approve AND has enough
67 -- approval limit to approve the service line on the cross charge
68 -- returns a value of 'Y' or 'N'
69 --
70 PROCEDURE can_preparer_approve(p_cc_id NUMBER
71 ,p_cc_line_num NUMBER
72 ,p_preparer_fnd_user_id NUMBER
73 ,p_sob_id NUMBER
74 ,p_prep_can_approve OUT NOCOPY VARCHAR2
75 )
76 IS
77 l_cc_approval_amt NUMBER;
78 l_preparer_id NUMBER;
79 l_originator_approve VARCHAR2(1);
80 l_profile_option_val fnd_profile_option_values.profile_option_value%TYPE;
81 l_authorization_limit NUMBER;
82
83 BEGIN
84
85 -- get the amount of the service line and populate the cross charge
86 -- approval amount.
87 SELECT abs(nvl(ITRL.entered_dr,0) - nvl(ITRL.entered_cr,0))
88 INTO l_cc_approval_amt
89 FROM IGI_ITR_CHARGE_LINES ITRL
90 WHERE ITRL.it_header_id = p_cc_id
91 AND ITRL.it_line_num = p_cc_line_num;
92
93 diagn_msg(l_state_level,'can_preparer_approve','l_cc_approval_amt ='||l_cc_approval_amt);
94
95 -- get employee id of the preparer
96 SELECT employee_id
97 INTO l_preparer_id
98 FROM fnd_user
99 WHERE user_id = p_preparer_fnd_user_id;
100
101 diagn_msg(l_state_level,'can_preparer_approve','l_preparer_id ='||l_preparer_id);
102
103
104 -- find if the originator can approve is set to yes or no
105 SELECT nvl(originator_approve_flag,'N')
106 INTO l_originator_approve
107 FROM igi_itr_charge_setup
108 WHERE set_of_books_id = p_sob_id;
109
110 diagn_msg(l_state_level,'can_preparer_approve','originator_approve_flag ='||l_originator_approve);
111
112 -- get authorization limit of preparer
113 l_authorization_limit := get_authorization_limit(l_preparer_id,
114 p_sob_id);
115
116
117 diagn_msg(l_state_level,'can_preparer_approve','l_authorization_limit ='||l_authorization_limit);
118
119 IF (l_originator_approve = 'Y') AND
120 (l_authorization_limit >= l_cc_approval_amt) THEN
121 p_prep_can_approve := 'Y';
122 ELSE
123 p_prep_can_approve := 'N';
124 END IF;
125
126 EXCEPTION
127 WHEN OTHERS
128 THEN
129 /* ssemwal for NOCOPY begin(1) */
130 p_prep_can_approve := Null;
131 /* ssemwal for NOCOPY end(1) */
132 IF ( l_unexp_level >= l_debug_level) THEN
133 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
134 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
135 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
136 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrub.IGI_ITR_PREP_APPRV_PKG.can_preparer_approve',TRUE);
137 END IF;
138 raise_application_error
139 (-20001,'IGI_ITR_PREP_APPRV_PKG.can_preparer_approve'||SQLERRM);
140
141 END can_preparer_approve;
142
143
144 END IGI_ITR_PREP_APPRV_PKG;