Query to get cancelled PO in Oracle Apps

Query to get cancelled PO in Oracle Apps

Hi friends, We are going to discuss about sql query to get cancelled PO in Oracle Apps. This SQL Query will help to extract the details of Cancelled PO in Oracle apps. If we want to extract the details of all cancelled PO in Oracle apps for particular BU then we can refer this sql query. When we do cancel any Purchase order in Oracle apps from application , Then its status do store in the PO table column (CLOSED_CODE). We can refer this column in the PO_HEADERS_ALL tables to find out the Cancelled PO in oracle apps. One more important Point related to cancellation of PO. In Oracle Apps , we do cancel the Purchase order in two levels. We can cancel the entire purchase order or we can also cancel the particular PO line. Once the PO got cancelled or PO line got cancelled then that PO and PO line cannot be eligible for the receiving. This is one of the Important SQL Query to find out the details of Cancelled PO in Oracle Apps. Please find below the complete details about Query to get cancelled PO in Oracle Apps.

Query to get cancelled PO in Oracle Apps

4 Important Tables Used by Query to get cancelled PO in Oracle Apps

1.PO_LINE_LOCATIONS_ALL

2.PO_DISTRIBUTIONS_ALL

3.PO_HEADERS_ALL

4.PO_LINES_ALL

Detail SQL Query get cancelled PO in Oracle Apps

SELECT
 ,poh.segment1 "PO NUM"
 ,poh.authorization_status "STATUS"
 ,pla.line_num "SEQ NUM"
 ,plla.line_location_id
 ,d.po_distribution_id
 ,poh.type_lookup_code "TYPE"
 FROM

 po.po_line_locations_all plla
 ,po.po_distributions_all d
 ,po.po_headers_all poh
 ,po.po_lines_all pla
 WHERE
 poh.po_header_id = pla.po_header_id

 AND nvl(poh.closed_code,'OPEN') in ('CLOSED','FINALLY CLOSED')
  and poh.closed_date is NULL
 and plla.po_line_id = pla.po_Line_id
 and plla.line_location_id = d.line_location_id

Query to get cancelled PO in Oracle Apps