[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_PVT
Source
1 PACKAGE BODY PO_PDOI_PVT AS
2 /* $Header: PO_PDOI_PVT.plb 120.7.12020000.4 2013/03/29 06:32:41 xueche ship $ */
3
4 d_pkg_name CONSTANT varchar2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE log_audsid;
12
13 PROCEDURE init_startup_values
14 ( p_calling_module IN VARCHAR2,
15 p_validation_level IN NUMBER,
16 p_commit IN VARCHAR2,
17 p_selected_batch_id IN NUMBER,
18 p_batch_size IN NUMBER,
19 p_buyer_id IN NUMBER,
20 p_document_type IN VARCHAR2,
21 p_document_subtype IN VARCHAR2,
22 p_create_items IN VARCHAR2,
23 p_create_sourcing_rules_flag IN VARCHAR2,
24 p_rel_gen_method IN VARCHAR2,
25 p_sourcing_level IN VARCHAR2,
26 p_sourcing_inv_org_id IN NUMBER,
27 p_approved_status IN VARCHAR2,
28 p_process_code IN VARCHAR2,
29 p_interface_header_id IN NUMBER,
30 p_org_id IN NUMBER,
31 p_ga_flag IN VARCHAR2,
32 p_submit_dft_flag IN VARCHAR2,
33 p_role IN VARCHAR2,
34 p_catalog_to_expire IN VARCHAR2,
35 p_err_lines_tolerance IN NUMBER,
36 p_clm_flag IN varchar2 --CLM PDOI Project
37 );
38
39 PROCEDURE init_sys_parameters;
40
41 PROCEDURE init_profile_parameters;
42
43 PROCEDURE init_product_parameters;
44
45 PROCEDURE get_when_to_archive_property
46 ( p_document_type IN VARCHAR2,
47 p_document_subtype IN VARCHAR2,
48 x_when_to_archive OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE gather_interface_table_stat;
52
53 PROCEDURE set_draft_errors;
54
55 PROCEDURE wrap_up;
56
57 -------------------------------------------------------
58 -------------- PUBLIC PROCEDURES ----------------------
59 -------------------------------------------------------
60
61 -----------------------------------------------------------------------
62 --Start of Comments
63 --Name: start_process
64 --Function:
65 -- Main procedure for PDOI, which is used for importing and updating
66 -- blankets, quotations or standard purchase orders
67 --Parameters:
68 --IN:
69 --p_api_version
70 -- API version of the program caller assumes
71 --p_init_msg_list
72 -- FND_API.G_TRUE if caller expects PDOI to initialize the message stack
73 -- maintained by FND_MSG_PUB.
74 -- FND_API.G_FALSE otherwise
75 --p_validation_level
76 -- Currently this parameter has no effect. PDOI always does full validation
77 --p_commit
78 -- Whether PDOI will issue commits
79 -- FND_API.G_TRUE if caller expects PDOI to commit data
80 -- FND_API.G_FALSE otherwise
81 --p_gather_intf_tbl_stat
82 -- Whether PDOI gather table statistics before processing.
83 -- 'Y' if statistics should be gathered. Consider this if a large
84 -- number of records are being inserted into the interface table
85 -- 'N' otherwise
86 --p_calling_module
87 -- The module name of the calling program. Value 'CATALOG UPLOAD' is reserved
88 -- to be used by calling from Calog upload program
89 --p_selected_batch_id
90 -- Batch id parameter. If this is specified, only the records with this batch
91 -- id will be processed.
92 --p_batch_size
93 -- Used for performance tuning. It specifies the number of header interface
94 -- records that will be processed for each bulk fetching. Default number is
95 -- PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE
96 --p_buyer_id
97 -- Default buyer of the document to be imported
98 --p_document_type
99 -- Type of the document that will be processed. Possible values are:
100 -- STANDARD, BLANKET, QUOTATION
101 --p_document_subtype
102 -- Default document subtype. Use it only if p_document_type is 'QUOTATION'.
103 --p_create_items
104 -- Specifies whether an item will be created as inventory item if the specified
105 -- item does not exist in the system.
106 -- 'Y' if item should be created
107 -- 'N' if item should NOT be created
108 --p_create_sourcing_rules_flag
109 -- Whether sourcing rules and ASL should be created as part of the creation
110 -- of blanket and quotation line
111 -- 'Y' if sourcing rules and ASL should be created
112 -- 'N' otherwise
113 --p_rel_gen_method
114 -- Release generation method of the ASL
115 --p_sourcing_level
116 -- Level of the sourcing rules assignment. Possible values:
117 -- ITEM, ITEM-ORGANIZATION
118 --p_sourcing_inv_org_id
119 -- If sourcing level is 'ITEM-ORGANIZATION', the organization where the
120 -- sourcing rule will be created in
121 --p_approved_status
122 -- Intended approval status the document after import. Possible values
123 -- INCOMPLETE - Incomplete documents
124 -- INITIATE APPROVAL - This means that the document will be submitted for
125 -- approval through approval workflow
126 -- APPROVED - Import as approved without submitting through approval wf
127 --p_process_code
128 -- Type of interface records to be processed. If this is specified, only
129 -- records with the specified process code will be processed
130 --p_interface_header_id
131 -- If this is specified, only record with this interface_header_id will be
132 -- processed
133 --p_org_id
134 -- Operating Unit where this PDOI will be running in. If this is not specified,
135 -- Current operating unit will be the operating unit for PDOI to run.
136 --p_ga_flag
137 -- Whether the blanket will be imported as global agreement or not.
138 -- 'Y' if blanekets should be imported as global agreements
139 --p_submit_dft_flag
140 -- ** Reserved for catalog upload **
141 -- Determines whether the draft changes should be submitted for buyer's
142 -- acceptance after they pass PDOI validations
143 -- 'Y' if changes should be submitted
144 -- 'N' otherwise
145 --p_role
146 -- ** Reserved for catalog upload ***
147 -- Role of the user calling PDOI. Possible values:
148 -- BUYER, SUPPLIER, CAT ADMIN
149 --p_catalog_to_expire
150 -- ** Reserved for catalog upload ***
151 -- If this is specified, all the lines with this value as the catalog name
152 -- will get expired
153 --p_err_lines_tolerance
154 -- ** Reserved for catalog upload **
155 -- Number of line errors PDOI can take before aborting the program.
156 -- Note: Even if PDOI is aborted, lines that get accepted will continue to
157 -- be processed.
158 --IN OUT:
159 --OUT:
160 --x_return_status
161 -- Return status of the API.
162 -- FND_API.G_RET_STS_SUCCESS if API is successful
163 -- FND_API.G_RET_STS_ERR if there are user errors
164 -- FND_API.G_RET_STS_UNEXP_ERR if unexpected error (exception) occurs
165 --x_processed_lines_count
166 -- ** Populated for catalog upload only **
167 -- Number of lines being processed
168 --x_rejected_lines_count
169 -- ** Populated for catalog upload only **
170 -- Number of lines being rejected
171 --x_err_tolerance_exceeded
172 -- ** Populated for catalog upload only **
173 -- indicates whether error threshold is exceeded
174 -- FND_API.G_TRUE if exceeded
175 -- FND_API.G_FALSE otherwise
176 --End of Comments
177 ------------------------------------------------------------------------
178 PROCEDURE start_process
179 ( p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2,
181 p_validation_level IN NUMBER,
182 p_commit IN VARCHAR2,
183 x_return_status OUT NOCOPY VARCHAR2,
184 p_gather_intf_tbl_stat IN VARCHAR2,
185 p_calling_module IN VARCHAR2,
186 p_selected_batch_id IN NUMBER,
187 p_batch_size IN NUMBER,
188 p_buyer_id IN NUMBER,
189 p_document_type IN VARCHAR2,
190 p_document_subtype IN VARCHAR2,
191 p_create_items IN VARCHAR2,
192 p_create_sourcing_rules_flag IN VARCHAR2,
193 p_rel_gen_method IN VARCHAR2,
194 p_sourcing_level IN VARCHAR2,
195 p_sourcing_inv_org_id IN NUMBER,
196 p_approved_status IN VARCHAR2,
197 p_process_code IN VARCHAR2,
198 p_interface_header_id IN NUMBER,
199 p_org_id IN NUMBER,
200 p_ga_flag IN VARCHAR2,
201 p_submit_dft_flag IN VARCHAR2,
202 p_role IN VARCHAR2,
203 p_catalog_to_expire IN VARCHAR2,
204 p_err_lines_tolerance IN NUMBER,
205 p_clm_flag IN varchar2, --CLM PDOI Project
206 x_processed_lines_count OUT NOCOPY NUMBER,
207 x_rejected_lines_count OUT NOCOPY NUMBER,
208 x_err_tolerance_exceeded OUT NOCOPY VARCHAR2
209 ) IS
210
211 d_api_version CONSTANT NUMBER := 1.0;
212 d_api_name CONSTANT VARCHAR2(30) := 'start_process';
213 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
214 d_position NUMBER;
215
216 l_all_headers_processed VARCHAR2(1);
217
218 l_validation_level VARCHAR2(10);
219 l_org_id PO_HEADERS_ALL.org_id%TYPE;
220 BEGIN
221
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223
224 d_position := 0;
225
226 IF (PO_LOG.d_proc) THEN
227 PO_LOG.proc_begin (d_module, 'p_api_version', p_api_version);
228 PO_LOG.proc_begin (d_module, 'p_init_msg_list', p_init_msg_list);
229 PO_LOG.proc_begin (d_module, 'p_validation_level', p_validation_level);
230 PO_LOG.proc_begin (d_module, 'p_commit', p_commit);
231 PO_LOG.proc_begin (d_module, 'p_gather_intf_tbl_stat', p_gather_intf_tbl_stat);
232 PO_LOG.proc_begin (d_module, 'p_calling_module', p_calling_module);
233 PO_LOG.proc_begin (d_module, 'p_selected_batch_id', p_selected_batch_id);
234 PO_LOG.proc_begin (d_module, 'p_batch_size', p_batch_size);
235 PO_LOG.proc_begin (d_module, 'p_buyer_id', p_buyer_id);
236 PO_LOG.proc_begin (d_module, 'p_document_type', p_document_type);
237 PO_LOG.proc_begin (d_module, 'p_document_subtype', p_document_subtype);
238 PO_LOG.proc_begin (d_module, 'p_create_items', p_create_items);
239 PO_LOG.proc_begin (d_module, 'p_create_sourcing_rules_flag', p_create_sourcing_rules_flag);
240 PO_LOG.proc_begin (d_module, 'p_rel_gen_method', p_rel_gen_method);
241 PO_LOG.proc_begin (d_module, 'p_sourcing_level', p_sourcing_level);
242 PO_LOG.proc_begin (d_module, 'p_sourcing_inv_org_id', p_sourcing_inv_org_id);
243 PO_LOG.proc_begin (d_module, 'p_approved_status', p_approved_status);
244 PO_LOG.proc_begin (d_module, 'p_process_code', p_process_code);
245 PO_LOG.proc_begin (d_module, 'p_interface_header_id', p_interface_header_id);
246 PO_LOG.proc_begin (d_module, 'p_org_id', p_org_id);
247 PO_LOG.proc_begin (d_module, 'p_ga_flag', p_ga_flag);
248 PO_LOG.proc_begin (d_module, 'p_submit_dft_flag', p_submit_dft_flag);
249 PO_LOG.proc_begin (d_module, 'p_role', p_role);
250 PO_LOG.proc_begin (d_module, 'p_catalog_to_expire', p_catalog_to_expire);
251 PO_LOG.proc_begin (d_module, 'p_err_lines_tolerance', p_err_lines_tolerance);
252 PO_LOG.proc_begin (d_module, 'p_clm_flag', p_clm_flag);
253 END IF;
254
255 PO_TIMING_UTL.init;
256 PO_TIMING_UTL.start_time (PO_PDOI_CONSTANTS.g_T_PDOI_ALL);
257
258 -- put down information about how to get logging messages
259 log_audsid;
260
261 IF (NOT FND_API.Compatible_API_Call
262 ( p_current_version_number => d_api_version
263 , p_caller_version_number => p_api_version
264 , p_api_name => d_api_name
265 , p_pkg_name => d_pkg_name
266 )
267 ) THEN
268 d_position := 10;
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270 END IF; -- not compatible_api
271
272 IF (p_init_msg_list = FND_API.G_TRUE) THEN
273 FND_MSG_PUB.initialize;
274 END IF;
275
276 d_position := 20;
277
278 IF (p_org_id IS NOT NULL) THEN
279 PO_MOAC_UTILS_PVT.set_org_context (p_org_id);
280 END IF;
281
282 d_position := 30;
283
284 l_org_id := PO_MOAC_UTILS_PVT.get_current_org_id;
285
286 d_position := 40;
287
288 PO_INTERFACE_ERRORS_UTL.init_errors_tbl; -- initialize errors tbl
289
290 d_position := 50;
291
292 init_startup_values
293 ( p_calling_module => p_calling_module,
294 p_validation_level => p_validation_level,
295 p_commit => p_commit,
296 p_selected_batch_id => p_selected_batch_id,
297 p_batch_size => NVL(p_batch_size, PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE),
298 p_buyer_id => p_buyer_id,
299 p_document_type => p_document_type,
300 p_document_subtype => p_document_subtype,
301 p_create_items => p_create_items,
302 p_create_sourcing_rules_flag => p_create_sourcing_rules_flag,
303 p_rel_gen_method => p_rel_gen_method,
304 p_sourcing_level => p_sourcing_level,
305 p_sourcing_inv_org_id => p_sourcing_inv_org_id,
306 p_approved_status => p_approved_status,
307 p_process_code => p_process_code,
308 p_interface_header_id => p_interface_header_id,
309 p_org_id => l_org_id,
310 p_ga_flag => p_ga_flag,
311 p_submit_dft_flag => p_submit_dft_flag,
312 p_role => p_role,
313 p_catalog_to_expire => p_catalog_to_expire,
314 p_err_lines_tolerance => p_err_lines_tolerance,
315 p_clm_flag => p_clm_flag --CLM PDOI Project
316 );
317
318 d_position := 60;
319
320 d_position := 70;
321
322 PO_PDOI_PREPROC_PVT.process; -- pre processing
323
324 --Bug 13343886
325 IF (p_gather_intf_tbl_stat = 'Y') THEN
326 d_position := 80;
327 gather_interface_table_stat;
328 END IF;
329
330 LOOP
331 d_position := 90;
332
333 BEGIN
334 -- looping for each round of header record processing
335 PO_PDOI_HEADER_GROUPING_PVT.process -- header grouping
336 ( x_all_headers_processed => l_all_headers_processed
337 );
338
339 d_position := 100;
340 EXIT WHEN l_all_headers_processed = FND_API.G_TRUE;
341
342 d_position := 110;
343 PO_PDOI_MAINPROC_PVT.process; -- main processing
344
345 d_position := 120;
346 PO_PDOI_POSTPROC_PVT.process; -- post processing
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 set_draft_errors;
351 RAISE;
352 END;
353 END LOOP;
354
355 d_position := 130;
356
357 x_processed_lines_count := PO_PDOI_PARAMS.g_out.processed_lines_count;
358 x_rejected_lines_count := PO_PDOI_PARAMS.g_out.rejected_lines_count;
359 x_err_tolerance_exceeded := PO_PDOI_PARAMS.g_out.err_tolerance_exceeded;
360
361 d_position := 140;
362
363 PO_PDOI_UTL.commit_work;
364
365 d_position := 150;
366
367 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_PDOI_ALL);
368
369 -- do interface errors reporting, log timing, etc.
370 wrap_up;
371
372 IF (PO_LOG.d_stmt) THEN
373 PO_LOG.proc_end(d_module);
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 PO_MESSAGE_S.add_exc_msg
378 ( p_pkg_name => d_pkg_name,
379 p_procedure_name => d_api_name || '.' || d_position
380 );
381
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383
384 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_PDOI_ALL);
385
386 wrap_up;
387
388 END start_process;
389
390 -------------------------------------------------------
391 -------------- PRIVATE PROCEDURES ---------------------
392 -------------------------------------------------------
393
394
395 -----------------------------------------------------------------------
396 --Start of Comments
397 --Name: log_audsid
398 --Function: put audsid to the log file. This id is used to retrieve all
399 -- pdoi messages from FND_LOG_MESSAGES table
400 --Parameters:
401 --IN:
402 --IN OUT:
403 --OUT:
404 --End of Comments
405 ------------------------------------------------------------------------
406
407 PROCEDURE log_audsid IS
408
409 d_api_name CONSTANT VARCHAR2(30) := 'log_audsid';
410 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
411 d_position NUMBER;
412
413 l_audsid NUMBER := USERENV('SESSIONID');
414 BEGIN
415 d_position := 0;
416
417 IF (PO_LOG.d_proc) THEN
418 PO_LOG.proc_begin (d_module);
419 END IF;
420
421 IF (PO_LOG.d_stmt) THEN
422 PO_LOG.stmt(d_module, d_position, 'AUDSID = ' || l_audsid);
423 END IF;
424
425 FND_FILE.put_line(FND_FILE.log,
426 'To get the log messages for PDOI, please use the ' ||
427 'following id to query against FND_LOG_MESSAGES table:');
428
429 FND_FILE.put_line(FND_FILE.log, 'AUDSID = ' || l_audsid);
430
431 IF (PO_LOG.d_proc) THEN
432 PO_LOG.proc_end (d_module);
433 END IF;
434
435 EXCEPTION
436 WHEN OTHERS THEN
437 PO_MESSAGE_S.add_exc_msg
438 ( p_pkg_name => d_pkg_name,
439 p_procedure_name => d_api_name || '.' || d_position
440 );
441 RAISE;
442 END log_audsid;
443
444
445 -----------------------------------------------------------------------
446 --Start of Comments
447 --Name: init_startup_values
448 --Function:
449 -- Derive startup values (system parameters, profiles, etc.) that will
450 -- be used throughout PDOI
451 --Parameters:
452 --IN:
453 --IN OUT:
454 --OUT:
455 --End of Comments
456 ------------------------------------------------------------------------
457 PROCEDURE init_startup_values
458 ( p_calling_module IN VARCHAR2,
459 p_validation_level IN NUMBER,
460 p_commit IN VARCHAR2,
461 p_selected_batch_id IN NUMBER,
462 p_batch_size IN NUMBER,
463 p_buyer_id IN NUMBER,
464 p_document_type IN VARCHAR2,
465 p_document_subtype IN VARCHAR2,
466 p_create_items IN VARCHAR2,
467 p_create_sourcing_rules_flag IN VARCHAR2,
468 p_rel_gen_method IN VARCHAR2,
469 p_sourcing_level IN VARCHAR2,
470 p_sourcing_inv_org_id IN NUMBER,
471 p_approved_status IN VARCHAR2,
472 p_process_code IN VARCHAR2,
473 p_interface_header_id IN NUMBER,
474 p_org_id IN NUMBER,
475 p_ga_flag IN VARCHAR2,
476 p_submit_dft_flag IN VARCHAR2,
477 p_role IN VARCHAR2,
478 p_catalog_to_expire IN VARCHAR2,
479 p_err_lines_tolerance IN NUMBER,
480 p_clm_flag IN VARCHAR2 --CLM PDOI Project
481 ) IS
482
483 d_api_name CONSTANT VARCHAR2(30) := 'init_startup_values';
484 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
485 d_position NUMBER;
486
487
488 BEGIN
489 d_position := 0;
490
491 IF (PO_LOG.d_proc) THEN
492 PO_LOG.proc_begin (d_module);
493 END IF;
494
495 -- Setup g_request
496 PO_PDOI_PARAMS.g_request.calling_module := NVL(p_calling_module, PO_PDOI_CONSTANTS.g_call_mod_UNKNOWN);
497 PO_PDOI_PARAMS.g_request.validation_level := p_validation_level;
498 PO_PDOI_PARAMS.g_request.commit_work := p_commit;
499 PO_PDOI_PARAMS.g_request.batch_id := p_selected_batch_id;
500 PO_PDOI_PARAMS.g_request.batch_size := p_batch_size;
501 PO_PDOI_PARAMS.g_request.buyer_id := p_buyer_id;
502 PO_PDOI_PARAMS.g_request.document_type := p_document_type;
503 PO_PDOI_PARAMS.g_request.document_subtype := p_document_subtype;
504 PO_PDOI_PARAMS.g_request.create_items := p_create_items;
505 PO_PDOI_PARAMS.g_request.create_sourcing_rules_flag := p_create_sourcing_rules_flag;
506 PO_PDOI_PARAMS.g_request.rel_gen_method := p_rel_gen_method;
507 PO_PDOI_PARAMS.g_request.sourcing_level := p_sourcing_level;
508 PO_PDOI_PARAMS.g_request.sourcing_inv_org_id := p_sourcing_inv_org_id;
509 PO_PDOI_PARAMS.g_request.approved_status := p_approved_status;
510 PO_PDOI_PARAMS.g_request.process_code := p_process_code;
511 PO_PDOI_PARAMS.g_request.interface_header_id := p_interface_header_id;
512 PO_PDOI_PARAMS.g_request.org_id := p_org_id;
513 PO_PDOI_PARAMS.g_request.ga_flag := p_ga_flag;
514 PO_PDOI_PARAMS.g_request.submit_dft_flag := p_submit_dft_flag;
515 PO_PDOI_PARAMS.g_request.role := p_role;
516 PO_PDOI_PARAMS.g_request.catalog_to_expire := p_catalog_to_expire;
517 PO_PDOI_PARAMS.g_request.err_lines_tolerance := p_err_lines_tolerance;
518 PO_PDOI_PARAMS.g_request.clm_flag := p_clm_flag; --CLM PDOI Project
519
520 d_position := 10;
521
522 -- Setup g_sys
523 init_sys_parameters;
524
525 d_position := 20;
526 -- Setup g_profile
527 init_profile_parameters;
528
529 d_position := 30;
530 -- Setup g_product
531 init_product_parameters;
532
533 d_position := 40;
534 -- default processing_id
535 SELECT PO_PDOI_PROCESSING_ID_S.nextval
536 INTO PO_PDOI_PARAMS.g_processing_id
537 FROM DUAL;
538
539 IF (PO_LOG.d_stmt) THEN
540 PO_LOG.stmt (d_module, d_position, 'g_processsing_id',
541 PO_PDOI_PARAMS.g_processing_id);
542 END IF;
543
544 d_position := 50;
545 -- initializing tracking variables for PDOI
546 PO_PDOI_PARAMS.g_original_doc_processed := FND_API.G_FALSE;
547 PO_PDOI_PARAMS.g_current_round_num := 0;
548
549 -- setup g_out
550 PO_PDOI_PARAMS.g_out.processed_lines_count := 0;
551 PO_PDOI_PARAMS.g_out.rejected_lines_count := 0;
552 PO_PDOI_PARAMS.g_out.err_tolerance_exceeded := FND_API.G_FALSE;
553
554 IF (PO_LOG.d_proc) THEN
555 PO_LOG.proc_end (d_module);
556 END IF;
557 EXCEPTION
558 WHEN OTHERS THEN
559 PO_MESSAGE_S.add_exc_msg
560 ( p_pkg_name => d_pkg_name,
561 p_procedure_name => d_api_name || '.' || d_position
562 );
563 RAISE;
564 END init_startup_values;
565
566
567 -----------------------------------------------------------------------
568 --Start of Comments
569 --Name: init_sys_parameters
570 --Function:
571 -- Derive system paramters and populate the same to
572 -- PO_PDOI_PARAMS.g_sys record
573 --Parameters:
574 --IN:
575 --IN OUT:
576 --OUT:
577 --End of Comments
578 ------------------------------------------------------------------------
579 PROCEDURE init_sys_parameters IS
580
581 d_api_name CONSTANT VARCHAR2(30) := 'init_sys_parameters';
582 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
583 d_position NUMBER;
584
585 l_enforce_buyer_name
586 PO_SYSTEM_PARAMETERS.enforce_buyer_name_flag %TYPE;
587 l_enforce_buyer_auth_flag
588 PO_SYSTEM_PARAMETERS.enforce_buyer_authority_flag%TYPE;
589 l_security_structure_id
590 PO_SYSTEM_PARAMETERS.security_position_structure_id%TYPE;
591 l_rev_sort_ordering
592 FINANCIALS_SYSTEM_PARAMETERS.revision_sort_ordering%TYPE;
593 l_notify_blanket_flag
594 PO_SYSTEM_PARAMETERS.notify_if_blanket_flag%TYPE;
595 l_budgetary_control_flag
596 GL_SETS_OF_BOOKS.enable_budgetary_control_flag%TYPE;
597 l_user_defined_req_num_code
598 PO_SYSTEM_PARAMETERS.user_defined_req_num_code%TYPE;
599 l_rfq_required_flag
600 PO_SYSTEM_PARAMETERS.rfq_required_flag%TYPE;
601 l_manual_req_num_type
602 PO_SYSTEM_PARAMETERS.manual_req_num_type%TYPE;
603 l_enforce_full_lot_qty
604 PO_SYSTEM_PARAMETERS.enforce_full_lot_quantities%TYPE;
605 l_disposition_warning_flag
606 PO_SYSTEM_PARAMETERS.disposition_warning_flag%TYPE;
607 l_reserve_at_completion_flag
608 FINANCIALS_SYSTEM_PARAMETERS.reserve_at_completion_flag%TYPE;
609 l_user_defined_rcpt_num_code
610 PO_SYSTEM_PARAMETERS.user_defined_receipt_num_code%TYPE;
611 l_manual_rcpt_num_type
612 PO_SYSTEM_PARAMETERS.manual_receipt_num_type%TYPE;
613 l_use_positions_flag
614 FINANCIALS_SYSTEM_PARAMETERS.use_positions_flag%TYPE;
615 l_user_defined_rfq_num_code
616 PO_SYSTEM_PARAMETERS.user_defined_rfq_num_code%TYPE;
617 l_manual_rfq_num_type
618 PO_SYSTEM_PARAMETERS.manual_rfq_num_type%TYPE;
619
620
621 BEGIN
622
623 d_position := 0;
624
625 IF (PO_LOG.d_proc) THEN
626 PO_LOG.proc_begin (d_module);
627 END IF;
628
629 -- po and financials system parameters
630 PO_CORE_S.get_po_parameters
631 ( x_currency_code => PO_PDOI_PARAMS.g_sys.currency_code,
632 x_coa_id => PO_PDOI_PARAMS.g_sys.coa_id,
633 x_po_encumberance_flag => PO_PDOI_PARAMS.g_sys.po_encumbrance_flag,
634 x_req_encumberance_flag => PO_PDOI_PARAMS.g_sys.req_encumbrance_flag,
635 x_sob_id => PO_PDOI_PARAMS.g_sys.sob_id,
636 x_ship_to_location_id => PO_PDOI_PARAMS.g_sys.ship_to_location_id,
637 x_bill_to_location_id => PO_PDOI_PARAMS.g_sys.bill_to_location_id,
638 x_fob_lookup_code => PO_PDOI_PARAMS.g_sys.fob_lookup_code,
639 x_freight_terms_lookup_code => PO_PDOI_PARAMS.g_sys.freight_terms_lookup_code,
640 x_terms_id => PO_PDOI_PARAMS.g_sys.terms_id,
641 x_default_rate_type => PO_PDOI_PARAMS.g_sys.default_rate_type,
642 x_taxable_flag => PO_PDOI_PARAMS.g_sys.taxable_flag,
643 x_receiving_flag => PO_PDOI_PARAMS.g_sys.receiving_flag,
644 x_enforce_buyer_name_flag => l_enforce_buyer_name,
645 x_enforce_buyer_auth_flag => l_enforce_buyer_auth_flag,
646 x_line_type_id => PO_PDOI_PARAMS.g_sys.line_type_id,
647 x_manual_po_num_type => PO_PDOI_PARAMS.g_sys.manual_po_num_type,
648 x_po_num_code => PO_PDOI_PARAMS.g_sys.user_defined_po_num_code,
649 x_price_lookup_code => PO_PDOI_PARAMS.g_sys.price_type_lookup_code,
650 x_invoice_close_tolerance => PO_PDOI_PARAMS.g_sys.invoice_close_tolerance,
651 x_receive_close_tolerance => PO_PDOI_PARAMS.g_sys.receive_close_tolerance,
652 x_security_structure_id => l_security_structure_id,
653 x_expense_accrual_code => PO_PDOI_PARAMS.g_sys.expense_accrual_code,
654 x_inv_org_id => PO_PDOI_PARAMS.g_sys.def_inv_org_id,
655 x_rev_sort_ordering => l_rev_sort_ordering,
656 x_min_rel_amount => PO_PDOI_PARAMS.g_sys.min_rel_amount,
657 x_notify_blanket_flag => l_notify_blanket_flag,
658 x_budgetary_control_flag => l_budgetary_control_flag,
659 x_user_defined_req_num_code => l_user_defined_req_num_code,
660 x_rfq_required_flag => l_rfq_required_flag,
661 x_manual_req_num_type => l_manual_req_num_type,
662 x_enforce_full_lot_qty => l_enforce_full_lot_qty,
663 x_disposition_warning_flag => l_disposition_warning_flag,
664 x_reserve_at_completion_flag => l_reserve_at_completion_flag,
665 x_user_defined_rcpt_num_code => l_user_defined_rcpt_num_code,
666 x_manual_rcpt_num_type => l_manual_rcpt_num_type,
667 x_use_positions_flag => l_use_positions_flag,
668 x_default_quote_warning_delay => PO_PDOI_PARAMS.g_sys.def_quote_warning_delay,
669 x_inspection_required_flag => PO_PDOI_PARAMS.g_sys.inspection_required_flag,
670 x_user_defined_quote_num_code => PO_PDOI_PARAMS.g_sys.user_defined_quote_num_code,
671 x_manual_quote_num_type => PO_PDOI_PARAMS.g_sys.manual_quote_num_type,
672 x_user_defined_rfq_num_code => l_user_defined_rfq_num_code,
673 x_manual_rfq_num_type => l_manual_rfq_num_type,
674 x_ship_via_lookup_code => PO_PDOI_PARAMS.g_sys.ship_via_lookup_code,
675 x_qty_rcv_tolerance => PO_PDOI_PARAMS.g_sys.qty_rcv_tolerance,
676 x_acceptance_required_flag => PO_PDOI_PARAMS.g_sys.acceptance_required_flag /* Bug 7518967 : Default Acceptance Required Check ER */
677 );
678
679 d_position := 10;
680 SELECT price_break_lookup_code,
681 supplier_authoring_acceptance,
682 cat_admin_authoring_acceptance
683 INTO PO_PDOI_PARAMS.g_sys.price_break_lookup_code,
684 PO_PDOI_PARAMS.g_sys.supplier_auth_acc,
685 PO_PDOI_PARAMS.g_sys.cat_admin_auth_acc
686 FROM po_system_parameters;
687
688 d_position := 20;
689 SELECT match_option,
690 business_group_id
691 INTO PO_PDOI_PARAMS.g_sys.invoice_match_option,
692 PO_PDOI_PARAMS.g_sys.def_business_group_id
693 FROM financials_system_parameters;
694
695 d_position := 30;
696 SELECT master_organization_id
697 INTO PO_PDOI_PARAMS.g_sys.master_inv_org_id
698 FROM mtl_parameters
699 WHERE organization_id = PO_PDOI_PARAMS.g_sys.def_inv_org_id;
700
701 d_position := 40;
702 -- receiving parameters for defaulting org
703 RCV_CORE_S.get_receiving_controls
704 ( x_line_loc_id => NULL,
705 x_item_id => NULL,
706 x_vendor_id => NULL,
707 x_org_id => PO_PDOI_PARAMS.g_sys.def_inv_org_id,
708 x_enforce_ship_to_loc => PO_PDOI_PARAMS.g_sys.enforce_ship_to_loc,
709 x_allow_substitutes => PO_PDOI_PARAMS.g_sys.allow_substitutes,
710 x_routing_id => PO_PDOI_PARAMS.g_sys.routing_id,
711 x_qty_rcv_tolerance => PO_PDOI_PARAMS.g_sys.qty_rcv_tolerance,
712 x_qty_rcv_exception => PO_PDOI_PARAMS.g_sys.qty_rcv_exception,
713 x_days_early_receipt => PO_PDOI_PARAMS.g_sys.days_early_receipt,
714 x_days_late_receipt => PO_PDOI_PARAMS.g_sys.days_late_receipt,
715 x_rcv_date_exception => PO_PDOI_PARAMS.g_sys.rcv_days_exception
716 );
717
718 d_position := 50;
719 get_when_to_archive_property
720 ( p_document_type => 'PA',
721 p_document_subtype => 'BLANKET',
722 x_when_to_archive => PO_PDOI_PARAMS.g_sys.when_to_archive_blanket
723 );
724
725 d_position := 60;
726 get_when_to_archive_property
727 ( p_document_type => 'PO',
728 p_document_subtype => 'STANDARD',
729 x_when_to_archive => PO_PDOI_PARAMS.g_sys.when_to_archive_std_po
730 );
731
732 d_position := 70;
733 SELECT MCSB.structure_id,
734 MCSB.category_set_id,
735 MCSB.default_category_id
736 INTO PO_PDOI_PARAMS.g_sys.def_structure_id,
737 PO_PDOI_PARAMS.g_sys.def_cat_set_id,
738 PO_PDOI_PARAMS.g_sys.def_category_id
739 FROM MTL_DEFAULT_CATEGORY_SETS MDCS,
740 MTL_CATEGORY_SETS_B MCSB
741 WHERE MDCS.functional_area_id = 2
742 AND MDCS.category_set_id = MCSB.category_set_id;
743
744 d_position := 80;
745 PO_PDOI_PARAMS.g_sys.is_federal_instance :=
746 PO_CORE_S.check_federal_instance
747 ( p_org_id => PO_PDOI_PARAMS.g_request.org_id
748 );
749
750 d_position := 90;
751 IF (PO_LOG.d_stmt) THEN
752 PO_LOG.stmt (d_module, d_position, 'currency_code', PO_PDOI_PARAMS.g_sys.currency_code);
753 PO_LOG.stmt (d_module, d_position, 'coa_id', PO_PDOI_PARAMS.g_sys.coa_id);
754 PO_LOG.stmt (d_module, d_position, 'po_encumbrance_flag', PO_PDOI_PARAMS.g_sys.po_encumbrance_flag);
755 PO_LOG.stmt (d_module, d_position, 'req_encumbrance_flag', PO_PDOI_PARAMS.g_sys.req_encumbrance_flag);
756 PO_LOG.stmt (d_module, d_position, 'sob_id', PO_PDOI_PARAMS.g_sys.sob_id);
757 PO_LOG.stmt (d_module, d_position, 'ship_to_location_id', PO_PDOI_PARAMS.g_sys.ship_to_location_id);
758 PO_LOG.stmt (d_module, d_position, 'bill_to_location_id', PO_PDOI_PARAMS.g_sys.bill_to_location_id);
759 PO_LOG.stmt (d_module, d_position, 'fob_lookup_code', PO_PDOI_PARAMS.g_sys.fob_lookup_code);
760 PO_LOG.stmt (d_module, d_position, 'freight_terms_lookup_code', PO_PDOI_PARAMS.g_sys.freight_terms_lookup_code);
761 PO_LOG.stmt (d_module, d_position, 'terms_id', PO_PDOI_PARAMS.g_sys.terms_id);
762 PO_LOG.stmt (d_module, d_position, 'default_rate_type', PO_PDOI_PARAMS.g_sys.default_rate_type);
763 PO_LOG.stmt (d_module, d_position, 'taxable_flag', PO_PDOI_PARAMS.g_sys.taxable_flag);
764 PO_LOG.stmt (d_module, d_position, 'receiving_flag', PO_PDOI_PARAMS.g_sys.receiving_flag);
765 PO_LOG.stmt (d_module, d_position, 'line_type_id', PO_PDOI_PARAMS.g_sys.line_type_id);
766 PO_LOG.stmt (d_module, d_position, 'manual_po_num_type', PO_PDOI_PARAMS.g_sys.manual_po_num_type);
767 PO_LOG.stmt (d_module, d_position, 'user_defined_po_num_code', PO_PDOI_PARAMS.g_sys.user_defined_po_num_code);
768 PO_LOG.stmt (d_module, d_position, 'price_type_lookup_code', PO_PDOI_PARAMS.g_sys.price_type_lookup_code);
769 PO_LOG.stmt (d_module, d_position, 'def_inv_org_id', PO_PDOI_PARAMS.g_sys.def_inv_org_id);
770 PO_LOG.stmt (d_module, d_position, 'min_rel_amount', PO_PDOI_PARAMS.g_sys.min_rel_amount);
771 PO_LOG.stmt (d_module, d_position, 'def_quote_warning_delay', PO_PDOI_PARAMS.g_sys.def_quote_warning_delay);
772 PO_LOG.stmt (d_module, d_position, 'inspection_required_flag', PO_PDOI_PARAMS.g_sys.inspection_required_flag);
773 PO_LOG.stmt (d_module, d_position, 'user_defined_quote_num_code', PO_PDOI_PARAMS.g_sys.user_defined_quote_num_code);
774 PO_LOG.stmt (d_module, d_position, 'manual_quote_num_type', PO_PDOI_PARAMS.g_sys.manual_quote_num_type);
775 PO_LOG.stmt (d_module, d_position, 'ship_via_lookup_code', PO_PDOI_PARAMS.g_sys.ship_via_lookup_code);
776 PO_LOG.stmt (d_module, d_position, 'qty_rcv_tolerance', PO_PDOI_PARAMS.g_sys.qty_rcv_tolerance);
777 PO_LOG.stmt (d_module, d_position, 'price_break_lookup_code', PO_PDOI_PARAMS.g_sys.price_break_lookup_code);
778 PO_LOG.stmt (d_module, d_position, 'invoice_close_tolerance', PO_PDOI_PARAMS.g_sys.invoice_close_tolerance);
779 PO_LOG.stmt (d_module, d_position, 'receive_close_tolerance', PO_PDOI_PARAMS.g_sys.receive_close_tolerance);
780 PO_LOG.stmt (d_module, d_position, 'expense_accrual_code', PO_PDOI_PARAMS.g_sys.expense_accrual_code);
781 PO_LOG.stmt (d_module, d_position, 'master_inv_org_id', PO_PDOI_PARAMS.g_sys.master_inv_org_id);
782 PO_LOG.stmt (d_module, d_position, 'enforce_ship_to_loc', PO_PDOI_PARAMS.g_sys.enforce_ship_to_loc);
783 PO_LOG.stmt (d_module, d_position, 'allow_substitutes', PO_PDOI_PARAMS.g_sys.allow_substitutes);
784 PO_LOG.stmt (d_module, d_position, 'routing_id', PO_PDOI_PARAMS.g_sys.routing_id);
785 PO_LOG.stmt (d_module, d_position, 'qty_rcv_exception', PO_PDOI_PARAMS.g_sys.qty_rcv_exception);
786 PO_LOG.stmt (d_module, d_position, 'days_early_receipt', PO_PDOI_PARAMS.g_sys.days_early_receipt);
787 PO_LOG.stmt (d_module, d_position, 'days_late_receipt', PO_PDOI_PARAMS.g_sys.days_late_receipt);
788 PO_LOG.stmt (d_module, d_position, 'rcv_days_exception', PO_PDOI_PARAMS.g_sys.rcv_days_exception);
789 PO_LOG.stmt (d_module, d_position, 'supplier_auth_acc', PO_PDOI_PARAMS.g_sys.supplier_auth_acc);
790 PO_LOG.stmt (d_module, d_position, 'cat_admin_auth_acc', PO_PDOI_PARAMS.g_sys.cat_admin_auth_acc);
791 PO_LOG.stmt (d_module, d_position, 'invoice_match_option', PO_PDOI_PARAMS.g_sys.invoice_match_option);
792 PO_LOG.stmt (d_module, d_position, 'when_to_archive_blanket', PO_PDOI_PARAMS.g_sys.when_to_archive_blanket);
793 PO_LOG.stmt (d_module, d_position, 'when_to_archive_std_po', PO_PDOI_PARAMS.g_sys.when_to_archive_std_po);
794 PO_LOG.stmt (d_module, d_position, 'def_business_group_id', PO_PDOI_PARAMS.g_sys.def_business_group_id);
795 PO_LOG.stmt (d_module, d_position, 'def_structure_id', PO_PDOI_PARAMS.g_sys.def_structure_id);
796 PO_LOG.stmt (d_module, d_position, 'def_cat_set_id', PO_PDOI_PARAMS.g_sys.def_cat_set_id);
797 PO_LOG.stmt (d_module, d_position, 'def_category_id', PO_PDOI_PARAMS.g_sys.def_category_id);
798 PO_LOG.stmt (d_module, d_position, 'is_federal_instance', PO_PDOI_PARAMS.g_sys.is_federal_instance);
799 END IF;
800
801 IF (PO_LOG.d_proc) THEN
802 PO_LOG.proc_end (d_module);
803 END IF;
804
805 EXCEPTION
806 WHEN OTHERS THEN
807 PO_MESSAGE_S.add_exc_msg
808 ( p_pkg_name => d_pkg_name,
809 p_procedure_name => d_api_name || '.' || d_position
810 );
811 RAISE;
812 END init_sys_parameters;
813
814
815 -----------------------------------------------------------------------
816 --Start of Comments
817 --Name: init_profile_parameters
818 --Function:
819 -- Derive necessary profiles and populate the same to PO_PDOI_PARAMS.g_profile
820 -- record
821 --Parameters:
822 --IN:
823 --IN OUT:
824 --OUT:
825 --End of Comments
826 ------------------------------------------------------------------------
827 PROCEDURE init_profile_parameters IS
828
829 d_api_name CONSTANT VARCHAR2(30) := 'init_profile_parameters';
830 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
831 d_position NUMBER;
832
833 BEGIN
834 d_position := 0;
835
836 IF (PO_LOG.d_proc) THEN
837 PO_LOG.proc_begin (d_module);
838 END IF;
839
840 PO_PDOI_PARAMS.g_profile.pdoi_write_to_file :=
841 NVL(FND_PROFILE.value('PO_PDOI_WRITE_TO_FILE'),'N');
842
843 PO_PDOI_PARAMS.g_profile.service_uom_class :=
844 NVL(FND_PROFILE.value('PO_RATE_UOM_CLASS'), '999');
845
846 PO_PDOI_PARAMS.g_profile.pdoi_archive_on_approval :=
847 NVL(FND_PROFILE.value('PO_PDOI_ARCHIVE_ON_APPROVAL'), 'N');
848
849 PO_PDOI_PARAMS.g_profile.override_funds :=
850 FND_PROFILE.value('PO_REQAPPR_OVERRIDE_FUNDS');
851
852 PO_PDOI_PARAMS.g_profile.xbg := NVL(HR_GENERAL.get_xbg_profile, 'N');
853
854 PO_PDOI_PARAMS.g_profile.po_price_update_tolerance :=
855 FND_PROFILE.value('PO_PRICE_UPDATE_TOLERANCE');
856
857 -- bug 5015608
858 -- get profile value from eTax. Default the value to 'Y' if NULL
859 PO_PDOI_PARAMS.g_profile.allow_tax_rate_override :=
860 NVL(FND_PROFILE.value('ZX_ALLOW_TAX_RECVRY_RATE_OVERRIDE'), 'Y');
861
862 PO_PDOI_PARAMS.g_profile.allow_tax_code_override :=
863 NVL(FND_PROFILE.value('ZX_ALLOW_TAX_CLASSIF_OVERRIDE'), 'Y');
864
865
866 IF (PO_LOG.d_stmt) THEN
867 PO_LOG.stmt (d_module, d_position, 'pdoi_write_to_file', PO_PDOI_PARAMS.g_profile.pdoi_write_to_file);
868 PO_LOG.stmt (d_module, d_position, 'service_uom_class', PO_PDOI_PARAMS.g_profile.service_uom_class);
869 PO_LOG.stmt (d_module, d_position, 'pdoi_archive_on_approval', PO_PDOI_PARAMS.g_profile.pdoi_archive_on_approval);
870 PO_LOG.stmt (d_module, d_position, 'override_funds', PO_PDOI_PARAMS.g_profile.override_funds);
871 PO_LOG.stmt (d_module, d_position, 'xbg', PO_PDOI_PARAMS.g_profile.xbg);
872 PO_LOG.stmt (d_module, d_position, 'po_price_update_tolerance', PO_PDOI_PARAMS.g_profile.po_price_update_tolerance);
873 PO_LOG.stmt (d_module, d_position, 'allow_tax_rate_override', PO_PDOI_PARAMS.g_profile.allow_tax_rate_override);
874 PO_LOG.stmt (d_module, d_position, 'allow_tax_code_override', PO_PDOI_PARAMS.g_profile.allow_tax_code_override);
875 END IF;
876
877 IF (PO_LOG.d_proc) THEN
878 PO_LOG.proc_end (d_module);
879 END IF;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 PO_MESSAGE_S.add_exc_msg
884 ( p_pkg_name => d_pkg_name,
885 p_procedure_name => d_api_name || '.' || d_position
886 );
887 RAISE;
888 END init_profile_parameters;
889
890 -----------------------------------------------------------------------
891 --Start of Comments
892 --Name: init_product_parameters
893 --Function:
894 -- Derive necessary product installation status and populate the same
895 -- to PO_PDOI_PARAMS.g_product record
896 --Parameters:
897 --IN:
898 --IN OUT:
899 --OUT:
900 --End of Comments
901 ------------------------------------------------------------------------
902 PROCEDURE init_product_parameters IS
903
904 d_api_name CONSTANT VARCHAR2(30) := 'init_product_parameters';
905 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
906 d_position NUMBER;
907
908 BEGIN
909 d_position := 0;
910
911 IF (PO_LOG.d_proc) THEN
912 PO_LOG.proc_begin (d_module);
913 END IF;
914
915 IF (PO_CORE_S.get_product_install_status('WIP') = 'I') THEN
916 PO_PDOI_PARAMS.g_product.wip_installed := FND_API.G_TRUE;
917 ELSE
918 PO_PDOI_PARAMS.g_product.wip_installed := FND_API.G_FALSE;
919 END IF;
920
921 IF (PO_CORE_S.get_product_install_status('INV') = 'I') THEN
922 PO_PDOI_PARAMS.g_product.inv_installed := FND_API.G_TRUE;
923 ELSE
924 PO_PDOI_PARAMS.g_product.inv_installed := FND_API.G_FALSE;
925 END IF;
926
927 IF (PA_PO_INTEGRATION.is_pjc_11i10_enabled <> 'N') THEN
928 PO_PDOI_PARAMS.g_product.project_11510_installed := FND_API.G_TRUE;
929 ELSE
930 PO_PDOI_PARAMS.g_product.project_11510_installed := FND_API.G_FALSE;
931 END IF;
932
933 IF (PO_CORE_S.get_product_install_status('PA') = 'I') THEN
934 PO_PDOI_PARAMS.g_product.pa_installed := FND_API.G_TRUE;
935 ELSE
936 PO_PDOI_PARAMS.g_product.pa_installed := FND_API.G_FALSE;
937 END IF;
938
939 IF (GMS_PO_API_GRP.gms_enabled) THEN
940 PO_PDOI_PARAMS.g_product.gms_enabled := FND_API.G_TRUE;
941 ELSE
942 PO_PDOI_PARAMS.g_product.gms_enabled := FND_API.G_FALSE;
943 END IF;
944
945 IF (PA_PO_INTEGRATION.is_pjc_po_cwk_intg_enab <> 'N') THEN
946 PO_PDOI_PARAMS.g_product.project_cwk_installed := FND_API.G_TRUE;
947 ELSE
948 PO_PDOI_PARAMS.g_product.project_cwk_installed := FND_API.G_FALSE;
949 END IF;
950
951 IF (PO_LOG.d_stmt) THEN
952 PO_LOG.stmt (d_module, d_position, 'wip_installed', PO_PDOI_PARAMS.g_product.wip_installed);
953 PO_LOG.stmt (d_module, d_position, 'inv_installed', PO_PDOI_PARAMS.g_product.inv_installed);
954 PO_LOG.stmt (d_module, d_position, 'project_11510_installed', PO_PDOI_PARAMS.g_product.project_11510_installed);
955 PO_LOG.stmt (d_module, d_position, 'pa_installed', PO_PDOI_PARAMS.g_product.pa_installed);
956 PO_LOG.stmt (d_module, d_position, 'gms_enabled', PO_PDOI_PARAMS.g_product.gms_enabled);
957 PO_LOG.stmt (d_module, d_position, 'project_cwk_installed', PO_PDOI_PARAMS.g_product.project_cwk_installed);
958 END IF;
959
960 IF (PO_LOG.d_proc) THEN
961 PO_LOG.proc_end (d_module);
962 END IF;
963 EXCEPTION
964 WHEN OTHERS THEN
965 PO_MESSAGE_S.add_exc_msg
966 ( p_pkg_name => d_pkg_name,
967 p_procedure_name => d_api_name || '.' || d_position
968 );
969 RAISE;
970 END init_product_parameters;
971
972 -----------------------------------------------------------------------
973 --Start of Comments
974 --Name: get_when_to_archive_property
975 --Function:
976 -- Derive the archive on property of the document
977 --Parameters:
978 --IN:
979 --p_document_type
980 -- Document Type
981 --p_document_subtype
982 -- Document Subtype
983 --IN OUT:
984 --OUT:
985 --x_when_to_archive
986 -- Archive on either 'PRINT' or 'APPROVE'
987 --End of Comments
988 ------------------------------------------------------------------------
989 PROCEDURE get_when_to_archive_property
990 ( p_document_type IN VARCHAR2,
991 p_document_subtype IN VARCHAR2,
992 x_when_to_archive OUT NOCOPY VARCHAR2
993 ) IS
994
995 d_api_name CONSTANT VARCHAR2(30) := 'get_when_to_archive_property';
996 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
997 d_position NUMBER;
998
999 BEGIN
1000 d_position := 0;
1001
1002 SELECT archive_external_revision_code
1003 INTO x_when_to_archive
1004 FROM po_document_types
1005 WHERE document_type_code = p_document_type
1006 AND document_subtype = p_document_subtype;
1007
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 PO_MESSAGE_S.add_exc_msg
1011 ( p_pkg_name => d_pkg_name,
1012 p_procedure_name => d_api_name || '.' || d_position
1013 );
1014 RAISE;
1015 END get_when_to_archive_property;
1016
1017
1018 -----------------------------------------------------------------------
1019 --Start of Comments
1020 --Name: gather_interface_table_stat
1021 --Function:
1022 -- Gather table statistics for interface tables. This is typically used
1023 -- when a large number of records need to be processed and the table
1024 -- stats of interface tables may be significantly changed from the last
1025 -- time the stats were gathered
1026 --Parameters:
1027 --IN:
1028 --IN OUT:
1029 --OUT:
1030 --End of Comments
1031 ------------------------------------------------------------------------
1032 PROCEDURE gather_interface_table_stat IS
1033
1034 d_api_name CONSTANT VARCHAR2(30) := 'gather_interface_table_stat';
1035 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1036 d_position NUMBER;
1037
1038 l_status VARCHAR2(1);
1039 l_industry VARCHAR2(1);
1040 l_schema VARCHAR2(30);
1041
1042 l_return_status BOOLEAN;
1043
1044 BEGIN
1045 d_position := 0;
1046
1047 IF (PO_LOG.d_proc) THEN
1048 PO_LOG.proc_begin (d_module);
1049 END IF;
1050
1051 PO_TIMING_UTL.start_time (PO_PDOI_CONSTANTS.g_T_GATHER_TBL_STAT);
1052
1053 -- Get the schema name of the table
1054 l_return_status := FND_INSTALLATION.get_app_info
1055 ( 'PO',
1056 l_status,
1057 l_industry,
1058 l_schema
1059 );
1060
1061 IF (l_return_status) THEN
1062 d_position := 10;
1063
1064 FND_STATS.gather_table_stats
1065 ( ownname => l_schema,
1066 tabname => 'PO_HEADERS_INTERFACE'
1067 );
1068
1069 d_position := 20;
1070 FND_STATS.gather_table_stats
1071 ( ownname => l_schema,
1072 tabname => 'PO_LINES_INTERFACE'
1073 );
1074
1075 d_position := 30;
1076 FND_STATS.gather_table_stats
1077 ( ownname => l_schema,
1078 tabname => 'PO_LINE_LOCATIONS_INTERFACE'
1079 );
1080
1081 d_position := 40;
1082 FND_STATS.gather_table_stats
1083 ( ownname => l_schema,
1084 tabname => 'PO_DISTRIBUTIONS_INTERFACE'
1085 );
1086
1087 d_position := 50;
1088 FND_STATS.gather_table_stats
1089 ( ownname => l_schema,
1090 tabname => 'PO_PRICE_DIFF_INTERFACE'
1091 );
1092
1093 d_position := 60;
1094 FND_STATS.gather_table_stats
1095 ( ownname => l_schema,
1096 tabname => 'PO_ATTR_VALUES_INTERFACE'
1097 );
1098
1099 d_position := 70;
1100 FND_STATS.gather_table_stats
1101 ( ownname => l_schema,
1102 tabname => 'PO_ATTR_VALUES_TLP_INTERFACE'
1103 );
1104 ELSE
1105 d_position := 80;
1106
1107 IF (PO_LOG.d_stmt) THEN
1108 PO_LOG.stmt(d_module, d_position, 'Cannot get appl info. ' ||
1109 'No stat gathering');
1110 END IF;
1111 END IF;
1112
1113 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_GATHER_TBL_STAT);
1114
1115 IF (PO_LOG.d_proc) THEN
1116 PO_LOG.proc_end (d_module);
1117 END IF;
1118
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 PO_MESSAGE_S.add_exc_msg
1122 ( p_pkg_name => d_pkg_name,
1123 p_procedure_name => d_api_name || '.' || d_position
1124 );
1125 RAISE;
1126 END gather_interface_table_stat;
1127
1128 -----------------------------------------------------------------------
1129 --Start of Comments
1130 --Name: set_draft_errors
1131 --Function:
1132 -- Mark the drafts that are created in current round as PDOI ERROR.
1133 -- no other program can update the document anymore until the PDOI ERROR is
1134 -- resolved by running PDOI again.
1135 --Parameters:
1136 --IN:
1137 --IN OUT:
1138 --OUT:
1139 --End of Comments
1140 ------------------------------------------------------------------------
1141 PROCEDURE set_draft_errors IS
1142
1143 d_api_name CONSTANT VARCHAR2(30) := 'set_draft_errors';
1144 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1145 d_position NUMBER;
1146
1147 BEGIN
1148 d_position := 0;
1149
1150 IF (PO_LOG.d_proc) THEN
1151 PO_LOG.proc_begin (d_module);
1152 END IF;
1153
1154 --SQL What: Mark the drafts that are processed by the current round as
1155 -- PDOI ERROR
1156 --SQL Why: Since the draft may have incomplete data, we want to prevent
1157 -- others from updating the document until the drafts are removed.
1158 -- The way to remove it is by running PDOI against the same document
1159 -- again.
1160 UPDATE po_drafts DFT
1161 SET DFT.status = PO_DRAFTS_PVT.g_status_PDOI_ERROR
1162 WHERE DFT.status = PO_DRAFTS_PVT.g_status_PDOI_PROCESSING
1163 AND DFT.draft_id IN
1164 (SELECT PHI.draft_id
1165 FROM po_headers_interface PHI
1166 WHERE PHI.processing_id IN (PO_PDOI_PARAMS.g_processing_id,
1167 -PO_PDOI_PARAMS.g_processing_id)
1168 AND PHI.processing_round_num=PO_PDOI_PARAMS.g_current_round_num);
1169
1170 IF (PO_LOG.d_proc) THEN
1171 PO_LOG.proc_end (d_module);
1172 END IF;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 PO_MESSAGE_S.add_exc_msg
1177 ( p_pkg_name => d_pkg_name,
1178 p_procedure_name => d_api_name || '.' || d_position
1179 );
1180 RAISE;
1181 END set_draft_errors;
1182
1183 -----------------------------------------------------------------------
1184 --Start of Comments
1185 --Name: wrap_up
1186 --Function:
1187 -- Perform actions to be done right before PDOI quits
1188 --Parameters:
1189 --IN:
1190 --IN OUT:
1191 --OUT:
1192 --End of Comments
1193 ------------------------------------------------------------------------
1194 PROCEDURE wrap_up IS
1195
1196 d_api_name CONSTANT VARCHAR2(30) := 'wrap_up';
1197 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1198 d_position NUMBER;
1199
1200 l_timing_info_tbl PO_TBL_VARCHAR4000;
1201
1202 BEGIN
1203
1204 d_position := 0;
1205
1206 IF (PO_LOG.d_proc) THEN
1207 PO_LOG.proc_begin (d_module);
1208 END IF;
1209
1210 -- flush all remaining errors to interface errors table
1211 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
1212
1213 d_position := 20;
1214
1215 PO_TIMING_UTL.get_formatted_timing_info
1216 ( p_cleanup => FND_API.G_TRUE,
1217 x_timing_info => l_timing_info_tbl
1218 );
1219
1220 d_position := 30;
1221
1222 -- Force Logging
1223 FOR i IN 1..l_timing_info_tbl.COUNT LOOP
1224 IF (10 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1225 FND_LOG.string (10, d_module, 'Timing Stat: ' || l_timing_info_tbl(i));
1226 END IF;
1227 END LOOP;
1228
1229 -- FOR i IN 1..l_timing_info_tbl.COUNT LOOP
1230 -- PO_LOG.stmt (d_module, d_position, 'Timing Stat: ', l_timing_info_tbl(i));
1231 -- END LOOP;
1232
1233
1234 IF (PO_LOG.d_proc) THEN
1235 PO_LOG.proc_end (d_module);
1236 END IF;
1237
1238 EXCEPTION
1239 WHEN OTHERS THEN
1240 PO_MESSAGE_S.add_exc_msg
1241 ( p_pkg_name => d_pkg_name,
1242 p_procedure_name => d_api_name || '.' || d_position
1243 );
1244 RAISE;
1245 END wrap_up;
1246
1247 END PO_PDOI_PVT;