Search This Blog

Saturday, November 20, 2010

List of POs - Audit Query

/* Formatted on 2009/06/01 13:03 (Formatter Plus v4.8.8) */
/* Formatted on 2009/06/01 14:39 (Formatter Plus v4.8.8) */

begin
mo_global.set_policy_context('S',81);
end;
select distinct segment1 from po_headers_all where  po_headers_all.creation_date BETWEEN TO_DATE ('01-APR-2008')
                                             AND TO_DATE ('30-APR-2009') --order by to_number(segment1)        
minus
                                 
SELECT  distinct  po_headers_all.segment1 AS "PO #",
        po_headers_all.type_lookup_code AS "PO Type",
         TO_CHAR (po_headers_all.creation_date, 'DD-Mon-YYYY') AS "PO Date",
         TO_CHAR (po_headers_all.creation_date, 'Month') AS "Month",
         NVL (po_headers_all.authorization_status, 'Incomplete') status,
         prh.segment1 "PR No", to_char(prh.creation_date,'DD-Mon-YYYY') "PR Date",
         hr_employees_current_v.full_name AS "Buyer Name",
         po_headers_all.agent_id AS "Buyer No", po_lines_all.line_num AS line,
         mcb.segment1 || '.' || mcb.segment2 "PO Item",
         po_lines_all.item_description AS description,
         po_lines_all.quantity AS "Order Quantity",
         po_lines_all.quantity * po_lines_all.unit_price AS "Line Total",
         NVL (pll.shipment_status, 'Open') "Receipt Status",
            gcc1.segment1
         || '-'
         || gcc1.segment2
         || '-'
         || gcc1.segment3
         || '-'
         || gcc1.segment4
         || '-'
         || gcc1.segment5
         || '-'
         || gcc1.segment6 "GL Debit Account",
            gcc2.segment1
         || '-'
         || gcc2.segment2
         || '-'
         || gcc2.segment3
         || '-'
         || gcc2.segment4
         || '-'
         || gcc2.segment5
         || '-'
         || gcc2.segment6 "Supplier Credit Account",
         aia.payment_status_flag,
         (SELECT DECODE (x.match_status_flag,
                         'A', 'Approved'
                        )
            FROM ap.ap_invoice_distributions_all x
           WHERE x.invoice_distribution_id = aid.invoice_distribution_id)
                                                          "Invoice Approved?",
         aia.amount_paid, apt.NAME "Payment Term", rsh.receipt_num "GRN Num",
         to_char(TRUNC (rct.transaction_date),'DD-Mon-YYYY') "GRN Date"  
    FROM po_vendors,
         po_headers_all,
         po_lines_all,
         po_distributions_all,
         hr_employees_current_v,
         mtl_categories_b mcb,
         po_requisition_headers_all prh,
         po_requisition_lines_all prn,
         po_req_distributions_all prd,
         po_line_locations_v pll,
         gl_code_combinations gcc1,
         gl_code_combinations gcc2,
         ap_invoice_distributions_all aid,
         ap_invoices_all aia,
         ap_invoice_payments_all aipa,
         ap_terms apt,
         rcv_transactions rct,
         rcv_shipment_headers rsh,
         rcv_shipment_lines rsl
   WHERE po_vendors.vendor_id = po_headers_all.vendor_id
     AND po_headers_all.po_header_id = po_lines_all.po_header_id
     AND po_lines_all.po_line_id = po_distributions_all.po_line_id
     AND po_headers_all.agent_id = hr_employees_current_v.employee_id
      --  AND po_headers_all.creation_date BETWEEN TO_DATE ('01-NOV-2008')
       --                                      AND TO_DATE ('30-APR-2009')
     AND mcb.category_id = po_lines_all.category_id
    -- AND po_headers_all.segment1 = 749
     AND prh.requisition_header_id = prn.requisition_header_id
     AND prn.requisition_line_id = prd.requisition_line_id
     AND prd.distribution_id = po_distributions_all.req_distribution_id
     AND pll.po_header_id = po_lines_all.po_header_id
     AND pll.po_line_id = po_lines_all.po_line_id
     AND gcc1.code_combination_id = po_distributions_all.code_combination_id
     AND aid.po_distribution_id(+) = po_distributions_all.po_distribution_id
     AND gcc2.code_combination_id(+) = aia.accts_pay_code_combination_id
     AND aia.invoice_id(+) = aid.invoice_id
     AND aipa.invoice_id(+) = aia.invoice_id
     AND apt.term_id(+) = po_headers_all.terms_id
     AND rct.shipment_line_id = rsl.shipment_line_id(+)
     AND rct.shipment_header_id = rsh.shipment_header_id(+)
     AND rct.po_header_id(+) = po_headers_all.po_header_id
     AND rct.transaction_type(+) = 'DELIVER'
     AND po_lines_all.item_description LIKE '%Sandip%'
GROUP BY po_headers_all.segment1,
         po_lines_all.line_num,
         po_vendors.vendor_name,
         po_headers_all.agent_id,
         hr_employees_current_v.full_name,
         po_headers_all.creation_date,
         po_lines_all.closed_code,
         po_headers_all.type_lookup_code,
         po_lines_all.item_description,
         po_lines_all.unit_meas_lookup_code,
         po_lines_all.quantity,
         po_lines_all.unit_price,
         mcb.segment1,
         mcb.segment2,
         prh.segment1,
         po_headers_all.authorization_status,
         prh.creation_date,
         pll.shipment_status,
            gcc1.segment1
         || '-'
         || gcc1.segment2
         || '-'
         || gcc1.segment3
         || '-'
         || gcc1.segment4
         || '-'
         || gcc1.segment5
         || '-'
         || gcc1.segment6,
            gcc2.segment1
         || '-'
         || gcc2.segment2
         || '-'
         || gcc2.segment3
         || '-'
         || gcc2.segment4
         || '-'
         || gcc2.segment5
         || '-'
         || gcc2.segment6,
        aid.dist_code_combination_id,
         aia.payment_status_flag,
         aid.invoice_distribution_id,
         aia.amount_paid,
         apt.NAME,
         rsh.receipt_num,
         TRUNC (rct.transaction_date)                              
ORDER BY --po_headers_all.creation_date,
         to_number(po_headers_all.segment1),
         po_lines_all.line_num
        
        
select distinct segment1 from po_headers_all        

3 comments:

  1. It's very simple to find out any matter on net as compared to textbooks, as I found this post at this website.
    Also visit my site ; visit the up coming website

    ReplyDelete
  2. Terriftic work! This is the type of info that are meant to bbe shared around the internet.

    Shame on the seek engines for no longer positioning this publish upper!
    Come on ovver and talk ver with my website . Thanks =)

    Also viosit mmy weblog - weight loss menu plan

    ReplyDelete