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()])?>      

Xml Publisher ( How to Delete Data Definition & Template from Back end )

Script for deleting the datadefinition
------------------------------------------------>
BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('INV','NRGINTR');
END;
--------------------------------------------------------
--------------------------------------------------------
script for deleting the template
------------------------------------------------------>
BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('INV','NRGINTR');
END;

How to Rigister Concurrent program from Backend

--- Creating Excecutables
Begin
      FND_PROGRAM.EXECUTABLE
      (executable           => 'XXHFTUPGITEMLDR'
      ,application          => 'Harbor Freight Custom Application'
      ,short_name           => 'XXHFTUPGITEMLDR'
      ,description          => 'SQLLOADER Item Program from 11i to R12'
      ,execution_method     => 'SQL*Loader'
      ,execution_file_name  => 'XXHFTUPGITEMLDR'
      ,subroutine_name      => ''
      ,icon_name            => ''
      ,language_code        => 'US'
      ,execution_file_path  => '');
end;
---- Creating Concurrent Programs ----
Begin

 FND_PROGRAM.REGISTER
       (program                  => 'HFT Item Conversion loader'                ---  Concurrent Program Name
       ,application              => 'Harbor Freight Custom Application'       ---  Application Name
       ,enabled                  => 'Y'                                                           ---  Enabled Flag
       ,short_name               => 'XXHFTUPGITEMLDR'                      ---  Short Name of Conc Program
       ,description              => ''                                                             ---  Description
       ,executable_short_name    => 'XXHFTUPGITEMLDR'               ---  Executable of the Conc Program
       ,executable_application   => 'Harbor Freight Custom Application'       ---  Executable application
       ,execution_options        => ''
       ,priority                 => ''
       ,save_output              => 'Y'
       ,print                    => 'Y'
       ,cols                     => ''
       ,rows                     => ''
       ,style                    => ''
       ,style_required           => 'N'
       ,printer                  => ''
       ,request_type             => ''
       ,request_type_application => ''
       ,use_in_srs               => 'Y'
       ,allow_disabled_values    => 'N'
       ,run_alone                => 'N'
       ,output_type              => 'Text'                                    ----  Out put format
       ,enable_trace             => 'N'
       ,restart                  => 'Y'
       ,nls_compliant            => 'Y'
       ,icon_name                => ''
       ,language_code            => 'US'
       ,mls_function_short_name  => ''
       ,mls_function_application => ''
       ,incrementor              => '');
end;

How to add Parameters to Concurrent Program
 Begin
 fnd_program.parameter
 (program_short_name =>  'xxhftupgitemldr'                 ---  Short Name of Conc Program 
 ,application => 'harbor freight custom application'         ---  Application Name
 ,sequence => '1'                                                           ---  Sequnce Number
 ,parameter => 'path'                                                      ---  Parameter Name
 ,description => 'path of data file.'                                   ---  Paramter Description
 ,enabled => 'y'                                                              ---  Enabled Flag
 ,value_set => 'ar_varchar_150'                                      ---  Value Set Name
 ,default_type => ''
 ,default_value => ''
 ,required => 'y'                                                                 ---  Required flag ( Mandatory or not)
 ,enable_security => 'n'
 ,range => ''
 ,display => 'y'
 ,display_size => '15'
 ,description_size => '50'
 ,concatenated_description_size=> '25'
 ,prompt => 'path '
 ,token => '');
end;

How to add Concurrent program to request group
Begin
 FND_PROGRAM.ADD_TO_GROUP
           (program_short_name  => 'XXHFTUPGITEMLDR'                 ---  Short Name of Conc Program 
           ,program_application =>  'Harbor Freight Custom Application'   ---  Application Name
           ,request_group       => 'All Inclusive GUI'                                   ---  Request group name
           ,group_application   => 'Inventory');                                         ---  Requst group Application Name
end;

How to delete Concurrent program & Executable
 IF (FND_PROGRAM.PROGRAM_EXISTS
             (program                  => 'XXHFTUPGITEMLDR'
             ,application              => 'Harbor Freight Custom Application'))
         THEN
              FND_PROGRAM.DELETE_PROGRAM('XXHFTUPGITEMLDR', 'HFT'); 
                                                                   ---  Short Name of Conc Program  , ---  Application Name
              FND_PROGRAM.DELETE_EXECUTABLE('XXHFTITEMLDR', 'HFT');   
                                                       ----  Short Name of Excutable Program  , ---  Application Name
   END IF;

Locks on Object & Remove Locks

Query to Knoe locks on the object:


SELECT c.owner, c.object_name, c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;


Example :  To remove locks on object:

ALTER SYSTEM KILL SESSION 'sid,serial#';

 ALTER SYSTEM KILL SESSION '155,52745';

How 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
ORDER by f.actual_completion_date-f.actual_start_date desc;

How to know version of Oracle Applications from backend

Printing Oracle Apps versions:

Use the below select statement to know the currently installed version of all your modules.

SELECT substr(a.application_short_name, 1, 5) code,

       substr(t.application_name, 1, 50) application_name,

       p.product_version version

FROM   fnd_application a,

       fnd_application_tl t,

       fnd_product_installations p

WHERE  a.application_id = p.application_id

AND    a.application_id = t.application_id

AND    t.language = USERENV('LANG');

Sample Output:

CODE   APPLICATION_NAME   VERSION
------------------------------------------------
AP       Payables                    12.1.0
PO       Purchasing                 12.1.0
AR       Receivables                12.1.0

Query for Display Calander.. in Oracle

    Query for Display Calander..                                                                 
                                                                     
                                                                     
                                             
SELECT   LPAD (MONTH, 20 - (20 - LENGTH (MONTH)) / 2) MONTH, "Su", "Mo", "Tu",
         "We", "Th", "Fr", "Sa"
    FROM (SELECT   TO_CHAR (dt, 'fmMonthfm YYYY') MONTH,
                   CASE
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Dec%'
                      AND TO_CHAR (dt + 1, 'iw') = '01'
                         THEN '53'
                      WHEN TO_CHAR (dt, 'fmMonthfm YYYY') LIKE 'Jan%'
                      AND TO_CHAR (dt + 1, 'iw') = '53'
                         THEN '.5'
                      ELSE TO_CHAR (dt + 1, 'iw')
                   END week,
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '1', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Su",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '2', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Mo",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '3', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Tu",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '4', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "We",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '5', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Th",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '6', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Fr",
                   MAX (DECODE (TO_CHAR (dt, 'd'),
                                '7', LPAD (TO_CHAR (dt, 'fmdd'), 2)
                               )
                       ) "Sa"
              FROM (SELECT TRUNC (SYSDATE, 'y') - 1 + ROWNUM dt
                      FROM all_objects
                     WHERE ROWNUM <=
                                ADD_MONTHS (TRUNC (SYSDATE, 'y'), 12)
                              - TRUNC (SYSDATE, 'y'))
          GROUP BY TO_CHAR (dt, 'fmMonthfm YYYY'), TO_CHAR (dt + 1, 'iw')
          order by TO_CHAR (DT + 1, 'iw'))
ORDER BY TO_DATE (MONTH, 'Month YYYY'), TO_NUMBER (week) ;