1 PACKAGE BODY PO_PCARD_PKG AS
2 /* $Header: POPCARDB.pls 120.0 2005/06/01 15:49:28 appldev noship $ */
3 /* Contains the functions that are used to support supplier and employee
4 * pcards*/
5
6 /* is_pcard_valid_and_active returns true if the pcard_id is active and not
7 * expired. If it is inactive or expired then return false.
8 */
9 function is_pcard_valid_and_active(x_pcard_id in number) return boolean is
10 x_valid number :=0;
11 begin
12 begin
13 select 1
14 into x_valid
15 from ap_cards ac
16 where ac.card_id = x_pcard_id
17 and (card_expiration_date is null or
18 card_expiration_date >= trunc(sysdate)) -- <HTMLAC>
19 and (INACTIVE_DATE is null or
20 INACTIVE_DATE >= trunc(sysdate) ); -- <HTMLAC>
21
22 return TRUE; /* It is active and not expired */
23 exception
24 when no_data_found then
25 return FALSE; /* It is either expired or inactive*/
26 when others then
27 raise;
28 end;
29
30 end is_pcard_valid_and_active;
31
32 -----------------------------------------------------------------------<HTMLAC>
33 -------------------------------------------------------------------------------
34 --Start of Comments
35 --Name: get_pcard_valid_active_tbl
36 --Pre-reqs:
37 -- None.
38 --Modifies:
39 -- None.
40 --Locks:
41 -- None.
42 --Function:
43 -- Bulk version of is_pcard_valid_and_active function. Takes in a table of
44 -- P-Card IDs and returns a table 'Y' and 'N' indicating whether the
45 -- corresponding ID in the input table is a valid and active P-Card.
46 --Parameters:
47 --IN:
48 --p_pcard_id_tbl
49 -- PO_TBL_NUMBER consisting of P-Card IDs to validate
50 --Returns:
51 -- PO_TBL_VARCHAR1 consisting of 'Y' or 'N' indicating whether the
52 -- corresponding ID in the input table is a valid and active P-Card.
53 --Testing:
54 -- None.
55 --End of Comments
56 -------------------------------------------------------------------------------
57 -------------------------------------------------------------------------------
58 FUNCTION get_pcard_valid_active_tbl
59 (
60 p_pcard_id_tbl IN PO_TBL_NUMBER
61 )
62 RETURN PO_TBL_VARCHAR1
63 IS
64 l_result_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
65 l_valid_pcard_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
66 l_key NUMBER;
67 l_pcard_id NUMBER;
68 l_pcard_is_valid BOOLEAN;
69 l_valid_pcards_exist BOOLEAN := false;
70
71 BEGIN
72
73 l_result_tbl.extend(p_pcard_id_tbl.COUNT);
74
75
76 -- Populate GT Table ------------------------------------------------------
77
78 l_key := PO_CORE_S.get_session_gt_nextval;
79
80 FORALL i IN 1..p_pcard_id_tbl.COUNT
81 INSERT INTO po_session_gt
82 ( key
83 , index_num1
84 )
85 VALUES
86 ( l_key
87 , p_pcard_id_tbl(i)
88 );
89
90 -- Execute Query ----------------------------------------------------------
91
92 -- Retrieve all IDs of P-Cards which are in the GT Table and are
93 -- valid (card_expiration_date) and active (inactive_date).
94 --
95 SELECT ac.card_id
96 BULK COLLECT
97 INTO l_valid_pcard_id_tbl
98 FROM ap_cards_all ac
99 , po_session_gt gt
100 WHERE ac.card_id = gt.index_num1 -- select only P-cards in GT table
101 AND gt.key = l_key -- which we inserted above
102 AND ( ( ac.card_expiration_date IS NULL )
103 OR ( ac.card_expiration_date >= trunc(sysdate) ) )
104 AND ( ( ac.inactive_date IS NULL )
105 OR ( ac.inactive_date >= trunc(sysdate) ) );
106
107 -- Set the l_valid_pcards_exist flag if the above query returned any rows.
108 --
109 IF ( l_valid_pcard_id_tbl.COUNT > 0 )
110 THEN
111 l_valid_pcards_exist := true;
112 END IF;
113
114
115 -- Clean Up GT Table ------------------------------------------------------
116
117 -- Delete all records that we inserted into the GT table.
118 --
119 DELETE FROM po_session_gt gt
120 WHERE gt.key = l_key;
121
122
123 -- Filter Results ---------------------------------------------------------
124
125 -- Loop through initial input list of P-Card IDs.
126 --
127 FOR i IN p_pcard_id_tbl.FIRST..p_pcard_id_tbl.LAST
128 LOOP
129
130 l_pcard_is_valid := false; -- initialize flag to false
131 l_pcard_id := p_pcard_id_tbl(i); -- current P-Card
132
133 -- Loop through all valid P-Cards to see if the current
134 -- P-Card exists in the table (only if there exist any valid P-Cards).
135 --
136 IF ( l_valid_pcards_exist )
137 THEN
138
139 FOR j IN l_valid_pcard_id_tbl.FIRST..l_valid_pcard_id_tbl.LAST
140 LOOP
141 IF ( l_pcard_id = l_valid_pcard_id_tbl(j) ) -- if P-Card found,
142 THEN -- mark valid flag
143 l_pcard_is_valid := true;
144 END IF;
145 END LOOP;
146
147 END IF; -- ( l_valid_pcards_exist )
148
149 -- Set result table entry to either 'Y' or 'N' depending on valid flag.
150 --
151 IF ( l_pcard_is_valid )
152 THEN
153 l_result_tbl(i) := 'Y';
154 ELSE
155 l_result_tbl(i) := 'N';
156 END IF;
157
158 END LOOP;
159
160 -- Return -----------------------------------------------------------------
161
162 return (l_result_tbl);
163
164 END get_pcard_valid_active_tbl;
165
166
167 /* is_site_pcard_enabled returns true if the vendor_id and vendor_site_id is
168 * enabled for Pcard. If not return false.
169 */
170 function is_site_pcard_enabled(x_vendor_id number,
171 x_vendor_site_id in number) return boolean is
172 x_valid number := 0;
173 begin
174 begin
175 --<Shared Proc FPJ>
176 --Modified the query to select from po_vendor_sites_all
177 --instead of po_vendor_sites.
178 select 1
179 into x_valid
180 from po_vendor_sites_all pvs
181 where pvs.VENDOR_ID = x_vendor_id
182 and pvs.vendor_site_id = x_vendor_site_id
183 and pvs.pcard_site_flag = 'Y';
184
185 return TRUE; /* Site is pcard enabled*/
186 exception
187 when no_data_found then
188 return FALSE; /* Site is not pcard enabled*/
189 when others then
190 raise;
191 end;
192
193 end is_site_pcard_enabled;
194
195 -----------------------------------------------------------------------<HTMLAC>
196 -------------------------------------------------------------------------------
197 --Start of Comments
198 --Name: get_site_pcard_enabled_tbl
199 --Pre-reqs:
200 -- None.
201 --Modifies:
202 -- None.
203 --Locks:
204 -- None.
205 --Function:
206 -- Bulk version of is_site_pcard_enabled function. Takes in a nested table
207 -- of Vendor and Vendor Site IDs and returns a table 'Y' and 'N' indicating
208 -- whether each Vendor Site is P-Card enabled.
209 --Parameters:
210 --IN:
211 --p_vendor_id_tbl
212 -- PO_TBL_NUMBER consisting of Supplier identifier
213 --p_vendor_site_id_tbl
214 -- PO_TBL_NUMBER consisting of Supplier Site identifier
215 --Returns:
216 -- PO_TBL_VARCHAR1 consisting of 'Y' or 'N' indicating whether the
217 -- corresponding Supplier Site is P-Card enabled.
218 --Testing:
219 -- None.
220 --End of Comments
221 -------------------------------------------------------------------------------
222 -------------------------------------------------------------------------------
223 FUNCTION get_site_pcard_enabled_tbl
224 (
225 p_vendor_id_tbl IN PO_TBL_NUMBER
226 , p_vendor_site_id_tbl IN PO_TBL_NUMBER
227 )
228 RETURN PO_TBL_VARCHAR1
229 IS
230 l_result_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
231
232 BEGIN
233
234 l_result_tbl.extend(p_vendor_id_tbl.COUNT);
235
236 FOR i IN p_vendor_id_tbl.FIRST..p_vendor_id_tbl.LAST
237 LOOP
238 IF ( is_site_pcard_enabled ( p_vendor_id_tbl(i)
239 , p_vendor_site_id_tbl(i) )
240 )
241 THEN
242 l_result_tbl(i) := 'Y';
243 ELSE
244 l_result_tbl(i) := 'N';
245 END IF;
246 END LOOP;
247
248 return (l_result_tbl);
249
250 END get_site_pcard_enabled_tbl;
251
252
253 /* get_vendor_pcard_info returns pcard_id for the given vendor_id and
254 * vendor_site_id for the supplier Pcard from ap_card_suppliers.
255 */
256 function get_vendor_pcard_info(x_vendor_id in number,
257 x_vendor_site_id IN number) return number IS
258 x_pcard_id number;
259 begin
260 begin
261 select card_id into x_pcard_id
262 from ap_card_suppliers
263 where vendor_id = x_vendor_id
264 and vendor_site_id = x_vendor_site_id;
265 exception
266 when no_data_found then
267 x_pcard_id := null;
268 when others then
269 raise;
270 end ;
271 return x_pcard_id;
272 end get_vendor_pcard_info;
273
274
275 /* get_valid_pcard_id returns pcard_id if the pcard_id is active,
276 * not expired and the site is pcard enabled. If not, returns null.
277 */
278 function get_valid_pcard_id(x_pcard_id in number,
279 x_vendor_id in number,
280 x_vendor_site_id in number) return number is
281 x_valid boolean;
282 x_derived_pcard_id number;
283 begin
284 if (x_pcard_id = -99999) then
285 x_derived_pcard_id := get_vendor_pcard_info(
286 x_vendor_id,x_vendor_site_id);
287 else
288 x_derived_pcard_id := x_pcard_id;
289 end if;
290
291 x_valid :=is_pcard_valid_and_active(x_derived_pcard_id);
292 if (x_valid) then
293 x_valid :=is_site_pcard_enabled(x_vendor_id,x_vendor_site_id);
294 end if;
295 if (x_valid = FALSE) then
296 x_derived_pcard_id := null;
297 end if;
298 return x_derived_pcard_id;
299 end get_valid_pcard_id;
300
301
302 END PO_PCARD_PKG;
[Home] [Help]