PO details reports Using xml element tag-

PO details reports Using xml element tag-:

Please find the below steps for PO details reports Using xml element tag:

CREATE OR REPLACE PACKAGE BODY po_details_test_pkg
as
procedure po_details_insert( v_errbuf        OUT NOCOPY VARCHAR2
                                 ,v_retcode       OUT NOCOPY NUMBER)
IS
    begin
INSERT INTO PO_DETAILS_RPT SELECT pha.segment1 "PO Number",
       pha.creation_date "PO Date",
       (select ap.segment1 from apps.ap_suppliers ap where ap.vendor_id = pha.vendor_id) "Vendor Number",       
       plla.need_by_date "Required Date",
       (select hl.location_code from apps.hr_locations_all hl 
       where hl.location_id = pha.ship_to_location_id) "Ship To",
       pha.comments "Description"               
  FROM po_headers_all pha
       ,po_lines_all pla
       ,po_line_locations_all plla 
       ,po_distributions_all pda
WHERE  pha.po_header_id=pda.po_header_id
       AND pla.po_line_id=pda.po_line_id
       AND plla.po_line_id=pla.po_line_id
       AND AUTHORIZATION_STATUS = 'APPROVED'
   AND pha.creation_date >SYSDATE-90
       -- AND pha.org_id=':p_org_id'
       AND pla.closed_date is null
       AND pla.cancel_date is null
       AND nvl(pla.unit_price,0) > 0 
       AND NOT EXISTS 
       (SELECT 1 FROM po_headers_all pha1
       ,po_distributions_all pda1
       ,ap_invoice_distributions_all aid
       ,ap_invoices_all aia
 WHERE pha1.po_header_id=pda1.po_header_id
   AND aid.po_distribution_id=pda1.po_distribution_id
   AND aia.invoice_id=aid.invoice_id
   AND pha1.segment1 = pha.segment1 
   AND pda1.po_distribution_id = pda.po_distribution_id)
ORDER BY 1;
end po_details_insert;

procedure po_display(v_errbuf        OUT NOCOPY VARCHAR2
                    ,v_retcode       OUT NOCOPY NUMBER)
IS
                  --DECLARE
                   l_refcursor SYS_REFCURSOR;
   l_xmltype XMLTYPE;
BEGIN
   OPEN l_refcursor FOR select * from PO_DETAILS_RPT;
   l_xmltype := XMLTYPE(l_refcursor);
   dbms_output.put_line(l_xmltype.getClobVal);
end po_display;
end po_details_test_pkg;



------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE po_details_test_pkg
as
procedure po_details_insert( v_errbuf        OUT NOCOPY VARCHAR2
                                 ,v_retcode       OUT NOCOPY NUMBER);
 
procedure po_display(v_errbuf        OUT NOCOPY VARCHAR2
                    ,v_retcode       OUT NOCOPY NUMBER);
end po_details_test_pkg;

--SELECT * FROM USER_ERRORS;