# Character to ASCII
printf "%d\n" "'A"
## ASCII number to character
awk -v char=65 'BEGIN { printf "%c\n", char; exit }'
Thursday, June 17, 2010
In a directory in Unix convert Tab Delimeted file to Comma delimeted
cd /applmgr/custom/inbound/data
for i in *.xls
do
echo $i
newfile=$i.csv
awk 'BEGIN {
FS = "\t"
OFS = ","
}
{ $1 = $1
for (i = 1; i <= NF; i++) { if ($i == "") { $i = "null" } } print $0 }' $i > $newfile
done
for i in *.xls
do
echo $i
newfile=$i.csv
awk 'BEGIN {
FS = "\t"
OFS = ","
}
{ $1 = $1
for (i = 1; i <= NF; i++) { if ($i == "") { $i = "null" } } print $0 }' $i > $newfile
done
Saturday, April 3, 2010
sql query, from the string ( 'abcdaefgahj' ), where I have to change the 3rd occurance of 'a' with replaced by 'o'. output will be look like ('abcdaefgohj')
SELECT REGEXP_REPLACE('abcdaefgahj', 'a','o', 1, 3) RESULT
FROM dual;
FROM dual;
Inventory Generate Cost of Goods Sold Account
There is one extension package available for this purpose in costing. package name should be CSTPSCHK and function namestd_get_account_id. We put the logic for deriving the COGS account in this function and this function will return the derived COGS account id.
Create Link Host File
Step I : Login through applmgr
Step II : Run below script
ln –s $FND_TOP/$APPLBIN/fndcpesr $Custom_TOP/bin/xx_host_file.prog
Step III : Check link has created or not.
Step II : Run below script
ln –s $FND_TOP/$APPLBIN/fndcpesr $Custom_TOP/bin/xx_host_file.prog
Step III : Check link has created or not.
Remove the database objects from schema
----------------------------------------------------------------------------
-- Script to delete DB objects (XXPA)
----------------------------------------------------------------------------
declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='XXPA'
and object_name like 'XX/_%' escape '/'
order by object_type;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SEQUENCE' then
l_stmt:= ' DROP SEQUENCE '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
----------------------------------------------------------------------------
-- Script to delete DB objects (APPS)
----------------------------------------------------------------------------
declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='APPS'
and object_name like 'XX/_PA/_%' escape '/'
order by object_type;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
----------------------------------------------------------------------------
-- Revoke script
----------------------------------------------------------------------------
declare
cursor c1 is
select distinct table_name
from all_tab_privs
where grantor ='XXPA';
l_tab_name all_tab_privs.table_name%TYPE;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
l_tab_name := r1.table_name;
l_stmt := 'REVOKE ALL ON '||l_tab_name||' FROM APPS ;';
execute immediate l_stmt;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
-- Script to delete DB objects (XXPA)
----------------------------------------------------------------------------
declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='XXPA'
and object_name like 'XX/_%' escape '/'
order by object_type;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SEQUENCE' then
l_stmt:= ' DROP SEQUENCE '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
----------------------------------------------------------------------------
-- Script to delete DB objects (APPS)
----------------------------------------------------------------------------
declare
cursor c1 is
select object_type,
object_name
from all_objects
where owner='APPS'
and object_name like 'XX/_PA/_%' escape '/'
order by object_type;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
if r1.object_type='FUNCTION' THEN
l_stmt:= ' DROP FUNCTION '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='SYNONYM' then
l_stmt:= ' DROP SYNONYM '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='PROCEDURE' then
l_stmt:= ' DROP PROCEDURE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='TABLE' then
l_stmt:= ' DROP TABLE '||r1.object_name;
execute immediate l_stmt;
elsif r1.object_type='VIEW' then
l_stmt:= ' DROP VIEW '||r1.object_name;
execute immediate l_stmt;
else
l_stmt:= ' DROP PACKAGE '||r1.object_name;
execute immediate l_stmt;
end if;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
----------------------------------------------------------------------------
-- Revoke script
----------------------------------------------------------------------------
declare
cursor c1 is
select distinct table_name
from all_tab_privs
where grantor ='XXPA';
l_tab_name all_tab_privs.table_name%TYPE;
l_stmt varchar2(1000);
begin
for r1 in c1 loop
l_tab_name := r1.table_name;
l_stmt := 'REVOKE ALL ON '||l_tab_name||' FROM APPS ;';
execute immediate l_stmt;
end loop;
exception
when others then
dbms_output.put_line('Unexpected error: '||SQLCODE||' - '||SQLERRM);
end;
Set Org in R12
begin
MO_GLOBAL.set_policy_context('S',82);
end;
============
MO_GLOBAL.set_policy_context('S',org_id);
'S' ==> Single org
'M' ==> Multi Org
MO_GLOBAL.set_policy_context('S',82);
end;
============
MO_GLOBAL.set_policy_context('S',org_id);
'S' ==> Single org
'M' ==> Multi Org
XML Publisher functions
add page total
-------------------
xdofx function
-------------------
1000?>red
Repeating frame or loop
------------------------
Placeholder
-------------------
For each alternate row, the background color attribute is set to gray for the row.
--------------------------------------------------
lightgray
If statement to display the row only if the element SALES has a value greater than 5000.
----------------------------------------------
5000?>
The placeholder for the quantity element surrounded by the "if" statement
-----------------------------------------
The opening of the if statement to test for the attribute value "PRIVATE". Note that this syntax
uses an XPath expression to navigate back to the "items" level of the XML to test the attribute. For
more information about using XPath in your templates, see XPath Overview, page 2-121.
------------------------------------------
=====
1000 then 'Higher'
else
if AMOUNT < 1000 then 'Lower' else 'Equal' end if?>
To create links for a dynamic table of contents: Enter the syntax:
--------------------------------------
====
To end on an even page with layout:
-------------------------------------------------
Insert the following syntax in a form field in your template:
-----------------------------------------------------------------
To end on an odd page layout:
-------------------------------
If you do not have layout requirements for the final page, but would like a blank page
ejected to force the page count to the preferred odd or even, use the following syntax:
------------------------------------------------------------------------------------------
or
Last Page Only Layout
--------------------------
Insert the following syntax on the final page:
-------------------------------
Intial Page
-------------------
Page break
--------------
Page Level calculation
------------------------
where TotalFieldName is the name you assign to your total (to reference later) and
'element' is the XML element field to be totaled. You can add this syntax to as many fields as you want to total.
Then when you want to display the total field, enter the following syntax:
--------------------------------------------------------------------------------------------
where
TotalFieldName is the name you assigned to give the page total field above and
Oracle-number-format is the format you wish to use to for the display, using the
Oracle format mask (for example: C9G999D00). For the list of Oracle format mask
Brought Forward/Carried Forward Totals
--------------------------------------------
Brought Forward:
********
Init PTs
Declares "InvAmt" as the placeholder that will hold the page total.FE
Begins the INVOICE group.10001-1
Placeholder for the Invoice Number tag. 1-Jan-2005
Placeholder for the Invoice Date tag.100.00
Placeholder for the Invoice Amount tag.InvAmt
Assigns the "InvAmt" page total object to the INVAMT element in the data.EFE
Closes the INVOICE group.End PTs
Closes the "InvAmt" page total.To
***************
Sorting
-----------------
OR
Checking for Nulls
----------------------
• To define behavior when the element is present and the value is not null, use the following:
----------------------------------------------------------------------------------------------
desired behavior
• To define behavior when the element is present, but is null, use the following:
---------------------------------------------------------------------------------------
desired behavior
• To define behavior when the element is not present, use the following:
--------------------------------------------------------------------------------------
desired behavior
Regrouping the XML Data
----------------------------
For example, to regroup the CD listing by COUNTRY, enter the following in your
template:2-84 Oracle XML Publisher Report Designer's Guide
----------------------------------------------------------------------------------
OR
To establish nested groupings within the already defined group, use the following syntax:
-------------------------------------------------------------------------------------
For example, after declaring the CD grouping by COUNTRY, you can then further group by YEAR within COUNTRY as follows:
----------------------------------------------------------------------------------------------------------------------
**** IMP ******
Group by TmpRng
-----------------
Range
------------
Months
-------------
End TmpRng
-----------------
Using Variables
-------------------
Use the following syntax to declare/set a variable value:
------------------------------------------------------------------
Use the following syntax to retrieve a variable value:
------------------------------------------------------------------
You can use this method to perform calculations. For example:
------------------------------------------------------------------
This sets the value of variable 'x' to its original value plus 1, much like using "x = x +1".
Defining Parameters
------------------------
where
PARAMETERNAME is the name of the parameter
DEFAULT is the default value for the parameter (the select statement is optional)
xdofo:ctx="begin" is a required string to push the parameter declaration to the
top of the template at runtime so that it can be referred to globally in the template.
The syntax must be declared in the Help Text field of a form field. The form field
can be placed anywhere in the template.
Dynamic Data Columns
----------------------------
• Dynamic Column Header
------------------------------------------------
Use this tag to define which group to split for the column headers of a table.
• Dynamic Column
-------------------------
Use this tag to define which group to split for the column data of a table.
• Dynamic Column Width
---------------------------------
or
Use one of these tags to define the width of the column when the width is described
in the XML data. The width can be described in two ways:
• An XML element stores the value of the width. In this case, use the syntax
,
where name is the XML element tag name that contains the value for the width.
• If the element defined in the split-column-header tag, contains a width
attribute, use the syntax
to use the value of that attribute
Group:TestScores
-----------------
Test Category
------------------
Column Header and Splitting
---------------------
%
Content and Splitting
------------------------------
end:TestScores
---------------------
-------------------
xdofx function
-------------------
1000?>
Repeating frame or loop
------------------------
Placeholder
-------------------
For each alternate row, the background color attribute is set to gray for the row.
--------------------------------------------------
If statement to display the row only if the element SALES has a value greater than 5000.
----------------------------------------------
5000?>
The placeholder for the quantity element surrounded by the "if" statement
-----------------------------------------
The opening of the if statement to test for the attribute value "PRIVATE". Note that this syntax
uses an XPath expression to navigate back to the "items" level of the XML to test the attribute. For
more information about using XPath in your templates, see XPath Overview, page 2-121.
------------------------------------------
=====
1000 then 'Higher'
else
if AMOUNT < 1000 then 'Lower' else 'Equal' end if?>
To create links for a dynamic table of contents: Enter the syntax:
--------------------------------------
====
To end on an even page with layout:
-------------------------------------------------
Insert the following syntax in a form field in your template:
-----------------------------------------------------------------
To end on an odd page layout:
-------------------------------
If you do not have layout requirements for the final page, but would like a blank page
ejected to force the page count to the preferred odd or even, use the following syntax:
------------------------------------------------------------------------------------------
or
Last Page Only Layout
--------------------------
Insert the following syntax on the final page:
-------------------------------
Intial Page
-------------------
Page break
--------------
Page Level calculation
------------------------
where TotalFieldName is the name you assign to your total (to reference later) and
'element' is the XML element field to be totaled. You can add this syntax to as many fields as you want to total.
Then when you want to display the total field, enter the following syntax:
--------------------------------------------------------------------------------------------
where
TotalFieldName is the name you assigned to give the page total field above and
Oracle-number-format is the format you wish to use to for the display, using the
Oracle format mask (for example: C9G999D00). For the list of Oracle format mask
Brought Forward/Carried Forward Totals
--------------------------------------------
Brought Forward:
********
Init PTs
Declares "InvAmt" as the placeholder that will hold the page total.FE
Begins the INVOICE group.10001-1
Placeholder for the Invoice Number tag. 1-Jan-2005
Placeholder for the Invoice Date tag.100.00
Placeholder for the Invoice Amount tag.InvAmt
Assigns the "InvAmt" page total object to the INVAMT element in the data.EFE
Closes the INVOICE group.End PTs
Closes the "InvAmt" page total.To
***************
Sorting
-----------------
OR
Checking for Nulls
----------------------
• To define behavior when the element is present and the value is not null, use the following:
----------------------------------------------------------------------------------------------
desired behavior
• To define behavior when the element is present, but is null, use the following:
---------------------------------------------------------------------------------------
desired behavior
• To define behavior when the element is not present, use the following:
--------------------------------------------------------------------------------------
desired behavior
Regrouping the XML Data
----------------------------
For example, to regroup the CD listing by COUNTRY, enter the following in your
template:2-84 Oracle XML Publisher Report Designer's Guide
----------------------------------------------------------------------------------
OR
To establish nested groupings within the already defined group, use the following syntax:
-------------------------------------------------------------------------------------
For example, after declaring the CD grouping by COUNTRY, you can then further group by YEAR within COUNTRY as follows:
----------------------------------------------------------------------------------------------------------------------
**** IMP ******
Group by TmpRng
-----------------
Range
------------
Months
-------------
End TmpRng
-----------------
Using Variables
-------------------
Use the following syntax to declare/set a variable value:
------------------------------------------------------------------
Use the following syntax to retrieve a variable value:
------------------------------------------------------------------
You can use this method to perform calculations. For example:
------------------------------------------------------------------
This sets the value of variable 'x' to its original value plus 1, much like using "x = x +1".
Defining Parameters
------------------------
where
PARAMETERNAME is the name of the parameter
DEFAULT is the default value for the parameter (the select statement is optional)
xdofo:ctx="begin" is a required string to push the parameter declaration to the
top of the template at runtime so that it can be referred to globally in the template.
The syntax must be declared in the Help Text field of a form field. The form field
can be placed anywhere in the template.
Dynamic Data Columns
----------------------------
• Dynamic Column Header
------------------------------------------------
Use this tag to define which group to split for the column headers of a table.
• Dynamic Column
-------------------------
Use this tag to define which group to split for the column data of a table.
• Dynamic Column Width
---------------------------------
or
Use one of these tags to define the width of the column when the width is described
in the XML data. The width can be described in two ways:
• An XML element stores the value of the width. In this case, use the syntax
,
where name is the XML element tag name that contains the value for the width.
• If the element defined in the split-column-header tag, contains a width
attribute, use the syntax
to use the value of that attribute
Group:TestScores
-----------------
Test Category
------------------
Column Header and Splitting
---------------------
%
Content and Splitting
------------------------------
end:TestScores
---------------------
choose_xml or regrouping
Default Text Entry in Example Form Field Help Text Entry in Form Field
End When>
End When>
Regrouping Of data:-
Regrouping Syntax
To regroup the data, use the following syntax:
The elements that were at the same hierarchy level as COUNTRY are now children of
COUNTRY. You can then refer to the elements of the group to display the values
desired.
To establish nested groupings within the already defined group, use the following
syntax:
For example, after declaring the CD grouping by COUNTRY, you can then further
group by YEAR within COUNTRY as follows:
At runtime, XML Publisher will loop through the occurrences of the new groupings,
displaying the fields that you defined in your template.
Note: This syntax is a simplification of the XSL for-each-group syntax.
If you choose not to use the simplified syntax above, you can use the
XSL syntax as shown below. The XSL syntax can only be used within a
form field of the template.
Template Example
The following figure shows a template that displays the CDs by Country, then Year,
and lists the details for each CD:
Default Text Entry Form Field Help Text Entry Description
Group by Country
The
tag declares the
new group. It regroups the
existing CD group by the
COUNTRY element.
USA Placeholder to display the
data value of the COUNTRY
tag.
Group by Year
The
tag
regroups the current group
(that is, COUNTRY), by the
YEAR element.
2000 Placeholder to display the
data value of the YEAR tag.
Group: Details
Once the data is grouped by
COUNTRY and then by
YEAR, the
command is used to
loop through the elements of
the current group (that is,
YEAR) and render the data
values (TITLE, ARTIST, and
PRICE) in the table.
My CD Placeholder to display the
data value of the TITLE tag.
John Doe Placeholder to display the
data value of the ARTIST tag.
1.00 Placeholder to display the
data value of the PRICE tag.
End Group Closes out the
tag.
Oracle HRMS Payroll Concepts
Slide 1: Oracle HRMS Payroll Concepts
Slide 2: Important TablesTable of Contents Payroll Terms Other Related Tables 2 Useful Queries Tables Relationship and Description
Slide 3: Elements are simply thePayroll Terms Element units used to build the earnings, deductions and benefits that can be given to Input Value is like a placeholder for holding thean employee. Input Value actual value of the Element. An element can have more than one input value. Element Link is the one which links the element to employees.Element Link This linking can be done by associating an element to a payroll and then associating that payroll to an employees assignment. An element can also be linked to employee by Employment Category, Grade and Job. 3
Slide 4: ThePayroll Terms (contd.) Element Entry Value Any payroll related actionactual value of the element entry Payroll Action performed on employee’s assignment is referred to as Payroll Action. It can be a This is aPayroll Run, Quick-Pay, Pre-payments or Payments Assignment Action record of actions performed by the Payroll process on each and every assignment. This is the actual calculated result of the payroll process forRun Result each and every element entry 4
Slide 5: Important Tables Per_all_people_f Pay_input_values_f Pay_element_types_f Per_all_assignments_f Pay_element_entry_values_f Pay_element_entries_f Pay_element_links_f 5 Pay_run_results Pay_assignment_actions Pay_payroll_actions
Slide 6: Table Flow Diagram Element and Element Elements Attached to Employees Payroll Processing Link Definition Per_all_people_f Pay_payroll_actions Pay_input_values_f Person_id Payroll_action_id Element_type_id Assignment_id Per_all_assignments_f Pay_assignment_actions Pay_element_types_f Assignment_id Assignment_action_id Element_type_id Element_link_id Source_id Pay_element_links_f Pay_element_entries_f Pay_run_results Element_entry_id Run_result_id Pay_element_entry_values_f Pay_run_result_values 6
Slide 7: Per_all_people_f This table holds personal information for employees, applicants, ex-employees, ex-applicants, contacts and other people. Key Columns PERSON_ID System generated EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date PERSON_TYPE_ID Links to PER_PERSON_TYPES START_DATE Start date for the person CURRENT_APPLICANT_FLAG Yes or null CURRENT_EMP_OR_APL_FLAG Yes or null CURRENT_EMPLOYEE_FLAG Yes or null DATE_OF_BIRTH Date of birth 7
Slide 8: Per_all_assignments_f This table holds information about employee assignments like his Job, Manager, Department, Payroll and his expense account. Key Columns ASSIGNMENT_ID System-generated EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date BUSINESS_GROUP_ID Identifies the Business group JOB_ID Identifies Employees Job ASSIGNMENT_STATUS_TYPE_ID Active or Suspended PAYROLL_ID Identifies the payroll assigned SUPERVISOR_ID Identifies the manager PERSON_ID links to PER_ALL_PEOPLE_F ORGANIZATION_ID Identifies the Cost Center/Dept DEFAULT_CODE_COMB_ID Default Expense Account 8
Slide 9: Pay_element_types_f This table holds the definitions of elements. Elements are the units used to build all the earnings, deductions and benefits that can be given to employees. Key Columns ELEMENT_TYPE_ID System-generated ELEMENT_NAME User name for the element type REPORTING_NAME Reporting Name used in SOE CLASSIFICATION_ID Earnings/Deductions/Information MULTIPLE_ENTRIES_ALLOWED_FLAG Indicates if multiple entries are allowed POST_TERMINATION_RULE Last standard process,final close,actual term 9 PROCESS_IN_RUN_FLAG If the element can be processed
Slide 10: Pay_input_values_f This table holds the definitions of the input values associated with a specific element. Input Value is like a placeholder for storing the actual values of the element Key Columns INPUT_VALUE_ID System-generated NAME Input value name UOM Hours, Money, Date etc USER_ENTERABLE If Users can enter values here directly DATABASE_ITEM If Database item needs to be created (used for FF) ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F 10 EFFECTIVE_START_DATE Effective start date
Slide 11: Pay_element_links_f This table that holds the eligibility rules that link elements to employees. An assignment must match the eligibility criteria defined for the element link before it can have an entry of that element. Key Columns ELEMENT_LINK_ID System-generated PAYROLL_ID Payroll attached to this element COST_ALLOCATION_KEYFLEX_ID To which account this element will be costed against ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F BALANCING_KEYFLEX_ID To which account this element will be costed against COSTABLE_TYPE If the Element can be costed or not TRANSFER_TO_GL_FLAG If the element values can be transfer to GL EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 11
Slide 12: Pay_element_entries_f This table holds the list of all elements that are actually assigned to an employees assignment. Each element is identified by element_link_id Key Columns ELEMENT_ENTRY_ID System-generated ASSIGNMENT_ID Links to PER_ALL_ASSIGNMENTS_F ELEMENT_LINK_ID Links to PAY_ELEMENT_LINKS_F EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 12
Slide 13: Pay_element_entry_values_f This table holds the actual values of the element entries. Key Columns ELEMENT_ENTRY_VALUE_ID System-generated ELEMENT_ENTRY_ID Links to PAY_ELEMENT_ENTRIES_F INPUT_VALUE_ID Links to PAY_INPUT_VALUES_F SCREEN_ENTRY_VALUE Actual value of the Element entry EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 13
Slide 14: Pay_payroll_actions This table holds general details about the execution of payroll processes, including their type and all the parameters passed to them. Key Columns PAYROLL_ACTION_ID System-generated ACTION_TYPE R-Run, Q-Quick Pay and P-Prepayments CONSOLIDATION_SET_ID This is used to consolidate multiple payroll processes PAYROLL_ID Identifies the Payroll ACTION_POPULATION_STATUS Indicates if the insert of assignment actions is complete ACTION_STATUS Unprocessed, Processing, Error, Complete. 14
Slide 15: Pay_assignment_actions When you run a payroll the payroll process creates a row(Assignment action) in this table for each assignment which has been processed by the payroll process. If at all there is a row in this table for an assignment that means that some payroll processing has taken place for that assignment and we can know the status of that payroll action by looking at the ACTION_STATUS which indicates whether it processed successfully, unsuccessfully, or not at all. Key Columns ASSIGNMENT_ACTION_ID System-generated ASSIGNMENT_ID Links to PER_ALL_ASSIGNMENTS_F PAYROLL_ACTION_ID Links to PAY_PAYROLL_ACTIONS 15
Slide 16: Pay_run_results This table holds the run results from processing each element entry. There is always a single run result for each entry. Key Columns RUN_RESULT_ID System-generated ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F ASSIGNMENT_ACTION_ID Links to PAY_ASSIGNMENT_ACTIONS ENTRY_TYPE E-element entry, D-additional entry, S- Override SOURCE_ID Links to PAY_ELEMENT_ENTRIES_F SOURCE_TYPE For example, E-normal entry, I-indirect result STATUS P-processed, U-unprocessed 16
Slide 17: Pay_run_result_values This table holds the actual run result values that are calculated from processing a single element entry. Key Columns INPUT_VALUE_ID Links to PAY_INPUT_VALUES_F RUN_RESULT_ID Links to PAY_RUN_RESULTS RESULT_VALUE The value of the result. (calculated amount) 17
Slide 18: Query to get Element Entry information SELECT ppf.employee_number PRN, ppf.full_name Name, pev.screen_entry_value "Annual Salary" FROM per_all_people_f ppf ,per_assignments_f paf ,pay_element_entries_f pee ,pay_element_entry_values_f pev ,pay_element_links_f pel ,pay_element_types_f pet ,pay_input_values_f piv WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pee.assignment_id AND pee.element_entry_id = pev.element_entry_id AND pee.element_link_id = pel.element_link_id AND pel.element_type_id = pet.element_type_id AND piv.element_type_id = pet.element_type_id 18
Slide 19: Query to get Run Results informationname, SELECT INITCAP(ppf.last_name ||','||ppf.first_name) ety.element_name, TO_NUMBER(rrv.result_value) Commission FROM per_people_x ppf, per_assignments_x paf, pay_assignment_actions pas pay_payroll_actions ppa, pay_run_results rr, pay_run_result_values rrv, pay_element_types_f ety, pay_input_values_x I, WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pas.assignment_id AND pas.assignment_action_id = rr.assignment_action_id AND ppa.payroll_action_id = pas.payroll_action_id AND rr.element_type_id = ety.element_type_id AND i.element_type_id = ety.element_type_id 19
Slide 20: Other related tables Pay_payrolls_f - Holds Per_time_periods - Holds Payroll Periods InformationPayroll Definitions Types InformationPer_time_period_types - Holds Payroll Period ClassificationsPay_element_classifications - Holds Element Set DefinitionsPay_consolidation_sets – Holds Consolidation Accounting flex field info 20Pay_cost_allocation_keyflex - Holds
Slide 2: Important TablesTable of Contents Payroll Terms Other Related Tables 2 Useful Queries Tables Relationship and Description
Slide 3: Elements are simply thePayroll Terms Element units used to build the earnings, deductions and benefits that can be given to Input Value is like a placeholder for holding thean employee. Input Value actual value of the Element. An element can have more than one input value. Element Link is the one which links the element to employees.Element Link This linking can be done by associating an element to a payroll and then associating that payroll to an employees assignment. An element can also be linked to employee by Employment Category, Grade and Job. 3
Slide 4: ThePayroll Terms (contd.) Element Entry Value Any payroll related actionactual value of the element entry Payroll Action performed on employee’s assignment is referred to as Payroll Action. It can be a This is aPayroll Run, Quick-Pay, Pre-payments or Payments Assignment Action record of actions performed by the Payroll process on each and every assignment. This is the actual calculated result of the payroll process forRun Result each and every element entry 4
Slide 5: Important Tables Per_all_people_f Pay_input_values_f Pay_element_types_f Per_all_assignments_f Pay_element_entry_values_f Pay_element_entries_f Pay_element_links_f 5 Pay_run_results Pay_assignment_actions Pay_payroll_actions
Slide 6: Table Flow Diagram Element and Element Elements Attached to Employees Payroll Processing Link Definition Per_all_people_f Pay_payroll_actions Pay_input_values_f Person_id Payroll_action_id Element_type_id Assignment_id Per_all_assignments_f Pay_assignment_actions Pay_element_types_f Assignment_id Assignment_action_id Element_type_id Element_link_id Source_id Pay_element_links_f Pay_element_entries_f Pay_run_results Element_entry_id Run_result_id Pay_element_entry_values_f Pay_run_result_values 6
Slide 7: Per_all_people_f This table holds personal information for employees, applicants, ex-employees, ex-applicants, contacts and other people. Key Columns PERSON_ID System generated EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date PERSON_TYPE_ID Links to PER_PERSON_TYPES START_DATE Start date for the person CURRENT_APPLICANT_FLAG Yes or null CURRENT_EMP_OR_APL_FLAG Yes or null CURRENT_EMPLOYEE_FLAG Yes or null DATE_OF_BIRTH Date of birth 7
Slide 8: Per_all_assignments_f This table holds information about employee assignments like his Job, Manager, Department, Payroll and his expense account. Key Columns ASSIGNMENT_ID System-generated EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date BUSINESS_GROUP_ID Identifies the Business group JOB_ID Identifies Employees Job ASSIGNMENT_STATUS_TYPE_ID Active or Suspended PAYROLL_ID Identifies the payroll assigned SUPERVISOR_ID Identifies the manager PERSON_ID links to PER_ALL_PEOPLE_F ORGANIZATION_ID Identifies the Cost Center/Dept DEFAULT_CODE_COMB_ID Default Expense Account 8
Slide 9: Pay_element_types_f This table holds the definitions of elements. Elements are the units used to build all the earnings, deductions and benefits that can be given to employees. Key Columns ELEMENT_TYPE_ID System-generated ELEMENT_NAME User name for the element type REPORTING_NAME Reporting Name used in SOE CLASSIFICATION_ID Earnings/Deductions/Information MULTIPLE_ENTRIES_ALLOWED_FLAG Indicates if multiple entries are allowed POST_TERMINATION_RULE Last standard process,final close,actual term 9 PROCESS_IN_RUN_FLAG If the element can be processed
Slide 10: Pay_input_values_f This table holds the definitions of the input values associated with a specific element. Input Value is like a placeholder for storing the actual values of the element Key Columns INPUT_VALUE_ID System-generated NAME Input value name UOM Hours, Money, Date etc USER_ENTERABLE If Users can enter values here directly DATABASE_ITEM If Database item needs to be created (used for FF) ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F 10 EFFECTIVE_START_DATE Effective start date
Slide 11: Pay_element_links_f This table that holds the eligibility rules that link elements to employees. An assignment must match the eligibility criteria defined for the element link before it can have an entry of that element. Key Columns ELEMENT_LINK_ID System-generated PAYROLL_ID Payroll attached to this element COST_ALLOCATION_KEYFLEX_ID To which account this element will be costed against ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F BALANCING_KEYFLEX_ID To which account this element will be costed against COSTABLE_TYPE If the Element can be costed or not TRANSFER_TO_GL_FLAG If the element values can be transfer to GL EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 11
Slide 12: Pay_element_entries_f This table holds the list of all elements that are actually assigned to an employees assignment. Each element is identified by element_link_id Key Columns ELEMENT_ENTRY_ID System-generated ASSIGNMENT_ID Links to PER_ALL_ASSIGNMENTS_F ELEMENT_LINK_ID Links to PAY_ELEMENT_LINKS_F EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 12
Slide 13: Pay_element_entry_values_f This table holds the actual values of the element entries. Key Columns ELEMENT_ENTRY_VALUE_ID System-generated ELEMENT_ENTRY_ID Links to PAY_ELEMENT_ENTRIES_F INPUT_VALUE_ID Links to PAY_INPUT_VALUES_F SCREEN_ENTRY_VALUE Actual value of the Element entry EFFECTIVE_START_DATE Effective start date EFFECTIVE_END_DATE Effective end date 13
Slide 14: Pay_payroll_actions This table holds general details about the execution of payroll processes, including their type and all the parameters passed to them. Key Columns PAYROLL_ACTION_ID System-generated ACTION_TYPE R-Run, Q-Quick Pay and P-Prepayments CONSOLIDATION_SET_ID This is used to consolidate multiple payroll processes PAYROLL_ID Identifies the Payroll ACTION_POPULATION_STATUS Indicates if the insert of assignment actions is complete ACTION_STATUS Unprocessed, Processing, Error, Complete. 14
Slide 15: Pay_assignment_actions When you run a payroll the payroll process creates a row(Assignment action) in this table for each assignment which has been processed by the payroll process. If at all there is a row in this table for an assignment that means that some payroll processing has taken place for that assignment and we can know the status of that payroll action by looking at the ACTION_STATUS which indicates whether it processed successfully, unsuccessfully, or not at all. Key Columns ASSIGNMENT_ACTION_ID System-generated ASSIGNMENT_ID Links to PER_ALL_ASSIGNMENTS_F PAYROLL_ACTION_ID Links to PAY_PAYROLL_ACTIONS 15
Slide 16: Pay_run_results This table holds the run results from processing each element entry. There is always a single run result for each entry. Key Columns RUN_RESULT_ID System-generated ELEMENT_TYPE_ID Links to PAY_ELEMENT_TYPES_F ASSIGNMENT_ACTION_ID Links to PAY_ASSIGNMENT_ACTIONS ENTRY_TYPE E-element entry, D-additional entry, S- Override SOURCE_ID Links to PAY_ELEMENT_ENTRIES_F SOURCE_TYPE For example, E-normal entry, I-indirect result STATUS P-processed, U-unprocessed 16
Slide 17: Pay_run_result_values This table holds the actual run result values that are calculated from processing a single element entry. Key Columns INPUT_VALUE_ID Links to PAY_INPUT_VALUES_F RUN_RESULT_ID Links to PAY_RUN_RESULTS RESULT_VALUE The value of the result. (calculated amount) 17
Slide 18: Query to get Element Entry information SELECT ppf.employee_number PRN, ppf.full_name Name, pev.screen_entry_value "Annual Salary" FROM per_all_people_f ppf ,per_assignments_f paf ,pay_element_entries_f pee ,pay_element_entry_values_f pev ,pay_element_links_f pel ,pay_element_types_f pet ,pay_input_values_f piv WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pee.assignment_id AND pee.element_entry_id = pev.element_entry_id AND pee.element_link_id = pel.element_link_id AND pel.element_type_id = pet.element_type_id AND piv.element_type_id = pet.element_type_id 18
Slide 19: Query to get Run Results informationname, SELECT INITCAP(ppf.last_name ||','||ppf.first_name) ety.element_name, TO_NUMBER(rrv.result_value) Commission FROM per_people_x ppf, per_assignments_x paf, pay_assignment_actions pas pay_payroll_actions ppa, pay_run_results rr, pay_run_result_values rrv, pay_element_types_f ety, pay_input_values_x I, WHERE ppf.person_id = paf.person_id AND paf.assignment_id = pas.assignment_id AND pas.assignment_action_id = rr.assignment_action_id AND ppa.payroll_action_id = pas.payroll_action_id AND rr.element_type_id = ety.element_type_id AND i.element_type_id = ety.element_type_id 19
Slide 20: Other related tables Pay_payrolls_f - Holds Per_time_periods - Holds Payroll Periods InformationPayroll Definitions Types InformationPer_time_period_types - Holds Payroll Period ClassificationsPay_element_classifications - Holds Element Set DefinitionsPay_consolidation_sets – Holds Consolidation Accounting flex field info 20Pay_cost_allocation_keyflex - Holds
Read SQL when it will pass as a parameter
PROCEDURE xx_read_sql_p (
p_N_run_id IN NUMBER DEFAULT NULL
,p_N_rpt_id IN NUMBER DEFAULT NULL
,p_N_cntrl_id IN NUMBER DEFAULT NULL
,p_C_sql_stmt IN VARCHAR2
,p_tbl_err_dtl OUT NOCOPY xx_pkg.tbl_err_dtl
,p_C_err_msg OUT NOCOPY VARCHAR2
,p_N_err_code OUT NOCOPY NUMBER
)
IS
l_refcur SYS_REFCURSOR;
l_query_string VARCHAR2(4000);
l_rec xx_pkg.xx_com_attribute_rec;
l_cnt NUMBER := 0;
l_string VARCHAR2(1000);
l_err_msg VARCHAR2 (600);
l_err_code NUMBER;
l_attribute1 VARCHAR2(240);
l_attribute2 VARCHAR2(240);
l_attribute3 VARCHAR2(240);
l_attribute4 VARCHAR2(240);
l_attribute5 VARCHAR2(240);
x_api_error EXCEPTION;
BEGIN
/* Fetch the latest run values */
BEGIN
SELECT attribute1
,attribute2
,attribute3
,attribute4
,attribute5
INTO l_attribute1
,l_attribute2
,l_attribute3
,l_attribute4
,l_attribute5
FROM apps.xx_com_err_details
WHERE run_id = p_N_run_id
AND report_id = p_N_rpt_id AND
ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_attribute1 := NULL;
l_attribute2 := NULL;
l_attribute3 := NULL;
l_attribute4 := NULL;
l_attribute5 := NULL;
WHEN OTHERS THEN
p_C_err_msg := 'Error : Fecth the latest run attribute values.Reason : '||SUBSTR(SQLERRM,1,150);
RAISE x_api_error;
END;
/* Assign the input sql string to l_query_string */
l_query_string := p_C_sql_stmt;
/* Open cursor and read the input sql string */
OPEN l_refcur FOR l_query_string USING l_attribute1,l_attribute2,l_attribute3,l_attribute4,l_attribute5;
LOOP
FETCH l_refcur INTO l_rec;
EXIT WHEN l_refcur%NOTFOUND;
l_cnt := l_cnt+1;
p_tbl_err_dtl(l_cnt).report_id := p_N_rpt_id;
p_tbl_err_dtl(l_cnt).run_id := p_N_run_id;
p_tbl_err_dtl(l_cnt).cntrl_id := p_N_cntrl_id;
p_tbl_err_dtl(l_cnt).attribute1 := l_rec.attribute1;
p_tbl_err_dtl(l_cnt).attribute2 := l_rec.attribute2;
p_tbl_err_dtl(l_cnt).attribute3 := l_rec.attribute3;
p_tbl_err_dtl(l_cnt).attribute4 := l_rec.attribute4;
p_tbl_err_dtl(l_cnt).attribute5 := l_rec.attribute5;
p_tbl_err_dtl(l_cnt).attribute6 := l_rec.attribute6;
p_tbl_err_dtl(l_cnt).attribute7 := l_rec.attribute7;
p_tbl_err_dtl(l_cnt).attribute8 := l_rec.attribute8;
p_tbl_err_dtl(l_cnt).attribute9 := l_rec.attribute9;
p_tbl_err_dtl(l_cnt).attribute10 := l_rec.attribute10;
p_tbl_err_dtl(l_cnt).attribute11 := l_rec.attribute11;
p_tbl_err_dtl(l_cnt).attribute12 := l_rec.attribute12;
p_tbl_err_dtl(l_cnt).attribute13 := l_rec.attribute13;
p_tbl_err_dtl(l_cnt).attribute14 := l_rec.attribute14;
p_tbl_err_dtl(l_cnt).attribute15 := l_rec.attribute15;
END LOOP;
CLOSE l_refcur;
/* Retun the success error code and message */
p_C_err_msg := 'Success';
p_N_err_code := 0;
EXCEPTION
WHEN x_api_error
THEN
p_N_err_code := 2;
ROLLBACK;
WHEN OTHERS
THEN
p_C_err_msg := 'Error main others xx_read_sql_p.Reason : '|| SUBSTR (SQLERRM, 1, 150);
p_N_err_code := 2;
apps.xx_pkg.xx_print_log_p (p_C_err_msg);
ROLLBACK;
END xx_read_sql_p;
p_N_run_id IN NUMBER DEFAULT NULL
,p_N_rpt_id IN NUMBER DEFAULT NULL
,p_N_cntrl_id IN NUMBER DEFAULT NULL
,p_C_sql_stmt IN VARCHAR2
,p_tbl_err_dtl OUT NOCOPY xx_pkg.tbl_err_dtl
,p_C_err_msg OUT NOCOPY VARCHAR2
,p_N_err_code OUT NOCOPY NUMBER
)
IS
l_refcur SYS_REFCURSOR;
l_query_string VARCHAR2(4000);
l_rec xx_pkg.xx_com_attribute_rec;
l_cnt NUMBER := 0;
l_string VARCHAR2(1000);
l_err_msg VARCHAR2 (600);
l_err_code NUMBER;
l_attribute1 VARCHAR2(240);
l_attribute2 VARCHAR2(240);
l_attribute3 VARCHAR2(240);
l_attribute4 VARCHAR2(240);
l_attribute5 VARCHAR2(240);
x_api_error EXCEPTION;
BEGIN
/* Fetch the latest run values */
BEGIN
SELECT attribute1
,attribute2
,attribute3
,attribute4
,attribute5
INTO l_attribute1
,l_attribute2
,l_attribute3
,l_attribute4
,l_attribute5
FROM apps.xx_com_err_details
WHERE run_id = p_N_run_id
AND report_id = p_N_rpt_id AND
ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_attribute1 := NULL;
l_attribute2 := NULL;
l_attribute3 := NULL;
l_attribute4 := NULL;
l_attribute5 := NULL;
WHEN OTHERS THEN
p_C_err_msg := 'Error : Fecth the latest run attribute values.Reason : '||SUBSTR(SQLERRM,1,150);
RAISE x_api_error;
END;
/* Assign the input sql string to l_query_string */
l_query_string := p_C_sql_stmt;
/* Open cursor and read the input sql string */
OPEN l_refcur FOR l_query_string USING l_attribute1,l_attribute2,l_attribute3,l_attribute4,l_attribute5;
LOOP
FETCH l_refcur INTO l_rec;
EXIT WHEN l_refcur%NOTFOUND;
l_cnt := l_cnt+1;
p_tbl_err_dtl(l_cnt).report_id := p_N_rpt_id;
p_tbl_err_dtl(l_cnt).run_id := p_N_run_id;
p_tbl_err_dtl(l_cnt).cntrl_id := p_N_cntrl_id;
p_tbl_err_dtl(l_cnt).attribute1 := l_rec.attribute1;
p_tbl_err_dtl(l_cnt).attribute2 := l_rec.attribute2;
p_tbl_err_dtl(l_cnt).attribute3 := l_rec.attribute3;
p_tbl_err_dtl(l_cnt).attribute4 := l_rec.attribute4;
p_tbl_err_dtl(l_cnt).attribute5 := l_rec.attribute5;
p_tbl_err_dtl(l_cnt).attribute6 := l_rec.attribute6;
p_tbl_err_dtl(l_cnt).attribute7 := l_rec.attribute7;
p_tbl_err_dtl(l_cnt).attribute8 := l_rec.attribute8;
p_tbl_err_dtl(l_cnt).attribute9 := l_rec.attribute9;
p_tbl_err_dtl(l_cnt).attribute10 := l_rec.attribute10;
p_tbl_err_dtl(l_cnt).attribute11 := l_rec.attribute11;
p_tbl_err_dtl(l_cnt).attribute12 := l_rec.attribute12;
p_tbl_err_dtl(l_cnt).attribute13 := l_rec.attribute13;
p_tbl_err_dtl(l_cnt).attribute14 := l_rec.attribute14;
p_tbl_err_dtl(l_cnt).attribute15 := l_rec.attribute15;
END LOOP;
CLOSE l_refcur;
/* Retun the success error code and message */
p_C_err_msg := 'Success';
p_N_err_code := 0;
EXCEPTION
WHEN x_api_error
THEN
p_N_err_code := 2;
ROLLBACK;
WHEN OTHERS
THEN
p_C_err_msg := 'Error main others xx_read_sql_p.Reason : '|| SUBSTR (SQLERRM, 1, 150);
p_N_err_code := 2;
apps.xx_pkg.xx_print_log_p (p_C_err_msg);
ROLLBACK;
END xx_read_sql_p;
Split the single comma sperated row in multiple row
SELECT *
FROM (SELECT TRIM (SUBSTR (txt,
INSTR (txt, ';', 1, LEVEL) + 1,
INSTR (txt, ';', 1, LEVEL + 1) -
INSTR (txt, ';', 1, LEVEL) -
1
)) AS token
FROM (SELECT ';' || 'a;b;c;d;e;f;g;h' || ';' AS txt
FROM dual)
CONNECT BY LEVEL <= LENGTH (txt) - LENGTH (REPLACE (txt, ';', '')) - 1);
INPUT
-------
'a;b;c;d;e;f;g;h'
OUTPUT
-------
a
b
c
d
e
f
g
h
FROM (SELECT TRIM (SUBSTR (txt,
INSTR (txt, ';', 1, LEVEL) + 1,
INSTR (txt, ';', 1, LEVEL + 1) -
INSTR (txt, ';', 1, LEVEL) -
1
)) AS token
FROM (SELECT ';' || 'a;b;c;d;e;f;g;h' || ';' AS txt
FROM dual)
CONNECT BY LEVEL <= LENGTH (txt) - LENGTH (REPLACE (txt, ';', '')) - 1);
INPUT
-------
'a;b;c;d;e;f;g;h'
OUTPUT
-------
a
b
c
d
e
f
g
h
Getting data from multiple rows into one column
select deptno,rtrim(xmlagg(xmlelement(e,ename||',')).extract('//text()'),',') ename from scott.emp group by deptno;
DATA :
Deptno Ename
10 A
10 B
10 C
20 D
20 E
20 F
OUTPUT:
Deptno Ename
10 A,B,C
20 D,E,F
DATA :
Deptno Ename
10 A
10 B
10 C
20 D
20 E
20 F
OUTPUT:
Deptno Ename
10 A,B,C
20 D,E,F
Subscribe to:
Posts (Atom)