[Home] [Help]
PACKAGE BODY: APPS.PO_AUTOCREATE_PVT
Source
1 PACKAGE BODY PO_AUTOCREATE_PVT AS
2 /* $Header: PO_AUTOCREATE_PVT.plb 120.4.12020000.18 2013/05/02 11:35:44 pamandav ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(1000) := 'PO_AUTOCREATE_PVT';
6 g_log_head CONSTANT VARCHAR2(1000) := 'po.plsql.PO_AUTOCREATE_PVT.';
7
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11
12
13 /* ----------------------------------------------------
14 ----------------- PUBLIC PROCEDURES ----------------
15 ---------------------------------------------------- */
16
17
18 /* ============================================================================
19 NAME: wrapup
20 DESC: Perform actions to be done right before AutoCreate quits.
21
22 NOTE: Call PO_INTERFACE_S.wrapup()
23 ============================================================================ */
24
25
26 PROCEDURE wrapup
27 IS
28 BEGIN
29 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
30 PO_DEBUG.debug_begin(p_log_head => g_log_head||'wrapup');
31 END IF;
32
33 DELETE po_distributions_interface
34 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
35
36 -- <SERVICES FPJ START>
37 DELETE po_price_diff_interface
38 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
39 -- <SERVICES FPJ END>
40
41 -- <Complex Work R12 Start>
42 DELETE po_line_locations_interface
43 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
44 -- <Complex Work R12 End>
45
46 DELETE po_lines_interface
47 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
48
49
50 DELETE po_headers_interface
51 WHERE interface_header_id = PO_AUTOCREATE_PARAMS.x_interface_header_id;
52
53 IF g_debug_stmt THEN --< Bug 3210331: use proper debugging >
54 PO_DEBUG.debug_end(p_log_head => g_log_head||'wrapup');
55 END IF;
56 END wrapup;
57
58
59
60
61 /* ============================================================================
62 NAME: CREATE_PO
63 DESC: Create/Add to PO from requisition data in the PO_HEADERS,LINES
64 and DISTRIBUTION interface tables.
65
66 ARGS: IN : x_interface_header_id IN NUMBER - Interface Header Id used to fetch the
67 interface data.
68 p_sourcing_k_doc_type IN VARCHAR2 - The document type that Sourcing
69 has seeded in Contracts.
70 Deafault null
71 p_conterms_exist_flag IN VARCHAR2 - Whether the sourcing document
72 has contract template attached.
73 Deafult - N
74 p_document_creation_method IN VARCHAR2 - Stores the method by which the
75 document has been created.
76 p_req_operating_unit_id IN NUMBER - The Operating Unit of Requisition raising Operating Unit,
77 or the current Operating Unit of the environment if called
78 from Oracle Sourcing 11.5.10+ and beyond
79 p_purch_operating_unit_id IN NUMBER - The Operating Unit where the PO is being created.
80
81 p_orig_org_id IN NUMBER
82
83 IN OUT : x_document_id IN OUT NOCOPY NUMBER
84
85 Algorithm:
86 1. This procedure will call the following in order.
87 PO_AUTOCREATE_PREPROC_PVT.process
88 PO_AUTOCREATE_MAINPROC_PVT.process
89 PO_AUTOCREATE_POSTPROC_PVT.process
90
91 2. This also takes care of cleaning up the interface wrapup and rolling
92 back the transaction incase of unhandled exception. This also takes
93 care communicating to the calling code about the result of the process.
94
95 3. This *might* need to be overloaded to expose this as an private / group
96 API. The API will follow the regular API standards.
97 ==============================================================================*/
98
99 PROCEDURE create_po(x_interface_header_id IN NUMBER
100 ,x_document_id IN OUT NOCOPY NUMBER
101 ,p_sourcing_k_doc_type IN VARCHAR2 --<CONTERMS FPJ>
102 ,p_conterms_exist_flag IN VARCHAR2 --<CONTERMS FPJ>
103 ,p_document_creation_method IN VARCHAR2 --<DBI FPJ>
104 ,p_req_operating_unit_id IN NUMBER
105 ,p_purch_operating_unit_id IN NUMBER
106 ,p_orig_org_id IN NUMBER
107 )
108 IS
109
110 l_api_name VARCHAR2(30) := 'create_po';
111 l_progress VARCHAR2(3) := '000';
112
113 l_return_status VARCHAR2(1);
114
115 BEGIN
116
117 IF g_debug_stmt THEN
118 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
119 END IF;
120
121 -- Create savepoint for create_po
122 SAVEPOINT create_po;
123
124 l_progress := '010';
125
126 PO_AUTOCREATE_PARAMS.x_interface_header_id := x_interface_header_id;
127 PO_AUTOCREATE_PARAMS.x_req_operating_unit_id := p_req_operating_unit_id;
128 PO_AUTOCREATE_PARAMS.x_purch_operating_unit_id := p_purch_operating_unit_id;
129 PO_AUTOCREATE_PARAMS.x_orig_org_id := p_orig_org_id;
130 PO_AUTOCREATE_PARAMS.x_sourcing_k_doc_type := p_sourcing_k_doc_type;
131 PO_AUTOCREATE_PARAMS.x_conterms_exist_flag := p_conterms_exist_flag;
132
133 l_progress := '015';
134
135 IF g_debug_stmt THEN
136 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
137 p_token => l_progress,
138 p_message => 'At begin of create_po : Interface Header Id:'||x_interface_header_id);
139
140 END IF;
141
142 l_progress := '020';
143 PO_AUTOCREATE_PREPROC_PVT.process;
144
145 l_progress := '030';
146 PO_AUTOCREATE_MAINPROC_PVT.process;
147
148 l_progress := '040';
149 PO_AUTOCREATE_POSTPROC_PVT.process;
150
151 l_progress := '050';
152 x_document_id := PO_AUTOCREATE_PARAMS.g_po_header_id;
153
154 if x_document_id is not null then
155 l_return_status := FND_API.G_RET_STS_SUCCESS ;
156 --Bug 14006061 : After autocreate is successfully complete need to clear the interface records.
157 wrapup;
158 else
159 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
160 END IF;
161
162
163 IF g_debug_stmt THEN
164 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
165 p_token => l_progress,
166 p_message => 'At the end of create_po');
167
168 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
169 p_token => l_progress,
170 p_message => 'l_return_status:'||l_return_status||' x_document_id:'||x_document_id);
171
172 END IF;
173
174 IF g_debug_stmt THEN
175 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
176 END IF;
177
178 EXCEPTION
179 WHEN OTHERS THEN
180
181 --CLM Phase 2 changes : error handling
182 PO_AUTOCREATE_PVT.report_error('PO_AUTOCREATE_ERR',x_token1_value => sqlerrm);
183
184 wrapup;
185 IF g_debug_unexp THEN
186 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
187 p_progress => l_progress);
188 END IF;
189 po_message_s.sql_error('CREATE_PO',l_progress,sqlcode);
190 --<Shared Proc FPJ Start>
191 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
192 x_document_id := null;
193 -- Added a Rollback when a Exception was raised
194 -- This Rollbacks all the Changes done when a Exception Condition was raised. This was done to avoid PO's with negative numbers getting created.
195 --raise;
196 --<SOURCING TO PO FPH>
197 --for sourcing transaction is controlled by the sourcing code.
198
199 if PO_AUTOCREATE_PARAMS.g_interface_source_code not in ('SOURCING', 'CONSUMPTION_ADVICE') then
200 ROLLBACK to savepoint create_po;
201 wrapup;
202 end if;
203
204 --
205 raise;
206
207 END create_po;
208
209
210
211 /* ============================================================================
212 NAME: calculate_local
213 DESC: This procedure serve as a hook to the function of localization team.
214
215 ==========================================================================*/
216
217 PROCEDURE calculate_local(document_type varchar2,
218 level_type varchar2,
219 level_id number
220
221 ) IS
222
223 l_cursor integer;
224 sqlstmt varchar2(2000);
225 l_jl_installed varchar2(30);
226 l_execute integer;
227 l_return number;
228 l_progress VARCHAR2(3) := '000';
229 l_api_name CONSTANT VARCHAR2(30) := 'calculate_local';
230 BEGIN
231
232 -- Added call to JG_GLOBE_UTIL_PKG.process_po_globe_event
233
234 IF g_debug_stmt THEN
235 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
236 END IF;
237
238 ---------------------------------------------------
239 -- Check whether the Regional Package is installed
240 ---------------------------------------------------
241 SELECT DISTINCT 'Package Installed'
242 INTO l_jl_installed
243 FROM user_objects
244 WHERE object_name = 'JG_GLOBE_UTIL_PKG'
245 AND object_type = 'PACKAGE BODY';
246
247 l_progress := '010';
248 ---------------------------------------------------
249 -- Execute dynamically the Regional Procedure
250 ---------------------------------------------------
251 sqlstmt := 'BEGIN JG_GLOBE_UTIL_PKG.process_po_globe_event(:p_document_type,:p_level_type,:p_level_id); END;';
252
253 -- Create the SQL statement
254 l_cursor := dbms_sql.open_cursor;
255
256 l_progress := '020';
257
258 -- Parse the SQL statement
259 dbms_sql.parse (l_cursor, sqlstmt, dbms_sql.native);
260
261 l_progress := '030';
262 -- Define the variables
263 dbms_sql.bind_variable(l_cursor, ':p_document_type', document_type);
264 dbms_sql.bind_variable(l_cursor, ':p_level_type', level_type);
265 dbms_sql.bind_variable(l_cursor, ':p_level_id', level_id);
266
267 l_progress := '040';
268 -- Execute the SQL statement
269 l_execute := dbms_sql.execute(l_cursor);
270
271 -- Get the return value (success)
272 -- dbms_sql.variable_value(l_cursor, ':b_return', l_return);
273
274 l_progress := '050';
275 -- Close the cursor
276 dbms_sql.close_cursor(l_cursor);
277
278 IF g_debug_stmt THEN
279 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
280 END IF;
281
282 EXCEPTION
283
284 WHEN no_data_found THEN
285
286 ----------------------------------------
287 -- Regional Procedure is not installed
288 ----------------------------------------
289 IF g_debug_stmt THEN
290 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
291 p_token => l_progress,
292 p_message => 'NO_DATA_FOUND: '||SQLERRM);
293 END IF;
294
295 WHEN OTHERS THEN
296
297 IF g_debug_unexp THEN
298 PO_DEBUG.debug_exc(p_log_head => g_log_head||l_api_name,
299 p_progress => l_progress);
300 END IF;
301 RAISE;
302 END calculate_local;
303
304 -- CLM Phase 2 Changes : Error Handling
305 PROCEDURE report_error( error_msg in VARCHAR2,
306 x_token1_name IN VARCHAR2 := 'SQLCODE',
307 x_token1_value IN VARCHAR2 := NULL,
308 x_token2_name IN VARCHAR2 := 'REQ_NUM',
309 x_token2_value IN VARCHAR2 := NULL,
310 x_token3_name IN VARCHAR2 := NULL,
311 x_token3_value IN VARCHAR2 := NULL,
312 x_token4_name IN VARCHAR2 := NULL,
313 x_token4_value IN VARCHAR2 := NULL )
314 IS
315
316 l_api_name VARCHAR2(30) := 'report_error';
317 l_progress VARCHAR2(10) := '000';
318 BEGIN
319
320 IF g_debug_stmt THEN
321 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
322 END IF;
323
324 fnd_message.set_name('PO',error_msg);
325 fnd_message.set_token(x_token1_name,x_token1_value);
326
327 if x_token2_value is not null then
328 fnd_message.set_token(x_token2_name,x_token2_value);
329 end if;
330
331 PO_AUTOCREATE_PARAMS.g_error_code_tbl.extend(1);
332 PO_AUTOCREATE_PARAMS.g_error_code_tbl(PO_AUTOCREATE_PARAMS.g_error_code_tbl.count) := fnd_message.get;
333
334 IF g_debug_stmt THEN
335 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
336 p_token => l_progress,
337 p_message => PO_AUTOCREATE_PARAMS.g_error_code_tbl(PO_AUTOCREATE_PARAMS.g_error_code_tbl.count));
338 END IF;
339
340 IF g_debug_stmt THEN
341 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
342 END IF;
343
344 END report_error;
345 -- CLM Phase 2 Changes : Error Handling
346
347 -- CLM Phase 2 Changes : Error Handling
348 FUNCTION get_requisition_line_num(p_req_line_id in number )
349 RETURN NUMBER
350 IS
351
352 l_api_name VARCHAR2(30) := 'get_requisition_line_num';
353 l_progress VARCHAR2(10) := '000';
354 l_requisition_number VARCHAR2(100):=null;
355
356 BEGIN
357
358 IF g_debug_stmt THEN
359 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
360 END IF;
361
362 select prh.segment1||','||prl.line_num_display into l_requisition_number
363 from po_requisition_lines_all prl, po_requisition_headers_all prh
364 where prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
365 and prl.requisition_line_id = p_req_line_id;
366
367 IF g_debug_stmt THEN
368 PO_DEBUG.debug_end(p_log_head => g_log_head||l_api_name);
369 END IF;
370
371 RETURN l_requisition_number;
372
373 EXCEPTION
374
375 when no_data_found then
376 return null;
377
378 when others then
379 return null;
380
381 END get_requisition_line_num;
382 -- CLM Phase 2 Changes : Error Handling
383
384 -- CLM Phase 2 Changes : Error Handling
385 /* bug 13818326 starts
386 Get the Error code table
387 so that it can be used in the sourcing flow*/
388 -- CLM Phase 2 Changes : Error Handling
389 FUNCTION get_error_code_tbl
390 RETURN PO_TBL_VARCHAR2000
391 IS
392
393 l_api_name VARCHAR2(30) := 'get_error_code_tbl';
394 l_progress VARCHAR2(10) := '000';
395
396 BEGIN
397
398 IF g_debug_stmt THEN
399 PO_DEBUG.debug_begin(p_log_head => g_log_head||l_api_name);
400 END IF;
401
402 RETURN PO_AUTOCREATE_PARAMS.g_error_code_tbl;
403
404 EXCEPTION
405
406 when no_data_found then
407 return null;
408
409 when others then
410 return null;
411
412 END get_error_code_tbl;
413 -- CLM Phase 2 Changes : Error Handling
414
415 PROCEDURE initialize_error_code_tbl
416 AS
417
418 l_api_name VARCHAR2(30) := 'initialize_error_code_tbl';
419 l_progress VARCHAR2(10) := '000';
420
421 BEGIN
422
423 PO_AUTOCREATE_PARAMS.g_error_code_tbl := PO_TBL_VARCHAR2000();
424
425 EXCEPTION
426
427 when no_data_found THEN
428 null;
429
430 when others then
431 null;
432
433 END ;
434 -- CLM Phase 2 Changes : Error Handling
435 /* bug 13818326 ends */
436
437 --<PAR Project START>
438 -----------------------------------------------------------------------
439 --Start of Comments
440 --Name: insert_row_in_uda_interface
441 --Pre-reqs: None
442 --Modifies:
443 --Locks:
444 --Procedure:
445 -- This procedure inserts row in po_uda_interface table.
446 --Parameters:
447 --IN:
448 -- p_transaction_id IN NUMBER,
449 -- p_row_identifier IN NUMBER,
450 -- p_pk1_value NUMBER ,
451 -- p_mod_draft_id NUMBER,
452 -- p_uda_template_id NUMBER,
453 -- p_attr_group_type VARCHAR2 ,
454 -- p_attr_group_name IN VARCHAR2 ,
455 -- p_attr_name IN VARCHAR2,
456 -- p_data_level IN VARCHAR2,
457 -- p_mod_value VARCHAR2
458 --IN OUT:
459 --OUT:
460 --p_attr_display_name po_tbl_varchar100
461 --Notes:
462 --Testing:
463 --End of Comments
464 ------------------------------------------------------------------------
465
466 PROCEDURE insert_row_in_uda_interface
467 (
468 p_transaction_id IN NUMBER,
469 p_row_identifier IN NUMBER,
470 p_pk1_value NUMBER ,
471 p_mod_draft_id NUMBER,
472 p_uda_template_id NUMBER,
473 p_attr_group_type VARCHAR2 ,
474 p_attr_group_name IN VARCHAR2 ,
475 p_attr_name IN VARCHAR2,
476 p_data_level IN VARCHAR2,
477 p_mod_value VARCHAR2
478
479 ) IS
480
481 d_api_name CONSTANT VARCHAR2(30) := 'insert_row_in_uda_interface';
482 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
483 d_position NUMBER ;
484 l_attr_group_id NUMBER;
485 l_attr_value_str VARCHAR2(1000);
486 l_attr_value_num NUMBER;
487 l_attr_value_date DATE;
488 l_attr_display_name VARCHAR2(1000);
489 l_process_status NUMBER := 1;
490 l_application_id NUMBER := FND_GLOBAL.resp_appl_id;
491 l_data_type_code VARCHAR2(30);
492
493
494
495 BEGIN
496
497 IF (PO_LOG.d_proc) THEN
498 PO_LOG.proc_begin(d_module, 'p_transaction_id', p_transaction_id);
499 PO_LOG.proc_begin(d_module, 'p_row_identifier', p_row_identifier);
500 PO_LOG.proc_begin(d_module, 'p_pk1_value', p_pk1_value);
501 PO_LOG.proc_begin(d_module, 'p_mod_draft_id', p_mod_draft_id);
502 PO_LOG.proc_begin(d_module, 'p_uda_template_id', p_uda_template_id);
503 PO_LOG.proc_begin(d_module, 'p_attr_group_type', p_attr_group_type);
504 PO_LOG.proc_begin(d_module, 'p_attr_group_name', p_attr_group_name);
505 PO_LOG.proc_begin(d_module, 'p_attr_name', p_attr_name);
506 PO_LOG.proc_begin(d_module, 'p_data_level', p_data_level);
507 PO_LOG.proc_begin(d_module, 'p_mod_value', p_mod_value);
508 END IF;
509
510 d_position := 0;
511
512 SELECT attr_display_name , eagv.attr_group_id , eav.data_type_code
513 INTO l_attr_display_name , l_attr_group_id , l_data_type_code
514 FROM ego_attrs_v eav, ego_attr_groups_v eagv
515 WHERE eav.attr_group_name = eagv.attr_group_name
516 AND eav.attr_group_type = eagv.attr_group_type
517 AND eav.attr_group_name = p_attr_group_name
518 AND eav.attr_name = p_attr_name
519 AND eav.attr_group_type = p_attr_group_type;
520
521 d_position := 10;
522
523 IF PO_LOG.d_stmt THEN
524 PO_LOG.stmt(d_module,d_position,'l_attr_display_name',l_attr_display_name);
525 PO_LOG.stmt(d_module,d_position,'l_attr_group_id',l_attr_group_id);
526 PO_LOG.stmt(d_module,d_position,'l_data_type_code',l_data_type_code);
527 END IF;
528
529 IF l_data_type_code = 'N'
530 THEN
531 l_attr_value_str := NULL;
532 l_attr_value_num := To_Number(p_mod_value);
533 l_attr_value_date := NULL;
534 END IF;
535
536 IF l_data_type_code = 'C'
537 THEN
538 l_attr_value_str := p_mod_value;
539 l_attr_value_num := NULL;
540 l_attr_value_date := NULL;
541 END IF;
542
543 IF l_data_type_code = 'X'
544 THEN
545 l_attr_value_str := NULL ;
546 l_attr_value_num := NULL;
547 l_attr_value_date := To_Date(p_mod_value);
548 END IF;
549
550 d_position := 20;
551
552 IF PO_LOG.d_stmt THEN
553 PO_LOG.stmt(d_module,d_position,'l_attr_value_str',l_attr_value_str);
554 PO_LOG.stmt(d_module,d_position,'l_attr_value_num',l_attr_value_num);
555 PO_LOG.stmt(d_module,d_position,'l_attr_value_date',l_attr_value_date);
556 END IF;
557
558
559 INSERT INTO po_uda_interface
560 (transaction_id,
561 row_identifier,
562 attr_group_id,
563 attr_group_app_id,
564 attr_group_type,
565 attr_group_name,
566 data_level_1,
567 attr_name,
568 attr_value_str,
569 attr_value_num,
570 attr_value_date,
571 --attr_disp_value, <Bug 16565392> : Need not be populated in this flow
572 pk1_value,
573 pk2_value,
574 uda_template_id,
575 process_status)
576 VALUES (p_transaction_id,
577 p_row_identifier,
578 l_attr_group_id,
579 l_application_id,
580 p_attr_group_type,
581 p_attr_group_name,
582 p_data_level,
583 p_attr_name,
584 l_attr_value_str,
585 l_attr_value_num,
586 l_attr_value_date,
587 --l_attr_display_name, <Bug 16565392> : Need not be populated in this flow
588 p_pk1_value,
589 p_mod_draft_id,
590 p_uda_template_id,
591 l_process_status);
592
593 END insert_row_in_uda_interface;
594
595 -----------------------------------------------------------------------
596 --Start of Comments
597 --Name: form_attr_display_name
598 --Pre-reqs: None
599 --Modifies:
600 --Locks:
601 --Procedure:
602 -- This procedure forms the list of display names of address
603 -- related attributes.This is usefult in UDA import.
604 --Parameters:
605 --IN:
606 -- p_attr_group_type VARCHAR2,
607 -- p_par_draft_id NUMBER,
608 -- p_po_header_id NUMBER,
609 --IN OUT:
610 --OUT:
611 --p_attr_display_name po_tbl_varchar100
612 --Notes:
613 --Testing:
614 --End of Comments
615 ------------------------------------------------------------------------
616
617
618 PROCEDURE form_attr_display_name
619 (
620 p_attr_group_type IN VARCHAR2,
621 p_par_draft_id NUMBER,
622 p_po_header_id NUMBER,
623 p_attr_display_name OUT NOCOPY po_tbl_varchar100) IS
624
625 d_api_name CONSTANT VARCHAR2(30) := 'form_attr_display_name';
626 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
627 d_position NUMBER ;
628
629 BEGIN
630
631 IF (PO_LOG.d_proc) THEN
632 PO_LOG.proc_begin(d_module, 'p_attr_group_type', p_attr_group_type);
633 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id);
634 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
635 PO_LOG.proc_begin(d_module, 'p_attr_display_name', p_attr_display_name);
636 END IF;
637
638 SELECT DISTINCT eav.attr_group_type|| To_Char( eagv.attr_group_id)|| To_Char(eav.attr_id) ||'LOVDisp'
639 BULK collect
640 INTO p_attr_display_name
641 FROM (SELECT * FROM ego_attrs_v WHERE attr_group_type = 'PO_HEADER_EXT_ATTRS') eav,
642 (SELECT * FROM ego_attr_groups_v WHERE attr_group_name = 'addresses') eagv ,
643 (SELECT * FROM po_entity_differences WHERE mod_doc_source_name = 'PO_ADDRESSES_DRAFT_DIFF_V') ped
644 WHERE eav.attr_group_name = eagv.attr_group_name
645 AND eav.attr_group_type = eagv.attr_group_type
646 AND SubStr(ped.column_name, InStr(ped.column_name, Chr(0))+1) = eav.attr_name
647 AND mod_doc_pk2_val = p_par_draft_id;
648
649 END form_attr_display_name;
650
651 -----------------------------------------------------------------------
652 --Start of Comments
653 --Name: POPULATE_UDA_DATA_IN_INTERFACE
654 --Pre-reqs: None
655 --Modifies:
656 --Locks:
657 --Procedure:
658 -- This procedure populated UDA data in po_uda_interface table
659 --Parameters:
660 --IN:p_draft_id NUMBER
661 -- p_mod_draft_id NUMBER ,
662 -- p_par_draft_id NUMBER,
663 -- p_pk1_value NUMBER,
664 -- p_mod_doc_source_name VARCHAR2,
665 -- p_transaction_id NUMBER ,
666 -- p_uda_template_id NUMBER
667 --IN OUT:
668 --OUT:
669 --Notes:
670 --Testing:
671 --End of Comments
672 ------------------------------------------------------------------------
673
674 PROCEDURE POPULATE_UDA_DATA_IN_INTERFACE
675 (
676 p_header_id IN NUMBER,
677 p_mod_draft_id IN NUMBER ,
678 p_par_draft_id IN NUMBER,
679 p_pk1_value IN NUMBER,
680 p_mod_doc_source_name IN VARCHAR2,
681 p_transaction_id NUMBER ,
682 p_uda_template_id IN NUMBER )
683
684 IS
685 d_api_name CONSTANT VARCHAR2(30) := 'POPULATE_UDA_DATA_IN_INTERFACE';
686 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
687 d_position NUMBER ;
688
689 l_attr_grp_tbl po_tbl_varchar100;
690 l_distinct_attr_grp_name_tbl po_tbl_varchar100;
691 l_attr_name_tbl po_tbl_varchar100;
692 l_mod_value_tbl po_tbl_varchar4000;
693 l_data_level VARCHAR2(150);
694 l_attr_value_num NUMBER;
695 l_attr_group_type VARCHAR2(40);
696 l_attr_group_name VARCHAR2(30);
697 l_attr_name VARCHAR2(30);
698 l_attr_display_name VARCHAR2(80);
699 l_attr_group_id NUMBER;
700 l_data_type_code VARCHAR2(30);
701 l_attr_value_str VARCHAR2(1000);
702 l_attr_value_date DATE;
703 l_process_status NUMBER := 1;
704 l_address_type_tbl po_tbl_varchar100 ;
705 l_row_identifier_add NUMBER;
706 l_value_set VARCHAR2(60);
707 l_attr_grp_name VARCHAR2(60);
708 l_row_identifier NUMBER;
709
710 BEGIN
711
712 IF (PO_LOG.d_proc) THEN
713 PO_LOG.proc_begin(d_module, 'p_mod_draft_id', p_mod_draft_id);
714 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id);
715 PO_LOG.proc_begin(d_module, 'p_pk1_value', p_pk1_value);
716 PO_LOG.proc_begin(d_module, 'p_mod_doc_source_name', p_mod_doc_source_name);
717 PO_LOG.proc_begin(d_module, 'p_transaction_id', p_transaction_id);
718 PO_LOG.proc_begin(d_module, 'p_uda_template_id', p_uda_template_id);
719 PO_LOG.proc_begin(d_module, 'p_header_id', p_header_id);
720 END IF;
721
722 po_gen_diff_pkg.mod_pk1 := p_header_id;
723 po_gen_diff_pkg.mod_pk2 := p_par_draft_id;
724
725
726 --Check the entity level and assign the datalevel
727 -- and attr group anme accordingly
728
729 IF p_mod_doc_source_name = 'PO_HEADERS_EXT_DRAFT_DIFF_V'
730 OR p_mod_doc_source_name = 'PO_ADDRESSES_DRAFT_DIFF_V'THEN
731 l_attr_group_type := 'PO_HEADER_EXT_ATTRS';
732 l_data_level := 'PO_HEADER';
733
734
735 ELSIF p_mod_doc_source_name = 'PO_LINES_EXT_DRAFT_DIFF_V' THEN
736 l_attr_group_type := 'PO_LINE_EXT_ATTRS';
737 l_data_level := 'PO_LINE';
738
739
740 ELSIF p_mod_doc_source_name = 'PO_LINE_LOCS_DRAFT_EXT_DIFF_V' THEN
741 l_attr_group_type := 'PO_SHIPMENTS_EXT_ATTRS';
742 l_data_level := 'PO_SHIPMENT';
743
744 END IF;
745
746 d_position :=10;
747
748 IF PO_LOG.d_stmt THEN
749 PO_LOG.stmt(d_module,d_position,'l_attr_group_type ',l_attr_group_type);
750 PO_LOG.stmt(d_module,d_position,'l_data_level ',l_data_level);
751 END IF;
752
753
754
755 --Seperate processing for address
756
757 IF p_mod_doc_source_name = 'PO_ADDRESSES_DRAFT_DIFF_V' THEN
758
759 --select distinct address type
760 SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) add_type
761 BULK COLLECT INTO l_address_type_tbl
762 FROM po_entity_differences
763 WHERE mod_doc_pk2_val = p_par_draft_id
764 and mod_doc_source_name = p_mod_doc_source_name ;
765
766
767 IF l_address_type_tbl.Count() <> 0 THEN
768
769 -- for each address type select the attrnames and their avlues
770
771 FOR i IN 1..l_address_type_tbl.Count() LOOP
772
773 l_row_identifier_add:= PO_UDA_INTERFACE_S.NEXTVAL;
774
775 IF PO_LOG.d_stmt THEN
776 PO_LOG.stmt(d_module,d_position,'l_address_type_tbl '||i||' is ',l_address_type_tbl(i));
777 PO_LOG.stmt(d_module,d_position,'l_row_identifier ',l_row_identifier);
778 END IF;
779
780 SELECT SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
781 BULK COLLECT INTO
782 l_attr_name_tbl, l_mod_value_tbl
783 FROM po_entity_differences
784 WHERE mod_doc_pk2_val = p_par_draft_id
785 AND mod_doc_pk1_val = p_pk1_value
786 and mod_doc_source_name = p_mod_doc_source_name
787 AND column_name LIKE l_address_type_tbl(i) ||'%' ;
788
789 -- For each attr value, get the additional details
790 FOR j IN 1..l_attr_name_tbl.Count() LOOP
791
792 insert_row_in_uda_interface
793 (
794 p_transaction_id => p_transaction_id,
795 p_row_identifier => l_row_identifier_add,
796 p_pk1_value => p_pk1_value ,
797 p_mod_draft_id => p_mod_draft_id,
798 p_uda_template_id => p_uda_template_id,
799 p_attr_group_type => l_attr_group_type ,
800 p_attr_group_name => 'addresses',
801 p_attr_name => l_attr_name_tbl(j),
802 p_data_level => l_data_level,
803 p_mod_value => l_mod_value_tbl(j)
804 ) ;
805
806 --Get Value Set Name
807 BEGIN
808
809 SELECT value_set_name
810 INTO l_value_set
811 FROM ego_attrs_v
812 WHERE attr_group_type = l_attr_group_type
813 AND attr_name =l_attr_name
814 AND ATTR_GROUP_NAME = 'addresses';
815
816 IF PO_LOG.d_stmt THEN
817 PO_LOG.stmt(d_module,d_position,'l_value_set of '||l_attr_group_type||' and '||l_attr_group_type||' is ',l_value_set);
818 END IF;
819 EXCEPTION
820 WHEN no_data_found THEN
821 NULL;
822 END;
823
824
825 IF l_value_set IS NOT NULL THEN
826 INSERT INTO po_uda_interface
827 (transaction_id,
828 row_identifier,
829 attr_group_id,
830 attr_group_app_id,
831 attr_group_type,
832 attr_group_name,
833 data_level_1,
834 attr_name,
835 attr_value_str,
836 attr_value_num,
837 attr_value_date,
838 --attr_disp_value, <Bug 16565392> : Need not be populated in this flow
839 pk1_value,
840 pk2_value,
841 uda_template_id,
842 process_status)
843 VALUES (p_transaction_id,
844 l_row_identifier_add,
845 l_attr_group_id,
846 FND_GLOBAL.resp_appl_id,
847 l_attr_group_type,
848 l_attr_group_name,
849 l_data_level,
850 'HiddenLKPType',
851 l_value_set,
852 l_attr_value_num,
853 l_attr_value_date,
854 --l_attr_display_name, <Bug 16565392> : Need not be populated in this flow
855 p_pk1_value,
856 p_mod_draft_id,
857 p_uda_template_id,
858 l_process_status);
859 END IF;
860
861
862 END LOOP;
863
864 --Insert additional record
865
866 SELECT attr_group_id
867 INTO l_attr_group_id
868 FROM ego_attr_groups_v
869 WHERE attr_group_name = 'addresses'
870 AND ATTR_GROUP_TYPE = l_attr_group_type;
871
872 INSERT INTO po_uda_interface
873 (transaction_id,
874 row_identifier,
875 attr_group_id,
876 attr_group_app_id,
877 attr_group_type,
878 attr_group_name,
879 data_level_1,
880 attr_name,
881 attr_value_str,
882 attr_value_num,
883 attr_value_date,
884 --attr_disp_value, <Bug 16565392> : Need not be populated in this flow
885 pk1_value,
886 pk2_value,
887 uda_template_id,
888 process_status)
889 VALUES (p_transaction_id,
890 l_row_identifier_add,
891 l_attr_group_id,
892 FND_GLOBAL.resp_appl_id,
893 l_attr_group_type,
894 'addresses',
895 l_data_level,
896 'HiddenAddType',
897 l_address_type_tbl(i),
898 l_attr_value_num,
899 l_attr_value_date,
900 --l_attr_display_name, <Bug 16565392> : Need not be populated in this flow
901 p_pk1_value,
902 p_mod_draft_id,
903 p_uda_template_id,
904 l_process_status);
905
906 INSERT INTO po_uda_interface
907 (transaction_id,
908 row_identifier,
909 attr_group_id,
910 attr_group_app_id,
911 attr_group_type,
912 attr_group_name,
913 data_level_1,
914 attr_name,
915 attr_value_str,
916 attr_value_num,
917 attr_value_date,
918 --attr_disp_value, <Bug 16565392> : Need not be populated in this flow
919 pk1_value,
920 pk2_value,
921 uda_template_id,
922 process_status)
923 VALUES (p_transaction_id,
924 l_row_identifier_add,
925 l_attr_group_id,
926 FND_GLOBAL.resp_appl_id,
927 l_attr_group_type,
928 'addresses',
929 l_data_level,
930 'HiddenLKPType',
931 'PO_UDA_ADDRESS_TYPES',
932 l_attr_value_num,
933 l_attr_value_date,
934 --l_attr_display_name, <Bug 16565392> : Need not be populated in this flow
935 p_pk1_value,
936 p_mod_draft_id,
937 p_uda_template_id,
938 l_process_status);
939
940 END LOOP;
941 END IF;
942 -- End of address processing
943
944 ELSE
945 -- For UDA attributes other than addresses
946
947 IF p_mod_doc_source_name = 'PO_HEADERS_EXT_DRAFT_DIFF_V' THEN
948
949 SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
950 SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
951 BULK COLLECT INTO
952 l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
953 FROM po_entity_differences
954 WHERE mod_doc_pk2_val = p_par_draft_id
955 AND mod_doc_pk1_val = p_pk1_value
956 AND mod_doc_source_name = p_mod_doc_source_name
957 ORDER BY attr_group_name,attr_name,mod_value ;
958
959 ELSIF p_mod_doc_source_name ='PO_LINES_EXT_DRAFT_DIFF_V' THEN
960
961 SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
962 SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
963 BULK COLLECT INTO
964 l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
965 FROM po_entity_differences
966 WHERE mod_doc_pk2_val = p_par_draft_id
967 AND mod_doc_pk3_val = p_pk1_value
968 AND mod_doc_source_name = p_mod_doc_source_name
969 ORDER BY attr_group_name,attr_name,mod_value ;
970
971 ELSIF p_mod_doc_source_name ='PO_LINE_LOCS_DRAFT_EXT_DIFF_V' THEN
972
973 SELECT DISTINCT SubStr(column_name, 1,InStr(column_name,'_'||Chr(0))-1) attr_group_name,
974 SubStr(column_name, InStr(column_name, Chr(0))+1) attr_name, mod_value
975 BULK COLLECT INTO
976 l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
977 FROM po_entity_differences
978 WHERE mod_doc_pk2_val = p_par_draft_id
979 AND mod_doc_pk4_val = p_pk1_value
980 AND mod_doc_source_name = p_mod_doc_source_name
981 ORDER BY attr_group_name,attr_name,mod_value ;
982
983 END IF;
984
985 FOR i IN 1..l_attr_grp_tbl.Count
986 LOOP
987 IF l_attr_grp_name IS NULL OR l_attr_grp_tbl(i) <> l_attr_grp_name
988 THEN
989 l_attr_grp_name := l_attr_grp_tbl(i);
990 l_row_identifier := PO_UDA_INTERFACE_S.NEXTVAL;
991 END IF;
992
993 insert_row_in_uda_interface
994 (
995 p_transaction_id => p_transaction_id,
996 p_row_identifier => l_row_identifier,
997 p_pk1_value => p_pk1_value ,
998 p_mod_draft_id => p_mod_draft_id,
999 p_uda_template_id => p_uda_template_id,
1000 p_attr_group_type => l_attr_group_type ,
1001 p_attr_group_name => l_attr_grp_tbl(i) ,
1002 p_attr_name => l_attr_name_tbl(i),
1003 p_data_level => l_data_level,
1004 p_mod_value => l_mod_value_tbl(i)
1005 ) ;
1006 END LOOP;
1007
1008 --Insert the remaining entries which are missing in po_uda-interface
1009 -- but which are needed for UDA import
1010
1011 --Collect all the distinct attr group names
1012 --<Bug 16565392> : Removing the code as part of clean up
1013
1014 --Collect the missing entries
1015
1016 IF p_mod_doc_source_name = 'PO_HEADERS_EXT_DRAFT_DIFF_V' THEN
1017 --<Bug 16565392 Starts>
1018 --Collecting data for all such attributes that need to be copied from PAR
1019 WITH ag_data AS (
1020 SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
1021 SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
1022 col_value col_value
1023 FROM po_headers_ext_draft_diff_v
1024 WHERE col_value IS NOT NULL
1025 ) --Collecting the attribute grp name, aatribute name and their value in ag_data
1026 SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
1027 BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
1028 FROM ag_data
1029 --The attr group should be currently existing in po_uda_interface
1030 --and the attribute should not exist in po_uda_interface
1031 WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
1032 FROM po_uda_interface
1033 WHERE transaction_id = p_transaction_id
1034 AND data_level_1 = l_data_level
1035 AND pk1_value = p_pk1_value
1036 AND PK2_VALUE = p_mod_draft_id)
1037 AND NOT EXISTS (SELECT 1 FROM po_uda_interface
1038 WHERE pk2_value =p_mod_draft_id
1039 AND transaction_id = p_transaction_id
1040 AND ATTR_GROUP_NAME = ag_data.attr_grp_name
1041 AND ATTR_NAME = ag_data.attr_name);
1042 --<Bug 16565392 Ends>
1043
1044 ELSIF p_mod_doc_source_name ='PO_LINES_EXT_DRAFT_DIFF_V' THEN
1045 --<Bug 16565392 Starts>
1046 --Collecting data for all such attributes that need to be copied from PAR
1047 WITH ag_data AS (
1048 SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
1049 SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
1050 col_value col_value
1051 FROM po_lines_ext_draft_diff_v
1052 WHERE col_value IS NOT NULL
1053 ) --Collecting the attribute grp name, aatribute name and their value in ag_data
1054 SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
1055 BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
1056 FROM ag_data
1057 --The attr group should be currently existing in po_uda_interface
1058 --and the attribute should not exist in po_uda_interface
1059 WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
1060 FROM po_uda_interface
1061 WHERE transaction_id = p_transaction_id
1062 AND data_level_1 = l_data_level
1063 AND pk1_value = p_pk1_value
1064 AND PK2_VALUE = p_mod_draft_id)
1065 AND NOT EXISTS (SELECT 1 FROM po_uda_interface
1066 WHERE pk2_value =p_mod_draft_id
1067 AND transaction_id = p_transaction_id
1068 AND ATTR_GROUP_NAME = ag_data.attr_grp_name
1069 AND ATTR_NAME = ag_data.attr_name);
1070 --<Bug 16565392 Ends>
1071
1072 ELSIF p_mod_doc_source_name ='PO_LINE_LOCS_DRAFT_EXT_DIFF_V' THEN
1073 --<Bug 16565392 Starts>
1074 --Collecting data for all such attributes that need to be copied from PAR
1075 WITH ag_data AS (
1076 SELECT SubStr(col_name, 1,InStr(col_name,'_'||Chr(0))-1) attr_grp_name,
1077 SubStr(col_name, InStr(col_name, Chr(0))+1) attr_name ,
1078 col_value col_value
1079 FROM po_line_locs_ext_draft_diff_v
1080 WHERE col_value IS NOT NULL
1081 ) --Collecting the attribute grp name, aatribute name and their value in ag_data
1082 SELECT ag_data.attr_grp_name, ag_data.attr_name, ag_data.col_value
1083 BULK COLLECT INTO l_attr_grp_tbl, l_attr_name_tbl, l_mod_value_tbl
1084 FROM ag_data
1085 --The attr group should be currently existing in po_uda_interface
1086 --and the attribute should not exist in po_uda_interface
1087 WHERE ag_data.attr_grp_name IN (SELECT DISTINCT attr_group_name
1088 FROM po_uda_interface
1089 WHERE transaction_id = p_transaction_id
1090 AND data_level_1 = l_data_level
1091 AND pk1_value = p_pk1_value
1092 AND PK2_VALUE = p_mod_draft_id)
1093 AND NOT EXISTS (SELECT 1 FROM po_uda_interface
1094 WHERE pk2_value =p_mod_draft_id
1095 AND transaction_id = p_transaction_id
1096 AND ATTR_GROUP_NAME = ag_data.attr_grp_name
1097 AND ATTR_NAME = ag_data.attr_name);
1098 --<Bug 16565392 Ends>
1099
1100 END IF;
1101
1102 FOR k IN 1..l_attr_name_tbl.Count()
1103 LOOP
1104
1105 --Fetch the row indentifier for the attr grp
1106 SELECT DISTINCT row_identifier
1107 INTO l_row_identifier
1108 FROM po_uda_interface
1109 WHERE transaction_id = p_transaction_id
1110 AND attr_group_name = l_attr_grp_tbl(k)
1111 AND pk1_value = p_pk1_value;
1112
1113 insert_row_in_uda_interface
1114 (
1115 p_transaction_id => p_transaction_id,
1116 p_row_identifier => l_row_identifier,
1117 p_pk1_value => p_pk1_value ,
1118 p_mod_draft_id => p_mod_draft_id,
1119 p_uda_template_id => p_uda_template_id,
1120 p_attr_group_type => l_attr_group_type ,
1121 p_attr_group_name => l_attr_grp_tbl(k) ,
1122 p_attr_name => l_attr_name_tbl(k),
1123 p_data_level => l_data_level,
1124 p_mod_value => l_mod_value_tbl(k)
1125 ) ;
1126 END LOOP;
1127
1128 END IF;
1129 END POPULATE_UDA_DATA_IN_INTERFACE;
1130
1131 -----------------------------------------------------------------------
1132 --Start of Comments
1133 --Name: do_post_processing_for_par
1134 --Pre-reqs: None
1135 --Modifies:
1136 --Locks:
1137 --Procedure:
1138 -- This procedure updates mod_draft_id, line status and document creation method for par lines
1139 --Parameters:
1140 --IN:
1141 -- p_mod_draft_id NUMBER ,
1142 -- p_par_draft_id NUMBER,
1143 -- p_par_line_tbl po_tbl_number
1144 --IN OUT:
1145 --OUT:
1146 --Notes:
1147 --Testing:
1148 --End of Comments
1149 ------------------------------------------------------------------------
1150 PROCEDURE do_post_processing_for_par
1151 (p_mod_draft_id IN NUMBER ,
1152 p_par_draft_id_tbl IN po_tbl_number,
1153 p_is_header_changed VARCHAR2 ,
1154 p_par_line_tbl IN po_tbl_number DEFAULT NULL ) IS
1155
1156 d_api_name CONSTANT VARCHAR2(30) := 'do_post_processing_for_par';
1157 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
1158 l_distribution_id_tbl po_tbl_number := NULL;
1159 l_par_distribution_id_tbl po_tbl_number := NULL;
1160
1161 BEGIN
1162
1163 IF (PO_LOG.d_proc) THEN
1164 PO_LOG.proc_begin(d_module, 'p_mod_draft_id', p_mod_draft_id);
1165 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id_tbl);
1166 END IF;
1167
1168 IF p_par_line_tbl IS NOT NULL THEN
1169
1170 FOR i IN 1..p_par_line_tbl.Count() LOOP
1171
1172 IF (po_log.d_stmt) THEN
1173 po_log.stmt (d_module, 10, 'p_par_line_tbl '|| p_par_line_tbl(i) );
1174 END IF;
1175
1176 IF p_par_line_tbl(i) IS NOT NULL THEN
1177 UPDATE po_lines_draft_all
1178 SET DRAFT_LINE_STATUS = 'MOD_CREATED',
1179 mod_draft_id = p_mod_draft_id
1180 WHERE draft_id = p_par_draft_id_tbl(i)
1181 AND po_line_id = p_par_line_tbl(i);
1182
1183 UPDATE po_headers_draft_all
1184 SET document_creation_method = 'PAR_AUTOCREATE'
1185 WHERE draft_id = p_mod_draft_id;
1186
1187 --Update po_line_id in po_requisition_lines_all after mod creation
1188 UPDATE po_requisition_lines_all
1189 SET po_line_id = ( SELECT mod_line_id
1190 FROM po_lines_draft_all par, po_requisition_lines_all req
1191 WHERE req.par_line_id = p_par_line_tbl(i)
1192 AND req.par_line_id = par.po_line_id
1193 AND par.draft_id = p_par_draft_id_tbl(i)
1194 AND par.draft_id = req.par_draft_id
1195 )
1196 WHERE par_line_id = p_par_line_tbl(i)
1197 AND par_draft_id = p_par_draft_id_tbl(i);
1198
1199 --Update req_distribution_id in po_requisition_lines_all after mod creation
1200 BEGIN
1201 SELECT distribution_id, par_distribution_id
1202 BULK COLLECT INTO l_distribution_id_tbl, l_par_distribution_id_tbl
1203 FROM po_req_distributions_all prd, po_requisition_lines_all prl
1204 WHERE prl.par_line_id = p_par_line_tbl(i)
1205 AND prl.par_draft_id = p_par_draft_id_tbl(i)
1206 AND prd.par_draft_id = prl.par_draft_id
1207 AND prl.requisition_line_id = prd.requisition_line_id;
1208
1209 IF l_distribution_id_tbl.Count > 0
1210 THEN
1211 FORALL i IN 1..l_distribution_id_tbl.Count
1212 UPDATE po_distributions_draft_all
1213 SET req_distribution_id = l_distribution_id_tbl(i)
1214 WHERE draft_id = p_mod_draft_id
1215 AND par_distribution_id = l_par_distribution_id_tbl(i);
1216 END IF;
1217 EXCEPTION
1218 WHEN No_Data_Found THEN
1219 NULL;
1220 END;
1221
1222 END IF;
1223 END LOOP;
1224 END IF;
1225
1226 IF p_is_header_changed = 'Y' THEN
1227 FOR i IN 1..p_par_draft_id_tbl.Count loop
1228 IF p_par_line_tbl(i) IS NULL THEN
1229 UPDATE po_headers_draft_all
1230 SET mod_draft_id = p_mod_draft_id
1231 WHERE draft_id = p_par_draft_id_tbl(i);
1232 END IF;
1233 END LOOP;
1234 END IF;
1235
1236
1237 END do_post_processing_for_par;
1238 -----------------------------------------------------------------------
1239 --Start of Comments
1240 --Name: do_post_processing_for_par
1241 --Pre-reqs: None
1242 --Modifies:
1243 --Locks:
1244 --Procedure:
1245 --This procedure returns the value 'N',when the complex prcing structure in modification and
1246 --PAR doesnt match and the Line in the modification is Locked.
1247 --Parameters:
1248 --IN:
1249 -- p_mod_draft_id NUMBER ,
1250 -- p_par_draft_id NUMBER,
1251 -- p_par_line_id NUMBER
1252 -- x_return_status
1253 --IN OUT:
1254 --OUT:
1255 --Notes:
1256 --Testing:
1257 --End of Comments
1258 ------------------------------------------------------------------------
1259 PROCEDURE COMPLEX_PRICING_VALIDATION
1260 (P_MOD_DRAFT_ID IN NUMBER ,
1261 P_PAR_DRAFT_ID IN NUMBER,
1262 P_PO_LINE_ID IN NUMBER,
1263 X_RETURN_TYPE OUT NOCOPY VARCHAR2
1264 ) IS
1265
1266 l_return_type VARCHAR2(1);
1267 d_api_name CONSTANT VARCHAR2(30) := 'do_post_processing_for_par';
1268 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
1269
1270 BEGIN
1271
1272 l_return_type := 'Y';
1273 IF (PO_LOG.d_proc) THEN
1274 PO_LOG.proc_begin(d_module, 'p_mod_draft_id', p_mod_draft_id);
1275 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id);
1276 END IF;
1277
1278 --If some changes are made to complex pricing attributes of a Mod Line, and in PAR/web-service the complex pricing structure is changed,
1279 --then an error should be thrown - changes are incompatible. Similarly, if complex pricing structure is already changed in Mod Line,
1280 --and PAR/web-service is requesting for change in complex pricing attributes, error should be thrown.
1281 BEGIN
1282 SELECT 'N'
1283 INTO l_return_type
1284 FROM dual
1285 WHERE EXISTS (SELECT 'the line in the Autocreated Mod is Locked'
1286 FROM po_entity_locks
1287 WHERE lock_by_draft_id = P_MOD_DRAFT_ID
1288 AND entity_pk1 = P_PO_LINE_ID
1289 AND entity_name = 'PO_LINE')
1290 AND EXISTS (SELECT 'the structure is not same in MOD and PAR'
1291 FROM po_lines_draft_all par, po_lines_draft_all mod
1292 WHERE par.po_line_id = P_PO_LINE_ID
1293 AND par.draft_id = P_PAR_DRAFT_ID
1294 AND mod.po_line_id = par.po_line_id
1295 AND mod.draft_id = P_MOD_DRAFT_ID
1296 AND ( Nvl(par.CONTRACT_TYPE,'N') <> Nvl(mod.CONTRACT_TYPE,'N')
1297 OR Nvl(par.CLM_IDC_TYPE,'N') <> Nvl(mod.CLM_IDC_TYPE,'N')));
1298 EXCEPTION
1299 WHEN No_Data_Found THEN
1300 l_return_type := 'Y';
1301 END;
1302
1303 IF (PO_LOG.d_proc) THEN
1304 PO_LOG.proc_begin(d_module, 'l_return_type', l_return_type);
1305 END IF ;
1306
1307 X_RETURN_TYPE := l_return_type;
1308 END COMPLEX_PRICING_VALIDATION;
1309
1310 -----------------------------------------------------------------------
1311 --Start of Comments
1312 --Name: UPDATE_MOD_LINE_ID
1313 --Pre-reqs: None
1314 --Modifies:
1315 --Locks:
1316 --Procedure:
1317 --This procedure updates mod_line_id in PAR line.
1318 --Parameters:
1319 --IN:
1320 -- p_par_draft_id
1321 -- p_par_line_id
1322 -- p_mod_line_id
1323 --IN OUT:
1324 --OUT:
1325 --Notes:
1326 --Testing:
1327 --End of Comments
1328 ------------------------------------------------------------------------
1329 PROCEDURE UPDATE_MOD_LINE_ID
1330 (p_par_draft_id IN NUMBER,
1331 p_par_line_id IN NUMBER,
1332 p_mod_line_id IN NUMBER)
1333 IS
1334
1335 d_api_name CONSTANT VARCHAR2(30) := 'update_mod_line_id';
1336 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
1337
1338 BEGIN
1339
1340 IF (PO_LOG.d_proc) THEN
1341 PO_LOG.proc_begin(d_module, 'p_par_draft_id', p_par_draft_id);
1342 PO_LOG.proc_begin(d_module, 'p_par_line_id', p_par_line_id);
1343 PO_LOG.proc_begin(d_module, 'p_mod_line_id', p_mod_line_id);
1344 END IF;
1345
1346 UPDATE po_lines_draft_all
1347 SET mod_line_id = p_mod_line_id
1348 WHERE po_line_id = p_par_line_id
1349 AND draft_id = p_par_draft_id;
1350
1351 IF (po_log.d_stmt) THEN
1352 po_log.stmt (d_module, 10, 'No of lines updated : ' || sql%rowcount );
1353 END IF;
1354
1355 END UPDATE_MOD_LINE_ID;
1356
1357 -----------------------------------------------------------------------
1358 --Start of Comments
1359 --Name: COPY_PAR_ATTACHMENTS
1360 --Pre-reqs: None
1361 --Modifies:
1362 --Locks:
1363 --Procedure:
1364 --This procedure copies attachments from PAR to mod.
1365 --Parameters:
1366 --IN:
1367 -- p_par_draft_id_tbl
1368 -- p_par_line_id_tbl
1369 -- p_mod_draft_id
1370 --IN OUT:
1371 --OUT:
1372 --Notes:
1373 --Testing:
1374 --End of Comments
1375 ------------------------------------------------------------------------
1376 PROCEDURE COPY_PAR_ATTACHMENTS
1377 (p_par_draft_id_tbl IN PO_TBL_NUMBER ,
1378 p_par_line_id_tbl IN PO_TBL_NUMBER,
1379 p_mod_draft_id IN NUMBER ) IS
1380
1381 --Fetch All PAR related Shipments
1382 CURSOR fetch_line_location_ids(p_line_id NUMBER, p_par_draft_id NUMBER , p_change_status VARCHAR2 ) IS
1383 SELECT line_location_id
1384 FROM po_line_locations_draft_all
1385 WHERE po_line_id = p_line_id
1386 AND draft_id = p_par_draft_id
1387 AND change_status = p_change_status ;
1388
1389 l_po_header_id NUMBER;
1390 l_mod_line_id NUMBER;
1391 l_mod_line_location_id NUMBER;
1392 l_attach_count NUMBER;
1393
1394 d_api_name CONSTANT VARCHAR2(30) := 'COPY_PAR_ATTACHMENTS';
1395 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
1396
1397 BEGIN
1398
1399 IF (PO_LOG.d_proc) THEN
1400 PO_LOG.proc_begin(d_module, 'p_par_draft_id_tbl', p_par_draft_id_tbl);
1401 PO_LOG.proc_begin(d_module, 'p_par_line_id_tbl', p_par_line_id_tbl);
1402 PO_LOG.proc_begin(d_module, 'p_mod_draft_id', p_mod_draft_id);
1403 END IF;
1404
1405 --Fetch the header Id
1406 SELECT document_id
1407 INTO l_po_header_id
1408 FROM po_drafts
1409 WHERE draft_id = p_mod_draft_id;
1410
1411
1412 IF (po_log.d_stmt) THEN
1413 po_log.stmt (d_module, 1, 'l_po_header_id '|| l_po_header_id );
1414 END IF;
1415
1416
1417 --Copy all header attachments
1418 FOR i IN 1..p_par_draft_id_tbl.Count() LOOP
1419
1420 -- Only header changes
1421 IF p_par_line_id_tbl(i) IS NULL THEN
1422
1423 --Call FND API only if there are attachments for the header
1424 l_attach_count := get_attachment_count(l_po_header_id, p_par_draft_id_tbl(i), 'PO_HEADERS');
1425 IF l_attach_count <> 0
1426 THEN
1427
1428 fnd_attached_documents2_pkg.copy_attachments ( 'PO_HEADERS',
1429 l_po_header_id||'-'||p_par_draft_id_tbl(i),
1430 '',
1431 '',
1432 '',
1433 '',
1434 'PO_HEADERS',
1435 l_po_header_id||'-'||p_mod_draft_id,
1436 '',
1437 '',
1438 '',
1439 '',
1440 fnd_global.user_id,
1441 fnd_global.login_id,
1442 '',
1443 '',
1444 ''
1445 );
1446
1447 END IF;
1448 END IF;
1449 END LOOP;
1450
1451 --Copy all line attachments
1452
1453 IF (po_log.d_stmt) THEN
1454 po_log.stmt (d_module, 5, 'No of Lines '|| p_par_line_id_tbl.count );
1455 po_log.stmt (d_module, 10, 'Processing of line level attachments - START');
1456 END IF;
1457
1458 --Iterate through each line and copy line and shipment level attachments
1459 FOR i IN 1..p_par_line_id_tbl.Count LOOP
1460
1461 IF p_par_line_id_tbl(i) IS NOT NULL THEN
1462
1463 l_attach_count := get_attachment_count(p_par_line_id_tbl(i), p_par_draft_id_tbl(i), 'PO_LINES') ;
1464
1465 IF (po_log.d_stmt) THEN
1466 po_log.stmt (d_module, 20, 'Po Line Id is '|| p_par_line_id_tbl(i) );
1467 po_log.stmt (d_module, 30, 'Attachment count is '|| l_attach_count);
1468 END IF;
1469
1470 IF l_attach_count <> 0
1471 THEN
1472 SELECT mod_line_id
1473 INTO l_mod_line_id
1474 FROM po_lines_draft_all
1475 WHERE draft_id =p_par_draft_id_tbl(i)
1476 AND po_line_id = p_par_line_id_tbl(i);
1477
1478 IF (po_log.d_stmt) THEN
1479 po_log.stmt (d_module, 40, 'l_mod_line_id is '|| l_mod_line_id );
1480 END IF;
1481
1482 --In case of new line or when there are changes on a line other than attachemnts , mod_line_id
1483 -- is stamped.
1484 IF l_mod_line_id IS NOT NULL THEN
1485 fnd_attached_documents2_pkg.copy_attachments ( 'PO_LINES',
1486 p_par_line_id_tbl(i)||'-'||p_par_draft_id_tbl(i),
1487 '',
1488 '',
1489 '',
1490 '',
1491 'PO_LINES',
1492 l_mod_line_id||'-'||p_mod_draft_id,
1493 '',
1494 '',
1495 '',
1496 '',
1497 fnd_global.user_id,
1498 fnd_global.login_id,
1499 '',
1500 '',
1501 ''
1502 );
1503 -- In case of an old line in which only attachments are updated,
1504 -- then l_mod_line_id might not be stamped
1505
1506 ELSE IF l_mod_line_id IS NULL THEN
1507 fnd_attached_documents2_pkg.copy_attachments ('PO_LINES',
1508 p_par_line_id_tbl(i)||'-'||p_par_draft_id_tbl(i),
1509 '',
1510 '',
1511 '',
1512 '',
1513 'PO_LINES',
1514 p_par_line_id_tbl(i)||'-'||p_mod_draft_id,
1515 '',
1516 '',
1517 '',
1518 '',
1519 fnd_global.user_id,
1520 fnd_global.login_id,
1521 '',
1522 '',
1523 ''
1524 );
1525 END IF;
1526 END IF;
1527 END IF;
1528
1529 IF (po_log.d_stmt) THEN
1530 po_log.stmt (d_module, 50, 'Processing of line level attachments - END');
1531 END IF;
1532
1533 --Copy shipments related attachmnets
1534
1535 IF (po_log.d_stmt) THEN
1536 po_log.stmt (d_module, 60, 'Processing of shipment level attachments - START');
1537 END IF;
1538
1539 -- Start with shipments that are updated
1540 for shipments_rec in fetch_line_location_ids(p_par_line_id_tbl(i), p_par_draft_id_tbl(i), 'UPDATE') LOOP
1541
1542 l_attach_count := get_attachment_count(shipments_rec.line_location_id, p_par_draft_id_tbl(i), 'PO_SHIPMENTS');
1543 IF (po_log.d_stmt) THEN
1544 po_log.stmt (d_module, 80, 'Line Location Id is '|| shipments_rec.line_location_id);
1545 po_log.stmt (d_module, 90, 'Attachment count is '|| l_attach_count);
1546 END IF;
1547
1548 IF get_attachment_count(shipments_rec.line_location_id, p_par_draft_id_tbl(i), 'PO_SHIPMENTS') <> 0
1549 THEN
1550 fnd_attached_documents2_pkg.copy_attachments('PO_SHIPMENTS',
1551 shipments_rec.line_location_id||'-'||p_par_draft_id_tbl(i),
1552 '',
1553 '',
1554 '',
1555 '',
1556 'PO_SHIPMENTS',
1557 shipments_rec.line_location_id||'-'||p_mod_draft_id,
1558 '',
1559 '',
1560 '',
1561 '',
1562 fnd_global.user_id,
1563 fnd_global.login_id,
1564 '',
1565 '',
1566 ''
1567 );
1568
1569 END IF;
1570 END LOOP;
1571
1572 -- New shipments
1573 for shipments_rec in fetch_line_location_ids(p_par_line_id_tbl(i), p_par_draft_id_tbl(i), 'NEW') LOOP
1574 l_attach_count := get_attachment_count(shipments_rec.line_location_id, p_par_draft_id_tbl(i), 'PO_SHIPMENTS');
1575 IF (po_log.d_stmt) THEN
1576 po_log.stmt (d_module, 110, 'Line Location Id is '|| shipments_rec.line_location_id);
1577 po_log.stmt (d_module, 120, 'Attachment count is '|| l_attach_count);
1578 END IF;
1579
1580 IF l_attach_count <> 0 THEN
1581 BEGIN
1582 --fetch the lineLocation id of the Modification
1583 SELECT line_location_id
1584 INTO l_mod_line_location_id
1585 FROM po_line_locations_draft_all
1586 WHERE draft_id = p_mod_draft_id
1587 AND po_line_id =Nvl((SELECT mod_line_id
1588 FROM po_lines_draft_all
1589 WHERE po_line_id = p_par_line_id_tbl(i)
1590 AND draft_id =p_par_draft_id_tbl(i)), p_par_line_id_tbl(i) )
1591 AND shipment_num = (SELECT shipment_num
1592 FROM po_line_locations_draft_all
1593 WHERE line_location_id = shipments_rec.line_location_id) ;
1594 EXCEPTION
1595 WHEN No_Data_Found
1596 THEN NULL;
1597 END;
1598
1599 IF (po_log.d_stmt) THEN
1600 po_log.stmt (d_module, 130, 'Line Location Id on the modifictaion is '|| l_mod_line_location_id);
1601 END IF;
1602
1603 IF l_mod_line_location_id IS NOT NULL THEN
1604 fnd_attached_documents2_pkg.copy_attachments('PO_SHIPMENTS',
1605 shipments_rec.line_location_id||'-'||p_par_draft_id_tbl(i),
1606 '',
1607 '',
1608 '',
1609 '',
1610 'PO_SHIPMENTS',
1611 l_mod_line_location_id||'-'||p_mod_draft_id,
1612 '',
1613 '',
1614 '',
1615 '',
1616 fnd_global.user_id,
1617 fnd_global.login_id,
1618 '',
1619 '',
1620 ''
1621 );
1622 END IF;
1623 END IF;
1624 END LOOP;
1625 END IF;
1626 END LOOP;
1627 END COPY_PAR_ATTACHMENTS;
1628 --<PAR Project END>
1629
1630 -----------------------------------------------------------------------
1631 --Start of get_attachment_count
1632 --Name: get_attachment_count
1633 --Pre-reqs: None
1634 --Modifies:
1635 --Locks:
1636 --Procedure:
1637 --This function returns the no of attachments for an entity Id
1638 --Parameters:
1639 --IN:
1640 -- p_pk1_value
1641 -- p_po_draft_id
1642 -- p_entity_name
1643 --IN OUT:
1644 --OUT: NUMBER
1645 --Notes:
1646 --Testing:
1647 --End of Comments
1648 ------------------------------------------------------------------------
1649 FUNCTION get_attachment_count(
1650 p_pk1_value IN NUMBER,
1651 p_po_draft_id IN NUMBER,
1652 p_entity_name IN VARCHAR2
1653 ) RETURN NUMBER IS
1654
1655 l_count NUMBER;
1656
1657 d_api_name CONSTANT VARCHAR2(30) := 'get_attachment_count';
1658 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name || '.';
1659
1660 BEGIN
1661
1662 IF (PO_LOG.d_proc) THEN
1663 PO_LOG.proc_begin(d_module, 'p_pk1_value ', p_pk1_value);
1664 PO_LOG.proc_begin(d_module, 'p_po_draft_id ', p_po_draft_id);
1665 PO_LOG.proc_begin(d_module, 'p_entity_name ', p_entity_name);
1666 END IF;
1667
1668
1669 SELECT Count(*) INTO l_count
1670 FROM FND_ATTACHED_DOCUMENTS
1671 WHERE entity_name = p_entity_name
1672 AND pk1_value = p_pk1_value||'-'|| p_po_draft_id ;
1673
1674 IF (po_log.d_stmt) THEN
1675 po_log.stmt (d_module, 10, 'Count is is '|| l_count);
1676 END IF;
1677
1678 RETURN l_count;
1679
1680 END get_attachment_count;
1681
1682
1683 END PO_AUTOCREATE_PVT;