eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PON_GLOBAL_UPDATE_PKG

Source


1 PACKAGE BODY PON_GLOBAL_UPDATE_PKG AS
2 -- $Header: PON_GLOBAL_UPDATE_PKG.plb 120.1 2012/01/05 05:42:34 irasoolm noship $
3 
4 -- Indicate if the debug mode is on
5 g_debug_mode    VARCHAR2(10);
6 -- module name for logging message
7 g_module_prefix CONSTANT VARCHAR2(40) := 'pon.plsql.pon_global_update_pkg.';
8 
9 PROCEDURE GLOBAL_LINE_UPDATE
10 (
11 p_auction_header_id IN NUMBER,
12 p_src_line_number       IN NUMBER ,
13 p_dest_line_ids_tbl IN PO_TBL_NUMBER,
14 p_option_from_date IN DATE,
15 p_option_to_date IN DATE,
16 p_clm_need_by_date IN DATE,
17 p_pop_start_date IN DATE,
18 p_pop_end_date IN DATE,
19 p_ship_to_loc_id IN NUMBER,
20 p_note_to_suppliers IN VARCHAR2 ,
21 x_result OUT NOCOPY VARCHAR2
22 )
23 
24 IS
25 
26 l_module VARCHAR2(100) := 'GLOBAL_LINE_UPDATE';
27 
28 l_dest_line_ids  VARCHAR2(32000) := '';
29 extension_id_tbl  PO_TBL_NUMBER;
30 attr_group_id_tbl PO_TBL_NUMBER;
31 sol_line_num_tbl    PO_TBL_NUMBER;
32 data_level_id_tbl  PO_TBL_NUMBER;
33 uda_template_id_tbl  PO_TBL_NUMBER;
34 l_count NUMBER :=0;
35 
36 
37 BEGIN
38 
39   IF g_debug_mode IS NULL THEN
40     g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
41   END IF;
42 
43   SAVEPOINT global_update;
44 
45 
46 
47   LOG_MESSAGE(l_module,'BEGIN: p_auction_header_id ' || p_auction_header_id
48                         || ' p_src_line_number '|| p_src_line_number );
49 
50     UPDATE pon_auction_item_prices_all
51     SET
52     clm_option_from_date = Decode(clm_option_indicator,'O',Nvl(p_option_from_date,clm_option_from_date),NULL),
53     clm_option_to_date = Decode(clm_option_indicator,'O',Nvl(p_option_to_date,clm_option_to_date),NULL),
54     note_to_bidders = Nvl(p_note_to_suppliers,note_to_bidders),
55 	  last_update_date  = SYSDATE,
56     last_updated_by = fnd_global.user_id,
57     last_update_login = fnd_global.login_id
58     WHERE auction_header_id = p_auction_header_id
59     AND line_Number in (select column_value from table(p_dest_line_ids_tbl));
60 
61     -- Ship to location is non-editable for autocreated lines
62     UPDATE pon_auction_item_prices_all
63     SET
64     ship_to_location_id = Decode(Nvl(clm_info_flag,'N'),'N',
65                               Nvl(p_ship_to_loc_id,ship_to_location_id),NULL)
66     WHERE auction_header_id = p_auction_header_id
67     AND line_Number in (select column_value from table(p_dest_line_ids_tbl))
68     AND Nvl(LINE_ORIGINATION_CODE,'NO') <> 'REQUISITION';
69 
70 
71     /*
72     * User can't give both CLM need by date and POP dates for a line
73     * while creating/updating a line
74     * This condition is enforced in Global update process
75     * as below
76     */
77     IF p_clm_need_by_date IS NOT NULL THEN
78       UPDATE pon_auction_item_prices_all
79       SET
80       clm_need_by_date = Decode(Nvl(clm_info_flag,'N'),'N', p_clm_need_by_date,NULL),
81       need_by_start_date = NULL,
82       need_by_date = NULL
83       WHERE auction_header_id = p_auction_header_id
84       AND line_Number in (select column_value from table(p_dest_line_ids_tbl));
85     ELSIF (p_pop_start_date IS NOT NULL AND p_pop_end_date IS NOT NULL) THEN
86       UPDATE pon_auction_item_prices_all paip
87       SET
88       need_by_start_date =Decode(Nvl(clm_info_flag,'N'),'N', p_pop_start_date,NULL),
89       need_by_date = Decode(Nvl(clm_info_flag,'N'),'N', p_pop_end_date,NULL),
90       clm_need_by_date = NULL
91       WHERE auction_header_id = p_auction_header_id
92       AND line_Number in (select column_value from table(p_dest_line_ids_tbl))
93       AND NOT EXISTS -- clm_need_by_date is mandatory for planned items
94       (SELECT 'x'
95         FROM   mtl_system_items_kfv msi, financials_system_params_all fsp
96         WHERE  nvl(fsp.org_id, -9999) = nvl(paip.org_id,-9999) and
97                 msi.organization_id = fsp.inventory_organization_id and
98         msi.inventory_item_id = paip.item_id and
99         (msi.INVENTORY_PLANNING_CODE in (1, 2) or msi.MRP_PLANNING_CODE in
100         (3, 4, 7, 8, 9)));
101     END IF;
102 
103     LOG_MESSAGE(l_module,'Named attributes updated ');
104 
105     MERGE INTO pon_auction_item_prices_ext_b DESTLINE
106     USING (
107     SELECT
108     attr_group_id,
109     LINE_NUMBER,
110     data_level_id,
111     last_update_date,
112     last_updated_by,
113     last_update_login,
114     creation_date,
115     created_by,
116     pk1_value,
117     pk2_value,
118     pk3_value,
119     pk4_value,
120     pk5_value,
121     c_ext_attr1,
122     c_ext_attr2,
123     c_ext_attr3,
124     c_ext_attr4,
125     c_ext_attr5,
126     c_ext_attr6,
127     c_ext_attr7,
128     c_ext_attr8,
129     c_ext_attr9,
130     c_ext_attr10,
131     c_ext_attr11,
132     c_ext_attr12,
133     c_ext_attr13,
134     c_ext_attr14,
135     c_ext_attr15,
136     c_ext_attr16,
137     c_ext_attr17,
138     c_ext_attr18,
139     c_ext_attr19,
140     c_ext_attr20,
141     c_ext_attr21,
142     c_ext_attr22,
143     c_ext_attr23,
144     c_ext_attr24,
145     c_ext_attr25,
146     c_ext_attr26,
147     c_ext_attr27,
148     c_ext_attr28,
149     c_ext_attr29,
150     c_ext_attr30,
151     c_ext_attr31,
152     c_ext_attr32,
153     c_ext_attr33,
154     c_ext_attr34,
155     c_ext_attr35,
156     c_ext_attr36,
157     c_ext_attr37,
158     c_ext_attr38,
159     c_ext_attr39,
160     c_ext_attr40,
161     n_ext_attr1,
162     n_ext_attr2,
163     n_ext_attr3,
164     n_ext_attr4,
165     n_ext_attr5,
166     n_ext_attr6,
167     n_ext_attr7,
168     n_ext_attr8,
169     n_ext_attr9,
170     n_ext_attr10,
171     n_ext_attr11,
172     n_ext_attr12,
173     n_ext_attr13,
174     n_ext_attr14,
175     n_ext_attr15,
176     n_ext_attr16,
177     n_ext_attr17,
178     n_ext_attr18,
179     n_ext_attr19,
180     n_ext_attr20,
181     uom_ext_attr1,
182     uom_ext_attr2,
183     uom_ext_attr3,
184     uom_ext_attr4,
185     uom_ext_attr5,
186     uom_ext_attr6,
187     uom_ext_attr7,
188     uom_ext_attr8,
189     uom_ext_attr9,
190     uom_ext_attr10,
191     uom_ext_attr11,
192     uom_ext_attr12,
193     uom_ext_attr13,
194     uom_ext_attr14,
195     uom_ext_attr15,
196     uom_ext_attr16,
197     uom_ext_attr17,
198     uom_ext_attr18,
199     uom_ext_attr19,
200     uom_ext_attr20,
201     d_ext_attr1,
202     d_ext_attr2,
203     d_ext_attr3,
204     d_ext_attr4,
205     d_ext_attr5,
206     d_ext_attr6,
207     d_ext_attr7,
208     d_ext_attr8,
209     d_ext_attr9,
210     d_ext_attr10,
211     uda_template_id,
212     auctioN_header_id
213     FROM pon_auction_item_prices_ext_b WHERE AUCTION_HEADER_ID =  p_auction_header_id
214     and LINE_NUMBER = p_src_line_number) SRCLINE
215     ON
216     (DESTLINE.LINE_NUMBER IN (select column_value from table(p_dest_line_ids_tbl))  AND DESTLINE.attr_group_id = SRCLINE.attr_group_id AND  SRCLINE.AUCTION_HEADER_ID =  p_auction_header_id )
217     WHEN MATCHED THEN
218     UPDATE
219     SET
220     DESTLINE.last_update_date = SYSDATE ,
221     DESTLINE.last_updated_by = SRCLINE.last_updated_by,
222     DESTLINE.last_update_login = SRCLINE.last_update_login,
223     DESTLINE.created_by = SRCLINE.created_by,
224     DESTLINE.creation_date = SYSDATE,
225     DESTLINE.c_ext_attr1 = nvl(SRCLINE.c_ext_attr1 , DESTLINE.c_ext_attr1),
226     DESTLINE.c_ext_attr2 = nvl(SRCLINE.c_ext_attr2 , DESTLINE.c_ext_attr2),
227     DESTLINE.c_ext_attr3 = nvl(SRCLINE.c_ext_attr3 , DESTLINE.c_ext_attr3),
228     DESTLINE.c_ext_attr4 = nvl(SRCLINE.c_ext_attr4 , DESTLINE.c_ext_attr4),
229     DESTLINE.c_ext_attr5 = nvl(SRCLINE.c_ext_attr5 , DESTLINE.c_ext_attr5),
230     DESTLINE.c_ext_attr6 = nvl(SRCLINE.c_ext_attr6 , DESTLINE.c_ext_attr6),
231     DESTLINE.c_ext_attr7 = nvl(SRCLINE.c_ext_attr7 , DESTLINE.c_ext_attr7),
232     DESTLINE.c_ext_attr8 = nvl(SRCLINE.c_ext_attr8 , DESTLINE.c_ext_attr8),
233     DESTLINE.c_ext_attr9 = nvl(SRCLINE.c_ext_attr9 , DESTLINE.c_ext_attr9),
234     DESTLINE.c_ext_attr10 = nvl(SRCLINE.c_ext_attr10 , DESTLINE.c_ext_attr10),
235     DESTLINE.c_ext_attr11 = nvl(SRCLINE.c_ext_attr11 , DESTLINE.c_ext_attr11),
236     DESTLINE.c_ext_attr12 = nvl(SRCLINE.c_ext_attr12 , DESTLINE.c_ext_attr12),
237     DESTLINE.c_ext_attr13 = nvl(SRCLINE.c_ext_attr13 , DESTLINE.c_ext_attr13),
238     DESTLINE.c_ext_attr14 = nvl(SRCLINE.c_ext_attr14 , DESTLINE.c_ext_attr14),
239     DESTLINE.c_ext_attr15 = nvl(SRCLINE.c_ext_attr15 , DESTLINE.c_ext_attr15),
240     DESTLINE.c_ext_attr16 = nvl(SRCLINE.c_ext_attr16 , DESTLINE.c_ext_attr16),
241     DESTLINE.c_ext_attr17 = nvl(SRCLINE.c_ext_attr17 , DESTLINE.c_ext_attr17),
242     DESTLINE.c_ext_attr18 = nvl(SRCLINE.c_ext_attr18 , DESTLINE.c_ext_attr18),
243     DESTLINE.c_ext_attr19 = nvl(SRCLINE.c_ext_attr19 , DESTLINE.c_ext_attr19),
244     DESTLINE.c_ext_attr20 = nvl(SRCLINE.c_ext_attr20 , DESTLINE.c_ext_attr20),
245     DESTLINE.c_ext_attr21 = nvl(SRCLINE.c_ext_attr21 , DESTLINE.c_ext_attr21),
246     DESTLINE.c_ext_attr22 = nvl(SRCLINE.c_ext_attr22 , DESTLINE.c_ext_attr22),
247     DESTLINE.c_ext_attr23 = nvl(SRCLINE.c_ext_attr23 , DESTLINE.c_ext_attr23),
248     DESTLINE.c_ext_attr24 = nvl(SRCLINE.c_ext_attr24 , DESTLINE.c_ext_attr24),
249     DESTLINE.c_ext_attr25 = nvl(SRCLINE.c_ext_attr25 , DESTLINE.c_ext_attr25),
250     DESTLINE.c_ext_attr26 = nvl(SRCLINE.c_ext_attr26 , DESTLINE.c_ext_attr26),
251     DESTLINE.c_ext_attr27 = nvl(SRCLINE.c_ext_attr27 , DESTLINE.c_ext_attr27),
252     DESTLINE.c_ext_attr28 = nvl(SRCLINE.c_ext_attr28 , DESTLINE.c_ext_attr28),
253     DESTLINE.c_ext_attr29 = nvl(SRCLINE.c_ext_attr29 , DESTLINE.c_ext_attr29),
254     DESTLINE.c_ext_attr30 = nvl(SRCLINE.c_ext_attr30 , DESTLINE.c_ext_attr30),
255     DESTLINE.c_ext_attr31 = nvl(SRCLINE.c_ext_attr31 , DESTLINE.c_ext_attr31),
256     DESTLINE.c_ext_attr32 = nvl(SRCLINE.c_ext_attr32 , DESTLINE.c_ext_attr32),
257     DESTLINE.c_ext_attr33 = nvl(SRCLINE.c_ext_attr33 , DESTLINE.c_ext_attr33),
258     DESTLINE.c_ext_attr34 = nvl(SRCLINE.c_ext_attr34 , DESTLINE.c_ext_attr34),
259     DESTLINE.c_ext_attr35 = nvl(SRCLINE.c_ext_attr35 , DESTLINE.c_ext_attr35),
260     DESTLINE.c_ext_attr36 = nvl(SRCLINE.c_ext_attr36 , DESTLINE.c_ext_attr36),
261     DESTLINE.c_ext_attr37 = nvl(SRCLINE.c_ext_attr37 , DESTLINE.c_ext_attr37),
262     DESTLINE.c_ext_attr38 = nvl(SRCLINE.c_ext_attr38 , DESTLINE.c_ext_attr38),
263     DESTLINE.c_ext_attr39 = nvl(SRCLINE.c_ext_attr39 , DESTLINE.c_ext_attr39),
264     DESTLINE.c_ext_attr40 = nvl(SRCLINE.c_ext_attr40 , DESTLINE.c_ext_attr40),
265     DESTLINE.n_ext_attr1 = nvl(SRCLINE.n_ext_attr1 , DESTLINE.n_ext_attr1),
266     DESTLINE.n_ext_attr2 = nvl(SRCLINE.n_ext_attr2 , DESTLINE.n_ext_attr2),
267     DESTLINE.n_ext_attr3 = nvl(SRCLINE.n_ext_attr3 , DESTLINE.n_ext_attr3),
268     DESTLINE.n_ext_attr4 = nvl(SRCLINE.n_ext_attr4 , DESTLINE.n_ext_attr4),
269     DESTLINE.n_ext_attr5 = nvl(SRCLINE.n_ext_attr5 , DESTLINE.n_ext_attr5),
270     DESTLINE.n_ext_attr6 = nvl(SRCLINE.n_ext_attr6 , DESTLINE.n_ext_attr6),
271     DESTLINE.n_ext_attr7 = nvl(SRCLINE.n_ext_attr7 , DESTLINE.n_ext_attr7),
272     DESTLINE.n_ext_attr8 = nvl(SRCLINE.n_ext_attr8 , DESTLINE.n_ext_attr8),
273     DESTLINE.n_ext_attr9 = nvl(SRCLINE.n_ext_attr9 , DESTLINE.n_ext_attr9),
274     DESTLINE.n_ext_attr10 = nvl(SRCLINE.n_ext_attr10 , DESTLINE.n_ext_attr10),
275     DESTLINE.n_ext_attr11 = nvl(SRCLINE.n_ext_attr11 , DESTLINE.n_ext_attr11),
276     DESTLINE.n_ext_attr12 = nvl(SRCLINE.n_ext_attr12 , DESTLINE.n_ext_attr12),
277     DESTLINE.n_ext_attr13 = nvl(SRCLINE.n_ext_attr13 , DESTLINE.n_ext_attr13),
278     DESTLINE.n_ext_attr14 = nvl(SRCLINE.n_ext_attr14 , DESTLINE.n_ext_attr14),
279     DESTLINE.n_ext_attr15 = nvl(SRCLINE.n_ext_attr15 , DESTLINE.n_ext_attr15),
280     DESTLINE.n_ext_attr16 = nvl(SRCLINE.n_ext_attr16 , DESTLINE.n_ext_attr16),
281     DESTLINE.n_ext_attr17 = nvl(SRCLINE.n_ext_attr17 , DESTLINE.n_ext_attr17),
282     DESTLINE.n_ext_attr18 = nvl(SRCLINE.n_ext_attr18 , DESTLINE.n_ext_attr18),
283     DESTLINE.n_ext_attr19 = nvl(SRCLINE.n_ext_attr19 , DESTLINE.n_ext_attr19),
284     DESTLINE.n_ext_attr20 = nvl(SRCLINE.n_ext_attr20 , DESTLINE.n_ext_attr20),
285     DESTLINE.uom_ext_attr1 = nvl(SRCLINE.uom_ext_attr1 , DESTLINE.uom_ext_attr1),
286     DESTLINE.uom_ext_attr2 = nvl(SRCLINE.uom_ext_attr2 , DESTLINE.uom_ext_attr2),
287     DESTLINE.uom_ext_attr3 = nvl(SRCLINE.uom_ext_attr3 , DESTLINE.uom_ext_attr3),
288     DESTLINE.uom_ext_attr4 = nvl(SRCLINE.uom_ext_attr4 , DESTLINE.uom_ext_attr4),
289     DESTLINE.uom_ext_attr5 = nvl(SRCLINE.uom_ext_attr5 , DESTLINE.uom_ext_attr5),
290     DESTLINE.uom_ext_attr6 = nvl(SRCLINE.uom_ext_attr6 , DESTLINE.uom_ext_attr6),
291     DESTLINE.uom_ext_attr7 = nvl(SRCLINE.uom_ext_attr7 , DESTLINE.uom_ext_attr7),
292     DESTLINE.uom_ext_attr8 = nvl(SRCLINE.uom_ext_attr8 , DESTLINE.uom_ext_attr8),
293     DESTLINE.uom_ext_attr9 = nvl(SRCLINE.uom_ext_attr9 , DESTLINE.uom_ext_attr9),
294     DESTLINE.uom_ext_attr10 = nvl(SRCLINE.uom_ext_attr10 , DESTLINE.uom_ext_attr10),
295     DESTLINE.uom_ext_attr11 = nvl(SRCLINE.uom_ext_attr11 , DESTLINE.uom_ext_attr11),
296     DESTLINE.uom_ext_attr12 = nvl(SRCLINE.uom_ext_attr12 , DESTLINE.uom_ext_attr12),
297     DESTLINE.uom_ext_attr13 = nvl(SRCLINE.uom_ext_attr13 , DESTLINE.uom_ext_attr13),
298     DESTLINE.uom_ext_attr14 = nvl(SRCLINE.uom_ext_attr14 , DESTLINE.uom_ext_attr14),
299     DESTLINE.uom_ext_attr15 = nvl(SRCLINE.uom_ext_attr15 , DESTLINE.uom_ext_attr15),
300     DESTLINE.uom_ext_attr16 = nvl(SRCLINE.uom_ext_attr16 , DESTLINE.uom_ext_attr16),
301     DESTLINE.uom_ext_attr17 = nvl(SRCLINE.uom_ext_attr17 , DESTLINE.uom_ext_attr17),
302     DESTLINE.uom_ext_attr18 = nvl(SRCLINE.uom_ext_attr18 , DESTLINE.uom_ext_attr18),
303     DESTLINE.uom_ext_attr19 = nvl(SRCLINE.uom_ext_attr19 , DESTLINE.uom_ext_attr19),
304     DESTLINE.uom_ext_attr20 = nvl(SRCLINE.uom_ext_attr20 , DESTLINE.uom_ext_attr20),
305     DESTLINE.d_ext_attr1 = nvl(SRCLINE.d_ext_attr1 , DESTLINE.d_ext_attr1),
306     DESTLINE.d_ext_attr2 = nvl(SRCLINE.d_ext_attr2 , DESTLINE.d_ext_attr2),
307     DESTLINE.d_ext_attr3 = nvl(SRCLINE.d_ext_attr3 , DESTLINE.d_ext_attr3),
308     DESTLINE.d_ext_attr4 = nvl(SRCLINE.d_ext_attr4 , DESTLINE.d_ext_attr4),
309     DESTLINE.d_ext_attr5 = nvl(SRCLINE.d_ext_attr5 , DESTLINE.d_ext_attr5),
310     DESTLINE.d_ext_attr6 = nvl(SRCLINE.d_ext_attr6 , DESTLINE.d_ext_attr6),
311     DESTLINE.d_ext_attr7 = nvl(SRCLINE.d_ext_attr7 , DESTLINE.d_ext_attr7),
312     DESTLINE.d_ext_attr8 = nvl(SRCLINE.d_ext_attr8 , DESTLINE.d_ext_attr8),
313     DESTLINE.d_ext_attr9 = nvl(SRCLINE.d_ext_attr9 , DESTLINE.d_ext_attr9),
314     DESTLINE.d_ext_attr10 = nvl(SRCLINE.d_ext_attr10 , DESTLINE.d_ext_attr10) ;
315 
316     LOG_MESSAGE(l_module,'Merge statement executed ');
317 
318     FORALL i IN 1..p_dest_line_ids_tbl.Count
319 
320     INSERT INTO pon_auction_item_prices_ext_b DESTlINE
321     (DESTLINE.extension_id,
322     DESTLINE.attr_group_id,
323     DESTLINE.LINE_NUMBER,
324     DESTLINE.data_level_id,
325     DESTLINE.pk1_value,
326     DESTLINE.pk2_value,
327     DESTLINE.pk3_value,
328     DESTLINE.pk4_value,
329     DESTLINE.pk5_value,
330     DESTLINE.last_update_date,
331     DESTLINE.last_updated_by,
332     DESTLINE.last_update_login,
333     DESTLINE.created_by,
334     DESTLINE.creation_date,
335     DESTLINE.c_ext_attr1,
336     DESTLINE.c_ext_attr2,
337     DESTLINE.c_ext_attr3,
338     DESTLINE.c_ext_attr4,
339     DESTLINE.c_ext_attr5,
340     DESTLINE.c_ext_attr6,
341     DESTLINE.c_ext_attr7,
342     DESTLINE.c_ext_attr8,
343     DESTLINE.c_ext_attr9,
344     DESTLINE.c_ext_attr10,
345     DESTLINE.c_ext_attr11,
346     DESTLINE.c_ext_attr12,
347     DESTLINE.c_ext_attr13,
348     DESTLINE.c_ext_attr14,
349     DESTLINE.c_ext_attr15,
350     DESTLINE.c_ext_attr16,
351     DESTLINE.c_ext_attr17,
352     DESTLINE.c_ext_attr18,
353     DESTLINE.c_ext_attr19,
354     DESTLINE.c_ext_attr20,
355     DESTLINE.c_ext_attr21,
356     DESTLINE.c_ext_attr22,
357     DESTLINE.c_ext_attr23,
358     DESTLINE.c_ext_attr24,
359     DESTLINE.c_ext_attr25,
360     DESTLINE.c_ext_attr26,
361     DESTLINE.c_ext_attr27,
362     DESTLINE.c_ext_attr28,
363     DESTLINE.c_ext_attr29,
364     DESTLINE.c_ext_attr30,
365     DESTLINE.c_ext_attr31,
366     DESTLINE.c_ext_attr32,
367     DESTLINE.c_ext_attr33,
368     DESTLINE.c_ext_attr34,
369     DESTLINE.c_ext_attr35,
370     DESTLINE.c_ext_attr36,
371     DESTLINE.c_ext_attr37,
372     DESTLINE.c_ext_attr38,
373     DESTLINE.c_ext_attr39,
374     DESTLINE.c_ext_attr40,
375     DESTLINE.n_ext_attr1,
376     DESTLINE.n_ext_attr2,
377     DESTLINE.n_ext_attr3,
378     DESTLINE.n_ext_attr4,
379     DESTLINE.n_ext_attr5,
380     DESTLINE.n_ext_attr6,
381     DESTLINE.n_ext_attr7,
382     DESTLINE.n_ext_attr8,
383     DESTLINE.n_ext_attr9,
384     DESTLINE.n_ext_attr10,
385     DESTLINE.n_ext_attr11,
386     DESTLINE.n_ext_attr12,
387     DESTLINE.n_ext_attr13,
388     DESTLINE.n_ext_attr14,
389     DESTLINE.n_ext_attr15,
390     DESTLINE.n_ext_attr16,
391     DESTLINE.n_ext_attr17,
392     DESTLINE.n_ext_attr18,
393     DESTLINE.n_ext_attr19,
394     DESTLINE.n_ext_attr20,
395     DESTLINE.uom_ext_attr1,
396     DESTLINE.uom_ext_attr2,
397     DESTLINE.uom_ext_attr3,
398     DESTLINE.uom_ext_attr4,
399     DESTLINE.uom_ext_attr5,
400     DESTLINE.uom_ext_attr6,
401     DESTLINE.uom_ext_attr7,
402     DESTLINE.uom_ext_attr8,
403     DESTLINE.uom_ext_attr9,
404     DESTLINE.uom_ext_attr10,
405     DESTLINE.uom_ext_attr11,
406     DESTLINE.uom_ext_attr12,
407     DESTLINE.uom_ext_attr13,
408     DESTLINE.uom_ext_attr14,
409     DESTLINE.uom_ext_attr15,
410     DESTLINE.uom_ext_attr16,
411     DESTLINE.uom_ext_attr17,
412     DESTLINE.uom_ext_attr18,
413     DESTLINE.uom_ext_attr19,
414     DESTLINE.uom_ext_attr20,
415     DESTLINE.d_ext_attr1,
416     DESTLINE.d_ext_attr2,
417     DESTLINE.d_ext_attr3,
418     DESTLINE.d_ext_attr4,
419     DESTLINE.d_ext_attr5,
420     DESTLINE.d_ext_attr6,
421     DESTLINE.d_ext_attr7,
422     DESTLINE.d_ext_attr8,
423     DESTLINE.d_ext_attr9,
424     DESTLINE.d_ext_attr10,
425     DESTLINE.uda_template_id,
426     DESTLINE.auctioN_header_id)
427     SELECT
428     EGO_EXTFWK_S.NEXTVAL,
429     SRCLINE.attr_group_id,
430     p_dest_line_ids_tbl(i),
431     SRCLINE.data_level_id,
432     SRCLINE.pk1_value,
433     SRCLINE.pk2_value,
434     SRCLINE.pk3_value,
435     SRCLINE.pk4_value,
436     SRCLINE.pk5_value,
437     SRCLINE.last_update_date,
438     SRCLINE.last_updated_by,
439     SRCLINE.last_update_login,
440     SRCLINE.created_by,
441     SRCLINE.creation_date,
442     SRCLINE.c_ext_attr1,
443     SRCLINE.c_ext_attr2,
444     SRCLINE.c_ext_attr3,
445     SRCLINE.c_ext_attr4,
446     SRCLINE.c_ext_attr5,
447     SRCLINE.c_ext_attr6,
448     SRCLINE.c_ext_attr7,
449     SRCLINE.c_ext_attr8,
450     SRCLINE.c_ext_attr9,
451     SRCLINE.c_ext_attr10,
452     SRCLINE.c_ext_attr11,
453     SRCLINE.c_ext_attr12,
454     SRCLINE.c_ext_attr13,
455     SRCLINE.c_ext_attr14,
456     SRCLINE.c_ext_attr15,
457     SRCLINE.c_ext_attr16,
458     SRCLINE.c_ext_attr17,
459     SRCLINE.c_ext_attr18,
460     SRCLINE.c_ext_attr19,
461     SRCLINE.c_ext_attr20,
462     SRCLINE.c_ext_attr21,
463     SRCLINE.c_ext_attr22,
464     SRCLINE.c_ext_attr23,
465     SRCLINE.c_ext_attr24,
466     SRCLINE.c_ext_attr25,
467     SRCLINE.c_ext_attr26,
468     SRCLINE.c_ext_attr27,
469     SRCLINE.c_ext_attr28,
470     SRCLINE.c_ext_attr29,
471     SRCLINE.c_ext_attr30,
472     SRCLINE.c_ext_attr31,
473     SRCLINE.c_ext_attr32,
474     SRCLINE.c_ext_attr33,
475     SRCLINE.c_ext_attr34,
476     SRCLINE.c_ext_attr35,
477     SRCLINE.c_ext_attr36,
478     SRCLINE.c_ext_attr37,
479     SRCLINE.c_ext_attr38,
480     SRCLINE.c_ext_attr39,
481     SRCLINE.c_ext_attr40,
482     SRCLINE.n_ext_attr1,
483     SRCLINE.n_ext_attr2,
484     SRCLINE.n_ext_attr3,
485     SRCLINE.n_ext_attr4,
486     SRCLINE.n_ext_attr5,
487     SRCLINE.n_ext_attr6,
488     SRCLINE.n_ext_attr7,
489     SRCLINE.n_ext_attr8,
490     SRCLINE.n_ext_attr9,
491     SRCLINE.n_ext_attr10,
492     SRCLINE.n_ext_attr11,
493     SRCLINE.n_ext_attr12,
494     SRCLINE.n_ext_attr13,
495     SRCLINE.n_ext_attr14,
496     SRCLINE.n_ext_attr15,
497     SRCLINE.n_ext_attr16,
498     SRCLINE.n_ext_attr17,
499     SRCLINE.n_ext_attr18,
500     SRCLINE.n_ext_attr19,
501     SRCLINE.n_ext_attr20,
502     SRCLINE.uom_ext_attr1,
503     SRCLINE.uom_ext_attr2,
504     SRCLINE.uom_ext_attr3,
505     SRCLINE.uom_ext_attr4,
506     SRCLINE.uom_ext_attr5,
507     SRCLINE.uom_ext_attr6,
508     SRCLINE.uom_ext_attr7,
509     SRCLINE.uom_ext_attr8,
510     SRCLINE.uom_ext_attr9,
511     SRCLINE.uom_ext_attr10,
512     SRCLINE.uom_ext_attr11,
513     SRCLINE.uom_ext_attr12,
514     SRCLINE.uom_ext_attr13,
515     SRCLINE.uom_ext_attr14,
516     SRCLINE.uom_ext_attr15,
517     SRCLINE.uom_ext_attr16,
518     SRCLINE.uom_ext_attr17,
519     SRCLINE.uom_ext_attr18,
520     SRCLINE.uom_ext_attr19,
521     SRCLINE.uom_ext_attr20,
522     SRCLINE.d_ext_attr1,
523     SRCLINE.d_ext_attr2,
524     SRCLINE.d_ext_attr3,
525     SRCLINE.d_ext_attr4,
526     SRCLINE.d_ext_attr5,
527     SRCLINE.d_ext_attr6,
528     SRCLINE.d_ext_attr7,
529     SRCLINE.d_ext_attr8,
530     SRCLINE.d_ext_attr9,
531     SRCLINE.d_ext_attr10,
532     SRCLINE.uda_template_id,
533     SRCLINE.auction_header_id
534   FROM pon_auction_item_prices_ext_b SRCLINE WHERE SRCLINE.LINE_NUMBER = p_src_line_number
535   AND auction_header_id = p_auction_header_id
536   AND NOT EXISTS (SELECT 1 FROM  pon_auction_item_prices_ext_b   pleb WHERE
537                  pleb.line_Number =p_dest_line_ids_tbl(i) AND
538                  pleb.attr_group_id = SRCLINE.attr_group_id AND
539                  pleb.auction_header_id = SRCLINE.auction_header_id);
540 
541 
542   LOG_MESSAGE(l_module,'Insert statement executed ');
543 
544   SELECT  extension_id,attr_group_id,line_number,data_level_id,uda_template_id
545   BULK COLLECT INTO  extension_id_tbl,attr_group_id_tbl,sol_line_num_tbl,
546   data_level_id_tbl,uda_template_id_tbl
547     FROM pon_auction_item_prices_ext_b
548     WHERE line_Number IN ( select column_value from table(p_dest_line_ids_tbl))
549      AND auction_header_id = p_auction_header_id;
550 
551    LOG_MESSAGE(l_module,'About to insert into pon_auction_item_prices_ext_tl ');
552 
553   FORALL i IN 1..extension_id_tbl.Count
554    INSERT INTO pon_auction_item_prices_ext_tl
555    (
556       EXTENSION_ID,
557       ATTR_GROUP_ID,
558       line_number,
559       DATA_LEVEL_ID,
560       SOURCE_LANG,
561       LANGUAGE,
562       LAST_UPDATE_DATE,
563       LAST_UPDATED_BY,
564       LAST_UPDATE_LOGIN,
565       CREATED_BY,
566       CREATION_DATE,
567       UDA_TEMPLATE_ID,
568       AUCTION_HEADER_ID
569    )
570    SELECT
571 
572       extension_id_tbl(i),
573       attr_group_id_tbl(i),
574       sol_line_num_tbl(i),
575       data_level_id_tbl(i),
576       USERENV('LANG'),
577       L.LANGUAGE_CODE,
578       SYSDATE,
579       0,
580       0,
581       0,
582       SYSDATE,
583       uda_template_id_tbl(i),
584       p_auction_header_id
585 
586    FROM FND_LANGUAGES L
587    WHERE L.INSTALLED_FLAG in ('I', 'B')
588    AND NOT EXISTS
589    (SELECT 1 FROM pon_auction_item_prices_ext_tl pltl WHERE
590     pltl.extension_id = extension_id_tbl(i)
591     AND pltl.LANGUAGE = L.LANGUAGE_CODE);
592 
593     LOG_MESSAGE(l_module,'Inserted into pon_auction_item_prices_ext_tl ');
594 
595   DELETE FROM pon_auction_item_prices_ext_b  WHERE line_Number = p_src_line_number
596   AND auction_header_id = p_auction_header_id;
597 
598   LOG_MESSAGE(l_module,'Deleted temporary record from pon_auction_item_prices_ext_b');
599   x_result:='S';
600 
601 EXCEPTION
602 WHEN OTHERS THEN
603      ROLLBACK TO global_update;
604      LOG_MESSAGE(l_module,'Exception : ' || SQLERRM );
605      x_result:='E';
606 END  global_line_update;
607 
608 -- ======================================================================
609 --   PROCEDURE  :  LOG_MESSAGE   PRIVATE
610 --   PARAMETERS :
611 --     p_module :  IN pass the module name
612 --     p_message:  IN the string to be logged
613 --
614 --   COMMENT    :  Common procedure to log messages in FND_LOG.
615 -- ======================================================================
616 PROCEDURE LOG_MESSAGE( p_module  IN VARCHAR2,
617                        p_message IN VARCHAR2)
618 IS
619 BEGIN
620   IF (g_debug_mode = 'Y') THEN
621       IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
622 
623           FND_LOG.string(log_level  => FND_LOG.level_statement,
624                          module     => g_module_prefix || p_module,
625                          message    => p_message);
626 
627       END IF;
628    END IF;
629 END LOG_MESSAGE;
630 
631 
632 END PON_GLOBAL_UPDATE_PKG;