1 PACKAGE BODY PO_PDOI_GRP AS
2 /* $Header: PO_PDOI_GRP.plb 120.6 2005/09/20 17:42 bao noship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_GRP');
6
7 -------------------------------------------------------
8 -------------- PUBLIC PROCEDURES ----------------------
9 -------------------------------------------------------
10
11 -----------------------------------------------------------------------
12 --Start of Comments
13 --Name: start_process
14 --Function:
15 -- Group procedure for PDOI, which is used for importing and updating
16 -- blankets, quotations or standard purchase orders
17 --Parameters:
18 --IN:
19 --p_api_version
20 -- API version of the program caller assumes
21 --p_init_msg_list
22 -- FND_API.G_TRUE if caller expects PDOI to initialize the message stack
23 -- maintained by FND_MSG_PUB.
24 -- FND_API.G_FALSE otherwise
25 --p_validation_level
26 -- Currently this parameter has no effect. PDOI always does full validation
27 --p_commit
28 -- Whether PDOI will issue commits
29 -- FND_API.G_TRUE if caller expects PDOI to commit data
30 -- FND_API.G_FALSE otherwise
31 --p_gather_intf_tbl_stat
32 -- Whether PDOI gather table statistics before processing.
33 -- 'Y' if statistics should be gathered. Consider this if a large
34 -- number of records are being inserted into the interface table
35 -- 'N' otherwise
36 --p_calling_module
37 -- The module name of the calling program. Value 'CATALOG UPLOAD' is reserved
38 -- to be used by calling from Calog upload program
39 --p_selected_batch_id
40 -- Batch id parameter. If this is specified, only the records with this batch
41 -- id will be processed.
42 --p_batch_size
43 -- Used for performance tuning. It specifies the number of header interface
44 -- records that will be processed for each bulk fetching. Default number is
45 -- PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE
46 --p_buyer_id
47 -- Default buyer of the document to be imported
48 --p_document_type
49 -- Type of the document that will be processed. Possible values are:
50 -- STANDARD, BLANKET, QUOTATION
51 --p_document_subtype
52 -- Default document subtype. Use it only if p_document_type is 'QUOTATION'.
53 --p_create_items
54 -- Specifies whether an item will be created as inventory item if the specified
55 -- item does not exist in the system.
56 -- 'Y' if item should be created
57 -- 'N' if item should NOT be created
58 --p_create_sourcing_rules_flag
59 -- Whether sourcing rules and ASL should be created as part of the creation
60 -- of blanket and quotation line
61 -- 'Y' if sourcing rules and ASL should be created
62 -- 'N' otherwise
63 --p_rel_gen_method
64 -- Release generation method of the ASL
65 --p_sourcing_level
66 -- Level of the sourcing rules assignment. Possible values:
67 -- ITEM, ITEM-ORGANIZATION
68 --p_sourcing_inv_org_id
69 -- If sourcing level is 'ITEM-ORGANIZATION', the organization where the
70 -- sourcing rule will be created in
71 --p_approved_status
72 -- Intended approval status the document after import. Possible values
73 -- INCOMPLETE - Incomplete documents
74 -- INITIATE APPROVAL - This means that the document will be submitted for
75 -- approval through approval workflow
76 -- APPROVED - Import as approved without submitting through approval wf
77 --p_process_code
78 -- Type of interface records to be processed. If this is specified, only
79 -- records with the specified process code will be processed
80 --p_interface_header_id
81 -- If this is specified, only record with this interface_header_id will be
82 -- processed
83 --p_org_id
84 -- Operating Unit where this PDOI will be running in. If this is not specified,
85 -- Current operating unit will be the operating unit for PDOI to run.
86 --p_ga_flag
87 -- Whether the blanket will be imported as global agreement or not.
88 -- 'Y' if blanekets should be imported as global agreements
89 --IN OUT:
90 --OUT:
91 --x_return_status
92 -- Return status of the API.
93 -- FND_API.G_RET_STS_SUCCESS if API is successful
94 -- FND_API.G_RET_STS_ERR if there are user errors
95 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
96 --End of Comments
97 ------------------------------------------------------------------------
98 PROCEDURE start_process
99 ( p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2,
101 p_validation_level IN NUMBER,
102 p_commit IN VARCHAR2,
103 x_return_status OUT NOCOPY VARCHAR2,
104 p_gather_intf_tbl_stat IN VARCHAR2,
105 p_calling_module IN VARCHAR2,
106 p_selected_batch_id IN NUMBER,
107 p_batch_size IN NUMBER,
108 p_buyer_id IN NUMBER,
109 p_document_type IN VARCHAR2,
110 p_document_subtype IN VARCHAR2,
111 p_create_items IN VARCHAR2,
112 p_create_sourcing_rules_flag IN VARCHAR2,
113 p_rel_gen_method IN VARCHAR2,
114 p_sourcing_level IN VARCHAR2,
115 p_sourcing_inv_org_id IN NUMBER,
116 p_approved_status IN VARCHAR2,
117 p_process_code IN VARCHAR2,
118 p_interface_header_id IN NUMBER,
119 p_org_id IN NUMBER,
120 p_ga_flag IN VARCHAR2
121 ) IS
122
123 d_api_version CONSTANT NUMBER := 1.0;
124 d_api_name CONSTANT VARCHAR2(30) := 'start_process';
125 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
126 d_position NUMBER;
127
128 l_processed_lines_count NUMBER;
129 l_rejected_lines_count NUMBER;
130 l_err_tolerance_exceeded VARCHAR2(1);
131
132 BEGIN
133
134 d_position := 0;
135
136 IF (PO_LOG.d_proc) THEN
137 PO_LOG.proc_end(d_module);
138 END IF;
139
140 IF (p_init_msg_list = FND_API.G_TRUE) THEN
141 FND_MSG_PUB.initialize;
142 END IF;
143
144 IF (NOT FND_API.Compatible_API_Call
145 ( p_current_version_number => d_api_version,
146 p_caller_version_number => p_api_version,
147 p_api_name => d_api_name,
148 p_pkg_name => d_pkg_name
149 )
150 ) THEN
151
152 d_position := 10;
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF; -- not compatible_api
155
156 d_position := 20;
157
158 PO_PDOI_PVT.start_process
159 ( p_api_version => 1.0,
160 p_init_msg_list => FND_API.G_FALSE,
161 p_validation_level => p_validation_level,
162 p_commit => p_commit,
163 x_return_status => x_return_status,
164 p_gather_intf_tbl_stat => p_gather_intf_tbl_stat,
165 p_calling_module => p_calling_module,
166 p_selected_batch_id => p_selected_batch_id,
167 p_batch_size => p_batch_size,
168 p_buyer_id => p_buyer_id,
169 p_document_type => p_document_type,
170 p_document_subtype => p_document_subtype,
171 p_create_items => p_create_items,
172 p_create_sourcing_rules_flag => p_create_sourcing_rules_flag,
173 p_rel_gen_method => p_rel_gen_method,
174 p_sourcing_level => p_sourcing_level,
175 p_sourcing_inv_org_id => p_sourcing_inv_org_id,
176 p_approved_status => p_approved_status,
177 p_process_code => p_process_code,
178 p_interface_header_id => p_interface_header_id,
179 p_org_id => p_org_id,
180 p_ga_flag => p_ga_flag,
181 p_submit_dft_flag => NULL,
182 p_role => PO_GLOBAL.g_ROLE_BUYER,
183 p_catalog_to_expire => NULL,
184 p_err_lines_tolerance => NULL,
185 x_processed_lines_count => l_processed_lines_count,
186 x_rejected_lines_count => l_rejected_lines_count,
187 x_err_tolerance_exceeded => l_err_tolerance_exceeded
188 );
189
190 d_position := 30;
191
192 IF (PO_LOG.d_proc) THEN
193 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
194 END IF;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 PO_MESSAGE_S.add_exc_msg
199 ( p_pkg_name => d_pkg_name,
200 p_procedure_name => d_api_name || '.' || d_position
201 );
202 RAISE;
203 END start_process;
204
205
206 -----------------------------------------------------------------------
207 --Start of Comments
208 --Name: catalog_upload
209 --Function:
210 -- This API will be called by iP during catalog upload process. This API
211 -- internally calls PDOI to perform import action.
212 --Parameters:
213 --IN:
214 --p_api_version
215 -- API version of the program caller assumes
216 --p_init_msg_list
217 -- FND_API.G_TRUE if caller expects PDOI to initialize the message stack
218 -- maintained by FND_MSG_PUB.
219 -- FND_API.G_FALSE otherwise
220 --p_validation_level
221 -- Currently this parameter has no effect. PDOI always does full validation
222 --p_commit
223 -- Whether PDOI will issue commits
224 -- FND_API.G_TRUE if caller expects PDOI to commit data
225 -- FND_API.G_FALSE otherwise
226 --p_gather_intf_tbl_stat
227 -- Whether PDOI gather table statistics before processing.
228 -- 'Y' if statistics should be gathered. Consider this if a large
229 -- number of records are being inserted into the interface table
230 -- 'N' otherwise
231 --p_selected_batch_id
232 -- Batch id parameter. If this is specified, only the records with this batch
233 -- id will be processed.
234 --p_batch_size
235 -- Used for performance tuning. It specifies the number of header interface
236 -- records that will be processed for each bulk fetching. Default number is
237 -- PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE
238 --p_buyer_id
239 -- Default buyer of the document to be imported
240 --p_document_type
241 -- Type of the document that will be processed. Possible values are:
242 -- STANDARD, BLANKET, QUOTATION
243 --p_document_subtype
244 -- Default document subtype. Use it only if p_document_type is 'QUOTATION'.
245 --p_create_items
246 -- Specifies whether an item will be created as inventory item if the specified
247 -- item does not exist in the system.
248 -- 'Y' if item should be created
249 -- 'N' if item should NOT be created
250 --p_create_sourcing_rules_flag
251 -- Whether sourcing rules and ASL should be created as part of the creation
252 -- of blanket and quotation line
253 -- 'Y' if sourcing rules and ASL should be created
254 -- 'N' otherwise
255 --p_rel_gen_method
256 -- Release generation method of the ASL
257 --p_sourcing_level
258 -- Level of the sourcing rules assignment. Possible values:
259 -- ITEM, ITEM-ORGANIZATION
260 --p_sourcing_inv_org_id
261 -- If sourcing level is 'ITEM-ORGANIZATION', the organization where the
262 -- sourcing rule will be created in
263 --p_approved_status
264 -- Intended approval status the document after import. Possible values
265 -- INCOMPLETE - Incomplete documents
266 -- INITIATE APPROVAL - This means that the document will be submitted for
267 -- approval through approval workflow
268 -- APPROVED - Import as approved without submitting through approval wf
269 --p_process_code
270 -- Type of interface records to be processed. If this is specified, only
271 -- records with the specified process code will be processed
272 --p_interface_header_id
273 -- If this is specified, only record with this interface_header_id will be
274 -- processed
275 --p_org_id
276 -- Operating Unit where this PDOI will be running in. If this is not specified,
277 -- Current operating unit will be the operating unit for PDOI to run.
278 --p_ga_flag
279 -- Whether the blanket will be imported as global agreement or not.
280 -- 'Y' if blanekets should be imported as global agreements
281 --p_submit_dft_flag
282 -- ** Reserved for catalog upload **
283 -- Determines whether the draft changes should be submitted for buyer's
284 -- acceptance after they pass PDOI validations
285 -- 'Y' if changes should be submitted
286 -- 'N' otherwise
287 --p_role
288 -- ** Reserved for catalog upload ***
289 -- Role of the user calling PDOI. Possible values:
290 -- BUYER, SUPPLIER, CAT ADMIN
291 --p_catalog_to_expire
292 -- ** Reserved for catalog upload ***
293 -- If this is specified, all the lines with this value as the catalog name
294 -- will get expired
295 --p_err_lines_tolerance
296 -- ** Reserved for catalog upload **
297 -- Number of line errors PDOI can take before aborting the program.
298 -- Note: Even if PDOI is aborted, lines that get accepted will continue to
299 -- be processed.
300 --IN OUT:
301 --OUT:
302 --x_return_status
303 -- Return status of the API.
304 -- FND_API.G_RET_STS_SUCCESS if API is successful
305 -- FND_API.G_RET_STS_ERR if there are user errors
306 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
307 --x_error_message
308 -- Concatenation of error in case an error exists
309 --x_processed_lines_count
310 -- ** Populated for catalog upload only **
311 -- Number of lines being processed
312 --x_rejected_lines_count
313 -- ** Populated for catalog upload only **
314 -- Number of lines being rejected
315 --x_err_tolerance_exceeded
316 -- ** Populated for catalog upload only **
317 -- indicates whether error threshold is exceeded
318 -- FND_API.G_TRUE if exceeded
319 -- FND_API.G_FALSE otherwise
320 --End of Comments
321 ------------------------------------------------------------------------
322 PROCEDURE catalog_upload
323 ( p_api_version IN NUMBER,
324 p_init_msg_list IN VARCHAR2,
325 p_validation_level IN NUMBER,
326 p_commit IN VARCHAR2,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_error_message OUT NOCOPY VARCHAR2,
329 p_gather_intf_tbl_stat IN VARCHAR2,
330 p_selected_batch_id IN NUMBER,
331 p_batch_size IN NUMBER,
332 p_buyer_id IN NUMBER,
333 p_document_type IN VARCHAR2,
334 p_document_subtype IN VARCHAR2,
335 p_create_items IN VARCHAR2,
336 p_create_sourcing_rules_flag IN VARCHAR2,
337 p_rel_gen_method IN VARCHAR2,
338 p_sourcing_level IN VARCHAR2,
339 p_sourcing_inv_org_id IN NUMBER,
340 p_approved_status IN VARCHAR2,
341 p_process_code IN VARCHAR2,
342 p_interface_header_id IN NUMBER,
343 p_org_id IN NUMBER,
344 p_ga_flag IN VARCHAR2,
345 p_submit_dft_flag IN VARCHAR2,
346 p_role IN VARCHAR2,
347 p_catalog_to_expire IN VARCHAR2,
348 p_err_lines_tolerance IN NUMBER,
349 x_processed_lines_count OUT NOCOPY NUMBER,
350 x_rejected_lines_count OUT NOCOPY NUMBER,
351 x_err_tolerance_exceeded OUT NOCOPY VARCHAR2
352 ) IS
353
354 d_api_version CONSTANT NUMBER := 1.0;
355 d_api_name CONSTANT VARCHAR2(30) := 'catalog_upload';
356 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
357 d_position NUMBER;
358
359 l_msg_temp VARCHAR2(2000);
360
361 BEGIN
362
363 d_position := 0;
364
365 IF (PO_LOG.d_proc) THEN
366 PO_LOG.proc_end(d_module);
367 END IF;
368
369 IF (p_init_msg_list = FND_API.G_TRUE) THEN
370 FND_MSG_PUB.initialize;
371 END IF;
372
373 IF (NOT FND_API.Compatible_API_Call
374 ( p_current_version_number => d_api_version,
375 p_caller_version_number => p_api_version,
376 p_api_name => d_api_name,
377 p_pkg_name => d_pkg_name
378 )
379 ) THEN
380
381 d_position := 10;
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 END IF; -- not compatible_api
384
385 d_position := 20;
386
387 PO_PDOI_PVT.start_process
388 ( p_api_version => 1.0,
389 p_init_msg_list => FND_API.G_FALSE,
390 p_validation_level => p_validation_level,
391 p_commit => p_commit,
392 x_return_status => x_return_status,
393 p_gather_intf_tbl_stat => p_gather_intf_tbl_stat,
394 p_calling_module => PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD,
395 p_selected_batch_id => p_selected_batch_id,
396 p_batch_size => p_batch_size,
397 p_buyer_id => p_buyer_id,
398 p_document_type => p_document_type,
399 p_document_subtype => p_document_subtype,
400 p_create_items => p_create_items,
401 p_create_sourcing_rules_flag => p_create_sourcing_rules_flag,
402 p_rel_gen_method => p_rel_gen_method,
403 p_sourcing_level => p_sourcing_level,
404 p_sourcing_inv_org_id => p_sourcing_inv_org_id,
405 p_approved_status => p_approved_status,
406 p_process_code => p_process_code,
407 p_interface_header_id => p_interface_header_id,
408 p_org_id => p_org_id,
409 p_ga_flag => p_ga_flag,
410 p_submit_dft_flag => p_submit_dft_flag,
411 p_role => p_role,
412 p_catalog_to_expire => p_catalog_to_expire,
413 p_err_lines_tolerance => p_err_lines_tolerance,
414 x_processed_lines_count => x_processed_lines_count,
415 x_rejected_lines_count => x_rejected_lines_count,
416 x_err_tolerance_exceeded => x_err_tolerance_exceeded
417 );
418
419 -- For Catalog Upload, we need to return error messaeg in case
420 -- an error occurs.
421 IF (x_return_status <> 'S') THEN
422 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
423 l_msg_temp := FND_MSG_PUB.get
424 ( p_msg_index => i,
425 p_encoded => 'F'
426 );
427
428 x_error_message := SUBSTRB(x_error_message || l_msg_temp || ' ',
429 2000);
430 END LOOP;
431 END IF;
432
433
434 d_position := 30;
435
436 IF (PO_LOG.d_proc) THEN
437 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
438 PO_LOG.proc_end(d_module, 'x_processed_liens_count', x_processed_lines_count);
439 PO_LOG.proc_end(d_module, 'x_rejected_lines_count', x_rejected_lines_count);
440 PO_LOG.proc_end(d_module, 'x_err_tolerance_exceeded', x_err_tolerance_exceeded);
441 END IF;
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 PO_MESSAGE_S.add_exc_msg
446 ( p_pkg_name => d_pkg_name,
447 p_procedure_name => d_api_name || '.' || d_position
448 );
449
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451
452 END catalog_upload;
453
454
455 -----------------------------------------------------------------------
456 --Start of Comments
457 --Name: handle_price_tolerance_resp
458 --Function:
459 -- This API handles response from buyer about lines that exceed
460 -- price tolerance
461 --Parameters:
462 --IN:
463 --p_api_version
464 -- API version of the program caller assumes
465 --p_init_msg_list
466 -- FND_API.G_TRUE if caller expects PDOI to initialize the message stack
467 -- maintained by FND_MSG_PUB.
468 -- FND_API.G_FALSE otherwise
469 --p_validation_level
470 -- Currently this parameter has no effect. PDOI always does full validation
471 --p_commit
472 -- Whether PDOI will issue commits
473 -- FND_API.G_TRUE if caller expects PDOI to commit data
474 -- FND_API.G_FALSE otherwise
475 --p_selected_batch_id
476 -- Batch id parameter. If this is specified, only the records with this batch
477 -- id will be processed.
478 --p_document_type
479 -- Type of the document that will be processed. Possible values are:
480 -- STANDARD, BLANKET, QUOTATION
481 --p_document_subtype
482 -- Default document subtype. Use it only if p_document_type is 'QUOTATION'.
483 --p_interface_header_id
484 -- If this is specified, only record with this interface_header_id will be
485 -- processed
486 --p_org_id
487 -- Operating Unit where this PDOI will be running in. If this is not specified,
488 -- Current operating unit will be the operating unit for PDOI to run.
489 --IN OUT:
490 --OUT:
491 --x_return_status
492 -- Return status of the API.
493 -- FND_API.G_RET_STS_SUCCESS if API is successful
494 -- FND_API.G_RET_STS_ERR if there are user errors
495 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
496 --End of Comments
497 ------------------------------------------------------------------------
498 PROCEDURE handle_price_tolerance_resp
499 ( p_api_version IN NUMBER,
500 p_init_msg_list IN VARCHAR2,
501 p_validation_level IN NUMBER,
502 p_commit IN VARCHAR2,
503 x_return_status OUT NOCOPY VARCHAR2,
504 p_selected_batch_id IN NUMBER,
505 p_document_type IN VARCHAR2,
506 p_document_subtype IN VARCHAR2,
507 p_interface_header_id IN NUMBER,
508 p_org_id IN NUMBER
509 ) IS
510
511 d_api_version CONSTANT NUMBER := 1.0;
512 d_api_name CONSTANT VARCHAR2(30) := 'handle_price_tolerance_resp';
513 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
514 d_position NUMBER;
515
516 l_processed_lines_count NUMBER;
517 l_rejected_lines_count NUMBER;
518 l_err_tolerance_exceeded VARCHAR2(1);
519
520 BEGIN
521
522 d_position := 0;
523
524 IF (PO_LOG.d_proc) THEN
525 PO_LOG.proc_end(d_module);
526 END IF;
527
528 IF (p_init_msg_list = FND_API.G_TRUE) THEN
529 FND_MSG_PUB.initialize;
530 END IF;
531
532 IF (NOT FND_API.Compatible_API_Call
533 ( p_current_version_number => d_api_version,
534 p_caller_version_number => p_api_version,
535 p_api_name => d_api_name,
536 p_pkg_name => d_pkg_name
537 )
538 ) THEN
539
540 d_position := 10;
541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
542 END IF; -- not compatible_api
543
544 d_position := 20;
545
546 PO_PDOI_PVT.start_process
547 ( p_api_version => 1.0,
548 p_init_msg_list => FND_API.G_FALSE,
549 p_validation_level => p_validation_level,
550 p_commit => p_commit,
551 x_return_status => x_return_status,
552 p_gather_intf_tbl_stat => 'N',
553 p_calling_module => PO_PDOI_CONSTANTS.g_call_mod_PRICE_TOL_RESP,
554 p_selected_batch_id => p_selected_batch_id,
555 p_batch_size => 1,
556 p_buyer_id => NULL,
557 p_document_type => p_document_type,
558 p_document_subtype => p_document_subtype,
559 p_create_items => 'N',
560 p_create_sourcing_rules_flag => 'N',
561 p_rel_gen_method => NULL,
562 p_sourcing_level => NULL,
563 p_sourcing_inv_org_id => NULL,
564 p_approved_status => NULL,
565 p_process_code => PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED,
566 p_interface_header_id => p_interface_header_id,
567 p_org_id => p_org_id,
568 p_ga_flag => NULL,
569 p_submit_dft_flag => NULL,
570 p_role => PO_GLOBAL.g_ROLE_BUYER,
571 p_catalog_to_expire => NULL,
572 p_err_lines_tolerance => NULL,
573 x_processed_lines_count => l_processed_lines_count,
574 x_rejected_lines_count => l_rejected_lines_count,
575 x_err_tolerance_exceeded => l_err_tolerance_exceeded
576 );
577
578 d_position := 30;
579
580 IF (PO_LOG.d_proc) THEN
581 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
582 END IF;
583
584 EXCEPTION
585 WHEN OTHERS THEN
586 PO_MESSAGE_S.add_exc_msg
587 ( p_pkg_name => d_pkg_name,
588 p_procedure_name => d_api_name || '.' || d_position
589 );
590
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 END handle_price_tolerance_resp;
593
594
595 END PO_PDOI_GRP;
[Home] [Help]