Xml Publisher Basics

Xml Publisher Basics

1) How wil u set sl.no in xml reports?
  we wil use postion attribute here
 syntax: <?position()?>
2) How wil u give pagebreaks after specific records(rows)?
 we wil use <?split-by-page-break?>
 syntax: <?if:position() mod 5=0 ?><?split-by-page-break:?><?end if?>
    ------  we should not use split-by -page-break in table.
3)  How 2 Avoid the page break inbetween the table
           Navg: In the table properties we wil select row tab
   in that we we wil select check box Allow row 2 break across the page
4)  How wil give Headers to all pages?
 by using start body and end body
 syntax: <?start-body?>
  <?end body?>
5)  Xml publisher can have Only one top level elements
6)  How 2 give count of elemnts in last page of the report?
 syntax: <?start@last-page:body?>
               count attrbute1
               <?end body?>
7)  How to appy colours alternatively?
 syntax: <?if@row:position() mod 2=0?>  <xsl:attribute name="background-color"  xdofo:ctx="incontext">silver</xsl:attribute><?end if?>
8)   How wil  u set Heading per each page?
           Navg: In the table properties we wil select row tab ->
  in that we we wil select check box repeat header for each page
9) Navigation for landscape
           Navg:  pagelayout ->page setup ->landscape(to the text----only 4 tat page)
10) How 2 apply colour in Background
           Navg: borders and shading -> shading -> fill
11) How 2 remove Boarders
           Navg: borders and shading -> borders -> (remove all sides except down)
12) Check box (check box) <?ename>kasi?>
14) Drop dowlist  select drop dronlist give  list of elements then go for add help text
15. If we want to hilight one cell then we wil write syntax like
  <?if:debit>1000?><xsl:
  attribute xdofo:ctx=
  "block" name="backgroundcolor">
  red</xsl:
  attribute><?end if?>
16).we wil  do running totals like this
 <?xdoxslt:set_variable($_
 XDOCTX, ’RTotalVar’, xdoxslt:
 get_variable($_XDOCTX,
 ’RTotalVar’) + INVAMT)?>
17) if i want to calclulate totals --------  <?add-page-total:variablename;'column_name'?>
       to display those columns               <?show-page-total:ct;’$#,##0.00’;’ ($#,##0.00)’?>
18.we can do sorting like this
syn:-<?sort:element name?>
ex:--<?sort:vendor_name?><?sort:vendor_num?>
18. set autotrace traceonly -to trace the query without data
19.select to_char(to_date('10/25/2008','MM/DD/YYYY'),'dd-mon-yyyy') from dual
20. we can execute pl/sql  proceures with EXEC or CALL command;

21) to remove extra 0's in xml
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"><?CP_COST?></fo:bidi-override>
22) sum of sal
<?sum(SAL[text()])?>       

Query to find procesing time of concurrent program

Query to find the concurrent program processing time:

SELECT f.request_id,
         pt.user_concurrent_program_name user_concurrent_program_name,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
         FLOOR (
            ( (f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60)
            / 3600)
         || ' HOURS '
         || FLOOR (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600)
               / 60)
         || ' MINUTES '
         || ROUND (
               ( (  (f.actual_completion_date - f.actual_start_date)
                  * 24
                  * 60
                  * 60)
                - FLOOR (
                     (  (f.actual_completion_date - f.actual_start_date)
                      * 24
                      * 60
                      * 60)
                     / 3600)
                  * 3600
                - (FLOOR (
                      ( (  (f.actual_completion_date - f.actual_start_date)
                         * 24
                         * 60
                         * 60)
                       - FLOOR (
                            (  (f.actual_completion_date - f.actual_start_date)
                             * 24
                             * 60
                             * 60)
                            / 3600)
                         * 3600)
                      / 60)
                   * 60)))
         || ' SECS '
            time_difference,
         DECODE (
            p.concurrent_program_name,
            'ALECDC', p.concurrent_program_name || '[' || f.description || ']',
            p.concurrent_program_name)
            concurrent_program_name,
         DECODE (f.phase_code,
                 'R', 'Running',
                 'C', 'Complete',
                 f.phase_code)
            Phase,
         f.status_code
    FROM apps.fnd_concurrent_programs p,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE     f.concurrent_program_id = p.concurrent_program_id
         AND f.program_application_id = p.application_id
         AND f.concurrent_program_id = pt.concurrent_program_id
         AND f.program_application_id = pt.application_id
         AND pt.language = USERENV ('Lang')
         AND f.actual_start_date IS NOT NULL
         AND USER_CONCURRENT_PROGRAM_NAME LIKE 'XXXtest%'
ORDER BY f.actual_completion_date - f.actual_start_date DESC;

Generating Random Password

This function is used to generate random password for user

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

select xxhcc_generatePassword(5) from dual;

Ans:- E9K8J6Y3H5

Reset APPS User Password from Back End

Reset APPS User Password from Back End 

Ask user for reset on first logon

This functionality is ask user to reset the password after logon

begin
fnd_user_pkg.updateuser(
x_user_name => ‘username’
, x_owner => ‘CUST’
, x_unencrypted_password => ‘welcome1012′,
x_password_date => to_date(’2′,’J’)
);
commit;
end;

Does not ask user for reset on first logon

-- Change password of TEST_USER to oracle123 (does not ask for reset on first logon)
BEGIN
   fnd_user_pkg.updateuser (x_user_name                   => 'TEST_USER',
x_owner => 'CUST',
x_unencrypted_password => 'oracle123',
                            x_end_date                    => fnd_user_pkg.null_date,
x_password_date => SYSDATE - 10,
                            x_password_accesses_left      => 10000,
                            x_password_lifespan_accesses  => 10000,
                            x_password_lifespan_days      => 10000
                           );
   COMMIT;
END;
Note: If the password is changed from the backend then the user will not be prompted to change his/her password on login screen.
The previous code uses a public API. We can use an Oracle private API to reset the password as well.

-- Change TEST_USER Password from PL/SQL
SET serverout on

DECLARE
   return_value   VARCHAR2 (200);
BEGIN
return_value := fnd_web_sec.change_password ('TEST_USER', 'oracle123');
   DBMS_OUTPUT.put_line ('Result ' || return_value);   -- Y Means Success Else Fail.
   COMMIT;
END;
You also execute the API as a SQL query


select fnd_web_sec.validate_login('SA1','etihad123') from dual;

Reseting User Password and Send Email to User

This functionality is used to reset the existing user password from back end and send password details to the user 

CREATE OR REPLACE FUNCTION xxhcc_generatePassword(LENGTH IN NUMBER)
RETURN VARCHAR
IS
tmpChr VARCHAR2(1);
tmpPswd VARCHAR2(32767);
tmpnum NUMBER(1);
BEGIN

FOR i IN 1 .. LENGTH
LOOP

SELECT CHR(ROUND(dbms_random.value(48,57),0)) INTO tmpnum FROM dual;
SELECT CHR(ROUND(dbms_random.value(65,90),0)) INTO tmpChr FROM dual;

tmpPswd:=tmpPswd||tmpChr||tmpnum;
END LOOP;

RETURN tmpPswd;
END;

then populated a temp table with the user names i wanted to change and ran the following code:

declare
l_pass varchar(10);
v_notification_id NUMBER;
l_user varchar(100);
CURSOR ss_user IS

SELECT username
FROM
xxhcc.xxhcc_ssuser;
line_rec ss_user%ROWTYPE;

begin

UPDATE FND_USER
SET end_date= NULL
WHERE user_name In (SELECT * FROM xxhcc.xxhcc_ssuser);

FOR line_rec IN ss_user
LOOP
l_user := line_rec.username;
select xxhcc_generatePassword(5) as password into l_pass from dual;
fnd_user_pkg.updateuser(
x_user_name => l_user
, x_owner => ‘CUST’
, x_unencrypted_password => l_pass,
x_password_date => to_date(‘2′,’J’));

—code to send mail
v_notification_id := wf_notification.send (ROLE => l_user,
msg_type => ‘UMXUPWD’,
msg_name => ‘MSG_PASSWORDRESET’,
priority => 1
);
— And set message attributes.
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘PASSWORD’,
avalue => l_pass
);
wf_notification.setattrtext (nid => v_notification_id,
aname => ‘#HIDE_MOREINFO’,
avalue => ‘N’);

end loop;

commit;
end;