[Home] [Help]
PACKAGE BODY: APPS.EGO_MASS_UPDATE_PVT
Source
1 PACKAGE BODY EGO_MASS_UPDATE_PVT AS
2 /* $Header: EGOMUPGB.pls 120.6 2006/08/14 12:59:18 supsrini noship $ */
3
4 --Debug Profile option used to write Error_Handler.Write_Debug
5 --Profile option name = INV_DEBUG_TRACE ; User Profile Option Name = INV: Debug Trace
6 --Value: 1 (True) ; 0 (False)
7 G_DEBUG CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
8
9
10
11 --========================================================================
12 -- PROCEDURE : Item_Org_Assignment PUBLIC
13 -- PARAMETERS: p_batch_id IN NUMBER Batch Id for records in Temp Tables
14 -- p_all_request_ids OUT NOCOPY VARCHAR2 Concatenated Request Ids
15 -- ,x_return_status OUT NOCOPY VARCHAR2 Standard OUT Parameter
16 -- ,x_msg_count OUT NOCOPY NUMBER Standard OUT Parameter
17 -- ,x_msg_data OUT NOCOPY VARCHAR2 Standard OUT Parameter
18 --
19 -- DESCRIPTION : This procedure Assigns Items to Organizations in
20 -- in Mass Update flows. Items and Orgs for the assignment are
21 -- obtained from temporary tables
22 --=========================================================================
23
24 PROCEDURE item_org_assignment
25 ( p_batch_id IN NUMBER
26 ,p_all_request_ids OUT NOCOPY VARCHAR2
27 ,x_return_status OUT NOCOPY VARCHAR2
28 ,x_msg_count OUT NOCOPY NUMBER
29 ,x_msg_data OUT NOCOPY VARCHAR2
30 )
31 IS
32
33 -- This parameter determines how many Item-Org pairs are included in one batch
34 -- Commented for Bug 5464843
35 -- l_max_batch_size NUMBER;
36
37 -- Set_process_id
38 l_set_process_id NUMBER;
39
40 -- set_process_id for each batch of item-org pairs
41 -- Commented for Bug 5464843
42 -- l_batch_set_process_id NUMBER;
43
44 -- This is the Organization In Context
45 -- PUOM Query requires In-Context Org
46 l_in_context_organization_id NUMBER;
47
48 l_master_organization_id NUMBER;
49
50 -- Concurrent request id
51 l_request_id NUMBER;
52
53 -- Concurrent request submission exception
54 l_submit_failure_exc EXCEPTION;
55
56 BEGIN
57
58 x_msg_count := 0;
59 x_return_status := FND_API.G_RET_STS_SUCCESS;
60
61 -- Get the set_process_id
62 -- This will be the initial set_process_id which will
63 -- be changed in the update loop
64 -- Can we avoid this select and directly use the seq val
65 -- Using currval in the insert may perhaps give diff values if nextval is used elsewhere?
66 SELECT mtl_system_items_intf_sets_s.NEXTVAL
67 INTO l_set_process_id
68 FROM dual;
69
70 Write_Debug('In Item Org Assignment API');
71
72 -- Commented for Bug 5464843
73 /* l_max_batch_size := NVL( TO_NUMBER (FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT') )
74 , 1000
75 );
76 */
77
78 --Write_Debug('INV_CCEOI_COMMIT_POINT = '||l_max_batch_size);
79
80 --In Context org - For primary uom defaulting
81 SELECT DISTINCT organization_id
82 INTO l_in_context_organization_id
83 FROM ego_massupdate_item_tmp
84 WHERE batch_id = p_batch_id;
85
86 -- =====================================================================
87 -- Insert all Item-Org pairs into Item Open Interface Table - MSII with
88 -- the same set_process_id. This will be changed in update loop.
89 -- =====================================================================
90
91 INSERT INTO mtl_system_items_interface
92 ( process_flag
93 , set_process_id
94 , transaction_type
95 , inventory_item_id
96 , item_number -- added for Item-Org assignment across master orgs
97 , description -- added for Item-Org assignment across master orgs
98 , organization_id
99 , primary_uom_code
100 , primary_unit_of_measure
101 , cost_of_sales_account
102 , encumbrance_account
103 , sales_account
104 , expense_account
105 , last_update_date
106 , last_updated_by
107 , creation_date
108 , created_by
109 , last_update_login
110 , request_id
111 , program_application_id
112 , program_id
113 , program_update_date
114 )
115 SELECT 1, --Process_Flag
116 l_set_process_id, --SET_PROCESS_ID
117 'CREATE', --TXN_TYPE
118 emit.inventory_item_id, --ITEM_ID
119 emit.item_number,
120 emit.description,
121 emot.organization_id_child, --ORG_ID
122 NVL( (SELECT msib.primary_uom_code
123 FROM mtl_system_items_b msib, mtl_parameters mp
124 WHERE msib.organization_id = mp.master_organization_id
125 AND mp.organization_id = emot.organization_id_child
126 AND msib.inventory_item_id = emit.inventory_item_id),
127 (SELECT msib2.primary_uom_code
128 FROM mtl_system_items_b msib2
129 WHERE msib2.organization_id = emit.organization_id
130 AND msib2.inventory_item_id = emit.inventory_item_id)
131 ) UOM_CODE,
132 NVL( (SELECT msib.primary_unit_of_measure -- Can We avoid this second select
133 FROM mtl_system_items_b msib, mtl_parameters mp
134 WHERE msib.organization_id = mp.master_organization_id
135 AND mp.organization_id = emot.organization_id_child
136 AND msib.inventory_item_id = emit.inventory_item_id),
137 (SELECT msib2.primary_unit_of_measure
138 FROM mtl_system_items_b msib2
139 WHERE msib2.organization_id = emit.organization_id
140 AND msib2.inventory_item_id = emit.inventory_item_id)
141 ), --PUOM
142 mp.cost_of_sales_account,
143 mp.encumbrance_account,
144 mp.sales_account,
145 mp.expense_account,
146 SYSDATE,
147 FND_GLOBAL.user_id,
148 SYSDATE,
149 FND_GLOBAL.user_id,
150 FND_GLOBAL.login_id,
151 FND_GLOBAL.conc_request_id,
152 FND_GLOBAL.prog_appl_id,
153 FND_GLOBAL.conc_program_id,
154 SYSDATE
155 FROM ego_massupdate_item_tmp emit,
156 ego_massupdate_org_tmp emot,
157 mtl_parameters mp
158 WHERE NOT EXISTS
159 (SELECT '1'
160 FROM mtl_system_items_b msib
161 WHERE msib.organization_id = emot.organization_id_child
162 AND msib.inventory_item_id = emit.inventory_item_id)
163 AND emit.selected_flag = 'Y'
164 AND emot.org_selected_flag = 'Y'
165 AND mp.organization_id = emot.organization_id_child
166 AND emot.batch_id = p_batch_id
167 AND emot.batch_id = emit.batch_id;
168
169 -- ===========================================================
170 -- Now within a loop update each batch of item orgs in MSII
171 -- with a particular set_process_id
172 -- Also insert these records into MIRI with the starting revision
173 -- ===========================================================
174
175 -- Commented for Bug 5464843
176 -- LOOP -- Item-Org Batch
177 -- ========================================================
178 -- Now updating each batch in MSII
179 -- ========================================================
180
181 -- Can we avoid this select and directly use the seq value
182 -- If used directly then error 'Exact fetch return more than one row' occurs
183 -- because for each row the sequence value is being incremented
184
185 /* SELECT mtl_system_items_intf_sets_s.NEXTVAL
186 INTO l_batch_set_process_id
187 FROM dual;
188
189 UPDATE mtl_system_items_interface
190 SET set_process_id = l_batch_set_process_id
191 WHERE set_process_id = l_set_process_id
192 AND ROWNUM <= l_max_batch_size;
193
194
195 -- If no more rows are left to be updated then exit the loop
196 IF (SQL%ROWCOUNT = 0) THEN
197 Write_Debug('REQUEST HAS BEEN SUBMITTED FOR ALL BATCHES');
198 EXIT;
199 END IF;
200
201 -- ===========================================================
202 -- Insert corresponing data into Revisions interface table
203 -- Item_id , org_id ... are derived from MSII
204 -- These are the same item-org pairs which are included
205 -- under set_process_id = l_batch_set_process_id
206 -- ===========================================================
207
208 INSERT INTO mtl_item_revisions_interface
209 ( inventory_item_id
210 , item_number -- added for Item-Org assignment across master orgs
211 , description -- added for Item-Org assignment across master orgs
212 , organization_id
213 , revision
214 , implementation_date
215 , effectivity_date
216 , transaction_id
217 , process_flag
218 , transaction_type
219 , set_process_id
220 , last_update_date
221 , last_updated_by
222 , creation_date
223 , created_by
224 , last_update_login
225 , request_id
226 , program_application_id
227 , program_id
228 , program_update_date
229 )
230 SELECT
231 msii.inventory_item_id
232 , msii.item_number
233 , msii.description
234 , msii.organization_id
235 , mp.starting_revision
236 , SYSDATE
237 , SYSDATE
238 , MTL_SYSTEM_ITEMS_INTERFACE_S.nextval --- TRANSACTION_ID
239 , 1
240 , 'CREATE'
241 , l_set_process_id --l_batch_set_process_id
242 , SYSDATE
243 , FND_GLOBAL.user_id
244 , SYSDATE
245 , FND_GLOBAL.user_id
246 , FND_GLOBAL.login_id
247 , FND_GLOBAL.conc_request_id
248 , FND_GLOBAL.prog_appl_id
249 , FND_GLOBAL.conc_program_id
250 , SYSDATE
251 FROM mtl_system_items_interface msii,
252 mtl_parameters mp
253 WHERE msii.set_process_id = l_set_process_id
254 AND mp.organization_id = msii.organization_id;
255
256 */
257 -- ====================================================
258 -- Now submit the concurrent request for this batch
259 -- ====================================================
260
261 --Commented for Bug 5464843
262 /* --set the options for the request submission
263 IF NOT FND_REQUEST.Set_Options
264 ( implicit => 'WARNING'
265 , protected => 'YES'
266 )
267 THEN
268 RAISE l_submit_failure_exc;
269 END IF;
270 */
271 --Commented for Bug 4719882
272 --PRAGMA AUTONOMOUS_TRANSACTION;
273
274 -- submit the request
275 l_request_id := FND_REQUEST.Submit_Request
276 ( application => 'INV'
277 , program => 'INCOIN'
278 , argument1 => null
279 , argument2 => 1
280 , argument3 => 1
281 , argument4 => 1
282 , argument5 => 2
283 --5464843 , argument6 => l_batch_set_process_id
284 , argument6 => l_set_process_id
285 , argument7 => 1
286 );
287 --Commented for Bug 5464843
288 -- p_all_request_ids := p_all_request_ids||' '||l_request_id;
289 p_all_request_ids := ' ' || l_request_id;
290
291 COMMIT;
292
293 -- Write_Debug('Completed processing for Batch with set_process_id = '||l_batch_set_process_id || ' and Conc Req Id = '||l_request_id);
294
295 -- END LOOP; -- Item-Org Batch
296 --Commented for Bug 5464843
297
298
299 Write_Debug('Completed processing all Batches; Conc Req Ids are = '||p_all_request_ids);
300
301 EXCEPTION
302
303 WHEN l_submit_failure_exc THEN
304 Write_Debug('Exception while submitting request');
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 x_msg_count := 1;
307 x_msg_data := SQLERRM;
308
309 WHEN OTHERS THEN
310 Write_Debug('WHEN OTHERS Exception');
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 x_msg_count := 1;
313 x_msg_data := SQLERRM;
314
315
316 END item_org_assignment;
317
321 --
318 --========================================================================
319 -- PROCEDURE : Write_Debug
320 -- PARAMETERS: p_msg IN VARCHAR2
322 -- DESCRIPTION : Debug Message Logger
323 --=========================================================================
324
325 PROCEDURE Write_Debug (p_msg IN VARCHAR2) IS
326 BEGIN
327
328 --If Profile set to TRUE
329 IF (G_DEBUG = 1) THEN
330 Error_Handler.Write_Debug(p_msg);
331 END IF;
332
333 END;
334
335 PROCEDURE clear_temp_tables( errbuf OUT NOCOPY VARCHAR2,
336 retcode OUT NOCOPY NUMBER,
337 hours NUMBER) IS
338 BEGIN
339
340 IF(hours is NULL) then
341 errbuf := 'Provide a valid value for hours';
342 retcode :=2;
343 FND_FILE.put_line(FND_FILE.log,errbuf);
344 RETURN;
345 END IF;
346
347 DELETE ego_massupdate_item_tmp where to_date(to_char (creation_date,'hh24:mi:ss
348 dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
349 dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');
350
351 DELETE ego_massupdate_org_tmp where to_date(to_char (creation_date,'hh24:mi:ss
352 dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy') < to_date(to_char (sysdate-hours/24,'hh24:mi:ss
353 dd-mm-yyyy'),'hh24:mi:ss dd-mm-yyyy');
354
355 COMMIT;
356
357 END clear_temp_tables;
358
359 END EGO_MASS_UPDATE_PVT;