1 PACKAGE ICX_POR_EXT_TEST AUTHID CURRENT_USER AS
2 /* $Header: ICXEXTTS.pls 115.5 2003/07/08 12:16:17 sosingha ship $*/
3
4 TEST_USER_ID PLS_INTEGER := -99999;
5
6 --------------------------------------------------------------
7 -- Global Variables --
8 --------------------------------------------------------------
9 gCategorySetId NUMBER;
10 gValidateFlag VARCHAR2(1);
11 gStructureId NUMBER;
12 gTestMode VARCHAR2(1) := NULL;
13 gBaseLang ICX_CAT_ITEMS_TLP.language%TYPE;
14 gCommitSize PLS_INTEGER := 2000;
15
16 -- Utilities
17 PROCEDURE setCommitSize(pCommitSize NUMBER);
18 PROCEDURE setTestMode(pTestMode VARCHAR2);
19 PROCEDURE setTableSpace(pTableTS VARCHAR2,
20 pIndexTS VARCHAR2);
21 PROCEDURE createTables;
22 PROCEDURE prepare(pCreateTables VARCHAR2 DEFAULT NULL);
23 PROCEDURE dropTables;
24 PROCEDURE cleanupData;
25 PROCEDURE cleanup;
26
27 -- Classification
28 PROCEDURE createCategory(p_category_id IN NUMBER,
29 p_concatenated_segments IN VARCHAR2,
30 p_description IN VARCHAR2,
31 p_web_status IN VARCHAR2,
32 p_start_date_active IN DATE,
33 p_end_date_active IN DATE,
34 p_disable_date IN DATE);
35 PROCEDURE updateCategory(p_category_id IN NUMBER,
36 p_concatenated_segments IN VARCHAR2,
37 p_description IN VARCHAR2,
38 p_web_status IN VARCHAR2,
39 p_start_date_active IN DATE,
40 p_end_date_active IN DATE,
41 p_disable_date IN DATE);
42 PROCEDURE translateCategory(p_category_id IN NUMBER,
43 p_description IN VARCHAR2,
44 p_language IN VARCHAR2);
45 PROCEDURE createTemplateHeader(p_org_id IN NUMBER,
46 p_express_name IN VARCHAR2,
47 p_type_lookup_code IN VARCHAR2,
48 p_inactive_date IN DATE);
49 PROCEDURE updateTemplateHeader(p_org_id IN NUMBER,
50 p_express_name IN VARCHAR2,
51 p_inactive_date IN DATE);
52
53 FUNCTION existCategory(p_category_key IN VARCHAR2,
54 p_category_name IN VARCHAR2,
55 p_category_type IN NUMBER)
56 RETURN BOOLEAN;
57 FUNCTION notExistCategory(p_category_key IN VARCHAR2)
58 RETURN BOOLEAN;
59 FUNCTION existCategoryTL(p_category_key IN VARCHAR2,
60 p_category_name IN VARCHAR2,
61 p_language IN VARCHAR2)
62 RETURN BOOLEAN;
63
64 -- Item
65 PROCEDURE createGSB(p_set_of_books_id IN NUMBER,
66 p_currency_code IN VARCHAR2);
67
68 PROCEDURE createFSP(p_org_id IN NUMBER,
69 p_inventory_organization_id IN NUMBER,
70 p_set_of_books_id IN NUMBER);
71 PROCEDURE createItem(p_inventory_item_id IN NUMBER,
72 p_organization_id IN NUMBER,
73 p_concatenated_segments IN VARCHAR2,
74 p_purchasing_enabled_flag IN VARCHAR2,
75 p_outside_operation_flag IN VARCHAR2,
76 p_internal_order_enabled_flag IN VARCHAR2,
77 p_list_price_per_unit IN NUMBER,
78 p_primary_uom_code IN VARCHAR2,
79 p_replenish_to_order_flag IN VARCHAR2,
80 p_base_item_id IN NUMBER,
81 p_auto_created_config_flag IN VARCHAR2,
82 p_unit_of_issue IN VARCHAR2,
83 p_description IN VARCHAR2,
84 p_category_id IN NUMBER);
85 PROCEDURE updateItem(p_inventory_item_id IN NUMBER,
86 p_organization_id IN NUMBER,
87 p_concatenated_segments IN VARCHAR2,
88 p_purchasing_enabled_flag IN VARCHAR2,
89 p_outside_operation_flag IN VARCHAR2,
90 p_internal_order_enabled_flag IN VARCHAR2,
91 p_list_price_per_unit IN NUMBER,
92 p_primary_uom_code IN VARCHAR2,
93 p_replenish_to_order_flag IN VARCHAR2,
94 p_base_item_id IN NUMBER,
95 p_auto_created_config_flag IN VARCHAR2,
96 p_unit_of_issue IN VARCHAR2,
97 p_description IN VARCHAR2,
98 p_category_id IN NUMBER);
99 PROCEDURE translateItem(p_inventory_item_id IN NUMBER,
100 p_organization_id IN NUMBER,
101 p_description IN VARCHAR2,
102 p_language IN VARCHAR2);
103 PROCEDURE deleteItem(p_inventory_item_id IN NUMBER,
104 p_organization_id IN NUMBER);
105 -- Vendor
106 PROCEDURE createVendor(p_vendor_id IN NUMBER,
107 p_vendor_name IN VARCHAR2);
108 PROCEDURE updateVendor(p_vendor_id IN NUMBER,
109 p_vendor_name IN VARCHAR2);
110 PROCEDURE createVendorSite(p_vendor_site_id IN NUMBER,
111 p_vendor_site_code IN VARCHAR2,
112 p_purchasing_site_flag IN VARCHAR2);
113 PROCEDURE updateVendorSite(p_vendor_site_id IN NUMBER,
114 p_purchasing_site_flag IN VARCHAR2,
115 p_inactive_date IN DATE);
116 -- ASL
117 PROCEDURE createASL(p_asl_id IN NUMBER,
118 p_asl_status_id IN NUMBER,
119 p_owning_organization_id IN NUMBER,
120 p_item_id IN NUMBER,
121 p_category_id IN NUMBER,
122 p_vendor_id IN NUMBER,
123 p_vendor_site_id IN NUMBER,
124 p_primary_vendor_item IN VARCHAR2,
125 p_disable_flag IN VARCHAR2,
126 p_allow_action_flag IN VARCHAR2,
127 p_purchasing_unit_of_measure IN VARCHAR2);
128 PROCEDURE updateASL(p_asl_id IN NUMBER,
129 p_asl_status_id IN NUMBER,
130 p_vendor_site_id IN NUMBER,
131 p_primary_vendor_item IN VARCHAR2,
132 p_disable_flag IN VARCHAR2,
133 p_allow_action_flag IN VARCHAR2,
134 p_purchasing_unit_of_measure IN VARCHAR2);
135 -- Template Line
136 PROCEDURE createTemplateLine(p_org_id IN NUMBER,
137 p_express_name IN VARCHAR2,
138 p_sequence_num IN NUMBER,
139 p_source_type_code IN VARCHAR2,
140 p_po_header_id IN NUMBER,
141 p_po_line_id IN NUMBER,
142 p_item_id IN NUMBER,
143 p_category_id IN NUMBER,
144 p_item_description IN VARCHAR2,
145 p_unit_price IN NUMBER,
146 p_unit_meas_lookup_code IN VARCHAR2,
147 p_suggested_vendor_id IN NUMBER,
148 p_suggested_vendor_site_id IN NUMBER,
149 p_vendor_product_code IN VARCHAR2);
150 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
151 -- Overload Template Line to accept Suggested Quantity
152 PROCEDURE createTemplateLine(p_org_id IN NUMBER,
153 p_express_name IN VARCHAR2,
154 p_sequence_num IN NUMBER,
155 p_source_type_code IN VARCHAR2,
156 p_po_header_id IN NUMBER,
157 p_po_line_id IN NUMBER,
158 p_item_id IN NUMBER,
159 p_category_id IN NUMBER,
160 p_item_description IN VARCHAR2,
161 p_unit_price IN NUMBER,
162 -- FPJ Bug# 3007068 sosingha: Extractor Changes for Kit Support project
163 p_suggested_quantity IN NUMBER,
164 p_unit_meas_lookup_code IN VARCHAR2,
165 p_suggested_vendor_id IN NUMBER,
166 p_suggested_vendor_site_id IN NUMBER,
167 p_vendor_product_code IN VARCHAR2);
168 PROCEDURE updateTemplateLine(p_org_id IN NUMBER,
169 p_express_name IN VARCHAR2,
170 p_sequence_num IN NUMBER,
171 p_po_header_id IN NUMBER,
172 p_po_line_id IN NUMBER,
173 p_item_description IN VARCHAR2,
174 p_unit_price IN NUMBER,
175 -- FPJ Bug# 3007068 sosingha: Extractor Changes for Kit Support project
176 p_suggested_quantity IN NUMBER,
177 p_unit_meas_lookup_code IN VARCHAR2,
178 p_suggested_vendor_site_id IN NUMBER,
179 p_vendor_product_code IN VARCHAR2);
180
181 -- Contract
182 PROCEDURE createContractHeader(p_po_header_id IN NUMBER,
183 p_org_id IN NUMBER,
184 p_segment1 IN VARCHAR2,
185 p_type_lookup_code IN VARCHAR2,
186 p_rate IN NUMBER,
187 p_currency_code IN VARCHAR2,
188 p_vendor_id IN NUMBER,
189 p_vendor_site_id IN NUMBER,
190 p_approved_date IN DATE,
191 p_approved_flag IN VARCHAR2,
192 p_approval_required_flag IN VARCHAR2,
193 p_cancel_flag IN VARCHAR2,
194 p_frozen_flag IN VARCHAR2,
195 p_closed_code IN VARCHAR2,
196 p_status_lookup_code IN VARCHAR2,
197 p_quotation_class_code IN VARCHAR2,
198 p_start_date IN DATE,
199 p_end_date IN DATE,
200 p_global_agreement_flag IN VARCHAR2);
201 PROCEDURE createContractLine(p_po_header_id IN NUMBER,
202 p_po_line_id IN NUMBER,
203 p_org_id IN NUMBER,
204 p_line_num IN NUMBER,
205 p_item_id IN NUMBER,
206 p_item_description IN VARCHAR2,
207 p_vendor_product_num IN VARCHAR2,
208 p_line_type_id IN NUMBER,
209 p_category_id IN NUMBER,
210 p_unit_price IN NUMBER,
211 p_unit_meas_lookup_code IN VARCHAR2,
212 p_attribute13 IN VARCHAR2,
213 p_attribute14 IN VARCHAR2,
214 p_cancel_flag IN VARCHAR2,
215 p_closed_code IN VARCHAR2,
216 p_expiration_date IN DATE,
217 p_outside_operation_flag IN VARCHAR2);
218 -- FPJ FPSL Extractor Changes
219 -- Add 5 parameters for Amount, Allow Price Override Flag,
220 -- Not to Exceed Price, Value Basis, Purchase Basis
221 -- Create a contract line
222 PROCEDURE createContractLine(p_po_header_id IN NUMBER,
223 p_po_line_id IN NUMBER,
224 p_org_id IN NUMBER,
225 p_line_num IN NUMBER,
226 p_item_id IN NUMBER,
227 p_item_description IN VARCHAR2,
228 p_vendor_product_num IN VARCHAR2,
229 p_line_type_id IN NUMBER,
230 p_category_id IN NUMBER,
231 p_unit_price IN NUMBER,
232 p_unit_meas_lookup_code IN VARCHAR2,
233 p_attribute13 IN VARCHAR2,
234 p_attribute14 IN VARCHAR2,
235 p_cancel_flag IN VARCHAR2,
236 p_closed_code IN VARCHAR2,
237 p_expiration_date IN DATE,
238 p_outside_operation_flag IN VARCHAR2,
239 p_amount IN NUMBER,
240 p_allow_price_override_flag IN VARCHAR2,
241 p_not_to_exceed_price IN NUMBER,
242 p_value_basis IN VARCHAR2,
243 p_purchase_basis IN VARCHAR2);
244 PROCEDURE updateContractHeader(p_po_header_id IN NUMBER,
245 p_rate IN NUMBER,
246 p_currency_code IN VARCHAR2,
247 p_vendor_site_id IN NUMBER,
248 p_approved_date IN DATE,
249 p_approved_flag IN VARCHAR2,
250 p_approval_required_flag IN VARCHAR2,
251 p_cancel_flag IN VARCHAR2,
252 p_frozen_flag IN VARCHAR2,
253 p_closed_code IN VARCHAR2,
254 p_start_date IN DATE,
255 p_end_date IN DATE,
256 p_global_agreement_flag IN VARCHAR2);
257 PROCEDURE updateContractLine(p_po_line_id IN NUMBER,
258 p_item_description IN VARCHAR2,
259 p_vendor_product_num IN VARCHAR2,
260 p_line_type_id IN NUMBER,
261 p_category_id IN NUMBER,
262 p_unit_price IN NUMBER,
263 p_unit_meas_lookup_code IN VARCHAR2,
264 p_attribute13 IN VARCHAR2,
265 p_attribute14 IN VARCHAR2,
266 p_cancel_flag IN VARCHAR2,
267 p_closed_code IN VARCHAR2,
268 p_creation_date IN DATE,
269 p_expiration_date IN DATE,
270 p_outside_operation_flag IN VARCHAR2);
271 -- Update a contract line
272 -- FPJ FPSL Extractor Changes
273 -- Add 3 parameters for Amount, Allow Price Override Flag and Not to Exceed Price
274 PROCEDURE updateContractLine(p_po_line_id IN NUMBER,
275 p_item_description IN VARCHAR2,
276 p_vendor_product_num IN VARCHAR2,
277 p_line_type_id IN NUMBER,
278 p_category_id IN NUMBER,
279 p_unit_price IN NUMBER,
280 p_unit_meas_lookup_code IN VARCHAR2,
281 p_attribute13 IN VARCHAR2,
282 p_attribute14 IN VARCHAR2,
283 p_cancel_flag IN VARCHAR2,
284 p_closed_code IN VARCHAR2,
285 p_creation_date IN DATE,
286 p_expiration_date IN DATE,
287 p_outside_operation_flag IN VARCHAR2,
288 p_amount IN NUMBER,
289 p_allow_price_override_flag IN VARCHAR2,
290 p_not_to_exceed_price IN NUMBER);
291 PROCEDURE createQuoteLL(p_line_location_id IN NUMBER,
292 p_po_line_id IN NUMBER,
293 p_start_date IN DATE,
294 p_end_date IN DATE,
295 p_approval_type IN VARCHAR2,
296 p_start_date_active IN DATE,
297 p_end_date_active IN DATE);
298 PROCEDURE updateQuoteLL(p_line_location_id IN NUMBER,
299 p_start_date IN DATE,
300 p_end_date IN DATE,
301 p_approval_type IN VARCHAR2,
302 p_start_date_active IN DATE,
303 p_end_date_active IN DATE);
304 PROCEDURE createGlobalA(p_po_header_id IN NUMBER,
305 p_organization_id IN NUMBER,
306 p_enabled_flag IN VARCHAR2,
307 p_vendor_site_id IN NUMBER,
308 p_purchasing_org_id IN NUMBER);
309 PROCEDURE updateGlobalA(p_po_header_id IN NUMBER,
310 p_organization_id IN NUMBER,
311 p_enabled_flag IN VARCHAR2,
312 p_vendor_site_id IN NUMBER,
313 p_purchasing_org_id IN NUMBER);
314
315 FUNCTION existItemsB(p_rt_item_id OUT NOCOPY NUMBER,
316 p_org_id IN NUMBER,
317 p_supplier_id IN NUMBER,
318 p_supplier IN VARCHAR2,
319 p_supplier_part_num IN VARCHAR2,
320 p_internal_item_id IN NUMBER,
321 p_internal_item_num IN VARCHAR2,
322 p_extractor_updated_flag IN VARCHAR2,
323 p_internal_flag IN VARCHAR2 DEFAULT NULL)
324 RETURN BOOLEAN;
325 FUNCTION notExistItemsB(p_org_id IN NUMBER,
326 p_supplier_id IN NUMBER,
327 p_supplier_part_num IN VARCHAR2,
328 p_internal_item_id IN NUMBER,
329 p_internal_flag IN VARCHAR2 DEFAULT NULL)
330 RETURN BOOLEAN;
331 FUNCTION existItemsTLP(p_rt_item_id IN NUMBER,
332 p_language IN VARCHAR2,
333 p_item_source_type IN VARCHAR2,
334 p_search_type IN VARCHAR2,
335 p_primary_category_id OUT NOCOPY NUMBER,
336 p_primary_category_name IN VARCHAR2,
337 p_internal_item_id IN NUMBER,
338 p_internal_item_num IN VARCHAR2,
339 p_supplier_id IN NUMBER,
340 p_supplier IN VARCHAR2,
341 p_supplier_part_num IN VARCHAR2,
342 p_description IN VARCHAR2,
343 p_picture IN VARCHAR2,
344 p_picture_url IN VARCHAR2)
345 RETURN BOOLEAN;
346 FUNCTION notExistItemsTLP(p_rt_item_id IN NUMBER,
347 p_language IN VARCHAR2)
348 RETURN BOOLEAN;
349 FUNCTION existCateoryItems(p_rt_item_id IN NUMBER,
350 p_rt_category_id IN NUMBER)
354 RETURN BOOLEAN;
351 RETURN BOOLEAN;
352 FUNCTION notExistCateoryItems(p_rt_item_id IN NUMBER,
353 p_rt_category_id IN NUMBER)
355 FUNCTION existExtItemsTLP(p_rt_item_id IN NUMBER,
356 p_rt_category_id IN NUMBER)
357 RETURN BOOLEAN;
358 FUNCTION notExistExtItemsTLP(p_rt_item_id IN NUMBER,
359 p_rt_category_id IN NUMBER)
360 RETURN BOOLEAN;
361 FUNCTION existItemPrices(p_rt_item_id IN NUMBER,
362 p_org_id IN VARCHAR2,
363 p_price_type IN VARCHAR2,
364 p_active_flag IN VARCHAR2,
365 p_asl_id IN NUMBER,
366 p_contract_id IN VARCHAR2,
367 p_contract_line_id IN NUMBER,
368 p_template_id IN VARCHAR2,
369 p_template_line_id IN NUMBER,
370 p_inventory_item_id IN VARCHAR2,
371 p_mtl_category_id IN VARCHAR2,
372 p_search_type IN VARCHAR2,
373 p_unit_price IN VARCHAR2,
374 p_currency IN VARCHAR2,
375 p_unit_of_measure IN VARCHAR2,
376 p_supplier_site_id IN VARCHAR2,
377 p_supplier_site_code IN VARCHAR2,
378 p_contract_num IN VARCHAR2,
379 p_contract_line_num IN NUMBER,
380 p_local_rt_item_id IN NUMBER DEFAULT NULL)
381 RETURN BOOLEAN;
382 FUNCTION notExistItemPrices(p_rt_item_id IN NUMBER,
383 p_org_id IN VARCHAR2,
384 p_price_type IN VARCHAR2,
385 p_active_flag IN VARCHAR2,
386 p_asl_id IN NUMBER,
387 p_contract_id IN VARCHAR2,
388 p_contract_line_id IN NUMBER,
389 p_template_id IN VARCHAR2,
390 p_template_line_id IN NUMBER,
391 p_inventory_item_id IN VARCHAR2)
392 RETURN BOOLEAN;
393
394 -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
395 FUNCTION checkSuggestedQuantity(p_rt_item_id IN NUMBER,
396 p_org_id IN VARCHAR2,
397 p_price_type IN VARCHAR2,
398 p_active_flag IN VARCHAR2,
399 p_template_id IN VARCHAR2,
400 p_template_line_id IN NUMBER,
401 p_inventory_item_id IN VARCHAR2,
402 p_mtl_category_id IN VARCHAR2,
403 -- FPJ Bug# 3007068 sosingha: Extractor Changes for Kit Support Project
404 p_suggested_quantity IN NUMBER,
405 p_local_rt_item_id IN NUMBER)
406 RETURN BOOLEAN;
407
408
409 END ICX_POR_EXT_TEST;
[Home] [Help]