Oracle External Table

Oracle External Tables provide great deal to upload data into Oracle table from a file without executing the Sql Loader.

What is External Table – Definition of External table from Oracle Database manual An external table load creates an external table for data in a data file and executes INSERT statements to insert the data from the data file into the target table.

There are two advantages of using external table loads over conventional path and direct path loads:

An external table load attempts to load data files in parallel. If a data file is big enough, it will attempt to load that file in parallel.

An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

Disadvantages of External Table.

Till R11g there is no option to execute DML against an external table. External tables supports SELECT only.

Steps for External table.

1. Create database directory and map it with your local PC directory. You need to have Admin Privileged to create Database directory.

2.Grant read/write access of database directory to user.

3.Create External table with DEFAULT DIRECTORY as Database directory.

Step #1

CREATE OR REPLACE DIRECTORY load_dir AS 'C:\myexternaltable';

GRANT CREATE ANY DIRECTORY to user_name

Grant DROP ANY DIRECTORY to user_name

Step #2

GRANT READ ON DIRECTORY load_dir TO user_name;

GRANT WRITE ON DIRECTORY load_dir TO user_name;

CREATE TABLE BG_STATEMENT_LINES_EXT

(

BG0 VARCHAR2(150),

BG1 VARCHAR(150),

BG2 VARCHAR2(150),

BG3 VARCHAR2(150),

BG4 VARCHAR2(150),

BG5 VARCHAR2(150))

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY LOAD_DIR

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

badfile load_dir:'bst_lns.bad'

logfile load_dir:'bst_lns.log'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(

BG0,

BG1,

BG2,

BG3,

BG4,

BG5

))

LOCATION

(

LOAD_DIR: 'file_name.csv')

)

REJECT LIMIT UNLIMITED

;

In my example I have mapped my PC's directory 'C:\myexternaltable' to database directory load_dir. While defining the external table, I have added the clause “DEFAULT DIRECTORY LOAD_DIR” to map External Table with database directory.

Data Processing

Create and Grant Database directory and External Table as explain above.

Get your data file in the same format as of External table.

Save your data file into your PC's directory that has mapped to database directory , make sure filename is same as defined “LOAD_DIR: 'file_name.csv'”

On Save, data from file will Import to External table.

via Oracle Technologies.: Oracle External Table.

Oracle Order to Cash Queries

Query to Join OM and requisition Interface table for Back 2 back Order

select l.line_id, l.flow_status_code , l.open_flag,pr.interface_source_code,pr.interface_source_line_id,pr.note_to_buyer,

pr.note_to_receiver

from

oe_order_lines_all l,

po_requisitions_interface_all pr

where l.line_id = pr.interface_source_line_id

and pr.interface_source_code='CTO'

Query to Join OM and Purchase Order tables for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id

from

mtl_reservations a,

oe_order_lines_all l,

po_headers_all ph

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = ph.po_header_id

Query to Join OM and PO Requisition table for Back 2 Back Order

select ph.segment1,a. supply_source_line_id, a.supply_source_header_id

from

mtl_reservations a,

oe_order_lines_all l,

po_requisition_headers_all pqh

where demand_source_line_id = &Enter_Order_lineID

and l.line_id = a.demand_source_line_id

and a.supply_source_header_id = pqh.requisition_header_id

Query to Join OM , WSH and AR table

SELECT h.order_number,l.line_id,l.ordered_quantity,l.shipped_quantity,l.invoiced_quantity,

wdd.delivery_detail_id,wnd.delivery_id,wdd.shipped_quantity,a.org_id,

a.creation_date ,a.trx_number,b.quantity_ordered , b.quantity_invoiced ,b.interface_line_attribute1,b.interface_line_attribute3,

b.interface_line_attribute6,interface_line_attribute12

from

ra_customer_trx_all a,

ra_customer_trx_lines_all b,

oe_order_headers_all h,

oe_order_lines_all l,

wsh_delivery_details wdd,

wsh_delivery_assignments wda,

wsh_new_deliveries wnd

where a.customer_trx_id = b.customer_trx_id

and a.interface_header_context = 'ORDER ENTRY'

and b.interface_line_attribute1 = to_char(h.order_number)

and h.header_id = l.header_id

and to_char(l.line_id) = b.interface_line_attribute6

and l.line_id = wdd.source_line_id

and wdd.delivery_detail_id = wda.delivery_detail_id

and wda.delivery_id = wnd.delivery_id

and to_char(wnd.delivery_id) = b.interface_line_attribute3

Mapping Between AR and OM (Transaction Flex field)

(RAL) – RA_CUSTOMER_TRX_LINES_ALL

RAL.INTERFACE_LINE_ATTRIBUTE1 Order_Num

RAL.INTERFACE_LINE_ATTRIBUTE2 Order_Type

RAL.INTERFACE_LINE_ATTRIBUTE3 Delivery ID

RAL.INTERFACE_LINE_ATTRIBUTE4 WayBill

RAL.INTERFACE_LINE_ATTRIBUTE6 Line_ID

RAL.INTERFACE_LINE_ATTRIBUTE8 Bill_Lading

RAL.INTERFACE_LINE_ATTRIBUTE10 WH_ID RAL.INTERFACE_LINE_ATTRIBUTE11 PA_ID

via Oracle Technologies.: Oracle Order to Cash Queries.

Oracle Application – AIM- Application implementation methodology

Oracle’s AIM Methodology is available for download via the link below. The methodology includes templates for all phases of the system development lifecycle. This package is quite a valuable resource for Project Managers.

 

Use below link to download ORacle AIM -

http://download-east.oracle.com/partners/265498.EXE

Oracle 10g : Top 10 Oracle PL/SQL Performance Tips

1. Avoid NOT EQUAL Operators ‘<>’ and ‘!=”

When these operators are used, the indexes on columns referenced cannot be used. If you need to use the ‘<>’ operator, you can alternatively use the OR clause to distinguish the <> conditions.

2. Avoid ‘IS NULL’ and ‘IS NOT NULL’

The value NULL is undefined, therefore an index on the referenced column will not be used. Creating defaults for NULL values as part of table creation can help with this issue. (i.e.. column VARCHAR2(1) NOT NULL DEFAULT ‘N”)

3. Avoid FUNCTIONS in SQL Where Clause

The optimizer will not use an index when a function is used on an indexed column.

4. Comparing datatypes that are not similar

The Oracle database converts a VARCHAR2 column to a numeric when comparing a string to a number. When this happens, the referenced index is not used. (i.e account_number = 100100).

5. Use the EXISTS function when possible

Utilize the EXISTS function rather than the IN function. The EXIST function looks for a single row match. The IN function has to return all qualifying rows.

6. Use BIND variables

With the use of bind variables, SQL statements can be reused in memory rather than having to be re-parsed with each call.

7. Where are you referencing form fields?

Avoid referencing form fields within the body of a trigger or program unit. Developer 2000 Forms has a PL/SQL engine independent of the database. Occasionally, the engine has to pass SQL off to the database for parsing. To minimize the communication between the client and server, pass form fields via an argument list.

8. Utilize the FORALL statement rather than a FOR LOOP.

When inserting/updating/deleting a large number of rows, you can do the update collectively, rather then one record at a time. This is quicker because only one context switch has to occur between PL/SQL and SQL.

9. Utilize the BULK COLLECT statement

BULK COLLECT works similar to a FORALL statement. It will fetch all rows from the database rather than one row at a time.

10. Using NOCOPY

The PL/SQL engine will pass the parameter by reference rather than by value. This means the values do not have to be copied back and forth. The greatest advantage is seen when passing large records or collections. Can only be used with OUT or IN OUT parameters.

References:

Richard J. Niemiec, Oracle Performance Tuning, Osborne/McGraw-Hill Book Co., 1999

Scott Urman, Oracle8i Advanced PL/SQL Programming, Osborne/McGraw-Hill Book Co., 2000

How to Compile Invalid Objects on APPS Schema For Packages & API

How to Compile Invalid Objects on APPS Schema For Packages and APIs-

 

This is an example for OZF – Trade Management Schema

declare
cursor invalid_objects is
select object_name, object_type
from user_objects
where object_type in ('PACKAGE', 'PACKAGE BODY')
and status = 'INVALID'
and object_name like 'OZF%';
begin
for rec in invalid_objects
loop
DBMS_DDL.ALTER_COMPILE (rec.object_type ,'OZF' ,rec.object_name);
end loop;
end;

How To Determine If An Invoice Was Created For A Self Service Expense Report?

How To Determine If An Invoice Was Created For A Self Service Expense  Report?

Just a Quick Tip

The Expense Report Import process should update ap_expense_report_headers_all.vouchno with the invoice_id after an expense report is successfuly imported into AP.

AP Invoices Associated with the Given Purchase Order Number

PO: Invoices Associated with the Given Purchase Order Number:-

——————————————————————————————————————

SELECT aip.invoice_id invoice_id,

b.po_number po_number

FROM ap_invoices_all aip,

(SELECT invoice_id,po_number FROM ( SELECT ai.invoice_id,

AP_INVOICES_PKG.GET_PO_NUMBER( ai.invoice_id) po_number

FROM AP_INVOICES_ALL AI) A

WHERE a.po_number <>&apos;UNMATCHED&apos;) b

where b.invoice_id=aip.invoice_id

and b.po_number= ‘Purchase Order Number’;

via Oracle Apps Training.

Display the number value in Words

Display the number value in Words:-

——————————————

The following query can be used to display the number in the words.

select ‘&a’, (to_char(to_date(‘&a’,’j’), ‘jsp’)) from dual;

Example:-

select 211, (to_char(to_date(211,’j'), ‘jsp’)) from dual;

via Oracle Apps Training.

Display the Database Version and Server Operating System Name

Display the Database Version and Server Operating System Name

—————————————————————————–

The following query gives the version of the Oracle Database.

SELECT banner FROM v$version;

Example:-

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – Prod

PL/SQL Release 10.2.0.2.0 – Production

CORE 10.2.0.2.0 Production

TNS for Linux: Version 10.2.0.2.0 – Production

NLSRTL Version 10.2.0.2.0 – Production

From above information, we can say this is 10g Database and Server Operating system is Linux.

The following query can be used to get the version of the Server Operating system.

BEGIN

dbms_output.put_line(&apos;Port String: &apos;||dbms_utility.port_string);

END;

Example:-

Port String: Linuxi386/Linux-2.0.34-8.1.0

From the above information, we can say this is Linux Operating system and version is 2.0.34-8.1.0

Note:- If you are login into the server with some ID then you can also know the name and version of the Server Operating system directly by typing

> uname

via Oracle Apps Training: Display the Database Version and Server Operating System Name.

Oracle Apps Training: Initialize Apps

Initialize Apps

—————-

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the

Application, User-name and Responsibility name given.

Example:-

DECLARE

l_appl_id NUMBER;

l_appl_name VARCHAR2 (100) := &apos;PA&apos;;

l_user_id NUMBER;

l_user_name VARCHAR2 (100) := &apos;OPERATIONS&apos;;

l_responsibility_id NUMBER;

l_resp_name VARCHAR2 (200)

:= &apos;Projects, Vision Operations (USA)&apos;;

BEGIN

– To get the Application ID of given Application.

SELECT application_id

INTO l_appl_id

FROM fnd_application

WHERE application_short_name = l_appl_name;

– To get the User ID information of given user

SELECT user_id

INTO l_user_id

FROM fnd_user

WHERE user_name = l_user_name;

– To get the Resp ID information of the given responsibility.

SELECT responsibility_id

INTO l_responsibility_id

FROM fnd_responsibility_tl

WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

–Initialixze the Application to use the API.

fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);

END;

I Hope the above script would help understanding about the Initializing the Apps API.

via Oracle Apps Training: Initialize Apps.

E-Business Suite 12.1.2 Release Content Documents Now Available (Oracle E-Business Suite Technology)

At OpenWorld a few weeks ago, I got the strong impression that the majority of you have heard that Premier Support for Oracle E-Business Suite Release 11i will end in November 2010. If the standing-room only R12 upgrade sessions were any indication, it seems that many of you are now planning your Oracle E-Business Suite Release 12 upgrades or migrations.

Oracle E-Business Suite Release 12.1.1 was released in early May 2009. Our next upcoming release will be EBS 12.1.2. The Release Content Documents (RCD) for Apps 12.1.2 have now been published — in advance of the actual General Availability of R12.1.2 — here:

via E-Business Suite 12.1.2 Release Content Documents Now Available (Oracle E-Business Suite Technology).

Forms Personalization – Get It While It’s Hot! (Oracle E-Business Suite Technology)

Well, it will be – and we&apos;re all excited by the move to the Fusion Applications platform, and its early form, Oracle Applications Framework.

But we still live in a world with Oracle Forms, and we will continue to do so for Release 12. Importantly, you can stay on these releases as long as they provide you business value, so you can move to Fusion Applications on your timeframe, not ours. (Close your eyes and say &apos;Applications Unlimited&apos; . Feels good, doesn&apos;t it?)

So it&apos;s important for us to let you change your business practices at the lowest cost of ownership on the Forms stack, and Forms Personalization lets you do that. It&apos;s a feature that&apos;s been out just under 2 years, but this is a great venue to highlight it to make sure you&apos;re taking maximum advantage of it.

What now?

Forms Personalization takes the Custom Library (CUSTOM.PLL) concepts and makes them much easier to implement. Like much easier. Like going from your VCR to a Tivo easier. CUSTOM.PLL coding is all hand-tooled PL/SQL which is controlled in a single source file, by default. It allows implementers to trap various Forms triggers and take actions based on them.

All Forms Personalizations, on the other hand, are stored as metadata, and so they are all seamlessly upgraded without needing to be re-applied or addressed – just like Flexfields or Folder definitions! Forms Personalization provides tools to perform the same configurations, in the form of a Form. (Say that fast, huh?)

via Forms Personalization – Get It While It’s Hot! (Oracle E-Business Suite Technology).