1 PACKAGE BODY pay_ca_payroll_utils AS
2 /* $Header: pycautil.pkb 120.1 2006/09/13 11:48:30 ssmukher noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_ca_payroll_utils
21
22 Description : The package has the common functions used in
23 CA Payroll.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- ------------------------------------
29 24-JUN-2003 ssouresr 115.0 Created.
30 18-MAR-2004 sdahiya 115.1 Modified check_balance_status to act as
31 wrapper for pay_us_payroll_utils.check_balance_status.
32 13-Sep-2006 ssmukher 115.2 Added the new procedure delete_actionid.
33
34 /*****************************************************************************
35 Name : check_balance_status
36 Purpose : Function should be used to identify whether the balances relevant
37 to partcular attribute are valid for use of BRA.
38 Arguments : 1. Start Date
39 2. Business Group Id
40 3. Atttribute Name
41 Return : 'Y' for valid status and 'N' for invalid status of balance
42 Notes : It will used by Canadian Reports to find
43 if all the balances related to a report are valid or not
44 *****************************************************************************/
45
46 FUNCTION check_balance_status(
47 p_start_date in date,
48 p_business_group_id in hr_organization_units.organization_id%type,
49 p_attribute_name in varchar2)
50 RETURN VARCHAR2
51 IS
52
53 lv_package_stage VARCHAR2(50) := 'pay_ca_payroll_utils.check_balance_status';
54
55 BEGIN
56 hr_utility.trace('Start of Procedure '||lv_package_stage);
57 hr_utility.set_location(lv_package_stage,10);
58 RETURN (pay_us_payroll_utils.check_balance_status(p_start_date, p_business_group_id, p_attribute_name, 'CA'));
59
60 EXCEPTION
61 WHEN others THEN
62 hr_utility.set_location(lv_package_stage,20);
63 hr_utility.trace('Invalid Attribute Name');
64 raise_application_error(-20101, 'Error in pay_ca_payroll_utils.check_balance_status');
65 raise;
66 END check_balance_status;
67
68 PROCEDURE delete_actionid(p_payroll_action_id IN NUMBER) IS
69
70 CURSOR c_get_report_type(p_payactid NUMBER) IS
71 SELECT
72 ppa.report_type
73 FROM
74 pay_payroll_actions ppa
75 WHERE
76 ppa.payroll_action_id = p_payactid;
77
78 CURSOR c_get_file_payroll_asgact (p_payactid NUMBER) IS
79 SELECT
80 pfd.file_detail_id
81 FROM pay_file_details pfd
82 WHERE pfd.source_id = p_payactid;
83
84 CURSOR c_get_file_asgact (p_payactid NUMBER) IS
85 SELECT
86 pfd.file_detail_id
87 FROM pay_file_details pfd
88 WHERE pfd.source_id in (
89 SELECT assignment_action_id
90 FROM pay_assignment_actions
91 WHERE payroll_action_id = p_payactid);
92
93 l_file_detid pay_file_details.file_detail_id%type;
94 type asg_act_list is table of pay_assignment_actions.assignment_action_id%type;
95 aalist asg_act_list;
96
97 BEGIN
98 open c_get_file_payroll_asgact (p_payroll_action_id);
99 fetch c_get_file_payroll_asgact
100 into l_file_detid;
101 close c_get_file_payroll_asgact;
102
103 DELETE
104 FROM pay_file_details
105 WHERE file_detail_id = l_file_detid;
106
107 open c_get_file_asgact(p_payroll_action_id);
108 loop
109 fetch c_get_file_asgact bulk collect into aalist limit 1000;
110 forall i in 1..aalist.count
111 delete from pay_file_details
112 where file_detail_id = aalist(i);
113
114 exit when c_get_file_asgact%notfound;
115 end loop;
116 close c_get_file_asgact;
117
118 /* Calling the core procedure to delete the Payroll actions
119 and Assignment actions */
120 pay_archive.remove_report_actions(p_payroll_action_id);
121
122 END delete_actionid;
123
124 END pay_ca_payroll_utils;
[Home] [Help]