[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXT_TEST
Source
1 PACKAGE BODY ICX_POR_EXT_TEST AS
2 /* $Header: ICXEXTTB.pls 115.10 2004/03/31 18:46:32 vkartik ship $*/
3
4 TYPE tCursorType IS REF CURSOR;
5 gTableTS VARCHAR2(30) := NULL;
6 gIndexTS VARCHAR2(30) := NULL;
7
8 --------------------------------------------------------------
9 -- Test Preparing Procedures --
10 --------------------------------------------------------------
11 -- Create tables for test
12 PROCEDURE createTables
13 IS
14 xErrLoc PLS_INTEGER:= 100;
15 xTableTS VARCHAR2(2000);
16 xIndexTS VARCHAR2(2000);
17
18 BEGIN
19 xErrLoc:= 50;
20 IF gTableTS IS NULL THEN
21 xTableTS := NULL;
22 ELSE
23 xTableTS := 'TABLESPACE ' || gTableTS ||
24 ' STORAGE (INITIAL 160K NEXT 160K PCTINCREASE 0)';
25 END IF;
26
27 IF gIndexTS IS NULL THEN
28 xIndexTS := NULL;
29 ELSE
30 xIndexTS := 'TABLESPACE ' || gIndexTS ||
31 ' STORAGE (INITIAL 160K NEXT 160K PCTINCREASE 0)';
32 END IF;
33
34 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
35 'CREATE TABLE imtl_categories_kfv');
36 EXECUTE IMMEDIATE
37 'CREATE TABLE imtl_categories_kfv( ' ||
38 ' category_id NUMBER, ' ||
39 ' concatenated_segments VARCHAR2(204), ' ||
40 ' structure_id NUMBER, ' ||
41 ' web_status VARCHAR2(1), ' ||
42 ' start_date_active DATE, ' ||
43 ' end_date_active DATE, ' ||
44 ' disable_date DATE, ' ||
45 ' last_update_date DATE) ' || xTableTS;
46 EXECUTE IMMEDIATE
47 'CREATE INDEX imtl_categories_kfv_i1 ON ' ||
48 ' imtl_categories_kfv(category_id) ' || xIndexTS;
49
50 xErrLoc:= 100;
51 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
52 'CREATE TABLE imtl_category_set_valid_cats');
53 EXECUTE IMMEDIATE
54 'CREATE TABLE imtl_category_set_valid_cats( ' ||
55 ' category_id NUMBER, ' ||
56 ' category_set_id NUMBER, ' ||
57 ' last_update_date DATE) ' || xTableTS;
58 EXECUTE IMMEDIATE
59 'CREATE INDEX imtl_category_set_vcats_i1 ON ' ||
60 ' imtl_category_set_valid_cats(category_id) ' || xIndexTS;
61
62 xErrLoc:= 120;
63 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
64 'CREATE TABLE imtl_categories_tl');
65 EXECUTE IMMEDIATE
66 'CREATE TABLE imtl_categories_tl( ' ||
67 ' category_id NUMBER, ' ||
68 ' description VARCHAR2(240), ' ||
69 ' language VARCHAR2(4), ' ||
70 ' source_lang VARCHAR2(4), ' ||
71 ' last_update_date DATE) ' || xTableTS;
72 EXECUTE IMMEDIATE
73 'CREATE INDEX imtl_categories_tl_i1 ON ' ||
74 ' imtl_categories_tl(category_id, language) ' || xIndexTS;
75
76 xErrLoc:= 140;
77 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
78 'CREATE TABLE ipo_reqexpress_headers_all');
79 EXECUTE IMMEDIATE
80 'CREATE TABLE ipo_reqexpress_headers_all( ' ||
81 ' org_id NUMBER, ' ||
82 ' express_name VARCHAR2(25), ' ||
83 ' type_lookup_code VARCHAR2(25), ' ||
84 ' inactive_date DATE, ' ||
85 ' last_update_date DATE) ' || xTableTS;
86 EXECUTE IMMEDIATE
87 'CREATE INDEX ipo_reqexpress_headers_i1 ON ' ||
88 ' ipo_reqexpress_headers_all(org_id, express_name) ' || xIndexTS;
89
90 xErrLoc:= 160;
91 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
92 'CREATE TABLE ipo_reqexpress_lines_all');
93 EXECUTE IMMEDIATE
94 'CREATE TABLE ipo_reqexpress_lines_all( ' ||
95 ' org_id NUMBER, ' ||
96 ' express_name VARCHAR2(25), ' ||
97 ' sequence_num NUMBER, ' ||
98 ' source_type_code VARCHAR2(25), ' ||
99 ' po_header_id NUMBER, ' ||
100 ' po_line_id NUMBER, ' ||
101 ' item_id NUMBER, ' ||
102 ' category_id NUMBER, ' ||
103 ' item_description VARCHAR2(240), ' ||
104 ' item_revision VARCHAR2(3), ' ||
105 ' line_type_id NUMBER, ' ||
106 ' suggested_buyer_id NUMBER, ' ||
107 ' unit_price NUMBER, ' ||
108 ' unit_meas_lookup_code VARCHAR2(25), ' ||
109 ' suggested_vendor_id NUMBER, ' ||
110 ' suggested_vendor_site_id NUMBER, ' ||
111 ' suggested_vendor_product_code VARCHAR2(25), ' ||
112 ' suggested_vendor_contact_id NUMBER, ' ||
113 ' creation_date DATE, ' ||
114 ' last_update_date DATE, ' ||
115 ' allow_price_override_flag VARCHAR2(1), ' ||
116 ' not_to_exceed_price NUMBER, ' ||
117 ' amount NUMBER, ' ||
118 ' suggested_quantity NUMBER) ' || xTableTS;
119 EXECUTE IMMEDIATE
120 'CREATE INDEX ipo_reqexpress_lines_i1 ON ' ||
121 ' ipo_reqexpress_lines_all(org_id, express_name, sequence_num) ' || xIndexTS;
122
123 xErrLoc:= 180;
124 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
125 'CREATE TABLE ipo_headers_all');
126 EXECUTE IMMEDIATE
127 'CREATE TABLE ipo_headers_all( ' ||
128 ' po_header_id NUMBER, ' ||
129 ' org_id NUMBER, ' ||
130 ' segment1 VARCHAR2(20), ' ||
131 ' type_lookup_code VARCHAR2(25), ' ||
132 ' rate_type VARCHAR2(30), ' ||
133 ' rate_date DATE, ' ||
134 ' rate NUMBER, ' ||
135 ' vendor_contact_id NUMBER, ' ||
136 ' agent_id NUMBER, ' ||
137 ' currency_code VARCHAR2(15), ' ||
138 ' vendor_id NUMBER, ' ||
139 ' vendor_site_id NUMBER, ' ||
140 ' approved_date DATE, ' ||
141 ' approved_flag VARCHAR2(1), ' ||
142 ' approval_required_flag VARCHAR2(1), ' ||
143 ' cancel_flag VARCHAR2(1), ' ||
144 ' frozen_flag VARCHAR2(1), ' ||
145 ' closed_code VARCHAR2(25), ' ||
146 ' status_lookup_code VARCHAR2(25), ' ||
147 ' quotation_class_code VARCHAR2(25), ' ||
148 ' start_date DATE, ' ||
149 ' end_date DATE, ' ||
150 ' global_agreement_flag VARCHAR2(1), ' ||
151 ' last_update_date DATE) ' || xTableTS;
152 EXECUTE IMMEDIATE
153 'CREATE INDEX ipo_headers_all_i1 ON ' ||
154 ' ipo_headers_all(po_header_id) ' || xIndexTS;
155 EXECUTE IMMEDIATE
156 'CREATE INDEX ipo_headers_all_i2 ON ' ||
157 ' ipo_headers_all(org_id, segment1) ' || xIndexTS;
158
159 -- FPJ FPSL Extractor Changes
160 -- Add 3 columns for Amount, Allow Price Override Flag and
161 -- Not to Exceed Price
162 xErrLoc:= 200;
163 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
164 'CREATE TABLE ipo_lines_all');
165 EXECUTE IMMEDIATE
166 'CREATE TABLE ipo_lines_all( ' ||
167 ' po_header_id NUMBER, ' ||
168 ' po_line_id NUMBER, ' ||
169 ' org_id NUMBER, ' ||
170 ' line_num NUMBER, ' ||
171 ' item_id NUMBER, ' ||
172 ' item_description VARCHAR2(240), ' ||
173 ' vendor_product_num VARCHAR2(25), ' ||
174 ' line_type_id NUMBER, ' ||
175 ' category_id NUMBER, ' ||
176 ' unit_price NUMBER, ' ||
177 ' unit_meas_lookup_code VARCHAR2(25), ' ||
178 ' attribute13 VARCHAR2(150), ' ||
179 ' attribute14 VARCHAR2(150), ' ||
180 ' cancel_flag VARCHAR2(1), ' ||
181 ' closed_code VARCHAR2(25), ' ||
182 ' expiration_date DATE, ' ||
183 ' item_revision VARCHAR2(3), ' ||
184 ' creation_date DATE, ' ||
185 ' last_update_date DATE, ' ||
186 ' amount NUMBER, ' ||
187 ' allow_price_override_flag VARCHAR2(1), ' ||
188 ' not_to_exceed_price NUMBER) ' || xTableTS;
189 EXECUTE IMMEDIATE
190 'CREATE INDEX ipo_lines_all_i1 ON ' ||
191 ' ipo_lines_all(po_header_id) ' || xIndexTS;
192 EXECUTE IMMEDIATE
193 'CREATE INDEX ipo_lines_all_i2 ON ' ||
194 ' ipo_lines_all(po_line_id) ' || xIndexTS;
195
196 xErrLoc:= 220;
197 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
198 'CREATE TABLE ipo_line_locations_all');
199 EXECUTE IMMEDIATE
200 'CREATE TABLE ipo_line_locations_all( ' ||
201 ' line_location_id NUMBER, ' ||
202 ' po_line_id NUMBER, ' ||
203 ' start_date DATE, ' ||
204 ' end_date DATE, ' ||
205 ' last_update_date DATE) ' || xTableTS;
206 EXECUTE IMMEDIATE
207 'CREATE INDEX ipo_line_locations_all_i1 ON ' ||
208 ' ipo_line_locations_all(line_location_id) ' || xIndexTS;
209 EXECUTE IMMEDIATE
210 'CREATE INDEX ipo_line_locations_all_i2 ON ' ||
211 ' ipo_line_locations_all(po_line_id) ' || xIndexTS;
212
213 xErrLoc:= 240;
214 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
215 'CREATE TABLE ipo_quotation_approvals_all');
216 EXECUTE IMMEDIATE
217 'CREATE TABLE ipo_quotation_approvals_all( ' ||
218 ' line_location_id NUMBER, ' ||
219 ' approval_type VARCHAR2(25), ' ||
220 ' start_date_active DATE, ' ||
221 ' end_date_active DATE, ' ||
222 ' last_update_date DATE) ' || xTableTS;
223 EXECUTE IMMEDIATE
224 'CREATE INDEX ipo_quotation_approvals_i1 ON ' ||
225 ' ipo_quotation_approvals_all(line_location_id) ' || xIndexTS;
226
227 --FPJ FPSL project
228 --Changing ipo_line_types to ipo_line_types_b
229 --Since the columns required for catalog are present in po_line_types_b
230 --No need of using po_line_types which is view on po_line_types_b and
231 --po_line_types_tl
232 -- Add 2 columns for order_type_lookup_code and purchase_basis
233 xErrLoc:= 260;
234 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
235 'CREATE TABLE ipo_line_types_b');
236 EXECUTE IMMEDIATE
237 'CREATE TABLE ipo_line_types_b( ' ||
238 ' line_type_id NUMBER, ' ||
239 ' outside_operation_flag VARCHAR2(1), ' ||
240 ' last_update_date DATE, ' ||
241 ' order_type_lookup_code VARCHAR2(25), '||
242 ' purchase_basis VARCHAR2(30) ) ' || xTableTS;
243 EXECUTE IMMEDIATE
244 'CREATE INDEX ipo_line_types_b_i1 ON ' ||
245 ' ipo_line_types_b(line_type_id) ' || xIndexTS;
246
247 xErrLoc:= 280;
248 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
249 'CREATE TABLE ipo_ga_org_assignments');
250 EXECUTE IMMEDIATE
251 'CREATE TABLE ipo_ga_org_assignments( ' ||
252 ' po_header_id NUMBER, ' ||
253 ' organization_id NUMBER, ' ||
254 ' enabled_flag VARCHAR2(1), ' ||
255 ' vendor_site_id NUMBER, ' ||
256 ' purchasing_org_id NUMBER, ' || -- Centralized Proc Impacts
257 ' last_update_date DATE) ' || xTableTS;
258 EXECUTE IMMEDIATE
259 'CREATE INDEX ipo_ga_org_assignments_i1 ON ' ||
260 ' ipo_ga_org_assignments(po_header_id) ' || xIndexTS;
261
262 xErrLoc:= 300;
263 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
264 'CREATE TABLE ipo_asl_attributes');
265 EXECUTE IMMEDIATE
266 'CREATE TABLE ipo_asl_attributes( ' ||
267 ' asl_id NUMBER, ' ||
268 ' purchasing_unit_of_measure VARCHAR2(25), ' ||
269 ' last_update_date DATE) ' || xTableTS;
270 EXECUTE IMMEDIATE
271 'CREATE INDEX ipo_asl_attributes_i1 ON ' ||
272 ' ipo_asl_attributes(asl_id) ' || xIndexTS;
273
274 xErrLoc:= 320;
275 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
276 'CREATE TABLE ipo_approved_supplier_list');
277 EXECUTE IMMEDIATE
278 'CREATE TABLE ipo_approved_supplier_list( ' ||
279 ' asl_id NUMBER, ' ||
280 ' asl_status_id NUMBER, ' ||
281 ' owning_organization_id NUMBER, ' ||
282 ' item_id NUMBER, ' ||
283 ' category_id NUMBER, ' ||
284 ' vendor_id NUMBER, ' ||
285 ' vendor_site_id NUMBER, ' ||
286 ' primary_vendor_item VARCHAR2(25), ' ||
287 ' disable_flag VARCHAR2(1), ' ||
288 ' creation_date DATE, ' ||
289 ' last_update_date DATE) ' || xTableTS;
290 EXECUTE IMMEDIATE
291 'CREATE INDEX ipo_asl_i1 ON ' ||
292 ' ipo_approved_supplier_list(asl_id) ' || xIndexTS;
293
294 xErrLoc:= 340;
295 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
296 'CREATE TABLE ipo_asl_status_rules');
297 EXECUTE IMMEDIATE
298 'CREATE TABLE ipo_asl_status_rules( ' ||
299 ' status_id NUMBER, ' ||
300 ' business_rule VARCHAR2(25), ' ||
301 ' allow_action_flag VARCHAR2(1), ' ||
302 ' last_update_date DATE) ' || xTableTS;
303 EXECUTE IMMEDIATE
304 'CREATE INDEX ipo_asl_status_rules_i1 ON ' ||
305 ' ipo_asl_status_rules(status_id) ' || xIndexTS;
306
307 xErrLoc:= 360;
308 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
309 'CREATE TABLE ipo_vendors');
310 EXECUTE IMMEDIATE
311 'CREATE TABLE ipo_vendors( ' ||
312 ' vendor_id NUMBER, ' ||
313 ' vendor_name VARCHAR2(240), ' ||
314 ' segment1 VARCHAR2(30), ' ||
315 ' last_update_date DATE) ' || xTableTS;
316 EXECUTE IMMEDIATE
317 'CREATE INDEX ipo_vendors_i1 ON ' ||
318 ' ipo_vendors(vendor_id) ' || xIndexTS;
319 EXECUTE IMMEDIATE
320 'CREATE INDEX ipo_vendors_i2 ON ' ||
321 ' ipo_vendors(vendor_name) ' || xIndexTS;
322
323 xErrLoc:= 380;
324 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
325 'CREATE TABLE ipo_vendor_sites_all');
326 EXECUTE IMMEDIATE
327 'CREATE TABLE ipo_vendor_sites_all( ' ||
328 ' vendor_site_id NUMBER, ' ||
329 ' vendor_site_code VARCHAR2(15), ' ||
330 ' purchasing_site_flag VARCHAR2(1), ' ||
331 ' inactive_date DATE, ' ||
332 ' last_update_date DATE) ' || xTableTS;
333 EXECUTE IMMEDIATE
334 'CREATE INDEX ipo_vendor_sites_all_i1 ON ' ||
335 ' ipo_vendor_sites_all(vendor_site_id) ' || xIndexTS;
336 EXECUTE IMMEDIATE
337 'CREATE INDEX ipo_vendor_sites_all_i2 ON ' ||
338 ' ipo_vendor_sites_all(vendor_site_code) ' || xIndexTS;
339
340 xErrLoc:= 400;
341 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
342 'CREATE TABLE imtl_system_items_kfv');
343 EXECUTE IMMEDIATE
344 'CREATE TABLE imtl_system_items_kfv( ' ||
345 ' inventory_item_id NUMBER, ' ||
346 ' organization_id NUMBER, ' ||
347 ' concatenated_segments VARCHAR2(40), ' ||
348 ' purchasing_enabled_flag VARCHAR2(1), ' ||
349 ' outside_operation_flag VARCHAR2(1), ' ||
350 ' internal_order_enabled_flag VARCHAR2(1), ' ||
351 ' list_price_per_unit NUMBER, ' ||
352 ' primary_uom_code VARCHAR2(3), ' ||
353 ' replenish_to_order_flag VARCHAR2(1), ' ||
354 ' base_item_id NUMBER, ' ||
355 ' auto_created_config_flag VARCHAR2(1), ' ||
356 ' unit_of_issue VARCHAR2(25), ' ||
357 ' last_update_date DATE) ' || xTableTS;
358 EXECUTE IMMEDIATE
359 'CREATE INDEX imtl_system_items_kfv_i1 ON ' ||
360 ' imtl_system_items_kfv(inventory_item_id, '||
361 ' organization_id) ' || xIndexTS;
362
363 xErrLoc:= 420;
364 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
365 'CREATE TABLE imtl_system_items_tl');
366 EXECUTE IMMEDIATE
367 'CREATE TABLE imtl_system_items_tl( ' ||
368 ' inventory_item_id NUMBER, ' ||
369 ' organization_id NUMBER, ' ||
370 ' description VARCHAR2(240), ' ||
371 ' language VARCHAR2(4), ' ||
372 ' source_lang VARCHAR2(4), ' ||
373 ' last_update_date DATE) ' || xTableTS;
374 EXECUTE IMMEDIATE
375 'CREATE INDEX imtl_system_items_tl_i1 ON ' ||
376 ' imtl_system_items_tl(inventory_item_id, '||
377 ' organization_id, language) ' || xIndexTS;
378
379 xErrLoc:= 440;
380 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
381 'CREATE TABLE imtl_item_categories');
382 EXECUTE IMMEDIATE
383 'CREATE TABLE imtl_item_categories( ' ||
384 ' inventory_item_id NUMBER, ' ||
385 ' organization_id NUMBER, ' ||
386 ' category_id NUMBER, ' ||
387 ' category_set_id NUMBER, ' ||
388 ' last_update_date DATE) ' || xTableTS;
389 EXECUTE IMMEDIATE
390 'CREATE INDEX imtl_item_categories_i1 ON ' ||
391 ' imtl_item_categories(inventory_item_id, category_id, '||
392 ' organization_id) ' || xIndexTS;
393
394 xErrLoc:= 460;
395 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
396 'CREATE TABLE ifinancials_system_params_all');
397 EXECUTE IMMEDIATE
398 'CREATE TABLE ifinancials_system_params_all( ' ||
399 ' org_id NUMBER, ' ||
400 ' inventory_organization_id NUMBER, ' ||
401 ' set_of_books_id NUMBER) ' || xTableTS;
402
403 xErrLoc:= 480;
404 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
405 'CREATE TABLE ipo_system_parameters_all');
406 EXECUTE IMMEDIATE
407 'CREATE TABLE ipo_system_parameters_all( ' ||
408 ' org_id NUMBER, ' ||
409 ' default_rate_type VARCHAR2(25), ' ||
410 ' last_update_date DATE) ' || xTableTS; -- Bug# 2945205 : pcreddy
411
412 xErrLoc:= 500;
413 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
414 'CREATE TABLE igl_sets_of_books');
415 EXECUTE IMMEDIATE
416 'CREATE TABLE igl_sets_of_books( ' ||
417 ' set_of_books_id NUMBER, ' ||
418 ' currency_code VARCHAR2(15)) ' || xTableTS;
419
420 xErrLoc:= 600;
421 EXCEPTION
422 WHEN OTHERS THEN
423 ROLLBACK;
424 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTables-'||
425 xErrLoc||' '||SQLERRM);
426 raise ICX_POR_EXT_UTL.gException;
427 END createTables;
428
429 PROCEDURE setCommitSize(pCommitSize NUMBER)
430 IS
431 BEGIN
432 gCommitSize := pCommitSize;
433 END setCommitSize;
434
435 PROCEDURE setTestMode(pTestMode VARCHAR2)
436 IS
437 BEGIN
438 gTestMode := pTestMode;
439 END setTestMode;
440
441 PROCEDURE setTableSpace(pTableTS VARCHAR2,
442 pIndexTS VARCHAR2)
443 IS
444 BEGIN
445 gTableTS := pTableTS;
446 gIndexTS := pIndexTS;
447 END setTableSpace;
448
449 -- Prepare unit testing
450 PROCEDURE prepare(pCreateTables VARCHAR2)
451 IS
452 xErrLoc PLS_INTEGER:= 100;
453 xReturnErr VARCHAR2(2000);
454 xStatus VARCHAR2(20);
455 xIndustry VARCHAR2(20);
456 xIndex PLS_INTEGER:= 0;
457 BEGIN
458 xErrLoc:= 50;
459 gTestMode := 'Y';
460
461 xErrLoc:= 80;
462 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Prepare...');
463
464 xErrLoc:= 100;
465 IF NVL(pCreateTables, 'Y') = 'Y' THEN
466 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
467 'Prepare: Create testing tables');
468 createTables;
469 END IF;
470
471 xErrLoc:= 120;
472 -- get category set info
473 SELECT category_set_id,
474 validate_flag,
475 structure_id
476 INTO gCategorySetId,
477 gValidateFlag,
478 gStructureId
479 FROM mtl_default_sets_view
480 WHERE functional_area_id = 2;
481
482 xErrLoc:= 140;
483 SELECT language_code
484 INTO gBaseLang
485 FROM fnd_languages
486 WHERE installed_flag = 'B';
487
488 xErrLoc:= 300;
489 EXCEPTION
490 WHEN OTHERS THEN
491 ROLLBACK;
492 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.prepare-'||
493 xErrLoc||' '||SQLERRM);
494 raise ICX_POR_EXT_UTL.gException;
495 END prepare;
496
497 --------------------------------------------------------------
498 -- Test Cleanup Procedures --
499 --------------------------------------------------------------
500 -- Drop tables for test
501 PROCEDURE dropTables
502 IS
503 xErrLoc PLS_INTEGER:= 100;
504
505 BEGIN
506 xErrLoc:= 100;
507 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
508 'DROP TABLE imtl_categories_kfv');
509 EXECUTE IMMEDIATE
510 'DROP TABLE imtl_categories_kfv';
511
512 xErrLoc:= 120;
513 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
514 'DROP TABLE imtl_category_set_valid_cats');
515 EXECUTE IMMEDIATE
516 'DROP TABLE imtl_category_set_valid_cats';
517
518 xErrLoc:= 140;
519 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
520 'DROP TABLE imtl_categories_tl');
521 EXECUTE IMMEDIATE
522 'DROP TABLE imtl_categories_tl';
523
524 xErrLoc:= 160;
525 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
526 'DROP TABLE ipo_reqexpress_headers_all');
527 EXECUTE IMMEDIATE
528 'DROP TABLE ipo_reqexpress_headers_all';
529
530 xErrLoc:= 180;
531 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
532 'DROP TABLE ipo_reqexpress_lines_all');
533 EXECUTE IMMEDIATE
534 'DROP TABLE ipo_reqexpress_lines_all';
535
536 xErrLoc:= 200;
537 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
538 'DROP TABLE ipo_headers_all');
539 EXECUTE IMMEDIATE
540 'DROP TABLE ipo_headers_all';
541
542 xErrLoc:= 220;
543 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
544 'DROP TABLE ipo_lines_all');
545 EXECUTE IMMEDIATE
546 'DROP TABLE ipo_lines_all';
547
548 xErrLoc:= 240;
549 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
550 'DROP TABLE ipo_line_locations_all');
551 EXECUTE IMMEDIATE
552 'DROP TABLE ipo_line_locations_all';
553
554 xErrLoc:= 260;
555 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
556 'DROP TABLE ipo_quotation_approvals_all');
557 EXECUTE IMMEDIATE
558 'DROP TABLE ipo_quotation_approvals_all';
559
560 xErrLoc:= 280;
561 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
562 'DROP TABLE ipo_line_types_b');
563 EXECUTE IMMEDIATE
564 'DROP TABLE ipo_line_types_b';
565 xErrLoc:= 300;
566
567 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
568 'DROP TABLE ipo_ga_org_assignments');
569 EXECUTE IMMEDIATE
570 'DROP TABLE ipo_ga_org_assignments';
571
572 xErrLoc:= 320;
573 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
574 'DROP TABLE ipo_asl_attributes');
575 EXECUTE IMMEDIATE
576 'DROP TABLE ipo_asl_attributes';
577
578 xErrLoc:= 340;
579 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
580 'DROP TABLE ipo_approved_supplier_list');
581 EXECUTE IMMEDIATE
582 'DROP TABLE ipo_approved_supplier_list';
583
584 xErrLoc:= 360;
585 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
586 'DROP TABLE ipo_asl_status_rules');
587 EXECUTE IMMEDIATE
588 'DROP TABLE ipo_asl_status_rules';
589
590 xErrLoc:= 380;
591 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
592 'DROP TABLE ipo_vendors');
593 EXECUTE IMMEDIATE
594 'DROP TABLE ipo_vendors';
595
596 xErrLoc:= 400;
597 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
598 'DROP TABLE ipo_vendor_sites_all');
599 EXECUTE IMMEDIATE
600 'DROP TABLE ipo_vendor_sites_all';
601
602 xErrLoc:= 420;
603 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
604 'DROP TABLE imtl_system_items_kfv');
605 EXECUTE IMMEDIATE
606 'DROP TABLE imtl_system_items_kfv';
607
608 xErrLoc:= 440;
609 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
610 'DROP TABLE imtl_system_items_tl');
611 EXECUTE IMMEDIATE
612 'DROP TABLE imtl_system_items_tl';
613
614 xErrLoc:= 460;
615 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
616 'DROP TABLE imtl_item_categories');
617 EXECUTE IMMEDIATE
618 'DROP TABLE imtl_item_categories';
619
620 xErrLoc:= 480;
621 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
622 'DROP TABLE ifinancials_system_params_all');
623 EXECUTE IMMEDIATE
624 'DROP TABLE ifinancials_system_params_all';
625
626 xErrLoc:= 500;
627 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
628 'DROP TABLE ipo_system_parameters_all');
629 EXECUTE IMMEDIATE
630 'DROP TABLE ipo_system_parameters_all';
631
632 xErrLoc:= 520;
633 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
634 'DROP TABLE igl_sets_of_books');
635 EXECUTE IMMEDIATE
636 'DROP TABLE igl_sets_of_books';
637
638 xErrLoc:= 600;
639 EXCEPTION
640 WHEN OTHERS THEN
641 ROLLBACK;
642 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.dropTables-'||
643 xErrLoc||' '||SQLERRM);
644 raise ICX_POR_EXT_UTL.gException;
645 END dropTables;
646
647 -- Clean up data for unit test
648 PROCEDURE cleanupData
649 IS
650 xErrLoc PLS_INTEGER:= 100;
651 xString VARCHAR2(2000);
652 cTestRows tCursorType;
653 xRowIds DBMS_SQL.UROWID_TABLE;
654 xRowCount PLS_INTEGER := 0;
655
656 BEGIN
657 xErrLoc:= 100;
658 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
659 'Delete test data from icx_cat_categories_tl');
660 xErrLoc:= 120;
661 OPEN cTestRows FOR
662 SELECT ROWID FROM icx_cat_categories_tl
663 WHERE last_updated_by = TEST_USER_ID;
664 xErrLoc := 140;
665 LOOP
666 xRowIds.DELETE;
667 xErrLoc := 160;
668 FETCH cTestRows
669 BULK COLLECT INTO xRowIds
670 LIMIT gCommitSize;
671 EXIT WHEN xRowIds.COUNT = 0;
672 xRowCount := xRowCount + xRowIds.COUNT;
673 xErrLoc := 180;
674 FORALL i IN 1..xRowIds.COUNT
675 DELETE icx_cat_categories_tl
676 WHERE rowid = xRowIds(i);
677 COMMIT;
678 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
679 'Processed records: ' || xRowCount);
680 END LOOP;
681 CLOSE cTestRows;
682
683 xErrLoc:= 200;
684 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
685 'Delete test data from icx_por_category_data_sources');
686 xErrLoc:= 220;
687 xRowCount := 0;
688 OPEN cTestRows FOR
689 SELECT ROWID FROM icx_por_category_data_sources
690 WHERE last_updated_by = TEST_USER_ID;
691 xErrLoc := 240;
692 LOOP
693 xRowIds.DELETE;
694 xErrLoc := 260;
695 FETCH cTestRows
696 BULK COLLECT INTO xRowIds
697 LIMIT gCommitSize;
698 EXIT WHEN xRowIds.COUNT = 0;
699 xRowCount := xRowCount + xRowIds.COUNT;
700 xErrLoc := 280;
701 FORALL i IN 1..xRowIds.COUNT
702 DELETE icx_por_category_data_sources
703 WHERE rowid = xRowIds(i);
704 COMMIT;
705 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
706 'Processed records: ' || xRowCount);
707 END LOOP;
708 CLOSE cTestRows;
709
710 xErrLoc:= 300;
711 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
712 'Delete test data from icx_por_category_order_map');
713 xErrLoc:= 320;
714 xRowCount := 0;
715 OPEN cTestRows FOR
716 SELECT ROWID FROM icx_por_category_order_map
717 WHERE last_updated_by = TEST_USER_ID;
718 xErrLoc := 340;
719 LOOP
720 xRowIds.DELETE;
721 xErrLoc := 360;
722 FETCH cTestRows
723 BULK COLLECT INTO xRowIds
724 LIMIT gCommitSize;
725 EXIT WHEN xRowIds.COUNT = 0;
726 xRowCount := xRowCount + xRowIds.COUNT;
727 xErrLoc := 380;
728 FORALL i IN 1..xRowIds.COUNT
729 DELETE icx_por_category_order_map
730 WHERE rowid = xRowIds(i);
731 COMMIT;
732 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
733 'Processed records: ' || xRowCount);
734 END LOOP;
735 CLOSE cTestRows;
736
737 xErrLoc:= 400;
738 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
739 'Delete test data from icx_cat_items_b');
740 xErrLoc:= 420;
741 xRowCount := 0;
742 OPEN cTestRows FOR
743 SELECT ROWID FROM icx_cat_items_b
744 WHERE last_updated_by = TEST_USER_ID;
745 xErrLoc := 440;
746 LOOP
747 xRowIds.DELETE;
748 xErrLoc := 460;
749 FETCH cTestRows
750 BULK COLLECT INTO xRowIds
751 LIMIT gCommitSize;
752 EXIT WHEN xRowIds.COUNT = 0;
753 xRowCount := xRowCount + xRowIds.COUNT;
754 xErrLoc := 480;
755 FORALL i IN 1..xRowIds.COUNT
756 DELETE icx_cat_items_b
757 WHERE rowid = xRowIds(i);
758 COMMIT;
759 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
760 'Processed records: ' || xRowCount);
761 END LOOP;
762 CLOSE cTestRows;
763
764 xErrLoc:= 500;
765 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
766 'Delete test data from icx_cat_items_tlp');
767 xErrLoc:= 520;
768 xRowCount := 0;
769 OPEN cTestRows FOR
770 SELECT ROWID FROM icx_cat_items_tlp
771 WHERE last_updated_by = TEST_USER_ID;
772 xErrLoc := 540;
773 LOOP
774 xRowIds.DELETE;
775 xErrLoc := 560;
776 FETCH cTestRows
777 BULK COLLECT INTO xRowIds
778 LIMIT gCommitSize;
779 EXIT WHEN xRowIds.COUNT = 0;
780 xRowCount := xRowCount + xRowIds.COUNT;
781 xErrLoc := 580;
782 FORALL i IN 1..xRowIds.COUNT
783 DELETE icx_cat_items_tlp
784 WHERE rowid = xRowIds(i);
785 COMMIT;
786 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
787 'Processed records: ' || xRowCount);
788 END LOOP;
789 CLOSE cTestRows;
790
791 xErrLoc:= 600;
792 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
793 'Delete test data from icx_cat_category_items');
794 xErrLoc:= 620;
795 xRowCount := 0;
796 OPEN cTestRows FOR
797 SELECT ROWID FROM icx_cat_category_items
798 WHERE last_updated_by = TEST_USER_ID;
799 xErrLoc := 640;
800 LOOP
801 xRowIds.DELETE;
802 xErrLoc := 660;
803 FETCH cTestRows
804 BULK COLLECT INTO xRowIds
805 LIMIT gCommitSize;
806 EXIT WHEN xRowIds.COUNT = 0;
807 xRowCount := xRowCount + xRowIds.COUNT;
808 xErrLoc := 680;
809 FORALL i IN 1..xRowIds.COUNT
810 DELETE icx_cat_category_items
811 WHERE rowid = xRowIds(i);
812 COMMIT;
813 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
814 'Processed records: ' || xRowCount);
815 END LOOP;
816 CLOSE cTestRows;
817
818 xErrLoc:= 700;
819 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
820 'Delete test data from icx_cat_ext_items_tlp');
821 xErrLoc:= 720;
822 xRowCount := 0;
823 OPEN cTestRows FOR
824 SELECT ROWID FROM icx_cat_ext_items_tlp
825 WHERE last_updated_by = TEST_USER_ID;
826 xErrLoc := 740;
827 LOOP
828 xRowIds.DELETE;
829 xErrLoc := 760;
830 FETCH cTestRows
831 BULK COLLECT INTO xRowIds
832 LIMIT gCommitSize;
833 EXIT WHEN xRowIds.COUNT = 0;
834 xRowCount := xRowCount + xRowIds.COUNT;
835 xErrLoc := 780;
836 FORALL i IN 1..xRowIds.COUNT
837 DELETE icx_cat_ext_items_tlp
838 WHERE rowid = xRowIds(i);
839 COMMIT;
840 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
841 'Processed records: ' || xRowCount);
842 END LOOP;
843 CLOSE cTestRows;
844
845 xErrLoc:= 800;
846 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
847 'Delete test data from icx_cat_item_prices');
848 xErrLoc:= 820;
849 xRowCount := 0;
850 OPEN cTestRows FOR
851 SELECT ROWID FROM icx_cat_item_prices
852 WHERE last_updated_by = TEST_USER_ID;
853 xErrLoc := 840;
854 LOOP
855 xRowIds.DELETE;
856 xErrLoc := 860;
857 FETCH cTestRows
858 BULK COLLECT INTO xRowIds
859 LIMIT gCommitSize;
860 EXIT WHEN xRowIds.COUNT = 0;
861 xRowCount := xRowCount + xRowIds.COUNT;
862 xErrLoc := 880;
863 FORALL i IN 1..xRowIds.COUNT
864 DELETE icx_cat_item_prices
865 WHERE rowid = xRowIds(i);
866 COMMIT;
867 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
868 'Processed records: ' || xRowCount);
869 END LOOP;
870 CLOSE cTestRows;
871
872 xErrLoc:= 900;
873 COMMIT;
874 EXCEPTION
875 WHEN OTHERS THEN
876 IF (cTestRows%ISOPEN) THEN
877 CLOSE cTestRows;
878 END IF;
879 ROLLBACK;
880 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanupData-'||
881 xErrLoc||' '||SQLERRM);
882 raise ICX_POR_EXT_UTL.gException;
883 END cleanupData;
884
885 -- Cleanup unit testing
886 PROCEDURE cleanup
887 IS
888 xErrLoc PLS_INTEGER:= 100;
889 xReturnErr varchar2(2000);
890
891 BEGIN
892 xErrLoc:= 100;
893 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
894 'Drop tables');
895 dropTables;
896 xErrLoc:= 200;
897 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
898 'Clean up data for unit test');
899 cleanupData;
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 ROLLBACK;
904 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanup-'||
905 xErrLoc||' '||SQLERRM);
906 raise ICX_POR_EXT_UTL.gException;
907 END cleanup;
908
909 --------------------------------------------------------------
910 -- Classification Test Utilities --
911 --------------------------------------------------------------
912 -- Create a category
913 PROCEDURE createCategory(p_category_id IN NUMBER,
914 p_concatenated_segments IN VARCHAR2,
915 p_description IN VARCHAR2,
916 p_web_status IN VARCHAR2,
917 p_start_date_active IN DATE,
918 p_end_date_active IN DATE,
919 p_disable_date IN DATE)
920 IS
921 xErrLoc PLS_INTEGER:= 100;
922
923 BEGIN
924 xErrLoc:= 50;
925 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
926 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
927 'createCategory(p_category_id: ' || p_category_id ||
928 ', p_concatenated_segments: ' || p_concatenated_segments ||
929 ', p_description: ' || p_description ||
930 ', p_web_status: ' || p_web_status ||
931 ', p_start_date_active: ' || p_start_date_active ||
932 ', p_end_date_active: ' || p_end_date_active ||
933 ', p_disable_date: ' || p_disable_date || ')');
934 END IF;
935
936 xErrLoc:= 100;
937 EXECUTE IMMEDIATE
938 'INSERT INTO imtl_categories_kfv( ' ||
939 'category_id, ' ||
940 'concatenated_segments, ' ||
941 'structure_id, ' ||
942 'web_status, ' ||
943 'start_date_active, ' ||
944 'end_date_active, ' ||
945 'disable_date, ' ||
946 'last_update_date) ' ||
947 'VALUES( ' ||
948 ':category_id, ' ||
949 ':concatenated_segments, ' ||
950 ':structure_id, ' ||
951 ':web_status, ' ||
952 ':start_date_active, ' ||
953 ':end_date_active, ' ||
954 ':disable_date, ' ||
955 'SYSDATE) '
956 USING p_category_id, p_concatenated_segments, gStructureId,
957 p_web_status, p_start_date_active, p_end_date_active,
958 p_disable_date;
959
960 xErrLoc:= 200;
961 EXECUTE IMMEDIATE
962 'INSERT INTO imtl_category_set_valid_cats( ' ||
963 'category_id, ' ||
964 'category_set_id, ' ||
965 'last_update_date) ' ||
966 'VALUES( ' ||
967 ':category_id, ' ||
968 ':category_set_id, ' ||
969 'SYSDATE) '
970 USING p_category_id, gCategorySetId;
971
972 xErrLoc:= 300;
973 EXECUTE IMMEDIATE
974 'INSERT INTO imtl_categories_tl( ' ||
975 'category_id, ' ||
976 'description, ' ||
977 'language, ' ||
978 'source_lang, ' ||
979 'last_update_date) ' ||
980 'VALUES( '||
981 ':category_id, ' ||
982 ':description, ' ||
983 ':language, ' ||
984 ':language, ' ||
985 'SYSDATE) '
986 USING p_category_id, p_description,
987 gBaseLang, gBaseLang;
988
989 xErrLoc:= 400;
990 COMMIT;
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 ROLLBACK;
995 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createCategory-'||
996 xErrLoc||' '||SQLERRM);
997 raise ICX_POR_EXT_UTL.gException;
998 END createCategory;
999
1000 -- Update a category
1001 PROCEDURE updateCategory(p_category_id IN NUMBER,
1002 p_concatenated_segments IN VARCHAR2,
1003 p_description IN VARCHAR2,
1004 p_web_status IN VARCHAR2,
1005 p_start_date_active IN DATE,
1006 p_end_date_active IN DATE,
1007 p_disable_date IN DATE)
1008 IS
1009 xErrLoc PLS_INTEGER:= 100;
1010
1011 BEGIN
1012 xErrLoc:= 50;
1013 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1014 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1015 'updateCategory(p_category_id: ' || p_category_id ||
1016 ', p_concatenated_segments: ' || p_concatenated_segments ||
1017 ', p_description: ' || p_description ||
1018 ', p_web_status: ' || p_web_status ||
1019 ', p_start_date_active: ' || p_start_date_active ||
1020 ', p_end_date_active: ' || p_end_date_active ||
1021 ', p_disable_date: ' || p_disable_date || ')');
1022 END IF;
1023
1024 xErrLoc:= 100;
1025 EXECUTE IMMEDIATE
1026 'UPDATE imtl_categories_kfv ' ||
1027 'SET concatenated_segments = DECODE(:concatenated_segments, ' ||
1028 ':miss_char, concatenated_segments,:concatenated_segments), ' ||
1029 'web_status = DECODE(:web_status,:miss_char, ' ||
1030 'web_status,:web_status), ' ||
1031 'start_date_active = DECODE(:start_date_active,:miss_date, ' ||
1032 'start_date_active,:start_date_active), ' ||
1033 'end_date_active = DECODE(:end_date_active,:miss_date, ' ||
1034 'end_date_active,:end_date_active), ' ||
1035 'disable_date = DECODE(:disable_date,:miss_date, disable_date, ' ||
1036 ':disable_date), ' ||
1037 'last_update_date = SYSDATE ' ||
1038 'WHERE category_id =:category_id '
1039 USING p_concatenated_segments, FND_API.G_MISS_CHAR, p_concatenated_segments,
1040 p_web_status, FND_API.G_MISS_CHAR, p_web_status,
1041 p_start_date_active, FND_API.G_MISS_DATE, p_start_date_active,
1042 p_end_date_active, FND_API.G_MISS_DATE, p_end_date_active,
1043 p_disable_date, FND_API.G_MISS_DATE, p_disable_date,
1044 p_category_id;
1045
1046
1047 xErrLoc:= 200;
1048 EXECUTE IMMEDIATE
1049 'UPDATE imtl_categories_tl ' ||
1050 'SET description = DECODE(:description,:miss_char, ' ||
1051 'description,:description), ' ||
1052 'last_update_date = SYSDATE ' ||
1053 'WHERE category_id =:category_id ' ||
1054 'AND language =:language '
1055 USING p_description, FND_API.G_MISS_CHAR, p_description,
1056 p_category_id, gBaseLang;
1057
1058 xErrLoc:= 300;
1059 COMMIT;
1060
1061 EXCEPTION
1062 WHEN OTHERS THEN
1063 ROLLBACK;
1064 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateCategory-'||
1065 xErrLoc||' '||SQLERRM);
1066 raise ICX_POR_EXT_UTL.gException;
1067 END updateCategory;
1068
1069 -- Translate a category
1070 PROCEDURE translateCategory(p_category_id IN NUMBER,
1071 p_description IN VARCHAR2,
1072 p_language IN VARCHAR2)
1073 IS
1074 xErrLoc PLS_INTEGER:= 100;
1075 xExist PLS_INTEGER:= 0;
1076
1077 BEGIN
1078 xErrLoc:= 50;
1079 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1080 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1081 'translateCategory(p_category_id: ' || p_category_id ||
1082 ', p_description: ' || p_description ||
1083 ', p_language: ' || p_language || ')');
1084 END IF;
1085
1086 xErrLoc:= 100;
1087 BEGIN
1088 SELECT 1
1089 INTO xExist
1090 FROM dual
1091 WHERE EXISTS (SELECT 'installed language'
1092 FROM fnd_languages
1093 WHERE installed_flag = 'I'
1094 AND language_code = p_language);
1095 EXCEPTION
1096 WHEN NO_DATA_FOUND THEN
1097 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1098 'Not an installed language: ' || p_language);
1099 RETURN;
1100 END;
1101
1102 xErrLoc:= 120;
1103 EXECUTE IMMEDIATE
1104 'INSERT INTO imtl_categories_tl( ' ||
1105 'category_id, ' ||
1106 'description, ' ||
1107 'language, ' ||
1108 'source_lang, ' ||
1109 'last_update_date) ' ||
1110 'SELECT:category_id, ' ||
1111 ':description, ' ||
1112 ':language, ' ||
1113 ':language, ' ||
1114 'SYSDATE ' ||
1115 'FROM dual ' ||
1116 'WHERE NOT EXISTS (SELECT 1 ' ||
1117 'FROM imtl_categories_tl ' ||
1118 'WHERE category_id =:category_id ' ||
1119 'AND language =:language) '
1120 USING p_category_id, p_description, p_language,
1121 p_language, p_category_id, p_language;
1122
1123 xErrLoc:= 200;
1124 EXECUTE IMMEDIATE
1125 'UPDATE imtl_categories_tl ' ||
1126 'SET description =:description, ' ||
1127 'last_update_date = SYSDATE ' ||
1128 'WHERE category_id =:category_id ' ||
1129 'AND language =:language '
1130 USING p_description, p_category_id, p_language;
1131
1132 xErrLoc:= 300;
1133 COMMIT;
1134
1135 EXCEPTION
1136 when others then
1137 ROLLBACK;
1138 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateCategory-'||
1139 xErrLoc||' '||SQLERRM);
1140 raise ICX_POR_EXT_UTL.gException;
1141 END translateCategory;
1142
1143 -- Create a template header
1144 PROCEDURE createTemplateHeader(p_org_id IN NUMBER,
1145 p_express_name IN VARCHAR2,
1146 p_type_lookup_code IN VARCHAR2,
1147 p_inactive_date IN DATE)
1148 IS
1149 xErrLoc PLS_INTEGER:= 100;
1150
1151 BEGIN
1152 xErrLoc:= 50;
1153 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1154 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1155 'createTemplateHeader(p_org_id: ' || p_org_id ||
1156 ', p_express_name: ' || p_express_name ||
1157 ', p_type_lookup_code: ' || p_type_lookup_code ||
1158 ', p_inactive_date: ' || p_inactive_date || ')');
1159 END IF;
1160
1161 xErrLoc:= 100;
1162 EXECUTE IMMEDIATE
1163 'INSERT INTO ipo_reqexpress_headers_all( ' ||
1164 'org_id, ' ||
1165 'express_name, ' ||
1166 'type_lookup_code, ' ||
1167 'inactive_date, ' ||
1168 'last_update_date) ' ||
1169 'VALUES( ' ||
1170 ':org_id, ' ||
1171 ':express_name, ' ||
1172 ':type_lookup_code, ' ||
1173 ':inactive_date, ' ||
1174 'SYSDATE) '
1175 USING p_org_id, p_express_name,
1176 p_type_lookup_code, p_inactive_date;
1177
1178 xErrLoc:= 400;
1179 COMMIT;
1180
1181 EXCEPTION
1182 WHEN OTHERS THEN
1183 ROLLBACK;
1184 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateHeader-'||
1185 xErrLoc||' '||SQLERRM);
1186 raise ICX_POR_EXT_UTL.gException;
1187 END createTemplateHeader;
1188
1189 -- Update a template header
1190 PROCEDURE updateTemplateHeader(p_org_id IN NUMBER,
1191 p_express_name IN VARCHAR2,
1192 p_inactive_date IN DATE)
1193 IS
1194 xErrLoc PLS_INTEGER:= 100;
1195
1196 BEGIN
1197 xErrLoc:= 50;
1198 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1199 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1200 'updateTemplateHeader(p_org_id: ' || p_org_id ||
1201 ', p_express_name: ' || p_express_name ||
1202 ', p_inactive_date: ' || p_inactive_date || ')');
1203 END IF;
1204
1205 xErrLoc:= 100;
1206 EXECUTE IMMEDIATE
1207 'UPDATE ipo_reqexpress_headers_all ' ||
1208 'SET inactive_date =:inactive_date, ' ||
1209 'last_update_date = SYSDATE ' ||
1210 'WHERE org_id =:org_id ' ||
1211 'AND express_name =:express_name '
1212 USING p_inactive_date, p_org_id, p_express_name;
1213
1214 xErrLoc:= 200;
1215 COMMIT;
1216
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 ROLLBACK;
1220 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateHeader-'||
1221 xErrLoc||' '||SQLERRM);
1222 raise ICX_POR_EXT_UTL.gException;
1223 END updateTemplateHeader;
1224
1225 --------------------------------------------------------------
1226 -- Classification Test Result Checking --
1227 --------------------------------------------------------------
1228 FUNCTION existCategory(p_category_key IN VARCHAR2,
1229 p_category_name IN VARCHAR2,
1230 p_category_type IN NUMBER)
1231 RETURN BOOLEAN
1232 IS
1233 xErrLoc PLS_INTEGER;
1234 xResult PLS_INTEGER;
1235 BEGIN
1236 xErrLoc:= 100;
1237 SELECT 1
1238 INTO xResult
1239 FROM icx_cat_categories_tl
1240 WHERE key = p_category_key
1241 AND category_name = p_category_name
1242 AND ROWNUM = 1;
1243 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1244 'Category[Key: ' || p_category_key || ', Name: ' ||
1245 p_category_name || '] exists in ICX_CAT_CATEGORIES_TL');
1246
1247 xErrLoc:= 140;
1248 SELECT 2
1249 INTO xResult
1250 FROM icx_por_category_data_sources
1251 WHERE category_key = p_category_key
1252 AND external_source_key = p_category_key
1253 AND ROWNUM = 1;
1254 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1255 'Category[Key: ' || p_category_key || ', Name: ' ||
1256 p_category_name || '] exists in ICX_POR_CATEGORY_DATA_SOURCES');
1257
1258 xErrLoc:= 180;
1259 IF p_category_type = ICX_POR_EXT_CLASS.CATEGORY_TYPE THEN
1260 SELECT 3
1261 INTO xResult
1262 FROM icx_por_category_order_map
1263 WHERE external_source_key = p_category_key
1264 AND ROWNUM = 1;
1265 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1266 'Category[Key: ' || p_category_key || ', Name: ' ||
1267 p_category_name || '] exists in ICX_POR_CATEGORY_ORDER_MAP');
1268 END IF;
1269
1270 xErrLoc:= 200;
1271 RETURN TRUE;
1272 EXCEPTION
1273 when NO_DATA_FOUND then
1274 xResult:= 0;
1275 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1276 'Category[Key: ' || p_category_key || ', Name: ' ||
1277 p_category_name || '] does not exist');
1278 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1279 'Category[Key: ' || p_category_key || ', Name: ' ||
1280 p_category_name || '] does not exist');
1281 RETURN FALSE;
1282 WHEN OTHERS THEN
1283 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategory-'||
1284 xErrLoc||' '||SQLERRM);
1285 raise ICX_POR_EXT_UTL.gException;
1286 END existCategory;
1287
1288 FUNCTION notExistCategory(p_category_key IN VARCHAR2)
1289 RETURN BOOLEAN
1290 IS
1291 xErrLoc PLS_INTEGER;
1292 xResult PLS_INTEGER;
1293 BEGIN
1294 xErrLoc:= 100;
1295 SELECT 0
1296 INTO xResult
1297 FROM icx_cat_categories_tl
1298 WHERE key = p_category_key
1299 AND ROWNUM = 1;
1300 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1301 'Category[Key: ' || p_category_key || '] exists in ICX_CAT_CATEGORIES_TL');
1302 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1303 'Category[Key: ' || p_category_key || '] exist');
1304
1305 xErrLoc:= 200;
1306 RETURN FALSE;
1307 EXCEPTION
1308 when NO_DATA_FOUND then
1309 xResult:= 1;
1310 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1311 'Category[Key: ' || p_category_key || '] does not exists');
1312 RETURN TRUE;
1313 WHEN OTHERS THEN
1314 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCategory-'||
1315 xErrLoc||' '||SQLERRM);
1316 raise ICX_POR_EXT_UTL.gException;
1317 END notExistCategory;
1318
1319 FUNCTION existCategoryTL(p_category_key IN VARCHAR2,
1320 p_category_name IN VARCHAR2,
1321 p_language IN VARCHAR2)
1322 RETURN BOOLEAN
1323 IS
1324 xErrLoc PLS_INTEGER;
1325 xResult PLS_INTEGER;
1326 BEGIN
1327 xErrLoc:= 100;
1328 SELECT 1
1329 INTO xResult
1330 FROM icx_cat_categories_tl
1331 WHERE key = p_category_key
1332 AND category_name = p_category_name
1333 AND language = p_language
1334 AND ROWNUM = 1;
1335 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1336 'Category[Key: ' || p_category_key || ', Name: ' ||
1337 p_category_name || ', Language: ' || p_language ||
1338 '] exists in ICX_CAT_CATEGORIES_TL');
1339
1340 xErrLoc:= 200;
1341 RETURN TRUE;
1342 EXCEPTION
1343 when NO_DATA_FOUND then
1344 xResult:= 0;
1345 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1346 'Category[Key: ' || p_category_key || ', Name: ' ||
1347 p_category_name || ', Language: ' || p_language ||
1348 '] does not exist');
1349 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1350 'Category[Key: ' || p_category_key || ', Name: ' ||
1351 p_category_name || ', Language: ' || p_language ||
1352 '] does not exist');
1353 RETURN FALSE;
1354 WHEN OTHERS THEN
1355 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategoryTL-'||
1356 xErrLoc||' '||SQLERRM);
1357 raise ICX_POR_EXT_UTL.gException;
1358 END existCategoryTL;
1359
1360 --------------------------------------------------------------
1361 -- Item Test Utilities --
1362 --------------------------------------------------------------
1363 -- Create a sets of book
1364 PROCEDURE createGSB(p_set_of_books_id IN NUMBER,
1365 p_currency_code IN VARCHAR2)
1366 IS
1367 xErrLoc PLS_INTEGER:= 100;
1368
1369 BEGIN
1370 xErrLoc:= 50;
1371 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1372 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1373 'createGSB(p_set_of_books_id: ' || p_set_of_books_id ||
1374 ', p_currency_code: ' || p_currency_code || ')');
1375 END IF;
1376
1377 xErrLoc:= 100;
1378 EXECUTE IMMEDIATE
1379 'INSERT INTO igl_sets_of_books( ' ||
1380 'set_of_books_id, ' ||
1381 'currency_code) ' ||
1382 'VALUES( ' ||
1383 ':set_of_books_id, ' ||
1384 ':currency_code) '
1385 USING p_set_of_books_id, p_currency_code;
1386
1387 xErrLoc:= 400;
1388 COMMIT;
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 ROLLBACK;
1392 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGSB-'||
1393 xErrLoc||' '||SQLERRM);
1394 raise ICX_POR_EXT_UTL.gException;
1395 END createGSB;
1396
1397 -- Create financial system parameters
1398 PROCEDURE createFSP(p_org_id IN NUMBER,
1399 p_inventory_organization_id IN NUMBER,
1400 p_set_of_books_id IN NUMBER)
1401 IS
1402 xErrLoc PLS_INTEGER:= 100;
1403
1404 BEGIN
1405 xErrLoc:= 50;
1406 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1407 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1408 'createFSP(p_org_id: ' || p_org_id ||
1409 ', p_inventory_organization_id: ' || p_inventory_organization_id ||
1410 ', p_set_of_books_id: ' || p_set_of_books_id || ')');
1411 END IF;
1412
1413 xErrLoc:= 100;
1414 EXECUTE IMMEDIATE
1415 'INSERT INTO ifinancials_system_params_all( ' ||
1416 'org_id, ' ||
1417 'inventory_organization_id, ' ||
1418 'set_of_books_id) ' ||
1419 'VALUES( ' ||
1420 ':org_id, ' ||
1421 ':inventory_organization_id, ' ||
1422 ':set_of_books_id) '
1423 USING p_org_id, p_inventory_organization_id, p_set_of_books_id;
1424
1425 xErrLoc:= 200;
1426 EXECUTE IMMEDIATE
1427 'INSERT INTO ipo_system_parameters_all( ' ||
1428 'org_id, ' ||
1429 'default_rate_type, ' ||
1430 'last_update_date) ' || -- Bug# 2945205 : pcreddy
1431 'VALUES( ' ||
1432 ':org_id, ' ||
1433 '''Corporate'', ' ||
1434 'SYSDATE) ' -- Bug# 2945205 : pcreddy
1435 USING p_org_id;
1436
1437 xErrLoc:= 400;
1438 COMMIT;
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 ROLLBACK;
1442 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createFSP-'||
1443 xErrLoc||' '||SQLERRM);
1444 raise ICX_POR_EXT_UTL.gException;
1445 END createFSP;
1446
1447 -- Create an item
1448 PROCEDURE createItem(p_inventory_item_id IN NUMBER,
1449 p_organization_id IN NUMBER,
1450 p_concatenated_segments IN VARCHAR2,
1451 p_purchasing_enabled_flag IN VARCHAR2,
1452 p_outside_operation_flag IN VARCHAR2,
1453 p_internal_order_enabled_flag IN VARCHAR2,
1454 p_list_price_per_unit IN NUMBER,
1455 p_primary_uom_code IN VARCHAR2,
1456 p_replenish_to_order_flag IN VARCHAR2,
1457 p_base_item_id IN NUMBER,
1458 p_auto_created_config_flag IN VARCHAR2,
1459 p_unit_of_issue IN VARCHAR2,
1460 p_description IN VARCHAR2,
1461 p_category_id IN NUMBER)
1462 IS
1463 xErrLoc PLS_INTEGER:= 100;
1464
1465 BEGIN
1466 xErrLoc:= 50;
1467 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1468 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1469 'createItem(p_inventory_item_id: ' || p_inventory_item_id ||
1470 ', p_organization_id: ' || p_organization_id ||
1471 ', p_concatenated_segments: ' || p_concatenated_segments ||
1472 ', p_purchasing_enabled_flag: ' || p_purchasing_enabled_flag ||
1473 ', p_outside_operation_flag: ' || p_outside_operation_flag ||
1474 ', p_internal_order_enabled_flag: ' || p_internal_order_enabled_flag ||
1475 ', p_list_price_per_unit: ' || p_list_price_per_unit ||
1476 ', p_primary_uom_code: ' || p_primary_uom_code ||
1477 ', p_replenish_to_order_flag: ' || p_replenish_to_order_flag ||
1478 ', p_base_item_id: ' || p_base_item_id ||
1479 ', p_auto_created_config_flag: ' || p_auto_created_config_flag ||
1480 ', p_unit_of_issue: ' || p_unit_of_issue ||
1481 ', p_description: ' || p_description ||
1482 ', p_category_id: ' || p_category_id || ')');
1483 END IF;
1484
1485 xErrLoc:= 100;
1486 EXECUTE IMMEDIATE
1487 'INSERT INTO imtl_system_items_kfv( ' ||
1488 'inventory_item_id, ' ||
1489 'organization_id, ' ||
1490 'concatenated_segments, ' ||
1491 'purchasing_enabled_flag, ' ||
1492 'outside_operation_flag, ' ||
1493 'internal_order_enabled_flag, ' ||
1494 'list_price_per_unit, ' ||
1495 'primary_uom_code, ' ||
1496 'replenish_to_order_flag, ' ||
1497 'base_item_id, ' ||
1498 'auto_created_config_flag, ' ||
1499 'unit_of_issue, ' ||
1500 'last_update_date) ' ||
1501 'VALUES( ' ||
1502 ':inventory_item_id, ' ||
1503 ':organization_id, ' ||
1504 ':concatenated_segments, ' ||
1505 ':purchasing_enabled_flag, ' ||
1506 ':outside_operation_flag, ' ||
1507 ':internal_order_enabled_flag, ' ||
1508 ':list_price_per_unit, ' ||
1509 ':primary_uom_code, ' ||
1510 ':replenish_to_order_flag, ' ||
1511 ':base_item_id, ' ||
1512 ':auto_created_config_flag, ' ||
1513 ':unit_of_issue, ' ||
1514 'SYSDATE) '
1515 USING p_inventory_item_id, p_organization_id,
1516 p_concatenated_segments, p_purchasing_enabled_flag,
1517 p_outside_operation_flag, p_internal_order_enabled_flag,
1518 p_list_price_per_unit, p_primary_uom_code,
1519 p_replenish_to_order_flag, p_base_item_id,
1520 p_auto_created_config_flag, p_unit_of_issue;
1521
1522 xErrLoc:= 200;
1523 EXECUTE IMMEDIATE
1524 'INSERT INTO imtl_system_items_tl( ' ||
1525 'inventory_item_id, ' ||
1526 'organization_id, ' ||
1527 'description, ' ||
1528 'language, ' ||
1529 'source_lang, ' ||
1530 'last_update_date) ' ||
1531 'VALUES( ' ||
1532 ':inventory_item_id, ' ||
1533 ':organization_id, ' ||
1534 ':description, ' ||
1535 ':language, ' ||
1536 ':language, ' ||
1537 'SYSDATE) '
1538 USING p_inventory_item_id, p_organization_id,
1539 p_description, gBaseLang, gBaseLang;
1540
1541 xErrLoc:= 300;
1542 EXECUTE IMMEDIATE
1543 'INSERT INTO imtl_item_categories( ' ||
1544 'inventory_item_id, ' ||
1545 'organization_id, ' ||
1546 'category_id, ' ||
1547 'category_set_id, ' ||
1548 'last_update_date) ' ||
1549 'VALUES( ' ||
1550 ':inventory_item_id, ' ||
1551 ':organization_id, ' ||
1552 ':category_id, ' ||
1553 ':category_set_id, ' ||
1554 'SYSDATE) '
1555 USING p_inventory_item_id, p_organization_id,
1556 p_category_id, gCategorySetId;
1557
1558 xErrLoc:= 400;
1559 COMMIT;
1560
1561 EXCEPTION
1562 WHEN OTHERS THEN
1563 ROLLBACK;
1564 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createItem-'||
1565 xErrLoc||' '||SQLERRM);
1566 raise ICX_POR_EXT_UTL.gException;
1567 END createItem;
1568
1569 -- Update an item
1570 PROCEDURE updateItem(p_inventory_item_id IN NUMBER,
1571 p_organization_id IN NUMBER,
1572 p_concatenated_segments IN VARCHAR2,
1573 p_purchasing_enabled_flag IN VARCHAR2,
1574 p_outside_operation_flag IN VARCHAR2,
1575 p_internal_order_enabled_flag IN VARCHAR2,
1576 p_list_price_per_unit IN NUMBER,
1577 p_primary_uom_code IN VARCHAR2,
1578 p_replenish_to_order_flag IN VARCHAR2,
1579 p_base_item_id IN NUMBER,
1580 p_auto_created_config_flag IN VARCHAR2,
1581 p_unit_of_issue IN VARCHAR2,
1582 p_description IN VARCHAR2,
1583 p_category_id IN NUMBER)
1584 IS
1585 xErrLoc PLS_INTEGER:= 100;
1586
1587 BEGIN
1588 xErrLoc:= 50;
1589 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1590 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1591 'updateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1592 ', p_organization_id: ' || p_organization_id ||
1593 ', p_concatenated_segments: ' || p_concatenated_segments ||
1594 ', p_purchasing_enabled_flag: ' || p_purchasing_enabled_flag ||
1595 ', p_outside_operation_flag: ' || p_outside_operation_flag ||
1596 ', p_internal_order_enabled_flag: ' || p_internal_order_enabled_flag ||
1597 ', p_list_price_per_unit: ' || p_list_price_per_unit ||
1598 ', p_primary_uom_code: ' || p_primary_uom_code ||
1599 ', p_replenish_to_order_flag: ' || p_replenish_to_order_flag ||
1600 ', p_base_item_id: ' || p_base_item_id ||
1601 ', p_auto_created_config_flag: ' || p_auto_created_config_flag ||
1602 ', p_unit_of_issue: ' || p_unit_of_issue ||
1603 ', p_description: ' || p_description ||
1604 ', p_category_id: ' || p_category_id || ')');
1605 END IF;
1606
1607 xErrLoc:= 100;
1608 EXECUTE IMMEDIATE
1609 'UPDATE imtl_system_items_kfv SET ' ||
1610 'concatenated_segments = DECODE(:concatenated_segments, '||
1611 ':miss_char, concatenated_segments,:concatenated_segments), '||
1612 'purchasing_enabled_flag = DECODE(:purchasing_enabled_flag, '||
1613 ':miss_char, purchasing_enabled_flag,:purchasing_enabled_flag), '||
1614 'outside_operation_flag = DECODE(:outside_operation_flag, ' ||
1615 ':miss_char, outside_operation_flag,:outside_operation_flag), '||
1616 'internal_order_enabled_flag = DECODE(:internal_order_enabled_flag, '||
1617 ':miss_char, internal_order_enabled_flag,:internal_order_enabled_flag), '||
1618 'list_price_per_unit = DECODE(:list_price_per_unit, '||
1619 ':miss_num, list_price_per_unit,:list_price_per_unit), '||
1620 'primary_uom_code = DECODE(:primary_uom_code, '||
1621 ':miss_char, primary_uom_code,:primary_uom_code), '||
1622 'replenish_to_order_flag = DECODE(:replenish_to_order_flag, '||
1623 ':miss_char, replenish_to_order_flag,:replenish_to_order_flag), '||
1624 'base_item_id = DECODE(:base_item_id, '||
1625 ':miss_num, base_item_id,:base_item_id), '||
1626 'auto_created_config_flag = DECODE(:auto_created_config_flag, '||
1627 ':miss_char, auto_created_config_flag,:auto_created_config_flag), '||
1628 'unit_of_issue = DECODE(:unit_of_issue, '||
1629 ':miss_char, unit_of_issue,:unit_of_issue), '||
1630 'last_update_date = SYSDATE ' ||
1631 'WHERE inventory_item_id =:inventory_item_id ' ||
1632 'AND organization_id =:organization_id '
1633 USING p_concatenated_segments, FND_API.G_MISS_CHAR, p_concatenated_segments,
1634 p_purchasing_enabled_flag, FND_API.G_MISS_CHAR, p_purchasing_enabled_flag,
1635 p_outside_operation_flag, FND_API.G_MISS_CHAR, p_outside_operation_flag,
1636 p_internal_order_enabled_flag, FND_API.G_MISS_CHAR, p_internal_order_enabled_flag,
1637 p_list_price_per_unit, FND_API.G_MISS_NUM, p_list_price_per_unit,
1638 p_primary_uom_code, FND_API.G_MISS_CHAR, p_primary_uom_code,
1639 p_replenish_to_order_flag, FND_API.G_MISS_CHAR, p_replenish_to_order_flag,
1640 p_base_item_id, FND_API.G_MISS_NUM, p_base_item_id,
1641 p_auto_created_config_flag, FND_API.G_MISS_CHAR, p_auto_created_config_flag,
1642 p_unit_of_issue, FND_API.G_MISS_CHAR, p_unit_of_issue,
1643 p_inventory_item_id, p_organization_id;
1644
1645 xErrLoc:= 200;
1646 EXECUTE IMMEDIATE
1647 'UPDATE imtl_system_items_tl SET ' ||
1648 'description = DECODE(:description, '||
1649 ':miss_char, description,:description), '||
1650 'last_update_date = SYSDATE ' ||
1651 'WHERE inventory_item_id =:inventory_item_id ' ||
1652 'AND organization_id =:organization_id '
1653 USING p_description, FND_API.G_MISS_CHAR, p_description,
1654 p_inventory_item_id, p_organization_id;
1655
1656 xErrLoc:= 300;
1657 EXECUTE IMMEDIATE
1658 'UPDATE imtl_item_categories SET ' ||
1659 'category_id = DECODE(:category_id, '||
1660 ':miss_num, category_id,:category_id), '||
1661 'last_update_date = SYSDATE ' ||
1662 'WHERE inventory_item_id =:inventory_item_id ' ||
1663 'AND organization_id =:organization_id '
1664 USING p_category_id, FND_API.G_MISS_NUM, p_category_id,
1665 p_inventory_item_id, p_organization_id;
1666
1667 xErrLoc:= 400;
1668 COMMIT;
1669
1670 EXCEPTION
1671 WHEN OTHERS THEN
1672 ROLLBACK;
1673 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateItem-'||
1674 xErrLoc||' '||SQLERRM);
1675 raise ICX_POR_EXT_UTL.gException;
1676 END updateItem;
1677
1678 -- Translate an item
1679 PROCEDURE translateItem(p_inventory_item_id IN NUMBER,
1680 p_organization_id IN NUMBER,
1681 p_description IN VARCHAR2,
1682 p_language IN VARCHAR2)
1683 IS
1684 xErrLoc PLS_INTEGER:= 100;
1685 xExist PLS_INTEGER:= 0;
1686
1687 BEGIN
1688 xErrLoc:= 50;
1689 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1690 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1691 'translateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1692 ', p_organization_id: ' || p_organization_id ||
1693 ', p_description: ' || p_description ||
1694 ', p_language: ' || p_language || ')');
1695 END IF;
1696
1697 xErrLoc:= 100;
1698 BEGIN
1699 SELECT 1
1700 INTO xExist
1701 FROM dual
1702 WHERE EXISTS (SELECT 'installed language'
1703 FROM fnd_languages
1704 WHERE installed_flag = 'I'
1705 AND language_code = p_language);
1706 EXCEPTION
1707 WHEN NO_DATA_FOUND THEN
1708 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1709 'Not an installed language: ' || p_language);
1710 RETURN;
1711 END;
1712
1713 xErrLoc:= 120;
1714 EXECUTE IMMEDIATE
1715 'INSERT INTO imtl_system_items_tl( ' ||
1716 'inventory_item_id, ' ||
1717 'organization_id, ' ||
1718 'description, ' ||
1719 'language, ' ||
1720 'source_lang, ' ||
1721 'last_update_date) ' ||
1722 'SELECT:inventory_item_id, ' ||
1723 ':organization_id, ' ||
1724 ':description, ' ||
1725 ':language, ' ||
1726 ':language, ' ||
1727 'SYSDATE ' ||
1728 'FROM dual ' ||
1729 'WHERE NOT EXISTS (SELECT 1 ' ||
1730 'FROM imtl_system_items_tl ' ||
1731 'WHERE inventory_item_id =:inventory_item_id ' ||
1732 'AND organization_id =:organization_id ' ||
1733 'AND language =:language) '
1734 USING p_inventory_item_id, p_organization_id,
1735 p_description, p_language, p_language,
1736 p_inventory_item_id, p_organization_id,
1737 p_language;
1738
1739 xErrLoc:= 200;
1740 EXECUTE IMMEDIATE
1741 'UPDATE imtl_system_items_tl ' ||
1742 'SET description =:description, ' ||
1743 'last_update_date = SYSDATE ' ||
1744 'WHERE inventory_item_id =:inventory_item_id ' ||
1745 'AND organization_id =:organization_id ' ||
1746 'AND language =:language '
1747 USING p_description, p_inventory_item_id,
1748 p_organization_id, p_language;
1749
1750 xErrLoc:= 300;
1751 COMMIT;
1752
1753 EXCEPTION
1754 when others then
1755 ROLLBACK;
1756 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateItem-'||
1757 xErrLoc||' '||SQLERRM);
1758 raise ICX_POR_EXT_UTL.gException;
1759 END translateItem;
1760
1761 -- Delete an item
1762 PROCEDURE deleteItem(p_inventory_item_id IN NUMBER,
1763 p_organization_id IN NUMBER)
1764 IS
1765 xErrLoc PLS_INTEGER:= 100;
1766
1767 BEGIN
1768 xErrLoc:= 50;
1769 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1770 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1771 'deleteItem(p_inventory_item_id: ' || p_inventory_item_id ||
1772 ', p_organization_id: ' || p_organization_id || ')');
1773 END IF;
1774
1775 xErrLoc:= 100;
1776 EXECUTE IMMEDIATE
1777 'DELETE FROM imtl_system_items_kfv ' ||
1778 'WHERE inventory_item_id =:inventory_item_id ' ||
1779 'AND organization_id =:organization_id '
1780 USING p_inventory_item_id, p_organization_id;
1781
1782 xErrLoc:= 200;
1783 EXECUTE IMMEDIATE
1784 'DELETE FROM imtl_system_items_tl ' ||
1785 'WHERE inventory_item_id =:inventory_item_id ' ||
1786 'AND organization_id =:organization_id '
1787 USING p_inventory_item_id, p_organization_id;
1788
1789 xErrLoc:= 300;
1790 EXECUTE IMMEDIATE
1791 'DELETE FROM imtl_item_categories ' ||
1792 'WHERE inventory_item_id =:inventory_item_id ' ||
1793 'AND organization_id =:organization_id '
1794 USING p_inventory_item_id, p_organization_id;
1795
1796 xErrLoc:= 400;
1797 COMMIT;
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 ROLLBACK;
1802 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.deleteItem-'||
1803 xErrLoc||' '||SQLERRM);
1804 raise ICX_POR_EXT_UTL.gException;
1805 END deleteItem;
1806
1807 -- Create a vendor
1808 PROCEDURE createVendor(p_vendor_id IN NUMBER,
1809 p_vendor_name IN VARCHAR2)
1810 IS
1811 xErrLoc PLS_INTEGER:= 100;
1812
1813 BEGIN
1814 xErrLoc:= 50;
1815 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1816 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1817 'createVendor(p_vendor_id: ' || p_vendor_id ||
1818 ', p_vendor_name: ' || p_vendor_name || ')');
1819 END IF;
1820
1821 xErrLoc:= 100;
1822 EXECUTE IMMEDIATE
1823 'INSERT INTO ipo_vendors( ' ||
1824 'vendor_id, ' ||
1825 'vendor_name, ' ||
1826 'last_update_date) ' ||
1827 'VALUES( ' ||
1828 ':vendor_id, ' ||
1829 ':vendor_name, ' ||
1830 'SYSDATE) '
1831 USING p_vendor_id, p_vendor_name;
1832
1833 xErrLoc:= 400;
1834 COMMIT;
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 ROLLBACK;
1838 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendor-'||
1839 xErrLoc||' '||SQLERRM);
1840 raise ICX_POR_EXT_UTL.gException;
1841 END createVendor;
1842
1843 -- Update a vendor
1844 PROCEDURE updateVendor(p_vendor_id IN NUMBER,
1845 p_vendor_name IN VARCHAR2)
1846 IS
1847 xErrLoc PLS_INTEGER:= 100;
1848
1849 BEGIN
1850 xErrLoc:= 50;
1851 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1852 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1853 'updateVendor(p_vendor_id: ' || p_vendor_id ||
1854 ', p_vendor_name: ' || p_vendor_name || ')');
1855 END IF;
1856
1857 xErrLoc:= 100;
1858 EXECUTE IMMEDIATE
1859 'UPDATE ipo_vendors ' ||
1860 'SET vendor_name =:vendor_name, ' ||
1861 'last_update_date = SYSDATE ' ||
1862 'WHERE vendor_id =:vendor_id '
1863 USING p_vendor_name, p_vendor_id;
1864
1865 xErrLoc:= 200;
1866 COMMIT;
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 ROLLBACK;
1871 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendor-'||
1872 xErrLoc||' '||SQLERRM);
1873 raise ICX_POR_EXT_UTL.gException;
1874 END updateVendor;
1875
1876 -- Create a vendor site
1877 PROCEDURE createVendorSite(p_vendor_site_id IN NUMBER,
1878 p_vendor_site_code IN VARCHAR2,
1879 p_purchasing_site_flag IN VARCHAR2)
1880 IS
1881 xErrLoc PLS_INTEGER:= 100;
1882
1883 BEGIN
1884 xErrLoc:= 50;
1885 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1886 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1887 'createVendorSite(p_vendor_site_id: ' || p_vendor_site_id ||
1888 ', p_vendor_site_code: ' || p_vendor_site_code ||
1889 ', p_purchasing_site_flag: ' || p_purchasing_site_flag || ')');
1890 END IF;
1891
1892 xErrLoc:= 100;
1893 EXECUTE IMMEDIATE
1894 'INSERT INTO ipo_vendor_sites_all( ' ||
1895 'vendor_site_id, ' ||
1896 'vendor_site_code, ' ||
1897 'purchasing_site_flag, ' ||
1898 'inactive_date, ' ||
1899 'last_update_date) ' ||
1900 'VALUES( ' ||
1901 ':vendor_site_id, ' ||
1902 ':vendor_site_code, ' ||
1903 ':purchasing_site_flag, ' ||
1904 'NULL, ' ||
1905 'SYSDATE) '
1906 USING p_vendor_site_id, p_vendor_site_code, p_purchasing_site_flag;
1907
1908 xErrLoc:= 400;
1909 COMMIT;
1910 EXCEPTION
1911 WHEN OTHERS THEN
1912 ROLLBACK;
1913 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendorSite-'||
1914 xErrLoc||' '||SQLERRM);
1915 raise ICX_POR_EXT_UTL.gException;
1916 END createVendorSite;
1917
1918 -- Update a vendor site
1919 PROCEDURE updateVendorSite(p_vendor_site_id IN NUMBER,
1920 p_purchasing_site_flag IN VARCHAR2,
1921 p_inactive_date IN DATE)
1922 IS
1923 xErrLoc PLS_INTEGER:= 100;
1924
1925 BEGIN
1926 xErrLoc:= 50;
1927 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1928 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1929 'createVendor(p_vendor_site_id: ' || p_vendor_site_id ||
1930 ', p_purchasing_site_flag: ' || p_purchasing_site_flag ||
1931 ', p_inactive_date: ' || p_inactive_date || ')');
1932 END IF;
1933
1934 xErrLoc:= 100;
1935 EXECUTE IMMEDIATE
1936 'UPDATE ipo_vendor_sites_all SET ' ||
1937 'purchasing_site_flag = DECODE(:purchasing_site_flag, '||
1938 ':miss_char, purchasing_site_flag,:purchasing_site_flag), '||
1939 'inactive_date = DECODE(:inactive_date, '||
1940 ':miss_date, inactive_date,:inactive_date), '||
1941 'last_update_date = SYSDATE ' ||
1942 'WHERE vendor_site_id =:vendor_site_id '
1943 USING p_purchasing_site_flag, FND_API.G_MISS_CHAR, p_purchasing_site_flag,
1944 p_inactive_date, FND_API.G_MISS_DATE, p_inactive_date,
1945 p_vendor_site_id;
1946
1947 xErrLoc:= 400;
1948 COMMIT;
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 ROLLBACK;
1952 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendorSite-'||
1953 xErrLoc||' '||SQLERRM);
1954 raise ICX_POR_EXT_UTL.gException;
1955 END updateVendorSite;
1956
1957 -- Create an ASL
1958 PROCEDURE createASL(p_asl_id IN NUMBER,
1959 p_asl_status_id IN NUMBER,
1960 p_owning_organization_id IN NUMBER,
1961 p_item_id IN NUMBER,
1962 p_category_id IN NUMBER,
1963 p_vendor_id IN NUMBER,
1964 p_vendor_site_id IN NUMBER,
1965 p_primary_vendor_item IN VARCHAR2,
1966 p_disable_flag IN VARCHAR2,
1967 p_allow_action_flag IN VARCHAR2,
1968 p_purchasing_unit_of_measure IN VARCHAR2)
1969 IS
1970 xErrLoc PLS_INTEGER:= 100;
1971
1972 BEGIN
1973 xErrLoc:= 50;
1974 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1975 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1976 'createASL(p_asl_id: ' || p_asl_id ||
1977 ', p_asl_status_id: ' || p_asl_status_id ||
1978 ', p_owning_organization_id: ' || p_owning_organization_id ||
1979 ', p_item_id: ' || p_item_id ||
1980 ', p_category_id: ' || p_category_id ||
1981 ', p_vendor_id: ' || p_vendor_id ||
1982 ', p_vendor_site_id: ' || p_vendor_site_id ||
1983 ', p_primary_vendor_item: ' || p_primary_vendor_item ||
1984 ', p_disable_flag: ' || p_disable_flag ||
1985 ', p_allow_action_flag: ' || p_allow_action_flag ||
1986 ', p_purchasing_unit_of_measure: ' || p_purchasing_unit_of_measure || ')');
1987 END IF;
1988
1989 xErrLoc:= 100;
1990 EXECUTE IMMEDIATE
1991 'INSERT INTO ipo_approved_supplier_list( ' ||
1992 'asl_id, ' ||
1993 'asl_status_id, ' ||
1994 'owning_organization_id, ' ||
1995 'item_id, ' ||
1996 'category_id, ' ||
1997 'vendor_id, ' ||
1998 'vendor_site_id, ' ||
1999 'primary_vendor_item, ' ||
2000 'disable_flag, ' ||
2001 'creation_date, ' ||
2002 'last_update_date) ' ||
2003 'VALUES( ' ||
2004 ':asl_id, ' ||
2005 ':asl_status_id, ' ||
2006 ':owning_organization_id, ' ||
2007 ':item_id, ' ||
2008 ':category_id, ' ||
2009 ':vendor_id, ' ||
2010 ':vendor_site_id, ' ||
2011 ':primary_vendor_item, ' ||
2012 ':disable_flag, ' ||
2013 'SYSDATE, ' ||
2014 'SYSDATE) '
2015 USING p_asl_id, p_asl_status_id,
2016 p_owning_organization_id, p_item_id,
2017 p_category_id, p_vendor_id, p_vendor_site_id,
2018 p_primary_vendor_item, p_disable_flag;
2019
2020 xErrLoc:= 200;
2021 EXECUTE IMMEDIATE
2022 'INSERT INTO ipo_asl_status_rules( ' ||
2023 'status_id, ' ||
2024 'business_rule, ' ||
2025 'allow_action_flag, ' ||
2026 'last_update_date) ' ||
2027 'VALUES( ' ||
2028 ':status_id, ' ||
2029 '''2_SOURCING'', ' ||
2030 ':allow_action_flag, ' ||
2031 'SYSDATE) '
2032 USING p_asl_status_id, p_allow_action_flag;
2033
2034 xErrLoc:= 300;
2035 EXECUTE IMMEDIATE
2036 'INSERT INTO ipo_asl_attributes( ' ||
2037 'asl_id, ' ||
2038 'purchasing_unit_of_measure, ' ||
2039 'last_update_date) ' ||
2040 'VALUES( ' ||
2041 ':asl_id, ' ||
2042 ':purchasing_unit_of_measure, ' ||
2043 'SYSDATE) '
2044 USING p_asl_id, p_purchasing_unit_of_measure;
2045
2046 xErrLoc:= 400;
2047 COMMIT;
2048
2049 EXCEPTION
2050 WHEN OTHERS THEN
2051 ROLLBACK;
2052 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createASL-'||
2053 xErrLoc||' '||SQLERRM);
2054 raise ICX_POR_EXT_UTL.gException;
2055 END createASL;
2056
2057 -- Update an ASL
2058 PROCEDURE updateASL(p_asl_id IN NUMBER,
2059 p_asl_status_id IN NUMBER,
2060 p_vendor_site_id IN NUMBER,
2061 p_primary_vendor_item IN VARCHAR2,
2062 p_disable_flag IN VARCHAR2,
2063 p_allow_action_flag IN VARCHAR2,
2064 p_purchasing_unit_of_measure IN VARCHAR2)
2065 IS
2066 xErrLoc PLS_INTEGER:= 100;
2067
2068 BEGIN
2069 xErrLoc:= 50;
2070 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2071 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2072 'updateASL(p_asl_id: ' || p_asl_id ||
2073 ', p_asl_status_id: ' || p_asl_status_id ||
2074 ', p_vendor_site_id: ' || p_vendor_site_id ||
2075 ', p_primary_vendor_item: ' || p_primary_vendor_item ||
2076 ', p_disable_flag: ' || p_disable_flag ||
2077 ', p_allow_action_flag: ' || p_allow_action_flag ||
2078 ', p_purchasing_unit_of_measure: ' || p_purchasing_unit_of_measure || ')');
2079 END IF;
2080
2081 xErrLoc:= 100;
2082 EXECUTE IMMEDIATE
2083 'UPDATE ipo_approved_supplier_list SET ' ||
2084 'vendor_site_id = DECODE(:vendor_site_id, '||
2085 ':miss_num, vendor_site_id,:vendor_site_id), '||
2086 'primary_vendor_item = DECODE(:primary_vendor_item, '||
2087 ':miss_char, primary_vendor_item,:primary_vendor_item), '||
2088 'disable_flag = DECODE(:disable_flag, '||
2089 ':miss_char, disable_flag,:disable_flag), '||
2090 'last_update_date = SYSDATE ' ||
2091 'WHERE asl_id =:asl_id '
2092 USING p_vendor_site_id, FND_API.G_MISS_NUM, p_vendor_site_id,
2093 p_primary_vendor_item, FND_API.G_MISS_CHAR, p_primary_vendor_item,
2094 p_disable_flag, FND_API.G_MISS_CHAR, p_disable_flag,
2095 p_asl_id;
2096
2097 xErrLoc:= 200;
2098 EXECUTE IMMEDIATE
2099 'UPDATE ipo_asl_status_rules SET ' ||
2100 'allow_action_flag = DECODE(:allow_action_flag, '||
2101 ':miss_char, allow_action_flag,:allow_action_flag), '||
2102 'last_update_date = SYSDATE ' ||
2103 'WHERE status_id =:status_id '
2104 USING p_allow_action_flag, FND_API.G_MISS_CHAR, p_allow_action_flag,
2105 p_asl_status_id;
2106
2107 xErrLoc:= 300;
2108 EXECUTE IMMEDIATE
2109 'UPDATE ipo_asl_attributes SET ' ||
2110 'purchasing_unit_of_measure = DECODE(:purchasing_unit_of_measure, '||
2111 ':miss_char, purchasing_unit_of_measure,:purchasing_unit_of_measure), '||
2112 'last_update_date = SYSDATE ' ||
2113 'WHERE asl_id =:asl_id '
2114 USING p_purchasing_unit_of_measure, FND_API.G_MISS_CHAR,
2115 p_purchasing_unit_of_measure, p_asl_id;
2116
2117 xErrLoc:= 400;
2118 COMMIT;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 ROLLBACK;
2123 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateASL-'||
2124 xErrLoc||' '||SQLERRM);
2125 raise ICX_POR_EXT_UTL.gException;
2126 END updateASL;
2127
2128 -- Create a template line
2129 PROCEDURE createTemplateLine(p_org_id IN NUMBER,
2130 p_express_name IN VARCHAR2,
2131 p_sequence_num IN NUMBER,
2132 p_source_type_code IN VARCHAR2,
2133 p_po_header_id IN NUMBER,
2134 p_po_line_id IN NUMBER,
2135 p_item_id IN NUMBER,
2136 p_category_id IN NUMBER,
2137 p_item_description IN VARCHAR2,
2138 p_unit_price IN NUMBER,
2139 p_unit_meas_lookup_code IN VARCHAR2,
2140 p_suggested_vendor_id IN NUMBER,
2141 p_suggested_vendor_site_id IN NUMBER,
2142 p_vendor_product_code IN VARCHAR2)
2143 IS
2144 xErrLoc PLS_INTEGER:= 100;
2145
2146 BEGIN
2147 xErrLoc:= 50;
2148 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2149 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2150 'createTemplateLine(p_org_id: ' || p_org_id ||
2151 ', p_express_name: ' || p_express_name ||
2152 ', p_sequence_num: ' || p_sequence_num ||
2153 ', p_source_type_code: ' || p_source_type_code ||
2154 ', p_po_header_id: ' || p_po_header_id ||
2155 ', p_po_line_id: ' || p_po_line_id ||
2156 ', p_item_id: ' || p_item_id ||
2157 ', p_category_id: ' || p_category_id ||
2158 ', p_item_description: ' || p_item_description ||
2159 ', p_unit_price: ' || p_unit_price ||
2160 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2161 ', p_suggested_vendor_id: ' || p_suggested_vendor_id ||
2162 ', p_suggested_vendor_site_id: ' || p_suggested_vendor_site_id ||
2163 ', p_vendor_product_code: ' || p_vendor_product_code || ')');
2164 END IF;
2165
2166 xErrLoc:= 100;
2167 EXECUTE IMMEDIATE
2168 'INSERT INTO ipo_reqexpress_lines_all( ' ||
2169 'org_id, ' ||
2170 'express_name, ' ||
2171 'sequence_num, ' ||
2172 'source_type_code, ' ||
2173 'po_header_id, ' ||
2174 'po_line_id, ' ||
2175 'item_id, ' ||
2176 'category_id, ' ||
2177 'item_description, ' ||
2178 'unit_price, ' ||
2179 'unit_meas_lookup_code, ' ||
2180 'suggested_vendor_id, ' ||
2181 'suggested_vendor_site_id, ' ||
2182 'suggested_vendor_product_code, ' ||
2183 'creation_date, ' ||
2184 'last_update_date) '||
2185 'VALUES( ' ||
2186 ':org_id, ' ||
2187 ':express_name, ' ||
2188 ':sequence_num, ' ||
2189 ':source_type_code, ' ||
2190 ':po_header_id, ' ||
2191 ':po_line_id, ' ||
2192 ':item_id, ' ||
2193 ':category_id, ' ||
2194 ':item_description, ' ||
2195 ':unit_price, ' ||
2196 ':unit_meas_lookup_code, ' ||
2197 ':suggested_vendor_id, ' ||
2198 ':suggested_vendor_site_id, ' ||
2199 ':suggested_vendor_product_code, ' ||
2200 'SYSDATE, ' ||
2201 'SYSDATE) '
2202 USING p_org_id, p_express_name,
2203 p_sequence_num, p_source_type_code,
2204 p_po_header_id, p_po_line_id,
2205 p_item_id, p_category_id,
2206 p_item_description, p_unit_price,
2207 p_unit_meas_lookup_code,
2208 p_suggested_vendor_id,
2209 p_suggested_vendor_site_id,
2210 p_vendor_product_code;
2211
2212 xErrLoc:= 400;
2213 COMMIT;
2214
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 ROLLBACK;
2218 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2219 xErrLoc||' '||SQLERRM);
2220 raise ICX_POR_EXT_UTL.gException;
2221 END createTemplateLine;
2222
2223 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
2224 -- Overloaded createTemplateLine Procedure to create a template line accepting Suggested Quantity
2225
2226 PROCEDURE createTemplateLine(p_org_id IN NUMBER,
2227 p_express_name IN VARCHAR2,
2228 p_sequence_num IN NUMBER,
2229 p_source_type_code IN VARCHAR2,
2230 p_po_header_id IN NUMBER,
2231 p_po_line_id IN NUMBER,
2232 p_item_id IN NUMBER,
2233 p_category_id IN NUMBER,
2234 p_item_description IN VARCHAR2,
2235 p_unit_price IN NUMBER,
2236 p_suggested_quantity IN NUMBER,
2237 p_unit_meas_lookup_code IN VARCHAR2,
2238 p_suggested_vendor_id IN NUMBER,
2239 p_suggested_vendor_site_id IN NUMBER,
2240 p_vendor_product_code IN VARCHAR2)
2241 IS
2242 xErrLoc PLS_INTEGER:= 100;
2243
2244 BEGIN
2245 xErrLoc:= 50;
2246 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2247 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2248 'createTemplateLine(p_org_id: ' || p_org_id ||
2249 ', p_express_name: ' || p_express_name ||
2250 ', p_sequence_num: ' || p_sequence_num ||
2251 ', p_source_type_code: ' || p_source_type_code ||
2252 ', p_po_header_id: ' || p_po_header_id ||
2253 ', p_po_line_id: ' || p_po_line_id ||
2254 ', p_item_id: ' || p_item_id ||
2255 ', p_category_id: ' || p_category_id ||
2256 ', p_item_description: ' || p_item_description ||
2257 ', p_unit_price: ' || p_unit_price ||
2258 ', p_suggested_quantity:' || p_suggested_quantity ||
2259 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2260 ', p_suggested_vendor_id: ' || p_suggested_vendor_id ||
2261 ', p_suggested_vendor_site_id: ' || p_suggested_vendor_site_id ||
2262 ', p_vendor_product_code: ' || p_vendor_product_code || ')');
2263 END IF;
2264
2265 xErrLoc:= 100;
2266 EXECUTE IMMEDIATE
2267 'INSERT INTO ipo_reqexpress_lines_all( ' ||
2268 'org_id, ' ||
2269 'express_name, ' ||
2270 'sequence_num, ' ||
2271 'source_type_code, ' ||
2272 'po_header_id, ' ||
2273 'po_line_id, ' ||
2274 'item_id, ' ||
2275 'category_id, ' ||
2276 'item_description, ' ||
2277 'unit_price, ' ||
2278 'suggested_quantity, ' ||
2279 'unit_meas_lookup_code, ' ||
2280 'suggested_vendor_id, ' ||
2281 'suggested_vendor_site_id, ' ||
2282 'suggested_vendor_product_code, ' ||
2283 'creation_date, ' ||
2284 'last_update_date) '||
2285 'VALUES( ' ||
2286 ':org_id, ' ||
2287 ':express_name, ' ||
2288 ':sequence_num, ' ||
2289 ':source_type_code, ' ||
2290 ':po_header_id, ' ||
2291 ':po_line_id, ' ||
2292 ':item_id, ' ||
2293 ':category_id, ' ||
2294 ':item_description, ' ||
2295 ':unit_price, ' ||
2296 ':suggested_quantity, ' ||
2297 ':unit_meas_lookup_code, ' ||
2298 ':suggested_vendor_id, ' ||
2299 ':suggested_vendor_site_id, ' ||
2300 ':suggested_vendor_product_code, ' ||
2301 'SYSDATE, ' ||
2302 'SYSDATE) '
2303 USING p_org_id, p_express_name,
2304 p_sequence_num, p_source_type_code,
2305 p_po_header_id, p_po_line_id,
2306 p_item_id, p_category_id,
2307 p_item_description, p_unit_price,
2308 p_suggested_quantity,
2309 p_unit_meas_lookup_code,
2310 p_suggested_vendor_id,
2311 p_suggested_vendor_site_id,
2312 p_vendor_product_code;
2313
2314 xErrLoc:= 400;
2315 COMMIT;
2316
2317 EXCEPTION
2318 WHEN OTHERS THEN
2319 ROLLBACK;
2320 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2321 xErrLoc||' '||SQLERRM);
2322 raise ICX_POR_EXT_UTL.gException;
2323 END createTemplateLine;
2324
2325 -- Update a template line
2326 PROCEDURE updateTemplateLine(p_org_id IN NUMBER,
2327 p_express_name IN VARCHAR2,
2328 p_sequence_num IN NUMBER,
2329 p_po_header_id IN NUMBER,
2330 p_po_line_id IN NUMBER,
2331 p_item_description IN VARCHAR2,
2332 p_unit_price IN NUMBER,
2333 -- FPJ Bug# 3007068 sosingha: Extractor Changes for Kit Support project.
2334 p_suggested_quantity IN NUMBER,
2335 p_unit_meas_lookup_code IN VARCHAR2,
2336 p_suggested_vendor_site_id IN NUMBER,
2337 p_vendor_product_code IN VARCHAR2)
2338 IS
2339 xErrLoc PLS_INTEGER:= 100;
2340
2341 BEGIN
2342 xErrLoc:= 50;
2343 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2344 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2345 'updateTemplateLine(p_org_id: ' || p_org_id ||
2346 ', p_express_name: ' || p_express_name ||
2347 ', p_sequence_num: ' || p_sequence_num ||
2348 ', p_po_header_id: ' || p_po_header_id ||
2349 ', p_po_line_id: ' || p_po_line_id ||
2350 ', p_item_description: ' || p_item_description ||
2351 ', p_unit_price: ' || p_unit_price ||
2352 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
2353 ', p_suggested_quantity: ' || p_suggested_quantity ||
2354 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2355 ', p_suggested_vendor_site_id: ' || p_suggested_vendor_site_id ||
2356 ', p_vendor_product_code: ' || p_vendor_product_code || ')');
2357 END IF;
2358
2359 xErrLoc:= 100;
2360 EXECUTE IMMEDIATE
2361 'UPDATE ipo_reqexpress_lines_all SET ' ||
2362 'po_header_id = DECODE(:po_header_id, '||
2363 ':miss_num, po_header_id,:po_header_id), '||
2364 'po_line_id = DECODE(:po_line_id, '||
2365 ':miss_num, po_line_id,:po_line_id), '||
2366 'item_description = DECODE(:item_description, '||
2367 ':miss_char, item_description,:item_description), '||
2368 'unit_price = DECODE(:unit_price, '||
2369 ':miss_num, unit_price,:unit_price), '||
2370 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
2371 'suggested_quantity = DECODE(:suggested_quantity, '||
2372 ':miss_num, suggested_quantity,:suggested_quantity), '||
2373 'unit_meas_lookup_code = DECODE(:unit_meas_lookup_code, '||
2374 ':miss_char, unit_meas_lookup_code,:unit_meas_lookup_code), '||
2375 'suggested_vendor_site_id = DECODE(:suggested_vendor_site_id, '||
2376 ':miss_num, suggested_vendor_site_id,:suggested_vendor_site_id), '||
2377 'suggested_vendor_product_code = DECODE(:suggested_vendor_product_code, '||
2378 ':miss_char, suggested_vendor_product_code,:suggested_vendor_product_code), '||
2379 'last_update_date = SYSDATE ' ||
2380 'WHERE org_id =:org_id ' ||
2381 'AND express_name =:express_name ' ||
2382 'AND sequence_num =:sequence_num '
2383 USING p_po_header_id, FND_API.G_MISS_NUM, p_po_header_id,
2384 p_po_line_id, FND_API.G_MISS_NUM, p_po_line_id,
2385 p_item_description, FND_API.G_MISS_CHAR, p_item_description,
2386 p_unit_price, FND_API.G_MISS_NUM, p_unit_price,
2387 -- FPJ Bug# 3007068 sosingha: Extractor Changes for Kit Support project
2388 p_suggested_quantity, FND_API.G_MISS_NUM, p_suggested_quantity,
2389 p_unit_meas_lookup_code, FND_API.G_MISS_CHAR, p_unit_meas_lookup_code,
2390 p_suggested_vendor_site_id, FND_API.G_MISS_NUM, p_suggested_vendor_site_id,
2391 p_vendor_product_code, FND_API.G_MISS_CHAR, p_vendor_product_code,
2392 p_org_id, p_express_name, p_sequence_num;
2393
2394 xErrLoc:= 400;
2395 COMMIT;
2396
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 ROLLBACK;
2400 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateLine-'||
2401 xErrLoc||' '||SQLERRM);
2402 raise ICX_POR_EXT_UTL.gException;
2403 END updateTemplateLine;
2404
2405 -- Create a contract header
2406 PROCEDURE createContractHeader(p_po_header_id IN NUMBER,
2407 p_org_id IN NUMBER,
2408 p_segment1 IN VARCHAR2,
2409 p_type_lookup_code IN VARCHAR2,
2410 p_rate IN NUMBER,
2411 p_currency_code IN VARCHAR2,
2412 p_vendor_id IN NUMBER,
2413 p_vendor_site_id IN NUMBER,
2414 p_approved_date IN DATE,
2415 p_approved_flag IN VARCHAR2,
2416 p_approval_required_flag IN VARCHAR2,
2417 p_cancel_flag IN VARCHAR2,
2418 p_frozen_flag IN VARCHAR2,
2419 p_closed_code IN VARCHAR2,
2420 p_status_lookup_code IN VARCHAR2,
2421 p_quotation_class_code IN VARCHAR2,
2422 p_start_date IN DATE,
2423 p_end_date IN DATE,
2424 p_global_agreement_flag IN VARCHAR2)
2425 IS
2426 xErrLoc PLS_INTEGER:= 100;
2427
2428 BEGIN
2429 xErrLoc:= 50;
2430 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2431 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2432 'createContractHeader(p_po_header_id: ' || p_po_header_id ||
2433 ', p_org_id: ' || p_org_id ||
2434 ', p_segment1: ' || p_segment1 ||
2435 ', p_type_lookup_code: ' || p_type_lookup_code ||
2436 ', p_rate: ' || p_rate ||
2437 ', p_currency_code: ' || p_currency_code ||
2438 ', p_vendor_id: ' || p_vendor_id ||
2439 ', p_vendor_site_id: ' || p_vendor_site_id ||
2440 ', p_approved_date: ' || p_approved_date ||
2441 ', p_approved_flag: ' || p_approved_flag ||
2442 ', p_approval_required_flag: ' || p_approval_required_flag ||
2443 ', p_cancel_flag: ' || p_cancel_flag ||
2444 ', p_frozen_flag: ' || p_frozen_flag ||
2445 ', p_closed_code: ' || p_closed_code ||
2446 ', p_status_lookup_code: ' || p_status_lookup_code ||
2447 ', p_quotation_class_code: ' || p_quotation_class_code ||
2448 ', p_start_date: ' || p_start_date ||
2449 ', p_end_date: ' || p_end_date ||
2450 ', p_global_agreement_flag: ' || p_global_agreement_flag || ')');
2451 END IF;
2452
2453 xErrLoc:= 100;
2454 EXECUTE IMMEDIATE
2455 'INSERT INTO ipo_headers_all( ' ||
2456 'po_header_id, ' ||
2457 'org_id, ' ||
2458 'segment1, ' ||
2459 'type_lookup_code, ' ||
2460 'rate, ' ||
2461 'currency_code, ' ||
2462 'vendor_id, ' ||
2463 'vendor_site_id, ' ||
2464 'approved_date, ' ||
2465 'approved_flag, ' ||
2466 'approval_required_flag, ' ||
2467 'cancel_flag, ' ||
2468 'frozen_flag, ' ||
2469 'closed_code, ' ||
2470 'status_lookup_code, ' ||
2471 'quotation_class_code, ' ||
2472 'start_date, ' ||
2473 'end_date, ' ||
2474 'global_agreement_flag, ' ||
2475 'last_update_date) ' ||
2476 'VALUES( ' ||
2477 ':po_header_id, ' ||
2478 ':org_id, ' ||
2479 ':segment1, ' ||
2480 ':type_lookup_code, ' ||
2481 ':rate, ' ||
2482 ':currency_code, ' ||
2483 ':vendor_id, ' ||
2484 ':vendor_site_id, ' ||
2485 ':approved_date, ' ||
2486 ':approved_flag, ' ||
2487 ':approval_required_flag, ' ||
2488 ':cancel_flag, ' ||
2489 ':frozen_flag, ' ||
2490 ':closed_code, ' ||
2491 ':status_lookup_code, ' ||
2492 ':quotation_class_code, ' ||
2493 ':start_date, ' ||
2494 ':end_date, ' ||
2495 ':global_agreement_flag, ' ||
2496 'SYSDATE) '
2497 USING p_po_header_id, p_org_id, p_segment1,
2498 p_type_lookup_code, p_rate, p_currency_code,
2499 p_vendor_id, p_vendor_site_id,
2500 p_approved_date, p_approved_flag,
2501 p_approval_required_flag, p_cancel_flag,
2502 p_frozen_flag, p_closed_code,
2503 p_status_lookup_code, p_quotation_class_code,
2504 p_start_date, p_end_date,
2505 p_global_agreement_flag;
2506
2507 xErrLoc:= 400;
2508 COMMIT;
2509
2510 EXCEPTION
2511 WHEN OTHERS THEN
2512 ROLLBACK;
2513 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractHeader-'||
2514 xErrLoc||' '||SQLERRM);
2515 raise ICX_POR_EXT_UTL.gException;
2516 END createContractHeader;
2517
2518 -- Create a contract line
2519 PROCEDURE createContractLine(p_po_header_id IN NUMBER,
2520 p_po_line_id IN NUMBER,
2521 p_org_id IN NUMBER,
2522 p_line_num IN NUMBER,
2523 p_item_id IN NUMBER,
2524 p_item_description IN VARCHAR2,
2525 p_vendor_product_num IN VARCHAR2,
2526 p_line_type_id IN NUMBER,
2527 p_category_id IN NUMBER,
2528 p_unit_price IN NUMBER,
2529 p_unit_meas_lookup_code IN VARCHAR2,
2530 p_attribute13 IN VARCHAR2,
2531 p_attribute14 IN VARCHAR2,
2532 p_cancel_flag IN VARCHAR2,
2533 p_closed_code IN VARCHAR2,
2534 p_expiration_date IN DATE,
2535 p_outside_operation_flag IN VARCHAR2)
2536 IS
2537 xErrLoc PLS_INTEGER:= 100;
2538
2539 BEGIN
2540 xErrLoc:= 50;
2541 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2542 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2543 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2544 ', p_po_line_id: ' || p_po_line_id ||
2545 ', p_org_id: ' || p_org_id ||
2546 ', p_line_num: ' || p_line_num ||
2547 ', p_item_id: ' || p_item_id ||
2548 ', p_item_description: ' || p_item_description ||
2549 ', p_vendor_product_num: ' || p_vendor_product_num ||
2550 ', p_line_type_id: ' || p_line_type_id ||
2551 ', p_category_id: ' || p_category_id ||
2552 ', p_unit_price: ' || p_unit_price ||
2553 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2554 ', p_attribute13: ' || p_attribute13 ||
2555 ', p_attribute14: ' || p_attribute14 ||
2556 ', p_cancel_flag: ' || p_cancel_flag ||
2557 ', p_closed_code: ' || p_closed_code ||
2558 ', p_expiration_date: ' || p_expiration_date ||
2559 ', p_outside_operation_flag: ' || p_outside_operation_flag || ')');
2560 END IF;
2561
2562 xErrLoc:= 100;
2563 EXECUTE IMMEDIATE
2564 'INSERT INTO ipo_lines_all( ' ||
2565 'po_header_id, ' ||
2566 'po_line_id, ' ||
2567 'org_id, ' ||
2568 'line_num, ' ||
2569 'item_id, ' ||
2570 'item_description, ' ||
2571 'vendor_product_num, ' ||
2572 'line_type_id, ' ||
2573 'category_id, ' ||
2574 'unit_price, ' ||
2575 'unit_meas_lookup_code, ' ||
2576 'attribute13, ' ||
2577 'attribute14, ' ||
2578 'cancel_flag, ' ||
2579 'closed_code, ' ||
2580 'expiration_date, ' ||
2581 'creation_date, ' ||
2582 'last_update_date) ' ||
2583 'VALUES( ' ||
2584 ':po_header_id, ' ||
2585 ':po_line_id, ' ||
2586 ':org_id, ' ||
2587 ':line_num, ' ||
2588 ':item_id, ' ||
2589 ':item_description, ' ||
2590 ':vendor_product_num, ' ||
2591 ':line_type_id, ' ||
2592 ':category_id, ' ||
2593 ':unit_price, ' ||
2594 ':unit_meas_lookup_code, ' ||
2595 ':attribute13, ' ||
2596 ':attribute14, ' ||
2597 ':cancel_flag, ' ||
2598 ':closed_code, ' ||
2599 ':expiration_date, ' ||
2600 'SYSDATE, ' ||
2601 'SYSDATE) '
2602 USING p_po_header_id, p_po_line_id, p_org_id,
2603 p_line_num, p_item_id, p_item_description,
2604 p_vendor_product_num, p_line_type_id,
2605 p_category_id, p_unit_price,
2606 p_unit_meas_lookup_code, p_attribute13,
2607 p_attribute14, p_cancel_flag,
2608 p_closed_code, p_expiration_date;
2609
2610 xErrLoc:= 200;
2611 EXECUTE IMMEDIATE
2612 'INSERT INTO ipo_line_types_b( ' ||
2613 'line_type_id, ' ||
2614 'outside_operation_flag, ' ||
2615 'last_update_date) ' ||
2616 'VALUES( ' ||
2617 ':line_type_id, ' ||
2618 ':outside_operation_flag, ' ||
2619 'SYSDATE) '
2620 USING p_line_type_id, p_outside_operation_flag;
2621
2622 xErrLoc:= 400;
2623 COMMIT;
2624
2625 EXCEPTION
2626 WHEN OTHERS THEN
2627 ROLLBACK;
2628 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2629 xErrLoc||' '||SQLERRM);
2630 raise ICX_POR_EXT_UTL.gException;
2631 END createContractLine;
2632
2633 -- FPJ FPSL Extractor Changes
2634 -- Add 5 parameters for Amount, Allow Price Override Flag,
2635 -- Not to Exceed Price, Value Basis, Purchase Basis
2636 -- Create a contract line
2637 PROCEDURE createContractLine(p_po_header_id IN NUMBER,
2638 p_po_line_id IN NUMBER,
2639 p_org_id IN NUMBER,
2640 p_line_num IN NUMBER,
2641 p_item_id IN NUMBER,
2642 p_item_description IN VARCHAR2,
2643 p_vendor_product_num IN VARCHAR2,
2644 p_line_type_id IN NUMBER,
2645 p_category_id IN NUMBER,
2646 p_unit_price IN NUMBER,
2647 p_unit_meas_lookup_code IN VARCHAR2,
2648 p_attribute13 IN VARCHAR2,
2649 p_attribute14 IN VARCHAR2,
2650 p_cancel_flag IN VARCHAR2,
2651 p_closed_code IN VARCHAR2,
2652 p_expiration_date IN DATE,
2653 p_outside_operation_flag IN VARCHAR2,
2654 p_amount IN NUMBER,
2655 p_allow_price_override_flag IN VARCHAR2,
2656 p_not_to_exceed_price IN NUMBER,
2657 p_value_basis IN VARCHAR2,
2658 p_purchase_basis IN VARCHAR2)
2659 IS
2660 xErrLoc PLS_INTEGER:= 100;
2661
2662 BEGIN
2663 xErrLoc:= 50;
2664 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2665 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2666 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2667 ', p_po_line_id: ' || p_po_line_id ||
2668 ', p_org_id: ' || p_org_id ||
2669 ', p_line_num: ' || p_line_num ||
2670 ', p_item_id: ' || p_item_id ||
2671 ', p_item_description: ' || p_item_description ||
2672 ', p_vendor_product_num: ' || p_vendor_product_num ||
2673 ', p_line_type_id: ' || p_line_type_id ||
2674 ', p_category_id: ' || p_category_id ||
2675 ', p_unit_price: ' || p_unit_price ||
2676 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2677 ', p_attribute13: ' || p_attribute13 ||
2678 ', p_attribute14: ' || p_attribute14 ||
2679 ', p_cancel_flag: ' || p_cancel_flag ||
2680 ', p_closed_code: ' || p_closed_code ||
2681 ', p_expiration_date: ' || p_expiration_date ||
2682 ', p_outside_operation_flag: ' || p_outside_operation_flag ||
2683 ', p_amount: ' || p_amount ||
2684 ', p_allow_price_override_flag: ' || p_allow_price_override_flag ||
2685 ', p_not_to_exceed_price: ' || p_not_to_exceed_price ||
2686 ', p_value_basis: ' || p_value_basis ||
2687 ', p_purchase_basis: ' || p_purchase_basis || ')');
2688 END IF;
2689
2690 xErrLoc:= 100;
2691 EXECUTE IMMEDIATE
2692 'INSERT INTO ipo_lines_all( ' ||
2693 'po_header_id, ' ||
2694 'po_line_id, ' ||
2695 'org_id, ' ||
2696 'line_num, ' ||
2697 'item_id, ' ||
2698 'item_description, ' ||
2699 'vendor_product_num, ' ||
2700 'line_type_id, ' ||
2701 'category_id, ' ||
2702 'unit_price, ' ||
2703 'unit_meas_lookup_code, ' ||
2704 'attribute13, ' ||
2705 'attribute14, ' ||
2706 'cancel_flag, ' ||
2707 'closed_code, ' ||
2708 'expiration_date, ' ||
2709 'creation_date, ' ||
2710 'last_update_date, ' ||
2711 'amount, ' ||
2712 'allow_price_override_flag, ' ||
2713 'not_to_exceed_price) ' ||
2714 'VALUES( ' ||
2715 ':po_header_id, ' ||
2716 ':po_line_id, ' ||
2717 ':org_id, ' ||
2718 ':line_num, ' ||
2719 ':item_id, ' ||
2720 ':item_description, ' ||
2721 ':vendor_product_num, ' ||
2722 ':line_type_id, ' ||
2723 ':category_id, ' ||
2724 ':unit_price, ' ||
2725 ':unit_meas_lookup_code, ' ||
2726 ':attribute13, ' ||
2727 ':attribute14, ' ||
2728 ':cancel_flag, ' ||
2729 ':closed_code, ' ||
2730 ':expiration_date, ' ||
2731 'SYSDATE, ' ||
2732 'SYSDATE, ' ||
2733 ':amount, ' ||
2734 ':allow_price_override_flag, ' ||
2735 ':not_to_exceed_price) '
2736 USING p_po_header_id, p_po_line_id, p_org_id,
2737 p_line_num, p_item_id, p_item_description,
2738 p_vendor_product_num, p_line_type_id,
2739 p_category_id, p_unit_price,
2740 p_unit_meas_lookup_code, p_attribute13,
2741 p_attribute14, p_cancel_flag,
2742 p_closed_code, p_expiration_date,
2743 p_amount, p_allow_price_override_flag, p_not_to_exceed_price;
2744
2745 xErrLoc:= 200;
2746 EXECUTE IMMEDIATE
2747 'INSERT INTO ipo_line_types_b( ' ||
2748 'line_type_id, ' ||
2749 'outside_operation_flag, ' ||
2750 'last_update_date, ' ||
2751 'order_type_lookup_code, ' ||
2752 'purchase_basis) ' ||
2753 'VALUES( ' ||
2754 ':line_type_id, ' ||
2755 ':outside_operation_flag, ' ||
2756 'SYSDATE, ' ||
2757 ':order_type_lookup_code, ' ||
2758 ':purchase_basis ) '
2759 USING p_line_type_id, p_outside_operation_flag,
2760 p_value_basis, p_purchase_basis;
2761
2762 xErrLoc:= 400;
2763 COMMIT;
2764
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 ROLLBACK;
2768 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2769 xErrLoc||' '||SQLERRM);
2770 raise ICX_POR_EXT_UTL.gException;
2771 END createContractLine;
2772
2773 -- Update a contract header
2774 PROCEDURE updateContractHeader(p_po_header_id IN NUMBER,
2775 p_rate IN NUMBER,
2776 p_currency_code IN VARCHAR2,
2777 p_vendor_site_id IN NUMBER,
2778 p_approved_date IN DATE,
2779 p_approved_flag IN VARCHAR2,
2780 p_approval_required_flag IN VARCHAR2,
2781 p_cancel_flag IN VARCHAR2,
2782 p_frozen_flag IN VARCHAR2,
2783 p_closed_code IN VARCHAR2,
2784 p_start_date IN DATE,
2785 p_end_date IN DATE,
2786 p_global_agreement_flag IN VARCHAR2)
2787 IS
2788 xErrLoc PLS_INTEGER:= 100;
2789
2790 BEGIN
2791 xErrLoc:= 50;
2792 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2793 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2794 'updateContractHeader(p_po_header_id: ' || p_po_header_id ||
2795 ', p_rate: ' || p_rate ||
2796 ', p_currency_code: ' || p_currency_code ||
2797 ', p_vendor_site_id: ' || p_vendor_site_id ||
2798 ', p_approved_date: ' || p_approved_date ||
2799 ', p_approved_flag: ' || p_approved_flag ||
2800 ', p_approval_required_flag: ' || p_approval_required_flag ||
2801 ', p_cancel_flag: ' || p_cancel_flag ||
2802 ', p_frozen_flag: ' || p_frozen_flag ||
2803 ', p_closed_code: ' || p_closed_code ||
2804 ', p_start_date: ' || p_start_date ||
2805 ', p_end_date: ' || p_end_date ||
2806 ', p_global_agreement_flag: ' || p_global_agreement_flag || ')');
2807 END IF;
2808
2809 xErrLoc:= 100;
2810 EXECUTE IMMEDIATE
2811 'UPDATE ipo_headers_all SET ' ||
2812 'rate = DECODE(:rate, '||
2813 ':miss_num, rate,:rate), '||
2814 'currency_code = DECODE(:currency_code, '||
2815 ':miss_char, currency_code,:currency_code), '||
2816 'vendor_site_id = DECODE(:vendor_site_id, '||
2817 ':miss_num, vendor_site_id,:vendor_site_id), '||
2818 'approved_date = DECODE(:approved_date, '||
2819 ':miss_date, approved_date,:approved_date), '||
2820 'approved_flag = DECODE(:approved_flag, '||
2821 ':miss_char, approved_flag,:approved_flag), '||
2822 'approval_required_flag = DECODE(:approval_required_flag, '||
2823 ':miss_char, approval_required_flag,:approval_required_flag), '||
2824 'cancel_flag = DECODE(:cancel_flag, '||
2825 ':miss_char, cancel_flag,:cancel_flag), '||
2826 'frozen_flag = DECODE(:frozen_flag, '||
2827 ':miss_char, frozen_flag,:frozen_flag), '||
2828 'closed_code = DECODE(:closed_code, '||
2829 ':miss_char, closed_code,:closed_code), '||
2830 'start_date = DECODE(:start_date, '||
2831 ':miss_date, start_date,:start_date), '||
2832 'end_date = DECODE(:end_date, '||
2833 ':miss_date, end_date,:end_date), '||
2834 'global_agreement_flag = DECODE(:global_agreement_flag, '||
2835 ':miss_char, global_agreement_flag,:global_agreement_flag), '||
2836 'last_update_date = SYSDATE ' ||
2837 'WHERE po_header_id =:po_header_id '
2838 USING p_rate, FND_API.G_MISS_NUM, p_rate,
2839 p_currency_code, FND_API.G_MISS_CHAR, p_currency_code,
2840 p_vendor_site_id, FND_API.G_MISS_NUM, p_vendor_site_id,
2841 p_approved_date, FND_API.G_MISS_DATE, p_approved_date,
2842 p_approved_flag, FND_API.G_MISS_CHAR, p_approved_flag,
2843 p_approval_required_flag, FND_API.G_MISS_CHAR, p_approval_required_flag,
2844 p_cancel_flag, FND_API.G_MISS_CHAR, p_cancel_flag,
2845 p_frozen_flag, FND_API.G_MISS_CHAR, p_frozen_flag,
2846 p_closed_code, FND_API.G_MISS_CHAR, p_closed_code,
2847 p_start_date, FND_API.G_MISS_DATE, p_start_date,
2848 p_end_date, FND_API.G_MISS_DATE, p_end_date,
2849 p_global_agreement_flag, FND_API.G_MISS_CHAR, p_global_agreement_flag,
2850 p_po_header_id;
2851
2852 xErrLoc:= 400;
2853 COMMIT;
2854
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 ROLLBACK;
2858 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractHeader-'||
2859 xErrLoc||' '||SQLERRM);
2860 raise ICX_POR_EXT_UTL.gException;
2861 END updateContractHeader;
2862
2863 -- Update a contract line
2864 PROCEDURE updateContractLine(p_po_line_id IN NUMBER,
2865 p_item_description IN VARCHAR2,
2866 p_vendor_product_num IN VARCHAR2,
2867 p_line_type_id IN NUMBER,
2868 p_category_id IN NUMBER,
2869 p_unit_price IN NUMBER,
2870 p_unit_meas_lookup_code IN VARCHAR2,
2871 p_attribute13 IN VARCHAR2,
2872 p_attribute14 IN VARCHAR2,
2873 p_cancel_flag IN VARCHAR2,
2874 p_closed_code IN VARCHAR2,
2875 p_creation_date IN DATE,
2876 p_expiration_date IN DATE,
2877 p_outside_operation_flag IN VARCHAR2)
2878 IS
2879 xErrLoc PLS_INTEGER:= 100;
2880
2881 BEGIN
2882 xErrLoc:= 50;
2883 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2884 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2885 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2886 ', p_item_description: ' || p_item_description ||
2887 ', p_vendor_product_num: ' || p_vendor_product_num ||
2888 ', p_line_type_id: ' || p_line_type_id ||
2889 ', p_unit_price: ' || p_unit_price ||
2890 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2891 ', p_attribute13: ' || p_attribute13 ||
2892 ', p_attribute14: ' || p_attribute14 ||
2893 ', p_cancel_flag: ' || p_cancel_flag ||
2894 ', p_closed_code: ' || p_closed_code ||
2895 ', p_creation_date: ' || p_creation_date ||
2896 ', p_expiration_date: ' || p_expiration_date ||
2897 ', p_outside_operation_flag: ' || p_outside_operation_flag || ')');
2898 END IF;
2899
2900 xErrLoc:= 100;
2901 EXECUTE IMMEDIATE
2902 'UPDATE ipo_lines_all SET ' ||
2903 'item_description = DECODE(:item_description, '||
2904 ':miss_char, item_description,:item_description), '||
2905 'vendor_product_num = DECODE(:vendor_product_num, '||
2906 ':miss_char, vendor_product_num,:vendor_product_num), '||
2907 'unit_price = DECODE(:unit_price, '||
2908 ':miss_num, unit_price,:unit_price), '||
2909 'unit_meas_lookup_code = DECODE(:unit_meas_lookup_code, '||
2910 ':miss_char, unit_meas_lookup_code,:unit_meas_lookup_code), '||
2911 'attribute13 = DECODE(:attribute13, '||
2912 ':miss_char, attribute13,:attribute13), '||
2913 'attribute14 = DECODE(:attribute14, '||
2914 ':miss_char, attribute14,:attribute14), '||
2915 'cancel_flag = DECODE(:cancel_flag, '||
2916 ':miss_char, cancel_flag,:cancel_flag), '||
2917 'closed_code = DECODE(:closed_code, '||
2918 ':miss_char, closed_code,:closed_code), '||
2919 'expiration_date = DECODE(:expiration_date, '||
2920 ':miss_date, expiration_date,:expiration_date), '||
2921 'last_update_date = SYSDATE ' ||
2922 'WHERE po_line_id =:po_line_id '
2923 USING p_item_description, FND_API.G_MISS_CHAR, p_item_description,
2924 p_vendor_product_num, FND_API.G_MISS_CHAR, p_vendor_product_num,
2925 p_unit_price, FND_API.G_MISS_NUM, p_unit_price,
2926 p_unit_meas_lookup_code, FND_API.G_MISS_CHAR, p_unit_meas_lookup_code,
2927 p_attribute13, FND_API.G_MISS_CHAR, p_attribute13,
2928 p_attribute14, FND_API.G_MISS_CHAR, p_attribute14,
2929 p_cancel_flag, FND_API.G_MISS_CHAR, p_cancel_flag,
2930 p_closed_code, FND_API.G_MISS_CHAR, p_closed_code,
2931 p_expiration_date, FND_API.G_MISS_DATE, p_expiration_date,
2932 p_po_line_id;
2933
2934 xErrLoc:= 200;
2935 EXECUTE IMMEDIATE
2936 'UPDATE ipo_line_types_b SET ' ||
2937 'outside_operation_flag = DECODE(:outside_operation_flag, '||
2938 ':miss_char, outside_operation_flag,:outside_operation_flag), '||
2939 'last_update_date = SYSDATE ' ||
2940 'WHERE line_type_id =:line_type_id '
2941 USING p_outside_operation_flag, FND_API.G_MISS_CHAR, p_outside_operation_flag,
2942 p_line_type_id;
2943
2944 xErrLoc:= 400;
2945 COMMIT;
2946
2947 EXCEPTION
2948 WHEN OTHERS THEN
2949 ROLLBACK;
2950 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
2951 xErrLoc||' '||SQLERRM);
2952 raise ICX_POR_EXT_UTL.gException;
2953 END updateContractLine;
2954
2955 -- Update a contract line
2956 -- FPJ FPSL Extractor Changes
2957 -- Add 3 parameters for Amount, Allow Price Override Flag and Not to Exceed Price
2958 PROCEDURE updateContractLine(p_po_line_id IN NUMBER,
2959 p_item_description IN VARCHAR2,
2960 p_vendor_product_num IN VARCHAR2,
2961 p_line_type_id IN NUMBER,
2962 p_category_id IN NUMBER,
2963 p_unit_price IN NUMBER,
2964 p_unit_meas_lookup_code IN VARCHAR2,
2965 p_attribute13 IN VARCHAR2,
2966 p_attribute14 IN VARCHAR2,
2967 p_cancel_flag IN VARCHAR2,
2968 p_closed_code IN VARCHAR2,
2969 p_creation_date IN DATE,
2970 p_expiration_date IN DATE,
2971 p_outside_operation_flag IN VARCHAR2,
2972 p_amount IN NUMBER,
2973 p_allow_price_override_flag IN VARCHAR2,
2974 p_not_to_exceed_price IN NUMBER)
2975 IS
2976 xErrLoc PLS_INTEGER:= 100;
2977
2978 BEGIN
2979 xErrLoc:= 50;
2980 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2981 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2982 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2983 ', p_item_description: ' || p_item_description ||
2984 ', p_vendor_product_num: ' || p_vendor_product_num ||
2985 ', p_line_type_id: ' || p_line_type_id ||
2986 ', p_unit_price: ' || p_unit_price ||
2987 ', p_unit_meas_lookup_code: ' || p_unit_meas_lookup_code ||
2988 ', p_attribute13: ' || p_attribute13 ||
2989 ', p_attribute14: ' || p_attribute14 ||
2990 ', p_cancel_flag: ' || p_cancel_flag ||
2991 ', p_closed_code: ' || p_closed_code ||
2992 ', p_creation_date: ' || p_creation_date ||
2993 ', p_expiration_date: ' || p_expiration_date ||
2994 ', p_outside_operation_flag: ' || p_outside_operation_flag ||
2995 ', p_amount: ' || p_amount ||
2996 ', p_allow_price_override_flag: ' || p_allow_price_override_flag ||
2997 ', p_not_to_exceed_price: ' || p_not_to_exceed_price || ')');
2998 END IF;
2999
3000 xErrLoc:= 100;
3001 EXECUTE IMMEDIATE
3002 'UPDATE ipo_lines_all SET ' ||
3003 'item_description = DECODE(:item_description, '||
3004 ':miss_char, item_description,:item_description), '||
3005 'vendor_product_num = DECODE(:vendor_product_num, '||
3006 ':miss_char, vendor_product_num,:vendor_product_num), '||
3007 'unit_price = DECODE(:unit_price, '||
3008 ':miss_num, unit_price,:unit_price), '||
3009 'unit_meas_lookup_code = DECODE(:unit_meas_lookup_code, '||
3010 ':miss_char, unit_meas_lookup_code,:unit_meas_lookup_code), '||
3011 'attribute13 = DECODE(:attribute13, '||
3012 ':miss_char, attribute13,:attribute13), '||
3013 'attribute14 = DECODE(:attribute14, '||
3014 ':miss_char, attribute14,:attribute14), '||
3015 'cancel_flag = DECODE(:cancel_flag, '||
3016 ':miss_char, cancel_flag,:cancel_flag), '||
3017 'closed_code = DECODE(:closed_code, '||
3018 ':miss_char, closed_code,:closed_code), '||
3019 'expiration_date = DECODE(:expiration_date, '||
3020 ':miss_date, expiration_date,:expiration_date), '||
3021 'amount = DECODE(:amount, '||
3022 ':miss_num, amount,:amount), '||
3023 'allow_price_override_flag = DECODE(:allow_price_override_flag, '||
3024 ':miss_char, allow_price_override_flag,:allow_price_override_flag), '||
3025 'not_to_exceed_price = DECODE(:not_to_exceed_price, '||
3026 ':miss_num, not_to_exceed_price,:not_to_exceed_price), '||
3027 'last_update_date = SYSDATE ' ||
3028 'WHERE po_line_id =:po_line_id '
3029 USING p_item_description, FND_API.G_MISS_CHAR, p_item_description,
3030 p_vendor_product_num, FND_API.G_MISS_CHAR, p_vendor_product_num,
3031 p_unit_price, FND_API.G_MISS_NUM, p_unit_price,
3032 p_unit_meas_lookup_code, FND_API.G_MISS_CHAR, p_unit_meas_lookup_code,
3033 p_attribute13, FND_API.G_MISS_CHAR, p_attribute13,
3034 p_attribute14, FND_API.G_MISS_CHAR, p_attribute14,
3035 p_cancel_flag, FND_API.G_MISS_CHAR, p_cancel_flag,
3036 p_closed_code, FND_API.G_MISS_CHAR, p_closed_code,
3037 p_expiration_date, FND_API.G_MISS_DATE, p_expiration_date,
3038 p_amount, FND_API.G_MISS_NUM, p_amount,
3039 p_allow_price_override_flag, FND_API.G_MISS_CHAR, p_allow_price_override_flag,
3040 p_not_to_exceed_price, FND_API.G_MISS_NUM, p_not_to_exceed_price,
3041 p_po_line_id;
3042
3043 xErrLoc:= 200;
3044 EXECUTE IMMEDIATE
3045 'UPDATE ipo_line_types_b SET ' ||
3046 'outside_operation_flag = DECODE(:outside_operation_flag, '||
3047 ':miss_char, outside_operation_flag,:outside_operation_flag), '||
3048 'last_update_date = SYSDATE ' ||
3049 'WHERE line_type_id =:line_type_id '
3050 USING p_outside_operation_flag, FND_API.G_MISS_CHAR, p_outside_operation_flag,
3051 p_line_type_id;
3052
3053 xErrLoc:= 400;
3054 COMMIT;
3055
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 ROLLBACK;
3059 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
3060 xErrLoc||' '||SQLERRM);
3061 raise ICX_POR_EXT_UTL.gException;
3062 END updateContractLine;
3063
3064 -- Create a quotation line location
3065 PROCEDURE createQuoteLL(p_line_location_id IN NUMBER,
3066 p_po_line_id IN NUMBER,
3067 p_start_date IN DATE,
3068 p_end_date IN DATE,
3069 p_approval_type IN VARCHAR2,
3070 p_start_date_active IN DATE,
3071 p_end_date_active IN DATE)
3072 IS
3073 xErrLoc PLS_INTEGER:= 100;
3074
3075 BEGIN
3076 xErrLoc:= 50;
3077 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3078 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3079 'createQuoteLL(p_line_location_id: ' || p_line_location_id ||
3080 ', p_po_line_id: ' || p_po_line_id ||
3081 ', p_start_date: ' || p_start_date ||
3082 ', p_end_date: ' || p_end_date ||
3083 ', p_approval_type: ' || p_approval_type ||
3084 ', p_start_date_active: ' || p_start_date_active ||
3085 ', p_end_date_active: ' || p_end_date_active || ')');
3086 END IF;
3087
3088 xErrLoc:= 100;
3089 EXECUTE IMMEDIATE
3090 'INSERT INTO ipo_line_locations_all( ' ||
3091 'line_location_id, ' ||
3092 'po_line_id, ' ||
3093 'start_date, ' ||
3094 'end_date, ' ||
3095 'last_update_date) ' ||
3096 'VALUES( ' ||
3097 ':line_location_id, ' ||
3098 ':po_line_id, ' ||
3099 ':start_date, ' ||
3100 ':end_date, ' ||
3101 'SYSDATE) '
3102 USING p_line_location_id, p_po_line_id,
3103 p_start_date, p_end_date;
3104
3105 xErrLoc:= 200;
3106 EXECUTE IMMEDIATE
3107 'INSERT INTO ipo_quotation_approvals_all( ' ||
3108 'line_location_id, ' ||
3109 'approval_type, ' ||
3110 'start_date_active, ' ||
3111 'end_date_active, ' ||
3112 'last_update_date) ' ||
3113 'VALUES( ' ||
3114 ':line_location_id, ' ||
3115 ':approval_type, ' ||
3116 ':start_date_active, ' ||
3117 ':end_date_active, ' ||
3118 'SYSDATE) '
3119 USING p_line_location_id, p_approval_type,
3120 p_start_date_active, p_end_date_active;
3121
3122 xErrLoc:= 400;
3123 COMMIT;
3124
3125 EXCEPTION
3126 WHEN OTHERS THEN
3127 ROLLBACK;
3128 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createQuoteLL-'||
3129 xErrLoc||' '||SQLERRM);
3130 raise ICX_POR_EXT_UTL.gException;
3131 END createQuoteLL;
3132
3133 -- Update a quotation line location
3134 PROCEDURE updateQuoteLL(p_line_location_id IN NUMBER,
3135 p_start_date IN DATE,
3136 p_end_date IN DATE,
3137 p_approval_type IN VARCHAR2,
3138 p_start_date_active IN DATE,
3139 p_end_date_active IN DATE)
3140 IS
3141 xErrLoc PLS_INTEGER:= 100;
3142
3143 BEGIN
3144 xErrLoc:= 50;
3145 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3146 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3147 'updateQuoteLL(p_line_location_id: ' || p_line_location_id ||
3148 ', p_start_date: ' || p_start_date ||
3149 ', p_end_date: ' || p_end_date ||
3150 ', p_approval_type: ' || p_approval_type ||
3151 ', p_start_date_active: ' || p_start_date_active ||
3152 ', p_end_date_active: ' || p_end_date_active || ')');
3153 END IF;
3154
3155 xErrLoc:= 100;
3156 EXECUTE IMMEDIATE
3157 'UPDATE ipo_line_locations_all SET ' ||
3158 'start_date = DECODE(:start_date, '||
3159 ':miss_date, start_date,:start_date), '||
3160 'end_date = DECODE(:end_date, '||
3161 ':miss_date, end_date,:end_date), '||
3162 'last_update_date = SYSDATE ' ||
3163 'WHERE line_location_id =:line_location_id '
3164 USING p_start_date, FND_API.G_MISS_DATE, p_start_date,
3165 p_end_date, FND_API.G_MISS_DATE, p_end_date,
3166 p_line_location_id;
3167
3168 xErrLoc:= 200;
3169 EXECUTE IMMEDIATE
3170 'UPDATE ipo_quotation_approvals_all SET ' ||
3171 'approval_type = DECODE(:approval_type, '||
3172 ':miss_char, approval_type,:approval_type), '||
3173 'approval_type, ' ||
3174 'start_date_active = DECODE(:start_date_active, '||
3175 ':miss_date, start_date_active,:start_date_active), '||
3176 'end_date_active = DECODE(:end_date_active, '||
3177 ':miss_date, end_date_active,:end_date_active), '||
3178 'last_update_date = SYSDATE ' ||
3179 'WHERE line_location_id =:line_location_id '
3180 USING p_approval_type, FND_API.G_MISS_CHAR, p_approval_type,
3181 p_start_date_active, FND_API.G_MISS_DATE, p_start_date_active,
3182 p_end_date_active, FND_API.G_MISS_DATE, p_end_date_active,
3183 p_line_location_id;
3184
3185 xErrLoc:= 400;
3186 COMMIT;
3187
3188 EXCEPTION
3189 WHEN OTHERS THEN
3190 ROLLBACK;
3191 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateQuoteLL-'||
3192 xErrLoc||' '||SQLERRM);
3193 raise ICX_POR_EXT_UTL.gException;
3194 END updateQuoteLL;
3195
3196 -- Create a global agreement assignment
3197 PROCEDURE createGlobalA(p_po_header_id IN NUMBER,
3198 p_organization_id IN NUMBER,
3199 p_enabled_flag IN VARCHAR2,
3200 p_vendor_site_id IN NUMBER,
3201 p_purchasing_org_id IN NUMBER) -- Centralized Proc Impacts
3202 IS
3203 xErrLoc PLS_INTEGER:= 100;
3204
3205 BEGIN
3206 xErrLoc:= 50;
3207 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3208 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3209 'createGlobalA(p_po_header_id: ' || p_po_header_id ||
3210 ', p_organization_id: ' || p_organization_id ||
3211 ', p_enabled_flag: ' || p_enabled_flag ||
3212 ', p_vendor_site_id: ' || p_vendor_site_id ||
3213 ', p_purchasing_org_id: ' || p_purchasing_org_id || ')'); -- Centralized Proc Impacts
3214 END IF;
3215
3216 xErrLoc:= 100;
3217 -- Centralized Proc Impacts : Insert the value for purchasing_org_id also
3218 EXECUTE IMMEDIATE
3219 'INSERT INTO ipo_ga_org_assignments( ' ||
3220 'po_header_id, ' ||
3221 'organization_id, ' ||
3222 'enabled_flag, ' ||
3223 'vendor_site_id, ' ||
3224 'purchasing_org_id, ' ||
3225 'last_update_date) ' ||
3226 'VALUES( ' ||
3227 ':po_header_id, ' ||
3228 ':organization_id, ' ||
3229 ':enabled_flag, ' ||
3230 ':vendor_site_id, ' ||
3231 ':purchasing_org_id, ' ||
3232 'SYSDATE) '
3233 USING p_po_header_id, p_organization_id,
3234 p_enabled_flag, p_vendor_site_id, p_purchasing_org_id;
3235
3236 xErrLoc:= 400;
3237 COMMIT;
3238
3239 EXCEPTION
3240 WHEN OTHERS THEN
3241 ROLLBACK;
3242 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGlobalA-'||
3243 xErrLoc||' '||SQLERRM);
3244 raise ICX_POR_EXT_UTL.gException;
3245 END createGlobalA;
3246
3247 -- Update a global agreement assignment
3248 PROCEDURE updateGlobalA(p_po_header_id IN NUMBER,
3249 p_organization_id IN NUMBER,
3250 p_enabled_flag IN VARCHAR2,
3251 p_vendor_site_id IN NUMBER,
3252 p_purchasing_org_id IN NUMBER) -- Centralized Proc Impacts
3253 IS
3254 xErrLoc PLS_INTEGER:= 100;
3255
3256 BEGIN
3257 xErrLoc:= 50;
3258 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3259 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3260 'updateGlobalA(p_po_header_id: ' || p_po_header_id ||
3261 ', p_organization_id: ' || p_organization_id ||
3262 ', p_enabled_flag: ' || p_enabled_flag ||
3263 ', p_vendor_site_id: ' || p_vendor_site_id ||
3264 ', p_purchasing_org_id: ' || p_purchasing_org_id || ')');
3265 END IF;
3266
3267 xErrLoc:= 100;
3268 EXECUTE IMMEDIATE
3269 'UPDATE ipo_ga_org_assignments SET ' ||
3270 'enabled_flag = DECODE(:enabled_flag, '||
3271 ':miss_char, enabled_flag,:enabled_flag), '||
3272 'vendor_site_id = DECODE(:vendor_site_id, '||
3273 ':miss_num, vendor_site_id,:vendor_site_id), '||
3274 'purchasing_org_id = DECODE(:purchasing_org_id, '|| -- Centralized Proc Impacts
3275 ':miss_num, purchasing_org_id,:purchasing_org_id), '||
3276 'last_update_date = SYSDATE ' ||
3277 'WHERE po_header_id =:po_header_id ' ||
3278 'AND organization_id =:organization_id '
3279 USING p_enabled_flag, FND_API.G_MISS_CHAR, p_enabled_flag,
3280 p_vendor_site_id, FND_API.G_MISS_NUM, p_vendor_site_id,
3281 p_purchasing_org_id, FND_API.G_MISS_NUM, p_purchasing_org_id,
3282 p_po_header_id, p_organization_id;
3283
3284 xErrLoc:= 400;
3285 COMMIT;
3286
3287 EXCEPTION
3288 WHEN OTHERS THEN
3289 ROLLBACK;
3290 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateGlobalA-'||
3291 xErrLoc||' '||SQLERRM);
3292 raise ICX_POR_EXT_UTL.gException;
3293 END updateGlobalA;
3294
3295 --------------------------------------------------------------
3296 -- Item Test Result Checking --
3297 --------------------------------------------------------------
3298 FUNCTION existItemsB(p_rt_item_id OUT NOCOPY NUMBER,
3299 p_org_id IN NUMBER,
3300 p_supplier_id IN NUMBER,
3301 p_supplier IN VARCHAR2,
3302 p_supplier_part_num IN VARCHAR2,
3303 p_internal_item_id IN NUMBER,
3304 p_internal_item_num IN VARCHAR2,
3305 p_extractor_updated_flag IN VARCHAR2,
3306 p_internal_flag IN VARCHAR2)
3307 RETURN BOOLEAN
3308 IS
3309 xErrLoc PLS_INTEGER;
3310 xSearchType VARCHAR2(20);
3311 xResult PLS_INTEGER;
3312 BEGIN
3313 xErrLoc:= 100;
3314 IF NVL(p_internal_flag, 'N') = 'N' THEN
3315 xSearchType := 'SUPPLIER';
3316 ELSE
3317 xSearchType := 'INTERNAL';
3318 END IF;
3319
3320 SELECT rt_item_id
3321 INTO p_rt_item_id
3322 FROM icx_cat_items_b i
3323 WHERE (org_id IS NULL AND p_org_id IS NULL OR
3324 org_id = p_org_id)
3325 AND (supplier_id = ICX_POR_EXT_ITEM.NULL_NUMBER AND
3326 NVL(p_supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER) =
3327 ICX_POR_EXT_ITEM.NULL_NUMBER OR
3328 supplier_id = p_supplier_id)
3329 AND (supplier IS NULL AND p_supplier IS NULL OR
3330 supplier = p_supplier)
3331 AND (supplier_part_num IS NULL AND p_supplier_part_num IS NULL OR
3332 supplier_part_num = p_supplier_part_num)
3333 AND (internal_item_id IS NULL AND p_internal_item_id IS NULL OR
3334 internal_item_id = p_internal_item_id)
3335 AND (internal_item_num IS NULL AND p_internal_item_num IS NULL OR
3336 internal_item_num = p_internal_item_num)
3337 AND extractor_updated_flag = p_extractor_updated_flag
3338 AND EXISTS (SELECT NULL
3339 FROM icx_cat_item_prices p
3340 WHERE p.rt_item_id = i.rt_item_id
3341 AND p.search_type = xSearchType);
3342
3343 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3344 'ItemsB[ORG_ID: ' || p_org_id ||
3345 ', SUPPLIER_ID: ' || p_supplier_id ||
3346 ', SUPPLIER: ' || p_supplier ||
3347 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3348 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3349 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3350 ', EXTRACTOR_UPDATED_FLAG: ' || p_extractor_updated_flag ||
3351 ', INTERNAL_FLAG: ' || p_internal_flag ||
3352 '] exists in ICX_CAT_ITEMS_B with RT_ITEM_ID: ' || p_rt_item_id);
3353
3354 xErrLoc:= 200;
3355 RETURN TRUE;
3356 EXCEPTION
3357 when NO_DATA_FOUND then
3358 xResult:= 0;
3359 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3360 'ItemsB[ORG_ID: ' || p_org_id ||
3361 ', SUPPLIER_ID: ' || p_supplier_id ||
3362 ', SUPPLIER: ' || p_supplier ||
3363 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3364 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3365 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3366 ', EXTRACTOR_UPDATED_FLAG: ' || p_extractor_updated_flag ||
3367 ', INTERNAL_FLAG: ' || p_internal_flag ||
3368 '] does not exist in ICX_CAT_ITEMS_B');
3369 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3370 'ItemsB[ORG_ID: ' || p_org_id ||
3371 ', SUPPLIER_ID: ' || p_supplier_id ||
3372 ', SUPPLIER: ' || p_supplier ||
3373 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3374 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3375 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3376 ', EXTRACTOR_UPDATED_FLAG: ' || p_extractor_updated_flag ||
3377 ', INTERNAL_FLAG: ' || p_internal_flag ||
3378 '] does not exist in ICX_CAT_ITEMS_B');
3379 RETURN FALSE;
3380 WHEN OTHERS THEN
3381 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsB-'||
3382 xErrLoc||' '||SQLERRM);
3383 raise ICX_POR_EXT_UTL.gException;
3384 END existItemsB;
3385
3386 FUNCTION notExistItemsB(p_org_id IN NUMBER,
3387 p_supplier_id IN NUMBER,
3388 p_supplier_part_num IN VARCHAR2,
3389 p_internal_item_id IN NUMBER,
3390 p_internal_flag IN VARCHAR2)
3391 RETURN BOOLEAN
3392 IS
3393 xErrLoc PLS_INTEGER;
3394 xSearchType VARCHAR2(20);
3395 xResult PLS_INTEGER;
3396 BEGIN
3397 xErrLoc:= 100;
3398 IF NVL(p_internal_flag, 'N') = 'N' THEN
3399 xSearchType := 'SUPPLIER';
3400 ELSE
3401 xSearchType := 'INTERNAL';
3402 END IF;
3403
3404 SELECT 0
3405 INTO xResult
3406 FROM icx_cat_items_b i
3407 WHERE (org_id IS NULL AND p_org_id IS NULL OR
3408 org_id = p_org_id)
3409 AND (supplier_id = ICX_POR_EXT_ITEM.NULL_NUMBER AND
3410 NVL(p_supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER) =
3411 ICX_POR_EXT_ITEM.NULL_NUMBER OR
3412 supplier_id = p_supplier_id)
3413 AND (supplier_part_num IS NULL AND p_supplier_part_num IS NULL OR
3414 supplier_part_num = p_supplier_part_num)
3415 AND (internal_item_id IS NULL AND p_internal_item_id IS NULL OR
3416 internal_item_id = p_internal_item_id)
3417 AND EXISTS (SELECT NULL
3418 FROM icx_cat_item_prices p
3419 WHERE p.rt_item_id = i.rt_item_id
3420 AND p.search_type = xSearchType);
3421
3422 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3423 'ItemsB[ORG_ID: ' || p_org_id ||
3424 ', SUPPLIER_ID: ' || p_supplier_id ||
3425 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3426 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3427 ', INTERNAL_FLAG: ' || p_internal_flag ||
3428 '] exists in ICX_CAT_ITEMS_B');
3429 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3430 'ItemsB[ORG_ID: ' || p_org_id ||
3431 ', SUPPLIER_ID: ' || p_supplier_id ||
3432 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3433 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3434 ', INTERNAL_FLAG: ' || p_internal_flag ||
3435 '] exists in ICX_CAT_ITEMS_B');
3436
3437 xErrLoc:= 200;
3438 RETURN FALSE;
3439 EXCEPTION
3440 when NO_DATA_FOUND then
3441 xResult:= 1;
3442 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3443 'ItemsB[ORG_ID: ' || p_org_id ||
3444 ', SUPPLIER_ID: ' || p_supplier_id ||
3445 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3446 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3447 ', INTERNAL_FLAG: ' || p_internal_flag ||
3448 '] does not exist in ICX_CAT_ITEMS_B');
3449 RETURN TRUE;
3450 WHEN OTHERS THEN
3451 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsB-'||
3452 xErrLoc||' '||SQLERRM);
3453 raise ICX_POR_EXT_UTL.gException;
3454 END notExistItemsB;
3455
3456 FUNCTION existItemsTLP(p_rt_item_id IN NUMBER,
3457 p_language IN VARCHAR2,
3458 p_item_source_type IN VARCHAR2,
3459 p_search_type IN VARCHAR2,
3460 p_primary_category_id OUT NOCOPY NUMBER,
3461 p_primary_category_name IN VARCHAR2,
3462 p_internal_item_id IN NUMBER,
3463 p_internal_item_num IN VARCHAR2,
3464 p_supplier_id IN NUMBER,
3465 p_supplier IN VARCHAR2,
3466 p_supplier_part_num IN VARCHAR2,
3467 p_description IN VARCHAR2,
3468 p_picture IN VARCHAR2,
3469 p_picture_url IN VARCHAR2)
3470 RETURN BOOLEAN
3471 IS
3472 xErrLoc PLS_INTEGER;
3473 xResult PLS_INTEGER;
3474 BEGIN
3475 xErrLoc:= 100;
3476 SELECT primary_category_id
3477 INTO p_primary_category_id
3478 FROM icx_cat_items_tlp
3479 WHERE rt_item_id = p_rt_item_id
3480 AND language = p_language
3481 AND item_source_type = p_item_source_type
3482 AND search_type = p_search_type
3483 AND primary_category_name = p_primary_category_name
3484 AND (supplier_id = ICX_POR_EXT_ITEM.NULL_NUMBER AND
3485 NVL(p_supplier_id, ICX_POR_EXT_ITEM.NULL_NUMBER) =
3486 ICX_POR_EXT_ITEM.NULL_NUMBER OR
3487 supplier_id = p_supplier_id)
3488 AND (supplier IS NULL AND p_supplier IS NULL OR
3489 supplier = p_supplier)
3490 AND (supplier_part_num IS NULL AND p_supplier_part_num IS NULL OR
3491 supplier_part_num = p_supplier_part_num)
3492 AND (internal_item_id IS NULL AND p_internal_item_id IS NULL OR
3493 internal_item_id = p_internal_item_id)
3494 AND (internal_item_num IS NULL AND p_internal_item_num IS NULL OR
3495 internal_item_num = p_internal_item_num)
3496 AND (description IS NULL AND p_description IS NULL OR
3497 description = p_description);
3498 /*
3499 AND (picture IS NULL AND p_picture IS NULL OR
3500 picture = p_picture)
3501 AND (picture_url IS NULL AND p_picture_url IS NULL OR
3502 picture_url = p_picture_url);
3503 */
3504
3505 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3506 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3507 ', LANGUAGE: ' || p_language ||
3508 ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3509 ', SEARCH_TYPE: ' || p_search_type ||
3510 ', PRIMARY_CATEGORY_ID: ' || p_primary_category_id ||
3511 ', PRIMARY_CATEGORY_NAME: ' || p_primary_category_name ||
3512 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3513 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3514 ', SUPPLIER_ID: ' || p_supplier_id ||
3515 ', SUPPLIER: ' || p_supplier ||
3516 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3517 ', DESCRIPTION: ' || p_description ||
3518 ', PICTURE: ' || p_picture ||
3519 ', PICTURE_URL: ' || p_picture_url ||
3520 '] exists in ICX_CAT_ITEMS_TLP');
3521
3522 xErrLoc:= 200;
3523 RETURN TRUE;
3524 EXCEPTION
3525 when NO_DATA_FOUND then
3526 xResult:= 0;
3527 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3528 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3529 ', LANGUAGE: ' || p_language ||
3530 ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3531 ', SEARCH_TYPE: ' || p_search_type ||
3532 ', PRIMARY_CATEGORY_ID: ' || p_primary_category_id ||
3533 ', PRIMARY_CATEGORY_NAME: ' || p_primary_category_name ||
3534 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3535 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3536 ', SUPPLIER_ID: ' || p_supplier_id ||
3537 ', SUPPLIER: ' || p_supplier ||
3538 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3539 ', DESCRIPTION: ' || p_description ||
3540 ', PICTURE: ' || p_picture ||
3541 ', PICTURE_URL: ' || p_picture_url ||
3542 '] does not exist in ICX_CAT_ITEMS_TLP');
3543 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3544 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3545 ', LANGUAGE: ' || p_language ||
3546 ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3547 ', SEARCH_TYPE: ' || p_search_type ||
3548 ', PRIMARY_CATEGORY_ID: ' || p_primary_category_id ||
3549 ', PRIMARY_CATEGORY_NAME: ' || p_primary_category_name ||
3550 ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3551 ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3552 ', SUPPLIER_ID: ' || p_supplier_id ||
3553 ', SUPPLIER: ' || p_supplier ||
3554 ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3555 ', DESCRIPTION: ' || p_description ||
3556 ', PICTURE: ' || p_picture ||
3557 ', PICTURE_URL: ' || p_picture_url ||
3558 '] does not exist in ICX_CAT_ITEMS_TLP');
3559 RETURN FALSE;
3560 WHEN OTHERS THEN
3561 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsTLP-'||
3562 xErrLoc||' '||SQLERRM);
3563 raise ICX_POR_EXT_UTL.gException;
3564 END existItemsTLP;
3565
3566 FUNCTION notExistItemsTLP(p_rt_item_id IN NUMBER,
3567 p_language IN VARCHAR2)
3568 RETURN BOOLEAN
3569 IS
3570 xErrLoc PLS_INTEGER;
3571 xResult PLS_INTEGER;
3572 BEGIN
3573 xErrLoc:= 100;
3574 SELECT 0
3575 INTO xResult
3576 FROM icx_cat_items_tlp
3577 WHERE rt_item_id = p_rt_item_id
3578 AND language = p_language;
3579
3580 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3581 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3582 ', LANGUAGE: ' || p_language ||
3583 '] does not exist in ICX_CAT_ITEMS_TLP');
3584 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3585 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3586 ', LANGUAGE: ' || p_language ||
3587 '] does not exist in ICX_CAT_ITEMS_TLP');
3588
3589 xErrLoc:= 200;
3590 RETURN FALSE;
3591 EXCEPTION
3592 when NO_DATA_FOUND then
3593 xResult:= 0;
3594 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3595 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3596 ', LANGUAGE: ' || p_language ||
3597 '] exists in ICX_CAT_ITEMS_TLP');
3598 RETURN TRUE;
3599 WHEN OTHERS THEN
3600 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsTLP-'||
3601 xErrLoc||' '||SQLERRM);
3602 raise ICX_POR_EXT_UTL.gException;
3603 END notExistItemsTLP;
3604
3605 FUNCTION existCateoryItems(p_rt_item_id IN NUMBER,
3606 p_rt_category_id IN NUMBER)
3607 RETURN BOOLEAN
3608 IS
3609 xErrLoc PLS_INTEGER;
3610 xResult PLS_INTEGER;
3611 BEGIN
3612 xErrLoc:= 100;
3613 SELECT 0
3614 INTO xResult
3615 FROM icx_cat_category_items
3616 WHERE rt_item_id = p_rt_item_id
3617 AND rt_category_id = p_rt_category_id;
3618
3619 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3620 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3621 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3622 '] exists in ICX_CAT_CATEGORY_ITEMS');
3623
3624 xErrLoc:= 200;
3625 RETURN TRUE;
3626 EXCEPTION
3627 when NO_DATA_FOUND then
3628 xResult:= 0;
3629 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3630 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3631 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3632 '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3633 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3634 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3635 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3636 '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3637 RETURN FALSE;
3638 WHEN OTHERS THEN
3639 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCateoryItems-'||
3640 xErrLoc||' '||SQLERRM);
3641 raise ICX_POR_EXT_UTL.gException;
3642 END existCateoryItems;
3643
3644 FUNCTION notExistCateoryItems(p_rt_item_id IN NUMBER,
3645 p_rt_category_id IN NUMBER)
3646 RETURN BOOLEAN
3647 IS
3648 xErrLoc PLS_INTEGER;
3649 xResult PLS_INTEGER;
3650 BEGIN
3651 xErrLoc:= 100;
3652 SELECT 0
3653 INTO xResult
3654 FROM icx_cat_category_items
3655 WHERE rt_item_id = p_rt_item_id
3656 AND rt_category_id = p_rt_category_id;
3657
3658 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3659 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3660 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3661 '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3662 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3663 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3664 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3665 '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3666
3667 xErrLoc:= 200;
3668 RETURN FALSE;
3669 EXCEPTION
3670 when NO_DATA_FOUND then
3671 xResult:= 0;
3672 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3673 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3674 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3675 '] exists in ICX_CAT_CATEGORY_ITEMS');
3676 RETURN TRUE;
3677 WHEN OTHERS THEN
3678 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCateoryItems-'||
3679 xErrLoc||' '||SQLERRM);
3680 raise ICX_POR_EXT_UTL.gException;
3681 END notExistCateoryItems;
3682
3683 FUNCTION existExtItemsTLP(p_rt_item_id IN NUMBER,
3684 p_rt_category_id IN NUMBER)
3685 RETURN BOOLEAN
3686 IS
3687 xErrLoc PLS_INTEGER;
3688 xResult PLS_INTEGER;
3689 BEGIN
3690 xErrLoc:= 100;
3691 SELECT 0
3692 INTO xResult
3693 FROM icx_cat_ext_items_tlp
3694 WHERE rt_item_id = p_rt_item_id
3695 AND rt_category_id = p_rt_category_id
3696 AND ROWNUM = 1;
3697
3698 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3699 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3700 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3701 '] exists in ICX_CAT_EXT_ITEMS_TLP');
3702
3703 xErrLoc:= 200;
3704 RETURN TRUE;
3705 EXCEPTION
3706 when NO_DATA_FOUND then
3707 xResult:= 0;
3708 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3709 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3710 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3711 '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3712 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3713 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3714 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3715 '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3716 RETURN FALSE;
3717 WHEN OTHERS THEN
3718 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existExtItemsTLP-'||
3719 xErrLoc||' '||SQLERRM);
3720 raise ICX_POR_EXT_UTL.gException;
3721 END existExtItemsTLP;
3722
3723 FUNCTION notExistExtItemsTLP(p_rt_item_id IN NUMBER,
3724 p_rt_category_id IN NUMBER)
3725 RETURN BOOLEAN
3726 IS
3727 xErrLoc PLS_INTEGER;
3728 xResult PLS_INTEGER;
3729 BEGIN
3730 xErrLoc:= 100;
3731 SELECT 0
3732 INTO xResult
3733 FROM icx_cat_ext_items_tlp
3734 WHERE rt_item_id = p_rt_item_id
3735 AND rt_category_id = p_rt_category_id
3736 AND ROWNUM = 1;
3737
3738 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3739 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3740 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3741 '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3742 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3743 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3744 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3745 '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3746
3747 xErrLoc:= 200;
3748 RETURN FALSE;
3749 EXCEPTION
3750 when NO_DATA_FOUND then
3751 xResult:= 0;
3752 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3753 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3754 ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3755 '] exists in ICX_CAT_EXT_ITEMS_TLP');
3756 RETURN TRUE;
3757 WHEN OTHERS THEN
3758 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistExtItemsTLP-'||
3759 xErrLoc||' '||SQLERRM);
3760 raise ICX_POR_EXT_UTL.gException;
3761 END notExistExtItemsTLP;
3762
3763 FUNCTION existItemPrices(p_rt_item_id IN NUMBER,
3764 p_org_id IN VARCHAR2,
3765 p_price_type IN VARCHAR2,
3766 p_active_flag IN VARCHAR2,
3767 p_asl_id IN NUMBER,
3768 p_contract_id IN VARCHAR2,
3769 p_contract_line_id IN NUMBER,
3770 p_template_id IN VARCHAR2,
3771 p_template_line_id IN NUMBER,
3772 p_inventory_item_id IN VARCHAR2,
3773 p_mtl_category_id IN VARCHAR2,
3774 p_search_type IN VARCHAR2,
3775 p_unit_price IN VARCHAR2,
3776 p_currency IN VARCHAR2,
3777 p_unit_of_measure IN VARCHAR2,
3778 p_supplier_site_id IN VARCHAR2,
3779 p_supplier_site_code IN VARCHAR2,
3780 p_contract_num IN VARCHAR2,
3781 p_contract_line_num IN NUMBER,
3782 p_local_rt_item_id IN NUMBER)
3783 RETURN BOOLEAN
3784 IS
3785 xErrLoc PLS_INTEGER;
3786 xRtItemId NUMBER;
3787 xResult PLS_INTEGER;
3788 BEGIN
3789 BEGIN
3790 xErrLoc:= 100;
3791 SELECT local_rt_item_id
3792 INTO xRtItemId
3793 FROM icx_cat_item_prices
3794 WHERE rt_item_id = p_rt_item_id
3795 AND org_id = p_org_id
3796 AND price_type = p_price_type
3797 AND active_flag = p_active_flag
3798 AND asl_id = p_asl_id
3799 AND contract_id = p_contract_id
3800 AND contract_line_id = p_contract_line_id
3801 AND template_id = p_template_id
3802 AND template_line_id = p_template_line_id
3803 AND inventory_item_id = p_inventory_item_id
3804 AND mtl_category_id = p_mtl_category_id
3805 AND search_type = p_search_type
3806 AND (unit_price IS NULL AND p_unit_price IS NULL OR
3807 unit_price = p_unit_price)
3808 AND (currency IS NULL AND p_currency IS NULL OR
3809 currency = p_currency)
3810 AND (unit_of_measure IS NULL AND p_unit_of_measure IS NULL OR
3811 unit_of_measure = p_unit_of_measure)
3812 AND supplier_site_id = p_supplier_site_id
3813 AND (supplier_site_code IS NULL AND p_supplier_site_code IS NULL OR
3814 supplier_site_code = p_supplier_site_code)
3815 AND (contract_num IS NULL AND p_contract_num IS NULL OR
3816 contract_num = p_contract_num)
3817 AND (contract_line_num IS NULL AND p_contract_line_num IS NULL OR
3818 contract_line_num = p_contract_line_num);
3819
3820 IF (p_local_rt_item_id IS NULL OR
3821 p_local_rt_item_id = xRtItemId)
3822 THEN
3823 xResult := 1;
3824 ELSE
3825 xResult := 0;
3826 END IF;
3827 EXCEPTION
3828 WHEN NO_DATA_FOUND THEN
3829 xResult:= 0;
3830 END;
3831
3832 IF xResult = 1 THEN
3833 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3834 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3835 ', ORG_ID: ' || p_org_id ||
3836 ', PRICE_TYPE: ' || p_price_type ||
3837 ', ACTIVE_FLAG: ' || p_active_flag ||
3838 ', ASL_ID: ' || p_asl_id ||
3839 ', CONTRACT_ID: ' || p_contract_id ||
3840 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3841 ', TEMPLATE_ID: ' || p_template_id ||
3842 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3843 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3844 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
3845 ', SEARCH_TYPE: ' || p_search_type ||
3846 ', UNIT_PRICE: ' || p_unit_price ||
3847 ', CURRENCY: ' || p_currency ||
3848 ', UNIT_OF_MEASURE: ' || p_unit_of_measure ||
3849 ', SUPPLIER_SITE_ID: ' || p_supplier_site_id ||
3850 ', SUPPLIER_SITE_CODE: ' || p_supplier_site_code ||
3851 ', CONTRACT_NUM: ' || p_contract_num ||
3852 ', CONTRACT_LINE_NUM: ' || p_contract_line_num ||
3853 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
3854 '] exists in ICX_CAT_ITEM_PRICES');
3855
3856 xErrLoc:= 200;
3857 RETURN TRUE;
3858 ELSE
3859 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3860 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3861 ', ORG_ID: ' || p_org_id ||
3862 ', PRICE_TYPE: ' || p_price_type ||
3863 ', ACTIVE_FLAG: ' || p_active_flag ||
3864 ', ASL_ID: ' || p_asl_id ||
3865 ', CONTRACT_ID: ' || p_contract_id ||
3866 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3867 ', TEMPLATE_ID: ' || p_template_id ||
3868 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3869 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3870 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
3871 ', SEARCH_TYPE: ' || p_search_type ||
3872 ', UNIT_PRICE: ' || p_unit_price ||
3873 ', CURRENCY: ' || p_currency ||
3874 ', UNIT_OF_MEASURE: ' || p_unit_of_measure ||
3875 ', SUPPLIER_SITE_ID: ' || p_supplier_site_id ||
3876 ', SUPPLIER_SITE_CODE: ' || p_supplier_site_code ||
3877 ', CONTRACT_NUM: ' || p_contract_num ||
3878 ', CONTRACT_LINE_NUM: ' || p_contract_line_num ||
3879 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
3880 '] does not exist in ICX_CAT_ITEM_PRICES');
3881 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3882 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3883 ', ORG_ID: ' || p_org_id ||
3884 ', PRICE_TYPE: ' || p_price_type ||
3885 ', ACTIVE_FLAG: ' || p_active_flag ||
3886 ', ASL_ID: ' || p_asl_id ||
3887 ', CONTRACT_ID: ' || p_contract_id ||
3888 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3889 ', TEMPLATE_ID: ' || p_template_id ||
3890 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3891 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3892 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
3893 ', SEARCH_TYPE: ' || p_search_type ||
3894 ', UNIT_PRICE: ' || p_unit_price ||
3895 ', CURRENCY: ' || p_currency ||
3896 ', UNIT_OF_MEASURE: ' || p_unit_of_measure ||
3897 ', SUPPLIER_SITE_ID: ' || p_supplier_site_id ||
3898 ', SUPPLIER_SITE_CODE: ' || p_supplier_site_code ||
3899 ', CONTRACT_NUM: ' || p_contract_num ||
3900 ', CONTRACT_LINE_NUM: ' || p_contract_line_num ||
3901 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
3902 '] does not exist in ICX_CAT_ITEM_PRICES');
3903 RETURN FALSE;
3904 END IF;
3905 EXCEPTION
3906 WHEN OTHERS THEN
3907 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
3908 xErrLoc||' '||SQLERRM);
3909 raise ICX_POR_EXT_UTL.gException;
3910 END existItemPrices;
3911
3912 FUNCTION notExistItemPrices(p_rt_item_id IN NUMBER,
3913 p_org_id IN VARCHAR2,
3914 p_price_type IN VARCHAR2,
3915 p_active_flag IN VARCHAR2,
3916 p_asl_id IN NUMBER,
3917 p_contract_id IN VARCHAR2,
3918 p_contract_line_id IN NUMBER,
3919 p_template_id IN VARCHAR2,
3920 p_template_line_id IN NUMBER,
3921 p_inventory_item_id IN VARCHAR2)
3922 RETURN BOOLEAN
3923 IS
3924 xErrLoc PLS_INTEGER;
3925 xResult PLS_INTEGER;
3926 BEGIN
3927 xErrLoc:= 100;
3928 SELECT 0
3929 INTO xResult
3930 FROM icx_cat_item_prices
3931 WHERE rt_item_id = p_rt_item_id
3932 AND org_id = p_org_id
3933 AND price_type = p_price_type
3934 AND active_flag = p_active_flag
3935 AND asl_id = p_asl_id
3936 AND contract_id = p_contract_id
3937 AND contract_line_id = p_contract_line_id
3938 AND template_id = p_template_id
3939 AND template_line_id = p_template_line_id
3940 AND inventory_item_id = p_inventory_item_id;
3941
3942 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3943 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3944 ', ORG_ID: ' || p_org_id ||
3945 ', PRICE_TYPE: ' || p_price_type ||
3946 ', ACTIVE_FLAG: ' || p_active_flag ||
3947 ', ASL_ID: ' || p_asl_id ||
3948 ', CONTRACT_ID: ' || p_contract_id ||
3949 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3950 ', TEMPLATE_ID: ' || p_template_id ||
3951 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3952 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3953 '] exists in ICX_CAT_ITEM_PRICES');
3954 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3955 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3956 ', ORG_ID: ' || p_org_id ||
3957 ', PRICE_TYPE: ' || p_price_type ||
3958 ', ACTIVE_FLAG: ' || p_active_flag ||
3959 ', ASL_ID: ' || p_asl_id ||
3960 ', CONTRACT_ID: ' || p_contract_id ||
3961 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3962 ', TEMPLATE_ID: ' || p_template_id ||
3963 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3964 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3965 '] exists in ICX_CAT_ITEM_PRICES');
3966
3967 xErrLoc:= 200;
3968 RETURN FALSE;
3969 EXCEPTION
3970 when NO_DATA_FOUND then
3971 xResult:= 0;
3972 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3973 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3974 ', ORG_ID: ' || p_org_id ||
3975 ', PRICE_TYPE: ' || p_price_type ||
3976 ', ACTIVE_FLAG: ' || p_active_flag ||
3977 ', ASL_ID: ' || p_asl_id ||
3978 ', CONTRACT_ID: ' || p_contract_id ||
3979 ', CONTRACT_LINE_ID: ' || p_contract_line_id ||
3980 ', TEMPLATE_ID: ' || p_template_id ||
3981 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3982 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3983 '] does not exist in ICX_CAT_ITEM_PRICES');
3984 RETURN TRUE;
3985 WHEN OTHERS THEN
3986 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemPrices-'||
3987 xErrLoc||' '||SQLERRM);
3988 raise ICX_POR_EXT_UTL.gException;
3989 END notExistItemPrices;
3990
3991 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
3992 -- Add new function checkSuggestedQuantity to check if the suggested_quantity extracted matches
3993 -- the suggested_quantity inserted or updated previously in ipo_reqexpress_lines_all
3994
3995 FUNCTION checkSuggestedQuantity(p_rt_item_id IN NUMBER,
3996 p_org_id IN VARCHAR2,
3997 p_price_type IN VARCHAR2,
3998 p_active_flag IN VARCHAR2,
3999 p_template_id IN VARCHAR2,
4000 p_template_line_id IN NUMBER,
4001 p_inventory_item_id IN VARCHAR2,
4002 p_mtl_category_id IN VARCHAR2,
4003 p_suggested_quantity IN NUMBER,
4004 p_local_rt_item_id IN NUMBER)
4005 RETURN BOOLEAN
4006 IS
4007 xErrLoc PLS_INTEGER;
4008 xRtItemId NUMBER;
4009 xResult PLS_INTEGER;
4010 BEGIN
4011 BEGIN
4012 xErrLoc:= 100;
4013 SELECT local_rt_item_id
4014 INTO xRtItemId
4015 FROM icx_cat_item_prices
4016 WHERE rt_item_id = p_rt_item_id
4017 AND org_id = p_org_id
4018 AND price_type = p_price_type
4019 AND active_flag = p_active_flag
4020 AND template_id = p_template_id
4021 AND template_line_id = p_template_line_id
4022 AND inventory_item_id = p_inventory_item_id
4023 AND mtl_category_id = p_mtl_category_id
4024 AND suggested_quantity = p_suggested_quantity;
4025 IF (p_local_rt_item_id IS NULL OR
4026 p_local_rt_item_id = xRtItemId)
4027 THEN
4028 xResult := 1;
4029 ELSE
4030 xResult := 0;
4031 END IF;
4032 EXCEPTION
4033 WHEN NO_DATA_FOUND THEN
4034 xResult:= 0;
4035 END;
4036
4037 IF xResult = 1 THEN
4038 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
4039 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4040 ', ORG_ID: ' || p_org_id ||
4041 ', PRICE_TYPE: ' || p_price_type ||
4042 ', ACTIVE_FLAG: ' || p_active_flag ||
4043 ', TEMPLATE_ID: ' || p_template_id ||
4044 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
4045 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
4046 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
4047 ', SUGGESTED_QUANTITY: ' || p_suggested_quantity ||
4048 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
4049 '] exists in ICX_CAT_ITEM_PRICES');
4050
4051 xErrLoc:= 200;
4052 RETURN TRUE;
4053 ELSE
4054 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
4055 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4056 ', ORG_ID: ' || p_org_id ||
4057 ', PRICE_TYPE: ' || p_price_type ||
4058 ', ACTIVE_FLAG: ' || p_active_flag ||
4059 ', TEMPLATE_ID: ' || p_template_id ||
4060 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
4061 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
4062 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
4063 ', SUGGESTED_QUANTITY: ' || p_suggested_quantity ||
4064 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
4065 '] does not exist in ICX_CAT_ITEM_PRICES');
4066 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
4067 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4068 ', ORG_ID: ' || p_org_id ||
4069 ', PRICE_TYPE: ' || p_price_type ||
4070 ', ACTIVE_FLAG: ' || p_active_flag ||
4071 ', TEMPLATE_ID: ' || p_template_id ||
4072 ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
4073 ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
4074 ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
4075 ', SUGGESTED_QUANTITY: ' || p_suggested_quantity ||
4076 ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
4077 '] does not exist in ICX_CAT_ITEM_PRICES');
4078 RETURN FALSE;
4079 END IF;
4080 EXCEPTION
4081 WHEN OTHERS THEN
4082 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
4083 xErrLoc||' '||SQLERRM);
4084 raise ICX_POR_EXT_UTL.gException;
4085 END checkSuggestedQuantity;
4086
4087 END ICX_POR_EXT_TEST;