Continuous Caluculation

Continuous calculations  enable to distribute payroll processing throughout a payroll period. we can run payroll process like payroll run,pre payments and costing for static employee data.


Continuous calculations works by using Triggers.Define triggers and events which inform the system when data has changed.When employee receives a sal increment the corresponding event is triggered.The identifies these triggers and marks the corresponding payroll processes to be repeated.


 A change an emp data is trigger for the continuous calculations process these three types.


Affect costing
Affect prepayments
Affect all three continuous calculation processes like payroll run,prepayments and costing


Continuous calculation recalculates all assignments that have run,prepayments and costing with status other then complete.

Retro Pay and Types

Retro Pay:
   Retro pay is used to employee earnings to altered to  retrospectively
   used to making adjustments to employee earnings
   it can effect group of employees or individual employee


Type: 
 There three types of Retro Pay's
1.Retro pay by Aggregate 
   combines all adjustments  into one entry for each balance


2.Retro pay by run

  user able to see how adjustments are distributed across process


3.Retro pay by Element
    when you enabling retro pay by element , Retro costing also available


 it allows payroll to reprocess retrospective changes and stores  the results at element entry level . Retro pay by Element also called as Enhanced Retro Pay.

Balances in Payroll

Balances are used to store values of various elements over a time period of time.You can use balances to record calculation of  employee sick leaves over a time period. Balances are fed by elements or classifications . You can't use combination of both . You must use one of them.


When you defining  the method of feeding to balances can also define whether the run results should add or subtract.


Balances can exist in different dimensions like current run, period to date ,Quarter to date and year to date.


You can use balances at person level or assignment level. Employees more then one assignment at time can have balances stored at person level. But, Individual balances stores at assignment level


You can adjust employee earnings , deductions or tax balances at any time during . You can also rollback or retry balances  for an  assignment  or payroll without retrying or rolling back entire payroll action.


Balance tables

1.                pay_defined_balances 
2.                pay_balance_types_tl 
3.                pay_balance_types 
4.                pay_balance_dimensions

Batch Element Entries(BEE)

Batch Element Entry is open interface  specifically designed for elements. it enables you to make  mass updates for assignments and assignment sets for specific elements.


using BEE allow you make quick changes to assignment element data. With BEE you can generate new batches using default values . The data will be validated before transferring to the BEE Tables.


Data Entry Using BEE Records Timecard Entry,One time payments (Bonus, travel reimbursement) and recurring entries .


 A Batch is made up of batch headers and batch lines . A batch header can have up to 20 lines.can also use Batch Element Entry Spreadsheet loader to manage the uploads.


you can protect unauthorized updates using following profile option 


"HR:Enable Security for Batch Element Entry to Y"


The batch status depends on status of Batch header


1)Valid
2) Transferring
3)Transfer Incomplete
4)Unprocessed
5)Error
6)Status Mismatch

Oracle Payroll

Oracle Payroll


Oracle payroll is nothing 


1) Group of employees
2) Assignment Sets
3) Consolidation Sets


Payrolls are about grouping employees into categories how they are paid in oracle.
Assignment sets to process employees in sub-groups Ex: Monthly pay, Weekly Pay
Consolidation set is used to group results for post-Processing


Processing:
Batch Submission and Quick pay options for processing pay for a group of employees or individual. Quick pay option  used when en employee Terminated.


Quick Pay:


Quick pay option is used when employee leaving organization , and need to process separately from the remaining payroll, You can use quick pay to make simple calculations and rollback the process for that employee .


Retro Pay:
Allows you to back pay employees in the current  payroll run 


Advanced Pay:
Allows you to pay employees in advance in the current payroll run. Both advance pay and Retro pay must be run before process the payroll.

SQLPLUS Script in UNIX Shell Produce Output on Same Line

A Simple Select statement is executed from CURRENT a shell. The value returned by the select query is transferred back to the shell.

For example:

RESULT=`sqlplus -s apps/WELCOME123@DEVPR12 < <(printf "set pages 0 \n select SYSDATE from dual; \n")`
echo "RESULT=${RESULT}"

Another Way to Execute sqlplus from unix shell

sqlplus -s /nolog << EOF
connect apps/Welcome123
select sysdate from dual;
exit;
EOF

Groovy Support in ADF

Groovy Support in ADF

Groovy is as an agile dynamic language, Oracle JDeveloper and Oracle ADF 11g introduce support for the Groovy scripting language, allowing you Groovy expressions to be used in attribute validation and as a source for attribute values Groovy need to have a minimum java version 1.5.Groovy comes into its own is within Oracle ADF Business Components is helping to bridge the gap between Oracle ADF's declarative business logic,Groovy supports 99% of java syntax it's so easy to paste some java code into groovey programs

 

Oracle ADF provides a number of different declarative points into which you can drop Groovy code:

 

• Values for view and entity objects attributes
• Validation rules on entity object attributes
• Expressions for error messages Oracle White Paper-Introduction to Groovy support in JDeveloper and Oracle ADF 11g.
• Values for view object bind variables
• View object transient attribute validation

Groovy Expressions in ADF Business Components View Objects

 

ADF Business Components view objects also provide the ability to use Groovy expressions. As with entity objects, view objects can support:
• Referencing attributes in the same view object
• Referencing attributes in other view objects
• Transient attribute validation Oracle White Paper-Introduction to Groovy support in JDeveloper and Oracle ADF 11g.
• Referencing methods in the Java class that backs the view object
• Referencing built in calls such as sum and min
View objects also allow Groovy expressions to be used in bind variables

.

Things to be aware of

 

· Semicolons are optional. Use them if you like (though you must use them to put several statements on one line).

· the return keyword is optional.

· You can use this keyword inside static methods (which refers to this class).

· Methods and classes are public by default.

· Protected in Groovy has the same meaning as protected in Java, i.e. you can have friends in the same package and derived classes can also see protected members.

· Inner classes are not supported at the moment. In most cases you can use Closures instead.

· The throws clause in a method signature is not checked by the Groovy compiler, because there is no difference between checked and unchecked exceptions.

· You will not get compile errors like you would in Java for using undefined members or passing arguments of the wrong type.

Some Examples:

Java code

 

class  HelloWorld {

private final Thing thing = new Thing ( ) ;

   {

          thing.doSomething ( ) ;

   }

}

 

Groovy Code

class HelloWorld {

private final thing = new Thing ( )

{

      thing.doSomething ( )

}

}

Semicolons is optional in Groovy

 

A = 10

println x

 

A = new java.util.Date()

println A

 

A = -3.1499392

println A

 

A = false

println A

 

A= "Hi"

println A

println is equalant to System.out.println

 

 

The first line java import tells to groovy the full name of sql object 

The second line creates SQL DB connection and stores in variable
Third line calls "eachRow" Method of sql and passing two parameters

1.Query String is first param
2.printing some sql result values
The closure the fields of "it" are accessed in two different ways. The first is as a simple field reference, accessing the id field of it. The second is the included Groovy expression mentioned above

 

Who will Benefit

Who don't already know java and who don't want learn java they can use Groovy to use power of JVM and Development kit while programming

conclusion

 

Java is Groovy,Groovy is Java

1.99% java code supports Groovy
2.Groovy supports JSR 175 annotation same as Java
3.Increases developer productivity by reducing scaffolding code when developing web, GUI ,database or console applications
4.Compiles straight to Java byte code so you can use it anywhere you can use Java

 

 

Source from: http://groovy.codehaus.org and oracle.com

Toad start-up Script

Save below script in c:\toad.startup.sql


BEGIN
INSERT INTO FND_SESSIONS
SELECT USERENV('SESSIONID'), TRUNC( SYSDATE )
FROM DUAL;
COMMIT;
END;
/
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO( 101 );
END;
/
BEGIN
FND_GLOBAL.SET_NLS_CONTEXT (P_NLS_LANGUAGE => 'AMERICAN');
COMMIT;
END;
/
---End of script


open toad then click on view-> Toad options->schema browser->Startup(As shown below)

click on apply and restart the toad, The above script files executed automatically and set to language and org information to your toad session.


If you want execute above script for every new connecton call same file in " File to execute on new connections: c:\toad_startup.sql "

SQL connect Tip



I am lazy to make Tns entry in TNSNAMES.ORA file
But, still you can connect sqlplus session using following tip
make the tns entry as shown below , First  open sqlplus connect window then follow below steps


Syntax:


(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xyzabc.com)(PORT=1521))(CONNECT_DATA=(SID=Devep11i)))


Username : apps
Password : apps
Host string : < paste above tns entry here>

click on ok , it will connect to specified dbserver with making entry in TNSNAMES.ORA 

Linux server auto connect using Putty


if you want auto connect to specific server using putty here is some tips


First locate "putty.exe"  then right click on "putty.exe" goto properties-> short cut


Consider below syntax as example
in target field enter below syntax


C:\putty.exe -ssh  "user_name"@"servername"  "port_number"  -pw  "Password"

1. -ssh is connection type
2. -pw is password
3. -port number (By default is 22 for ssh)

C:\putty.exe -ssh ABCABC@devUnix100.com 22 -pw Welcome123!

click on Apply then double click on "putty.exe"  it will connect you server without password

Be careful it's not good practice to keep your password at file properties :)

comparison ADF vs OAF

Oracle recommendations 

If you want to integrate your extensions tightly with the E-Business Suite,
you should use the OAF Release 12 technology stack, which includes the
use of JDeveloper 10g and ADF BC (or use OAF Release 11i, which uses
JDeveloper 9i).


 If you are building a separate application that does not integrate tightly
with the E-Business Suite, but needs E-Business Suite-specific capabilities
like Flexfields and personalization, you should use the OAF Release 12
technology stack. If your application does not need to integrate with the
E-Business Suite, or doesn't require any of the E-Business Suite-specific
capabilities enumerated above, you should use ADF 10g with SOA based
integration with E-Business Suite.


 If you are building a separate application that does not need to integrate at
all with the E-Business Suite, and you need an AJAX-style rich client user
interface, you should use ADF 11g.

Source : oracle support

inv item categories and iproc item categories

Below query gives Mapping between inv item categories and iproc item categories using mapping table ICX_POR_CATEGORY_ORDER_MAP

SELECT
MC . SEGMENT1 ,
MC . DESCRIPTION ,
MCT . CATEGORY_ID ,
ICCT . CATEGORY_NAME ,
ICCT . RT_CATEGORY_ID
FROM 
MTL_CATEGORIES_TL MCT ,
MTL_CATEGORIES_KFV MCK ,
ICX_POR_CATEGORY_ORDER_MAP IPCO ,
ICX_CAT_CATEGORIES_TL ICCT ,
MTL_CATEGORIES MC ,
MTL_CATEGORY_SET_VALID_CATS mcsv
WHERE MCT . CATEGORY_ID = MCK . CATEGORY_ID
AND MCT . CATEGORY_ID = MC . CATEGORY_ID
AND mct . CATEGORY_ID = mcsv . CATEGORY_ID
AND IPCO . EXTERNAL_SOURCE_KEY = MCK . CATEGORY_ID
AND ICCT . RT_CATEGORY_ID = IPCO . RT_CATEGORY_ID

Script to Converting BLOB to CLOB

BLOB to CLOB

step-1) create temp table
step-2) prepare conversion script
step-3) execute script to store converted CLOB to temp table


Table creation


CREATE TABLE
APPS . XXNR_XML_DATA_TEMPLATE_TEMP (
TEMPLATE_CODE VARCHAR2 ( 200 BYTE),
DATA_SOURCE_CODE VARCHAR2 ( 200 BYTE),
FILE_NAME VARCHAR2 ( 200 BYTE),
FILE_DATA CLOB
);


Conversion Script


CREATE OR REPLACE PROCEDURE XX_BLOB_RAMA
IS
v_clob CLOB ;
v_varchar VARCHAR2 ( 32767 );
v_start PLS_INTEGER := 1 ;
v_buffer PLS_INTEGER := 32767 ;

BEGIN
DBMS_OUTPUT.put_line ( 'starting blob to clob conversion' );
alter session set nls_language= 'AMERICAN' ;

FOR x
IN (SELECT xtv . template_code ,
xdd . DATA_SOURCE_CODE ,
xl . LOB_CODE ,
xl . file_data ,
xl . FILE_NAME
FROM XDO_TEMPLATES_VL xtv , XDO_DS_DEFINITIONS_VL xdd , XDO_LOBS xl
WHERE xdd . DATA_SOURCE_CODE = xtv . DATA_SOURCE_CODE
AND xl . LOB_CODE = xtv . DATA_SOURCE_CODE
AND lob_type = 'DATA_TEMPLATE'
AND xl . APPLICATION_SHORT_NAME IN ( 'XXPEP' , 'XXABC' ))
LOOP
BEGIN

DBMS_OUTPUT.put_line ( 'prccessing DATA_SOURCE_CODE ' || x . DATA_SOURCE_CODE );
v_start := 1 ;
v_clob := NULL;
v_varchar := NULL;
DBMS_LOB.CREATETEMPORARY ( v_clob , TRUE);
FOR i IN 1 .. CEIL ( DBMS_LOB.GETLENGTH ( x . file_data ) / v_buffer )

LOOP
v_varchar :=
UTL_RAW . CAST_TO_VARCHAR2 (
DBMS_LOB.SUBSTR ( x . file_data , v_buffer , v_start ) );
DBMS_LOB.WRITEAPPEND ( v_clob , LENGTH ( v_varchar ), v_varchar );
v_start := v_start + v_buffer ;
END LOOP;

INSERT INTO XXNR_XML_DATA_TEMPLATE_TEMP
VALUES ( x . TEMPLATE_CODE ,
x . DATA_SOURCE_CODe ,
x . FILE_NAME ,
v_clob );
COMMIT;

DBMS_OUTPUT.put_line ( 'DATA_SOURCE_CODE ' || x . DATA_SOURCE_CODE || ' processed.' );
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;
END LOOP;
END XX_BLOB_RAMA ;


Finally execute the script


BEGIN
XX_BLOB_RAMA ;
END;


see the below script result in custom table  XXNR_XML_DATA_TEMPLATE_TEMP

Alert History Query

Query to get alert history

SELECT
alv . *
FROM ALR_ALERTS al ,
ALR_ACTION_HISTORY aah ,
ALR_OUTPUT_HISTORY aoh ,
ALR_ACTIONS_V alv ,
alr_alert_historY_view aahv
WHERE al . alert_name = <name of alert > ---------'XXPEP_PENDING_APPROVALS'
AND al . alert_id = aah . alert_id
AND aah . check_id = aoh . check_id
AND alv . ALERT_ID = aah . alert_id
AND aahv . alert_name = al . alert_name;

Oracle Application Framework (OAF) vs  Application Development Frame work (ADF)


ADF :

1. J2EE enabled Frame work
2. ADF Uses JSF
3. Design time page Editor
4. Minimal Java Coding
5. Declarative controller
6. Rapid Development
7. Support MVC Architecture
8. Deployed in Web logic server
9. ADF is open source
10. Support web service and SOA Service

OAF:

1. Oracle application Frame Work
2. OAF uses UIX
3. No UI page Editor
4. Complex Java coding
5. Support MVC Architecture
6. Deployed in Oracle  Application Server
7. Development is more complex

Linux/Unix echo colour coding 








Below colour code statements are very useful when your coding installation script in Linux/ UNIX environments

echo -e  "\033[1;30m";        

echo Dark Gray

echo -e "\033[0;30m";

echo Black

echo -e "\033[1;31m";

echo Light RED

echo -e "\033[0;32m";

echo Light Green

echo -e "\033[0;33m";

echo Brown

echo -e "\033[1;33m";

echo Yellow

echo -e "\033[0;34m";

echo blue

echo -e "\033[1;34m";

echo light blue

echo -e "\033[0;35m";

echo purple

echo -e "\033[0;36m";

echo Cyan

echo -e "\033[1;36m";

echo Light Gray

echo -e "\033[1;37m";

echo White

echo -e "\033[0m";

echo Neutral

Payroll Tables

Payroll Tables


Below are the basic main tables between HR and Payroll


1) Pay_all_payrolls_f-> Payroll_id  linked with PER_ALL_ASSIGNEMNTS_F.Payroll_id
2)Pay_personal_payment_methods_F->Assignment_id linked with PER_ALL_ASSIGNEMNTS_F.Assignment_id
3)PAY_ORG_PAYMENT_METHODS_F->Payment_type_id linked with   pay_payment_types.Payment_type_id
4) PER_ALL_ASSINMENTS_F->Assignment_id linked with PAY_ELEMENT_ENTRIES_F.Assignment_id
5)PAY_ELEMENT_TYPES_F->ELEMENT_LINK_ID linked with  PER_ELEMENT_LINKS_F.ELEMENT_LINK_ID 
6)PAY_ELEMENT_TYPES_F-> element_type_id linked with pay_input_values_f.element_type_id 
7)HR_ALL_POSITIONS_F->position_id  linked with PAY_ELEMENT_TYPES_F.Position_id

Positions in Oracle HRMS


Positions are used to define employee roles within Oracle Human Resources.position is a specific occurrence of one job, fixed within one organization,Positions are independent of the employees

A position will need to be defined for every unique combination of:

·   Job

·   Organization

·   Reporting To Position Hierarchy

·   Valid Grades (Valid Grades to which incumbents are assigned)

·   Position Requirements (Required qualifications or valid experience)

·   Position Evaluation (Evaluation information and overall evaluation score for the Position)

·   Position Key Flex Field (Name Field components, such as Position Title, Position ID or other client defined keys)

·   Position Successor

·   Probation Periods (To define the length of the Probation Period for incumbents holding this position)

Advantages:

 Position definition with no override attributes, ensures derivation from the position.  It is more accurate because the definition focuses on the position and is not affected by the employee in the position

  Position attributes change less often than employee movement.  When the position attributes change, the system automatically updates incumbent records with the new value. 

Types of Positions

Pooled:  This approach is very good for organizations where groups of people are doing the same work (many employees assigned to one position), have the same reporting relationship (predominate in manufacturing and transportation industries).  This approach allows multiple people to occupy a single position that has the same attributes and reporting relationship.

Shared: This approach supports the ability to assign employees to several part-time positions.  This approach is becoming more common.  In some companies, an employee works part-time (20 hours) in one department and then part-time in another department.  In essence the company divides the employee and distributes the cost across the two departments.  The company benefits from only having to pay benefits to one person.

Single Incumbent: This approach is usually used for positions, which are managerial or at least static.  This approach is usually needed for those positions, which will have spending authority levels, and defined succession planning.  This approach assumes on position per person

Table Information

1)PER_POSITION_DEFINITIONS

This is a key flexfield combinations table.  It stores segment combinations for positions that are stored in the HR_ALL_POSITIONS_F table.

2)HR_ALL_POSITIONS_F

This table holds datetracked position definitions.

Column POSITION_DEFINITION_ID links this table to PER_POSITION_DEFINITIONS to identify the segment values.
This table was introduce in R11i and prior to that the non-datetracked table PER_ALL_POSITIONS was used.

3)PER_POSITION_EXTRA_INFO

This table holds details of extra information for a position.

4)PER_POSITION_INFO_TYPES

This table holds the definitions of extra information types that may be held against a position.

4)PER_POSITION_LIST

Holds the list of positions that can be accessed by a specific security profile.
The rows in this table are generated by the 'Security List Maintenance' process.

5)PER_POSITION_STRUCTURES

Holds information about position hierarchies defined for each Business Group.

6)PER_ALL_POSITIONS

PO still uses this table and a concurrent request 'Synchronize Positions Process' is available to keep the two tables in synch.

APIs

1)hr_position_api (peposapi.pkh)

   create_position

  delete_position

  update_position

2)hr_position_extra_info_api (pepoiapi.pkh)

create_position_extra_info

delete_position_extra_info

update_position_extra_info

3)hr_position_requirement_api (pepsrapi.pkh)

   create_position_requirement

   per_position_structure_api (pepstapi.pkh)

   create_pos_struct_and_def_ver

   create_position_structure

   delete_position_structure

   update_position_structure

4)per_pos_structure_version_api (pepsvapi.pkh)

  create_pos_structure_version

  delete_pos_structure_version

  update_pos_structure_version

5)hr_pos_hierarchy_ele_api (pepseapi.pkh)

  create_pos_hierachy_ele

  delete_pos_hierachy_ele

  update_pos_hierachy_ele

Portable Benthic Cracked PL EDIT Software

download from below link

http://www.megaupload.com/?d=U8KWOWO0

Link for Etrm Download

Using below link you can download Etrms

http://download.oracle.com/docs/cd/A89549_01/html/trmset.html

fnd_concurrent.set_completion_status

By using following API you can manually warn out the oracle Reports in oracle applications.

Call This API in after report trigger in oracle reports 6i and see the result.

g_flag := fnd_concurrent.set_completion_status

('WARNING',

'Any Message...'

);

Run reports in oracle applications concurrent process.

HRMS PYUPIP

PYPIP is a utility used for tracing the flow of HRMS PL/SQL .The database trace pipe allows the user to record the flow of HRMS PL/SQl code.

The PL/SQL code can reside in a script or in package its displays all different break points when the code is executed.

PYPIP can be launched in multiple ways

1) From Unix version

2)Self service applications

3)The professional USer Interface(PUI).

Requisition Preferences

You can setup some of PO Requisition fields defaults for

• Need By Date
• charge account
• Justification
• Notes to receiver/Buyer
• Requestor
• Organization
• Ship to Location
• Taxable
• Destination Type
• Project Information

Note : You can Override the charge account number when destination type is Expense.

PO Requisition cancellation Rules

You can cencel PO requisition that have been submitted for approval from requisition status Page.

But You must follow the Rules.

1)Cancel Requisition of any status.

2)cannot support cancelling individual requisition lines.

3)cannot cancel requisition once it has a line converted to a Purchase Order.

Iproc Product Features

• Standard Web shopping experience
• Complete catalog and content management
• Next Generation catalog engine
• Streamlined requisition process
• View and respond your notifications
• View company latest requisition policies
• Respond to the Requisitions

CUSTOM.PLL

It is mechanism that allows extension of oracle applications without modification of oracle apps code.

Custom.pll let you change properties ,functionality and operation of forms.

How it works

it works by sending events from each oracle applications form to the CUTOM library, which is automatically attached at runtime.

Details

The custom Library is availble within oracle applications that are developed in oracle forms.

The Custom library is actually a pair of files called CUSTOM.pll and CUSTOM.plx.

The plx version is a compiled version of .pll file.

Library Rules

Since there is only a single library that is shared by all forms in installation

• You can't use SQL in the CUSTOM library except server side packages instead.
• you can't change the specification of the CUSTOM package
• you can't attach the PCORE library to the CUSTOM library
• Always use FNDSQF library for function security flexfields and message procedures.
• Don't use CALL_FORM or OPEN_FORM within the CUSTOM library, Use FND_FUCTION package instead
• You can switch on and off dynamically by the user selecting Help-> tools->Custom code-> off option.
• You can prevent users from being able to switch off the CUSTOM library by setting profile option Diagnostics to No

Usage of CUSTON library

There are four main ways to use the CUSTOM library .

ZOOM : A zoom opens another from and can pass parameters to opened form.

Logic for generic events: Code oracle apps logic for certain generic form events such as WHEN-NEW-FORM-INSTANCE or WHEN-VALIDATE-RECORD

Logic for product-Specific events: code or replace oracle apps logic for certain product-specific events

Custom entries for the Special menu: Add entries to the Special menu for oracle Apps forms.

Note

The CUSTOM library provides a non-invasive mechanism that allows customers to extend the application in a manageable and controlled environment