1 PACKAGE BODY fv_cross_doc_ref as
2 /* $Header: FVDOCCRB.pls 120.12 2003/12/17 21:20:05 ksriniva ship $ */
3 g_module_name VARCHAR2(100) := 'fv.plsql.fv_cross_doc_ref.';
4
5 vp_vendor_id po_vendors.vendor_id%TYPE;
6 vp_vendor_site_id po_vendor_sites.vendor_site_id%TYPE;
7 vp_po_header_id po_headers.po_header_id%TYPE;
8 vp_po_date po_headers.creation_date%TYPE;
9 vp_requisition_header_id po_requisition_headers.requisition_header_id%TYPE;
10 vp_requisition_line_id po_requisition_lines.requisition_line_id%TYPE;
11 vp_req_date po_requisition_headers.creation_date%TYPE;
12 vp_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
13 vp_rec_date rcv_shipment_headers.creation_date%TYPE;
14 vp_buyer po_headers.agent_id%TYPE;
15 vp_invoice_id ap_invoices.invoice_id%TYPE;
16 vp_invoice_date ap_invoices.invoice_date%TYPE;
17 vp_invoice_amount ap_invoices.invoice_amount%TYPE;
18 vp_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
19 vp_check_id ap_checks.check_id%TYPE;
20 vp_check_date ap_checks.creation_date%TYPE;
21 vp_amount ap_checks.amount%TYPE;
22 vp_treasury_pay_number ap_checks.treasury_pay_number%TYPE;
23 vp_treasury_pay_date ap_checks.treasury_pay_date%TYPE;
24 vp_valid_req_supplier NUMBER;
25 vp_supplier_name po_vendors.vendor_name%TYPE;
26 vp_supplier_site po_vendor_sites.vendor_site_code%TYPE;
27 vp_session_id NUMBER;
28
29 PROCEDURE po_master;
30 PROCEDURE req_master;
31 PROCEDURE rec_master;
32 PROCEDURE inv_master;
33 PROCEDURE pay_master;
34
35
36 -----------------------------------------------------------------------
37 -- MAIN
38 ----------------------------------------------------------------------
39
40 PROCEDURE main
41 (
42 p_vendor_id IN po_vendors.vendor_id%TYPE ,
43 p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE ,
44 p_po_header_id IN po_headers.po_header_id%TYPE,
45 p_po_date IN po_headers.creation_date%TYPE,
46 p_requisition_header_id IN po_requisition_headers.requisition_header_id%TYPE,
47 p_requisition_line_id IN po_requisition_lines.requisition_line_id%TYPE,
48 p_req_date IN po_requisition_headers.creation_date%TYPE,
49 p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
50 p_receipt_date IN rcv_shipment_headers.creation_date%TYPE,
51 p_buyer IN po_headers.agent_id%TYPE,
52 p_invoice_id IN ap_invoices.invoice_id%TYPE,
53 p_invoice_date IN ap_invoices.invoice_date%TYPE ,
54 p_invoice_amount IN ap_invoices.invoice_amount%TYPE ,
55 p_invoice_type IN ap_invoices.invoice_type_lookup_code%TYPE ,
56 p_check_id IN ap_checks.check_id%TYPE,
57 p_check_date IN ap_checks.creation_date%TYPE,
58 p_amount IN ap_checks.amount%TYPE,
59 p_treasury_pay_number IN ap_checks.treasury_pay_number%TYPE,
60 p_treasury_pay_date IN ap_checks.treasury_pay_date%TYPE,
61 p_valid_req_supplier IN NUMBER,
62 p_supplier_name IN po_vendors.vendor_name%TYPE,
63 p_supplier_site IN po_vendor_sites.vendor_site_code%TYPE,
64 p_result IN VARCHAR2,
65 p_err_code OUT NOCOPY NUMBER,
66 p_session_id IN NUMBER
67
68
69 ) IS
70 l_module_name VARCHAR2(200) := g_module_name || 'main';
71 l_errbuf VARCHAR2(1024);
72
73 BEGIN
74
75 vp_vendor_id := p_vendor_id;
76 vp_vendor_site_id := p_vendor_site_id;
77 vp_po_header_id := p_po_header_id;
78 vp_po_date := p_po_date;
79 vp_requisition_header_id := p_requisition_header_id;
80 vp_req_date := p_req_date;
81 vp_shipment_header_id := p_shipment_header_id;
82 vp_rec_date := p_receipt_date;
83 vp_buyer := p_buyer;
84 vp_invoice_id := p_invoice_id;
85 vp_invoice_date := p_invoice_date;
86 vp_invoice_amount := p_invoice_amount;
87 vp_invoice_type := p_invoice_type;
88 vp_check_id := p_check_id;
89 vp_check_date := p_check_date;
90 vp_amount := p_amount;
91 vp_treasury_pay_number := p_treasury_pay_number;
92 vp_treasury_pay_date := p_treasury_pay_date;
93 vp_valid_req_supplier := p_valid_req_supplier;
94 vp_supplier_name := p_supplier_name;
95 vp_supplier_site := p_supplier_site;
96 vp_session_id := p_session_id;
97 IF p_result = 'PO' THEN
98 po_master;
99 ELSIF p_result = 'REQ' THEN
100 req_master;
101 ELSIF p_result = 'REC' THEN
102 rec_master;
103 ELSIF p_result = 'INV' THEN
104 inv_master;
105 ELSIF p_result = 'PAY' THEN
106 pay_master;
107 END IF;
108 EXCEPTION
109 WHEN OTHERS THEN
110 p_err_code := -1;
111 l_errbuf := SQLERRM;
112 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
113
114 END MAIN;
115
116
117 PROCEDURE po_master IS
118 l_module_name VARCHAR2(200) := g_module_name || 'po_master';
119 l_errbuf VARCHAR2(1024);
120
121 CURSOR po_cur IS
122 SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
123 FROM po_headers ph,po_lines pl,po_line_locations pll
124 WHERE ph.po_header_id = pl.po_header_id
125 AND pl.po_line_id = pll.po_line_id
126 AND vendor_id = vp_vendor_id
127 AND vendor_site_id = vp_vendor_site_id
128 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
129 AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
130 AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
131 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
132 UNION
133 SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
134 FROM po_headers ph,po_lines pl,po_line_locations pll
135 WHERE ph.po_header_id = pl.po_header_id
136 AND pl.po_line_id = pll.po_line_id
137 AND vendor_id = vp_vendor_id
138 AND vendor_site_id = vp_vendor_site_id
139 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
140 AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
141 AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
142 AND EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id
143 AND po_header_id = ph.po_header_id AND po_line_id = pl.po_line_id);
144 po_rec po_cur%ROWTYPE;
145
146 CURSOR req_cur IS
147 SELECT ph.po_header_id,pl.po_line_id, pll.line_location_id
148 FROM po_headers ph,po_lines pl,po_line_locations pll
149 WHERE ph.po_header_id = pl.po_header_id
150 AND pl.po_line_id = pll.po_line_id
151 AND EXISTS(SELECT prh.requisition_header_id,prl.requisition_line_id
152 FROM po_requisition_headers prh,po_requisition_lines prl
153 WHERE prh.requisition_header_id = prl.requisition_header_id
154 AND authorization_status = 'APPROVED'
155 AND prl.line_location_id = pll.line_location_id
156 AND prl.suggested_vendor_location = vp_supplier_site
157 AND prl.suggested_vendor_name = vp_supplier_name
158 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
159 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))
160 UNION
161 SELECT rh.requisition_header_id,rh.requisition_line_id
162 FROM (SELECT DISTINCT prh.requisition_header_id,
163 prl.requisition_line_id,prl.line_location_id
164 FROM po_requisition_headers prh,po_requisition_lines prl
165 WHERE prl.requisition_header_id = prh.requisition_header_id
166 AND authorization_status = 'APPROVED'
167 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
168 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
169 po_line_locations plx,po_headers ph
170 WHERE plx.line_location_id = rh.line_location_id
171 AND plx.po_header_id = ph.po_header_id
172 AND ph.vendor_id = vp_vendor_id
173 AND rh.line_location_id = pll.line_location_id
174 AND ph.vendor_site_id = vp_vendor_site_id
175 AND NOT EXISTS (SELECT 1 FROM po_vendors pv,po_requisition_lines prl,po_vendor_sites pvs
176 WHERE prl.suggested_vendor_name = pv.vendor_name
177 AND prl.suggested_vendor_location = pvs.vendor_site_code
178 AND prl.requisition_line_id = rh.requisition_line_id ));
179
180
181 req_rec req_cur%ROWTYPE;
182
183 CURSOR rec_cur IS
184 SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
185 FROM po_headers ph,po_line_locations pll
186 WHERE ph.po_header_id = pll.po_header_id
187 AND vendor_id = vp_vendor_id
188 AND vendor_site_id = vp_vendor_site_id
189 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
190 AND EXISTS (SELECT 1 FROM rcv_transactions rt
191 WHERE rt.po_header_id = ph.po_header_id
192 AND rt.po_line_id = pll.po_line_id
193 AND rt.po_line_location_id = pll.line_location_id
194 AND EXISTS (SELECT 1 from rcv_shipment_headers rsh
195 WHERE rsh.shipment_header_id = rt.shipment_header_id
196 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
197 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))
198 UNION
199 SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id
200 FROM po_headers ph,po_line_locations pll
201 WHERE ph.po_header_id = pll.po_header_id
202 AND vendor_id = vp_vendor_id
203 AND vendor_site_id = vp_vendor_site_id
204 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
205 WHERE fst.po_header_id = ph.po_header_id
206 AND fst.po_line_id = pll.po_line_id
207 AND fst.po_line_location_id = pll.line_location_id
208 AND fst.session_id = vp_session_id)
209 AND EXISTS (SELECT 1 FROM rcv_transactions rt
210 WHERE rt.po_header_id = ph.po_header_id
211 AND rt.po_line_id = pll.po_line_id
212 AND rt.po_line_location_id = pll.line_location_id
213 AND EXISTS (SELECT 1 from rcv_shipment_headers rsh
214 WHERE rsh.shipment_header_id = rt.shipment_header_id
215 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
216 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))));
217
218 rec_rec rec_cur%ROWTYPE;
219 CURSOR inv_cur IS
220 SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id FROM po_headers ph,po_line_locations pll
221 WHERE ph.po_header_id = pll.po_header_id
222 AND vendor_id = vp_vendor_id
223 AND vendor_site_id = vp_vendor_site_id
224 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
225 AND (EXISTS (SELECT 1 FROM po_distributions pd
226 WHERE pd.po_header_id = ph.po_header_id
227 AND pd.line_location_id = pll.line_location_id
228 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
229 WHERE ia.po_distribution_id= pd.po_distribution_id
230 AND EXISTS (SELECT 1 FROM ap_invoices ap
231 WHERE invoice_id = ia.invoice_id
232 AND invoice_id = NVL(vp_invoice_id,invoice_id)
233 AND TRUNC(ap.invoice_date) = NVL(vp_invoice_date,TRUNC(ap.invoice_date))
234 AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
235 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount))))
236 OR EXISTS (SELECT 1 FROM rcv_transactions rt
237 WHERE rt.po_header_id = ph.po_header_id
238 AND rt.po_line_location_id = pll.line_location_id
239 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
240 WHERE ia.rcv_transaction_id= rt.transaction_id
241 AND EXISTS (SELECT 1 FROM ap_invoices ap
242 WHERE invoice_id = ia.invoice_id
243 AND invoice_id = NVL(vp_invoice_id,invoice_id)
244 AND TRUNC(ap.invoice_date) = NVL(vp_invoice_date,TRUNC(ap.invoice_date))
245 AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
246 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))))
247 UNION
248 SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id FROM po_headers ph,po_line_locations pll
249 WHERE vendor_id = vp_vendor_id
250 AND ph.po_header_id = pll.po_header_id
251 AND vendor_site_id = vp_vendor_site_id
252 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
253 WHERE fst.po_header_id = ph.po_header_id
254 AND fst.po_line_id = pll.po_line_id
255 AND fst.po_line_location_id = pll.line_location_id
256 AND fst.session_id = vp_session_id)
257 AND (EXISTS (SELECT 1 FROM po_distributions pd
258 WHERE pd.po_header_id = ph.po_header_id
259 AND pd.line_location_id = pll.line_location_id
260 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
261 WHERE ia.po_distribution_id= pd.po_distribution_id
262 AND EXISTS (SELECT 1 FROM ap_invoices ap
263 WHERE invoice_id = ia.invoice_id
264 AND invoice_id = NVL(vp_invoice_id,invoice_id)
265 AND TRUNC(ap.invoice_date) =NVL(vp_invoice_date,TRUNC(ap.invoice_date))
266 AND invoice_type_lookup_code =NVL(vp_invoice_type,invoice_type_lookup_code)
267 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount))))
268 OR EXISTS (SELECT 1 FROM rcv_transactions rt
269 WHERE rt.po_header_id = ph.po_header_id
270 AND rt.po_line_location_id = pll.line_location_id
271 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
272 WHERE ia.rcv_transaction_id= rt.transaction_id
273 AND EXISTS (SELECT 1 FROM ap_invoices ap
274 WHERE invoice_id = ia.invoice_id
275 AND invoice_id = NVL(vp_invoice_id,invoice_id)
276 AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
277 AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
278 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))));
279
280 inv_rec inv_cur%ROWTYPE;
281
282 CURSOR pay_cur IS
283 SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
284 FROM po_headers ph,po_line_locations pll
285 WHERE ph.po_header_id = pll.po_header_id
286 AND vendor_id = vp_vendor_id
287 AND vendor_site_id = vp_vendor_site_id
288 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp
289 WHERE session_id = vp_session_id)
290 AND EXISTS (SELECT 1 from po_distributions pd
291 WHERE pd.po_header_id = ph.po_header_id
292 AND pd.line_location_id = pll.line_location_id
293 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
294 WHERE aid.po_distribution_id = pd.po_distribution_id
295 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
296 WHERE aip.invoice_id = aid.invoice_id
297 AND EXISTS (SELECT 1 FROM ap_checks ac
298 WHERE ac.check_id = aip.check_id
299 AND ac.check_id = NVL(vp_check_id,ac.check_id)
300 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
301 AND ac.amount = nvl(vp_amount,ac.amount)
302 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
303 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
304 OR vp_treasury_pay_date IS NULL)))))
305 OR EXISTS (SELECT 1 from rcv_transactions rt
306 WHERE rt.po_header_id = ph.po_header_id
307 AND rt.po_line_location_id = pll.line_location_id
308 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
309 WHERE aid.rcv_transaction_id = rt.transaction_id
310 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
311 WHERE aip.invoice_id = aid.invoice_id
312 AND EXISTS (SELECT 1 FROM ap_checks ac
313 WHERE ac.check_id = aip.check_id
314 AND ac.check_id = NVL(vp_check_id,ac.check_id)
315 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
316 AND ac.amount = nvl(vp_amount,ac.amount)
317 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
318 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
319 OR vp_treasury_pay_date IS NULL))))))
320 UNION
321 SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
322 FROM po_headers ph,po_line_locations pll
323 WHERE ph.po_header_id = pll.po_header_id
324 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
325 WHERE fst.po_header_id = ph.po_header_id
326 AND fst.po_line_id = pll.po_line_id
327 AND fst.po_line_location_id = pll.line_location_id
328 AND fst.session_id = vp_session_id)
329 AND vendor_id = vp_vendor_id
330 AND vendor_site_id = vp_vendor_site_id
331 AND EXISTS (SELECT 1 from po_distributions pd
332 WHERE pd.po_header_id = ph.po_header_id
333 AND pd.line_location_id = pll.line_location_id
334 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
335 WHERE aid.po_distribution_id = pd.po_distribution_id
336 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
337 WHERE aip.invoice_id = aid.invoice_id
338 AND EXISTS (SELECT 1 FROM ap_checks ac
339 WHERE ac.check_id = aip.check_id
340 AND ac.check_id = NVL(vp_check_id,ac.check_id)
341 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
342 AND ac.amount = nvl(vp_amount,ac.amount)
343 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
344 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
345 OR vp_treasury_pay_date IS NULL)))))
346 OR EXISTS (SELECT 1 from rcv_transactions rt
347 WHERE rt.po_header_id = ph.po_header_id
348 AND rt.po_line_location_id = pll.line_location_id
349 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
350 WHERE aid.rcv_transaction_id = rt.transaction_id
351 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
352 WHERE aip.invoice_id = aid.invoice_id
353 AND EXISTS (SELECT 1 FROM ap_checks ac
354 WHERE ac.check_id = aip.check_id
355 AND ac.check_id = NVL(vp_check_id,ac.check_id)
356 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
357 AND ac.amount = nvl(vp_amount,ac.amount)
358 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
359 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
360 OR vp_treasury_pay_date IS NULL))))));
361
362 pay_rec pay_cur%ROWTYPE;
363 BEGIN
364 DELETE FROM fv_doc_cr_temp
365 WHERE session_id = vp_session_id;
366 LOOP
367 IF (vp_requisition_header_id IS NOT NULL OR vp_req_date IS NOT NULL) THEN
368 OPEN req_cur;
369 DELETE FROM fv_doc_cr_temp
370 WHERE session_id = vp_session_id;
371 LOOP
372 FETCH req_cur INTO req_rec;
373 EXIT WHEN req_cur%NOTFOUND;
374 INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
375 VALUES (req_rec.po_header_id,req_rec.po_line_id,req_rec.line_location_id,vp_session_id);
376 END LOOP;
377 IF NOT (req_cur%rowcount <> 0 ) THEN
378 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
379 CLOSE req_cur;
380 EXIT;
381 END IF;
382 CLOSE req_cur;
383 END IF;
384 IF (vp_po_header_id IS NOT NULL OR vp_po_date IS NOT NULL OR vp_buyer IS NOT NULL) THEN
385 OPEN po_cur;
386 DELETE FROM fv_doc_cr_temp
387 WHERE session_id = vp_session_id;
388 LOOP
389 FETCH po_cur INTO po_rec;
390 EXIT WHEN po_cur%NOTFOUND;
391 INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
392 VALUES(po_rec.po_header_id,po_rec.po_line_id,po_rec.line_location_id,vp_session_id);
393 END LOOP;
394 IF (po_cur%rowcount = 0 ) THEN
395 DELETE FROM fv_doc_cr_temp
396 WHERE session_id = vp_session_id;
397 CLOSE po_cur;
398 EXIT;
399 END IF;
400 CLOSE po_cur;
401 END IF ;
402
403 IF (vp_shipment_header_id IS NOT NULL OR vp_rec_date is NOT NULL) THEN
404 OPEN rec_cur;
405 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
406 LOOP
407 FETCH rec_cur INTO rec_rec;
408 EXIT WHEN rec_cur%NOTFOUND;
409 INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
410 VALUES (rec_rec.po_header_id,rec_rec.po_line_id,rec_rec.line_location_id,vp_session_id);
411 END LOOP;
412 IF NOT (rec_cur%rowcount <> 0 ) THEN
413 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
414 CLOSE rec_cur;
415 EXIT;
416 END IF;
417 CLOSE rec_cur;
418 END IF;
419 IF (vp_invoice_id IS NOT NULL OR vp_invoice_date IS NOT NULL OR vp_invoice_type IS NOT NULL
420 OR vp_invoice_amount IS NOT NULL) THEN
421 OPEN inv_cur;
422 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
423 LOOP
424 FETCH inv_cur INTO inv_rec;
425 EXIT WHEN inv_cur%NOTFOUND;
426 INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
427 VALUES (inv_rec.po_header_id,inv_rec.po_line_id,inv_rec.line_location_id,vp_session_id);
428 END LOOP;
429 IF (inv_cur%rowcount = 0 ) THEN
430 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
431 CLOSE inv_cur;
432 EXIT;
433 END IF;
434 CLOSE inv_cur;
435 END IF;
436 IF (vp_check_id IS NOT NULL OR vp_check_date IS NOT NULL
437 OR vp_amount IS NOT NULL OR vp_treasury_pay_number IS NOT NULL
438 OR vp_treasury_pay_date IS NOT NULL) THEN
439 OPEN pay_cur;
440 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
441 LOOP
442 FETCH pay_cur INTO pay_rec;
443 EXIT WHEN pay_cur%NOTFOUND;
444 INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
445 VALUES (pay_rec.po_header_id,pay_rec.po_line_id,pay_rec.line_location_id,vp_session_id);
446 END LOOP;
447 IF NOT (pay_cur%rowcount <> 0 ) THEN
448 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
449 CLOSE pay_cur;
450 EXIT;
451 END IF;
452 CLOSE pay_cur;
453 END IF;
454 EXIT;
455 END LOOP;
456 EXCEPTION
457 WHEN OTHERS THEN
458 l_errbuf := SQLERRM;
459 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
460 RAISE;
461
462 END po_master;
463
464 PROCEDURE req_master IS
465 l_module_name VARCHAR2(200) := g_module_name || 'req_master';
466 l_errbuf VARCHAR2(1024);
467
468 CURSOR po_cur IS
469 SELECT prh.requisition_header_id ,prl.requisition_line_id
470 FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
471 WHERE prh.requisition_header_id =prl.requisition_header_id
472 AND pll.line_location_id = prl.line_location_id
473 AND ph.po_header_id = pll.po_header_id
474 AND ph.vendor_id = vp_vendor_id
475 AND ph.vendor_site_id = vp_vendor_site_id
476 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
477 AND TRUNC(ph.creation_date) = NVL(vp_po_date,TRUNC(ph.creation_date))
478 AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
479 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
480 UNION
481 SELECT prh.requisition_header_id ,prl.requisition_line_id
482 FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
483 WHERE prh.requisition_header_id = prl.requisition_header_id
484 AND pll.line_location_id = prl.line_location_id
485 AND ph.po_header_id = pll.po_header_id
486 AND ph.vendor_id = vp_vendor_id
487 AND ph.vendor_site_id = vp_vendor_site_id
488 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
489 AND TRUNC(ph.creation_date) = NVL(vp_po_date,TRUNC(ph.creation_date))
490 AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
491 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
492 WHERE fst.requisition_header_id = prh.requisition_header_id
493 AND requisition_line_id = prl.requisition_line_id
494 AND fst.session_id = vp_session_id);
495
496 po_rec po_cur%ROWTYPE;
497
498
499 CURSOR req_cur IS
500 SELECT prh.requisition_header_id,prl.requisition_line_id
501 FROM po_requisition_headers prh,po_requisition_lines prl
502 WHERE prh.requisition_header_id = prl.requisition_header_id
503 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
504 AND authorization_status = 'APPROVED'
505 AND prl.suggested_vendor_location = vp_supplier_site
506 AND prl.suggested_vendor_name = vp_supplier_name
507 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))
508 UNION
509 SELECT rh.requisition_header_id,rh.requisition_line_id
510 FROM (SELECT DISTINCT prh.requisition_header_id,
511 prl.requisition_line_id,prl.line_location_id
512 FROM po_requisition_headers prh,po_requisition_lines prl
513 WHERE prl.requisition_header_id = prh.requisition_header_id
514 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
515 AND authorization_status = 'APPROVED'
516 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
517 po_line_locations plx,po_headers ph
518 WHERE plx.line_location_id = rh.line_location_id
519 AND plx.po_header_id = ph.po_header_id
520 AND ph.vendor_id = vp_vendor_id
521 AND ph.vendor_site_id = vp_vendor_site_id
522 AND NOT EXISTS (SELECT 1 FROM po_vendors pv,po_requisition_lines prl,po_vendor_sites pvs
523 WHERE prl.suggested_vendor_name = pv.vendor_name
524 AND prl.suggested_vendor_location = pvs.vendor_site_code
525 AND prl.requisition_line_id = rh.requisition_line_id );
526
527
528 req_rec req_cur%ROWTYPE;
529
530 CURSOR rec_cur IS
531 SELECT requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
532 WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
533 AND (EXISTS (SELECT 1 FROM po_req_distributions prd
534 WHERE prd.requisition_line_id = prl.requisition_line_id
535 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
536 WHERE rt.po_header_id = ph.po_header_id
537 AND rt.req_distribution_id = prd.distribution_id
538 AND ph.vendor_site_id = vp_vendor_site_id
539 AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
540 WHERE rsh.vendor_id = vp_vendor_id
541 AND rsh.shipment_header_id = rt.shipment_header_id
542 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
543 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
544 OR EXISTS (SELECT 1 FROM po_distributions pd
545 WHERE pd.line_location_id = prl.line_location_id
546 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
547 WHERE rt.po_header_id = ph.po_header_id
548 AND rt.po_line_location_id = pd.line_location_id
549 AND ph.vendor_site_id = vp_vendor_site_id
550 AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
551 WHERE rsh.vendor_id = vp_vendor_id
552 AND rsh.shipment_header_id = rt.shipment_header_id
553 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
554 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))))
555
556 UNION
557 SELECT requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
558 WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
559 WHERE requisition_header_id = prl.requisition_header_id
560 AND requisition_line_id = prl.requisition_line_id
561 AND session_id = vp_session_id)
562 AND (EXISTS (SELECT 1 FROM po_req_distributions prd
563 WHERE prd.requisition_line_id = prl.requisition_line_id
564 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
565 WHERE rt.po_header_id = ph.po_header_id
566 AND rt.req_distribution_id = prd.distribution_id
567 AND ph.vendor_site_id = vp_vendor_site_id
568 AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
569 WHERE rsh.vendor_id = vp_vendor_id
570 AND rsh.shipment_header_id = rt.shipment_header_id
571 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
572 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
573 OR EXISTS (SELECT 1 FROM po_distributions pd
574 WHERE pd.line_location_id = prl.line_location_id
575 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
576 WHERE rt.po_header_id = ph.po_header_id
577 AND rt.po_line_location_id = pd.line_location_id
578 AND ph.vendor_site_id = vp_vendor_site_id
579 AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
580 WHERE rsh.vendor_id = vp_vendor_id
581 AND rsh.shipment_header_id = rt.shipment_header_id
582 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
583 AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))));
584
585
586 rec_rec rec_cur%ROWTYPE;
587
588
589 CURSOR inv_cur IS
590 SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
591 WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
592 AND EXISTS (SELECT 1 FROM po_req_distributions prd
593 WHERE prd.requisition_line_id = prl.requisition_line_id
594 AND EXISTS (SELECT 1 FROM po_distributions pd
595 WHERE pd.req_distribution_id = prd.distribution_id
596 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
597 WHERE aid.po_distribution_id= pd.po_distribution_id
598 AND EXISTS (SELECT 1 FROM ap_invoices ai
599 WHERE ai.invoice_id = aid.invoice_id
600 AND ai.vendor_id = vp_vendor_id
601 AND ai.vendor_site_id = vp_vendor_site_id
602 AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
603 AND TRUNC(ai.invoice_date) = nvl(vp_invoice_date,TRUNC(ai.invoice_date))
604 AND ai.invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
605 AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount)))
606 OR EXISTS (SELECT 1 FROM po_line_locations pll
607 WHERE pll.line_location_id = pd.line_location_id
608 AND EXISTS ( SELECT 1 FROM rcv_transactions rt
609 WHERE rt.po_line_location_id = pll.line_location_id
610 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
611 WHERE aid.rcv_transaction_id = rt.transaction_id
612 AND EXISTS (SELECT 1 FROM ap_invoices ai
613 WHERE ai.invoice_id = aid.invoice_id
614 AND ai.vendor_id = vp_vendor_id
615 AND ai.vendor_site_id = vp_vendor_site_id
616 AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
617 AND TRUNC(ai.invoice_date) = nvl(vp_invoice_date,TRUNC(ai.invoice_date))
618 AND ai.invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
619 AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount))))))))
620 UNION
621 SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
622 WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
623 WHERE requisition_header_id = prl.requisition_header_id
624 AND requisition_line_id = prl.requisition_line_id
625 AND session_id = vp_session_id)
626 AND EXISTS (SELECT 1 FROM po_req_distributions prd
627 WHERE prd.requisition_line_id = prl.requisition_line_id
628 AND EXISTS (SELECT 1 FROM po_distributions pd
629 WHERE pd.req_distribution_id = prd.distribution_id
630 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
631 WHERE aid.po_distribution_id= pd.po_distribution_id
632 AND EXISTS (SELECT 1 FROM ap_invoices ai
633 WHERE ai.invoice_id = aid.invoice_id
634 AND ai.vendor_id = vp_vendor_id
635 AND ai.vendor_site_id = vp_vendor_site_id
636 AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
637 AND trunc(ai.invoice_date) = nvl(vp_invoice_date,trunc(ai.invoice_date))
638 AND ai.invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
639 AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount)))
640 OR EXISTS (SELECT 1 FROM po_line_locations pll
641 WHERE pll.line_location_id = pd.line_location_id
642 AND EXISTS (SELECT 1 FROM rcv_transactions rt
643 WHERE rt.po_line_location_id = pll.line_location_id
644 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
645 WHERE aid.rcv_transaction_id = rt.transaction_id
646 AND EXISTS (SELECT 1 FROM ap_invoices ai
647 WHERE ai.invoice_id = aid.invoice_id
648 AND ai.vendor_id = vp_vendor_id
649 AND ai.vendor_site_id = vp_vendor_site_id
650 AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
651 AND TRUNC(ai.invoice_date) = NVL(vp_invoice_date,TRUNC(ai.invoice_date))
652 AND ai.invoice_type_lookup_code =NVL(vp_invoice_type,invoice_type_lookup_code)
653 AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount))))))));
654
655 inv_rec inv_cur%ROWTYPE;
656
657 CURSOR pay_cur IS
658 SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
659 WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
660 AND EXISTS (SELECT 1 FROM po_req_distributions prd WHERE prd.requisition_line_id = prl.requisition_line_id
661 AND EXISTS (SELECT 1 FROM po_distributions pd
662 WHERE pd.req_distribution_id = prd.distribution_id
663 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
664 WHERE aid.po_distribution_id = pd.po_distribution_id
665 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
666 WHERE aip.invoice_id = aid.invoice_id
667 AND EXISTS (SELECT 1 FROM ap_checks ac
668 WHERE ac.check_id = aip.check_id
669 AND ac.vendor_id = vp_vendor_id
670 AND ac.vendor_site_id = vp_vendor_site_id
671 AND ac.check_id = NVL(vp_check_id,ac.check_id)
672 AND ac.check_date = NVL(vp_check_date,ac.check_date)
673 AND ac.amount = nvl(vp_amount,ac.amount)
674 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
675 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
676 OR vp_treasury_pay_date IS NULL))))
677 OR EXISTS (SELECT 1 FROM po_line_locations pll
678 WHERE pll.line_location_id = pd.line_location_id
679 AND EXISTS (SELECT 1 FROM rcv_transactions rt
680 WHERE rt.po_line_location_id = pll.line_location_id
681 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
682 WHERE aid.rcv_transaction_id = rt.transaction_id
683 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
684 WHERE aip.invoice_id = aid.invoice_id
685 AND EXISTS (SELECT 1 FROM ap_checks ac
686 WHERE ac.check_id = aip.check_id
687 AND ac.vendor_id = vp_vendor_id
688 AND ac.vendor_site_id = vp_vendor_site_id
689 AND ac.check_id = NVL(vp_check_id,ac.check_id)
690 AND ac.check_date = NVL(vp_check_date,ac.check_date)
691 AND ac.amount = nvl(vp_amount,ac.amount)
692 AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
693 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
694 OR vp_treasury_pay_date IS NULL)))))))))
695 UNION
696 SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
697 WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
698 WHERE requisition_header_id = prl.requisition_header_id
699 AND requisition_line_id = prl.requisition_line_id
700 AND session_id = vp_session_id)
701 AND EXISTS (SELECT 1 FROM po_req_distributions prd
702 WHERE prd.requisition_line_id = prl.requisition_line_id
703 AND EXISTS (SELECT 1 FROM po_distributions pd
704 WHERE pd.req_distribution_id = prd.distribution_id
705 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
706 WHERE aid.po_distribution_id = pd.po_distribution_id
707 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
708 WHERE aip.invoice_id = aid.invoice_id
709 AND EXISTS (SELECT 1 FROM ap_checks ac
710 WHERE ac.check_id = aip.check_id
711 AND ac.check_id = NVL(vp_check_id,ac.check_id)
712 AND ac.check_date = NVL(vp_check_date,ac.check_date)
713 AND ac.amount = nvl(vp_amount,ac.amount)
714 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
715 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
716 OR vp_treasury_pay_date IS NULL))))
717 OR EXISTS (SELECT 1 FROM po_line_locations pll
718 WHERE pll.line_location_id = pd.line_location_id
719 AND EXISTS (SELECT 1 FROM rcv_transactions rt
720 WHERE rt.po_line_location_id = pll.line_location_id
721 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
722 WHERE aid.rcv_transaction_id = rt.transaction_id
723 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
724 WHERE aip.invoice_id = aid.invoice_id
725 AND EXISTS (SELECT 1 FROM ap_checks ac
726 WHERE ac.check_id = aip.check_id
727 AND ac.vendor_id = vp_vendor_id
728 AND ac.vendor_site_id = vp_vendor_site_id
729 AND ac.check_id = NVL(vp_check_id,ac.check_id)
730 AND ac.check_date = NVL(vp_check_date,ac.check_date)
731 AND ac.amount = nvl(vp_amount,ac.amount)
732 AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
733 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
734 OR vp_treasury_pay_date IS NULL)))))))));
735 pay_rec pay_cur%ROWTYPE;
736 BEGIN
737 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
738 LOOP
739
740 IF (vp_requisition_header_id IS NOT NULL OR vp_req_date is NOT NULL) THEN
741 OPEN req_cur;
742 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
743 LOOP
744 FETCH req_cur INTO req_rec;
745 EXIT WHEN req_cur%NOTFOUND;
746 INSERT INTO fv_doc_cr_temp (requisition_header_id,requisition_line_id,session_id )
747 VALUES (req_rec.requisition_header_id,req_rec.requisition_line_id,vp_session_id );
748 END LOOP;
749 IF NOT (req_cur%rowcount <> 0 ) THEN
750 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
751 CLOSE req_cur;
752 EXIT;
753 END IF;
754 CLOSE req_cur;
755 END IF;
756
757 IF (vp_po_header_id IS NOT NULL OR vp_po_date IS NOT NULL OR vp_buyer IS NOT NULL) THEN
758 OPEN po_cur;
759 LOOP
760 FETCH po_cur INTO po_rec;
761 EXIT WHEN po_cur%NOTFOUND;
762 INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
763 VALUES (po_rec.requisition_header_id,po_rec.requisition_line_id,vp_session_id );
764 END LOOP;
765 IF NOT (po_cur%rowcount <> 0 ) THEN
766 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
767 CLOSE po_cur;
768 EXIT;
769 END IF;
770 CLOSE po_cur;
771 END IF ;
772
773 IF (vp_shipment_header_id IS NOT NULL OR vp_rec_date is NOT NULL) THEN
774 OPEN rec_cur;
775 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
776 LOOP
777 FETCH rec_cur INTO rec_rec;
778 EXIT WHEN rec_cur%NOTFOUND;
779 INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
780 VALUES (rec_rec.requisition_header_id,rec_rec.requisition_line_id,vp_session_id );
781 END LOOP;
782 IF NOT (rec_cur%rowcount <> 0 ) THEN
783 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
784 CLOSE rec_cur;
785 EXIT;
786 END IF;
787 CLOSE rec_cur;
788 END IF;
789
790 IF (vp_invoice_id IS NOT NULL OR vp_invoice_date IS NOT NULL OR vp_invoice_type IS NOT NULL
791 OR vp_invoice_amount IS NOT NULL) THEN
792 OPEN inv_cur;
793 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
794 LOOP
795 FETCH inv_cur INTO inv_rec;
796 EXIT WHEN inv_cur%NOTFOUND;
797 INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
798 VALUES (inv_rec.requisition_header_id,inv_rec.requisition_line_id,vp_session_id);
799 END LOOP;
800 IF (inv_cur%rowcount = 0 ) THEN
801 CLOSE inv_cur;
802 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
803 EXIT;
804 END IF;
805 CLOSE inv_cur;
806 END IF;
807 IF (vp_check_id IS NOT NULL OR vp_check_date IS NOT NULL OR vp_amount IS NOT NULL
808 OR vp_treasury_pay_number IS NOT NULL OR vp_treasury_pay_date IS NOT NULL) THEN
809 OPEN pay_cur;
810 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
811 LOOP
812 FETCH pay_cur INTO pay_rec;
813 EXIT WHEN pay_cur%NOTFOUND;
814 INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
815 VALUES (pay_rec.requisition_header_id,pay_rec.requisition_line_id,vp_session_id );
816 END LOOP;
817 IF NOT (pay_cur%rowcount <> 0 ) THEN
818 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
819 CLOSE pay_cur;
820 EXIT;
821 END IF;
822 CLOSE pay_cur;
823 END IF;
824 EXIT;
825 END LOOP;
826 EXCEPTION
827 WHEN OTHERS THEN
828 l_errbuf := SQLERRM;
829 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
830 RAISE;
831
832 END req_master;
833
834 PROCEDURE rec_master IS
835 l_module_name VARCHAR2(200) := g_module_name || 'rec_master';
836 l_errbuf VARCHAR2(1024);
837
838 CURSOR rec_cur IS
839 SELECT shipment_header_id FROM fv_receipt_master_v frm
840 WHERE shipment_header_id = NVL(vp_shipment_header_id,shipment_header_id)
841 AND TRUNC(receipt_date) = NVL(vp_rec_date,TRUNC(receipt_date))
842 AND vendor_id = vp_vendor_id
843 AND vendor_site_id = vp_vendor_site_id;
844 rec_rec rec_cur%ROWTYPE;
845
846 CURSOR po_cur IS
847 SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
848 WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
849 WHERE rt.shipment_header_id = rsh.shipment_header_id
850 AND EXISTS (select 1 FROM po_headers ph
851 WHERE ph.po_header_id = rt.po_header_id
852 AND vendor_id = vp_vendor_id
853 AND vendor_site_id = vp_vendor_site_id
854 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
855 AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
856 AND ph.agent_id = NVL(vp_buyer,ph.agent_id)))
857 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
858 UNION
859 SELECT shipment_header_id FROM rcv_shipment_headers rsh
860 WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
861 WHERE rt.shipment_header_id = rsh.shipment_header_id
862 AND EXISTS (select 1 FROM po_headers ph
863 WHERE ph.po_header_id = rt.po_header_id
864 AND vendor_id = vp_vendor_id
865 AND vendor_site_id = vp_vendor_site_id
866 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
867 AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
868 AND ph.agent_id = NVL(vp_buyer,ph.agent_id)))
869 AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
870 WHERE shipment_header_id = rsh.shipment_header_id
871 AND session_id = vp_session_id);
872 po_rec rec_cur%ROWTYPE;
873 CURSOR req_cur IS
874 SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
875 WHERE rsh.vendor_id = vp_vendor_id
876 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
877 AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
878 WHERE ph.po_header_id = rt.po_header_id
879 AND rt.shipment_header_id = rsh.shipment_header_id
880 AND ph.vendor_site_id = vp_vendor_site_id
881 AND (EXISTS (SELECT 1 FROM po_req_distributions prd
882 WHERE prd.distribution_id = rt.req_distribution_id
883 AND EXISTS (SELECT 1 FROM po_requisition_lines prl,po_requisition_headers prh
884 WHERE prl.requisition_header_id = prh.requisition_header_id
885 AND prl.requisition_line_id = prd.requisition_line_id
886 AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
887 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))
888 OR EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
889 WHERE prl.requisition_header_id = prh.requisition_header_id
890 AND prl.line_location_id = rt.po_line_location_id
891 AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
892 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))
893
894 UNION
895 SELECT shipment_header_id FROM rcv_shipment_headers rsh
896 WHERE rsh.vendor_id = vp_vendor_id
897 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fdct
898 WHERE fdct.shipment_header_id = rsh.shipment_header_id
899 AND fdct.session_id = vp_session_id)
900 AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
901 WHERE rt.po_header_id = ph.po_header_id
902 AND rt.shipment_header_id = rsh.shipment_header_id
903 AND ph.vendor_site_id = vp_vendor_site_id
904 AND (EXISTS (SELECT 1 FROM po_req_distributions prd
905 WHERE prd.distribution_id = rt.req_distribution_id
906 AND EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
907 WHERE prl.requisition_header_id = prh.requisition_header_id
908 AND prl.requisition_line_id = prd.requisition_line_id
909 AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
910 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))
911 OR EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
912 WHERE prl.requisition_header_id = prh.requisition_header_id
913 AND prl.line_location_id = rt.po_line_location_id
914 AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
915 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))));
916
917
918 req_rec req_cur%ROWTYPE;
919
920
921 CURSOR inv_cur IS
922 SELECT shipment_header_id FROM rcv_shipment_headers rsh
923 WHERE vendor_id = vp_vendor_id
924 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
925 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
926 WHERE ph.po_header_id = rt.po_header_id
927 AND rt.shipment_header_id = rsh.shipment_header_id
928 AND ph.vendor_site_id = vp_vendor_site_id
929 AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
930 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
931 WHERE ia.po_distribution_id= pd.po_distribution_id
932 AND EXISTS (SELECT 1 FROM ap_invoices ap
933 WHERE invoice_id = ia.invoice_id
934 AND invoice_id = NVL(vp_invoice_id,invoice_id)
935 AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
936 AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
937 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))))
938 UNION
939 SELECT shipment_header_id FROM rcv_shipment_headers rsh
940 WHERE vendor_id = vp_vendor_id
941 AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
942 WHERE shipment_header_id = rsh.shipment_header_id
943 AND session_id = vp_session_id)
944 AND EXISTS (SELECT 1 FROM rcv_transactions rt ,po_headers ph
945 WHERE rt.po_header_id = ph.po_header_id
946 AND rt.shipment_header_id = rsh.shipment_header_id
947 AND ph.vendor_site_id = vp_vendor_site_id
948 AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
949 AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
950 WHERE ia.po_distribution_id= pd.po_distribution_id
951 AND EXISTS (SELECT 1 FROM ap_invoices ap
952 WHERE invoice_id = ia.invoice_id
953 AND invoice_id = NVL(vp_invoice_id,invoice_id)
954 AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
955 AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
956 AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))));
957
958 inv_rec inv_cur%ROWTYPE;
959 CURSOR pay_cur IS
960 SELECT shipment_header_id FROM rcv_shipment_headers rsh
961 WHERE vendor_id = vp_vendor_id
962 AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
963 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
964 WHERE rt.po_header_id = ph.po_header_id
965 AND rt.shipment_header_id = rsh.shipment_header_id
966 AND ph.vendor_site_id = vp_vendor_site_id
967 AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
968 AND EXISTS( SELECT 1 FROM ap_invoice_distributions aid
969 WHERE aid.po_distribution_id= pd.po_distribution_id
970 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
971 WHERE aip.invoice_id = aid.invoice_id
972 AND EXISTS (SELECT 1 FROM ap_checks ac
973 WHERE ac.check_id = aip.check_id
974 AND ac.check_id = NVL(vp_check_id,ac.check_id)
975 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
976 AND ac.amount = nvl(vp_amount,ac.amount)
977 AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
978 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL))))))
979 UNION
980 SELECT shipment_header_id FROM rcv_shipment_headers rsh
981 WHERE vendor_id = vp_vendor_id
982 AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
983 WHERE shipment_header_id = rsh.shipment_header_id
984 AND session_id = vp_session_id)
985 AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
986 WHERE rt.po_header_id = ph.po_header_id
987 AND rt.shipment_header_id = rsh.shipment_header_id
988 AND ph.vendor_site_id = vp_vendor_site_id
989 AND EXISTS (SELECT 1 FROM po_distributions pd
990 WHERE pd.line_location_id = rt.po_line_location_id
991 AND EXISTS( SELECT 1 FROM ap_invoice_distributions aid
992 WHERE aid.po_distribution_id= pd.po_distribution_id
993 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
994 WHERE aip.invoice_id = aid.invoice_id
995 AND EXISTS (SELECT 1 FROM ap_checks ac
996 WHERE ac.check_id = aip.check_id
997 AND ac.check_id = NVL(vp_check_id,ac.check_id)
998 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
999 AND ac.amount = nvl(vp_amount,ac.amount)
1000 AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
1001 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL))))));
1002
1003
1004 pay_rec pay_cur%ROWTYPE;
1005 BEGIN
1006 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1007 LOOP
1008 IF (vp_shipment_header_id IS NOT NULL OR vp_rec_date is NOT NULL) THEN
1009 OPEN rec_cur;
1010 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1011 LOOP
1012 FETCH rec_cur INTO rec_rec;
1013 EXIT WHEN rec_cur%NOTFOUND;
1014 INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
1015 VALUES (rec_rec.shipment_header_id,vp_session_id);
1016 END LOOP;
1017 IF NOT (rec_cur%rowcount <> 0 ) THEN
1018 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1019 CLOSE rec_cur;
1020 EXIT;
1021 END IF;
1022 CLOSE rec_cur;
1023 END IF;
1024 IF (vp_po_header_id IS NOT NULL OR vp_po_date IS NOT NULL OR vp_buyer IS NOT NULL) THEN
1025 OPEN po_cur;
1026 LOOP
1027 FETCH po_cur INTO po_rec;
1028 EXIT WHEN po_cur%NOTFOUND;
1029 INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
1030 VALUES (po_rec.shipment_header_id,vp_session_id);
1031 END LOOP;
1032 IF NOT (po_cur%rowcount <> 0 ) THEN
1033 DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;
1034 CLOSE po_cur;
1035 EXIT;
1036 END IF;
1037 CLOSE po_cur;
1038 END IF ;
1039 IF (vp_requisition_header_id IS NOT NULL OR vp_req_date is NOT NULL) THEN
1040 OPEN req_cur;
1041 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1042 LOOP
1043 FETCH req_cur INTO req_rec;
1044 EXIT WHEN req_cur%NOTFOUND;
1045 INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
1046 VALUES (req_rec.shipment_header_id,vp_session_id);
1047 END LOOP;
1048 IF NOT (req_cur%rowcount <> 0 ) THEN
1049 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1050 CLOSE req_cur;
1051 EXIT;
1052 END IF;
1053 CLOSE req_cur;
1054 END IF;
1055 IF (vp_invoice_id IS NOT NULL OR vp_invoice_date IS NOT NULL OR vp_invoice_type IS NOT NULL
1056 OR vp_invoice_amount IS NOT NULL) THEN
1057 OPEN inv_cur;
1058 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1059 LOOP
1060 FETCH inv_cur INTO inv_rec;
1061 EXIT WHEN inv_cur%NOTFOUND;
1062 INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
1063 VALUES (inv_rec.shipment_header_id,vp_session_id);
1064 END LOOP;
1065 IF NOT (inv_cur%rowcount <> 0 ) THEN
1066 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1067 CLOSE inv_cur;
1068 EXIT;
1069 END IF;
1070 CLOSE inv_cur;
1071 END IF;
1072 IF (vp_check_id IS NOT NULL OR vp_check_date IS NOT NULL
1073 OR vp_amount IS NOT NULL OR vp_treasury_pay_number IS NOT NULL
1074 OR vp_treasury_pay_date IS NOT NULL) THEN
1075 OPEN pay_cur;
1076 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1077 LOOP
1078 FETCH pay_cur INTO pay_rec;
1079 EXIT WHEN pay_cur%NOTFOUND;
1080 INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
1081 VALUES (pay_rec.shipment_header_id,vp_session_id);
1082 END LOOP;
1083 IF NOT (pay_cur%rowcount <> 0 ) THEN
1084 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1085 CLOSE pay_cur;
1086 EXIT;
1087 END IF;
1088 CLOSE pay_cur;
1089 END IF;
1090 EXIT;
1091 END LOOP;
1092 EXCEPTION
1093 WHEN OTHERS THEN
1094 l_errbuf := SQLERRM;
1095 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1096 RAISE;
1097
1098 END rec_master;
1099
1100
1101 PROCEDURE inv_master IS
1102 l_module_name VARCHAR2(200) := g_module_name || 'inv_master';
1103 l_errbuf VARCHAR2(1024);
1104
1105 CURSOR req_cur IS
1106 SELECT invoice_id FROM ap_invoices ai
1107 WHERE vendor_id = vp_vendor_id
1108 AND vendor_site_id = vp_vendor_site_id
1109 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1110 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1111 WHERE aid.invoice_id = ai.invoice_id
1112 AND EXISTS (SELECT 1 FROM po_distributions pd
1113 WHERE pd.po_distribution_id = aid.po_distribution_id
1114 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1115 WHERE prd.distribution_id = pd.req_distribution_id
1116 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1117 WHERE prl.requisition_line_id = prd.requisition_line_id
1118 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1119 WHERE prh.requisition_header_id = prl.requisition_header_id
1120 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1121 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
1122 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1123 WHERE rt.transaction_id = aid.rcv_transaction_id
1124 AND EXISTS (SELECT 1 FROM po_line_locations pll
1125 WHERE rt.po_line_location_id = pll.line_location_id
1126 AND EXISTS(SELECT 1 FROM po_distributions pd
1127 WHERE pd.line_location_id = pll.line_location_id
1128 AND EXISTS (SELECT 1 FROM po_req_distributions prd
1129 WHERE prd.distribution_id = pd.req_distribution_id
1130 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1131 WHERE prl.requisition_line_id = prd.requisition_line_id
1132 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1133 WHERE prh.requisition_header_id = prl.requisition_header_id
1134 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1135 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))))))
1136 UNION
1137 SELECT invoice_id FROM ap_invoices ai
1138 WHERE vendor_id = vp_vendor_id
1139 AND vendor_site_id = vp_vendor_site_id
1140 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1141 WHERE invoice_id= ai.invoice_id
1142 AND session_id = vp_session_id)
1143 AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1144 WHERE aid.invoice_id = ai.invoice_id
1145 AND EXISTS (SELECT 1 FROM po_distributions pd
1146 WHERE pd.po_distribution_id = aid.po_distribution_id
1147 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1148 WHERE prd.distribution_id = pd.req_distribution_id
1149 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1150 WHERE prl.requisition_line_id = prd.requisition_line_id
1151 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1152 WHERE prh.requisition_header_id = prl.requisition_header_id
1153 AND prh.requisition_header_id =NVL(vp_requisition_header_id,prh.requisition_header_id )
1154 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
1155 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1156 WHERE rt.transaction_id = aid.rcv_transaction_id
1157 AND EXISTS (SELECT 1 FROM po_line_locations pll
1158 WHERE rt.po_line_location_id = pll.line_location_id
1159 AND EXISTS(SELECT 1 FROM po_distributions pd
1160 WHERE pd.line_location_id = pll.line_location_id
1161 AND EXISTS (SELECT 1 FROM po_req_distributions prd
1162 WHERE prd.distribution_id = pd.req_distribution_id
1163 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1164 WHERE prl.requisition_line_id = prd.requisition_line_id
1165 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1166 WHERE prh.requisition_header_id = prl.requisition_header_id
1167 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1168 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))))));
1169
1170 req_rec req_cur%ROWTYPE;
1171
1172 CURSOR po_cur IS
1173 SELECT invoice_id FROM ap_invoices ai
1174 WHERE vendor_id = vp_vendor_id
1175 AND vendor_site_id = vp_vendor_site_id
1176 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1177 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1178 WHERE aid.invoice_id = ai.invoice_id
1179 AND EXISTS (SELECT 1 FROM po_distributions pd
1180 WHERE pd.po_distribution_id = aid.po_distribution_id
1181 AND EXISTS(SELECT 1 FROM po_headers ph
1182 WHERE ph.po_header_id = pd.po_header_id
1183 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1184 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1185 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1186 WHERE rt.transaction_id = aid.rcv_transaction_id
1187 AND EXISTS(SELECT 1 FROM po_headers ph
1188 WHERE ph.po_header_id = rt.po_header_id
1189 AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
1190 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))
1191 UNION
1192 SELECT invoice_id FROM ap_invoices ai
1193 WHERE vendor_id = vp_vendor_id
1194 AND vendor_site_id = vp_vendor_site_id
1195 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1196 WHERE invoice_id = ai.invoice_id
1197 AND session_id = vp_session_id)
1198 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1199 WHERE aid.invoice_id = ai.invoice_id
1200 AND EXISTS (SELECT 1 FROM po_distributions pd
1201 WHERE pd.po_distribution_id = aid.po_distribution_id
1202 AND EXISTS(SELECT 1 FROM po_headers ph
1203 WHERE ph.po_header_id = pd.po_header_id
1204 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1205 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1206 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1207 WHERE rt.transaction_id = aid.rcv_transaction_id
1208 AND EXISTS(SELECT 1 FROM po_headers ph
1209 WHERE ph.po_header_id = rt.po_header_id
1210 AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
1211 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))));
1212
1213
1214
1215
1216 po_rec po_cur%ROWTYPE;
1217
1218 CURSOR rec_cur IS
1219 SELECT invoice_id FROM ap_invoices ai
1220 WHERE vendor_id = vp_vendor_id
1221 AND vendor_site_id = vp_vendor_site_id
1222 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1223 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1224 WHERE aid.invoice_id = ai.invoice_id
1225 AND (EXISTS (SELECT 1 FROM rcv_transactions rt
1226 WHERE rt.transaction_id = aid.rcv_transaction_id
1227 AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
1228 WHERE rt.shipment_header_id = rsh.shipment_header_id
1229 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1230 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))
1231 OR EXISTS (SELECT 1 FROM po_distributions pd
1232 WHERE pd.po_distribution_id = aid.po_distribution_id
1233 AND EXISTS(SELECT 1 FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh
1234 WHERE rsl.po_line_location_id = pd.line_location_id
1235 AND rsl.shipment_header_id = rsh.shipment_header_id
1236 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1237 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))))
1238 UNION
1239 SELECT invoice_id FROM ap_invoices ai
1240 WHERE vendor_id = vp_vendor_id
1241 AND vendor_site_id = vp_vendor_site_id
1242 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1243 WHERE invoice_id = ai.invoice_id
1244 AND session_id = vp_session_id)
1245 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1246 WHERE aid.invoice_id = ai.invoice_id
1247 AND (EXISTS (SELECT 1 FROM rcv_transactions rt
1248 WHERE rt.transaction_id = aid.rcv_transaction_id
1249 AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
1250 WHERE rt.shipment_header_id = rsh.shipment_header_id
1251 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1252 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))
1253 OR EXISTS (SELECT 1 FROM po_distributions pd
1254 WHERE pd.po_distribution_id = aid.po_distribution_id
1255 AND EXISTS(SELECT 1 FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh
1256 WHERE rsl.po_line_location_id = pd.line_location_id
1257 AND rsl.shipment_header_id = rsh.shipment_header_id
1258 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1259 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))));
1260
1261 rec_rec rec_cur%ROWTYPE;
1262
1263
1264 CURSOR inv_cur IS
1265 SELECT invoice_id FROM ap_invoices
1266 WHERE vendor_id = vp_vendor_id
1267 AND vendor_site_id = vp_vendor_site_id
1268 AND invoice_id = NVL(vp_invoice_id,invoice_id)
1269 AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
1270 AND invoice_amount = NVL(vp_invoice_amount,invoice_amount)
1271 AND TRUNC(invoice_date) = NVL(TRUNC(vp_invoice_date), TRUNC(invoice_date)) ;
1272 inv_rec inv_cur%ROWTYPE;
1273
1274 CURSOR pay_cur IS
1275 SELECT invoice_id FROM ap_invoices ai
1276 WHERE vendor_id = vp_vendor_id
1277 AND vendor_site_id = vp_vendor_site_id
1278 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1279 AND EXISTS ( SELECT 1 FROM ap_invoice_payments aip
1280 WHERE aip.invoice_id = ai.invoice_id
1281 AND EXISTS (SELECT 1 FROM ap_checks ac
1282 WHERE ac.check_id = aip.check_id
1283 AND ac.check_id = NVL(vp_check_id,ac.check_id)
1284 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
1285 AND ac.amount = nvl(vp_amount,ac.amount)
1286 AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
1287 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
1288 ))
1289 UNION
1290 SELECT invoice_id FROM ap_invoices ai
1291 WHERE vendor_id = vp_vendor_id
1292 AND vendor_site_id = vp_vendor_site_id
1293 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1294 WHERE invoice_id = ai.invoice_id
1295 AND session_id = vp_session_id)
1296 AND EXISTS ( SELECT 1 FROM ap_invoice_payments aip
1297 WHERE aip.invoice_id = ai.invoice_id
1298 AND EXISTS (SELECT 1 FROM ap_checks ac
1299 WHERE ac.check_id = aip.check_id
1300 AND ac.check_id = NVL(vp_check_id,ac.check_id)
1301 AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
1302 AND ac.amount = nvl(vp_amount,ac.amount)
1303 AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
1304 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
1305 ));
1306 pay_rec pay_cur%ROWTYPE;
1307
1308
1309 BEGIN
1310 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1311 LOOP
1312 IF (vp_invoice_id IS NOT NULL OR vp_invoice_date IS NOT NULL OR
1313 vp_invoice_type IS NOT NULL OR vp_invoice_amount IS NOT NULL) THEN
1314 OPEN inv_cur;
1315 LOOP
1316 FETCH inv_cur INTO inv_rec;
1317 EXIT WHEN inv_cur%NOTFOUND;
1318 INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
1319 VALUES (inv_rec.invoice_id,vp_session_id);
1320 END LOOP;
1321 IF (inv_cur%rowcount = 0 ) THEN
1322 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1323 CLOSE inv_cur;
1324 EXIT;
1325 END IF;
1326 CLOSE inv_cur;
1327 END IF;
1328 IF (vp_po_header_id IS NOT NULL OR vp_po_date IS NOT NULL OR vp_buyer IS NOT NULL) THEN
1329 OPEN po_cur;
1330 LOOP
1331 FETCH po_cur INTO po_rec;
1332 EXIT WHEN po_cur%NOTFOUND;
1333 INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
1334 VALUES (po_rec.invoice_id,vp_session_id);
1335 END LOOP;
1336 IF (po_cur%rowcount = 0 ) THEN
1337 DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;
1338 CLOSE po_cur;
1339 EXIT;
1340 END IF;
1341 CLOSE po_cur;
1342 END IF ;
1343 IF (vp_requisition_header_id IS NOT NULL OR vp_req_date is NOT NULL) THEN
1344 OPEN req_cur;
1345 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1346 LOOP
1347 FETCH req_cur INTO req_rec;
1348 EXIT WHEN req_cur%NOTFOUND;
1349 INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
1350 VALUES (req_rec.invoice_id,vp_session_id);
1351 END LOOP;
1352 IF (req_cur%rowcount =0 ) THEN
1353 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1354 CLOSE req_cur;
1355 EXIT;
1356 END IF;
1357 CLOSE req_cur;
1358 END IF;
1359 IF (vp_shipment_header_id IS NOT NULL OR vp_rec_date is NOT NULL) THEN
1360 OPEN rec_cur;
1361 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1362 LOOP
1363 FETCH rec_cur INTO rec_rec;
1364 EXIT WHEN rec_cur%NOTFOUND;
1365 INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
1366 VALUES (rec_rec.invoice_id,vp_session_id);
1367 END LOOP;
1368 IF (rec_cur%rowcount = 0 ) THEN
1369 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1370 CLOSE rec_cur;
1371 EXIT;
1372 END IF;
1373 CLOSE rec_cur;
1374 END IF;
1375 IF (vp_check_id IS NOT NULL OR vp_check_date IS NOT NULL
1376 OR vp_amount IS NOT NULL OR vp_treasury_pay_number IS NOT NULL
1377 OR vp_treasury_pay_date IS NOT NULL) THEN
1378 OPEN pay_cur;
1379 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1380 LOOP
1381 FETCH pay_cur INTO pay_rec;
1382 EXIT WHEN pay_cur%NOTFOUND;
1383 INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
1384 VALUES (pay_rec.invoice_id,vp_session_id);
1385 END LOOP;
1386 IF (pay_cur%rowcount = 0 ) THEN
1387 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1388 CLOSE pay_cur;
1389 EXIT;
1390 END IF;
1391 CLOSE pay_cur;
1392 END IF;
1393 EXIT;
1394 END LOOP;
1395 EXCEPTION
1396 WHEN OTHERS THEN
1397 l_errbuf := SQLERRM;
1398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1399 RAISE;
1400
1401 END inv_master;
1402
1403
1404 PROCEDURE pay_master IS
1405 l_module_name VARCHAR2(200) := g_module_name || 'pay_master';
1406 l_errbuf VARCHAR2(1024);
1407
1408 CURSOR req_cur IS
1409 SELECT check_id FROM ap_checks ac
1410 WHERE vendor_id = vp_vendor_id
1411 AND vendor_site_id = vp_vendor_site_id
1412 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1413 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1414 WHERE aip.check_id = ac.check_id
1415 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1416 WHERE aid.invoice_id = aip.invoice_id
1417 AND (EXISTS (SELECT 1 FROM po_distributions pd
1418 WHERE pd.po_distribution_id = aid.po_distribution_id
1419 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1420 WHERE prd.distribution_id = pd.req_distribution_id
1421 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1422 WHERE prl.requisition_line_id = prd.requisition_line_id
1423 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1424 WHERE prh.requisition_header_id = prl.requisition_header_id
1425 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1426 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
1427 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1428 WHERE rt.transaction_id = aid.rcv_transaction_id
1429 AND EXISTS (SELECT 1 FROM po_line_locations pll
1430 WHERE pll.line_location_id = rt.po_line_location_id
1431 AND EXISTS (SELECT 1 FROM po_distributions pd
1432 WHERE pd.line_location_id = pll.line_location_id
1433 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1434 WHERE prd.distribution_id = pd.req_distribution_id
1435 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1436 WHERE prl.requisition_line_id = prd.requisition_line_id
1437 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1438 WHERE prh.requisition_header_id = prl.requisition_header_id
1439 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1440 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))))))))
1441
1442 UNION
1443 SELECT check_id FROM ap_checks ac
1444 WHERE vendor_id = vp_vendor_id
1445 AND vendor_site_id = vp_vendor_site_id
1446 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1447 WHERE check_id = ac.check_id
1448 AND session_id = vp_session_id)
1449 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1450 WHERE aip.check_id = ac.check_id
1451 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1452 WHERE aid.invoice_id = aip.invoice_id
1453 AND (EXISTS (SELECT 1 FROM po_distributions pd
1454 WHERE pd.po_distribution_id = aid.po_distribution_id
1455 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1456 WHERE prd.distribution_id = pd.req_distribution_id
1457 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1458 WHERE prl.requisition_line_id = prd.requisition_line_id
1459 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1460 WHERE prh.requisition_header_id = prl.requisition_header_id
1461 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1462 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
1463 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1464 WHERE rt.transaction_id = aid.rcv_transaction_id
1465 AND EXISTS (SELECT 1 FROM po_line_locations pll
1466 WHERE pll.line_location_id = rt.po_line_location_id
1467 AND EXISTS (SELECT 1 FROM po_distributions pd
1468 WHERE pd.line_location_id = pll.line_location_id
1469 AND EXISTS(SELECT 1 FROM po_req_distributions prd
1470 WHERE prd.distribution_id = pd.req_distribution_id
1471 AND EXISTS (SELECT 1 FROM po_requisition_lines prl
1472 WHERE prl.requisition_line_id = prd.requisition_line_id
1473 AND EXISTS (SELECT 1 FROM po_requisition_headers prh
1474 WHERE prh.requisition_header_id = prl.requisition_header_id
1475 AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
1476 AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))))))));
1477 req_rec req_cur%ROWTYPE;
1478
1479 CURSOR po_cur IS
1480 SELECT check_id FROM ap_checks ac
1481 WHERE vendor_id = vp_vendor_id
1482 AND vendor_site_id = vp_vendor_site_id
1483 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1484 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1485 WHERE aip.check_id = ac.check_id
1486 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1487 WHERE aid.invoice_id = aip.invoice_id
1488 AND (EXISTS (SELECT 1 FROM po_distributions pd
1489 WHERE pd.po_distribution_id = aid.po_distribution_id
1490 AND EXISTS(SELECT 1 FROM po_headers ph
1491 WHERE ph.po_header_id = pd.po_header_id
1492 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1493 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1494 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1495 WHERE rt.transaction_id = aid.rcv_transaction_id
1496 AND EXISTS(SELECT 1 FROM po_headers ph
1497 WHERE ph.po_header_id = rt.po_header_id
1498 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1499 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))))
1500
1501 UNION
1502 SELECT check_id FROM ap_checks ac
1503 WHERE vendor_id = vp_vendor_id
1504 AND vendor_site_id = vp_vendor_site_id
1505 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1506 WHERE check_id= ac.check_id
1507 AND session_id = vp_session_id)
1508 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1509 WHERE aip.check_id = ac.check_id
1510 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1511 WHERE aid.invoice_id = aip.invoice_id
1512 AND (EXISTS (SELECT 1 FROM po_distributions pd
1513 WHERE pd.po_distribution_id = aid.po_distribution_id
1514 AND EXISTS(SELECT 1 FROM po_headers ph
1515 WHERE ph.po_header_id = pd.po_header_id
1516 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1517 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1518 OR EXISTS (SELECT 1 FROM rcv_transactions rt
1519 WHERE rt.transaction_id = aid.rcv_transaction_id
1520 AND EXISTS(SELECT 1 FROM po_headers ph
1521 WHERE ph.po_header_id = rt.po_header_id
1522 AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1523 AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))));
1524
1525 po_rec po_cur%ROWTYPE;
1526
1527 CURSOR rec_cur IS
1528 SELECT check_id FROM ap_checks ac
1529 WHERE vendor_id = vp_vendor_id
1530 AND vendor_site_id = vp_vendor_site_id
1531 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1532 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1533 WHERE aip.check_id = ac.check_id
1534 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1535 WHERE aid.invoice_id = aip.invoice_id
1536 AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.po_distribution_id = aid.po_distribution_id
1537 AND EXISTS( SELECT 1 FROM rcv_transactions rt
1538 WHERE rt.po_line_location_id = pd.line_location_id
1539 AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
1540 WHERE rt.shipment_header_id = rsh.shipment_header_id
1541 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1542 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date)))))))
1543 UNION
1544 SELECT check_id FROM ap_checks ac
1545 WHERE vendor_id = vp_vendor_id
1546 AND vendor_site_id = vp_vendor_site_id
1547 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1548 WHERE check_id = ac.check_id
1549 AND session_id = vp_session_id)
1550 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1551 WHERE aip.check_id = ac.check_id
1552 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1553 WHERE aid.invoice_id = aip.invoice_id
1554 AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.po_distribution_id = aid.po_distribution_id
1555 AND EXISTS( SELECT 1 FROM rcv_transactions rt
1556 WHERE rt.po_line_location_id = pd.line_location_id
1557 AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
1558 WHERE rt.shipment_header_id = rsh.shipment_header_id
1559 AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
1560 AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date)))))));
1561
1562 rec_rec rec_cur%ROWTYPE;
1563
1564
1565 CURSOR inv_cur IS
1566 SELECT check_id FROM ap_checks ac
1567 WHERE vendor_id = vp_vendor_id
1568 AND vendor_site_id = vp_vendor_site_id
1569 AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
1570 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1571 WHERE aip.check_id = ac.check_id
1572 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1573 WHERE aid.invoice_id = aip.invoice_id
1574 AND EXISTS (SELECT 1 FROM ap_invoices ai
1575 WHERE ai.invoice_id = aid.invoice_id
1576 AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
1577 AND ai.invoice_id = NVL(vp_invoice_id,ai.invoice_id )
1578 AND ai.invoice_amount = NVL(vp_invoice_amount,ai.invoice_amount)
1579 AND TRUNC(ai.invoice_date) = NVL( vp_invoice_date,TRUNC(ai.invoice_date)))))
1580 UNION
1581 SELECT check_id FROM ap_checks ac
1582 WHERE vendor_id = vp_vendor_id
1583 AND vendor_site_id = vp_vendor_site_id
1584 AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
1585 WHERE check_id = ac.check_id
1586 AND session_id = vp_session_id)
1587 AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
1588 WHERE aip.check_id = ac.check_id
1589 AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1590 WHERE aid.invoice_id = aip.invoice_id
1591 AND EXISTS (SELECT 1 FROM ap_invoices ai
1592 WHERE ai.invoice_id = aid.invoice_id
1593 AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
1594 AND ai.invoice_id = NVL(vp_invoice_id,ai.invoice_id )
1595 AND ai.invoice_amount = NVL(vp_invoice_amount,ai.invoice_amount)
1596 AND TRUNC(ai.invoice_date) = NVL( TRUNC(vp_invoice_date),TRUNC(ai.invoice_date)))));
1597 inv_rec inv_cur%ROWTYPE;
1598
1599 CURSOR pay_cur IS
1600 SELECT check_id FROM ap_checks ac
1601 WHERE vendor_id = vp_vendor_id
1602 AND vendor_site_id = vp_vendor_site_id
1603 AND ac.check_id = NVL(vp_check_id,ac.check_id)
1604 AND ac.check_date = NVL(vp_check_date,ac.check_date)
1605 AND ac.amount = nvl(vp_amount,ac.amount)
1606 AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
1607 AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1));
1608 pay_rec pay_cur%ROWTYPE;
1609
1610 BEGIN
1611 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1612 LOOP
1613 IF (vp_check_id IS NOT NULL OR vp_check_date IS NOT NULL
1614 OR vp_amount IS NOT NULL OR vp_treasury_pay_date IS NOT NULL OR vp_treasury_pay_number IS NOT NULL) THEN
1615 OPEN pay_cur;
1616 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1617 LOOP
1618 FETCH pay_cur INTO pay_rec;
1619 EXIT WHEN pay_cur%NOTFOUND;
1620 INSERT INTO fv_doc_cr_temp (check_id,session_id)
1621 VALUES (pay_rec.check_id,vp_session_id);
1622 END LOOP;
1623 IF NOT (pay_cur%rowcount <> 0 ) THEN
1624 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1625 CLOSE pay_cur;
1626 EXIT;
1627 END IF;
1628 CLOSE pay_cur;
1629 END IF;
1630 IF (vp_po_header_id IS NOT NULL OR vp_po_date IS NOT NULL OR vp_buyer IS NOT NULL) THEN
1631 OPEN po_cur;
1632 LOOP
1633 FETCH po_cur INTO po_rec;
1634 EXIT WHEN po_cur%NOTFOUND;
1635 INSERT INTO fv_doc_cr_temp (check_id,session_id)
1636 VALUES (po_rec.check_id,vp_session_id);
1637 END LOOP;
1638 IF NOT (po_cur%rowcount <> 0 ) THEN
1639 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1640 CLOSE po_cur;
1641 EXIT;
1642 END IF;
1643 CLOSE po_cur;
1644 END IF ;
1645 IF (vp_requisition_header_id IS NOT NULL OR vp_req_date is NOT NULL) THEN
1646 OPEN req_cur;
1647 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1648 LOOP
1649 FETCH req_cur INTO req_rec;
1650 EXIT WHEN req_cur%NOTFOUND;
1651 INSERT INTO fv_doc_cr_temp (check_id,session_id)
1652 VALUES (req_rec.check_id,vp_session_id);
1653 END LOOP;
1654 IF NOT (req_cur%rowcount <> 0 ) THEN
1655 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1656 CLOSE req_cur;
1657 EXIT;
1658 END IF;
1659 CLOSE req_cur;
1660 END IF;
1661 IF (vp_shipment_header_id IS NOT NULL OR vp_rec_date is NOT NULL) THEN
1662 OPEN rec_cur;
1663 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1664 LOOP
1665 FETCH rec_cur INTO rec_rec;
1666 EXIT WHEN rec_cur%NOTFOUND;
1667 INSERT INTO fv_doc_cr_temp (check_id,session_id)
1668 VALUES (rec_rec.check_id,vp_session_id);
1669 END LOOP;
1670 IF NOT (rec_cur%rowcount <> 0 ) THEN
1671 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1672 CLOSE rec_cur;
1673 EXIT;
1674 END IF;
1675 CLOSE rec_cur;
1676 END IF;
1677 IF (vp_invoice_id IS NOT NULL OR vp_invoice_date IS NOT NULL
1678 OR vp_invoice_type IS NOT NULL OR vp_invoice_amount IS NOT NULL) THEN
1679 OPEN inv_cur;
1680 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1681 LOOP
1682 FETCH inv_cur INTO inv_rec;
1683 EXIT WHEN inv_cur%NOTFOUND;
1684 INSERT INTO fv_doc_cr_temp (check_id,session_id)
1685 VALUES (inv_rec.check_id,vp_session_id);
1686 END LOOP;
1687 IF NOT (inv_cur%rowcount <> 0 ) THEN
1688 DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
1689 CLOSE inv_cur;
1690 EXIT;
1691 END IF;
1692 CLOSE inv_cur;
1693 END IF;
1694 EXIT;
1695 END LOOP;
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 l_errbuf := SQLERRM;
1700 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1701 RAISE;
1702
1703 end pay_master;
1704
1705
1706 END fv_cross_doc_ref;
1707
1708
1709 ----------------------------------------------------------------------
1710 -- END OF PACKAGE BODY
1711 ----------------------------------------------------------------------
1712
[Home] [Help]