1 PACKAGE BODY GMF_AP_GET_INVOICE_PRICE AS
2 /* $Header: gmfinvpb.pls 115.7 2002/12/04 17:04:23 umoogala ship $ */
3 CURSOR cur_ap_get_invoice_price1 (
4 startdate in date,
5 enddate in date,
6 invoicenum in varchar2,
7 invoicelineno in number,
8 invoiceid in number,
9 vendorid in number,
10 invoicetype in varchar2,
14 id.distribution_line_number,
11 linetype in varchar2 ) IS
12 SELECT DISTINCT
13 i.invoice_num,
15 i.invoice_id,
16 i.vendor_id,
17 i.invoice_type_lookup_code,
18 i.approval_status,
19 id.parent_invoice_id,
20 pl.po_header_id,
21 pl.po_line_id,
22 pd.line_location_id,
23 id.line_type_lookup_code,
24 msi.segment1,
25 pl.item_description,
26 id.quantity_invoiced,
27 pl.unit_meas_lookup_code,
28 id.amount,
29 id.base_amount,
30 msi.list_price_per_unit,
31 id.unit_price,
32 i.invoice_currency_code,
33 sob.currency_code,
34 id.exchange_rate,
35 i.invoice_date,
36 i.gl_date,
37 id.creation_date,
38 id.created_by,
39 id.last_update_date,
40 id.last_updated_by,
41 i.cancelled_date
42 FROM
43 ap_invoices_all i,
44 ap_invoice_distributions_all id,
45 po_distributions_all pd,
46 cpg_oragems_mapping map,
47 po_lines_all pl,
48 mtl_system_items msi,
49 gl_sets_of_books sob
50 WHERE i.invoice_num = invoicenum
51 AND i.invoice_id LIKE NVL ( invoiceid, i.invoice_id)
52 AND i.vendor_id LIKE NVL ( vendorid, i.vendor_id)
53 AND i.invoice_type_lookup_code = invoicetype
54 AND i.invoice_id = id.invoice_id
55 AND id.distribution_line_number LIKE
56 NVL ( invoicelineno, id.distribution_line_number)
57 AND id.po_distribution_id = pd.po_distribution_id
58 AND id.line_type_lookup_code LIKE
59 NVL( linetype, id.line_type_lookup_code)
60 AND i.set_of_books_id = sob.set_of_books_id
61 AND pl.po_line_id = pd.po_line_id
62 AND pl.item_id = msi.inventory_item_id
63 AND map.po_line_location_id = pd.line_location_id
64 AND map.po_line_id = pd.po_line_id
65 AND map.po_header_id = pd.po_header_id
66 AND id.last_update_date BETWEEN
67 NVL( startdate, id.last_update_date) AND
68 NVL( enddate, id.last_update_date);
69
70 CURSOR cur_ap_get_invoice_price2 (
71 startdate in date,
72 enddate in date,
73 invoicenum in varchar2,
74 invoicelineno in number,
75 invoiceid in number,
76 vendorid in number,
77 invoicetype in varchar2,
78 linetype in varchar2 ) IS
79 SELECT DISTINCT
80 i.invoice_num,
81 id.distribution_line_number,
82 i.invoice_id,
83 i.vendor_id,
84 i.invoice_type_lookup_code,
85 i.approval_status,
86 id.parent_invoice_id,
87 pl.po_header_id,
88 pl.po_line_id,
89 pd.line_location_id,
90 id.line_type_lookup_code,
91 msi.segment1,
92 pl.item_description,
93 id.quantity_invoiced,
94 pl.unit_meas_lookup_code,
95 id.amount,
96 id.base_amount,
97 msi.list_price_per_unit,
98 id.unit_price,
99 i.invoice_currency_code,
100 sob.currency_code,
101 id.exchange_rate,
102 i.invoice_date,
103 i.gl_date,
104 id.creation_date,
105 id.created_by,
106 id.last_update_date,
107 id.last_updated_by,
108 i.cancelled_date
109 FROM
110 ap_invoices_all i,
111 ap_invoice_distributions_all id,
112 po_distributions_all pd,
113 cpg_oragems_mapping map,
114 po_lines_all pl,
115 mtl_system_items msi,
116 gl_sets_of_books sob
117 WHERE i.invoice_num LIKE NVL( invoicenum, i.invoice_num)
118 AND i.invoice_id LIKE NVL ( invoiceid, i.invoice_id)
119 AND i.vendor_id LIKE NVL ( vendorid, i.vendor_id)
120 AND i.invoice_type_lookup_code = invoicetype
121 AND i.invoice_id = id.invoice_id
122 AND id.distribution_line_number LIKE
123 NVL ( invoicelineno, id.distribution_line_number)
124 AND id.po_distribution_id = pd.po_distribution_id
125 AND id.line_type_lookup_code LIKE
126 NVL( linetype, id.line_type_lookup_code)
127 AND i.set_of_books_id = sob.set_of_books_id
128 AND pl.po_line_id = pd.po_line_id
129 AND pl.item_id = msi.inventory_item_id
130 AND map.po_line_location_id = pd.line_location_id
131 AND map.po_line_id = pd.po_line_id
132 AND map.po_header_id = pd.po_header_id
133 AND id.last_update_date BETWEEN
134 NVL( startdate, id.last_update_date) AND
135 NVL( enddate, id.last_update_date);
136
137 PROCEDURE proc_ap_get_invoice_price (
138 start_date in date,
139 end_date in date,
140 invoicenum in out nocopy varchar2,
141 invoice_line_no in out nocopy number,
142 invoiceid in out nocopy number,
143 vendor_id in out nocopy number,
144 invoice_type in out nocopy varchar2,
145 previous_invoice_num out nocopy varchar2,
146 invoice_status in out nocopy varchar2,
147 po_header_id in out nocopy number,
148 po_line_id in out nocopy number,
149 po_line_location_id in out nocopy number,
150 line_type in out nocopy varchar2,
151 item_no out nocopy varchar2,
152 item_desc out nocopy varchar2,
153 invoice_qty out nocopy number,
154 invoice_uom out nocopy varchar2,
155 invoice_amount out nocopy number,
156 invoice_base_amount out nocopy number,
157 base_unit_price out nocopy number,
158 unit_price out nocopy number,
159 billing_currency out nocopy varchar2,
160 base_currency out nocopy varchar2,
161 exchange_rate out nocopy number,
162 invoice_date out nocopy date,
163 gl_date out nocopy date,
164 creation_date out nocopy date,
165 created_by out nocopy number,
166 last_update_date out nocopy date,
167 last_updated_by out nocopy number ,
168 t_cancelled_date in out nocopy date,
169 t_match_status_flag in out nocopy varchar2,
170 t_hold_count in out nocopy number,
171 approval out nocopy varchar2,
172 statuscode out nocopy number,
173 rowtofetch in out nocopy number) IS
174 /* created_by number;*/
175 /* last_updated_by number;*/
176 tmp_invoice_id number;
177 t_match_status_flag2 varchar2(3);
178 /** MC BUG# 1554483 **/
179 /** UOM CONVERSION... create a variable to hold unit_of_measure and a cursor **/
180 t_unit_of_measure MTL_UNITS_OF_MEASURE.unit_of_measure%type;
181
182 CURSOR cr_um_code is
183 SELECT um_code from sy_uoms_mst
184 WHERE unit_of_measure = t_unit_of_measure ;
185
186 BEGIN
187 IF invoicenum IS NOT NULL THEN
188 IF NOT cur_ap_get_invoice_price1%ISOPEN THEN
189 OPEN cur_ap_get_invoice_price1(
190 start_date,
191 end_date,
192 invoicenum,
193 invoice_line_no,
194 invoiceid,
195 vendor_id,
196 invoice_type,
197 line_type);
198 END IF;
199 ELSE
200 IF NOT cur_ap_get_invoice_price2%ISOPEN THEN
201 OPEN cur_ap_get_invoice_price2(
202 start_date,
203 end_date,
204 invoicenum,
205 invoice_line_no,
206 invoiceid,
207 vendor_id,
208 invoice_type,
209 line_type);
210 END IF;
211 END IF;
212
213 IF invoicenum IS NOT NULL THEN
214 BEGIN
215 FETCH cur_ap_get_invoice_price1
216 INTO invoicenum,
217 invoice_line_no,
218 invoiceid,
219 vendor_id,
220 invoice_type,
221 invoice_status,
222 tmp_invoice_id,
223 po_header_id,
224 po_line_id,
225 po_line_location_id,
226 line_type,
227 item_no,
228 item_desc,
229 invoice_qty,
230 t_unit_of_measure, -- MC BUG# 1554483 invoice_uom,
231 invoice_amount,
232 invoice_base_amount,
233 base_unit_price,
234 unit_price,
235 billing_currency,
236 base_currency,
237 exchange_rate,
238 invoice_date,
239 gl_date,
240 creation_date,
241 created_by,
242 last_update_date,
243 last_updated_by,
244 t_cancelled_date;
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 statuscode := 100;
248 WHEN OTHERS THEN
249 statuscode := SQLCODE;
250 END;
251 IF ( cur_ap_get_invoice_price1%NOTFOUND ) THEN
252 statuscode := 100;
253 END IF;
254 IF (cur_ap_get_invoice_price1%NOTFOUND) OR rowtofetch = 1 THEN
255 CLOSE cur_ap_get_invoice_price1;
256 END IF;
257 ELSE
258 BEGIN
259 FETCH cur_ap_get_invoice_price2
260 INTO invoicenum,
261 invoice_line_no,
262 invoiceid,
263 vendor_id,
264 invoice_type,
265 invoice_status,
266 tmp_invoice_id,
267 po_header_id,
268 po_line_id,
269 po_line_location_id,
270 line_type,
271 item_no,
272 item_desc,
273 invoice_qty,
274 t_unit_of_measure, -- MC BUG# 1554483 invoice_uom,
275 invoice_amount,
276 invoice_base_amount,
277 base_unit_price,
278 unit_price,
279 billing_currency,
280 base_currency,
281 exchange_rate,
282 invoice_date,
283 gl_date,
284 creation_date,
285 created_by,
286 last_update_date,
287 last_updated_by,
288 t_cancelled_date;
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 statuscode := 100;
292 WHEN OTHERS THEN
293 statuscode := SQLCODE;
294 END;
295 IF ( cur_ap_get_invoice_price2%NOTFOUND ) THEN
296 statuscode := 100;
297 END IF;
298 IF (cur_ap_get_invoice_price2%NOTFOUND) OR rowtofetch = 1 THEN
299 CLOSE cur_ap_get_invoice_price2;
300 END IF;
301 END IF;
302
303 IF tmp_invoice_id IS NOT NULL THEN
304 SELECT invoice_num
305 INTO previous_invoice_num
306 FROM ap_invoices_all
307 WHERE invoice_id = tmp_invoice_id;
308 END IF;
309 /** MC BUG# 1554483 **/
310 OPEN cr_um_code;
311 FETCH cr_um_code into invoice_uom;
312 CLOSE cr_um_code;
313
314 /* added_by := pkg_gl_get_currencies.get_name ( created_by );*/
315 /* modified_by := pkg_gl_get_currencies.get_name (last_updated_by);*/
316 t_hold_count := 0;
317
318 IF invoicenum IS NOT NULL THEN
319 select count(*)
320 into t_hold_count
321 from ap_holds_all aph, ap_invoices_all api
322 where
323 api.invoice_num = invoicenum
324 and api.invoice_id = aph.invoice_id
325 and aph.release_lookup_code is null;
326 ELSE
327 /* Bug 2539636 : This condition never occurs. Also, this sql is the top one
328 performance repository.
329 select count(*)
330 into t_hold_count
331 from ap_holds_all aph, ap_invoices_all api
332 where
333 api.invoice_id = aph.invoice_id
334 and aph.release_lookup_code is null;
335 */
336 NULL;
337 END IF;
338
339 /**
340 * 25-Feb-2000 Rajesh Seshadri Bug 1172792 - CBO changes
341 * changed "column_name = nvl(input, column_name)" clause
342 * to "(column_name = input_value or input_value is null)"
343 * and in the second query modified the inner query to
344 * refer to the outer table also.
345 */
346
347 IF invoiceid IS NOT NULL THEN
348 select min(decode(match_status_flag, 'N', '1N', 'T', '2T',
349 'A', '3A', '4'))
350 into t_match_status_flag
351 from ap_invoice_distributions_all
352 where invoice_id = invoiceid;
353 ELSE
354 /* Bug 2539636 : This condition never occurs. Also, this sql is the top one
355 performance repository.
356 select min(decode(match_status_flag, 'N', '1N', 'T', '2T',
357 'A', '3A', '4'))
358 into t_match_status_flag
359 from ap_invoice_distributions_all;
360 */
361 NULL;
362 END IF;
363
364 BEGIN
365 IF invoiceid IS NOT NULL THEN
366 select distinct '1N' /* Need just one value ONLY (if it exist)!!! */
367 into t_match_status_flag2
368 from ap_invoice_distributions_all
369 where invoice_id = invoiceid
370 and match_status_flag is null
371 and exists (select 'There are tested and untested lines'
372 from ap_invoice_distributions_all
373 where invoice_id = invoiceid
374 and match_status_flag in ('T', 'A'));
375 ELSE
376 /* Bug 2539636 : This condition never occurs. Also, this sql is the top one
377 performance repository.
378 select distinct '1N' Need just one value ONLY (if it exist)!!!
379 into t_match_status_flag2
380 from ap_invoice_distributions_all
381 where match_status_flag is null
382 and exists (select 'There are tested and untested lines'
383 from ap_invoice_distributions_all
384 where match_status_flag in ('T', 'A')
385 and rownum < 2);
386 */
387 NULL;
388 END IF;
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 t_match_status_flag2 := '4';
392 END;
393 if t_match_status_flag > t_match_status_flag2 then
394 t_match_status_flag := t_match_status_flag2;
395 end if;
396 if t_match_status_flag in ('3A', '2T') and t_hold_count = 0 then
397 approval := 'Approved';
398 elsif t_match_status_flag = '1N' then
399 approval := 'Needs Reapproval';
400 elsif t_match_status_flag in ('4', '') then
401 approval := 'Never Approved';
402 else
403 approval := 'Invoice Does Not Exist ';
404 end if;
405 if t_cancelled_date is null then
406 null;
407 else
408 approval := 'Cancelled';
409 end if;
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 statuscode := 100;
413 WHEN OTHERS THEN
414 statuscode := SQLCODE;
415 END proc_ap_get_invoice_price;
416 END GMF_AP_GET_INVOICE_PRICE;
[Home] [Help]