Wednesday, 5 August 2015

REF Cursors

What are Ref Cursors?
Ref cursor is a Data type. A variable created using this data type is usually called as a Cursor Variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions, packages etc.)

It is of two types:
Strong: With a Return Type
Weak: Without a Return Type

Advantages of Ref Cursors:
* Uses the same memory area for all the active sets created by different queries.
* Can be used to pass result sets between sub programs.
* Ability to change the query based on a certain criterion.

Difference between Static and Ref Cursors:

Static Cursors cannot be passed to sub programs whereas ref cursors can be passed between sub programs.
Static Cursors as the name suggests are Static and decided at the design time itself whereas Ref Cursors are changed during the execution time as per certain criterion.

Important Note: Ensure that any open cursor is closed before attempting to open the next cursor.

Examples of Ref Cursors:
A simple Ref Cursor:

declare type ref_cursor is REF CURSOR;
var_emp ref_cursor;
var emp.ename%type;
begin
open var_emp for select ename from emp;
loop
fetch var_emp into var;
exit when var_emp%notfound;
dbms_output.put_line(var);
end loop;
close var_emp;
end;

%ROWTYPE with Ref Cursor:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
er emp%rowtype;
begin
open c_emp for select * from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.ename ' - ' er.sal);
end loop;
close c_emp;
end;

RECORDS with Ref Cursor:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record ( name varchar2(20), sal number(6) );
er rec_emp;
begin
open c_emp for select ename,sal from emp;
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
end;
Multiple queries using Ref Cursor:
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
( name varchar2(20),
sal number(6) );
er rec_emp;
begin
open c_emp for select ename,sal from emp where deptno = 10;
dbms_output.put_line('Department: 10');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
open c_emp for select ename,sal from emp where deptno = 20;
dbms_output.put_line('Department: 20');
dbms_output.put_line('--------------');
loop
fetch c_emp into er;
exit when c_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
close c_emp;
end;

Ref Cursor used as Parameters between Sub Programs:

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record ( name varchar2(20),
sal number(6) );
procedure PrintEmployeeDetails(p_emp r_cursor) is
er rec_emp;
begin
loop
fetch p_emp into er;
exit when p_emp%notfound;
dbms_output.put_line(er.name ' - ' er.sal);
end loop;
end;
begin
for i in (select deptno,dname from dept)
loop
open c_emp for select ename,sal from emp where deptno = i.deptno;
dbms_output.put_line(i.dname);
dbms_output.put_line('--------------');
PrintEmployeeDetails(c_emp);
close c_emp;
end loop;
end;
Thus, these are some of the uses of Ref Cursors. Feel free to post any comments on this article.

Wednesday, 1 July 2015

XSL FO File

XSL FO file:-
If we are having HTML tags and that needs to be converted accordingly in oracle ebs, we need to create the xsl file and import that file in the rtf.

<?xml version='1.0' encoding='utf-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fo="http://www.w3.org/1999/XSL/Format" version="1.0">
<xsl:output method="xml" encoding="UTF-8"/>


<xsl:template match="BLOCKQUOTE">
<fo:block start-indent="0.25in">
<xsl:apply-templates select="*|text()"/>
</fo:block>
</xsl:template>
<xsl:template match="BLOCKQUOTE">
<fo:block start-indent="0.25in">
<xsl:apply-templates select="*|text()"/>
</fo:block>
</xsl:template>
<xsl:template match="P|p">
<fo:block white-space-collapse="false" padding-bottom="3pt" linefeed-treatment="preserve">
<xsl:apply-templates select="text()|*|@*"/>
</fo:block>
</xsl:template>
<xsl:template match="ol|OL">
<fo:list-block provisional-distance-between-starts="1cm"
provisional-label-separation="0.5cm">
<xsl:attribute name="space-after">
<xsl:choose>
<xsl:when test="ancestor::ul or ancestor::ol">
<xsl:text>0pt</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>12pt</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
<xsl:attribute name="start-indent">
<xsl:variable name="ancestors">
<xsl:choose>
<xsl:when test="count(ancestor::ol) or count(ancestor::ul)">
<xsl:value-of select="1 +
(count(ancestor::ol) +
count(ancestor::ul)) *
1.25"/>
</xsl:when>
<xsl:otherwise>
<xsl:text>1</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:value-of select="concat($ancestors, 'cm')"/>
</xsl:attribute>
<xsl:apply-templates select="*"/>
</fo:list-block>
</xsl:template>

<xsl:template match="ol/li|OL/LI">
<fo:list-item>
<fo:list-item-label end-indent="label-end()">
<fo:block>
<xsl:variable name="value-attr">
<xsl:choose>
<xsl:when test="../@start">
<xsl:number value="position() + ../@start - 1"/>
</xsl:when>
<xsl:otherwise>
<xsl:number value="position()"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:choose>
<xsl:when test="../@type='i'">
<xsl:number value="$value-attr" format="i. "/>
</xsl:when>
<xsl:when test="../@type='I'">
<xsl:number value="$value-attr" format="I. "/>
</xsl:when>
<xsl:when test="../@type='a'">
<xsl:number value="$value-attr" format="a. "/>
</xsl:when>
<xsl:when test="../@type='A'">
<xsl:number value="$value-attr" format="A. "/>
</xsl:when>
<xsl:otherwise>
<xsl:number value="$value-attr" format="1. "/>
</xsl:otherwise>
</xsl:choose>
</fo:block>
</fo:list-item-label>
<fo:list-item-body start-indent="body-start()">
<fo:block>
<xsl:apply-templates select="*|text()"/>
</fo:block>
</fo:list-item-body>
</fo:list-item>
</xsl:template>

<xsl:template match="table">
<fo:table table-layout="fixed">
<xsl:choose>
<xsl:when test="@cols">
<xsl:call-template name="build-columns">
<xsl:with-param name="cols"
select="concat(@cols, ' ')"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<fo:table-column column-width="200pt"/>
</xsl:otherwise>
</xsl:choose>
<fo:table-body>
<xsl:apply-templates select="*"/>
</fo:table-body>
</fo:table>
</xsl:template>

<xsl:template name="build-columns">
<xsl:param name="cols"/>
<xsl:if test="string-length(normalize-space($cols))">
<xsl:variable name="next-col">
<xsl:value-of select="substring-before($cols, ' ')"/>
</xsl:variable>
<xsl:variable name="remaining-cols">
<xsl:value-of select="substring-after($cols, ' ')"/>
</xsl:variable>
<xsl:choose>
<xsl:when test="contains($next-col, 'pt')">
<fo:table-column column-width="{$next-col}"/>
</xsl:when>
<xsl:when test="number($next-col) > 0">
<fo:table-column column-width="{concat($next-col, 'pt')}"/>
</xsl:when>
<xsl:otherwise>
<fo:table-column column-width="50pt"/>
</xsl:otherwise>
</xsl:choose>
<xsl:call-template name="build-columns">
<xsl:with-param name="cols" select="concat($remaining-cols, ' ')"/>
</xsl:call-template>
</xsl:if>
</xsl:template>

<xsl:template match="td">
<fo:table-cell
padding-start="3pt" padding-end="3pt"
padding-before="3pt" padding-after="3pt">
<xsl:if test="@colspan">
<xsl:attribute name="number-columns-spanned">
<xsl:value-of select="@colspan"/>
</xsl:attribute>
</xsl:if>
<xsl:if test="@rowspan">
<xsl:attribute name="number-rows-spanned">
<xsl:value-of select="@rowspan"/>
</xsl:attribute>
</xsl:if>
<xsl:if test="@border='1' or
ancestor::tr[@border='1'] or
ancestor::thead[@border='1'] or
ancestor::table[@border='1']">
<xsl:attribute name="border-style">
<xsl:text>solid</xsl:text>
</xsl:attribute>
<xsl:attribute name="border-color">
<xsl:text>black</xsl:text>
</xsl:attribute>
<xsl:attribute name="border-width">
<xsl:text>1pt</xsl:text>
</xsl:attribute>
</xsl:if>
<xsl:variable name="align">
<xsl:choose>
<xsl:when test="@align">
<xsl:choose>
<xsl:when test="@align='center'">
<xsl:text>center</xsl:text>
</xsl:when>
<xsl:when test="@align='right'">
<xsl:text>end</xsl:text>
</xsl:when>
<xsl:when test="@align='justify'">
<xsl:text>justify</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>start</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:when test="ancestor::tr[@align]">
<xsl:choose>
<xsl:when test="ancestor::tr/@align='center'">
<xsl:text>center</xsl:text>
</xsl:when>
<xsl:when test="ancestor::tr/@align='right'">
<xsl:text>end</xsl:text>
</xsl:when>
<xsl:when test="ancestor::tr/@align='justify'">
<xsl:text>justify</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>start</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:when test="ancestor::thead">
<xsl:text>center</xsl:text>
</xsl:when>
<xsl:when test="ancestor::table[@align]">
<xsl:choose>
<xsl:when test="ancestor::table/@align='center'">
<xsl:text>center</xsl:text>
</xsl:when>
<xsl:when test="ancestor::table/@align='right'">
<xsl:text>end</xsl:text>
</xsl:when>
<xsl:when test="ancestor::table/@align='justify'">
<xsl:text>justify</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>start</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>
<xsl:text>start</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<fo:block text-align="{$align}">
<xsl:apply-templates select="*|text()"/>
</fo:block>
</fo:table-cell>
</xsl:template>

<xsl:template match="tfoot">
<xsl:apply-templates select="tr"/>
</xsl:template>

<xsl:template match="th">
<fo:table-cell
padding-start="3pt" padding-end="3pt"
padding-before="3pt" padding-after="3pt">
<xsl:if test="@border='1' or
ancestor::tr[@border='1'] or
ancestor::table[@border='1']">
<xsl:attribute name="border-style">
<xsl:text>solid</xsl:text>
</xsl:attribute>
<xsl:attribute name="border-color">
<xsl:text>black</xsl:text>
</xsl:attribute>
<xsl:attribute name="border-width">
<xsl:text>1pt</xsl:text>
</xsl:attribute>
</xsl:if>
<fo:block font-weight="bold" text-align="center">
<xsl:apply-templates select="*|text()"/>
</fo:block>
</fo:table-cell>
</xsl:template>

<xsl:template match="thead">
<xsl:apply-templates select="tr"/>
</xsl:template>

<xsl:template match="tr">
<fo:table-row>
<xsl:apply-templates select="*|text()"/>
</fo:table-row>
</xsl:template>

<xsl:template match="ul|UL">
<fo:list-block provisional-distance-between-starts="1cm"
provisional-label-separation="0.5cm">
<xsl:attribute name="space-after">
<xsl:choose>
<xsl:when test="ancestor::ul or ancestor::ol">
<xsl:text>0pt</xsl:text>
</xsl:when>
<xsl:otherwise>
<xsl:text>12pt</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:attribute>
<xsl:attribute name="start-indent">
<xsl:variable name="ancestors">
<xsl:choose>
<xsl:when test="count(ancestor::ol) or count(ancestor::ul)">
<xsl:value-of select="1 +
(count(ancestor::ol) +
count(ancestor::ul)) *
1.25"/>
</xsl:when>
<xsl:otherwise>
<xsl:text>1</xsl:text>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:value-of select="concat($ancestors, 'cm')"/>
</xsl:attribute>
<xsl:apply-templates select="*"/>
</fo:list-block>
</xsl:template>

<xsl:template match="ul/li|UL/LI">
<fo:list-item>
<fo:list-item-label end-indent="label-end()">
<fo:block>•</fo:block>
</fo:list-item-label>
<fo:list-item-body start-indent="body-start()">
<fo:block>
<xsl:apply-templates select="*|text()"/>
</fo:block>
</fo:list-item-body>
</fo:list-item>
</xsl:template>

<xsl:template match="HR|hr">
<fo:block text-align="justify">
<fo:leader leader-pattern="rule">
<xsl:apply-templates select="@*"/>
</fo:leader>
</fo:block>
</xsl:template>
<xsl:template match="BR|br">
<xsl:text>
</xsl:text>
</xsl:template>
<xsl:template match="STRONG|B|b">
<fo:inline font-weight="bold">
<xsl:apply-templates/>
</fo:inline>
</xsl:template>
<xsl:template match="EM|I|i">
<fo:inline font-style="italic">
<xsl:apply-templates/>
</fo:inline>
</xsl:template>
<xsl:template match="U|u">
<fo:inline text-decoration="underline">
<xsl:apply-templates/>
</fo:inline>
</xsl:template>


<xsl:template match="@align">
<xsl:attribute name="text-align"><xsl:value-of select="."/></xsl:attribute>
</xsl:template>

<xsl:template match="@*">
</xsl:template>
</xsl:stylesheet>

Tuesday, 19 May 2015

How to print certain string based on the value in oracle

Requirement:- I want to Print the text Senior Manager when the text is Senior Manager. Division No:7

Solution:- select SUBSTR(NAME,0,INSTR(NAME,'.')-1) JOB from per_jobs

Output:-JOB

Deputy General Manager
Deputy Manager
General Manager
Manager
Managing Director
Senior Manager

Monday, 18 May 2015

How to change the date format as DD-MON-YYYY, when you are passing as parameter and printing in the output

How to change the date format as DD-MON-YYYY, when you are passing as parameter and printing in the output in XML PUBLISHER or BI PUBLISHER.

Requirement:- I want the date format to be printed as DD-MON-YYYY in the output. But the date is a parameter and there is no database column for this one.

In the output the date was printing like this:
2015-01-01T00:00:00.000+05:30

I want this to be printed as 01-JAN-2015

When I use <?xdofx:to_char(trunc(P_FROM_DATE),'DD-MON-YYYY')?>
it was coming as 31-Dec-2014
that means - 1 date.

Solution:- The solution for this is:

<?format-date:P_FROM_DATE;'dd-MMM-yyyy';'Asia/Calcutta'?>

By using the above xml tag, I am able to print the date as 01-Jan-2015.

I got the solution in this way, I am happy if it helps to anyone by seeing my post.

Friday, 17 April 2015

How to find the concurrent program and concurrent program short name using the query

select fcpt.user_concurrent_program_name, fcp.concurrent_program_name from
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs fcp
where
fcp.CONCURRENT_PROGRAM_ID= fcpt.CONCURRENT_PROGRAM_ID
and
fcpt.created_by =1706

Profile Value in oracle apps

If you want the query to display the data from different org_id's dont use org_id hardcoded in your query. Use Profile value for this.

SELECT sum(AIA.invoice_amount) INVOICE_AMT
,sum(NVL(AIPA.amount,APSA.amount_remaining)) PAYMENT_AMT
FROM
ap_invoices_all AIA
,ap_checks_all ACA
,ap_invoice_payments_all AIPA
,ap_payment_schedules_all APSA
,pa_projects_all PPA
WHERE AIA.invoice_id = AIPA.invoice_id (+)
AND APSA.invoice_id = AIA.invoice_id
AND AIA.vendor_id = NVL(:P_SUPPLIER_NAME,AIA.vendor_id)
AND AIA.vendor_site_id = NVL(:P_SUPPLIER_SITE_NAME, AIA.vendor_site_id)
AND NVL(ACA.check_date,SYSDATE) >= NVL(:P_FROM_DATE,NVL(ACA.check_date,SYSDATE))
AND NVL(ACA.check_date,SYSDATE) <= NVL(:P_TO_DATE,NVL(ACA.check_date,SYSDATE))
AND AIPA.check_id = ACA.check_id (+)
AND AIA.project_id = PPA.project_id (+)
AND AIA.invoice_type_lookup_code = 'STANDARD'
AND AIA.org_id = FND_PROFILE.value('ORG_ID')
GROUP BY AIA.vendor_id, AIA.vendor_site_id,aia.org_id

Combination of replace and regexp_replace

The Combination of using replace and regexp_replace

SELECT
clause CLAUSE, regexp_replace(REPLACE(replace(replace(replace(replace(replace(tc_description,' ',chr(10)),' ',chr(10)),' ',chr(10)),
'',chr(10)),'',chr(10)),'P_OP_MARGIN',NVL(:CP_OPERATING_MARGIN,'8')),'&', '\&'||'amp;') TC_DESCRIPTION
FROM
BRITANNIA_TERM_CONDTION_MSTR NTCM
,britannia_term_clause_mstr NTCLM
WHERE
NTCM.status = 1
AND NTCLM.status = 1
AND TC_TYPE = 15
AND TC_NAME = 127
AND NTCM.term_condition_id = NTCLM.TERM_CONDITION_ID
ORDER BY CLAUSE;

Items Uploading to MTL_SYSTEM_ITEMS_B

Items Uploading

1. Prepare the Flex Data

2. Prepare the Item Data

3. Move the csv’s into the server.

4. Go to BRITANNIA Inventory Super User Responsibility and run the concurrent program BRITANNIA ITEM FLEX LOADER

And BRITANNIA Item Flex Conversion.

5. Check the Flex has been created or not using the query

select

attribute1,attribute2,attribute3,description_attribute3,attribute4,description ,status

from xxBRITANNIA_flex_item_stg

where trunc(creation_date)=trunc(sysdate)

and attribute1 ='99'

and status='PROCESSED';

6. Run the BRITANNIA ITEM LOADER Concurrent Program and provide the path of the file in the Parameter.

7. Run the BRITANNIA ITEM CONVERSION Concurrent Program and after that Run the IMPORT ITEMS Concurrent Program.

This has to be run in the Master Organization using Change Organization and the items have been loaded. Check using the Query.

select * from mtl_system_items_b

where trunc(creation_date)=trunc(sysdate)

and organization_id=84

8. After that run the concurrent Program Assign Items to the Child Organization Concurrent Program and specify the Organization Id for uploading the items.

9. After that Apply Item Category Assignment using the program.

*********************Item Category Assignment****************

DECLARE

x NUMBER := 0;

CURSOR lcu_items

Select INVENTORY_ITEM_ID, category_id

from MTL_SYSTEM_ITEMS_B MSIB

,MTL_CATEGORIES MC

WHERE TRUNC(MSIB.CREATION_DATE) = TRUNC(SYSDATE)

AND ORGANIZATION_ID = 84

AND MC.segment1 = MSIB.segment1

AND MC.segment2 = MSIB.segment2;

BEGIN

FOR i IN lcu_items LOOP

x := x+1;

UPDATE inv.mtl_item_categories

set category_id = i.category_id

where inventory_item_id = i.inventory_item_id;

end loop;

dbms_output.put_line('Total Update: '||x);

--COMMIT;

END;

oracleapps world