, , ,

Script To find Oracle API’s for any module

Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules

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 ‘PA_%API%’
order by
a.owner, a.name;

/by
, , , ,

Oracle Applications 11i Technical FAQ’s

  1. What is Concurrent Programming?
Concurrent Processing in Oracle Apps simultaneously executes programs running in the Background with online operations to fully utilize your hardware capacity.
Use Concurrent Programming for
Long Running – Data intensive tasks such as Posting a Journal or generating a report.
  1. What is the Role of Concurrent Managers?
A Concurrent Manager is a component of Concurrent processing that monitors and runs tasks without tying up your computer.
  1. What is AOL?
Oracle Applications are constructed and maintained using the Application Object Library (AOL).
The Three main areas of AOL are
    • Applications Security
    • Operating Profile
    • Concurrent Processing
    •  
  1. Define Interfaces. What are the different types of Interfaces?
Interfaces are used to integrate external systems and data conversion in Oracle Applications.
These can be used to either transfer data from Oracle Applications to a Flat File or Data from Legacy System to Oracle Applications.
There are two types of Interfaces. Inbound and Outbound Interfaces.
Inbound Interfaces are used to transfer data from external system to Oracle Applications.
Outbound Interfaces are used to transfer data from Oracle Applications to External System.
Open Interface is the interface whose interface logic is provided by Oracle.
Custom Interface is an Interface whose logic is developed by implementation team.
  1. What are alerts? What are the different types of alerts? Explain.
Alerts
    • Immediately inform you of the database activity as it happens.
    • Can Periodically trigger off events as and when required
    • Can take predefined actions
    • Allow you to define distribution list
    • Can keep history of the exceptions and actions taken against them.
There are two types of alerts.
    • Event Alerts
    • Periodic Alerts
    •  
  1.  What is the Flex field? What are the types of Flex field?
    • Flex Field is “Flexible Field”
    • A Flexfield is made up of Segments.
    • Each segment has a name that can be assigned and has set of valid values.
    • There are two types of Flex field Key Flex Field and Descriptive Flex Fields
    •  
  2. What are the tables related to flex field?
    • FND_FLEX_VALUES
    • FND_FLEX_VALUE_SETS
    • FND_FLEX_VALUES_TL     
  3. What is AD_DD package?
AD_DD Package is used to register the Table, Columns, and Primary Key in Oracle Applications.
            PROCEDURE REGISTER_TABLE
          Arguments: 
    • P_APPL_SHORT_NAME     
    • P_TAB_NAME            
    • P_TAB_TYPE            
    • P_NEXT_EXTENT         
    • P_PCT_FREE            
    • P_PCT_USED            
         PROCEDURE REGISTER_COLUMN
   Arguments
·         P_APPL_SHORT_NAME     
·         P_TAB_NAME            
·         P_COL_NAME            
·         P_COL_SEQ             
·         P_COL_TYPE            
·         P_COL_WIDTH           
·         P_NULLABLE            
·         P_TRANSLATE           
·         P_PRECISION           
·         P_SCALE                
  1. What are the types of Value sets?
·         None
·         Table
·         Special
·         Pair
·         Dependent
·         Independent
·         Translatable Dependent
·         Translatable Independent
  1. What are the Special and Pair Flex Field?
Special – Value Sets uses FlexField itself
Pair – Two Flex Fields together specifies a range of valid values.
  1. What are the Translatable Dependent and Independent Flex Fields?
   Translatable Independent – Input must exist on previously defined set List of        
   Values. Translated value can be used.
   Translatable Dependent means Input is checked against a subset of values  
   Based on a prior value. Translated value can be used.
  1. What is FND_REQUEST.SUBMIT_REQUEST?
Submits a Concurrent Request for Processing by a Concurrent Manager.
Arguments – Application,program,description,start_time,sub_request,arg1..
  1. What is Client Info?
By calling this Program in SQL*PLUS or reports with correct parameters user can achieve concurrent program environment for testing.
FND_CLIENT_INFO.setup_client_info(application_id Number,
                                                                     Responsibility_id Number,
                                                                    User_id  Number,
                                                                     Security_Group_id Number);

  1. Give the Directory structure in apps?
$APPL_TOP  – Product Directory- Version-

  1. What are the steps in Registering Concurrent Program?
    • Go to Programs and Define Executables.
    • Go to Programs and Define Concurrent Program
    • Go to Responsibility and attach the Request group you want.
  2. What are the different types of executable available in Concurrent Programming?
    • Host
    • Oracle Reports
    • PL/SQL Stored Procedures
    • SQL*LOADER
    • SQL*PLUS
    • Spawned
    • JSP
  3. What are Request Sets?
Request set is a collection of Reports/Programs that you group together and can be submitted to run is a single interaction.

  1. What is Standard Request Submission (SRS Feature)?
SRS provides you with a set of windows for running reports and Programs and a set of windows for creating groups of reports and programs to run together.
Features
    • Specify whether reports or programs in a request set run sequentially or simultaneously
    • Specify whether to continue with a request set if a report or program in a sequential set fails
    • View a log file
    • Specify alternative requests based on completion status of previously run requests in a request set.
  1. What are the different API’s for Concurrent Programming?
    • FND_CONCURRENT
    • FND_FILE
    • FND_PROGRAM
    • FND_SET
    • FND_REQUEST
    • FND_REQUEST_INFO
    • FND_SUBMIT
  2. What are the Different PLL’s Used in Forms?
    • CUSTOM.pll
    • FNDSQF.pll
    • APPCORE.pll
    • APPCORE2.pll
    • appdaypk.pll
    • APPSTAND.pll
  3. What are the Steps in Forms Customization?
    • Define the Form Name in FORM Screen
    • Define Form Functions
    • Attach to Menu/Attach to Request group
  4. What are the triggers that can be modified during Forms Customization?
    • Pre-Forms
    • When-New-Form-Instance
    • Query_Find
    • Post-Form
    • Key-Clrfrm
    • Accept
  5. What are the triggers that cannot be modified during Forms Customization?
    • STANDARD_ATTACHMENT
    • ZOOM
    • FOLDER_ACTION
    • KEY-HELP
    • KEY-EXIT
    • KEY-COMMIT
    • WHEN-WINDOW_CLOSED
    • CLOSE_WINDOW
  6. What are the FlexField Qualifiers?
A Flex field qualifier identifies a particular segment of a key flex field.
  1. What are the Segment Qualifiers?
A Segment Qualifier identifies a particular type of value in a single segment of a key flex field.
  1. What is a Dynamic Insertion?
Dynamic Insertion is the insertion of new valid combination into a Key Flexfields Combinations Table from a form other than the combinations form.
All Validation rules still will apply during insertion.
  1. What are the different Level of Profiles?
User Profiles are used
    • To set options that affect your applications behavior o your preference.
    • A Collection of changeable options that affect the way your applications run
    • Modify Product Specific variables
    • Gives Control over certain Oracle Applications features.
            Profile Levels
·         Site Level
·         Application Level
·         Responsibility Level
·         User Level
Site Level is the lowest level.
  1. Explain Multi-Organization Structure?

Set of book

A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calendar.
Business Group
This is highest level in the Organization Structure. The Business group secures HR Information. Multiple set of books can share same business group.
Legal Entity
A legal company for which you prepare fiscal or tax reports.
Balancing Entity
Represents an accounting entity for which you prepare financial statements.
This is the segment in Accounting Flexfield.
Operating Unit
An Organization that Uses Oracle Cash Management, Order Management and shipping Execution, Oracle Payables, Oracle Purchasing, Oracle receivables.
It may be a Sales office division or a department. An Operating Unit is associated with legal entity.
Inventory Organization
An Organization for which you track Inventory transactions and balances and/or an Organization that manufactures or distributes products.
HR Organization
HR Organization represents the basic work structure of any enterprise.            They usually represent Functional Management or reporting groups that exists within a business group.
  1. How can u see Multi-Organization is enabled or not from SQL Prompt?
SELECT MULTI_ORG_FLAG FROM fnd_product_groups;
  1. What are the two mandatory parameters required for PL/SQL stored Procedure Concurrent Program?
Errbuf and retcode two OUT Parameters are required while defining PL/SQL stored Procedure Concurrent Program.
Errbuf Returns any error messageand retcode returns completion status.
Retcode returns 0 for success, 1 for warnings and 2 for error.
Oracle Alert FAQ’s
  1. What are the event alerts?
Triggered off when a particular event occurs in the Database.
Event can be Insert or Update.
      What are Alert Action Level?
There are two levels of actions that can be performed
            Detail Action and Summary Action.
  1. What is a Summary Threshold?
Oracle Alerts automatically determine whether to perform a detail action or Summary action based on the number of exceptions in the database.
  1. What are the different actions that can be performed in Alerts?
    • Message Actions
    • Concurrent Program Actions
    • Operating Script Actions
    • SQL Statement Script Actions
  2. What are the steps involved in Creating Periodic alerts?
·         Write a select statement
·         Verify and Run the SQL
·         Specify Alert Inputs
·         Define Alert Outputs
·         Create Periodic alert actions
·         Define Periodic alert message actions
·         Create a periodic alert action set
·         Check your periodic alert.
  1. What is the Distribution List in Alerts?
A Pre-defined set of electronic mail ids and printer Instructions that you can use on message actions instead of re-entering all the recipient names each time.
  1. What are the Advance features available in alerts?
·         Distribution List
·         Periodic sets
·         Summary Thresholds
·         Action Set Checks
  1. What are the four implicit Inputs in Alerts?
Mailid, rowid, org_id, date_last_checked
/by
, , ,

How to Develop XML Publisher Report (XMLP) or BI Publisher Reports?

Training  of XML Publisher Report (XMLP) or BI Publisher Reports:

How to Develop XML Publisher Report (XMLP) or BI Publisher Reports?

hey Guys Now adays BI Publisher is in Boom technology formally it know as XMLP means XML Publisher.
We can develop this by using following steps:

1)Develop the Report(.rdf) Only DataModel

2)Save the report and Compoile and Generate the data in .xml
           Format File=>Generate to File => XML

3)Open the Ms-Word document Design the Layout include the follwing Tool Bar options called Form and Templete builder

4)Define the Table and Specify the Titles and Labels whatever we would like to print

5)Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax : Here G_USER_ID is Group name from .rdf Data Model Note: It means Indirectley Repeating Frame starting

6)Define the Fields which we would like to display Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax ?
Note: Define all the fields like this

7)We have to close the Repeating Frame (For each Loop) Include ‘Text Form Field’ Double CLick => Add Help Text button enter the following syntax

8)Save the document in .rtf(Rich Text File format)

9)Load the .xml file data and generate the Output in pdf Format Data=>Load XMl Data=>Load the .xml File=>Once Data is Loaded succesfully

10)Generate the Preview as per this Preview=>PDF

Registering in Apps

1)Develop the .rdf file and .rtf file

2)Register both .rdf and .rtf file

.rdf File Registration

1)Move the .rdf file into CUS_TOP11.5.0ReportsUS

2)Create Execuiteable

Concurrent Program Output = XML

Request Group

Responsibility

User

SRS

3)Copy the Concurrent Program Short name .rtf File

RTF Registration:

1)Goto the XML Publisher Administrator Responsibility

2) Creation of Data Defination
Select Data Definations form Enter Data Defination

Name = Enter Any Name

Code = Concurrent Program Short Name

Application = Any application

startdate = Current Date

3)Copy the DataDefination name we have to add this to the Templete

4)Creation of Template form

Open the Templete Form

Enter Templete Name : Any Name

DataDefination : enter data defination whatever we have created

Code : Concurrent Program Short Name

Type : rtf

application : any Application

File : Upload the .rtf file

Laguage : English

Territory : United States

5)Select Apply button

After register the .rtf file submit concurrent Program from SRS window system will automatically pick the .rtf file and generates the Output.

If we want to customze the .rtf file we have to download by using templete , Customize thenupload the .rtf templeteme, a.sal, b.sal, b.empno, b.ename from emp a, emp b where a.mgr=b.empno and a.sal>b.sal

/by
, , , , ,

Payables Open Interface’s Control File

load data infile ‘AP_INV.TXT’
append into table    AP_INVOICES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (INVOICE_NUM              CHAR “RTRIM(:INVOICE_NUM)”,
     INVOICE_TYPE_LOOKUP_CODE   CHAR “RTRIM(:INVOICE_TYPE_LOOKUP_CODE)”,
     INVOICE_DATE               DATE “DD-MON-RRRR”,
     VENDOR_NUM                 CHAR “RTRIM(:VENDOR_NUM)”,
     VENDOR_SITE_CODE           FILLER,
     INVOICE_AMOUNT             CHAR “RTRIM(:INVOICE_AMOUNT)”,
     TERMS_NAME                 CHAR “RTRIM(:TERMS_NAME)”,
     DESCRIPTION                CHAR “RTRIM(:DESCRIPTION)”,
     DUMMY1 FILLER,
     DUMMY2 FILLER,
     DUMMY3 FILLER,
     DUMMY4 FILLER,
     GL_DATE  DATE “TO_DATE((TO_CHAR(:GL_DATE,’DD-‘)||DECODE(TO_CHAR(:GL_DATE,’MON’),’AUG’,’SEP’,’SEP’,’SEP’,’OCT’,’OCT’,TO_CHAR(:GL_DATE,’MON’))||TO_CHAR(:GL_DATE,’-YYYY’)))”,
     VOUCHER_NUM                CHAR “RTRIM(:VOUCHER_NUM)”,
     INVOICE_RECEIVED_DATE      DATE,
     AMOUNT_APPLICABLE_TO_DISCOUNT,
     TERMS_DATE                 DATE,
     SOURCE                     CONSTANT ‘MAXCIM INVOICE’,
     PAYMENT_CURRENCY_CODE      CONSTANT ‘USD’,
     PAYMENT_METHOD_LOOKUP_CODE CONSTANT ‘CHECK’,
     CALC_TAX_DURING_IMPORT_FLAG CONSTANT ‘N’,
     ORG_ID                     CONSTANT ‘166’,
     SHIP_TO_LOCATION           CONSTANT ‘Materials Warehouse’,
     INVOICE_CURRENCY_CODE      CONSTANT ‘USD’,
     creation_date              SYSDATE,
     last_updated_by            CONSTANT 1093,
     last_update_date           SYSDATE,
     created_by                 CONSTANT 1093,
     INVOICE_ID “AP_INVOICES_INTERFACE_S.NEXTVAL”)

load data infile ‘AP_LINE_TEST.TXT’
append into table    AP_INVOICE_LINES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (REFERENCE_2              CHAR “RTRIM(:REFERENCE_2)”,
     TYPE              FILLER,
     ACCOUNTING_DATE         DATE “DD-MON-RRRR”,
     DESCRIPTION          CHAR “RTRIM(:DESCRIPTION)”,
     DIST_CODE_COMBINATION_ID          CHAR “RTRIM(:DIST_CODE_COMBINATION_ID)”,
     LAST_UPDATED_BY          CHAR “RTRIM(:LAST_UPDATED_BY)”,
     AMOUNT                  CHAR “RTRIM(:AMOUNT)”,
     CREATED_BY          CHAR “RTRIM(:CREATED_BY)”,
     CREATION_DATE          DATE “DD-MON-RRRR”,
     ATTRIBUTE1          CHAR “RTRIM(:ATTRIBUTE1)”,
     ATTRIBUTE2          CHAR “RTRIM(:ATTRIBUTE2)”,
     ATTRIBUTE3          CHAR “RTRIM(:ATTRIBUTE3)”,
     TYPE_1099          CHAR “RTRIM(:TYPE_1099)”,
     UNIT_OF_MEAS_LOOKUP_CODE      CHAR “RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)”,
     TAX_RATE              CHAR “RTRIM(:TAX_RATE)”,
     QUANTITY_INVOICED      CHAR “RTRIM(:QUANTITY_INVOICED)”,
     UNIT_PRICE          CHAR “RTRIM(:UNIT_PRICE)”,
     INVOICE_ID “AP_INVOICE_LINES_INTERFACE_S.NEXTVAL”)

/by
, , , ,

What are Steps for Report Development in Apps?

Steps for Report Development….

1. Develop the report as per client requirement using the Report-6i tool.

2. Move the report (.rdf) file from local machine to respective path in the server. If client have the CUST_TOP then move into Cust_Top else move it to the related Standard Top.Custom Top – CUST_TOP/ 11.5.0/ Report/ US/ .rdf Standard Top – PO_TOP/ 11.5.0/ Report/ US/ .rdf (For PO report)

3. Create “Executable” for that report (After log on to Oracle Applications and Select “System Administrator” responsibility)

4. Create “Concurrent Program” and attach Executable to Conc. Prgm. and define Parameters and Incompatibles if any.Concurrent Program: It is an instance of the executable file along with parameters & incompatible.

5. Create “Request Group” and attach Conc. Prgm. to Request Group.Request Group is nothing but a collection of Conc. Prgms.

6. Create “Responsibility” and attach the Request Group to Responsibility.

7. Create “User” attach Responsibility to User”
             so that the user can run this Conc. Prgm. form the “SRS Window” (Standard Request Submission).

Note: All the Conc. Prgms. should run from the SRS window (Even if we run from Back-End)By default the user has the rights of System Administrator or Application Developer responsibilities

Every form in Oracle Applications contains 3-Types of Fields.
    1. Yellow color – Mandatory2. Green Color – Read-Only3. White Color – Optional

How to Create New User in Oracle Apps?
User Creation: Open IE and type path of Oracle Application in address bar enter User Name and Password

User Name: OPERATIONS  Password: WELCOME

Select System Administrator Responsibility
Select Security / User / Define  Give the required information and Save.
 Switch the user to newly created  User.
 Note: When we create any User, the User stored at FND_USER.

How we can find the Table names of Apps Screen?
We can find through Help => Record-History => FND_USER

How to find Column Name from front end Apps Screen :
To find all column names: Help => Diagnostics => Examine

What is WHO Columns and How many WHO Columns in Oracle Apps?
Ans : 4 types of who columns for each table in Oracle Applications
Created By  Creation Date  Updated By  Updated Date

/by