[Home] [Help]
28: Date Name Vers Bug No Description
29: ----------- ---------- ------ --------- -----------------------------------
30: 31-Jan-2012 vvijayku 120.0 Created.
31: 09-Mar-2012 vvijayku 120.1 13772336 Corrected the insertion of ARR and
32: ACC tags to the pay_us_rpt_totals
33: table.
34: 15-Mar-2012 vvijayku 120.2 13827477 Added new conditions to prevent the
35: fetching and display of the balance
36: value if either none of the dimensions
47: 2) Consolidated the fetching of
48: balance values code into the
49: function fetch_and_store_balances
50: 3) Consolidated the Insertion of
51: balance values to pay_us_rpt_totals
52: code into the function
53: populate_tab_rpt_totals
54: 4) Renamed the variables used in the
55: package to follow a standard
116: Procedures:
117: -----------
118: 1) purge_old_data - This procedure is called from the Submit button on the SS Page.
119: This will clear the old element balance data that is present in the
120: table pay_us_rpt_totals. This step is necessary before the new set of balances
121: are fetched and inserted in the table pay_us_rpt_totals.In short, for every query
122: old data is removed and set of new data as per the current selection is populated in the
123: table pay_us_rpt_totals.
124:
117: -----------
118: 1) purge_old_data - This procedure is called from the Submit button on the SS Page.
119: This will clear the old element balance data that is present in the
120: table pay_us_rpt_totals. This step is necessary before the new set of balances
121: are fetched and inserted in the table pay_us_rpt_totals.In short, for every query
122: old data is removed and set of new data as per the current selection is populated in the
123: table pay_us_rpt_totals.
124:
125: 2) populate_element_info - This is the main procedure which is used to fetch and insert the
119: This will clear the old element balance data that is present in the
120: table pay_us_rpt_totals. This step is necessary before the new set of balances
121: are fetched and inserted in the table pay_us_rpt_totals.In short, for every query
122: old data is removed and set of new data as per the current selection is populated in the
123: table pay_us_rpt_totals.
124:
125: 2) populate_element_info - This is the main procedure which is used to fetch and insert the
126: element balance values in the table pay_us_rpt_totals.
127:
122: old data is removed and set of new data as per the current selection is populated in the
123: table pay_us_rpt_totals.
124:
125: 2) populate_element_info - This is the main procedure which is used to fetch and insert the
126: element balance values in the table pay_us_rpt_totals.
127:
128: Following is the code flow in the procedure:
129:
130: 1) The legislation code is fetched using the cursor c_leg_code.
212: stored in t_defbal_details_tab.
213: 7.6)The balance dimension details and the balance value stored in
214: t_defbal_details_tab are transferred to t_ele_bal_horizontal_tab.
215: 8) The function populate_tab_rpt_totals is executed which inserts the
216: data present in t_ele_bal_horizontal_tab to the table pay_us_rpt_totals.
217: This data in pay_us_rpt_totals is later queried by the SS Page and
218: displayed in the Results Region.
219: ********************************************************************************************/
220:
213: 7.6)The balance dimension details and the balance value stored in
214: t_defbal_details_tab are transferred to t_ele_bal_horizontal_tab.
215: 8) The function populate_tab_rpt_totals is executed which inserts the
216: data present in t_ele_bal_horizontal_tab to the table pay_us_rpt_totals.
217: This data in pay_us_rpt_totals is later queried by the SS Page and
218: displayed in the Results Region.
219: ********************************************************************************************/
220:
221: l_package VARCHAR2(30) := 'pay_ip_employee_balances.';
229:
230: /******************************************************************************
231: Name : purge_old_data
232: Purpose : This procedure will purge the old balance data from the table
233: pay_us_rpt_totals
234: ******************************************************************************/
235: PROCEDURE purge_old_data (p_purge varchar2)
236: IS
237: PRAGMA AUTONOMOUS_TRANSACTION;
241:
242: IF p_purge = 'Y' THEN
243: hr_utility.set_location(l_package||l_procedure, 10);
244:
245: DELETE PAY_US_RPT_TOTALS
246: WHERE GRE_NAME = 'IP_VIEW_BALANCE'
247: AND ORGANIZATION_NAME IN ('ASG','PER');
248: COMMIT;
249:
285: Name : populate_element_info
286: Purpose : This procedure fetches the elements for which the balances are to
287: be retrieved.For the selected elements balance values are fetched and
288: stored in a PL/SQL table which is then used to populate the table
289: pay_us_rpt_totals.
290: The View Employee Balance SS Page fetches data from this table and display it on the page
291: following are the parameters passed to the procedure.
292:
293: 1) p_assignment_id - The assignment id of the employee
1358:
1359: /******************************************************************************
1360: * Name : populate_tab_rpt_totals
1361: * Purpose : This function is used to insert the data in the pl/sql table
1362: * t_ele_bal_horizontal_tab into pay_us_rpt_totals_table.
1363: ******************************************************************************/
1364:
1365: FUNCTION populate_tab_rpt_totals (p_insert_flag varchar2)
1366: RETURN NUMBER IS
1381: l_id_value := 3; -- Accruals Balance
1382: ELSE
1383: l_id_value := 1; -- Primary Balance
1384: END IF;
1385: INSERT INTO pay_us_rpt_totals
1386: (GRE_NAME,
1387: ORGANIZATION_ID,
1388: ORGANIZATION_NAME,
1389: LOCATION_NAME,
1642: l_insert_flag := 'N';
1643: hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Assignment Action Mode is: '||to_char(l_insert_flag));
1644: END IF;
1645:
1646: -- call the function to populate the table pay_us_rpt_totals
1647: l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1648:
1649: ELSIF l_flag = 'N' THEN -- no elements are fetched
1650: hr_utility.set_location(l_package||l_procedure, 150);
1719: l_insert_flag := 'N';
1720: hr_utility.trace('Insert Flag value in Quick Pay/Payroll Run and Date Mode is: '||to_char(l_insert_flag));
1721: END IF;
1722:
1723: -- call the function to populate the table pay_us_rpt_totals
1724: l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1725:
1726: ELSIF l_flag = 'N' THEN -- no elements are fetched
1727: hr_utility.set_location(l_package||l_procedure, 230);
1794: l_insert_flag := 'N';
1795: hr_utility.trace('Insert Flag value in Balance Initialization and Date Mode is: '||to_char(l_insert_flag));
1796: END IF;
1797:
1798: -- call the function to populate the table pay_us_rpt_totals
1799: l_return_insert := POPULATE_TAB_RPT_TOTALS(l_insert_flag);
1800:
1801: ELSIF l_flag = 'N' THEN -- no elements are fetched
1802: hr_utility.set_location(l_package||l_procedure, 310);