Programming: 2014

Monday, December 29, 2014

How to Find Oracle Application User Password?

Finding EBS Application Passwords

It is easy to

Find Oracle EBS Application User Passwords

in just 2 simple steps:

Step 1 - Create Following Package:

CREATE OR REPLACE PACKAGE MY_PKG
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END ;

CREATE OR REPLACE PACKAGE BODY MY_PKG
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END ;

2 - Run Following Query :

SELECT usr.user_name,
       MY_PKG.decrypt
          ((SELECT (SELECT MY_PKG.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
FROM fnd_user usr
WHERE usr.user_name = :USER_NAME;

Friday, December 19, 2014

Oracle EBS APIs and Their Uses / Uses of Oracle APIs

source:http://www.shareoracleapps.com/p/var-acctoctrue.html

Wednesday, October 22, 2014

How to Set Forms Path for Oracle Form Builder (Forms_Path)

If you are willing to compile Oracle Forms on your PC having windows, XP, 7 or 8 then you have add an environment variable named "FORMS_PATH" and assign it a value where your forms, resources and plsql are available on your system.

In my case I have placed AU_TOP to my D: drive.

Then value of environment variable will be:

D:\au\12.0.0\resource;D:\au\12.0.0\plsql;D:\au\12.0.0\forms\US

Navigation:
Right Click on My Computer > Properties > Advance > Environment Variable.

Monday, July 21, 2014

Oracle Payroll : Error During Batch Transfer / Error During Batch Validation ( Fixed )

+---------------------------------------------------------------------------+
Human Resources: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

PAYLINK module: BEE Batch Process
+---------------------------------------------------------------------------+

Current system time is 21-JUL-2014 09:12:47

+---------------------------------------------------------------------------+

===============================================================================
Oracle Human Resources Generic Process
Started at : 21-JUL-2014 09:10:41
Chunk Size : 20
Threads : 1
Logging Category :
User Messaging : N
Interlock : N
Trace : N
RR Buffer Size : 500
RRV Buffer Size : 500
Bal Buffer Size : 500
EE Buffer Size : 500
Logging Area :
Assignment ID for Start:
Assignment ID for End o:

-------------------------------------------------------------------------
PL/SQL Output
-------------------------------------------------------------------------
-------------------------------------------------------------------------
HR_6881_HRPROC_ORA_ERR
SQLERRMC ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3239
ORA-06512: at line 2

SQL_NO 4809
TABLE_NAME py_rollback_pkg.rollback_payroll_action
APP-PAY-06881: Error ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3239
ORA-06512: at line 2
has occurred in table py_rollback_pkg.rollback_payroll_action at location 4809

Cause: an oracle error has occurred. The failure was reported on table py_rollback_pkg.rollback_payroll_action at location 4809 with the error text ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "APPS.PY_ROLLBACK_PKG", line 3239
ORA-06512: at line 2
.

Action: Please contact your support representative.

+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...

Finished executing request completion options.
Concurrent program returned no reason for failure.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 21-JUL-2014 09:12:50

+---------------------------------------------------------------------------+

Possible Causes:

1 - Wrong Assignment Number Entered. ( Number Not Exits OR Terminated Employees )
Solution:
Check Your Data.
OR
Try to load few data.

2 - Data Corruption
Solution:
Log SR.

Thursday, July 10, 2014

Export Data To Excel Using C# / C Sharp

Export Data From Data Set / Data Table / Data Grid View to MS Excel:

You can export data from Data Set / Data Table / Data Grid View to MS Excel Using Following Function:

public void ExportToExcel(string reportName, string parameter, string path, DataGridView dgv , string userName)
{
int i = 0;
int j = 0;
int col = 0;

string folderPath = "";

FolderBrowserDialog fbdExportFileLocation = new FolderBrowserDialog();
if (fbdExportFileLocation.ShowDialog() == DialogResult.OK)
{
folderPath = fbdExportFileLocation.SelectedPath;
}

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

// Report Name
xlWorkSheet.Cells[1, 1] = reportName;

// Parameters
xlWorkSheet.Cells[3, 1] = parameter;

//
xlWorkSheet.Cells[4, 1] = "User Name : " + userName;

// Report Columns Heading
for (col = 0; col <= dgv.ColumnCount - 1; col++)
{

xlWorkSheet.Cells[5, col + 1] = dgv.Columns[col].HeaderText.ToString().Replace('_',' ');

}

Excel.Range Rows = xlWorkSheet.get_Range("A1", "A5");
Rows.EntireRow.Font.Bold = true;

Excel.Range Row1 = xlWorkSheet.get_Range("A1", "A1");
Row1.EntireRow.Font.Size = 16;

for (i = 0; i <= dgv.RowCount - 1; i++)
{
for (j = 0; j <= dgv.ColumnCount - 1; j++)
{
DataGridViewCell cell = dgv[j, i];
xlWorkSheet.Cells[i + 6, j + 1] = cell.Value;
}

}

xlWorkBook.SaveAs(folderPath+"\\REPORT-"+reportName+".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created, you can find the file on : " + folderPath + "\\REPORT-" + reportName + ".xls");
}

Now you can convert code from Any Language to Other Language : Link

Oracle String Functions

Name	Description
`ASCII()`	Return numeric value of left-most character
`BIN()`	Return a string containing binary representation of a number
`BIT_LENGTH()`	Return length of argument in bits
`CHAR_LENGTH()`	Return number of characters in argument
`CHAR()`	Return the character for each integer passed
`CHARACTER_LENGTH()`	Synonym for CHAR_LENGTH()
`CONCAT_WS()`	Return concatenate with separator
`CONCAT()`	Return concatenated string
`ELT()`	Return string at index number
`EXPORT_SET()`	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
`FIELD()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`HEX()`	Return a hexadecimal representation of a decimal or string value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`LCASE()`	Synonym for LOWER()
`LEFT()`	Return the leftmost number of characters as specified
`LENGTH()`	Return the length of a string in bytes
`LIKE`	Simple pattern matching
`LOAD_FILE()`	Load the named file
`LOCATE()`	Return the position of the first occurrence of substring
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MATCH`	Perform full-text search
`MID()`	Return a substring starting from the specified position
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`OCT()`	Return a string containing octal representation of a number
`OCTET_LENGTH()`	Synonym for LENGTH()
`ORD()`	Return character code for leftmost character of the argument
`POSITION()`	Synonym for LOCATE()
`QUOTE()`	Escape the argument for use in an SQL statement
`REGEXP`	Pattern matching using regular expressions
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`RIGHT()`	Return the specified rightmost number of characters
`RLIKE`	Synonym for REGEXP
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`STRCMP()`	Compare two strings
`SUBSTR()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`SUBSTRING()`	Return the substring as specified
`TRIM()`	Remove leading and trailing spaces
`UCASE()`	Synonym for UPPER()
`UNHEX()`	Return a string containing hex representation of a number
`UPPER()`	Convert to uppercase

Reference : For Further Details

Thursday, June 12, 2014

How to find EBS Employee Type?

Query to find Person Type:

SELECT
PAPF.EMPLOYEE_NUMBER,
PAPF.FULL_NAME,
PPT.USER_PERSON_TYPE PERSON_TYPE
FROM

PER_PERSON_TYPE_USAGES_F PTYPE,

PER_PERSON_TYPES PPT,

PER_ALL_PEOPLE_F PAPF

WHERE
PTYPE.PERSON_ID = PAPF.PERSON_ID
AND PPT.PERSON_TYPE_ID = PTYPE.PERSON_TYPE_ID
AND SYSDATE BETWEEN PTYPE.EFFECTIVE_START_DATE AND PTYPE.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

Wednesday, May 28, 2014

Oracle Dependent Objects / Query to find Dependent Views , Functions, Procedures and Fast Formulas

How to find dependent objects in Oracle?

SELECT A.OWNER||'.'||A.NAME DEPENDENT_OBJECTS
FROM
(
SELECT
*
FROM
DBA_DEPENDENCIES
) A
START WITH REFERENCED_NAME = :OBJECT_NAME
CONNECT BY NOCYCLE ((REFERENCED_NAME = PRIOR NAME) AND (REFERENCED_OWNER = PRIOR OWNER))

Thursday, May 15, 2014

Denomination

Salary Breakup:

Denomination:

Use the following Query to find the Denomination:

You can change values in order to get your desired results.

SELECT
TRUNC(SAL/1000) THOUSAND_NOTES,
MOD(SAL,1000) REMANING_AFTER_THOUSAND,
TRUNC(MOD(SAL,1000)/500) FIVE_HUNDRED_NOTES,
MOD(MOD(SAL,1000),500) REMANING_AFTER_FIVE,
TRUNC(MOD(MOD(SAL,1000),500)/100) HUNDRED_NOTES,
MOD(MOD(MOD(SAL,1000),500),100) REMANING_AFTER_HUNDRED,
TRUNC(MOD(MOD(MOD(SAL,1000),500),100)/50) FIFTY_NOTES,
MOD(MOD(MOD(MOD(SAL,1000),500),100),50) REMANING_AFTER_50
FROM
(
SELECT
95780 SAL
FROM
DUAL
UNION ALL
SELECT
95000 SAL
FROM
DUAL
UNION ALL
SELECT
90080 SAL
FROM
DUAL
)

Wednesday, May 7, 2014

Purge Element Batch Error Lines/ How to Register Procedure in Oracle ERP?

While uploading an element entry batch using Web ADI and getting few Error lines, you can use the following Procedure to remove lines which contains errors. So that you can transfer successful lines.

Oracle ERP Payroll Delete Error Lines From Element Batch Entry

Step 1: Create Following Procedure

CREATE OR REPLACE PROCEDURE APPS.ERROR_BATCH_LINE_DELTE_GTM
(
ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
BATCH_ID_P IN NUMBER
) IS

BEGIN

DELETE FROM PAY_BATCH_LINES WHERE BATCH_LINE_STATUS='E' AND BATCH_ID=BATCH_ID_P;

COMMIT;

END;

Step 2: Now Register Procedure to EBS to make it available for the user:

Application Developer > Concurrent > Executable

Application Developer > Concurrent > Program

Parameter Form:

Note that your procedure have 3 parameters:

1 - ERRBUF is output parameter used for System Error Buffer. (Do not assign any value to this parameter)
2 - RETCODE is output parameter used to return Error Code. (Do not assign any value to this parameter)
3 - BATCH_ID_P is input parameter used to Delete the specified batch. (Assign a value to this parameter)

Create a LOV which show batch element names and return Batch Id. Then assign to Program.

Tuesday, May 6, 2014

Oracle Top Interview Questions

1- What is Sub Query?

A sub query is a SQL query nested inside a larger query. A sub query may used in :

SELECT clause

FROM clause

WHERE clause

A sub query can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another sub query.

2- What are the various Oracle database objects?

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES

3- What is Alias?

Alias is a temporary name provided to a column or table. It gets over after the execution of SQL statement.

4- What is a view?

View is a virtual table which can be based on one or more tables.

5- What is a hot backup?

Hot Backup is basically taking a backup of the database while it is still up and running and database must be in archive log mode.

Benefit: The database is still available for use while the backup is occurring and you can recover the database to any point in time.

6- What is a cold backup?

Cold Backup is basically taking a backup of the database while it is shut down and does not require being in archive log mode.

Benefit: It is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

7- Difference between TRUNCATE and DELETE for a table.

Both Truncate and delete commands can use to delete all data from a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.

8- What are benefits of index?

It provides faster access to data blocks in a table.

9- In a parent and child table relationship you want to drop parent and re-create it. How would you do this without affecting the children tables?

Steps:

1- Disable the foreign key constraint to the parent.

2- Drop the parent table.

3- Re-create the parent table.

4- Enable the foreign key constraint.

10- What is the difference between $ORACLE_BASE and $ORACLE_HOME?

ORACLE_BASE is the root directory for oracle and ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

11- How would you determine the time zone by using database query?

SELECT dbtimezone FROM DUAL;

12- What is the difference between a FUNCTION, PROCEDURE and PACKAGE?

They are all named PL/SQL blocks.

Function must return a value. It can be called inside a query.

Procedure may or may not return value.

Package is the collection of functions, procedures, variables which can be logically grouped together.

13- What is Snap Shot in Oracle Database?

Snapshots are read only copies of a master table located on a remote node and these are periodically refreshed to reflect changes made to the master table.