Programming

Friday, March 27, 2015

Update Element Entry API

CREATE OR REPLACE PROCEDURE MNJ_UPDATE_LEAVE_HOURS( ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)
AS
   L_EFFECTIVE_START_DATE   DATE    := NULL;
   L_EFFECTIVE_END_DATE     DATE    := NULL;
   L_UPDATE_WARNING         BOOLEAN;
   L_ELEMENT_TYPE_ID        NUMBER := NULL;
   L_BUSINESS_GROUP_ID      NUMBER := 81;

   CURSOR DAYS_CUR
   IS

    SELECT
        PAAF.ASSIGNMENT_NUMBER,
        PEEF.ASSIGNMENT_ID,
        PEEF.ELEMENT_ENTRY_ID,
        PEEF.ELEMENT_TYPE_ID,
        PEEF.EFFECTIVE_START_DATE,
        PEEF.EFFECTIVE_END_DATE,
        PEEF.OBJECT_VERSION_NUMBER,
        PIVF.INPUT_VALUE_ID,
        PETF.ELEMENT_NAME,
        VAL.SCREEN_ENTRY_VALUE,
        UPPER(PIVF.NAME)    INPUT_VALUE
    FROM
        PAY_INPUT_VALUES_F          PIVF,
        PAY_ELEMENT_ENTRIES_F       PEEF,
        PAY_ELEMENT_ENTRY_VALUES_F VAL,
        PER_ALL_ASSIGNMENTS_F       PAAF,
        PAY_ELEMENT_TYPES_F         PETF
    WHERE
        PEEF.ELEMENT_ENTRY_ID       =   VAL.ELEMENT_ENTRY_ID
    AND PEEF.EFFECTIVE_START_DATE   =   VAL.EFFECTIVE_START_DATE
    AND PEEF.EFFECTIVE_END_DATE     =   VAL.EFFECTIVE_END_DATE
    AND PIVF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND PIVF.INPUT_VALUE_ID         =   VAL.INPUT_VALUE_ID
    AND PAAF.ASSIGNMENT_ID          =   PEEF.ASSIGNMENT_ID
    AND PETF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND SYSDATE     BETWEEN             PAAF.EFFECTIVE_START_DATE   AND PAAF.EFFECTIVE_END_DATE
    AND PAAF.PRIMARY_FLAG           =   'Y'
    AND NVL(PEEF.ATTRIBUTE1,'N')    =   'N'
    AND UPPER(PIVF.NAME)            =   'DAYS'
    AND PEEF.ELEMENT_TYPE_ID        IN ( '676' , '677' );   --Casual Leave First Half , Casual Leave Second Half

   CURSOR DATE_CUR ( P_ASSIGNMENT_ID NUMBER , P_ELEMENT_TYPE_ID NUMBER )
   IS

    SELECT
        PEEF.ASSIGNMENT_ID,
        PEEF.ELEMENT_ENTRY_ID,
        PEEF.ELEMENT_TYPE_ID,
        PEEF.EFFECTIVE_START_DATE,
        PEEF.EFFECTIVE_END_DATE,
        PEEF.OBJECT_VERSION_NUMBER,
        PIVF.INPUT_VALUE_ID,
        VAL.SCREEN_ENTRY_VALUE,
        UPPER(PIVF.NAME)    INPUT_VALUE
    FROM
        PAY_INPUT_VALUES_F          PIVF,
        PAY_ELEMENT_ENTRIES_F       PEEF,
        PAY_ELEMENT_ENTRY_VALUES_F VAL
    WHERE
        PEEF.ELEMENT_ENTRY_ID       =   VAL.ELEMENT_ENTRY_ID
    AND PEEF.EFFECTIVE_START_DATE   =   VAL.EFFECTIVE_START_DATE
    AND PEEF.EFFECTIVE_END_DATE     =   VAL.EFFECTIVE_END_DATE
    AND PIVF.ELEMENT_TYPE_ID        =   PEEF.ELEMENT_TYPE_ID
    AND PIVF.INPUT_VALUE_ID         =   VAL.INPUT_VALUE_ID
    AND NVL(PEEF.ATTRIBUTE1,'N')    =   'N'
    AND UPPER(PIVF.NAME)            =   'EFFECTIVE DATE'
    AND PEEF.ELEMENT_TYPE_ID        IN   ('676' , '677' )   --Casual Leave First Half , Casual Leave Second Half
    AND PEEF.ASSIGNMENT_ID          =   P_ASSIGNMENT_ID
    AND PEEF.ELEMENT_TYPE_ID        =   P_ELEMENT_TYPE_ID;

BEGIN

   fnd_file.put_line(fnd_file.output, 'Assignment Number | Element Name | Status' );

   FOR REC IN DAYS_CUR LOOP

      BEGIN

            PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
            P_VALIDATE                => FALSE,
            P_DATETRACK_UPDATE_MODE   => 'CORRECTION',
            P_EFFECTIVE_DATE          => SYSDATE, --L_EFFECTIVE_DATE,
            P_BUSINESS_GROUP_ID       => L_BUSINESS_GROUP_ID,
            P_ELEMENT_ENTRY_ID        => REC.ELEMENT_ENTRY_ID,
            P_OBJECT_VERSION_NUMBER   => REC.OBJECT_VERSION_NUMBER,
            P_INPUT_VALUE_ID1         => REC.INPUT_VALUE_ID,
            P_ENTRY_VALUE1            => NVL(REC.SCREEN_ENTRY_VALUE,0)/2,
            P_EFFECTIVE_START_DATE    => REC.EFFECTIVE_START_DATE,
            P_EFFECTIVE_END_DATE      => REC.EFFECTIVE_END_DATE,
            P_UPDATE_WARNING          => L_UPDATE_WARNING);

               FOR D_REC IN DATE_CUR(REC.ASSIGNMENT_ID , REC.ELEMENT_TYPE_ID) LOOP

                  BEGIN

                    PAY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY (
                        P_VALIDATE                => FALSE,
                        P_DATETRACK_UPDATE_MODE   => 'CORRECTION',
                        P_EFFECTIVE_DATE          => SYSDATE, --L_EFFECTIVE_DATE,
                        P_BUSINESS_GROUP_ID       => L_BUSINESS_GROUP_ID,
                        P_ELEMENT_ENTRY_ID        => D_REC.ELEMENT_ENTRY_ID,
                        P_OBJECT_VERSION_NUMBER   => D_REC.OBJECT_VERSION_NUMBER,
                        P_INPUT_VALUE_ID1         => D_REC.INPUT_VALUE_ID,
                        P_ENTRY_VALUE1            => SYSDATE,
                        P_EFFECTIVE_START_DATE    => D_REC.EFFECTIVE_START_DATE,
                        P_EFFECTIVE_END_DATE      => D_REC.EFFECTIVE_END_DATE,
                        P_UPDATE_WARNING          => L_UPDATE_WARNING);

                  END;

               END LOOP;

        UPDATE
            PAY_ELEMENT_ENTRIES_F       PEEF
        SET
            PEEF.ATTRIBUTE1         =   'Y'
        WHERE
            PEEF.ASSIGNMENT_ID      =   REC.ASSIGNMENT_ID
        AND PEEF.ELEMENT_ENTRY_ID   =   REC.ELEMENT_ENTRY_ID
        AND PEEF.ELEMENT_TYPE_ID    =   REC.ELEMENT_TYPE_ID;

        COMMIT;

        fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME || ' | Successfully Updated.' );
            DBMS_OUTPUT.PUT_LINE ('Element Entry has been Updated: ' || REC.ELEMENT_ENTRY_ID);
      EXCEPTION
         WHEN OTHERS
         THEN

        fnd_file.put_line(fnd_file.output, REC.ASSIGNMENT_NUMBER || ' | ' || REC.ELEMENT_NAME || ' | Error Occured.'||sqlerrm );

      END;

   END LOOP;

EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line(fnd_file.output,'Error Occured While Executing Procedure : ' || SQLERRM);

END;

Oracle EBS Create Element Entry Procedure

CREATE OR REPLACE PROCEDURE APPS.MNJ_CREATE_ELEMENT_ENTRY( ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2,P_MONTH VARCHAR2, P_PAYROLL_ID NUMBER ,P_ELEMENT_ID NUMBER ,P_INPUT_VALUE_ID NUMBER, P_VALUE VARCHAR2)

IS

   LN_ELEMENT_LINK_ID                   NUMBER;
   LD_EFFECTIVE_START_DATE              DATE;
   LD_EFFECTIVE_END_DATE                DATE;
   LN_ELEMENT_ENTRY_ID                  NUMBER;
   LN_OBJECT_VERSION_NUMBER             NUMBER;
   LB_CREATE_WARNING                    BOOLEAN;
   LN_INPUT_VALUE_ID                    NUMBER;
   LN_SCREEN_ENTRY_VALUE                VARCHAR2(60);
   LN_ELEMENT_TYPE_ID                   NUMBER;

CURSOR C_EMP IS

SELECT
    PPF.LAST_NAME,
    PPF.PERSON_ID,
    PAAF.ASSIGNMENT_ID,
    PAAF.ASSIGNMENT_NUMBER,
    PAAF.PAYROLL_ID
FROM
    PER_PEOPLE_F            PPF,
    PER_ALL_ASSIGNMENTS_F   PAAF
WHERE
    PPF.PERSON_ID       =   PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG   =   'Y'
AND LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')) BETWEEN PPF.EFFECTIVE_START_DATE     AND PPF.EFFECTIVE_END_DATE
AND LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')) BETWEEN PAAF.EFFECTIVE_START_DATE    AND PAAF.EFFECTIVE_END_DATE
--AND PPF.EMPLOYEE_NUMBER =   '10595'
AND PAAF.PAYROLL_ID     =   P_PAYROLL_ID;

BEGIN

        LN_ELEMENT_LINK_ID := -1;

                fnd_file.put_line(fnd_file.output, 'Assignment Number | Status' );

        FOR CUR IN C_EMP LOOP

        -- Get Element Link Id
        ----------------------

            Begin

                LN_ELEMENT_LINK_ID :=   HR_ENTRY_API.GET_LINK
                                                            (
                                                             P_ASSIGNMENT_ID   => CUR.ASSIGNMENT_ID,
                                                             P_ELEMENT_TYPE_ID => P_ELEMENT_ID,
                                                             P_SESSION_DATE    => LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY'))
                                                            );

                DBMS_OUTPUT.PUT_LINE( 'Element Link Id: ' || LN_ELEMENT_LINK_ID );

            Exception When Others Then

                LN_ELEMENT_LINK_ID :=   -1;

                fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Unable to find element link.' );

            End;

            IF      LN_ELEMENT_LINK_ID > 0 THEN

                Begin

                    -- Create Element Entry
                    -- ------------------------------
                    PAY_ELEMENT_ENTRY_API.CREATE_ELEMENT_ENTRY
                     (     -- INPUT DATA ELEMENTS
                           -- -----------------------------
                           P_EFFECTIVE_DATE                 => LAST_DAY(TO_DATE(P_MONTH,'MON-YYYY')),
                           P_BUSINESS_GROUP_ID              => FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'),
                           P_ASSIGNMENT_ID                  => CUR.ASSIGNMENT_ID,
                           P_ELEMENT_LINK_ID                => LN_ELEMENT_LINK_ID,
                           P_ENTRY_TYPE                     => 'E',
                           P_INPUT_VALUE_ID1                => P_INPUT_VALUE_ID,
                           P_ENTRY_VALUE1                   => P_VALUE,
                           -- OUTPUT DATA ELEMENTS
                           -- --------------------------------
                           P_EFFECTIVE_START_DATE           => LD_EFFECTIVE_START_DATE,
                           P_EFFECTIVE_END_DATE             => LD_EFFECTIVE_END_DATE,
                           P_ELEMENT_ENTRY_ID               => LN_ELEMENT_ENTRY_ID,
                           P_OBJECT_VERSION_NUMBER          => LN_OBJECT_VERSION_NUMBER,
                           P_CREATE_WARNING                 => LB_CREATE_WARNING
                     );

                    fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Successfully Created.' );
                    dbms_output.put_line( 'Successfull - Element Entry Id: ' || ln_element_entry_id );

                    COMMIT;

                    LN_ELEMENT_LINK_ID := -1;

                Exception When Others Then

                    LN_ELEMENT_LINK_ID := -1;
                    fnd_file.put_line(fnd_file.output, CUR.ASSIGNMENT_NUMBER ||' | Error Occured While Element Entry.' );

                    Rollback;

                End;

            END IF;

        END LOOP;

Exception When Others Then

    dbms_output.put_line( 'Error : ' || sqlerrm );
    fnd_file.put_line(fnd_file.output, 'Error : ' || sqlerrm );

End;
/

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

Here you go