Rajesh K: April 2015

Sunday, April 26, 2015

PL/SQL Procedure to Return Table Type

Few days ago I got a requirement in my company to provide a 'Manager-wise Infected Portfolio' which will show Managers with No of Contracts in their Portfolio, current overdue amount, outstanding exposure and last three month's exposure based of cutoff date user is providing.

Pattern look likes this

Name No of Contracts / OverdueAmount / ThisMonthExposure / Exposure Month3 / Exposure Month2 / Exposure Month1

Solution:

So I decided to solve this task using Database procedure which will take some inputs and return whole PL/SQL Table type containing multiple rows of the same contract but data of different Months.....I used that procedure in a form and when user press the button it inserts all rows in a database table to generate a report.

Using this operation the report generation process became faster than my older work working directly into Form application.

Any ways come to the example here are the steps to implement this kind of requirement.

Note: For some reasons i m not showing my exact code but just showing the concept of my logic that I've implemented for this Example u need traditional SCOTT schema in Oracle database.

Example:

In This Example I m going to show the PL/SQL procedure to receive input parameter DeptCode (The Department code from Employee table) and return all employees of the same department but as PL/SQL Table

--Create a Package which will contain structure of your returning table (You can do it database object types as well)

Create or replace Package MyTablePackage as

-- Create a Record type for Table

TYPE MyRecord
IS RECORD
(EmpCd number,
EmpName Varchar2(60),
DepartCode number,
Salary number );

--Declaring a Returing Table type

TYPE MyTableType IS TABLE OF MyRecord
INDEX BY BINARY_INTEGER;

-- Procedure will use that Table type as OUT parameter
PROCEDURE MyProcedure
(DeptCode number,
OutTableParam OUT MyTableType);
end MyTablePackage;

--Next is to create Package body.
Create or replace PACKAGE BODY MYTABLEPACKAGE AS
PROCEDURE MyProcedure
(DeptCode number, OutTableParam OUT MyTableType) AS
v_counter number := 0;
BEGIN
     for EmpCursor in
         (select empno, ename, sal
           from scott.emp
           where deptno = deptcode
           order by empno)
     loop
       v_counter := v_counter + 1;
       OutTableParam(v_counter).EmpCd := EmpCursor.empno;
   OutTableParam(v_counter).EmpName := EmpCursor.ename;
       OutTableParam(v_counter).DepartCode := Deptcode;
   OutTableParam(v_counter).Salary := EmpCursor.sal;
    end loop;
END MyProcedure;
END MYTABLEPACKAGE;

---To Verify the output

Set serveroutput on
declare
   v_counter BINARY_INTEGER := 1;
   v_mytable MyTablePackage.MyTableType;
begin
   --Passing Parameters to Procedure Department No 10
MyTablePackage.MyProcedure(10, v_mytable);

while v_counter <= v_mytable.count
loop

dbms_output.put_line(v_mytable(v_counter).EmpCd||'|'||

v_mytable(v_counter).EmpName||'|'||

v_mytable(v_counter).DepartCode||'|'||

                                           v_mytable(v_counter).Salary);
      v_counter := v_counter + 1;
   end loop;
end;

Output would be like this (   MyTablePackage.MyProcedure(10, v_mytable);)

Department No 10
7839|KING|10|5000
7934|MILLER|10|1300

Department No 30 (   MyTablePackage.MyProcedure(30, v_mytable);)
7499|ALLEN|30|1600
7521|WARD|30|1250
7654|MARTIN|30|1250
7698|BLAKE|30|2850
7844|TURNER|30|1500
7900|JAMES|30|950

Friday, April 17, 2015

Script to delete template and Data Definition of XML Publisher

select * from XDO_TEMPLATES_B where template_code = :p_template_code;

select * from XDO_TEMPLATES_TL where template_code = :p_template_code;

select * from XDO_LOBS where lob_code = :p_template_code;

select * from XDO_DS_DEFINITIONS_TL where data_source_code = :p_template_code;

select * from XDO_DS_DEFINITIONS_B where data_source_code = :p_template_code;

declare
p_template_code XDO_TEMPLATES_B.template_code%type := 'XXGRREP' ;
begin
delete from XDO_TEMPLATES_B where template_code = :p_template_code
delete from XDO_TEMPLATES_TL where template_code = :p_template_code
delete from xdo_lobs where lob_code = :p_template_code
delete from XDO_DS_DEFINITIONS_TL where data_source_code = :p_template_code
delete from XDO_DS_DEFINITIONS_b where data_source_code = :p_template_code
end;

Saturday, April 4, 2015

P2P complete query

SELECT PRHA.SEGMENT1 REQNO,
       PRHA.TYPE_LOOKUP_CODE REQTYPE,
       PHA.SEGMENT1 PONO,
       PHA.TYPE_LOOKUP_CODE POTYPE,
       APS.VENDOR_NAME SUPPLIERNAME,
       ASSA.VENDOR_SITE_CODE SUPPLIERSITE,
       RSH.SHIPMENT_NUM RECEIPTNO,
       AIA.INVOICE_ID INVID,
       AIA.INVOICE_NUM INVOICENO,
       AIA.INVOICE_AMOUNT INVAMOUNT,
       AIA.INVOICE_TYPE_LOOKUP_CODE INVTYPE,
       AIA.INVOICE_CURRENCY_CODE INVCURRENCY,
       AIPA.INVOICE_PAYMENT_ID PAYMENTID,
       AIPA.AMOUNT PAYMENTAMOUNT,
       ACA.CHECK_ID CHECKID,
       ACA.BANK_ACCOUNT_NAME BANKNAME,
       ACA.BANK_ACCOUNT_NUM BANKNO,
       ACA.BANK_ACCOUNT_TYPE BANKTYPE,
       GJH.JE_HEADER_ID BATCHHEADERID,
       GJH.JE_SOURCE BATCHSOURCE,
       GJB.JE_BATCH_ID BATCHID,
       GJB.NAME BATCHNAME
FROM PO_REQUISITION_HEADERS_ALL PRHA,
       PO_REQUISITION_LINES_ALL PRLA,
       PO_REQ_DISTRIBUTIONS_ALL PRDA,
       PO_DISTRIBUTIONS_ALL PDA,
       PO_LINE_LOCATIONS_ALL PLLA,
       PO_LINES_ALL PLA,
       PO_HEADERS_ALL PHA,
       AP_SUPPLIERS APS,
       AP_SUPPLIER_SITES_ALL ASSA,
       RCV_SHIPMENT_HEADERS RSH,
       RCV_SHIPMENT_LINES RSL,
       AP_INVOICES_ALL AIA,
       AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
       AP_INVOICE_PAYMENTS_ALL AIPA,
       AP_CHECKS_ALL ACA,
       GL_JE_HEADERS GJH,
       GL_JE_LINES GJL,
       GL_JE_BATCHES GJB
WHERE     PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
       AND PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
       AND PDA.REQ_DISTRIBUTION_ID = PRDA.DISTRIBUTION_ID
       AND PLLA.LINE_LOCATION_ID = PDA.LINE_LOCATION_ID
       AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
       AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
       AND APS.VENDOR_ID = PHA.VENDOR_ID
       AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
       AND APS.VENDOR_ID = ASSA.VENDOR_ID
       AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
       AND RSL.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
       AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
       AND APS.VENDOR_ID = AIA.VENDOR_ID
       AND AIDA.INVOICE_ID = AIA.INVOICE_ID
       AND AIPA.INVOICE_ID = AIA.INVOICE_ID
       AND AIPA.CHECK_ID = ACA.CHECK_ID
       AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
       AND TO_CHAR (AIDA.INVOICE_ID) = GJL.REFERENCE_2
       AND TO_CHAR (AIDA.DISTRIBUTION_LINE_NUMBER) = GJL.REFERENCE_3
       AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
       AND PRHA.SEGMENT1 = '100'                                            --Requisition Number

Thursday, April 2, 2015

Create XML Publisher Report using Data Templates

The data templates are useful when you need to create a XML Publisher report without using the RDF. The XML Publisher data engine enables you to rapidly generate any kind of XML data structure against any database in a scalable, efficient manner which you can easily use in your templates.
The data template is the method by which you communicate your request for data to the data engine. It is an XML document whose elements collectively define how the data engine will process the template to generate the XML.

The Data Template Definition:

The data template is an XML document that consists of four basic sections:

define parameters,
define triggers,
define data query,
define data structure

Here is a sample data template:

1] Parameters Section:

A parameter is a variable whose value you can set at runtime. Parameters are especially useful for modifying SELECT statements and setting PL/SQL variables at runtime. However, the Parameters section of the data template is optional.

How to Define Parameters:

</parameters>

How to Pass Parameters:

To pass parameters, (for example, to restrict the query), use bind variables in your query. For example:

SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO;

2] Data Query Section:

The section of the data template is required.

How to Define SQL Queries

The element is placed between the open and close dataQuery tags. The element has a related attribute, name. It is expressed within the tag. The query is entered in the CDATA section.

<![CDATA[SELECT *

FROM apps.gl_balances glb

WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO]]>

</sqlStatement>

</dataQuery>

Lexical References:

You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, or HAVING. Use a lexical reference when you want the parameter to replace multiple values at runtime.
Create a lexical reference using the following syntax:
&parametername

Define the lexical parameters as follows:
• Before creating your query, define a parameter in the PL/SQL default package for each lexical reference in the query. The data engine uses these values to replace the lexical parameters.
• Create your query containing lexical references

<![CDATA[ &p_query ]]>

</sqlStatement>

</dataQuery>

In the function xxfin_test_pkg.beforeReport, you can design your query at runtime as below:

p_query VARCHAR2(2000) :=
‘SELECT * apps.gl_balances glb
WHERE glb.period_name BETWEEN :P_PERIOD_FROM AND :P_PERIOD_TO’;

How to Define a Data Link between Queries:

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the element to define the link between queries.
The following example shows a query link using a bind variable:

<![CDATA[SELECT PARENT_COL FROM XXTEST_LINK_TABLE1]]>

</sqlStatement>

</dataQuery>

<![CDATA[SELECT CHILD_COL FROM XXTEST_LINK_TABLE2]]>

</sqlStatement>

</dataQuery>

childQuery="Q2" childColumn="CHILD_COL"/>

The element has a set of attributes. Use these attributes to specify the required link information. You can specify any number of links.

3] Using Data Triggers:

Data triggers execute PL/SQL functions at specific times during the execution and generation of XML output. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as perform initialization tasks and access the database.
Data triggers are optional, and you can have as many elements as necessary. The element has a set of related attributes. These are expressed within the tag.
For example:

Name: The event name to fire this trigger.
Source: The PL/SQL . where the executable code resides.

4] Data Structure Section:

In the data structure section you define what the XML output will be and how it will be structured. The complete group hierarchy is available for output. You can specify all the columns within each group and break the order of those columns; you can use summaries, and placeholders to further customize within the groups.

Sample Data Structure:

</group>

</dataStructure>

How to Call a Data Template:

There are two methods for calling the data engine to process your data template:
• Concurrent Manager
• Data Engine Java APIs
Before you can use either of these methods, you must first register your data template in the Template Manager as a Data Definition. After that you attached the RTF template to that data definition.

Calling a Data Template from the Concurrent Manager:

To use the concurrent manager to execute your data template, you must register a Concurrent Program, using the define Concurrent Programs form:

Executable Name Enter the XML Publisher data engine executable: XDODTEXE
Output Format Select “XML” as the output format.

Note: We do not need to create a concurrent executable as we use a seeded executable XDODTEXE

Concurrent Program – Parameters:
For each parameter in the Data Template, define a parameter in the concurrent program. The Data Template parameter name should match the concurrent program parameter token

Few tips for best practices:

Performing operations in SQL is faster than performing them in the data template or PL/SQL. It is recommended that you use SQL for the following operations:

Use a WHERE clause instead of a group filter to exclude records.

Perform calculations directly in your query rather than in the template.

To maximize performance when building data queries in the data template: XML Publisher tests have shown that using bind variables is more efficient than using the link tag.
The dataStructure section is required for multiple queries and optional for single queries. If omitted for a single query, the data engine will generate flat XML.