XML Report TiPs

Metalink ID and Patch:
=======================
XML Publisher 4.5 - 3554613
XML Publisher 5.0 – 3822219
XML Publisher 5.0.1 - 4236958 (Core bug fixes)
Template Builder Plugin - 4239263 incl user guide, demos, etc
Conc Manager Integration – 3435480
Please check the associated About Documents for these patches prior to application
Check Printing Paper – Note:312353.1
Single Step Reporting Paper - Note:295409.1
Public Site

Tables:
=======
PER_GB_XDO_TEMPLATES
XDO_DS_DEFINITIONS_B
XDO_DS_DEFINITIONS_TL
XDO_DS_DEFINITIONS_VL
XDO_LOBS
XDO_TEMPLATES_B
XDO_TEMPLATES_TL
XDO_TEMPLATES_VL
XDO_TEMPLATE_FIELDS
XDO_TRANS_UNITS
XDO_TRANS_UNIT_PROPS
XDO_TRANS_UNIT_VALUES

Important: Since the latest release of XML Publisher, Applications Object Library (FND) patch 3435480 has been released to fully integrate XML Publisher with the Concurrent Manager. If you have taken this patch, you no longer have to run the XML Publisher Concurrent Request as a second step.

This Patch is in 5.5 version.

Develop xml code from plsql:
===========================
PROCEDURE do_it(errbuf OUT VARCHAR2, retcode OUT VARCHAR2) IS
CURSOR c_xml IS
SELECT SYS_XMLAGG(xmlelement( "e",
xmlforest(
e.ename as "EmployeeName",
e.sal as "Dept"
))).getCLobVal() FROM scott.emp e, scott.dept d
WHERE e.deptno = d.deptno;
l_xml CLOB;
proceed BOOLEAN;
l_curr PLS_INTEGER;
l_next PLS_INTEGER;
BEGIN
proceed := TRUE;
l_curr := 1;
l_next := 1;
OPEN c_xml();
FETCH c_xml INTO l_xml;
CLOSE c_xml;
IF proceed THEN
WHILE l_curr < dbms_lob.getlength(l_xml)
LOOP
l_next := INSTR(l_xml, ' l_next := INSTR(l_xml, '>', l_next);
Fnd_File.put_line(Fnd_File.OUTPUT, SUBSTR(l_xml, l_curr, l_next - l_curr + 1));
l_curr := l_next + 1;
END LOOP;
END IF;
Fnd_File.put_line(Fnd_File.LOG, 'XML succesfully created!');
END do_it;
/

Bold Character:
< ? if:ACCTD_AMT >1000 ? > < xsl:attribute xdofo:ctx="block" name="font-weight" > bold < / xsl:attribute >< ? end if ? >

Background Color:
< ? if:ACCTD_AMT >1000? > < xsl:attribute xdofo:ctx="block" name="background-color" >lime< / xsl:attribute >< ? end if ? >

ADD Page Total:
< ? add-page-total:pt;'ACCTD_AMT' ? >

How to run SQLscripts using crontab?

Suppose that you want to run your stored procedure at the particular time on your Oracle database.

Create the following file (my_job.sh) under your home directory and don't forget to change execute permission on this file:

#!/bin/ksh

export ORACLE_SID=

export ORACLE_HOME=

export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib

export TMPDIR=/tmp

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:.

sqlplus -s << EOF < your_username > / < your_password >

exec ;

exit
EOF

Warning! - set correct ORACLE_SID and ORACLE_HOME.

Schedule this job using crontab - see reference on crontab

For example the following listing of crontab shows that my_job.sh will run at 5:00:AM every day:

#--------------------------------------------------------------
# min hour daymo month daywk cmd
#--------------------------------------------------------------
# My Job
#--------------------------------------------------------------
0 5 * * * /home/oracle/my_job.sh
#*****************************************************************

Kill Session

To Find out SID and SERIAL#:

SELECT VA.SQL_TEXT,VS.SID, VS.SERIAL#,process,vs.status, vp.pid
FROM V$SQLAREA VA, V$SESSION VS, v$process vp
WHERE VA.ADDRESS = VS.SQL_ADDRESS
AND VA.HASH_VALUE = VS.SQL_HASH_VALUE
AND VS.PADDR = VP.ADDR
AND upper(sql_text) LIKE '%SEARCHING_TEXT%';

-- to kill the session

ALTER SYSTEM KILL SESSION 'sid,serial#'

How and When U Need to Bounce Apache Server?

When???

Bounce the Apache Server when the following changes are made:

1. Create new template
2. Define new expense type for existing template
3. Update end date from expense type (Change immediate for disconnected )
4. End date currency in Currencies window
5. Change prompt value for expense type (Change immediate for details)
6. Change profile option
7. Change fnd messages
8. Recompile plsql packages
9. Enable tax codes for OIE (Change immediate for details page)
10. Update inactive on date for template
11. Define Itemization setup (Itemization required does not require bounce, Specify initial set of expense types does not require bounce. Subsequent changes to set requires bounce)

How???

The Apache server can be shutdown with the seeded Oracle script 'adapcctl.sh'. This script normally resides in $COMMON_TOP/admin/scripts.

When you are in $COMMON_TOP/admin/scripts, to stop Apache enter the command 'adapcctl.sh stop'. To start Apache enter 'adapcctl.sh start'.

New Terminologies in R12

Some of the new terminologies used in Oracle Applications Release 12 are:

11i Vs. 12

Sets of Books = Ledgers
Global Accounting Engine = Subledger Accounting
AX Posting Sets of Books = Secondary Ledgers
Global Intercompany System = Advanced Global Intercompany System
GL Intercompany Accounts = Intracompany Balancing
Translated Currency = Balance level RCs
Multiple Reporting Currencies = Reporting Currencies
MRC Primary Set of Books = Primary Ledger
MRC Reporting Set of Books = Reporting Currency
Thin MRC Reporting Book = Journal level RC
Full MRC Reporting Book = Subledger level RC
Translated Balance = Balance Level RC

Hope it will be helpful. ………..