Oracle Conversion
Conversion is to convert something for some destined requirement.
Exactly this is what literally means in Oracle too.
This is converting the business data into specified oracle formats which you need to create in oracle system irrespective of the current source of the data, which can be held by the oracle base tables properly.
For example, you were using a legacy system suppose a plain MS Excel or MS Access or any other system where you maintain your business data. But now you are planning to implement oracle EBS for your business and processes and you need your existing data (legacy data) to be moved into oracle. In this scenario, you cannot simply copy and paste your existing data into oracle system table (base tables). So for this you need to convert the data into oracle format and insert/create those data in oracle system through different process which oracle allows.
Pl/Sql interview questions and answers
- Difference b/w procedure and function? A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block. Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.
- Difference b/w ROWID and ROWNUM? ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored. ROWNUM: It gives a sequence number in which rows are retrieved from the database.
1.
|
What is the Diff between APPS Schema and other Schemas?
Oracle apps Technical Interview Questions and Answers Apps schema contains only Synonyms we can't create tables in apps schema,where as other schemas contains tables, & all the objects. Here only we will create the tables and giving grants on created tables. Almost all every time we will conenct to apps schema only. |
2.
|
What is meant by Custom Top and what is the Purpose? Custom Top is nothing but Customer Top, which is created for customer only. we can have multiple custom tops based on client requirement. It is used to store developed & customized components. whenever oracle corp appling patches it will over ride on all the modules except custom top. that's why we will use custom top. |
SQL PLSQL Interview Questions and Answers
What is PL/SQL?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
What are the components of a PL/SQL Block?
Declarative part
Executable part
Exception part
What are the datatypes a available in PL/SQL?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.
What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?
% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are:
I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
Oracle Functional GL Interview Questions and Answers
1. HOW MANY KEY FLEXFIELDS ARE THERE IN ORACLE FINANCIALS?
· General Ledger
o Accounting Flexfield
· Assets
o Asset Key Flexfield
o Location Flexfield
o Category Flexfield
· Service
o Service Item Flexfield
· Receivables
o Territory Flexfield
o Sales Tax Location Flexfield
· Inventory
o Item Categories
o System Items
o Sales Orders
o Item Catalogs
2. WHAT ARE THE BENEFITS OF FLEXFIELDS?
- Configure applications to support your own accounting, product and other codes.
- Enable the construction of intelligent keys.
- Configure application to capture additional data.
- Use the application to validate values and value combinations entered by the user.
- Support multiple flexfield structures depending on data context.
3. WHAT ARE THE TYPES OF FLEXFIELDS?
- Key flexfield
- Descriptive flexfield
If data entered into Oracle is wrong then the processes which use this data will also give incorrect output or will end with an error. I have had problem with users who have been asked not to enter semi colons or spaces in the email address field while sending the PO for approval. As per the process if semicolons or spaces are entered the custom PO workflow will not raise an error but the email notifications will not reach the intended recipients.
I needed to prevent the users from entering semi colons or spaces by mistake. So I created a form personalization so that Oracle displays a popup error message and stops further processing until the user corrects his entry.
Form personalization does not have WHEN-VALIDATE-ITEM as it is available for a form. If I have to use this option then I have to resort toWHEN-VALIDATE-RECORD trigger. This trigger will fire when the user has entered the values within a form block and clicks outside the block. Oracle will validate the data as per the personalization and raise the conditions if those are met. We have used a message type Error, which will not allow the user from going ahead as it always raises an error condition on the form.
Personalization step
Open PO form. Click on Help > Diagnostics > Custom Code > Personalize
Create a new personalization
Seq:
Description: PO EMail address check
Level: Function
Condition tab
Trigger Event: WHEN-VALIDATE-RECORD
Trigger Object: PO_APPROVE
Condition: INSTR(${item.po_approve.email_address.value}, ‘;’) > 0 OR INSTR(${item.po_approve.email_address.value}, ‘ ‘) > 0
Actions tab
Automating Database Startup and Shutdown 11g R2 on Linux
The automatic startup and shutdown of the Oracle database can be achieved with the files dbstart and dbshut both provided by Oracle. These files rely on the existence of the file /etc/oratab to work.
The format of the /etc/oratab file is as follows:
SID:ORACLE_HOME:AUTO
or in my example:
ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:Y
To start and stop the database when the machine comes up and goes down by modifying the startup routines for the Linux machine. This is quite easy, although I should point out here that this may change depending on which flavor of Linux (slackware, debian, redhat, etc). I will show examples which work for Oracle Linux 5.x. To modify these for your own flavor of Linux, please see your Linux documentation sets.
The automatic startup and shutdown of the Oracle database can be achieved with the files dbstart and dbshut both provided by Oracle. These files rely on the existence of the file /etc/oratab to work.
The format of the /etc/oratab file is as follows:
SID:ORACLE_HOME:AUTO
or in my example:
ORCL:/u01/app/oracle/product/11.2.0/dbhome_1:Y
To start and stop the database when the machine comes up and goes down by modifying the startup routines for the Linux machine. This is quite easy, although I should point out here that this may change depending on which flavor of Linux (slackware, debian, redhat, etc). I will show examples which work for Oracle Linux 5.x. To modify these for your own flavor of Linux, please see your Linux documentation sets.
It has taken me a good deal longer that I would have expected to change the hostname on some cloned VMs that are home to Oracle 11.2 databases running on ASM with Oracle Restart.
I was clearly not alone in thinking that it would just be a case of running localconfig delete/localconfig add, as I have done in the past for hosts running Oracle database 10.2 with ASM for storage. However, things have changed and having done a fair amount of searching using both MetaLink (My Oracle Support) and Google it appears that there isn’t much out there yet in the form of clear instructions. The best I could find was My Oracle Support article ID 887658.1, which got me moving in the right direction.
So, in the hope of saving others the time I have lost working this out, here are the steps I used to rename a Linux host running Oracle Database 11.2 with ASM in an Oracle Restart configuration. The steps have been written for an installation that splits the ownership of the “Grid Infrastructure” and the database between a user named grid and a user named oracle respectively. It is important who you run at least some of these commands as, so please check which user you are before each command. Or more likely, if things don’t look to be working then check which user you ran the command as before going further. It is easy to backtrack if you do get it wrong.
I was clearly not alone in thinking that it would just be a case of running localconfig delete/localconfig add, as I have done in the past for hosts running Oracle database 10.2 with ASM for storage. However, things have changed and having done a fair amount of searching using both MetaLink (My Oracle Support) and Google it appears that there isn’t much out there yet in the form of clear instructions. The best I could find was My Oracle Support article ID 887658.1, which got me moving in the right direction.
So, in the hope of saving others the time I have lost working this out, here are the steps I used to rename a Linux host running Oracle Database 11.2 with ASM in an Oracle Restart configuration. The steps have been written for an installation that splits the ownership of the “Grid Infrastructure” and the database between a user named grid and a user named oracle respectively. It is important who you run at least some of these commands as, so please check which user you are before each command. Or more likely, if things don’t look to be working then check which user you ran the command as before going further. It is easy to backtrack if you do get it wrong.
EBS AP Invoicing, AP Supervisor
N > Invoices > Entry > Invoices
Invoice Workbench
1. Navigate to the Invoice Workbench form.
Note: The
Invoice Workbench displays
2. Search for Invoice
Click
(I) Flashlight.
Note:
The Find Invoices screen displays Search can be done by
supplier, PO Number, Invoice Number, etc
Value Sets
Value Set is a collection of values. It validates the values entered by the user for a flex-field, report parameters or a concurrent.The navigation path: Application :- Validation :- Sets
Value Set is associated with the following validations which holds the key to the value sets.
Types of Validations:
- None :- Validation is minimal.
The Oracle General Ledger module within Oracle Financials is an integrated part of the ERP package of Oracle applications. It is a financial management solution within E-business suite for entering and reporting on the financial data of large companies. Oracle claims its GL module can import and post 42 million journal lines per hour, which would make it a suitable GL application for very large enterprises with high GL transaction volume
The screenshot below [2] from Oracle demonstrates the accounting cycle of Oracle General Ledger.
Before setting up oracle GL, The following need to be set up:
(M) = Mandatory step, others are optional
The screenshot below [2] from Oracle demonstrates the accounting cycle of Oracle General Ledger.
Before setting up oracle GL, The following need to be set up:
(M) = Mandatory step, others are optional
- Chart of Accounts (M)
- Account Combinations
- Period Types (M)
- Calendar (M)
- Transaction Calendar
- Currencies (M)
- Set of Books or SOB (M)
- Assign SOB to a responsibility (M)
- Daily conversion Rate types (M)
- Rates
Introduction to Cycle count
Cycle Counting is:
- Performed periodically to ensure accuracy of inventory quantities & values.
- Frequency of counting item may be based on the classification of items.
- Example:
- Class A items shall be counted 4 times a year
- Class B items shall be counted 2 times a year
- Class C items shall be counted once in a year
- A method of taking inventory that does not require the inventory organization to shut down in order to count actual inventory items.
- Oracle Inventory: ABC Codes
- ABC Compiles.
- ABC Classes.
- ABC Assignment Groups.
- Inventory, Vision Operations (USA): Counting > Cycle Counting > Cycle Counts
- Setting up the cycle count parameters and scheduling.
- Printing cycle count requests.
- Performing the item counting.
- Entering actual item counts.
- Resolving inventory discrepancies in the database with Finance department.
- Item classification shall be done (by finance and material dept)
- Frequency of counting shall be decides (by Material dept)
- System setup shall be done (by Material / IT)
- Creating a ABC compile.
- Generating ABC descending value report.
- Defining ABC Classes.
- Defining ABC Assignment Group.
- Associating classes and assignment group.
- Assigning items to classes.
- Updating item assignment.
- Generating the ABC assignment report.
- Login to Oracle.
- Select “Inventory, Vision Operations (USA)”
- Select “ABC COMPILE”.
System Reference
User Responsibilities
The store’s in-charge is responsible for:
The store’s person is responsible for:
Ownership
The Head of Material’s Department is responsible for ensuring that this document is necessary, reflects actual practices and supports company policy.
Prerequisites
Steps for ABC Analysis
1. Create a ABC compile
The ABC compile ranks all the items in your inventory based upon compile criteria. This ranking is used to assign classes to your items.
Compile Criterion:
ABC analysis is done based on the value of consumptions. But initially till next 6 months from the date of Oracle implementation it will be based on Stock-in-hand value. Moving ahead it will be shifted to Consumption base.
Current on-hand quantity |
Use the current on-hand quantity of inventory. Assign the sequence number by descending quantity.
|
Current on-hand value | Use the current on-hand quantity of inventory times the cost for the cost type. Assign the sequence number by descending value. |
Historical usage value | Use the historical usage value (transaction history). This is the sum of the transaction quantities times the unit cost of the transactions for the time period you specify. Assign the sequence number by descending value. |
Historical usage quantity | Use the historical usage quantity (transaction history) for the time period you specify. Assign the sequence number by descending quantity. |
Historical number of transactions | Use the historical number of transactions (transaction history) for the time period you specify. A ssign the sequence number by descending number of transactions. |
Forecasted usage value | Use the forecasted usage value based on the forecast quantity calculated and the cost type you specify. Assign the sequence number by descending value. |
Forecasted usage quantity | Use the forecasted usage quantity. Assign the sequence number by descending quantity. |
Previous cycle count adjustment quantity | Use the previous cycle count adjustment quantity. Oracle Inventory sums the value of all cycle count adjustments since the last ABC compile date. Assign the sequence number by descending quantity. |
Previous cycle count adjustment value | Use the previous cycle count adjustment transaction value. Oracle Inventory sums the value of all cycle count adjustments since the last ABC compile date. Assign the sequence number by descending value. |
MRP demand usage quantity | Use the MRP demand usage quantity. Oracle Inventory sums the MRP gross requirements for the MRP plan you specify. Assign the sequence number by descending quantity. |
MRP demand usage value |
Use the MRP demand usage value. Oracle Inventory sums the MRP gross requirements for the MRP plan you specify. The value is derived from the item cost type you choose in the ABC compile form. Assign the sequence number by descending value.
|
How to design An Accounting Flexfield Parameter for your Report?
By Jag - August 06, 2014
If you want to design similar functionality as below in your report parameter, do the below steps.
1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.
Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD
2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:
Edit:
FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”
NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”
VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”
NAVIGATE=”!DIR” VDATE=””
Use Global Variables in Form Personalization:
Here let’s say the requirement is to run a concurrent program through a custom menu from an oracle form and in that concurrent program parameters, we need to fetch some values from the oracle form itself. In that case we need to first assign the form values to some Global Variables and then use them in the concurrent program parameters. Here are the Steps:
1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)
2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.
3] On the triggering event: SPECIAL15, do the following actions:
- Select Type as ‘Property’ and select Object Type as ‘Global Variable’.
- Give a name to the global variable in the ‘Target Object’ tab. (Ex. G_ITEM_NAME, G_ORG_CODE)
- Select the Property Name as ‘VALUE’.
- In the value tab- Give ‘=:Block_name.Field_name’ (Use: Help > Diagnostics > Examine). This is the value which you want to put in the global variable.
SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.
SRW.MESSAGE:
It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.
SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);
Example:
Here is a brief description of the key tables in Oracle Inventory.
Table | Description |
MTL_PARAMETERS | It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here. |
MTL_SYSTEM_ITEMS_B | This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages. |
VARIOUS QUERIES ON CUSTOMER DATA (HZ TABLES)
Product: TCA / Oracle Receivables
Overview
There have been many instances where I have been asked by the Business Users to provide Customer Listing reports based on different parameters. The different requests typically are as follows
1. Customer listing with all Sites for a specific Org
2. A listing of only those Customer with whom we have had transactions in the last ‘x’ years
3. A listing of all Customer Sites that do not have any Business purpose associated with it
4. Customer Listing By Collector
5. Customer Listing along with Profile Class names and Collector names
6. And so on …
We have the Standard Customer Listing Reports in the application. However these reports are not org stripped because the HZ_CUST_ACCOUNTS data is not org stripped plus the standard reports could not be used for all different flavors of the Business requests. In these cases, I used the following queries
Queries
Supplier API in R12
The Following API’s are used in oracle apps R12 to upload supplier data :
API Header : POS_VENDOR_PUB_PKG.CREATE_VENDOR ( parameters)
PROCEDURE Create_Vendor(
p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER
);
p_vendor_rec IN AP_VENDOR_PUB_PKG.r_vendor_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_vendor_id OUT NOCOPY NUMBER,
x_party_id OUT NOCOPY NUMBER
);
SCRIPT:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
DECLARE
l_vendor_rec ap_vendor_pub_pkg.r_vendor_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_id NUMBER;
l_party_id NUMBER;
BEGIN
--Required
l_vendor_rec.segment1 := '00002359'; --ID
l_vendor_rec.vendor_name := 'ABC'; --Supplier Name
--Optional
l_vendor_rec.match_option:='R' ;
pos_vendor_pub_pkg.create_vendor
(
p_vendor_rec =>l_vendor_rec,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
x_vendor_id =>l_vendor_id,
x_party_id =>l_party_id
);
COMMIT;
dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_id: '||l_vendor_id);
dbms_output.put_line('party_id: '||l_party_id);
END;
|
API Sites: POS_VENDOR_PUB_PKG.CREATE_VENDOR_SITE( parameters)