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;
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;
No comments:
Post a Comment