Wednesday 2 May 2012

SQLPLSQL,REPORTS,XML:FAQS

===================

=======================
Key Interface Table For Different Modules In Oracle Applications



 Following are the Key Interface Tables for different modules in Oracle Applications [Oracle EBS]:


ORACLE FINANCIALS – PAYABLES - INVOICES
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

ORACLE FINANCIALS – RECEIVABLES - CUSTOMERS
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE
==================XML====================================

ORACLE FINANCIALS – RECEIVABLES - INVOICES
RA_INTERFACE_LINES_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL

ORACLE FINANCIALS – RECEIVABLES – LOCK BOX
AR_PAYMENTS_INTERFACE_ALL

ORACLE FINANCIALS – RECEIVABLES - TAX
AR_TAX_INTERFACE

ORACLE FINANCIALS - CASH MANAGEMENT – BANK STATEMENTS
CE_STATEMENT_HEADERS_INT_ALL
CE_STATEMENT_LINES_INTERFACE

ORACLE FINANCIALS - FIXED ASSETS
FA_INV_INTERFACE

ORACLE FINANCIALS - GENERAL LEDGER – JOURNAL ENTRY
GL_INTERFACE

ORACLE MANUFACTURING – COST MANAGEMENT
CST_PC_ITEM_COST_INTERFACE
CST_PC_COST_DET_INTERFACE

ORACLE MANUFACTURING - INVENTORY
MTL_REPLENISH_HEADERS_INT
MTL_REPLENISH_LINES_INT
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TRANSACTIONS_INTERFACE

ORACLE MANUFACTURING – PURCHASING – PURCHASE ORDERS
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE

ORACLE MANUFACTURING – PURCHASING - RECEIVING
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE

ORACLE MANUFACTURING - ORDER ENTRY – SALES ORDERS
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE

ORACLE MANUFACTURING - MASTER SCHEDULING/MRP
MRP_FORECAST_INTERFACE
MRP_SCHEDULE_INTERFACE

ORACLE MANUFACTURING - SHOP FLOOR MANAGEMENT
WSM_LOT_JOB_INTERFACE
WSM_STARTING_LOTS_INTERFACE
WSM_LOT_MOVE_TXN_INTERFACE

ORACLE MANUFACTURING - QUALITY
QA_RESULTS_INTERFACE

ORACLE MANUFACTURING - WORK IN PROCESS
WIP_MOVE_TXN_INTERFACE
CST_COMP_SNAP_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

=================================

==================================
Oracle Reports Interview Questions & Answers Part-1



Questions:
•Interview Questions on Oracle Reports Part-1
•Oracle Reports - Technical Interview Questions & Answers
•D2K Technical Interview Questions And Answers Part-1


1. How many different layouts are available in Reports?

Ans: There are eight different layout formats:
1. Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group


2. How many different triggers are available in Report?

Ans: There are five types of triggers in report 6i
1) Before report trigger
2) After report trigger
3) Before Parameter trigger
4) After parameter trigger
5) Between pages trigger
 
3.What is the Firing sequence of report trigger?

Ans: The reports triggers are fired in the following sequence.


*Before Parameter Form
* After Parameter Form
* Before Report
* Between Pages
* After Report


4. What is the difference between After Parameter Trigger and Before Report Trigger?

Ans:
After parameter Trigger: It will fire after the parameter form is displayed.here we can do validation on parameter values.


Before Report Trigger: It will fire before the report is executed and after the query is parsed and date is fetched.


5. What is the Format Trigger?

Ans: Format Trigger is a PL/SQL function. This trigger is going to fire before an object is printed in report output. it return boolean-true then go to print -false then don't print.


6. What happens when Flex mode is on ?

When flex mode is on, reports automatically resizes the parent when the child is resized.


7. What happens when confine mode on ?

When confine mode is on, the object cannot be moved outside its parent in the layout. 


8. What is a lexical parameter?

Lexical Parameter is used to replace the where, order by conditions at run time.


9. What are bind variables?

Bind variables are used in oracle reports  for replacing the single parameter in the select statement.


10. What is the minimum number of groups required for a matrix report?

The minimum of groups required for a matrix report are 4


11. What is the use of an Anchor in Reports?

Anchor is used to make fixed distance between two objects in Reports Layout.


12. What is the difference between Frame and Repeating Frame?

Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example a frame might be used to surround all objects owned by a group to surround column headings or to surround summaries.


When you default the layout for a report Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.


Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.


When you default the layout for a report Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.


13. What are different types of column in reports?

There are three types of columns in Oracle report:
1) Placeholder Column: Placeholder column is used to store a value for a variable.
2) Formula Column:  Used For doing mathematical calculations and returning one value
3) Summary Column: The summary columns perform aggregate functions such as SUM, COUNT, MAX, MIN, AVG, and the like.


14. Can u have more than one layout in report?


It is possible to have more than one layout in a report by using the additional layout option in the layout editor.
===================
====================


1. What are Placeholder Columns ?


A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).






2. What are the various Module Types in Reports ?




A You can build three types of modules with Oracle Reports:




* external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules




* external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules




* reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules






3. What are Physical and Logical pages in Reports ?




A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.






Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.


Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.


4. What are the various page layout sections in Oracle Reports ?




A report has three sections :
•the report header pages,
•report body/margin pages,
•and report trailer pages.
5. What are various types of parameters ?




There are two types of parameters:


• default (system parameters)
• user-created (bind and lexical parameters)
6. How do you reference parameters and columns in reports ?




There are two ways to reference a parameter in Oracle reports:




* As bind references


* As lexical references




7. What are Bind Referencing and Lexical Referencing ?




Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.






Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.


8. What are the Types of Matrix report ?




 1. Single query
 2. Multi query
 3. Nested Query
 4. Matrix Break


9. What are widow lines ?


Widow lines are the minimum number of lines of the boilerplate text or field that should appear on the logical page where the text starts to print. If the number of lines specified for this property cannot fit on the logical page, then all lines of the boilerplate are moved to the next page.




10. What are widow records ?


Widow records are the minimum number of instances (records) that should appear on the logical page where the repeating frame starts to print. If the number of instances specified for this property cannot fit on the logical page where the repeating frame is initially triggered to print, then the repeating frame will start formatting on the next page.


11. What is 'page protect' property for objects ?


Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.


12. What is the 'Print Condition Type' property ?


'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.


13. What is the 'Print Condition Object' property ?


'Print Condition Object' property specifies the object on which to base the Print Condition Type of the current object. For example, if you specify a Print Condition Type of All and a Print Condition Object of Anchoring Object, the current object will be triggered to print on every logical page on which its anchoring object (parent object) appears.


14. What are the various values of the 'Print Condition Object' property in Reports ?


The various values are :


•Anchoring Object : Anchoring Object is the parent object to which the current object is implicitly or explicitly anchored.
•Enclosing Object : Enclosing Object is the object that encloses the current object.
15. What is the horizontal of vertical sizing property of objects ?


Horizontal of vertical sizing property specifies how the horizontal or vertical size of the object may change at runtime to accommodate the objects or data within it.


=============
1.What are the various types of reports ?


There are 8 Types of reports in oracle report.


1.Tabular
2. Form Like
3. Form Letter
4. Mailing Label
5. Group Left
6. Group Above
7. Matrix
8. Matrix with group







2. What are Anchors in Oracle Reports?

An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

3. What are the various types of anchors in Reports ?

A There are two types of anchors in Oracle Reports:
* implicit (anchors that Oracle Reports creates when a report is run)
* explicit (anchors you create)

4. What is an Implicit Anchor?

Implicit Anchors : At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.



5. What is an Explicit Anchor?



Explicit Anchors : Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog
6. What are the various report triggers ? What is their order of firing ?

A There are eight report triggers. Of these there are five global triggers called the Report Triggers. They are fired in the following order :
* Before Parameter Form
* After Parameter Form
* Before Report
* Between Pages
* After Report

7. Apart from the global report triggers what are the other triggers used in Oracle Reports?


Apart from the Five Global Report Triggers, there are three other types of triggers :

* Validation Triggers
* Format Triggers
* Action Triggers



8. What is Before Form Trigger? What is the importance of Before Form Trigger?



Before Form : It Fires before the Runtime Parameter Form is displayed. Using this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).

9. What is After Form Trigger? What is the importance of After Form Trigger?


After Form : It Fires after the Runtime Parameter Form is displayed. Using this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

10. What is Before Report Trigger? What is the importance of Before Report Trigger?


Before Report : Fires before the report is executed but after queries are parsed and data is fetched.


11. What is Between Pages Trigger? What is the importance of Between Pages Trigger?

Between Pages : Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)
12. What is After Report Trigger? What is the importance of After Report Trigger?


After Report : Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.


13. What is Validation Trigger? What is the importance of Validation  Trigger?


Validation Triggers : Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.


14. What is Format Trigger? What is the importance of Format Trigger?

Format Triggers : Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

15. What is Action Trigger? What is the importance of Action Trigger?


Action Triggers : Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.
=========
1.What are the various values of the horizontal of vertical sizing property ?


The various values are :
Contract : Contract means the vertical size of the object decreases, if the formatted objects or data within it are short enough, but it cannot increase to a height greater than that shown in the editor. Note : Truncation of data may occur. (You can think of this option as meaning "only contract, do not expand.")


Expand : Expand means the vertical size of the object increases, if the formatted objects or data within it are tall enough, but it cannot decrease to a height less than that shown in the editor. (You can think of this option as meaning "only expand, do not contract.")


Fixed : Fixed means the height of the object is the same on each logical page, regardless of the size of the objects or data within it. Note : Truncation of data may occur. The height of the object is defined to be its height in the editor.


Variable : Variable means the object may expand or contract vertically to accommodate the objects or data within it (with no extra space), which means the height shown in the editor has no effect on the object's height at runtime.


2. How do you display a message in reports ?


Using SRW.Message


3. What are the various values of 'Print Panel Order' property of report ?


The various values are :
•Across/Down : Across/Down means the physical pages of the report body will print left-to-right then top-to-bottom.
•Down/Across : Down/Across means the physical pages of the report body will print top-to-bottom and then left-to-right.
4. What is the 'Print Direction' Property of Repeating frames ?


'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.


5. What are the various values of the 'Print Direction' Property of Repeating frames ?
Following are the various values :
•Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.
•Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.
•Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.
•Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.
6. What is the 'Keep with Anchoring Object' object property ?


'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.


7. What is 'Page Break Before' object property ?


'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.


8. What is 'Page Break After' object property ?


'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.


9. What is the 'Break Order' property of columns ?


The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.


10. What are the various types of links ?


The Data Link tool draws a link between a parent group and a child query. Creating a link is a drag and drop operation. Clicking and dragging from one column to another creates a link between those two columns (column to column link). Clicking and dragging from one query to another creates all possible links between columns selected by the queries based on database constraints (query to query link). Clicking and dragging between two groups creates a group-to-group link (i.e., a link with no columns).


11. Name some of the procedures in the SRW package ?


* SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report


12. What are the various report layout regions ?
 There are three report regions in the Layout editor :


• header
• body/margin
• trailer
13. In Brief Describe the various report layout regions?


Header : The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.


Body/Margin : The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.
A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.


Trailer : The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.


14. What is more efficient : Maximum rows or Group Filter ?


Maximum Rows in the Query property sheet restricts the number of records fetched by the query. A group filter determines which records to include and which records to exclude. Since Maximum Rows actually restricts the amount of data retrieved, it is faster than a group filter in most cases. If you are using a Filter of Last or Conditional, Oracle Reports must retrieve all of the records in the group before applying the filter criteria. As a result, Maximum Rows or a Filter of First is faster.
===============
1. What is SRW Package?
The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc.


2. What is SRW package and some procedures in SRW?
It is the standard reports package and it has many procedures like USER_EXITS, DO_SQL, RUN_REPORT, MESSAGE,TRACE, BREAK, SET_ATTR.


3. Where in reports do you set the context information (like org_id)?
SRW.INIT


4. What is User Parameters?
Parameter, which is created by user. For to restrict values with where clause in select statement. We can use Lovs in  user parameter with static and Dynamic Select Statement.


5. What is System Parameters?
These are built-in parameters provided by Oracle corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., " $?).
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
Screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").


6. How can you print barcodes in oracle reports?
By installing the Barcode Font and using the Chart field in the Layout.


7. What are Format triggers?
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether for Headings, repeating frames, field and boilerplate object. Example:
i) To format the max (Sal) for particular department.
ii) To format the Sal column with a Dollar ($) prefix.
iii) To format Date formats….etc


8. What is Data Model?
Data Model is logical group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups and then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report.
9. How do you call a concurrent program or another report from a report?
We can use FND_SUBMIT.REQUEST() to call a concurrent program from a report. Use SRW.RUN_REPORT() to run a report directly without registering it as a concurrent program.


10. What is Layout model?
Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.


11. What is Live Previewer?
The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.


12. What is Parameter Form
The Parameter Form view is the work area in which you define the format of the report's Runtime Parameter Form. To do this, you define and modify parameter form objects (fields and boilerplate).When you run a report, Report Builder uses the Parameter Form view as a template for the Runtime Parameter Form. Fields and boilerplate appear in the Runtime Parameter Form exactly as they appear in the Parameter Form view. If you do not define a Runtime Parameter Form in the Parameter Form view, Report Builder displays a default Parameter Form for you at runtime.


13. What are user exits in reports and name a few?
User exits provided a way to pass control from Reports Builder to a program you have written, which performs some function, and then returns control to Reports Builder. Ex: SRW.DO_SQL, SRW.USER_EXIT.


14. What is Group?
Groups are created to organize the columns in your report. When you create a query, Report Builder automatically creates a group that contains the columns selected by the query. You create additional groups to produce break levels in the report, either manually or by using the Report Wizard to create a group above or group left report.


15. What is Template?
Templates define common characteristics and objects that you want to apply to multiple reports. For example, you can define a template that includes the company logo and sets fonts and colors for selected areas of a report.
Creation of Template: In Report editor , open a existing Template or Create a new Template and save it concerned directory. Then Edit CAGPREFS.ORA File , and Specify which type of Template are u going to develop.
Ex. Tabular, form, matrix Then give your developed template *.tdf file name.
Develop Report with Newly developed Template.


16. How can you display one record per page?
Set Repeating Frame Properties : Maximum records per page=1 And it will override group filter property.


18. What are the Non_query fields?
Following are the non_query fields:
Aggregated Information,
Calculated information,
A string Function


19. How Can I highlight and change all the format masks and print conditions of a bunch of fields all at once?
If you want to highlight a bunch of objects then right click and select "properties", Oracle gives you a stacked set of the individual properties forms for each of the selected objects. While this may be useful for some objects, it requires changing values individually for each object. However, you can select the group of fields and then select "Common properties" from the "Tools" menu which will allow you to set the format mask , print conditions etc. for the whole set of objects at once.

===============
1.What is the difference between Conditional Formatting and format trigger?
Both provide the similar functionality. They are used to format the output based on particular conditions. Format triggers provide a wide variety of options when compared to conditional formatting(GUI). In format Triggers we have the option to write PL/SQL code where as conditional formatting is GUI based which provide limited options.


2.What is Flex mode and Confine mode?
Confine mode
On: child objects cannot be moved outside their enclosing parent objects.
Off: child objects can be moved outside their enclosing parent objects.
Flex mode:
On: parent borders "stretch" when child objects are moved against them.


4. What is Page Break?
To limit the records per page.


5. What is Page Protector property in oracle reports ?
The Page Protect property indicates whether to try to keep the entire object and its contents on the same logical page. Setting Page Protect to Yes means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page. Ex: if you set yes, the object information prints another page.

6.How do you mail the output of a report?
You can use following methogs to mail the output of a report.
1. Use UTL_SMTP (refer to Scripts tab for more details)
2. Use MAILX called in a shell script registered as a concurrent program with parameters File name and path.


7. What is Print Direction?
The Print Direction property is the direction in which successive instances of the repeating frame appear.


8. What is Vertical and Horizental Elasticity
The Horizontal Elasticity property is determins how the horizontal size of the object will change at runtime to accommodate the objects or data within it:


9. What is Formula Column?
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.


10. How do you display only one record on each page in a report?
Give Page Break in the Format trigger of the repeating frame.


11. What is Summary columns?
A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.


12. What is Boilerplate?
Boilerplate is any text or graphics that appear in a report every time it is run. Report Builder will create one boilerplate object for each label selected in the Report Wizard (it is named B_
Column name). Also, one boilerplate object is sometimes created for each report summary. A boilerplate object is owned by the object surrounding it, unless otherwise noted.


13. What is Data Link
When we join multiple queries in a report the join condition is stored in the data link section
Data links relates the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group. When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime. You can view the SELECT statements for the individual parent and child queries in the Builder, but can not view the SELECT statement that includes the clause created by the data link you define.


14. What is Break Column?
We can break a column through data model , it is Displayed once for a group


15. How can you grey out/ highlight /hide some records based on conditions in a report?
You can use Conditional formatting to achieve it.


16. How do u call Report from form?
Use RUN_PRODUCT and RUN_REPORT_OBJECT to call a report from Oracle Forms.


17. What is Report Bursting?The capability of producing multiple copies of a given report or portion of it in different output formats is referred to as report bursting. Reports bursting offers you to deliver a single report to multiple destinations simultaneously. It offers you to create multiple reports out of one single report model. For example, you can create just one employee report for all your departments and send an email with a PDF-attachment containing the report to each manager. Each report would contain only the relevant department information for that particular manager. Using the reports bursting functionality will reduce the overhead since you will only have a single data fetch and report format.

18. What is Additional Layout?
Additional layout is created for two different formats using same query and groups without modifying default layout created by report wizard., we can use both layouts according to user requirement.


19. How do you write the report output to Excel file or text file?
You can use the following methods to write the output of oracle reports to Excel or text file.
1.Use TEXT_IO package
2.Use SPOOL in After Report trigger

3.Use UTL Package

==============

How To Add Any Responsibility To A User From Back End

=====================

 How To Add System Administrator Responsibility To A User from backend
Most of the time oracle apps developers are given only limited access to the front end application for development, in such cases you would like to add a responsibility from back end.



By using the below PL/SQL script/code you can add any responsibility to a user.


Syntax:fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);

Usage:BEGINfnd_user_pkg.addresp ('OPERATIONS','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Sysadmin Responsibility to OPERATIONS user using pl/sql', SYSDATE, SYSDATE + 100);COMMIT;DBMS_OUTPUT.put_line ('Responsibility Added Successfully');EXCEPTIONWHEN OTHERS  THENDBMS_OUTPUT.put_line ( ' Responsibility is not added due to ' || SQLCODE || SUBSTR (SQLERRM, 1, 100));ROLLBACK;END;

===================

How To Put Comments On A Database Table Or A Column.

===========================

Comments are always recommended for good programming practices. So If you want to put some comments on a database tables of columns of a database table then go through this simple tutorial:



Syntax For Comments on Table:
COMMENT ON TABLE
IS 'the comments should be written here';
Syntax For Comments on Columns:

COMMENT ON TABLE | COLUMN table.column
IS 'the comments should be written here';
The comments provided using the above syntax will be saved in the database and can be retrieved using the following queries:
For tables:
select * from all_tab_comments
where table_name ='table_name'

Or


select * from user_tab_comments
where table_name = 'table_name'

For Columns:
select * from all_col_comments
where table_name = 'table_name'
Or


select * from user_col_comments
where table_name = 'table_name'

=======================


How To Change The Header Of An Advance Table Column Dynamically

=============


Dynamically changing the Advance Table Column Header
To change the column of a header dynamically use the following code in your controller.
import oracle.apps.fnd.framework.webui.beans.table.OAAdvancedTableBean;
import oracle.apps.fnd.framework.webui.beans.table.OAColumnBean;
import oracle.apps.fnd.framework.webui.beans.table.OASortableHeaderBean;

OAAdvancedTableBean tableBean = (OAAdvancedTableBean)webBean.findIndexedChildRecursive("AdvTabRN");
OAColumnBean columnBean = (OAColumnBean)tableBean.findIndexedChildRecursive("empidCol");
OASortableHeaderBean colHeaderBean = (OASortableHeaderBean)columnBean.getColumnHeader();
colHeaderBean.setText("<New column name>");

=============
How To Check The Patch Level Status Of All Modules In Oracle Apps

===========

SQL Query  to see the patch level status of all modules in oracle apps.

SELECT a.application_name,DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,patch_levelFROM apps.fnd_application_vl a,apps.fnd_product_installations bWHERE a.application_id = b.application_id;

=============
How To Copy Files Using a PL/SQL Script

=====================

Usually we get requirements to move files from one location to another. The most easy option we think of for this purpose is through shell script, but there is a simple pl/sql api which can do this job easily for us.


You must have heard about UTL_FILE utility provided by oracle. We can use this utility for moving our files from one locatio to another. It has some limitations as well, such as the directory structure used by it should be in the UTL_FILE_PATH in init.ora or Directory object should exist for that path


The following code/Script can be used for Copying the file:


Utl_File.Fcopy ( src_location => p_file_location
,src_filename => p_file_name
, dest_location => p_arch_location,
dest_filename => p_arch_file );


The following code/Script can be used for removing the file:


UTL_FILE.Fremove(p_file_location,lc_datafile_name);

=================
How To Find All The Concurrent Requested Submitted Today Or Yesterday

====================

SQL Query/script/code/ to get all the concurrent requested Concurrent requests submitted today or yesterday :


select request_id,
       decode(parent_request_id, -1, 'None') as parent_request_id,
       fcpt.user_concurrent_program_name,
       decode(  fcr.status_code,
                'A', 'Waiting',
                'B', 'Resuming',
                'C', 'Normal',
                'D', 'Cancelled',
                'E', 'Error',
                'F', 'Scheduled',
                'G', 'Warning',
                'H', 'On Hold',
                'I', 'Normal',
                'M', 'No Manager',
                'Q', 'Standby',
                'R', 'Normal',
                'S', 'Suspended',
                'T', 'Terminating',
                'U', 'Disabled',
                'W', 'Paused',
                'X', 'Terminated',
                'Z', 'Waiting') as status,      
       decode(  fcr.phase_code,
                'C', 'Completed',
                'I', 'Inactive',
                'P', 'Pending',
                'R', 'Running') as phase,
       fu.user_name,
       fr.responsibility_name,             
       completion_text,
       argument_text,
       request_date,
       requested_start_date,
       actual_start_date,
       actual_completion_date,
       fcp.concurrent_program_name,
       fe.executable_name,
       fe.execution_file_name
from   fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_executables fe,
       fnd_responsibility_vl fr,
       fnd_user fu
where  fcr.concurrent_program_id = fcp.concurrent_program_id
and    fcp.concurrent_program_id = fcpt.concurrent_program_id
and    fcp.executable_id = fe.executable_id
and    fcr.responsibility_id = fr.responsibility_id
and    fcr.requested_by = fu.user_id
and    fcr.request_date > sysdate --Use sysdate - 1 for getting the requests raised Yesterday.
--and    fcr.concurrent_program_id = XXXX
order by request_id desc

=================================

How To Find All The Oracle API's For Any Module

================

Use the following SQL Query/code/Script to find all the Oracle API's for any particular  module:


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'AP_%API%' –- Checking for AP Related APIs
order by
a.owner, a.name

=========================

How To Find All The Running Concurrent Requests And The Time Taken By Them

==========================

The below SQL query can be used for analyzing concurrent program run time and identifying their performances.


select request_id,
         fcpt.user_concurrent_program_name,
         completion_text,
         actual_start_date,
         actual_completion_date,
         to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
 from fnd_concurrent_requests fcr,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcp.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.actual_start_date > sysdate - 1
    order by actual_completion_date - actual_start_date desc

=============================

How To Find Out When A Certain Profile Was Changed And Which User Changed It

======================

SQL Query to find out when a profile was changed and who changed it.


SELECT t.user_profile_option_name,profile_option_value,v.creation_date,v.last_update_date,v.creation_date - v.last_update_date "Change Date",(SELECT UNIQUE user_nameFROM fnd_userWHERE user_id = v.created_by) "Created By",(SELECT user_nameFROM fnd_userWHERE user_id = v.last_updated_by) "Last Update By"FROM fnd_profile_options o,fnd_profile_option_values v,fnd_profile_options_tl tWHERE o.profile_option_id = v.profile_option_idAND o.application_id = v.application_idAND start_date_active <= SYSDATEAND NVL (end_date_active, SYSDATE) >= SYSDATEAND o.profile_option_name = t.profile_option_nameAND level_id =  :p_valueAND t.LANGUAGE IN (SELECT language_codeFROM fnd_languagesWHERE installed_flag = 'B'UNIONSELECT nls_languageFROM fnd_languagesWHERE installed_flag = 'B')ORDER BY user_profile_option_name;

==========================

How To Get All Active Workflow Users And Their Roles



Use the following SQL query/code/script to get all the active workflow users and their roles:


select  wu.name user_name,
        wr.name role_name
from    wf_users wu,
        wf_user_roles wur,
        wf_roles wr
where   wu.name = wur.user_name
and     wur.role_name = wr.name
and     wu.status = 'ACTIVE'
and     wr.status = 'ACTIVE'
and     wr.orig_system = 'WF_LOCAL_ROLES'
order by wu.name,wr.name
       
=================

How To Get All Types Of Concurrent Requests For All Applications In Oracle Apps

=================

SQL Query/code/script to get all types of concurrent requests for all the Applications in Oracle Applications:


SELECT fa.application_short_name,fcpv.user_concurrent_program_name,description,DECODE (fcpv.execution_method_code,'B', 'Request Set Stage Function','Q', 'SQL*Plus','H', 'Host','L', 'SQL*Loader','A', 'Spawned','I', 'PL/SQL Stored Procedure','P', 'Oracle Reports','S', 'Immediate',fcpv.execution_method_code) exe_method,output_file_type,program_type,printer_name,minimum_width,minimum_length,concurrent_program_name,concurrent_program_idFROM fnd_concurrent_programs_vl fcpv, fnd_application faWHERE fcpv.application_id = fa.application_idORDER BY description

==================

Q. What are the API’s FOR CUSTOMER INTERFACE?

==========

Ans: HZ_CUST_A/C_VZPUB.UPDATE_CUST_A/C
  HZ_CUST_A/C_VZPUB.CREATE_CUST_A/C
       FND_PROFILES
       FND_APPLICATIONS
       FND_GLOBAL
       FND-FILE
       FND_CONCSUB(can submit conc program in host invironment)

==================
Following query gives Operating Unit Information and corresponding Inventory Orgs related information as well.

=======
SELECT hou.NAME operating_unit_name,
hou.short_code,
hou.organization_id operating_unit_id,
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id,
ood.chart_of_accounts_id
FROM hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

==================
Following script can be used to create an Organization in Oracle Apps. Its using the API below:

===================
    * p_organization_rec hz_party_v2pub.organization_rec_type;

Create Organization
DECLARE
p_organization_rec hz_party_v2pub.organization_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
BEGIN
p_organization_rec.organization_name := 'abc';
p_organization_rec.created_by_module := 'abc_demo';
hz_party_v2pub.create_organization ('T',
p_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_id,
x_party_number,
x_profile_id
);
DBMS_OUTPUT.put_line ('party id ' || x_party_id);
DBMS_OUTPUT.put_line (SUBSTR ('x_return_status = ' || x_return_status,
1,
255
)
);
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line (SUBSTR ('x_msg_data = ' || x_msg_data, 1, 255));
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
END;

The above API will create records in hz_parties table in apps schema.

===========XML===
XML Publisher

XML Publisher
Creating an RTF Template
XML Input File
<?xml version="1.0" encoding="WINDOWS-1252" ?>
- <VENDOR_REPORT>
- <LIST_G_VENDOR_NAME>
- <G_VENDOR_NAME>
<VENDOR_NAME>COMPANY A</VENDOR_NAME>
- <LIST_G_INVOICE_NUM>
- <G_INVOICE_NUM>
<SET_OF_BOOKS_ID>124</SET_OF_BOOKS_ID>
<GL_DATE>10-NOV-03</GL_DATE>
<INV_TYPE>Standard</INV_TYPE>
<INVOICE_NUM>031110</INVOICE_NUM>
<INVOICE_DATE>10-NOV-03</INVOICE_DATE>
<INVOICE_CURRENCY_CODE>EUR</INVOICE_CURRENCY_CODE>
<ENT_AMT>122</ENT_AMT>
<ACCTD_AMT>122</ACCTD_AMT>
<VAT_CODE>VAT22%</VAT_CODE>
</G_INVOICE_NUM>
</LIST_G_INVOICE_NUM>
<ENT_SUM_VENDOR>1000.00</ENT_SUM_VENDOR>
<ACCTD_SUM_VENDOR>1000.00</ACCTD_SUM_VENDOR>
</G_VENDOR_NAME>
</LIST_G_VENDOR_NAME>
<ACCTD_SUM_REP>108763.68</ACCTD_SUM_REP>
<ENT_SUM_REP>122039</ENT_SUM_REP>
</VENDOR_REPORT>
XML files are composed of elements. Each tag set is an element. For example
<INVOICE_DATE></INVOICE_DATE> is the invoice date element. "INVOICE_DATE"
is the tag name. The data between the tags is the value of the element. For example, the
value of INVOICE_DATE is "10-NOV-03".
Enter placeholders in your document using the following syntax:
<?XML element tag name?>
EX : <?VENDOR_NAME?>
Form Field Method
Use Microsoft Word's Text Form Field Options window to insert the placeholder tags:
1. Enable the Forms toolbar in your Microsoft Word application.
2. Position your cursor in the place you want to create a placeholder.
3. Select the Text Form Field toolbar icon. This action inserts a form field area in your
document.
4. Double-click the form field area to invoke the Text Form Field Options dialog box.
5. (Optional) Enter a description of the field in the Default text field. The entry in this
field will populate the placeholder's position on the template.
For the example, enter "Supplier 1".
6. Select the Add Help Text button.
7. In the help text entry field, enter the XML element's tag name using the syntax:
<?XML element tag name?>
Template Field Name Default Text Entry (Form Field Method)
<?INVOICE_NUM?>
<?INVOICE_DATE?>
<?GL_DATE?>
<?INVOICE_CURRENCY_CODE?>
<?ENT_AMT?>
<?ACCTD_AMT?>
<?ENT_SUM_VENDOR?>
<?ACCTD_SUM_VENDOR?>
LOOPS: Without loops we will get only one record for output and using loops we can expect expected results.
For-each is a loop:
Insert the following tag before the first element:
<?for-each:XML group element tag name?>
Insert the following tag after the final element:
<?end for-each?>
<?for-each:G_VENDOR_NAME?> <?sort:VENDOR_NAME?>
<?INVOICE_NUM?>
<?INVOICE_DATE?>
<?GL_DATE?>
<?INVOICE_CURRENCY_CODE?>
<?ENT_AMT?>
<?ACCTD_AMT?>
<?ENT_SUM_VENDOR?>
<?ACCTD_SUM_VENDOR?>
<?end for-each?>
Multiple or Complex Headers and Footers:
Using start body n end body we can divide header n footer.
<?start:body?>
<?end body?>
1. Insert <?start:body?> before the Suppliers group tag:
<?for-each:G_VENDOR_NAME?>
2. Insert <?end body?> after the Suppliers group closing tag: <?end for-each?>
Images and Charts
url:{'http://image location'}
For example, enter:
url:{'http://www.oracle.com/images/ora_log.gif'}
url:{'${OA_MEDIA}/image name'}
you can retrieve the chart title from an XML tag by using the following syntax:
<Title text="{CHARTTITLE}" visible="true" horizontalAlighment="CENTER"/>
where "CHARTTITLE" is the XML tag name that contains the chart title. Note that the
tag name is enclosed in curly braces.
The next section defines the column and row labels:
<LocalGridData colCount="{count(//division)}" rowCount="1">
<RowLabels>
<Label>Total Sales $1000s</Label>
</RowLabels>
<ColLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</ColLabels>
The LocalGridData element has two attributes: colCount and rowCount. These
define the number of columns and rows that will be shown at runtime. In this example,
a count function calculates the number of columns to render:
colCount="{count(//division)}"
The rowCount has been hard-coded to 1. This value defines the number of sets of data
to be charted. In this case it is 1.
XSL : XML STYLE SHEET LANGUAGE
Using xsl language we can design the template.
<ColLabels>
<xsl:for-each select="//division">
<Label>
<xsl:value-of select="name"/>
</Label>
</xsl:for-each>
</ColLabels>
This code loops through the <division> group and inserts the value of the <name>
element into the <Label> tag. At runtime, this will generate the following XML:
<ColLabels>
<Label>Groceries</Label>
<Label>Toys</Label>
<Label>Cars</Label>
<Label>Hardware</Label>
<Label>Electronics</Label>
</ColLabels>
The next section defines the actual data values to chart:
<DataValues>
<RowData>
<xsl:for-each select="//division">
<Cell>
<xsl:value-of select="totalsales"/>
</Cell>
</xsl:for-each>
</RowData>
</DataValues>
Replicate a Shape
<?for-each@shape:SHAPE_GROUP?>
<?shape-offset-y:(position()-1)*100?>
<?end for-each?>
where
for-each@shape opens the for-each loop for the shape context
Add Text to a Shape
<?shape-text:SHAPETEXT?>
Add Text Along a Path
<?shape-text-along-path:SHAPETEXT?>
where SHAPETEXT is the element from the XML data. At runtime the value of the
element SHAPETEXT will be inserted above and along the line
Moving a Shape
You can move a shape or transpose it along both the x and y-axes based on the XML
data. For example to move a shape 200 pixels along the y-axis and 300 along the x-axis,
enter the following commands in the property dialog of the shape:
<?shape-offset-x:300?>
<?shape-offset-y:200?>
Rotating a Shape
To rotate a shape about a specified axis based on the incoming data, use the following
command:
<?shape-rotate:ANGLE;'POSITION'?>
Skewing a Shape
You can skew a shape along its x or y axis using the following commands:
<?shape-skew-x:ANGLE;'POSITION'?>
<?shape-skew-y:ANGLE;'POSITION'?>
Changing the Size of a Shape
You can change the size of a shape using the appropriate commands either along a
single axis or both axes. To change a shape's size along both axes, use:
<?shape-size:RATIO?>
Combining Commands
<for-each@shape:SHAPE_GROUP?>
<?shape-text:position()?>
<?shape-offset-y:position()*50?>
<?shape-rotate:5;'center/center'?>
<?shape-size-x:position()+1?>
<end for-each?>
ADD Page Total
<?add-page-total:TotalFieldName;'element'?>
Show Page Total
<?show-page-total:TotalFieldName;'Oracle-number-format'?>
Page Breaks
To create a page break after the occurrence of a specific element use the
"split-by-page-break" alias. This will cause the report output to insert a hard page break
between every instance of a specific element.
To insert a page break between each occurrence of a group, insert the
"split-by-page-break" form field within the group immediately before the <?end
for-each?> tag that closes the group. In the Help Text of this form field enter the
syntax:
<?split-by-page-break:?>
Initial Page Number
Some reports require that the initial page number be set at a specified number
Use the following syntax in your template to set the initial page number:
<?initial-page-number:pagenumber?>
where pagenumber is the XML element or parameter that holds the numeric value.
EX:
<REPORT>
<PAGESTART>200<\PAGESTART>
....
</REPORT>
Enter the following in your template:
<?initial-page-number:PAGESTART?>
Example 2 - Set page number by passing a parameter value
If you define a parameter called PAGESTART, you can pass the initial value by calling
the parameter.
Enter the following in your template:
<?initial-page-number:$PAGESTART?>
Last Page Only Content
XML Publisher provides this ability. To utilize this feature, you must:
1. Create a section break in your template to ensure the content of the final page is
separated from the rest of the report.
2. Insert the following syntax on the final page:
<?start@last-page:body?>
<?end body?>
To end on an even page with layout:
Insert the following syntax in a form field in your template:
<?section:force-page-count;'end-on-even-layout'?>
To end on an odd page layout:
<?section:force-page-count;'end-on-odd-layout'?>
If you do not have layout requirements for the final page, but would like a blank page
ejected to force the page count to the preferred odd or even, use the following syntax:
<?section:force-page-count;'end-on-even'?>
or
<?section:force-page-count;'end-on-odd'?>
Generating Bookmarks in PDF Output
To create links for a static table of contents:
Enter the syntax:
<?copy-to-bookmark:?>
directly above your table of contents and
2-58 Oracle XML Publisher Core Components Guide
<?end copy-to-bookmark:?>
directly below the table of contents.
• To create links for a dynamic table of contents:
Enter the syntax:
<?convert-to-bookmark:?>
directly above the table of contents and
<?end convert-to-bookmark:?>
directly below the table of contents.
Drop-Down Form Field Options
<?contintentIndex?>
The drop-down form field for the
continentIndex element. See the preceding
description for its contents. At runtime, the value of
the XML element is replaced with the value it is
cross-referenced to in the drop-down form field.
If Statements
<?if:condition?>
<?end if?>.
For example, to set up the Payables Invoice Register to display invoices only when the
Supplier name is "Company A", insert the syntax
<?if:VENDOR_NAME='COMPANYA'?>
before the Supplier field on the template.
Enter the <?end if?> tag after the invoices table.
EX1:
<?if:SUCCESS='N'?>not<?end if?>
<?if@inlines:SUCCESS='N'?>not<?end if?>
If-then-Else Statements
<?xdofx:if element_condition then result1 else result2 end if?>
IF X=0 THEN
Y=2
ELSE
Y=3
END IF
<?xdofx:if AMOUNT > 1000 then 'Higher'
else
if AMOUNT < 1000 then 'Lower'
else
'Equal'
end if?>
Choose Statements
Use the choose, when, and otherwise elements to express multiple conditional tests.
If certain conditions are met in the incoming XML data then specific sections of the
template will be rendered. This is a very powerful feature of the RTF template. In
regular XSL programming, if a condition is met in the choose command then further
XSL code is executed. In the template, however, you can actually use visual widgets in
the conditional flow (in the following example, a table).
Use the following syntax for these elements:
<?choose:?>
<?when:expression?>
<?otherwise?>
The code to highlight the debit column as shown in the table is:
<?if:debit>1000?>
<xsl:attribute
xdofo:ctx="block" name="background-color">red
</xsl:attribute>
<?end if?>
Field Form Field Entry
F <?for-each:CD?>
TITLE <?TITLE?>
ARTIST <?ARTIST?>
E <?end for-each?>
(star shape) Web Tab Entry:
<?for-each@shape:xdoxslt:foreach_number($_XDOCTX,0
,USER_RATING,1)?>
<?shape-offset-x:(position()-1)*25?>
<?end for-each?>
Using Functions
Group by TmpRng <?for-each-group:temp;floor(degree div 10?>
<?sort:floor(degree div 10)?>
Range <?concat(floor(degree div 10)*10,' F to ',floor(degree
div 10)*10+10, F')?>
Months <?count(current-group())?>
End TmpRng <?end for-each-group?>
Securing a PDF Output
For this example, suppose you want to use a password from the XML data to secure the
PDF output document. The XML data is as follows:
Creating an RTF Template 2-93
<PO>
<security>true</security>
<password>welcome</password>
<PO_DETAILS>
..
</PO>
In the Properties dialog set two properties: pdf-security to set the security feature as
enabled or not, and pdf-open-password to set the password. Enter the following in
the Properties dialog:
Name: xdo-pdf-security
Type: Text
Value: {/PO/security}
Name: xdo-pdf-open-password
Type: Text
Value: {/PO/password}
Dynamic Data Columns
Dynamic Column Header
<?split-column-header:group element name?>
Use this tag to define which group to split for the column headers of a table.
• Dynamic Column <?split-column-data:group element name?>
Use this tag to define which group to split for the column data of a table.
• Dynamic Column Width
<?split-column-width:name?> or
<?split-column-width:@width?>
Defining Columns to Repeat Across Pages
If your table columns expand horizontally across more than one page, you can define
how many row heading columns you want to repeat on every page. Use the following
syntax to specify the number of columns to repeat:
<?horizontal-break-table:number?>
where number is the number of columns (starting from the left) to repeat.
Note that this functionality is supported for PDF output only..
Time Zone Specification
<?format-date:hiredate;'LONG_TIME_TZ';'Asia/Shanghai'?>
Using External Fonts
<font family="MyFontName" style="normal" weight="normal">
<truetype path="\user\fonts\MyFontName.ttf"/>
</font>
Register the Barcode Encoding Class
Use the following syntax in a form field in your template to register the barcode
encoding class:
<?register-barcode-vendor:java_class_name;barcode_vendor_id?>
This command requires a Java class name (this will carry out the encoding) and a
barcode vendor ID as defined by the class. This command must be placed in the
template before the commands to encode the data in the template. For example:
<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.Ba
rcodeUtil';'XMLPBarVendor'?>
where
oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtil is the Java
class and
XMLPBarVendor is the vendor ID that is defined by the class.
Using XSL Elements
You can use any XSL element in your template by inserting the XSL syntax into a form
field.
If you are using the basic RTF method, you cannot insert XSL syntax directly into your
template. XML Publisher has extended the following XSL elements for use in RTF
templates.
To use these in a basic-method RTF template, you must use the XML Publisher Tag
form of the XSL element. If you are using form fields, use either option.
Apply a Template Rule
Use this element to apply a template rule to the current element's child nodes.
XSL Syntax: <xsl:apply-templates select="name">
XML Publisher Tag: <?apply:name?> This function applies to <xsl:template-match="n"> where n is the element name.
Copy the Current Node
Use this element to create a copy of the current node.
XSL Syntax: <xsl:copy-of select="name">
XML Publisher Tag: <?copy-of:name?>
Call Template
Use this element to call a named template to be inserted into or applied to the current
template. For example, use this feature to render a table multiple times.
XSL Syntax: <xsl:call-template name="name">
XML Publisher Tag: <?call-template:name?>
Template Declaration
Use this element to apply a set of rules when a specified node is matched.
XSL Syntax: <xsl:template name="name">
XML Publisher Tag: <?template:name?>
Variable Declaration
Use this element to declare a local or global variable.
XSL Syntax: <xsl:variable name="name">
XML Publisher Tag: <?variable:name?>
Example:
<xsl:variable name="color" select="'red'"/>
Assigns the value "red" to the "color" variable. The variable can then be referenced in
the template.
Import Stylesheet
Use this element to import the contents of one style sheet into another.
Note: An imported style sheet has lower precedence than the importing
style sheet.
XSL Syntax: <xsl:import href="/url">
XML Publisher Tag: <?import:url?>
Define the Root Element of the Stylesheet
This and the <xsl:stylesheet> element are completely synonymous elements. Both
are used to define the root element of the style sheet.
Note: An included style sheet has the same precedence as the including
style sheet.
XSL Syntax: <xsl:stylesheet xmlns:x="url">
XML Publisher Tag: <?namespace:x=url?>
API Method Call
The following is an example of an API method call:
String xmlInputPath = "c:\\tmp\\toc.xml";
String pdfOutputPath = "c:\\tmp\\final_book.pdf";
PDFBookBinder bookBinder = new PDFBookBinder(xmlInputPath,
pdfOutputPath);
bookBinder.setConfig(new Properties());
bookBinder.process();
Hierarchy and Elements of the Document Processor XML File
The Document Processor XML file has the following element hierarchy:
Requestset
request
delivery
filesystem
print
fax
number
email
message
document
background
text
pagenumber
template
data
Element Attributes Description
requestset xmlns Root element must contain [xmlns:xapi="http://xml
ns.oracle.com/oxp/xapi/"] block
The version is not required,
but defaults to "1.0".
request N/A lement that contains the data and template processing definitions.
delivery N/A Defines where the generated output is sent.
document output-type Specify one output that can have several template
elements. The output-type
attribute is optional. Valid
values are:
pdf (Default)
rtf
html
excel
text
filesystem output Specify this element to save the output to the file system.
Define the directory path in the output attribute.
print • printer
• server-alias
The print element can occur multiple times under delivery to print one document to several printers. Specify the printer attribute as a URI, such as: "ipp://myprintserver:6
31/printers/printernam e"
fax • server
• server-alias
Specify a URI in the server attribute, for example: "ipp://myfaxserver1:63 1/printers/myfaxmachin e" number The number element can occur multiple times to list
multiple fax numbers. Each element occurrence must contain only one number.
email • server
• port
• from
• reply-to
• server-alias
Specify the outgoing mail server (SMTP) in the server attribute. Specify the mail server port in the port attribute.
message • to
• cc
• bcc
• attachment
• subject
The message element can be placed several times under the email element. You can
specify character data in the message element. You can specify multiple e-mail addresses in the to, cc and bcc attributes separated by a comma. The attachment value is either true or false (default). If attachment is true, then a generated document will be
attached when the e-mail is sent. The subject attribute is optional.
background where If the background text is required on a specific page, then set the where value to the page numbers required. The page index starts at 1. The default value is 0, which places the background on all pages.
text • title
• default
Specify the watermark text in the title value. A default value of "yes" automatically draws the watermark with forward slash type. The default value is yes.
pagenumber • initial-page-index
• initial-value
• x-pos
• y-pos
The initial-page-index default value is 0. The initial-value default value is 1. "Helvetica" is used for the page number font. The x-posprovides lower left x position. The y-pos provides lower left y position.
template • locale
• location
• type
Contains template information. Valid values for the type attribute are pdf rtf
xsl-fo etext The default value is "pdf".
Data location
Define the location attribute to specify the location of the data, or attach
the actual XML data with subelements. The default value of location is "inline". It the location points to either an XML file or a URL, then the data should contain an XML declaration with the proper encoding. If the location attribute is not specified, the data
element should contain the subelements for the actual data. This must not include an
XML declaration.
Defining multiple templates and data
The following example builds on the previous examples by applying two data sources
to one template and two data sources to a second template, and then merging the two
into a single output file. Note that when merging documents, the output-type must
be "pdf".
Example
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile3.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="d:\mywork\template1.pdf">
<xapi:data>
<field1>The first set of data</field1>
</xapi:data>
<xapi:data>
<field1>The second set of data</field1>
</xapi:data>
</xapi:template>
<xapi:template type="pdf"
location="d:\mywork\template2.pdf">
<xapi:data>
<field1>The third set of data</field1>
</xapi:data>
<xapi:data>
<field1>The fourth set of data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Retrieving templates over HTTP
This sample is identical to the previous example, except in this case the two templates
are retrieved over HTTP:
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\temp\out4.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="http://your.server:9999/templates/template1.pdf">
<xapi:data>
<field1>The first page data</field1>
</xapi:data>
<xapi:data>
<field1>The second page data</field1>
</xapi:data>
</xapi:template>
<xapi:template type="pdf"
location="http://your.server:9999/templates/template2.pdf">
<xapi:data>
<field1>The third page data</field1>
</xapi:data>
<xapi:data>
<field1>The fourth page data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Defining two data sets
The following example shows how to define two data sources to merge with one
template to produce one output file delivered to the file system:
Example
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile.pdf"/>
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf"
location="d:\mywork\template1.pdf">
<xapi:data>
<field1>The first set of data</field1>
</xapi:data>
<xapi:data>
<field1>The second set of data</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>
Simple XML sample
The following sample is a simple example that shows the definition of one template (
template1.pdf) and one data source (data1) to produce one output file (
outfile.pdf) delivered to the file system: Example
<?xml version="1.0" encoding="UTF-8" ?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
<xapi:request>
<xapi:delivery>
<xapi:filesystem output="d:\tmp\outfile.pdf" />
</xapi:delivery>
<xapi:document output-type="pdf">
<xapi:template type="pdf" location="d:\mywork\template1.pdf">
<xapi:data>
<field1>data1</field1>
</xapi:data>
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>


<dataTemplate name="INTDOMHOLD" defaultPackage="" description="INTDOMHOLD">
<parameters>
<parameter name="p_hold" dataType="VARCHAR2" defaultValue=""/>
</parameters>
<dataQuery>
<sqlStatement name="Q1" dataSourceRef=""><![CDATA[SELECT ooh.order_number
,ooh.flow_status_code header_status
,hp.party_name customer_name
, ool.line_number ||
'.' ||
ool.shipment_number line
,ott.NAME line_type
,ool.unit_selling_price expected_price
,ool.flow_status_code line_status
,ool.ordered_item
,'Yes' "Invoiceable Item"
,DECODE( oh.released_flag, 'N', 'Yes', 'No' ) hold_active
,ohd.NAME hold_name
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_until_date
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_release_date
,( SELECT rct.trx_number
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' )invoice
,( SELECT TO_CHAR( rct.trx_date, 'DD-Mon-YYYY' )
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice_date
FROM oe_order_lines_all ool
,oe_order_headers_all ooh
,oe_order_holds_all oh
,mtl_system_items_b msi
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_cust_site_uses_all hcsu
,oe_transaction_types_tl ott
,oe_hold_sources_all ohs
,oe_hold_definitions ohd
WHERE ooh.header_id = ool.header_id
AND ool.org_id = msi.organization_id
AND ool.ordered_item = msi.segment1
AND msi.invoiceable_item_flag = 'Y'
AND ool.flow_status_code IN( 'CLOSED', 'INVOICE_HOLD' )
AND ool.line_type_id = ott.transaction_type_id
AND ott.NAME = 'OKS Billing Line'
AND ool.header_id = 11878
AND ool.line_id = oh.line_id
AND ooh.header_id = oh.header_id
AND oh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oh.released_flag = 'N'
AND ool.invoice_to_org_id = hcsu.site_use_id(+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
AND hca.party_id = hp.party_id(+)
AND hcas.cust_account_id = hca.cust_account_id(+)
AND hcsu.site_use_code(+) = 'BILL_TO'
AND ohd.NAME = NVL( :p_hold, ohd.NAME )
-- AND ohs.hold_until_date = NVL( :p_to_release, ohs.hold_until_date )
AND NOT EXISTS(
SELECT 1
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND sales_order_line = ool.line_number
AND sales_order = ooh.order_number
AND interface_line_attribute2 = 'OKS Billing Line' )
UNION
SELECT ooh.order_number
,ooh.flow_status_code header_status
,hp.party_name customer_name
, ool.line_number ||
'.' ||
ool.shipment_number line
,ott.NAME line_type
,ool.unit_selling_price expected_price
,ool.flow_status_code line_status
,ool.ordered_item
,'Yes' "Invoiceable Item"
,DECODE( oh.released_flag, 'N', 'Yes', 'No' ) hold_active
,ohd.NAME hold_name
,TO_CHAR( ohs.hold_until_date, 'DD-Mon-YYYY' ) hold_until_date
,TO_CHAR( ohr.creation_date, 'DD-Mon-YYYY' ) hold_release_date
,( SELECT rct.trx_number
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice
,( SELECT TO_CHAR( rct.trx_date, 'DD-Mon-YYYY' )
FROM ra_customer_trx_lines_all rcl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = rcl.customer_trx_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.sales_order_line = ool.line_number
AND rcl.sales_order = ooh.order_number
AND rcl.interface_line_attribute2 = 'OKS Billing Order' ) invoice_date
FROM oe_order_lines_all ool
,oe_order_headers_all ooh
,oe_order_holds_all oh
,mtl_system_items_b msi
,apps.hz_parties hp
,apps.hz_cust_accounts hca
,apps.hz_cust_acct_sites_all hcas
,apps.hz_cust_site_uses_all hcsu
,oe_transaction_types_tl ott
,oe_hold_sources_all ohs
,oe_hold_definitions ohd
,oe_hold_releases ohr
WHERE ooh.header_id = ool.header_id
AND ool.org_id = msi.organization_id
AND ool.ordered_item = msi.segment1
AND msi.invoiceable_item_flag = 'Y'
AND ool.flow_status_code IN( 'CLOSED', 'INVOICE_HOLD' )
AND ool.line_type_id = ott.transaction_type_id
AND ott.NAME = 'OKS Billing Line'
AND ool.header_id = 11878
AND ool.line_id = oh.line_id
AND ooh.header_id = oh.header_id
AND oh.hold_source_id = ohs.hold_source_id
AND ohs.hold_id = ohd.hold_id
AND oh.released_flag = 'Y'
AND ohr.hold_release_id = oh.hold_release_id
AND ool.invoice_to_org_id = hcsu.site_use_id(+)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id(+)
AND hca.party_id = hp.party_id(+)
AND hcas.cust_account_id = hca.cust_account_id(+)
AND hcsu.site_use_code(+) = 'BILL_TO'
AND ohd.NAME = NVL( :p_hold, ohd.NAME )
-- AND ohr.creation_date = NVL( :p_to_release, ohr.creation_date )
AND NOT EXISTS(
SELECT 1
FROM ra_customer_trx_lines_all
WHERE interface_line_context = 'ORDER ENTRY'
AND sales_order_line = ool.line_number
AND sales_order = ooh.order_number
AND interface_line_attribute2 = 'OKS Billing Line')]]>
</sqlStatement>
</dataQuery>
<dataStructure>
<group name="G_ORDER" source="Q1" groupFilter="">
<element name="ORDER" value="ORDER" function=""/>
<element name="HEADER_STATUS" value="HEADER_STATUS" function=""/>
<element name="CUSTOMER" value="CUSTOMER" function=""/>
<element name="LINE" value="LINE" function=""/>
<element name="LINE_TYPE" value="LINE_TYPE" function=""/>
<element name="EXPECTED_PRICE" value="EXPECTED_PRICE" function=""/>
<element name="LINE_STATUS" value="LINE_STATUS" function=""/>
<element name="ORDERED_ITEM" value="ORDERED_ITEM" function=""/>
<element name="HOLD_ACTIVE" value="HOLD_ACTIVE" function=""/>
<element name="HOLD_NAME" value="HOLD_NAME" function=""/>
<element name="HOLD_UNTIL_DATE" value="HOLD_UNTIL_DATE" function=""/>
<element name="HOLD_RELEASE_DATE" value="HOLD_RELEASE_DATE" function=""/>
<element name="INVOICE" value="INVOICE" function=""/>
<element name="INVOICE_DATE" value="INVOICE_DATE" function=""/>
</group>
</dataStructure>
</dataTemplate>

No comments:

Post a Comment