Loading

Tuesday, March 9, 2010

Queries joining the main Purchase tables with their important columns listed


--For Blanket po  

   select poh.po_header_id 
         ,poh.segment1 po_number
         ,poh.type_lookup_code
         ,poh.vendor_id
         ,poh.vendor_site_id
         ,poh.currency_code
         ,poh.start_date
         ,poh.end_date
         ,poh.authorization_status
         ,pol.po_line_id
         ,pol.item_id
         ,pol.item_description
         ,pol.category_id
         ,pol.unit_price 
         ,por.po_release_id
         ,por.release_type
         ,por.release_num
         ,por.release_date
         ,poll.line_location_id
         ,poll.need_by_date
         ,poll.promised_date
         ,poll.taxable_flag
         ,poll.closed_code
         ,pod.po_distribution_id
         ,pod.quantity_ordered
         ,pod.destination_type_code
         ,pod.destination_organization_id
         ,pod.distribution_type
   FROM   po_headers_all poh
         ,po_lines_all   pol
         ,po_releases_all por
         ,po_line_locations_all poll
         ,po_distributions_all  pod
  WHERE  1 = 1
    AND  poh.po_header_id  = pol.po_header_id
    AND  poh.po_header_id  = por.po_header_id   
    AND  poll.po_header_id = poh.po_header_id
    AND  poll.po_line_id   = pol.po_line_id
    AND  poll.po_release_id= por.po_release_id
    AND  pod.po_line_id    = pol.po_line_id   
    AND  pod.po_release_id = por.po_release_id
    AND  poh.po_header_id  = &po_header_id
   

    --For Standard PO
    select poh.po_header_id
         ,poh.segment1 po_number
         ,poh.type_lookup_code
         ,poh.vendor_id
         ,poh.vendor_site_id
         ,poh.currency_code         
         ,poh.authorization_status
         ,pol.po_line_id
         ,pol.item_id
         ,pol.item_description
         ,pol.category_id
         ,pol.unit_price          
         ,poll.line_location_id
         ,poll.need_by_date
         ,poll.promised_date
         ,poll.taxable_flag
         ,poll.closed_code
         ,pod.po_distribution_id
         ,pod.quantity_ordered
         ,pod.destination_type_code
         ,pod.destination_organization_id
         ,pod.distribution_type
   FROM   po_headers_all poh
         ,po_lines_all   pol         
         ,po_line_locations_all poll
         ,po_distributions_all  pod
  WHERE  1 = 1
    AND  poh.po_header_id     = pol.po_header_id         
    AND  poll.po_header_id    = poh.po_header_id
    AND  poll.po_line_id      = pol.po_line_id    
    AND  pod.po_line_id       = pol.po_line_id   
    AND  pod.line_location_id = poll.line_location_id
    AND  pod.po_header_id     = poh.po_header_id
    AND  poh.po_header_id  = &Po_header_id;

For invoice related information please have a look into below link


0 Responses to “Queries joining the main Purchase tables with their important columns listed”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.