[Home] [Help]
PACKAGE BODY: APPS.OKS_IMPORT_PURGE
Source
1 PACKAGE BODY OKS_IMPORT_PURGE AS
2 -- $Header: OKSPKIMPPRGB.pls 120.1 2007/08/20 14:00:07 vmutyala noship $
3 --+=======================================================================+
4 --| Copyright (c) 2003 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OKSPKIMPPRGS.pls Created By Vamshi Mutyala |
10 --| |
11 --| DESCRIPTION |
12 --| Service Contracts Import Purge Package |
13 --| |
14 --+========================================================================
15 --===================
16 -- GLOBALS
17 --===================
18
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKS_IMPORT_PURGE';
20
21 --========================================================================
22 -- PRIVATE CONSTANTS AND VARIABLES
23 --========================================================================
24 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'oks.plsql.import.' || G_PKG_NAME;
25 G_WORKER_REQ_ID CONSTANT NUMBER := FND_GLOBAL.conc_request_id;
26 G_MODULE_HEAD CONSTANT VARCHAR2(200) := G_MODULE_NAME || '(Req Id = '||G_WORKER_REQ_ID||').';
27 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
28 G_UNEXPECTED_LOG CONSTANT BOOLEAN := fnd_log.level_unexpected >= G_LOG_LEVEL AND
29 fnd_log.TEST(fnd_log.level_unexpected, G_MODULE_HEAD);
30 G_ERROR_LOG CONSTANT BOOLEAN := G_UNEXPECTED_LOG AND fnd_log.level_error >= G_LOG_LEVEL;
31 G_EXCEPTION_LOG CONSTANT BOOLEAN := G_ERROR_LOG AND fnd_log.level_exception >= G_LOG_LEVEL;
32 G_EVENT_LOG CONSTANT BOOLEAN := G_EXCEPTION_LOG AND fnd_log.level_event >= G_LOG_LEVEL;
33 G_PROCEDURE_LOG CONSTANT BOOLEAN := G_EVENT_LOG AND fnd_log.level_procedure >= G_LOG_LEVEL;
34 G_STMT_LOG CONSTANT BOOLEAN := G_PROCEDURE_LOG AND fnd_log.level_statement >= G_LOG_LEVEL;
35
36 --=========================
37 -- PROCEDURES AND FUNCTIONS
38 --=========================
39 --========================================================================
40 -- PROCEDURE : clear_ad_cache PRIVATE
41 -- PARAMETERS: P_batch_id in Batch Number
42 -- COMMENT : This procedure will clear the AD Cache for current batch id
43 --=========================================================================
44 PROCEDURE clear_ad_cache ( P_batch_id IN NUMBER)
45 IS
46 l_stmt_num NUMBER := 0;
47 l_routine CONSTANT VARCHAR2(30) := 'clear_ad_cache';
48 l_product varchar2(30) := 'OKS';
49 l_table_name varchar2(30) := 'OKS_HEADERS_INTERFACE';
50 l_update_name varchar2(30);
51 l_status varchar2(30);
52 l_industry varchar2(30);
53 l_retstatus boolean;
54 l_table_owner varchar2(30);
55 BEGIN
56 --
57 -- Clear AD cache for the current batch id
58 --
59 IF G_PROCEDURE_LOG THEN
60 fnd_log.string(fnd_log.level_procedure,
61 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
62 'Entering with ' ||
63 'P_batch_id = ' || P_batch_id);
64 END IF;
65
66 l_retstatus := fnd_installation.get_app_info(l_product,
67 l_status,
68 l_industry,
69 l_table_owner);
70
71 IF ((l_retstatus = FALSE) OR (l_table_owner is null)) then
72 IF G_ERROR_LOG THEN
73 fnd_log.string(fnd_log.level_error,
74 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
75 'Cannot get schema name for product : '||l_product);
76 END IF;
77 raise_application_error(-20001,'Cannot get schema name for product : '||l_product);
78 END IF;
79
80 l_update_name := 'Import'||P_batch_id;
81 ad_parallel_updates_pkg.purge_processed_units(l_table_owner,
82 l_table_name,
83 l_update_name);
84
85 DELETE FROM AD_PARALLEL_UPDATES
86 WHERE OWNER = l_table_owner AND TABLE_NAME = l_table_name AND script_name = l_update_name;
87
88
89 IF G_PROCEDURE_LOG THEN
90 fnd_log.string(fnd_log.level_procedure,
91 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
92 'Exit.');
93 END IF;
94 EXCEPTION
95 WHEN FND_API.G_EXC_ERROR THEN
96 RAISE FND_API.G_EXC_ERROR;
97 WHEN OTHERS THEN
98 FND_MESSAGE.Set_Name('OKS', 'OKS_IMPORT_UNEXPECTED');
99 FND_MESSAGE.set_token('ROUTINE', G_PKG_NAME||'.'||l_routine);
100 FND_MESSAGE.set_token('MESSAGE', 'stmt_num '||l_stmt_num||' ('||SQLCODE||') '||SQLERRM);
101 FND_MSG_PUB.Add;
102 RAISE FND_API.G_EXC_ERROR;
103 END clear_ad_cache;
104
105 --========================================================================
106 -- PROCEDURE : Purge PRIVATE
107 -- PARAMETERS: X_errbuf out Error message buffer
108 -- X_retcode out Return status code
109 -- P_batch_id in Batch Id
110 -- P_Num_Workers in Number of workers
111 -- P_commit_size in Work unit size
112 -- COMMENT : This procedure is the manager in AD parallel framework
113 -- to trigger workers for purge process
114 --=========================================================================
115 PROCEDURE Purge (X_errbuf OUT NOCOPY VARCHAR2,
116 X_retcode OUT NOCOPY VARCHAR2,
117 P_batch_id IN NUMBER,
118 P_Num_Workers IN NUMBER,
119 P_commit_size IN NUMBER)
120 IS
121
122 l_stmt_num NUMBER := 0;
123 l_routine CONSTANT VARCHAR2(30) := 'Purge';
124 l_msg_data VARCHAR2(2000);
125 l_msg_count NUMBER;
126 l_row_count NUMBER := 0;
127 BEGIN
128 --
129 -- Manager processing for OKS_HEADERS_INTERFACE table
130 --
131 IF G_PROCEDURE_LOG THEN
132 fnd_log.string(fnd_log.level_procedure,
133 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
134 'Entering with ' ||
135 'P_batch_id = ' || P_batch_id ||','||
136 'P_Num_Workers = ' || P_Num_Workers ||','||
137 'P_commit_size = ' || P_commit_size );
138 END IF;
139
140 l_stmt_num := 10;
141
142 SELECT count(1) INTO l_row_count FROM OKS_HEADERS_INTERFACE
143 WHERE BATCH_ID = P_batch_id
144 AND INTERFACE_STATUS = 'S'
145 AND rownum = 1;
146
147 IF l_row_count > 0 THEN
148
149 l_stmt_num := 20;
150 clear_ad_cache(P_batch_id);
151
152 l_stmt_num := 30;
153 AD_CONC_UTILS_PKG.submit_subrequests(X_errbuf,
154 X_retcode,
155 'OKS',
156 'OKSIMPPURGWRKR',
157 P_commit_size,
158 P_Num_Workers,
159 P_batch_id
160 );
161 ELSE
162 DELETE FROM OKS_IMPORT_STATISTICS WHERE BATCH_ID = P_batch_id;
163 END IF;
164 IF G_PROCEDURE_LOG THEN
165 fnd_log.string(fnd_log.level_procedure,
166 G_MODULE_HEAD || l_routine || '.' ||l_stmt_num,
167 'Exit with ' ||
168 'X_errbuf = ' || X_errbuf ||','||
169 'X_retcode = ' || X_retcode);
170 END IF;
171 EXCEPTION
172 WHEN FND_API.G_EXC_ERROR THEN
173
174 FND_MSG_PUB.Count_And_Get
175 (p_encoded => FND_API.G_FALSE
176 ,p_count => l_msg_count
177 ,p_data => l_msg_data
178 );
179 X_retcode := '2';
180 X_errbuf := l_msg_data;
181
182 IF G_EXCEPTION_LOG THEN
183 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION, G_MODULE_HEAD || l_routine , l_msg_data);
184 END IF;
185
186 WHEN OTHERS THEN
187 X_errbuf := 'Stmt no '||l_stmt_num||' '|| SQLCODE || substr(SQLERRM, 1, 200);
188 X_retcode := '2';
189 FND_MSG_PUB.Count_And_Get
190 (p_encoded => FND_API.G_FALSE
191 ,p_count => l_msg_count
192 ,p_data => l_msg_data
193 );
194
195 IF G_EXCEPTION_LOG THEN
196 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION
197 , G_MODULE_HEAD || l_routine ||'.others_exc'
198 , 'others: ' || X_errbuf || ' ' || substr(l_msg_data, 1,250)
199 );
200 END IF;
201
202 END Purge;
203
204
205 END OKS_IMPORT_PURGE;