APPS.FV_CROSS_DOC_REF dependencies on FV_DOC_CR_TEMP

Line 131: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_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

Line 142: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_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

Line 189: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_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

Line 204: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst

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)

Line 224: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 252: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst

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)

Line 288: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 324: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst

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)

Line 364: DELETE FROM fv_doc_cr_temp

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;

Line 369: 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;

Line 374: INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)

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;

Line 378: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 386: DELETE FROM fv_doc_cr_temp

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;

Line 391: INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)

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

Line 395: DELETE FROM fv_doc_cr_temp

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;

Line 405: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 409: INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)

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;

Line 413: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 422: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 426: INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)

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;

Line 430: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 440: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 444: INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)

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;

Line 448: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 479: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_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

Line 491: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst

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:

Line 532: WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 558: WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 591: WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 622: WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 659: WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 697: WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 737: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 742: 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 )

Line 746: INSERT INTO fv_doc_cr_temp (requisition_header_id,requisition_line_id,session_id )

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;

Line 750: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 762: INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)

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;

Line 766: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 775: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 779: INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)

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;

Line 783: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 793: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 797: INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)

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;

Line 802: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_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;

Line 810: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 814: INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)

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;

Line 818: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 857: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_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

Line 869: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 876: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE 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

Line 897: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fdct

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

Line 924: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 941: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 962: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 982: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp

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

Line 1006: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1010: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1014: INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)

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;

Line 1018: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1029: INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)

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;

Line 1033: DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1041: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1045: INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)

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;

Line 1049: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1058: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1062: INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)

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;

Line 1066: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1076: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1080: INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)

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;

Line 1084: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1109: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1140: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1176: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1195: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1222: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1242: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1278: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1293: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1310: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1318: INSERT INTO fv_doc_cr_temp (invoice_id,session_id)

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;

Line 1322: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1333: INSERT INTO fv_doc_cr_temp (invoice_id,session_id)

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;

Line 1337: DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1345: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1349: INSERT INTO fv_doc_cr_temp (invoice_id,session_id)

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;

Line 1353: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1361: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1365: INSERT INTO fv_doc_cr_temp (invoice_id,session_id)

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;

Line 1369: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1379: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1383: INSERT INTO fv_doc_cr_temp (invoice_id,session_id)

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;

Line 1387: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1412: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1446: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1483: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1505: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1531: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1547: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1569: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)

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

Line 1584: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp

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

Line 1611: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1616: 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)

Line 1620: INSERT INTO fv_doc_cr_temp (check_id,session_id)

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;

Line 1624: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1635: INSERT INTO fv_doc_cr_temp (check_id,session_id)

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;

Line 1639: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1647: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1651: INSERT INTO fv_doc_cr_temp (check_id,session_id)

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;

Line 1655: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1663: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1667: INSERT INTO fv_doc_cr_temp (check_id,session_id)

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;

Line 1671: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;

Line 1680: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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)

Line 1684: INSERT INTO fv_doc_cr_temp (check_id,session_id)

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;

Line 1688: DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;

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;