ABOUT ERP

ERP :

What is ERP?

Enterprise Resource Planning or ERP is an industry term for integrated, multi-module application software packages that are designed to serve and support multiple business functions.

An ERP system can include software for manufacturing, order entry, accounts receivable and payable, general ledger, purchasing, warehousing, transportation and human resources. Evolving out of the manufacturing industry, ERP implies the use of packaged software rather than proprietary software written by or for one customer.

ERP modules may be able to interface with an organization's own software with varying degrees of effort, and, depending on the software, ERP modules may be alterable via the vendor's proprietary tools as well as proprietary or standard programming languages.


Brief History of ERP

The focus of manufacturing systems in the 1960's was on Inventory control. Most of the software packages then (usually customized) were designed to handle inventory based on traditional inventory concepts. In the 1970's the focus shifted to MRP (Material Requirement Planning) systems that translated the Master Schedule built for the end items into time-phased net requirements for the sub-assemblies, components and raw materials planning and procurement.

In the 1980's the concept of MRP-II (Manufacturing Resources Planning) evolved which was an extension of MRP to shop floor and Distribution management activities. In the early 1990's, MRP-II was further extended to cover areas like Engineering, Finance, Human Resources, Projects Management etc i.e. the complete gamut of activities within any business enterprise. Hence, the term ERP (Enterprise Resource Planning) was coined.

Why is it Necessary?

By becoming the integrated information solution across the entire organization, ERP systems
allow companies to better understand their business. With ERP software, companies can standardize business processes and more easily enact best practices. By creating more efficient processes, companies can concentrate their efforts on serving their customers and maximizing profit.

Market Leaders

The top five ERP vendors, SAP, Oracle Corporation, Peoplesoft, Inc. (now Oracle Corp.), JD Edwards & Company, and Baan International, account for 64 percent of total ERP market revenue. These vendors continue to play a major role in shaping the landscape of new target markets, with expanded product functionality, and higher penetration rates. SAP dominates the $6.7 billion ERP applications market in Europe with 39% market share. Oracle and PeopleSoft come second and third respectively, followed by SAGE Group and Microsoft Business Solutions.

FOR ORACLE APPS BEGINNERS

TECHNICAL TERMS IN ORACLE APPLICATIONS:

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.

The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes.

In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION.

Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly.

The invoice amount could be paid as a single amount or could be paid in installments. Harry’s customer, Fruit Mart pays him in installments (partial payments).

So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet.
This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY.

Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.

Oracle Accounts Payables handles all the payments to the vendors.

Oracle Inventory deals with the items you maintain in stock, warehouse etc.

Order Management helps you collect all the information that your customers order.

Oracle Receivables help you collect the money for the orders that are delivered to the customers.

Oracle Human Resources helps maintain the Employee information, helps run paychecks etc.

Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc.

For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in.

So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.
All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.


Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House

Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number
DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns

Oracle Reports
Oracle Form
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

ORACLE HRMS

ORACLE HUMAN RESOURCES

1. What are the seeded Person Types?

Person types are used to describe people entered into the Oracle Human Resources product. The person types names may be modified if needed, or new person types can be added.

In 10.7 and 11.0.3 there are eight person types delivered by the system, and identified by a system name. For each, there is a predefined user name.
You can change the predefined name and you can create unique user names.

The 10.7 and 11.0.3 delivered person types are:

ApplicantApplicant and Ex-ApplicantEmployeeEmployee and ApplicantEx-applicantEx-employeeEx-employee and ApplicantExternalIn 11i additional person types have been delivered to accommodate legislationsand customers using 11i benefits modules.
The information is stored in PER_PERSON_TYPES.

In 10.7 and 11.0.3 go to PER_ALL_PEOPLE_F to PER_PERSON_TYPES. In 11i go to PER_PERSON_TYPE_USAGES_F and then to PER_PERSON_TYPES.


2. Where do I add or change the person types?

(10.7/11.0.3 & 11.5.x)In application versions 10.7, 11.0.3 and 11.5.x follow this navigation:Other Definitions > Person Types.To change a user name for a Person Type:Delete the contents of the User Name field and type in your preferred name.

To map other User Names to a Person Type system name:

A. Select New Record from the Edit menu.B. Enter a unique User Name and select the System Name to which you want to map.
Deactivating User NamesYou cannot delete User Names in use in the system, but you can deactivate them by unchecking the Active check box. Notice that you cannot deactivate a default User Name. You must first select another User Name as the default.

3. Can external Person Types be contacts?

Yes, anything defined with a System Name of External is considered a contact and will appear in the Contact Type list of values. A contact is someone associated with an employee or an applicant. For example, people who are dependents of employees, people whom employees name as beneficiaries, and people whom employees list as their contacts in case of emergency are all entered as contacts.

4. There is a new form in application version 11.5.x called Person Type Usage. Howdoes this work in connection with the person types?

This new form in 11.5.x (Navigation: People> Enter & Maintain, Others) is called Person Type Usage. If you need to change the user value for any person type applied to a person, you do this in the Person Type Usages window, which is now datetracked for HR user person type changes. The information is stored in the table: PER_PERSON_TYPE_USAGES_F. The form is also used by 11i Benefits module customers for managing benefits.

5. What is the difference between the Contact Type and the Relationship Type on the Contact form (PERWSDPC)?

The Type field refers to the Person Type as defined in the Person Types form. The information is stored in the PER_PERSON_TYPES table. It is defined and maintained in the Person Types (PERWSDPT) form located under Other Definitions off the main navigation menu. Anything defined with a system name of external is considered a Contact and will show up in the Contact Type list of values.The Relationship Type refers to the definition of the association that this contact has to the employee (i.e. spouse, emergency contact, friend, etc.).

The information is stored in the PER_CONTACT_RELATIONSHIPS table. The list of values is created and maintained in the Other Definitions > QuickCode Values navigation on the main menu.

6. Why does the Person Type that I just created not show up on my list of values on the Enter and Maintain form?

If the Person Type has a system name of 'External' then that Person Type is not eligible for this list of values. 'External' system name values are Contacts.

7. How do I delete or end date a Person Type?

Person Type cannot be deleted or end dated. The Person Type may only be deactivated. Navigate to Other Definitions > Person Types.
The user may either check or uncheck the Active column.

8. What has happened to the 11i person types since applying PER G (1988754)?

After applying PER G, the person type field is not available for direct updates on the Person form.

(Navigation: People > Enter & Maintain) The functionality of this field has been changed. The Type field (person_type) now works in relation to the Action field that appears under the Type. These changes were also explained in the PER G readme. The manner in which person types are recorded against a person has been changed to enable you to record multiple person type records against a person. Now you can update a person's type using the new Actions field available on the People form.
For example, when you create a new employee select the Create Employment action and a list of user person types for the system person type of Employee is displayed for your selection. If this person also becomes an applicant, you select the Create Applicant action and an applicant record is created for the person. The Type value now displayed will be Employee.Applicant. Previously, using the example above, this person would have been given a single person type of 'Employee and Applicant'.
With this new functionality this person will have two records, one Employee and one Applicant. This is displayed in the Type field as a concatenated version of the two types. If you are using the default user values for these types, the value displayed is Employee.Applicant.If you need to change the user value for any person type applied to a person, you do this in the Person Type Usages window, which is now datetracked for HR user person type changes.

Navigation: People > Enter & Maintain, Others. This means you can reduce the number of system person types that need to be maintained and offer greater flexibility in defining person type combinations. The eight system person types previously recognized by the application continue to be maintained for existing persons but can no longer be used for new records in 11i.

Because of this update you may need to make a few changes to your application in order for it to work with the latest data.If you are using the USER_PERSON_TYPE column of the PER_PERSON_TYPES tablein your custom reports or forms you must redirect them. Instead of going from PER_ALL_PEOPLE_F to PER_PERSON_TYPES, you will need to initially join to PER_PERSON_TYPE_USAGES_F and then go to PER_PERSON_TYPES.

If you have any formulas that use the following database items, you should replace them with the new items as the current ones will be obsoleted in a future patch. See the following listed columns of Current DB Items and its corresponding New DB Items.

Current DB Item: New DB Item:PER_PERSON_TYPE PTU_PER_PERSON_TYPECON_PERSON_TYPE PTU_CON_PERSON_TYPEREC_PERSON_TYPE PTU_REC_PERSON_TYPE SUP_PERSON_TYPE PTU_SUP_PERSON_TYPEThe new person type usages model enables you to have more flexibility when using person type as a parameter to create customized windows. You should no longer need be able to customize windows by Person Function and therefore this option will be removed.

FLEX FIELDS (KFFDFF) AND SQL PLUS

KFF'S AND DFF'S


Steps Involved :

1. Registering A Tables & Columns :Register the Table & Columns if the KFF/DFF is on User Defined Table.

The following is theexampleAD_DD.register_table('AR','RAJ_KFF_TEST','T',8,10,90);ad_dd.register_column('AR','RAJ_KFF_TEST','Attribute_category',1,'Varchar2',20,'N','N');ad_dd.register_column('AR','RAJ_KFF_TEST','CCID',2,'NUMBER',10,'N’,'N');ad_dd.register_column('AR','RAJ_KFF_TEST','attribute1',3,'VARCHAR2',20,'N','N');For a KFF ....It is necessary to have a column to store CCID and a column to Store Structure Information In the above example the Attribute_category is used to store Structure Info.and CCID to store ccid number. These are specified at Registration of the Flexflied.For A DFF...It is enough to store only the Structure Info.Example the Attribute_category may act as an Structure Field.

2. Registering The FlexFiled :

After creating the table and registering it, register the FlexField You You want to Use.Remember : U have to Use this name when referencing the flexfield.Also u have to specify the CCID column & Structure Column for KFF here.Remember : U have to enable the columns of the table here, otherwise u can't define segments for the same.

3. Defining the Segments :

Every Flexfiled must have Segment Qualifiers And FlexField columns these are defined in the AOLat Segment Definition.After defining these segments freeze and compile the flexfield segments definition.These 3 steps complete the process of Registration of table and flexfield & definition of Segments.Some Finer Points :1. You have to check the Dynamic Insertion Allowed ( for KFF only) to allow the users to dynamically createan intelligent combination key.2. You can check the Protect check box to ensure that users do not change the definition of flexfield by mistake.Incorporating DFF's / KFF's in the forms :The whole process essentially requires 4 steps ....1. Modify 7 triggers2. Create Hidden Fields corresponding to the segments3. Define the FlexField in the New-Form-Instance-Trigger

4. Set the Profile Options

( Flex:Open_Descr_Window , Flex:Open-Key_Window ) to YESGeneric Activities to Open A Form In Apps ....You have to go through some steps to enable a form in Apps.
1. Open your Template.fmb ( this is provided by Oracle-Apps) .
2. Delete the BLOCKNAME ( datablock , canvas , window ) from the Template.fmb
3. Save this form Module with ur custom name.
4. Create A Canvas , subclass it with Profile class Canvas
5. Similary create a Window ( subclass it with Window profile class) , Datablock.
6. Modify the APPS_CUSTOM package body with following code....if (wnd = '') thenapp_window.close_first_window;Give your name of Window at the Bolded place.
7. Modify The pre-form trigger as follows..app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');Give Your block name here at the bolded place.

Now modify the required triggers for implementing the DFF's and KFF's ......

.1. The Following triggers should be change...a. When-new-form-instanceb. pre-queryc. post-queryd. WHEN-NEW-ITEM-INSTANCEe. PRE-UPDATEf. WHEN-VALIDATE-RECORDg. WHEN-VALIDATE-ITEMadd FND_FLEX.EVENT('EVENT NAME') where EVENT NAME is the trigger name itself.eg. FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');

2. Create Hidden Fields ( Set Canvas to null) . These should be as many as the number of segments u defined.

3. Define Your flex-filed at the New-Form-Instance trigger as follows ...

FND_KEY_FLEX.DEFINE(BLOCK=>'FLEX_BLOCK',FIELD=>'KFF_SEGS',APPL_SHORT_NAME=>'AR', DESC_FLEX_NAME=>'DFF_FLEX')for DFF....Block - is the block name in the formField - Field which is acting as DFF/KFFAppl_Short_name :- AR/ AP / CS etc.,

Desc_FLEX_NAME :- Name of the Flex Filed U have given at the time of Registration.For KFF.....CODE - the number u give at the time of registering ur KFFNUM - Use the following SQL to get the number . ( default is 101 )

SELECT ID_FLEX_NUM FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE='CODE';

4. Create a TEXT-FIELD to act as DFF or KFFFor DFF subclass it with ....TEXT_ITEM_DESC_FLEXFor Kff subclass it with ...TEXT_ITEM itself5. Save ur work andattach this form to a functionFunction to a menu.That's all....Good Luck !! Happy Experimenting...




SQL*Plus




1 Which command in SQL*Plus is used to save the query output to a file?

SPOOL


2 How would you extract a SQL statement in the SQL buffer of SQL*Plus?

Enter a SLASH (/)

3 What is the default display length of the DATE Datatype column?

Eight

4 Which Clause in a query limits the rows selected?

WHERE

5 Which SQL*Plus command always overwrites a file?

SPOOL

6 Which single-row function could you use to return a specific portion of a character?

SUBSTR

7 Which of the following is not a Group Function?

(AVG, COUNT, LEAST, STDDEV, VARIANCE)LEAST

8 When using multiple tables to query information, in which Clause do you specify the table names?

FROM

9 What are the special characters allowed in a table name?

(&, #, @, $)#, $

10 What is the default length of a CHAR Data type column, if no length is specified in the table definition?

1

11)Which named PL/Sql program must return a value?

FUNCTION must include a RETURN statement and must return a value

12 How do you return multiple values from a procedure?

Use OUT parameterOUT parameters pass values out of a procedureIN parameters pass values into the procedure

13 Which section of the PL/Sql block handles errors and abnormal conditions?

EXCEPTION SECTION

14 What is the mandatory clause in a SELECT statement when used inside a PL/Sql block?

INTO

15 In which section of a PL/Sql block is a constant assigned value?

DECLARATION SECTION

16 What is the name of the Pl/Sql block that is associated with a table and executes automatically when an event occurs?

TRIGGER

17 What type of constant can be defined when you declare a variable?

NOT NULL

18 What is the PL/Sql stored program that fire when an event occurs?

TRIGGER

19 In a PL/Sql block, what is the value of SQL%NOTFOUND before executing any DML Statement?

NULL

20 Which of the following requires an explicit cursor if processing more than one row?

(SELECT, UPDATE, DELETE, INSERT)SELECT

21 Which commands are allowed inside a PL/Sql block?

(TRUNCATE, DELETE, SAVEPOINT, ALTER TABLE)DELETE, SAVEPOINTDDL Statements are not allowed inside a PL/Sql blockTRUNCATE and ALTER TABLE are DDL statements

22 What is the PRAGMA exception_init used for?

To associate an exception name with and exception numberException_init is used to associate (give a name) to an error number

23 What command do you use to induce an error condition?

RAISE

24 What causes a TOO_MANY_ROWS exception?

A SELECT INTO returned more than one row

25 Which of the following types of exceptions cannot be handled in an exception?

(Syntax Errors, database Errors, Datatype Mismatch Errors, Divide By Zero Errors)Syntax ErrorsSyntax Errors are handled by PL/Sql compiler and not the runtime engineThe exception section cannot handle them

26 What function can be used to retrieve the error number for the current exception?

SQLCODE

27 What function can be used to retrieve the text associated with the current exception?

SQLERRM

28 What Clause do you need to include in your exception section to handle any exception not previously specified?

WHEN OTHERS

29 At a minimum, how many join conditions should be there in the WHERE Clause to avoid a Cartesian join if there are Three Tables in the FROM Clause?

2There should be at least (n - 1) join conditions when joining (n) tables to avoid a Cartesian join

30 A view can only be used to query and update data, you cannot Insert into or delete from a view.

True or falseFALSE

31 Which option is not available in Oracle when modifying tables?

(Add new Columns, Rename an Existing Column, Drop an Existing Column)Rename an Existing ColumnYou cannot rename an Existing column using ALTER TABLE

REPORTS QUESTIONS

REPORTS

1. What is a Lexical Parameter?

Lexical parameters are used to substitute multiple values at runtime and are identified by a preceding ‘&’. Lexicals can consist of as little a one line where clause to an entire select statementLexical Parameters are used to execute query dynamically.Example: An example of a lexical parameter usage in a select statement is as follows

Select * from emp, deptno&where.

In the properties of the 'where' user parameter, make sure that the data type of the 'where' user parameter is set as character. If you know the maximum length that your where clause is going be, You can set the width of the where parameter to be slightly greater than that number. Otherwise, set it to some number like 100.If your lexical parameter ('where') width is not enough to hold the where condition assigned to it, you will receive one of the following errors depending on your Reports version.REP-0450 - Unhandled exception,and ORA-6502- PL/SQL numeric or value error.orREP-1401 - Fatal PL/SQL error in after triggerand ORA-6502-PL/SQL numeric or value error.

2. What is a Bind Variable?

Bind parameters are used to substitute single value at runtime for evaluation and are identified by a preceding ‘:’. An example of a bind parameter in a select statement is provided below, where :P_EMP is the bind parameter reference.Select ename,empnoFrom empWhere empno= :P_EMPThese are used as tokens while registering concurrent program.

3. Difference between lexical and bind variable?

Bind references are used to replace a single value in SQL or PL/SQL. 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. Binds may not be referenced in the FROM clause. An example is:SELECT ORDID, TOTALFROM ORDWHERE CUSTID = :CUSTLexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value. An example is:SELECT ORDID, TOTALFROM &ATABLE

4. How many types of Triggers are there and what are they? Tell their sequence of execution.

Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database. To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers (you cannot create new global report triggers):Before Parameter Form triggerAfter Parameter Form triggerBefore Report triggerBetween Pages triggerAfter Report triggerBefore Report trigger and After Report trigger should be declared compulsory. In the Before Report trigger we declare the srw.user_exit(‘ fnd srwinit’) user exist and in the After Report trigger srw.user_exit (‘fnd srwexit’)The sequence/order of events when a report is executed is as follows:Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).
2 After Parameter Form trigger is fired (unless the user cancels from the Runtime ParameterForm).
3 Report is "compiled."
4 Queries are parsed.
5 Before Report trigger is fired.
6 SET TRANSACTION READONLY is executed (if specified via the READONLY argumentor setting).
7 The report is executed and the Between Pages trigger fires for each page except the last one.(Note that data can be fetched at any time while the report is being formatted.) COMMITscan occur during this time due to any of the following--user exit with DDL, SRW.DO_SQLwith DDL, or if ONFAILURE=COMMIT, and the report fails.
8 COMMIT is executed (if READONLY is specified) to end the transaction.
9 After Report trigger is fired.10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via theONSUCCESS argument or setting.
Cautions=========
1. In steps 4 through 9, avoid DDL statements that would modify the tables on which thereport is based. Step 3 takes a snapshot of the tables and the snapshot must remain validthroughout the execution of the report. In steps 7 through 9, avoid DML statements thatwould modify the contents of the tables on which the report is based. Queries may beexecuted in any order, which makes DML statements unreliable (unless performed on tablesnot used by the report).2. If you specify READONLY, you should avoid DDL altogether. When you execute a DDLstatement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If youare using READONLY, this will prematurely end the transaction begun by SETTRANSACTION READONLY.


Report trigger restrictions=============================

1. If you are sending your report output to the Runtime Previewer or Live Previewer, youshould note that some or all of the report triggers may be fired before you see the reportoutput. For example, suppose that you use SRW.MESSAGE to issue a message in theBetween Pages trigger when a condition is met. If there are forward references in the report(e.g., a total number of pages displayed before the last page), Report Builder may have toformat ahead to compute the forward references. Hence, even though you have not yet seena page, it may already have been formatted and the trigger fired.

2. In report triggers, you can use the values of report-level columns and parameters. Forexample, you might need to use the value of a parameter called COUNT1 in a condition(e.g., IF :COUNT1 = 10). Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.

3. In the Before and After Parameter Form, and Before and After Report triggers, you can setthe values of parameters (e.g., give them a value in an assignment statement, :COUNT1 =15). In the Before and After Report triggers, you can also set the values of report-level,placeholder columns.

4. In the Between Pages trigger, you cannot set the values of any data model objects. Note alsothat the use of PL/SQL global variables to indirectly set the values of columns or parametersis not recommended. If you do this, you may get unpredictable results.

5. If you run a report from Report Builder Runtime (i.e., not the command line orSRW.RUN_REPORT), you should commit database changes you make in the BeforeParameter Form, After Parameter Form, and Validation triggers before the report runs.When running in this way, these triggers will share the parent process’ database connection.When the report is actually executed, however, it will establish its own database connection.

6. A lexical reference cannot be used to create additional bind variables after the AfterParameter Form trigger fires. For example, suppose you have a query like the following(note that the WHERE clause is replaced by a lexical reference):SELECT ENAME, SAL FROM EMP&where_clauseIf the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, youmust specify the value in the After Parameter Form trigger or earlier. You would get anerror if you supplied the following value for the parameter in the Before Report trigger. Ifyou supplied this same value in the After Parameter Form trigger, the report would run.WHERE SAL = :new_bind

5. What is a Format Trigger?

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.
The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.

6. What is Anchoring?

It is a feature thru which we can control the position of the boiler plate or data fields in layout.Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol is attached to the parent object.When you create a default layout, Reports will create some of its own implicit anchors. These are not visible. There may be occasions when you want to create your own explicit anchors to force objects to be positioned together or to conditionally specify when the object prints.You create an explicit anchor as follows:

1. Select the Anchor tool in the Layout Tool Palette.

2. Click on an edge of the Child object.

3. Move the cursor to the edge of the Parent object and double click to fix the anchor.

You can position the anchor at any distance down the edge of the object. The distance is a percentage of the total length of the edge. You can adjust this position in the anchor property sheet.

Examples of using explicit anchors:

ANCHORING BOILERPLATE TO A FRAME--------------------------------You may want to display some boiler plate to the right of, and half way down a vertical list of records.In this case, you would create an anchor from the child boilerplate to the parent, group or repeating frame. Ensure the parent end point is 50% down the right edge of the frame.ANCHORING CONDITIONAL OBJECTS----------------------------To adjust the position of a layout object if the anchoring parent does not display, you can define your explicit anchor as collapsible either horizontally or vertically. The child layout object then collapses, to suppress additional spacing, if the parent object does not print.An example of where you might use this would be on Mailing Labels.Mailing Labels often include optional fields to allow variable number of lines in an address. You may want to suppress the fields that are null, so that the address in the labels does not have gaps between the lines.For example:f_namef_address1f_address2f_address3f_address4where f_address2 is an optional field.1. Select f_address2 in the layout editor and go into the property sheet.2. In Reports V2.5, under the general layout tab, click on the Format TriggerEdit button to create the following format trigger.In other versions of Reports, under advanced layout, click on the FormatTrigger to create the following format trigger.FUNCTION f_address2 RETURN BOOLEAN ISBEGINIF :address2 IS NULL THENRETURN (FALSE);ELSERETURN (TRUE);END IF;END;

3. Then create an anchor from f_address3 (the field below) upto to f_address2 (the optional field). In the anchor properties place a check in the collapse vertically check box.

4. Create another anchor, this time from f_address4 to f_address3, again setting it to collapse vertically. This process needs to be done for all the fields below the optional field to avoid any unwanted spaces.

7. What is 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.Repeating frames are place holders for records. Repeating frames print once for each record of a group and control record-level formatting. Reports will generate one repeating frame for each group when you create a default layout.Reports will place containers of columns inside of the frames. Each repeating frame retrieves only one row in its fetch cycle for any one repetition. Until it is constrained by another frame, it will repeat itself until the while loop condition can no longer be satisfied.We give group in data model as source to repeating frame.

8. What are Confined Mode and Flex Mode?

Confined mode allows objects to be locked into the place in the layout.
Objects are maintained within their containers.
CONFINE mode is not for a specific object, but applies to all objects on the layout when it is enabled (locked).
When it is turned off (unlocked), you are allowed to move an object outside its surrounding frame.
When it is turned on (locked), you are unable to move an object outside its surrounding frame. This is to prevent unnecessary 'Frequency Errors'.

Flex mode preserves the layout structure while allowing expanding and shrinking of the layout.

FLEX mode, when enabled, allows surrounding frames to grow as an object is resized or moved. Only one object at a time can be moved either vertically or horizontally, not diagonally.

9. What are User Exits?

You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder.You can write the following types of user exits:* ORACLE Precompiler user exits*

OCI (ORACLE Call Interface) user exits* Non-ORACLE user exits.User exits can perform the following tasks:* Perform complex data manipulation* Pass data to Report Builder from operating system text files* Manipulate LONG RAW data* Support PL/SQL blocks* Control real time devices, such as a printer or a robotYou can use user exits for other tasks, such as mathematical processing.However, it is recommended that you perform such tasks with PL/SQL within Report Builder itself.

Ex: FNDSRWINIT, FNDSRWEXIT.

10. How do I Register a Custom Report?

Step 1: Register a concurrent program executableNavigate to the Define Executable form (AOL Reference manual pg 9-84)This determines the type of program being run,ie an Oracle Report. Fill in the executable name, application and execution method. For the Execution File, fill in just the filename. The concurrent manager will look in the appropriate directory under the application's top directory.

For spawned programs, the file must be in the bin directory, for Oracle Reports the rdf file must be in the srw directory.For PLSQL concurrent programs, put the name of the stored procedure.

Step 2: Define the concurrent programNavigate to the Define Concurrent Program form (AOL Reference manual pg 9-87)This form links a concurrent program to the executable you just defined, as well as defines the programs parameters, incompatibilities, and other options.Enter the concurrent program name, application, short name and description. Check Standard Submission if you want to be able to submit this program from the Standard Report Submission form.Enter the name of the executable you defined and any report information if necessary. Also define any parameters your program needs here and any incompatibilities.

Step 3: Add the concurrent program to a Report GroupFirst you will need to find the name of the Report Group to use.Go to Security->Responsibility and query the responsibility you want to run the program with.It should show a Report Group name. Query this name in Security->Responsibility->ReportAdd your new program to the list of available programs. Now when you go to submit a request with this responsibility, you will be able to submit your custom program.

11. What is a Token?

Token is used to attach a bindvariable to a report parameter while registering the report as concurrent program.

12. What is the use of ‘Send to Back’ and ‘Bring to Front’?

To change the order in which objects are layered on top of each other.Send to Back to move the object behind all other objects.Bring to Front to move the object in front of all other objects.

13. If 2nd parameter value is based on 1st parameter then how do u declare it?

Let v2 be the value set definition of 2nd parameter and v1 be the value set definition for the first parameter thenIn the value set definition of v2 = value $FLEX$.v1

14. What are Summary Column, Place holder Column, and Formula Column?

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.A placeholder is a column for which you set the data type and value in PL/SQL that you define. You can set the value of a placeholder column in the following places.
A place holder column stores a value which we can refer in the layout.A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula columns should not be used to set values for parameters.

15. How do u hide fields in a Report?

Ans: Using the Format Trigger we can hide the fields./* Suppose that you are building a master/detail report** and, if no detail records are retrieved for a master** record, you do not want the boilerplate labels to** appear. To do this, you first create a summary** column called MYCOUNT with a Function of Count in** the source group of the master repeating frame.** In the format trigger for the group frame that** surrounds the detail repeating frame and its labels,** you enter the following:*/function my_formtrig return BOOLEAN isbeginif :mycount = 0 thenreturn (false);elsereturn (true);end if;end;

16. What kinds of reports u have worked on?

Custom Reports and Standard reports

17. Name Custom Reports and…-------------------------------------


Tell some of reports that you did

18. How many types of Report formats we have?

Custom Reports and Standard reports

19. What is the minimum number of groups required for a Matrix type report?

To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.

View the video report builder help

20. What is the difference between Bitmap and Character based reports?

Explain in detail.Bitmap vs. Character-Mode Report DesignHere is an example to help explain how Oracle Reports are designed and printed in both the bitmap and character-mode environments.Assume you wish to print "Cc" where "C" is a different font and a larger point size than "c" and is in boldface type (where "c" is not).In Oracle Reports Designer, bitmap mode, you can make "C" bold and in a different font and point size than "c". This is because you are generating postscript output. Postscript is a universal printer language and any postscript printer is able to interpret your different design instructions.In Oracle Reports Designer, character mode, the APPLICATIONS STANDARDS EQUIRE the report to be designed in ONE FONT/ ONE CHARACTER SIZE. Character mode reports generate ASCII output. In ASCII you cannot dynamically change the font and character size. The standard is in effect so a report prints as identically as possible from both conventional and postscript printers.Bitmap vs. Character-Mode Report PrintingThese sequences contrast the two printing environments. In postscript, "C" can be in a different font and point size than "c". Both or either could also be bold, for example.In ASCII, "C" must be in the same font and character size as "c". Both or either could also be bold, for example.Oracle ReportsDesigner----- ar20runb ------ Postscript ---- Postscript--- "Cc"executable language printer output"Cc"-------- ar20run ----*-- ASCII--------- Printer ------ "cc"executable characters outputSRW driver(for bold, underline,page break escape sequences)

21. What Printer Styles are used for? Did you develop any printer styles? Srw.driver

22. How do you fix a performance problem in a Report?

Check Report main query and fine tune it.Create indexes on columns used in where condition (eliminate full table scan)Enable Trace(set trace on in before report and set trace off in after report)Before Report:srw.do_sql('alter session set sql_trace=true');After Report:srw.do_sql('alter session set sql_trace=false');Trace file will be generated at location:select value from v$parameterwhere name = 'user_dump_dest';To better see execution plans in a trace file, you need to format thegenerated trace file with tkprof statement.

23. What is the significance of p_conc_request_id?

P_conc_request_id is declared as the user parameter for reports which will get org specific data. P_conc_request_id datatype is character and length is 15.

24. How to call a stored procedure in the report?

What is the use of that?Package.prcedure26. How do you set ORG_ID in a SQL*Plus session?

Call the Below Anonymous pl/sql block.

BEGINfnd_client_info.set_org_context(‘204');END;Orexec dbms_application_info.set_client_info(‘org_id’);


27. While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass two additional parameters.

Can u list them?p_errorcode and p_errorbuffer as out parameters in main procedure.It requires 2 IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps. They are1. errcode IN VARCHAR22. errbuff IN VARCHAR2

28. How we can call from form to form, form to report?

Calling a Form from another Form: FND_EXECUTE(…);NOTE: The calling and called Forms must be registered with Applications.Calling a Report from a Form: FND_REQUEST.SUBMIT_REQUEST(…);NOTE: This method can be used to call any concurrent program.

29. What are logical page and physical page?

In the Runtime Previewer, you can scroll though a single page of report output, page through the entire report, and split the screen to view different sections of the same report concurrently.A physical page (or panel) is the size of a page that will be output by your printer. A logical page is the size of one page of your actual report (it can be any number of physical pages wide or long). The Runtime Previewer displays the logical pages of your report output, one at a time.

30. Why is ref cursor is used in the reports?Dynamic refcursor

31. When we create a report we use the tables, there is some difference when we use the multi-org tables and ordinary tables, can u tell the difference?Set p_conc_request_id for org specific tables.

32. We have 2 different databases, and each system has 2 tables.

Know there is a link provided between them. The client want a report to be developed based on the 4 tables that r there in the 2 different databases. The solution must be efficient?Assume that the two databases be DB1 and DB2. At one time I could connect to only one database say DB1. Now I should able to access the tables in DB2 from DB1. First I create a DBlink in DB1 that access the tables in DB2. Using the DBlink, create snapshots for each of the tables in DB2. Now we can use these Snapshots in query, as if like tables in DB1.The purpose for creating snapshot is both security and to reduce the network load, for each access of the tables in DB2.

PO INTERFACE

PO INTERFACE


PO_HEADERS_INTERFACE is the interface table

that imports header information from

e– Commerce Gateway for blanket purchase orders and catalog quotations.



INTERFACE_HEADER_IDBATCH_IDINTERFACE_SOURCE_CODE



PO_LINES_INTERFACE is the interface table that imports lines information from e– commerce Gateway for blanket purchase orders and catalog quotations.



INTERFACE_LINE_ID

INTERFACE_HEADER_IDACTION NULL Action to be completed:

New or AddGROUP_CODE (Null) Indicates the grouping of the requisition lines



PO_DISTRIBUTIONS_INTERFACE is the interface table that imports distribution information from e– Commerce Gateway for blanket purchase orders and catalog quotations.



INTERFACE_HEADER_ID

INTERFACE_LINE_ID

INTERFACE_DISTRIBUTION_ID

ORG_ID

PO_REQUISITIONS_INTERFACE_ALL



contains requisition information from other applications.

Import feature uses this information to create new requisition headers, lines and distributions.

TRANSACTION_ID (PK) Transaction unique identifierPROCESS_FLAG NULL Transaction processing state



PO_REQ_DIST_INTERFACE_ALL Is the interface table that creates multiple distributions using Requisition Import.

ACCRUAL_ACCOUNT_ID Unique identifier for the General Ledger accrual account

ALLOCATION_TYPE Specifies the method of allocation across distributions.

Can be PERCENT.ALLOCATION_VALUE Allocation split value

BATCH_ID Import batch identifier

BUDGET_ACCOUNT_ID Unique identifier for the General Ledger budget account

CHARGE_ACCOUNT_ID Unique identifier for the General Ledger charge account

RCV_HEADERS_INTERFACEis the interface table that stores receiving header information.

HEADER_INTERFACE_ID Interface EDI header unique identifier

GROUP_ID NULL Interface group for set processingEDI_CONTROL_NUM EDI transaction control number if datais sent via EDIPROCESSING_STATUS_CODE Processing status of the interfaceheader rowRECEIPT_SOURCE_CODE Source type of the shipmentASN_TYPE NULL The document type: values areASN, ASBN, or RECEIVETRANSACTION_TYPE The transaction purpose code: values areNEW, REPLACE, ADD,or CANCELRCV_LOTS_INTERFACEHolds temporary lot number transaction records for a parent record in the RCV_TRANSACTIONS_INTERFACE table.RCV_TRANSACTIONS_INTERFACEstores information about receiving transactions that are waiting to be processed by the receiving transaction processor or were rejected due to an error when the transaction processor attempted to process the transaction.TRANSACTION_TYPETRANSACTION_DATEPROCESSING_STATUS_CODEInterface Table --PO_REQUISITION_INTERFACE_ALLProgram Name to Launch the PO Import program -Requisition Import programIn the first phase, the program validates your data and derives or defaults additional information. The program generates an error message for every validation that fails and creates a rowPO_REQUISITIONS_INTERFACE_ALLIt contains requisition information from other applications. Each row includes all the information necessary to create approved or unapproved requisitions in Oracle Purchasing.The Oracle Purchasing Requisition Import feature uses this information to create new requisition headers, lines and distributions.PO_REQUISITIONS_INTERFACE_ALLtable are identical to the corresponding columns in thePO_REQUISITIONS_HEADERS_ALL,PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALLtables.Po_requisition_interface_allINTERFACE_SOURCE_CODE --VDP,SOURCE_TYPE_CODE --INVENTORY,DESTINATION_TYPE_CODE --EXPENSE,AUTHORIZATION_STATUS --Status Always Approved we will get ..,REQ_NUMBER_SEGMENT1 --Request id,PREPARER_ID -- ASSOCIATE ID comes from VDP,LINE_TYPE_ID,LINE_TYPE --vArchar Requisition line type name,QUANTITY --Quantity,UNIT_PRICE,CHARGE_ACCOUNT_ID -- to be hard coded based on the line type,DESTINATION_ORGANIZATION_ID --v-org_id,DELIVER_TO_LOCATION_ID -- location id,DELIVER_TO_REQUESTOR_ID -- ASSOCIATE ID Same as Prepare_id,ITEM_ID -- item code,ITEM_DESCRIPTION --Based on the item code description is taken,CATEGORY_ID,UOM_CODE,CURRENCY_CODE --if this is supplied then next two should be filled,RATE,RATE_DATE,RATE_TYPEIf you use Oracle Master Scheduling/MRP or a non– Oracle MRP system with Oracle Purchasing, you may find that you need to reschedule requisitions as your planning requirements change.Reschedule Interface Table Since you have already loaded your requisitions into Oracle Purchasing, you simply need to identify for Oracle Purchasing the requisition lines you want to reschedule. After you identify each line to reschedule, you can update the quantity and the need– by date for the corresponding requisition line.PO_RESCHEDULE_INTERFACE table for each change you want to make to a requisition. Each row includes the requisition line identifier, the new quantity, and the new need– by date for the requisition line. You run the Requisition Reschedule program to implement the changes. Import Requisitions from the External System to Oracle Purchasing Write a Import Program to load the external data Dump data into the some interface table .Validate the data through some PL/SQL(Applying Business Logic---Run Import ProgramRequisition is imported and created as manynumber of requisition as successful records .Others -if error then run exceptional report to see the reason of failure.

ORACLE XML PUBLISHER & ORACLE DISCOVERER

ORACLE XML PUBLISHER & ORACLE DISCOVERER



1.What Concurrent managers are supported by the Sysadmin team?

The Internal, Standard, and Conflict resolution managers are the managers supported by the sysadmin team. The setup and related issues to other managers would need to be directed within the associated product group.
ie. Inventory manager

Where do the concurrent manager log files reside?

APPLCSF\logWhere do the concurrent request and log files reside?APPLCFS\out or \out

Where are temporary files located?

APPLPTMPAPPLTMPREPORTS25_TMP

2.How do you change PMON to LOCK?

Check process monitor (PMON) method. Connect as database user "APPS".SELECT profile_option_valueFROM fnd_profile_option_valuesWHERE level_id = 10001AND level_value = 0AND application_id = 0

AND profile_option_id =(SELECT profile_option_idFROM fnd_profile_options

WHERE profile_option_name = 'CONC_PMON_METHOD');
This should return one row with a value of 'LOCK'If the value is 'RDBMS' or 'OS' run the script

$FND_TOP\sql\AFIMPMON.SQL - this will set the PMON method to LOCK instead of RDBMS.Stop database and restart database server.If not already shutdown in previous step, stop and restart the database.If running on NT, restart the NT Server completely.**************************************************************

3. How to start the CCM under NT?

On the NT server, Click on Start / Settings / Control Panel / Services and look for: OracleConcMgr. Click once on the service and once on the Start button.

4. How to shut down the CCM under NT?

On the NT server, Click on Start / Settings / Control Panel / Services and look for: OracleConcMgr. Click once on the service and once on the Stop button. Command line usage to Start:net start OracleConcMgrCommand line usage to Shutdown:net stop OracleConcMgr**************************************************************

5. How do I terminate a concurrent request that cannot be canceled? I

dentify the request number to terminate?Please shut down the concurrent managers and issue the following sql command as applsys:update fnd_concurrent_requestsset status_code = 'E', phase_code = 'C'where Request_id = ; (reqnum = request number)**************************************************************One of your manager(s) are not activating.Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, delete the extra rows.This is done from sqlplus as apps as follows:select *from SYS.DUAL;Any extra rows should be deleted.delete rownumfrom SYS.DUAL;rownum = the row number to delete Any extra rows for apps.fnd_dual must be removed performing the following SQL command:delete from fnd_dualwhere rownum < (select max(rownum) from fnd_dual); Bounce your Managers. ************************************************************** If the ICM itself should go down, requests will continue to run normally, except for 'queue control' requests. If the ICM should go down, you can restart it with 'startmgr'. You do not need to kill the other managers first. **************************************************************

6. How do I purge requests that are in Pending status?

The Purge Concurrent Requests program will only purge requests that are in Completed status. You will have to set their status to Completed before purging them. Using sqlplus as APPLSYS perform the following:UPDATE fnd_concurrent_requestsSET phase_code = 'C' WHERE phase_code = 'P'Pl/sql concurrentfact: Oracle Applications 11.0symptom: Cannot run a custom stored procedure as a concurrent requestsymptom: Concurrent request completes with errorssymptom:

PLS-306: Wrong number or type of argumentssymptom:

ORA-6550: line 1 column 7cause: All stored procedures that run as concurrent programs must specify an errbuf and retcode as the first and second parameters in the stored procedure (see example below).

These parameters should be OUT parameters and they are the only OUT parameters that can be specified in the parameter list. CREATE PROCEDURE sample_proc ( errbuf OUT VARCHAR2 , retcode OUT NUMBER , argument1 IN VARCHAR2 , argument2 IN VARCHAR2 )

7.How can users submit requests with CONCSUB without giving them the APPS password?

Try this:1. As the applmgr user, create a shell script that runs CONCSUB Either hardcode the parameters for the report, or pass them in as parameters to the script.2. Change the permissions on this script to 700. Now no one can read this script and get the password.3. Create another script that calls the first script. Pass parameters along if you need to. Change the permissions on this script to 6755. Now any user can execute and read the second script, which calls the first one. Have the users run this script to submit their requests without knowing the password.

8. What resources are provided for developing applications which will be integrated into Oracle Applications Release 11?

a. The Oracle Applications Developer's Guide Release 11 and the Oracle Applications User Interface Standards Release 11.b. The AU_TOP/forms/US/TEMPLATE.fmb for developing a new form.c. The AU_TOP/forms/US/APPSTAND.fmb contains standard property classes for your runtime platform.d. The AU_TOP/resource/FNDSQF.pll contains routines for Flexfields, Function Security, User Profiles, Message Dictionary.e. The AU_TOP/resource/APPCORE.pll contains standard User Interface routines.f. The AU_TOP/resource/APPDAYPK.pll contains the Calendar Widget routines.g. The AU_TOP/resource/CUSTOM.pll for adding custom code which affects Oracle Applications forms without changing Oracle Applications code.h. The AU_TOP/resource/GLOBE.pll allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms without modifying the base Oracle Applications forms. Globe calls routines JA, JE, and JL libraries.i. The AU_TOP/resource/JA.pll called from Globe and contains Asia/Pacific code.j. The AU_TOP/resource/JE.pll called from Globe and contains EMEA (Europe/Middle East/Africa) code.k. The AU_TOP/resource/JL.pll called from Globe and contains Latin America code.l. The AU_TOP/resource/VERT.pll allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modifying the base Oracle Applications forms.m. Oracle Developer/2000 Server Release 1.6.1.

NOTE: All FMB and PLL files must be migrated to your desktop if you intend to develop and integrate custom applications into Oracle Applications Release 11.9. What are the supported versions of Forms and Reports used for developing on Oracle Applications Release 11?a. The following supported versions are provided in Developer/2000 Release 1.6.1:

i. Forms 4.5
ii. Reports 2.510.

How do I compile and/or generate an Oracle Applications form?

a. UNIXcd $AU_TOP/forms/USf45gen module=FNDSCAUS.fmb userid=APPS/APPS output_file=/appl/v1100000/fnd/11.0.28/forms/US/FNDSCAUS.fmx module_type=form batch=no compile_all=special

b. Windows NTcd F:\applr11\au\11.0.28\forms\USf45gen32 userid=APPS/APPS module=FNDSCAUS.fmb output_file= applr11\fnd\forms\US\FNDSCAUS.fmx module_type=form batch=no compile_all=special11.

How do I open, compile and/or generate a custom Oracle Applications form on my desktop?
a. To port the AU_TOP/forms/US and AU_TOP/resource files to your Windows desktop:

i. Make copies of all required files.
ii. Replicate the AU_TOP directory structure on your desktop.
iii. Move the files to their appropriate AU_TOP/forms/US for FMB and AU_TOP/resource for PLL.
iv. Include the AU_TOP/forms/US and AU_TOP/resource directories in your FORMS45_PATH.
v. Open, compile and/or generate forms.

NOTE: The FORMS45_PATH is specified either in your Registry or oracle.ini.
NOTE: It may sometimes be necessary to convert FMB -> FMT and PLL -> PLD before porting from a Unix platform to your desktop.12. How do I add a CUSTOM_TOP to Oracle Applications?

a. Replicate an existing Oracle Applications product directory structure underneath your APPL_TOP:APPL_TOPXXCUS_TOPbin------forms-----html-----lib-----log-----mesg-----out-----reportsUS USb. Make sure all the permissions on the files and directories are the same as the other product directories.c. Add the full path to this CUSTOM_TOP to your APPLSYS.env ( Source your APPLSYS.env) or your Windows NT Registry:UNIX: /u01/oracle/apps/vd11/xxcus/11.0.28Windows NT: D:\oa\appltst\vd11\xxcus\11.0.28d. Login to Oracle Applications using the System Administrator or Application Developer Responsibility.e. Navigate: Application -> Register and add your new CUSTOM_TOP to Oracle Applications.Application Short Name Basepath Description------------------------- ------------------- ----------------- ------------------Custom Application XXCUS XXCUS_TOP Custom Applicationf. Shutdown and restart your Internal Concurrent Manager (ICM) so that the concurrent manager will recognize the change to the environment that was made to the APPLSYS. ENV and Registry. To make a query only menu, first determine which function tomodify.

2. Grant query only access on the Users form in the System Administrator Security:User:Define form, find the top most menu for the System Administrator GUI Responsibility in the Menu field of the Security:Responsibility form. For the System Administrator GUI Responsibility it is Navigator Menu - System Administrator GUI.

3. Navigate to the Application:Menu form and query for this menu in the User Menu Name field. Query through several submenus to get to the name of the function needed; in this case, Users.
Security Menu - System Administrator GUI
User Menu - System Administrator GUI Users

4. Navigate to the Application:Function form and query for the function in the User Function Name field of the Description zone.

5. Create a new Function using this function name, and enter QUERY_ONLY=YES in the parameters field.

6. Create or modify the menu and menu structure that needs to be attached to this function.

7. Create or modify the responsibility to associate with the above menu/menu structure.

8. Test the new menu and function. Exit and restart the Oracle Applications session for any changes to responsibilities to take effect.



DISCOVERER


Oracle Discoverer introduction
http://www.uploadbigfiles.net/download.php?file=170008 Oracle Discoverer 3-1 Introduction.doc
http://www.uploadbigfiles.net/download.php?file=316009 Oracle Discoverer Plus - Introduction.doc

ORACLE APPS E-BOOKS AND AOL

ORACLE APPS E-BOOKS



Easy to Learn Oracle Apps Tutorials : http://www.exforsys.com/content/category/17/260/342/

Oracle Apps Student Guides : http://www.megaupload.com/?d=YTD92KVF http://files.filefront.com/11i+Student+Guidesrar/;5289744;;/fileinfo.html

Oracle Student Guides : http://www.megaupload.com/?d=WPXTBMMV

Oracle Financials Open Interface Manual : http://www.megaupload.com/?d=K0QVC3ID

Video Tutorials on Oracle General Ledger : http://www.megaupload.com/?d=JOET6PQS

Oracle ADI and 11i Tip Sheets : http://www.megaupload.com/?d=UYQDBNC9

Oracle Student Guides on HRMS : http://www.megaupload.com/?d=PT3HT7U2

GL Practices : http://www.megaupload.com/?d=7TXUMWZ1

Order Management Demos : http://www.megaupload.com/?d=DVNKZRL7

OPM Setup Demos : http://www.megaupload.com/?d=AOXMEDDC

Oracle Student Guide on Order-To-Cash Life Cycle: http://www.megaupload.com/?d=0TADDC1I

Standard Reports in GL,AR,AP,PO: http://www.megaupload.com/?d=ZT2D0VWX

Good Document on How to Integrate Custom Report with Oracle Apps : http://www.megaupload.com/?d=ABHRCOTK

SQL How-To's for Practice: http://www.megaupload.com/?d=RHYD2D3G

White papers on AP : http://www.megaupload.com/?d=DYQZXAT3 http://d.turboupload.com/d/732112/AP.zip.html

White papers on GL : http://www.megaupload.com/?d=TKV860E3

Oracle Financials Open Interface Manual
: http://www.megaupload.com/?d=K0QVC3ID

1.What Concurrent managers are supported by the Sysadmin team?
The Internal, Standard, and Conflict resolution managers are the managers supported by the sysadmin team. The setup and related issues to other managers would need to be directed within the associated product group. ie. Inventory managerWhere do the concurrent manager log files reside? APPLCSF\logWhere do the concurrent request and log files reside?APPLCFS\out or \outWhere are temporary files located?APPLPTMPAPPLTMPREPORTS25_TMP

2.How do you change PMON to LOCK?Check process monitor (PMON) method. Connect as database user "APPS".SELECT profile_option_valueFROM fnd_profile_option_valuesWHERE level_id = 10001AND level_value = 0AND application_id = 0
AND profile_option_id =(SELECT profile_option_idFROM fnd_profile_optionsWHERE profile_option_name = 'CONC_PMON_METHOD');This should return one row with a value of 'LOCK'If the value is 'RDBMS' or 'OS' run the script$FND_TOP\sql\AFIMPMON.SQL - this will set the PMON method to LOCK instead of RDBMS.Stop database and restart database server.If not already shutdown in previous step, stop and restart the database.If running on NT, restart the NT Server completely.**************************************************************

3. How to start the CCM under NT?
On the NT server, Click on Start / Settings / Control Panel / Services and look for: OracleConcMgr. Click once on the service and once on the Start button.

4. How to shut down the CCM under NT?On the NT server, Click on Start / Settings / Control Panel / Services and look for: OracleConcMgr. Click once on the service and once on the Stop button. Command line usage to Start:net start OracleConcMgrCommand line usage to Shutdown:net stop OracleConcMgr**************************************************************

5. How do I terminate a concurrent request that cannot be canceled? Identify the request number to terminate?Please shut down the concurrent managers and issue the following sql command as applsys:update fnd_concurrent_requestsset status_code = 'E', phase_code = 'C'where Request_id = ; (reqnum = request number)**************************************************************One of your manager(s) are not activating.Check the count in sys.dual, system.dual, & apps.fnd_dual. There should only be one (1) row for each. If the count is greater, delete the extra rows.This is done from sqlplus as apps as follows:select *from SYS.DUAL;Any extra rows should be deleted.delete rownumfrom SYS.DUAL;rownum = the row number to delete Any extra rows for apps.fnd_dual must be removed performing the following SQL command:delete from fnd_dualwhere rownum < (select max(rownum) from fnd_dual); Bounce your Managers. ************************************************************** If the ICM itself should go down, requests will continue to run normally, except for 'queue control' requests. If the ICM should go down, you can restart it with 'startmgr'. You do not need to kill the other managers first. **************************************************************

6. How do I purge requests that are in Pending status?
The Purge Concurrent Requests program will only purge requests that are in Completed status. You will have to set their status to Completed before purging them. Using sqlplus as APPLSYS perform the following:UPDATE fnd_concurrent_requestsSET phase_code = 'C' WHERE phase_code = 'P'Pl/sql concurrentfact: Oracle Applications 11.0symptom: Cannot run a custom stored procedure as a concurrent requestsymptom: Concurrent request completes with errorssymptom: PLS-306: Wrong number or type of argumentssymptom: ORA-6550: line 1 column 7cause: All stored procedures that run as concurrent programs must specify an errbuf and retcode as the first and second parameters in the stored procedure (see example below). These parameters should be OUT parameters and they are the only OUT parameters that can be specified in the parameter list. CREATE PROCEDURE sample_proc ( errbuf OUT VARCHAR2 , retcode OUT NUMBER , argument1 IN VARCHAR2 , argument2 IN VARCHAR2 )

7.How can users submit requests with CONCSUB without giving them the APPS password?Try this:1. As the applmgr user, create a shell script that runs CONCSUB Either hardcode the parameters for the report, or pass them in as parameters to the script.2. Change the permissions on this script to 700. Now no one can read this script and get the password.3. Create another script that calls the first script. Pass parameters along if you need to. Change the permissions on this script to 6755. Now any user can execute and read the second script, which calls the first one. Have the users run this script to submit their requests without knowing the password.

8. What resources are provided for developing applications which will be integrated into Oracle Applications Release 11?

a. The Oracle Applications Developer's Guide Release 11 and the Oracle Applications User Interface Standards Release 11.

b. The AU_TOP/forms/US/TEMPLATE.fmb for developing a new form.

c. The AU_TOP/forms/US/APPSTAND.fmb contains standard property classes for your runtime platform.

d. The AU_TOP/resource/FNDSQF.pll contains routines for Flexfields, Function Security, User Profiles, Message Dictionary.

e. The AU_TOP/resource/APPCORE.pll contains standard User Interface routines.

f. The AU_TOP/resource/APPDAYPK.pll contains the Calendar Widget routines.

g. The AU_TOP/resource/CUSTOM.pll for adding custom code which affects Oracle Applications forms without changing Oracle Applications code.

h. The AU_TOP/resource/GLOBE.pll allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms without modifying the base Oracle Applications forms. Globe calls routines JA, JE, and JL libraries.

i. The AU_TOP/resource/JA.pll called from Globe and contains Asia/Pacific code.

j. The AU_TOP/resource/JE.pll called from Globe and contains EMEA (Europe/Middle East/Africa) code.

k. The AU_TOP/resource/JL.pll called from Globe and contains Latin America code.

l. The AU_TOP/resource/VERT.pll allows Oracle Applications developers to incorporate vertical industry features
(for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modifying the base Oracle Applications forms.

m. Oracle Developer/2000 Server Release 1.6.1.NOTE: All FMB and PLL files must be migrated to your desktop if you intend to develop and integrate custom applications into Oracle Applications Release 11.9.

What are the supported versions of Forms and Reports used for developing on Oracle Applications Release 11?

a. The following supported versions are provided in Developer/2000 Release 1.6.1:

i. Forms 4.5
ii. Reports 2.510.

How do I compile and/or generate an Oracle Applications form?

a. UNIXcd $AU_TOP/forms/USf45gen module=FNDSCAUS.fmb userid=APPS/APPS output_file=/appl/v1100000/fnd/11.0.28/forms/US/FNDSCAUS.fmx module_type=form batch=no compile_all=specialb. Windows NTcd F:\applr11\au\11.0.28\forms\USf45gen32 userid=APPS/APPS module=FNDSCAUS.fmb output_file= applr11\fnd\forms\US\FNDSCAUS.fmx module_type=form batch=no compile_all=special11.

How do I open, compile and/or generate a custom Oracle Applications form on my desktop?

a. To port the AU_TOP/forms/US and AU_TOP/resource files to your Windows desktop:

i. Make copies of all required files.
ii. Replicate the AU_TOP directory structure on your desktop.
iii. Move the files to their appropriate AU_TOP/forms/US for FMB and AU_TOP/resource for PLL.
iv. Include the AU_TOP/forms/US and AU_TOP/resource directories in your FORMS45_PATH.v. Open, compile and/or generate forms.

NOTE: The FORMS45_PATH is specified either in your Registry or oracle.ini.NOTE: It may sometimes be necessary to convert FMB -> FMT and PLL -> PLD before porting from a Unix platform to your desktop.12. How do I add a CUSTOM_TOP to Oracle Applications?a. Replicate an existing Oracle Applications product directory structure underneath your APPL_TOP:APPL_TOPXXCUS_TOPbin------forms-----html-----lib-----log-----mesg-----out-----reportsUS USb. Make sure all the permissions on the files and directories are the same as the other product directories.c. Add the full path to this CUSTOM_TOP to your APPLSYS.env ( Source your APPLSYS.env) or your Windows NT Registry:UNIX: /u01/oracle/apps/vd11/xxcus/11.0.28Windows NT: D:\oa\appltst\vd11\xxcus\11.0.28d. Login to Oracle Applications using the System Administrator or Application Developer Responsibility.e.

Navigate: Application -> Register and add your new CUSTOM_TOP to Oracle Applications.Application Short Name Basepath Description------------------------- ------------------- ----------------- ------------------Custom Application XXCUS XXCUS_TOP Custom Applicationf. Shutdown and restart your Internal Concurrent Manager (ICM) so that the concurrent manager will recognize the change to the environment that was made to the APPLSYS. ENV and Registry. To make a query only menu, first determine which function tomodify.

2. Grant query only access on the Users form in the System Administrator Security:User:Define form, find the top most menu for the System Administrator GUI Responsibility in the Menu field of the Security:Responsibility form. For the System Administrator GUI Responsibility it is Navigator Menu - System Administrator GUI.

3. Navigate to the Application:Menu form and query for this menu in the User Menu Name field. Query through several submenus to get to the name of the function needed; in this case, Users.
Security Menu - System Administrator GUI
User Menu - System Administrator GUI Users

4. Navigate to the Application:Function form and query for the function in the User Function Name field of the Description zone.

5. Create a new Function using this function name, and enter QUERY_ONLY=YES in the parameters field.

6. Create or modify the menu and menu structure that needs to be attached to this function.

7. Create or modify the responsibility to associate with the above menu/menu structure.

8. Test the new menu and function. Exit and restart the Oracle Applications session for any changes to responsibilities to take effect.