[Home] [Help]
PACKAGE BODY: APPS.PO_COPYDOC_S1
Source
1 PACKAGE BODY po_copydoc_s1 AS
2 /* $Header: POXCPO1B.pls 120.63.12020000.4 2013/02/25 09:23:49 mabaig ship $*/
3
4 --< Shared Proc FPJ Start >
5 -- Debugging booleans used to bypass logging when turned off
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8
9 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_COPYDOC_S1';
10 g_module_prefix CONSTANT VARCHAR2(30) := 'po.plsql.' || g_pkg_name || '.';
11 --< Shared Proc FPJ End >
12
13 --<Unified Catalog R12: Start>
14 -- The module base for this package.
15 D_PACKAGE_BASE CONSTANT VARCHAR2(50) := PO_LOG.get_package_base(g_pkg_name);
16
17 -- The module base for the subprogram.
18 D_copy_attributes CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'copy_attributes');
19 --<Unified Catalog R12: End>
20
21 --<Enhanced Pricing Start:>
22 D_copy_line_adjustments CONSTANT VARCHAR2(100) := PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'copy_line_adjustments');
23
24 -- Cursor definitions:
25
26 CURSOR po_line_cursor(x_po_header_id po_lines.po_header_id%TYPE) IS
27 SELECT *
28 FROM PO_LINES
29 WHERE po_header_id = x_po_header_id
30 ORDER BY line_num;
31
32 CURSOR po_shipment_cursor(x_po_line_id po_line_locations.po_line_id%TYPE) IS
33 SELECT *
34 FROM PO_LINE_LOCATIONS
35 WHERE po_line_id = x_po_line_id
36 AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET') --Bug: 1773758 1992096
37 ORDER BY shipment_num;
38
39 CURSOR po_distribution_cursor(x_line_location_id po_distributions.line_location_id%TYPE) IS
40 SELECT *
41 FROM PO_DISTRIBUTIONS
42 WHERE line_location_id = x_line_location_id
43 AND distribution_type <> 'AGREEMENT' --bug 3338216: filter BPA dists
44 ORDER BY distribution_num;
45
46 -- CLM CLIN/SLIN Changes
47 CURSOR clm_po_line_cursor(x_po_header_id po_lines.po_header_id%TYPE) IS
48 SELECT DISTINCT group_line_id
49 FROM PO_LINES
50 WHERE po_header_id = x_po_header_id
51 AND group_line_id IS NOT NULL
52 UNION
53 SELECT DISTINCT clm_base_line_num
54 FROM PO_LINES
55 WHERE po_header_id = x_po_header_id
56 AND clm_base_line_num IS NOT NULL;
57
58 CURSOR clm_po_distribution_cursor(x_po_header_id po_lines.po_header_id%TYPE) IS
59 SELECT DISTINCT group_line_id
60 FROM PO_DISTRIBUTIONS
61 WHERE po_header_id = x_po_header_id
62 AND distribution_type <> 'AGREEMENT'
63 AND group_line_id IS NOT NULL;
64
65 -- End of Cursor definitions
66
67 -- Private function prototypes
68
69 PROCEDURE fetch_header(
70 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
71 x_from_po_header_id IN po_headers.po_header_id%TYPE,
72 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
73 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
74 x_return_code OUT NOCOPY NUMBER
75 );
76 PROCEDURE insert_header(
77 x_po_header_record IN PO_HEADERS%ROWTYPE,
78 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
79 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
80 x_return_code OUT NOCOPY NUMBER,
81 x_copy_terms IN VARCHAR2 --<CONTERMS FPJ>
82 );
83 PROCEDURE insert_line(
84 x_po_line_record IN po_lines%ROWTYPE,
85 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
86 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
87 x_return_code OUT NOCOPY NUMBER
88 );
89
90 PROCEDURE insert_shipment(
91 x_po_shipment_record IN po_line_locations%ROWTYPE,
92 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
93 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
94 x_line_num IN po_online_report_text.line_num%TYPE,
95 x_accrue_on_receipt_flag IN VARCHAR2,
96 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
97 x_return_code OUT NOCOPY NUMBER,
98 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
99 p_orig_line_location_id IN NUMBER -- <eTax Integration R12>
100 );
101
102 PROCEDURE insert_distribution(
103 x_po_distribution_record IN po_distributions%ROWTYPE,
104 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
105 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
106 x_line_num IN po_online_report_text.line_num%TYPE,
107 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
108 x_return_code OUT NOCOPY NUMBER
109 );
110
111 PROCEDURE handle_fatal(
112 x_return_code OUT NOCOPY NUMBER
113 );
114
115 PROCEDURE process_header(
116 x_action_code IN VARCHAR2,
117 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
118 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
119 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
120 x_from_po_header_id IN po_headers.po_header_id%TYPE,
121 x_to_segment1 IN po_headers.segment1%TYPE,
122 x_agent_id IN po_headers.agent_id%TYPE,
123 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
124 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
125 x_copy_attachments IN BOOLEAN,
126 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
127 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
128 x_return_code OUT NOCOPY NUMBER,
129 x_copy_terms IN VARCHAR2 -- <FPJ CONTERMS>
130 );
131
132 PROCEDURE process_line(
133 x_action_code IN VARCHAR2,
134 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
135 x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE,
136 x_orig_po_line_id IN po_lines.po_line_id%TYPE,
137 x_wip_install_status IN VARCHAR2,
138 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
139 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
140 x_po_header_id IN po_lines.po_header_id%TYPE,
141 x_from_po_header_id IN po_lines.po_header_id%TYPE,
142 x_copy_attachments IN BOOLEAN,
143 x_copy_price IN BOOLEAN,
144 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
145 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
146 x_return_code OUT NOCOPY NUMBER,
147 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
148 p_calling_program IN VARCHAR2,
149 p_is_clm_doc IN VARCHAR2 --<Bug 14370174>
150 );
151
152 --< Shared Proc FPJ Start >
153 PROCEDURE process_shipment
154 (
155 p_action_code IN VARCHAR2,
156 p_to_doc_subtype IN VARCHAR2,
157 p_orig_line_location_id IN NUMBER,
158 p_po_header_id IN NUMBER,
159 p_po_line_id IN NUMBER,
160 p_item_category_id IN NUMBER, --< Shared Proc FPJ >
161 p_copy_attachments IN BOOLEAN,
162 p_copy_price IN BOOLEAN,
163 p_online_report_id IN NUMBER,
164 p_line_num IN NUMBER,
165 p_inv_org_id IN NUMBER,
166 p_item_id IN NUMBER, -- Bug 3433867
167 x_po_shipment_record IN OUT NOCOPY PO_LINE_LOCATIONS%ROWTYPE,
168 x_sequence IN OUT NOCOPY NUMBER,
169 x_return_code OUT NOCOPY NUMBER,
170 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
171 );
172
173 --<Encumbrance FPJ: add sob_id to param list>
174 PROCEDURE process_distribution
175 (
176 p_action_code IN VARCHAR2,
177 p_to_doc_subtype IN VARCHAR2,
178 p_orig_po_distribution_id IN NUMBER,
179 p_generate_new_accounts IN BOOLEAN,
180 p_copy_attachments IN BOOLEAN,
181 p_online_report_id IN NUMBER,
182 p_po_header_rec IN PO_HEADERS%ROWTYPE,
183 p_po_line_rec IN PO_LINES%ROWTYPE,
184 p_po_shipment_rec IN PO_LINE_LOCATIONS%ROWTYPE,
185 p_sob_id IN FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE,
186 x_po_distribution_rec IN OUT NOCOPY PO_DISTRIBUTIONS%ROWTYPE,
187 x_sequence IN OUT NOCOPY NUMBER,
188 x_return_code OUT NOCOPY NUMBER
189 );
190 --< Shared Proc FPJ End >
191
192
193 -- Global variable declarations
194
195 g_debug_flag BOOLEAN := TRUE;
196 --<R12 eTax Integration>
197 g_tax_attribute_update_code PO_HEADERS_ALL.tax_attribute_update_code%TYPE;
198
199 -- End of Global variable declarations
200
201
202 PROCEDURE copydoc_debug(
203 x_message IN VARCHAR2
204 ) IS
205 BEGIN
206 IF (g_debug_flag) THEN
207 --dbms_output.put_line('[Debug] '||x_message);
208 null;
209 END IF;
210 END;
211
212 --<HTML Agreements R12 Start>
213 -- Making this procedure a autonomous transaction so that even if copy doc
214 -- fails we should get the error
215 --<HTML Agreements R12 End>
216 PROCEDURE online_report(
217 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
218 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
219 x_message IN po_online_report_text.text_line%TYPE,
220 x_line_num IN po_online_report_text.line_num%TYPE,
221 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
222 x_distribution_num IN po_online_report_text.distribution_num%TYPE,
223 x_message_type IN VARCHAR2 -- <PO_PJM_VALIDATION FPI>
224 ) IS
225 pragma AUTONOMOUS_TRANSACTION; --<HTML Agreements R12>
226 x_text_line po_online_report_text.text_line%TYPE := NULL;
227 x_line_num_msg VARCHAR2(100) := NULL;
228 x_shipment_num_msg VARCHAR2(100) := NULL;
229 x_distribution_num_msg VARCHAR2(100) := NULL;
230 -- <PO_PJM_VALIDATION FPI>
231 -- Increased x_text_line_length to 2000 (length of PO_ONLINE_REPORT_TEXT.text_line);
232 -- changed substr to substrb below to handle multibyte characters.
233 x_text_line_length NUMBER := 2000;
234
235 BEGIN
236
237 IF ((x_online_report_id IS NULL) OR (x_message IS NULL) OR (nvl(x_sequence, 0) < 1)) THEN
238 RETURN;
239 END IF;
240
241 IF (nvl(x_line_num, 0) >= 1) THEN
242 x_line_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
243 x_text_line := substrb(x_line_num_msg || x_line_num || ' ', 1, x_text_line_length);
244 IF (nvl(x_shipment_num, 0) >= 1) THEN
245 x_shipment_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
246 x_text_line := substrb(x_text_line || x_shipment_num_msg || x_shipment_num || ' ', 1, x_text_line_length);
247 IF (nvl(x_distribution_num, 0) >= 1) THEN
248 x_distribution_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
249 x_text_line := substrb(x_text_line || x_distribution_num_msg || x_distribution_num || ' ', 1, x_text_line_length);
250 END IF;
251 END IF;
252 END IF;
253
254 x_text_line := substrb(x_text_line || x_message, 1, x_text_line_length);
255
256 BEGIN
257 INSERT INTO PO_ONLINE_REPORT_TEXT (
258 online_report_id,
259 sequence,
260 last_updated_by,
261 last_update_date,
262 created_by,
263 creation_date,
264 last_update_login,
265 text_line,
266 message_type -- <PO_PJM_VALIDATION FPI>
267 )
268 VALUES (
269 x_online_report_id,
270 x_sequence,
271 fnd_global.user_id,
272 SYSDATE,
273 fnd_global.user_id,
274 SYSDATE,
275 fnd_global.login_id,
276 x_text_line,
277 x_message_type -- <PO_PJM_VALIDATION FPI>
278 );
279 COMMIT;
280
281 x_sequence := x_sequence + 1;
282
283 EXCEPTION
284 WHEN OTHERS THEN
285 NULL;
286 END;
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 NULL;
291 END online_report;
292
293 --<HTML AGREEMENTS R12 Start>
294 ------------------------------------------------------------------------
295 --Start of Comments
296 --Name: ret_and_del_online_report_rec
297 --Pre-reqs:
298 -- None.
299 --Modifies:
300 -- po_online_report_text.
301 --Locks:
302 -- None.
303 --Function:
304 -- This procedure would return the message_text and message_type
305 -- and would also delete the data from the po_online_report_text.
306 -- As this procedure is an autonomous transaction we would commit
307 -- as soon as we delete
308 --IN:
309 -- p_online_report_id
310 -- Online Report Id of the message to be retrieved and deleted
311 --OUT:
312 --x_message_type
313 -- MessageType for online report message if any inserted to the table
314 -- while procedure execution
315 --x_text_line
316 -- Message if any inserted to the online_report_text table while procedure
317 -- execution.
318 --Testing:
319 -- Refer the Unit Test Plan for 'HTML Agreements R12'
320 --End of Comments
321 ----------------------------------------------------------------------------
322 PROCEDURE ret_and_del_online_report_rec( p_online_report_id IN NUMBER
323 ,x_message_type OUT NOCOPY VARCHAR2
324 ,x_message OUT NOCOPY VARCHAR2)
325 IS
326 pragma AUTONOMOUS_TRANSACTION;
327 d_pos NUMBER;
328 d_module VARCHAR2(70) := 'po.plsql.PO_COPYDOC_S1.RET_AND_DEL_ONLINE_REPORT_REC';
329 d_log_msg VARCHAR2(200);
330 /*Bug:13077836 start */
331 TYPE MsgTextLineTab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
332 TYPE MsgTypeTab IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
333 l_message_type MsgTypeTab;
334 l_message MsgTextLineTab;
335 /*Bug:13077836 end */
336 BEGIN
337 IF (PO_LOG.d_proc) THEN
338 PO_LOG.proc_begin(d_module);
339 PO_LOG.proc_begin(d_module, 'p_online_report_id', p_online_report_id);
340 END IF;
341 d_pos := 10;
342 DELETE PO_ONLINE_REPORT_TEXT
343 WHERE ONLINE_REPORT_ID = p_online_report_id
344 RETURNING MESSAGE_TYPE, TEXT_LINE
345 BULK COLLECT INTO l_message_type, l_message; /*Bug:13077836 delete is returning more than one row*/
346 d_pos := 20;
347 FOR l_index in 1..l_message.COUNT LOOP
348 x_message:=x_message||l_message(l_index)||'~';
349 x_message_type:=l_message_type(l_index);
350 End Loop;
351 IF PO_LOG.d_stmt THEN
352 PO_LOG.stmt(d_module,d_pos,'x_message_type',x_message_type);
353 PO_LOG.stmt(d_module,d_pos,'x_message',x_message);
354 END IF;
355 COMMIT;
356 d_pos := 30;
357 IF PO_LOG.d_event THEN
358 PO_LOG.event(d_module,d_pos,'Committed after Deleting Record from PO_Online_Report_Text');
359 END IF;
360 IF PO_LOG.d_proc THEN
361 PO_LOG.proc_end(d_module,'x_message_type',x_message_type);
362 PO_LOG.proc_end(d_module,'x_message',x_message);
363 PO_LOG.proc_end(d_module);
364 END IF;
365 EXCEPTION
366 WHEN OTHERS THEN
367 d_log_msg := 'Unhandled Exception in ' || d_module;
368 IF PO_LOG.d_exc THEN
369 PO_LOG.exc(d_module,d_pos,d_log_msg);
370 END IF;
371 RAISE;
372 END ret_and_del_online_report_rec;
373 --<HTML AGREEMENTS R12 End>
374
375 PROCEDURE copydoc_sql_error(
376 x_routine IN VARCHAR2,
377 x_progress IN VARCHAR2,
378 x_sqlcode IN NUMBER,
379 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
380 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
381 x_line_num IN po_online_report_text.line_num%TYPE,
382 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
383 x_distribution_num IN po_online_report_text.distribution_num%TYPE
384 ) IS
385
386 x_message VARCHAR2(240);
387
388 BEGIN
389
390 fnd_message.set_name('PO', 'PO_ALL_SQL_ERROR');
391 fnd_message.set_token('ROUTINE', x_routine);
392 fnd_message.set_token('ERR_NUMBER', x_progress);
393 fnd_message.set_token('SQL_ERR', SQLERRM(x_sqlcode));
394
395 x_message := substr(fnd_message.get, 1, 240);
396
397 online_report(x_online_report_id,
398 x_sequence,
399 x_message,
400 x_line_num,
401 x_shipment_num,
402 x_distribution_num);
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 NULL;
407 END copydoc_sql_error;
408
409 /*************************************************************
410 ** Get all the header info for the document with x_from_po_header_id
411 ** and store into x_po_header_record for future processing
412 *************************************************************/
413 PROCEDURE fetch_header(
414 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
415 x_from_po_header_id IN po_headers.po_header_id%TYPE,
416 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
417 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
418 x_return_code OUT NOCOPY NUMBER
419 ) IS
420
421 x_progress VARCHAR2(4);
422
423 BEGIN
424
425 x_progress := '001';
426
427 SELECT *
428 INTO x_po_header_record
429 FROM PO_HEADERS
430 WHERE po_header_id = x_from_po_header_id;
431
432 x_return_code := 0;
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 copydoc_sql_error('fetch_header', x_progress, sqlcode,
437 x_online_report_id,
438 x_sequence,
439 0, 0, 0);
440 x_return_code := -1;
441 END fetch_header;
442
443
444 /****************************************************************
445 ** create new PO record from info. stored in x_po_header_record
446 ****************************************************************/
447 PROCEDURE insert_header(
448 x_po_header_record IN PO_HEADERS%ROWTYPE,
449 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
450 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
451 x_return_code OUT NOCOPY NUMBER,
452 x_copy_terms IN VARCHAR2 --<CONTERMS FPJ>
453 ) IS
454
455 l_progress VARCHAR2(4);
456 -- Bug: 1566075 Declare variable
457 tmp_pay_on_code VARCHAR2(25) := NULL;
458 tmp_vendor_site_id NUMBER := NULL;
459
460 --< Shared Proc FPJ Start >
461 l_org_assign_rec PO_GA_ORG_ASSIGNMENTS%ROWTYPE;
462 l_org_row_id ROWID;
463 --< Shared Proc FPJ End >
464
465 l_return_status VARCHAR2(1);
466 l_msg_data VARCHAR2(2000);
467 l_msg_count NUMBER;
468 l_ame_approval_id NUMBER;
469 l_clm_default_dist VARCHAR2(1);
470 l_ame_transaction_type po_headers_all.ame_transaction_type%type; --PO AME porting
471 BEGIN
472
473 l_progress := '000';
474
475 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
476 PO_DEBUG.debug_stmt
477 (p_log_head => g_module_prefix||'insert_header',
478 p_token => 'invoked',
479 p_message => 'header ID: '||x_po_header_record.po_header_id||
480 ' online_report ID: '||x_online_report_id);
481 END IF;
482
483 --SELECT po_ame_approvals_s.NEXTVAL into l_ame_approval_id FROM dual;
484 /* PO AME Approval workflow change : Fetch next sequence value of ame_approval_id
485 in case AME transaction type is populated in Style Headers page*/
486 -- Start : PO AME Approval workflow
487 l_ame_approval_id := null;
488 l_ame_transaction_type := NULL;
489 IF (x_po_header_record.type_lookup_code = 'STANDARD'
490 OR (x_po_header_record.type_lookup_code in ('BLANKET','CONTRACT')
491 AND nvl(x_po_header_record.global_agreement_flag,'N') = 'Y')) THEN
492 BEGIN
493 SELECT po_ame_approvals_s.NEXTVAL, ame_transaction_type
494 INTO l_ame_approval_id, l_ame_transaction_type
495 FROM po_doc_style_headers podsh
496 WHERE podsh.style_id= x_po_header_record.style_id
497 AND podsh.ame_transaction_type IS NOT NULL;
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 l_ame_approval_id := null;
501 l_ame_transaction_type := null;
502 END;
503 END IF;
504 -- End : PO AME Approval workflow
505
506 -- <CLM-LnSc-START>
507 -- Fetch the value for CLM_DEFAULT_DIST_FLAG
508 l_clm_default_dist := PO_CORE_S.get_create_default_dist(
509 p_org_id => x_po_header_record.org_id,
510 p_style_id => x_po_header_record.style_id,
511 p_doc_sub_type => x_po_header_record.type_lookup_code);
512 -- <CLM-LnSc-END>
513
514 -- Bug #2015328, added decode statements to null out start_date and
515 -- end_date when a standard purchase order is copied from other type
516 -- of docs. This is because the std. purchase order should not have
517 -- the start and end date defined in terms and conditions.
518 INSERT INTO PO_HEADERS (
519 acceptance_due_date,
520 acceptance_required_flag,
521 agent_id,
522 amount_limit,
523 approval_required_flag,
524 approved_date,
525 approved_flag,
526 attribute1,
527 attribute10,
528 attribute11,
529 attribute12,
530 attribute13,
531 attribute14,
532 attribute15,
533 attribute2,
534 attribute3,
535 attribute4,
536 attribute5,
537 attribute6,
538 attribute7,
539 attribute8,
540 attribute9,
541 attribute_category,
542 authorization_status,
543 bill_to_location_id,
544 blanket_total_amount,
545 cancel_flag,
546 closed_code,
547 closed_date,
548 comments,
549 confirming_order_flag,
550 created_by,
551 creation_date,
552 currency_code,
553 edi_processed_flag,
554 edi_processed_status,
555 enabled_flag,
556 end_date,
557 end_date_active,
558 firm_date,
559 firm_status_lookup_code,
560 fob_lookup_code,
561 freight_terms_lookup_code,
562 from_header_id,
563 from_type_lookup_code,
564 frozen_flag,
565 global_agreement_flag, -- Global Agreements (FP-I)
566 global_attribute1,
567 global_attribute10,
568 global_attribute11,
569 global_attribute12,
570 global_attribute13,
571 global_attribute14,
572 global_attribute15,
573 global_attribute16,
574 global_attribute17,
575 global_attribute18,
576 global_attribute19,
577 global_attribute2,
578 global_attribute20,
579 global_attribute3,
580 global_attribute4,
581 global_attribute5,
582 global_attribute6,
583 global_attribute7,
584 global_attribute8,
585 global_attribute9,
586 global_attribute_category,
587 government_context,
588 interface_source_code,
589 last_updated_by,
590 last_update_date,
591 last_update_login,
592 min_release_amount,
593 mrc_rate,
594 mrc_rate_date,
595 mrc_rate_type,
596 note_to_authorizer,
597 note_to_receiver,
598 note_to_vendor,
599 org_id,
600 pay_on_code,
601 pcard_id,
602 po_header_id,
603 price_update_tolerance,
604 printed_date,
605 print_count,
606 -- Standard WHO column: program_application_id,
607 -- Standard WHO column: program_id,
608 -- Standard WHO column: program_update_date,
609 quotation_class_code,
610 quote_type_lookup_code,
611 quote_vendor_quote_number,
612 quote_warning_delay,
613 quote_warning_delay_unit,
614 rate,
615 rate_date,
616 rate_type,
617 reference_num,
618 reply_date,
619 reply_method_lookup_code,
620 -- Standard WHO column: request_id,
621 revised_date,
622 revision_num,
623 rfq_close_date,
624 segment1,
625 segment2,
626 segment3,
627 segment4,
628 segment5,
629 ship_to_location_id,
630 ship_via_lookup_code,
631 start_date,
632 start_date_active,
633 status_lookup_code,
634 summary_flag,
635 supply_agreement_flag,
636 terms_id,
637 type_lookup_code,
638 user_hold_flag,
639 vendor_contact_id,
640 vendor_id,
641 vendor_order_num,
642 vendor_site_id,
643 wf_item_key,
644 wf_item_type,
645 shipping_control, -- <INBOUND LOGISTICS FPJ>
646 conterms_exist_flag, -- <CONTERMS FPJ>
647 encumbrance_required_flag, -- <ENCUMBRANCE FPJ>
648 document_creation_method -- <DBI FPJ>
649 ,style_id --<R12 STYLES PHASE II >
650 ,tax_attribute_update_code --<R12 eTax Integration>
651 , created_language -- <Unified Catalog R12>
652 , last_updated_program -- <Unified Catalog R12>
653 -- Bug 5295179 START
654 , supplier_notif_method
655 , fax
656 , email_address
657 -- Bug 5295179 END
658 , Enable_All_Sites -- <R12.1 GCPA >
659 , clm_document_number
660 , ame_transaction_type
661 , ame_approval_id
662 , clm_default_dist_flag
663 , umbrella_program_id --Bug 13247667
664 , clm_payment_instr_code -- Payment Instruction & Sunset memo
665 , clm_contract_finance_code
666 ) VALUES (
667 x_po_header_record.acceptance_due_date,
668 x_po_header_record.acceptance_required_flag,
669 x_po_header_record.agent_id,
670 x_po_header_record.amount_limit,
671 x_po_header_record.approval_required_flag,
672 x_po_header_record.approved_date,
673 x_po_header_record.approved_flag,
674 x_po_header_record.attribute1,
675 x_po_header_record.attribute10,
676 x_po_header_record.attribute11,
677 x_po_header_record.attribute12,
678 x_po_header_record.attribute13,
679 x_po_header_record.attribute14,
680 x_po_header_record.attribute15,
681 x_po_header_record.attribute2,
682 x_po_header_record.attribute3,
683 x_po_header_record.attribute4,
684 x_po_header_record.attribute5,
685 x_po_header_record.attribute6,
686 x_po_header_record.attribute7,
687 x_po_header_record.attribute8,
688 x_po_header_record.attribute9,
689 x_po_header_record.attribute_category,
690 x_po_header_record.authorization_status,
691 x_po_header_record.bill_to_location_id,
692 x_po_header_record.blanket_total_amount,
693 x_po_header_record.cancel_flag,
694 x_po_header_record.closed_code,
695 x_po_header_record.closed_date,
696 x_po_header_record.comments,
697 x_po_header_record.confirming_order_flag,
698 x_po_header_record.created_by,
699 x_po_header_record.creation_date,
700 x_po_header_record.currency_code,
701 x_po_header_record.edi_processed_flag,
702 x_po_header_record.edi_processed_status,
703 x_po_header_record.enabled_flag,
704 decode(x_po_header_record.type_lookup_code, 'STANDARD', to_date(NULL),
705 x_po_header_record.end_date), -- Bug #2015328 BUG #5743193
706 x_po_header_record.end_date_active,
707 x_po_header_record.firm_date,
708 x_po_header_record.firm_status_lookup_code,
709 x_po_header_record.fob_lookup_code,
710 x_po_header_record.freight_terms_lookup_code,
711 x_po_header_record.from_header_id,
712 x_po_header_record.from_type_lookup_code,
713 x_po_header_record.frozen_flag,
714 x_po_header_record.global_agreement_flag, -- Global Agreements (FP-I)
715 x_po_header_record.global_attribute1,
716 x_po_header_record.global_attribute10,
717 x_po_header_record.global_attribute11,
718 x_po_header_record.global_attribute12,
719 x_po_header_record.global_attribute13,
720 x_po_header_record.global_attribute14,
721 x_po_header_record.global_attribute15,
722 x_po_header_record.global_attribute16,
723 x_po_header_record.global_attribute17,
724 x_po_header_record.global_attribute18,
725 x_po_header_record.global_attribute19,
726 x_po_header_record.global_attribute2,
727 x_po_header_record.global_attribute20,
728 x_po_header_record.global_attribute3,
729 x_po_header_record.global_attribute4,
730 x_po_header_record.global_attribute5,
731 x_po_header_record.global_attribute6,
732 x_po_header_record.global_attribute7,
733 x_po_header_record.global_attribute8,
734 x_po_header_record.global_attribute9,
735 x_po_header_record.global_attribute_category,
736 x_po_header_record.government_context,
737 x_po_header_record.interface_source_code,
738 x_po_header_record.last_updated_by,
739 x_po_header_record.last_update_date,
740 x_po_header_record.last_update_login,
741 decode(x_po_header_record.global_agreement_flag , 'Y' , null ,
742 x_po_header_record.min_release_amount) ,
743 x_po_header_record.mrc_rate,
744 x_po_header_record.mrc_rate_date,
745 x_po_header_record.mrc_rate_type,
746 x_po_header_record.note_to_authorizer,
747 x_po_header_record.note_to_receiver,
748 x_po_header_record.note_to_vendor,
749 x_po_header_record.org_id,
750 x_po_header_record.pay_on_code, -- <BUG 4766467>
751 x_po_header_record.pcard_id,
752 x_po_header_record.po_header_id,
753 x_po_header_record.price_update_tolerance,
754 x_po_header_record.printed_date,
755 x_po_header_record.print_count,
756 -- Standard WHO column: x_po_header_record.program_application_id,
757 -- Standard WHO column: x_po_header_record.program_id,
758 -- Standard WHO column: x_po_header_record.program_update_date,
759 x_po_header_record.quotation_class_code,
760 x_po_header_record.quote_type_lookup_code,
761 x_po_header_record.quote_vendor_quote_number,
762 x_po_header_record.quote_warning_delay,
763 x_po_header_record.quote_warning_delay_unit,
764 x_po_header_record.rate,
765 x_po_header_record.rate_date,
766 x_po_header_record.rate_type,
767 x_po_header_record.reference_num,
768 x_po_header_record.reply_date,
769 x_po_header_record.reply_method_lookup_code,
770 -- Standard WHO column: x_po_header_record.request_id,
771 x_po_header_record.revised_date,
772 x_po_header_record.revision_num,
773 x_po_header_record.rfq_close_date,
774 x_po_header_record.segment1,
775 x_po_header_record.segment2,
776 x_po_header_record.segment3,
777 x_po_header_record.segment4,
778 x_po_header_record.segment5,
779 x_po_header_record.ship_to_location_id,
780 x_po_header_record.ship_via_lookup_code,
781 decode(x_po_header_record.type_lookup_code, 'STANDARD' , to_date(NULL),
782 x_po_header_record.start_date), -- Bug #2015328 Bug #5743193
783 x_po_header_record.start_date_active,
784 x_po_header_record.status_lookup_code,
785 x_po_header_record.summary_flag,
786 x_po_header_record.supply_agreement_flag,
787 x_po_header_record.terms_id,
788 x_po_header_record.type_lookup_code,
789 x_po_header_record.user_hold_flag,
790 x_po_header_record.vendor_contact_id,
791 x_po_header_record.vendor_id,
792 x_po_header_record.vendor_order_num,
793 x_po_header_record.vendor_site_id,
794 x_po_header_record.wf_item_key,
795 x_po_header_record.wf_item_type,
796 x_po_header_record.shipping_control, -- <INBOUND LOGISTICS FPJ>
797 decode(x_copy_terms, 'N', 'N', x_po_header_record.conterms_exist_flag),
798 -- <CONTERMS FPJ>
799 x_po_header_record.encumbrance_required_flag, -- <ENCUMBRANCE FPJ>
800 -- Bug 3648268 Use lookup code instead of hardcoded value
801 'COPY_DOCUMENT' --<DBI FPJ>
802 ,x_po_header_record.style_id --<R12 STYLES PHASE II >
803 ,g_tax_attribute_update_code --<R12 eTax Integration>
804 , x_po_header_record.created_language -- <Unified Catalog R12>
805 , 'COPY_DOC' -- <Unified Catalog R12>
806 -- Bug 5295179 START
807 , x_po_header_record.supplier_notif_method
808 , x_po_header_record.fax
809 , x_po_header_record.email_address
810 -- Bug 5295179 END
811 , decode(x_po_header_record.type_lookup_code,
812 'CONTRACT',x_po_header_record.Enable_All_Sites,
813 NULL) -- <R12.1 GCPA>
814 , x_po_header_record.segment1
815 , l_ame_transaction_type -- PO AME project copy fetched value from style page
816 , l_ame_approval_id
817 , l_clm_default_dist
818 , x_po_header_record.umbrella_program_id --Bug 13247667
819 , x_po_header_record.clm_payment_instr_code -- Payment Instruction & Sunset memo
820 , x_po_header_record.clm_contract_finance_code
821 );
822
823 l_progress := '030';
824
825 -- Global Agreements (FP-I): If Global Agreement, must also insert
826 -- assignment information for Owning Org into PO_GA_ORG_ASSIGNMENTS.
827 --
828 IF ( x_po_header_record.global_agreement_flag = 'Y' ) THEN
829
830 l_progress := '050';
831
832 --< Shared Proc FPJ Start >
833 -- Refactor code to use GA utility procedures and row handlers
834
835 IF (PO_GA_PVT.is_global_agreement
836 (p_po_header_id => x_po_header_record.from_header_id))
837 THEN
838 l_progress := '060';
839 -- The original document is a global agreement, so blindly copy all
840 -- of its org assignments over for the new GA.
841
842 PO_GA_ORG_ASSIGN_PVT.copy_rows
843 (p_init_msg_list => FND_API.g_false,
844 x_return_status => l_return_status,
845 p_from_po_header_id => x_po_header_record.from_header_id,
846 p_to_po_header_id => x_po_header_record.po_header_id,
847 p_last_update_date => x_po_header_record.last_update_date,
848 p_last_updated_by => x_po_header_record.last_updated_by,
849 p_creation_date => x_po_header_record.creation_date,
850 p_created_by => x_po_header_record.created_by,
851 p_last_update_login => x_po_header_record.last_update_login);
852
853 ELSE
854 l_progress := '070';
855 -- The original document is not a global agreement, so just insert
856 -- one org assignment for the owning org.
857
858 l_org_assign_rec.po_header_id := x_po_header_record.po_header_id;
859 l_org_assign_rec.organization_id := x_po_header_record.org_id;
860 l_org_assign_rec.enabled_flag := 'Y';
861 l_org_assign_rec.vendor_site_id :=
862 x_po_header_record.vendor_site_id;
863 l_org_assign_rec.last_update_date :=
864 x_po_header_record.last_update_date;
865 l_org_assign_rec.last_updated_by :=
866 x_po_header_record.last_updated_by;
867 l_org_assign_rec.creation_date := x_po_header_record.creation_date;
868 l_org_assign_rec.created_by := x_po_header_record.created_by;
869 l_org_assign_rec.last_update_login :=
870 x_po_header_record.last_update_login;
871 l_org_assign_rec.purchasing_org_id := x_po_header_record.org_id;
872
873 PO_GA_ORG_ASSIGN_PVT.insert_row
874 (p_init_msg_list => FND_API.g_false,
875 x_return_status => l_return_status,
876 p_org_assign_rec => l_org_assign_rec,
877 x_row_id => l_org_row_id);
878
879 END IF;
880
881 -- Check the return status of call to row handler
882 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
883 RAISE FND_API.g_exc_error;
884 END IF;
885 --< Shared Proc FPJ End >
886
887 END IF;
888
889 x_return_code := 0;
890
891 EXCEPTION
892 WHEN OTHERS THEN
893 copydoc_sql_error('insert_header', l_progress, sqlcode,
894 x_online_report_id,
895 x_sequence,
896 0, 0, 0);
897 x_return_code := -1;
898 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
899 PO_DEBUG.debug_exc
900 (p_log_head => g_module_prefix||'insert_header',
901 p_progress => l_progress);
902 END IF;
903 END insert_header;
904
905
906 /** create new line record with info from x_po_line_record **/
907 PROCEDURE insert_line(
908 x_po_line_record IN po_lines%ROWTYPE,
909 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
910 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
911 x_return_code OUT NOCOPY NUMBER
912 ) IS
913
914 l_progress VARCHAR2(4);
915
916 -- <SERVICES FPJ START>
917 l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
918 l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE;
919 l_matching_basis PO_LINE_TYPES_B.matching_basis%TYPE;
920 l_category_id PO_LINE_TYPES_B.category_id%TYPE;
921 l_unit_meas_lookup_code PO_LINE_TYPES_B.unit_of_measure%TYPE;
922 l_unit_price PO_LINE_TYPES_B.unit_price%TYPE;
923 l_outside_operation_flag PO_LINE_TYPES_B.outside_operation_flag%TYPE;
924 l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
925 l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
926 -- <SERVICES FPJ END>
927
928 BEGIN
929
930 l_progress := '001';
931
932 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
933 PO_DEBUG.debug_stmt
934 (p_log_head => g_module_prefix||'insert_line',
935 p_token => 'invoked',
936 p_message => 'line ID: '||x_po_line_record.po_line_id||
937 ' online_report ID: '||x_online_report_id);
938 END IF;
939
940 -- <SERVICES FPJ START>
941 -- Retrieve the values for order_type_lookup_code, purchase_basis
942 -- and matching_basis
943 -- CLM CLIN/SLIN Change
944 IF Nvl(x_po_line_record.clm_info_flag,'N') = 'N' then
945 PO_LINE_TYPES_SV.get_line_type_def(
946 x_po_line_record.line_type_id,
947 l_order_type_lookup_code,
948 l_purchase_basis,
949 l_matching_basis,
950 l_category_id,
951 l_unit_meas_lookup_code,
952 l_unit_price,
953 l_outside_operation_flag,
954 l_receiving_flag,
955 l_receive_close_tolerance);
956 END IF;
957 -- <SERVICES FPJ END>
958
959 -- <SERVICES FPJ>
960 -- Added order_type_lookup_code, purchase_basis and matching_basis
961 -- for PO Line level denormalization
962 INSERT INTO PO_LINES (
963 allow_price_override_flag,
964 attribute1,
965 attribute10,
966 attribute11,
967 attribute12,
968 attribute13,
969 attribute14,
970 attribute15,
971 attribute2,
972 attribute3,
973 attribute4,
974 attribute5,
975 attribute6,
976 attribute7,
977 attribute8,
978 attribute9,
979 attribute_category,
980 base_qty,
981 base_uom,
982 cancelled_by,
983 cancel_date,
984 cancel_flag,
985 cancel_reason,
986 capital_expense_flag,
987 category_id,
988 closed_by,
989 closed_code,
990 closed_date,
991 closed_flag,
992 closed_reason,
993 committed_amount,
994 -- contract_num, -- <GC FPJ>
995 contract_id, -- <GC FPJ>
996 created_by,
997 creation_date,
998 expiration_date,
999 firm_date,
1000 firm_status_lookup_code,
1001 from_header_id,
1002 from_line_id,
1003 from_line_location_id, -- <SERVICES FPJ>
1004 global_attribute1,
1005 global_attribute10,
1006 global_attribute11,
1007 global_attribute12,
1008 global_attribute13,
1009 global_attribute14,
1010 global_attribute15,
1011 global_attribute16,
1012 global_attribute17,
1013 global_attribute18,
1014 global_attribute19,
1015 global_attribute2,
1016 global_attribute20,
1017 global_attribute3,
1018 global_attribute4,
1019 global_attribute5,
1020 global_attribute6,
1021 global_attribute7,
1022 global_attribute8,
1023 global_attribute9,
1024 global_attribute_category,
1025 government_context,
1026 hazard_class_id,
1027 item_description,
1028 item_id,
1029 item_revision,
1030 last_updated_by,
1031 last_update_date,
1032 last_update_login,
1033 line_num,
1034 line_reference_num,
1035 line_type_id,
1036 list_price_per_unit,
1037 market_price,
1038 max_order_quantity,
1039 min_order_quantity,
1040 min_release_amount,
1041 negotiated_by_preparer_flag,
1042 note_to_vendor,
1043 not_to_exceed_price,
1044 org_id,
1045 over_tolerance_error_flag,
1046 po_header_id,
1047 po_line_id,
1048 price_break_lookup_code,
1049 price_type_lookup_code,
1050 project_id,
1051 qc_grade,
1052 qty_rcv_tolerance,
1053 quantity,
1054 quantity_committed,
1055 reference_num,
1056 secondary_qty,
1057 secondary_uom,
1058 task_id,
1059 taxable_flag,
1060 tax_code_id,
1061 transaction_reason_code,
1062 type_1099,
1063 unit_meas_lookup_code,
1064 unit_price,
1065 base_unit_price, -- <FPJ Advanced Price>
1066 unordered_flag,
1067 un_number_id,
1068 user_hold_flag,
1069 vendor_product_num,
1070 oke_contract_header_id, --added oke columns
1071 oke_contract_version_id, --added oke columns
1072 secondary_unit_of_measure, -- 1548597
1073 secondary_quantity, -- 1548597
1074 preferred_grade, -- 1548597
1075 job_id, -- Services FPJ
1076 amount, -- Services FPJ
1077 start_date, -- Services FPJ
1078 contractor_first_name, -- Services FPJ
1079 contractor_last_name, -- Services FPJ
1080 order_type_lookup_code, -- <SERVICES FPJ>
1081 purchase_basis, -- <SERVICES FPJ>
1082 matching_basis, -- <SERVICES FPJ>
1083 manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1084 ,tax_attribute_update_code --<R12 eTax Integration>
1085 , retainage_rate -- <Complex Work R12>
1086 , max_retainage_amount -- <Complex Work R12>
1087 , progress_payment_rate -- <Complex Work R12>
1088 , recoupment_rate -- <Complex Work R12>
1089 , catalog_name -- <Unified Catalog R12>
1090 , supplier_part_auxid -- <Unified Catalog R12>
1091 , ip_category_id -- <Unified Catalog R12>
1092 , last_updated_program -- <Unified Catalog R12>
1093 , GROUP_LINE_ID,
1094 LINE_NUM_DISPLAY,
1095 CLM_INFO_FLAG,
1096 CLM_OPTION_INDICATOR,
1097 CLM_BASE_LINE_NUM,
1098 CLM_OPTION_NUM,
1099 CLM_OPTION_FROM_DATE,
1100 CLM_OPTION_TO_DATE,
1101 CLM_FUNDED_FLAG,
1102 CONTRACT_TYPE,
1103 COST_CONSTRAINT,
1104 CLM_IDC_TYPE,
1105 CLM_EXERCISED_FLAG,
1106 CLM_EXERCISED_DATE,
1107 clm_delivery_event_code, --<Event Based Delivery>
1108 clm_pop_exception_reason, --<Sunset Memo POP Validation>
1109 clm_payment_instr_code, -- Payment Instruction
1110 clm_uda_pricing_total, -- UCA Project CLM R4 Changes
1111 clm_exhibit_name -- Elins project CLM R4 Changes
1112 ) VALUES (
1113 x_po_line_record.allow_price_override_flag,
1114 x_po_line_record.attribute1,
1115 x_po_line_record.attribute10,
1116 x_po_line_record.attribute11,
1117 x_po_line_record.attribute12,
1118 x_po_line_record.attribute13,
1119 x_po_line_record.attribute14,
1120 x_po_line_record.attribute15,
1121 x_po_line_record.attribute2,
1122 x_po_line_record.attribute3,
1123 x_po_line_record.attribute4,
1124 x_po_line_record.attribute5,
1125 x_po_line_record.attribute6,
1126 x_po_line_record.attribute7,
1127 x_po_line_record.attribute8,
1128 x_po_line_record.attribute9,
1129 x_po_line_record.attribute_category,
1130 x_po_line_record.base_qty,
1131 x_po_line_record.base_uom,
1132 x_po_line_record.cancelled_by,
1133 x_po_line_record.cancel_date,
1134 x_po_line_record.cancel_flag,
1135 x_po_line_record.cancel_reason,
1136 x_po_line_record.capital_expense_flag,
1137 x_po_line_record.category_id,
1138 x_po_line_record.closed_by,
1139 x_po_line_record.closed_code,
1140 x_po_line_record.closed_date,
1141 x_po_line_record.closed_flag,
1142 x_po_line_record.closed_reason,
1143 x_po_line_record.committed_amount,
1144 -- x_po_line_record.contract_num, -- <GC FPJ>
1145 x_po_line_record.contract_id, -- <GC FPJ>
1146 x_po_line_record.created_by,
1147 x_po_line_record.creation_date,
1148 x_po_line_record.expiration_date,
1149 x_po_line_record.firm_date,
1150 x_po_line_record.firm_status_lookup_code,
1151 x_po_line_record.from_header_id,
1152 x_po_line_record.from_line_id,
1153 x_po_line_record.from_line_location_id, -- <SERVICES FPJ>
1154 x_po_line_record.global_attribute1,
1155 x_po_line_record.global_attribute10,
1156 x_po_line_record.global_attribute11,
1157 x_po_line_record.global_attribute12,
1158 x_po_line_record.global_attribute13,
1159 x_po_line_record.global_attribute14,
1160 x_po_line_record.global_attribute15,
1161 x_po_line_record.global_attribute16,
1162 x_po_line_record.global_attribute17,
1163 x_po_line_record.global_attribute18,
1164 x_po_line_record.global_attribute19,
1165 x_po_line_record.global_attribute2,
1166 x_po_line_record.global_attribute20,
1167 x_po_line_record.global_attribute3,
1168 x_po_line_record.global_attribute4,
1169 x_po_line_record.global_attribute5,
1170 x_po_line_record.global_attribute6,
1171 x_po_line_record.global_attribute7,
1172 x_po_line_record.global_attribute8,
1173 x_po_line_record.global_attribute9,
1174 x_po_line_record.global_attribute_category,
1175 x_po_line_record.government_context,
1176 x_po_line_record.hazard_class_id,
1177 x_po_line_record.item_description,
1178 x_po_line_record.item_id,
1179 x_po_line_record.item_revision,
1180 x_po_line_record.last_updated_by,
1181 x_po_line_record.last_update_date,
1182 x_po_line_record.last_update_login,
1183 x_po_line_record.line_num,
1184 x_po_line_record.line_reference_num,
1185 x_po_line_record.line_type_id,
1186 x_po_line_record.list_price_per_unit,
1187 x_po_line_record.market_price,
1188 x_po_line_record.max_order_quantity,
1189 x_po_line_record.min_order_quantity,
1190 x_po_line_record.min_release_amount,
1191 x_po_line_record.negotiated_by_preparer_flag,
1192 x_po_line_record.note_to_vendor,
1193 x_po_line_record.not_to_exceed_price,
1194 x_po_line_record.org_id,
1195 x_po_line_record.over_tolerance_error_flag,
1196 x_po_line_record.po_header_id,
1197 x_po_line_record.po_line_id,
1198 x_po_line_record.price_break_lookup_code,
1199 x_po_line_record.price_type_lookup_code,
1200 x_po_line_record.project_id,
1201 x_po_line_record.qc_grade,
1202 x_po_line_record.qty_rcv_tolerance,
1203 x_po_line_record.quantity,
1204 x_po_line_record.quantity_committed,
1205 x_po_line_record.reference_num,
1206 x_po_line_record.secondary_qty,
1207 x_po_line_record.secondary_uom,
1208 x_po_line_record.task_id,
1209 x_po_line_record.taxable_flag,
1210 x_po_line_record.tax_code_id,
1211 x_po_line_record.transaction_reason_code,
1212 x_po_line_record.type_1099,
1213 x_po_line_record.unit_meas_lookup_code,
1214 x_po_line_record.unit_price,
1215 x_po_line_record.base_unit_price, -- <FPJ Advanced Price>
1216 x_po_line_record.unordered_flag,
1217 x_po_line_record.un_number_id,
1218 x_po_line_record.user_hold_flag,
1219 x_po_line_record.vendor_product_num,
1220 x_po_line_record.oke_contract_header_id, -- added oke columns
1221 x_po_line_record.oke_contract_version_id, -- added oke columns
1222 x_po_line_record.secondary_unit_of_measure, -- 1548597
1223 x_po_line_record.secondary_quantity, -- 1548597
1224 x_po_line_record.preferred_grade, -- 1548597
1225 x_po_line_record.job_id, -- Services FPJ
1226 x_po_line_record.amount, -- Services FPJ
1227 x_po_line_record.start_date, -- Services FPJ
1228 x_po_line_record.contractor_first_name, -- Services FPJ
1229 x_po_line_record.contractor_last_name, -- Services FPJ
1230 l_order_type_lookup_code, -- <SERVICES FPJ>
1231 l_purchase_basis, -- <SERVICES FPJ>
1232 l_matching_basis, -- <SERVICES FPJ>
1233 x_po_line_record.manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1234 ,g_tax_attribute_update_code --<R12 eTax Integration>
1235 , x_po_line_record.retainage_rate -- <Complex Work R12>
1236 , x_po_line_record.max_retainage_amount -- <Complex Work R12>
1237 , x_po_line_record.progress_payment_rate -- <Complex Work R12>
1238 , x_po_line_record.recoupment_rate -- <Complex Work R12>
1239 , x_po_line_record.catalog_name -- <Unified Catalog R12>
1240 , x_po_line_record.supplier_part_auxid -- <Unified Catalog R12>
1241 , x_po_line_record.ip_category_id -- <Unified Catalog R12>
1242 , 'COPY_DOC' -- <Unified Catalog R12>
1243 , x_po_line_record.group_line_id,
1244 x_po_line_record.line_num_display,
1245 x_po_line_record.clm_info_flag,
1246 x_po_line_record.clm_option_indicator,
1247 x_po_line_record.clm_base_line_num,
1248 x_po_line_record.clm_option_num,
1249 x_po_line_record.clm_option_from_date,
1250 x_po_line_record.clm_option_to_date,
1251 x_po_line_record.clm_funded_flag,
1252 x_po_line_record.contract_type,
1253 x_po_line_record.cost_constraint,
1254 x_po_line_record.clm_idc_type,
1255 x_po_line_record.clm_exercised_flag,
1256 x_po_line_record.clm_exercised_date,
1257 x_po_line_record.clm_delivery_event_code, --<Event Based Delivery>
1258 x_po_line_record.clm_pop_exception_reason, --<Sunset Memo POP Validation>
1259 x_po_line_record.clm_payment_instr_code, -- Payment Instruction
1260 x_po_line_record.clm_uda_pricing_total, -- UCA Project CLM R4 Changes
1261 x_po_line_record.clm_exhibit_name -- Elins project CLM R4 changes
1262 );
1263
1264
1265 x_return_code := 0;
1266
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 copydoc_sql_error('insert_line', l_progress, sqlcode,
1270 x_online_report_id,
1271 x_sequence,
1272 x_po_line_record.line_num, 0, 0);
1273 x_return_code := -1;
1274 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
1275 PO_DEBUG.debug_exc
1276 (p_log_head => g_module_prefix||'insert_line',
1277 p_progress => l_progress);
1278 END IF;
1279 END insert_line;
1280
1281
1282 PROCEDURE insert_shipment(
1283 x_po_shipment_record IN po_line_locations%ROWTYPE,
1284 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1285 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1286 x_line_num IN po_online_report_text.line_num%TYPE,
1287 x_accrue_on_receipt_flag IN VARCHAR2,
1288 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
1289 x_return_code OUT NOCOPY NUMBER,
1290 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
1291 p_orig_line_location_id IN NUMBER -- <eTax Integration R12>
1292 ) IS
1293
1294 l_progress VARCHAR2(4);
1295
1296 --bug #2717053: changed var names/types to conform to standards
1297 l_vendor_site_id po_headers.vendor_site_id%TYPE := null;
1298 l_to_ship_to_location_id po_line_locations.ship_to_location_id%TYPE;
1299 l_to_ship_to_organization_id po_line_locations.ship_to_organization_id%TYPE;
1300 l_item_id po_lines.item_id%TYPE := null;
1301 l_count number:= 0;
1302 l_quantity po_line_locations.quantity%TYPE;
1303 l_quote_type_code po_headers.quote_type_lookup_code%TYPE := null;
1304
1305 x_sob_id number:= null;
1306
1307 -- CONSIGNED FPI START
1308 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1309 l_vendor_id
1310 po_headers.vendor_id%TYPE := null;
1311 l_consigned_flag
1312 po_line_locations.consigned_flag%TYPE := null;
1313 l_consigned_from_supplier_flag
1314 po_asl_attributes.consigned_from_supplier_flag%TYPE := null;
1315 l_enable_vmi_flag
1316 po_asl_attributes.enable_vmi_flag%TYPE := null;
1317 l_accrue_on_receipt_flag
1318 po_line_locations.accrue_on_receipt_flag%TYPE := null;
1319 l_closed_code
1320 po_line_locations.closed_code%TYPE := null;
1321 l_closed_reason
1322 po_line_locations.closed_reason%TYPE := null;
1323 l_inspection_required_flag
1324 po_line_locations.inspection_required_flag%TYPE := null;
1325 l_receipt_required_flag
1326 po_line_locations.receipt_required_flag%TYPE := null;
1327 l_match_option
1328 po_line_locations.match_option%TYPE := null;
1329 l_last_billing_date date := null;
1330 l_consigned_billing_cycle number := null;
1331 l_invoice_close_tolerance number := null;
1332 l_item_inv_asset_flag
1333 mtl_system_items_b.inventory_asset_flag%TYPE := NULL;
1334 l_return_status varchar2(1) := NULL;
1335 l_msg_count number := NULL;
1336 l_msg_data varchar2(2000) := NULL;
1337 -- CONSIGNED FPI END
1338
1339 --<INVCONV R12 START>
1340 l_unit_meas_lookup_code MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE := NULL;
1341 x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE := NULL;
1342 x_secondary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE := NULL;
1343 x_secondary_quantity PO_LINES_ALL.SECONDARY_QUANTITY%TYPE := NULL;
1344 --<INVCONV R12 END>
1345
1346 --<Complex Work R12 START> Bug 4958300
1347 l_line_value_basis PO_LINE_LOCATIONS_ALL.value_basis%type;
1348 l_line_matching_basis PO_LINE_LOCATIONS_ALL.matching_basis%type;
1349 --<Complex Work R12 END>
1350
1351 l_clm_delivery_event_code po_lines_all.clm_delivery_event_code%type;
1352
1353 BEGIN
1354
1355 l_progress := '000';
1356
1357 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
1358 PO_DEBUG.debug_stmt
1359 (p_log_head => g_module_prefix||'insert_shipment',
1360 p_token => 'invoked',
1361 p_message => 'ship ID: '||x_po_shipment_record.line_location_id||
1362 ' inv_org ID: '||x_inv_org_id||' accrue_on_receipt_flag: '||
1363 x_accrue_on_receipt_flag||' online_report ID: '||
1364 x_online_report_id);
1365 END IF;
1366
1367 --bug #2717053: reorganized the defaulting logic for RFQs/Quotations
1368 --In FPI Timephased, we changed the structure of BPA price breaks so
1369 --that org, loc and qty could all be null. Now, when copying these
1370 --price breaks, we have to make sure we don't copy in org/loc or qty
1371 --values that couldn't be entered that way on the forms themselves.
1372
1373 l_to_ship_to_organization_id := x_po_shipment_record.ship_to_organization_id;
1374 l_to_ship_to_location_id := x_po_shipment_record.ship_to_location_id;
1375 l_quantity := x_po_shipment_record.quantity;
1376
1377 l_progress := '010';
1378
1379 --<INVCONV R12>:added UOM to select below
1380 --<Complex Work R12> bug 4958300: added value basis/matching basis
1381
1382 -- <Event Based Delivery>
1383 -- Take clm_delivery_event_code from po_lines
1384 -- Update Need By Date and Promised Date only if delivery event is not used.
1385
1386 select item_id, unit_meas_lookup_code,
1387 order_type_lookup_code, matching_basis,
1388 clm_delivery_event_code
1389 into l_item_id, l_unit_meas_lookup_code,
1390 l_line_value_basis, l_line_matching_basis,
1391 l_clm_delivery_event_code
1392 from po_lines_all
1393 where po_line_id=x_po_shipment_record.po_line_id;
1394
1395 -- CONSIGNED FPI START
1396 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1397 -- initialize local variables for shipment attributes with values
1398 -- passed in from the IN parameters
1399 l_accrue_on_receipt_flag := x_accrue_on_receipt_flag;
1400 l_invoice_close_tolerance := x_po_shipment_record.invoice_close_tolerance;
1401 l_closed_code := x_po_shipment_record.closed_code;
1402 l_closed_reason := x_po_shipment_record.closed_reason;
1403 l_inspection_required_flag := x_po_shipment_record.inspection_required_flag;
1404 l_receipt_required_flag := x_po_shipment_record.receipt_required_flag;
1405 l_match_option := x_po_shipment_record.match_option;
1406 -- CONSIGNED FPI END
1407
1408 IF (x_po_shipment_record.shipment_type IN ('QUOTATION', 'RFQ')) THEN
1409
1410 l_progress := '020';
1411
1412 Begin
1413 SELECT ph.quote_type_lookup_code
1414 INTO l_quote_type_code
1415 FROM po_headers ph
1416 WHERE ph.po_header_id = x_po_shipment_record.po_header_id;
1417 Exception
1418 When others then
1419 l_quote_type_code := NULL;
1420 End;
1421
1422 IF (l_quote_type_code IN ('CATALOG', 'STANDARD')) THEN
1423 --note: we don't do any defaulting for Bid RFQs/Quotes.
1424 --since we can not copy a document INTO a Bid RFQ, we
1425 --dont' have to worry about consistency with Forms entry
1426
1427 IF ((l_to_ship_to_organization_id IS NULL) AND
1428 (l_to_ship_to_location_id IS NOT NULL)) THEN
1429
1430 l_progress := '030';
1431
1432 -- try to infer org if org is null, but loc is not.
1433 -- (you can not specify a loc w/o an org in the forms)
1434 -- first check against the hr_locations table
1435 begin
1436 select inventory_organization_id
1437 into l_to_ship_to_organization_id
1438 from hr_locations_v
1439 where location_id = l_to_ship_to_location_id
1440 and ship_to_site_flag = 'Y';
1441 exception
1442 when no_data_found then
1443 l_to_ship_to_organization_id := NULL;
1444 end;
1445
1446 l_progress := '040';
1447
1448 -- check the org value from hr_locations to ensure
1449 -- that the item is defined in this org
1450 if (l_to_ship_to_organization_id is NOT NULL) then
1451 select count(*) into l_count from
1452 mtl_system_items where
1453 inventory_item_id=l_item_id and
1454 organization_id=l_to_ship_to_organization_id ;
1455 if (l_count=0) then
1456 l_to_ship_to_organization_id:=NULL;
1457 end if;
1458 end if;
1459
1460 l_progress := '050';
1461
1462 -- if there was a null org from hr tables, or if
1463 -- the item was not valid, use the inv_org from
1464 -- financials_system_parameters (x_inv_org_id)
1465 -- if the item is not defined for the FSP org, then
1466 -- leave the org null.
1467 if (l_to_ship_to_organization_id is NULL) then
1468 select count(*) into l_count from
1469 mtl_system_items where
1470 inventory_item_id=l_item_id and
1471 organization_id=x_inv_org_id;
1472 if (l_count <> 0) then
1473 l_to_ship_to_organization_id := x_inv_org_id;
1474 end if;
1475 end if;
1476
1477 END IF; --end of org null/loc not null check
1478
1479 IF (l_quantity IS NULL) THEN
1480 --pb qty can not be NULL in RFQ/Quote form: use zero instead
1481 l_quantity := 0;
1482 END IF; --end of qty check
1483
1484 END IF; --end of l_quote_type_code check
1485
1486 -- <Complex Work R12>: Also skip complex work POs.
1487 ELSIF ((NOT p_is_complex_work_po) AND
1488 (x_po_shipment_record.shipment_type <> 'PRICE BREAK')) THEN
1489 -- shipment is not a BPA, Quote or RFQ price break
1490
1491 l_progress := '060';
1492
1493 IF (l_to_ship_to_organization_id IS NULL) THEN
1494 l_progress := '070';
1495
1496 begin
1497 select vendor_site_id into l_vendor_site_id from
1498 po_headers where
1499 po_header_id=x_po_shipment_record.po_header_id;
1500
1501 select ship_to_location_id into l_to_ship_to_location_id
1502 from po_vendor_sites where
1503 vendor_site_id=l_vendor_site_id;
1504
1505 if (l_to_ship_to_location_id is null) then
1506 select fsp.ship_to_location_id
1507 into l_to_ship_to_location_id
1508 from financials_system_parameters fsp;
1509 end if;
1510 SELECT inventory_organization_id
1511 INTO l_to_ship_to_organization_id
1512 FROM hr_locations_v
1513 WHERE location_id = l_to_ship_to_location_id
1514 AND ship_to_site_flag = 'Y';
1515 exception
1516 when no_data_found then
1517 l_to_ship_to_organization_id:=null;
1518 end;
1519
1520 l_progress := '080';
1521
1522 select count(*) into l_count from
1523 mtl_system_items where
1524 inventory_item_id=l_item_id and
1525 organization_id=l_to_ship_to_organization_id ;
1526
1527 if (l_count=0) then
1528 l_to_ship_to_organization_id:=null;
1529 end if;
1530
1531 if l_to_ship_to_organization_id is null then
1532 return;
1533 end if;
1534
1535 END IF; --end check if org_id is NULL
1536
1537 l_progress := '090';
1538
1539 -- CONSIGNED FPI START
1540 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1541 IF (x_po_shipment_record.shipment_type = 'STANDARD') THEN
1542
1543 IF(l_to_ship_to_organization_id IS NOT NULL AND
1544 l_item_id IS NOT NULL)
1545 THEN
1546 l_progress := '100';
1547
1548 PO_THIRD_PARTY_STOCK_GRP.Get_Item_Inv_Asset_Flag
1549 (p_api_version => 1.0 ,
1550 p_init_msg_list => NULL ,
1551 x_return_status => l_return_status ,
1552 x_msg_count => l_msg_count ,
1553 x_msg_data => l_msg_data ,
1554 p_organization_id => l_to_ship_to_organization_id,
1555 p_inventory_item_id => l_item_id ,
1556 x_inventory_asset_flag => l_item_inv_asset_flag );
1557 END IF;
1558
1559 IF(l_item_inv_asset_flag = 'Y')
1560 THEN
1561 l_progress := '110';
1562
1563 select vendor_id, vendor_site_id
1564 into l_vendor_id, l_vendor_site_id
1565 from po_headers
1566 where po_header_id = x_po_shipment_record.po_header_id;
1567
1568 l_progress := '120';
1569
1570 PO_THIRD_PARTY_STOCK_GRP.get_asl_attributes
1571 (p_api_version => 1.0 ,
1572 p_init_msg_list => NULL ,
1573 x_return_status => l_return_status ,
1574 x_msg_count => l_msg_count ,
1575 x_msg_data => l_msg_data ,
1576 p_inventory_item_id => l_item_id ,
1577 p_vendor_id => l_vendor_id ,
1578 p_vendor_site_id => l_vendor_site_id ,
1579 p_using_organization_id => l_to_ship_to_organization_id ,
1580 x_consigned_from_supplier_flag => l_consigned_from_supplier_flag,
1581 x_enable_vmi_flag => l_enable_vmi_flag ,
1582 x_last_billing_date => l_last_billing_date ,
1583 x_consigned_billing_cycle => l_consigned_billing_cycle );
1584
1585 IF(l_consigned_from_supplier_flag = 'Y')
1586 THEN
1587 l_consigned_flag := 'Y';
1588 l_accrue_on_receipt_flag := 'N';
1589 l_invoice_close_tolerance := 100;
1590 l_closed_code := 'CLOSED FOR INVOICE';
1591 FND_MESSAGE.SET_NAME('PO', 'PO_SUP_CONS_CLOSED_REASON');
1592 l_closed_reason := FND_MESSAGE.GET;
1593 l_inspection_required_flag := 'N';
1594 l_receipt_required_flag := 'N';
1595 l_match_option := 'P';
1596 END IF;
1597 END IF;
1598 END IF; --end if shipment_type is STANDARD
1599 -- CONSIGNED FPI END
1600
1601 END IF; --end check of shipment_type
1602 --bug #2717053: end of reorganized defaulting logic for RFQs/Quotations
1603
1604 -- Bug 2761415 In 115.35, moved the receipt/invoice close tolerance
1605 -- defaulting logic (Bug 2473335) to PO_COPYDOC_S4.validate_shipment,
1606 -- since it should only apply when copying from RFQ's and quotations.
1607
1608 l_progress := '130';
1609
1610 --<INVCONV R12 START>
1611 -- if item is dual uom control , derive shipment secondary quantity and
1612 -- secondary uom if it is null
1613 x_secondary_unit_of_measure := x_po_shipment_record.secondary_unit_of_measure ;
1614 x_secondary_quantity := x_po_shipment_record.secondary_quantity ;
1615
1616 -- <Complex Work R12>: Default null for secondary fields on complex work po
1617
1618 IF ((NOT p_is_complex_work_po) AND
1619 (x_po_shipment_record.shipment_type IN ('STANDARD','PLANNED'))) THEN
1620 IF x_secondary_quantity IS NULL and l_item_id IS NOT NULL THEN
1621
1622 po_uom_s.get_secondary_uom(l_item_id,
1623 l_to_ship_to_organization_id,
1624 x_secondary_uom_code,
1625 x_secondary_unit_of_measure);
1626
1627 IF x_secondary_unit_of_measure IS NOT NULL THEN
1628 PO_UOM_S.uom_convert ( l_quantity,l_unit_meas_lookup_code,l_item_id,
1629 x_secondary_unit_of_measure ,x_secondary_quantity) ;
1630 ELSE
1631 x_secondary_quantity := null ;
1632 x_secondary_unit_of_measure := null ;
1633 END IF;
1634 END IF;
1635 ELSE
1636 x_secondary_quantity := null ;
1637 x_secondary_unit_of_measure := null ;
1638 END IF;
1639
1640 --<INVCONV R12 END>
1641
1642 INSERT INTO PO_LINE_LOCATIONS (
1643 accrue_on_receipt_flag,
1644 allow_substitute_receipts_flag,
1645 approved_date,
1646 approved_flag,
1647 attribute1,
1648 attribute10,
1649 attribute11,
1650 attribute12,
1651 attribute13,
1652 attribute14,
1653 attribute15,
1654 attribute2,
1655 attribute3,
1656 attribute4,
1657 attribute5,
1658 attribute6,
1659 attribute7,
1660 attribute8,
1661 attribute9,
1662 attribute_category,
1663 calculate_tax_flag,
1664 cancelled_by,
1665 cancel_date,
1666 cancel_flag,
1667 cancel_reason,
1668 closed_by,
1669 closed_code,
1670 closed_date,
1671 closed_flag,
1672 closed_reason,
1673 country_of_origin_code,
1674 created_by,
1675 creation_date,
1676 days_early_receipt_allowed,
1677 days_late_receipt_allowed,
1678 encumbered_date,
1679 encumbered_flag,
1680 encumber_now,
1681 end_date,
1682 enforce_ship_to_location_code,
1683 estimated_tax_amount,
1684 firm_date,
1685 firm_status_lookup_code,
1686 fob_lookup_code,
1687 freight_terms_lookup_code,
1688 from_header_id,
1689 from_line_id,
1690 from_line_location_id,
1691 global_attribute1,
1692 global_attribute10,
1693 global_attribute11,
1694 global_attribute12,
1695 global_attribute13,
1696 global_attribute14,
1697 global_attribute15,
1698 global_attribute16,
1699 global_attribute17,
1700 global_attribute18,
1701 global_attribute19,
1702 global_attribute2,
1703 global_attribute20,
1704 global_attribute3,
1705 global_attribute4,
1706 global_attribute5,
1707 global_attribute6,
1708 global_attribute7,
1709 global_attribute8,
1710 global_attribute9,
1711 global_attribute_category,
1712 government_context,
1713 inspection_required_flag,
1714 invoice_close_tolerance,
1715 last_accept_date,
1716 last_updated_by,
1717 last_update_date,
1718 last_update_login,
1719 lead_time,
1720 lead_time_unit,
1721 line_location_id,
1722 match_option,
1723 need_by_date,
1724 org_id,
1725 po_header_id,
1726 po_line_id,
1727 po_release_id,
1728 price_discount,
1729 price_override,
1730 -- Standard WHO column: program_application_id
1731 -- Standard WHO column: program_id
1732 -- Standard WHO column: program_update_date
1733 promised_date,
1734 qty_rcv_exception_code,
1735 qty_rcv_tolerance,
1736 quantity,
1737 quantity_accepted,
1738 quantity_billed,
1739 quantity_cancelled,
1740 quantity_received,
1741 quantity_rejected,
1742 quantity_shipped,
1743 receipt_days_exception_code,
1744 receipt_required_flag,
1745 receive_close_tolerance,
1746 receiving_routing_id,
1747 -- Standard WHO column: request_id
1748 shipment_num,
1749 shipment_type,
1750 ship_to_location_id,
1751 ship_to_organization_id,
1752 ship_via_lookup_code,
1753 source_shipment_id,
1754 start_date,
1755 taxable_flag,
1756 tax_code_id,
1757 tax_name, --<R12 eTax Integration>
1758 tax_user_override_flag,
1759 terms_id,
1760 -- unencumbered_quantity, -- <Removed Encumbrance FPJ>
1761 unit_meas_lookup_code,
1762 unit_of_measure_class,
1763 --togeorge 10/05/2000
1764 --added note to receiver
1765 note_to_receiver,
1766 -- start of 1548597
1767 secondary_unit_of_measure,
1768 secondary_quantity,
1769 preferred_grade,
1770 secondary_quantity_received,
1771 secondary_quantity_accepted,
1772 secondary_quantity_rejected,
1773 secondary_quantity_cancelled,
1774 -- end of 1548597
1775 consigned_flag, -- CONSIGNED FPI
1776 amount, -- Services FPJ
1777 amount_accepted, -- Services FPJ
1778 amount_billed, -- Services FPJ
1779 amount_cancelled, -- Services FPJ
1780 amount_received, -- Services FPJ
1781 amount_rejected, -- Services FPJ
1782 transaction_flow_header_id, --< Shared Proc FPJ >
1783 manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1784 --<DBI Req Fulfillment 11.5.11 Start >
1785 , shipment_closed_date
1786 , closed_for_receiving_date
1787 , closed_for_invoice_date
1788 --<DBI Req Fulfillment 11.5.11 End >
1789 ,outsourced_assembly --<SHIKYU R12>
1790 ,tax_attribute_update_code --<R12 eTax Integration>
1791 , value_basis -- <Complex Work R12>
1792 , matching_basis -- <Complex Work R12>
1793 , payment_type -- <Complex Work R12>
1794 , description -- <Complex Work R12>
1795 , work_approver_id -- <Complex Work R12>
1796 , original_shipment_id --<eTax Integration R12>
1797 , clm_delivery_period --<Event Based Delivery>
1798 , clm_delivery_period_uom
1799 , clm_promise_period
1800 , clm_promise_period_uom
1801 , clm_pop_duration
1802 , clm_pop_duration_uom
1803
1804 ) VALUES (
1805 l_accrue_on_receipt_flag, -- CONSIGNED FPI
1806 -- x_accrue_on_receipt_flag, -- Bug: 1402128
1807 -- x_po_shipment_record.accrue_on_receipt_flag,
1808 x_po_shipment_record.allow_substitute_receipts_flag,
1809 x_po_shipment_record.approved_date,
1810 x_po_shipment_record.approved_flag,
1811 x_po_shipment_record.attribute1,
1812 x_po_shipment_record.attribute10,
1813 x_po_shipment_record.attribute11,
1814 x_po_shipment_record.attribute12,
1815 x_po_shipment_record.attribute13,
1816 x_po_shipment_record.attribute14,
1817 x_po_shipment_record.attribute15,
1818 x_po_shipment_record.attribute2,
1819 x_po_shipment_record.attribute3,
1820 x_po_shipment_record.attribute4,
1821 x_po_shipment_record.attribute5,
1822 x_po_shipment_record.attribute6,
1823 x_po_shipment_record.attribute7,
1824 x_po_shipment_record.attribute8,
1825 x_po_shipment_record.attribute9,
1826 x_po_shipment_record.attribute_category,
1827 x_po_shipment_record.calculate_tax_flag,
1828 x_po_shipment_record.cancelled_by,
1829 x_po_shipment_record.cancel_date,
1830 x_po_shipment_record.cancel_flag,
1831 x_po_shipment_record.cancel_reason,
1832 x_po_shipment_record.closed_by,
1833 --x_po_shipment_record.closed_code,
1834 l_closed_code, -- CONSIGNED FPI
1835 x_po_shipment_record.closed_date,
1836 x_po_shipment_record.closed_flag,
1837 --x_po_shipment_record.closed_reason,
1838 l_closed_reason, -- CONSIGNED FPI
1839 x_po_shipment_record.country_of_origin_code,
1840 x_po_shipment_record.created_by,
1841 x_po_shipment_record.creation_date,
1842 x_po_shipment_record.days_early_receipt_allowed,
1843 x_po_shipment_record.days_late_receipt_allowed,
1844 x_po_shipment_record.encumbered_date,
1845 x_po_shipment_record.encumbered_flag,
1846 x_po_shipment_record.encumber_now,
1847 x_po_shipment_record.end_date,
1848 x_po_shipment_record.enforce_ship_to_location_code,
1849 x_po_shipment_record.estimated_tax_amount,
1850 x_po_shipment_record.firm_date,
1851 x_po_shipment_record.firm_status_lookup_code,
1852 x_po_shipment_record.fob_lookup_code,
1853 x_po_shipment_record.freight_terms_lookup_code,
1854 x_po_shipment_record.from_header_id,
1855 x_po_shipment_record.from_line_id,
1856 x_po_shipment_record.from_line_location_id,
1857 x_po_shipment_record.global_attribute1,
1858 x_po_shipment_record.global_attribute10,
1859 x_po_shipment_record.global_attribute11,
1860 x_po_shipment_record.global_attribute12,
1861 x_po_shipment_record.global_attribute13,
1862 x_po_shipment_record.global_attribute14,
1863 x_po_shipment_record.global_attribute15,
1864 x_po_shipment_record.global_attribute16,
1865 x_po_shipment_record.global_attribute17,
1866 x_po_shipment_record.global_attribute18,
1867 x_po_shipment_record.global_attribute19,
1868 x_po_shipment_record.global_attribute2,
1869 x_po_shipment_record.global_attribute20,
1870 x_po_shipment_record.global_attribute3,
1871 x_po_shipment_record.global_attribute4,
1872 x_po_shipment_record.global_attribute5,
1873 x_po_shipment_record.global_attribute6,
1874 x_po_shipment_record.global_attribute7,
1875 x_po_shipment_record.global_attribute8,
1876 x_po_shipment_record.global_attribute9,
1877 x_po_shipment_record.global_attribute_category,
1878 x_po_shipment_record.government_context,
1879 --x_po_shipment_record.inspection_required_flag,
1880 l_inspection_required_flag, -- CONSIGNED FPI
1881 --x_po_shipment_record.invoice_close_tolerance, -- Bug 2761415
1882 l_invoice_close_tolerance, -- CONSIGNED FPI
1883 decode(l_clm_delivery_event_code, --<Event Based Delivery>
1884 null, x_po_shipment_record.last_accept_date,
1885 null),
1886 x_po_shipment_record.last_updated_by,
1887 x_po_shipment_record.last_update_date,
1888 x_po_shipment_record.last_update_login,
1889 x_po_shipment_record.lead_time,
1890 x_po_shipment_record.lead_time_unit,
1891 x_po_shipment_record.line_location_id,
1892 --x_po_shipment_record.match_option,
1893 l_match_option, -- CONSIGNED FPI
1894 decode(l_clm_delivery_event_code, --<Event Based Delivery>
1895 null, x_po_shipment_record.need_by_date,
1896 null),
1897 x_po_shipment_record.org_id,
1898 x_po_shipment_record.po_header_id,
1899 x_po_shipment_record.po_line_id,
1900 x_po_shipment_record.po_release_id,
1901 x_po_shipment_record.price_discount,
1902 x_po_shipment_record.price_override,
1903 -- Standard WHO column: x_po_shipment_record.program_application_id
1904 -- Standard WHO column: x_po_shipment_record.program_id
1905 -- Standard WHO column: x_po_shipment_record.program_update_date
1906 decode(l_clm_delivery_event_code, --<Event Based Delivery>
1907 null, x_po_shipment_record.promised_date,
1908 null),
1909 x_po_shipment_record.qty_rcv_exception_code,
1910 x_po_shipment_record.qty_rcv_tolerance,
1911 l_quantity, --bug#2717053
1912 x_po_shipment_record.quantity_accepted,
1913 x_po_shipment_record.quantity_billed,
1914 x_po_shipment_record.quantity_cancelled,
1915 x_po_shipment_record.quantity_received,
1916 x_po_shipment_record.quantity_rejected,
1917 x_po_shipment_record.quantity_shipped,
1918 x_po_shipment_record.receipt_days_exception_code,
1919 --x_po_shipment_record.receipt_required_flag,
1920 l_receipt_required_flag, -- CONSIGNED FPI
1921 x_po_shipment_record.receive_close_tolerance, -- Bug 2761415
1922 x_po_shipment_record.receiving_routing_id,
1923 -- Standard WHO column: x_po_shipment_record.request_id
1924 x_po_shipment_record.shipment_num,
1925 x_po_shipment_record.shipment_type,
1926 l_to_ship_to_location_id, --bug#2717053
1927 l_to_ship_to_organization_id, --bug#2717053
1928 x_po_shipment_record.ship_via_lookup_code,
1929 x_po_shipment_record.source_shipment_id,
1930 x_po_shipment_record.start_date,
1931 x_po_shipment_record.taxable_flag,
1932 x_po_shipment_record.tax_code_id,
1933 x_po_shipment_record.tax_name, --<R12 eTax Integration>
1934 x_po_shipment_record.tax_user_override_flag,
1935 x_po_shipment_record.terms_id,
1936 -- <Removed Encumbrance FPJ>
1937 -- x_po_shipment_record.unencumbered_quantity,
1938 x_po_shipment_record.unit_meas_lookup_code,
1939 x_po_shipment_record.unit_of_measure_class,
1940 --togeorge 10/05/2000
1941 --added note to receiver
1942 x_po_shipment_record.note_to_receiver,
1943 x_secondary_unit_of_measure,
1944 x_secondary_quantity,
1945 x_po_shipment_record.preferred_grade,
1946 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1947 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1948 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1949 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1950 --<INVCONV R12 END>
1951 l_consigned_flag , -- CONSIGNED FPI
1952 x_po_shipment_record.amount, -- Services FPJ (Except for ordered amt all other amts are 0)
1953 0, -- Services FPJ
1954 0, -- Services FPJ
1955 0, -- Services FPJ
1956 0, -- Services FPJ
1957 0, -- Services FPJ
1958 x_po_shipment_record.transaction_flow_header_id, --< Shared Proc FPJ >
1959 x_po_shipment_record.manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1960 --<DBI Req Fulfillment 11.5.11 Start >
1961 , decode(l_closed_code,'CLOSED',
1962 nvl(x_po_shipment_record.closed_date,sysdate), null) ---shipment closed date
1963 , decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
1964 'CLOSED FOR RECEIVING',sysdate,null) --closed for receiving date
1965 , decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
1966 'CLOSED FOR INVOICE',sysdate,null) ---closed for invoice date
1967 --<DBI Req Fulfillment 11.5.11 End >
1968 , x_po_shipment_record.outsourced_assembly -- <SHIKYU R12>
1969 ,g_tax_attribute_update_code --<R12 eTax Integration>
1970 -- <Complex Work R12 START> bug 4958300: added nvl for basis columns
1971 , nvl(x_po_shipment_record.value_basis, l_line_value_basis)
1972 , nvl(x_po_shipment_record.matching_basis, l_line_matching_basis)
1973 , x_po_shipment_record.payment_type
1974 , x_po_shipment_record.description
1975 , x_po_shipment_record.work_approver_id
1976 -- <Complex Work R12 END>
1977 , NVL2(g_tax_attribute_update_code, p_orig_line_location_id, null) --<eTax Integration R12>
1978 , x_po_shipment_record.clm_delivery_period --<Event Based Delivery>
1979 , x_po_shipment_record.clm_delivery_period_uom
1980 , x_po_shipment_record.clm_promise_period
1981 , x_po_shipment_record.clm_promise_period_uom
1982 , x_po_shipment_record.clm_pop_duration
1983 , x_po_shipment_record.clm_pop_duration_uom
1984 );
1985
1986 x_return_code := 0;
1987
1988 EXCEPTION
1989 WHEN OTHERS THEN
1990 copydoc_sql_error('insert_shipment', l_progress, sqlcode,
1991 x_online_report_id,
1992 x_sequence,
1993 x_line_num, x_po_shipment_record.shipment_num, 0);
1994 x_return_code := -1;
1995 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
1996 PO_DEBUG.debug_exc
1997 (p_log_head => g_module_prefix||'insert_shipment',
1998 p_progress => l_progress);
1999 END IF;
2000 END insert_shipment;
2001
2002
2003
2004 PROCEDURE insert_distribution(
2005 x_po_distribution_record IN po_distributions%ROWTYPE,
2006 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2007 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2008 x_line_num IN po_online_report_text.line_num%TYPE,
2009 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
2010 x_return_code OUT NOCOPY NUMBER
2011 ) IS
2012
2013 l_progress VARCHAR2(4);
2014
2015 l_return_status VARCHAR2(1);
2016 l_msg_data VARCHAR2(2000);
2017 l_msg_count NUMBER;
2018
2019 --<GRANTS FPJ START>
2020 l_msg_buf VARCHAR2(2000);
2021 l_gms_po_interface_obj gms_po_interface_type;
2022 l_award_id PO_DISTRIBUTIONS_ALL.award_id%TYPE := NULL;
2023 --<GRANTS FPJ END>
2024
2025 BEGIN
2026
2027 l_progress := '000';
2028 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2029 PO_DEBUG.debug_stmt
2030 (p_log_head => g_module_prefix||'insert_distribution',
2031 p_token => 'invoked',
2032 p_message => 'dist ID: '||
2033 x_po_distribution_record.po_distribution_id||
2034 ' online_report ID: '||x_online_report_id);
2035 END IF;
2036
2037 --<GRANTS FPJ START>
2038 SAVEPOINT insert_distribution_savepoint;
2039
2040 --If this distribution references an award_id, then
2041 --create new award distribution lines.
2042
2043 IF x_po_distribution_record.award_id IS NOT NULL THEN
2044
2045 l_progress := '010';
2046
2047 l_gms_po_interface_obj := gms_po_interface_type(
2048 gms_type_number(x_po_distribution_record.po_distribution_id),
2049 gms_type_number(x_po_distribution_record.distribution_num),
2050 gms_type_number(x_po_distribution_record.project_id),
2051 gms_type_number(x_po_distribution_record.task_id),
2052 gms_type_number(x_po_distribution_record.award_id),
2053 gms_type_number(NULL));
2054
2055 l_progress := '020';
2056
2057 PO_GMS_INTEGRATION_PVT.maintain_adl (
2058 p_api_version => 1.0,
2059 x_return_status => l_return_status,
2060 x_msg_count => l_msg_count,
2061 x_msg_data => l_msg_data,
2062 p_caller => 'COPYDOC',
2063 x_po_gms_interface_obj => l_gms_po_interface_obj);
2064
2065 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2066 l_progress := '030';
2067 RAISE FND_API.G_EXC_ERROR;
2068 END IF;
2069
2070 l_award_id := l_gms_po_interface_obj.award_set_id_out(1);
2071
2072 END IF;
2073
2074 l_progress := '040';
2075
2076 --<GRANTS FPJ END>
2077
2078 /** Bug 1003635
2079 * bgu, Oct. 06, 1999
2080 * Should not copy encumberance reserve related fields.
2081 */
2082
2083 INSERT INTO PO_DISTRIBUTIONS (
2084 accrual_account_id,
2085 accrued_flag,
2086 accrue_on_receipt_flag,
2087 amount_billed,
2088 attribute1,
2089 attribute10,
2090 attribute11,
2091 attribute12,
2092 attribute13,
2093 attribute14,
2094 attribute15,
2095 attribute2,
2096 attribute3,
2097 attribute4,
2098 attribute5,
2099 attribute6,
2100 attribute7,
2101 attribute8,
2102 attribute9,
2103 attribute_category,
2104 award_id,
2105 -- base_amount_billed, -- June 07, 1999, bgu
2106 bom_resource_id,
2107 budget_account_id,
2108 code_combination_id,
2109 created_by,
2110 creation_date,
2111 deliver_to_location_id,
2112 deliver_to_person_id,
2113 destination_context,
2114 destination_organization_id,
2115 destination_subinventory,
2116 destination_type_code,
2117 distribution_num,
2118 -- encumbered_amount, -- Oct. 06, 1999, bgu
2119 encumbered_flag,
2120 end_item_unit_number,
2121 expenditure_item_date,
2122 expenditure_organization_id,
2123 expenditure_type,
2124 failed_funds_lookup_code,
2125 -- gl_cancelled_date,
2126 gl_closed_date,
2127 gl_encumbered_date,
2128 gl_encumbered_period_name,
2129 government_context,
2130 kanban_card_id,
2131 last_updated_by,
2132 last_update_date,
2133 last_update_login,
2134 line_location_id,
2135 -- <Removed Encumbrance FPJ>
2136 -- mrc_encumbered_amount,
2137 mrc_rate,
2138 mrc_rate_date,
2139 -- <Removed Encumbrance FPJ>
2140 -- mrc_unencumbered_amount,
2141 nonrecoverable_tax,
2142 org_id,
2143 po_distribution_id,
2144 po_header_id,
2145 po_line_id,
2146 po_release_id,
2147 prevent_encumbrance_flag,
2148 -- Standard WHO column: program_application_id
2149 -- Standard WHO column: program_id
2150 -- Standard WHO column: program_update_date
2151 project_accounting_context,
2152 project_id,
2153 quantity_billed,
2154 quantity_cancelled,
2155 quantity_delivered,
2156 quantity_ordered,
2157 rate,
2158 rate_date,
2159 recoverable_tax,
2160 recovery_rate,
2161 -- Standard WHO column: request_id
2162 req_distribution_id,
2163 req_header_reference_num,
2164 req_line_reference_num,
2165 set_of_books_id,
2166 source_distribution_id,
2167 task_id,
2168 tax_recovery_override_flag,
2169 -- <Removed Encumbrance FPJ>
2170 -- unencumbered_amount,
2171 -- unencumbered_quantity,
2172 variance_account_id,
2173 wip_entity_id,
2174 wip_line_id,
2175 wip_operation_seq_num,
2176 wip_repetitive_schedule_id,
2177 wip_resource_seq_num,
2178 --togeorge 10/05/2000
2179 --added oke columns
2180 oke_contract_line_id,
2181 oke_contract_deliverable_id,
2182 amount_ordered, -- Services FPJ
2183 amount_delivered, -- Services FPJ
2184 amount_cancelled, -- Services FPJ
2185 distribution_type, -- <Encumbrance FPJ>
2186 amount_to_encumber, -- Bug 3309589
2187 dest_charge_account_id, --< Shared Proc FPJ >
2188 dest_variance_account_id --< Shared Proc FPJ >
2189 ,tax_attribute_update_code, --<R12 eTax Integration>
2190 group_line_id, -- CLM CLIN/SLIN Changes
2191 amount_funded, -- CLM Partial Funding Changes
2192 funded_value, -- CLM Partial Funding Changes
2193 partial_funded_flag, -- CLM Partial Funding Changes
2194 quantity_funded, -- CLM Partial Funding Changes
2195 change_in_funded_value, -- CLM Partial Funding Changes
2196 acrn, -- ACRN Project
2197 clm_payment_sequence_num -- Payment Instruction
2198 ) VALUES (
2199 x_po_distribution_record.accrual_account_id,
2200 -- x_po_distribution_record.accrued_flag, 2861250
2201 null,
2202 x_po_distribution_record.accrue_on_receipt_flag,
2203 x_po_distribution_record.amount_billed,
2204 x_po_distribution_record.attribute1,
2205 x_po_distribution_record.attribute10,
2206 x_po_distribution_record.attribute11,
2207 x_po_distribution_record.attribute12,
2208 x_po_distribution_record.attribute13,
2209 x_po_distribution_record.attribute14,
2210 x_po_distribution_record.attribute15,
2211 x_po_distribution_record.attribute2,
2212 x_po_distribution_record.attribute3,
2213 x_po_distribution_record.attribute4,
2214 x_po_distribution_record.attribute5,
2215 x_po_distribution_record.attribute6,
2216 x_po_distribution_record.attribute7,
2217 x_po_distribution_record.attribute8,
2218 x_po_distribution_record.attribute9,
2219 x_po_distribution_record.attribute_category,
2220 l_award_id, --<GRANTS FPJ>
2221 -- x_po_distribution_record.base_amount_billed,
2222 x_po_distribution_record.bom_resource_id,
2223 x_po_distribution_record.budget_account_id,
2224 x_po_distribution_record.code_combination_id,
2225 x_po_distribution_record.created_by,
2226 x_po_distribution_record.creation_date,
2227 x_po_distribution_record.deliver_to_location_id,
2228 x_po_distribution_record.deliver_to_person_id,
2229 x_po_distribution_record.destination_context,
2230 x_po_distribution_record.destination_organization_id,
2231 x_po_distribution_record.destination_subinventory,
2232 x_po_distribution_record.destination_type_code,
2233 x_po_distribution_record.distribution_num,
2234 -- x_po_distribution_record.encumbered_amount,
2235 -- x_po_distribution_record.encumbered_flag,
2236 'N',
2237 x_po_distribution_record.end_item_unit_number,
2238 x_po_distribution_record.expenditure_item_date,
2239 x_po_distribution_record.expenditure_organization_id,
2240 x_po_distribution_record.expenditure_type,
2241 x_po_distribution_record.failed_funds_lookup_code,
2242 -- x_po_distribution_record.gl_cancelled_date,
2243 x_po_distribution_record.gl_closed_date,
2244 x_po_distribution_record.gl_encumbered_date,
2245 x_po_distribution_record.gl_encumbered_period_name,
2246 x_po_distribution_record.government_context,
2247 x_po_distribution_record.kanban_card_id,
2248 x_po_distribution_record.last_updated_by,
2249 x_po_distribution_record.last_update_date,
2250 x_po_distribution_record.last_update_login,
2251 x_po_distribution_record.line_location_id,
2252 -- <Removed Encumbrance FPJ>
2253 -- x_po_distribution_record.mrc_encumbered_amount,
2254 x_po_distribution_record.mrc_rate,
2255 x_po_distribution_record.mrc_rate_date,
2256 -- <Removed Encumbrance FPJ>
2257 -- x_po_distribution_record.mrc_unencumbered_amount,
2258 x_po_distribution_record.nonrecoverable_tax,
2259 x_po_distribution_record.org_id,
2260 x_po_distribution_record.po_distribution_id,
2261 x_po_distribution_record.po_header_id,
2262 x_po_distribution_record.po_line_id,
2263 x_po_distribution_record.po_release_id,
2264 x_po_distribution_record.prevent_encumbrance_flag,
2265 -- Standard WHO column: x_po_distribution_record.program_application_id
2266 -- Standard WHO column: x_po_distribution_record.program_id
2267 -- Standard WHO column: x_po_distribution_record.program_update_date
2268 x_po_distribution_record.project_accounting_context,
2269 x_po_distribution_record.project_id,
2270 x_po_distribution_record.quantity_billed,
2271 x_po_distribution_record.quantity_cancelled,
2272 x_po_distribution_record.quantity_delivered,
2273 x_po_distribution_record.quantity_ordered,
2274 x_po_distribution_record.rate,
2275 x_po_distribution_record.rate_date,
2276 x_po_distribution_record.recoverable_tax,
2277 x_po_distribution_record.recovery_rate,
2278 -- Standard WHO column: x_po_distribution_record.request_id
2279 x_po_distribution_record.req_distribution_id,
2280 x_po_distribution_record.req_header_reference_num,
2281 x_po_distribution_record.req_line_reference_num,
2282 x_po_distribution_record.set_of_books_id,
2283 x_po_distribution_record.source_distribution_id,
2284 x_po_distribution_record.task_id,
2285 x_po_distribution_record.tax_recovery_override_flag,
2286 -- <Removed Encumbrance FPJ>
2287 -- x_po_distribution_record.unencumbered_amount,
2288 -- x_po_distribution_record.unencumbered_quantity,
2289 x_po_distribution_record.variance_account_id,
2290 x_po_distribution_record.wip_entity_id,
2291 x_po_distribution_record.wip_line_id,
2292 x_po_distribution_record.wip_operation_seq_num,
2293 x_po_distribution_record.wip_repetitive_schedule_id,
2294 x_po_distribution_record.wip_resource_seq_num,
2295 --togeorge 10/05/2000
2296 --added oke columns
2297 x_po_distribution_record.oke_contract_line_id,
2298 x_po_distribution_record.oke_contract_deliverable_id,
2299 x_po_distribution_record.amount_ordered, -- Services FPJ
2300 0, -- Services FPJ
2301 0, -- Services FPJ
2302 x_po_distribution_record.distribution_type, -- <Encumbrance FPJ>
2303 x_po_distribution_record.amount_to_encumber, -- Bug 3309589
2304 x_po_distribution_record.dest_charge_account_id, --< Shared Proc FPJ >
2305 x_po_distribution_record.dest_variance_account_id --< Shared Proc FPJ >
2306 ,NVL2(g_tax_attribute_update_code,'CREATE',NULL), --<R12 eTax Integration>
2307 x_po_distribution_record.group_line_id, -- CLM CLIN/SLIN Changes
2308 x_po_distribution_record.amount_funded, -- CLM Partial Funding Changes
2309 x_po_distribution_record.funded_value, -- CLM Partial Funding Changes
2310 x_po_distribution_record.partial_funded_flag, -- CLM Partial Funding Changes
2311 x_po_distribution_record.quantity_funded, -- CLM Partial Funding Changes
2312 x_po_distribution_record.funded_value, -- CLM Partial Funding Changes
2313 x_po_distribution_record.acrn, -- ACRN Project
2314 x_po_distribution_record.clm_payment_sequence_num
2315 );
2316
2317 x_return_code := 0;
2318
2319 l_progress := '050';
2320
2321
2322 EXCEPTION
2323
2324 --<GRANTS FPJ START>
2325 WHEN FND_API.G_EXC_ERROR THEN
2326 ROLLBACK TO SAVEPOINT insert_distribution_savepoint;
2327 FOR i in 1..FND_MSG_PUB.count_msg LOOP
2328 BEGIN
2329 l_msg_buf := SUBSTRB(FND_MSG_PUB.get(p_msg_index=>i,
2330 p_encoded=>FND_API.G_FALSE),
2331 1, 2000);
2332 online_report(x_online_report_id,
2333 x_sequence,
2334 l_msg_buf,
2335 x_line_num,
2336 x_shipment_num,
2337 x_po_distribution_record.distribution_num);
2338 END;
2339 END LOOP;
2340 x_return_code := -1;
2341 --<GRANTS FPJ END>
2342 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2343 PO_DEBUG.debug_stmt
2344 (p_log_head => g_module_prefix||'insert_distribution',
2345 p_token => l_progress,
2346 p_message => 'FND_API.g_exc_error exception caught.');
2347 END IF;
2348
2349 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2350 PO_DEBUG.debug_exc
2351 (p_log_head => g_module_prefix||'insert_distribution',
2352 p_progress => l_progress);
2353 END IF;
2354
2355 WHEN OTHERS THEN
2356 ROLLBACK TO SAVEPOINT insert_distribution_savepoint; --<GRANTS FPJ>
2357 copydoc_sql_error('insert_distribution', l_progress, sqlcode,
2358 x_online_report_id,
2359 x_sequence,
2360 x_line_num, x_shipment_num, x_po_distribution_record.distribution_num);
2361 x_return_code := -1;
2362 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2363 PO_DEBUG.debug_exc
2364 (p_log_head => g_module_prefix||'insert_distribution',
2365 p_progress => l_progress);
2366 END IF;
2367 END insert_distribution;
2368
2369
2370 PROCEDURE handle_fatal(
2371 x_return_code OUT NOCOPY NUMBER
2372 ) IS
2373 BEGIN
2374
2375 x_return_code := -1;
2376
2377 IF (po_line_cursor%ISOPEN) THEN
2378 CLOSE po_line_cursor;
2379 END IF;
2380 IF (po_shipment_cursor%ISOPEN) THEN
2381 CLOSE po_shipment_cursor;
2382 END IF;
2383 IF (po_distribution_cursor%ISOPEN) THEN
2384 CLOSE po_distribution_cursor;
2385 END IF;
2386 IF (clm_po_line_cursor%ISOPEN) THEN
2387 CLOSE clm_po_line_cursor;
2388 END IF;
2389 IF (clm_po_distribution_cursor%ISOPEN) THEN
2390 CLOSE clm_po_distribution_cursor;
2391 END IF;
2392
2393 END handle_fatal;
2394
2395 /**************************************************************
2396 ** Fetch header info from FROM PO header
2397 ** Validate header with new info if there's any
2398 ** Insert header with old and new info to the new header
2399 ** Copy attachment if necessary
2400 ***************************************************************/
2401 PROCEDURE process_header(
2402 x_action_code IN VARCHAR2,
2403 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
2404 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
2405 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
2406 x_from_po_header_id IN po_headers.po_header_id%TYPE,
2407 x_to_segment1 IN po_headers.segment1%TYPE,
2408 x_agent_id IN po_headers.agent_id%TYPE,
2409 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
2410 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
2411 x_copy_attachments IN BOOLEAN,
2412 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2413 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2414 x_return_code OUT NOCOPY NUMBER,
2415 x_copy_terms IN VARCHAR2
2416 ) IS
2417
2418 l_progress VARCHAR2(4);
2419 x_internal_return_code NUMBER := NULL;
2420
2421 /* FPJ CONTERMS START */
2422 l_po_from_document_type po_headers.type_lookup_code%TYPE;
2423 l_contracts_from_document_type VARCHAR2(150); --Change this in 11iX
2424 l_contracts_to_document_type VARCHAR2(150); --Change this in 11iX
2425
2426 l_return_status VARCHAR2(1);
2427 l_msg_count NUMBER;
2428 l_msg_data VARCHAR2(2000);
2429
2430 l_conterms_exist_flag VARCHAR2(1);
2431 l_temp_copy_terms VARCHAR2(1);
2432 l_internal_party_id po_headers_all.org_id%TYPE;
2433 l_internal_contact_id po_headers_all.agent_id%TYPE;
2434 l_external_party_id po_headers_all.vendor_id%TYPE;
2435 l_external_party_site_id po_headers_all.vendor_site_id%TYPE;
2436 l_external_contact_id po_headers_all.vendor_contact_id%TYPE;
2437 l_copy_contracts_attachments VARCHAR2(1) := 'N';
2438 /* FPJ CONTERMS END */
2439
2440 BEGIN
2441
2442 l_progress := '000';
2443 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2444 PO_DEBUG.debug_stmt
2445 (p_log_head => g_module_prefix||'process_header',
2446 p_token => 'invoked',
2447 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
2448 x_to_doc_subtype||' from header ID: '||x_from_po_header_id||
2449 ' to segment1: '||x_to_segment1||' header ID: '||
2450 x_po_header_record.po_header_id||' agent ID: '||x_agent_id||
2451 ' sob ID: '||x_sob_id||' inv_org ID: '||x_inv_org_id||
2452 ' online_report ID: '||x_online_report_id);
2453 END IF;
2454
2455 l_progress := '010';
2456
2457 fetch_header(x_po_header_record,
2458 x_from_po_header_id,
2459 x_online_report_id,
2460 x_sequence,
2461 x_internal_return_code);
2462
2463 -- <FPJ CONTERMS> store the source document document type
2464 l_po_from_document_type := x_po_header_record.type_lookup_code;
2465
2466 --COMMIT; < HTML Agreements R12>
2467
2468 l_progress := '020';
2469
2470 IF (x_internal_return_code = 0) THEN
2471 l_progress := '020';
2472
2473 /** New po_header_id and segment1 are created and stored in
2474 x_po_header_record ****/
2475 po_copydoc_s2.validate_header(x_action_code,
2476 x_to_doc_subtype,
2477 x_to_global_flag, -- Global Agreements (FP-I)
2478 x_po_header_record,
2479 x_to_segment1,
2480 x_agent_id,
2481 x_sob_id,
2482 x_inv_org_id,
2483 x_online_report_id,
2484 x_sequence,
2485 x_internal_return_code);
2486 -- COMMIT; < HTML Agreements R12>
2487 IF (x_internal_return_code = 0) THEN
2488 l_progress := '030';
2489
2490 insert_header(x_po_header_record,
2491 x_online_report_id,
2492 x_sequence,
2493 x_internal_return_code,
2494 x_copy_terms);
2495 -- COMMIT; < HTML Agreements R12>
2496 IF (x_internal_return_code = 0) THEN
2497 l_progress := '040';
2498
2499 /* FPJ CONTERMS START */
2500
2501 -- process user choice, proceed only if choice to copy is made
2502 IF NVL(x_copy_terms, 'N') IN ('L', 'D') THEN
2503
2504 l_progress := '050';
2505
2506 -- Additional check to see if source document has terms attached
2507 -- SQL what: select conterms_exist_flag among other columns
2508 -- SQL why: need to check if source document has terms
2509 -- SQL join: po_header_id
2510 SELECT conterms_exist_flag
2511 ,org_id
2512 ,agent_id
2513 ,vendor_id
2514 ,vendor_site_id
2515 ,vendor_contact_id
2516 INTO l_conterms_exist_flag
2517 ,l_internal_party_id
2518 ,l_internal_contact_id
2519 ,l_external_party_id
2520 ,l_external_party_site_id
2521 ,l_external_contact_id
2522 FROM po_headers
2523 WHERE po_header_id = x_from_po_header_id;
2524
2525 -- contract terms exist and user has chosen to copy
2526 IF (UPPER(l_conterms_exist_flag) = 'Y') THEN
2527
2528 l_progress := '060';
2529
2530 -- decode so that Y/N could be passed to Contracts API
2531 IF (x_copy_terms = 'L') THEN
2532 l_temp_copy_terms := 'N';
2533 ELSIF (x_copy_terms = 'D') THEN
2534 l_temp_copy_terms := 'Y';
2535 END IF;
2536
2537 /* <Bug3365562 Start>. Commented out the following piece of code.
2538 Whether or not to attach Contract Attachments needs to
2539 determined on basis of 'Contract Terms' radio buttons
2540 on Copy Doc form rather than the 'Copy Attachments'
2541 checkbox. 'Copy Attachments' checkbox will only
2542 determine whether PO Attachments are copied or not.
2543 -- check to see if attachments need to be copied
2544 IF (x_copy_attachments) THEN
2545 l_copy_contracts_attachments := 'Y';
2546 ELSE
2547 l_copy_contracts_attachments := 'N';
2548 END IF;*/
2549 -- l_copy_contracts_attachments can be put directly to 'Y' because
2550 -- this part is entered only when x_copy_terms is 'L' or 'D' in both
2551 -- of which cases we need to copy Contract Articles.
2552 l_copy_contracts_attachments := 'Y';
2553 -- <Bug3365562 End>
2554
2555 -- decode source document type for contracts
2556 IF (l_po_from_document_type IN ('CONTRACT', 'BLANKET')) THEN
2557 l_contracts_from_document_type := 'PA_'||l_po_from_document_type;
2558 ELSIF (l_po_from_document_type = 'STANDARD') THEN
2559 l_contracts_from_document_type := 'PO_'||l_po_from_document_type;
2560 END IF;
2561
2562 -- x_po_header_record now contains new po header
2563 -- decode target document type for contracts
2564 IF (x_po_header_record.type_lookup_code IN ( 'CONTRACT', 'BLANKET')) THEN
2565 l_contracts_to_document_type := 'PA_'|| x_po_header_record.type_lookup_code;
2566 ELSIF (x_po_header_record.type_lookup_code = 'STANDARD') THEN
2567 l_contracts_to_document_type := 'PO_'|| x_po_header_record.type_lookup_code;
2568 END IF;
2569
2570 -- call contracts API to copy terms and deliverables
2571 OKC_TERMS_COPY_GRP.copy_doc (
2572 p_api_version => 1.0,
2573 p_init_msg_list => FND_API.G_FALSE,
2574 p_commit => FND_API.G_FALSE,
2575 p_source_doc_type => l_contracts_from_document_type,
2576 p_source_doc_id => x_from_po_header_id,
2577 p_target_doc_type => l_contracts_to_document_type,
2578 p_target_doc_id => x_po_header_record.po_header_id,
2579 p_keep_version => l_temp_copy_terms,
2580 p_article_effective_date => SYSDATE,
2581 -- Bug 3365562. Passing this parameter as 'N' so that
2582 -- deliverable attachments do not get copied.
2583 p_copy_del_attachments_yn => 'N',
2584 p_copy_deliverables => 'Y',
2585 p_copy_doc_attachments => l_copy_contracts_attachments,
2586 p_document_number => x_po_header_record.segment1,
2587 p_internal_party_id => to_char(l_internal_party_id),
2588 p_internal_contact_id => to_char(l_internal_contact_id),
2589 p_external_party_id => to_char(l_external_party_id),
2590 p_external_party_site_id => to_char(l_external_party_site_id),
2591 p_external_contact_id => to_char(l_external_contact_id),
2592 x_return_status => l_return_status,
2593 x_msg_data => l_msg_data,
2594 x_msg_count => l_msg_count
2595 );
2596
2597 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2598 l_progress := '070';
2599
2600 -- terms copying failed , set conterms exist flag on the new doc to N
2601 UPDATE po_headers_all
2602 SET conterms_exist_flag = 'N'
2603 WHERE po_header_id = x_po_header_record.po_header_id;
2604 END IF; -- return status is error
2605
2606 -- COMMIT; < HTML Agreements R12>
2607 END IF; -- conterms exist flag
2608
2609 END IF; -- if chosen to copy
2610
2611 l_progress := '080';
2612
2613 IF (x_copy_attachments) THEN
2614 l_progress := '090';
2615 fnd_attached_documents2_pkg.copy_attachments('PO_HEADERS',
2616 x_from_po_header_id,
2617 '',
2618 '',
2619 '',
2620 '',
2621 'PO_HEADERS',
2622 x_po_header_record.po_header_id,
2623 '',
2624 '',
2625 '',
2626 '',
2627 fnd_global.user_id,
2628 fnd_global.login_id,
2629 '',
2630 '',
2631 '');
2632 -- COMMIT; < HTML Agreements R12>
2633 END IF;
2634 END IF;
2635 END IF;
2636 END IF;
2637
2638 x_return_code := x_internal_return_code;
2639
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 x_return_code := -1;
2643 copydoc_sql_error('process_header', l_progress, sqlcode,
2644 x_online_report_id,
2645 x_sequence,
2646 0, 0, 0);
2647 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2648 PO_DEBUG.debug_exc
2649 (p_log_head => g_module_prefix||'process_header',
2650 p_progress => l_progress);
2651 END IF;
2652 END process_header;
2653
2654 --<Unified Catalog R12: Start>
2655 --------------------------------------------------------------------------------
2656 --Start of Comments
2657 --Name: copy_attributes
2658 --Pre-reqs:
2659 -- None
2660 --Modifies:
2661 -- None
2662 --Locks:
2663 -- None.
2664 --Function:
2665 -- To copy the Attribute Values and TLP rows from a given document to a new
2666 -- document.
2667 --
2668 --Parameters:
2669 --IN:
2670 --p_orig_po_line_id
2671 -- The PO_LINE_ID of the document from which the data has to be copied.
2672 --p_new_po_line_id
2673 -- The PO_LINE_ID of the new document.
2674 --p_line_num
2675 -- The line number in the document which is being ccopied.
2676 --p_online_report_id
2677 -- The key to PO_ONLINE_REPORT_TEXT where error messages will be added in case
2678 -- of error
2679 --OUT:
2680 --x_sequence
2681 -- The sequence number of the error for this document. It will be incremented
2682 -- by 1 inside the procedure copydoc_sql_error()
2683 --End of Comments
2684 --------------------------------------------------------------------------------
2685 PROCEDURE copy_attributes
2686 (
2687 p_orig_po_line_id IN PO_LINES.po_line_id%TYPE
2688 , p_new_po_line_id IN PO_LINES.po_line_id%TYPE
2689 , p_line_num IN PO_LINES.line_num%TYPE
2690 , p_online_report_id IN PO_ONLINE_REPORT_TEXT.online_report_id%TYPE
2691 , x_sequence IN OUT NOCOPY PO_ONLINE_REPORT_TEXT.sequence%TYPE
2692 )
2693 IS
2694 d_mod CONSTANT VARCHAR2(100) := D_copy_attributes;
2695 l_progress VARCHAR2(4);
2696
2697 BEGIN
2698 l_progress := '010';
2699
2700 IF PO_LOG.d_proc THEN
2701 PO_LOG.proc_begin(d_mod,'p_orig_po_line_id',p_orig_po_line_id);
2702 PO_LOG.proc_begin(d_mod,'p_new_po_line_id',p_new_po_line_id);
2703 PO_LOG.proc_begin(d_mod,'p_line_num',p_line_num);
2704 PO_LOG.proc_begin(d_mod,'p_online_report_id',p_online_report_id);
2705 PO_LOG.proc_begin(d_mod,'x_sequence',x_sequence);
2706 END IF;
2707
2708 PO_ATTRIBUTE_VALUES_PVT.copy_attributes
2709 (
2710 p_orig_po_line_id => p_orig_po_line_id
2711 , p_new_po_line_id => p_new_po_line_id
2712 );
2713
2714 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
2715 EXCEPTION
2716 WHEN OTHERS THEN
2717 copydoc_sql_error(x_routine => d_mod,
2718 x_progress => l_progress,
2719 x_sqlcode => SQLCODE,
2720 x_online_report_id => p_online_report_id,
2721 x_sequence => x_sequence,
2722 x_line_num => p_line_num,
2723 x_shipment_num => 0,
2724 x_distribution_num => 0);
2725 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
2726 RAISE;
2727 END copy_attributes;
2728 --<Unified Catalog R12: End>
2729
2730 --<Enhanced Pricing Start:>
2731 --------------------------------------------------------------------------------
2732 --Start of Comments
2733 --Name: copy_line_adjustments
2734 --Pre-reqs:
2735 -- None
2736 --Modifies:
2737 -- None
2738 --Locks:
2739 -- None.
2740 --Function:
2741 -- To copy the adjustments from a given document line to a new
2742 -- document line
2743 --
2744 --Parameters:
2745 --IN:
2746 --p_orig_po_line_id
2747 -- The PO_LINE_ID of the document from which the data has to be copied.
2748 --p_new_po_header_id
2749 -- The PO_HEADER_ID of the new document
2750 --p_new_po_line_id
2751 -- The PO_LINE_ID of the new document.
2752 --p_line_num
2753 -- The line number in the document which is being ccopied.
2754 --p_online_report_id
2755 -- The key to PO_ONLINE_REPORT_TEXT where error messages will be added in case
2756 -- of error
2757 --OUT:
2758 --x_sequence
2759 -- The sequence number of the error for this document. It will be incremented
2760 -- by 1 inside the procedure copydoc_sql_error()
2761 --End of Comments
2762 --------------------------------------------------------------------------------
2763 PROCEDURE copy_line_adjustments
2764 (
2765 p_orig_po_line_id IN PO_LINES.po_line_id%TYPE
2766 , p_new_po_header_id IN PO_HEADERS.po_header_id%TYPE
2767 , p_new_po_line_id IN PO_LINES.po_line_id%TYPE
2768 , p_line_num IN PO_LINES.line_num%TYPE
2769 , p_online_report_id IN PO_ONLINE_REPORT_TEXT.online_report_id%TYPE
2770 , x_sequence IN OUT NOCOPY PO_ONLINE_REPORT_TEXT.sequence%TYPE
2771 )
2772 IS
2773 d_mod CONSTANT VARCHAR2(100) := D_copy_line_adjustments;
2774 l_api_name CONSTANT varchar2(30) := 'copy_line_adjustments';
2775 l_log_head CONSTANT varchar2(100) := g_module_prefix || l_api_name;
2776 l_progress VARCHAR2(4);
2777
2778 l_return_status VARCHAR2(1);
2779 l_return_status_text VARCHAR2(2000);
2780
2781 BEGIN
2782 l_progress := '010';
2783
2784 IF g_debug_stmt THEN
2785 PO_DEBUG.debug_begin(l_log_head);
2786 PO_DEBUG.debug_var(l_log_head,l_progress,'p_orig_po_line_id',p_orig_po_line_id);
2787 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_po_header_id',p_new_po_header_id);
2788 PO_DEBUG.debug_var(l_log_head,l_progress,'p_new_po_line_id',p_new_po_line_id);
2789 PO_DEBUG.debug_var(l_log_head,l_progress,'p_line_num',p_line_num);
2790 PO_DEBUG.debug_var(l_log_head,l_progress,'p_online_report_id',p_online_report_id);
2791 PO_DEBUG.debug_var(l_log_head,l_progress,'x_sequence',x_sequence);
2792 END IF;
2793
2794 l_progress := '020';
2795 PO_PRICE_ADJUSTMENTS_PKG.copy_line_adjustments
2796 (p_src_po_line_id => p_orig_po_line_id,
2797 p_dest_po_header_id => p_new_po_header_id,
2798 p_dest_po_line_id => p_new_po_line_id,
2799 p_mode => PO_PRICE_ADJUSTMENTS_PKG.G_COPY_ALL_MOD,
2800 x_return_status_text => l_return_status_text,
2801 x_return_status => l_return_status);
2802
2803 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2804 online_report(p_online_report_id,
2805 x_sequence,
2806 l_return_status_text,
2807 p_line_num,
2808 0,
2809 0);
2810 IF g_debug_stmt THEN
2811 PO_DEBUG.debug_stmt(l_log_head,l_progress,l_return_status_text);
2812 END IF;
2813 END IF;
2814
2815 IF g_debug_stmt THEN
2816 PO_DEBUG.debug_end(l_log_head);
2817 PO_DEBUG.debug_var(l_log_head,l_progress,'x_sequence',x_sequence);
2818 END IF;
2819 EXCEPTION
2820 WHEN OTHERS THEN
2821 copydoc_sql_error(x_routine => d_mod,
2822 x_progress => l_progress,
2823 x_sqlcode => SQLCODE,
2824 x_online_report_id => p_online_report_id,
2825 x_sequence => x_sequence,
2826 x_line_num => p_line_num,
2827 x_shipment_num => 0,
2828 x_distribution_num => 0);
2829 IF g_debug_unexp THEN
2830 PO_DEBUG.debug_stmt(l_log_head,l_progress,'Unhandled exception');
2831 END IF;
2832 RAISE;
2833 END copy_line_adjustments;
2834 --<Enhanced Pricing End>
2835
2836 PROCEDURE process_line(
2837 x_action_code IN VARCHAR2,
2838 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
2839 x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE,
2840 x_orig_po_line_id IN po_lines.po_line_id%TYPE,
2841 x_wip_install_status IN VARCHAR2,
2842 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
2843 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
2844 x_po_header_id IN po_lines.po_header_id%TYPE,
2845 x_from_po_header_id IN po_lines.po_header_id%TYPE,
2846 x_copy_attachments IN BOOLEAN,
2847 x_copy_price IN BOOLEAN,
2848 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2849 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2850 x_return_code OUT NOCOPY NUMBER,
2851 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
2852 p_calling_program IN VARCHAR2,
2853 p_is_clm_doc IN VARCHAR2 --<Bug 14370174>
2854 ) IS
2855
2856 l_progress VARCHAR2(4);
2857 x_internal_return_code NUMBER := NULL;
2858
2859 l_entity_type po_price_differentials.entity_type%TYPE; -- SERVICES FPJ
2860
2861 BEGIN
2862
2863 l_progress := '000';
2864 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2865 PO_DEBUG.debug_stmt
2866 (p_log_head => g_module_prefix||'process_line',
2867 p_token => 'invoked',
2868 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
2869 x_to_doc_subtype||' header ID: '||x_po_header_id||
2870 ' orig_line ID: '||x_orig_po_line_id||' line ID: '||
2871 x_po_line_record.po_line_id||' sob ID: '||x_sob_id||
2872 ' inv_org ID: '||x_inv_org_id||' online_report ID: '||
2873 x_online_report_id);
2874 END IF;
2875
2876 l_progress := '010';
2877
2878 /* Functionality for PA->RFQ Copy : dreddy
2879 new parameter copy_price is added */
2880 po_copydoc_s3.validate_line(x_action_code,
2881 x_to_doc_subtype,
2882 x_po_line_record,
2883 x_orig_po_line_id,
2884 x_wip_install_status,
2885 x_sob_id,
2886 x_inv_org_id,
2887 x_po_header_id,
2888 x_online_report_id,
2889 x_sequence,
2890 x_copy_price,
2891 x_internal_return_code,
2892 p_is_complex_work_po, -- <Complex Work R12>
2893 p_is_clm_doc); --<Bug 14370174>
2894 --COMMIT; < HTML Agreements R12>
2895
2896
2897 -- Excersied option lines has to be copied as regular lines in copy document flow.
2898
2899 IF (p_calling_program LIKE 'COPY_DOCUMENT') THEN
2900
2901 -- Reset all the option line indicators
2902 IF(Nvl(x_po_line_record.CLM_EXERCISED_FLAG,'N') LIKE 'Y') THEN
2903
2904 x_po_line_record.CLM_OPTION_INDICATOR := NULL ;
2905 x_po_line_record.CLM_BASE_LINE_NUM := NULL ;
2906 x_po_line_record.CLM_OPTION_NUM := NULL ;
2907 x_po_line_record.CLM_OPTION_FROM_DATE := NULL ;
2908 x_po_line_record.CLM_OPTION_TO_DATE := NULL ;
2909 x_po_line_record.CLM_EXERCISED_DATE := NULL ;
2910 x_po_line_record.CLM_EXERCISED_FLAG := NULL ;
2911
2912 END IF ;
2913
2914 -- Reset all base line indicators if it has only one exercised option line
2915
2916 IF (Nvl(x_po_line_record.CLM_OPTION_INDICATOR,' ') LIKE 'B') THEN
2917
2918 SELECT Decode(Count(po_line_id),0,NULL,'B')
2919 INTO x_po_line_record.CLM_OPTION_INDICATOR
2920 FROM po_lines_all
2921 WHERE po_header_id = x_from_po_header_id
2922 AND CLM_BASE_LINE_NUM =x_orig_po_line_id
2923 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N' ;
2924
2925 END IF ;
2926
2927 --UCA Project - CLMR4 Changes Begin
2928 IF ( (Nvl( x_po_line_record.clm_undef_flag,'N') = 'Y' )
2929 AND ( x_po_line_record.order_type_lookup_code <> 'QUANTITY')) THEN
2930
2931 IF (x_po_line_record.matching_basis = 'AMOUNT') THEN
2932 x_po_line_record.amount := x_po_line_record.clm_uda_pricing_total;
2933 ELSE
2934 x_po_line_record.quantity := x_po_line_record.clm_uda_pricing_total;
2935 END IF;
2936 END IF;
2937 --UCA Project - CLMR4 Changes End
2938
2939 END IF ;
2940
2941 IF (x_internal_return_code = 0) THEN
2942 l_progress := '020';
2943
2944 insert_line(x_po_line_record,
2945 x_online_report_id,
2946 x_sequence,
2947 x_internal_return_code);
2948 -- COMMIT; < HTML Agreements R12>
2949
2950 IF (x_internal_return_code = 0) THEN
2951 IF (x_copy_attachments) THEN
2952 l_progress := '030';
2953
2954 fnd_attached_documents2_pkg.copy_attachments('PO_LINES',
2955 x_orig_po_line_id,
2956 '',
2957 '',
2958 '',
2959 '',
2960 'PO_LINES',
2961 x_po_line_record.po_line_id,
2962 '',
2963 '',
2964 '',
2965 '',
2966 fnd_global.user_id,
2967 fnd_global.login_id,
2968 '',
2969 '',
2970 '');
2971 -- COMMIT; < HTML Agreements R12>
2972 END IF;
2973 END IF;
2974
2975 -- Services FPJ Start
2976 -- After line insertion copy the price differentials related to the new line
2977 -- if any price differentials exist.
2978
2979 if x_to_doc_subtype = 'STANDARD' then
2980 l_entity_type := 'PO LINE';
2981 else
2982 l_entity_type := 'BLANKET LINE';
2983 end if;
2984
2985 l_progress := '040';
2986
2987 IF PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials(p_entity_type => l_entity_type,
2988 p_entity_id => x_orig_po_line_id) THEN
2989 l_progress := '050';
2990
2991 PO_PRICE_DIFFERENTIALS_PVT.copy_price_differentials (p_to_entity_id => x_po_line_record.po_line_id,
2992 p_to_entity_type => l_entity_type,
2993 p_from_entity_id => x_orig_po_line_id,
2994 p_from_entity_type => l_entity_type );
2995 -- COMMIT; < HTML Agreements R12>
2996
2997 END IF;
2998
2999 -- Services FPJ End
3000
3001 -- <Unified Catalog R12 Start>
3002 IF x_to_doc_subtype IN ('BLANKET', 'QUOTATION') THEN
3003 copy_attributes(p_orig_po_line_id => x_orig_po_line_id,
3004 p_new_po_line_id => x_po_line_record.po_line_id,
3005 p_line_num => x_po_line_record.line_num,
3006 p_online_report_id => x_online_report_id,
3007 x_sequence => x_sequence);
3008 END IF;
3009 -- <Unified Catalog R12 End>
3010
3011 --<Enhanced Pricing Start>
3012 copy_line_adjustments(p_orig_po_line_id => x_orig_po_line_id,
3013 p_new_po_header_id => x_po_header_id,
3014 p_new_po_line_id => x_po_line_record.po_line_id,
3015 p_line_num => x_po_line_record.line_num,
3016 p_online_report_id => x_online_report_id,
3017 x_sequence => x_sequence);
3018 --<Enhanced Pricing End>
3019 END IF; -- IF (x_internal_return_code = 0)
3020
3021 x_return_code := x_internal_return_code;
3022
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 x_return_code := -1;
3026 copydoc_sql_error('process_line', l_progress, sqlcode,
3027 x_online_report_id,
3028 x_sequence,
3029 x_po_line_record.line_num, 0, 0);
3030 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
3031 PO_DEBUG.debug_exc
3032 (p_log_head => g_module_prefix||'process_line',
3033 p_progress => l_progress);
3034 END IF;
3035 END process_line;
3036
3037 --< Shared Proc FPJ Start >
3038 --
3039 PROCEDURE process_shipment
3040 (
3041 p_action_code IN VARCHAR2,
3042 p_to_doc_subtype IN VARCHAR2,
3043 p_orig_line_location_id IN NUMBER,
3044 p_po_header_id IN NUMBER,
3045 p_po_line_id IN NUMBER,
3046 p_item_category_id IN NUMBER, --< Shared Proc FPJ >
3047 p_copy_attachments IN BOOLEAN,
3048 p_copy_price IN BOOLEAN,
3049 p_online_report_id IN NUMBER,
3050 p_line_num IN NUMBER,
3051 p_inv_org_id IN NUMBER,
3052 p_item_id IN NUMBER, -- Bug 3433867
3053 x_po_shipment_record IN OUT NOCOPY PO_LINE_LOCATIONS%ROWTYPE,
3054 x_sequence IN OUT NOCOPY NUMBER,
3055 x_return_code OUT NOCOPY NUMBER,
3056 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
3057 ) IS
3058
3059 l_progress VARCHAR2(4);
3060 l_internal_return_code NUMBER := NULL;
3061
3062 -- Bug: 1402128 Declare the variables
3063
3064 l_item_id number;
3065 l_inventory_organization_id number;
3066 l_planned_item_flag varchar2(1);
3067 l_outside_op_flag varchar2(1);
3068 l_outside_op_uom_type varchar2(25);
3069 l_invoice_close_tolerance number;
3070 l_receive_close_tolerance number;
3071 l_receipt_required_flag varchar2(1);
3072 l_stock_enabled_flag varchar2(1);
3073 l_item_status varchar2(1);
3074 l_internal_orderable varchar2(1);
3075 l_purchasing_enabled varchar2(1);
3076 l_inventory_asset_flag varchar2(1);
3077
3078 l_expense_accrual_code varchar2(100);
3079 l_accrue_on_receipt_flag varchar2(1);
3080 l_poll_receipt_required_flag varchar2(1);
3081
3082 --<INVCONV R12 START>
3083 l_secondary_default_ind MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND%TYPE ;
3084 l_grade_control_flag MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG%TYPE ;
3085 l_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
3086 --<INVCONV R12 END>
3087
3088 BEGIN
3089
3090 l_progress := '000';
3091 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3092 PO_DEBUG.debug_stmt
3093 (p_log_head => g_module_prefix||'process_shipment',
3094 p_token => 'invoked',
3095 p_message => 'action_code: ' ||p_action_code||' to_doc_subtype: '||
3096 p_to_doc_subtype||' header ID: '||p_po_header_id||
3097 ' line ID: '||p_po_line_id||' orig_ship ID: '||
3098 p_orig_line_location_id||' item_cat ID: '||p_item_category_id
3099 ||' inv_org ID: '||p_inv_org_id||' online_report ID: '||
3100 p_online_report_id);
3101 END IF;
3102
3103 l_progress := '020';
3104
3105 /* Functionality for PA->RFQ Copy : dreddy
3106 new parameter copy_price is added */
3107 po_copydoc_s4.validate_shipment
3108 (p_action_code => p_action_code,
3109 p_to_doc_subtype => p_to_doc_subtype,
3110 x_po_shipment_record => x_po_shipment_record,
3111 p_orig_line_location_id => p_orig_line_location_id,
3112 p_po_header_id => p_po_header_id,
3113 p_po_line_id => p_po_line_id,
3114 p_item_category_id => p_item_category_id, --< Shared Proc FPJ >
3115 p_item_id => p_item_id, -- Bug 3433867
3116 p_online_report_id => p_online_report_id,
3117 x_sequence => x_sequence,
3118 p_line_num => p_line_num,
3119 p_copy_price => p_copy_price,
3120 p_inv_org_id => p_inv_org_id, -- Bug 2761415
3121 x_return_code => l_internal_return_code,
3122 p_is_complex_work_po => p_is_complex_work_po); -- <Complex Work R12>
3123 --COMMIT; < HTML Agreements R12>
3124
3125 l_progress := '030';
3126
3127 /* Bug 1715229 : We need to initialize the accrue flag with whatever we got
3128 * back from the validate shipment procedure because if we do not go into the
3129 * if condition below , a null will be passed to the insert_shipment proc. */
3130
3131 l_accrue_on_receipt_flag := x_po_shipment_record.accrue_on_receipt_flag;
3132
3133 /* Bug: 1402128 get the accrue on receipt flag, since same code is used for
3134 copy of the PO to PO so we are restricting this for quotation only */
3135
3136 IF (p_action_code = 'QUOTATION') Then
3137
3138 l_progress := '040';
3139
3140 /* Get the item_id, accrue on receipt flag, and expense accrual code */
3141
3142 select receipt_required_flag
3143 into l_poll_receipt_required_flag
3144 from po_line_locations
3145 where line_location_id = p_orig_line_location_id;
3146
3147 l_progress := '050';
3148
3149 select expense_accrual_code into l_expense_accrual_code
3150 from po_system_parameters;
3151
3152 l_progress := '060';
3153
3154 select item_id into l_item_id from po_lines
3155 where po_line_id = p_po_line_id;
3156
3157
3158 if l_item_id is not NULL then
3159
3160 l_progress := '070';
3161
3162 -- Get the status of item and discard other values
3163
3164 po_items_sv2.get_item_details(l_item_id,
3165 p_inv_org_id,
3166 l_planned_item_flag,
3167 l_outside_op_flag,
3168 l_outside_op_uom_type,
3169 l_invoice_close_tolerance,
3170 l_receive_close_tolerance,
3171 l_receipt_required_flag,
3172 l_stock_enabled_flag,
3173 l_internal_orderable,
3174 l_purchasing_enabled,
3175 l_inventory_asset_flag,
3176 --<INVCONV R12 START>
3177 l_secondary_default_ind,
3178 l_grade_control_flag,
3179 l_secondary_unit_of_measure ) ;
3180 --<INVCONV R12 END>
3181
3182 IF (l_outside_op_flag = 'Y') THEN
3183 l_item_status := 'O'; -- Outside Processing
3184 ELSE
3185 IF (l_stock_enabled_flag = 'Y') THEN
3186 l_item_status := 'E'; -- Inventory
3187 ELSE
3188 l_item_status := 'D'; -- Expense
3189 END IF;
3190
3191 END IF;
3192
3193 -- Get the Accrue on receipt flag
3194
3195 IF (l_item_status = 'O') THEN
3196 l_accrue_on_receipt_flag := 'Y';
3197
3198 ELSIF (l_item_status = 'E') THEN
3199 l_accrue_on_receipt_flag := 'Y';
3200 ELSE
3201 IF (l_expense_accrual_code = 'PERIOD END') THEN
3202 l_accrue_on_receipt_flag := 'N';
3203 ELSE
3204 l_accrue_on_receipt_flag := l_poll_receipt_required_flag ;
3205 End IF;
3206 END IF;
3207
3208 ELSE
3209 IF (l_expense_accrual_code = 'PERIOD END') THEN
3210 l_accrue_on_receipt_flag := 'N';
3211 ELSE
3212 l_accrue_on_receipt_flag := l_poll_receipt_required_flag ;
3213 End IF;
3214
3215 End if; -- End of item_id condition
3216
3217 END IF; -- End if Action code condition
3218
3219 l_progress := '080';
3220
3221 -- Pass the accrue on receipt flag as a parameter
3222
3223 IF (l_internal_return_code = 0) THEN
3224 l_progress := '090';
3225 insert_shipment(x_po_shipment_record,
3226 p_online_report_id,
3227 x_sequence,
3228 p_line_num,
3229 l_accrue_on_receipt_flag, -- Bug: 1402128
3230 p_inv_org_id, -- Bug 2761415
3231 l_internal_return_code,
3232 p_is_complex_work_po, -- <Complex Work R12>
3233 p_orig_line_location_id); --<eTax Integration R12>
3234
3235 -- End bug fix : 1402128
3236
3237 -- COMMIT; < HTML Agreements R12>
3238
3239 IF (l_internal_return_code = 0) THEN
3240 IF (p_copy_attachments) THEN
3241 l_progress := '100';
3242 fnd_attached_documents2_pkg.copy_attachments('PO_SHIPMENTS',
3243 p_orig_line_location_id,
3244 '',
3245 '',
3246 '',
3247 '',
3248 'PO_SHIPMENTS',
3249 x_po_shipment_record.line_location_id,
3250 '',
3251 '',
3252 '',
3253 '',
3254 fnd_global.user_id,
3255 fnd_global.login_id,
3256 '',
3257 '',
3258 '');
3259 -- COMMIT; < HTML Agreements R12>
3260 END IF;
3261
3262 l_progress := '110';
3263
3264 -- Services FPJ Start
3265 -- After line insertion copy the price differentials related to the new shipment
3266 -- if the shipment has any price differentials
3267
3268 IF PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials(p_entity_type => 'PRICE BREAK',
3269 p_entity_id => p_orig_line_location_id) THEN
3270
3271 l_progress := '120';
3272
3273 PO_PRICE_DIFFERENTIALS_PVT.copy_price_differentials (p_to_entity_id => x_po_shipment_record.line_location_id,
3274 p_to_entity_type => 'PRICE BREAK',
3275 p_from_entity_id => p_orig_line_location_id,
3276 p_from_entity_type => 'PRICE BREAK' );
3277 -- COMMIT; < HTML Agreements R12>
3278
3279 END IF;
3280
3281 -- Services FPJ End
3282
3283 END IF;
3284 END IF;
3285
3286 x_return_code := l_internal_return_code;
3287
3288 EXCEPTION
3289 WHEN OTHERS THEN
3290 x_return_code := -1;
3291 copydoc_sql_error('process_shipment', l_progress, sqlcode,
3292 p_online_report_id,
3293 x_sequence,
3294 p_line_num, x_po_shipment_record.shipment_num, 0);
3295 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
3296 PO_DEBUG.debug_exc
3297 (p_log_head => g_module_prefix||'process_shipment',
3298 p_progress => l_progress);
3299 END IF;
3300 END process_shipment;
3301
3302
3303 --< Shared Proc FPJ > Added header, line, shipment records as parameters. Also
3304 -- added generate accounts boolean
3305 --<Encumbrance FPJ: add sob_id to param list>
3306 PROCEDURE process_distribution
3307 (
3308 p_action_code IN VARCHAR2,
3309 p_to_doc_subtype IN VARCHAR2,
3310 p_orig_po_distribution_id IN NUMBER,
3311 p_generate_new_accounts IN BOOLEAN,
3312 p_copy_attachments IN BOOLEAN,
3313 p_online_report_id IN NUMBER,
3314 p_po_header_rec IN PO_HEADERS%ROWTYPE,
3315 p_po_line_rec IN PO_LINES%ROWTYPE,
3316 p_po_shipment_rec IN PO_LINE_LOCATIONS%ROWTYPE,
3317 p_sob_id IN FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE,
3318 x_po_distribution_rec IN OUT NOCOPY PO_DISTRIBUTIONS%ROWTYPE,
3319 x_sequence IN OUT NOCOPY NUMBER,
3320 x_return_code OUT NOCOPY NUMBER
3321 )
3322 IS
3323
3324 l_progress VARCHAR2(4);
3325 l_internal_return_code NUMBER := NULL;
3326 l_return_status VARCHAR2(1);
3327
3328 --<ENCUMBRANCE FPJ>: use local vars for this to handle BPA case
3329 l_line_id PO_LINES_ALL.po_line_id%TYPE;
3330 l_line_num PO_LINES_ALL.line_num%TYPE;
3331 l_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
3332 l_shipment_num PO_LINE_LOCATIONS_ALL.shipment_num%TYPE;
3333 l_distribution_type PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
3334
3335 BEGIN
3336
3337 l_progress := '000';
3338
3339 --<ENCUMBRANCE FPJ START> Encumbered BPA dists do not have line/shipment
3340 IF nvl(p_po_header_rec.encumbrance_required_flag, 'N') = 'Y' THEN
3341 l_line_id := NULL;
3342 l_line_num := NULL;
3343 l_line_location_id := NULL;
3344 l_shipment_num := NULL;
3345 l_distribution_type := 'AGREEMENT';
3346
3347 ELSE
3348 l_line_id := p_po_line_rec.po_line_id;
3349 l_line_num := p_po_line_rec.line_num;
3350 l_line_location_id := p_po_shipment_rec.line_location_id;
3351 l_shipment_num := p_po_shipment_rec.shipment_num;
3352 l_distribution_type := p_po_shipment_rec.shipment_type;
3353
3354 END IF;
3355 --<ENCUMBRANCE FPJ END>
3356
3357 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3358 PO_DEBUG.debug_stmt
3359 (p_log_head => g_module_prefix||'process_distribution',
3360 p_token => 'invoked',
3361 p_message => 'action_code: '||p_action_code||' to_doc_subtype: '||
3362 p_to_doc_subtype||' header ID: '||
3363 p_po_header_rec.po_header_id||' line ID: '||
3364 l_line_id||' ship ID: '||
3365 l_line_location_id||' orig_dist ID: '||
3366 p_orig_po_distribution_id||' dist ID: '||
3367 x_po_distribution_rec.po_distribution_id||
3368 ' online_report ID: '||p_online_report_id);
3369 END IF;
3370
3371 IF (p_action_code = 'PO') THEN
3372
3373 --< Shared Proc FPJ Start >
3374 IF p_generate_new_accounts THEN
3375
3376 l_progress := '020';
3377
3378 PO_COPYDOC_S5.generate_accounts
3379 (x_return_status => l_return_status,
3380 p_online_report_id => p_online_report_id,
3381 p_po_header_rec => p_po_header_rec,
3382 p_po_line_rec => p_po_line_rec,
3383 p_po_shipment_rec => p_po_shipment_rec,
3384 x_po_distribution_rec => x_po_distribution_rec,
3385 x_sequence => x_sequence);
3386
3387 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
3388 l_progress := '030';
3389 RAISE FND_API.g_exc_error;
3390 END IF;
3391
3392 END IF;
3393 --< Shared Proc FPJ End >
3394
3395 l_progress := '040';
3396
3397 po_copydoc_s5.validate_distribution(p_action_code,
3398 p_to_doc_subtype,
3399 x_po_distribution_rec,
3400 p_po_header_rec.po_header_id,
3401 l_line_id,
3402 l_line_location_id,
3403 p_online_report_id,
3404 x_sequence,
3405 l_line_num,
3406 l_shipment_num,
3407 p_sob_id,
3408 l_internal_return_code);
3409 -- COMMIT; < HTML Agreements R12>
3410
3411 IF (l_internal_return_code = 0) THEN
3412
3413 l_progress := '050';
3414
3415 /* Bug#1562540: kagarwal
3416 ** Desc: When copying POs we should not be copying the Requisition reference
3417 ** in the target PO Distribution from the distribution of the source PO.
3418 ** Setting the Req reference columns as null in the source distribution record.
3419 */
3420 x_po_distribution_rec.req_distribution_id := NULL;
3421 x_po_distribution_rec.req_header_reference_num := NULL;
3422 x_po_distribution_rec.req_line_reference_num := NULL;
3423
3424 --<ENCUMBRANCE FPJ: add distribution type>
3425 x_po_distribution_rec.distribution_type := l_distribution_type;
3426
3427 insert_distribution(x_po_distribution_rec,
3428 p_online_report_id,
3429 x_sequence,
3430 l_line_num,
3431 l_shipment_num,
3432 l_internal_return_code);
3433 -- COMMIT; < HTML Agreements R12>
3434
3435 IF (l_internal_return_code = 0) THEN
3436 IF (p_copy_attachments) THEN
3437 l_progress := '060';
3438
3439 fnd_attached_documents2_pkg.copy_attachments('PO_DISTRIBUTIONS',
3440 p_orig_po_distribution_id,
3441 '',
3442 '',
3443 '',
3444 '',
3445 'PO_DISTRIBUTIONS',
3446 x_po_distribution_rec.po_distribution_id,
3447 '',
3448 '',
3449 '',
3450 '',
3451 fnd_global.user_id,
3452 fnd_global.login_id,
3453 '',
3454 '',
3455 '');
3456 -- COMMIT; < HTML Agreements R12>
3457 END IF;
3458 END IF;
3459 END IF;
3460 END IF;
3461
3462 x_return_code := l_internal_return_code;
3463
3464 EXCEPTION
3465 --< Shared Proc FPJ Start >
3466 WHEN FND_API.g_exc_error THEN
3467 x_return_code := -1;
3468 IF g_debug_stmt THEN
3469 PO_DEBUG.debug_stmt
3470 (p_log_head => g_module_prefix||'process_distribution',
3471 p_token => l_progress,
3472 p_message => 'FND_API.g_exc_error exception caught.');
3473 END IF;
3474 --< Shared Proc FPJ End >
3475 WHEN OTHERS THEN
3476 x_return_code := -1;
3477 copydoc_sql_error('process_distribution', l_progress, sqlcode,
3478 p_online_report_id,
3479 x_sequence,
3480 l_line_num,
3481 l_shipment_num,
3482 x_po_distribution_rec.distribution_num);
3483 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
3484 PO_DEBUG.debug_exc
3485 (p_log_head => g_module_prefix||'process_distribution',
3486 p_progress => l_progress);
3487 END IF;
3488 END process_distribution;
3489 --
3490 --< Shared Proc FPJ End >
3491
3492 /***************************************************************
3493 Copy header, line, shipment and distribution separately.
3494 for each, process and then copy
3495 ***************************************************************/
3496 --<HTML Agreements R12 Start>
3497 -- Added p_commit parameter so that we can control whether the api should
3498 -- commit and return the control or return without commiting the data.
3499 -- We are defaulting the value to Y so that if the code is called from FORMS
3500 -- we would mantain the existing behavior
3501 --<HTML Agreements R12 End>
3502 PROCEDURE copy_document(
3503 x_action_code IN VARCHAR2,
3504 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
3505 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
3506 x_copy_attachments IN BOOLEAN,
3507 x_copy_price IN BOOLEAN,
3508 x_from_po_header_id IN po_headers.po_header_id%TYPE,
3509 x_to_po_header_id OUT NOCOPY po_headers.po_header_id%TYPE,
3510 x_online_report_id OUT NOCOPY po_online_report_text.online_report_id%TYPE,
3511 x_to_segment1 IN OUT NOCOPY po_headers.segment1%TYPE,
3512 x_agent_id IN po_headers.agent_id%TYPE,
3513 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
3514 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
3515 x_wip_install_status IN VARCHAR2,
3516 x_return_code OUT NOCOPY NUMBER,
3517 x_copy_terms IN VARCHAR2, -- <FPJ CONTERMS>
3518 p_api_commit IN BOOLEAN, --<HTML Agreements R12>
3519 p_from_doc_type IN VARCHAR2 --<R12 eTax Integration>
3520 ) IS
3521
3522 COPYDOC_FATAL EXCEPTION;
3523
3524 x_po_header_record PO_HEADERS%ROWTYPE;
3525 x_po_line_record po_lines%ROWTYPE;
3526 x_po_shipment_record po_line_locations%ROWTYPE;
3527 x_po_distribution_record po_distributions%ROWTYPE;
3528
3529 x_orig_po_line_id po_lines.po_line_id%TYPE;
3530 x_orig_line_location_id po_line_locations.line_location_id%TYPE;
3531 x_orig_po_distribution_id po_distributions.po_distribution_id%TYPE;
3532
3533 x_document_num VARCHAR(100);
3534 x_line_num po_online_report_text.line_num%TYPE := NULL;
3535 x_shipment_num po_online_report_text.shipment_num%TYPE := NULL;
3536 x_distribution_num po_online_report_text.distribution_num%TYPE := NULL;
3537
3538 x_progress VARCHAR2(4);
3539 x_internal_return_code NUMBER;
3540
3541 x_sequence po_online_report_text.sequence%TYPE := 1;
3542 /* this is used only for BID quotation to calculate quantity at the line
3543 level from shipments. */
3544 x_line_quantity NUMBER;
3545 x_orig_quotation_class_code po_headers.quotation_class_code%TYPE := NULL;
3546
3547 --< Shared Proc FPJ Start >
3548 l_orig_txn_flow_header_id
3549 PO_LINE_LOCATIONS_ALL.transaction_flow_header_id%TYPE;
3550 l_generate_new_accounts BOOLEAN := FALSE;
3551 --< Shared Proc FPJ End >
3552 l_calling_program VARCHAR2(30); --<eTax Integration R12>
3553 l_return_status VARCHAR2(1); --<eTax Integration R12>
3554 l_msg_count NUMBER;
3555 l_msg_data VARCHAR2(2000);
3556
3557
3558 l_is_complex_work_po BOOLEAN; -- <Complex Work R12>
3559
3560 -- CLM CLIN/SLIN Changes
3561 l_cursor_group_line_id po_lines.po_line_id%TYPE;
3562 l_group_line_id po_lines.po_line_id%TYPE;
3563 l_line_num_display po_lines.line_num_display%TYPE;
3564 l_line_num po_lines.line_num%TYPE;
3565 l_cursor_line_id po_lines.po_line_id%TYPE;
3566 l_clm_line_id po_lines.po_line_id%TYPE;
3567
3568 l_template_id NUMBER;
3569 l_match_val VARCHAR2(2);
3570 l_clm_src_doc_id NUMBER;
3571 l_doc_number VARCHAR2(50);
3572 l_is_complex_finance_po VARCHAR2(10);
3573 l_is_clm_doc VARCHAR2(1) := 'N'; --<bug 14370174>
3574 d_module VARCHAR2(70) := 'po.plsql.PO_COPYDOC_S1.copy_document';
3575
3576 BEGIN
3577 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3578 PO_DEBUG.debug_stmt
3579 (p_log_head => g_module_prefix||'copy_document',
3580 p_token => 'invoked',
3581 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
3582 x_to_doc_subtype||' from header ID: '||x_from_po_header_id||
3583 ' to segment1: '||x_to_segment1||' agent ID: '||x_agent_id||
3584 ' sob ID: '||x_sob_id);
3585 END IF;
3586 -- Standard start of API savepoint
3587 SAVEPOINT COPY_DOCUMENT_PVT;
3588 x_to_po_header_id := NULL;
3589
3590 x_progress := '000';
3591 BEGIN
3592 SELECT po_online_report_text_s.nextval
3593 INTO x_online_report_id
3594 FROM SYS.DUAL;
3595 EXCEPTION
3596 WHEN OTHERS THEN
3597 x_online_report_id := NULL;
3598 po_copydoc_s1.COPYDOC_sql_error('copy_documents', x_progress, sqlcode,
3599 x_online_report_id,
3600 x_sequence,
3601 0, 0, 0);
3602 END;
3603
3604 x_progress := '010';
3605
3606 IF (x_action_code = NULL OR x_from_po_header_id = NULL) THEN
3607 RAISE COPYDOC_FATAL;
3608 END IF;
3609
3610 -- <Complex Work R12 Start.
3611 x_progress := '015';
3612
3613 l_is_complex_work_po :=
3614 PO_COMPLEX_WORK_PVT.is_complex_work_po(p_po_header_id => x_from_po_header_id);
3615
3616 -- Bug#5159320 : Added a condition for x_to_doc_subtype=CONTRACT
3617 -- to allow Complex work style CPAs also to be duplicated.
3618 IF (l_is_complex_work_po AND
3619 ( NOT(
3620 (x_action_code = 'PO')
3621 AND
3622 ((x_to_doc_subtype = 'STANDARD') OR (x_to_doc_subtype = 'CONTRACT')))
3623 ))
3624 THEN
3625 RAISE COPYDOC_FATAL;
3626 END IF;
3627
3628 -- <Complex Work R12 End>
3629
3630
3631 x_progress := '020';
3632 -- <R12 eTax Integration Start>
3633 -- Initialize global variable for tax. It will
3634 -- be used for insertion into header, line, shipment and distribution
3635 IF (x_to_doc_subtype IN ('STANDARD', 'PLANNED')) THEN
3636 IF (p_from_doc_type = 'QUOTATION') THEN
3637 g_tax_attribute_update_code := 'CREATE';
3638 l_calling_program :='COPY_QUOTE';
3639 ELSE
3640 g_tax_attribute_update_code := 'COPY_AND_CREATE';
3641 l_calling_program :='COPY_DOCUMENT';
3642 END IF;
3643 END IF;
3644 -- <R12 eTax Integration End>
3645 -- lpo, 06/04/98
3646 -- Note the following conditions for validate_(header|line|shipment|distribution)():
3647 -- Precondition : x_po_(header|line|shipment|distribution)_record contains
3648 -- the record we want to copy FROM.
3649 -- Postcondition: x_po_(header|line|shipment_location|distribution)_record contains
3650 -- the NEW record ready to be inserted.
3651 -- I intend to make most of the complexity reside in the validation phase; thus leaving
3652 -- the fetch and insert phases fairly simple.
3653 process_header(x_action_code,
3654 x_to_doc_subtype,
3655 x_to_global_flag, -- Global Agreements (FP-I)
3656 x_po_header_record,
3657 x_from_po_header_id,
3658 x_to_segment1,
3659 x_agent_id,
3660 x_sob_id,
3661 x_inv_org_id,
3662 x_copy_attachments,
3663 x_online_report_id,
3664 x_sequence,
3665 x_internal_return_code,
3666 x_copy_terms); -- <FPJ CONTERMS>
3667
3668 x_progress := '030';
3669
3670 IF (x_internal_return_code <> 0) THEN
3671 RAISE COPYDOC_FATAL;
3672 END IF;
3673
3674 /**** during processing header, new po_header_id and segment1 are created **/
3675 x_to_po_header_id := x_po_header_record.po_header_id;
3676 IF (x_to_segment1 IS NULL) THEN
3677 x_to_segment1 := x_po_header_record.segment1;
3678 END IF;
3679
3680
3681 COPY_UDA_ATTRS
3682 (
3683 p_level => 'HEADER'
3684 ,p_from_po_header_id => x_from_po_header_id
3685 ,p_from_pk_value => x_from_po_header_id
3686 ,p_to_pk_value => x_po_header_record.po_header_id
3687 ,x_po_header_record => x_po_header_record
3688 ,x_po_line_record => x_po_line_record
3689 ,x_po_shipment_record => x_po_shipment_record
3690 ,x_po_distribution_rec => x_po_distribution_record
3691 ,x_return_status => l_return_status
3692 ,x_msg_count => l_msg_count
3693 ,x_msg_data => l_msg_data
3694 );
3695
3696 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3697 RAISE COPYDOC_FATAL;
3698 END IF;
3699
3700 --update the clm_document_number
3701 IF po_clm_clo_util.getCLMStatus = 'Y' THEN
3702 SELECT UDA_TEMPLATE_ID, clm_source_document_id
3703 INTO l_template_id, l_clm_src_doc_id
3704 FROM po_headers_all
3705 WHERE PO_HEADER_ID = x_from_po_header_id;
3706
3707 l_match_val := po_doc_numbering_pkg.get_owned_by_issuing_org(l_clm_src_doc_id);
3708
3709 po_doc_numbering_pkg.DEFAULT_DOC_NUMBER_UDA(x_po_header_record.po_header_id,
3710 -1,
3711 l_template_id,
3712 'Base Document',
3713 l_match_val,
3714 'PO',
3715 l_doc_number,
3716 l_return_status);
3717
3718 IF l_doc_number IS NOT NULL THEN
3719 x_document_num := REPLACE(l_doc_number,'-','');
3720
3721 IF Length(x_document_num) > 20 THEN
3722 x_document_num := po_core_sv1.default_po_unique_identifier
3723 ('PO_HEADERS');
3724 END IF;
3725
3726 UPDATE po_headers_all
3727 SET clm_document_number = l_doc_number,
3728 segment1 = x_document_num
3729 WHERE po_header_id = x_po_header_record.po_header_id;
3730 END IF;
3731
3732 END IF;
3733
3734 -- UDA_COPY_HEADER
3735
3736 x_progress := '040';
3737
3738 --<ENCUMBRANCE FPJ START>
3739 --Encumbrance required flag in the x_po_header_record is set in the
3740 --validate_header procedure during the header processing
3741
3742 IF nvl(x_po_header_record.encumbrance_required_flag, 'N') = 'Y' THEN
3743
3744 --Since there will be a one-one mapping between header and distribution
3745 --for an Encumbered blanket agreement to be copied to another blanket
3746 --agreement, just after the completion of headers processing, we copy
3747 --the distribution from the encumbered blanket agreement to the new
3748 --blanket agreement
3749 x_progress := '042';
3750
3751 BEGIN
3752 SELECT * INTO x_po_distribution_record
3753 FROM PO_DISTRIBUTIONS POD
3754 WHERE POD.PO_HEADER_ID = x_from_po_header_id
3755 AND POD.distribution_type = 'AGREEMENT';
3756
3757 EXCEPTION
3758 WHEN TOO_MANY_ROWS THEN
3759 po_copydoc_s1.COPYDOC_sql_error('copy_documents', x_progress, sqlcode,
3760 x_online_report_id,
3761 x_sequence,
3762 0, 0, 1);
3763 x_internal_return_code := -1;
3764 RAISE COPYDOC_FATAL;
3765 END;
3766
3767
3768 x_progress := '044';
3769 x_orig_po_distribution_id := x_po_distribution_record.po_distribution_id;
3770
3771 process_distribution(
3772 p_action_code => x_action_code
3773 , p_to_doc_subtype => x_to_doc_subtype
3774 , p_orig_po_distribution_id => x_orig_po_distribution_id
3775 , p_generate_new_accounts => FALSE
3776 , p_copy_attachments => x_copy_attachments
3777 , p_online_report_id => x_online_report_id
3778 , p_po_header_rec => x_po_header_record
3779 , p_po_line_rec => NULL
3780 , p_po_shipment_rec => NULL
3781 , p_sob_id => x_sob_id
3782 , x_po_distribution_rec => x_po_distribution_record
3783 , x_sequence => x_sequence
3784 , x_return_code => x_internal_return_code);
3785
3786 x_progress := '046';
3787
3788 COPY_UDA_ATTRS
3789 (
3790 p_level => 'DISTRIBUTION'
3791 ,p_from_po_header_id => x_from_po_header_id
3792 ,p_from_pk_value => x_orig_po_distribution_id
3793 ,p_to_pk_value => x_po_distribution_record.po_distribution_id
3794 ,x_po_header_record => x_po_header_record
3795 ,x_po_line_record => x_po_line_record
3796 ,x_po_shipment_record => x_po_shipment_record
3797 ,x_po_distribution_rec => x_po_distribution_record
3798 ,x_return_status => l_return_status
3799 ,x_msg_count => l_msg_count
3800 ,x_msg_data => l_msg_data
3801 );
3802
3803 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3804 RAISE COPYDOC_FATAL;
3805 END IF;
3806
3807 -- UDA_COPY_DIST
3808
3809 END IF; --bug 3338216: changed this from an 'else' to 'end if'
3810 --<ENCUMBRANCE FPJ END>
3811
3812 x_progress := '048';
3813
3814 SELECT quotation_class_code
3815 INTO x_orig_quotation_class_code
3816 FROM po_headers
3817 WHERE po_header_id = x_from_po_header_id;
3818
3819 --<Bug 14370174>
3820 IF po_clm_clo_util.getCLMStatus = 'Y'
3821 THEN
3822 l_is_clm_doc := 'Y';
3823 END IF;
3824
3825 IF (PO_LOG.d_proc) THEN
3826 PO_LOG.proc_begin(d_module, 'l_is_clm_doc', l_is_clm_doc);
3827 END IF;
3828
3829 OPEN po_line_cursor(x_from_po_header_id);
3830
3831 /** for every line in the PO, fetch and store in x_po_line_record,
3832 then process line.
3833 Repeat above steps until all lines have been fetched and processed **/
3834 <<LINES>>
3835 LOOP
3836
3837 FETCH po_line_cursor INTO x_po_line_record;
3838 EXIT LINES WHEN po_line_cursor%NOTFOUND;
3839
3840 x_progress := '050';
3841
3842 x_orig_po_line_id := x_po_line_record.po_line_id;
3843 x_line_num := x_po_line_record.line_num;
3844
3845 /* Functionality for PA->RFQ Copy : dreddy
3846 new parameter copy_price is added */
3847 process_line(x_action_code,
3848 x_to_doc_subtype,
3849 x_po_line_record,
3850 x_orig_po_line_id,
3851 x_wip_install_status,
3852 x_sob_id,
3853 x_inv_org_id,
3854 x_to_po_header_id,
3855 x_from_po_header_id,
3856 x_copy_attachments,
3857 x_copy_price,
3858 x_online_report_id,
3859 x_sequence,
3860 x_internal_return_code,
3861 l_is_complex_work_po,
3862 l_calling_program, -- <Complex Work R12>
3863 l_is_clm_doc); --<Bug 14370174>
3864
3865 COPY_UDA_ATTRS
3866 (
3867 p_level => 'LINE'
3868 ,p_from_po_header_id => x_from_po_header_id
3869 ,p_from_pk_value => x_orig_po_line_id
3870 ,p_to_pk_value => x_po_line_record.po_line_id
3871 ,x_po_header_record => x_po_header_record
3872 ,x_po_line_record => x_po_line_record
3873 ,x_po_shipment_record => x_po_shipment_record
3874 ,x_po_distribution_rec => x_po_distribution_record
3875 ,x_return_status => l_return_status
3876 ,x_msg_count => l_msg_count
3877 ,x_msg_data => l_msg_data
3878 );
3879
3880 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3881 RAISE COPYDOC_FATAL;
3882 END IF;
3883 -- UDA_LINE
3884
3885 IF (x_internal_return_code = 0) THEN
3886
3887 OPEN po_shipment_cursor(x_orig_po_line_id);
3888 x_line_quantity := 0; -- initialize for each line
3889
3890 <<SHIPMENTS>>
3891 LOOP
3892
3893 FETCH po_shipment_cursor INTO x_po_shipment_record;
3894 EXIT SHIPMENTS WHEN po_shipment_cursor%NOTFOUND;
3895
3896 /** Bug 940844
3897 * bgu, July 21, 1999
3898 * For blanket agreement, only its shipment of type 'PRICE BREAK'
3899 * should be copied. We should not copy shipment of type 'BLANKET',
3900 * i.e., the shipment of BA's release.
3901 */
3902 IF (x_po_header_record.type_lookup_code='BLANKET'
3903 and (x_po_shipment_record.shipment_type='BLANKET')) then
3904 x_progress := '060';
3905 ELSE
3906 x_progress := '070';
3907
3908 x_orig_line_location_id := x_po_shipment_record.line_location_id;
3909 x_shipment_num := x_po_shipment_record.shipment_num;
3910
3911 --< Shared Proc FPJ Start >
3912 l_orig_txn_flow_header_id :=
3913 x_po_shipment_record.transaction_flow_header_id;
3914
3915 process_shipment(p_action_code => x_action_code,
3916 p_to_doc_subtype => x_to_doc_subtype,
3917 p_orig_line_location_id => x_orig_line_location_id,
3918 p_po_header_id => x_to_po_header_id,
3919 p_po_line_id => x_po_line_record.po_line_id,
3920 p_item_category_id => x_po_line_record.category_id,
3921 p_item_id => x_po_line_record.item_id,--Bug 3433867
3922 p_copy_attachments => x_copy_attachments,
3923 p_copy_price => x_copy_price,
3924 p_online_report_id => x_online_report_id,
3925 p_line_num => x_line_num,
3926 p_inv_org_id => x_inv_org_id,
3927 x_po_shipment_record => x_po_shipment_record,
3928 x_sequence => x_sequence,
3929 x_return_code => x_internal_return_code,
3930 p_is_complex_work_po => l_is_complex_work_po); -- <Complex Work R12>
3931
3932 -- Need to generate new accounts if the txn flow processed is now
3933 -- different than the original txn flow
3934 IF (NVL(l_orig_txn_flow_header_id, -99) <>
3935 NVL(x_po_shipment_record.transaction_flow_header_id, -99))
3936 THEN
3937 l_generate_new_accounts := TRUE;
3938 END IF;
3939 --< Shared Proc FPJ End >
3940
3941 -- Modified the condition for Bug 9783916 ,now this block is for BID, Std PO and Planned PO
3942 IF(x_internal_return_code = 0 AND (x_orig_quotation_class_code = 'BID' OR (x_action_code='PO' AND (x_to_doc_subtype = 'STANDARD' OR x_to_doc_subtype = 'PLANNED') AND NOT l_is_complex_work_po))) THEN
3943 x_line_quantity := x_line_quantity + nvl(x_po_shipment_record.quantity, 0);
3944 END IF;
3945
3946 --Bug 12547526
3947 IF l_is_complex_work_po THEN
3948
3949 SELECT Nvl(contract_financing_flag,'N')
3950 INTO l_is_complex_finance_po
3951 FROM po_doc_style_headers
3952 WHERE style_id = (SELECT style_id FROM po_headers_all WHERE po_header_id = x_from_po_header_id);
3953
3954
3955 IF( l_is_complex_finance_po LIKE 'Y' AND x_po_shipment_record.shipment_type LIKE 'STANDARD') THEN
3956
3957 x_line_quantity := x_line_quantity + Nvl(x_po_shipment_record.quantity,0); --Add the shipment quantities of STANDARD type for Complex Services (Finance) PO.
3958
3959 ELSIF l_is_complex_finance_po LIKE 'N' THEN
3960
3961 x_line_quantity := Nvl(x_po_shipment_record.quantity,0); -- Copy any one of the shipment quantities for Complex Services (Actuals) PO.
3962
3963 END IF ;
3964
3965
3966 END IF ;
3967
3968 --Enf of Bug 12547526
3969
3970 COPY_UDA_ATTRS
3971 (
3972 p_level => 'SHIPMENT'
3973 ,p_from_po_header_id => x_from_po_header_id
3974 ,p_from_pk_value => x_orig_line_location_id
3975 ,p_to_pk_value => x_po_shipment_record.line_location_id
3976 ,x_po_header_record => x_po_header_record
3977 ,x_po_line_record => x_po_line_record
3978 ,x_po_shipment_record => x_po_shipment_record
3979 ,x_po_distribution_rec => x_po_distribution_record
3980 ,x_return_status => l_return_status
3981 ,x_msg_count => l_msg_count
3982 ,x_msg_data => l_msg_data
3983 );
3984
3985 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3986 RAISE COPYDOC_FATAL;
3987 END IF;
3988
3989 -- UDA_COPY_SHIP
3990
3991 IF (x_internal_return_code = 0) THEN
3992 OPEN po_distribution_cursor(x_orig_line_location_id);
3993
3994 <<DISTRIBUTIONS>>
3995 LOOP
3996
3997 FETCH po_distribution_cursor INTO x_po_distribution_record;
3998 EXIT DISTRIBUTIONS WHEN po_distribution_cursor%NOTFOUND;
3999
4000 x_progress := '080';
4001
4002 x_orig_po_distribution_id := x_po_distribution_record.po_distribution_id;
4003 x_distribution_num := x_po_distribution_record.distribution_num;
4004
4005 --< Shared Proc FPJ Start >
4006 process_distribution
4007 (p_action_code => x_action_code,
4008 p_to_doc_subtype => x_to_doc_subtype,
4009 p_orig_po_distribution_id => x_orig_po_distribution_id,
4010 p_generate_new_accounts => l_generate_new_accounts,
4011 p_copy_attachments => x_copy_attachments,
4012 p_online_report_id => x_online_report_id,
4013 p_po_header_rec => x_po_header_record,
4014 p_po_line_rec => x_po_line_record,
4015 p_po_shipment_rec => x_po_shipment_record,
4016 p_sob_id => x_sob_id,
4017 x_po_distribution_rec => x_po_distribution_record,
4018 x_sequence => x_sequence,
4019 x_return_code => x_internal_return_code);
4020 --< Shared Proc FPJ End >
4021
4022 COPY_UDA_ATTRS
4023 (
4024 p_level => 'DISTRIBUTION'
4025 ,p_from_po_header_id => x_from_po_header_id
4026 ,p_from_pk_value => x_orig_po_distribution_id
4027 ,p_to_pk_value => x_po_distribution_record.po_distribution_id
4028 ,x_po_header_record => x_po_header_record
4029 ,x_po_line_record => x_po_line_record
4030 ,x_po_shipment_record => x_po_shipment_record
4031 ,x_po_distribution_rec => x_po_distribution_record
4032 ,x_return_status => l_return_status
4033 ,x_msg_count => l_msg_count
4034 ,x_msg_data => l_msg_data
4035 );
4036 -- UDA_COPY_DIST
4037
4038 END LOOP DISTRIBUTIONS;
4039 CLOSE po_distribution_cursor;
4040 END IF;
4041 END IF; -- For testing whether the shipment is for a blanket release
4042 END LOOP SHIPMENTS;
4043 CLOSE po_shipment_cursor;
4044
4045 /* got total quantity from all shipments under the line */
4046 -- Modified the condition for Bug 9783916 ,now this block is for BID, Std PO and Planned PO
4047 IF (x_orig_quotation_class_code = 'BID' OR (x_action_code='PO' AND (x_to_doc_subtype = 'STANDARD' OR x_to_doc_subtype = 'PLANNED'))) THEN
4048 x_progress := '090';
4049
4050 UPDATE PO_LINES
4051 SET quantity = x_line_quantity
4052 WHERE po_header_id = x_to_po_header_id
4053 AND po_line_id = x_po_line_record.po_line_id;
4054
4055 -- COMMIT; < HTML Agreements R12>
4056 END IF;
4057
4058 END IF;
4059
4060 END LOOP LINES;
4061 CLOSE po_line_cursor;
4062
4063
4064 -- CLM Phase 4 - Elins project
4065 -- Copy the exhibit details from source document.
4066 INSERT INTO po_exhibit_details
4067 (
4068 po_exhibit_details_id,
4069 exhibit_name,
4070 exhibit_description,
4071 is_cdrl,
4072 po_header_id,
4073 reference_line_id,
4074 revision_num,
4075 last_update_date,
4076 last_updated_by,
4077 creation_date,
4078 created_by,
4079 last_update_login
4080 )
4081 SELECT
4082 po_exhibit_details_s.nextval,
4083 exhibit_name,
4084 exhibit_description,
4085 is_cdrl,
4086 x_to_po_header_id,
4087 (SELECT nw.po_line_id FROM po_lines nw ,po_lines od
4088 WHERE nw.po_header_id = x_to_po_header_id
4089 AND nw.line_num = od.line_num
4090 AND nw.line_num_display = od.line_num_display
4091 AND od.po_header_id = x_from_po_header_id
4092 AND od.po_line_id = reference_line_id
4093 ),
4094 revision_num,
4095 SYSDATE,
4096 fnd_global.user_id,
4097 SYSDATE,
4098 fnd_global.user_id,
4099 fnd_global.login_id
4100 FROM
4101 po_exhibit_details
4102 WHERE
4103 po_header_id = x_from_po_header_id;
4104
4105 -- CLM CLIN/SLIN Changes Start
4106 OPEN clm_po_line_cursor(x_to_po_header_id);
4107
4108 <<CLMLINES>>
4109 LOOP
4110 FETCH clm_po_line_cursor INTO l_cursor_line_id;
4111 EXIT CLMLINES WHEN clm_po_line_cursor%NOTFOUND;
4112
4113 SELECT line_num_display, line_num
4114 INTO l_line_num_display, l_line_num
4115 FROM po_lines
4116 WHERE po_header_id = x_from_po_header_id AND
4117 po_line_id = l_cursor_line_id AND ROWNUM =1 ;
4118
4119 SELECT po_line_id
4120 INTO l_clm_line_id
4121 FROM po_lines
4122 WHERE po_header_id = x_to_po_header_id AND
4123 line_num = l_line_num AND
4124 line_num_display = l_line_num_display AND ROWNUM =1 ;
4125
4126 UPDATE PO_LINES
4127 SET group_line_id = Decode(group_line_id, l_cursor_line_id, l_clm_line_id, group_line_id),
4128 clm_base_line_num = Decode(clm_base_line_num, l_cursor_line_id, l_clm_line_id, clm_base_line_num)
4129 WHERE po_header_id = x_to_po_header_id AND
4130 ((group_line_id IS NOT NULL AND
4131 group_line_id = l_cursor_line_id) OR
4132 (clm_base_line_num IS NOT NULL AND
4133 clm_base_line_num = l_cursor_line_id));
4134
4135 END LOOP CLMLINES;
4136 CLOSE clm_po_line_cursor;
4137
4138 OPEN clm_po_distribution_cursor(x_to_po_header_id);
4139
4140 <<CLMDISTRIBUTIONS>>
4141 LOOP
4142 FETCH clm_po_distribution_cursor INTO l_cursor_group_line_id;
4143 EXIT CLMDISTRIBUTIONS WHEN clm_po_distribution_cursor%NOTFOUND;
4144
4145 SELECT line_num_display, line_num
4146 INTO l_line_num_display, l_line_num
4147 FROM po_lines
4148 WHERE po_header_id = x_from_po_header_id AND
4149 po_line_id = l_cursor_group_line_id AND ROWNUM =1 ;
4150
4151 SELECT po_line_id
4152 INTO l_group_line_id
4153 FROM po_lines
4154 WHERE po_header_id = x_to_po_header_id AND
4155 line_num = l_line_num AND
4156 line_num_display = l_line_num_display AND ROWNUM =1 ;
4157
4158
4159 UPDATE PO_DISTRIBUTIONS
4160 SET group_line_id = l_group_line_id
4161 WHERE po_header_id = x_to_po_header_id AND
4162 group_line_id IS NOT NULL AND
4163 group_line_id = l_cursor_group_line_id;
4164
4165 END LOOP CLMDISTRIBUTIONS;
4166 CLOSE clm_po_distribution_cursor;
4167 -- CLM CLIN/SLIN Changes End
4168
4169 --Call PO Tax API to calculate tax
4170 PO_TAX_INTERFACE_PVT.calculate_tax(
4171 p_po_header_id_tbl => PO_TBL_NUMBER(x_to_po_header_id),
4172 p_po_release_id_tbl => PO_TBL_NUMBER(),
4173 p_calling_program =>l_calling_program,
4174 x_return_status => l_return_status);
4175 --<eTax Integration R12 End>
4176 x_progress := '100';
4177
4178 IF (x_sequence > 1) THEN
4179 --< Shared Proc FPJ > Corrected calculation of return code so that it will
4180 -- be negative if any records were inserted into online report table.
4181 x_return_code := 1 - x_sequence;
4182 ELSE
4183 x_return_code := 0;
4184 --< HTML Agreements R12 Start>
4185 x_progress := '110';
4186 -- We only commit if p_commit is true
4187 IF p_api_commit THEN
4188 COMMIT WORK;
4189 END IF;
4190 --< HTML Agreements R12 End>
4191 END IF;
4192
4193 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
4194 PO_DEBUG.debug_stmt
4195 (p_log_head => g_module_prefix||'copy_document',
4196 p_token => 'end',
4197 p_message => 'x_return_code = '||x_return_code||
4198 ' x_sequence = '||x_sequence);
4199 END IF;
4200
4201 EXCEPTION
4202 WHEN COPYDOC_FATAL THEN
4203 ROLLBACK TO SAVEPOINT COPY_DOCUMENT_PVT; --< HTML Agreements R12>
4204 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
4205 PO_DEBUG.debug_stmt
4206 (p_log_head => g_module_prefix||'copy_document',
4207 p_token => x_progress,
4208 p_message => 'COPYDOC_FATAL exception caught.');
4209 END IF;
4210 handle_fatal(x_return_code);
4211 RAISE;
4212 WHEN OTHERS THEN
4213 ROLLBACK TO SAVEPOINT COPY_DOCUMENT_PVT; --< HTML Agreements R12>
4214 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
4215 PO_DEBUG.debug_exc
4216 (p_log_head => g_module_prefix||'copy_document',
4217 p_progress => x_progress);
4218 END IF;
4219 copydoc_sql_error('copy_document', x_progress, sqlcode,
4220 x_online_report_id,
4221 x_sequence,
4222 x_line_num,
4223 x_shipment_num,
4224 x_distribution_num);
4225 -- COMMIT; < HTML Agreements R12>
4226 handle_fatal(x_return_code);
4227 RAISE;
4228 END copy_document;
4229
4230 -- Bug 2744363 START
4231 /**
4232 * Function: po_is_dropship
4233 * Requires: none
4234 * Modifies: none
4235 * Effects: Checks whether the given PO is drop ship
4236 * Returns: TRUE if any of the shipments in the given PO are drop ship,
4237 * FALSE otherwise.
4238 **/
4239 FUNCTION po_is_dropship (
4240 p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE
4241 ) RETURN BOOLEAN IS
4242
4243 CURSOR l_po_shipment_csr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) IS
4244 SELECT line_location_id
4245 FROM PO_LINE_LOCATIONS
4246 WHERE po_header_id = p_po_header_id
4247 AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET');
4248
4249 l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
4250
4251 BEGIN
4252 OPEN l_po_shipment_csr(p_po_header_id);
4253
4254 LOOP
4255 FETCH l_po_shipment_csr INTO l_line_location_id;
4256 EXIT WHEN l_po_shipment_csr%NOTFOUND;
4257
4258 IF (OE_DROP_SHIP_GRP.po_line_location_is_drop_ship(l_line_location_id)
4259 IS NOT NULL) THEN
4260 CLOSE l_po_shipment_csr;
4261 RETURN TRUE;
4262 END IF;
4263 END LOOP;
4264
4265 CLOSE l_po_shipment_csr;
4266 RETURN FALSE;
4267 EXCEPTION
4268 WHEN OTHERS THEN
4269 CLOSE l_po_shipment_csr;
4270 RAISE;
4271 END;
4272 -- Bug 2744363 END
4273
4274 -- <CONFIG_ID FPJ START>
4275
4276 ----------------------------------------------------------------------------
4277 --Start of Comments
4278 --Name: po_has_config_id
4279 --Pre-reqs:
4280 -- None
4281 --Modifies:
4282 -- None
4283 --Locks:
4284 -- None
4285 --Function:
4286 -- Checks whether any lines on the given PO have a config ID.
4287 --Parameters:
4288 --IN:
4289 --p_po_header_id
4290 -- header ID of the PO to check
4291 --Returns:
4292 -- TRUE if any of the lines on the given PO have a config ID,
4293 -- FALSE otherwise.
4294 --Testing:
4295 -- None
4296 --End of Comments
4297 ----------------------------------------------------------------------------
4298
4299 FUNCTION po_has_config_id(
4300 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
4301 ) RETURN BOOLEAN IS
4302
4303 l_has_config_id NUMBER;
4304
4305 BEGIN
4306
4307 SELECT count(*) INTO l_has_config_id
4308 FROM po_lines
4309 WHERE po_header_id = p_po_header_id AND
4310 supplier_ref_number IS NOT NULL;
4311
4312 IF (l_has_config_id = 0) THEN
4313 RETURN FALSE;
4314 ELSE
4315 RETURN TRUE;
4316 END IF;
4317
4318 EXCEPTION
4319
4320 WHEN OTHERS THEN
4321 PO_MESSAGE_S.sql_error('PO_HAS_CONFIG_ID', '000', sqlcode);
4322 RAISE;
4323
4324 END po_has_config_id;
4325
4326 ----------------------------------------------------------------------------
4327 --Start of Comments
4328 --Name: req_has_config_id
4329 --Pre-reqs:
4330 -- None
4331 --Modifies:
4332 -- None
4333 --Locks:
4334 -- None
4335 --Function:
4336 -- Checks whether any lines on the given requisition have a config ID.
4337 --Parameters:
4338 --IN:
4339 --p_requisition_header_id
4340 -- header ID of the requisition to check
4341 --Returns: TRUE if any of the lines on the given requisition have a config ID,
4342 -- FALSE otherwise.
4343 --Testing:
4344 -- None
4345 --End of Comments
4346 ----------------------------------------------------------------------------
4347
4348 FUNCTION req_has_config_id(
4349 p_requisition_header_id IN PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE
4350 ) RETURN BOOLEAN IS
4351
4352 l_has_config_id NUMBER;
4353
4354 BEGIN
4355
4356 SELECT count(*) INTO l_has_config_id
4357 FROM po_requisition_lines
4358 WHERE requisition_header_id = p_requisition_header_id AND
4359 supplier_ref_number IS NOT NULL;
4360
4361 IF (l_has_config_id = 0) THEN
4362 RETURN FALSE;
4363 ELSE
4364 RETURN TRUE;
4365 END IF;
4366
4367 EXCEPTION
4368
4369 WHEN OTHERS THEN
4370 PO_MESSAGE_S.sql_error('REQ_HAS_CONFIG_ID', '000', sqlcode);
4371 RAISE;
4372
4373 END req_has_config_id;
4374
4375 -- <CONFIG_ID FPJ END>
4376 --<HTML Agreements R12 Start>
4377 ------------------------------------------------------------------------
4378 --Start of Comments
4379 --Name: val_params_and_duplicate_doc
4380 --Pre-reqs:
4381 -- None.
4382 --Modifies:
4383 -- None.
4384 --Locks:
4385 -- None Directly.
4386 --Function:
4387 -- It will act as a wrapper to the copy_document procedure. It will get
4388 -- all the required parameters for invoking copy_document procedure and
4389 -- invoke it. This procedure only supports PO/PA copy
4390 --IN:
4391 -- p_po_header_id
4392 -- Document Header Id of The Existing Document to be copied
4393 --p_copy_attachment
4394 -- Flag determining whether attachment can be copied or not
4395 --p_copy_terms
4396 -- Flag determining whether terms can be copied or not
4397 --OUT:
4398 --x_new_po_header_id
4399 -- Document Header Id of The New Document created
4400 --x_errmsg_code
4401 -- Contains the message name of the error to be shown in case of an
4402 -- expected error due to validation failure
4403 --x_message_type
4404 -- MessageType for online report message if any inserted to the table
4405 -- while procedure execution
4406 --x_text_line
4407 -- Message if any inserted to the online_report_text table while procedure
4408 -- execution.
4409 --x_return_status
4410 -- Return Status of API .
4411 --x_exception_msg
4412 -- Message in case of Unhandled Exception.
4413 --IN OUT:
4414 --x_new_segment1
4415 -- In case Numbering is manual user provides a value else if automatic
4416 -- the generated value is returned to the user
4417 --Testing:
4418 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4419 --End of Comments
4420 ----------------------------------------------------------------------------
4421
4422 procedure val_params_and_duplicate_doc( p_po_header_id IN NUMBER
4423 ,p_copy_attachment IN VARCHAR2
4424 ,p_copy_terms IN VARCHAR2
4425 ,x_new_segment1 IN OUT NOCOPY VARCHAR2
4426 ,x_new_po_header_id OUT NOCOPY NUMBER
4427 ,x_errmsg_code OUT NOCOPY VARCHAR2
4428 ,x_message_type OUT NOCOPY VARCHAR2
4429 ,x_text_line OUT NOCOPY VARCHAR2
4430 ,x_return_status OUT NOCOPY VARCHAR2
4431 ,x_exception_msg OUT NOCOPY VARCHAR2)
4432 IS
4433 l_doc_org_id PO_HEADERS_ALL.ORG_ID%type;
4434 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%type;
4435 l_global_agreement_flag PO_HEADERS_ALL.global_agreement_flag%type;
4436 l_inv_org_id FINANCIALS_SYSTEM_PARAMS_ALL.inventory_organization_id%type;
4437 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%type;
4438 l_return_code NUMBER;
4439 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%type;
4440 d_pos NUMBER;
4441 d_module VARCHAR2(70) := 'po.plsql.PO_COPYDOC_S1.VAL_PARAMS_AND_DUPLICATE_DOC';
4442 d_log_msg VARCHAR2(200);
4443 BEGIN
4444 --Initialise the variables
4445 d_pos := 0;
4446 IF (PO_LOG.d_proc) THEN
4447 PO_LOG.proc_begin(d_module);
4448 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
4449 PO_LOG.proc_begin(d_module, 'p_copy_attachment', p_copy_attachment);
4450 PO_LOG.proc_begin(d_module, 'p_copy_terms', p_copy_terms);
4451 PO_LOG.proc_begin(d_module, 'x_new_segment1', x_new_segment1);
4452 END IF;
4453 d_pos := 10;
4454 x_return_status := FND_API.g_ret_sts_success;
4455 x_errmsg_code := NULL;
4456 l_return_code := NULL;
4457 l_online_report_id := NULL;
4458 x_exception_msg := NULL;
4459
4460 d_pos := 15;
4461 --Get the required header attributes for the PO/PA
4462 SELECT POH.ORG_ID,POH.TYPE_LOOKUP_CODE,
4463 POH.GLOBAL_AGREEMENT_FLAG,
4464 FSP.INVENTORY_ORGANIZATION_ID,
4465 FSP.SET_OF_BOOKS_ID
4466 INTO l_doc_org_id, l_type_lookup_code,
4467 l_global_agreement_flag,
4468 l_inv_org_id, l_sob_id
4469 FROM po_headers_all POH, financials_system_params_all FSP
4470 WHERE po_header_id = p_po_header_id
4471 AND poh.org_id = fsp.org_id;
4472
4473 IF PO_LOG.d_stmt THEN
4474 PO_LOG.stmt(d_module,d_pos,'l_doc_org_id',l_doc_org_id);
4475 PO_LOG.stmt(d_module,d_pos,'l_type_lookup_code',l_type_lookup_code);
4476 PO_LOG.stmt(d_module,d_pos,'l_global_agreement_flag',l_global_agreement_flag);
4477 PO_LOG.stmt(d_module,d_pos,'l_inv_org_id',l_inv_org_id);
4478 PO_LOG.stmt(d_module,d_pos,'l_sob_id',l_sob_id);
4479 END IF;
4480
4481 d_pos := 20;
4482 --Check if the PO Number Provided by the user is unique
4483 IF (x_new_segment1 IS NOT NULL) THEN
4484 d_pos := 25;
4485 IF(NOT PO_CORE_S.Check_Doc_Number_Unique(x_new_segment1,
4486 l_doc_org_id,
4487 l_type_lookup_code)) THEN
4488 x_errmsg_code := 'PO_ALL_ENTER_UNIQUE_VAL';
4489 IF PO_LOG.d_exc THEN
4490 PO_LOG.exc(d_module,d_pos,'Segment1 value not unique');
4491 END IF;
4492 RAISE FND_API.g_exc_error;
4493 END IF;
4494 END IF;
4495 d_pos := 30;
4496 --Check if the PO is a drop ship PO
4497 IF(PO_COPYDOC_S1.po_is_dropship(p_po_header_id)) THEN
4498 d_pos := 35;
4499 x_errmsg_code := 'PO_NOT_SUPPORT_COPY_DROPSHIPPO';
4500 IF PO_LOG.d_exc THEN
4501 PO_LOG.exc(d_module,d_pos,'Duplicate Document not supported for DropShip PO');
4502 END IF;
4503 RAISE FND_API.g_exc_error;
4504 END IF;
4505 d_pos := 40;
4506 --Check if the PO has a config id associated with it
4507 IF(PO_COPYDOC_S1.po_has_config_id(p_po_header_id)) THEN
4508 d_pos := 45;
4509 x_errmsg_code := 'PO_CANNOT_COPY_CONFIG_ID_DOC';
4510 IF PO_LOG.d_exc THEN
4511 PO_LOG.exc(d_module,d_pos,'Duplicate Document not supported for PO with Config ID');
4512 END IF;
4513 RAISE FND_API.g_exc_error;
4514 END IF;
4515 d_pos := 50;
4516 --If validations go thru fine invoke the copy_document procedure
4517 IF PO_LOG.d_event THEN
4518 PO_LOG.event(d_module,d_pos,'Invoking PO_COPYDOC_S1.Copy Document Procedure');
4519 END IF;
4520
4521 copy_document (
4522 x_action_code => 'PO',
4523 x_to_doc_subtype => l_type_lookup_code,
4524 x_to_global_flag => l_global_agreement_flag,
4525 x_copy_attachments => PO_CORE_S.flag_to_boolean(p_copy_attachment),
4526 x_copy_price => PO_CORE_S.flag_to_boolean('N'),
4527 x_from_po_header_id => p_po_header_id,
4528 x_to_po_header_id => x_new_po_header_id,
4529 x_online_report_id => l_online_report_id,
4530 x_to_segment1 => x_new_segment1,
4531 x_agent_id => fnd_global.employee_id,
4532 x_sob_id => l_sob_id,
4533 x_inv_org_id => l_inv_org_id,
4534 x_wip_install_status => PO_CORE_S.get_product_install_status('WIP'),
4535 x_return_code => l_return_code,
4536 x_copy_terms => p_copy_terms,
4537 p_api_commit => FALSE); --Do not Commit
4538
4539 IF PO_LOG.d_event THEN
4540 PO_LOG.event(d_module,d_pos,'PO_COPYDOC_S1.Copy Document Procedure call completed');
4541 END IF;
4542 d_pos := 60;
4543 --If online_report_id is not null get the message
4544 IF (l_online_report_id is NOT NULL) THEN
4545 d_pos := 65;
4546 PO_COPYDOC_S1.ret_and_del_online_report_rec( l_online_report_id
4547 ,x_message_type
4548 ,x_text_line);
4549 IF PO_LOG.d_stmt THEN
4550 PO_LOG.stmt(d_module,d_pos,'l_online_report_id',l_online_report_id);
4551 PO_LOG.stmt(d_module,d_pos,'x_message_type',x_message_type);
4552 PO_LOG.stmt(d_module,d_pos,'x_text_line',x_text_line);
4553 END IF;
4554 END IF;
4555 d_pos := 70;
4556 IF(l_return_code < 0) THEN
4557 d_pos := 75;
4558 RAISE FND_API.g_exc_unexpected_error;
4559 END IF;
4560
4561 IF PO_LOG.d_proc THEN
4562 PO_LOG.proc_end(d_module,'x_new_segment1',x_new_segment1);
4563 PO_LOG.proc_end(d_module,'x_new_po_header_id',x_new_po_header_id);
4564 PO_LOG.proc_end(d_module,'x_errmsg_code',x_errmsg_code);
4565 PO_LOG.proc_end(d_module,'x_message_type',x_message_type);
4566 PO_LOG.proc_end(d_module,'x_text_line',x_text_line);
4567 PO_LOG.proc_end(d_module,'x_return_status',x_return_status);
4568 PO_LOG.proc_end(d_module,'x_exception_msg',x_exception_msg);
4569 PO_LOG.proc_end(d_module);
4570 END IF;
4571 EXCEPTION
4572 WHEN FND_API.g_exc_error THEN
4573 x_return_status := FND_API.g_ret_sts_error;
4574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4575 x_return_status := FND_API.g_ret_sts_unexp_error;
4576 IF(x_text_line is not null) THEN
4577 x_exception_msg := x_text_line;
4578 x_return_status := FND_API.g_ret_sts_error; /*Bug:13077836 */
4579 ELSE
4580 x_exception_msg := 'Unexpected Error in ' || d_module||'-'||d_pos;
4581 END IF;
4582 IF PO_LOG.d_exc THEN
4583 PO_LOG.exc(d_module,d_pos,x_exception_msg);
4584 END IF;
4585 WHEN OTHERS THEN
4586 x_return_status := FND_API.g_ret_sts_unexp_error;
4587 IF (l_online_report_id is NOT NULL) THEN
4588 PO_COPYDOC_S1.ret_and_del_online_report_rec( l_online_report_id
4589 ,x_message_type
4590 ,x_text_line);
4591 END IF;
4592 IF(x_text_line is not null) THEN
4593 x_exception_msg := x_text_line;
4594 ELSE
4595 x_exception_msg := 'Unhandled Exception in ' || d_module||'-'||d_pos;
4596 END IF;
4597 IF PO_LOG.d_exc THEN
4598 PO_LOG.exc(d_module,d_pos,x_exception_msg);
4599 END IF;
4600 END val_params_and_duplicate_doc;
4601 --<HTML Agreements R12 End>
4602
4603 ------------------------------------------------------------------------
4604 --Start of Comments
4605 --Name: COPY_UDA_ATTRS
4606 --Pre-reqs:
4607 -- None.
4608 --Modifies:
4609 -- None.
4610 --Locks:
4611 -- None Directly.
4612 --Function:
4613 -- It will act as a wrapper to the copy_uda_attrs in po_uda_data_util procedure. It will get
4614 -- all the required parameters for invoking copy_uda_attrs procedure and
4615 -- invoke it. This procedure only supports PO/PA copy
4616 --IN:
4617 -- p_level
4618 -- Takes in the level (HEADER, LINE, SHIPMENT, DISTRIBUTION)
4619 -- p_from_pk_value
4620 -- Takes in the appropriate id (PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, PO_DISTRIBUTION_ID
4621 -- p_to_pk_value
4622 -- Takes in the appropriate id (to ids )(PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID, PO_DISTRIBUTION_ID
4623 --OUT:
4624 --x_return_status
4625 -- Return Status of API .
4626 --x_msg_count
4627 -- Return error count of API .
4628 --x_msg_data
4629 -- Return message of API .
4630
4631
4632 --End of Comments
4633 ----------------------------------------------------------------------------
4634 PROCEDURE COPY_UDA_ATTRS
4635 (
4636 p_level IN VARCHAR2
4637 ,p_from_po_header_id IN NUMBER
4638 ,p_from_pk_value IN NUMBER
4639 ,p_to_pk_value IN NUMBER
4640 ,x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE
4641 ,x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE
4642 ,x_po_shipment_record IN OUT NOCOPY PO_LINE_LOCATIONS%ROWTYPE
4643 ,x_po_distribution_rec IN OUT NOCOPY PO_DISTRIBUTIONS%ROWTYPE
4644 ,x_return_status OUT NOCOPY VARCHAR2
4645 ,x_msg_count OUT NOCOPY NUMBER
4646 ,x_msg_data OUT NOCOPY VARCHAR2
4647 )
4648 IS
4649 l_template_id NUMBER;
4650 l_functional_area_code VARCHAR2(500);
4651 l_document_type_code VARCHAR2(500);
4652 l_document_style_id NUMBER;
4653 l_enabled_flag VARCHAR2(5);
4654 l_errorcode VARCHAR2(50);
4655 l_progress VARCHAR2(50);
4656 l_ags_table PO_TBL_VARCHAR30;
4657 --l_doc_num_ag_name VARCHAR2(30); --Bug 13482355
4658
4659 l_api_name CONSTANT varchar2(30) := 'COPY_UDA_ATTRS';
4660 l_log_head CONSTANT VARCHAR2(100) := 'po_copydoc_s1';
4661 l_pk_col_name VARCHAR2(200);
4662
4663 l_clm_delivery_event_code po_lines_all.clm_delivery_event_code%type;
4664
4665 BEGIN
4666 l_progress := '8880';
4667 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Starting code for UDA Submission check');
4668 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'PK VALUE IS :' || p_to_pk_value);
4669
4670 IF p_level = 'HEADER' THEN
4671
4672 IF PO_CLM_CLO_UTIL.Isclminstalled THEN
4673
4674 UPDATE PO_HEADERS_ALL
4675 SET uda_template_id = x_po_header_record.uda_template_id,
4676 uda_template_date = x_po_header_record.uda_template_date,
4677 clm_effective_date = x_po_header_record.clm_effective_date,
4678 clm_vendor_offer_number = x_po_header_record.clm_vendor_offer_number,
4679 clm_award_administrator = x_po_header_record.clm_award_administrator,
4680 clm_no_signed_copies_to_return = x_po_header_record.clm_no_signed_copies_to_return,
4681 clm_min_guarantee_award_amt = x_po_header_record.clm_min_guarantee_award_amt,
4682 clm_min_guar_award_amt_percent = x_po_header_record.clm_min_guar_award_amt_percent,
4683 clm_min_order_amount = x_po_header_record.clm_min_order_amount,
4684 clm_max_order_amount = x_po_header_record.clm_max_order_amount,
4685 clm_amt_synced_to_agreement = x_po_header_record.clm_amt_synced_to_agreement,
4686 -- Commented for bug 9777432 clm_amount_released = x_po_header_record.clm_amount_released,
4687 clm_external_idv = x_po_header_record.clm_external_idv,
4688 clm_supplier_name = x_po_header_record.clm_supplier_name,
4689 clm_supplier_site_name = x_po_header_record.clm_supplier_site_name,
4690 clm_source_document_id =x_po_header_record.clm_source_document_id,
4691 clm_issuing_office = x_po_header_record.clm_issuing_office,
4692 clm_cotr_office = x_po_header_record.clm_cotr_office,
4693 clm_cotr_contact = x_po_header_record.clm_cotr_contact,
4694 clm_priority_code = x_po_header_record.clm_priority_code,
4695 clm_standard_form = x_po_header_record.clm_standard_form,
4696 clm_document_format = x_po_header_record.clm_document_format,
4697 clm_award_type = x_po_header_record.clm_award_type,
4698 clm_contract_officer = x_po_header_record.clm_contract_officer
4699 WHERE PO_HEADER_ID = p_to_pk_value;
4700
4701 END IF;
4702
4703 SELECT UDA_TEMPLATE_ID
4704 INTO l_template_id
4705 FROM po_headers_all
4706 WHERE PO_HEADER_ID = p_from_pk_value;
4707
4708 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Template id is ' || l_template_id);
4709
4710 IF l_template_id IS NULL THEN
4711 RETURN;
4712 END IF;
4713
4714 l_pk_col_name := 'PO_HEADER_ID';
4715
4716 ELSIF p_level = 'LINE' THEN
4717
4718 IF PO_CLM_CLO_UTIL.Isclminstalled THEN
4719
4720 UPDATE PO_LINES_ALL
4721 SET uda_template_id = x_po_line_record.uda_template_id,
4722 clm_min_total_amount = x_po_line_record.clm_min_total_amount,
4723 clm_max_total_amount = x_po_line_record.clm_max_total_amount,
4724 clm_min_total_quantity = x_po_line_record.clm_min_total_quantity,
4725 clm_max_total_quantity = x_po_line_record.clm_max_total_quantity,
4726 clm_min_order_amount = x_po_line_record.clm_min_order_amount,
4727 clm_max_order_amount = x_po_line_record.clm_max_order_amount,
4728 clm_min_order_quantity = x_po_line_record.clm_min_order_quantity,
4729 clm_max_order_quantity = x_po_line_record.clm_max_order_quantity,
4730 -- Commented the below 2 lines as the below fields should not be copied to a new document
4731 -- and should be calculated a fresh for the current new document
4732 -- Commented for bug 9935615 clm_total_amount_ordered = x_po_line_record.clm_total_amount_ordered,
4733 -- Commented for bug 9935615 clm_total_quantity_ordered = x_po_line_record.clm_total_quantity_ordered,
4734 clm_fsc_psc = x_po_line_record.clm_fsc_psc,
4735 clm_mdaps_mais = x_po_line_record.clm_mdaps_mais,
4736 clm_naics = x_po_line_record.clm_naics
4737 WHERE PO_LINE_ID = p_to_pk_value;
4738
4739 END IF;
4740
4741
4742 SELECT UDA_TEMPLATE_ID
4743 INTO l_template_id
4744 FROM po_lines_all
4745 WHERE PO_LINE_ID = p_from_pk_value;
4746
4747 IF l_template_id IS NULL THEN
4748 RETURN;
4749 END IF;
4750
4751 l_pk_col_name := 'PO_LINE_ID';
4752
4753 ELSIF p_level = 'SHIPMENT' THEN
4754
4755 IF PO_CLM_CLO_UTIL.Isclminstalled THEN
4756
4757 -- <Event Based Delivery>
4758 -- Take clm_delivery_event_code from po_lines
4759 -- Update POP Dates only if delivery event is not used.
4760
4761 SELECT pl.CLM_DELIVERY_EVENT_CODE
4762 INTO l_clm_delivery_event_code
4763 FROM po_lines_all pl, po_line_locations_all pll
4764 WHERE pll.LINE_LOCATION_ID = p_from_pk_value
4765 AND pll.po_line_id = pl.po_line_id;
4766
4767 UPDATE PO_LINE_LOCATIONS_ALL
4768 SET uda_template_id = x_po_shipment_record.uda_template_id,
4769 clm_period_perf_end_date = decode(l_clm_delivery_event_code, --<Event Based Delivery>
4770 null, x_po_shipment_record.clm_period_perf_end_date,
4771 null),
4772 clm_period_perf_start_date = decode(l_clm_delivery_event_code, --<Event Based Delivery>
4773 null, x_po_shipment_record.clm_period_perf_start_date,
4774 null)
4775 WHERE LINE_LOCATION_ID = p_to_pk_value;
4776
4777 END IF;
4778
4779 SELECT UDA_TEMPLATE_ID
4780 INTO l_template_id
4781 FROM po_line_locations_all
4782 WHERE LINE_LOCATION_ID = p_from_pk_value;
4783
4784 IF l_template_id IS NULL THEN
4785 RETURN;
4786 END IF;
4787
4788 l_pk_col_name := 'LINE_LOCATION_ID';
4789
4790 ELSIF p_level = 'DISTRIBUTION' THEN
4791
4792 IF PO_CLM_CLO_UTIL.Isclminstalled THEN
4793
4794 UPDATE PO_DISTRIBUTIONS_ALL
4795 SET uda_template_id = x_po_distribution_rec.uda_template_id,
4796 clm_misc_loa = x_po_distribution_rec.clm_misc_loa,
4797 clm_defence_funding = x_po_distribution_rec.clm_defence_funding,
4798 clm_fms_case_number = x_po_distribution_rec.clm_fms_case_number,
4799 clm_agency_acct_identifier = x_po_distribution_rec.clm_agency_acct_identifier,
4800 Partial_Funded_Flag = x_po_distribution_rec.Partial_Funded_Flag,
4801 Funded_Value = x_po_distribution_rec.Funded_Value,
4802 Quantity_Funded = x_po_distribution_rec.Quantity_Funded,
4803 Amount_Funded = x_po_distribution_rec.Amount_Funded--,
4804 --Change_in_Funded_Value = x_po_distribution_rec.Change_in_Funded_Value
4805 WHERE PO_DISTRIBUTION_ID = p_to_pk_value;
4806
4807 END IF;
4808
4809 SELECT UDA_TEMPLATE_ID
4810 INTO l_template_id
4811 FROM po_distributions_all
4812 WHERE PO_DISTRIBUTION_ID = p_from_pk_value;
4813
4814 IF l_template_id IS NULL THEN
4815 RETURN;
4816 END IF;
4817
4818 l_pk_col_name := 'PO_DISTRIBUTION_ID';
4819
4820 END IF;
4821
4822 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Before collecting data');
4823
4824
4825 SELECT 'PURCHASING', TYPE_LOOKUP_CODE, STYLE_ID
4826 INTO l_functional_area_code, l_document_type_code, l_document_style_id
4827 FROM po_headers_all
4828 WHERE PO_HEADER_ID = p_from_po_header_id;
4829
4830 l_progress := '8881';
4831 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Fetched template details');
4832 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'l_functional_area_code :' || l_functional_area_code);
4833 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'l_document_type_code : ' || l_document_type_code);
4834 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'l_document_style_id : ' || l_document_style_id);
4835
4836 l_progress := '8882';
4837 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Before Check_Uda_Enabled');
4838
4839 PO_CLM_CLO_UTIL.Check_Uda_Enabled
4840 (
4841 p_functional_area_code => l_functional_area_code
4842 ,p_document_type_code => l_document_type_code
4843 ,p_document_style_id => l_document_style_id
4844 ,x_enabled_flag => l_enabled_flag
4845 ,x_return_status => x_return_status
4846 ,x_errorcode => l_errorcode
4847 ,x_msg_count => x_msg_count
4848 ,x_msg_data => x_msg_data
4849 );
4850
4851 l_progress := '8883';
4852 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'After Check_Uda_Enabled with x_return_status : ' || x_return_status);
4853 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Check_Uda_Enabled with x_enabled_flag : ' || l_enabled_flag);
4854
4855 l_progress := '8884';
4856 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4857 THEN
4858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4859 END IF;
4860
4861 l_progress := '8885';
4862 IF l_enabled_flag = 'Y' THEN
4863 l_progress := '8886';
4864
4865 IF p_level = 'HEADER' THEN
4866 --We should not copy the document numbering usages, so adding the
4867 --document numbering usage in the exclusion list
4868 --Bug 13482355
4869 BEGIN
4870 SELECT DISTINCT ags.ATTR_GROUP_NAME
4871 BULK COLLECT INTO l_ags_table
4872 FROM po_headers_all_ext_b phaeb,
4873 po_uda_ag_template_usages usages,
4874 ego_attr_groups_v ags
4875 WHERE phaeb.po_header_id = p_from_pk_value
4876 AND phaeb.draft_id=-1
4877 AND phaeb.attr_group_id = ags.attr_group_id
4878 AND phaeb.attr_group_id = usages.attribute_group_id
4879 AND usages.attribute_category in ('DOCUMENT_NUMBERING', 'CLOSEOUT')
4880 AND usages.template_id=l_template_id;
4881 EXCEPTION
4882 WHEN No_Data_Found THEN
4883 l_ags_table := NULL;
4884 END;
4885
4886 --Bug 13482355
4887 --l_ags_table := PO_TBL_VARCHAR30(l_doc_num_ag_name);
4888
4889 /* PO_UDA_DATA_UTIL.AutoCreate_User_Attrs
4890 (
4891 from_template_id => l_template_id
4892 ,to_template_id => l_template_id
4893 ,from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4894 (
4895 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_from_pk_value)
4896 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4897 )
4898 ,to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4899 (
4900 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_to_pk_value)
4901 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4902 )
4903 ,copy_attribute_groups => 'EXCLUSIVE'
4904 ,attribute_group_table => l_ags_table
4905 ,x_return_status => x_return_status
4906 ,x_msg_count => x_msg_count
4907 ,x_msg_data => x_msg_data
4908 ); */
4909
4910 -- Modifed for bug 10385583
4911
4912 PO_UDA_DATA_UTIL.Copy_User_Attrs
4913 (
4914 x_template_id => l_template_id
4915 ,x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4916 (
4917 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_from_pk_value)
4918 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4919 )
4920 ,x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4921 (
4922 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_to_pk_value)
4923 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4924 )
4925 ,x_copy_attribute_groups => 'EXCLUSIVE'
4926 ,x_attribute_group_table => l_ags_table
4927 ,x_return_status => x_return_status
4928 ,x_msg_count => x_msg_count
4929 ,x_msg_data => x_msg_data
4930 );
4931
4932 ELSE
4933 PO_UDA_DATA_UTIL.Copy_User_Attrs
4934 (
4935 x_template_id => l_template_id
4936 ,x_from_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4937 (
4938 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_from_pk_value)
4939 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4940 )
4941 ,x_to_pk_col_value_pairs => EGO_COL_NAME_VALUE_PAIR_ARRAY
4942 (
4943 EGO_COL_NAME_VALUE_PAIR_OBJ( l_pk_col_name , p_to_pk_value)
4944 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'DRAFT_ID' , -1)
4945 )
4946 ,x_return_status => x_return_status
4947 ,x_msg_count => x_msg_count
4948 ,x_msg_data => x_msg_data
4949 );
4950 END IF;
4951 l_progress := '8887';
4952 END IF;
4953 l_progress := '8888';
4954 EXCEPTION
4955 WHEN OTHERS THEN
4956 PO_MESSAGE_S.add_exc_msg
4957 (
4958 p_pkg_name => 'po_copydoc_s1',
4959 p_procedure_name => 'COPY_UDA_ATTRS' || '.' || l_progress
4960 );
4961 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, SQLERRM);
4962 PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Exception in COPY_UDA_ATTRS');
4963 x_return_status := 'U';
4964 END COPY_UDA_ATTRS;
4965
4966
4967
4968
4969 END po_copydoc_s1;