BOGE PRASANTH REDDY ORACLE APPLICATIONS BLOG: 04/20/11

Wednesday, 20 April 2011

R12 - How to link GL data to the subledger data or vice versa

Applications

gl_je_lines (je_header_id, je_line_num)                -> gl_import_references (je_header_id, je_line_num)

gl_import_references (gl_sl_link_table, gl_sl_link_id) -> xla_ae_lines (gl_sl_link_table, gl_sl_link_id)

xla_ae_lines (applicaiton_id, ae_header_id)            -> xla_ae_headers (application_id, ae_header_id)

xla_ae_headers (application_id, event_id)              -> xla_events (application_id, event_id)

xla_events (application_id, entity_id)                 -> xla.xla_transaction_entities (application_id, entity_id)
xla.xla_transaction_entities (source_id_int_1, etc) after filtering by application_id, entity_code and ledger_id     -> subledger's table(its key columns mentioned in xla_entity_id_mappings) for that ledger_id

For Ex:
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_INVOICES and ledger_id -> ap_invoices_all (invoice_id) for that set_of_books_id.
xla.xla_transaction_entities (source_id_int_1) filtered by application_id 200, entity_code AP_PAYMENTS and ledger_id -> ap_checks_all (check_id) for that set_of_books_id.

xla.xla_transaction_entities (source_id_int_1) filtered by application_id 222, entity_code TRANSACTIONS and ledger_id -> ra_customer_trx_all (customer_trx_id) for that set_of_books_id.

DATE FUNCTIONS IN SQL:

DATE FUNCTIONS IN SQL:

Date

Current Date

CURRENT_DATE
SYSDATE

SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

Formats

Day	Month	Year	Fill Mode	Julian Date
D	MM	YY	FM	J
DD	MON	YYYY
DDTH		RR
DAY		RRRR

+ AND -

SELECT SYSDATE + 1 FROM dual;

SELECT SYSDATE - 1 FROM dual;

ADD_MONTHS

Add A Month To A Date

ADD_MONTHS(<date>, <number of months_integer>

SELECT add_months(SYSDATE, 2) FROM dual;

-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('28-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('29-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('30-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('31-JAN-2007'), 1) FROM dual;

SELECT add_months(TO_DATE('01-FEB-2007'), 1) FROM dual;

CURRENT_DATE

Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE

col sessiontimezone format a30

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

DUMP

Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value

DUMP(<value> [,<return_format>[,<start_position>[,<length>]]])

8	Octal
10	Decimal
16	Hexidecimal
17	Single Characters
1008	octal notation with the character set name
1010	decimal notation with the character set name
1016	hexadecimal notation with the character set name
1017	single characters with the character set name

col drows format a40

SELECT DUMP(SYSDATE) DROWS FROM dual;

SELECT DUMP(SYSDATE, 8) DROWS FROM dual;

SELECT DUMP(SYSDATE, 16) DROWS FROM dual;

GREATEST

Return the Latest Date

GREATEST(<date>, <date>, <date>, ...)

CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;

INTERVAL

Interval to adjust date-time

INTERVAL '<integer>' <unit>

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE - INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

LAST_DAY

Returns The Last Date Of A Month

LAST_DAY(<date>)

SELECT * FROM t;

SELECT LAST_DAY(datecol1) FROM t;

LEAST

Return the Earliest Date

LEAST(<date>, <date>, <date>, ...)

SELECT * FROM t;

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;

LENGTH

Returns length in characters

LENGTH(<date>)

SELECT LENGTH(last_ddl_time) FROM user_objects;

LENGTHB

Returns length in bytes

LENGTHB(<date>)

SELECT LENGTHB(last_ddl_time) FROM user_objects;

Note: Additional forms of LENGTH (LENGTHC, LENGTH2, and LENGTH4) are also available.

MAX

Return the Latest Date

MAX(<date>)

SELECT * FROM t;

SELECT MAX(datecol1) FROM t;

MIN

Return the Earliest Date

MIN(<date>)

SELECT * FROM t;

SELECT MIN(datecol1) FROM t;

MONTHS_BETWEEN

Returns The Months Separating Two Dates

MONTHS_BETWEEN(<latest_date>, <earliest_date>)

SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual;

NEW_TIME

Returns the date and time in time zone zone2 when date and time in time zone zone1 are date

Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.

SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

NEXT_DAY

Date of next specified date following a date

NEXT_DAY(<date>, <day of the week>)

Options are SUN, MON, TUE, WED, THU, FRI, and SAT

SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;

ROUND

Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day

ROUND(<date_value>, <format>)

SELECT ROUND(TO_DATE('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual;

Spelled Out Using TO_CHAR

Spelled Demo

DDSP

HH24SP

MISP

MMSP

SSSP

SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2008', 'DD-MM-YYYY'), 'DDSP-MMSP-YYYYSP')
FROM dual;

SELECT TO_CHAR(TO_DATE(sal,'J'), 'JSP')
FROM emp;

SYSDATE

Returns the current date and time set for the operating system on which the database resides

SYSDATE

SELECT SYSDATE FROM dual;

TO_DATE

In Oracle/PLSQL, the to_date function converts a string to a date.

TO_DATE(<string1>, [ format_mask ], [ nls_language ])

string1 is the string that will be converted to a date.The format_mask parameter is optional. It is the format that will be used to convert string1 to a date.
nls_language is optional. The nls_language parameter sets the default language of the database. This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism. This parameter also determines the default values of the parameters NLS_DATE_LANGUAGE and NLS_SORT.
The following table shows options for the format_mask parameter. These parameters can be used in various combinations.

Parameter Explanation

YEAR Year, spelled out alphabetically

YYYY 4-digit year

YYY
YY
Y Last 3, 2, or 1 digit(s) of year.

IYY
IY
I Last 3, 2, or 1 digit(s) of ISO year.

IYYY 4-digit year based on the ISO standard

RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.

Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM Month (01-12; JAN = 01).

MON Abbreviated name of the month.

MONTH The name of month, padded with blanks to length of 9 characters.

RM Roman numeral month (I-XII; JAN = I).

WW The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW The week of year (1-52 or 1-53) based on the ISO standard.

D Day of the week (1-7). Sunday is day 1 when nls_territory is set to 'AMERICA' but differs if another nls_territory is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1.

DAY Name of the day.

DD The day of month (1-31).

DDD The day of year (1-366).

DY Abbreviated name of the day. (Mon, Tue, Wed, etc)

J Julian day; the number of days since January 1, 4712 BC.

HH Hour of day (1-12).

HH12 Hour of day (1-12).

HH24 Hour of day (0-23).

MI Minute (0-59).

SS Second (0-59).

SSSSS Number of seconds past midnight (0-86399).

FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF5'.

AM, A.M., PM, or P.M. Meridian indicator

AD or A.D AD indicator

BC or B.C. BC indicator

TZD Daylight savings identifier. For example, 'PST'

TZH Time zone hour.

TZM Time zone minute.

TZR Time zone region.

TRUNC

Convert a date to the date at midnight

TRUNC(<date_time>)

CREATE TABLE t (
datecol DATE);

INSERT INTO t (datecol) VALUES (SYSDATE);

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'HH'));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'MI'));

COMMIT;

SELECT TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS')
FROM t;

Selectively remove part of the date information

Special thanks to Dave Hayes for reminding me of this.

TRUNC(<date_time>, '<format>')

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the month
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MON'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MONTH'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the year
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'YEAR'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

Dates in WHERE Clause Joins

SELECT SYSDATE FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE FROM dual;

/

/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

CREATE TABLE t (
datecol DATE);

INSERT INTO t
(datecol)
VALUES
(SYSDATE);

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT * FROM t
WHERE datecol = SYSDATE;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT TRUNC(SYSDATE) FROM dual;

SELECT * FROM t
WHERE TRUNC(datecol) = TRUNC(SYSDATE);

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

VSIZE

Returns The Number Of Bytes Required By A Value

VSIZE(e IN DATE) RETURN NUMBER

SELECT VSIZE(SYSDATE) FROM dual;

Date Calculations

Returns A Day A Specified Number Of Days In The Future Skipping Weekends

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum   POSITIVE;
SkipCntr NATURAL := 0;
BEGIN
WHILE Counter < Days2Add LOOP
    CurDate := CurDate+1;
    DayNum := TO_CHAR(CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
END LOOP;
RETURN start_date + Counter + SkipCntr;
END business_date;
/

Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter.

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
days2add NUMBER) RETURN DATE IS
Counter NATURAL := 0;
CurDate DATE := start_date;
DayNum POSITIVE;
SkipCntr NATURAL := 0;
Direction INTEGER := 1;  -- days after start_date
BusinessDays NUMBER := Days2Add;
BEGIN
IF Days2Add < 0 THEN
    Direction := - 1; -- days before start_date
    BusinessDays := (-1) * BusinessDays;
END IF;

WHILE Counter < BusinessDays LOOP
    CurDate := CurDate + Direction;
    DayNum := TO_CHAR( CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
END LOOP;

RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
/

Returns The First Day Of A Month

CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS
vMo VARCHAR2(2);
vYr VARCHAR2(4);
BEGIN
vMo := TO_CHAR(value_in, 'MM');
vYr := TO_CHAR(value_in, 'YYYY');
RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
WHEN OTHERS THEN
RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/

Time Calculations

Returns The Number Of Seconds Between Two Date-Time Values

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1   NUMBER;
NDATE_2   NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
  -- Get Julian date number from first date (DATE_1)
NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)
NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)
NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)
NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/

Calculating time from seconds

Posted by John K. Hinsdale
12/30/06 to c.d.o.misc

SELECT DECODE(FLOOR(999999/86400), 0, '',
FLOOR(999999/86400) || ' day(s), ') ||
TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;

Obtain counts per ten minute increment

Posted by Michele Cadot
03/09/08 to c.d.o.misc

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT TRUNC(SYSDATE) + dbms_random.value(0,86400)/86400
FROM dual
CONNECT BY LEVEL <= 10;

WITH data AS (
SELECT TRUNC(SYSDATE)+dbms_random.value(0,86400)/86400 h
FROM dual
CONNECT BY LEVEL <= 10)
SELECT TO_CHAR(h,'DD/MM/YYYY HH24:MI:SS') h, TO_CHAR(TRUNC(h)
+ TRUNC(TO_CHAR(h,'SSSSS')/600)/144, 'DD/MM/YYYY HH24:MI:SS') "10m"
FROM data
ORDER BY h;

Database Structures FAQ's

1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two are more redo log files, and one or more control files.

2. What are the components of logical database structure of Oracle database?
There are tablespaces and database's schema objects.

3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

4. What is SYSTEM tablespace and when is it created?
Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

5. Explain the relationship among database, tablespace and data file.
Each databases logically divided into one or more tablespaces one or more data files are explicitly created for each tablespace.

6. What is schema?
A schema is collection of database objects of a user.

7. What are Schema Objects?
Schema objects are the logical structures that directly refer to the database's data. Schema objects include tables, views, sequences, synonyms, indexes, clusters, database triggers, procedures, functions packages and database links.

8. Can objects of the same schema reside in different table spaces?
Yes.

9. Can a tablespace hold objects from different schemes?Yes.

10. What is Oracle table?
A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

11. What is an Oracle view?
A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

12. Do a view contain data?
Views do not contain or store data.

13. Can a view based on another view?
Yes.

14. What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.

15. What is an Oracle sequence?
A sequence generates a serial list of unique numbers for numerical columns of a database's tables.

16. What is a synonym?
A synonym is an alias for a table, view, sequence or program unit.

17. What are the types of synonyms?
There are two types of synonyms private and public.

18. What is a private synonym?Only its owner can access a private synonym.

19. What is a public synonym?
Any database user can access a public synonym.

20. What are synonyms used for?
- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

21. What is an Oracle index?
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

22. How are the index updates?Indexes are automatically maintained and used by Oracle. Changes to table data are automatically incorporated into all relevant indexes.

23. What are clusters?Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

24. What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

25. What is index cluster?
A cluster with an index on the cluster key.

26. What is hash cluster?A row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stores together on disk.

27. When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

28. What is database link?A database link is a named object that describes a "path" from one database to another.

29. What are the types of database links?
Private database link, public database link & network database link.

30. What is private database link?
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.

31. What is public database link?
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.

32. What is network database link?Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

33. What is data block?
Oracle database's data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk.

34. How to define data block size?
A data block size is specified for each Oracle database when the database is created. A database users and allocated free database space in Oracle data blocks. Block size is specified in init.ora file and cannot be changed latter.

35. What is row chaining?In circumstances, all of the data for a row in a table may not be able to fit in the same data block. When this occurs, the data for the row is stored in a chain of data block (one or more) reserved for that segment.

36. What is an extent?An extent is a specific number of contiguous data blocks, obtained in a single allocation and used to store a specific type of information.

37. What is a segment?
A segment is a set of extents allocated for a certain logical structure.

38. What are the different types of segments?
Data segment, index segment, rollback segment and temporary segment.

39. What is a data segment?
Each non-clustered table has a data segment. All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's data segment.

40. What is an index segment?
Each index has an index segment that stores all of its data.

41. What is rollback segment?
A database contains one or more rollback segments to temporarily store "undo" information.

42. What are the uses of rollback segment?
To generate read-consistent database information during database recovery and to rollback uncommitted transactions by the users.

43. What is a temporary segment?
Temporary segments are created by Oracle when a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segment extents are released to the system for future use.

44. What is a datafile?
Every Oracle database has one or more physical data files. A database's data files contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

45. What are the characteristics of data files?
A data file can be associated with only one database. Once created a data file can't change size. One or more data files form a logical unit of database storage called a tablespace.

46. What is a redo log?
The set of redo log files for a database is collectively known as the database redo log.

47. What is the function of redo log?The primary function of the redo log is to record all changes made to data.

48. What is the use of redo log information?The information in a redo log file is used only to recover the database from a system or media failure prevents database data from being written to a database's data files.

49. What does a control file contains?
- Database name
- Names and locations of a database's files and redolog files.
- Time stamp of database creation.

50. What is the use of control file?
When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

Few Questions to have a look

What is the interface?Interface Table is a table which is used as medium for transfer of data between two systems.What is INBOUND and OUT BOUND? (Different types of interfaces)
Inbound Interface:For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.Outbound Interface:For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programsWhat is multi org?Legal entity has more than one operating unit is called as multi org”a) Business group --- Human resources information is secured byBusiness groupb) Legal entity. --- inter-company and fiscal/tax reporting.Security responsibility operating unit.c) Operating unit --- secures AR, OE, AP, PA and PO Information.d) Organizations --- is a specialize unit of work at particular locationsWhat are the User PARAMETERS in the Reports?P_CONC_REQUEST_IDP_FLEX_VALUEFND USER EXITS:-FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application ObjectLibrary user exits to detect that they have been called by an Oracle Reports program.FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.FND FLEXIDVAL are used to display flex field information like prompt, value etcFND FLEXSQL these user exits allow you to use flex fields in your reportsFND FORMAT_CURRENCY is used to print currency in various formats by using formula columnWhat are the requests groups?a) Single request: - this allows you to submit an individual request.b) Request set : - this allows you to submit a pre-defined set of requests.Difference between Bind and Lexical parameters?BIND VARIABLE :are used to replace a single value in sql, pl/sql bind variable may be used to replace expressions in select, where, group, order by, having, connect by, start with cause of queries.bind reference may not be referenced in FROM clause (or) in place of reserved words or clauses.LEXICAL REFERENCE:you can use lexical reference to replace the clauses appearing AFTER select,from, group by, having, connect by, start with. you can’t make lexical reference in a pl/sql ements.update clause:1) use explicit locking to deny access for the duration of a transaction2) lock the rows before update or deleteEx : select …….From…….For update[ of column ref] [no_wait]where current of clause?1) use cursor to update or delete the current rowWhere current of <>What is the package?Group logically related pl/sql types, items and subprograms.1. package specification2. package bodyAdvantages of a package:A. Modularity
B. Easier Application Design
C.Information Hiding
D,OverloadingYou cannot overload:•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and theirtotal number is same).•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in thesame family (number and decimal belong to the same family)•Two subprograms if their formal parameters differ only in subtype and the different subtypes are basedon types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)•Two functions that differ only in return type, even if the types are in different families.What are triggers?triggers are similar to procedures, in that they are the named pl/sql blocks with declarative,executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.The triggering event is a DML (insert, update, delete) operations on a data base tablefires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or databaseTrigger timing :1) before
2) after
3) instead of ( this is used for views)events :1) insert
2)update
3) deleteTrigger type :1) statement level
2) row level.Firing sequence of database triggers1. before statement trigger
2. before row trigger
3. after row trigger
4. after statement trigger
LOCKS?Is to reduce concurrency
1) share lock
it allows the other users for only reading not to insert
2) exclusive lock
only one user can have the privileges of insert orothers can only read.
3) update lock
multiple user can read, update delete Lock levels :1) table level
2) table space
3) data base level.
What are ad-hoc reports?
Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query

Important Tables and colums in GL,AP,AR,PO and HRMS

Gl_CODE_CONMBINATIONS,-CODE_COMBINATION_ID.
-CART_OF _ACCOUNTS_ID.
-DETAIL_POSTING_ALLOWED_FLAG.
-ACCOUNT_TYPE.
-TEMPLATE_ID.
SEGMENT1,SEGMENT2,GL_BALANCES
-SET_OF_BOOKS_ID,
-CURRENCY_CODE,
-CODE_COMBINATION_ID,
-ACTUAL_FLAG,
-PERIOD_NET_DR
-PERIOD_NET_CRGL_LOOKUPS-LOOKUP_TYPE,
-LOOKUP_CODE,
-MEANING,
-DESCRIPTION,
-ENABLED FLAG,
-START_DATE_ACTIVE,
-END_DATE_ACTIVE,
-description ACCT_TYPE,
GL_SETS_OF_BOOKS
-SET_OF_BOOKS_ID,
-CURRENCY_CODE,
-CHART_OF_ACCOUNTS_ID,
-NAME,
-PERIOD_SET_NAME,
-DESCRIPTION,
GL_PERIOD_SETS
-PERIOD_SET_NAME,
-DESCRIPTION,
-ATTRIBUTES1
-PERIOD_SET_ID.AP_PAYMENT_SCHEDULES-INVOICE_ID,
-PAYMENT_CROSS_RATE,
-PAYMENT_NUM,
-AMOUNT_REMAINING,
-DUE_DATE,
-FUTURE_PAY_DUE_DATE,
-GROSS_AMOUNT,
-PAYMENT_METHOD,
-PAYMENT_PRIORITY,
-PAYMENT_STATUS
AP_INVOICE_ALL
-INVOICE_ID,
-VENDOR_ID,
-INVOICE_NUM,
-SET_OF_BOOKS_ID,
-INVOICE_CURRENCY_CODE,
-PAYMENT_CURRENCY_CODE,
-INVOICE_AMOUNT
-VEODOR_SITE_ID,
-AMOUNT_PAID.
PO_VENDORS
-VENDOR_ID,
-VENDOR_NAME,
-SEGMENT1,
-SUMMARY_FLAG,
-ENABLED_FLAG,
-EMPLOYEE_ID,
-VENDOR_TYPE_LOOKUP_CODE,
-SHIP_LOCATION_ID.
PO_VENDOR_SITES
-ADDRESS_STYLE,
-LANGUAGE,
-PROVINCE,
-COUNTRY,
-AREA_CODE,
-PHONE,
-SHIP_TO_LOCATION,
-BILL_TO_LOCATION,
-PAYMENT_METHOD,
-BANK_ACCT_METHOD,
-BANK_ACCT_NAME,
-BANK_ACCT_NUMBERAP_LOOKUP_CODES-LOOKUP_TYPE,
-LOOKUP_CODE,
-DISABLED_FIELD,
-DESCRIPTION
-ENABLED_FLAG,
AP_DOC_SEQUENCE_AUDIT
-DOC_SEQUENCE_ID,
-DOC_SEQUENCE_ASSIGNMENT_ID,
-DOC_SEQUENCE_VALUE,
-CAREATION_DATE,
-CREATED_BY
FND_DOC_SEQUENCE_ASSIGNMENTS
-DOC_SEQUENCE_ASSIGNMENT_ID,
-CAREATED_BY,
-APPLICATION_ID,
-DOC_SEQUENCE_ID,
-CATEGORY_CODE,
-SET_OF_BOOKS_ID
-METHOD_CODE,FND_COLUMNS-APPLICATION_ID,
-TABLE_ID,
-COLUMN_ID,
-COLUMN_NAME,
-USER_COLUMN_NAME,
-COLUMN_SEQUENCE,
-WIDTH,
-NULL_ALLOWED_FLAG,
-TRANSFLATE_FLAG,
FLEXFILED_USAGE_CODE,
-DESCRIPTION.
RA_CUSTOMERS
-ROW_ID
-CUSTOMER_ID,
-PARTY_ID,
-PARTY_NUMBER,
PARTY_TYPE,
CUSTOMER_NAME,
-CUSTOMER_NUMBER
-ORIG_SYSTEM_NUMBER.
-STATUS,
-CUSTOMER_TYPE,
RA_ADRESS_ALL
-ROW_ID,
-PARTY_SITE_ID,
-PARTY_ID
-PARTY_LOCATION_ID,
KEY_ACCOUNT_FLAG,
-PROGRAM_UPDATE_DATE,
-TERRITORY _ID,
-ADDRESS_KEY,
RA_SITE_USES_ALL
-SITE_USE_ID,
-SITE_USE_CODE,
-ADDRESS_ID,
PRIMARY_KEY,
-STATUS,
-LOCATION,
-CONTACT_ID,
-BILL_TO_SITE_USE_ID,
-ORIG_SYSTEM_REFERENCE,
-WAREHOUSE_ID,
-ORDER_TYPE_ID,AR_PAYMENT_SCHEDULES-PAYMENT_SCHEDULE_ID,
-DUE_DATE,
-AMOUNT_DUE_ORIGINAL,
AMOUNT_REMAINING,
-NUMBER_OF_DUE_DATES,
-STATUS,
-INVOICE_CURRENCY_CODE,
CUST_TRX_TYPE_ID,
-CUSTOMER_ID,
-CUSTOMER_TRX_ID
AR_CASH _RECEIPTS_V
-ROW_ID,
-CASH_RECEIPT_ID,
-AMOUNT,
-FUNCTIONAL_AMOUNT,
-NET_AMOUNT,
-CURRENCY_CODE,
-RECEIPT_NUMBER,
-RECEIPT_DATE,
-TYPE.
RA_CUSTOMER_TRX_PARTIAL_V
-ROW_ID,
-CUSTOMER_TRX_ID,
-TRX_NUMBER,
-OLD_TRX_NUMBER,
-TRX_DATE,
-TERM_DUE_DATE,
-INITIAL_CUSTOMER_TRX_ID,
-BATCH_ID,
-TERM_ID
RA_CUSTOMER_TRX_LINES_V
-ROW_ID,
-CUSTOMER_TRX_LINE_ID,
-CUSTOMER_TRX_ID,
-DESCRIPTION.
RA_PAYMENTS_SCHEDULES_ALL
-PAYMENT_SCHEDULE_ID,
-DUE_DATE,
-AMOUNT_DUE_ORIGINAL,
-AMOUNT_DUE_REMAINING,
-NUMBEr_OF_DUE_DATES,
-STATUS,
-INVOICE_CURRENCY_CODE,
-CLASS,
-TERMD_ID.
RA_CUSTOMER_TRX_ALL
-CUSTOMER_TRX_ID,
-TRX_NUMBER,
-CUSTOMER_TRX_TYPE_ID,
-TRX_DATE,
-SET_OF_BOOKS_ID,
-BILL_TO_CONTACT_ID,
-BATCH_ID,
-SHIP_TO_CUSTOMER_ID,
-SHIP_TO_SITE_USE_ID.
AR_CUSTOMER_PROFILES
-CUSTOMER_PROFILE_ID,
-CUSTOMER_ID,
-STATUS,
COLLECTOR_ID,
-CREDIT_CHECKING
-TOLERANCE,
-CUSTOMER_PROFILE_CLASS_ID,
-SITE_USE_ID,
-CREDIT_RATING
HRMS TABLES BY SCREEN WISE.
================================PEOPLE(SCREEN)
-ENTER AND MAINTAINPER_PEOPLE_V
PER_ADDRESS_V --------ADDRESSES OF EMPLOYEES
PER_IMAGES -------STORES EMPLOYEE PHOTOS
PER_ASSIGNMENTS_V ------STORES_ASSIGNMENTS
PER_PAY_PROPOSALS_V2 –STOES SALARIES
PAY_PAYWISE_ELEMENT_ENTRIES---ENTRIES
PER_SPEICAL_INFO_TYPES_VS ---SPECIAL INFORMWORKSTRUCTURES
LOCATIONS
-HR_LOCATIONS_VORGANIZATION-HR_ORGANIZATION_UNITS_V
HIERARCHY
-PER_ORGANIZATION_STRUCTURES_VJOBS-PER_JOBS_VL
CAREER PATHS
-PER_CAREER_PATHSPAYROLLS
DESCRIPTION-PAY_PAYROLLS_V2PAYMENT METHOD-PAY_ORG_PAYMENT_METHODS_V
GL MAPPING SEGMENT
-PAY_PAYWSPGL_PAYROLLS
PER_PEOPLE_V
Person_id,employee_number,full_name,nationality,
Business_group_id,marital_status,original_date_of_hire,sex,
Current_employee_flag,effective_start_date,effective_end_date,title.PER_ALL_ASSIGNMENTS_FAssignment_id,Person_id,Job_id,Grade_id,Organoization_id
Business_group_id,Location_id,Supervisor_id,Position_id,
Recruiter_id,Primary_Flag,Effective_start_date,Effective_end_date,,
Payroll_id.
PER_JOBS HR_ALL_POSITIONS_F
Job_id,Business_group_id Psotion_id,Business_grouip_id,
Job_definition_id,Name, Position_defintion_id,Name,
status,Date_from Status,Date_from
PER_GRADES
Postion_id,
Business_grouip_id,
Position_defintion_id,
Name,satus,Date_from
PER_JOB_DEFINITIONS
Job_definition_idPER_GRADE_DEFINITIONSSummary_flag, Grade_definition_id,
Segment1, Summary_flag,
Segment2, Segment1
Enabled_flag Segment2
Enablled_Flag
PER_ADDRESSES_V
(It stores all the address and contact details of every Employee.The primary column is Row_id)
Columns: row_id,address_id,business_group_id,person_id,Priamry_flag,style,address_line1,
address_line2,address_type,postal_code
PER_IMAGESLIt stores the photo images of Employee by maintaining link to the per_people_f table .Images are stored in the in the format of BLOB)
Columns: Row_id,special_information_types,business_group_id,id_flex_num,Name,Enabled_Flag etc.PAY_PAYWSMEE_ELEMENT_ENTRIES(It Stores the elements)
Columns: Element_entry_id,Assignment_id,Effective_start_date,Effective_end_date,
Element_link_id,Original_entry_id ETC,PAY_PAYROLLS_F:(Stores payroll type)Columns: Payroll_id, _id,Effective_start_date,Effective_end_date,default_payment_method_id,
Business_group_id,period_type,payroll_name,gl_set_books_id.
PER_TIME_PERIODS_V(Stores the period of payroll)
Columns: row_id,time_period,payroll_id.
HR_LOCATIONS
Location_id,Location_code,business_group_id,description_style,Address_line1,Address_line2
Town_or_City,Country,ship_to_location_id.
HR_ALL_ORGANIZATION_UNITS
Organization_id,Business_group_id,Location_id,Date_from,Name,type,Internal_External_flagHR_ALL_POSITIONS_FPosition_id,Effective_start_date,Effective_end_date,business_group_id,Job_id,Location_id,
Organization_id,Date_Effecticve,Name,Permanent_Temporary_Flag,Position_type.PER_PERSON_ANALYSES(It stores special information types SIT)
Analysis_criteria_idHR_LOCATIONSLOCATION_ID,Location_code,Business_group_id,Description_style,Address_line1,
Address_line2,Town_or_city,Country,Ship_to_Location_id.
HR_ALL_ORGANIZATIONS_UNITS
Organization_id,Business_group_id,Location_id,Date_from,Name,Type,
Internal_Eternal_Flag
HR_ALL_PEOPLE_F
Position_f,Effective_start_date,Effective_end_dateBusiness_group_id,Job_id,Location_id,Organization_id,Date_Effective,Name,Permanent_temporary_flag,Position_type. PER_ALL_PEOPLE_F
Person_id

KFF and DFF

Steps Involved :
1. Registering A Tables & Columns :
Register the Table & Columns if the KFF/DFF is on User Defined Table. The following is the
example
AD_DD.register_table('AR','RAJ_KFF_TEST','T',8,10,90);
ad_dd.register_column('AR','RAJ_KFF_TEST','Attribute_category',1,'Varchar2',20,'N','N');
ad_dd.register_column('AR','RAJ_KFF_TEST','CCID',2,'NUMBER',10,'N’,'N');
ad_dd.register_column('AR','RAJ_KFF_TEST','attribute1',3,'VARCHAR2',20,'N','N');
For a KFF ....
It is necessary to have a column to store CCID and a column to Store Structure Information In the above example the Attribute_category is used to store Structure Info.
and CCID to store ccid number. These are specified at Registration of the Flexflied.For A DFF...It is enough to store only the Structure Info.
Example the Attribute_category may act as an Structure Field.2. Registering The FlexFiled :After creating the table and registering it, register the FlexField You You want to Use.
Remember : U have to Use this name when referencing the flexfield.
Also u have to specify the CCID column & Structure Column for KFF here.
Remember : U have to enable the columns of the table here, otherwise u can't define segments for the same.3. Defining the Segments :Every Flexfiled must have Segment Qualifiers And FlexField columns these are defined in the AOL
at Segment Definition.
After defining these segments freeze and compile the flexfield segments definition.
These 3 steps complete the process of Registration of table and flexfield & definition of Segments.Some Finer Points :1. You have to check the Dynamic Insertion Allowed ( for KFF only) to allow the users to dynamically create
an intelligent combination key.
2. You can check the Protect check box to ensure that users do not change the definition of flexfield by mistake.Incorporating DFF's / KFF's in the forms :The whole process essentially requires 4 steps ....
1. Modify 7 triggers
2. Create Hidden Fields corresponding to the segments
3. Define the FlexField in the New-Form-Instance-Trigger
4. Set the Profile Options ( Flex:Open_Descr_Window , Flex:Open-Key_Window ) to YES
Generic Activities to Open A Form In Apps ....You have to go through some steps to enable a form in Apps.
1. Open your Template.fmb ( this is provided by Oracle-Apps) .
2. Delete the BLOCKNAME ( datablock , canvas , window ) from the Template.fmb
3. Save this form Module with ur custom name.
4. Create A Canvas , subclass it with Profile class Canvas
5. Similary create a Window ( subclass it with Window profile class) , Datablock.
6. Modify the APPS_CUSTOM package body with following code....
if (wnd = '') then
app_window.close_first_window;
Give your name of Window at the Bolded place.
7. Modify The pre-form trigger as follows..
app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');
Give Your block name here at the bolded place.Now modify the required triggers for implementing the DFF's and KFF's .......1. The Following triggers should be change...
a. When-new-form-instance
b. pre-query
c. post-query
d. WHEN-NEW-ITEM-INSTANCE
e. PRE-UPDATE
f. WHEN-VALIDATE-RECORD
g. WHEN-VALIDATE-ITEM
add FND_FLEX.EVENT('EVENT NAME') where EVENT NAME is the trigger name itself.
eg. FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
2. Create Hidden Fields ( Set Canvas to null) . These should be as many as the number of segments u defined .
3. Define Your flex-filed at the New-Form-Instance trigger as follows ...
FND_KEY_FLEX.DEFINE(BLOCK=>'FLEX_BLOCK',FIELD=>'KFF_SEGS',APPL_SHORT_NAME=>'AR', DESC_FLEX_NAME=>'DFF_FLEX')
for DFF....
Block - is the block name in the form
Field - Field which is acting as DFF/KFF
Appl_Short_name :- AR/ AP / CS etc.,
Desc_FLEX_NAME :- Name of the Flex Filed U have given at the time of Registration.For KFF.....CODE - the number u give at the time of registering ur KFF
NUM - Use the following SQL to get the number . ( default is 101 )
SELECT ID_FLEX_NUM FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE='CODE';
4. Create a TEXT-FIELD to act as DFF or KFF
For DFF subclass it with ....
TEXT_ITEM_DESC_FLEX
For Kff subclass it with ...
TEXT_ITEM itself
5. Save ur work and
attach this form to a function
Function to a menu.

PL/SQL Faq's

1. What is PL/SQL and what is it used for?PL/SQL is Oracle's Procedural Language extension to SQL. PL/SQL's language syntax,
structure and data types are similar to that of ADA. The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading,
information hiding (all but inheritance). PL/SQL is commonly used to write data-centric
programs to manipulate data in an Oracle database.2. Should one use PL/SQL or Java to code procedures and triggers?Internally the Oracle database supports two procedural languages, namely PL/SQL and Java.
This leads to questions like "Which of the two is the best?" and "Will Oracle ever de support PL/SQL in favor of Java?”
Many Oracle applications are based on PL/SQL and it would be difficult of Oracle
to ever de support PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example,
Oracle 9iDB supports native compilation of Pl/SQL code to binaries.
PL/SQL and Java appeal to different people in different job roles.
The following table briefly describes the difference between these two language environments:
PL/SQL:
Data centric and tightly integrated into the database
Proprietary to Oracle and difficult to port to other database systems
Data manipulation is slightly faster in PL/SQL than in Java
Easier to use than Java (depending on your background)
JAVA:
Open standard, not proprietary to Oracle
Incurs some data conversion overhead between the Database and Java type systems
Java is more difficult to use (depending on your background)
3. How can one see if somebody modified any code?
Code for stored procedures, functions and packages is stored in the Oracle Data Dictionary.
One can detect code changes by looking at the LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:
SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI’) MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE LAST_DDL_TIME > '&CHECK_FROM_DATE';4. How can one search PL/SQL code for a string/ key value?The following query is handy if you want to know where a certain table, field or expression
is referenced in your PL/SQL source code.
SELECT TYPE, NAME, LINE
FROM USER_SOURCE
WHERE UPPER(TEXT) LIKE '%&KEYWORD%';
5. How can one keep a history of PL/SQL code changes?
One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema
(or database) level trigger (available from Oracle 8.1.7). This way one can easily revert
to previous code should someone make any catastrophic changes. Look at this example:
CREATE TABLE SOURCE_HIST -- Create history table
AS SELECT SYSDATE CHANGE_DATE, USER_SOURCE.*
FROM USER_SOURCE WHERE 1=2;
CREATE OR REPLACE TRIGGER change_hist -- Store code in hist table
AFTER CREATE ON SCOTT.SCHEMA -- Change SCOTT to your schema name
DECLARE
BEGIN
if DICTIONARY_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY', 'TYPE') then
-- Store old code in SOURCE_HIST table
INSERT INTO SOURCE_HIST
SELECT sysdate, user_source.* FROM USER_SOURCE
WHERE TYPE = DICTIONARY_OBJ_TYPE
AND NAME = DICTIONARY_OBJ_NAME;
end if;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, SQLERRM);
END;
/
show errors
6. How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to
protect the source code. This is done via a standalone utility that transforms the
PL/SQL source code into portable binary object code (somewhat larger than the original).
This way you can distribute software without having to worry about exposing your
proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand
and know how to execute such scripts. Just be careful, there is no "decode" command available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.plb
7. Can one print to the screen from PL/SQL?
One can use the DBMS_OUTPUT package to write information to an output buffer.
This buffer can be displayed on the screen from SQL*Plus if you issue the
SET SERVEROUTPUT ON; command. For example:
set serveroutput on
begin
dbms_output.put_line('Look Ma, I can print from PL/SQL!!!');
end;
/
DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much,
the output buffer will overflow. In that case, set the buffer size to a larger value,
eg.: set serveroutput on size 200000
If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember,
and then EXEC NULL;. If you haven't cleared the DBMS_OUTPUT buffer with the disable
or enable procedure, SQL*Plus will display the entire contents of the buffer when
it executes this dummy PL/SQL block.
8. Can one read/write files from PL/SQL?
Included in Oracle 7.3 is an UTL_FILE package that can read and write operating system files.
The directory you intend writing to has to be in your INIT.ORA file
(see UTL_FILE_DIR=... parameter). Before Oracle 7.3 the only means of writing a file was
to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
Copy this example to get started:
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/tmp', 'myfile', 'w');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in
INIT.ORA.');
END;
/
9. Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using
the "EXECUTE IMMEDATE" statement. Users running Oracle versions below 8i can look at the
DBMS_SQL package (see FAQ about Dynamic SQL).
begin
EXECUTE IMMEDIATE 'CREATE TABLE X(A DATE)';
end;
NOTE: The DDL statement in quotes should not be terminated with a semicolon.10. Can one use dynamic SQL statements from PL/SQL?Starting from Oracle8i one can use the "EXECUTE IMMEDIATE" statement to execute dynamic SQL
and PL/SQL statements (statements created at run-time). Look at these examples.
Note that statements are NOT terminated by semicolons:
EXECUTE IMMEDIATE 'CREATE TABLE x (a NUMBER)';
-- Using bind variables...
sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
-- Returning a cursor...
sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements.
Look at these examples:
CREATE OR REPLACE PROCEDURE DYNSQL AS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;
/
More complex DBMS_SQL example using bind variables:
CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS
v_cursor integer;
v_dname char(20);
v_rows integer;
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cursor, 'select dname from dept where deptno > :x', DBMS_SQL.V7);
DBMS_SQL.BIND_VARIABLE(v_cursor, ':x', no);
DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);
v_rows := DBMS_SQL.EXECUTE(v_cursor);
loop
if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then
exit;
end if;
DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);
DBMS_OUTPUT.PUT_LINE('Deptartment name: 'v_dname);
end loop;
DBMS_SQL.CLOSE_CURSOR(v_cursor);
EXCEPTION
when others then
DBMS_SQL.CLOSE_CURSOR(v_cursor);
raise_application_error(-20000, 'Unknown Exception Raised: 'sqlcode' '
sqlerrm);
END;
/11. What is the difference between %TYPE and %ROWTYPE?The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs,
and allows programs to adapt as the database changes to meet new business needs.
%ROWTYPE is used to declare a record with the same types as found in the specified
database table, view or cursor. Example:
DECLARE
v_EmpRecord emp%ROWTYPE;
%TYPE is used to declare a field with the same type as that of a specified table's column.
Example:
DECLARE
v_EmpNo emp.empno%TYPE;
12. What is the result of comparing NULL with NULL?
NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL
is evaluated to NULL. Look at this code example to convince yourself.
declare
a number := NULL;
b number := NULL;
begin
if a=b then
dbms_output.put_line('True, NULL = NULL');
elsif a<>b then
dbms_output.put_line('False, NULL <> NULL');
else
dbms_output.put_line('Undefined NULL is neither = nor <> to NULL');
end if;
end;13. How does one get the value of a sequence into a PL/SQL variable?As you might know, one cannot use sequences directly from PL/SQL.
Oracle (for some silly reason) prohibits this:
i := sq_sequence.NEXTVAL;
However, one can use embedded SQL statements to obtain sequence values:
select sq_sequence.NEXTVAL into :i from dual;
Thanks to Ronald van Woensel
14. Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7.
However, one can write an external program (using one of the precompiler languages,
OCI or Perl with Oracle access modules) to act as a listener on a database pipe
(SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe,
the listener picks it up and run the requests. Results are passed back on a different
database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.
15. How does one loop through tables in PL/SQL?
Look at the following nested loop code example.
DECLARE
CURSOR dept_cur IS
SELECT deptno
FROM dept
ORDER BY deptno;
-- Employee cursor all employees for a dept number
CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS
SELECT ename
FROM emp
WHERE deptno = v_dept_no;
BEGIN
FOR dept_rec IN dept_cur LOOP
dbms_output.put_line('Employees in Department 'TO_CHAR(dept_rec.deptno));
FOR emp_rec in emp_cur(dept_rec.deptno) LOOP
dbms_output.put_line('...Employee is 'emp_rec.ename);
END LOOP;
END LOOP;
END;
/16. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop
to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit,
the sooner the extents in the rollback segments will be cleared for new transactions,
causing ORA-1555 errors.
To fix this problem one can easily rewrite code like this:
FOR records IN my_cursor LOOP
...do some stuff...
COMMIT;
END LOOP;
... to ...
FOR records IN my_cursor LOOP
...do some stuff...
i := i+1;
IF mod(i, 10000) THEN -- Commit every 10000 records
COMMIT;
END IF;
END LOOP;
If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.
NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.17. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?PL/SQL respect object privileges given directly to the user, but does not observe
privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus,
but will give an error in PL/SQL. Choose one of the following solutions:
Grant direct access on the tables to your user. Do not use roles!
GRANT select ON scott.emp TO my_user;
Define your procedures with invoker rights (Oracle 8i and higher);
Move all the tables to one user/schema.18. What is a mutating and constraining table?"Mutating" means "changing". A mutating table is a table that is currently being modified
by an update, delete, or insert statement. When a trigger tries to reference a table that is
in state of flux (being changed), it is considered "mutating" and raises an error
since Oracle should not return data that has not yet reached its final state. Another way this

CUSTOMER INTERFACE USING SQL LOADER

Save this file .csv(comma separated value)
Create the following Staging Table in apps schema
Name Null? Type
------------------------------- -------- --------------------------
ORIG_SYSTEM_CUSTOMER_REF VARCHAR2(240)
SITE_USE_CODE VARCHAR2(30)
ORIG_SYSTEM_ADDRESS_REF_BILL VARCHAR2(240)
ORIG_SYSTEM_ADDRESS_REF_SHIP VARCHAR2(240)
CUSTOMER_NAME VARCHAR2(360)
CUSTOMER_TYPE VARCHAR2(25)
CUSTOMER_CLASS_CODE VARCHAR2(30)
CUSTOMER_CATEGORY_CODE VARCHAR2(30)
ADDRESS1 VARCHAR2(240)
ADDRESS2 VARCHAR2(240)
ADDRESS3 VARCHAR2(240)
ADDRESS4 VARCHAR2(240)
CITY VARCHAR2(60)
COUNTY VARCHAR2(60)
STATE VARCHAR2(60)
POSTAL_CODE VARCHAR2(60)
COUNTRY VARCHAR2(60)
SITE_USE_TAX_CODE VARCHAR2(50)
SITE_SHIP_VIA_CODE VARCHAR2(25)
BILL_TO_ORIG_ADDRESS_REF VARCHAR2(240)
CUST_TAX_EXEMPT_NUM VARCHAR2(30)
CUSTOMER_PROFILE_CLASS_NAME VARCHAR2(30)
OVERALL_CREDIT_LIMIT NUMBER
COLLECTOR_NAME VARCHAR2(30)
PAYMENT_METHOD_NAME VARCHAR2(30)
CONTACT_FIRST_NAME VARCHAR2(40)
CONTACT_LAST_NAME VARCHAR2(50)
CONTACT_TITLE VARCHAR2(30)
TELEPHONE_AREA_CODE VARCHAR2(10)
TELEPHONE VARCHAR2(25)
TELEPHONE_TYPE VARCHAR2(30)
EMAIL_ADDRESS VARCHAR2(240)
Query and find the following interface tables
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
And insert the data in these interface table in following manner
Now create the control file using TOAD sql loader wizard it is described in SQLLOADER.DOC FILE
After then create sql stored procedure for inserting data in interface table from staging table
Staging table is CUSTOMER_INT
Interface tables are
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
Create the following PL/SQL procedure
declare
cursor cust_cur is select * from customer_int;
l_address_ref varchar2(100);
l_primary_site_use_flag varchar2(1) := 'Y';
l_flag varchar2(1) := 'Y';
begin
delete from ra_customers_interface_all;
delete from ra_customer_profiles_int_all;
commit;

for c in cust_cur loop
if c.site_use_code = 'Bill-To' then
l_address_ref := c.orig_system_address_ref_bill;
else
l_address_ref := c.orig_system_address_ref_ship;
end if;
if l_flag = 'Y' then
insert into ra_customers_interface_all
(orig_system_customer_ref
,site_use_code
,orig_system_address_ref
,insert_update_flag
,customer_name
,customer_type
,customer_class_code
,customer_category_code
,address1
,address2
,address3
,address4
,city
,county
,state
,postal_code
,country
,site_use_tax_code
,site_ship_via_code
,bill_to_orig_address_ref
,cust_tax_exempt_num
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,primary_site_use_flag
,customer_status
)
values (c.orig_system_customer_ref
,decode(c.site_use_code,'Bill-To','BILL_TO','Ship-To','SHIP_TO')
,l_address_ref
,'I'
,c.customer_name
,decode(c.customer_type,'External','R','Internal','I')
,c.customer_class_code
,c.customer_category_code
,c.address1
,c.address2
,c.address3
,c.address4
,c.city
,c.county
,c.state
,c.postal_code
,c.country
,c.site_use_tax_code
,c.site_ship_via_code
,c.bill_to_orig_address_ref
,c.cust_tax_exempt_num
,-1
,sysdate
,sysdate
,-1
,204
,l_primary_site_use_flag
,'A'
);
insert into ra_customer_profiles_int_all
(orig_system_customer_ref
,insert_update_flag
,customer_profile_class_name
,credit_hold
,overall_credit_limit
,credit_checking
,collector_name
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,currency_code
,trx_credit_limit
,validated_flag
)
values (c.orig_system_customer_ref
,'I'
,c.customer_profile_class_name
,'N'
,c.overall_credit_limit
,'Y'
,c.collector_name
,-1
,sysdate
,sysdate
,-1
,204
,'USD'
,c.overall_credit_limit
,'Y'
);
end if;

if l_flag = 'N' then
insert into ra_customers_interface_all
(orig_system_customer_ref
,site_use_code
,orig_system_address_ref
,insert_update_flag
,customer_name
,customer_type
,customer_class_code
,customer_category_code
,address1
,address2
,address3
,address4
,city
,county
,state
,postal_code
,country
,site_use_tax_code
,site_ship_via_code
,bill_to_orig_address_ref
,cust_tax_exempt_num
,last_updated_by
,last_update_date
,creation_date
,created_by
,org_id
,primary_site_use_flag
,customer_status
)
values (c.orig_system_customer_ref
,decode(c.site_use_code,'Bill-To','BILL_TO','Ship-To','SHIP_TO')
,l_address_ref
,'I'
,c.customer_name
,decode(c.customer_type,'External','R','Internal','I')
,c.customer_class_code
,c.customer_category_code
,c.address1
,c.address2
,c.address3
,c.address4
,c.city
,c.county
,c.state
,c.postal_code
,c.country
,c.site_use_tax_code
,c.site_ship_via_code
,c.bill_to_orig_address_ref
,c.cust_tax_exempt_num
,-1
,sysdate
,sysdate
,-1
,204
,l_primary_site_use_flag
,'A'
);
end if;
commit;
l_flag := 'N';
l_primary_site_use_flag := 'N';
end loop;
end;
When we run this procedure in apps schema, this is procedure copy the data to interface tables from staging table

After then after we move to oracle apps and do the following steps
First go to responsibility: Receivables Vision Operations(USA)

Run the customer interface

The output of customer interface
Here all records are inserted in base tables with out any exception

BOGE PRASANTH REDDY ORACLE APPLICATIONS BLOG

Wednesday, 20 April 2011

R12 - How to link GL data to the subledger data or vice versa

R12 - How to link GL data to the subledger data or vice versa