Purpose: The
purpose of this post is to create a report in Oracle APPS using PL/SQL and XML
BI Publisher. PL/SQL program (Procedure) will be used to generate XML dataset and BI
Publisher for RTF template.
This case will help you to directly generate the report
output in excel format from simple PL/SQL program (Procedure).
Steps:
·
First write the PL/SQL Program as per the
requirement. Here I will be creating a simple MRN Register (Receiving report).
--Begin of the Procedure--
CREATE OR REPLACE
PROCEDURE XXFIL_MRN_REGISTER_P (
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
P_ORG_ID IN NUMBER,
P_ORGANIZATION_ID IN
NUMBER,
P_FROM_DATE IN VARCHAR2,
P_TO_DATE IN VARCHAR2,
P_VENDOR_ID IN NUMBER
)
AS
L_FROM_DATE DATE;
L_TO_DATE DATE;
L_ERROR_CODE NUMBER;
L_ERR_POSITION VARCHAR2 (2000);
L_ERR_MESSAGE VARCHAR2 (2000);
L_ORG_NAME VARCHAR2 (200);
L_VENDOR_NAME VARCHAR2 (200);
CURSOR C
IS
SELECT
PHA.SEGMENT1 ORDER_NUM,
RSH.RECEIPT_NUM,
PV.VENDOR_NAME,
RSH.SHIPMENT_NUM INVOICE_NUM,
TRUNC (RSH.SHIPPED_DATE)
INVOICE_DATE,
RSL.ITEM_ID,
MSI.SEGMENT1
|| '.'
|| MSI.SEGMENT2
|| '.'
|| MSI.SEGMENT3 ITEM_CODE,
MSI.DESCRIPTION,
RSH.CREATION_DATE RECEIPT_DATE,
SUM (PLLA.QUANTITY) ORDER_QTY,
SUM(RSL.QUANTITY_RECEIVED)
RECEIPT_QTY,
SUM(RSL.QUANTITY_RECEIVED *
PLA.UNIT_PRICE) RECEIPT_AMT
FROM PO_HEADERS_ALL PHA,
RCV_SHIPMENT_HEADERS RSH,
PO_VENDORS PV,
RCV_SHIPMENT_LINES RSL,
PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS_B MSI,
PO_LINE_LOCATIONS_ALL PLLA
WHERE 1 = 1
AND PHA.PO_HEADER_ID =
RSL.PO_HEADER_ID
AND PV.VENDOR_ID = RSH.VENDOR_ID
AND RSL.PO_LINE_ID = PLA.PO_LINE_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND MSI.INVENTORY_ITEM_ID =
RSL.ITEM_ID
AND RSH.SHIP_TO_ORG_ID =
MSI.ORGANIZATION_ID
AND RSH.SHIPMENT_HEADER_ID =
RSL.SHIPMENT_HEADER_ID
AND RSH.SHIP_TO_ORG_ID =
NVL
(P_ORGANIZATION_ID, RSH.SHIP_TO_ORG_ID)
AND TRUNC(RSH.SHIPPED_DATE) >=
TRUNC(NVL(L_FROM_DATE,RSH.SHIPPED_DATE))
AND TRUNC(RSH.SHIPPED_DATE) <=
TRUNC(NVL(L_TO_DATE,RSH.SHIPPED_DATE))
AND PLA.ORG_ID = NVL (P_ORG_ID,
PLA.ORG_ID)
AND RSH.VENDOR_ID = NVL
(P_VENDOR_ID, RSH.VENDOR_ID)
GROUP BY PHA.SEGMENT1,
MSI.SEGMENT1,
MSI.SEGMENT2,
MSI.SEGMENT3,
MSI.DESCRIPTION,
RSH.RECEIPT_NUM,
RSH.CREATION_DATE,
RSL.ITEM_ID,
PV.VENDOR_NAME,
RSH.SHIPMENT_NUM,
TRUNC (RSH.SHIPPED_DATE)
ORDER BY PHA.SEGMENT1, RSH.RECEIPT_NUM;
BEGIN
L_FROM_DATE := FND_DATE.CANONICAL_TO_DATE
(P_FROM_DATE);
L_TO_DATE
:= FND_DATE.CANONICAL_TO_DATE (P_TO_DATE);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<?xml
version="1.0" encoding="US-ASCII"?>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<LIST_G_REPORT>');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_ORG_ID>'
|| REPLACE (P_ORG_ID, CHR
(38), 'and')
|| '</P_ORG_ID>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_VENDOR_ID>'
|| REPLACE (P_VENDOR_ID,
CHR (38), 'and')
|| '</P_VENDOR_ID>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_FROM_DATE>' ||
TRUNC (L_FROM_DATE)
|| '</P_FROM_DATE>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_TO_DATE>' || TRUNC
(L_TO_DATE) || '</P_TO_DATE>'
);
BEGIN
SELECT VENDOR_NAME
INTO L_VENDOR_NAME
FROM AP_SUPPLIERS
WHERE VENDOR_ID = P_VENDOR_ID;
EXCEPTION
WHEN OTHERS
THEN
L_VENDOR_NAME := NULL;
END;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_VENDOR_NAME>'
|| REPLACE
(L_VENDOR_NAME, CHR (38), 'and')
||
'</P_VENDOR_NAME>'
);
BEGIN
SELECT NAME
INTO L_ORG_NAME
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID = P_ORG_ID;
EXCEPTION
WHEN OTHERS
THEN
L_ORG_NAME := NULL;
END;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<P_ORG_NAME>'
|| REPLACE (L_ORG_NAME,
CHR (38), 'and')
|| '</P_ORG_NAME>'
);
FOR R IN C
LOOP
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<LIST_F_REPORT>');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<ORDER_NUM>'
|| REPLACE
(R.ORDER_NUM, CHR (38), '')
||
'</ORDER_NUM>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<RECEIPT_NUM>'
|| REPLACE
(R.RECEIPT_NUM, CHR (38), '')
||
'</RECEIPT_NUM>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<INVOICE_NUM>'
|| REPLACE
(R.INVOICE_NUM, CHR (38), '')
||
'</INVOICE_NUM>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<INVOICE_DATE>'
|| REPLACE
(R.INVOICE_DATE, CHR (38), '')
||
'</INVOICE_DATE>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<VENDOR_NAME>'
|| REPLACE (R.VENDOR_NAME, CHR (38),
'')
||
'</VENDOR_NAME>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<ITEM_ID>'
|| REPLACE (R.ITEM_ID,
CHR (38), '')
|| '</ITEM_ID>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<ITEM_CODE>'
|| REPLACE
(R.ITEM_CODE, CHR (38), '')
|| '</ITEM_CODE>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<DESCRIPTION>'
|| REPLACE
(R.DESCRIPTION, CHR (38), '')
||
'</DESCRIPTION>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<ORDER_QTY>'
|| REPLACE
(R.ORDER_QTY, CHR (38), '')
||
'</ORDER_QTY>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<RECEIPT_QTY>'
|| REPLACE
(R.RECEIPT_QTY, CHR (38), '')
||
'</RECEIPT_QTY>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<RECEIPT_DATE>'
|| REPLACE
(R.RECEIPT_DATE, CHR (38), '')
||
'</RECEIPT_DATE>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'<RECEIPT_AMT>'
|| REPLACE
(R.RECEIPT_AMT, CHR (38), '')
||
'</RECEIPT_AMT>'
);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'</LIST_F_REPORT>');
END LOOP;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'</LIST_G_REPORT>');
EXCEPTION
WHEN OTHERS
THEN
L_ERROR_CODE := SQLCODE;
L_ERR_MESSAGE := SUBSTR (SQLERRM, 1,
2000);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
'Error Mesasage:' || L_ERR_MESSAGE);
FND_FILE.PUT_LINE
(FND_FILE.OUTPUT, '</LIST_G_REPORT>');
END;
--End of the Procedure--
·
Compile the above mentioned procedure and
register it as concurrent program in the application.
·
Now run the program and generate the XML output
and save it on your system.
·
Create the rtf template (How to create the rtf
template? Click here) and load the xml data and preview the output.
·
Register the rtf template in the application
(How to register rtf template? Click here).
·
Run the program and view the output.
No comments:
Post a Comment