PO Report Development using APEX features

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

PO Report Development using APEX features

Post by admin »

Hi,
In Oracle APEX reports development was always a challenge as standard features only allow tabular reports but not like PO, Invoice, etc. To develop such reports you need to install a third-party plugin or you need to buy a license of XML Publisher. Here is a solution to this problem.

Explained in the video,

https://youtu.be/ksZSUBDrnEM

SAMPLE CODE

Print Button Code
Execute JavaScript Code: window.print();

CREATE TABLE PO_HEADERS_ALL
(
PO_HEADER_ID NUMBER PRIMARY KEY,
VENDOR_NAME VARCHAR2 (100),
VENDOR_SITE_NAME VARCHAR2 (100),
VENDOR_CONTACT_NAME VARCHAR2 (100),
SHIP_TO_LOCATION VARCHAR2 (100),
BILL_TO_LOCATION VARCHAR2 (100),
TERMS_CODE VARCHAR2 (100),
PO_DATE DATE
);

CREATE TABLE PO.PO_LINES_ALL
(
PO_LINE_ID NUMBER primary key,
PO_HEADER_ID NUMBER NOT NULL,
LINE_NUM NUMBER NOT NULL,
ITEM_CODE VARCHAR2(100),
ITEM_DESCRIPTION VARCHAR2(240),
UOM VARCHAR2(32),
UNIT_PRICE NUMBER,
QUANTITY NUMBER,
AMOUNT NUMBER
);

CREATE SEQUENCE po_headers_id START WITH 1 NOCACHE;

CREATE SEQUENCE po_lines_id START WITH 1 NOCACHE;

DECLARE
v_header VARCHAR2 (500);
v_body VARCHAR2 (2000);
v_footer VARCHAR2 (500);
v_amount NUMBER DEFAULT 0;
-- Generate reports using HTML Tags in Oracle APEX for training purpose by ERPstuff.com
-- Version 1.0 Dated 6-DEC-2019

BEGIN
v_header := '<!DOCTYPE html>
<html>
<head>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}

td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}


</style>
</head>
<body>';


FOR h IN (SELECT *
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = :P7_PO_HEADER_ID)
LOOP
v_body :=
v_body
|| '<table>
<tr> <td>PO NUMBER </td><td>'
|| h.PO_HEADER_ID
|| '</td><td>VENDOR NAME </td><td>'
|| h.VENDOR_NAME
|| '</tr>
<tr> <td>VENDOR SITE NAME </td><td>'
|| h.VENDOR_SITE_NAME
|| '</td><td>VENDOR CONTACT NAME </td><td>'
|| h.VENDOR_CONTACT_NAME
|| '</tr>
<tr> <td>SHIP TO LOCATION </td><td>'
|| h.SHIP_TO_LOCATION
|| '</td><td>BILL TO LOCATION </td><td>'
|| h.BILL_TO_LOCATION
|| '</tr>
<tr> <td>TERMS CODE </td><td>'
|| h.TERMS_CODE
|| '</td><td>PO DATE </td><td>'
|| h.PO_DATE
|| '</tr>
</table><br><br>
<table>
<tr> <th>LINE NUM</th> <th>ITEM CODE</th> <th>ITEM DESCRIPTION</th> <th>UOM</th> <th>UNIT PRICE</th> <th>QUANTITY</th> <th>AMOUNT</th></tr>';

FOR l IN ( SELECT *
FROM PO_LINES_ALL
WHERE po_header_id = h.po_header_id
ORDER BY LINE_NUM)
LOOP
v_body :=
v_body
|| '<tr> <td>'
|| l.LINE_NUM
|| '</td> <td>'
|| l.ITEM_CODE
|| '</td><td>'
|| l.ITEM_DESCRIPTION
|| '</td><td>'
|| l.UOM
|| '</td><td>'
|| l.UNIT_PRICE
|| '</td><td>'
|| l.QUANTITY
|| '</td><td>'
|| l.AMOUNT
|| '</td>';
v_amount := v_amount + l.amount;
END LOOP;

v_body :=
v_body
|| '<tr> <td></td> <td></td><td></td><td></td><td></td><td>TOTAL:</td><td>'
|| v_AMOUNT
|| '</td>';

v_body := v_body || '</table>';
END LOOP;

v_footer := '</body>
</html>';

HTP.P (v_header || v_body || v_footer);
END;
Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 1 guest