1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to display PO Details which don't have Invoices in Oracle apps R12

Discussion in 'Oracle Apps Technical' started by jagadekara, May 28, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    The following Query gives you the PO Details in R12 which have receipts and which don't have Invoices.

    Code (SQL):
    SELECT asp.vendor_name
          ,pha.segment1
          ,pha.creation_date po_date
          ,pha.type_lookup_code
          ,SUM(pla.unit_price* pla.quantity) po_amount
      FROM po_distributions_all pda
          ,po_headers_all pha
          ,rcv_shipment_lines rsl
          ,ap_suppliers asp
          ,po_lines_all pla
     WHERE 1=1
       AND pda.po_header_id=pha.po_header_id
       AND pda.po_distribution_id NOT IN
          (SELECT po_distribution_id FROM po_distributions_all pda
           WHERE po_distribution_id IN (SELECT DISTINCT  po_distribution_id FROM ap_invoice_distributions_all))
       AND rsl.po_header_id=pha.po_header_id  
       AND asp.vendor_id=pha.vendor_id
       AND pha.po_header_id=pla.po_header_id
       AND pla.po_line_id=pda.po_line_id
    GROUP BY asp.vendor_name,pha.segment1,pha.creation_date,pha.type_lookup_code
       ;
     
  2. oracleappstraining18

    oracleappstraining18 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    informative and useful
     
  3. oracleappstraining18

    oracleappstraining18 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    Please update more
     
  4. oracleappstraining18

    oracleappstraining18 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Bangalore
    anything more with respect to PO is welcome
     
  5. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    I believe we need to exclude cancelled PO lines for correct 'PO Amount'.

    Also usage of NOT EXISTS for checking if po_distribution_id is present in AP_INVOICE_DISTRIBUTIONS_ALL will increase the performance.

    Thanks,
    Yowan Cristo