Thursday, December 13, 2012

Procure to Pay cycle query with out Receipt



select distinct
           reqh.segment1 REQ_NUM,
           reqh.AUTHORIZATION_STATUS REQ_STATUS,
           poh.segment1 PO_NUM,
           pol.line_num,
           poh.AUTHORIZATION_STATUS PO_STATUS,
           i.invoice_num,
           i.invoice_amount,
           i.amount_paid,
            i.vendor_id,   
           c.check_number,
           h.gl_transfer_flag,
           h.period_name 
    from ap_invoices_all i,
         ap_invoice_distributions_all invd,
         po_headers_all poh,
         po_lines_all pol,
         po_distributions_all pod,
         po_vendors v,
         po_requisition_headers_all reqh,
         po_requisition_lines_all reql,
         po_req_distributions_all reqd,    
         ap_invoice_payments_all p,
         ap_checks_all c,
         ap_ae_headers_all h,
         ap_ae_lines_all l
    where 1=1    
    and i.vendor_id = v.vendor_id
    and c.check_id = p.check_id
    and p.invoice_id = i.invoice_id
    and poh.PO_HEADER_ID = pol.PO_HEADER_ID
    and reqh.REQUISITION_HEADER_ID = reql.REQUISITION_HEADER_ID
    and reqd.REQUISITION_LINE_ID = reql.REQUISITION_LINE_ID
    and pod.REQ_DISTRIBUTION_ID = reqd.DISTRIBUTION_ID
    and pod.PO_HEADER_ID = poh.PO_HEADER_ID
    and pod.PO_DISTRIBUTION_ID = invd.PO_DISTRIBUTION_ID
    and invd.INVOICE_ID = i.INVOICE_ID
    and h.ae_header_id = l.ae_header_id
    and l.SOURCE_TABLE = 'AP_INVOICES'
    AND l.SOURCE_ID = i.invoice_id
    and reqh.segment1 = '123456'   -- REQ NUMBER

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect