1 package body pay_ce_support_pkg as
2 /* $Header: pyceinsp.pkb 120.1 2005/12/09 07:09:12 adkumar noship $ */
3
4 function bank_segment_value(
5 p_external_account_id in number,
6 p_lookup_type in varchar2,
7 p_legislation_code in varchar2) return varchar2 is
8
9 l_segment_name varchar2(9);
10 l_segment_value varchar2(150);
11
12 cursor c_bank_flex_segment is
13 select decode(decode(substr(hrl.meaning,1,3),hrl.lookup_code||'_',substr(hrl.meaning,4),hrl.meaning),
14 'SEGMENT1',pea.segment1,
15 'SEGMENT2',pea.segment2,
16 'SEGMENT3',pea.segment3,
17 'SEGMENT4',pea.segment4,
18 'SEGMENT5',pea.segment5,
19 'SEGMENT6',pea.segment6,
20 'SEGMENT7',pea.segment7,
21 'SEGMENT8',pea.segment8,
22 'SEGMENT9',pea.segment9,
23 'SEGMENT10',pea.segment10,
24 'SEGMENT11',pea.segment11,
25 'SEGMENT12',pea.segment12,
26 'SEGMENT13',pea.segment13,
27 'SEGMENT14',pea.segment14,
28 'SEGMENT15',pea.segment15,
29 'SEGMENT16',pea.segment16,
30 'SEGMENT17',pea.segment17,
31 'SEGMENT18',pea.segment18,
32 'SEGMENT19',pea.segment19,
33 'SEGMENT20',pea.segment20,
34 'SEGMENT21',pea.segment21,
35 'SEGMENT22',pea.segment22,
36 'SEGMENT23',pea.segment23,
37 'SEGMENT24',pea.segment24,
38 'SEGMENT25',pea.segment25,
39 'SEGMENT26',pea.segment26,
40 'SEGMENT27',pea.segment27,
41 'SEGMENT28',pea.segment28,
42 'SEGMENT29',pea.segment29,
43 'SEGMENT30',pea.segment30,
44 'EMPTY')
45 from pay_external_accounts pea,
46 hr_lookups hrl
47 where external_account_id = p_external_account_id
48 and hrl.lookup_type = p_lookup_type
49 and hrl.lookup_code = p_legislation_code;
50 --
51 l_bank_name varchar2(80);
52 --
53 begin
54
55 if p_external_account_id is null then
56 return null;
57 end if;
58
59 open c_bank_flex_segment;
60 fetch c_bank_flex_segment into l_segment_value;
61 close c_bank_flex_segment;
62
63 if l_segment_value = 'EMPTY' then
64
65 return null;
66
67 else
68 --
69 -- Bug 1532646 - the error in the bug is that Sort Code is being returned
70 -- where Bank Name should be. This is easily fixed by updating the lookup/
71 -- segment mappings. However, this then causes the following problem:
72 -- The valueset for Bank Name on the GB Bank key flex stores
73 -- the lookup_code, rather than the meaning. So, if the legislation is GB
74 -- and the lookup_type is BANK_NAME, then go get the meaning from hr_lookups
75 -- so the bank name is inserted into the Cash Management table.
76 --
77 if p_legislation_code = 'GB' then
78 --
79 if p_lookup_type = 'BANK_NAME' then
80 --
81 select meaning
82 into l_bank_name
83 from hr_lookups
84 where lookup_code = l_segment_value
85 and lookup_type = 'GB_BANKS'
86 and application_id between 800 and 899;
87 --
88 l_segment_value := l_bank_name;
89 --
90 end if;
91 --
92 end if;
93
94 --
95 -- Ensure that returned segment value <= 30 bytes in length and that it
96 -- does not contain the trailing blanks to replace a 'broken' multi-byte
97 -- character.
98 --
99 l_segment_value := rtrim(substrb(l_segment_value, 1, 60));
100
101 return l_segment_value;
102
103 end if;
104
105 exception
106 when others then
107
108 raise;
109
110 end bank_segment_value;
111
112
113 function pay_and_ce_licensed return boolean is
114
115 l_ret_var boolean;
116 l_status_ce varchar2(1);
117 l_status_pay varchar2(1);
118 l_industry varchar2(1);
119 l_oracle_schema varchar2(30);
120
121 begin
122
123 hr_utility.set_location('pay_ce_support_pkg.pay_and_ce_licensed', 10);
124
125 l_ret_var := fnd_installation.get_app_info('PAY',
126 l_status_pay,
127 l_industry,
128 l_oracle_schema);
129
130 l_ret_var := fnd_installation.get_app_info('CE',
131 l_status_ce,
132 l_industry,
133 l_oracle_schema);
134
135 if (l_status_pay = 'I' and l_status_ce = 'I') then
136
137 return true;
138
139 else
140
141 return false;
142
143 end if;
144
145 end pay_and_ce_licensed;
146
147
148 function session_date return date is
149
150 l_session_date date;
151
152 cursor c_session_date is
153 select effective_date
154 from fnd_sessions
155 where session_id = userenv('sessionid');
156
157 begin
158
159 open c_session_date;
160 fetch c_session_date into l_session_date;
161 if c_session_date%notfound then
162
163 l_session_date := trunc(sysdate);
164
165 end if;
166
167 return l_session_date;
168
169 end session_date;
170
171
172 function payment_status(p_payment_id number) return varchar2 is
173
174 cursor c_payment_status is
175 select
176 hrl.meaning
177 from
178 hr_lookups hrl,
179 pay_ce_reconciled_payments pcrp
180 where
181 hrl.lookup_code = pcrp.status_code
182 and pcrp.assignment_action_id = p_payment_id
183 and hrl.lookup_type = 'RECON_STATUS';
184
185 l_payment_status varchar2(80);
186
187 begin
188
189 open c_payment_status;
190 fetch c_payment_status into l_payment_status;
191 if c_payment_status%found then
192
193 close c_payment_status;
194 return l_payment_status;
195
196 else
197
198 close c_payment_status;
199 return (hr_general.decode_lookup('RECON_STATUS', 'U'));
200
201 end if;
202
203 end payment_status;
204
205 function lookup_meaning(p_meaning varchar2,p_code varchar2) return varchar2 is
206 l_meaning hr_lookups.meaning%type;
207 begin
208 --
209 select decode(substr(p_meaning,1,3),p_code||'_',substr(p_meaning,4),p_meaning)
210 into l_meaning
211 from dual;
212 --
213 return l_meaning;
214 --
215 end lookup_meaning;
216
217 end pay_ce_support_pkg;
[Home] [Help]