OracleApps

BI Publisher Integration with Oracle Apps

Before BI Publisher was released as a Product that was integrated with Oracle Applications, Oracle Reports 6i was the most widely used Tool for generating Report outputs and print documents like PO’s, Invoices, statements etc…
Complex Report layouts often took longer to create and test using the Oracle Reports Tool. With the launch of BI Publisher (originally XML Publisher), Oracle has made a breakthrough with the ease and simplicity by which report outputs can be generated.
To be able to use BI Publisher with Oracle Apps, Patches need to be applied which are available for download from metalink.
This article provides an insight into the components involved in creating an end to end BI Publisher solution for Oracle Apps Concurrent Programs.
BI Publisher makes use of XML data files and templates to generate a PDF output.
XML Data file: The XML Data file can be generated in multiple ways from Oracle.
One way would be to set the option on the Concurrent Program Definition to generate an XML file. Another way would be to use plsql code to generate the XML. This XML file then becomes the input for the XML Publisher Concurrent Programs which processes it with the associated template to produce the output.
Pseudo XML data file

10000 ABC.
000 ABC PRODUCT.
1
USD

XML Publisher templates: These templates can be any of MS Word (.rtf )RTF, Adobe Acrobat(.pdf)PDF, XML, eText, MS-Excel

PDF and RTF templates Creation
PDF templates:-
PDF templates require the Adobe Standard Software. An already existing .pdf file can be used to prepare a template for XML Publisher. In most cases there is little or no amount of work involved in designing the layout, as the form fields are placed on a predesigned available blank layout. It is important to note that no matter what the version of the Adobe Acrobat Standard software you use to create the templates, all templates must be converted to Acrobat PDF Version 1.4. As of date only PDF templates Version 1.4 are supported with XML Publisher. This can be achieved by Navigating to AdvancedàPDF Optimizer. Note the current version and use the option Make compatible with: Acrobat 5.0 and later.
No logic of any kind can be used on PDF templates, which make it rigid and pushes back the task of including complex logic in the source generating the XML Data. However it is recommended to use PDF templates in standard Forms (Government or other eg W2), where the template for the form is subject to very little or no change at all.
It is therefore imperative to analyze business requirements thoroughly and decide on the usability of rtf or pdf templates at the outset of the implementation.
Creation of PDF Templates:
To create a pdf template for BI Publisher, form fields need to be added.
These form fields contain XML tags that assign XML data values to the fields at runtime.
To add form fields, open a .pdf file in Acrobat standard.
Navigate ToolsàFormsàText field tool.
Draw the text form field on the pdf where you want the data to appear. Double click on the form field and add the XML tag to the Name field in the Properties box. Note that the Name added should be available in the XML Data tree file.eg: If POH_PO_NUM is an XML tag that holds the Purchase Order Number in XML Data file, you will need to add POH_PO_NUM to the Name field in the Properties for that form field on the PDF template. If you wish to use names that are different from the XML file, a mapping feature is available in the Template Definition to map the PDF template form field names with the XML tag names.
Below is a screenshot of a PDF template. You can set the properties for the data that will be displayed by this form field. Some of the properties you can set are the font type, font size, multi-line…

RTF templates:-
RTF templates are easier to use and support complex logic. These templates can be created with any Version of MS Word. The template file should be of .rtf format.
Designing a complex template structurally can be a little more tedious as compared to PDF templates, as MS Word is primarily not a design tool and most rtf template designs would have to be created from scratch.RTF templates can be used where
–there are many groups and subgroups with data,
–conditional formatting of form fields is required
–conditional display of data is required
–spooling multiple pages with different header/footer.
The above is a list off of the many more uses of the rtf template.
Creation of RTF Templates.
To create a rtf template for XML Publisher, you need to first design the template placing tables, nested tables, assigning the header/footer, setting the type landscape/portrait, setting the margins…Once the layout design is ready form fields need to be added.
In MS Word 2008 version Navigate to Developer, the form fields can be found under the Control option.
These form fields contain XML tags that assign XML data values to the fields at runtime.
Draw the text form field on the rtf template. Double click on the form field to add the XML tag. You can enter in the XML tag name or any name in the Default Text field if you want to mask the actual XML name or if you want to give the xml tag a more meaningful name.
Click on Add help text to enter the XML tag in the format , transaction_date being an xml tag in the input XML data file.
Note that this is also the area for adding any kind of logic to be applied on the data group.
However there is a limit on the number of characters that can be added to this area.
If the logic you intend to add has too many characters, the Help Key can be used.
Below is a screenshot of an RTF template. You can set the properties for the data directly from the MS word toolbar –Home which has Text font, size, colour etc…
Some of the properties you can set are the font type, font size, multi-line…All you need to do is select the form field and set the properties from the toolbar.
Note: As of now MS word does not allow to create form fields in the header or footer section of the .rtf template. The alternative is to write the XML tag the way it is in the header and footer as can be seen with .The grey areas are the form fields on the template.

XML Publisher Concurrent Programs in Oracle APPS (Ebusiness suite):
The following programs are an out of the box solution provided for XML Publisher reports. These programs make a call to java class files to integrate the XML data file and the templates to produce an output. Based on the requirements, you may use either one of the following programs
1) Program: XML Report Publisher
Short Name: XDOREPPB
This program can be used when no delivery channel like email, fax,… is required. Using this program enables the user to view the output at will from the View Output tab of the concurrent Program that runs the Oracle report. The user can also print the output by setting the print options while submitting the Concurrent Program.
2) Program: XML Publisher Report Bursting Program
Short Name: XDOBURSTREP
This program is used when there are multiple delivery channels involved, for eg: the output document needs to be printed emailed as well as faxed to the requestor/vendor…
Or when there is a range of data to be printed. In this case Bursting reuses the same template to generate a new file for each data set in the XML file.
To implement bursting, a control file is created. This control file is an XML file that contains delivery channel information like email server, print server, email address, no of copies, template, printer name….
The values can be hard coded in the file but it is best to assign these values dynamically at run time. The bursting control file reads these values from the XML data files through variables. The bursting program creates the output based on the template and the xml data file and delivers it to the channels specified.
———————————————————————————————————————
XML Publisher Setups
1) Use an existing Concurrent Program like PO Print or Invoice Print or create a new one based on your requirement.
Ensure the following are set on the Concurrent Program Definition
System AdministratoràConcurrentàProgramàDefine
Query your concurrent program. If the executable is an Oracle Report,
In the output section, set the following
Format = XML
Style= PDF Publisher
2) XML Publisher Administrator Responsibility- Create Data and Template Definitions
1. Create a new data definition
XML Publisher AdministratoràData Definition
Name: Enter a name for the data definition.
Code: Enter the short_name of the concurrent program which generates the XML file.
The short_name associates XML Publisher setups with the Oracle Reports concurrent Program. The Code for the Data definition should always be the same as the short_name of the concurrent program for XML Publisher reporting to work.
Application: Enter the same application that the Oracle Report concurrent Program uses.
Click on apply to put this Data Definition into effect.
You can alternatively add the following files to the Data Definition based on your setup.
XML Schema: A .xsd file needs to be uploaded when using PDF templates where the custom form field names need to be mapped with the XML tag names.
Data Template: A sample data xml file can be uploaded to this field.
Preview Data: Adding a sample .xml data file enables to preview the output from the template Definition.
Bursting Control file:The .xml control file has to be uploaded to this field when using bursting of the XML data file for generating outputs. The XDOBURSTREP program uses this file to process the output.
Sample bursting control file (Skip this if not using Bursting)
Note: If using bursting, to use the out of the box XML Publisher Report Bursting Program,
You can use the FND_REQUEST.SUBMIT_REQUEST in the After Report trigger in Oracle Reports or in your plsql code depending on your source file which generates the xml file to automate the complete process.
1. Create a new Template Definition
XML Publisher AdministratoràTemplate Definition
Name: Enter a name for the template definition
Code: Enter a short name for the template definition, preferably but not mandatory, the same used for the Data Definition code.
Application: Enter the Application the same as the Data Definition and Oracle Report Concurrent Program.
Data Definition: Select the Data Definition created in the above step.
Type: Enter the template type, RTF if it is an MS word template, PDF if it is a PDF template.
File: Upload the template file to be used for generating the output.
Language: Enter the language as English
Territory: Enter the territory as United States
Apply the changes to activate the Template definition.
1. Set Administration Properties
XML Publisher AdministratoràAdministration
Under Properties, Expand the General tab, specify a Temporary Directory Path from your file system. If this is not set, the XDOBURSTREP Program will error
3) Printer setup:-If Pasta printing is not enabled in your organization
System administratoràInstallàPrinter-àRegister
Query the printers- check the Type field for the Printers.
If Printer Type= ‘–PASTA Universal Printer Type’, for all your printers then you are set to use Pasta Printing and no further steps would be necessary.
If your printer type is for eg: HPLJ4SI you may proceed to follow the steps below
Add PDF Publisher style with PASTA_PDF as a driver for printer type
———————————————————————————————————————
Once the above setups are done, Navigate to the responsibility.
Submit the request.
When it completes click on View output to see the pdf generated by XML Publisher.
If using Bursting, the output will get emailed/printed/faxed based on the values provided for the bursting control file at run time.

Interfaces and Conversions in Oracle Applications

Overview:
Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications. This article briefs you about some of the major interface programs.
Conversion/Interface Strategy:
1.Data Mapping: During the data mapping process, list of all the data sets and data elements that will need to be moved into the Oracle tables as part of conversion are identified. Data mapping tables are prepared as part of this activity that show what are the data elements that are needed by the target system to meet the business requirements and from where they will be extracted in the old system.

2.Download Programs: After the conversion data mapping is complete, download programs are developed that are used to extract the identified conversion data elements from the current systems in the form of an ASCII flat file. The structure of the flat file must match the structure of the Oracle standard interface tables. These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

3.Upload Program: Once the data has been extracted to a flat file, it is then moved to the target file system and the data from the file is loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities. Then programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables.

4.Interface Program: Once the interface tables are populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. The interface programs validate the data, derive and assign the default values and ultimately populate the production base tables.

Interface/Conversion examples with details:
The below list of interfaces/conversions are covered in this section. Details like pre-requisites required, interface tables, interface program, base tables, validations that need to be performed after inserting the details into the interface tables and required columns that need to be populated in the interface table are discussed for each interface.
1.Order Import Interface (Sales Order Conversion)
2.Item import (Item conversion)
3.Inventory On-hand quantity Interface
4.Customer conversion
5.Auto Invoice Interface
6.AR Receipts
7.Lockbox Interface
8.AP Invoices
9.Vendor
10.Purchase Orders
11.Requisition
12.Receiving
13.Journal import
14.Budget import
15.Daily Conversion Rates

1.Order Import Interface (Sales Order Conversion): Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.

Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity

Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL

Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES

Concurrent Program: Order Import

Validations:
•Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
•Check for sales_rep_id. Should exist for a booked order.
•Ordered_date should exist (header level)
•Delivery_lead_time should exist (line level)
•Earliest_acceptable_date should exist.
•Freight_terms should exist
Notes:
During import of orders, shipping tables are not populated.
If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.

Orders can be categorized based on their status:
1. Entered orders
2. Booked orders
3. Closed orders

Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders.

Some important columns that need to populated in the interface tables:
OE_HEADERS_IFACE_ALL:
•ORIG_SYS_DOCUMENT_REF
•ORDER_SOURCE
•CONVERSION_RATE
•ORG_ID
•ORDER_TYPE_ID
•PRICE_LIST
•SOLD_FROM_ORG_ID
•SOLD_TO_ORG_ID
•SHIP_TO_ORG_ID
•SHIP_FROM_ORG_ID
•CUSTOMER_NAME
•INVOICE_TO_ORG_ID
•OPERATION_CODE
OE_LINES_IFACE_ALL
•ORDER_SOURCE_ID
•ORIG_SYS_DOCUMENT_REF
•ORIG_SYS_LINE_REF
•ORIG_SYS_SHIPMENT_REF
•INVENTORY_ITEM_ID
•LINK_TO_LINE_REF
•REQUEST_DATE
•DELIVERY_LEAD_TIME
•DELIVERY_ID
•ORDERED_QUANTITY
•ORDER_QUANTITY_UOM
•SHIPPING_QUANTITY
•PRICING_QUANTITY
•PRICING_QUANTITY_UOM
•SOLD_FROM_ORG_ID
•SOLD_TO_ORG_ID
•INVOICE_TO_ ORG_ID
•SHIP_TO_ORG_ID
•PRICE_LIST_ID
•PAYMENT_TERM_ID

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

2.Item import (Item conversion): The Item Interface lets you import items into Oracle Inventory.

Pre-requisites:
•Creating an Organization
•Code Combinations
•Templates
•Defining Item Status Codes
•Defining Item Types

Interface tables:
•MTL_SYSTEM_ITEMS_INTERFACE
•MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
•MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
•MTL_INTERFACE_ERRORS (View errors after import)

Concurrent Program: Item import

In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have any value. Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.

Base Tables:
•MTL_SYSTEM_ITEMS_B
•MTL_ITEM_REVISIONS_B
•MTL_CATEGORIES_B
•MTL_CATEGORY_SETS_B
•MTL_ITEM_STATUS
•MTL_ITEM_TEMPLATES

Validations:
•Check for valid item type.
•Check for valid part_id/segment of the source table.
•Validate part_id/segment1 for master org.
•Validate and translate template id of the source table.
•Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
•Check for valid item status.
•Validate primary uom of the source table.
•Validate attribute values.
•Validate other UOMs of the source table.
•Check for unique item type. Discard the item, if part has non-unique item type.
•Check for description, inv_um uniqueness
•Validate organization id.
•Load master records and category records only if all
•Load child record if no error found.

Some important columns that need to populated in the interface tables:
MTL_SYSTEM_ITEMS_INTERFACE:
PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
SET_PROCESS_ID = 1
ORGANIZATION_ID
DESCRIPTION
ITEM_NUMBER and/or SEGMENT (n)
MATERIAL_COST
REVISION
TEMPLATE_ID
SUMMARY_FLAG
ENABLED_FLAG
PURCHASING_ITEM_FLAG
SALES_ACCOUNT (defaulted from
MTL_PARAMETERS.SALES_ACCOUNT)
COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)
MTL_ITEM_CATEGORIES_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER.
ORGANIZATION_ID or ORGANIZATION_CODE or both.
TRANSACTION_TYPE = 'CREATE' ('UPDATE' or 'DELETE' is not possible through Item Import).
CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
CATEGORY_ID or CATEGORY_NAME or both.
PROCESS_FLAG = 1
SET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)
MTL_ITEM_REVISIONS_INTERFACE:
INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the
ORGANIZATION_ID or ORGANIZATION_CODE or both
REVISION
CHANGE_NOTICE
ECN_INITIATION_DATE
IMPLEMENTATION_DATE
IMPLEMENTED_SERIAL_NUMBER
EFFECTIVITY_DATE
ATTRIBUTE_CATEGORY
ATTRIBUTEn
REVISED_ITEM_SEQUENCE_ID
DESCRIPTION
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.

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

3.Inventory On-hand quantity Interface: This interface lets you import the on hand inventory into Oracle.

Interface tables:
•MTL_TRANSACTIONS_INTERFACE
•MTL_MTL_TRANSACTION_LOTS_INTERFACE (If the item is Lot Controlled)
•MTLL_SERIAL_NUMBERS_INTERFACE (If the item is Serial Controlled)

Concurrent Program: Launch the Transaction Manager through Interface Manager or explicitly call the API – INV_TXN_MANAGER_PUB.PROCESS_TRANSACTIONS () to launch a dedicated transaction worker to process them.
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, and PROCESS_FLAG. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base Tables:
•MTL_ON_HAND_QUANTITIES
•MTL_LOT_NUMBERS
•MTL_SERIAL_NUMBERS

Validations:
Validate organization_id
Check if item is assigned to organization
Validate disposition_id
Check if the item for the org is lot controlled before inserting into the Lots interface table.
Check if the item for the org is serial controlled before inserting into Serial interface table.
Check if inventory already exists for that item in that org and for a lot.
Validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Some important columns that need to be populated in the interface tables:
MTL_TRANSACTIONS_INTERFACE:
TRANSACTION_SOURCE_NAME (ANY USER DEFINED VALUE),
TRANSACTION_HEADER_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL)
TRANSACTION_INTERFACE_ID (MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL – If item is lot or serial controlled, use this field to link to mtl_transactions_interface otherwise leave it as NULL),
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
PROCESS_FLAG (1 = Yet to be processed, 2 = Processed, 3= Error)
TRANSACTION_MODE (2 = Concurrent – to launch a dedicated transaction worker to explicitly process a set of transactions, 3 = Background – will be picked up by transaction manager polling process and assigned to transaction worker. These will not be picked up until the transaction manager is running)
SOURCE_CODE,
SOURCE_HEADER_ID,
SOURCE_LINE_ID (Details about the source like Order Entry etc for tracking purposes)
TRANSACTION_SOURCE_ID
Source Type Foreign Key Reference
Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or schedule WIP_ENTITIES.WIP_ENTITY_ID
Sales Order MTL_SALES_ORDERS.SALES_ORDER_ID
ITEM_SEGMENT1 TO 20,
TRANSACTION_QTY,
TRANSACTION_UOM,
SUBINVENTORY_CODE,
ORGANIZATION_ID,
LOC_SEGMENT1 TO 20.
MTL_TRANSACTION_LOTS_INTERFACE:
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
LOT_EXPIRATION_DATE,
TRANSACTION_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID (This is required for items under both lot and serial control to identify child records in mtl_serial_numbers_interface)
MTL_SERIAL_NUMBERS_INTERFACE:
TRANSACTION_INTERFACE_ID,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
VENDOR_SERIAL_NUMBER

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

4.Customer conversion: Customer Interface helps you create customers in Oracle Applications.

Interface tables:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base tables:
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Concurrent program: Customer Interface

Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Some important columns that need to be populated in the interface tables:
RA_CUSTOMERS_INTERFACE_ALL:
ORIG_SYSTEM_CUSTOMER_REF
SITE_USE_CODE
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG (I = Insert, U = Update)
CUSTOMER_NAME
CUSTOMER_NUMBER
CUSTOMER_STATUS
PRIMARY_SITE_USE_FLAG
LOCATION
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
STATE
PROVINCE
COUNTY
POSTAL_CODE
COUNTRY
CUSTOMER_ATTRIBUTE1
CUSTOMER_ATTRIBUTE2
CUSTOMER_ATTRIBUTE3
CUSTOMER_ATTRIBUTE4
CUSTOMER_ATTRIBUTE5
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE
ORG_ID
CUSTOMER_NAME_PHONETIC

RA_CUSTOMER_PROFILES_INT_ALL:
INSERT_UPDATE_FLAG
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
CUSTOMER_PROFILE_CLASS_NAME
CREDIT_HOLD
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATION_DATE
CREATED_BY
ORG_ID

RA_CONTACT_PHONES_INT_ALL:
ORIG_SYSTEM_CONTACT_REF
ORIG_SYSTEM_TELEPHONE_REF
ORIG_SYSTEM_CUSTOMER_REF
ORIG_SYSTEM_ADDRESS_REF
INSERT_UPDATE_FLAG
CONTACT_FIRST_NAME
CONTACT_LAST_NAME
CONTACT_TITLE
CONTACT_JOB_TITLE
TELEPHONE
TELEPHONE_EXTENSION
TELEPHONE_TYPE
TELEPHONE_AREA_CODE
LAST_UPDATE_DATE
LAST_UPDATED_BY
LAST_UPDATE_LOGIN
CREATION_DATE
CREATED_BY
EMAIL_ADDRESS
ORG_ID
--Customer API
Trading Community Architecture (TCA) is an architecture concept designed to support complex trading communities. These APIs utilize the new TCA model, inserting directly to the HZ tables.
API Details:
1.Set the organization id
Exec dbms_application_info.set_client_info(‘204’);
2.Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE
3.Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE
4.Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE
5.Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE
6.Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:
HZ_PARTIES
HZ_PARTY_SITES
HZ_LOCATIONS
HZ_CUST_ACCOUNTS
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITE_USES
Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation:
Validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Receivables/Customer-TCA-Architecture-and-API/index.aspx

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

5.Auto Invoice interface: This interface is used to import Customer invoices, Credit memos, Debit memos and On Account credits.

Pre-requisites:
Set of Books
Code combinations
Items
Sales representatives
Customers
Sales Tax rate
Payment Terms
Transaction Types
Freight Carriers
FOB
Batch Sources
Accounting Rules

Interface tables:
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_ERRORS (details about the failed records)

Base tables:
RA_BATCHES
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_LINE_SALESREPS
RA_CUST_TRX_GL_DIST_ALL
RA_CUSTOMER_TRX_TYPES_ALL

Concurrent Program: Auto invoice master program

Validations:
Check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id,
Validate if the amount includes tax flag.

Some important columns that need to be populated in the interface tables:
RA_INTERFACE_LINES_ALL:
AGREEMENT_ID
COMMENTS
CONVERSION_DATE
CONVERSION_RATE
CONVERSION_TYPE
CREDIT_METHOD_FOR_ACCT_RULE
CREDIT_METHOD_FOR_INSTALLMENTS
CURRENCY_CODE
CUSTOMER_BANK_ACCOUNT_ID
CUST_TRX_TYPE_ID
DOCUMENT_NUMBER
DOCUMENT_NUMBER_SEQUENCE_ID
GL_DATE
HEADER_ATTRIBUTE1–15
HEADER_ATTRIBUTE_CATEGORY
INITIAL_CUSTOMER_TRX_ID
INTERNAL_NOTES
INVOICING_RULE_ID
ORIG_SYSTEM_BILL_ADDRESS_ID
ORIG_SYSTEM_BILL_CONTACT_ID
ORIG_SYSTEM_BILL_CUSTOMER_ID
ORIG_SYSTEM_SHIP_ADDRESS_ID
ORIG_SYSTEM_SHIP_CONTACT_ID
ORIG_SYSTEM_SHIP_CUSTOMER_ID
ORIG_SYSTEM_SOLD_CUSTOMER_ID
ORIG_SYSTEM_BATCH_NAME
PAYMENT_SERVER_ORDER_ID
PREVIOUS_CUSTOMER_TRX_ID
PRIMARY_SALESREP_ID
PRINTING_OPTION
PURCHASE_ORDER
PURCHASE_ORDER_DATE
PURCHASE_ORDER_REVISION
REASON_CODE
RECEIPT_METHOD_ID
RELATED_CUSTOMER_TRX_ID
SET_OF_BOOKS_ID
TERM_ID
TERRITORY_ID
TRX_DATE
TRX_NUMBER

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

6.Receipt API: To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Pre-requisites:
Set of Books
Code combinations
Items
Quick Codes
Sales representatives
Customers
Sales Tax rate

API:
AR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY

Base tables:
AR_CASH_RECEIPTS

Validations:
Check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.

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

7.Lockbox interface: AutoLockbox lets us automatically process receipts that are sent directly to the bank instead of manually feeding them in Oracle Receivables.

AutoLockbox is a three step process:
1.Import: During this step, Lockbox reads and formats the data from your bank file into interface table AR_PAYMENTS_INTERFACE_ALL using a SQL *Loader
script.
2.Validation: The validation program checks data in this interface table for compatibility with Receivables. Once validated, the data is transferred into QuickCash tables (AR_INTERIM_CASH_RECEIPTS_ALL and
AR_INTERIM_CASH_RCPT_LINES_ALL).
3.Post QuickCash: This step applies the receipts and updates your customer’s balances.

Pre-Requisites:
Banks
Receipt Class
Payment Method
Receipt Source
Lockbox
Transmission format
AutoCash Rule sets

Interface tables:
AR_PAYMENTS_INTERFACE_ALL (Import data from bank file)
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Base Tables:
AR_CASH_RECEIPTS
AR_RECEIVABLES_APPLICATIONS
AR_ADJUSTMENTS
AR_DISTRIBUTIONS_ALL
AR_PAYMENT_SCHEDULES_ALL

Concurrent program: Lockbox
Validations:
Check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).

Some important columns that need to be populated in the interface tables:
AR_PAYMENTS_INTERFACE_ALL:
STATUS
RECORD_TYPE
LOCKBOX_NUMBER
BATCH_NAME
TRANSIT_ROUTING_NUMBER
ACCOUNT
CHECK_NUMBER
REMITTANCE_AMOUNT
DEPOSIT_DATE
ITEM_NUMBER
CURRENCY_CODE
DEPOSIT_TIME

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

8.AP invoice interface: This interface helps us to import vendor invoices into Oracle applications from external systems into Oracle Applications.

Pre-requisites:
Set of Books
Code combinations
Employees
Lookups

Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

Base tables:
AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import
Validations:
Check for valid vendor
Check for Source, Location, org_id, currency_code’s validity
Check for valid vendor site code.
Check if record already exists in payables interface table.

Some important columns that need to be populated in the interface tables:
AP_INVOICES_INTERFACE:
INVOICE_ID
INVOICE_NUM
INVOICE_DATE
VENDOR_NUM
VENDOR_SITE_ID
INVOICE_AMOUNT
INVOICE_CURRENCY_CODE
EXCHANGE_RATE
EXCHANGE_RATE_TYPE
EXCHANGE_DATE
DESCRIPTION
SOURCE
PO_NUMBER
PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
ATTRIBUTE1 TO 15
ORG_ID

AP_INVOICE_LINES_INTERFACE:
INVOICE_ID
INVOICE_LINE_ID
LINE_TYPE_LOOKUP_CODE
AMOUNT
DESCRIPTION
TAX_CODE
PO_NUMBER
PO_LINE_NUMBER
PO_SHIPMENT_NUM
PO_DISTRIBUTION_NUM
PO_UNIT_OF_MEASURE
QUANTITY_INVOICED
DIST_CODE_CONCATENATED
DIST_CODE_COMBINATION_ID
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ORG_ID

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

9.Vendor conversion/interface: This interface is used to import suppliers, supplier sites and site contacts into Oracle applications.

Pre-requisites setup’s required:
Payment terms
Pay Groups
CCID
Supplier classifications
Bank Accounts
Employees (if employees have to set up as vendors)

Interface tables:
AP_SUPPLIERS_INT
AP_SUPPLIER_SITES_INT
AP_SUP_SITE_CONTACT_INT

Base Tables:
PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS

Interface programs:
Supplier Open Interface Import
Supplier Sites Open Interface Import
Supplier Site Contacts Open Interface Import

Validations:
Check if vendor already exists
Check if vendor site already exists
Check if site contact already exists
Check if term is defined.
Some important columns that need to be populated in the interface tables:

AP_SUPPLIERS_INT:
VENDOR_NUMBER, VENDOR_NAME, VENDOR_TYPE, STATE_REPORTABLE, FED_REPORTABLE, NUM_1099, TYPE_1099, PAY_GROUP_LOOKUP_CODE, VENDOR_ID is auto generated.

AP_SUPPLIER_SITES_INT:
VENDOR_SITE_ID, ORG_ID, VENDOR_SITE_CODE, INACTIVE_DATE, PAY_SITE, PURCHASING_SITE, SITE_PAYMENT_TERM, ADDRESS1, ADDRESS2.ADDRESS3, CITY, STATE, COUNTRY, ZIP, PH_NUM, FAX_NUMBER, TAX_REPORTING_SITE_FLAG.
AP_SUP_SITE_CONTACTS_INT:
VENDOR_ID, VENDOR_SITE_ID, FIRST_NAME, LAST_NAME, AREA_CODE, PHONE, EMAIL, ORG_ID

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

10.Purchase Order conversion: The Purchasing Document Open Interface concurrent program was replaced by two new concurrent programs Import Price Catalogs and Import Standard Purchase Orders. Import Price Catalogs concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements. Import Standard Purchase Orders concurrent program is used to import Unapproved or Approved Standard Purchase Orders.
Import Standard Purchase Orders

Pre-requisites:
Suppliers, sites and contacts
Buyers
Line Types
Items
PO
Charge account setup

Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS (Fallouts)

Interface Program:
Import Standard Purchase Orders.

Base Tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL

Validations:
Header:
Check if OU name is valid
Check if Supplier is valid
Check if Supplier site is valid
Check if buyer is valid
Check if Payment term is valid
Check if Bill to and ship to are valid
Check if FOB, freight terms are valid
Lines:
Check if Line_type, ship_to_org, item, uom, ship_to_location_id, requestor, charge_account, deliver_to_location are valid
General:
Check for duplicate records in interface tables
Check if the record already exists in base tables.

Some important columns that need to be populated in the interface tables:
PO_HEADERS_INTERFACE:
INTERFACE_HEADER_ID (PO_HEADERS_INTERFACE_S.NEXTVAL), BATCH_ID, ORG_ID, INTERFACE_SOURCE_CODE, ACTION (‘ORIGINAL’,’UPDATE’,’REPLACE’), GROUP_CODE, DOCUMENT_TYPE_CODE, PO_HEADER_ID (NULL), RELEASE_ID, RELEASE_NUM, CURRENCY_CODE, RATE, AGENT_NAME, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION, BILL_TO_LOCATION, , PAYMENT_TERMS

PO_LINES_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, LINE_NUM, SHIPMENT_NUM, ITEM, REQUISITION_LINE_ID, UOM, UNIT_PRICE, FREIGHT_TERMS, FOB

PO_DISTRIBUTIONS_INTERFACE:
INTERFACE_LINE_ID, INTERFACE_HEADER_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, QUANTITY_ORDERED, QTY_DELIVERED, QTY_BILLED, QTY_CANCELLED, DELIVER_TO_LOCATION_ID, DELIVER_TO_PERSON_ID, SET_OF_BOOKS, CHARGE_ACCT, AMOUNT_BILLED.

Import Blanket Purchase Agreements:
Interface Tables:
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE

Interface program: Import Price Catalogs

Base tables:
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL

Example:
Suppose you want to create a blanket with one line and two price breaks and the details for the price break are as below:
1) Quantity = 500, price = 10, effective date from '01-JAN-2006' to
'31-JUN-2006'
2) Quantity = 500, price = 11, effective date from '01-JUL-2006' to
'01-JAN-2007'
To create the above the BPA, you would create ONE record in PO_HEADERS_INTERFACE and THREE records in PO_LINES_INTERFACE
LINE1: It will have only the line information. LINE NUM would be 1.
LINE2: For the first Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 1 and SHIPMENT_TYPE would be ‘PRICE BREAK’
LINE3: For the second Price Break details, LINE NUM will be the same as above i.e. 1. SHIPMENT_NUM would be 2 and SHIPMENT_TYPE would be ‘PRICE BREAK’
All the line-level records above must have the same INTERFACE_HEADER_ID.
For detailed explanation refer to the below article:
http://www.erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Import-Blanket-Purchase-Agreements/index.aspx

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

11.Requisition import: You can automatically import requisitions into Oracle Applications using the Requisitions Open Interface

Pre-requisites:
Set of Books
Code combinations
Employees
Items
Define a Requisition Import Group-By method in the Options window.
Associate a customer with your deliver-to location using the Customer Addresses window for internally sourced requisitions.

Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Base tables:
PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: REQUISITION IMPORT

Validations:
Check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.

Some important columns that need to be populated in the interface tables:
PO_REQUISITIONS_INTERFACE_ALL:
INTERFACE_SOURCE_CODE (to identify the source of your Requisitions)
DESTINATION_TYPE_CODE
AUTHORIZATION_STATUS
PREPARER_ID or PREPARER_NAME
QUANTITY
CHARGE_ACCOUNT_ID or charge account segment values
DESTINATION_ORGANIZATION_ID or DESTINATION_ORGANIZATION_CODE
DELIVER_TO_LOCATION_ID or DELIVER_TO_LOCATION_CODE
DELIVER_TO_REQUESTOR_ID or DELIVER_TO_REQUESTOR_NAME
ORG_ID
ITEM_ID or item segment values (values if the SOURCE_TYPE_CODE or DESTINATION_TYPE_CODE is 'INVENTORY')
PO_REQ_DIST_INTERFACE_ALL:
CHARGE_ACCOUNT_ID or charge account segment values
DISTRIBUTION_NUMBER
DESTINATION_ORGANIZATION_ID
DESTINATION_TYPE_CODE
INTERFACE_SOURCE_CODE
ORG_ID
DIST_SEQUENCE_ID (if MULTI_DISTRIBUTIONS is set to Y)

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

12.PO Receipts Interface: The Receiving Open Interface is used for processing and validating receipt data that comes from sources other than the Receipts window in Purchasing.

Pre-requisites:
Set of Books
Code combinations
Employees
Items

Interface tables:
RCV_HEADERS_INTERFACE
RCV_TRANSACTIONS_INTERFACE
PO_INTERFACE_ERRORS

Concurrent program: RECEIVING OPEN INTERFACE

Base tables:
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS

Validations:
Check that SHIPPED_DATE should not be later than today.
Check if vendor is valid.
If Invoice number is passed, check for its validity
Check if Item is valid

Some important columns that need to be populated in the interface tables:
RCV_HEADERS_INTERFACE:
HEADER_INTERFACE_ID
GROUP_ID
PROCESSING_STATUS_CODE
RECEIPT_SOURCE_CODE
TRANSACTION_TYPE
SHIPMENT_NUM
RECEIPT_NUM
VENDOR_NAME
SHIP_TO_ORGANIZATION_CODE
SHIPPED_DATE
INVOICE_NUM
INVOICE_DATE
TOTAL_INVOICE_AMOUNT
PAYMENT_TERMS_ID
EMPLOYEE_NAME
VALIDATION_FLAG (Indicates whether to validate a row or not, values ‘Y’, ‘N’)
RCV_TRANSACTIONS_INTERFACE:
INTERFACE_TRANSACTION_ID
GROUP_ID
TRANSACTION_TYPE (’SHIP’ for a standard shipment (an ASN or ASBN) or ’RECEIVE’ for a standard receipt)
TRANSACTION_DATE
PROCESSING_STATUS_CODE =’PENDING’
CATEGORY_ID
QUANTITY
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_REVISION
EMPLOYEE_ID
AUTO_TRANSACT_CODE
SHIP_TO_LOCATION_ID
RECEIPT_SOURCE_CODE
TO_ORGANIZATION_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
SUBINVENTORY
HEADER_INTERFACE_ID
DELIVER_TO_PERSON_NAME
DELIVER_TO_LOCATION_CODE
VALIDATION_FLAG
ITEM_NUM
VENDOR_ITEM_NUM
VENDOR_ID
VENDOR_SITE_ID
ITEM_ID
ITEM_DESCRIPTION
SHIP_TO_LOCATION_ID

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

13.GL Journal interface: This interface lets you import journals from other applications like Receivables, Payables etc to integrate the information with General Ledger.

Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations
Currencies
Categories
Journal Sources

Interface tables: GL_INTERFACE

Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES
Validations:
Validate SOB, journal source name, journal category name, actual flag
A – Actual amounts
B – Budget amounts
E – Encumbrance amount
If you enter E in the interface table, then enter appropriate encumbrance ID, if B enter budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if code combination is valid and enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.

Some important columns that need to be populated in the interface tables:
GL_INTERFACE:
STATUS
SET_OF_BOOKS_ID
ACCOUNTING_DATE
CURRENCY_CODE
DATE_CREATED
CREATED_BY
ACTUAL_FLAG
USER_JE_CATEGORY_NAME
USER_JE_SOURCE_NAME
CURRENCY_CONVERSION_DATE
ENCUMBRANCE_TYPE_ID
BUDGET_VERSION_ID
USER_CURRENCY_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
SEGMENT1 to
ENTERED_DR
ENTERED_CR
ACCOUNTED_DR
ACCOUNTED_CR
TRANSACTION_DATE
PERIOD_NAME
JE_LINE_NUM
CHART_OF_ACCOUNTS_ID
FUNCTIONAL_CURRENCY_CODE
CODE_COMBINATION_ID
DATE_CREATED_IN_GL
GROUP_ID

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

14.GL budget interface: Budget interface lets you load budget data from external sources into Oracle Applications.

Pre-requisites:
Set of Books
Flex field Value sets
Code Combinations

Interface tables: GL_BUDGET_INTERFACE

Base tables:
GL_BUDGETS
GL_BUDGET_ASSIGNMENTS
GL_BUDGET_TYPES

Concurrent program: Budget Upload

Validations:
Check if CURRENCY_CODE is valid.
Check if SET_OF_BOOKS_ID is valid.
Check if BUDGET_ENTITY_NAME (budget organization) is valid.

Some important columns that need to be populated in the interface tables:
GL_BUDGET_INTERFACE:
BUDGET_NAME NOT
BUDGET_ENTITY_NAME
CURRENCY_CODE
FISCAL_YEAR
UPDATE_LOGIC_TYPE
BUDGET_ENTITY_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
BUDGET_VERSION_ID
PERIOD_TYPE
DR_FLAG
STATUS
ACCOUNT_TYPE
PERIOD1_AMOUNT through PERIOD60_AMOUNT
SEGMENT1 through SEGMENT30

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

15.GL daily conversion rates: This interface lets you load the rates automatically into General Ledger.

Pre-requisites:
Currencies
Conversion rate Types

Interface tables: GL_DAILY_RATES_INTERFACE
Base tables:
GL_DAILY_RATES
GL_DAILY_CONVERSION_TYPES

Concurrent Program: You do not need to run any import programs. The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

Validations:
Check if FROM_CURRENCY and TO_CURRENCY are valid.
Check if USER_CONVERSION_TYPE is valid.

Some important columns that need to be populated in the interface tables:
GL_DAILY_RATES_INTERFACE:
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG (D= Delete, I = Insert, U = Update)
INVERSE_CONVERSION_RATE

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

How to disable a parameter based on the other parameter

Requirement
-----------
There are 2 parameters, First parameter is independent value set and second parameter is Table value set. First parameter contains two values 'Summarized' and 'Detailed'.Second parameter contains the values like 100,200,300,400, etc.

Now while submitting the concurrent program the second parameter should be in disables mode and when we enter the First parameter value as 'Detailed' then only the second parameter should be enabled. If the First parameter is null or if we enter the value as 'Summarized' then it should be in disabled mode.

Solution
--------
For this we have to take one extra parameter that is dummy parameter along with First and Second parameters.

Parameter Seq,
1.First Parameter
2.Dummy Parameter
3.Second Parameter

1.First Parameter Properties,
Name: First_param
value set: First_param_vs (Table or Independent value set)
required check box: based on the requirement.
Default type/ Default Value : Null
display check box: Checked or based on the requirement.

2.Dummy Parameter
Name: Dummy_param
value set: Dummy_param_vs (Table)
required check box: Checked.
Default type: Sql Statement
Default value: select decode(:$FLEX$.First_param_vs,'Detailed',1,null) from dual
display check box: Unchecked.
Value Set
Table: (select decode(:$FLEX$.First_param_vs,'Detailed',1,null) x from dual) y
id: y.x

3.Second Parameter
Name: Second_param
value set: Second_param_vs (Table)
required check box: based on the requirement.
Default type/ Default Value : Null
display check box: Checked or based on the requirement.
Value Set
Table: Table Name
id: table_name.column_name
where: :$FLEX$.Dummy_param_vs = 1

Oracle SQL FAQ - Part3

1. How does one select EVERY Nth row from a table?
One can easily select all even, odd, or Nth rows from a table using SQL queries like this:
Method 1: Using a subquery
SELECT * FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,4)FROM emp);

Method 2: Use dynamic views (available from Oracle7.2):
SELECT * FROM (SELECT rownum rn, empno, enameFROM emp) temp
WHERE MOD(temp.ROWNUM,4) = 0;

Method 3: Using GROUP BY and HAVING
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING MOD(rownum,n) = 0 OR rownum = 2-n;
Please note, there is no explicit row order in a relational database. However, these queries are quite fun and may even help in the odd situation.

2. How does one select the LAST N rows from a table?
From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the LAST N or BOTTOM N rows. Examples:
Get the bottom 10 employees based on their salary
SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal ASC) sal_rank
FROM emp )
WHERE sal_rank <= 10;
Select the employees getting the lowest 10 salaries
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the bottom N rows using an inner-query with an ORDER BY clause:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1)
WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol <= a.maxcol)
ORDER BY maxcol;

3. How does one select the TOP N rows from a table?
From Oracle 9i onwards, the RANK() and DENSE_RANK() functions can be used to determine the TOP N rows. Examples:
Get the top 10 employees based on their salary
SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank <= 10;

Select the employees making the top 10 salaries
SELECT ename, sal
FROM ( SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) sal_dense_rank
FROM emp )
WHERE sal_dense_rank <= 10;
For Oracle 8i and above, one can get the Top N rows using an inner-query with an ORDER BY clause:
SELECT *
FROM (SELECT * FROM my_table ORDER BY col_name_1 DESC)
WHERE ROWNUM < 10;
Use this workaround for older (8.0 and prior) releases:
SELECT *
FROM my_table a
WHERE 10 >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;

4. How to generate a text graphs (histograms) using SQL?
SELECT d.dname AS "Department",
LPAD('+', COUNT(*), '+') as "Graph"
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;
Sample output:
Department Graph
-------------- --------------------------------------------------
ACCOUNTING +++
RESEARCH +++++
SALES ++++++
In the above example, the value returned by COUNT(*) is used to control the number of "*" characters to return for each department. We simply pass COUNT(*) as an argument to the string function LPAD (or RPAD) to return the desired number of *'s.

Map/ concatenate several rows to a column
This FAQ will demonstrate how row values can be concatenated into a single column value (similar to MySQL's [i]GROUP_CONCAT[/i] function).
Start by creating this function:
SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
2 ret VARCHAR2(4000);
3 hold VARCHAR2(4000);
4 cur sys_refcursor;
5 BEGIN
6 OPEN cur FOR q;
7 LOOP
8 FETCH cur INTO hold;
9 EXIT WHEN cur%NOTFOUND;
10 IF ret IS NULL THEN
11 ret := hold;
12 ELSE
13 ret := ret || ',' || hold;
14 END IF;
15 END LOOP;
16 RETURN ret;
17 END;
18 /
Function created.
This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.
Here is an example of how to map several rows to a single concatenated column:
SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
2 FROM dual;
DEPARTMENTS
-----------------------------------------------------------------------ACCOUNTING,RESEARCH,SALES,OPERATIONS
This example is more interresting, it concatenates a column across several rows based on an aggregation:
SQL> col employees format a50
SQL> SELECT deptno,
2 rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
3 FROM emp
4 GROUP BY deptno
5 /
DEPTNO EMPLOYEES
---------- --------------------------------------------------
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
20 SMITH,JONES,SCOTT,ADAMS,FORD
10 CLARK,KING,MILLER

5. What is the difference between VARCHAR, VARCHAR2 and CHAR data types?
Both CHAR and VARCHAR2 types are used to store character string values, however, they behave very differently. The VARCHAR type should not be used:
CHAR
---------
CHAR should be used for storing fixed length character strings. String values will be space/blank padded before stored on disk. If this type is used to store variable length strings, it will waste a lot of disk space.
SQL> CREATE TABLE char_test (col1 CHAR(10));
Table created.

SQL> INSERT INTO char_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM char_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32
Note: ASCII character 32 is a blank space.

VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
SQL> CREATE TABLE varchar_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121
VARCHAR2
----------------
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
SQL> CREATE TABLE varchar2_test (col1 VARCHAR2(10));
Table created.

SQL> INSERT INTO varchar2_test VALUES ('qwerty');
1 row created.

SQL> SELECT col1, length(col1), dump(col1) "ASCII Dump" FROM varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------ -----------------------------------------------qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

Oracle SQL FAQ - Part2

1. Can one retrieve only rows X to Y from a table?
SELECT * FROM (
SELECT ename, rownum rn
FROM emp WHERE rownum < 101
) WHERE RN between 91 and 100 ;
Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).
SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;
Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:
SELECT *
FROM tableX
WHERE rowid in (
SELECT rowid FROM tableX
WHERE rownum <= 7
MINUS
SELECT rowid FROM tableX
WHERE rownum < 5);
"this one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."
SELECT *
FROM (SELECT a.*, rownum RN
FROM (SELECT *
FROM t1 ORDER BY key_column) a
WHERE rownum <=7)
WHERE rn >=5;
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.
The generic solution to get full information of rows between x and y

SELECT * FROM emp WHERE empno in (SELECT empno FROM emp GROUP BY rownum,empno HAVING rownum BETWEEN &x AND &y);

"select particular rows from a table : select for rownum = 4, 15 and 17."
select * from (
select rownum myrownum, emp.* from employees emp
) mytable
where myrownum in (4,15,17);

"selecting row between range of rownum: select for rownum between (12, 20)."
select * from (
select rownum myrownum, emp.* from employees emp
) mytable
where myrownum between 12 and 20;

"Replace 12 and 20 with &x and &y respectively to assign range dynamically."
select * from (
select rownum myrownum, emp.* from employees emp
) mytable
where myrownum between &x and &y;

"Combined query to give complete flexibility to pick particular rows and also a given range."
select * from (
select rownum myrownum, emp.* from employees emp
) mytable
where myrownum between 12 and 17
or myrownum in ( 3, 18, 25);

2. Can one retrieve only the Nth row from a table?
SELECT * FROM t1 a
WHERE n = (SELECT COUNT(rowid)
FROM t1 b
WHERE a.rowid >= b.rowid);
SELECT * FROM (
SELECT ENAME,ROWNUM RN FROM EMP WHERE ROWNUM < 101 )
WHERE RN = 100;
Note: In this first query we select one more than the required row number, then we select the required one. Its far better than using a MINUS operation.
SELECT f1 FROM t1
WHERE rowid = (
SELECT rowid FROM t1
WHERE rownum <= 10
MINUS
SELECT rowid FROM t1
WHERE rownum < 10);
SELECT rownum,empno FROM scott.emp a
GROUP BY rownum,empno HAVING rownum = 4;
Alternatively...
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN
(SELECT rowid FROM emp WHERE rownum < 10);
Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

3. How can one dump/ examine the exact content of a database column?
Table data can be extracted from the database as octal, decimal or hex values:
SELECT DUMP(col1, 10)
FROM tab1
WHERE cond1 = val1;
DUMP(COL1)
-------------------------------------
Typ=96 Len=4: 65,66,67,32
For this example, type=96 is indicating a CHAR column. The last byte in the column is 32, which is the ASCII code for a space. This tells us that this column is blank-padded.

4. How does one add a column to the middle of a table?
Oracle only allows columns to be added to the end of an existing table. Example:
SQL> CREATE TABLE tab1 ( col1 NUMBER );
Table created.

SQL> ALTER TABLE tab1 ADD (col2 DATE);
Table altered.

SQL> DESC tab1
Name Null? Type
----------------------------------------- -------- --------------------COL1 NUMBER
COL2 DATE
Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:
ALTER TABLE tablename ADD columnname AFTER columnname;
Oracle does not support this syntax. However, it doesn't mean that it cannot be done.
Workarounds:
1. Create a new table and copy the data across.
SQL> RENAME tab1 TO tab1_old;
Table renamed.

SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
Table created.
2. Use the DBMS_REDEFINITION package to change the structure on-line while users are working.

5. How does one code a hierarchical tree-structured query?
The SCOTT/TIGER database schema contains a table EMP with a self-referencing relation (EMPNO and MGR columns). This table is perfect for testing and demonstrating tree-structured queries as the MGR column contains the employee number of the "current" employee's boss.
The LEVEL pseudo-column is an indication of how deep in the tree one is. Oracle can handle queries with a depth of up to 255 levels. Look at this example:
SQL> SELECT level, empno, ename, mgr
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL
5 /
LEVEL EMPNO ENAME MGR
---------- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
...
One can produce an indented report by using the level number to substring or lpad() a series of spaces, and concatenate that to the string. Look at this example:
SQL> SELECT LPAD(' ', LEVEL * 2) || ename
2 FROM emp
3 CONNECT BY PRIOR empno = mgr
4 START WITH mgr IS NULL;
LPAD(,LEVEL*2)||ENAME
------------------------------------------------------
KING
JONES
SCOTT
...
Use the "start with" clause to specify the start of the tree. More than one record can match the starting condition. One disadvantage of having a "connect by prior" clause is that you cannot perform a join to other tables. The "connect by prior" clause is rarely implemented in the other database offerings. Trying to do this programmatically is difficult as one has to do the top level query first, then, for each of the records open a cursor to look for child nodes.
One way of working around this is to use PL/SQL, open the driving cursor with the "connect by prior" statement, and the select matching records from other tables on a row-by-row basis, inserting the results into a temporary table for later retrieval.
NOTE: Tree-structured queries are definitely non-relational (enough to kill Codd and make him roll in his grave). Also, this feature is not often found in other database offerings.

6. How does one count/sum data values in a column?
Count/sum FIX values:
Use this simple query to count the number of data values in a column:
select my_table_column, count(*)
from my_table
group by my_table_column;
A more sophisticated example...
select dept, sum( decode(sex,'M',1,0)) MALE,
sum( decode(sex,'F',1,0)) FEMALE,
count(decode(sex,'M',1,'F',1)) TOTAL
from my_emp_table
group by dept;
Count/sum RANGES of data values in a column:
A value x will be between values y and z if GREATEST(x, y) = LEAST(x, z). Look at this example:
select f2,
sum(decode(greatest(f1,59), least(f1,100), 1, 0)) "Range 60-100",
sum(decode(greatest(f1,30), least(f1, 59), 1, 0)) "Range 30-59",
sum(decode(greatest(f1, 0), least(f1, 29), 1, 0)) "Range 00-29"
from my_table
group by f2;
For equal size ranges it might be easier to calculate it with DECODE(TRUNC(value/range), 0, rate_0, 1, rate_1, ...). Eg.
select ename "Name", sal "Salary",
decode( trunc(f2/1000, 0), 0, 0.0,
1, 0.1,
2, 0.2,
3, 0.31) "Tax rate"
from my_table;

7. How does one drop/ rename a column in a table?
Drop a column
From Oracle 8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Workarounds for older releases:
SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select >specific columns> from t1_base;
SQL> create table t2 as select >specific columns> from t1;
SQL> drop table t1;
SQL> rename t2 to t1;
Rename a column
From Oracle 9i one can RENAME a column from a table. Look at this example:
ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;
Workarounds for older releases:
Use a view with correct column names:
rename t1 to t1_base;
create view t1 >column list with new name> as select * from t1_base;
Recreate the table with correct column names:
create table t2 >column list with new name> as select * from t1;
drop table t1;
rename t2 to t1;
Add a column with a new name and drop an old column:
alter table t1 add ( newcolame datatype );
update t1 set newcolname=oldcolname;
alter table t1 drop column oldcolname;

8. How does one implement IF-THEN-ELSE logic in a SELECT statement?
One can use the CASE statement or functions like DECODE, NVL, NVL2, NULLIF, COALESCE, etc.
Here is the syntax for the CASE-statement:
CASE exp WHEN comparison_exp1 THEN return_exp1
[WHEN comparison_exp2 THEN return_exp2
WHEN comparison_exp3 THEN return_exp3
ELSE else_exp
]
END
And for DECODE:
DECODE( col | exprn, srch1, rslt1 [, srch2, rslt2,...,] [,default] )

9. How does one prevent Oracle from using an Index?
In certain cases, one may want to disable the use of a specific, or all indexes for a given query. Here are some examples:
Adding an expression to the indexed column
SQL>select count(*) from t where empno+0=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
Specifying the FULL hint to force full table scan
SQL>select /*+ FULL(t) */ * from t where empno=1000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO GRADE
---------- ---------- --------- ---------- --------- ---------- ------- 1000 Victor DBA 7839 20-MAY-03 11000 0 10 JUNIOR

Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=41)
1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=41)
Specifying NO_INDEX hint
SQL>select /*+ NO_INDEX(T) */ count(*) from t where empno=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)
Using a function over the indexed column
SQL>select count(*) from t where to_number(empno)=1000;
COUNT(*)
----------
1
Execution Plan
--------------------------------------------- ----- --------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=3)

Oracle SQL FAQ - Part1

1. What is SQL and where does it come from?
Structured Query Language (SQL) is a language that provides an interface to relational database systems. The proper pronunciation of SQL, and the preferred pronunciation within Oracle Corp, is "sequel" and not "ess cue ell".
SQL was developed by IBM in the 1970s for use in System R, and is a de facto standard, as well as an ISO and ANSI standard.
In common usage SQL also encompasses DML (Data Manipulation Language), for INSERTs, UPDATEs, DELETEs and DDL (Data Definition Language), used for creating and modifying tables and other database structures.
The development of SQL is governed by standards. A major revision to the SQL standard was completed in 1992, called SQL2. SQL3 support object extensions and are (partially?) implemented in Oracle8 and 9i.
Example SQL statements:
CREATE TABLE table1 (column1 NUMBER, column2 VARCHAR2(30));
INSERT INTO table1 VALUES (1, 'XYZ');
SELECT * FROM table1 WHERE column2 = 'XYZ';

2. What are the difference between DDL, DML and DCL commands?
DDL - Data Definition Language: statements used to define the database structure or schema. Some examples:
• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object
DML - Data Manipulation Language: statements used for managing data within schema objects. Some examples:
• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to the data
• LOCK TABLE - controls concurrency
DCL - Data Control Language. Some examples:
• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command
TCL - Transaction Control: statements used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
DML are not auto-commit. i.e. you can roll-back the operations, but DDL are auto-commit

3. Difference between TRUNCATE, DELETE and DROP commands?
The DELETE command is used to remove some or all rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
14

SQL> DELETE FROM emp WHERE job = 'CLERK';
4 rows deleted.
SQL> COMMIT;
Commit complete.

SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
10
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.
SQL> TRUNCATE TABLE emp;
Table truncated.
SQL> SELECT COUNT(*) FROM emp;
COUNT(*)
----------
0
The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
SQL> DROP TABLE emp;
Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.
From Oracle 10g a table can be "undropped". Example:
SQL> FLASHBACK TABLE emp TO BEFORE DROP;
Flashback complete.
PS: DELETE will not free up used space within a table. This means that repeated DELETE commands will severely fragment the table and queries will have to navigate this "free space" in order to retrieve rows.

4. How does one escape special characters when writing SQL queries?
Escape quotes
Use two quotes for every one displayed. Examples:
SQL> SELECT 'Frank''s Oracle site' AS text FROM DUAL;
TEXT
--------------------
Franks's Oracle site

SQL> SELECT 'A ''quoted'' word.' AS text FROM DUAL;
TEXT
----------------
A 'quoted' word.

SQL> SELECT 'A ''''double quoted'''' word.' AS text FROM DUAL;
TEXT
-------------------------
A ''double quoted'' word.
Escape wildcard characters
The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:
SELECT name FROM emp
WHERE id LIKE '%/_%' ESCAPE '/';
SELECT name FROM emp
WHERE id LIKE '%\%%' ESCAPE '\';
Escape ampersand (&) characters in SQL*Plus
When using SQL*Plus, the DEFINE setting can be changed to allow &'s (ampersands) to be used in text:
SET DEFINE ~
SELECT 'Laurel & Hardy' FROM dual;
Other methods:
Define an escape character:
SET ESCAPE '\'
SELECT '\&abc' FROM dual;
Don't scan for substitution variables:
SET SCAN OFF
SELECT '&ABC' x FROM dual;
Another way to escape the & would be to use concatenation, which would not require any SET commands -
SELECT 'Laurel ' || '&' || ' Hardy' FROM dual;
Use the 10g Quoting mechanism:
Syntax
q'[QUOTE_CHAR]Text[QUOTE_CHAR]'
Make sure that the QUOTE_CHAR doesnt exist in the text.
SELECT q'{This is Orafaq's 'quoted' text field}' FROM DUAL;

5. Can one select a random collection of rows from a table?
The following methods can be used to select a random collection of rows from a table:
The SAMPLE Clause
From Oracle 8i, the easiest way to randomly select rows from a table is to use the SAMPLE clause with a SELECT statement. Examples:
SELECT * FROM emp SAMPLE(10);
In the above example, Oracle is instructed to randomly return 10% of the rows in the table.
SELECT * FROM emp SAMPLE(5) BLOCKS;
This example will sample 5% of all formatted database blocks instead of rows.
This clause only works for single table queries on local tables. If you include the SAMPLE clause within a multi-table or remote query, you will get a parse error or "ORA-30561: SAMPLE option not allowed in statement with multiple table references". One way around this is to create an inline view on the driving table of the query with the SAMPLE clause. Example:
SELECT t1.dept, t2.emp
FROM (SELECT * FROM dept SAMPLE(5)) t1,
emp t2
WHERE t1.dep_id = t2.dep_id;
If you examine the execution plan of a "Sample Table Scan", you should see a step like this:
TABLE ACCESS (SAMPLE) OF 'EMP' (TABLE)
ORDER BY dbms_random.value()
This method orders the data by a random column number. Example:
SQL> SELECT * FROM (SELECT ename
2 FROM emp
3 ORDER BY dbms_random.value())
4 WHERE rownum <= 3;
ENAME
----------
WARD
MILLER
TURNER
The ORA_HASH() function
The following example retrieves a subset of the data in the emp table by specifying 3 buckets (0 to 2) and then returning the data from bucket 1:
SELECT * FROM emp WHERE ORA_HASH(empno, 2) = 1;

6. How does one eliminate duplicates rows from a table?
Choose one of the following queries to identify or remove duplicate rows from a table leaving only unique records in the table:
Method 1:
Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key):
SQL> DELETE FROM table_name A
2 WHERE ROWID > ( SELECT min(rowid)
3 FROM table_name B
4 WHERE A.key_values = B.key_values );
Method 2:
This method is usually faster. However, remember to recreate all indexes, constraints, triggers, etc. on the table when done.
SQL> create table table_name2 as select distinct * from table_name1;
SQL> drop table table_name1;
SQL> rename table_name2 to table_name1;
Method 3:
SQL> DELETE FROM my_table t1
2 WHERE EXISTS ( SELECT 'x' FROM my_table t2
3 WHERE t2.key_value1 = t1.key_value1
4 AND t2.key_value2 = t1.key_value2
4 AND t2.rowid > t1.rowid );
Note: One can eliminate N^2 unnecessary operations by creating an index on the joined fields in the inner loop (no need to loop through the entire table on each pass by a record). This will speed-up the deletion process.
Note 2: If you are comparing NULL columns, use the NVL function. Remember that NULL is not equal to NULL. This should not be a problem as all key columns should be NOT NULL by definition.
Method 4:
This method collects the first row (order by rowid) for each key values and delete the rows that are not in this set:
SQL> DELETE FROM my_table t1
1 WHERE rowid NOT IN ( SELECT min(rowid)
2 FROM my_table t2
3 GROUP BY key_value1, key_value2 );
Note: IF key_value1 is null or key_value2 is null, this still works correctly.

7. How does one get the time difference between two date columns?
Oracle allows two date values to be subtracted from each other returning a numeric value indicating the number of days between the two dates (may be a fraction). This example will show how to relate it back to a time value.
Let's investigate some solutions. Test data:
SQL> CREATE TABLE dates (date1 DATE, date2 DATE);
Table created.
SQL>
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/24);
1 row created.
SQL> INSERT INTO dates VALUES (SYSDATE, SYSDATE-1/60/24);
1 row created.
SQL> SELECT (date1 - date2) FROM dates;
DATE1-DATE2
-----------
1
.041666667
.000694444
Solution 1
SQL> SELECT floor(((date1-date2)*24*60*60)/3600)
2 || ' HOURS ' ||
3 floor((((date1-date2)*24*60*60) -
4 floor(((date1-date2)*24*60*60)/3600)*3600)/60)
5 || ' MINUTES ' ||
6 round((((date1-date2)*24*60*60) -
7 floor(((date1-date2)*24*60*60)/3600)*3600 -
8 (floor((((date1-date2)*24*60*60) -
9 floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60) ))
10 || ' SECS ' time_difference
11 FROM dates;
TIME_DIFFERENCE
--------------------------------------------------------------------------------
24 HOURS 0 MINUTES 0 SECS
1 HOURS 0 MINUTES 0 SECS
0 HOURS 1 MINUTES 0 SECS
Solution 2
If you don't want to go through the floor and ceiling maths, try this method:
SQL> SELECT to_number( to_char(to_date('1','J') +
2 (date1 - date2), 'J') - 1) days,
3 to_char(to_date('00:00:00','HH24:MI:SS') +
4 (date1 - date2), 'HH24:MI:SS') time
5 FROM dates;
DAYS TIME
---------- --------
1 00:00:00
0 01:00:00
0 00:01:00
Solution 3
Here is a simpler method:
SQL> SELECT trunc(date1-date2) days,
2 to_char(trunc(sysdate) + (date1 - date2), 'HH24 "Hours" MI "Minutes" SS "Seconds"') time
3 FROM dates;
DAYS TIME
---------- ------------------------------
1 00 Hours 00 Minutes 00 Seconds
0 01 Hours 00 Minutes 00 Seconds
0 00 Hours 01 Minutes 00 Seconds

8. How does one add a day/hour/minute/second to a date value?
The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:
SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE SYSDATE+1/24 SYSDATE+1/1440 SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13
The following format is frequently used with Oracle Replication:
select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53
Here are a couple of examples:
Description Date Expression
Now SYSDATE
Tomorow/ next day SYSDATE + 1
Seven days from now SYSDATE + 7
One hour from now SYSDATE + 1/24
Three hours from now SYSDATE + 3/24
A half hour from now SYSDATE + 1/48
10 minutes from now SYSDATE + 10/1440
30 seconds from now SYSDATE + 30/86400
Tomorrow at 12 midnight TRUNC(SYSDATE + 1)
Tomorrow at 8 AM TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m TRUNC(LEAST(NEXT_DAY(sysdate, 'MONDAY'), NEXT_DAY(sysdate, 'WEDNESDAY'), NEXT_DAY(sysdate, 'FRIDAY'))) + 9/24

9. How does one code a matrix/crosstab/pivot report in SQL?
Newbies frequently ask how one can display "rows as columns" or "columns as rows". Look at these example crosstab queries (also sometimes called transposed, matrix or pivot queries):
SELECT *
FROM (SELECT job,
sum(decode(deptno,10,sal)) DEPT10,
sum(decode(deptno,20,sal)) DEPT20,
sum(decode(deptno,30,sal)) DEPT30,
sum(decode(deptno,40,sal)) DEPT40
FROM scott.emp
GROUP BY job)
ORDER BY 1;
JOB DEPT10 DEPT20 DEPT30 DEPT40
--------- ---------- ---------- ---------- ----------
ANALYST 6000
CLERK 1300 1900 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
Here is the same query with some fancy headers and totals:
SQL> ttitle "Crosstab Report"
SQL> break on report;
SQL> compute sum of dept10 dept20 dept30 dept40 total on report;
SQL>
SQL> SELECT *
2 FROM (SELECT job,
3 sum(decode(deptno,10,sal)) DEPT10,
4 sum(decode(deptno,20,sal)) DEPT20,
5 sum(decode(deptno,30,sal)) DEPT30,
6 sum(decode(deptno,40,sal)) DEPT40,
7 sum(sal) TOTAL
8 FROM emp
9 GROUP BY job)
10 ORDER BY 1;

Mon Aug 23 page 1
Crosstab Report

JOB DEPT10 DEPT20 DEPT30 DEPT40 TOTAL
--------- ---------- ---------- ---------- ---------- ----------
ANALYST 6000 6000
CLERK 1300 1900 950 4150
MANAGER 2450 2975 2850 8275
PRESIDENT 5000 5000
SALESMAN 5600 5600
---------- ---------- ---------- ---------- ----------
sum 8750 10875 9400 29025
Here's another variation on the theme:
SQL> SELECT DECODE(MOD(v.row#,3)
2 ,1, 'Number: ' ||deptno
3 ,2, 'Name: ' ||dname
4 ,0, 'Location: '||loc
5 ) AS "DATA"
6 FROM dept,
7 (SELECT rownum AS row# FROM user_objects WHERE rownum < 4) v
8 WHERE deptno = 30
9 /
DATA
--------------------------------------- ---------
Number: 30
Name: SALES
Location: CHICAGO
From Oracle 11g, we can use pivot option

To migrate DFF from Instance1 to Instance2

/*Download description flexfield*/
Run the below download command in home directory of Instance1, the ldt file descript_flex.ldt is created in the same directory.

FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt desc_flex_application DESCRIPTIVE_FLEXFIELD_NAME=description_flexfield_name

desc_flex_application - is the shortname of the Application of the DFF.
description_flexfield_name - This is not title of the DFF.

To get description_flexfield_name:
Open the DFF in Application DeveloperFlexfieldDescriptiveSegments

click help->diagnostics->examine->
block=table, field=DESCRIPTIVE_FLEXFIELD_NAME.
The value in the field “Value” is the description_flexfield_name.

For Example, The description_flexfield_name of the DFF “Additional Information” in the above screenshot is: CS_INCIDENTS_ALL_B_EXT.
Application is: Service.
Short name of Service is: CS
And the download command is:

FNDLOAD apps/ 0 Y DOWNLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt CS DESCRIPTIVE_FLEXFIELD_NAME=CS_INCIDENTS_ALL_B_EXT

/*Upload description flexfield*/
Transfer the ldt file descript_flex.ldt from Instance1 to the home directory of the Instance2 and run the below upload command in the same directory.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct descript_flex.ldt

/*Compiling description flexfield*/
After migration, run the below compilation command to compile the flexfield in the Instance2.

fdfcmp apps/ 0 Y D desc_flex_application description_flexfield_name