1 PACKAGE ICX_POR_EXT_DIAG AUTHID CURRENT_USER AS
2 /* $Header: ICXEXTDS.pls 115.8 2004/03/31 18:46:15 vkartik ship $*/
3
4 INVALID_BUSINESS_GROUP PLS_INTEGER := -1;
5 INVALID_OPERATING_UNIT PLS_INTEGER := -2;
6 INVALID_TYPE PLS_INTEGER := -3;
7 INVALID_CATEGORY PLS_INTEGER := -4;
8 INVALID_TEMPLATE_HEADER PLS_INTEGER := -5;
9 INVALID_TEMPLATE_LINE PLS_INTEGER := -6;
10 INVALID_BLANKET_LINE PLS_INTEGER := -7;
11 INVALID_QUOTATION_LINE PLS_INTEGER := -8;
12 INVALID_ASL PLS_INTEGER := -9;
13 INVALID_ITEM PLS_INTEGER := -10;
14
15 VALID_FOR_EXTRACT PLS_INTEGER := 0;
16 /* Diagnostic messages for not extracted item
17 1. The following item was not extracted from the requisition template
18 because the inactive date of the template is earlier than today:
19 [ITEM_NUMBER]. To extract this item, the date must be tomorrow or
20 later. The item exists on the following template: [TEMPLATE_NAME].
21 The template exists in the following operating unit:
22 [OPERATING_UNIT_NAME].
23 2. The following item was not extracted from the requisition template
24 because the template was copied from an unapproved, cancelled, closed,
25 finally closed, or frozen blanket purchase agreement: [ITEM_NUMBER].
26 The item exists on the following template: [TEMPLATE_NAME]. The
27 template exists in the following operating unit: [OPERATING_UNIT_NAME].
28 3. The following item was not extracted from the requisition template
29 because the template was copied from a blanket purchase agreements
30 whose header effective dates do not include today: [ITEM_NUMBER]. The
31 item exists on the following template: [TEMPLATE_NAME]. The template
32 exists in the following operating unit: [OPERATING_UNIT_NAME].
33 4. The following item was not extracted from the requisition template
34 because the blanket purchase agreement line from which the template was
35 copied is expired, cancelled, closed, or finally closed: [ITEM_NUMBER].
36 The item exists on the following template: [TEMPLATE_NAME]. The template
37 exists in the following operating unit: [OPERATING_UNIT_NAME].
38 5. The following item was not extracted from the requisition template
39 because the line type of the blanket purchase agreement line from which
40 the template was copied is for outside processing: [ITEM_NUMBER]. The
41 item exists on the following template: [TEMPLATE_NAME]. The template
42 exists in the following operating unit: [OPERATING_UNIT_NAME].
43 6. The following item was not extracted from the blanket purchase agreement
44 because the agreement is unapproved, cancelled, closed, finally closed,
45 or frozen: [ITEM_NUMBER]. The agreement number is [AGREEMENT_NUMBER].
46 The agreement exists in the following operating unit:
47 [OPERATING_UNIT_NAME].
48 7. The following item was not extracted from the blanket purchase agreement
49 because the header effective dates do not include today: [ITEM_NUMBER].
50 The agreement number is [AGREEMENT_NUMBER]. The agreement exists in the
51 following operating unit: [OPERATING_UNIT_NAME].
52 8. The following item was not extracted from the blanket purchase agreement
53 because the agreement line is expired, cancelled, closed, or finally
54 closed: [ITEM_NUMBER]. The agreement number is [AGREEMENT_NUMBER]. The
55 agreement exists in the following operating unit: [OPERATING_UNIT_NAME].
56 9. The following item was not extracted from the blanket purchase agreement
57 because the agreement line type is for outside processing: [ITEM_NUMBER].
58 The agreement number is [AGREEMENT_NUMBER]. The agreement exists in the
59 following operating unit: [OPERATING_UNIT_NAME].
60 10. The following item was not extracted from the quotation because the
61 quotation is not a catalog quotation with an active status: [ITEM_NUMBER].
62 The quotation number is [QUOTATION_NUMBER]. The quotation exists in the
63 following operating unit: [OPERATING_UNIT_NAME].
64 11. The following item was not extracted from the quotation because the
65 quotation is not approved for requisitions: [ITEM_NUMBER]. To extract
66 the item, the quotation must have an effective price break that is
67 approved for 'All Orders' or 'Requisitions'. The quotation number is
68 [QUOTATION_NUMBER]. The quotation exists in the following operating
69 unit: [OPERATING_UNIT_NAME].
70 12. The following item was not extracted from the quotation because the
71 header effective dates do not include today: [ITEM_NUMBER]. The
72 quotation number is [QUOTATION_NUMBER]. The quotation exists in the
73 following operating unit: [OPERATING_UNIT_NAME].
74 13. The following item was not extracted from the approved supplier list(ASL)
75 because the ASL entry is disabled for the supplier: [ITEM_NUMBER].
76 The inventory organization for the item is: [INVENTORY_ORGANIZATION].
77 The supplier is: [SUPPLIER_NAME].
78 14. The following item was not extracted from the approved supplier list(ASL)
79 because the ASL entry for the item indicates the supplier is not allowed
80 to source the item: [ITEM_NUMBER]. To extract the item from the ASL,
81 the status and business rules for the entry need to indicate the supplier
82 is allowed to source the item. The inventory organization for the item is:
83 [INVENTORY_ORGANIZATION]. The supplier is: [SUPPLIER_NAME].
84 15. The following item was not extracted from the approved supplier list
85 because there is no list price defined for the item: [ITEM_NUMBER].
86 The item exists in the following inventory organization:
87 [INVENTORY_ORGANIZATION]. The supplier is: [SUPPLIER_NAME].
88 16. The following item was not extracted because the item is either not
89 defined as purchasable or is an outside processing item: [ITEM_NUMBER].
90 The item exists in the following inventory organization:
91 [INVENTORY_ORGANIZATION].
92 17. The following item was not extracted because the item is not defined
93 as internally orderable: [ITEM_NUMBER]. The item exists in the
94 following inventory organization: [INVENTORY_ORGANIZATION].
95 18. The following item was not extracted because the item is not defined
96 as purchasable or internally orderable: [ITEM_NUMBER]. The item exists
97 in the following inventory organization: [INVENTORY_ORGANIZATION].
98 19. The following item was not extracted because there is no list price
99 defined for the item: [ITEM_NUMBER]. The item exists in the following
100 inventory organization: [INVENTORY_ORGANIZATION].
101 20. The following item was not extracted because the category to which
102 it belongs has not yet been extracted: [ITEM_NUMBER]. Extract the
103 following category first: [ITEM_CATEGORY]. Then extract the item.
104 The item exists in the following operating unit:
105 [OPERATING_UNIT_NAME].
106 21. The following item was not extracted from the requisition template
107 because the template header has not yet been extracted: [ITEM_NUMBER].
108 Extract the following template header first: [TEMPLATE_NAME]. Then
109 extract the item. The template exists in the following operating unit:
110 [OPERATING_UNIT_NAME].
111 22. The following item was not extracted from the global agreement:
112 [ITEM_NUMBER], because the agreement is not enabled in the following
113 operating unit: [OPERATING_UNIT_NAME]. The agreement number is
114 [AGREEMENT_NUMBER].
115 23. The following item was not extracted from the global agreement:
116 [ITEM_NUMBER], because the supplier site [SUPPLIER_SITE_CODE] is
117 invalid in the following operating unit:
118 [OPERATING_UNIT_NAME]. The agreement number is [AGREEMENT_NUMBER].
119 24. The following item was not extracted from the global agreement:
120 [ITEM_NUMBER], because the item is invalid in the following operating
121 unit: [OPERATING_UNIT_NAME]. The agreement number is [AGREEMENT_NUMBER].
122 */
123 INACTIVE_TEMPLATE PLS_INTEGER := 1;
124 TEMPLATE_INACTIVE_BLANKET PLS_INTEGER := 2;
125 TEMPLATE_INEFFECTIVE_BLANKET PLS_INTEGER := 3;
126 TEMPLATE_INACTIVE_BLANKET_LINE PLS_INTEGER := 4;
127 TEMPLATE_OUTSIDE_BLANKET PLS_INTEGER := 5;
128 INACTIVE_BLANKET PLS_INTEGER := 6;
129 INEFFECTIVE_BLANKET PLS_INTEGER := 7;
130 INACTIVE_BLANKET_LINE PLS_INTEGER := 8;
131 OUTSIDE_BLANKET PLS_INTEGER := 9;
132 INACTIVE_QUOTATION PLS_INTEGER := 10;
133 QUOTATION_NO_EFFECTIVE_PRICE PLS_INTEGER := 11;
134 INEFFECTIVE_QUOTATION PLS_INTEGER := 12;
135 DISABLED_ASL PLS_INTEGER := 13;
136 UNALLOWED_ASL PLS_INTEGER := 14;
137 ASL_NO_PRICE PLS_INTEGER := 15;
138 UNPURCHASABLE_OUTSIDE PLS_INTEGER := 16;
139 NOTINTERNAL PLS_INTEGER := 17;
140 UNPURCHASABLE_NOTINTERNAL PLS_INTEGER := 18;
141 ITEM_NO_PRICE PLS_INTEGER := 19;
142 CATEGORY_NOT_EXTRACTED PLS_INTEGER := 20;
143 TEMPLATE_HEADER_NOT_EXTRACTED PLS_INTEGER := 21;
144 GLOBAL_AGREEMENT_DISABLED PLS_INTEGER := 22;
145 GLOBAL_AGREEMENT_INVALID_SITE PLS_INTEGER := 23;
146 GLOBAL_AGREEMENT_INVALID_ITEM PLS_INTEGER := 24;
147 GLOBAL_AGREEMENT_INVALID_UOM PLS_INTEGER := 25;
148
149 -- Not Extracted Category
150 NOT_WEBENABLED_CATEGORY PLS_INTEGER := 30;
151 INACTIVE_CATEGORY PLS_INTEGER := 31;
152 INVALID_CATEGORY_SET PLS_INTEGER := 32;
153 INACTIVE_TEMPLATE_HEADER PLS_INTEGER := 33;
154
155 --------------------------------------------------------------
156 -- Construct Message Procedures --
157 --------------------------------------------------------------
158 -- Get operating unit name
159 FUNCTION getOperatingUnit(pOperatingUnitId IN NUMBER)
160 RETURN VARCHAR2;
161
162 -- Get inventory organization name
163 FUNCTION getInventoryOrg(pInventoryOrgId IN NUMBER)
164 RETURN VARCHAR2;
165
166 -- Construct message, usually pOrgName carries Operating Unit or
167 -- Inventory Organization, pDocName carries document number, and
168 -- pExtraValue carries line number, item number or description,
169 -- or supplier site code.
170 FUNCTION constructMessage(pStatus IN NUMBER,
171 pOrgName IN VARCHAR2,
172 pDocName IN VARCHAR2,
173 pExtraValue IN VARCHAR2,
174 pExtraValue2 IN VARCHAR2 DEFAULT NULL)
175 RETURN VARCHAR2;
176
177 FUNCTION getPriceReport(p_document_type IN VARCHAR2,
178 p_org_id IN NUMBER,
179 p_inventory_organization_id IN NUMBER,
180 p_status IN NUMBER,
181 p_contract_num IN VARCHAR2,
182 p_internal_item_num IN VARCHAR2,
183 p_description IN VARCHAR2,
184 p_supplier_site_code IN VARCHAR2,
185 p_template_id IN VARCHAR2,
186 p_supplier IN VARCHAR2,
187 p_supplier_part_num IN VARCHAR2)
188 RETURN VARCHAR2;
189 --------------------------------------------------------------
190 -- Main Check Procedures --
191 --------------------------------------------------------------
192 -- Check classification status
193 FUNCTION checkClassStatus(pType IN VARCHAR2,
194 pValue IN VARCHAR2,
195 pMessage OUT NOCOPY VARCHAR2)
196 RETURN NUMBER;
197
198 FUNCTION checkClassStatus(pType IN VARCHAR2,
199 pValue IN VARCHAR2)
200 RETURN VARCHAR2;
201
202 -- Check item status
203 FUNCTION checkItemStatus(pOperatingUnitId IN NUMBER,
204 pType IN VARCHAR2,
205 pValue1 IN VARCHAR2,
206 pValue2 IN VARCHAR2,
207 pMessage OUT NOCOPY VARCHAR2)
208 RETURN NUMBER;
209
210 FUNCTION checkItemStatus(pBusinessGroup IN VARCHAR2,
211 pOperatingUnit IN VARCHAR2,
212 pType IN VARCHAR2,
213 pValue1 IN VARCHAR2,
214 pValue2 IN VARCHAR2,
215 pMessage OUT NOCOPY VARCHAR2)
216 RETURN NUMBER;
217 FUNCTION checkItemStatus(pOperatingUnitId IN NUMBER,
218 pType IN VARCHAR2,
219 pValue1 IN VARCHAR2,
220 pValue2 IN VARCHAR2)
221 RETURN VARCHAR2;
222
223 FUNCTION checkItemStatus(pBusinessGroup IN VARCHAR2,
224 pOperatingUnit IN VARCHAR2,
225 pType IN VARCHAR2,
226 pValue1 IN VARCHAR2,
227 pValue2 IN VARCHAR2)
228 RETURN VARCHAR2;
229
230 FUNCTION getStatusString(pStatus IN NUMBER)
231 RETURN VARCHAR2;
232
233 FUNCTION getContractLineStatus(p_contract_line_id IN NUMBER,
234 p_test_mode IN VARCHAR2)
235 RETURN NUMBER;
236
237 -- Centralized Procurement Impacts project # pcreddy
238 -- Pass the values for purchasing_org's purchasing_enabled flag,
239 -- outside_operation flag and uom_code
240 FUNCTION getGlobalAgreementStatus(p_enabled_flag IN VARCHAR2,
241 p_purchasing_site IN VARCHAR2,
242 p_inactive_date IN DATE,
243 p_local_purchasing_enabled IN VARCHAR2,
244 p_local_outside_operation IN VARCHAR2,
245 p_local_uom_code IN VARCHAR2,
246 p_purchasing_enabled IN VARCHAR2,
247 p_outside_operation IN VARCHAR2,
248 p_uom_code IN VARCHAR2,
249 p_purchasing_uom_code IN VARCHAR2,
250 p_test_mode IN VARCHAR2)
251 RETURN NUMBER;
252
253 --Bug#3464695
254 FUNCTION getTemplateLineStatus(p_template_id IN VARCHAR2,
255 p_template_line_id IN NUMBER,
256 p_org_id IN NUMBER,
257 p_inactive_date IN DATE,
258 p_contract_line_id IN NUMBER,
259 p_test_mode IN VARCHAR2)
260 RETURN NUMBER;
261
262 --Bug#3464695
263 FUNCTION getASLStatus(p_asl_id IN NUMBER,
264 p_disable_flag IN VARCHAR2,
265 p_asl_status_id IN NUMBER,
266 p_item_price IN NUMBER,
267 p_test_mode IN VARCHAR2)
268 RETURN NUMBER;
269 FUNCTION getPurchasingItemStatus(p_purchasing_enabled_flag IN VARCHAR2,
270 p_outside_operation_flag IN VARCHAR2,
271 p_list_price_per_unit IN NUMBER,
272 p_test_mode IN VARCHAR2)
273 RETURN NUMBER;
274 FUNCTION getInternalItemStatus(p_internal_order_enabled_flag IN VARCHAR2,
275 p_test_mode IN VARCHAR2)
276 RETURN NUMBER;
277 FUNCTION getPriceStatus(p_price_type IN VARCHAR2,
278 p_row_id IN ROWID,
279 p_test_mode IN VARCHAR2)
280 RETURN NUMBER;
281 FUNCTION isValidExtPrice(pDocumentType IN NUMBER,
282 pStatus IN NUMBER,
283 pLoadContract IN VARCHAR2,
284 pLoadTemplateLine IN VARCHAR2,
285 pLoadItemMaster IN VARCHAR2,
286 pLoadInternalItem IN VARCHAR2)
287 RETURN NUMBER;
288
289 END ICX_POR_EXT_DIAG;
[Home] [Help]