[Home] [Help]
PACKAGE BODY: APPS.RCV_VALIDATE_PO
Source
1 PACKAGE BODY RCV_VALIDATE_PO AS
2 /* $Header: RCVTIR4B.pls 120.9 2011/11/04 13:03:52 sadibhat ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: prevent_doc_action()
7
8 ===========================================================================*/
9
10 FUNCTION prevent_doc_action( x_Entity IN varchar2,
11 x_Action IN varchar2,
12 x_Po_num IN varchar2,
13 x_Org_id IN NUMBER,
14 x_Po_header_id IN NUMBER,
15 x_Release_num IN NUMBER,
16 x_Release_id IN NUMBER,
17 x_Po_line_num IN NUMBER,
18 x_Po_line_id IN NUMBER,
19 x_Shipment_num IN NUMBER,
20 x_Shipment_line_id IN NUMBER,
21 x_Item_id IN NUMBER,
22 x_item_num IN varchar2,
23 x_Item_revision IN varchar2,
24 x_Item_description IN varchar2,
25 x_Unit_of_measure IN varchar2
26 ) RETURN BOOLEAN IS
27 l_count number := 0;
28 x_progress VARCHAR2(3) := NULL;
29
30 begin
31
32 x_progress := '010';
33
34 IF x_entity = 'Header' then
35
36 x_progress := '020';
37
38 begin
39
40 SELECT Count(1)
41 INTO l_count
42 FROM rcv_transactions_interface
43 WHERE transaction_status_code = 'PENDING'
44 and processing_status_code <> 'ERROR'
45 AND source_document_code = 'PO'
46 AND (po_header_id = x_po_header_id OR (document_num = x_po_num AND org_id = x_Org_id))
47 AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num);
48
49 EXCEPTION
50
51 WHEN NO_DATA_FOUND THEN
52 RETURN (FALSE);
53
54 END;
55
56 x_progress := '030';
57
58 ELSIF x_entity = 'Line' THEN
59
60 x_progress := '040';
61
62 begin
63
64 SELECT Count(1)
65 INTO l_count
66 FROM rcv_transactions_interface
67 WHERE transaction_status_code = 'PENDING'
68 and processing_status_code <> 'ERROR'
69 AND source_document_code = 'PO'
70 AND ( po_header_id = nvl(x_po_header_id,po_header_id)
71 OR (document_num = nvl(x_po_num,document_num) AND org_id = nvl(x_Org_id,org_id)) )
72 AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num)
73 AND ( po_line_id = x_po_line_id OR document_line_num = x_po_line_num
74 OR item_id = x_item_id OR item_num = x_item_num OR item_description = x_Item_description);
75
76 EXCEPTION
77
78 WHEN NO_DATA_FOUND THEN
79 RETURN (FALSE);
80
81 END;
82
83 x_progress := '050';
84
85 ELSIF x_entity = 'Shipment' THEN
86
87 x_progress := '060';
88
89 begin
90
91 SELECT Count(1)
92 INTO l_count
93 FROM rcv_transactions_interface
94 WHERE transaction_status_code = 'PENDING'
95 and processing_status_code <> 'ERROR'
96 AND source_document_code = 'PO'
97 AND ( po_header_id = nvl(x_po_header_id,po_header_id)
98 OR (document_num = nvl(x_po_num,document_num) AND org_id = nvl(x_Org_id,org_id)) )
99 AND (nvl(x_Release_id, po_release_id) = po_release_id OR nvl(x_Release_num, release_num) = release_num)
100 AND ( po_line_id = nvl(x_po_line_id,po_line_id) OR document_line_num = nvl(x_po_line_num,document_line_num)
101 OR ( item_id = nvl(x_item_id,item_id) OR item_num = nvl(x_item_num,item_num)
102 OR item_description = nvl(item_description,x_Item_description) ) )
103 AND (po_line_location_id = x_Shipment_line_id OR document_shipment_line_num = x_shipment_num);
104
105
106 EXCEPTION
107
108 WHEN NO_DATA_FOUND THEN
109 RETURN (FALSE);
110
111 END;
112
113 END IF;
114
115 x_progress := '070';
116
117 if l_count <>0 then
118
119 return (TRUE);
120
121 end if;
122
123 EXCEPTION
124
125 WHEN OTHERS THEN
126
127 po_message_s.sql_error('prevent_doc_action', x_progress,sqlcode);
128 RAISE;
129
130 End prevent_doc_action;
131
132
133 /*===========================================================================
134
135 PROCEDURE NAME: validate_novation_receipts()
136
137 ===========================================================================*/
138
139 PROCEDURE validate_novation_receipts (
140 p_request_id IN NUMBER,
141 p_vendor_id IN NUMBER,
142 p_novation_date IN DATE,
143 p_header_id_tbl IN PO_TBL_NUMBER,
144 x_validation_results IN OUT NOCOPY po_multi_mod_val_results_type,
145 x_validation_result_type OUT NOCOPY VARCHAR2,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_error_msg OUT NOCOPY VARCHAR2 ) IS
148
149 CURSOR c_rt_invioce (p_po_header_id IN NUMBER) IS
150 SELECT DISTINCT shipment_header_id
151 FROM (
152 SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsl.quantity_received
153 FROM rcv_transactions rt,
154 rcv_shipment_lines rsl,
155 po_line_locations_all poll
156 WHERE rt.transaction_type IN ('RECEIVE', 'MATCH')
157 AND rt.shipment_line_id = rsl.shipment_line_id
158 AND rsl.po_line_location_id = poll.line_location_id
159 AND rsl.po_header_id = poll.po_header_id
160 AND rt.po_line_location_id = poll.line_location_id
161 AND rt.po_header_id = poll.po_header_id
162 AND poll.match_option = 'R'
163 AND rt.source_document_code = 'PO'
164 AND rt.quantity IS NOT NULL
165 AND rt.po_header_id = p_po_header_id
166 GROUP BY rsl.shipment_header_id, rsl.shipment_line_id, rsl.quantity_received
167 HAVING rsl.quantity_received > Sum(Nvl(rt.quantity_billed, 0))
168 UNION
169 SELECT rsl.shipment_header_id, rsl.shipment_line_id, rsl.amount_received
170 FROM rcv_transactions rt,
171 rcv_shipment_lines rsl,
172 po_line_locations_all poll
173 WHERE rt.transaction_type IN ('RECEIVE', 'MATCH')
174 AND rt.shipment_line_id = rsl.shipment_line_id
175 AND rsl.po_line_location_id = poll.line_location_id
176 AND rsl.po_header_id = poll.po_header_id
177 AND rt.po_line_location_id = poll.line_location_id
178 AND rt.po_header_id = poll.po_header_id
179 AND poll.match_option = 'R'
180 AND rt.source_document_code = 'PO'
181 AND rt.amount IS NOT NULL
182 AND rt.po_header_id = p_po_header_id
183 GROUP BY rsl.shipment_header_id, rsl.shipment_line_id, rsl.amount_received
184 HAVING rsl.amount_received > Sum(Nvl(rt.amount_billed, 0)) ) ;
185
186
187 CURSOR c_rt_novation (p_po_header_id IN NUMBER, p_date IN DATE) IS
188 SELECT rsh.shipment_header_id,
189 rsh.receipt_num,
190 Min(rt.transaction_date)
191 FROM rcv_transactions rt,
192 rcv_shipment_headers rsh
193 WHERE rt.shipment_header_id = rsh.shipment_header_id
194 AND rt.po_header_id = p_po_header_id
195 AND rt.source_document_code = 'PO'
196 AND rt.transaction_type IN ('RECEIVE', 'MATCH')
197 AND rt.transaction_date >= p_date
198 GROUP BY rsh.shipment_header_id,
199 rsh.receipt_num;
200
201
202 l_po_header_id po_headers_all.po_header_id%TYPE;
203 l_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
204 l_receipt_date rcv_transactions.transaction_date%TYPE;
205 l_receipt_number rcv_shipment_headers.receipt_num%TYPE;
206 l_multi_mod_val_result_id NUMBER;
207 l_receipt_amount NUMBER;
208 l_exception_type VARCHAR2(200) := NULL ;
209 l_message_name VARCHAR2(200) := NULL;
210 l_rti_count NUMBER;
211 l_progress VARCHAR2(5);
212 i NUMBER;
213
214
215 BEGIN
216
217 i := 1;
218 l_progress := '000';
219 x_validation_result_type := 'SUCCESS';
220 x_validation_results := po_multi_mod_val_results_type.new_instance();
221
222 FOR i IN 1 .. p_header_id_tbl.Count LOOP
223
224 l_po_header_id := p_header_id_tbl(i);
225 l_progress := '010';
226
227 OPEN c_rt_invioce(l_po_header_id);
228 LOOP
229
230 l_progress := '020';
231 FETCH c_rt_invioce INTO l_shipment_header_id;
232
233 IF c_rt_invioce%NOTFOUND THEN
234 EXIT;
235 END IF;
236
237 l_progress := '030';
238 SELECT rsh.receipt_num, Min(rt.transaction_date)
239 INTO l_receipt_number, l_receipt_date
240 FROM rcv_shipment_headers rsh,
241 rcv_transactions rt
242 WHERE rt.shipment_header_id = rsh.shipment_header_id
243 AND rt.transaction_type IN ('RECEIVE', 'MATCH')
244 AND rsh.shipment_header_id = l_shipment_header_id
245 GROUP BY rsh.receipt_num;
246
247 SELECT Nvl(Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) ), 0)
248 INTO l_receipt_amount
249 FROM rcv_shipment_lines rsl,
250 po_line_locations_all poll,
251 po_lines_all pol
252 WHERE rsl.po_line_location_id = poll.line_location_id
253 AND rsl.po_line_id = pol.po_line_id
254 AND pol.po_line_id = poll.po_line_id
255 AND rsl.quantity_shipped IS NOT NULL
256 AND rsl.shipment_header_id = l_shipment_header_id ;
257
258 IF l_receipt_amount = 0 THEN
259 SELECT Nvl(Sum(Nvl(rsl.amount_received, 0)), 0)
260 INTO l_receipt_amount
261 FROM rcv_shipment_lines rsl
262 WHERE rsl.quantity_shipped IS NULL
263 AND rsl.shipment_header_id = l_shipment_header_id ;
264
265 END IF;
266
267 l_progress := '040';
268
269 l_exception_type := 'PO_SUPCHG_UNINV_RCV';
270 l_message_name := NULL;
271 x_validation_result_type := 'WARNING';
272 SELECT po_multi_mod_val_results_s.nextval
273 INTO l_multi_mod_val_result_id
274 FROM dual;
275
276 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
277 p_multi_mod_request_id => p_request_id,
278 p_result_type => 'WARNING',
279 p_validation_type => 'RECEIPTS',
280 p_exception_type => l_exception_type,
281 p_document_id => l_po_header_id,
282 p_document_number => NULL,
283 p_related_document_id => l_shipment_header_id,
284 p_related_document_number => l_receipt_number,
285 p_related_document_date => l_receipt_date,
286 p_related_document_amount => l_receipt_amount,
287 p_message_application => 'PO',
288 p_message_name => l_message_name);
289
290 END LOOP; -- FETCH c_rt_invioce INTO ..
291
292 CLOSE c_rt_invioce;
293
294
295 /* If there are any open transactions in the receiving open interface for any
296 of the selected documents, generate an exception */
297 l_progress := '050';
298 l_rti_count := 0;
299 l_exception_type := NULL;
300 l_message_name := NULL;
301
302 SELECT count(*)
303 INTO l_rti_count
304 FROM rcv_transactions_interface rti
305 WHERE rti.po_header_id = l_po_header_id
306 OR ( (rti.receipt_source_code = 'VENDOR' OR rti.source_document_code = 'PO')
307 AND EXISTS
308 ( SELECT 1
309 FROM po_headers_all poh
310 WHERE type_lookup_code IN('STANDARD', 'BLANKET', 'SCHEDULED')
311 AND poh.segment1 = rti.document_num
312 AND poh.org_id = Nvl(rti.org_id, poh.org_id)
313 AND poh.po_header_id = l_po_header_id )
314 );
315
316 IF l_rti_count > 0 THEN
317
318 l_progress := '060';
319
320 l_exception_type := 'PO_SUPCHG_WITH_RCV_TRX_SUM';
321 l_message_name := 'PO_SUPCHG_WITH_RCV_TRX';
322 x_validation_result_type := 'WARNING';
323 SELECT po_multi_mod_val_results_s.nextval
324 INTO l_multi_mod_val_result_id
325 FROM dual;
326
327 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
328 p_multi_mod_request_id => p_request_id,
329 p_result_type => 'WARNING',
330 p_validation_type => 'RECEIPTS',
331 p_exception_type => l_exception_type,
332 p_document_id => l_po_header_id,
333 p_related_document_id => NULL,
334 p_related_document_number => NULL,
335 p_related_document_date => NULL,
336 p_related_document_amount => NULL,
337 p_message_application => 'PO',
338 p_message_name => l_message_name);
339 END IF;
340
341
342 /* If there are any receipts where the receipt date is ON OR AFTER the
343 'Effective Date of Novation' , then generate an exception */
344
345 l_exception_type := NULL;
346 l_message_name := NULL;
347 OPEN c_rt_novation(l_po_header_id, p_novation_date);
348 LOOP
349
350 l_progress := '070';
351 FETCH c_rt_novation INTO l_shipment_header_id, l_receipt_number, l_receipt_date;
352
353 IF c_rt_novation%NOTFOUND THEN
354 EXIT;
355 END IF;
356
357 l_progress := '080';
358 SELECT Sum(Nvl(rsl.quantity_received, 0) * NVL(poll.price_override, pol.unit_price) )
359 INTO l_receipt_amount
360 FROM rcv_shipment_lines rsl,
361 po_line_locations_all poll,
362 po_lines_all pol
363 WHERE rsl.po_line_location_id = poll.line_location_id
364 AND rsl.po_line_id = pol.po_line_id
365 AND pol.po_line_id = poll.po_line_id
366 AND rsl.quantity_shipped IS NOT NULL
367 AND rsl.shipment_header_id = l_shipment_header_id ;
368
369 IF l_receipt_amount = 0 THEN
370 SELECT Sum(Nvl(rsl.amount_received, 0))
371 INTO l_receipt_amount
372 FROM rcv_shipment_lines rsl
373 WHERE rsl.quantity_shipped IS NULL
374 AND rsl.shipment_header_id = l_shipment_header_id ;
375
376 END IF;
377
378 l_progress := '090';
379 l_exception_type := 'PO_SUPCHG_RDAT_GE_NOVDAT_SUM';
380 l_message_name := 'PO_SUPCHG_RDAT_GE_NOVDAT';
381 x_validation_result_type := 'WARNING';
382 SELECT po_multi_mod_val_results_s.nextval
383 INTO l_multi_mod_val_result_id
384 FROM dual;
385
386 x_validation_results.add_result (p_multi_mod_val_result_id => l_multi_mod_val_result_id,
387 p_multi_mod_request_id => p_request_id,
388 p_result_type => 'WARNING',
389 p_validation_type => 'RECEIPTS',
390 p_exception_type => l_exception_type,
391 p_document_id => l_po_header_id,
392 p_related_document_id => l_shipment_header_id,
393 p_related_document_number => l_receipt_number,
394 p_related_document_date => l_receipt_date,
395 p_related_document_amount => l_receipt_amount,
396 p_message_application => 'PO',
397 p_message_name => l_message_name);
398
399
400 END LOOP; -- FETCH c_rt_novation INTO ..
401
402 CLOSE c_rt_novation;
403
404
405
406 END LOOP; -- FOR i IN 1 .. n LOOP
407
408 l_progress := '100';
409 x_return_status := fnd_api.g_ret_sts_success;
410
411 EXCEPTION
412 WHEN OTHERS
413 THEN
414
415 x_validation_result_type := 'WARNING';
416 x_return_status := fnd_api.g_ret_sts_error;
417 x_error_msg := 'All records failed by rcv_validate_po.validate_novation_receipts in process '||l_progress;
418
419 END validate_novation_receipts;
420
421 End RCV_VALIDATE_PO;
422