In this blog, we will discuss how to generate a PDF using OIC and BI reports by supplying data from PaaS DB. So, let’s begin.
INTEGRATION FLOW:
STEP 1: Create a procedure to generate XML.
create or replace PACKAGE BODY IMS_GEN_INV_PDF_PKG
AS
PROCEDURE generate_invoice_xml (
p_invoice_id IN VARCHAR2,
p_xml OUT CLOB
) AS
l_header_xml XMLTYPE;
l_lines_xml XMLTYPE;
l_project_lines_xml XMLTYPE;
l_res XMLTYPE;
BEGIN
SELECT XMLELEMENT(
“header”,
XMLFOREST(
ih.CUST_NAME AS “name”,
ih.BILL_TO_ADDRESS AS “bill_to”,
ih.BILL_TO_STATE_CODE AS “bill_state_code”,
ih.BILL_TO_GSTIN AS “bill_gstin”,
ih.SHIP_TO_ADDRESS AS “ship_to”,
ih.SHIP_TO_STATE_CODE AS “ship_state_code”,
ih.SHIP_TO_GSTIN AS “ship_gstin”,
ih.attention AS “attention”,
ih.PPM_DRAFT_INVOICE_NUMBER AS “invoice_no”,
TO_CHAR(ih.INVOICE_DATE, ‘DD-Mon-YYYY’) AS “invoice_date”,
TO_CHAR(ih.INVOICE_DUE_DATE, ‘DD-Mon-YYYY’) AS “due_date”,
ih.reference AS “reference”,
ih.CUST_PO_NUM AS “po_number”,
ih.TOTAL_HEADER_AMOUNT_IN_BASE_CURRENCY AS “total_amount_due”,
ih.TOTAL_HEADER_AMOUNT_IN_BASE_CURRENCY AS “total_amount_due_rounded”,
spell_number(ih.TOTAL_HEADER_AMOUNT_IN_BASE_CURRENCY) AS “total_amount_in_words”,
ih.qr_code AS “qr_code_string”,
ih.irn_no AS “irn_no”,
ih.ack_date as “ack_date”,
ih.ack_no as “ack_no”,
ih.sign_string AS “sign_string”
)
)
INTO l_header_xml
FROM XXIM_AR_INVOICE_HEADERS_TBL ih
WHERE ih.INVOICE_HDR_ID = p_invoice_id;
SELECT XMLELEMENT(
“lines”,
XMLAGG(
XMLELEMENT(
“line”,
XMLFOREST(
ROWNUM AS “sr_no”,
il.HSN AS “hsn_sac_codes”,
il.LINE_DESCRIPTION AS “description”,
evt.AUTHENTICATION_EFFORTS AS “quantity”,
evt.BILLING_RATE AS “unit_price”,
il.TOTAL_BILLING_AMOUNT_IN_BASE_CURRENCY AS “line_total”
)
)
)
)
INTO l_lines_xml
FROM XXIM_AR_INVOICE_LINES_TBL il,
XXMA_PAAS_EVENT_TBL evt WHERE evt.PROJECT_ID = il.PROJECT_ID
and il.INVOICE_HDR_ID = p_invoice_id and evt.INV_HEADER_ID=p_invoice_id;
SELECT XMLELEMENT(
“project_lines”,
XMLAGG(
XMLELEMENT(
“project_line”,
XMLFOREST(
evt.PROJECT_NAME AS “project_name”,
evt.EMPLOYEE_NAME AS “employee_name”,
res.BILLING_ROLE AS “role”,
evt.INV_CYCLE_START_DATE AS “from_date”,
evt.INV_CYCLE_END_DATE AS “to_date”,
evt.AUTHENTICATION_EFFORTS AS “billed_hours”,
evt.BILLING_RATE AS “billing_rate”,
evt.BILLING_PERC AS “billing_percentage”,
evt.DISCOUNT AS “discount”,
evt.AMOUNT_IN_BILL_TXN_CURR AS “invoice_amount”
)
)
)
)
INTO l_project_lines_xml
FROM
XXMA_PAAS_EVENT_TBL evt,
XXIM_AR_INVOICE_HEADERS_TBL hdr,
XXFUSION_RESOURCE_ASSG_MST_TBL res
WHERE evt.PROJECT_ID = hdr.PROJECT_ID and evt.PROJECT_ID = res.PROJECT_ID and hdr.INVOICE_HDR_ID = p_invoice_id;
select XMLELEMENT(“invoice”, l_header_xml, l_lines_xml, l_project_lines_xml) into l_res from dual;
p_xml:=convert(l_res.getclobval() ,’UTF8′);
END generate_invoice_xml;
end IMS_GEN_INV_PDF_PKG;
STEP 2: Create RTF template based on above XML.
a) Create Data Model
- •Create the data model with the required parameters and select sample XML code to build the RTF template.
b) Create Report from the Data Model
- •Create report by sending sample data and format the RTF template.
STEP 3: OIC to send XML data to BI RUN REPORT SERVICE and get the final PDF output.
a) Choose ATP DB Connection to run procedure.
-
- •Select ATP DB connection.
- •Give any name for the endpoint and choose Invoke stored procedure in the dropdown as an operation.
-
- •Choose the Schema where the procedure is written and select package and procedure under that schema.
-
- •Verify the Details, which are chosen in the summary part.
- •Here Invoice_ID is taken as Input parameter to generate XML, which is mapped in the mapper.
b) Choose SOAP BI connection to send XML output to BI report.
-
- •Once the XML output is generated from procedure, add SOAP connection by giving any name for EndPoint.
-
- •Choose runReport operation from the dropdown.
-
- •Select Accept attachments in request and tick No to configure headers.
-
- •Check Summary for WSDL and selected operation details and click on finish button.
-
- •Give attributeLocale as “en-US” and attributeTemplate as “pdf”.
- •reportAbsolutePath is taken from the fusion report. For ex: “/Custom/Projects/Revenue Integration /Report TM-Resourcewise-with GST.xdo”.
- •reportRawData will take the XML output we get from the response by running Procedure.
- •sizeOfDataChunk Download will take value ‘-1’.
- •Validate mapping and finish the process.
- •You can map the output base64 report bytes to the response of the integrations – which can be used by UI app to download the PDF.
If you would like to know more, let us know in the comments or get in touch with us on [email protected].
Pooja is an Associate consultant at Conneqtion Group. She has completed her graduation in Computer science engineering from Visvesvaraya Technological University and is proficient with OIC, BIP reports, PL/SQL, MERN stack development.
