[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
09-Mar-2012 vvijayku 120.1 13772336 Corrected the insertion of ARR and
ACC tags to the pay_us_rpt_totals
table.
15-Mar-2012 vvijayku 120.2 13827477 Added new conditions to prevent the
fetching and display of the balance
value if either none of the dimensions
are selected in the SS page or if
there are no elements fetched for the
selected Element Classification.
also added a new Function for the
validation of the Assignment.
29-Mar-2012 vvijayku 120.3 13902938 Did the following changes in the
package:
1) Implemented the Balance Initialization
code to retrieve the balance
initialization balance values.
2) Consolidated the fetching of
balance values code into the
function fetch_and_store_balances
3) Consolidated the Insertion of
balance values to pay_us_rpt_totals
code into the function
populate_tab_rpt_totals
4) Renamed the variables used in the
package to follow a standard
pattern.
5) Modified the Assignment
validation code in the function
validate_assignment.
6) Made use of pay_ip_route_support to correctly
fetch the tax_year and tax_quarter.
03-Apr-2012 vvijayku 120.4 13902938 Changed the code that fetches the l_pay_start_date
to make use of the beginning of Tax Year instead
of the beginning of the Calendar Year.
06-Apr-2012 vvijayku 120.5 13902938 Removed all the occurrences of per_all_assignments_f
and replaced it with per_assignments_f. Replaced the
use of trunc(p_session_date,'YYYY') to
the use of Tax Year using pay_ip_route_support.tax_year
function.
********************************************************************************************/
/*******************************************************************************************
Description
-----------
PL/SQL Tables used :
------------------
1) t_eb_bal_context_tab - This is the pl/sql table that holds the context data that is required
to fetch the balance values using the pay_balance_pkg package.
2) t_eb_bal_result_tab - This is the pl/sql table which is passed to the pay_balance_pkg
package which will hold the retrieved balance values along with the
respective defined balance ids.
3) t_eb_balance_value_tab - This is the pl/sql table that holds the defined balance ids for
which the balance values are to be bulk fetched using the
pay_balance_pkg package.
4) t_earnings_tab - This is the pl/sql table that holds the details of the earning type elements
along with the primary balance type id for which the balance values are to
be fetched.
5) t_deduction_tab - This is the pl/sql table that holds the details of the deduction type
elements along with the primary balance type id, arrears balance type id
and accruals balance type id for which the balance values are to be
fetched.
6) t_defbal_details_tab - This is the pl/sql table that holds the details of the defined
balance ids for the each of the Primary Balance ids, Arrears balance
type ids, Accruals balance type ids and Assignment level and Person
level PTD, MTD, QTD, YTD dimensions along with the element name,
element type id, balance type id, balance value, balance dimension id
and database item suffix of the dimension. This information is held
in a verticle format i.e. each record of the pl/sql table holds
the information for a distinct defined balance id.
7) t_ele_bal_horizontal_tab - This is the pl/sql table that holds the data present in the
pl/sql table t_defbal_details_tab but in the horizontal format
i.e. the information is categorized into Primary Balance data,
Arrears balance data and Accruals Balance data. Each of these
types of data has a single record in this table. The Balance
values for the defined balances of the Primary Balance is stored
in the corresponding row for the Primary Balance similarly the
balance values for Arrears and Accruals balances are stored in
their respective rows.
Procedures:
-----------
1) purge_old_data - This procedure is called from the Submit button on the SS Page.
This will clear the old element balance data that is present in the
table pay_us_rpt_totals. This step is necessary before the new set of balances
are fetched and inserted in the table pay_us_rpt_totals.In short, for every query
old data is removed and set of new data as per the current selection is populated in the
table pay_us_rpt_totals.
2) populate_element_info - This is the main procedure which is used to fetch and insert the
element balance values in the table pay_us_rpt_totals.
Following is the code flow in the procedure:
1) The legislation code is fetched using the cursor c_leg_code.
2) The element classification id is fetched using the cursor
c_get_class_name. The cursor uses the element classification
name that is passed as a parameter to the procedure and the
legislation code fetched in step 1.
3) Next the cursor c_action_type_tu is used to identify what is
the latest payroll process action type for the current assignment.
4) If the Action type returned in step 3 is 'Q' or 'R'
then if
the assignment_action_id passed to the procedure IS NOT -1 (page is opened
in assignment_action mode)
then.if
the element classification is of the type DEDUCTIONS
then
using the cursor c_element_info_asact_dedn the relevant element
information is fetched and stored in the pl/sql table t_deduction_tab
else if the element classification is of they type EARNINGS
then
using the cursor c_element_info_asact_earn the relevant element
information is fetched and stored in the pl/sql table t_earnings_tab.
5) If the Action type returned in step 3 is 'Q' or 'R',
then if
the assignment_action_id passed to the procedure IS -1 (page is opened
in Date mode)
then if
the element classification is of the type DEDUCTIONS
then
using the cursor c_element_info_date_dedn the relevant element
information is fetched and stored in the pl/sql table t_deduction_tab
else if the element classification is of they type EARNINGS
then
using the cursor c_element_info_date_earn the relevant element
information is fetched and stored in the pl/sql table t_earnings_tab.
6) If the Action type returned in step 3 is 'I'
then if
the element classification is of the type DEDUCTIONS
then
using the cursor c_element_info_bi_dedn the relevant element
information is fetched and stored in the pl/sql table t_deduction_tab
else if the element classification is of they type EARNINGS
then
using the cursor c_element_info_bi_earn the relevant element
information is fetched and stored in the pl/sql table t_earnings_tab.
7) Once the relevant element information is stored in
t_earnings_tab or t_deduction_tab, based on the type of element
classification, the function fetch_and_store_balances is called to
fetch and store the element balance values in the pl/sql table
t_ele_bal_horizontal_tab.
7.1) The function fetch_and_store_balances, uses the Primary Balance
type id information stored in t_earnings_tab/t_deduction_tab based
on whether the element classification is of the type EARNINGS or
DEDUCTIONS respectively and fetches and stores the Defined Balance
Id information in the pl/sql table t_defbal_details_tab. To fetch
this information cursor c_get_asg_def_bal_id is called when
ASG level balance values are required and cursor c_get_per_def_bal_id
is called when PER level balances are required.
7.2) If the type of element classification is DEDUCTIONS and the
balance level to be fetched is ASG then the
cursors c_get_asg_arr_defbal and c_get_asg_acc_defbal are called
to fetch and store the Arrears and Accruals Defined balance ids
respectively in the pl/sql table t_defbal_details_tab.
7.3))create a horizontal data structure to store all the Balance Values
for Primary Balance, Arrears Balance and Acrruals Balance for
the given element in individual rows in a pl/sql table i.e. the
balance values for all the defined balances of the primary balance
should be stored in a single row and similarly the balance value
of arrears defined balance id should be stored in a single row
and balance value of accruals balance should be stored in a single
row. This table structure is created using the pl/sql table
t_ele_bal_horizontal_tab. Maximum possible rows in the table is 3.
7.4)if the p_assignment_action_id IS NOT -1 (assignment action mode) then
the relevant defined balance ids from t_defbal_details_tab is
stored in t_eb_balance_value_tab which is later passed to the
bulk fetch the balance values for the given p_assignment_action_id
The balance values are stored in the pl/sql table t_eb_bal_result_tab
which is later transferred to the table t_defbal_details_tab.
7.5)In step 7.4 if the p_assignment_action_id IS -1 (Date mode) then the
relevant defined balance ids are passed as parameter to the
function get_bal and the returned balance value is directly
stored in t_defbal_details_tab.
7.6)The balance dimension details and the balance value stored in
t_defbal_details_tab are transferred to t_ele_bal_horizontal_tab.
8) The function populate_tab_rpt_totals is executed which inserts the
data present in t_ele_bal_horizontal_tab to the table pay_us_rpt_totals.
This data in pay_us_rpt_totals is later queried by the SS Page and
displayed in the Results Region.
********************************************************************************************/
l_package VARCHAR2(30) := 'pay_ip_employee_balances.';
DELETE PAY_US_RPT_TOTALS
WHERE GRE_NAME = 'IP_VIEW_BALANCE'
AND ORGANIZATION_NAME IN ('ASG','PER');
SELECT 'Y'
INTO l_valid_asg
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.effective_start_date <= to_date(p_effective_date,'YYYY-MM-DD')
AND paf.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,to_date(p_effective_date,'YYYY-MM-DD'))
AND rownum=1;
be retrieved.For the selected elements balance values are fetched and
stored in a PL/SQL table which is then used to populate the table
pay_us_rpt_totals.
The View Employee Balance SS Page fetches data from this table and display it on the page
following are the parameters passed to the procedure.
1) p_assignment_id - The assignment id of the employee
2) p_assignment_action_id - Assignemnt action id of the Quick pay or
Payroll run. Values can be
a) -1 When page is invoked in Date Mode
b) A valid Assignment Action id.
3) p_classification_name - Element Classification name selected in
VEB SS Page.
4) p_session_date - Effective Date entered by the user in the SS Page
5) p_tax_unit_id - Tax Unit Id to which the assignment belongs as on
the p_session_date. Values can be
a) -1 If the loclisation doesnt support tax unit id
b) A valid Tax Unit Id (TU legislation)
6) p_per_month - Whether Person level MTD Balance value should be
fetched or not. Valid values can be 0 (No), b) 1 (Yes)
7) p_per_qtd - Whether Person level QTD Balance value should be
fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
8) p_per_ytd - Whether Person level YTD Balance value should be
fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
9) p_asg_ptd - Whether Asg level PTD Balance value should be fetched
or not. Valid values can be a) 0 (No), b) 1 (Yes)
10)p_asg_month - Whether Asg level MTD Balance value should be
fetched or not. Valid values can be a) 0 (No), b) 1 (Yes)
11)p_asg_qtd - Whether Asg level QTD Balance value should be fetched
or not. Valid values can be a) 0 (No), b) 1 (Yes)
12)p_asg_ytd - Whether Asg level YTD Balance value should be fetched
or not. Valid values can be a) 0 (No), b) 1 (Yes)
13)p_asg_arr_itd - Whether Asg level Arrears balance value should
be fetched or not. Valid values can be a) 0 (No),
b) 1 (Yes)
14)p_asg_acc_itd - Whether Asg level Accruals balance value should
be fetched or not. Valid values can be a) 0 (No),
b) 1 (Yes)
15)p_business_group_id - Business Group Id to which the emp belongs to
16)p_balance_level - The level of balance required. Valid values
can be
a) PER (Person level)
b) ASG (Assignment level)
******************************************************************************/
PROCEDURE populate_element_info( p_assignment_id in number,
p_assignment_action_id in number,
p_classification_name in pay_element_classifications.classification_name%TYPE,
p_session_date in pay_element_types_f.effective_start_date%TYPE,
p_tax_unit_id in number,
p_per_month in number,
p_per_qtd in number,
p_per_ytd in number,
p_asg_ptd in number,
p_asg_month in number,
p_asg_qtd in number,
p_asg_ytd in number,
p_asg_arr_itd in number,
p_asg_acc_itd in number,
p_business_group_id in pay_element_types_f.business_group_id%TYPE,
p_balance_level in varchar2
)
IS PRAGMA AUTONOMOUS_TRANSACTION;
l_insert_flag varchar2(5);
l_return_insert number;
SELECT legislation_code
FROM per_business_groups
WHERE business_group_id = p_business_group_id;
SELECT c.classification_id
FROM pay_element_classifications c
WHERE UPPER(c.classification_name) = UPPER(p_classification_name)
AND c.legislation_code = p_leg_code;
SELECT pay.action_type
FROM pay_assignment_actions pac
,pay_payroll_actions pay
WHERE pay.payroll_action_id = pac.payroll_action_id
AND pay.action_type IN ('Q','R','I')
AND pac.assignment_id = p_assignment_id
AND pac.action_status = 'C'
AND pac.tax_unit_id = p_tax_unit_id
AND EXISTS
(SELECT 'x'
FROM pay_run_results prr,
pay_assignment_actions pac1
WHERE prr.assignment_action_id = pac1.assignment_action_id
AND pac1.assignment_id = pac.assignment_id
)
ORDER BY decode(pay.action_type,'Q','1','R','1','I','2','3');
SELECT pay.action_type
FROM pay_assignment_actions pac
,pay_payroll_actions pay
WHERE pay.payroll_action_id = pac.payroll_action_id
AND pay.business_group_id = p_business_group_id
AND pay.action_type IN ('Q','R','I')
AND pac.assignment_id = p_assignment_id
AND pac.action_status = 'C'
AND EXISTS
(SELECT 'x'
FROM pay_run_results prr
,pay_assignment_actions pac1
WHERE prr.assignment_action_id = pac1.assignment_action_id
AND pac1.assignment_id = pac.assignment_id)
ORDER BY decode(pay.action_type,'Q','1','R','1','I','2','3');
SELECT DISTINCT pet.element_name,
pet.element_type_id,
pdb_call.defined_balance_id,
pbt.balance_type_id,
pbd.balance_dimension_id,
pbd.database_item_suffix
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_element_types_f pet
WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
AND pet.classification_id = l_classification_id
AND pet.element_information10 = fnd_number.number_to_canonical(pbt.balance_type_id)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix IN ('_ASG_TU_PTD','_ASG_TU_MONTH','_ASG_TU_TQTD','_ASG_TU_TYTD'))
OR (p_tax_unit_id = -1 AND pbd.database_item_suffix IN ('_ASG_PTD','_ASG_MONTH','_ASG_TQTD','_ASG_TYTD')))
AND pbd.legislation_code = l_legislation_code
ORDER BY pbd.database_item_suffix;
SELECT DISTINCT pet.element_name,
pet.element_type_id,
pdb_call.defined_balance_id,
pbt.balance_type_id,
pbd.balance_dimension_id,
pbd.database_item_suffix
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_element_types_f pet
WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
AND pet.classification_id = l_classification_id
AND pet.element_information10 = fnd_number.number_to_canonical(pbt.balance_type_id)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix IN ('_PER_TU_MONTH','_PER_TU_TQTD','_PER_TU_TYTD'))
OR (p_tax_unit_id = -1 AND pbd.database_item_suffix IN ('_PER_MONTH','_PER_TQTD','_PER_TYTD')))
AND pbd.legislation_code = l_legislation_code
ORDER BY pbd.database_item_suffix;
SELECT DISTINCT pet.element_name,
pet.element_type_id,
pdb_call.defined_balance_id,
pbt.balance_type_id,
pbd.balance_dimension_id
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_element_types_f pet
WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
AND pet.classification_id = l_classification_id
AND pet.element_information11 = fnd_number.number_to_canonical(pbt.balance_type_id)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix = '_ASG_TU_ITD')
OR (p_tax_unit_id = -1 AND pbd.database_item_suffix = '_ASG_ITD'))
AND pbd.legislation_code = l_legislation_code;
SELECT DISTINCT pet.element_name,
pet.element_type_id,
pdb_call.defined_balance_id,
pbt.balance_type_id,
pbd.balance_dimension_id
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_element_types_f pet
WHERE pbt.balance_type_id = fnd_number.canonical_to_number(p_balance_type_id)
AND ((pbt.business_group_id IS NOT NULL AND pbt.business_group_id = p_business_group_id)
OR (pbt.legislation_code IS NOT NULL AND pbt.legislation_code = l_legislation_code))
AND pet.classification_id = l_classification_id
AND pet.element_information13 = fnd_number.number_to_canonical(pbt.balance_type_id)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND ((p_tax_unit_id <> -1 AND pbd.database_item_suffix = '_ASG_TU_ITD')
OR (p_tax_unit_id = -1 AND pbd.database_item_suffix = '_ASG_ITD'))
AND pbd.legislation_code = l_legislation_code;
SELECT DISTINCT pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
FROM pay_element_types_f pet
,pay_element_types_f pet2
,pay_element_entries_f ee
WHERE pet2.classification_id = l_classification_id
AND pet2.element_information10 IS NOT NULL
AND ee.effective_end_date >= l_pay_start_date
AND ee.effective_start_date <= nvl(l_action_date , p_session_date )
AND ee.effective_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
AND pet2.element_type_id = pet.element_type_id
AND PET.effective_start_date =
(SELECT max(pet1.effective_start_date)
FROM pay_element_types_f pet1
WHERE pet1.element_type_id = pet.element_type_id
AND pet1.effective_start_date <= p_session_date )
AND ee.assignment_id = p_assignment_id
AND EXISTS
(SELECT prr.element_type_id
FROM pay_run_results prr
WHERE prr.source_id = ee.element_entry_id
AND prr.source_type IN ( 'E' , 'I' )
AND prr.element_type_id + 0 = pet.element_type_id
)
ORDER BY 2;
SELECT DISTINCT pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information13
FROM pay_element_types_f pet
,pay_element_types_f pet2
,pay_element_entries_f ee
WHERE pet2.classification_id = l_classification_id
AND pet2.element_information10 IS NOT NULL
AND ee.effective_end_date >= l_pay_start_date
AND ee.effective_start_date <= nvl(l_action_date , p_session_date )
AND ee.effective_start_date BETWEEN pet2.effective_start_date AND pet2.effective_end_date
AND pet2.element_type_id = pet.element_type_id
AND PET.effective_start_date =
(SELECT max(pet1.effective_start_date)
FROM pay_element_types_f pet1
WHERE pet1.element_type_id = pet.element_type_id
AND pet1.effective_start_date <= p_session_date )
AND ee.assignment_id = p_assignment_id
AND EXISTS
(SELECT prr.element_type_id
FROM pay_run_results prr
WHERE prr.source_id = ee.element_entry_id
AND prr.source_type IN ( 'E' , 'I' )
AND prr.element_type_id + 0 = pet.element_type_id
)
ORDER BY 2;
SELECT DISTINCT pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
FROM pay_element_types_f pet
,pay_run_results prr
WHERE prr.assignment_action_id = p_assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.business_group_id = p_business_group_id
AND pet.classification_id = l_classification_id
AND pet.element_information10 IS NOT NULL;
SELECT DISTINCT pet.element_name
,pet.element_type_id
,pet.classification_id
,pet.element_information10
,pet.element_information11
,pet.element_information13
FROM pay_element_types_f pet
,pay_run_results prr
WHERE prr.assignment_action_id = p_assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.business_group_id = p_business_group_id
AND pet.classification_id = l_classification_id
AND pet.element_information10 IS NOT NULL;
SELECT DISTINCT pet2. element_name
,pet2.element_type_id
,pet2.classification_id
,pet2.element_information10
FROM pay_element_classifications ec
,pay_element_types_f et
,pay_element_links_f el
,pay_element_entries_f ee
,pay_element_entry_values_f eev
,pay_balance_feeds_f pbf
,pay_element_types_f pet2
,pay_element_classifications pec
,pay_input_values_f piv
,pay_balance_types pbt
WHERE ec.classification_name = 'Balance Initialization'
AND ec.legislation_code IS NULL
AND ee.assignment_id = p_assignment_id
AND ee.element_link_id = el.element_link_id
AND el.element_type_id = et.element_type_id
AND et.classification_id = ec.classification_id
AND ee.element_entry_id = eev.element_entry_id
AND eev.input_value_id = pbf.input_value_id
AND piv.input_value_id = pbf.input_value_id
AND et.element_type_id = piv.element_type_id
AND nvl(l_action_date ,p_session_date) BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND pbf.balance_type_id = pet2.element_information10
AND pbt.balance_type_id = pet2.element_information10
AND pet2.element_information10 IS NOT NULL
AND pet2.classification_id = pec.classification_id
AND pec.classification_name = p_classification_name
AND pec.legislation_code = l_legislation_code
AND nvl(l_action_date, p_session_date ) BETWEEN pet2.effective_start_date
AND pet2.effective_end_date
AND eev.screen_entry_value IS NOT NULL
ORDER BY 2;
SELECT DISTINCT pet2. element_name
,pet2.element_type_id
,pet2.classification_id
,pet2.element_information10
,pet2.element_information11
,pet2.element_information13
FROM pay_element_classifications ec
,pay_element_types_f et
,pay_element_links_f el
,pay_element_entries_f ee
,pay_element_entry_values_f eev
,pay_balance_feeds_f pbf
,pay_element_types_f pet2
,pay_element_classifications pec
,pay_input_values_f piv
,pay_balance_types pbt
WHERE ec.classification_name = 'Balance Initialization'
AND ec.legislation_code IS NULL
AND ee.assignment_id = p_assignment_id
AND ee.element_link_id = el.element_link_id
AND el.element_type_id = et.element_type_id
AND et.classification_id = ec.classification_id
AND ee.element_entry_id = eev.element_entry_id
AND eev.input_value_id = pbf.input_value_id
AND piv.input_value_id = pbf.input_value_id
AND et.element_type_id = piv.element_type_id
AND nvl(l_action_date ,p_session_date) BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND pbf.balance_type_id = pet2.element_information10
AND pbt.balance_type_id = pet2.element_information10
AND pet2.element_information10 IS NOT NULL
AND pet2.classification_id = pec.classification_id
AND pec.classification_name = p_classification_name
AND pec.legislation_code = l_legislation_code
AND nvl(l_action_date, p_session_date ) BETWEEN pet2.effective_start_date
AND pet2.effective_end_date
AND eev.screen_entry_value IS NOT NULL
ORDER BY 2;
SELECT balance_dimension_id INTO l_dim_id
FROM pay_balance_dimensions
WHERE database_item_suffix = p_dimension
AND legislation_code = l_legislation_code;
SELECT defined_balance_id INTO l_defbal_id
FROM pay_defined_balances
WHERE balance_type_id = p_bal_id
AND balance_dimension_id = l_dim_id
AND nvl(business_group_id,p_business_group_id) = p_business_group_id
AND nvl(legislation_code,l_legislation_code) = l_legislation_code;
SELECT paf.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf,
hr_soft_coding_keyflex hsk
WHERE paf.assignment_id = p_assignment_id
AND paf.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND hsk.segment1 = to_char(p_tax_unit_id);
SELECT paf.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND paf.business_group_id = p_business_group_id
AND p_session_date BETWEEN paf.effective_start_date
AND paf.effective_end_date;
SELECT paf2.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
WHERE paf1.assignment_id = p_assignment_id
AND paf2.person_id = paf1.person_id
AND paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
AND p_session_date BETWEEN paf2.effective_start_date
AND paf2.effective_end_date
AND hsk.segment1 = to_char(p_tax_unit_id)
AND rownum=1;
SELECT paf2.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf1,
per_assignments_f paf2
WHERE paf1.assignment_id = p_assignment_id
AND paf2.person_id = paf1.person_id
AND paf2.business_group_id = p_business_group_id
AND p_session_date BETWEEN paf2.effective_start_date
AND paf2.effective_end_date
AND rownum=1;
SELECT paf2.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf1,
per_assignments_f paf2,
hr_soft_coding_keyflex hsk
WHERE paf1.assignment_id = p_assignment_id
AND paf2.person_id = paf1.person_id
AND paf2.soft_coding_keyflex_id = hsk.soft_coding_keyflex_id
AND hsk.segment1 = to_char(p_tax_unit_id)
AND paf2.effective_end_date < p_session_date
AND paf2.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
AND paf2.effective_end_date =
(SELECT MAX(paf3.effective_end_date)
FROM per_assignments_f paf3
WHERE paf3.person_id = paf1.person_id
AND paf3.effective_end_date < p_session_date
)
AND rownum=1;
SELECT paf2.assignment_id
INTO l_temp_assignment_id
FROM per_assignments_f paf1,
per_assignments_f paf2
WHERE paf1.assignment_id = p_assignment_id
AND paf2.person_id = paf1.person_id
AND paf2.business_group_id = p_business_group_id
AND paf2.effective_end_date < p_session_date
AND paf2.effective_end_date >= pay_ip_route_support.tax_year(p_business_group_id,p_session_date)
AND paf2.effective_end_date =
(SELECT MAX(paf3.effective_end_date)
FROM per_assignments_f paf3
WHERE paf3.person_id = paf1.person_id
AND paf3.effective_end_date < p_session_date
)
AND rownum=1;
t_defbal_details_tab.delete; -- purging the pl/sql table to clear any old data.
t_defbal_details_tab.delete;
t_ele_bal_horizontal_tab.delete;
t_eb_balance_value_tab.delete;
selected at the SS Page level that are passed to the current package. The selected defined
balance ids are stored in the pl/sql table t_eb_balance_value_tab which is later passed to
the pay_balance_pkg to bulk fetch the balance values.
*/
j:=1;
selected at the SS Page level that are passed to the current package. The selected defined
balance ids and associated balance type ids are passed to the get_bal function which returns
the balance value for the current defined balance id. These balance values are stored in the
pl/sql table t_defbal_details_tab.
*/
hr_utility.set_location(l_package||l_function, 170);
* Purpose : This function is used to insert the data in the pl/sql table
* t_ele_bal_horizontal_tab into pay_us_rpt_totals_table.
******************************************************************************/
FUNCTION populate_tab_rpt_totals (p_insert_flag varchar2)
RETURN NUMBER IS
l_function VARCHAR2(30);
IF p_insert_flag = 'Y' THEN
hr_utility.set_location(l_package||l_function, 20);
INSERT INTO pay_us_rpt_totals
(GRE_NAME,
ORGANIZATION_ID,
ORGANIZATION_NAME,
LOCATION_NAME,
LOCATION_ID,
STATE_NAME,
TAX_UNIT_ID,
VALUE2,
VALUE3,
VALUE4,
VALUE5,
VALUE6,
VALUE7,
VALUE8,
VALUE9,
VALUE10,
VALUE11,
VALUE12,
VALUE13,
VALUE14)
VALUES ('IP_VIEW_BALANCE',
p_assignment_id,
p_balance_level,
t_ele_bal_horizontal_tab(i).ele_name,
t_ele_bal_horizontal_tab(i).ele_type_id,
fnd_date.date_to_canonical(p_session_date),
l_id_value,
t_ele_bal_horizontal_tab(i).bal_type_id,
t_ele_bal_horizontal_tab(i).ptd_bal_dim_id,
t_ele_bal_horizontal_tab(i).ptd_bal_value,
t_ele_bal_horizontal_tab(i).mtd_bal_dim_id,
t_ele_bal_horizontal_tab(i).mtd_bal_value,
t_ele_bal_horizontal_tab(i).qtd_bal_dim_id,
t_ele_bal_horizontal_tab(i).qtd_bal_value,
t_ele_bal_horizontal_tab(i).ytd_bal_dim_id,
t_ele_bal_horizontal_tab(i).ytd_bal_value,
t_ele_bal_horizontal_tab(i).arr_bal_dim_id,
t_ele_bal_horizontal_tab(i).arr_bal_value,
t_ele_bal_horizontal_tab(i).acc_bal_dim_id,
t_ele_bal_horizontal_tab(i).acc_bal_value);
END IF; -- END IF clause for p_insert_flag
SELECT MIN(ptp.start_date) first_period_start_date
INTO l_pay_start_date
FROM per_assignments_f paf,
per_time_periods ptp
WHERE paf.assignment_id = p_assignment_id
AND ptp.payroll_id = paf.payroll_id
AND ptp.regular_payment_date >= pay_ip_route_support.tax_year(p_business_group_id,nvl(l_action_date, p_session_date));
ELSE -- Atleast one of the options has been selected at the page level.
hr_utility.set_location(l_package||l_procedure, 70);
t_deduction_tab.delete;
t_earnings_tab.delete;
IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
hr_utility.set_location(l_package||l_procedure, 140);
l_insert_flag := 'Y';
hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_insert_flag));
l_insert_flag := 'N';
hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_insert_flag));
l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
t_earnings_tab.delete;
t_deduction_tab.delete;
IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
hr_utility.set_location(l_package||l_procedure, 220);
l_insert_flag := 'Y';
hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_insert_flag));
l_insert_flag := 'N';
hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_insert_flag));
l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
t_deduction_tab.delete;
t_earnings_tab.delete;
IF l_flag = 'Y' THEN -- Only if the element details were found proceed further to fetch and insert the balance values.
hr_utility.set_location(l_package||l_procedure, 300);
l_insert_flag := 'Y';
hr_utility.trace('Insert Flag value in Balance Initialization and Date Mode is: '||to_char(l_insert_flag));
l_insert_flag := 'N';
hr_utility.trace('Insert Flag value in Balance Initialization and Date Mode is: '||to_char(l_insert_flag));
l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);