Includes two scripts to fetch all the transactions information related with in a procure to pay cycle.
\nTwo scripts are provided to use one with receipts and other when receipts are not created.<\/p>\n
Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL Transfer flag e.t.c<\/p>\n
–Procure to Pay query without receipts — PROCURE TO PAY CYCLE QUERY WITH RECEIPTS<\/p>\n Includes two scripts to fetch all the transactions information related with in a procure to pay cycle. Two scripts are provided to use one with receipts and other when receipts are not created. Few important fields that were included in the script are Requisition Number, Purchase Order Number, Invoice Number, Customer Number, Invoice Amount, GL […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[],"class_list":["post-1854","post","type-post","status-publish","format-standard","hentry","category-scripts"],"yoast_head":"\n
\n
\nselect distinct
\n reqh.segment1 REQ_NUM,
\n reqh.AUTHORIZATION_STATUS REQ_STATUS, -- poh.po_header_id,
\n poh.segment1 PO_NUM,
\n pol.line_num,
\n poh.AUTHORIZATION_STATUS PO_STATUS,
\n -- i.invoice_id,
\n i.invoice_num,
\n i.invoice_amount,
\n i.amount_paid,
\n i.vendor_id,
\n -- v.vendor_name,
\n -- p.check_id,
\n c.check_number,
\n h.gl_transfer_flag,
\n h.period_name
\n from ap_invoices_all i,
\n ap_invoice_distributions_all invd,
\n po_headers_all poh,
\n po_lines_all pol,
\n po_distributions_all pod,
\n po_vendors v,
\n po_requisition_headers_all reqh,
\n po_requisition_lines_all reql,
\n po_req_distributions_all reqd,
\n ap_invoice_payments_all p,
\n ap_checks_all c,
\n ap_ae_headers_all h,
\n ap_ae_lines_all l
\n where 1=1
\n and i.vendor_id = v.vendor_id
\n and c.check_id = p.check_id
\n and p.invoice_id = i.invoice_id
\n and poh.PO_HEADER_ID = pol.PO_HEADER_ID
\n and reqh.REQUISITION_HEADER_ID = reql.REQUISITION_HEADER_ID
\n and reqd.REQUISITION_LINE_ID = reql.REQUISITION_LINE_ID
\n and pod.REQ_DISTRIBUTION_ID = reqd.DISTRIBUTION_ID
\n and pod.PO_HEADER_ID = poh.PO_HEADER_ID
\n and pod.PO_DISTRIBUTION_ID = invd.PO_DISTRIBUTION_ID
\n and invd.INVOICE_ID = i.INVOICE_ID
\n and h.ae_header_id = l.ae_header_id
\n and l.SOURCE_TABLE = 'AP_INVOICES'
\n AND l.SOURCE_ID = i.invoice_id
\n --and poh.segment1 = 4033816 -- PO NUMBER
\n and reqh.segment1 = '501' -- REQ NUMBER
\n --and i.invoice_num = 3114 -- INVOICE NUMBER
\n --and c.check_number = -- CHECK NUMBER
\n --and vendor_id = -- VENDOR ID<\/code><\/p>\n
\nSELECT DISTINCT reqh.segment1 req_num, reqh.authorization_status req_status,
\n -- POH.PO_HEADER_ID,
\n poh.segment1 po_num, pol.line_num,
\n poh.authorization_status po_status, rcvh.receipt_num,
\n rcv.inspection_status_code,
\n -- I.INVOICE_ID,
\n i.invoice_num, i.invoice_amount,
\n i.amount_paid, i.vendor_id,
\n -- V.VENDOR_NAME,
\n -- P.CHECK_ID,
\n c.check_number, h.gl_transfer_flag,
\n h.period_name
\n FROM ap_invoices_all i,
\n ap_invoice_distributions_all invd,
\n po_headers_all poh,
\n po_lines_all pol,
\n po_distributions_all pod,
\n po_vendors v,
\n po_requisition_headers_all reqh,
\n po_requisition_lines_all reql,
\n po_req_distributions_all reqd,
\n rcv_transactions rcv,
\n rcv_shipment_headers rcvh,
\n rcv_shipment_lines rcvl,
\n ap_invoice_payments_all p,
\n ap_checks_all c,
\n ap_ae_headers_all h,
\n ap_ae_lines_all l
\n WHERE 1 = 1
\n AND i.vendor_id = v.vendor_id
\n AND c.check_id = p.check_id
\n AND p.invoice_id = i.invoice_id
\n AND poh.po_header_id = pol.po_header_id
\n AND reqh.requisition_header_id = reql.requisition_header_id
\n AND reqd.requisition_line_id = reql.requisition_line_id
\n AND pod.req_distribution_id = reqd.distribution_id
\n AND pod.po_header_id = poh.po_header_id
\n --AND POH.PO_HEADER_ID = RCV.PO_HEADER_ID
\n AND rcvh.shipment_header_id = rcv.shipment_header_id(+)
\n --AND RCVH.SHIPMENT_HEADER_ID = RCVL.SHIPMENT_HEADER_ID
\n --AND RCV.TRANSACTION_TYPE = 'RECEIVE'
\n --AND RCV.SOURCE_DOCUMENT_CODE = 'PO'
\n --AND POL.PO_LINE_ID = RCV.PO_LINE_ID
\n --AND POD.PO_DISTRIBUTION_ID = RCV.PO_DISTRIBUTION_ID
\n AND pod.po_distribution_id = invd.po_distribution_id
\n AND invd.invoice_id = i.invoice_id
\n AND h.ae_header_id = l.ae_header_id
\n AND l.source_table = 'AP_INVOICES'
\n AND l.source_id = i.invoice_id
\n --AND POH.SEGMENT1 = 36420 -- PO NUMBER
\n AND reqh.segment1 = '501' -- REQ NUMBER
\n --AND I.INVOICE_NUM = 3114 -- INVOICE NUMBER
\n --AND C.CHECK_NUMBER = -- CHECK NUMBER
\n --AND VENDOR_ID = -- VENDOR ID
\n --AND RECEIPT_NUM = 692237<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"