[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
FROM po_headers ph,po_lines pl,po_line_locations pll
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
UNION
SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
FROM po_headers ph,po_lines pl,po_line_locations pll
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
AND EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id
AND po_header_id = ph.po_header_id AND po_line_id = pl.po_line_id);
SELECT ph.po_header_id,pl.po_line_id, pll.line_location_id
FROM po_headers ph,po_lines pl,po_line_locations pll
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pll.po_line_id
AND EXISTS(SELECT prh.requisition_header_id,prl.requisition_line_id
FROM po_requisition_headers prh,po_requisition_lines prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND authorization_status = 'APPROVED'
AND prl.line_location_id = pll.line_location_id
AND prl.suggested_vendor_location = vp_supplier_site
AND prl.suggested_vendor_name = vp_supplier_name
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))
UNION
SELECT rh.requisition_header_id,rh.requisition_line_id
FROM (SELECT DISTINCT prh.requisition_header_id,
prl.requisition_line_id,prl.line_location_id
FROM po_requisition_headers prh,po_requisition_lines prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND authorization_status = 'APPROVED'
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
po_line_locations plx,po_headers ph
WHERE plx.line_location_id = rh.line_location_id
AND plx.po_header_id = ph.po_header_id
AND ph.vendor_id = vp_vendor_id
AND rh.line_location_id = pll.line_location_id
AND ph.vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM po_vendors pv,po_requisition_lines prl,po_vendor_sites pvs
WHERE prl.suggested_vendor_name = pv.vendor_name
AND prl.suggested_vendor_location = pvs.vendor_site_code
AND prl.requisition_line_id = rh.requisition_line_id ));
SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
FROM po_headers ph,po_line_locations pll
WHERE ph.po_header_id = pll.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_id = pll.po_line_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 from rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))
UNION
SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id
FROM po_headers ph,po_line_locations pll
WHERE ph.po_header_id = pll.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
WHERE fst.po_header_id = ph.po_header_id
AND fst.po_line_id = pll.po_line_id
AND fst.po_line_location_id = pll.line_location_id
AND fst.session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_id = pll.po_line_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 from rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))));
SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id FROM po_headers ph,po_line_locations pll
WHERE ph.po_header_id = pll.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) = NVL(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.rcv_transaction_id= rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) = NVL(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))))
UNION
SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id FROM po_headers ph,po_line_locations pll
WHERE vendor_id = vp_vendor_id
AND ph.po_header_id = pll.po_header_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
WHERE fst.po_header_id = ph.po_header_id
AND fst.po_line_id = pll.po_line_id
AND fst.po_line_location_id = pll.line_location_id
AND fst.session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) =NVL(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code =NVL(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.rcv_transaction_id= rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))));
SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
FROM po_headers ph,po_line_locations pll
WHERE ph.po_header_id = pll.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 from po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.line_location_id = pll.line_location_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id = pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL)))))
OR EXISTS (SELECT 1 from rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL))))))
UNION
SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
FROM po_headers ph,po_line_locations pll
WHERE ph.po_header_id = pll.po_header_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
WHERE fst.po_header_id = ph.po_header_id
AND fst.po_line_id = pll.po_line_id
AND fst.po_line_location_id = pll.line_location_id
AND fst.session_id = vp_session_id)
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 from po_distributions pd
WHERE pd.po_header_id = ph.po_header_id
AND pd.line_location_id = pll.line_location_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id = pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL)))))
OR EXISTS (SELECT 1 from rcv_transactions rt
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL))))));
DELETE FROM fv_doc_cr_temp
WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp
WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
VALUES (req_rec.po_header_id,req_rec.po_line_id,req_rec.line_location_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp
WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
VALUES(po_rec.po_header_id,po_rec.po_line_id,po_rec.line_location_id,vp_session_id);
DELETE FROM fv_doc_cr_temp
WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
VALUES (rec_rec.po_header_id,rec_rec.po_line_id,rec_rec.line_location_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
VALUES (inv_rec.po_header_id,inv_rec.po_line_id,inv_rec.line_location_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (po_header_id,po_line_id,po_line_location_id,session_id)
VALUES (pay_rec.po_header_id,pay_rec.po_line_id,pay_rec.line_location_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
SELECT prh.requisition_header_id ,prl.requisition_line_id
FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
WHERE prh.requisition_header_id =prl.requisition_header_id
AND pll.line_location_id = prl.line_location_id
AND ph.po_header_id = pll.po_header_id
AND ph.vendor_id = vp_vendor_id
AND ph.vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = NVL(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
UNION
SELECT prh.requisition_header_id ,prl.requisition_line_id
FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND pll.line_location_id = prl.line_location_id
AND ph.po_header_id = pll.po_header_id
AND ph.vendor_id = vp_vendor_id
AND ph.vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = NVL(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
WHERE fst.requisition_header_id = prh.requisition_header_id
AND requisition_line_id = prl.requisition_line_id
AND fst.session_id = vp_session_id);
SELECT prh.requisition_header_id,prl.requisition_line_id
FROM po_requisition_headers prh,po_requisition_lines prl
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
AND authorization_status = 'APPROVED'
AND prl.suggested_vendor_location = vp_supplier_site
AND prl.suggested_vendor_name = vp_supplier_name
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))
UNION
SELECT rh.requisition_header_id,rh.requisition_line_id
FROM (SELECT DISTINCT prh.requisition_header_id,
prl.requisition_line_id,prl.line_location_id
FROM po_requisition_headers prh,po_requisition_lines prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
AND authorization_status = 'APPROVED'
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
po_line_locations plx,po_headers ph
WHERE plx.line_location_id = rh.line_location_id
AND plx.po_header_id = ph.po_header_id
AND ph.vendor_id = vp_vendor_id
AND ph.vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM po_vendors pv,po_requisition_lines prl,po_vendor_sites pvs
WHERE prl.suggested_vendor_name = pv.vendor_name
AND prl.suggested_vendor_location = pvs.vendor_site_code
AND prl.requisition_line_id = rh.requisition_line_id );
SELECT requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.req_distribution_id = prd.distribution_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
OR EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = prl.line_location_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pd.line_location_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))))
UNION
SELECT requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE requisition_header_id = prl.requisition_header_id
AND requisition_line_id = prl.requisition_line_id
AND session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.req_distribution_id = prd.distribution_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
OR EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = prl.line_location_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.po_line_location_id = pd.line_location_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND rsh.shipment_header_id = rt.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))));
SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.req_distribution_id = prd.distribution_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND ai.vendor_id = vp_vendor_id
AND ai.vendor_site_id = vp_vendor_site_id
AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ai.invoice_date) = nvl(vp_invoice_date,TRUNC(ai.invoice_date))
AND ai.invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount)))
OR EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = pd.line_location_id
AND EXISTS ( SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND ai.vendor_id = vp_vendor_id
AND ai.vendor_site_id = vp_vendor_site_id
AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ai.invoice_date) = nvl(vp_invoice_date,TRUNC(ai.invoice_date))
AND ai.invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount))))))))
UNION
SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE requisition_header_id = prl.requisition_header_id
AND requisition_line_id = prl.requisition_line_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.req_distribution_id = prd.distribution_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND ai.vendor_id = vp_vendor_id
AND ai.vendor_site_id = vp_vendor_site_id
AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
AND trunc(ai.invoice_date) = nvl(vp_invoice_date,trunc(ai.invoice_date))
AND ai.invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount)))
OR EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = pd.line_location_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND ai.vendor_id = vp_vendor_id
AND ai.vendor_site_id = vp_vendor_site_id
AND ai.invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ai.invoice_date) = NVL(vp_invoice_date,TRUNC(ai.invoice_date))
AND ai.invoice_type_lookup_code =NVL(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_amount = nvl(vp_invoice_amount,invoice_amount))))))));
SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM po_req_distributions prd WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.req_distribution_id = prd.distribution_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id = pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.vendor_id = vp_vendor_id
AND ac.vendor_site_id = vp_vendor_site_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND ac.check_date = NVL(vp_check_date,ac.check_date)
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL))))
OR EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = pd.line_location_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.vendor_id = vp_vendor_id
AND ac.vendor_site_id = vp_vendor_site_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND ac.check_date = NVL(vp_check_date,ac.check_date)
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL)))))))))
UNION
SELECT prl.requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
WHERE EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE requisition_header_id = prl.requisition_header_id
AND requisition_line_id = prl.requisition_line_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.requisition_line_id = prl.requisition_line_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.req_distribution_id = prd.distribution_id
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id = pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND ac.check_date = NVL(vp_check_date,ac.check_date)
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL))))
OR EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = pd.line_location_id
AND EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.rcv_transaction_id = rt.transaction_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.vendor_id = vp_vendor_id
AND ac.vendor_site_id = vp_vendor_site_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND ac.check_date = NVL(vp_check_date,ac.check_date)
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) =NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
OR vp_treasury_pay_date IS NULL)))))))));
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (requisition_header_id,requisition_line_id,session_id )
VALUES (req_rec.requisition_header_id,req_rec.requisition_line_id,vp_session_id );
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
VALUES (po_rec.requisition_header_id,po_rec.requisition_line_id,vp_session_id );
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
VALUES (rec_rec.requisition_header_id,rec_rec.requisition_line_id,vp_session_id );
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
VALUES (inv_rec.requisition_header_id,inv_rec.requisition_line_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (requisition_header_id ,requisition_line_id,session_id)
VALUES (pay_rec.requisition_header_id,pay_rec.requisition_line_id,vp_session_id );
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
SELECT shipment_header_id FROM fv_receipt_master_v frm
WHERE shipment_header_id = NVL(vp_shipment_header_id,shipment_header_id)
AND TRUNC(receipt_date) = NVL(vp_rec_date,TRUNC(receipt_date))
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id;
SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND EXISTS (select 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = NVL(vp_buyer,ph.agent_id)))
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
UNION
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND EXISTS (select 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
AND TRUNC(ph.creation_date) = nvl(vp_po_date,TRUNC(ph.creation_date))
AND ph.agent_id = NVL(vp_buyer,ph.agent_id)))
AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE shipment_header_id = rsh.shipment_header_id
AND session_id = vp_session_id);
SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND (EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = rt.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl,po_requisition_headers prh
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))
OR EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.line_location_id = rt.po_line_location_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))
UNION
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE rsh.vendor_id = vp_vendor_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fdct
WHERE fdct.shipment_header_id = rsh.shipment_header_id
AND fdct.session_id = vp_session_id)
AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND (EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = rt.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))
OR EXISTS (SELECT 1 FROM po_requisition_lines prl ,po_requisition_headers prh
WHERE prl.requisition_header_id = prh.requisition_header_id
AND prl.line_location_id = rt.po_line_location_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id ,prh.requisition_header_id)
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))));
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE vendor_id = vp_vendor_id
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))))
UNION
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE vendor_id = vp_vendor_id
AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE shipment_header_id = rsh.shipment_header_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt ,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions ia
WHERE ia.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoices ap
WHERE invoice_id = ia.invoice_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND TRUNC(ap.invoice_date) = nvl(vp_invoice_date,TRUNC(ap.invoice_date))
AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))));
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE vendor_id = vp_vendor_id
AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
AND EXISTS( SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL))))))
UNION
SELECT shipment_header_id FROM rcv_shipment_headers rsh
WHERE vendor_id = vp_vendor_id
AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
WHERE shipment_header_id = rsh.shipment_header_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
WHERE rt.po_header_id = ph.po_header_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND ph.vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = rt.po_line_location_id
AND EXISTS( SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.po_distribution_id= pd.po_distribution_id
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = aid.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL))))));
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
VALUES (rec_rec.shipment_header_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
VALUES (po_rec.shipment_header_id,vp_session_id);
DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
VALUES (req_rec.shipment_header_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
VALUES (inv_rec.shipment_header_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (shipment_header_id,session_id)
VALUES (pay_rec.shipment_header_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS (SELECT 1 FROM po_line_locations pll
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS(SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))))))
UNION
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE invoice_id= ai.invoice_id
AND session_id = vp_session_id)
AND (EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id =NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS (SELECT 1 FROM po_line_locations pll
WHERE rt.po_line_location_id = pll.line_location_id
AND EXISTS(SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = pll.line_location_id
AND EXISTS (SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))))));
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = pd.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))
UNION
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE invoice_id = ai.invoice_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = pd.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))));
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND (EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))
OR EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh
WHERE rsl.po_line_location_id = pd.line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))))
UNION
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE invoice_id = ai.invoice_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = ai.invoice_id
AND (EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))
OR EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM rcv_shipment_lines rsl,rcv_shipment_headers rsh
WHERE rsl.po_line_location_id = pd.line_location_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date))))));
SELECT invoice_id FROM ap_invoices
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND invoice_id = NVL(vp_invoice_id,invoice_id)
AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
AND invoice_amount = NVL(vp_invoice_amount,invoice_amount)
AND TRUNC(invoice_date) = NVL(TRUNC(vp_invoice_date), TRUNC(invoice_date)) ;
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS ( SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
))
UNION
SELECT invoice_id FROM ap_invoices ai
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE invoice_id = ai.invoice_id
AND session_id = vp_session_id)
AND EXISTS ( SELECT 1 FROM ap_invoice_payments aip
WHERE aip.invoice_id = ai.invoice_id
AND EXISTS (SELECT 1 FROM ap_checks ac
WHERE ac.check_id = aip.check_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND TRUNC(ac.check_date) = NVL(vp_check_date,TRUNC(ac.check_date))
AND ac.amount = nvl(vp_amount,ac.amount)
AND NVL(ac.treasury_pay_number,-1) = nvl(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1))
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
));
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
VALUES (inv_rec.invoice_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
VALUES (po_rec.invoice_id,vp_session_id);
DELETE from fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
VALUES (req_rec.invoice_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
VALUES (rec_rec.invoice_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (invoice_id,session_id)
VALUES (pay_rec.invoice_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = pll.line_location_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))))))))
UNION
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE check_id = ac.check_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS (SELECT 1 FROM po_line_locations pll
WHERE pll.line_location_id = rt.po_line_location_id
AND EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.line_location_id = pll.line_location_id
AND EXISTS(SELECT 1 FROM po_req_distributions prd
WHERE prd.distribution_id = pd.req_distribution_id
AND EXISTS (SELECT 1 FROM po_requisition_lines prl
WHERE prl.requisition_line_id = prd.requisition_line_id
AND EXISTS (SELECT 1 FROM po_requisition_headers prh
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id )
AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date)))))))))));
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = pd.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))))
UNION
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE check_id= ac.check_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND (EXISTS (SELECT 1 FROM po_distributions pd
WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = pd.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
OR EXISTS (SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_id = aid.rcv_transaction_id
AND EXISTS(SELECT 1 FROM po_headers ph
WHERE ph.po_header_id = rt.po_header_id
AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))));
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS( SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pd.line_location_id
AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date)))))))
UNION
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE check_id = ac.check_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.po_distribution_id = aid.po_distribution_id
AND EXISTS( SELECT 1 FROM rcv_transactions rt
WHERE rt.po_line_location_id = pd.line_location_id
AND EXISTS(SELECT 1 FROM rcv_shipment_headers rsh
WHERE rt.shipment_header_id = rsh.shipment_header_id
AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id )
AND TRUNC(rsh.creation_date) = NVL( vp_rec_date,TRUNC(rsh.creation_date)))))));
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_id = NVL(vp_invoice_id,ai.invoice_id )
AND ai.invoice_amount = NVL(vp_invoice_amount,ai.invoice_amount)
AND TRUNC(ai.invoice_date) = NVL( vp_invoice_date,TRUNC(ai.invoice_date)))))
UNION
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND EXISTS (SELECT 1 FROM fv_doc_cr_temp
WHERE check_id = ac.check_id
AND session_id = vp_session_id)
AND EXISTS (SELECT 1 FROM ap_invoice_payments aip
WHERE aip.check_id = ac.check_id
AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
WHERE aid.invoice_id = aip.invoice_id
AND EXISTS (SELECT 1 FROM ap_invoices ai
WHERE ai.invoice_id = aid.invoice_id
AND invoice_type_lookup_code = NVL(vp_invoice_type,invoice_type_lookup_code)
AND ai.invoice_id = NVL(vp_invoice_id,ai.invoice_id )
AND ai.invoice_amount = NVL(vp_invoice_amount,ai.invoice_amount)
AND TRUNC(ai.invoice_date) = NVL( TRUNC(vp_invoice_date),TRUNC(ai.invoice_date)))));
SELECT check_id FROM ap_checks ac
WHERE vendor_id = vp_vendor_id
AND vendor_site_id = vp_vendor_site_id
AND ac.check_id = NVL(vp_check_id,ac.check_id)
AND ac.check_date = NVL(vp_check_date,ac.check_date)
AND ac.amount = nvl(vp_amount,ac.amount)
AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date) OR vp_treasury_pay_date IS NULL)
AND NVL(ac.treasury_pay_number,-1) = NVL(vp_treasury_pay_number,NVL(ac.treasury_pay_number,-1));
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (check_id,session_id)
VALUES (pay_rec.check_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (check_id,session_id)
VALUES (po_rec.check_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (check_id,session_id)
VALUES (req_rec.check_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (check_id,session_id)
VALUES (rec_rec.check_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;
INSERT INTO fv_doc_cr_temp (check_id,session_id)
VALUES (inv_rec.check_id,vp_session_id);
DELETE FROM fv_doc_cr_temp WHERE session_id = vp_session_id;