Interview FAQs.

Bulk Collect:

Bulk Processing in PL/SQL

The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.

Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.

Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values. The UPDATE statement in the increase_salary procedure fits this scenario; the only thing that changes with each new execution of the statement is the employee ID.

In this article, I will cover the two most important of these features: BULK COLLECT and FORALL.

• BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval
• FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly.

PROCEDURE process_all_rows

IS[-

   TYPE employees_aat 

   IS TABLE OF employees%ROWTYPE

      INDEX BY PLS_INTEGER;

   l_employees employees_aat;

BEGIN

   SELECT *

   BULK COLLECT INTO l_employees

      FROM employees;

     

   FOR indx IN 1 .. l_employees.COUNT 

   LOOP

       analyze_compensation 

      (l_employees(indx));

   END LOOP;

END process_all_rows;

Few Interfaces Explained here.

Order Import Interface.
Item import 
Item import
Inventory On-hand quantity Interface
Auto Invoice Interface.
AR Receipts
AP Invoices

Vendor
Purchase Orders
Requisition
Receiving
Journal import
Budget import
Daily Conversion Rates

XMLP Reports Developed

1. Invoicing
2. Check Printing
3. Forms and labels
4. Contracts etc

XML Publisher FAQs.

1.How does the concurrent request relate both data template and layout template it should be using for generating the output?

Ans: The concurrent program ‘short name’ will be mapped to the ‘code’ of the Data template. Layout template is attached to the data template, this forms the mapping between all the three.

2.What is Data Template and Layout Template?

Data Template:
Datatemplate is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated, this generated xml output is then applied on to the layout template for the final output

Layout Template:

Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. The data output in xml format (from Data template) will be loaded in layout template at run time and the required final output file is generated.

3.How to get a output of a XMLP report in different formats like PDF, DOC, XLS, TXT ?

Ans:While submitting the concurrent program you select the output format in options form of “Up on Completions” selection.



4.Can you have multiple layout templates for a singe data template?

Ans: Yes! Multiple layouts can be attached, so that user will have a choice here to use one among them at the time of concurrent program submission.

5.Do we need to create multiple layout templates for printing report in multiple languages?

Ans:We can achieve multi language report by two ways.

       1.Different layout template for different languages This approach can be chosen if the layout is also different from language to language.

       2. Generate XLIFF template for each language
           XLIFF (XML Localization Interchange File Format) : format for exchanging localization data. XML based format that enables translators to concentrate on the text to be translated. We use this option when we want to use the same layout and apply specific translation.

6.How do you migrate layout or data template across instances?
Ans:We can use XDOLoader utility to migrate both data template and layout. Below is the 

About CUSTOM.pll.

Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a newbusiness rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.
CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are
1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime.
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens.
6. Enabling Special Menu

Primarily there are two methods of extending Oracle Forms, and these are 
    CUSTOM.pll
    FORMS Personalizations
In this article we will cover the basics of using CUSTOM.pll for extending Oracle Forms

How and why does CUSTOM.pll work?
Every form in Oracle Apps is created using something called as TEMPLATE.fmb. But some of the modules like HRMS have their own HR Specific Templates[HRTEMPLT.fmb]. These template files have form level triggers that make call to CUSTOM.pll. The triggers that can be trapped using CUSTOM.pll in HRMS screen can be different than those which can be trapped for other modules.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this?
Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?
 Once you make changes you need to compile the pll. Use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.

For following Events call will go to CUSTOM Library:
 
WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)
Custom Library contains Custom Package which is having two Functions and one procedure.
1] ZOOM_AVAILABLE:
This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.

Sample code1:

function zoom_available return Boolean is
    form_name  varchar2(30) := name_in('system.current_form');
    block_name varchar2(30) := name_in('system.cursor_block');
begin
    if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then
      return TRUE;
    else
      return FALSE;
    end if;
end zoom_available;

How to make the changes get affected?

Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.
Forms Personalization: an alternative of custom.pll 
In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.
Advantages of Forms Personalization over Custom.PLL:

• Forms personalization can be used by an user with limited PL/SQL knowledge. 
• Changes take place immediately on reopening the form.
• Anything which can be done using Custom.PLL can be done using Forms Personalization also.
• Personalizations are stored in base tables related to Form Personalization.
• CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
• Easy to disable/enable with click of a button.
• Can be moved easily through FNDLOAD from one instance to other.
• Can be restricted at site/responsibility/user level.
• Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

Briefly about Accounts Payables and FAQs

Invoices:Invoices is a formal document to pay to vendor. Upon shipment of goods invoices are normally sent by suppliers and same is entered in payable's module. You can manipulate(create,edit,adjust) group of invoices or invoices batches in invoices workbench.
Invoices can be manually entered or automatically imported from purchasing,EDI or Interface.

Types of Invoices:

Standard
Credit Memo
Debit Memo
Expense Report
Prepayments
Retainage Release
Transportation Invoice
Withhold TaxMixed.

Credit Memo and Debit Memo: Credit Memo is a negative amount invoice given by supplier to give a credit. Debit Memo is a negative amount invoice created by you and sent to supplier.

Invoice Holds: Holds are applied to prevent payments to suppliers

Payments: For each eligible Invoice or invoice lines, payments are created.

Prepayments in Oracle Payables:

Normal Payable cycle is that you first create invoice when you get one from you supplier for the goods that you have bought and then make a payment of the amount in the invoice to the supplier.
But there are scenarios where the supplier requests for an advance when you order the goods. Supplier wants you to pay a certain amount at the time of order and then pay the remaining upon delivery of the goods.

You can enter two types of prepayments: Temporary and Permanent.

Temporary prepayments can be applied to invoices or expense reports you receive. For example, you use a Temporary prepayment to pay a hotel a catering deposit. When the hotel’s invoice arrives, apply the prepayment to the invoice to reduce the invoice amount you pay.

Permanent prepayments cannot be applied to invoices. For example, you use a Permanent prepayment to pay a lease deposit for which you do not expect to be invoiced.

Withholding Tax Invoices in Oracle AP:

Withholding tax (WHT) is the amount withheld while making a payment to a supplier/contractor/any payee. Instead it is paid to the tax authorities.

You may be required to withhold taxes from your employee expense reports and supplier invoices. Payables lets you to automatically withhold tax based on the certain conditions you specify in Withholding Tax region of the Payables Options window.

Retainage Release Invoices in Oracle AP:

Retainage means retaining certain amount of money with us without paying the supplier in full. The retained amount is released or paid to the supplier when all the terms of the agreement have been met or all the work is completed.

AP: Payment Manager Process in R12.

Payment Batch lets you create payments for multiple invoices that meet the criteria you specify in a single shot. In R12, it lets you select invoices with different Operating units, Currencies in a single batch.

Note: The process of creating payment batches and processing payments in R12 has changed completely when compared to 11i.

Navigation: Payables responsibility > Payments > Entry > Payment Manager.

SQL Loader - Introduction,Structure of the data file,Structure of a Controlfile,Datafile as External and/or within controlfile,Run SQL Loader from Unix and Register as Conc Program.

#Introduction:
SQL LOADER is an Oracle utility used to load data into table given a datafile which has the records that need to be loaded. SQL*Loader takes data file, as well as a control file, to insert data into the table. When a Control file is executed, it can create Three (3) files called log file, bad file or reject file, discard file.

• Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
• Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle errors.
• Discard file specifies the records that do not meet any of the loading criteria like when any of the WHEN clauses specified in the control file. These records differ from rejected records.

#Structure of the data file:

The data file can be in fixed record format or variable record format.

Fixed Record Format would look like the below. In this case you give a specific position where the Control file can expect a data field:

7369 SMITH      CLERK        7902  12/17/1980         800

7499 ALLEN      SALESMAN  7698  2/20/1981           1600

7521 WARD      SALESMAN  7698  2/22/1981           1250

Variable Record Format would like below where the data fields are separated by a delimiter.

Note: The Delimiter can be anything you like. In this case it is “|”

1196700|9|0|692.64

1378901|2|3900|488.62

1418700|2|2320|467.92

#Structure of a Control file:

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n — Number of logical records to skip (Default 0)

LOAD = n — Number of logical records to load (Default all)

ERRORS = n — Number of errors to allow (Default 50)

ROWS = n   — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n — Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run

(header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE)

LOADDATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE* specifies that the data is found in the control file and not in an external file. INFILE ‘$FILE’, can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   ‘/home/vision/kap/import2.csv’ specifies the filepath and the filename.

#Ex:Datafile as External:

LOAD DATA

INFILE   ‘/home/vision/kap/import2.csv’

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

#Example where datafile is in the Control file:

LOAD DATA

INFILE *

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

BEGINDATA

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

#Example where file name and path is sent as a parameter when registered as a concurrent program

LOAD DATA

INFILE ‘$FILE’

INTO TABLE kap_emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

TYPE OF LOADING:

INSERT   — If the table you are loading is empty, INSERT can be used.

APPEND  — If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn’t already exist, the new rows are simply loaded.

REPLACE — All rows in the table are deleted and the new data is loaded

TRUNCATE — SQL*Loader uses the SQL TRUNCATE command.

INTOTABLEis required to identify the table to be loaded into. In the above example INTO TABLE “APPS”.”BUDGET”, APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY ‘”‘ specifies that data fields may also be enclosed by quotation marks.

TRAILINGNULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

#Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

sqlldr USERID=scott/tiger CONTROL= LOG=

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

#Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

Skip columns:

You can skip columns using the ‘FILLER’ option.

Load Data
—
—
—
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)