[Home] [Help]
PACKAGE BODY: APPS.IEX_TERR_ASSIGNMENT_CLEANUP
Source
1 PACKAGE BODY IEX_TERR_ASSIGNMENT_CLEANUP AS
2 /* $Header: iexttacb.pls 120.1 2005/10/28 05:06:05 lkkumar noship $ */
3
4 ---------------------------------------------------------------------------
5 -- Start of Comments
6 ---------------------------------------------------------------------------
7 -- PACKAGE NAME: IEX_TERR_ASSIGNMENT_CLEANUP
8 -- ---------------------------------------------------------------------
9 -- PURPOSE
10 --
11 -- Dependent Package for the concurrent program "Generate Access Records".
12 -- Performs any pre and post territory assignment cleanups
13 --
14 -- Procedures:
15 -- (see below for specification)
16 --
17 -- NOTES
18 -- This package to be called from the concurrent program
19 -- "Generate Access Records"
20 --
21 -- HISTORY
22 -- Copied from AS_TERR_ASSIGNMENT_CLEANUP to remove the dependency
23 ---------------------------------------------------------------------------
24
25
26 /*-------------------------------------------------------------------------+
27 | PRIVATE CONSTANTS
28 +-------------------------------------------------------------------------*/
29 G_PKG_NAME CONSTANT VARCHAR2(30):='IEX_TERR_ASSIGNMENT_CLEANUP';
30 G_FILE_NAME CONSTANT VARCHAR2(12):='iexttacb.pls';
31
32 -- ffang 121302, bug 2703096
33 -- Number of record to do an incremental commit
34 G_NUM_REC CONSTANT NUMBER:=10000;
35 G_DEL_REC CONSTANT NUMBER:=10001;
36 deadlock_detected EXCEPTION;
37 PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
38
39
40 /*-------------------------------------------------------------------------+
41 | PRIVATE DATATYPES
42 +-------------------------------------------------------------------------*/
43
44 /*-------------------------------------------------------------------------*
45 | PRIVATE VARIABLES
46 *-------------------------------------------------------------------------*/
47
48 /*-------------------------------------------------------------------------*
49 | PRIVATE ROUTINES SPECIFICATION
50 *-------------------------------------------------------------------------*/
51
52 /*------------------------------------------------------------------------*
53 | PUBLIC ROUTINES
54 *------------------------------------------------------------------------*/
55
56 /*-------------------------------------------------------------------------*
57 | PUBLIC ROUTINE
58 | Cleanup_Duplicate_Resources
59 |
60 | PURPOSE
61 |
62 | NOTES
63 |
64 |
65 | HISTORY
66 *-------------------------------------------------------------------------*/
67 PROCEDURE Cleanup_Duplicate_Resources(
68 x_errbuf OUT NOCOPY VARCHAR2,
69 x_retcode OUT NOCOPY VARCHAR2,
70 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
71 IS
72
73 BEGIN
74 NULL;
75 EXCEPTION
76 WHEN others THEN
77 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_TERR_ASSIGNMENT_CLEANUP::Cleanup_Duplicate_Resources');
78 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
79 ' SQLERRM: ' || SQLERRM);
80 x_errbuf := SQLERRM;
81 x_retcode := SQLCODE;
82 RAISE;
83
84 END Cleanup_Duplicate_Resources;
85
86
87 /*-------------------------------------------------------------------------*
88 | PUBLIC ROUTINE
89 | Cleanup_Terrritory_Accesses
90 |
91 | PURPOSE
92 |
93 |
94 | NOTES
95 |
96 |
97 | HISTORY
98 *-------------------------------------------------------------------------*/
99 PROCEDURE Cleanup_Terrritory_Accesses(
100 x_errbuf OUT NOCOPY VARCHAR2,
101 x_retcode OUT NOCOPY VARCHAR2,
102 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
103 IS
104 BEGIN
105 NULL;
106 END Cleanup_Terrritory_Accesses;
107
108
109 /*-------------------------------------------------------------------------*
110 | PUBLIC ROUTINE
111 | Perform_Account_Cleanup
112 |
113 | PURPOSE
114 | Updates the unqualified account records in as_accesses_all_all
115 | table.
116 |
117 | NOTES
118 |
119 |
120 | HISTORY
121 *-------------------------------------------------------------------------*/
122
123
124 PROCEDURE Perform_Account_Cleanup(
125 x_errbuf OUT NOCOPY VARCHAR2,
126 x_retcode OUT NOCOPY VARCHAR2,
127 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
128 IS
129
130 TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
131 l_customer_id customer_id_list;
132 l_customer_id_empty customer_id_list;
133
134 TYPE access_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
135 l_access_id access_id_list;
136 l_access_id_empty access_id_list;
137
138 l_flag BOOLEAN;
139 l_first NUMBER;
140 l_last NUMBER;
141 l_var NUMBER;
142 l_attempts NUMBER := 0;
143
144 l_worker_id NUMBER;
145
146 l_del_flag BOOLEAN:=FALSE;
147 l_limit_flag BOOLEAN := FALSE;
148 l_max_fetches NUMBER := 10000;
149 l_loop_count NUMBER := 0;
150
151
152 CURSOR del_acct(c_worker_id number) IS
153 SELECT distinct trans_object_id
154 FROM JTF_TAE_1001_ACCOUNT_TRANS
155 WHERE worker_id=c_worker_id;
156
157 BEGIN
158 IEX_TERR_WINNERS_PUB.Print_Debug('*** iexttacb.pls::IEX_TERR_ASSIGNMENT_CLEANUP::Perform_Account_Cleanup() ***');
159
160 /** Commented because we are not writing anything into as_access_all_all **/
161
162 EXCEPTION
163 WHEN others THEN
164 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_TERR_ASSIGNMENT_CLEANUP::Perform_Account_Cleanup');
165 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
166 ' SQLERRM: ' || SQLERRM);
167 x_errbuf := SQLERRM;
168 x_retcode := SQLCODE;
169 RAISE;
170
171 END Perform_Account_Cleanup;
172
173
174 /*-------------------------------------------------------------------------*
175 | PUBLIC ROUTINE
176 | Perform_Chgd_Accts_Cleanup
177 |
178 | PURPOSE
179 | To delete all the records in as_changed_accounts_all
180 | where request_id is not null ( only in NEW mode )
181 |
182 | NOTES
183 |
184 |
185 | HISTORY
186 *-------------------------------------------------------------------------*/
187
188 PROCEDURE Perform_Chgd_Accts_Cleanup(
189 x_errbuf OUT NOCOPY VARCHAR2,
190 x_retcode OUT NOCOPY VARCHAR2,
191 p_terr_globals IN IEX_TERR_WINNERS_PUB.TERR_GLOBALS)
192 IS
193 TYPE customer_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
194 TYPE last_update_date_list is TABLE of DATE INDEX BY BINARY_INTEGER;
195 TYPE last_updated_by_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
196 TYPE creation_date_list is TABLE of DATE INDEX BY BINARY_INTEGER;
197 TYPE created_by_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
198 TYPE last_update_login_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
199 TYPE change_type_list is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER;
200 TYPE org_id_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
201
202 l_customer_id customer_id_list;
203 l_last_update_date last_update_date_list;
204 l_last_updated_by last_updated_by_list;
205 l_creation_date creation_date_list;
206 l_created_by created_by_list;
207 l_last_update_login last_update_login_list;
208 l_change_type change_type_list;
209 l_org_id org_id_list;
210
211 l_flag BOOLEAN;
212 l_first NUMBER;
213 l_last NUMBER;
214 l_var NUMBER;
215
216
217 l_new_mode_flag varchar2(1):='Y';
218
219 waiting_for_resource EXCEPTION;
220 PRAGMA EXCEPTION_INIT(waiting_for_resource, -54);
221
222 l_attempts NUMBER := 0;
223
224 l_status VARCHAR2(2);
225 l_industry VARCHAR2(2);
226 l_oracle_schema VARCHAR2(32) := 'OSM';
227 l_schema_return BOOLEAN;
228
229 BEGIN
230
231 l_schema_return := FND_INSTALLATION.get_app_info('IEX', l_status, l_industry, l_oracle_schema);
232
233
234 IEX_TERR_WINNERS_PUB.g_debug_flag:=p_terr_globals.debug_flag;
235
236 /** Commented because we are not writing anything into as_access_all_all **/
237
238 EXCEPTION
239 WHEN others THEN
240 IEX_TERR_WINNERS_PUB.Print_Debug('Exception: others in IEX_TERR_ASSIGNMENT_CLEANUP::Perform_Chgd_Accts_Cleanup');
241 IEX_TERR_WINNERS_PUB.Print_Debug('SQLCODE: ' || to_char(SQLCODE) ||
242 ' SQLERRM: ' || SQLERRM);
243 x_errbuf := SQLERRM;
244 x_retcode := SQLCODE;
245 --RAISE;
246
247 END Perform_Chgd_Accts_Cleanup;
248
249 END IEX_TERR_ASSIGNMENT_CLEANUP;