1 PACKAGE PA_PURGE_PUB AS
2 -- $Header: PAXPURGS.pls 120.2 2007/10/20 12:16:02 bifernan noship $
3
4 --
5 -- PROCEDURE
6 -- START_PROCESS
7 -- PURPOSE
8 -- This API is called from the executable of the concurrent program :
9 -- ADM: Purge Obsolete Projects Data
10 -- Based on the Purge Type Value selected in the Concurrent Program ,
11 -- It calls respective APIs to do the purging.
12 --
13 -- Parameter Name In/Out Data Type Null? Default Value Description
14 -- ------------- ------ ---------- ------ ------------- ---------------------------------
15 -- P_PURGE_TYPE IN VARCHAR2 NOT NULL Indicates the purge option.
16 -- Valid values are: ALL DAILY_FCST_INFO PROJECTS_WORKFLOWS REPORTING_EXCEPTIONS
17 --
18 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
19 -- Valid values are: 'Y' 'N'
20 --
21 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
22 -- ERRBUF OUT VARCHAR2 N/A N/A Indicates the error buffer to the concurrent program.
23 -- RETCODE OUT VARCHAR2 N/A N/A Indicates the return code to the concurrent program.
24
25 -- HISTORY
26 -- avaithia 01-March-2006 Created
27 --
28
29 PROCEDURE START_PROCESS
30 (
31 errbuf OUT NOCOPY VARCHAR2 ,
32 retcode OUT NOCOPY VARCHAR2 ,
33 p_purge_type IN VARCHAR2 ,
34 p_debug_mode IN VARCHAR2 default 'N' ,
35 p_commit_size IN NUMBER default 10000
36 );
37
38 --
39 -- PROCEDURE
40 -- PURGE_FORECAST_ITEMS
41 -- PURPOSE
42 -- This API purges unused forecast item data from the 3 tables pa_forecast_items ,pa_forecast_item_details
43 -- and pa_fi_amount_details
44 --
45 -- Parameter Name In/Out Data Type Null? Default Value Description
46 -- ------------- ------ ---------- ------ ------------- ---------------------------------
47 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
48 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
49 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
50 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
51 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
52 --
53 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
54 -- in the message stack.
55 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
56 -- if only one error exists.
57
58 -- HISTORY
59 -- avaithia 01-March-2006 Created
60 --
61
62 PROCEDURE PURGE_FORECAST_ITEMS
63 (
64 p_debug_mode IN VARCHAR2 default 'N' ,
65 p_commit_size IN NUMBER default 10000 ,
66 p_request_id IN NUMBER ,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2
70 );
71
72 --
73 -- PROCEDURE
74 -- PURGE_PROJ_WORKFLOW
75 -- PURPOSE
76 -- This API purges unused denormalized workflow data from 3 tables pa_wf_processes , pa_wf_process_details
77 -- and pa_wf_ntf_performers
78 --
79 --
80 -- Parameter Name In/Out Data Type Null? Default Value Description
81 -- ------------- ------ ---------- ------ ------------- ---------------------------------
82 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
83 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
84 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
85 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
86 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
87 --
88 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
89 -- in the message stack.
90 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
91 -- if only one error exists.
92
93 -- HISTORY
94 -- avaithia 01-March-2006 Created
95 --
96
97 PROCEDURE PURGE_PROJ_WORKFLOW
98 (
99 p_debug_mode IN VARCHAR2 default 'N' ,
100 p_commit_size IN NUMBER default 10000 ,
101 p_request_id IN NUMBER ,
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_msg_count OUT NOCOPY NUMBER,
104 x_msg_data OUT NOCOPY VARCHAR2
105 );
106
107 --
108 -- PROCEDURE
109 -- PURGE_REPORTING_EXCEPTIONS
110 -- PURPOSE
111 -- This API will purge unused reporting exception data from pa_reporting_exceptions
112 --
113 -- Parameter Name In/Out Data Type Null? Default Value Description
114 -- ------------- ------ ---------- ------ ------------- ---------------------------------
115 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
116 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
117 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
118 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
119 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
120 --
121 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
122 -- in the message stack.
123 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
124 -- if only one error exists.
125
126 -- HISTORY
127 -- avaithia 01-March-2006 Created
128 --
129
130 PROCEDURE PURGE_REPORTING_EXCEPTIONS
131 (
132 p_debug_mode IN VARCHAR2 default 'N' ,
133 p_commit_size IN NUMBER default 10000 ,
134 p_request_id IN NUMBER ,
135 x_return_status OUT NOCOPY VARCHAR2,
136 x_msg_count OUT NOCOPY NUMBER,
137 x_msg_data OUT NOCOPY VARCHAR2
138 );
139
140 --
141 -- PROCEDURE
142 -- PURGE_ORG_AUTHORITY
143 -- PURPOSE
144 -- This API purges organization authority records of all terminated employees or contingent
145 -- workers whose termination date is earlier than system date
146 --
147 --
148 -- Parameter Name In/Out Data Type Null? Default Value Description
149 -- ------------- ------ ---------- ------ ------------- ---------------------------------
150 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
151 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
152 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
153 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
154 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
155 --
156 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
157 -- in the message stack.
158 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
159 -- if only one error exists.
160
161 -- HISTORY
162 -- bifernan 16-October-2007 Created
163 --
164
165 PROCEDURE PURGE_ORG_AUTHORITY
166 (
167 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
168 p_commit_size IN NUMBER DEFAULT 10000 ,
169 p_request_id IN NUMBER ,
170 x_return_status OUT NOCOPY VARCHAR2 ,
171 x_msg_count OUT NOCOPY NUMBER ,
172 x_msg_data OUT NOCOPY VARCHAR2
173 );
174
175 --
176 -- PROCEDURE
177 -- PURGE_PJI_DEBUG
178 -- PURPOSE
179 -- This API purges the tables used by project performance summarization model to store
180 -- debug information.
181 --
182 -- Parameter Name In/Out Data Type Null? Default Value Description
183 -- ------------- ------ ---------- ------ ------------- ---------------------------------
184 -- P_DEBUG_MODE IN VARCHAR2 NOT NULL 'N' Indicates the debug option.
185 -- P_COMMIT_SIZE IN NUMBER NOT NULL 10000 Indicates the commit size.
186 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
187 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
188 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
189 --
190 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
191 -- in the message stack.
192 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
193 -- if only one error exists.
194
195 -- HISTORY
196 -- bifernan 16-October-2007 Created
197 --
198
199 PROCEDURE PURGE_PJI_DEBUG
200 (
201 p_debug_mode IN VARCHAR2 DEFAULT 'N' ,
202 p_commit_size IN NUMBER DEFAULT 10000 ,
203 p_request_id IN NUMBER ,
204 x_return_status OUT NOCOPY VARCHAR2 ,
205 x_msg_count OUT NOCOPY NUMBER ,
206 x_msg_data OUT NOCOPY VARCHAR2
207 );
208
209 --
210 -- PROCEDURE
211 -- PRINT_OUTPUT_REPORT
212 -- PURPOSE
213 -- This API will print the output report to concurrent log file.
214 -- Parameter Name In/Out Data Type Null? Default Value Description
215 -- ------------- ------ ---------- ------ ------------- ---------------------------------
216 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
217 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
218 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
219 --
220 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
221 -- in the message stack.
222 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
223 -- if only one error exists.
224
225 -- HISTORY
226 -- avaithia 01-March-2006 Created
227 --
228
229 PROCEDURE PRINT_OUTPUT_REPORT
230 (
231 p_request_id IN NUMBER ,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2
235 );
236
237 --
238 -- PROCEDURE
239 -- INSERT_PURGE_LOG
240 -- PURPOSE
241 -- This API will populate the log table for deleted table information.
242 --
243 -- Parameter Name In/Out Data Type Null? Default Value Description
244 -- ------------- ------ ---------- ------ ------------- ---------------------------------
245 -- P_REQUEST_ID IN NUMBER NOT NULL Indicates the concurrent request id.
246 -- P_TABLE_NAME IN VARCHAR2 NOT NULL Indicates the table name deleted.
247 -- P_ROWS_DELETED IN NUMBER NOT NULL Indicates the number of rows deleted.
248 -- X_RETURN_STATUS OUT VARCHAR2 N/A N/A Indicates the return status of the API.
249 -- Valid values are: 'S' for Success 'E' for Error 'U' for Unexpected Error
250 --
251 -- X_MSG_COUNT OUT NUMBER N/A N/A Indicates the number of error messages
252 -- in the message stack.
253 -- X_MSG_DATA OUT VARCHAR2 N/A N/A Indicates the error message text
254 -- if only one error exists.
255
256 -- HISTORY
257 -- avaithia 01-March-2006 Created
258 --
259
260 PROCEDURE INSERT_PURGE_LOG
261 (
262 p_request_id IN NUMBER ,
263 p_table_name IN VARCHAR2 ,
264 p_rows_deleted IN NUMBER ,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2
268 );
269
270 END PA_PURGE_PUB;