1 PACKAGE BODY HXT_USER_EXITS AS
2 /* $Header: hxtuserx.pkb 120.0 2005/05/29 06:06:07 appldev noship $ */
3
4
5 /*****************************************************************
6 * *
7 * CUSTOMER: Base Version *
8 * *
9 * This package is designed for creation of customer exit points *
10 * that will allow customer specific procedures to be implemented*
11 * *
12 * Initial code SPR C167 by BC on 06-05-96 *
13 * Timeclock code added by BC on 07-23-96
14 *****************************************************************/
15 /*--------------------------------------------------------------*/
16 /*****************************************************************
17 * Procedure Define_Reference_Number()is called at the timecard *
18 * level. It allows flexibilty in grouping batches together by *
19 * any reference critieria deemed necessary by the customer. *
20 ******************************************************************
21 * The default set's a string composed or the current user's id *
22 * and period end date when called from HXT_TIME_GEN.generate_time*
23 * *
24 * The default for timeclock timecard generation will add the *
25 * 'CLOCK:' identifier. Coupled with the period end date, this *
26 * will identify the batches created for timeclock entries. *
27 *****************************************************************/
28 PROCEDURE Define_Reference_Number(i_payroll_id IN NUMBER,
29 i_time_period_id IN NUMBER,
30 i_assignment_id IN NUMBER,
31 i_person_id IN NUMBER,
32 i_user_id IN VARCHAR2,
33 i_source_flag IN CHAR,
34 o_reference_number OUT NOCOPY VARCHAR2,
35 o_error_message OUT NOCOPY VARCHAR2)IS
36
37 --BEGIN GLOBAL
38 -- l_source_description pay_pdt_batch_headers.reference_num%TYPE := 'MAN_';
39 l_source_description pay_batch_headers.batch_reference%TYPE := 'MAN_';
40 --END GLOBAL
41 l_date DATE;
42
43 BEGIN
44 l_date := hxt_util.Get_Period_End(i_time_period_id);
45
46 IF i_source_flag = 'C' THEN
47 l_source_description := 'C_'||fnd_date.date_to_chardate(l_date)||'/'||i_user_id; --FORMS60
48 ELSIF i_source_flag = 'A' THEN
49 l_source_description := 'A_'||fnd_date.date_to_chardate(l_date)||'/'||i_user_id; --FORMS60
50 ELSIF i_source_flag = 'M' THEN
51 l_source_description := 'M_'||fnd_date.date_to_chardate(l_date)||'/'||i_user_id; --FORMS60
52 ELSIF i_source_flag = 'R' THEN -- RETROPAY
53 l_source_description := 'RETRO'; -- RETROPAY
54
55 END IF;
56
57 o_reference_number := l_source_description;
58 -- to_char(HXT_util.Get_Period_End(i_time_period_id))||
59 -- '/'||
60 -- i_user_id;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 --HXT11o_error_message := 'Error('||SQLERRM||') occured creating in Define Reference Number (person id: '||TO_CHAR(i_person_id)||')';
65 fnd_message.set_name('HXT','HXT_39299_ERR_CRT_REF_NUM'); --HXT11
66 fnd_message.set_token('SQLERR',SQLERRM); --HXT11
67 fnd_message.set_token('PERSON_ID',TO_CHAR(i_person_id)); --HXT11
68 o_error_message := FND_MESSAGE.GET; --HXT11
69 FND_MESSAGE.CLEAR; --HXT11
70
71 END Define_Reference_Number;
72 --BEGIN GLOBAL
73 /*****************************************************************
74 * Procedure Define_Batch_Name()is called wherever batches are *
75 * created in OTM. It will create a unique batch name based on *
76 * batch ID. *
77 *****************************************************************/
78 PROCEDURE Define_Batch_Name(i_batch_id IN NUMBER,
79 o_batch_name OUT NOCOPY VARCHAR2,
80 o_error_message OUT NOCOPY VARCHAR2) IS
81
82 BEGIN
83 o_batch_name := 'Batch #' || to_char(i_batch_id);
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 fnd_message.set_name('HXT','HXT_39485_ERR_DEF_BATCH_NAME');
88 fnd_message.set_token('SQLERR',SQLERRM);
89 o_error_message := FND_MESSAGE.GET;
90 FND_MESSAGE.CLEAR;
91 END Define_Batch_Name;
92 /******************************************************************************************************
93 FUNCTION retro_hours()
94
95 Designed to obtain hours for completed retro transactions.
96
97 ******************************************************************************************************/
98 FUNCTION retro_hours(i_row_id IN VARCHAR2) RETURN NUMBER IS
99
100 CURSOR new_hours IS
101 SELECT retro.hours - expired.hours
102 FROM hxt_det_hours_worked_f expired,
103 hxt_det_hours_worked_f retro
104 WHERE retro.rowid = CHARTOROWID(i_row_id)
105 AND retro.parent_id = expired.parent_id
106 AND retro.element_type_id = expired.element_type_id
107 AND expired.pay_status = 'A'
108 AND expired.effective_end_date = (SELECT MAX(ex.effective_end_date)
109 FROM hxt_det_hours_worked_f ex
110 WHERE ex.effective_end_date < retro.effective_start_date
111 AND ex.parent_id = retro.parent_id
112 AND ex.pay_status = 'A'
113 AND ex.element_type_id = retro.element_type_id);
114
115 l_hours NUMBER DEFAULT NULL;
116
117 BEGIN
118 OPEN new_hours;
119 FETCH new_hours INTO l_hours;
120 CLOSE new_hours;
121 RETURN l_hours;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 RETURN NULL;
126 END retro_hours;
127 /******************************************************************************************************
128 FUNCTION retro_amount()
129
130 Designed to obtain amounts for completed retro transactions.
131
132 ******************************************************************************************************/
133 FUNCTION retro_amount(i_row_id IN VARCHAR2) RETURN NUMBER IS
134
135 CURSOR new_amount IS
136 SELECT retro.amount - expired.amount
137 FROM hxt_det_hours_worked_f expired,
138 hxt_det_hours_worked_f retro
139 WHERE retro.rowid = CHARTOROWID(i_row_id)
140 AND retro.parent_id = expired.parent_id
141 AND retro.element_type_id = expired.element_type_id
142 AND expired.pay_status = 'A'
143 AND expired.effective_end_date = (SELECT MAX(ex.effective_end_date)
144 FROM hxt_det_hours_worked_f ex
145 WHERE ex.effective_end_date < retro.effective_start_date
146 AND ex.parent_id = retro.parent_id
147 AND ex.pay_status = 'A'
148 AND ex.element_type_id = retro.element_type_id);
149
150 l_amount NUMBER DEFAULT NULL;
151
152 BEGIN
153 OPEN new_amount;
154 FETCH new_amount INTO l_amount;
155 CLOSE new_amount;
156 RETURN l_amount;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 RETURN NULL;
161 END retro_amount;
162 /*END RETROPAY*/
163 /*END SIR015*/
164 END HXT_USER_EXITS;