[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_EXT_DIAG
Source
1 PACKAGE BODY ICX_POR_EXT_DIAG AS
2 /* $Header: ICXEXTDB.pls 120.1 2006/01/10 11:36:01 sbgeorge noship $*/
3
4 --------------------------------------------------------------
5 -- Global Variables --
6 --------------------------------------------------------------
7 gOperatingUnitId NUMBER := -999;
8 gOperatingUnitName hr_operating_units.name%TYPE;
9 gInventoryOrgId NUMBER := -999;
10 gInventoryOrgName hr_all_organization_units.name%TYPE;
11
12 gCategorySetId NUMBER;
13 gValidateFlag VARCHAR2(1);
14 gStructureId NUMBER;
15
16 --------------------------------------------------------------
17 -- Construct Message Procedures --
18 --------------------------------------------------------------
19 -- Get operating unit name
20 FUNCTION getOperatingUnit(pOperatingUnitId IN NUMBER)
21 RETURN VARCHAR2
22 IS
23 xErrLoc PLS_INTEGER := 100;
24 BEGIN
25 IF (gOperatingUnitId <> pOperatingUnitId) THEN
26 SELECT organization_id,
27 name
28 INTO gOperatingUnitId,
29 gOperatingUnitName
30 FROM hr_operating_units
31 WHERE organization_id = pOperatingUnitId;
32 END IF;
33 RETURN gOperatingUnitName;
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 RETURN 'OU: '||pOperatingUnitId;
37 WHEN OTHERS THEN
38 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.getOperatingUnit-'||
39 xErrLoc||' '||SQLERRM);
40 raise ICX_POR_EXT_UTL.gException;
41 END getOperatingUnit;
42
43 -- Get inventory organization name
44 FUNCTION getInventoryOrg(pInventoryOrgId IN NUMBER)
45 RETURN VARCHAR2
46 IS
47 xErrLoc PLS_INTEGER := 100;
48 BEGIN
49 IF (pInventoryOrgId <> gInventoryOrgId) THEN
50 SELECT organization_id,
51 name
52 INTO gInventoryOrgId,
53 gInventoryOrgName
54 FROM hr_all_organization_units
55 WHERE organization_id = pInventoryOrgId;
56 END IF;
57 RETURN gInventoryOrgName;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 RETURN 'InvOrg: '||pInventoryOrgId;
61 WHEN OTHERS THEN
62 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.getInventoryOrg-'||
63 xErrLoc||' '||SQLERRM);
64 raise ICX_POR_EXT_UTL.gException;
65 END getInventoryOrg;
66
67 -- Construct message, usually pOrgName carries Operating Unit or
68 -- Inventory Organization, pDocName carries document number, and
69 -- pExtraValue carries line number, item number or description,
70 -- or supplier site code.
71 FUNCTION constructMessage(pStatus IN NUMBER,
72 pOrgName IN VARCHAR2,
73 pDocName IN VARCHAR2,
74 pExtraValue IN VARCHAR2,
75 pExtraValue2 IN VARCHAR2)
76 RETURN VARCHAR2
77 IS
78 xErrLoc PLS_INTEGER := 100;
79 xMessage VARCHAR2(4000);
80 xIcxSchema VARCHAR2(20);
81
82 BEGIN
83 xErrLoc := 100;
84 xIcxSchema := ICX_POR_EXT_UTL.getIcxSchema;
85 xErrLoc := 200;
86 IF pStatus = INVALID_TEMPLATE_LINE THEN
87 xMessage := 'Invalid template line ' || pDocName || ', ' ||
88 pExtraValue || ' in Operating Unit: ' || pOrgName;
89 RETURN xMessage;
90 ELSIF pStatus = INVALID_BLANKET_LINE THEN
91 xMessage := 'Invalid blanket line ' || pDocName || ', ' ||
92 pExtraValue || ' in Operating Unit: ' || pOrgName;
93 RETURN xMessage;
94 ELSIF pStatus = INVALID_QUOTATION_LINE THEN
95 xMessage := 'Invalid quotation line ' || pDocName || ', ' ||
96 pExtraValue || ' in Operating Unit: ' || pOrgName;
97 RETURN xMessage;
98 ELSIF pStatus = INVALID_ASL THEN
99 xMessage := 'Invalid ASL ' || pDocName || ', ' ||
100 pExtraValue || ' in Operating Unit: ' || pOrgName;
101 RETURN xMessage;
102 ELSIF pStatus = INVALID_ITEM THEN
103 xMessage := 'Invalid item ' || pExtraValue ||
104 ' in Operating Unit: ' || pOrgName;
105 RETURN xMessage;
106 END IF;
107
108 xErrLoc := 300;
109 IF pStatus IN (INACTIVE_TEMPLATE, TEMPLATE_INACTIVE_BLANKET,
110 TEMPLATE_INEFFECTIVE_BLANKET,
111 TEMPLATE_INACTIVE_BLANKET_LINE,
112 TEMPLATE_OUTSIDE_BLANKET)
113 THEN
114 IF pStatus = INACTIVE_TEMPLATE THEN
115 xErrLoc := 310;
116 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INACTIVE_TEMPLATE');
117 ELSIF pStatus = TEMPLATE_INACTIVE_BLANKET THEN
118 xErrLoc := 320;
119 fnd_message.set_name(xIcxSchema, 'ICX_CAT_TEMP_INACTIVE_BLANK');
120 ELSIF pStatus = TEMPLATE_INEFFECTIVE_BLANKET THEN
121 xErrLoc := 330;
122 fnd_message.set_name(xIcxSchema, 'ICX_CAT_TEMP_INEFFECTIVE_BLANK');
123 ELSIF pStatus = TEMPLATE_INACTIVE_BLANKET_LINE THEN
124 xErrLoc := 340;
125 fnd_message.set_name(xIcxSchema, 'ICX_CAT_TEMP_INACTIVE_BLANK_LN');
126 ELSIF pStatus = TEMPLATE_OUTSIDE_BLANKET THEN
127 xErrLoc := 350;
128 fnd_message.set_name(xIcxSchema, 'ICX_CAT_TEMP_OUTSIDE_BLANK_LN');
129 END IF;
130
131 xErrLoc := 360;
132 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
133 xErrLoc := 370;
134 fnd_message.set_token('TEMPLATE_NAME', pDocName);
135 xErrLoc := 380;
136 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
137 xErrLoc := 390;
138 xMessage := fnd_message.get;
139
140 xErrLoc := 395;
141 RETURN xMessage;
142 END IF;
143
144 xErrLoc := 400;
145 IF pStatus IN (INACTIVE_BLANKET, INEFFECTIVE_BLANKET,
146 INACTIVE_BLANKET_LINE, OUTSIDE_BLANKET,
147 GLOBAL_AGREEMENT_DISABLED,
148 GLOBAL_AGREEMENT_INVALID_ITEM,
149 GLOBAL_AGREEMENT_INVALID_UOM)
150 THEN
151 IF pStatus = INACTIVE_BLANKET THEN
152 xErrLoc := 410;
153 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INACTIVE_BLANKET');
154 ELSIF pStatus = INEFFECTIVE_BLANKET THEN
155 xErrLoc := 420;
156 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INEFFECTIVE_BLANKET');
157 ELSIF pStatus = INACTIVE_BLANKET_LINE THEN
158 xErrLoc := 430;
159 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INACTIVE_BLANKET_LINE');
160 ELSIF pStatus = OUTSIDE_BLANKET THEN
161 xErrLoc := 440;
162 fnd_message.set_name(xIcxSchema, 'ICX_CAT_OUTSIDE_BLANKET');
163 ELSIF pStatus = GLOBAL_AGREEMENT_DISABLED THEN
164 xErrLoc := 442;
165 fnd_message.set_name(xIcxSchema, 'ICX_CAT_GA_DISABLED');
166 ELSIF pStatus = GLOBAL_AGREEMENT_INVALID_ITEM THEN
167 xErrLoc := 444;
168 fnd_message.set_name(xIcxSchema, 'ICX_CAT_GA_INVALID_ITEM');
169 ELSIF pStatus = GLOBAL_AGREEMENT_INVALID_UOM THEN
170 xErrLoc := 446;
171 fnd_message.set_name(xIcxSchema, 'ICX_CAT_GA_INVALID_UOM');
172 END IF;
173
174 xErrLoc := 450;
175 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
176 xErrLoc := 460;
177 fnd_message.set_token('AGREEMENT_NUMBER', pDocName);
178 xErrLoc := 470;
179 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
180 xErrLoc := 480;
181 xMessage := fnd_message.get;
182
183 xErrLoc := 490;
184 RETURN xMessage;
185 END IF;
186
187 xErrLoc := 492;
188 IF pStatus = GLOBAL_AGREEMENT_INVALID_SITE THEN
189 fnd_message.set_name(xIcxSchema, 'ICX_CAT_GA_INVALID_SITE');
190 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
191 fnd_message.set_token('AGREEMENT_NUMBER', pDocName);
192 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
193 fnd_message.set_token('SUPPLIER_SITE_CODE', pExtraValue2);
194 xMessage := fnd_message.get;
195
196 RETURN xMessage;
197 END IF;
198
199 xErrLoc := 500;
200 IF pStatus IN (INACTIVE_QUOTATION, INEFFECTIVE_QUOTATION,
201 QUOTATION_NO_EFFECTIVE_PRICE)
202 THEN
203 IF pStatus = INACTIVE_QUOTATION THEN
204 xErrLoc := 510;
205 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INACTIVE_QUOTATION');
206 ELSIF pStatus = INEFFECTIVE_QUOTATION THEN
207 xErrLoc := 520;
208 fnd_message.set_name(xIcxSchema, 'ICX_CAT_INEFFECTIVE_QUOTATION');
209 ELSIF pStatus = QUOTATION_NO_EFFECTIVE_PRICE THEN
210 xErrLoc := 530;
211 fnd_message.set_name(xIcxSchema, 'ICX_CAT_QUOTE_NO_EFFECT_PRICE');
212 END IF;
213
214 xErrLoc := 550;
215 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
216 xErrLoc := 560;
217 fnd_message.set_token('QUOTATION_NUMBER', pDocName);
218 xErrLoc := 570;
219 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
220 xErrLoc := 580;
221 xMessage := fnd_message.get;
222
223 xErrLoc := 590;
224 RETURN xMessage;
225 END IF;
226
227 xErrLoc := 600;
228 IF pStatus IN (DISABLED_ASL, UNALLOWED_ASL,
229 ASL_NO_PRICE)
230 THEN
231 IF pStatus = DISABLED_ASL THEN
232 xErrLoc := 610;
233 fnd_message.set_name(xIcxSchema, 'ICX_CAT_DISABLED_ASL');
234 ELSIF pStatus = UNALLOWED_ASL THEN
235 xErrLoc := 620;
236 fnd_message.set_name(xIcxSchema, 'ICX_CAT_UNALLOWED_ASL');
237 ELSIF pStatus = ASL_NO_PRICE THEN
238 xErrLoc := 630;
239 fnd_message.set_name(xIcxSchema, 'ICX_CAT_ASL_NO_PRICE');
240 END IF;
241
242 xErrLoc := 650;
243 fnd_message.set_token('INVENTORY_ORGANIZATION', pOrgName);
244 xErrLoc := 660;
245 fnd_message.set_token('SUPPLIER_NAME', pDocName);
246 xErrLoc := 670;
247 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
248 xErrLoc := 680;
249 xMessage := fnd_message.get;
250
251 xErrLoc := 690;
252 RETURN xMessage;
253 END IF;
254
255 xErrLoc := 700;
256 IF pStatus IN (UNPURCHASABLE_OUTSIDE, NOTINTERNAL,
257 UNPURCHASABLE_NOTINTERNAL, ITEM_NO_PRICE)
258 THEN
259 IF pStatus = UNPURCHASABLE_OUTSIDE THEN
260 xErrLoc := 710;
261 fnd_message.set_name(xIcxSchema, 'ICX_CAT_UNPURCHAE_OUTSIDE');
262 ELSIF pStatus = NOTINTERNAL THEN
263 xErrLoc := 720;
264 fnd_message.set_name(xIcxSchema, 'ICX_CAT_NOINTERNAL');
265 ELSIF pStatus = UNPURCHASABLE_NOTINTERNAL THEN
266 xErrLoc := 730;
267 fnd_message.set_name(xIcxSchema, 'ICX_CAT_UNPURCHASE_NOINTERNAL');
268 ELSIF pStatus = ITEM_NO_PRICE THEN
269 xErrLoc := 740;
270 fnd_message.set_name(xIcxSchema, 'ICX_CAT_ITEM_NO_PRICE');
271 END IF;
272
273 xErrLoc := 750;
274 fnd_message.set_token('INVENTORY_ORGANIZATION', pOrgName);
275 xErrLoc := 770;
276 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
277 xErrLoc := 780;
278 xMessage := fnd_message.get;
279
280 xErrLoc := 790;
281 RETURN xMessage;
282 END IF;
283
284 xErrLoc := 800;
285 IF pStatus = CATEGORY_NOT_EXTRACTED THEN
286 xErrLoc := 810;
287 fnd_message.set_name(xIcxSchema, 'ICX_CAT_CATEGORY_NOT_EXTRACTED');
288 xErrLoc := 850;
289 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
290 xErrLoc := 860;
291 fnd_message.set_token('ITEM_CATEGORY', pDocName);
292 xErrLoc := 870;
293 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
294 xErrLoc := 880;
295 xMessage := fnd_message.get;
296
297 xErrLoc := 890;
298 RETURN xMessage;
299 END IF;
300
301 xErrLoc := 900;
302 IF pStatus = TEMPLATE_HEADER_NOT_EXTRACTED THEN
303 xErrLoc := 910;
304 fnd_message.set_name(xIcxSchema, 'ICX_CAT_TEMPHEAD_NOT_EXTRACTED');
305 xErrLoc := 950;
306 fnd_message.set_token('OPERATING_UNIT_NAME', pOrgName);
307 xErrLoc := 960;
308 fnd_message.set_token('TEMPLATE_NAME', pDocName);
309 xErrLoc := 970;
310 fnd_message.set_token('ITEM_NUMBER', pExtraValue);
311 xErrLoc := 980;
312 xMessage := fnd_message.get;
313
314 xErrLoc := 990;
315 RETURN xMessage;
316 END IF;
317
318 xErrLoc := 1000;
319 RETURN xMessage;
320 EXCEPTION
321 when others then
322 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.constructMessage-'||
323 xErrLoc||' '||SQLERRM);
324 raise ICX_POR_EXT_UTL.gException;
325 END constructMessage;
326
327 FUNCTION getPriceReport(p_document_type IN VARCHAR2,
328 p_org_id IN NUMBER,
329 p_inventory_organization_id IN NUMBER,
330 p_status IN NUMBER,
331 p_contract_num IN VARCHAR2,
332 p_internal_item_num IN VARCHAR2,
333 p_description IN VARCHAR2,
334 p_supplier_site_code IN VARCHAR2,
335 p_template_id IN VARCHAR2,
336 p_supplier IN VARCHAR2,
337 p_supplier_part_num IN VARCHAR2)
338 RETURN VARCHAR2
339 IS
340 xReport VARCHAR2(4000) := '';
341 xOperatingUnit hr_operating_units.name%TYPE;
342 xInventoryOrg hr_all_organization_units.name%TYPE;
343 xItemNum VARCHAR2(2000);
344 BEGIN
345 IF p_document_type IN (ICX_POR_EXT_ITEM.TEMPLATE_TYPE,
346 ICX_POR_EXT_ITEM.CONTRACT_TYPE,
347 ICX_POR_EXT_ITEM.INTERNAL_TEMPLATE_TYPE,
348 ICX_POR_EXT_ITEM.GLOBAL_AGREEMENT_TYPE)
349 THEN
350 xOperatingUnit := getOperatingUnit(p_org_id);
351 IF p_internal_item_num IS NULL THEN
352 IF p_supplier_part_num = TO_CHAR(ICX_POR_EXT_ITEM.NULL_NUMBER) THEN
353 xItemNum := p_description;
354 ELSE
355 xItemNum := p_supplier_part_num;
356 END IF;
357 ELSE
358 xItemNum := p_internal_item_num;
359 END IF;
360
361 IF p_document_type IN (ICX_POR_EXT_ITEM.CONTRACT_TYPE,
362 ICX_POR_EXT_ITEM.GLOBAL_AGREEMENT_TYPE)
363 THEN
364 xReport := constructMessage(p_status,
365 xOperatingUnit, p_contract_num,
366 xItemNum, p_supplier_site_code);
367 ELSE
368 xReport := constructMessage(p_status,
369 xOperatingUnit, p_template_id, xItemNum);
370 END IF;
371 ELSIF p_document_type IN (ICX_POR_EXT_ITEM.ASL_TYPE,
372 ICX_POR_EXT_ITEM.PURCHASING_ITEM_TYPE,
373 ICX_POR_EXT_ITEM.INTERNAL_ITEM_TYPE)
374 THEN
375 xInventoryOrg := getInventoryOrg(
376 p_inventory_organization_id);
377
378 IF p_document_type = ICX_POR_EXT_ITEM.ASL_TYPE THEN
379 xReport := constructMessage(p_status,
380 xInventoryOrg, p_supplier,
381 p_internal_item_num);
382 ELSE
383 xReport := constructMessage(p_status,
384 xInventoryOrg, NULL, p_internal_item_num);
385 END IF;
386 END IF;
387 RETURN xReport;
388 END getPriceReport;
389
390 --------------------------------------------------------------
391 -- Check Classification Procedures --
392 --------------------------------------------------------------
393 -- Check category status
394 FUNCTION checkCategoryStatus(pValue IN VARCHAR2,
395 pMessage OUT NOCOPY VARCHAR2)
396 RETURN NUMBER
397 IS
398 xErrLoc PLS_INTEGER := 100;
399 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
400 xCategoryId NUMBER;
401 xWebEnabledFlag VARCHAR2(1);
402 xStartDate DATE;
403 xEndDate DATE;
404 xDisableDate DATE;
405
406 BEGIN
407 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
408 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
409 'checkCategoryStatus(pValue: ' || pValue || ')');
410 END IF;
411
412 xErrLoc := 100;
413 BEGIN
414 SELECT category_id,
415 web_status,
416 start_date_active,
417 end_date_active,
418 disable_date
419 INTO xCategoryId,
420 xWebEnabledFlag,
421 xStartDate,
422 xEndDate,
423 xDisableDate
424 FROM mtl_categories_kfv
425 WHERE structure_id = gStructureId
426 AND concatenated_segments = pValue;
427 EXCEPTION
428 WHEN OTHERS THEN
429 xStatus := INVALID_CATEGORY;
430 pMessage := 'Invalid category: ' || pValue;
431 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
432 RETURN xStatus;
433 END;
434 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
435 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
436 'Category found with category_id: ' || xCategoryId ||
437 ', xWebEnabledFlag: ' || NVL(xWebEnabledFlag, 'NULL') ||
438 ', xStartDate: ' || NVL(TO_CHAR(xStartDate, 'MM/DD/YY HH24:MI:SS'),
439 'NULL') || ', xEndDate: ' ||
440 NVL(TO_CHAR(xEndDate, 'MM/DD/YY HH24:MI:SS'), 'NULL') ||
441 ', xDisableDate: ' || NVL(TO_CHAR(xDisableDate, 'MM/DD/YY HH24:MI:SS'),
442 'NULL'));
443 END IF;
444
445 xErrLoc := 220;
446 IF xWebEnabledFlag <> 'Y' THEN
447 xStatus := NOT_WEBENABLED_CATEGORY;
448 pMessage := 'Not webenabled category: ' || xCategoryId;
449 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
450 RETURN xStatus;
451 END IF;
452
453 xErrLoc := 240;
454 IF (NVL(xStartDate, SYSDATE) > SYSDATE OR
455 NVL(xEndDate, SYSDATE+1) <= SYSDATE OR
456 NVL(xDisableDate, SYSDATE+1) <= SYSDATE)
457 THEN
458 xStatus := INACTIVE_CATEGORY;
459 pMessage := 'Inactive category: ' || xCategoryId;
460 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
461 RETURN xStatus;
462 END IF;
463
464 xErrLoc := 300;
465 IF gValidateFlag = 'Y' THEN
466 BEGIN
467 SELECT VALID_FOR_EXTRACT
468 INTO xStatus
469 FROM mtl_category_set_valid_cats
470 WHERE category_set_id = gCategorySetId
471 AND category_id = xCategoryId;
472 EXCEPTION
473 WHEN OTHERS THEN
474 xStatus := INVALID_CATEGORY_SET;
475 pMessage := 'Invalid category set: ' || xCategoryId;
476 RETURN xStatus;
477 END;
478 END IF;
479
480 xErrLoc := 400;
481 RETURN xStatus;
482 EXCEPTION
483 when others then
484 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkCategoryStatus-'||
485 xErrLoc||' '||SQLERRM);
486 raise ICX_POR_EXT_UTL.gException;
487 END checkCategoryStatus;
488
489 -- Check template header status
490 FUNCTION checkTemplateHeaderStatus(pValue IN VARCHAR2,
491 pMessage OUT NOCOPY VARCHAR2)
492 RETURN NUMBER
493 IS
494 xErrLoc PLS_INTEGER := 100;
495 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
496 xInactiveDate DATE;
497
498 BEGIN
499 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
500 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
501 'checkTemplateHeaderStatus(pValue: ' || pValue || ')');
502 END IF;
503
504 xErrLoc := 100;
505 BEGIN
506 SELECT inactive_date
507 INTO xInactiveDate
508 FROM po_reqexpress_headers_all
509 WHERE express_name = pValue
510 AND rownum = 1;
511 EXCEPTION
512 WHEN OTHERS THEN
513 xStatus := INVALID_TEMPLATE_HEADER;
514 pMessage := 'Invalid template header: ' || pValue;
515 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
516 RETURN xStatus;
517 END;
518 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
519 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
520 'Template header found with xInactiveDate: ' ||
521 NVL(TO_CHAR(xInactiveDate, 'MM/DD/YY HH24:MI:SS'), 'NULL'));
522 END IF;
523
524 xErrLoc := 200;
525 IF NVL(xInactiveDate, SYSDATE+1) <= SYSDATE THEN
526 xStatus := INACTIVE_TEMPLATE_HEADER;
527 pMessage := 'Inactive template header: ' || pValue;
528 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
529 RETURN xStatus;
530 END IF;
531
532 xErrLoc := 400;
533 RETURN xStatus;
534 EXCEPTION
535 when others then
536 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkTemplateHeaderStatus-'||
537 xErrLoc||' '||SQLERRM);
538 raise ICX_POR_EXT_UTL.gException;
539 END checkTemplateHeaderStatus;
540
541
542 --------------------------------------------------------------
543 -- Check Item Procedures --
544 --------------------------------------------------------------
545 -- Check extracted category status
546 FUNCTION checkExtCategoryStatus(pCategoryId IN NUMBER,
547 pItemNum IN VARCHAR2,
548 pMessage OUT NOCOPY VARCHAR2)
549 RETURN NUMBER
550 IS
551 xErrLoc PLS_INTEGER := 100;
552 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
553 xCategoryName mtl_categories_kfv.concatenated_segments%TYPE;
554 xRtCategoryId NUMBER;
555
556 BEGIN
557 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
558 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
559 'checkExtCategoryStatus(pCategoryId: ' || pCategoryId || ')');
560 END IF;
561
562 xErrLoc := 100;
563 BEGIN
564 SELECT VALID_FOR_EXTRACT,
565 rt_category_id
566 INTO xStatus,
567 xRtCategoryId
568 FROM icx_cat_categories_tl
569 WHERE key = to_char(pCategoryId)
570 AND type = ICX_POR_EXT_CLASS.CATEGORY_TYPE
571 AND rownum = 1;
572 EXCEPTION
573 WHEN OTHERS THEN
574 xStatus := CATEGORY_NOT_EXTRACTED;
575 SELECT concatenated_segments
576 INTO xCategoryName
577 FROM mtl_categories_kfv
578 WHERE category_id = pCategoryId
579 AND rownum = 1;
580
581 xErrLoc := 150;
582 pMessage := constructMessage(xStatus, gOperatingUnitName,
583 xCategoryName, pItemNum);
584 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
585 RETURN xStatus;
586 END;
587 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
588 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
589 'Category extracted as rt_category_id: '||xRtCategoryId);
590 END IF;
591
592 xErrLoc := 400;
593 RETURN xStatus;
594 EXCEPTION
595 when others then
596 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkExtCategoryStatus-'||
597 xErrLoc||' '||SQLERRM);
598 raise ICX_POR_EXT_UTL.gException;
599 END checkExtCategoryStatus;
600
601 -- Check extracted template header status
602 FUNCTION checkExtTemplateHeaderStatus(pTemplateName IN VARCHAR2,
603 pItemNum IN VARCHAR2,
604 pMessage OUT NOCOPY VARCHAR2)
605 RETURN NUMBER
606 IS
607 xErrLoc PLS_INTEGER := 100;
608 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
609 xRtCategoryId NUMBER;
610
611 BEGIN
612 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
613 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
614 'checkExtTemplateHeaderStatus(pTemplateName: ' || pTemplateName || ')');
615 END IF;
616
617 xErrLoc := 100;
618 BEGIN
619 SELECT VALID_FOR_EXTRACT,
620 rt_category_id
621 INTO xStatus,
622 xRtCategoryId
623 FROM icx_cat_categories_tl
624 WHERE key = pTemplateName||'_tmpl'
625 AND type = ICX_POR_EXT_CLASS.TEMPLATE_HEADER_TYPE
626 AND rownum = 1;
627 EXCEPTION
628 WHEN OTHERS THEN
629 xStatus := TEMPLATE_HEADER_NOT_EXTRACTED;
630
631 xErrLoc := 150;
632 pMessage := constructMessage(xStatus, gOperatingUnitName,
633 pTemplateName, pItemNum);
634 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
635 RETURN xStatus;
636 END;
637 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
638 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
639 'Template header extracted as rt_category_id: '||xRtCategoryId);
640 END IF;
641
642 xErrLoc := 400;
643 RETURN xStatus;
644 EXCEPTION
645 when others then
646 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkExtTemplateHeaderStatus-'||
647 xErrLoc||' '||SQLERRM);
648 raise ICX_POR_EXT_UTL.gException;
649 END checkExtTemplateHeaderStatus;
650
651 -- Check blanket/quotation status
652 FUNCTION checkContractLineStatus(pType IN VARCHAR2,
653 pOperatingUnitId IN NUMBER,
654 pPONum IN VARCHAR2,
655 pLineNum IN VARCHAR2,
656 pMessage OUT NOCOPY VARCHAR2)
657 RETURN NUMBER
658 IS
659 xErrLoc PLS_INTEGER := 100;
660 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
661 xContractLineId NUMBER;
662 xItemNum VARCHAR2(700);
663 xCategoryId NUMBER;
664 xOperatingUnit hr_operating_units.name%TYPE;
665
666 BEGIN
667 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
668 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
669 'checkContractLineStatus(pType: ' || pType ||
670 ', pOperatingUnitId: ' || pOperatingUnitId ||
671 ', pPONum: ' || pPONum ||
672 ', pLineNum: ' || pLineNum || ')');
673 END IF;
674
675 xErrLoc := 100;
676 xOperatingUnit := getOperatingUnit(pOperatingUnitId);
677
678 xErrLoc := 120;
679 BEGIN
680 SELECT pl.po_line_id,
681 getContractLineStatus(pl.po_line_id,
682 ICX_POR_EXT_TEST.gTestMode),
683 NVL(mi.concatenated_segments, pl.item_description),
684 pl.category_id
685 INTO xContractLineId,
686 xStatus,
687 xItemNum,
688 xCategoryId
689 FROM po_headers_all ph,
690 po_lines_all pl,
691 financials_system_params_all fsp,
692 mtl_system_items_kfv mi
693 WHERE ph.segment1 = pPONum
694 AND (ph.org_id is null and pOperatingUnitId is null or ph.org_id = pOperatingUnitId)
695 AND ph.type_lookup_code = pType
696 AND ph.po_header_id = pl.po_header_id
697 AND pl.line_num = TO_NUMBER(pLineNum)
698 AND (ph.org_id is null and fsp.org_id is null or ph.org_id = fsp.org_id)
699 AND fsp.inventory_organization_id = NVL(mi.organization_id,
700 fsp.inventory_organization_id)
701 AND pl.item_id = mi.inventory_item_id (+)
702 AND rownum = 1;
703 EXCEPTION
704 WHEN OTHERS THEN
705 IF pType = 'BLANKET' THEN
706 xStatus := INVALID_BLANKET_LINE;
707 ELSE
708 xStatus := INVALID_QUOTATION_LINE;
709 END IF;
710 xErrLoc := 150;
711 pMessage := constructMessage(xStatus, xOperatingUnit,
712 pPONum, pLineNum);
713 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
714 RETURN xStatus;
715 END;
716 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
717 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
718 'Contract line found ' || xContractLineId ||
719 ' with status: ' || xStatus);
720 END IF;
721
722 xErrLoc := 200;
723 IF (xStatus <> VALID_FOR_EXTRACT) THEN
724 pMessage := constructMessage(xStatus, xOperatingUnit,
725 pPONum, xItemNum);
726 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
727 RETURN xStatus;
728 END IF;
729
730 xErrLoc := 400;
731 xStatus := checkExtCategoryStatus(xCategoryId, xItemNum, pMessage);
732 RETURN xStatus;
733 EXCEPTION
734 when others then
735 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkContractLineStatus-'||
736 xErrLoc||' '||SQLERRM);
737 raise ICX_POR_EXT_UTL.gException;
738 END checkContractLineStatus;
739
740 -- Check template status
741 FUNCTION checkTemplateLineStatus(pOperatingUnitId IN NUMBER,
742 pTemplateName IN VARCHAR2,
743 pLineNum IN VARCHAR2,
744 pMessage OUT NOCOPY VARCHAR2)
745 RETURN NUMBER
746 IS
747 xErrLoc PLS_INTEGER := 100;
748 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
749 xItemNum VARCHAR2(700);
750 xCategoryId NUMBER;
751 xOperatingUnit hr_operating_units.name%TYPE;
752
753 BEGIN
754 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
755 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
756 'checkTemplateLineStatus(pOperatingUnitId: ' || pOperatingUnitId ||
757 ', pTemplateName: ' || pTemplateName ||
758 ', pLineNum: ' || pLineNum || ')');
759 END IF;
760
761 xErrLoc := 100;
762 xOperatingUnit := getOperatingUnit(pOperatingUnitId);
763
764 xErrLoc := 120;
765 BEGIN
766 SELECT getTemplateLineStatus(prl.express_name,
767 prl.sequence_num,
768 prl.org_id,
769 prh.inactive_date,
770 prl.po_line_id,
771 ICX_POR_EXT_TEST.gTestMode),
772 NVL(mi.concatenated_segments, prl.item_description),
773 prl.category_id
774 INTO xStatus,
775 xItemNum,
776 xCategoryId
777 FROM po_reqexpress_headers_all prh,
778 po_reqexpress_lines_all prl,
779 financials_system_params_all fsp,
780 mtl_system_items_kfv mi
781 WHERE prh.express_name = pTemplateName
782 AND (prh.org_id is null and pOperatingUnitId is null or prh.org_id = pOperatingUnitId)
783 AND prl.express_name = prh.express_name
784 AND (prh.org_id is null and prl.org_id is null or prl.org_id = prh.org_id)
785 AND prl.sequence_num = TO_NUMBER(pLineNum)
786 AND (prh.org_id is null and fsp.org_id is null or prh.org_id = fsp.org_id)
787 AND fsp.inventory_organization_id = NVL(mi.organization_id,
788 fsp.inventory_organization_id)
789 AND prl.item_id = mi.inventory_item_id (+)
790 AND rownum = 1;
791 EXCEPTION
792 WHEN OTHERS THEN
793 xStatus := INVALID_TEMPLATE_LINE;
794 xErrLoc := 150;
795 pMessage := constructMessage(xStatus, xOperatingUnit,
796 pTemplateName, pLineNum);
797 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
798 RETURN xStatus;
799 END;
800 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
801 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
802 'Tempalte line found with status: ' || xStatus);
803 END IF;
804
805 xErrLoc := 200;
806 IF (xStatus <> VALID_FOR_EXTRACT) THEN
807 pMessage := constructMessage(xStatus, xOperatingUnit,
808 pTemplateName, xItemNum);
809 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
810 RETURN xStatus;
811 END IF;
812
813 xErrLoc := 400;
814 xStatus := checkExtCategoryStatus(xCategoryId, xItemNum, pMessage);
815 xErrLoc := 420;
816 IF xStatus = VALID_FOR_EXTRACT THEN
817 xStatus := checkExtTemplateHeaderStatus(pTemplateName,
818 xItemNum, pMessage);
819 END IF;
820
821 RETURN xStatus;
822 EXCEPTION
823 when others then
824 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkTemplateLineStatus-'||
825 xErrLoc||' '||SQLERRM);
826 raise ICX_POR_EXT_UTL.gException;
827 END checkTemplateLineStatus;
828
829 -- Check ASL status
830 FUNCTION checkASLStatus(pOperatingUnitId IN NUMBER,
831 pASLId IN VARCHAR2,
832 pMessage OUT NOCOPY VARCHAR2)
833 RETURN NUMBER
834 IS
835 xErrLoc PLS_INTEGER := 100;
836 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
837 xSupplier po_vendors.vendor_name%TYPE;
838 xItemNum VARCHAR2(700);
839 xOrgName hr_all_organization_units.name%TYPE;
840 xCategoryId NUMBER;
841 xOperatingUnit hr_operating_units.name%TYPE;
842
843 BEGIN
844 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
845 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
846 'checkASLStatus(pOperatingUnitId: ' || pOperatingUnitId ||
847 ', pASLId: ' || pASLId || ')');
848 END IF;
849
850 xErrLoc := 100;
851 xOperatingUnit := getOperatingUnit(pOperatingUnitId);
852
853 xErrLoc := 120;
854 BEGIN
855 SELECT getASLStatus(pasl.asl_id,
856 pasl.disable_flag,
857 pasl.asl_status_id,
858 mi.list_price_per_unit,
859 ICX_POR_EXT_TEST.gTestMode),
860 pv.vendor_name,
861 mi.concatenated_segments,
862 NVL(pasl.category_id, mic.category_id),
863 hr.name
864 INTO xStatus,
865 xSupplier,
866 xItemNum,
867 xCategoryId,
868 xOrgName
869 FROM po_approved_supplier_list pasl,
870 po_vendors pv,
871 financials_system_params_all fsp,
872 mtl_system_items_kfv mi,
873 mtl_item_categories mic,
874 hr_all_organization_units hr
875 WHERE pasl.asl_id = pASLId
876 AND (fsp.org_id is null and pOperatingUnitId is null or fsp.org_id = pOperatingUnitId)
877 AND fsp.inventory_organization_id = pasl.owning_organization_id
878 AND pasl.vendor_id = pv.vendor_id
879 AND fsp.inventory_organization_id = mi.organization_id
880 AND pasl.item_id = mi.inventory_item_id
881 AND mic.category_set_id = gCategorySetId
882 AND mic.inventory_item_id = mi.inventory_item_id
883 AND mic.organization_id = mi.organization_id
884 AND mi.organization_id = hr.organization_id
885 AND rownum = 1;
886 EXCEPTION
887 WHEN OTHERS THEN
888 xStatus := INVALID_ASL;
889 xErrLoc := 150;
890 pMessage := constructMessage(xStatus, xOperatingUnit,
891 pASLId, NULL);
892 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
893 RETURN xStatus;
894 END;
895 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
896 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
897 'ASL found with status: ' || xStatus);
898 END IF;
899
900 xErrLoc := 200;
901 IF (xStatus <> VALID_FOR_EXTRACT) THEN
902 pMessage := constructMessage(xStatus, xOrgName,
903 xSupplier, xItemNum);
904 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
905 RETURN xStatus;
906 END IF;
907
908 xErrLoc := 400;
909 xStatus := checkExtCategoryStatus(xCategoryId, xItemNum, pMessage);
910 RETURN xStatus;
911 EXCEPTION
912 when others then
913 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkASLStatus-'||
914 xErrLoc||' '||SQLERRM);
915 raise ICX_POR_EXT_UTL.gException;
916 END checkASLStatus;
917
918 -- Check master item status
919 FUNCTION checkMasterStatus(pOperatingUnitId IN NUMBER,
920 pItemNum IN VARCHAR2,
921 pMessage OUT NOCOPY VARCHAR2)
922 RETURN NUMBER
923 IS
924 xErrLoc PLS_INTEGER := 100;
925 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
926 xStatus1 PLS_INTEGER;
927 xStatus2 PLS_INTEGER;
928 xItemId NUMBER;
929 xCategoryId NUMBER;
930 xOrgName hr_all_organization_units.name%TYPE;
931 xLoadPurchasing VARCHAR2(1);
932 xLoadInternal VARCHAR2(1);
933 xOperatingUnit hr_operating_units.name%TYPE;
934
935 BEGIN
936 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
937 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
938 'checkMasterStatus(pOperatingUnitId: ' || pOperatingUnitId ||
939 ', pItemNum: ' || pItemNum || ')');
940 END IF;
941
942 xErrLoc := 100;
943 xOperatingUnit := getOperatingUnit(pOperatingUnitId);
944
945 xErrLoc := 120;
946 BEGIN
947 SELECT getPurchasingItemStatus(mi.purchasing_enabled_flag,
948 mi.outside_operation_flag,
949 mi.list_price_per_unit,
950 ICX_POR_EXT_TEST.gTestMode),
951 getInternalItemStatus(mi.internal_order_enabled_flag,
952 ICX_POR_EXT_TEST.gTestMode),
953 mi.inventory_item_id,
954 mic.category_id,
955 hr.name
956 INTO xStatus1,
957 xStatus2,
958 xItemId,
959 xCategoryId,
960 xOrgName
961 FROM financials_system_params_all fsp,
962 mtl_system_items_kfv mi,
963 mtl_item_categories mic,
964 hr_all_organization_units hr
965 WHERE (fsp.org_id is null and pOperatingUnitId is null or fsp.org_id = pOperatingUnitId)
966 AND fsp.inventory_organization_id = mi.organization_id
967 AND mi.concatenated_segments = pItemNum
968 AND mic.category_set_id = gCategorySetId
969 AND mic.inventory_item_id = mi.inventory_item_id
970 AND mic.organization_id = mi.organization_id
971 AND mi.organization_id = hr.organization_id
972 AND rownum = 1;
973 EXCEPTION
974 WHEN OTHERS THEN
975 xStatus := INVALID_ITEM;
976 xErrLoc := 150;
977 pMessage := constructMessage(xStatus, xOperatingUnit,
978 NULL, pItemNum);
979 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
980 RETURN xStatus;
981 END;
982 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
983 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
984 'Item found ' || xItemId || ' with purchasing status: ' ||
985 xStatus1 || ', internal status: ' || xStatus2);
986 END IF;
987
988 xErrLoc := 200;
989 SELECT NVL(load_item_master, 'N'),
990 NVL(load_internal_item, 'N')
991 INTO xLoadPurchasing,
992 xLoadInternal
993 FROM icx_por_loader_values;
994
995 xErrLoc := 240;
996 IF (xLoadPurchasing = 'Y' AND xLoadInternal = 'Y') THEN
997 IF (xStatus1 <> VALID_FOR_EXTRACT AND
998 xStatus2 <> VALID_FOR_EXTRACT)
999 THEN
1000 xStatus := UNPURCHASABLE_NOTINTERNAL;
1001 ELSE
1002 xStatus := VALID_FOR_EXTRACT;
1003 END IF;
1004 ELSIF xLoadPurchasing = 'Y' THEN
1005 xStatus := xStatus1;
1006 ELSIF xLoadInternal = 'Y' THEN
1007 xStatus := xStatus2;
1008 END IF;
1009
1010 IF (xStatus <> VALID_FOR_EXTRACT) THEN
1011 pMessage := constructMessage(xStatus, xOrgName,
1012 NULL, pItemNum);
1013 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
1014 RETURN xStatus;
1015 END IF;
1016
1017 xErrLoc := 400;
1018 xStatus := checkExtCategoryStatus(xCategoryId, pItemNum, pMessage);
1019 RETURN xStatus;
1020 EXCEPTION
1021 when others then
1022 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkMasterStatus-'||
1023 xErrLoc||' '||SQLERRM);
1024 raise ICX_POR_EXT_UTL.gException;
1025 END checkMasterStatus;
1026
1027 FUNCTION getStatusString(pStatus IN NUMBER)
1028 RETURN VARCHAR2
1029 IS
1030 xStatusStr VARCHAR2(80);
1031 BEGIN
1032 IF pStatus = INVALID_BUSINESS_GROUP THEN
1033 xStatusStr := 'INVALID_BUSINESS_GROUP';
1034 ELSIF pStatus = INVALID_OPERATING_UNIT THEN
1035 xStatusStr := 'INVALID_OPERATING_UNIT';
1036 ELSIF pStatus = INVALID_TYPE THEN
1037 xStatusStr := 'INVALID_TYPE';
1038 ELSIF pStatus = INVALID_CATEGORY THEN
1039 xStatusStr := 'INVALID_CATEGORY';
1040 ELSIF pStatus = INVALID_TEMPLATE_HEADER THEN
1041 xStatusStr := 'INVALID_TEMPLATE_HEADER';
1042 ELSIF pStatus = INVALID_TEMPLATE_LINE THEN
1043 xStatusStr := 'INVALID_TEMPLATE_LINE';
1044 ELSIF pStatus = INVALID_BLANKET_LINE THEN
1045 xStatusStr := 'INVALID_BLANKET_LINE';
1046 ELSIF pStatus = INVALID_QUOTATION_LINE THEN
1047 xStatusStr := 'INVALID_QUOTATION_LINE';
1048 ELSIF pStatus = INVALID_ASL THEN
1049 xStatusStr := 'INVALID_ASL';
1050 ELSIF pStatus = INVALID_ITEM THEN
1051 xStatusStr := 'INVALID_ITEM';
1052 ELSIF pStatus = VALID_FOR_EXTRACT THEN
1053 xStatusStr := 'VALID_FOR_EXTRACT';
1054 ELSIF pStatus = INACTIVE_TEMPLATE THEN
1055 xStatusStr := 'INACTIVE_TEMPLATE';
1056 ELSIF pStatus = TEMPLATE_INACTIVE_BLANKET THEN
1057 xStatusStr := 'TEMPLATE_INACTIVE_BLANKET';
1058 ELSIF pStatus = TEMPLATE_INEFFECTIVE_BLANKET THEN
1059 xStatusStr := 'TEMPLATE_INEFFECTIVE_BLANKET';
1060 ELSIF pStatus = TEMPLATE_INACTIVE_BLANKET_LINE THEN
1061 xStatusStr := 'TEMPLATE_INACTIVE_BLANKET_LINE';
1062 ELSIF pStatus = TEMPLATE_OUTSIDE_BLANKET THEN
1063 xStatusStr := 'TEMPLATE_OUTSIDE_BLANKET';
1064 ELSIF pStatus = INACTIVE_BLANKET THEN
1065 xStatusStr := 'INACTIVE_BLANKET';
1066 ELSIF pStatus = INEFFECTIVE_BLANKET THEN
1067 xStatusStr := 'INEFFECTIVE_BLANKET';
1068 ELSIF pStatus = INACTIVE_BLANKET_LINE THEN
1069 xStatusStr := 'INACTIVE_BLANKET_LINE';
1070 ELSIF pStatus = OUTSIDE_BLANKET THEN
1071 xStatusStr := 'OUTSIDE_BLANKET';
1072 ELSIF pStatus = INACTIVE_QUOTATION THEN
1073 xStatusStr := 'INACTIVE_QUOTATION';
1074 ELSIF pStatus = QUOTATION_NO_EFFECTIVE_PRICE THEN
1075 xStatusStr := 'QUOTATION_NO_EFFECTIVE_PRICE';
1076 ELSIF pStatus = INEFFECTIVE_QUOTATION THEN
1077 xStatusStr := 'INEFFECTIVE_QUOTATION';
1078 ELSIF pStatus = DISABLED_ASL THEN
1079 xStatusStr := 'DISABLED_ASL';
1080 ELSIF pStatus = UNALLOWED_ASL THEN
1081 xStatusStr := 'UNALLOWED_ASL';
1082 ELSIF pStatus = ASL_NO_PRICE THEN
1083 xStatusStr := 'ASL_NO_PRICE';
1084 ELSIF pStatus = UNPURCHASABLE_OUTSIDE THEN
1085 xStatusStr := 'UNPURCHASABLE_OUTSIDE';
1086 ELSIF pStatus = NOTINTERNAL THEN
1087 xStatusStr := 'NOTINTERNAL';
1088 ELSIF pStatus = UNPURCHASABLE_NOTINTERNAL THEN
1089 xStatusStr := 'UNPURCHASABLE_NOTINTERNAL';
1090 ELSIF pStatus = ITEM_NO_PRICE THEN
1091 xStatusStr := 'ITEM_NO_PRICE';
1092 ELSIF pStatus = CATEGORY_NOT_EXTRACTED THEN
1093 xStatusStr := 'CATEGORY_NOT_EXTRACTED';
1094 ELSIF pStatus = TEMPLATE_HEADER_NOT_EXTRACTED THEN
1095 xStatusStr := 'TEMPLATE_HEADER_NOT_EXTRACTED';
1096 ELSIF pStatus = NOT_WEBENABLED_CATEGORY THEN
1097 xStatusStr := 'NOT_WEBENABLED_CATEGORY';
1098 ELSIF pStatus = INACTIVE_CATEGORY THEN
1099 xStatusStr := 'INACTIVE_CATEGORY';
1100 ELSIF pStatus = INVALID_CATEGORY_SET THEN
1101 xStatusStr := 'INVALID_CATEGORY_SET';
1102 ELSIF pStatus = INACTIVE_TEMPLATE_HEADER THEN
1103 xStatusStr := 'INACTIVE_TEMPLATE_HEADER';
1104 ELSIF pStatus = GLOBAL_AGREEMENT_DISABLED THEN
1105 xStatusStr := 'GLOBAL_AGREEMENT_DISABLED';
1106 ELSIF pStatus = GLOBAL_AGREEMENT_INVALID_SITE THEN
1107 xStatusStr := 'GLOBAL_AGREEMENT_INVALID_SITE';
1108 ELSIF pStatus = GLOBAL_AGREEMENT_INVALID_ITEM THEN
1109 xStatusStr := 'GLOBAL_AGREEMENT_INVALID_ITEM';
1110 ELSIF pStatus = GLOBAL_AGREEMENT_INVALID_UOM THEN
1111 xStatusStr := 'GLOBAL_AGREEMENT_INVALID_UOM';
1112 END IF;
1113
1114 RETURN xStatusStr;
1115
1116 END getStatusString;
1117
1118
1119 --------------------------------------------------------------
1120 -- Main Check Procedures --
1121 --------------------------------------------------------------
1122 -- Check classification status
1123 FUNCTION checkClassStatus(pType IN VARCHAR2,
1124 pValue IN VARCHAR2,
1125 pMessage OUT NOCOPY VARCHAR2)
1126 RETURN NUMBER
1127 IS
1128 xErrLoc PLS_INTEGER := 100;
1129 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1130 xBusinessGroupId PLS_INTEGER;
1131 xOperatingUnitId PLS_INTEGER;
1132 xType VARCHAR2(20);
1133
1134 BEGIN
1135 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1136 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1137 'checkClassStatus(pType: ' || pType ||
1138 ', pValue: ' || pValue || ')');
1139 END IF;
1140
1141 xErrLoc := 100;
1142 SELECT category_set_id,
1143 validate_flag,
1144 structure_id
1145 INTO gCategorySetId,
1146 gValidateFlag,
1147 gStructureId
1148 FROM mtl_default_sets_view
1149 WHERE functional_area_id = 2;
1150
1151 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1152 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1153 'Category Set Information[category_set_id: ' || gCategorySetId ||
1154 ', validate_flag: ' || gValidateFlag ||
1155 ', structure_id: ' || gStructureId || ']');
1156 END IF;
1157
1158 xErrLoc := 200;
1159 xType := SUBSTR(UPPER(pType), 1, 20);
1160
1161 IF xType = 'CATEGORY' THEN
1162 RETURN checkCategoryStatus(pValue, pMessage);
1163 ELSIF xType = 'TEMPLATE_HEADER' THEN
1164 RETURN checkTemplateHeaderStatus(pValue, pMessage);
1165 ELSE
1166 xStatus := INVALID_TYPE;
1167 pMessage := 'Invalid type: ' || pType;
1168 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
1169
1170 RETURN xStatus;
1171 END IF;
1172
1173 RETURN xStatus;
1174 EXCEPTION
1175 when others then
1176 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkClassStatus-'||
1177 xErrLoc||' '||SQLERRM);
1178 ICX_POR_EXT_UTL.printStackTrace;
1179 raise ICX_POR_EXT_UTL.gException;
1180 END checkClassStatus;
1181
1182 -- Check classification status
1183 FUNCTION checkClassStatus(pType IN VARCHAR2,
1184 pValue IN VARCHAR2)
1185 RETURN VARCHAR2
1186 IS
1187 xMessage VARCHAR2(2000);
1188 xStatus PLS_INTEGER;
1189 BEGIN
1190 xStatus := checkClassStatus(pType, pValue, xMessage);
1191 RETURN 'Status: ['||getStatusString(xStatus)||'] '||xMessage;
1192 END checkClassStatus;
1193
1194 -- Check item status
1195 FUNCTION checkItemStatus(pBusinessGroup IN VARCHAR2,
1196 pOperatingUnit IN VARCHAR2,
1197 pType IN VARCHAR2,
1198 pValue1 IN VARCHAR2,
1199 pValue2 IN VARCHAR2,
1200 pMessage OUT NOCOPY VARCHAR2)
1201 RETURN NUMBER
1202 IS
1203 xErrLoc PLS_INTEGER := 100;
1204 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1205 xBusinessGroupId PLS_INTEGER;
1206 xOperatingUnitId PLS_INTEGER;
1207
1208 BEGIN
1209 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1210 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1211 'checkItemStatus(pBusinessGroup: ' || pBusinessGroup ||
1212 ', pOperatingUnit: ' || pOperatingUnit ||
1213 ', pType: ' || pType ||
1214 ', pValue1: ' || NVL(pValue1, 'NULL') ||
1215 ', pValue2: ' || NVL(pValue2, 'NULL') || ')');
1216 END IF;
1217
1218 xErrLoc := 100;
1219 -- Check business group
1220 BEGIN
1221 SELECT business_group_id
1222 INTO xBusinessGroupId
1223 FROM per_business_groups_perf
1224 WHERE name = pBusinessGroup;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 xStatus := INVALID_BUSINESS_GROUP;
1228 pMessage := 'Invalid business group: ' || pBusinessGroup;
1229 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
1230 RETURN xStatus;
1231 END;
1232 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1233 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1234 'Business group found with business_group_id: ' || xBusinessGroupId);
1235 END IF;
1236
1237 xErrLoc := 200;
1238 -- Check operating unit
1239 BEGIN
1240 SELECT organization_id
1241 INTO xOperatingUnitId
1242 FROM hr_operating_units
1243 WHERE business_group_id = xBusinessGroupId
1244 AND name = pOperatingUnit;
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 xStatus := INVALID_OPERATING_UNIT;
1248 pMessage := 'Invalid operating unit: ' || pOperatingUnit;
1249 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
1250 RETURN xStatus;
1251 END;
1252
1253 xErrLoc := 300;
1254 xStatus := checkItemStatus(xOperatingUnitId, pType, pValue1,
1255 pValue2, pMessage);
1256
1257 RETURN xStatus;
1258 EXCEPTION
1259 when others then
1260 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkItemStatus-'||
1261 xErrLoc||' '||SQLERRM);
1262 ICX_POR_EXT_UTL.printStackTrace;
1263 raise ICX_POR_EXT_UTL.gException;
1264 END checkItemStatus;
1265
1266 -- Check item status
1267 FUNCTION checkItemStatus(pOperatingUnitId IN NUMBER,
1268 pType IN VARCHAR2,
1269 pValue1 IN VARCHAR2,
1270 pValue2 IN VARCHAR2,
1271 pMessage OUT NOCOPY VARCHAR2)
1272 RETURN NUMBER
1273 IS
1274 xErrLoc PLS_INTEGER := 100;
1275 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1276 xType VARCHAR2(20);
1277
1278 BEGIN
1279 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1280 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1281 'checkItemStatus(pOperatingUnitID: ' || pOperatingUnitId ||
1282 ', pType: ' || pType ||
1283 ', pValue1: ' || NVL(pValue1, 'NULL') ||
1284 ', pValue2: ' || NVL(pValue2, 'NULL') || ')');
1285 END IF;
1286
1287 xErrLoc := 100;
1288 SELECT category_set_id,
1289 validate_flag,
1290 structure_id
1291 INTO gCategorySetId,
1292 gValidateFlag,
1293 gStructureId
1294 FROM mtl_default_sets_view
1295 WHERE functional_area_id = 2;
1296
1297 IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1298 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1299 'Category Set Information[category_set_id: ' || gCategorySetId ||
1300 ', validate_flag: ' || gValidateFlag ||
1301 ', structure_id: ' || gStructureId || ']');
1302 END IF;
1303
1304 xErrLoc := 200;
1305
1306 xType := SUBSTR(UPPER(pType), 1, 20);
1307
1308 IF xType IN ('BLANKET', 'QUOTATION') THEN
1309 RETURN checkContractLineStatus(xType, pOperatingUnitId, pValue1,
1310 pValue2, pMessage);
1311 ELSIF xType = 'TEMPLATE' THEN
1312 RETURN checkTemplateLineStatus(pOperatingUnitId, pValue1,
1313 pValue2, pMessage);
1314 ELSIF xType = 'ASL' THEN
1315 RETURN checkASLStatus(pOperatingUnitId, pValue1, pMessage);
1316 ELSIF xType = 'ITEM' THEN
1317 RETURN checkMasterStatus(pOperatingUnitId, pValue1, pMessage);
1318 ELSE
1319 xStatus := INVALID_TYPE;
1320 pMessage := 'Invalid type: ' || pType;
1321 ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL, pMessage);
1322 RETURN xStatus;
1323 END IF;
1324
1325 xErrLoc := 300;
1326 RETURN xStatus;
1327 EXCEPTION
1328 when others then
1329 ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_DIAG.checkItemStatus-'||
1330 xErrLoc||' '||SQLERRM);
1331 ICX_POR_EXT_UTL.printStackTrace;
1332 raise ICX_POR_EXT_UTL.gException;
1333 END checkItemStatus;
1334
1335 -- Check item status
1336 FUNCTION checkItemStatus(pBusinessGroup IN VARCHAR2,
1337 pOperatingUnit IN VARCHAR2,
1338 pType IN VARCHAR2,
1339 pValue1 IN VARCHAR2,
1340 pValue2 IN VARCHAR2)
1341 RETURN VARCHAR2
1342 IS
1343 xMessage VARCHAR2(2000);
1344 xStatus PLS_INTEGER;
1345 BEGIN
1346 xStatus := checkItemStatus(pBusinessGroup, pOperatingUnit, pType,
1347 pValue1, pValue2, xMessage);
1348 RETURN 'Status: ['||getStatusString(xStatus)||'] '||xMessage;
1349 END checkItemStatus;
1350
1351 -- Check item status
1352 FUNCTION checkItemStatus(pOperatingUnitId IN NUMBER,
1353 pType IN VARCHAR2,
1354 pValue1 IN VARCHAR2,
1355 pValue2 IN VARCHAR2)
1356 RETURN VARCHAR2
1357 IS
1358 xMessage VARCHAR2(2000);
1359 xStatus PLS_INTEGER;
1360 BEGIN
1361 xStatus := checkItemStatus(pOperatingUnitId, pType, pValue1,
1362 pValue2, xMessage);
1363 RETURN 'Status: ['||getStatusString(xStatus)||'] '||xMessage;
1364 END checkItemStatus;
1365
1366 --------------------------------------------------------------
1367 -- Functions to get extracted price status --
1368 --------------------------------------------------------------
1369 FUNCTION getContractLineStatus(p_contract_line_id IN NUMBER,
1370 p_test_mode IN VARCHAR2)
1371 RETURN NUMBER
1372 IS
1373 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1374 xString VARCHAR2(2000);
1375 xHTypeLookupCode po_headers_all.type_lookup_code%TYPE;
1376 xHApprovedDate DATE;
1377 xHApprovedFlag VARCHAR2(1);
1378 xHCancelFlag VARCHAR2(1);
1379 xHFrozenFlag VARCHAR2(1);
1380 xHClosedCode po_headers_all.closed_code%TYPE;
1381 xLClosedCode po_lines_all.closed_code%TYPE;
1382 xLCancelFlag VARCHAR2(1);
1383 xHStatusLookupCode po_headers_all.status_lookup_code%TYPE;
1384 xHQuotationClassCode po_headers_all.quotation_class_code%TYPE;
1385 xHStartDate DATE;
1386 xHEndDate DATE;
1387 xLExpirationDate DATE;
1388
1389 BEGIN
1390 xString :=
1391 'SELECT ' || VALID_FOR_EXTRACT || ' ';
1392 IF p_test_mode = 'Y' THEN
1393 xString := xString ||
1394 'FROM ipo_line_types_b plt, ' ||
1395 'ipo_headers_all ph, ' ||
1396 'ipo_lines_all pl ';
1397 ELSE
1398 xString := xString ||
1399 'FROM po_line_types_b plt, ' ||
1400 'po_headers_all ph, ' ||
1401 'po_lines_all pl ';
1402 END IF;
1403 xString := xString ||
1404 'WHERE pl.po_line_id = :contract_line_id ' ||
1405 'AND ph.po_header_id = pl.po_header_id ' ||
1406 'AND ph.type_lookup_code IN (''BLANKET'', ''QUOTATION'') ' ||
1407 'AND ((ph.approved_date IS NOT NULL AND ' ||
1408 ' ph.approved_flag = ''Y'' AND ' ||
1409 ' NVL(ph.cancel_flag, ''N'') <> ''Y'' AND ' ||
1410 ' NVL(ph.frozen_flag, ''N'') <> ''Y'' AND ' ||
1411 ' NVL(ph.closed_code, ''OPEN'') NOT IN (''CLOSED'', ''FINALLY CLOSED'') AND ' ||
1412 ' NVL(pl.closed_code, ''OPEN'') NOT IN (''CLOSED'', ''FINALLY CLOSED'') AND ' ||
1413 ' NVL(pl.cancel_flag, ''N'') <> ''Y'') OR ' ||
1414 ' (ph.status_lookup_code = ''A'' AND ' ||
1415 ' ph.quotation_class_code = ''CATALOG'')) ' ||
1416 'AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(ph.start_date), TRUNC( SYSDATE - 1)) AND ' ||
1417 ' NVL(TRUNC(ph.end_date), TRUNC( SYSDATE + 1)) ' ||
1418 'AND TRUNC( SYSDATE) <= NVL(TRUNC(pl.expiration_date), TRUNC( SYSDATE+1)) ' ||
1419 'AND pl.line_type_id = plt.line_type_id ' ||
1420 'AND NVL(plt.outside_operation_flag, ''N'') = ''N'' ' ||
1421 'AND (ph.type_lookup_code =''BLANKET'' OR ' ||
1422 ' (ph.type_lookup_code =''QUOTATION'' AND ' ||
1423 ' (NVL(ph.approval_required_flag,''N'') = ''N'' OR ' ||
1424 ' (ph.approval_required_flag =''Y'' AND ' ||
1425 ' EXISTS (SELECT ''current approved effective price break'' ';
1426
1427 IF p_test_mode = 'Y' THEN
1428 xString := xString ||
1429 ' FROM ipo_line_locations_all pll, ' ||
1430 ' ipo_quotation_approvals_all pqa ';
1431 ELSE
1432 xString := xString ||
1433 ' FROM po_line_locations_all pll, ' ||
1434 ' po_quotation_approvals_all pqa ';
1435 END IF;
1436
1437 xString := xString ||
1438 ' WHERE pl.po_line_id = pll.po_line_id ' ||
1439 ' AND SYSDATE BETWEEN NVL(pll.start_date, SYSDATE-1) AND ' ||
1440 ' NVL(pll.end_date, SYSDATE+1) ' ||
1441 ' AND pqa.line_location_id = pll.line_location_id ' ||
1442 ' AND pqa.approval_type IN (''ALL ORDERS'',''REQUISITIONS'') ' ||
1443 ' AND SYSDATE BETWEEN NVL(pqa.start_date_active, SYSDATE-1) ' ||
1444 ' AND NVL(pqa.end_date_active, SYSDATE+1)))))) ';
1445
1446 BEGIN
1447 EXECUTE IMMEDIATE xString INTO xStatus
1448 USING p_contract_line_id;
1449
1450 /* Bug#3352834: Validating the Inventory Item's Purchasable Mode.
1451 * after validating the blanket.
1452 */
1453 xString :=
1454 'SELECT decode(mi.inventory_item_id, ' ||
1455 ' NULL, ' || VALID_FOR_EXTRACT || ', ' ||
1456 ' decode(NVL(mi.purchasing_enabled_flag, ''N''), ' ||
1457 ' ''N'', ' || INVALID_BLANKET_LINE || ', ' ||
1458 ' ' || VALID_FOR_EXTRACT || ')) ' ||
1459 'FROM ';
1460 IF p_test_mode = 'Y' THEN
1461 xString := xString ||
1462 'ipo_headers_all ph, ' ||
1463 'ipo_lines_all pl, ' ||
1464 'imtl_system_items_kfv mi, ' ||
1465 'ifinancials_system_params_all fsp ';
1466 ELSE
1467 xString := xString ||
1468 'po_headers_all ph, ' ||
1469 'po_lines_all pl, ' ||
1470 'mtl_system_items_kfv mi, ' ||
1471 'financials_system_params_all fsp ';
1472 END IF;
1473 xString := xString ||
1474 'WHERE pl.po_line_id = :contract_line_id ' ||
1475 ' AND ph.po_header_id = pl.po_header_id ' ||
1476 ' AND ph.type_lookup_code IN (''BLANKET'', ''QUOTATION'') ' ||
1477 ' AND pl.item_id = mi.inventory_item_id (+) ' ||
1478 ' AND (ph.org_id is null and fsp.org_id is null or ph.org_id = fsp.org_id) ' ||
1479 ' AND fsp.inventory_organization_id = NVL(mi.organization_id, fsp.inventory_organization_id)';
1480
1481 BEGIN
1482 EXECUTE IMMEDIATE xString INTO xStatus
1483 USING p_contract_line_id;
1484 /* Not handling the exception, as the query would always return
1485 a record. */
1486 END;
1487
1488 RETURN xStatus;
1489 EXCEPTION
1490 WHEN NO_DATA_FOUND THEN
1491 xString :=
1492 'SELECT ph.type_lookup_code, ' ||
1493 'ph.approved_date, ' ||
1494 'ph.approved_flag, ' ||
1495 'ph.cancel_flag, ' ||
1496 'ph.frozen_flag, ' ||
1497 'ph.closed_code, ' ||
1498 'pl.closed_code, ' ||
1499 'pl.cancel_flag, ' ||
1500 'ph.status_lookup_code, ' ||
1501 'ph.quotation_class_code, ' ||
1502 'ph.start_date, ' ||
1503 'ph.end_date, ' ||
1504 'pl.expiration_date ';
1505
1506 IF p_test_mode = 'Y' THEN
1507 xString := xString ||
1508 'FROM ipo_headers_all ph, ' ||
1509 'ipo_lines_all pl ';
1510 ELSE
1511 xString := xString ||
1512 'FROM po_headers_all ph, ' ||
1513 'po_lines_all pl ';
1514 END IF;
1515 xString := xString ||
1516 'WHERE ph.po_header_id = pl.po_header_id ' ||
1517 'AND pl.po_line_id = :contract_line_id';
1518
1519 EXECUTE IMMEDIATE xString
1520 INTO xHTypeLookupCode,
1521 xHApprovedDate,
1522 xHApprovedFlag,
1523 xHCancelFlag,
1524 xHFrozenFlag,
1525 xHClosedCode,
1526 xLClosedCode,
1527 xLCancelFlag,
1528 xHStatusLookupCode,
1529 xHQuotationClassCode,
1530 xHStartDate,
1531 xHEndDate,
1532 xLExpirationDate
1533 USING p_contract_line_id;
1534
1535 IF xHTypeLookupCode = 'BLANKET' THEN
1536 IF NOT (xHApprovedDate IS NOT NULL AND
1537 xHApprovedFlag = 'Y' AND
1538 NVL(xHCancelFlag, 'N') <> 'Y' AND
1539 NVL(xHFrozenFlag, 'N') <> 'Y' AND
1540 NVL(xHClosedCode, 'OPEN') NOT IN
1541 ('CLOSED', 'FINALLY CLOSED'))
1542 THEN
1543 RETURN INACTIVE_BLANKET;
1544 END IF;
1545 IF NOT (TRUNC(SYSDATE) BETWEEN
1546 NVL(TRUNC(xHStartDate), TRUNC(SYSDATE-1)) AND
1547 NVL(TRUNC(xHEndDate), TRUNC(SYSDATE+1)))
1548 THEN
1549 RETURN INEFFECTIVE_BLANKET;
1550 END IF;
1551 IF NOT (NVL(xLClosedCode, 'OPEN') NOT IN
1552 ('CLOSED', 'FINALLY CLOSED') AND
1553 NVL(xLCancelFlag, 'N') <> 'Y' AND
1554 TRUNC(SYSDATE) <= NVL(TRUNC(xLExpirationDate),
1555 TRUNC(SYSDATE+1)))
1556 THEN
1557 RETURN INACTIVE_BLANKET_LINE;
1558 END IF;
1559 -- Otherwise
1560 RETURN OUTSIDE_BLANKET;
1561 ELSIF xHTypeLookupCode = 'QUOTATION' THEN
1562 IF (xHStatusLookupCode <> 'A' OR
1563 xHQuotationClassCode <> 'CATALOG')
1564 THEN
1565 RETURN INACTIVE_QUOTATION;
1566 END IF;
1567 IF NOT (TRUNC(SYSDATE) BETWEEN
1568 NVL(TRUNC(xHStartDate), TRUNC(SYSDATE-1)) AND
1569 NVL(TRUNC(xHEndDate), TRUNC(SYSDATE+1)) AND
1570 TRUNC(SYSDATE) <= NVL(TRUNC(xLExpirationDate),
1571 TRUNC(SYSDATE+1)))
1572 THEN
1573 RETURN INEFFECTIVE_QUOTATION;
1574 END IF;
1575 -- Otherwise
1576 RETURN QUOTATION_NO_EFFECTIVE_PRICE;
1577 ELSE
1578 -- Should reach here
1579 RETURN INVALID_BLANKET_LINE;
1580 END IF;
1581 END;
1582
1583 RETURN xStatus;
1584 END getContractLineStatus;
1585
1586 FUNCTION getGlobalAgreementStatus(p_enabled_flag IN VARCHAR2,
1587 p_purchasing_site IN VARCHAR2,
1588 p_inactive_date IN DATE,
1589 p_local_purchasing_enabled IN VARCHAR2,
1590 p_local_outside_operation IN VARCHAR2,
1591 p_local_uom_code IN VARCHAR2,
1592 p_purchasing_enabled IN VARCHAR2,
1593 p_outside_operation IN VARCHAR2,
1594 p_uom_code IN VARCHAR2,
1595 p_purchasing_uom_code IN VARCHAR2,
1596 p_test_mode IN VARCHAR2)
1597 RETURN NUMBER
1598 IS
1599 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1600 BEGIN
1601 IF NVL(p_enabled_flag, 'N') = 'N' THEN
1602 RETURN GLOBAL_AGREEMENT_DISABLED;
1603 END IF;
1604 IF (p_purchasing_site = 'N' OR
1605 NVL(p_inactive_date, SYSDATE+1) <= SYSDATE)
1606 THEN
1607 RETURN GLOBAL_AGREEMENT_INVALID_SITE;
1608 END IF;
1609 IF (p_local_purchasing_enabled = 'N' OR p_purchasing_enabled = 'N' OR
1610 p_local_outside_operation <> 'N' OR p_outside_operation <> 'N')
1611 THEN
1612 RETURN GLOBAL_AGREEMENT_INVALID_ITEM;
1613 END IF;
1614 IF (p_uom_code IS NOT NULL AND
1615 p_local_uom_code IS NOT NULL AND
1616 p_purchasing_uom_code IS NOT NULL AND
1617 p_uom_code <> p_local_uom_code AND
1618 p_uom_code <> p_purchasing_uom_code)
1619 THEN
1620 BEGIN
1621 SELECT VALID_FOR_EXTRACT
1622 INTO xStatus
1623 FROM dual
1624 WHERE EXISTS (SELECT 'same UOM class'
1625 FROM mtl_units_of_measure uom1,
1626 mtl_units_of_measure uom2,
1627 mtl_units_of_measure uom3
1628 WHERE uom1.uom_code = p_uom_code
1629 AND uom2.uom_code = p_local_uom_code
1630 AND uom3.uom_code = p_purchasing_uom_code
1631 AND uom1.uom_class = uom2.uom_class
1632 AND uom1.uom_class = uom3.uom_class);
1633
1634 EXCEPTION
1635 WHEN NO_DATA_FOUND THEN
1636 RETURN GLOBAL_AGREEMENT_INVALID_UOM;
1637 END;
1638 END IF;
1639
1640 RETURN xStatus;
1641 END getGlobalAgreementStatus;
1642
1643 FUNCTION getTemplateLineStatus(p_template_id IN VARCHAR2,
1644 p_template_line_id IN NUMBER,
1645 p_org_id IN NUMBER,
1646 p_inactive_date IN DATE,
1647 p_contract_line_id IN NUMBER,
1648 p_test_mode IN VARCHAR2)
1649 RETURN NUMBER
1650 IS
1651 xString VARCHAR2(2000);
1652 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1653 BEGIN
1654 IF NVL(p_inactive_date, SYSDATE+1) <= SYSDATE THEN
1655 RETURN INACTIVE_TEMPLATE;
1656 END IF;
1657
1658 IF (p_contract_line_id IS NOT NULL AND
1659 p_contract_line_id <> ICX_POR_EXT_ITEM.NULL_NUMBER)
1660 THEN
1661 xStatus := getContractLineStatus(p_contract_line_id, p_test_mode);
1662 IF xStatus = VALID_FOR_EXTRACT THEN
1663 RETURN VALID_FOR_EXTRACT;
1664 ELSIF xStatus = INACTIVE_BLANKET THEN
1665 RETURN TEMPLATE_INACTIVE_BLANKET;
1666 ELSIF xStatus = INEFFECTIVE_BLANKET THEN
1667 RETURN TEMPLATE_INEFFECTIVE_BLANKET;
1668 ELSIF xStatus = INACTIVE_BLANKET_LINE THEN
1669 RETURN TEMPLATE_INACTIVE_BLANKET_LINE;
1670 ELSIF xStatus = OUTSIDE_BLANKET THEN
1671 RETURN TEMPLATE_OUTSIDE_BLANKET;
1672 ELSE
1673 -- Should not reach here
1674 RETURN INACTIVE_TEMPLATE;
1675 END IF;
1676 END IF;
1677
1678 /* Bug#3464695: Validating the Inventory Item's Purchasable Mode.
1679 * after validating the template.
1680 * Bug#3524364: Also validate the Inventory Item's internally
1681 * orderable flag.
1682 */
1683 xString :=
1684 'SELECT decode(mi.inventory_item_id, NULL, ' ||
1685 VALID_FOR_EXTRACT || ', ' ||
1686 'decode(NVL(mi.purchasing_enabled_flag, ''N''), ''N'', ' ||
1687 'decode(NVL(mi.internal_order_enabled_flag, ''N''), ''Y'', ' ||
1688 'decode(prl.source_type_code, ''INVENTORY'', ' ||
1689 VALID_FOR_EXTRACT || ', ' ||
1690 INVALID_TEMPLATE_LINE || '), ' ||
1691 INVALID_TEMPLATE_LINE || '), ' ||
1692 VALID_FOR_EXTRACT || ')) ' ||
1693 'FROM ';
1694 IF p_test_mode = 'Y' THEN
1695 xString := xString ||
1696 'ipo_reqexpress_lines_all prl, ' ||
1697 'imtl_system_items_kfv mi, ' ||
1698 'ifinancials_system_params_all fsp ' ;
1699 ELSE
1700 xString := xString ||
1701 'po_reqexpress_lines_all prl, ' ||
1702 'mtl_system_items_kfv mi, ' ||
1703 'financials_system_params_all fsp ';
1704 END IF;
1705
1706 xString := xString ||
1707 'WHERE prl.express_name = :express_name ' ||
1708 ' AND prl.sequence_num = :sequence_num ' ||
1709 ' AND (prl.org_id is null and :org_id is null or prl.org_id = :org_id) ' ||
1710 ' AND prl.item_id = mi.inventory_item_id (+) ' ||
1711 ' AND (prl.org_id is null and fsp.org_id is null or prl.org_id = fsp.org_id) ' ||
1712 ' AND fsp.inventory_organization_id = NVL(mi.organization_id, fsp.inventory_organization_id)';
1713
1714 BEGIN
1715 EXECUTE IMMEDIATE xString INTO xStatus
1716 USING p_template_id, p_template_line_id, p_org_id, p_org_id;
1717 /* Not handling the exception, as the query would always return
1718 a record. */
1719 END;
1720
1721 RETURN xStatus;
1722 END getTemplateLineStatus;
1723
1724 FUNCTION getASLStatus(p_asl_id IN NUMBER,
1725 p_disable_flag IN VARCHAR2,
1726 p_asl_status_id IN NUMBER,
1727 p_item_price IN NUMBER,
1728 p_test_mode IN VARCHAR2)
1729 RETURN NUMBER
1730 IS
1731 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1732 xString VARCHAR2(2000);
1733 BEGIN
1734 IF (NVL(p_disable_flag, 'N') <> 'N') THEN
1735 RETURN DISABLED_ASL;
1736 END IF;
1737
1738 IF (p_item_price IS NULL) THEN
1739 RETURN ASL_NO_PRICE;
1740 END IF;
1741
1742 xString :=
1743 'SELECT ' || VALID_FOR_EXTRACT || ' ';
1744 IF p_test_mode = 'Y' THEN
1745 xString := xString ||
1746 'FROM ipo_asl_status_rules ';
1747 ELSE
1748 xString := xString ||
1749 'FROM po_asl_status_rules ';
1750 END IF;
1751 xString := xString ||
1752 'WHERE status_id = :asl_status_id ' ||
1753 'AND business_rule = ''2_SOURCING'' ' ||
1754 'AND allow_action_flag = ''Y'' ' ||
1755 'AND rownum = 1';
1756
1757 BEGIN
1758 EXECUTE IMMEDIATE xString INTO xStatus
1759 USING p_asl_status_id;
1760 EXCEPTION
1761 WHEN NO_DATA_FOUND THEN
1762 RETURN UNALLOWED_ASL;
1763 END;
1764
1765 /* Bug#3464695: Validating the Inventory Item's Purchasable Mode.
1766 * after validating the ASL.
1767 * Bug#3738786: owning_organization_id in po_approved_supplier_list
1768 * is the inventory_organization_id and not org_id in financial_system_params_all
1769 * Since the following query is only to find the validity of item in the ASL
1770 * AND ASLs are inventory org based and not OU based(which means to create
1771 * an ASL in an inventory org-INV1 the master item to be attached should be
1772 * enabled in INV1)
1773 * So, we can remove the join with financials_system_params_all completely
1774 */
1775 xString :=
1776 'SELECT decode(mi.inventory_item_id, ' ||
1777 ' NULL, ' || VALID_FOR_EXTRACT || ', ' ||
1778 ' decode(NVL(mi.purchasing_enabled_flag, ''N''), ' ||
1779 ' ''N'', ' || INVALID_ASL || ', ' ||
1780 ' ' || VALID_FOR_EXTRACT || ')) ' ||
1781 'FROM ';
1782 IF p_test_mode = 'Y' THEN
1783 xString := xString ||
1784 'ipo_approved_supplier_list pasl, ' ||
1785 'imtl_system_items_kfv mi ';
1786 ELSE
1787 xString := xString ||
1788 'po_approved_supplier_list pasl, ' ||
1789 'mtl_system_items_kfv mi ';
1790 END IF;
1791 xString := xString ||
1792 'WHERE pasl.asl_id = :asl_id ' ||
1793 ' AND pasl.item_id = mi.inventory_item_id ' ||
1794 ' AND pasl.owning_organization_id = mi.organization_id ';
1795
1796 BEGIN
1797 EXECUTE IMMEDIATE xString INTO xStatus
1798 USING p_asl_id;
1799 /* Not handling the exception, as the query would always return
1800 a record. */
1801 END;
1802
1803 RETURN xStatus;
1804 END getASLStatus;
1805
1806 FUNCTION getPurchasingItemStatus(p_purchasing_enabled_flag IN VARCHAR2,
1807 p_outside_operation_flag IN VARCHAR2,
1808 p_list_price_per_unit IN NUMBER,
1809 p_test_mode IN VARCHAR2)
1810 RETURN NUMBER
1811 IS
1812 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1813 BEGIN
1814 IF (p_list_price_per_unit IS NULL) THEN
1815 RETURN ITEM_NO_PRICE;
1816 END IF;
1817
1818 IF NOT (p_purchasing_enabled_flag = 'Y' AND
1819 NVL(p_outside_operation_flag, 'N') <> 'Y')
1820 THEN
1821 RETURN UNPURCHASABLE_OUTSIDE;
1822 END IF;
1823
1824 RETURN xStatus;
1825 END getPurchasingItemStatus;
1826
1827 FUNCTION getInternalItemStatus(p_internal_order_enabled_flag IN VARCHAR2,
1828 p_test_mode IN VARCHAR2)
1829 RETURN NUMBER
1830 IS
1831 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1832 BEGIN
1833 IF (p_internal_order_enabled_flag <> 'Y') THEN
1834 RETURN NOTINTERNAL;
1835 END IF;
1836
1837 RETURN xStatus;
1838 END getInternalItemStatus;
1839
1840 FUNCTION getPriceStatus(p_price_type IN VARCHAR2,
1841 p_row_id IN ROWID,
1842 p_test_mode IN VARCHAR2)
1843 RETURN NUMBER
1844 IS
1845 xStatus PLS_INTEGER := VALID_FOR_EXTRACT;
1846 xString VARCHAR2(2000);
1847 l_inactive_date DATE;
1848 l_contract_line_id NUMBER;
1849 l_enabled_flag VARCHAR2(1);
1850 l_purchasing_site VARCHAR2(1);
1851 l_purchasing_enabled VARCHAR2(1);
1852 l_outside_operation VARCHAR2(1);
1853 l_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1854
1855 -- Centralized Procurement Impacts Enhancement - pcreddy
1856 l_purchasing_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1857 l_local_purchasing_enabled VARCHAR2(1);
1858 l_local_outside_operation VARCHAR2(1);
1859
1860 l_local_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
1861 l_disable_flag VARCHAR2(1);
1862 l_asl_status_id NUMBER;
1863 l_item_price NUMBER;
1864 l_internal_order_enabled VARCHAR2(1);
1865
1866 -- Bug#3464695
1867 l_asl_id NUMBER;
1868 l_express_name ICX_CAT_ITEM_PRICES.TEMPLATE_ID%TYPE;
1869 l_sequence_num NUMBER;
1870 l_rt_org_id NUMBER;
1871
1872 BEGIN
1873 IF p_price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE') THEN
1874 xString :=
1875 'SELECT prl.express_name, '||
1876 'prl.sequence_num, '||
1877 'prl.org_id, '||
1878 'prh.inactive_date, '||
1879 'p.contract_line_id '||
1880 'FROM icx_cat_item_prices p, ';
1881 IF p_test_mode = 'Y' THEN
1882 xString := xString ||
1883 'ipo_reqexpress_headers_all prh, '||
1884 'ipo_reqexpress_lines_all prl ';
1885 ELSE
1886 xString := xString ||
1887 'po_reqexpress_headers_all prh, '||
1888 'po_reqexpress_lines_all prl ';
1889 END IF;
1890 xString := xString ||
1891 'WHERE p.rowid = :row_id ' ||
1892 'AND p.org_id = nvl(prh.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
1893 'AND p.template_id = prh.express_name '||
1894 'AND p.org_id = nvl(prl.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
1895 'AND p.template_id = prl.express_name '||
1896 'AND p.template_line_id = prl.sequence_num ';
1897 EXECUTE IMMEDIATE xString
1898 INTO l_express_name,
1899 l_sequence_num,
1900 l_rt_org_id,
1901 l_inactive_date,
1902 l_contract_line_id
1903 USING p_row_id;
1904 xStatus := getTemplateLineStatus(l_express_name,
1905 l_sequence_num,
1906 l_rt_org_id,
1907 l_inactive_date,
1908 l_contract_line_id,
1909 p_test_mode);
1910
1911 ELSIF p_price_type IN ('BLANKET', 'QUOTATION') THEN
1912 SELECT contract_line_id
1913 INTO l_contract_line_id
1914 FROM icx_cat_item_prices
1915 WHERE rowid = p_row_id;
1916 xStatus := getContractLineStatus(l_contract_line_id,
1917 p_test_mode);
1918 ELSIF p_price_type = 'GLOBAL_AGREEMENT' THEN
1919 xString :=
1920 'SELECT t.enabled_flag, '||
1921 'pvs.purchasing_site_flag, '||
1922 'pvs.inactive_date, '||
1923 'mi.purchasing_enabled_flag, '||
1924 'mi.outside_operation_flag, '||
1925 'p.unit_of_measure, '||
1926 'mi.primary_uom_code, '||
1927 'mi2.purchasing_enabled_flag, '||
1928 'mi2.outside_operation_flag, '||
1929 'mi2.primary_uom_code '||
1930 'FROM icx_cat_item_prices p, ';
1931 IF p_test_mode = 'Y' THEN
1932 xString := xString ||
1933 'ipo_ga_org_assignments t, '||
1934 'ipo_vendor_sites_all pvs, '||
1935 'imtl_system_items_kfv mi, '||
1936 'ifinancials_system_params_all fsp, '||
1937 'imtl_system_items_kfv mi2, '|| -- Centralized proc Impacts
1938 'ifinancials_system_params_all fsp2 ';
1939 ELSE
1940 xString := xString ||
1941 'po_ga_org_assignments t, '||
1942 'po_vendor_sites_all pvs, '||
1943 'mtl_system_items_kfv mi, '||
1944 'financials_system_params_all fsp, '||
1945 'mtl_system_items_kfv mi2, '|| -- Centralized proc Impacts
1946 'financials_system_params_all fsp2 ';
1947 END IF;
1948 xString := xString ||
1949 'WHERE p.rowid = :row_id ' ||
1950 'AND p.contract_id = t.po_header_id '||
1951 'AND p.org_id = t.organization_id '||
1952 'AND t.vendor_site_id = pvs.vendor_site_id (+) '||
1953 'AND p.org_id = nvl(fsp.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
1954 'AND t.purchasing_org_id = fsp2.org_id '|| -- Centralized proc Impacts
1955 'AND p.inventory_item_id = mi.inventory_item_id (+) '||
1956 'AND p.inventory_item_id = mi2.inventory_item_id (+) '|| -- Centralized proc Impacts
1957 'AND fsp.inventory_organization_id = NVL(mi.organization_id, '||
1958 'fsp.inventory_organization_id) '||
1959 'AND fsp2.inventory_organization_id = NVL(mi2.organization_id, '||
1960 'fsp2.inventory_organization_id) '; -- Centralized proc Impacts
1961 EXECUTE IMMEDIATE xString
1962 INTO l_enabled_flag,
1963 l_purchasing_site,
1964 l_inactive_date,
1965 l_local_purchasing_enabled, -- Centralized proc Impacts
1966 l_local_outside_operation,
1967 l_uom_code,
1968 l_local_uom_code,
1969 l_purchasing_enabled,
1970 l_outside_operation,
1971 l_purchasing_uom_code
1972 USING p_row_id;
1973 xStatus := getGlobalAgreementStatus(l_enabled_flag,
1974 l_purchasing_site,
1975 l_inactive_date,
1976 l_local_purchasing_enabled,
1977 l_local_outside_operation,
1978 l_local_uom_code,
1979 l_purchasing_enabled,
1980 l_outside_operation,
1981 l_uom_code,
1982 l_purchasing_uom_code,
1983 p_test_mode);
1984 ELSIF p_price_type = 'ASL' THEN
1985 xString :=
1986 'SELECT pasl.asl_id, '||
1987 'pasl.disable_flag, '||
1988 'pasl.asl_status_id, '||
1989 'mi.list_price_per_unit '||
1990 'FROM icx_cat_item_prices p, ';
1991 IF p_test_mode = 'Y' THEN
1992 xString := xString ||
1993 'ipo_approved_supplier_list pasl, '||
1994 'imtl_system_items_kfv mi, '||
1995 'ifinancials_system_params_all fsp ';
1996 ELSE
1997 xString := xString ||
1998 'po_approved_supplier_list pasl, '||
1999 'mtl_system_items_kfv mi, '||
2000 'financials_system_params_all fsp ';
2001 END IF;
2002 xString := xString ||
2003 'WHERE p.rowid = :row_id ' ||
2004 'AND p.org_id = nvl(fsp.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
2005 'AND fsp.inventory_organization_id = pasl.owning_organization_id '||
2006 'AND pasl.item_id = mi.inventory_item_id '||
2007 'AND pasl.owning_organization_id = mi.organization_id '||
2008 'AND p.asl_id = pasl.asl_id ';
2009 EXECUTE IMMEDIATE xString
2010 INTO l_asl_id,
2011 l_disable_flag,
2012 l_asl_status_id,
2013 l_item_price
2014 USING p_row_id;
2015 xStatus := getASLStatus(l_asl_id,
2016 l_disable_flag,
2017 l_asl_status_id,
2018 l_item_price,
2019 p_test_mode);
2020 ELSIF p_price_type = 'PURCHASING_ITEM' THEN
2021 xString :=
2022 'SELECT mi.purchasing_enabled_flag, '||
2023 'mi.outside_operation_flag, '||
2024 'mi.list_price_per_unit '||
2025 'FROM icx_cat_item_prices p, ';
2026 IF p_test_mode = 'Y' THEN
2027 xString := xString ||
2028 'imtl_system_items_kfv mi, '||
2029 'ifinancials_system_params_all fsp ';
2030 ELSE
2031 xString := xString ||
2032 'mtl_system_items_kfv mi, '||
2033 'financials_system_params_all fsp ';
2034 END IF;
2035 xString := xString ||
2036 'WHERE p.rowid = :row_id ' ||
2037 'AND p.org_id = nvl(fsp.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
2038 'AND fsp.inventory_organization_id = mi.organization_id '||
2039 'AND p.inventory_item_id = mi.inventory_item_id ';
2040 EXECUTE IMMEDIATE xString
2041 INTO l_local_purchasing_enabled,
2042 l_local_outside_operation,
2043 l_item_price
2044 USING p_row_id;
2045 xStatus := getPurchasingItemStatus(l_local_purchasing_enabled,
2046 l_local_outside_operation,
2047 l_item_price,
2048 p_test_mode);
2049 ELSIF p_price_type = 'INTERNAL_ITEM' THEN
2050 xString :=
2051 'SELECT mi.internal_order_enabled_flag '||
2052 'FROM icx_cat_item_prices p, ';
2053 IF p_test_mode = 'Y' THEN
2054 xString := xString ||
2055 'imtl_system_items_kfv mi, '||
2056 'ifinancials_system_params_all fsp ';
2057 ELSE
2058 xString := xString ||
2059 'mtl_system_items_kfv mi, '||
2060 'financials_system_params_all fsp ';
2061 END IF;
2062 xString := xString ||
2063 'WHERE p.rowid = :row_id ' ||
2064 'AND p.org_id = nvl(fsp.org_id, '||ICX_POR_EXT_ITEM.NULL_NUMBER||') '||
2065 'AND fsp.inventory_organization_id = mi.organization_id '||
2066 'AND p.inventory_item_id = mi.inventory_item_id ';
2067 EXECUTE IMMEDIATE xString
2068 INTO l_internal_order_enabled
2069 USING p_row_id;
2070 xStatus := getInternalItemStatus(l_internal_order_enabled,
2071 p_test_mode);
2072 END IF;
2073
2074 RETURN xStatus;
2075 EXCEPTION
2076 WHEN NO_DATA_FOUND THEN
2077 IF p_price_type IN ('TEMPLATE', 'INTERNAL_TEMPLATE') THEN
2078 xStatus := INVALID_TEMPLATE_LINE;
2079 ELSIF p_price_type IN ('BLANKET', 'GLOBAL_AGREEMENT') THEN
2080 xStatus := INVALID_BLANKET_LINE;
2081 ELSIF p_price_type = 'QUOTATION' THEN
2082 xStatus := INVALID_QUOTATION_LINE;
2083 ELSIF p_price_type = 'ASL' THEN
2084 xStatus := INVALID_ASL;
2085 ELSIF p_price_type IN ('PURCHASING_ITEM', 'INTERNAL_ITEM') THEN
2086 xStatus := INVALID_ITEM;
2087 END IF;
2088 RETURN xStatus;
2089 END getPriceStatus;
2090
2091 FUNCTION isValidExtPrice(pDocumentType IN NUMBER,
2092 pStatus IN NUMBER,
2093 pLoadContract IN VARCHAR2,
2094 pLoadTemplateLine IN VARCHAR2,
2095 pLoadItemMaster IN VARCHAR2,
2096 pLoadInternalItem IN VARCHAR2)
2097 RETURN NUMBER
2098 IS
2099 BEGIN
2100 IF pDocumentType IN (ICX_POR_EXT_ITEM.TEMPLATE_TYPE,
2101 ICX_POR_EXT_ITEM.INTERNAL_TEMPLATE_TYPE)
2102 THEN
2103 IF (pLoadTemplateLine = 'Y' AND
2104 pStatus = VALID_FOR_EXTRACT)
2105 THEN
2106 IF pDocumentType = ICX_POR_EXT_ITEM.INTERNAL_TEMPLATE_TYPE THEN
2107 IF pLoadInternalItem = 'Y' THEN
2108 RETURN 1;
2109 END IF;
2110 RETURN 0;
2111 END IF;
2112 RETURN 1;
2113 END IF;
2114 RETURN 0;
2115 END IF;
2116
2117 IF pDocumentType IN (ICX_POR_EXT_ITEM.CONTRACT_TYPE,
2118 ICX_POR_EXT_ITEM.GLOBAL_AGREEMENT_TYPE)
2119 THEN
2120 IF (pLoadContract = 'Y' AND
2121 pStatus = VALID_FOR_EXTRACT)
2122 THEN
2123 RETURN 1;
2124 END IF;
2125 RETURN 0;
2126 END IF;
2127
2128 IF pDocumentType IN (ICX_POR_EXT_ITEM.ASL_TYPE,
2129 ICX_POR_EXT_ITEM.PURCHASING_ITEM_TYPE)
2130 THEN
2131 IF (pLoadItemMaster = 'Y' AND
2132 pStatus = VALID_FOR_EXTRACT)
2133 THEN
2134 RETURN 1;
2135 END IF;
2136 RETURN 0;
2137 END IF;
2138
2139 IF pDocumentType = ICX_POR_EXT_ITEM.INTERNAL_ITEM_TYPE THEN
2140 IF (pLoadInternalItem = 'Y' AND
2141 pStatus = VALID_FOR_EXTRACT)
2142 THEN
2143 RETURN 1;
2144 END IF;
2145 RETURN 0;
2146 END IF;
2147
2148 RETURN 0;
2149 END isValidExtPrice;
2150
2151 END ICX_POR_EXT_DIAG;