[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_WORKFLOW_API_PKG
Source
1 PACKAGE BODY pay_ca_workflow_api_pkg AS
2 /* $Header: paycawfapipkg.pkb 120.0 2005/05/29 11:10 appldev 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_workflow_api_pkg
21
22 Description :
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 24-JUN-2004 ssouresr 115.0 Created
29 ******************************************************************************/
30
31
32 /* ************************************************************************
33 This procedure gets the ROE assignment info and create the document.
34 ************************************************************************ */
35
36 PROCEDURE get_roe_assignment_info (document_id in varchar2,
37 display_type in varchar2,
38 document in out nocopy varchar2,
39 document_type in out nocopy varchar2) IS
40
41 ln_request_id number(15);
42 ln_business_group_id number(15);
43 ld_payroll_date_paid varchar2(20);
44 ln_complete number(9);
45 ln_error number(9);
46 ln_unprocessed number(9);
47 X_Segment1 VARCHAR2(240);
48 X_Segment2 VARCHAR2(240);
49 X_Segment3 VARCHAR2(240);
50 l_space varchar2(25);
51
52
53 CURSOR asg_roe_info_cur (p_req_id number) IS
54 SELECT to_char(count(paa.assignment_action_id)) ASG_COUNT,
55 paa.action_status ASG_STATUS
56 FROM pay_assignment_actions paa,
57 pay_payroll_actions ppa
58 WHERE paa.payroll_action_id = ppa.payroll_action_id
59 AND ppa.request_id = to_number(p_req_id)
60 AND ppa.business_group_id = ln_business_group_id
61 AND ppa.effective_date =
62 trunc(to_date(ld_payroll_date_paid,'YYYY/MM/DD HH24:MI:SS'))
63 AND ppa.action_type = 'X'
64 AND ppa.report_type = 'ROE'
65 AND paa.source_action_id is null
66 AND paa.run_type_id is null
67 GROUP BY paa.action_status;
68
69
70 BEGIN
71
72 l_space := ' ';
73 ln_complete := 0;
74 ln_error := 0;
75 ln_unprocessed := 0;
76
77 hr_utility.trace('Before ROE Assignment Information');
78 hr_utility.trace('Document Id '||document_id);
79
80 ln_request_id := substr(document_id,1,instr(document_id,':') -1 );
81 ln_business_group_id := substr(document_id,instr(document_id,':',1,1)+1 ,
82 instr(document_id,':',1,2) -instr(document_id,':',1,1)-1 );
83 ld_payroll_date_paid := substr(document_id,instr(document_id,':',1,2)+1 );
84
85 hr_utility.trace('ln_request_id = '||ln_request_id);
86 hr_utility.trace('ln_business_group_id = '||ln_business_group_id);
87 hr_utility.trace('ld_payroll_date_paid = '||ld_payroll_date_paid);
88
89 FOR asg_roe_info_rec IN asg_roe_info_cur (ln_request_id)
90 LOOP
91
92 if asg_roe_info_rec.ASG_STATUS = 'C' then
93 ln_complete := asg_roe_info_rec.ASG_COUNT;
94 elsif asg_roe_info_rec.ASG_STATUS = 'E' then
95 ln_error := asg_roe_info_rec.ASG_COUNT;
96 elsif asg_roe_info_rec.ASG_STATUS = 'U' then
97 ln_unprocessed := asg_roe_info_rec.ASG_COUNT;
98 end if;
99
100 END LOOP ;
101
102 X_segment1 := 'Total Assignments Successfully Processed :'||to_char(ln_complete)||l_space||wf_core.newline;
103 X_segment2 := 'Total Assignments With Error :'||to_char(ln_error)||l_space||wf_core.newline;
104 X_segment3 := 'Total Assignments Not Processed :'||to_char(ln_unprocessed)||l_space||wf_core.newline;
105
106 document := '<p>'||X_segment1||'<br>'||X_segment2||'<br>'||X_segment3||'<br></p>';
107
108 document := document || l_space||wf_core.newline;
109
110 hr_utility.trace('Document '||document);
111
112
113 document_type := 'text/html';
114
115 exception when others then
116 hr_utility.trace('Exception Others');
117
118 end get_roe_assignment_info;
119
120
121 /* ************************************************************************
122 This procedure gets the ROE Magnetic Media assignment info and creates the document.
123 ************************************************************************ */
124
125
126 PROCEDURE get_roe_mag_assignment_info (document_id in varchar2,
127 display_type in varchar2,
128 document in out nocopy varchar2,
129 document_type in out nocopy varchar2) IS
130
131 ln_request_id number(15);
132 ln_business_group_id number(15);
133 ld_payroll_date_paid varchar2(20);
134 ln_complete number(9);
135 ln_error number(9);
136 ln_unprocessed number(9);
137 X_Segment1 VARCHAR2(240);
138 X_Segment2 VARCHAR2(240);
139 X_Segment3 VARCHAR2(240);
140 l_space varchar2(25);
141
142
143 CURSOR asg_roe_mag_info_cur (p_req_id number) IS
144 SELECT to_char(count(paa.assignment_action_id)) ASG_COUNT,
145 paa.action_status ASG_STATUS
146 FROM pay_assignment_actions paa,
147 pay_payroll_actions ppa
148 WHERE paa.payroll_action_id = ppa.payroll_action_id
149 AND ppa.request_id = to_number(p_req_id)
150 AND ppa.business_group_id = ln_business_group_id
151 AND ppa.effective_date =
152 trunc(to_date(ld_payroll_date_paid,'YYYY/MM/DD HH24:MI:SS'))
153 AND ppa.action_type = 'X'
154 AND ppa.report_type = 'MAG_ROE'
155 AND paa.source_action_id is null
156 AND paa.run_type_id is null
157 GROUP BY paa.action_status;
158
159
160 BEGIN
161
162 l_space := ' ';
163 ln_complete := 0;
164 ln_error := 0;
165 ln_unprocessed := 0;
166
167 hr_utility.trace('Before ROE MAG Assignment Information');
168 hr_utility.trace('Document Id '||document_id);
169
170 ln_request_id := substr(document_id,1,instr(document_id,':') -1 );
171 ln_business_group_id := substr(document_id,instr(document_id,':',1,1)+1 ,
172 instr(document_id,':',1,2) -instr(document_id,':',1,1)-1 );
173 ld_payroll_date_paid := substr(document_id,instr(document_id,':',1,2)+1 );
174
175 hr_utility.trace('ln_request_id = '||ln_request_id);
176 hr_utility.trace('ln_business_group_id = '||ln_business_group_id);
177 hr_utility.trace('ld_payroll_date_paid = '||ld_payroll_date_paid);
178
179 FOR asg_roe_mag_info_rec IN asg_roe_mag_info_cur (ln_request_id)
180 LOOP
181
182 if asg_roe_mag_info_rec.ASG_STATUS = 'C' then
183 ln_complete := asg_roe_mag_info_rec.ASG_COUNT;
184 elsif asg_roe_mag_info_rec.ASG_STATUS = 'E' then
185 ln_error := asg_roe_mag_info_rec.ASG_COUNT;
186 elsif asg_roe_mag_info_rec.ASG_STATUS = 'U' then
187 ln_unprocessed := asg_roe_mag_info_rec.ASG_COUNT;
188 end if;
189
190 END LOOP ;
191
192 X_segment1 := 'Total Assignments Successfully Processed :'||to_char(ln_complete)||l_space||wf_core.newline;
193 X_segment2 := 'Total Assignments With Error :'||to_char(ln_error)||l_space||wf_core.newline;
194 X_segment3 := 'Total Assignments Not Processed :'||to_char(ln_unprocessed)||l_space||wf_core.newline;
195
196 document := '<p>'||X_segment1||'<br>'||X_segment2||'<br>'||X_segment3||'<br></p>';
197
198 document := document || l_space||wf_core.newline;
199
200 hr_utility.trace('Document '||document);
201
202 document_type := 'text/html';
203
204 exception when others then
205 hr_utility.trace('Exception Others');
206
207 end get_roe_mag_assignment_info;
208
209 end pay_ca_workflow_api_pkg;