[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_ITEM_UPLOAD_VALIDATE
Source
1 PACKAGE BODY ICX_POR_ITEM_UPLOAD_VALIDATE AS
2 /* $Header: ICXIULVB.pls 115.33 2004/03/31 21:51:08 vkartik ship $*/
3
4 --BUG#2228935
5 g_operating_unit_id NUMBER;
6
7 /**
8 ** Proc : validate_interface_data
9 ** Desc : Validate data of interface table.
10 **/
11
12 PROCEDURE validate_interface_data (p_job_supplier_name IN VARCHAR2,
13 p_job_supplier_id IN NUMBER,
14 p_exchange_operator_name IN VARCHAR2,
15 p_table_name IN VARCHAR2,
16 p_language IN VARCHAR2,
17 p_start_row IN NUMBER,
18 p_end_row IN NUMBER) IS
19 xErrLoc INTEGER := 0;
20 l_count1 INTEGER;
21 l_sql_string VARCHAR2(4000);
22 l_cursor_id NUMBER;
23 l_result_count NUMBER;
24 l_organization_id NUMBER;
25 -- Bug#1991093
26 l_list_price_name VARCHAR2(90);
27 l_list_price_currency VARCHAR2(4);
28 l_list_price_id NUMBER;
29 l_bus_group_id NUMBER := 0; /* vkartik */
30 l_supplier_id NUMBER := 0; /* vkartik */
31 l_chk_multi_org VARCHAR2(1); --Bug#2375254
32
33 BEGIN
34
35 xErrLoc := 100;
36
37 l_bus_group_id := p_job_supplier_id; /* vkartik */
38
39 -- Figure out what the action should be
40 -- We use bind for row number but not language since the language
41 -- will not change within a job, but the row numbers will
42 -- BUG#2228935
43 -- If operating unit specified from the UI then also joining with oracle_item_subtable
44 -- So as to handle the special cases for extracted items
45 -- having more than one row in items table for the same supplier supplier_part_num
46 -- Outer joined with oracle_item_subtable as bulkloaded items will have no row in oracle_item_subtable
47 if ( g_operating_unit_id is not null) then
48 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
49 ' SET (system_action, rt_item_id) = (SELECT DISTINCT decode(it2.action, ' ||
50 '''SYNC'', decode(it2.row_type, ''PRICE'', ''ADD'', ' ||
51 'decode(item.rt_item_id, NULL, ''ADD'', decode(tl.rt_item_id, ' ||
52 'NULL, ''TRANSLATE'', ''UPDATE''))), it2.action), item.rt_item_id ' ||
53 'FROM icx_por_items_tl tl, icx_por_items item, ' || p_table_name ||
54 ' it2, icx_por_oracle_item_subtable orc ' ||
55 'WHERE it1.rowid = it2.rowid ' ||
56 'AND it2.supplier_name = item.a1 (+) ' ||
57 'AND it2.supplier_part_num = item.a3 (+) ' ||
58 'AND item.rt_item_id = orc.rt_item_id (+) ' ||
59 'AND (orc.orc_operating_unit_id = :oper_unit_id OR orc.orc_operating_unit_id is null) '||
60 'AND item.rt_item_id = tl.rt_item_id (+) ' ||
61 'AND tl.language (+) = ''' || p_language || ''') ' ||
62 'WHERE line_number between :startrow AND :endrow';
63 else
64 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
65 ' SET (system_action, rt_item_id) = (SELECT decode(it2.action, ' ||
66 '''SYNC'', decode(it2.row_type, ''PRICE'', ''ADD'', ' ||
67 'decode(item.rt_item_id, NULL, ''ADD'', decode(tl.rt_item_id, ' ||
68 'NULL, ''TRANSLATE'', ''UPDATE''))), it2.action), item.rt_item_id ' ||
69 'FROM icx_por_items_tl tl, icx_por_items item, ' || p_table_name ||
70 ' it2 WHERE it1.rowid = it2.rowid AND it2.supplier_name = item.a1 (+) ' ||
71 'AND it2.supplier_part_num = item.a3 (+) ' ||
72 'AND item.rt_item_id = tl.rt_item_id (+) ' ||
73 'AND tl.language (+) = ''' || p_language || ''') ' ||
74 'WHERE line_number between :startrow AND :endrow';
75 end if;
76
77 l_cursor_id := DBMS_SQL.open_cursor;
78 xErrLoc := 110;
79 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
80 xErrLoc := 120;
81 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
82 xErrLoc := 130;
83 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
84 xErrLoc := 140;
85 if ( g_operating_unit_id is not null) then -- BUG#2228935
86 DBMS_SQL.bind_variable(l_cursor_id, ':oper_unit_id', g_operating_unit_id);
87 end if;
88 xErrLoc := 14000;
89 l_result_count := DBMS_SQL.execute(l_cursor_id);
90 xErrLoc := 150;
91 DBMS_SQL.close_cursor(l_cursor_id);
92
93 commit;
94
95 -- Now validate:
96 -- Mandatory variables for adding items: supplier_part_num, description,
97 -- price, uom
98 -- Mandatory variables for translating items - description
99 -- Mandatory variables for updating items - none
100 -- Mandatory variables for updating item price - price, uom
101 -- Mandatory variables for deleting items - supplier_part_num
102 -- Mandatory variables for adding price - supplier_part_num, price, uom,
103 -- buyer, price list name, currency
104 -- Mandatory variables for deleting price - supplier_part_num, buyer
105 -- Item exists if action is delete
106
107 xErrLoc := 200;
108 -- Bug#1581013: Get the Buyer Id here
109 -- Bug#2160017: If the Buyer name is "All-Buyers", Buyer id must be -2
110 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
111 'SET (buyer_id, error_message) = (SELECT decode(it2.buyer_name, null, -2, ''All-Buyers'', -2, buy.organization_id), it1.error_message || ' ||
112 'decode(it2.system_action, ''ADD'', decode(it2.supplier_part_num, null, ' ||
113 '''.SUPPLIER_PART_NUM:ICX_POR_SUPPLIER_PART_REQD'', null), ' ||
114 '''DELETE'', decode(it2.supplier_part_num, null, ' ||
115 '''.SUPPLIER_PART_NUM:ICX_POR_SUPPLIER_PART_REQD'', null), null) || ' ||
116 'decode(it2.system_action, ''DELETE'', decode(it2.rt_item_id, null, ' ||
117 '''.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART'', null), null) || ' ||
118 -- Bug 1344934: Loading item and buyer-specific price in a file will
119 -- reject buyer-specific price
120 -- Will check item of Price line during moving data process
121 -- 'decode(it2.row_type, ''PRICE'', decode(it2.rt_item_id, null, ' ||
122 -- '''.SUPPLIER_PART_NUM:ICX_POR_PRC_INVALID_SUP_PART'', null), null) || ' ||
123 'decode(it2.system_action, ''TRANSLATE'', decode(it2.description, null, ' ||
124 '''.DESCRIPTION:ICX_POR_INVALID_DESCRIPTION'', null), null) || ' ||
125 'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM_PRICE'', ' ||
126 'decode(it2.description, null, ' ||
127 '''.DESCRIPTION:ICX_POR_INVALID_DESCRIPTION'', null), null),null) || ' ||
128 'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM'', ' ||
129 'decode(it2.price_string, null, ' ||
130 '''.PRICE:ICX_POR_PRICE_REQD'', null), null), null) || ' ||
131 'decode(it2.system_action, ''ADD'', decode(it2.row_type, ''ITEM'', ' ||
132 'decode(it2.uom_code, null, ' ||
133 '''.PRICE:ICX_POR_UOM_REQD'', null), null), null) || ' ||
134 'decode(it2.row_type, ''PRICE'', decode(it2.buyer_name, null, ' ||
135 '''.BUYER:ICX_POR_BUYER_REQD'', null), null) || ' ||
136 'decode(it2.row_type, ''PRICE'', decode(it2.pricelist_name, null, ' ||
137 '''.PRICELIST:ICX_POR_CAT_PRICE_LIST_NAME_M'', null), null) || ' ||
138 'decode(it2.row_type, ''PRICE'', decode(it2.currency_code, null, ' ||
139 '''.CURRENCY:ICX_POR_CURRENCY_REQD'', null), null) || ' ||
140 'decode(it2.row_type, ''PRICE'', decode(it2.price_string, null, ' ||
141 '''.PRICE:ICX_POR_PRICE_REQD'', null), null) || ' ||
142 'decode(it2.row_type, ''PRICE'', decode(it2.uom_code, null, ' ||
143 '''.UOM:ICX_POR_UOM_REQD'', null), null) || ' ||
144 -- BUG#2228935 Check Price and UOM reqd only if system_action is ADD
145 'decode(it2.system_action, ''ADD'', ' ||
146 'decode(it2.row_type, ''ITEM_PRICE'', decode(it2.price_string, null, ' ||
147 '''.PRICE:ICX_POR_PRICE_REQD'', null), null), null) || ' ||
148 -- BUG#2228935 Check Price and UOM reqd only if system_action is ADD
149 'decode(it2.system_action, ''ADD'', ' ||
150 'decode(it2.row_type, ''ITEM_PRICE'', decode(it2.uom_code, null, ' ||
151 '''.UOM:ICX_POR_UOM_REQD'', null), null), null) || ' ||
152 'decode(it2.buyer_name, null, null, :all_buyer_list_name' ||
153 ', null, decode(buy.name, null, ' ||
154 '''.BUYER:ICX_POR_INVALID_BUYER'')) ' ||
155 ' FROM hr_all_organization_units buy, ' || p_table_name || ' it2 WHERE it1.rowid = it2.rowid ' ||
156 ' AND it2.buyer_name = buy.name (+) ' ||
157 ' AND :bus_group_id = buy.business_group_id (+)) ' ||
158 'WHERE line_number between :startrow AND :endrow';
159
160 l_cursor_id := DBMS_SQL.open_cursor;
161 xErrLoc := 210;
162 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
163 xErrLoc := 220;
164 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
165 xErrLoc := 230;
166 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
167 xErrLoc := 240;
168 -- Bug#1581013
169 DBMS_SQL.bind_variable(l_cursor_id, ':all_buyer_list_name', p_exchange_operator_name);
170 xErrLoc := 245;
171 DBMS_SQL.bind_variable(l_cursor_id, ':bus_group_id', l_bus_group_id);
172 xErrLoc := 250;
173 l_result_count := DBMS_SQL.execute(l_cursor_id);
174 xErrLoc := 260;
175 DBMS_SQL.close_cursor(l_cursor_id);
176
177 commit;
178
179 xErrLoc := 300;
180 -- Get exchange operator id
181 -- vkartik
182 l_organization_id := -2 ;
183
184 l_sql_string := 'SELECT vendor_id ' ||
185 'FROM po_vendors supp, ' || p_table_name || ' it2 ' ||
186 'WHERE supp.vendor_name = it2.supplier_name ';
187 l_cursor_id := DBMS_SQL.open_cursor;
188 xErrLoc := 301;
189 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
190 xErrLoc := 302;
191 DBMS_SQL.define_column(l_cursor_id, 1, l_supplier_id);
192 xErrLoc := 303;
193 l_result_count := DBMS_SQL.execute(l_cursor_id);
194 xErrLoc := 304;
195
196 IF DBMS_SQL.fetch_rows(l_cursor_id) <> 0 THEN
197 DBMS_SQL.column_value(l_cursor_id, 1, l_supplier_id);
198 END IF;
199
200 xErrLoc := 305;
201
202 DBMS_SQL.close_cursor(l_cursor_id);
203
204 -- SELECT organization_id INTO l_organization_id FROM hr_all_organization_units
205 -- WHERE name = p_exchange_operator_name;
206 -- AND type = 'EXCHANGE_OPERATOR'; ?? DIV ??
207
208 -- SELECT party_id INTO l_organization_id FROM hz_parties
209 -- WHERE party_name = p_exchange_operator_name
210 -- AND party_type = 'EXCHANGE_OPERATOR';
211
212 xErrLoc := 310;
213 /*
214 BEGIN
215 -- Get Exchange price list name and id
216 --Bug#1505751: was using supplier id from batch jobs, which is wrong.
217 SELECT header_id, name, currency_code
218 INTO l_list_price_id, l_list_price_name, l_list_price_currency
219 FROM icx_por_price_lists
220 WHERE supplier_id = l_supplier_id AND Buyer_id = l_organization_id;
221
222 EXCEPTION
223 WHEN no_data_found THEN
224 l_list_price_id := NULL;
225 l_list_price_name := NULL;
226 l_list_price_currency := NULL;
227 END;
228 */
229
230 -- Now validate (if specified):
231 -- UNSPSC code exists in ICX_UNSPSC_CODES
232 -- Uom code exists in MTL_UNITS_OF_MEASURE
233 -- Currency exists in FND_CURRENCIES
234 -- Product type exists in FND_LOOKUP_VALUES
235 -- If price list already exists, currency and price list name is the same
236 -- Buyer is Exchange operator if row_type is ITEM_PRICE system action is 'ADD'
237 -- Buyer is not exchange operator if row_type is 'PRICE' and action is DELETE
238 -- Owner is a valid user
239
240 xErrLoc := 400;
241
242 -- Bug#1975528
243 -- Use Bind variables instead of literals.
244 -- move the site validation part in this sql into the
245 -- site validation sql that is following this sql. Remove joins
246 -- to po_vendor_sites_all.
247 -- Bug#1581013: Dont get the buyer id here..
248 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
249 'SET (pricelist_id, supplier_id, error_message) = ' ||
250 '(SELECT DISTINCT pl.header_id, ' ||
251 'supp.vendor_id, it1.error_message || '||
252
253 'decode(it2.supplier_name, null, null, decode(supp.vendor_name, null, ' ||
254 '''.SNAME:ICX_POR_INVALID_SUPPLIER'', null)) || ' ||
255 'decode(greatest( nvl(supp.start_date_active,sysdate-1), sysdate), sysdate, null, ' ||
256 '''.SNAME:ICX_POR_INACTIVE_SUPPLIER'') || ' ||
257 'decode(greatest( nvl(supp.end_date_active ,sysdate+1), sysdate), sysdate, '||
258 '''.SNAME:ICX_POR_INACTIVE_SUPPLIER'', null) || ' ||
259 'decode(it2.item_type, null, null, decode(lkp.lookup_code, null, ' ||
260 '''.ITEM_TYPE:ICX_POR_INVALID_ITEM_TYPE'', null)) || ' ||
261 'decode(it2.currency_code, null, null, decode(cur.currency_code, null, ' ||
262 '''.CURRENCY:ICX_POR_INVALID_CURRENCY'', null)) ' ||
263 ' FROM po_vendors supp, icx_por_price_lists pl, ' ||
264 ' fnd_lookup_values lkp, fnd_currencies cur, '||
265 p_table_name ||' it2 ' ||
266 ' WHERE it1.rowid = it2.rowid ' ||
267 ' AND upper(it2.currency_code) = cur.currency_code (+) ' ||
268 ' AND it2.item_type = lkp.lookup_code (+) ' ||
269 ' AND lkp.lookup_type (+) = ''ICX_CATALOG_ITEM_TYPE'' ' ||
270 ' AND lkp.language (+) = :language' ||
271 ' AND it2.buyer_id = pl.buyer_id (+) ' ||
272 --Bug#1581013: Currency validation
273 ' AND it2.currency_code = pl.currency_code (+) ' ||
274 ' AND pl.supplier_id (+) = :supplier_id ' ||
275 ' AND supp.vendor_name (+) = it2.supplier_name ' ||
276 ') WHERE line_number BETWEEN :startrow AND :endrow ';
277 l_cursor_id := DBMS_SQL.open_cursor;
278
279 xErrLoc := 410;
280 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
281 xErrLoc := 420;
282 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
283 xErrLoc := 430;
284 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
285 xErrLoc := 435;
286 DBMS_SQL.bind_variable(l_cursor_id, ':supplier_id', l_supplier_id);
287 xErrLoc := 440;
288 DBMS_SQL.bind_variable(l_cursor_id, ':language', p_language);
289 xErrLoc := 470;
290 l_result_count := DBMS_SQL.execute(l_cursor_id);
291 xErrLoc := 480;
292 DBMS_SQL.close_cursor(l_cursor_id);
293
294 xErrLoc := 700;
295
296 -- Bug#1975528
297 -- Check supplier_site, buyer combination specified in the file is valid
298 -- Need a separate sql
299 -- Bug#1975528
300 -- Added the constraint, vendor_id for po_vendor_sites_all table
301 -- Added extra site validation that existed from the previous sql
302 -- into this sql.
303
304 -- Bug#2375254
305 -- Need seperate sql for non multi-org instance
306 -- as site.org_id is null in non multi-org instance
307
308 xErrLoc := 701;
309
310 SELECT nvl(multi_org_flag, 'N') INTO l_chk_multi_org
311 FROM fnd_product_groups ;
312
313 xErrLoc := 705;
314
315 if (l_chk_multi_org='Y') then
316
317 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
318 'SET error_message = error_message ||( ' ||
319 'SELECT DISTINCT error_message || ' ||
320 'decode(site.purchasing_site_flag, ' ||'''N''' || ', ' ||
321 '''.SITE:ICX_POR_INVALID_SUPP_SITE_2'', null ) || ' ||
322 'decode(greatest(nvl(site.inactive_date ,sysdate+1), sysdate), sysdate, ' ||
323 '''.SITE:ICX_POR_INACTIVE_SUPP_SITE'', null) || ' ||
324 -- Bug#2054819
325 -- Bug 2182815 fixed by sosingha
326 -- Bug 2107543 fixed by sosingha
327 'decode(it2.supplier_site, null, null, decode(it2.buyer_id,'
328 || '''-2'', ''.SITE:ICX_POR_OU_REQD'', '
329 -- Bug 2325999
330 || 'decode(it2.row_type, ''PRICE'', '
331 || ' decode(it2.rt_item_id, null, ''.SITE:ICX_POR_PRC_INVALID_SUP_PART'', '
332 || ' decode(site.vendor_site_code,null, ''.SITE:ICX_POR_INVALID_SUPP_SITE'', null)), '
333 || 'decode(site.vendor_site_code, null, '
334 ||'''.SITE:ICX_POR_INVALID_SUPP_SITE'', null)))) '||
335 'FROM ' || p_table_name || ' it2 , po_vendor_sites_all site '||
336 'WHERE it1.rowid = it2.rowid ' ||
337 ' AND it2.buyer_id IS NOT NULL ' ||
338 -- Bug#1975528: Vendor ID constraint added.
339 ' AND site.vendor_id (+) = it2.supplier_id ' ||
340 ' AND site.vendor_site_code (+) = UPPER(it2.supplier_site) ' ||
341 ' AND site.org_id (+) = it2.buyer_id ' ||
342 ') WHERE line_number BETWEEN :startrow AND :endrow ';
343
344 else
345
346 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
347 'SET error_message = error_message ||( ' ||
348 'SELECT DISTINCT error_message || ' ||
349 'decode(site.purchasing_site_flag, ' ||'''N''' || ', ' ||
350 '''.SITE:ICX_POR_INVALID_SUPP_SITE_2'', null ) || ' ||
351 'decode(greatest(nvl(site.inactive_date ,sysdate+1), sysdate), sysdate, ' ||
352 '''.SITE:ICX_POR_INACTIVE_SUPP_SITE'', null) || ' ||
353 'decode(it2.supplier_site, null, null, ' ||
354 'decode(it2.row_type, ''PRICE'', ' ||
355 'decode(it2.rt_item_id, null, ''.SITE:ICX_POR_PRC_INVALID_SUP_PART'', ' ||
356 'decode(site.vendor_site_code, null, ''.SITE:ICX_POR_INVALID_SUPP_SITE'', ' ||
357 'null)), decode(site.vendor_site_code, null, ' ||
358 '''.SITE:ICX_POR_INVALID_SUPP_SITE'', null))) ' ||
359 'FROM ' || p_table_name || ' it2 , po_vendor_sites_all site '||
360 'WHERE it1.rowid = it2.rowid ' ||
361 ' AND site.vendor_id (+) = it2.supplier_id ' ||
362 ' AND site.vendor_site_code (+) = UPPER(it2.supplier_site) ' ||
363 ') WHERE line_number BETWEEN :startrow AND :endrow ';
364
365 end if;
366
367 l_cursor_id := DBMS_SQL.open_cursor;
368 xErrLoc := 710;
369 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
370 xErrLoc := 720;
371 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
372 xErrLoc := 730;
373 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
374 xErrLoc := 740;
375 l_result_count := DBMS_SQL.execute(l_cursor_id);
376 xErrLoc := 750;
377 DBMS_SQL.close_cursor(l_cursor_id);
378
379
380 xErrLoc := 500;
381 --Bug#1581013: Removed code to check for the uniqueness of the pricelist name
382
383 xErrLoc := 600;
384 -- Find the current category if the action is update
385 -- Bug#2049568 : Check for only Genus categories,
386 -- (item could be in both a Genus category and template)
387 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
388 'SET old_category_id = (SELECT cai.rt_category_id ' ||
389 'FROM icx_por_category_items cai, icx_por_categories_tl ca '||
390 'WHERE cai.rt_item_id = it1.rt_item_id '||
391 ' AND ca.rt_category_id = cai.rt_category_id '||
392 ' AND ca.type = 2 '||
393 ' AND ca.language = ''' || p_language || ''') ' ||
394 'WHERE it1.system_action = ''UPDATE'' AND it1.rt_item_id IS NOT NULL ' ||
395 'AND line_number BETWEEN :startrow AND :endrow ';
396
397 l_cursor_id := DBMS_SQL.open_cursor;
398 xErrLoc := 610;
399 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
400 xErrLoc := 620;
401 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
402 xErrLoc := 630;
403 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
404 xErrLoc := 640;
405 l_result_count := DBMS_SQL.execute(l_cursor_id);
406 xErrLoc := 650;
407 DBMS_SQL.close_cursor(l_cursor_id);
408
409 commit;
410
411
412 EXCEPTION
413 WHEN OTHERS THEN
414 ROLLBACK;
415
416 RAISE_APPLICATION_ERROR(-20000,
417 'Exception at ICX_POR_ITEM_UPLOAD_VALIDATE.validate_interface_data('
418 || xErrLoc || '): ' || SQLERRM);
419
420 END validate_interface_data;
421
422 PROCEDURE validate_duplicate_item2(p_table_name IN VARCHAR2) IS
423 xErrLoc NUMBER;
424 l_result_count NUMBER;
425 l_cursor_id NUMBER;
426 l_sql_string VARCHAR2(4000);
427 BEGIN
428 -- There are duplicate item numbers within the interface table
429 xErrLoc := 100;
430
431 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
432 'SET it1.error_message = it1.error_message || ' ||
433 'decode(it1.error_message, it1.error_message, ' ||
434 'decode(it1.row_type, ''PRICE'', ''.PRICELIST:ICX_POR_DUP_PRICE_LIST1'', '||
435 '''ITEM_PRICE'', ''.SUPPLIER_PART_NUM:ICX_POR_DUP_SUPPLIER_PART'', ' ||
436 '''.SUPPLIER_PART_NUM:ICX_POR_DUP_SUPPLIER_PART'')) ' ||
437 'WHERE NOT it1.ROWID = ( ' ||
438 'SELECT MAX(it2.ROWID) ' ||
439 'FROM ' || p_table_name || ' it2 ' ||
440 'WHERE it2.supplier_part_num = it1.supplier_part_num ' ||
441 'AND it2.supplier_id = it1.supplier_id ' ||
442 'AND NVL(it2.buyer_id,-2) = NVL(it1.buyer_id,-2) ' ||
443 -- Bug#2352152 : Constraint for currency check
444 'AND NVL(it2.currency_code,''USD'') = NVL(it1.currency_code,''USD'') ' ||
445 'AND it2.row_type = it1.row_type )';
446
447 l_cursor_id := DBMS_SQL.open_cursor;
448 xErrLoc := 110;
449 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
450 xErrLoc := 120;
451 l_result_count := DBMS_SQL.execute(l_cursor_id);
452 xErrLoc := 130;
453 DBMS_SQL.close_cursor(l_cursor_id);
454
455 xErrLoc := 140;
456
457 /*
458 Bug# 2192779 - srmani: Included the validation for duplicate item
459 with same supplier-supplier part number but different buyer.
460 In this case there will be only a single entry for the idential items
461 in items table and one entry in price lists table for each buyer.
462 Care is taken care of that the ones modified in the previous cursor
463 are omitted over here.
464 */
465
466 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
467 'SET it1.row_type = ''PRICE'', it1.system_action = ''ADD'' '||
468 'WHERE NOT it1.ROWID = ( ' ||
469 'SELECT MIN(it2.ROWID) ' ||
470 'FROM ' || p_table_name || ' it2 ' ||
471 'WHERE it2.error_message IS NULL ' ||
472 'AND it2.supplier_part_num = it1.supplier_part_num ' ||
473 'AND it2.supplier_id = it1.supplier_id ' ||
474 'AND it2.row_type = it1.row_type ) ' ||
475 'AND it1.error_message IS NULL ';
476
477 l_cursor_id := DBMS_SQL.open_cursor;
478 xErrLoc := 150;
479 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
480 xErrLoc := 160;
481 l_result_count := DBMS_SQL.execute(l_cursor_id);
482 xErrLoc := 170;
483 DBMS_SQL.close_cursor(l_cursor_id);
484
485 commit;
486
487 EXCEPTION
488 WHEN OTHERS THEN
489 ROLLBACK;
490
491 RAISE_APPLICATION_ERROR(-20000,
492 'Exception at ICX_POR_ITEM_UPLOAD_VALIDATE.validate_duplicate_item2('
493 || xErrLoc || '): ' || SQLERRM);
494 END validate_duplicate_item2;
495
496 PROCEDURE check_pricelines(p_table_name IN VARCHAR2,
497 p_start_row IN NUMBER,
498 p_end_row IN NUMBER) IS
499 xErrLoc NUMBER;
500 l_result_count NUMBER;
501 l_cursor_id NUMBER;
502 l_sql_string VARCHAR2(4000);
503 BEGIN
504 xErrLoc := 100;
505
506 l_sql_string := 'UPDATE ' || p_table_name || ' it1 ' ||
507 --Bug#1505751
508 'SET priceline_rowid = (SELECT DISTINCT pl.rowid FROM icx_por_price_list_lines pl ' ||
509 'WHERE it1.pricelist_id = pl.header_id ' ||
510 'AND it1.rt_item_id = pl.item_id ' ||
511 'AND pl.buyer_approval_status = ''APPROVED'' ' ||
512 'AND ((it1.row_type = ''ITEM_PRICE'' ' ||
513 'AND it1.system_action IN (''UPDATE'', ''TRANSLATE'')) ' ||
514 'OR (it1.row_type = ''PRICE'' ' ||
515 'AND it1.system_action IN (''ADD'',''DELETE'')))) ' ||
516 'WHERE line_number between :startrow AND :endrow';
517
518 l_cursor_id := DBMS_SQL.open_cursor;
519 xErrLoc := 110;
520 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
521 xErrLoc := 120;
522 DBMS_SQL.bind_variable(l_cursor_id, ':startrow', p_start_row);
523 xErrLoc := 130;
524 DBMS_SQL.bind_variable(l_cursor_id, ':endrow', p_end_row);
525 xErrLoc := 140;
526 l_result_count := DBMS_SQL.execute(l_cursor_id);
527 xErrLoc := 150;
528 DBMS_SQL.close_cursor(l_cursor_id);
529
530 commit;
531
532 EXCEPTION
533 WHEN OTHERS THEN
534 ROLLBACK;
535
536 RAISE_APPLICATION_ERROR(-20000,
537 'Exception at ICX_POR_ITEM_UPLOAD_VALIDATE.check_pricelines('
538 || xErrLoc || '): ' || SQLERRM);
539 END check_pricelines;
540
541 /**
542 ** Proc : validate_interface_data
543 ** Desc : Validate data of interface table.
544 **/
545
546 PROCEDURE validate_interface_data (p_job_supplier_name IN VARCHAR2,
547 p_job_supplier_id IN NUMBER,
548 p_exchange_operator_name IN VARCHAR2,
549 p_table_name IN VARCHAR2,
550 p_language IN VARCHAR2,
551 p_row_count IN NUMBER) IS
552 xErrLoc NUMBER;
553 l_batch_size NUMBER := 10000;
554 l_start_row NUMBER := 0;
555 l_end_row NUMBER := 0;
556 l_sql_string VARCHAR2(4000);
557 l_cursor_id NUMBER;
558 l_count1 NUMBER := 0;
559 l_result_count NUMBER;
560 l_bus_group_id NUMBER := 0; --BUG#2228935
561 BEGIN
562 xErrLoc := 100;
563
564 IF ICX_POR_ITEM_UPLOAD_VALIDATE.g_debug_channel THEN
565 ICX_POR_ITEM_UPLOAD_VALIDATE.g_job_number :=
566 substr(p_table_name,16,length(p_table_name)-instr(p_table_name,'_IT')+1);
567 ICX_POR_ITEM_UPLOAD_VALIDATE.g_module_name:=
568 'ICX.PLSQL.LOADER.'|| ICX_POR_ITEM_UPLOAD_VALIDATE.g_job_number;
569 fnd_global.apps_initialize(-1, -1, 178);
570 fnd_profile.put('AFLOG_ENABLED', 'Y');
571 fnd_profile.put('AFLOG_MODULE', ICX_POR_ITEM_UPLOAD_VALIDATE.g_module_name);
572 fnd_profile.put('AFLOG_LEVEL', '1');
573 fnd_profile.put('AFLOG_FILENAME', '');
574 fnd_log_repository.init;
575 END IF;
576
577
578 /*Bug#2047776
579 -- Check duplicate item with same file
580 l_sql_string :=
581 'SELECT count(*) - count(distinct supplier_name || supplier_part_num) ' ||
582 'FROM ' || p_table_name;
583
584 l_cursor_id := DBMS_SQL.open_cursor;
585 xErrLoc := 110;
586 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
587 xErrLoc := 120;
588 DBMS_SQL.define_column(l_cursor_id, 1, l_count1);
589 xErrLoc := 130;
590 l_result_count := DBMS_SQL.execute(l_cursor_id);
591 xErrLoc := 140;
592
593 LOOP
594 IF DBMS_SQL.fetch_rows(l_cursor_id) = 0 THEN
595 EXIT;
596 END IF;
597
598 DBMS_SQL.column_value(l_cursor_id, 1, l_count1);
599 END LOOP;
600
601 xErrLoc := 150;
602 DBMS_SQL.close_cursor(l_cursor_id);
603 */
604
605 --BUG#2228935
606 l_bus_group_id := p_job_supplier_id;
607
608 l_sql_string :=
609 'SELECT buy.organization_id ' ||
610 'FROM hr_all_organization_units buy, icx_por_uploader_subtable ipus, ' || p_table_name || ' it ' ||
611 'WHERE buy.business_group_id = :bus_group_id ' ||
612 'AND buy.name = ipus.operating_unit ' ||
613 'AND ipus.job_number = it.job_number ' ||
614 'AND ROWNUM < 2';
615
616 l_cursor_id := DBMS_SQL.open_cursor;
617 xErrLoc := 110;
618 DBMS_SQL.parse(l_cursor_id, l_sql_string, DBMS_SQL.NATIVE);
619 xErrLoc := 120;
620 DBMS_SQL.bind_variable(l_cursor_id, ':bus_group_id', l_bus_group_id);
621 xErrLoc := 130;
622 DBMS_SQL.define_column(l_cursor_id, 1, g_operating_unit_id);
623 xErrLoc := 130;
624 l_result_count := DBMS_SQL.execute(l_cursor_id);
625 xErrLoc := 140;
626
627 IF DBMS_SQL.fetch_rows(l_cursor_id) <> 0 THEN
628 DBMS_SQL.column_value(l_cursor_id, 1, g_operating_unit_id);
629 ELSE
630 g_operating_unit_id := null;
631 END IF;
632
633 xErrLoc := 150;
634 DBMS_SQL.close_cursor(l_cursor_id);
635
636 xErrLoc := 160;
637
638 WHILE l_end_row < p_row_count LOOP
639 l_start_row := l_end_row + 1;
640 l_end_row := l_end_row + l_batch_size;
641 validate_interface_data(p_job_supplier_name, p_job_supplier_id,
642 p_exchange_operator_name, p_table_name, p_language,
643 l_start_row, l_end_row);
644 END LOOP;
645
646 -- Duplicate exists within the same file
647 --Bug#2047776
648 xErrLoc := 170;
649 validate_duplicate_item2(p_table_name);
650
651 -- Populate rowid for price lines that need to be updated
652 xErrLoc := 180;
653 l_start_row := 0;
654 l_end_row := 0;
655
656 WHILE l_end_row < p_row_count LOOP
657 l_start_row := l_end_row + 1;
658 l_end_row := l_end_row + l_batch_size;
659 check_pricelines(p_table_name, l_start_row, l_end_row);
660 END LOOP;
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 ROLLBACK;
665
666 RAISE_APPLICATION_ERROR(-20000,
667 'Exception at ICX_POR_ITEM_UPLOAD_VALIDATE.validate_interface_data('
668 || xErrLoc || '): ' || SQLERRM);
669 END validate_interface_data;
670
671 /*
672 ** Procedure to insert the SQL string and Bind variables into
673 ** FND_LOG_MESSAGES table using the AOL API.
674 */
675
676 PROCEDURE insert_fnd_log_messages(p_debug_bind_variables VARCHAR2,
677 p_debug_sql_string VARCHAR2) is
678 l_size NUMBER := 2000;
679 l_sql_string_length NUMBER := LENGTH(p_debug_sql_string);
680 l_bind_string_length NUMBER := LENGTH(p_debug_bind_variables);
681 l_debug_sql_string VARCHAR2(20000) := p_debug_sql_string;
682 l_debug_bind_variable VARCHAR2(20000) := p_debug_bind_variables;
683 l_start NUMBER := 0;
684
685 BEGIN
686
687 /*Insert the Debug SQL string */
688 WHILE l_start < l_sql_string_length LOOP
689 l_start := l_start + l_size;
690 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
691 ICX_POR_ITEM_UPLOAD_VALIDATE.g_module_name,
692 substrb(l_debug_sql_string,1,l_size));
693 l_debug_sql_string := substrb(l_debug_sql_string,l_size+1);
694 END LOOP;
695
696 l_start := 0;
697 /*Insert the Debug Bind Variable string */
698 WHILE l_start < l_bind_string_length LOOP
699 l_start := l_start + l_size;
700 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
701 ICX_POR_ITEM_UPLOAD_VALIDATE.g_module_name,
702 substrb(l_debug_bind_variable,1,l_size));
703 l_debug_bind_variable := substrb(l_debug_bind_variable,l_size+1);
704 END LOOP;
705
706 END insert_fnd_log_messages;
707
708
709 /* Procedure to set the debug channel*/
710 PROCEDURE set_debug_channel(p_debug_channel number) is
711 BEGIN
712 IF p_debug_channel=1 THEN
713 ICX_POR_ITEM_UPLOAD_VALIDATE.g_debug_channel := true;
714 ELSE
715 ICX_POR_ITEM_UPLOAD_VALIDATE.g_debug_channel :=false;
716 END IF;
717 END set_debug_channel;
718
719
720 END ICX_POR_ITEM_UPLOAD_VALIDATE;