Column Mapping from Oracle Order Management and Oracle Receivables

Recompiling the invalid objects

BEGIN
exec sys.utl_recomp.recomp_parallel(20);
END;
/

Useful query :Relation between AR invoice and Sales Order

Query for to find out AR invoice and Sales Order Details


SELECT oha.order_number, 
               ola.line_number so_line_number,
               ola.ordered_item item_name,
              ola.ordered_quantity * ola.unit_selling_price LINE_AMOUNT,
              rcta.trx_number Transaction_number, rcta.trx_date,
              rctla.line_number TRX_line_number,
               rctla.unit_selling_price unit_selling_price
  FROM oe_order_headers_all oha,
              oe_order_lines_all ola,
               ra_customer_trx_all rcta,
               ra_customer_trx_lines_all rctla
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND order_number = :p_order_number

Oracle 10g new features for developers

10g Limit less LOB
  Since first introduced the Oracle LOB type has been limited to 4GB (enough for most uses)
• Oracle 10g allows LOB data to be limited only by tablespace page size
• Current limit: – 8–128 terabytes
• Supported environments:
– PL/SQL using DBMS_LOB
– Java using JDBC
– C/C++ using OCI

10g R2 DML Error Logging


Insert, Update, Delete, and Merge add ERROR logging allowing you to capture DML errors and log
them
INSERT … /* or UPDATE, DELETE, MERGE */
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED }
– Default error table defined by DBMS_ERRLOG package: ERR$_ followed by first 25 characters of DML target table
– Simple expression is value to be used as statement tag (may be result of SQL function call)
– Reject limit default is zero

Creating the Error Log Table


Oracle provides a PL/SQL packaged procedure to create the logging table (for each table to be logged)
execute DBMS_ERRLOG.CREATE_ERROR_LOG('myemp', 'myemplog');
– myemp Table DML is being applied to
– myemplog Logging table for rejected rows
• Creates a database table containing:
– ORA_ERR_NUMBER$ Error number
– ORA_ERR_MESG$ Error message
– ORA_ERR_ROWID$ Rowid of impacted rows
– ORA_ERR_OPTYP$ Operation type (I,U,D,M)
– ORA_ERR_TAG$ Text from LOG_ERRORS
– All column values (good & bad) as varchar2(4000)

Error Log Output


insert into emp select * from myempbig
log errors into myemplog ('Log test3')
reject limit unlimited;
0 rows created.
ORA_ERR_NUMBER$ 12899
ORA_ERR_MESG$ ORA-12899: value too large for column
"JOHN"."EMP"."JOB" (actual: 13, maximum: 9)
ORA_ERR_ROWID$
ORA_ERR_OPTYP$ I
ORA_ERR_TAG$ Log test3
EMPNO 6543
ENAME STEPHENSON
JOB WEB DEVELOPER
MGR 7369
HIREDATE 03-SEP-06
SAL 3000
COMM
DEPTNO 40

10g SQL*Plus Misc


SET SERVEROUTPUT ON now works immediately within PL/SQL block where executed
• DBMS_OUTPUT.PUT_LINE text line maximum increased from 255 to 32767 bytes
• Recycle Bin keeps deleted database objects until Purged
• DESCRIBE automatically attempts to validate invalid objects before display
• White space now allowed in file names
• Substitution variables allowed in SET PROMPT
• Three pre-defined SQL*Plus variables added:
– _DATE Current date or a user defined fixed string.
– _PRIVILEGE Privilege level of connection
(AS SYSDBA, AS SYSOPER or blank)
– _USER Currently connected userid
• APPEND, CREATE, REPLACE extensions to SPOOL

SET SERVEROUTPUT ON

It is common for PL/SQL developers to use the DBMS_OUTPUT.PUT_LINE procedure to write to the console during testing and debugging
• To enable output from DBMS_OUTPUT.PUT_LINE you must enable SERVEROUTPUT
• In Oracle 10g this command has been enhanced to include a default of UNLIMITED buffer size eliminating the need to specify a buffer size
• You may also specify “WORD_WRAPPED” to cause DBMS_OUTPUT.PUT_LINE output to be wrapped at clear word breaks
set serveroutput on size 1000000 –- size limited
set serveroutput on unlimited -- size unlimited
set serveroutput on -- size unlimited (default)

10g Using Recyclebin


Careful! Dropping tables no longer really drops them… This might be a problem for applications with
lots of “temp”-type tables

drop table myTable;

show recyclebin
ORIGINAL    RECYCLEBIN NAME     TYPE  DROP TIME
myTable RB$$41506$TABLE$0   TABLE 2004-04-01:22:11:13

flashback table myTable to before drop;

drop table myTable purge;

purge recyclebin;

10g Regular Expressions

Oracle now has three functions that allow the use of POSIX-compliant regular expressions 

in SQL

– REGEXP_LIKE Allows pattern matching

– REGEXP_INSTR Search for string matching pattern and return position

– REGEXP_REPLACE Find string matching pattern and replace it

– REGEXP_SUBSTR Search for string matching pattern and return substring

Regular Expression Examples

select employee_id,phone_number
from hr.employees
where REGEXP_LIKE(phone_number,
'[[:digit:]]{3}[[:punct:]][[:digit:]]{2}[[:punct:]]');

Example

select first_name, last_name
from hr.employees
where REGEXP_LIKE (first_name, '^ste(v|ph)en$');

LIKE vs REGEXP_LIKE


Here are two statement that generate exactly the same output and nearly the same execution plan

select prod_id , substr(prod_name,1,20) prod_name , substr(prod_desc,1,30) prod_desc
from sh.products
where prod_name like ('E%') 
or prod_name like ('P%')
order by prod_id;

select prod_id, substr(prod_name,1,20) prod_name, substr(prod_desc,1,30) prod_desc
from sh.products
where regexp_like (prod_name,'^E|^P')
order by prod_id;

creating responsibility in oracle apps R12

Log in to Oracle apps front end.
Select the System Administrator Responsibility
Navigation : Security -> Responsibility ->Define







Enter the Responsibility name , Application Name, Responsibility Key , Menu and Data group details.

Then Click on the SAVE button.
The responsibility will be saved in the system. From back end you can check by using the below query.

select * from fnd_reponsiblity_tl where responsibility_name like '%name%'

Once the responsibility is saved successfully. Then assign the user to the responsibility.

Switch the menu to user (Security -> User->Define)
Query the user name to which you want to add the responsibility. Then add the responsibility to the user.