[Home] [Help]
3: g_module_name VARCHAR2(100) := 'fv.plsql.fv_cross_doc_ref.';
4:
5: vp_vendor_id po_vendors.vendor_id%TYPE;
6: vp_vendor_site_id po_vendor_sites.vendor_site_id%TYPE;
7: vp_po_header_id po_headers.po_header_id%TYPE;
8: vp_po_date po_headers.creation_date%TYPE;
9: vp_requisition_header_id po_requisition_headers.requisition_header_id%TYPE;
10: vp_requisition_line_id po_requisition_lines.requisition_line_id%TYPE;
11: vp_req_date po_requisition_headers.creation_date%TYPE;
4:
5: vp_vendor_id po_vendors.vendor_id%TYPE;
6: vp_vendor_site_id po_vendor_sites.vendor_site_id%TYPE;
7: vp_po_header_id po_headers.po_header_id%TYPE;
8: vp_po_date po_headers.creation_date%TYPE;
9: vp_requisition_header_id po_requisition_headers.requisition_header_id%TYPE;
10: vp_requisition_line_id po_requisition_lines.requisition_line_id%TYPE;
11: vp_req_date po_requisition_headers.creation_date%TYPE;
12: vp_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
10: vp_requisition_line_id po_requisition_lines.requisition_line_id%TYPE;
11: vp_req_date po_requisition_headers.creation_date%TYPE;
12: vp_shipment_header_id rcv_shipment_headers.shipment_header_id%TYPE;
13: vp_rec_date rcv_shipment_headers.creation_date%TYPE;
14: vp_buyer po_headers.agent_id%TYPE;
15: vp_invoice_id ap_invoices.invoice_id%TYPE;
16: vp_invoice_date ap_invoices.invoice_date%TYPE;
17: vp_invoice_amount ap_invoices.invoice_amount%TYPE;
18: vp_invoice_type ap_invoices.invoice_type_lookup_code%TYPE;
40: PROCEDURE main
41: (
42: p_vendor_id IN po_vendors.vendor_id%TYPE ,
43: p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE ,
44: p_po_header_id IN po_headers.po_header_id%TYPE,
45: p_po_date IN po_headers.creation_date%TYPE,
46: p_requisition_header_id IN po_requisition_headers.requisition_header_id%TYPE,
47: p_requisition_line_id IN po_requisition_lines.requisition_line_id%TYPE,
48: p_req_date IN po_requisition_headers.creation_date%TYPE,
41: (
42: p_vendor_id IN po_vendors.vendor_id%TYPE ,
43: p_vendor_site_id IN po_vendor_sites.vendor_site_id%TYPE ,
44: p_po_header_id IN po_headers.po_header_id%TYPE,
45: p_po_date IN po_headers.creation_date%TYPE,
46: p_requisition_header_id IN po_requisition_headers.requisition_header_id%TYPE,
47: p_requisition_line_id IN po_requisition_lines.requisition_line_id%TYPE,
48: p_req_date IN po_requisition_headers.creation_date%TYPE,
49: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
47: p_requisition_line_id IN po_requisition_lines.requisition_line_id%TYPE,
48: p_req_date IN po_requisition_headers.creation_date%TYPE,
49: p_shipment_header_id IN rcv_shipment_headers.shipment_header_id%TYPE,
50: p_receipt_date IN rcv_shipment_headers.creation_date%TYPE,
51: p_buyer IN po_headers.agent_id%TYPE,
52: p_invoice_id IN ap_invoices.invoice_id%TYPE,
53: p_invoice_date IN ap_invoices.invoice_date%TYPE ,
54: p_invoice_amount IN ap_invoices.invoice_amount%TYPE ,
55: p_invoice_type IN ap_invoices.invoice_type_lookup_code%TYPE ,
119: l_errbuf VARCHAR2(1024);
120:
121: CURSOR po_cur IS
122: SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
123: FROM po_headers ph,po_lines pl,po_line_locations pll
124: WHERE ph.po_header_id = pl.po_header_id
125: AND pl.po_line_id = pll.po_line_id
126: AND vendor_id = vp_vendor_id
127: AND vendor_site_id = vp_vendor_site_id
130: AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
131: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
132: UNION
133: SELECT ph.po_header_id,pl.po_line_id,pll.line_location_id
134: FROM po_headers ph,po_lines pl,po_line_locations pll
135: WHERE ph.po_header_id = pl.po_header_id
136: AND pl.po_line_id = pll.po_line_id
137: AND vendor_id = vp_vendor_id
138: AND vendor_site_id = vp_vendor_site_id
144: po_rec po_cur%ROWTYPE;
145:
146: CURSOR req_cur IS
147: SELECT ph.po_header_id,pl.po_line_id, pll.line_location_id
148: FROM po_headers ph,po_lines pl,po_line_locations pll
149: WHERE ph.po_header_id = pl.po_header_id
150: AND pl.po_line_id = pll.po_line_id
151: AND EXISTS(SELECT prh.requisition_header_id,prl.requisition_line_id
152: FROM po_requisition_headers prh,po_requisition_lines prl
165: WHERE prl.requisition_header_id = prh.requisition_header_id
166: AND authorization_status = 'APPROVED'
167: AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
168: AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
169: po_line_locations plx,po_headers ph
170: WHERE plx.line_location_id = rh.line_location_id
171: AND plx.po_header_id = ph.po_header_id
172: AND ph.vendor_id = vp_vendor_id
173: AND rh.line_location_id = pll.line_location_id
181: req_rec req_cur%ROWTYPE;
182:
183: CURSOR rec_cur IS
184: SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
185: FROM po_headers ph,po_line_locations pll
186: WHERE ph.po_header_id = pll.po_header_id
187: AND vendor_id = vp_vendor_id
188: AND vendor_site_id = vp_vendor_site_id
189: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
196: AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
197: AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))))
198: UNION
199: SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id
200: FROM po_headers ph,po_line_locations pll
201: WHERE ph.po_header_id = pll.po_header_id
202: AND vendor_id = vp_vendor_id
203: AND vendor_site_id = vp_vendor_site_id
204: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
216: AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date))));
217:
218: rec_rec rec_cur%ROWTYPE;
219: CURSOR inv_cur IS
220: SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id FROM po_headers ph,po_line_locations pll
221: WHERE ph.po_header_id = pll.po_header_id
222: AND vendor_id = vp_vendor_id
223: AND vendor_site_id = vp_vendor_site_id
224: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
244: AND TRUNC(ap.invoice_date) = NVL(vp_invoice_date,TRUNC(ap.invoice_date))
245: AND invoice_type_lookup_code = nvl(vp_invoice_type,invoice_type_lookup_code)
246: AND invoice_amount = nvl(vp_invoice_amount,invoice_amount)))))
247: UNION
248: SELECT ph.po_header_id,pll.po_line_id ,pll.line_location_id FROM po_headers ph,po_line_locations pll
249: WHERE vendor_id = vp_vendor_id
250: AND ph.po_header_id = pll.po_header_id
251: AND vendor_site_id = vp_vendor_site_id
252: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
280: inv_rec inv_cur%ROWTYPE;
281:
282: CURSOR pay_cur IS
283: SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
284: FROM po_headers ph,po_line_locations pll
285: WHERE ph.po_header_id = pll.po_header_id
286: AND vendor_id = vp_vendor_id
287: AND vendor_site_id = vp_vendor_site_id
288: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp
318: AND (TRUNC(ac.treasury_pay_date) = TRUNC(vp_treasury_pay_date)
319: OR vp_treasury_pay_date IS NULL))))))
320: UNION
321: SELECT ph.po_header_id,pll.po_line_id,pll.line_location_id
322: FROM po_headers ph,po_line_locations pll
323: WHERE ph.po_header_id = pll.po_header_id
324: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fst
325: WHERE fst.po_header_id = ph.po_header_id
326: AND fst.po_line_id = pll.po_line_id
466: l_errbuf VARCHAR2(1024);
467:
468: CURSOR po_cur IS
469: SELECT prh.requisition_header_id ,prl.requisition_line_id
470: FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
471: WHERE prh.requisition_header_id =prl.requisition_header_id
472: AND pll.line_location_id = prl.line_location_id
473: AND ph.po_header_id = pll.po_header_id
474: AND ph.vendor_id = vp_vendor_id
478: AND ph.agent_id = nvl(vp_buyer,ph.agent_id)
479: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
480: UNION
481: SELECT prh.requisition_header_id ,prl.requisition_line_id
482: FROM po_requisition_headers prh,po_requisition_lines prl,po_line_locations pll,po_headers ph
483: WHERE prh.requisition_header_id = prl.requisition_header_id
484: AND pll.line_location_id = prl.line_location_id
485: AND ph.po_header_id = pll.po_header_id
486: AND ph.vendor_id = vp_vendor_id
513: WHERE prl.requisition_header_id = prh.requisition_header_id
514: AND prh.requisition_header_id = NVL(vp_requisition_header_id,prh.requisition_header_id)
515: AND authorization_status = 'APPROVED'
516: AND TRUNC(prh.creation_date) = NVL(vp_req_date,TRUNC(prh.creation_date))) rh,
517: po_line_locations plx,po_headers ph
518: WHERE plx.line_location_id = rh.line_location_id
519: AND plx.po_header_id = ph.po_header_id
520: AND ph.vendor_id = vp_vendor_id
521: AND ph.vendor_site_id = vp_vendor_site_id
531: SELECT requisition_header_id,prl.requisition_line_id FROM po_requisition_lines prl
532: WHERE NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
533: AND (EXISTS (SELECT 1 FROM po_req_distributions prd
534: WHERE prd.requisition_line_id = prl.requisition_line_id
535: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
536: WHERE rt.po_header_id = ph.po_header_id
537: AND rt.req_distribution_id = prd.distribution_id
538: AND ph.vendor_site_id = vp_vendor_site_id
539: AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
542: AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
543: AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
544: OR EXISTS (SELECT 1 FROM po_distributions pd
545: WHERE pd.line_location_id = prl.line_location_id
546: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
547: WHERE rt.po_header_id = ph.po_header_id
548: AND rt.po_line_location_id = pd.line_location_id
549: AND ph.vendor_site_id = vp_vendor_site_id
550: AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
560: AND requisition_line_id = prl.requisition_line_id
561: AND session_id = vp_session_id)
562: AND (EXISTS (SELECT 1 FROM po_req_distributions prd
563: WHERE prd.requisition_line_id = prl.requisition_line_id
564: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
565: WHERE rt.po_header_id = ph.po_header_id
566: AND rt.req_distribution_id = prd.distribution_id
567: AND ph.vendor_site_id = vp_vendor_site_id
568: AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
571: AND rsh.shipment_header_id = NVL(vp_shipment_header_id,rsh.shipment_header_id)
572: AND TRUNC(rsh.creation_date) = NVL(vp_rec_date,TRUNC(rsh.creation_date)))))
573: OR EXISTS (SELECT 1 FROM po_distributions pd
574: WHERE pd.line_location_id = prl.line_location_id
575: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
576: WHERE rt.po_header_id = ph.po_header_id
577: AND rt.po_line_location_id = pd.line_location_id
578: AND ph.vendor_site_id = vp_vendor_site_id
579: AND EXISTS (SELECT 1 FROM rcv_shipment_headers rsh
846: CURSOR po_cur IS
847: SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
848: WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
849: WHERE rt.shipment_header_id = rsh.shipment_header_id
850: AND EXISTS (select 1 FROM po_headers ph
851: WHERE ph.po_header_id = rt.po_header_id
852: AND vendor_id = vp_vendor_id
853: AND vendor_site_id = vp_vendor_site_id
854: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
858: UNION
859: SELECT shipment_header_id FROM rcv_shipment_headers rsh
860: WHERE EXISTS (SELECT 1 FROM rcv_transactions rt
861: WHERE rt.shipment_header_id = rsh.shipment_header_id
862: AND EXISTS (select 1 FROM po_headers ph
863: WHERE ph.po_header_id = rt.po_header_id
864: AND vendor_id = vp_vendor_id
865: AND vendor_site_id = vp_vendor_site_id
866: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id)
873: CURSOR req_cur IS
874: SELECT rsh.shipment_header_id FROM rcv_shipment_headers rsh
875: WHERE rsh.vendor_id = vp_vendor_id
876: AND NOT EXISTS (SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
877: AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
878: WHERE ph.po_header_id = rt.po_header_id
879: AND rt.shipment_header_id = rsh.shipment_header_id
880: AND ph.vendor_site_id = vp_vendor_site_id
881: AND (EXISTS (SELECT 1 FROM po_req_distributions prd
896: WHERE rsh.vendor_id = vp_vendor_id
897: AND EXISTS (SELECT 1 FROM fv_doc_cr_temp fdct
898: WHERE fdct.shipment_header_id = rsh.shipment_header_id
899: AND fdct.session_id = vp_session_id)
900: AND EXISTS(SELECT 1 FROM rcv_transactions rt,po_headers ph
901: WHERE rt.po_header_id = ph.po_header_id
902: AND rt.shipment_header_id = rsh.shipment_header_id
903: AND ph.vendor_site_id = vp_vendor_site_id
904: AND (EXISTS (SELECT 1 FROM po_req_distributions prd
921: CURSOR inv_cur IS
922: SELECT shipment_header_id FROM rcv_shipment_headers rsh
923: WHERE vendor_id = vp_vendor_id
924: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
925: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
926: WHERE ph.po_header_id = rt.po_header_id
927: AND rt.shipment_header_id = rsh.shipment_header_id
928: AND ph.vendor_site_id = vp_vendor_site_id
929: AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
940: WHERE vendor_id = vp_vendor_id
941: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
942: WHERE shipment_header_id = rsh.shipment_header_id
943: AND session_id = vp_session_id)
944: AND EXISTS (SELECT 1 FROM rcv_transactions rt ,po_headers ph
945: WHERE rt.po_header_id = ph.po_header_id
946: AND rt.shipment_header_id = rsh.shipment_header_id
947: AND ph.vendor_site_id = vp_vendor_site_id
948: AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
959: CURSOR pay_cur IS
960: SELECT shipment_header_id FROM rcv_shipment_headers rsh
961: WHERE vendor_id = vp_vendor_id
962: AND NOT EXISTS(SELECT 1 FROM fv_doc_cr_temp WHERE session_id = vp_session_id)
963: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
964: WHERE rt.po_header_id = ph.po_header_id
965: AND rt.shipment_header_id = rsh.shipment_header_id
966: AND ph.vendor_site_id = vp_vendor_site_id
967: AND EXISTS (SELECT 1 FROM po_distributions pd WHERE pd.line_location_id = rt.po_line_location_id
981: WHERE vendor_id = vp_vendor_id
982: AND EXISTS(SELECT 1 FROM fv_doc_cr_temp
983: WHERE shipment_header_id = rsh.shipment_header_id
984: AND session_id = vp_session_id)
985: AND EXISTS (SELECT 1 FROM rcv_transactions rt,po_headers ph
986: WHERE rt.po_header_id = ph.po_header_id
987: AND rt.shipment_header_id = rsh.shipment_header_id
988: AND ph.vendor_site_id = vp_vendor_site_id
989: AND EXISTS (SELECT 1 FROM po_distributions pd
1177: AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1178: WHERE aid.invoice_id = ai.invoice_id
1179: AND EXISTS (SELECT 1 FROM po_distributions pd
1180: WHERE pd.po_distribution_id = aid.po_distribution_id
1181: AND EXISTS(SELECT 1 FROM po_headers ph
1182: WHERE ph.po_header_id = pd.po_header_id
1183: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1184: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1185: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1183: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1184: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1185: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1186: WHERE rt.transaction_id = aid.rcv_transaction_id
1187: AND EXISTS(SELECT 1 FROM po_headers ph
1188: WHERE ph.po_header_id = rt.po_header_id
1189: AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
1190: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))
1191: UNION
1198: AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1199: WHERE aid.invoice_id = ai.invoice_id
1200: AND EXISTS (SELECT 1 FROM po_distributions pd
1201: WHERE pd.po_distribution_id = aid.po_distribution_id
1202: AND EXISTS(SELECT 1 FROM po_headers ph
1203: WHERE ph.po_header_id = pd.po_header_id
1204: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1205: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1206: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1204: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1205: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1206: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1207: WHERE rt.transaction_id = aid.rcv_transaction_id
1208: AND EXISTS(SELECT 1 FROM po_headers ph
1209: WHERE ph.po_header_id = rt.po_header_id
1210: AND ph.po_header_id = NVL(vp_po_header_id,rt.po_header_id )
1211: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))));
1212:
1486: AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1487: WHERE aid.invoice_id = aip.invoice_id
1488: AND (EXISTS (SELECT 1 FROM po_distributions pd
1489: WHERE pd.po_distribution_id = aid.po_distribution_id
1490: AND EXISTS(SELECT 1 FROM po_headers ph
1491: WHERE ph.po_header_id = pd.po_header_id
1492: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1493: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1494: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1492: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1493: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1494: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1495: WHERE rt.transaction_id = aid.rcv_transaction_id
1496: AND EXISTS(SELECT 1 FROM po_headers ph
1497: WHERE ph.po_header_id = rt.po_header_id
1498: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1499: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))))
1500:
1510: AND EXISTS (SELECT 1 FROM ap_invoice_distributions aid
1511: WHERE aid.invoice_id = aip.invoice_id
1512: AND (EXISTS (SELECT 1 FROM po_distributions pd
1513: WHERE pd.po_distribution_id = aid.po_distribution_id
1514: AND EXISTS(SELECT 1 FROM po_headers ph
1515: WHERE ph.po_header_id = pd.po_header_id
1516: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1517: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1518: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1516: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1517: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date))))
1518: OR EXISTS (SELECT 1 FROM rcv_transactions rt
1519: WHERE rt.transaction_id = aid.rcv_transaction_id
1520: AND EXISTS(SELECT 1 FROM po_headers ph
1521: WHERE ph.po_header_id = rt.po_header_id
1522: AND ph.po_header_id = NVL(vp_po_header_id,ph.po_header_id )
1523: AND TRUNC(ph.creation_date) = NVL( vp_po_date,TRUNC(ph.creation_date)))))));
1524: