myscripts

Tuesday, 11 April 2017

How to create an XML for creating a table in Oracle

SQL> select dbms_metadata.get_xml('TABLE','EMP','SCOTT') from dual;

<?xml version="1.0"?><ROWSET><ROW>
<TABLE_T>
<VERS_MAJOR>2</VERS_MAJOR>
<VERS_MINOR>5 </VERS_MINOR>
<OBJ_NUM>20056</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>20056</OBJ_NUM>
<DATAOBJ_NUM>20056</DATAOBJ_NUM>
<OWNER_NUM>48</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>EMP</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2016-11-29 16:22:24</CTIME>
<MTIME>2017-01-05 17:33:32</MTIME>
<STIME>2016-12-09 16:30:57</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>15</SPARE2>
<SPARE3>48</SPARE3>
<OWNER_NAME2>SCOTT</OWNER_NAME2>
</SCHEMA_OBJ>
<STORAGE>
<FILE_NUM>1</FILE_NUM>
<BLOCK_NUM>44984</BLOCK_NUM>
<TYPE_NUM>5</TYPE_NUM>
<TS_NUM>0</TS_NUM>
<BLOCKS>8</BLOCKS>
<EXTENTS>1</EXTENTS>
<INIEXTS>8</INIEXTS>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
<USER_NUM>48</USER_NUM>
<LISTS>1</LISTS>
<GROUPS>1</GROUPS>
<BITMAPRANGES>2147483645</BITMAPRANGES>
<CACHEHINT>0</CACHEHINT>
<SCANHINT>0</SCANHINT>
<HWMINCR>20056</HWMINCR>
<FLAGS>4325377</FLAGS>
</STORAGE>
<TS_NAME>SYSTEM</TS_NAME>
<BLOCKSIZE>8192</BLOCKSIZE>
<DATAOBJ_NUM>20056</DATAOBJ_NUM>
<COLS>8</COLS>
<PCT_FREE>10</PCT_FREE>
<PCT_USED>40</PCT_USED>
<INITRANS>1</INITRANS>
<MAXTRANS>255</MAXTRANS>
<FLAGS>1074266129</FLAGS>
<AUDIT_VAL>--------------------------------------</AUDIT_VAL>
<ROWCNT>14</ROWCNT>
<BLKCNT>1</BLKCNT>
<EMPCNT>6</EMPCNT>
<AVGSPC>7422</AVGSPC>
<CHNCNT>0</CHNCNT>
<AVGRLN>41</AVGRLN>
<AVGSPC_FLB>7422</AVGSPC_FLB>
<FLBCNT>1</FLBCNT>
<ANALYZETIME>2017/01/04 14:39:07</ANALYZETIME>
<SAMPLESIZE>14</SAMPLESIZE>
<INTCOLS>8</INTCOLS>
<KERNELCOLS>8</KERNELCOLS>
<PROPERTY>537919488</PROPERTY>
<PROPERTY2>0</PROPERTY2>
<XMLSCHEMACOLS>N</XMLSCHEMACOLS>
<TSTZ_COLS>N</TSTZ_COLS>
<TRIGFLAG>0</TRIGFLAG>
<SPARE1>736</SPARE1>
<SPARE3>1225763</SPARE3>
<SPARE5>NN0</SPARE5>
<SPARE6>2016/12/09 11:00:57</SPARE6>
<COL_LIST>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<SEGCOLLENGTH>22</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>EMPNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<PRECISION_NUM>4</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>1</NOT_NULL>
<INTCOL_NUM>1</INTCOL_NUM>
<BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<CON>
<OWNER_NUM>48</OWNER_NUM>
<NAME>SYS_C006988</NAME>

<CON_NUM>6988</CON_NUM>
<OBJ_NUM>20056</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>7</CONTYPE>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>2016/11/29 16:22:24</MTIME>
<FLAGS>12</FLAGS>
</CON>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>2</COL_NUM>
<SEGCOL_NUM>2</SEGCOL_NUM>
<SEGCOLLENGTH>10</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>ENAME</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>10</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>2</INTCOL_NUM>
<BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>10</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>3</COL_NUM>
<SEGCOL_NUM>3</SEGCOL_NUM>
<SEGCOLLENGTH>9</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>JOB</NAME>
<TYPE_NUM>1</TYPE_NUM>
<LENGTH>9</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>3</INTCOL_NUM>
<BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>873</CHARSETID>
<CHARSETFORM>1</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>9</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>4</COL_NUM>
<SEGCOL_NUM>4</SEGCOL_NUM>
<SEGCOLLENGTH>22</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>MGR</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<PRECISION_NUM>4</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>4</INTCOL_NUM>
<BASE_INTCOL_NUM>4</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>5</COL_NUM>
<SEGCOL_NUM>5</SEGCOL_NUM>
<SEGCOLLENGTH>7</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>HIREDATE</NAME>
<TYPE_NUM>12</TYPE_NUM>
<LENGTH>7</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>5</INTCOL_NUM>
<BASE_INTCOL_NUM>5</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>

<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>6</COL_NUM>
<SEGCOL_NUM>6</SEGCOL_NUM>
<SEGCOLLENGTH>22</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>SAL</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<PRECISION_NUM>7</PRECISION_NUM>
<SCALE>2</SCALE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>6</INTCOL_NUM>
<BASE_INTCOL_NUM>6</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>7</COL_NUM>
<SEGCOL_NUM>7</SEGCOL_NUM>
<SEGCOLLENGTH>22</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>COMM</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<PRECISION_NUM>7</PRECISION_NUM>
<SCALE>2</SCALE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>7</INTCOL_NUM>
<BASE_INTCOL_NUM>7</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
<COL_LIST_ITEM>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>8</COL_NUM>
<SEGCOL_NUM>8</SEGCOL_NUM>
<SEGCOLLENGTH>22</SEGCOLLENGTH>
<OFFSET>0</OFFSET>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
<LENGTH>22</LENGTH>
<FIXEDSTORAGE>0</FIXEDSTORAGE>
<PRECISION_NUM>2</PRECISION_NUM>
<SCALE>0</SCALE>
<NOT_NULL>0</NOT_NULL>
<INTCOL_NUM>8</INTCOL_NUM>
<BASE_INTCOL_NUM>8</BASE_INTCOL_NUM>
<BASE_COL_TYPE>0</BASE_COL_TYPE>
<PROPERTY>0</PROPERTY>
<CHARSETID>0</CHARSETID>
<CHARSETFORM>0</CHARSETFORM>
<SPARE1>0</SPARE1>
<SPARE2>0</SPARE2>
<SPARE3>0</SPARE3>
</COL_LIST_ITEM>
</COL_LIST>
<CON0_LIST/>
<CON1_LIST>
<CON1_LIST_ITEM>
<OWNER_NUM>48</OWNER_NUM>
<NAME>SYS_C006994</NAME>
<CON_NUM>6994</CON_NUM>
<OBJ_NUM>20056</OBJ_NUM>
<PROPERTY>537919488</PROPERTY>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>2</CONTYPE>
<ENABLED>0</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>2016/12/06 16:11:45</MTIME>
<FLAGS>8</FLAGS>
<OID_OR_SETID>0</OID_OR_SETID>
<COL_LIST>
<COL_LIST_ITEM>
<CON_NUM>6994</CON_NUM>
<OBJ_NUM>20056</OBJ_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>

<NAME>EMPNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</COL_LIST_ITEM>
</COL_LIST>
</CON1_LIST_ITEM>
</CON1_LIST>
<CON2_LIST>
<CON2_LIST_ITEM>
<OWNER_NUM>48</OWNER_NUM>
<NAME>CC01</NAME>
<CON_NUM>7022</CON_NUM>
<OBJ_NUM>20056</OBJ_NUM>
<NUMCOLS>1</NUMCOLS>
<CONTYPE>4</CONTYPE>
<ROBJ_NUM>20057</ROBJ_NUM>
<RCON_NUM>7005</RCON_NUM>
<ENABLED>1</ENABLED>
<INTCOLS>1</INTCOLS>
<MTIME>2016/12/06 16:14:12</MTIME>
<FLAGS>7</FLAGS>
<SCHEMA_OBJ>
<OBJ_NUM>20057</OBJ_NUM>
<DATAOBJ_NUM>20057</DATAOBJ_NUM>
<OWNER_NUM>48</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>DEPT</NAME>
<NAMESPACE>1</NAMESPACE>
<TYPE_NUM>2</TYPE_NUM>
<TYPE_NAME>TABLE</TYPE_NAME>
<CTIME>2016-11-29 16:22:24</CTIME>
<MTIME>2016-12-07 14:20:37</MTIME>
<STIME>2016-11-29 16:22:24</STIME>
<STATUS>1</STATUS>
<FLAGS>0</FLAGS>
<SPARE1>6</SPARE1>
<SPARE2>1</SPARE2>
<SPARE3>48</SPARE3>
<OWNER_NAME2>SCOTT</OWNER_NAME2>
</SCHEMA_OBJ>
<SRC_COL_LIST>
<SRC_COL_LIST_ITEM>
<CON_NUM>7022</CON_NUM>
<OBJ_NUM>20056</OBJ_NUM>
<INTCOL_NUM>8</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>20056</OBJ_NUM>
<COL_NUM>8</COL_NUM>
<INTCOL_NUM>8</INTCOL_NUM>
<SEGCOL_NUM>8</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</SRC_COL_LIST_ITEM>
</SRC_COL_LIST>
<TGT_COL_LIST>
<TGT_COL_LIST_ITEM>
<CON_NUM>7005</CON_NUM>
<OBJ_NUM>20057</OBJ_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<POS_NUM>1</POS_NUM>
<SPARE1>0</SPARE1>
<OID_OR_SETID>0</OID_OR_SETID>
<COL>
<OBJ_NUM>20057</OBJ_NUM>
<COL_NUM>1</COL_NUM>
<INTCOL_NUM>1</INTCOL_NUM>
<SEGCOL_NUM>1</SEGCOL_NUM>
<PROPERTY>0</PROPERTY>
<NAME>DEPTNO</NAME>
<TYPE_NUM>2</TYPE_NUM>
</COL>
</TGT_COL_LIST_ITEM>
</TGT_COL_LIST>
</CON2_LIST_ITEM>
</CON2_LIST>
<PKREF_LIST/>
<REFPAR_LEVEL>0</REFPAR_LEVEL>
</TABLE_T>
</ROW></ROWSET>

Monday, 13 March 2017

ORACLE REPORTS Interview topics

Anchor: Anchors are used to determine the relative position of child object with its parent object.

Some objects are increase in its size while executing in a reports which leads to pushing the other objects. to prevent this we will use anchors to place one object relative to the other.

Restrictions:
* We can anchor one object to only one other object.
* we cannot anchor matrix objects, anchors , margins to any other object.
* Nothing can be anchored to a hidden object.
* Moving an anchor can also cause the two other object that are anchored will also move.
* An Anchor cannot be re-sized.
* Anchor must be always in the top layer of parent and child objects.

Oracle Reports - SRW PACKAGE most useful procedures

SRW.BREAK:

This procedure temporarily stops the report execution and displays the read only values of columns and parameter values, Report execution resumes when read only screen accepted.

syntax: SRW.BREAK;

SRW.CONTEXT_FAILURE: (EXCEPTION);

Oracle reports stops execution and raises the error when ever we call a built in procedure in an invalid context. ex: calling a srw.get_page_num from pram form triggers.

SRW.DO_SQL:
This procedure is used to execute the ddl /dml from report builder. DML are much faster using in plsql rather than DO_SQL.

syntax: SRW.DO_SQL('SQL Statment');

SRW.DO_SQL_FAILURE (EXCEPTION):

Oracle raises this error when srw.do_sql fails execution.

SRW.GET_PAGE_NUM: This procedure is used to get the page number of the report. It must be used in thefields format trigger.
page_num ---attribute
Syntax: SRW.GET_PAGE_NUM (page_num);

SRW.SET_FORMAT_ORDER: This procedure is used to alter the order of execution of main , header & trailer sections of a report. Even though we change the order of execution of these section these will be display by the default order HEADER - MAIN - TRAILER.

This will be used to display the contents of the main section in the header section. So, that we can populate the summarized vales in the header section / trailer sections.

Syntax: SRW.SET_FORMAT_ORDER(SRW.main_section,srw.header_section,srw.trailer_section);

SRW.SET_FIELD : To conditionally change the field value of char/number/date datatype.

Syntax: SRW.SET_FIELD (object_id [---it is always 0], text CHAR|number NUM|date DATE);

SRW.SET_MAXROW: Specifies the maximum number of records that can be fetched from a specified query. used in case your are going to display limited number of rows from the query. it is used to increase the performance.
used only with before report trigger

syntax: SRW.SET_MAXROW (query_name CHAR, maxnum PLS_INTEGER);

SRW.SET_PRINTER_TRAY: This procedure is used to print the report from different trays. based on conditions we can print some reports in on one color pages and other from different color. etc.,

It must be used with after_report, before_report, between pages.

syntax: SRW>SET_PRINTER_TRAY('TRAY');

SRW.INTEGER_ERROR (exception): Raises when SRW package program with non integer values.

ex: Srw.message / set_max_row.

SRW.MESSAGE : It is used to display message in report execution. msg numer is of 5 digits length if u provide less than that then it will be padded with zero's.

syntax: SRW.MESSAGE(error number, message);

SRW.NULL_ARGUMENTS (EXCEPTION):
It stops execution when you pass null arguments for any of the procedures,(rw.message,srw.set_max_rows, srw.do_sql, srw.userexits,srw.run_report)....

SRW.PROGRAM_ABORT : Used to stop the report execution explicitly.

Syntax: SRW.PROGRAM_ABORT;

SRW.RUN_REPORT: This report is used to invoke RWRUN60.

* To run drill down reports (run report from action triggers)
* sending parts of report to multiple recipients.
* sending parts of reports to multiple printers.
* run multiple reports from a single driver report.

If you want to pas prameter form values to srw.run_report then it must be called after before param trigger.

syntax: SRW.RUN_REPORT(COMMAND_LINE CHAR);

SRW.RUN_REPORT_FAILURE(EXCEPTION) : Oracle stops execution when srw.run_report fails and raise this execption.

SRW.GETERR_RUN : This will get the error message associated with the latest SRW.RUN_REPORT failure.

Syntax: attribute:=srw.geterr_run;

SRW.UNKNOWN_USER_EXIT (EXECPTION) : Oracle raises this exception when we pass an invalid user exit value.

Saturday, 25 February 2017

How To Split Comma Separated values in oracle SQL

with t as
(select 'NUTHAN,RAJU,RAM,CHERRY' as STR from dual)
Select Regexp_substr(STR ,'[^,]+',1,level) from T
connect by level <=regexp_count(STR,',',1)+1;

How to convert a column into comma separated values in oracle

select rtrim(xmlagg(xmleleent('X',empno||',')).extract('//text()'),',') from emp;

Sunday, 11 December 2016

How to create a table with Blob data type and insert data

Working with Blob Datatypes

Step 1:

To work with lobs you must Create a table with columns of lob data types.

SQL> create table tab1 (col1 number(4),
2 col2 blob);

Table created.

Step 2:

Insert a row and initialize the blob with Empty_blob() so that it will generate a lob locator.

SQL> insert into tab1 values(1,empty_blob());

1 row created.

Step 3:

Create a procedure / anonymous block to write the data into the blob. Here i am creating an anonymous block. The below procedure will load the data into blob.

SQL> declare

2 v_lob blob;

3 vamt integer:=32767;

4 offst integer:=1;

5 vbuff raw(32767):=utl_raw.cast_to_raw(rpad('NUTHAN',32767,'A'));

6 begin

7 select col2 into v_lob from tab1 for update;

8 dbms_lob.write(v_lob,vamt,offst,vbuff);

9 exception

10 when others then

11 dbms_output.put_line(dbms_utility.format_error_stack);

12 end;

13 /

PL/SQL procedure successfully completed.

Step 4:

Query the table now you can see data is populated into the blob and it will show the data in binary format.

Key points to remember:

Blob's store the data only in binary format. Hence the parameters like(amount,offset,length ) are specified in terms of bytes.
The values of amount , length, offset values must not be more than maxlobsize (264).
Only Positive values of parameters are allowed. negative numbers from the tail of a lob are not allowed.
Buffer used for Blob is of Raw datatype. and max size of raw allowed is (32767).
Must lock the row of the blob that needs to be modified by using any of the procedure (Write, Append, Copy, Erase, Trim) else oracle will raise an error.

Saturday, 10 December 2016

PLSQL CODE for creating a login page and validating the credentials

--
--#############################################################################
--# Name : pa_gl_login
--# Description : This procedure generates login page for authentication
--#
--#############################################################################
PROCEDURE pa_gl_login
AS
BEGIN
HTP.p ('<head>');
HTP.p ('<STYLE TYPE="text/css">');

HTP.P (
'TH{font-family: Arial; font-size: 9pt; background-color: lightblue;}');
HTP.p ('TD{font-family: Arial; font-size: 9pt;}');
--->
HTP.p ('</STYLE>');

HTP.p ('</head>');
HTP.p ('<BODY bgcolor="#FFFFFF">');
HTP.p ('<CENTER>');
HTP.p ('<TABLE BORDER=0>');
HTP.p (' <TR>');
HTP.p (
' <TD align="center"><IMG "PATH OF YOUR IMAGE".gif" border=0></TD>'); --width=250 height=110
HTP.p (' </TR>');
HTP.p (' <TR>');
HTP.p (
' <TD align="right" ><font size="5"><b>'
|| 'XXX PAGL REJECTIONS'
|| '<b></TD>');

HTP.p ('</TR>');
HTP.p ('<TR><TD> </TD></TR>');
HTP.formOpen ('XXX_FND_WEB_PKG.Rejection_validate_data',
'post',
NULL,
NULL);
-- Title bar
-- Parameter block
HTP.p (
'<TABLE width="fixed" align="center" border="0" cellpadding="0" cellspacing="0">');
-- p_context
HTP.p ('<TR><TD width="35%" align="left"><b>USERNAME:</b></TD>');

HTP.p (
' <TD align="left"><INPUT TYPE="text" NAME="p_username" style="height:25px;width: 170px;" value="" required autocomplete="off" ></TD>');
HTP.p ('</TR>');
HTP.P ('<Tr><TD>  </TD></Tr>');

HTP.p ('<TR><TD width="35%" align="left"><b>PASSWORD:</b></TD>');
HTP.p (
' <TD align="left"><INPUT TYPE="password" NAME="p_password" style="height:25px;width: 170px;" value="" required autocomplete="off"></TD>');
HTP.p ('</TR>');
HTP.p ('<TR><TD></TD></TR>');
HTP.p ('</TABLE>');
HTP.p ('<br>');
HTP.p (
' <INPUT TYPE="submit" VALUE="Login"><INPUT TYPE="reset" VALUE="Clear">');

HTP.formClose;
HTP.p ('<br>');
HTP.PRINT (
'
<font color="#ff0000"><sup>*</sup><i>Please login with your oracle credentials</i>');

HTP.p ('</BODY>');
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'There are errors while processing the program. Please contact tecnical team'
|| SQLERRM);
END PA_gl_login;

--
--#############################################################################
--# Name : validate_login
--# Description : This procedure validates the login details

--#############################################################################
PROCEDURE Rejection_validate_data (p_username IN VARCHAR2,
p_password IN VARCHAR2)
IS
BEGIN
IF fnd_user_pkg.ValidateLogin (UPPER (p_username), p_password) = TRUE
THEN
XXX_FND_WEB_PKG.Rejection_details (UPPER (p_username));
ELSE
HTP.p ('<h1 align="center">Login failed!</h1>');
HTP.P (
'<p align="center"> Please enter a valid Username and Password.</p>');
HTP.p (
'<p align="center"><A HREF="XXX_fnd_web_pkg.pa_gl_login">Click here</A>');
HTP.p ('To Login Again.</p>');
END IF;
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'Error occured while processing the program. Please contact technical team.'
|| SQLERRM);
END Rejection_validate_data;

PLSQL code to create a web page using mod_plsql / htp/htp packages

PROCEDURE XXXXXXXXXX (p_username IN VARCHAR2)
IS
v_title VARCHAR2 (50) := 'XXX PAGL REJECTIONS ('||sys_context('USERENV','DB_NAME')||')';
v_cnt VARCHAR2 (200) := 0;
parameter LONG;

CURSOR reject_rec
IS
SELECT JPGRT.*
FROM XXX_PA_GL_REJECTION_TAB JPGRT,
PA_COST_DISTRIBUTION_LINES_ALL PCDLA
WHERE JPGRT.V_EXPENDITURE_ITEM_ID = PCDLA.EXPENDITURE_ITEM_ID
AND JPGRT.Approver LIKE '%' || p_username || '%'
AND JPGRT.resolved_flag = 'N'
ORDER BY JPGRT.org_id;

BEGIN
-- HTP.P ('<!DOCTYPE html>');
HTP.p ('<TITLE>' || v_title || '</TITLE>');
HTP.p ('<head>');
HTP.p ('<STYLE TYPE="text/css">');
HTP.P (
'TH{font-family: Arial; font-size: 9pt; background-color:lightblue;}');
HTP.p ('TD{font-family: Arial; font-size: 9pt; font-weight: normal;}');
---------------->
HTP.p('
a {text-decoration:none;}');
HTP.p ('</STYLE>');
HTP.p ('</head>');
HTP.p ('<BODY bgcolor="#FFFFFF">');
HTP.p ('<CENTER>');
HTP.p (' <H1 align="center" bgcolor="#E6E6FA">' || v_title || '</H1>');

HTP.p ('<hr>');
HTP.p (
'<table width = 100%><tr><td align = left><font color="#ff0000"><sup>*</SUP><i>Choose only when you want to approve for any one of the proposed fix.</i></td><td align = right><font color="#ff0000"><sup>*</SUP><i>Enter key words related to your search (Example: ORG_ID, EMPLOYEE NUMBER etc.,)</i></td></tr></table>');

HTP.p (
'<form id="testForm" enctype="multipart/form-data" method = "post">');

--- HTP.p ('<CENTER>');
-- Title bar
-- Parameter block

HTP.p (
'<table width = 100% ><tr><td align= left><INPUT TYPE="checkbox" class="checkbox1" autocomplete="off" name="checked"> UPDATE ATTRIBUTE4
<INPUT TYPE="checkbox" class="checkbox2" autocomplete="off" name="checked"> UPDATE EXPENDITURE DATE

</td><td align= right>Filter:
<input type=text style="width: 200px;" placeholder="Enter text to search. . .    " id="searchTerm" class="search_box" onkeyup="doSearch()" />
</td></tr></table>');
------------------------------------------
HTP.p (
'<TABLE id="Table001" width=100% border=1 cellpadding="0" cellspacing="0">');

HTP.p ('<thead><TR><Th align="center"></Th>');
-------- HTP.p ('<Th align="center"><b>ORG_ID</b></Th>');
HTP.p ('<Th align="center">EMPLOYEE<br>NUMBER</Th>');
HTP.p ('<Th align="center">EMPLOYEE<br>FULLNAME</Th>');
HTP.P ('<Th align="center">EXPENDITURE_ITEM_ID</Th>');
HTP.P ('<Th align="center">CURRENT ATTRIBUTE4</Th>');
HTP.P ('<Th align="center">PROPOSED ATTRIBUTE4</Th>');
HTP.P ('<Th align="center">OLD_EXPENDITURE_DATE</Th>');
HTP.P ('<Th align="center">NEW_EXPENDITURE_DATE</Th>');
----- HTP.P ('<Th align="center">COMMENTS</Th>');
HTP.p ('</TR></thead>');
HTP.p ('<tbody>');

FOR i IN reject_rec
LOOP
---- p_context

HTP.p ('<TR>');
HTP.p (
' <TD align="center"><INPUT TYPE="checkbox" class="checkbox" autocomplete="off" name='
|| i.v_expenditure_item_id
|| '></TD>');

------------- HTP.p ('<TD align="center"><b>' || i.ORG_ID || '</b></TD>');
HTP.p ('<TD align="center"><b>' || i.V_EMP_ID || '</b></TD>');
HTP.p ('<TD align="center"><b>' || i.EMPLOYEE_NAME || '</b></TD>');
HTP.P (
' <Td align="center"><b><a class=text href="XXX_FND_WEB_PKG.Transaction_details?p_expenditure_item_id='
|| i.V_EXPENDITURE_ITEM_ID||'&p_org_id='||i.org_id
|| '" target="_blank;" style="text-decoration:none;">'
|| i.V_EXPENDITURE_ITEM_ID
|| '</a></b> </TD>');

HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.OLD_ATTRIBUTE_VALUE), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.NEW_ATTRIBUTE_VALUE), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.OLD_EXPENDITURE_DATE,'DD-MON-YYYY'), ' ')
|| '</b></TD>');
HTP.P (
' <Td align="center"><b>'
|| NVL (TO_CHAR (i.NEW_EXPENDITURE_DATE,'DD-MON-YYYY'), ' ')
|| '</b></TD>');

------ HTP.p ('<TD align="center"><b>' || i.COMMENTS || '</b></TD>');
HTP.p ('</TR>');

v_cnt := 1;
END LOOP;

HTP.p ('</tbody></TABLE>');

-- HTP.p (
--- ' <TD align="left"><INPUT TYPE="INTEGER" NAME="parameter" style="height:20px;width: 170px;" value=" " autocomplete="off" ></TD>');

IF v_cnt = 0
THEN
HTP.p ('<br>');
HTP.PRINT ('<font size=5>There are no rejections to display');
ELSE
HTP.p ('<br>');
HTP.p (
'<input type="button" onclick= "submitfn()" width="50px" value=Approve></button>');

HTP.p (
'<input type="button" onclick= "submitnn()" width="50px" value=Reject></button>');
HTP.p ('<input type="hidden" id="refreshed" value="no">'); ------formaction="XXX_FND_WEB_PKG.pa_gl_Reject_Rejections" Value = "Reject"
END IF;

HTP.formClose;

HTP.P ('<form id="testForm1" method = "post">');
HTP.p (
'<INPUT TYPE="hidden" NAME="p_username" required autocomplete="off" value='
|| p_username
|| '>');
HTP.p ('<INPUT TYPE="hidden" name="P1" id="P1_id">');
HTP.p ('<INPUT TYPE="hidden" name="P2" id="P2_id">');
HTP.p ('<INPUT type="hidden" name="parameter" id="parameter_id">');
HTP.p ('</form>');

HTP.script (
'
function doSearch() {
var searchText = document.getElementById("searchTerm").value;
var targetTable = document.getElementById("Table001");
var targetTableColCount;

for (var rowIndex = 0; rowIndex < targetTable.rows.length; rowIndex++) {
var rowData =" ";

if (rowIndex == 0) {
targetTableColCount = targetTable.rows.item(rowIndex).cells.length;
continue;
}

for (var colIndex = 0; colIndex < targetTableColCount; colIndex++) {
rowData += targetTable.rows.item(rowIndex).cells.item(colIndex).textContent;
}

if (rowData.indexOf(searchText) == -1)
targetTable.rows.item(rowIndex).style.display = "none";
else
targetTable.rows.item(rowIndex).style.display = "table-row";
}
}

if (!document.getElementsByClassName) {
document.getElementsByClassName = function (cn) {
var rx = new RegExp("(?:^|\\s)" + cn+ "(?:$|\\s)");
var allT = document.getElementsByTagName("*"), allCN = [],ac="", i = 0, a;
while (a = allT[i=i+1]) {
ac=a.className;
if ( ac && ac.indexOf(cn) !==-1) {
if(ac===cn){ allCN[allCN.length] = a; continue; }
rx.test(ac) ? (allCN[allCN.length] = a) : 0;
}
}
return allCN;
}
}

function submitfn() {
var boxes = document.getElementsByClassName("checkbox");
var box1 = document.getElementsByClassName("checkbox1");
var box2 = document.getElementsByClassName("checkbox2");
var checked = [];
var checked1 = [];
var checked2 = [];
for(var i=0; boxes[i]; ++i){
if(boxes[i].checked){
checked.push(boxes[i].name);
}
}
for(var i=0; box1[i]; ++i){
if(box1[i].checked){
checked1.push(box1[i].name);
}
}
for(var i=0; box2[i]; ++i){
if(box2[i].checked){
checked2.push(box2[i].name);
}
}

var parameter = checked.join();
var P1 = checked1.join();
var P2 = checked2.join();
form=document.getElementById("testForm1");
document.getElementById("parameter_id").value = parameter;
document.getElementById("P1_id").value = P1;
document.getElementById("P2_id").value = P2;
form.action ="XXX_FND_WEB_PKG.pa_gl_update_Rejections";

if (confirm("Do you want to submit")) {
form.submit();
}
else
{
return false;
}
}

function submitnn() {
var boxes = document.getElementsByClassName("checkbox");
var box1 = document.getElementsByClassName("checkbox1");
var box2 = document.getElementsByClassName("checkbox2");
var checked = [];
var checked1 = [];
var checked2 = [];
for(var i=0; boxes[i]; ++i){
if(boxes[i].checked){
checked.push(boxes[i].name);
}
}
for(var i=0; box1[i]; ++i){
if(box1[i].checked){
checked1.push(box1[i].name);
}
}
for(var i=0; box2[i]; ++i){
if(box2[i].checked){
checked2.push(box2[i].name);
}
}

var parameter = checked.join();
var P1 = checked1.join();
var P2 = checked2.join();
form=document.getElementById("testForm1");
document.getElementById("parameter_id").value = parameter;
document.getElementById("P1_id").value = P1;
document.getElementById("P2_id").value = P2;
form.action ="XXX_FND_WEB_PKG.pa_gl_Reject_Rejections";




if (confirm("Do you want to submit")) {
form.submit();
}
else
{
return false;
}
}',
'javascript');

HTP.p ('</BODY>');
EXCEPTION
WHEN OTHERS
THEN
HTP.PRINT (
'Error occured while processing the program. Please contact technical team.'
|| SQLERRM);
END XXXXXXXXXX ;