[Home] [Help]
PACKAGE BODY: APPS.PO_COPYDOC_S1
Source
1 PACKAGE BODY po_copydoc_s1 AS
2 /* $Header: POXCPO1B.pls 120.25 2007/12/26 07:59:00 adevadul 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 -- Cursor definitions:
22
23 CURSOR po_line_cursor(x_po_header_id po_lines.po_header_id%TYPE) IS
24 SELECT *
25 FROM PO_LINES
26 WHERE po_header_id = x_po_header_id
27 ORDER BY line_num;
28
29 CURSOR po_shipment_cursor(x_po_line_id po_line_locations.po_line_id%TYPE) IS
30 SELECT *
31 FROM PO_LINE_LOCATIONS
32 WHERE po_line_id = x_po_line_id
33 AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET') --Bug: 1773758 1992096
34 ORDER BY shipment_num;
35
36 CURSOR po_distribution_cursor(x_line_location_id po_distributions.line_location_id%TYPE) IS
37 SELECT *
38 FROM PO_DISTRIBUTIONS
39 WHERE line_location_id = x_line_location_id
40 AND distribution_type <> 'AGREEMENT' --bug 3338216: filter BPA dists
41 ORDER BY distribution_num;
42
43 -- End of Cursor definitions
44
45 -- Private function prototypes
46
47 PROCEDURE fetch_header(
48 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
49 x_from_po_header_id IN po_headers.po_header_id%TYPE,
50 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
51 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
52 x_return_code OUT NOCOPY NUMBER
53 );
54 PROCEDURE insert_header(
55 x_po_header_record IN PO_HEADERS%ROWTYPE,
56 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
57 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
58 x_return_code OUT NOCOPY NUMBER,
59 x_copy_terms IN VARCHAR2 --<CONTERMS FPJ>
60 );
61 PROCEDURE insert_line(
62 x_po_line_record IN po_lines%ROWTYPE,
63 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
64 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
65 x_return_code OUT NOCOPY NUMBER
66 );
67
68 PROCEDURE insert_shipment(
69 x_po_shipment_record IN po_line_locations%ROWTYPE,
70 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
71 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
72 x_line_num IN po_online_report_text.line_num%TYPE,
73 x_accrue_on_receipt_flag IN VARCHAR2,
74 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
75 x_return_code OUT NOCOPY NUMBER,
76 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
77 p_orig_line_location_id IN NUMBER -- <eTax Integration R12>
78 );
79
80 PROCEDURE insert_distribution(
81 x_po_distribution_record IN po_distributions%ROWTYPE,
82 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
83 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
84 x_line_num IN po_online_report_text.line_num%TYPE,
85 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
86 x_return_code OUT NOCOPY NUMBER
87 );
88
89 PROCEDURE handle_fatal(
90 x_return_code OUT NOCOPY NUMBER
91 );
92
93 PROCEDURE process_header(
94 x_action_code IN VARCHAR2,
95 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
96 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
97 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
98 x_from_po_header_id IN po_headers.po_header_id%TYPE,
99 x_to_segment1 IN po_headers.segment1%TYPE,
100 x_agent_id IN po_headers.agent_id%TYPE,
101 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
102 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
103 x_copy_attachments IN BOOLEAN,
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_return_code OUT NOCOPY NUMBER,
107 x_copy_terms IN VARCHAR2 -- <FPJ CONTERMS>
108 );
109
110 PROCEDURE process_line(
111 x_action_code IN VARCHAR2,
112 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
113 x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE,
114 x_orig_po_line_id IN po_lines.po_line_id%TYPE,
115 x_wip_install_status IN VARCHAR2,
116 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
117 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
118 x_po_header_id IN po_lines.po_header_id%TYPE,
119 x_copy_attachments IN BOOLEAN,
120 x_copy_price IN BOOLEAN,
121 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
122 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
123 x_return_code OUT NOCOPY NUMBER,
124 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
125 );
126
127 --< Shared Proc FPJ Start >
128 PROCEDURE process_shipment
129 (
130 p_action_code IN VARCHAR2,
131 p_to_doc_subtype IN VARCHAR2,
132 p_orig_line_location_id IN NUMBER,
133 p_po_header_id IN NUMBER,
134 p_po_line_id IN NUMBER,
135 p_item_category_id IN NUMBER, --< Shared Proc FPJ >
136 p_copy_attachments IN BOOLEAN,
137 p_copy_price IN BOOLEAN,
138 p_online_report_id IN NUMBER,
139 p_line_num IN NUMBER,
140 p_inv_org_id IN NUMBER,
141 p_item_id IN NUMBER, -- Bug 3433867
142 x_po_shipment_record IN OUT NOCOPY PO_LINE_LOCATIONS%ROWTYPE,
143 x_sequence IN OUT NOCOPY NUMBER,
144 x_return_code OUT NOCOPY NUMBER,
145 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
146 );
147
148 --<Encumbrance FPJ: add sob_id to param list>
149 PROCEDURE process_distribution
150 (
151 p_action_code IN VARCHAR2,
152 p_to_doc_subtype IN VARCHAR2,
153 p_orig_po_distribution_id IN NUMBER,
154 p_generate_new_accounts IN BOOLEAN,
155 p_copy_attachments IN BOOLEAN,
156 p_online_report_id IN NUMBER,
157 p_po_header_rec IN PO_HEADERS%ROWTYPE,
158 p_po_line_rec IN PO_LINES%ROWTYPE,
159 p_po_shipment_rec IN PO_LINE_LOCATIONS%ROWTYPE,
160 p_sob_id IN FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE,
161 x_po_distribution_rec IN OUT NOCOPY PO_DISTRIBUTIONS%ROWTYPE,
162 x_sequence IN OUT NOCOPY NUMBER,
163 x_return_code OUT NOCOPY NUMBER
164 );
165 --< Shared Proc FPJ End >
166
167 -- End of Private function prototypes
168
169
170 -- Global variable declarations
171
172 g_debug_flag BOOLEAN := TRUE;
173 --<R12 eTax Integration>
174 g_tax_attribute_update_code PO_HEADERS_ALL.tax_attribute_update_code%TYPE;
175
176 -- End of Global variable declarations
177
178
179 PROCEDURE copydoc_debug(
180 x_message IN VARCHAR2
181 ) IS
182 BEGIN
183 IF (g_debug_flag) THEN
184 --dbms_output.put_line('[Debug] '||x_message);
185 null;
186 END IF;
187 END;
188
189 --<HTML Agreements R12 Start>
190 -- Making this procedure a autonomous transaction so that even if copy doc
191 -- fails we should get the error
192 --<HTML Agreements R12 End>
193 PROCEDURE online_report(
194 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
195 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
196 x_message IN po_online_report_text.text_line%TYPE,
197 x_line_num IN po_online_report_text.line_num%TYPE,
198 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
199 x_distribution_num IN po_online_report_text.distribution_num%TYPE,
200 x_message_type IN VARCHAR2 -- <PO_PJM_VALIDATION FPI>
201 ) IS
202 pragma AUTONOMOUS_TRANSACTION; --<HTML Agreements R12>
203 x_text_line po_online_report_text.text_line%TYPE := NULL;
204 x_line_num_msg VARCHAR2(100) := NULL;
205 x_shipment_num_msg VARCHAR2(100) := NULL;
206 x_distribution_num_msg VARCHAR2(100) := NULL;
207 -- <PO_PJM_VALIDATION FPI>
208 -- Increased x_text_line_length to 2000 (length of PO_ONLINE_REPORT_TEXT.text_line);
209 -- changed substr to substrb below to handle multibyte characters.
210 x_text_line_length NUMBER := 2000;
211
212 BEGIN
213
214 IF ((x_online_report_id IS NULL) OR (x_message IS NULL) OR (nvl(x_sequence, 0) < 1)) THEN
215 RETURN;
216 END IF;
217
218 IF (nvl(x_line_num, 0) >= 1) THEN
219 x_line_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_LINE');
220 x_text_line := substrb(x_line_num_msg || x_line_num || ' ', 1, x_text_line_length);
221 IF (nvl(x_shipment_num, 0) >= 1) THEN
222 x_shipment_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_SHIPMENT');
223 x_text_line := substrb(x_text_line || x_shipment_num_msg || x_shipment_num || ' ', 1, x_text_line_length);
224 IF (nvl(x_distribution_num, 0) >= 1) THEN
225 x_distribution_num_msg := fnd_message.get_string('PO', 'PO_ZMVOR_DISTRIBUTION');
226 x_text_line := substrb(x_text_line || x_distribution_num_msg || x_distribution_num || ' ', 1, x_text_line_length);
227 END IF;
228 END IF;
229 END IF;
230
231 x_text_line := substrb(x_text_line || x_message, 1, x_text_line_length);
232
233 BEGIN
234 INSERT INTO PO_ONLINE_REPORT_TEXT (
235 online_report_id,
236 sequence,
237 last_updated_by,
238 last_update_date,
239 created_by,
240 creation_date,
241 last_update_login,
242 text_line,
243 message_type -- <PO_PJM_VALIDATION FPI>
244 )
245 VALUES (
246 x_online_report_id,
247 x_sequence,
248 fnd_global.user_id,
249 SYSDATE,
250 fnd_global.user_id,
251 SYSDATE,
252 fnd_global.login_id,
253 x_text_line,
254 x_message_type -- <PO_PJM_VALIDATION FPI>
255 );
256 COMMIT;
257
258 x_sequence := x_sequence + 1;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 NULL;
263 END;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 NULL;
268 END online_report;
269
270 --<HTML AGREEMENTS R12 Start>
271 ------------------------------------------------------------------------
272 --Start of Comments
273 --Name: ret_and_del_online_report_rec
274 --Pre-reqs:
275 -- None.
276 --Modifies:
277 -- po_online_report_text.
278 --Locks:
279 -- None.
280 --Function:
281 -- This procedure would return the message_text and message_type
282 -- and would also delete the data from the po_online_report_text.
283 -- As this procedure is an autonomous transaction we would commit
284 -- as soon as we delete
285 --IN:
286 -- p_online_report_id
287 -- Online Report Id of the message to be retrieved and deleted
288 --OUT:
289 --x_message_type
290 -- MessageType for online report message if any inserted to the table
291 -- while procedure execution
292 --x_text_line
293 -- Message if any inserted to the online_report_text table while procedure
294 -- execution.
295 --Testing:
296 -- Refer the Unit Test Plan for 'HTML Agreements R12'
297 --End of Comments
298 ----------------------------------------------------------------------------
299 PROCEDURE ret_and_del_online_report_rec( p_online_report_id IN NUMBER
300 ,x_message_type OUT NOCOPY VARCHAR2
301 ,x_message OUT NOCOPY VARCHAR2)
302 IS
303 pragma AUTONOMOUS_TRANSACTION;
304 d_pos NUMBER;
305 d_module VARCHAR2(70) := 'po.plsql.PO_COPYDOC_S1.RET_AND_DEL_ONLINE_REPORT_REC';
306 d_log_msg VARCHAR2(200);
307 BEGIN
308 IF (PO_LOG.d_proc) THEN
309 PO_LOG.proc_begin(d_module);
310 PO_LOG.proc_begin(d_module, 'p_online_report_id', p_online_report_id);
311 END IF;
312 d_pos := 10;
313 DELETE PO_ONLINE_REPORT_TEXT
314 WHERE ONLINE_REPORT_ID = p_online_report_id
315 RETURNING MESSAGE_TYPE, TEXT_LINE
316 INTO x_message_type, x_message;
317 d_pos := 20;
318 IF PO_LOG.d_stmt THEN
319 PO_LOG.stmt(d_module,d_pos,'x_message_type',x_message_type);
320 PO_LOG.stmt(d_module,d_pos,'x_message',x_message);
321 END IF;
322 COMMIT;
323 d_pos := 30;
324 IF PO_LOG.d_event THEN
325 PO_LOG.event(d_module,d_pos,'Committed after Deleting Record from PO_Online_Report_Text');
326 END IF;
327 IF PO_LOG.d_proc THEN
328 PO_LOG.proc_end(d_module,'x_message_type',x_message_type);
329 PO_LOG.proc_end(d_module,'x_message',x_message);
330 PO_LOG.proc_end(d_module);
331 END IF;
332 EXCEPTION
333 WHEN OTHERS THEN
334 d_log_msg := 'Unhandled Exception in ' || d_module;
335 IF PO_LOG.d_exc THEN
336 PO_LOG.exc(d_module,d_pos,d_log_msg);
337 END IF;
338 RAISE;
339 END ret_and_del_online_report_rec;
340 --<HTML AGREEMENTS R12 End>
341
342 PROCEDURE copydoc_sql_error(
343 x_routine IN VARCHAR2,
344 x_progress IN VARCHAR2,
345 x_sqlcode IN NUMBER,
346 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
347 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
348 x_line_num IN po_online_report_text.line_num%TYPE,
349 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
350 x_distribution_num IN po_online_report_text.distribution_num%TYPE
351 ) IS
352
353 x_message VARCHAR2(240);
354
355 BEGIN
356
357 fnd_message.set_name('PO', 'PO_ALL_SQL_ERROR');
358 fnd_message.set_token('ROUTINE', x_routine);
359 fnd_message.set_token('ERR_NUMBER', x_progress);
360 fnd_message.set_token('SQL_ERR', SQLERRM(x_sqlcode));
361
362 x_message := substr(fnd_message.get, 1, 240);
363
364 online_report(x_online_report_id,
365 x_sequence,
366 x_message,
367 x_line_num,
368 x_shipment_num,
369 x_distribution_num);
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 NULL;
374 END copydoc_sql_error;
375
376 /*************************************************************
377 ** Get all the header info for the document with x_from_po_header_id
378 ** and store into x_po_header_record for future processing
379 *************************************************************/
380 PROCEDURE fetch_header(
381 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
382 x_from_po_header_id IN po_headers.po_header_id%TYPE,
383 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
384 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
385 x_return_code OUT NOCOPY NUMBER
386 ) IS
387
388 x_progress VARCHAR2(4);
389
390 BEGIN
391
392 x_progress := '001';
393
394 SELECT *
395 INTO x_po_header_record
396 FROM PO_HEADERS
397 WHERE po_header_id = x_from_po_header_id;
398
399 x_return_code := 0;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 copydoc_sql_error('fetch_header', x_progress, sqlcode,
404 x_online_report_id,
405 x_sequence,
406 0, 0, 0);
407 x_return_code := -1;
408 END fetch_header;
409
410
411 /****************************************************************
412 ** create new PO record from info. stored in x_po_header_record
413 ****************************************************************/
414 PROCEDURE insert_header(
415 x_po_header_record IN PO_HEADERS%ROWTYPE,
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 x_copy_terms IN VARCHAR2 --<CONTERMS FPJ>
420 ) IS
421
422 l_progress VARCHAR2(4);
423 -- Bug: 1566075 Declare variable
424 tmp_pay_on_code VARCHAR2(25) := NULL;
425 tmp_vendor_site_id NUMBER := NULL;
426
427 --< Shared Proc FPJ Start >
428 l_org_assign_rec PO_GA_ORG_ASSIGNMENTS%ROWTYPE;
429 l_org_row_id ROWID;
430 --< Shared Proc FPJ End >
431
432 l_return_status VARCHAR2(1);
433 l_msg_data VARCHAR2(2000);
434 l_msg_count NUMBER;
435
436 BEGIN
437
438 l_progress := '000';
439
440 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
441 PO_DEBUG.debug_stmt
442 (p_log_head => g_module_prefix||'insert_header',
443 p_token => 'invoked',
444 p_message => 'header ID: '||x_po_header_record.po_header_id||
445 ' online_report ID: '||x_online_report_id);
446 END IF;
447
448 -- Bug #2015328, added decode statements to null out start_date and
449 -- end_date when a standard purchase order is copied from other type
450 -- of docs. This is because the std. purchase order should not have
451 -- the start and end date defined in terms and conditions.
452 INSERT INTO PO_HEADERS (
453 acceptance_due_date,
454 acceptance_required_flag,
455 agent_id,
456 amount_limit,
457 approval_required_flag,
458 approved_date,
459 approved_flag,
460 attribute1,
461 attribute10,
462 attribute11,
463 attribute12,
464 attribute13,
465 attribute14,
466 attribute15,
467 attribute2,
468 attribute3,
469 attribute4,
470 attribute5,
471 attribute6,
472 attribute7,
473 attribute8,
474 attribute9,
475 attribute_category,
476 authorization_status,
477 bill_to_location_id,
478 blanket_total_amount,
479 cancel_flag,
480 closed_code,
481 closed_date,
482 comments,
483 confirming_order_flag,
484 created_by,
485 creation_date,
486 currency_code,
487 edi_processed_flag,
488 edi_processed_status,
489 enabled_flag,
490 end_date,
491 end_date_active,
492 firm_date,
493 firm_status_lookup_code,
494 fob_lookup_code,
495 freight_terms_lookup_code,
496 from_header_id,
497 from_type_lookup_code,
498 frozen_flag,
499 global_agreement_flag, -- Global Agreements (FP-I)
500 global_attribute1,
501 global_attribute10,
502 global_attribute11,
503 global_attribute12,
504 global_attribute13,
505 global_attribute14,
506 global_attribute15,
507 global_attribute16,
508 global_attribute17,
509 global_attribute18,
510 global_attribute19,
511 global_attribute2,
512 global_attribute20,
513 global_attribute3,
514 global_attribute4,
515 global_attribute5,
516 global_attribute6,
517 global_attribute7,
518 global_attribute8,
519 global_attribute9,
520 global_attribute_category,
521 government_context,
522 interface_source_code,
523 last_updated_by,
524 last_update_date,
525 last_update_login,
526 min_release_amount,
527 mrc_rate,
528 mrc_rate_date,
529 mrc_rate_type,
530 note_to_authorizer,
531 note_to_receiver,
532 note_to_vendor,
533 org_id,
534 pay_on_code,
535 pcard_id,
536 po_header_id,
537 price_update_tolerance,
538 printed_date,
539 print_count,
540 -- Standard WHO column: program_application_id,
541 -- Standard WHO column: program_id,
542 -- Standard WHO column: program_update_date,
543 quotation_class_code,
544 quote_type_lookup_code,
545 quote_vendor_quote_number,
546 quote_warning_delay,
547 quote_warning_delay_unit,
548 rate,
549 rate_date,
550 rate_type,
551 reference_num,
552 reply_date,
553 reply_method_lookup_code,
554 -- Standard WHO column: request_id,
555 revised_date,
556 revision_num,
557 rfq_close_date,
558 segment1,
559 segment2,
560 segment3,
561 segment4,
562 segment5,
563 ship_to_location_id,
564 ship_via_lookup_code,
565 start_date,
566 start_date_active,
567 status_lookup_code,
568 summary_flag,
569 supply_agreement_flag,
570 terms_id,
571 type_lookup_code,
572 user_hold_flag,
573 vendor_contact_id,
574 vendor_id,
575 vendor_order_num,
576 vendor_site_id,
577 wf_item_key,
578 wf_item_type,
579 shipping_control, -- <INBOUND LOGISTICS FPJ>
580 conterms_exist_flag, -- <CONTERMS FPJ>
581 encumbrance_required_flag, -- <ENCUMBRANCE FPJ>
582 document_creation_method -- <DBI FPJ>
583 ,style_id --<R12 STYLES PHASE II >
584 ,tax_attribute_update_code --<R12 eTax Integration>
585 , created_language -- <Unified Catalog R12>
586 , last_updated_program -- <Unified Catalog R12>
587 -- Bug 5295179 START
588 , supplier_notif_method
589 , fax
590 , email_address
591 -- Bug 5295179 END
592 , Enable_All_Sites -- <R12.1 GCPA >
593 ) VALUES (
594 x_po_header_record.acceptance_due_date,
595 x_po_header_record.acceptance_required_flag,
596 x_po_header_record.agent_id,
597 x_po_header_record.amount_limit,
598 x_po_header_record.approval_required_flag,
599 x_po_header_record.approved_date,
600 x_po_header_record.approved_flag,
601 x_po_header_record.attribute1,
602 x_po_header_record.attribute10,
603 x_po_header_record.attribute11,
604 x_po_header_record.attribute12,
605 x_po_header_record.attribute13,
606 x_po_header_record.attribute14,
607 x_po_header_record.attribute15,
608 x_po_header_record.attribute2,
609 x_po_header_record.attribute3,
610 x_po_header_record.attribute4,
611 x_po_header_record.attribute5,
612 x_po_header_record.attribute6,
613 x_po_header_record.attribute7,
614 x_po_header_record.attribute8,
615 x_po_header_record.attribute9,
616 x_po_header_record.attribute_category,
617 x_po_header_record.authorization_status,
618 x_po_header_record.bill_to_location_id,
619 x_po_header_record.blanket_total_amount,
620 x_po_header_record.cancel_flag,
621 x_po_header_record.closed_code,
622 x_po_header_record.closed_date,
623 x_po_header_record.comments,
624 x_po_header_record.confirming_order_flag,
625 x_po_header_record.created_by,
626 x_po_header_record.creation_date,
627 x_po_header_record.currency_code,
628 x_po_header_record.edi_processed_flag,
629 x_po_header_record.edi_processed_status,
630 x_po_header_record.enabled_flag,
631 decode(x_po_header_record.type_lookup_code, 'STANDARD', to_date(NULL),
632 x_po_header_record.end_date), -- Bug #2015328 BUG #5743193
633 x_po_header_record.end_date_active,
634 x_po_header_record.firm_date,
635 x_po_header_record.firm_status_lookup_code,
636 x_po_header_record.fob_lookup_code,
637 x_po_header_record.freight_terms_lookup_code,
638 x_po_header_record.from_header_id,
639 x_po_header_record.from_type_lookup_code,
640 x_po_header_record.frozen_flag,
641 x_po_header_record.global_agreement_flag, -- Global Agreements (FP-I)
642 x_po_header_record.global_attribute1,
643 x_po_header_record.global_attribute10,
644 x_po_header_record.global_attribute11,
645 x_po_header_record.global_attribute12,
646 x_po_header_record.global_attribute13,
647 x_po_header_record.global_attribute14,
648 x_po_header_record.global_attribute15,
649 x_po_header_record.global_attribute16,
650 x_po_header_record.global_attribute17,
651 x_po_header_record.global_attribute18,
652 x_po_header_record.global_attribute19,
653 x_po_header_record.global_attribute2,
654 x_po_header_record.global_attribute20,
655 x_po_header_record.global_attribute3,
656 x_po_header_record.global_attribute4,
657 x_po_header_record.global_attribute5,
658 x_po_header_record.global_attribute6,
659 x_po_header_record.global_attribute7,
660 x_po_header_record.global_attribute8,
661 x_po_header_record.global_attribute9,
662 x_po_header_record.global_attribute_category,
663 x_po_header_record.government_context,
664 x_po_header_record.interface_source_code,
665 x_po_header_record.last_updated_by,
666 x_po_header_record.last_update_date,
667 x_po_header_record.last_update_login,
668 decode(x_po_header_record.global_agreement_flag , 'Y' , null ,
669 x_po_header_record.min_release_amount) ,
670 x_po_header_record.mrc_rate,
671 x_po_header_record.mrc_rate_date,
672 x_po_header_record.mrc_rate_type,
673 x_po_header_record.note_to_authorizer,
674 x_po_header_record.note_to_receiver,
675 x_po_header_record.note_to_vendor,
676 x_po_header_record.org_id,
677 x_po_header_record.pay_on_code, -- <BUG 4766467>
678 x_po_header_record.pcard_id,
679 x_po_header_record.po_header_id,
680 x_po_header_record.price_update_tolerance,
681 x_po_header_record.printed_date,
682 x_po_header_record.print_count,
683 -- Standard WHO column: x_po_header_record.program_application_id,
684 -- Standard WHO column: x_po_header_record.program_id,
685 -- Standard WHO column: x_po_header_record.program_update_date,
686 x_po_header_record.quotation_class_code,
687 x_po_header_record.quote_type_lookup_code,
688 x_po_header_record.quote_vendor_quote_number,
689 x_po_header_record.quote_warning_delay,
690 x_po_header_record.quote_warning_delay_unit,
691 x_po_header_record.rate,
692 x_po_header_record.rate_date,
693 x_po_header_record.rate_type,
694 x_po_header_record.reference_num,
695 x_po_header_record.reply_date,
696 x_po_header_record.reply_method_lookup_code,
697 -- Standard WHO column: x_po_header_record.request_id,
698 x_po_header_record.revised_date,
699 x_po_header_record.revision_num,
700 x_po_header_record.rfq_close_date,
701 x_po_header_record.segment1,
702 x_po_header_record.segment2,
703 x_po_header_record.segment3,
704 x_po_header_record.segment4,
705 x_po_header_record.segment5,
706 x_po_header_record.ship_to_location_id,
707 x_po_header_record.ship_via_lookup_code,
708 decode(x_po_header_record.type_lookup_code, 'STANDARD' , to_date(NULL),
709 x_po_header_record.start_date), -- Bug #2015328 Bug #5743193
710 x_po_header_record.start_date_active,
711 x_po_header_record.status_lookup_code,
712 x_po_header_record.summary_flag,
713 x_po_header_record.supply_agreement_flag,
714 x_po_header_record.terms_id,
715 x_po_header_record.type_lookup_code,
716 x_po_header_record.user_hold_flag,
717 x_po_header_record.vendor_contact_id,
718 x_po_header_record.vendor_id,
719 x_po_header_record.vendor_order_num,
720 x_po_header_record.vendor_site_id,
721 x_po_header_record.wf_item_key,
722 x_po_header_record.wf_item_type,
723 x_po_header_record.shipping_control, -- <INBOUND LOGISTICS FPJ>
724 decode(x_copy_terms, 'N', 'N', x_po_header_record.conterms_exist_flag),
725 -- <CONTERMS FPJ>
726 x_po_header_record.encumbrance_required_flag, -- <ENCUMBRANCE FPJ>
727 -- Bug 3648268 Use lookup code instead of hardcoded value
728 'COPY_DOCUMENT' --<DBI FPJ>
729 ,x_po_header_record.style_id --<R12 STYLES PHASE II >
730 ,g_tax_attribute_update_code --<R12 eTax Integration>
731 , x_po_header_record.created_language -- <Unified Catalog R12>
732 , 'COPY_DOC' -- <Unified Catalog R12>
733 -- Bug 5295179 START
734 , x_po_header_record.supplier_notif_method
735 , x_po_header_record.fax
736 , x_po_header_record.email_address
737 -- Bug 5295179 END
738 , decode(x_po_header_record.type_lookup_code,
739 'CONTRACT',x_po_header_record.Enable_All_Sites,
740 NULL) -- <R12.1 GCPA>
741
742 );
743
744 l_progress := '030';
745
746 -- Global Agreements (FP-I): If Global Agreement, must also insert
747 -- assignment information for Owning Org into PO_GA_ORG_ASSIGNMENTS.
748 --
749 IF ( x_po_header_record.global_agreement_flag = 'Y' ) THEN
750
751 l_progress := '050';
752
753 --< Shared Proc FPJ Start >
754 -- Refactor code to use GA utility procedures and row handlers
755
756 IF (PO_GA_PVT.is_global_agreement
757 (p_po_header_id => x_po_header_record.from_header_id))
758 THEN
759 l_progress := '060';
760 -- The original document is a global agreement, so blindly copy all
761 -- of its org assignments over for the new GA.
762
763 PO_GA_ORG_ASSIGN_PVT.copy_rows
764 (p_init_msg_list => FND_API.g_false,
765 x_return_status => l_return_status,
766 p_from_po_header_id => x_po_header_record.from_header_id,
767 p_to_po_header_id => x_po_header_record.po_header_id,
768 p_last_update_date => x_po_header_record.last_update_date,
769 p_last_updated_by => x_po_header_record.last_updated_by,
770 p_creation_date => x_po_header_record.creation_date,
771 p_created_by => x_po_header_record.created_by,
772 p_last_update_login => x_po_header_record.last_update_login);
773
774 ELSE
775 l_progress := '070';
776 -- The original document is not a global agreement, so just insert
777 -- one org assignment for the owning org.
778
779 l_org_assign_rec.po_header_id := x_po_header_record.po_header_id;
780 l_org_assign_rec.organization_id := x_po_header_record.org_id;
781 l_org_assign_rec.enabled_flag := 'Y';
782 l_org_assign_rec.vendor_site_id :=
783 x_po_header_record.vendor_site_id;
784 l_org_assign_rec.last_update_date :=
785 x_po_header_record.last_update_date;
786 l_org_assign_rec.last_updated_by :=
787 x_po_header_record.last_updated_by;
788 l_org_assign_rec.creation_date := x_po_header_record.creation_date;
789 l_org_assign_rec.created_by := x_po_header_record.created_by;
790 l_org_assign_rec.last_update_login :=
791 x_po_header_record.last_update_login;
792 l_org_assign_rec.purchasing_org_id := x_po_header_record.org_id;
793
794 PO_GA_ORG_ASSIGN_PVT.insert_row
795 (p_init_msg_list => FND_API.g_false,
796 x_return_status => l_return_status,
797 p_org_assign_rec => l_org_assign_rec,
798 x_row_id => l_org_row_id);
799
800 END IF;
801
802 -- Check the return status of call to row handler
803 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
804 RAISE FND_API.g_exc_error;
805 END IF;
806 --< Shared Proc FPJ End >
807
808 END IF;
809
810 x_return_code := 0;
811
812 EXCEPTION
813 WHEN OTHERS THEN
814 copydoc_sql_error('insert_header', l_progress, sqlcode,
815 x_online_report_id,
816 x_sequence,
817 0, 0, 0);
818 x_return_code := -1;
819 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
820 PO_DEBUG.debug_exc
821 (p_log_head => g_module_prefix||'insert_header',
822 p_progress => l_progress);
823 END IF;
824 END insert_header;
825
826
827 /** create new line record with info from x_po_line_record **/
828 PROCEDURE insert_line(
829 x_po_line_record IN po_lines%ROWTYPE,
830 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
831 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
832 x_return_code OUT NOCOPY NUMBER
833 ) IS
834
835 l_progress VARCHAR2(4);
836
837 -- <SERVICES FPJ START>
838 l_order_type_lookup_code PO_LINE_TYPES_B.order_type_lookup_code%TYPE;
839 l_purchase_basis PO_LINE_TYPES_B.purchase_basis%TYPE;
840 l_matching_basis PO_LINE_TYPES_B.matching_basis%TYPE;
841 l_category_id PO_LINE_TYPES_B.category_id%TYPE;
842 l_unit_meas_lookup_code PO_LINE_TYPES_B.unit_of_measure%TYPE;
843 l_unit_price PO_LINE_TYPES_B.unit_price%TYPE;
844 l_outside_operation_flag PO_LINE_TYPES_B.outside_operation_flag%TYPE;
845 l_receiving_flag PO_LINE_TYPES_B.receiving_flag%TYPE;
846 l_receive_close_tolerance PO_LINE_TYPES_B.receive_close_tolerance%TYPE;
847 -- <SERVICES FPJ END>
848
849 BEGIN
850
851 l_progress := '001';
852
853 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
854 PO_DEBUG.debug_stmt
855 (p_log_head => g_module_prefix||'insert_line',
856 p_token => 'invoked',
857 p_message => 'line ID: '||x_po_line_record.po_line_id||
858 ' online_report ID: '||x_online_report_id);
859 END IF;
860
861 -- <SERVICES FPJ START>
862 -- Retrieve the values for order_type_lookup_code, purchase_basis
863 -- and matching_basis
864 PO_LINE_TYPES_SV.get_line_type_def(
865 x_po_line_record.line_type_id,
866 l_order_type_lookup_code,
867 l_purchase_basis,
868 l_matching_basis,
869 l_category_id,
870 l_unit_meas_lookup_code,
871 l_unit_price,
872 l_outside_operation_flag,
873 l_receiving_flag,
874 l_receive_close_tolerance);
875 -- <SERVICES FPJ END>
876
877 -- <SERVICES FPJ>
878 -- Added order_type_lookup_code, purchase_basis and matching_basis
879 -- for PO Line level denormalization
880 INSERT INTO PO_LINES (
881 allow_price_override_flag,
882 attribute1,
883 attribute10,
884 attribute11,
885 attribute12,
886 attribute13,
887 attribute14,
888 attribute15,
889 attribute2,
890 attribute3,
891 attribute4,
892 attribute5,
893 attribute6,
894 attribute7,
895 attribute8,
896 attribute9,
897 attribute_category,
898 base_qty,
899 base_uom,
900 cancelled_by,
901 cancel_date,
902 cancel_flag,
903 cancel_reason,
904 capital_expense_flag,
905 category_id,
906 closed_by,
907 closed_code,
908 closed_date,
909 closed_flag,
910 closed_reason,
911 committed_amount,
912 -- contract_num, -- <GC FPJ>
913 contract_id, -- <GC FPJ>
914 created_by,
915 creation_date,
916 expiration_date,
917 firm_date,
918 firm_status_lookup_code,
919 from_header_id,
920 from_line_id,
921 from_line_location_id, -- <SERVICES FPJ>
922 global_attribute1,
923 global_attribute10,
924 global_attribute11,
925 global_attribute12,
926 global_attribute13,
927 global_attribute14,
928 global_attribute15,
929 global_attribute16,
930 global_attribute17,
931 global_attribute18,
932 global_attribute19,
933 global_attribute2,
934 global_attribute20,
935 global_attribute3,
936 global_attribute4,
937 global_attribute5,
938 global_attribute6,
939 global_attribute7,
940 global_attribute8,
941 global_attribute9,
942 global_attribute_category,
943 government_context,
944 hazard_class_id,
945 item_description,
946 item_id,
947 item_revision,
948 last_updated_by,
949 last_update_date,
950 last_update_login,
951 line_num,
952 line_reference_num,
953 line_type_id,
954 list_price_per_unit,
955 market_price,
956 max_order_quantity,
957 min_order_quantity,
958 min_release_amount,
959 negotiated_by_preparer_flag,
960 note_to_vendor,
961 not_to_exceed_price,
962 org_id,
963 over_tolerance_error_flag,
964 po_header_id,
965 po_line_id,
966 price_break_lookup_code,
967 price_type_lookup_code,
968 project_id,
969 qc_grade,
970 qty_rcv_tolerance,
971 quantity,
972 quantity_committed,
973 reference_num,
974 secondary_qty,
975 secondary_uom,
976 task_id,
977 taxable_flag,
978 tax_code_id,
979 transaction_reason_code,
980 type_1099,
981 unit_meas_lookup_code,
982 unit_price,
983 base_unit_price, -- <FPJ Advanced Price>
984 unordered_flag,
985 un_number_id,
986 user_hold_flag,
987 vendor_product_num,
988 oke_contract_header_id, --added oke columns
989 oke_contract_version_id, --added oke columns
990 secondary_unit_of_measure, -- 1548597
991 secondary_quantity, -- 1548597
992 preferred_grade, -- 1548597
993 job_id, -- Services FPJ
994 amount, -- Services FPJ
995 start_date, -- Services FPJ
996 contractor_first_name, -- Services FPJ
997 contractor_last_name, -- Services FPJ
998 order_type_lookup_code, -- <SERVICES FPJ>
999 purchase_basis, -- <SERVICES FPJ>
1000 matching_basis, -- <SERVICES FPJ>
1001 manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1002 ,tax_attribute_update_code --<R12 eTax Integration>
1003 , retainage_rate -- <Complex Work R12>
1004 , max_retainage_amount -- <Complex Work R12>
1005 , progress_payment_rate -- <Complex Work R12>
1006 , recoupment_rate -- <Complex Work R12>
1007 , catalog_name -- <Unified Catalog R12>
1008 , supplier_part_auxid -- <Unified Catalog R12>
1009 , ip_category_id -- <Unified Catalog R12>
1010 , last_updated_program -- <Unified Catalog R12>
1011 ) VALUES (
1012 x_po_line_record.allow_price_override_flag,
1013 x_po_line_record.attribute1,
1014 x_po_line_record.attribute10,
1015 x_po_line_record.attribute11,
1016 x_po_line_record.attribute12,
1017 x_po_line_record.attribute13,
1018 x_po_line_record.attribute14,
1019 x_po_line_record.attribute15,
1020 x_po_line_record.attribute2,
1021 x_po_line_record.attribute3,
1022 x_po_line_record.attribute4,
1023 x_po_line_record.attribute5,
1024 x_po_line_record.attribute6,
1025 x_po_line_record.attribute7,
1026 x_po_line_record.attribute8,
1027 x_po_line_record.attribute9,
1028 x_po_line_record.attribute_category,
1029 x_po_line_record.base_qty,
1030 x_po_line_record.base_uom,
1031 x_po_line_record.cancelled_by,
1032 x_po_line_record.cancel_date,
1033 x_po_line_record.cancel_flag,
1034 x_po_line_record.cancel_reason,
1035 x_po_line_record.capital_expense_flag,
1036 x_po_line_record.category_id,
1037 x_po_line_record.closed_by,
1038 x_po_line_record.closed_code,
1039 x_po_line_record.closed_date,
1040 x_po_line_record.closed_flag,
1041 x_po_line_record.closed_reason,
1042 x_po_line_record.committed_amount,
1043 -- x_po_line_record.contract_num, -- <GC FPJ>
1044 x_po_line_record.contract_id, -- <GC FPJ>
1045 x_po_line_record.created_by,
1046 x_po_line_record.creation_date,
1047 x_po_line_record.expiration_date,
1048 x_po_line_record.firm_date,
1049 x_po_line_record.firm_status_lookup_code,
1050 x_po_line_record.from_header_id,
1051 x_po_line_record.from_line_id,
1052 x_po_line_record.from_line_location_id, -- <SERVICES FPJ>
1053 x_po_line_record.global_attribute1,
1054 x_po_line_record.global_attribute10,
1055 x_po_line_record.global_attribute11,
1056 x_po_line_record.global_attribute12,
1057 x_po_line_record.global_attribute13,
1058 x_po_line_record.global_attribute14,
1059 x_po_line_record.global_attribute15,
1060 x_po_line_record.global_attribute16,
1061 x_po_line_record.global_attribute17,
1062 x_po_line_record.global_attribute18,
1063 x_po_line_record.global_attribute19,
1064 x_po_line_record.global_attribute2,
1065 x_po_line_record.global_attribute20,
1066 x_po_line_record.global_attribute3,
1067 x_po_line_record.global_attribute4,
1068 x_po_line_record.global_attribute5,
1069 x_po_line_record.global_attribute6,
1070 x_po_line_record.global_attribute7,
1071 x_po_line_record.global_attribute8,
1072 x_po_line_record.global_attribute9,
1073 x_po_line_record.global_attribute_category,
1074 x_po_line_record.government_context,
1075 x_po_line_record.hazard_class_id,
1076 x_po_line_record.item_description,
1077 x_po_line_record.item_id,
1078 x_po_line_record.item_revision,
1079 x_po_line_record.last_updated_by,
1080 x_po_line_record.last_update_date,
1081 x_po_line_record.last_update_login,
1082 x_po_line_record.line_num,
1083 x_po_line_record.line_reference_num,
1084 x_po_line_record.line_type_id,
1085 x_po_line_record.list_price_per_unit,
1086 x_po_line_record.market_price,
1087 x_po_line_record.max_order_quantity,
1088 x_po_line_record.min_order_quantity,
1089 x_po_line_record.min_release_amount,
1090 x_po_line_record.negotiated_by_preparer_flag,
1091 x_po_line_record.note_to_vendor,
1092 x_po_line_record.not_to_exceed_price,
1093 x_po_line_record.org_id,
1094 x_po_line_record.over_tolerance_error_flag,
1095 x_po_line_record.po_header_id,
1096 x_po_line_record.po_line_id,
1097 x_po_line_record.price_break_lookup_code,
1098 x_po_line_record.price_type_lookup_code,
1099 x_po_line_record.project_id,
1100 x_po_line_record.qc_grade,
1101 x_po_line_record.qty_rcv_tolerance,
1102 x_po_line_record.quantity,
1103 x_po_line_record.quantity_committed,
1104 x_po_line_record.reference_num,
1105 x_po_line_record.secondary_qty,
1106 x_po_line_record.secondary_uom,
1107 x_po_line_record.task_id,
1108 x_po_line_record.taxable_flag,
1109 x_po_line_record.tax_code_id,
1110 x_po_line_record.transaction_reason_code,
1111 x_po_line_record.type_1099,
1112 x_po_line_record.unit_meas_lookup_code,
1113 x_po_line_record.unit_price,
1114 x_po_line_record.base_unit_price, -- <FPJ Advanced Price>
1115 x_po_line_record.unordered_flag,
1116 x_po_line_record.un_number_id,
1117 x_po_line_record.user_hold_flag,
1118 x_po_line_record.vendor_product_num,
1119 x_po_line_record.oke_contract_header_id, -- added oke columns
1120 x_po_line_record.oke_contract_version_id, -- added oke columns
1121 x_po_line_record.secondary_unit_of_measure, -- 1548597
1122 x_po_line_record.secondary_quantity, -- 1548597
1123 x_po_line_record.preferred_grade, -- 1548597
1124 x_po_line_record.job_id, -- Services FPJ
1125 x_po_line_record.amount, -- Services FPJ
1126 x_po_line_record.start_date, -- Services FPJ
1127 x_po_line_record.contractor_first_name, -- Services FPJ
1128 x_po_line_record.contractor_last_name, -- Services FPJ
1129 l_order_type_lookup_code, -- <SERVICES FPJ>
1130 l_purchase_basis, -- <SERVICES FPJ>
1131 l_matching_basis, -- <SERVICES FPJ>
1132 x_po_line_record.manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1133 ,g_tax_attribute_update_code --<R12 eTax Integration>
1134 , x_po_line_record.retainage_rate -- <Complex Work R12>
1135 , x_po_line_record.max_retainage_amount -- <Complex Work R12>
1136 , x_po_line_record.progress_payment_rate -- <Complex Work R12>
1137 , x_po_line_record.recoupment_rate -- <Complex Work R12>
1138 , x_po_line_record.catalog_name -- <Unified Catalog R12>
1139 , x_po_line_record.supplier_part_auxid -- <Unified Catalog R12>
1140 , x_po_line_record.ip_category_id -- <Unified Catalog R12>
1141 , 'COPY_DOC' -- <Unified Catalog R12>
1142 );
1143
1144
1145 x_return_code := 0;
1146
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 copydoc_sql_error('insert_line', l_progress, sqlcode,
1150 x_online_report_id,
1151 x_sequence,
1152 x_po_line_record.line_num, 0, 0);
1153 x_return_code := -1;
1154 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
1155 PO_DEBUG.debug_exc
1156 (p_log_head => g_module_prefix||'insert_line',
1157 p_progress => l_progress);
1158 END IF;
1159 END insert_line;
1160
1161
1162 PROCEDURE insert_shipment(
1163 x_po_shipment_record IN po_line_locations%ROWTYPE,
1164 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1165 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1166 x_line_num IN po_online_report_text.line_num%TYPE,
1167 x_accrue_on_receipt_flag IN VARCHAR2,
1168 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE, -- Bug 2761415
1169 x_return_code OUT NOCOPY NUMBER,
1170 p_is_complex_work_po IN BOOLEAN, -- <Complex Work R12>
1171 p_orig_line_location_id IN NUMBER -- <eTax Integration R12>
1172 ) IS
1173
1174 l_progress VARCHAR2(4);
1175
1176 --bug #2717053: changed var names/types to conform to standards
1177 l_vendor_site_id po_headers.vendor_site_id%TYPE := null;
1178 l_to_ship_to_location_id po_line_locations.ship_to_location_id%TYPE;
1179 l_to_ship_to_organization_id po_line_locations.ship_to_organization_id%TYPE;
1180 l_item_id po_lines.item_id%TYPE := null;
1181 l_count number:= 0;
1182 l_quantity po_line_locations.quantity%TYPE;
1183 l_quote_type_code po_headers.quote_type_lookup_code%TYPE := null;
1184
1185 x_sob_id number:= null;
1186
1187 -- CONSIGNED FPI START
1188 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1189 l_vendor_id
1190 po_headers.vendor_id%TYPE := null;
1191 l_consigned_flag
1192 po_line_locations.consigned_flag%TYPE := null;
1193 l_consigned_from_supplier_flag
1194 po_asl_attributes.consigned_from_supplier_flag%TYPE := null;
1195 l_enable_vmi_flag
1196 po_asl_attributes.enable_vmi_flag%TYPE := null;
1197 l_accrue_on_receipt_flag
1198 po_line_locations.accrue_on_receipt_flag%TYPE := null;
1199 l_closed_code
1200 po_line_locations.closed_code%TYPE := null;
1201 l_closed_reason
1202 po_line_locations.closed_reason%TYPE := null;
1203 l_inspection_required_flag
1204 po_line_locations.inspection_required_flag%TYPE := null;
1205 l_receipt_required_flag
1206 po_line_locations.receipt_required_flag%TYPE := null;
1207 l_match_option
1208 po_line_locations.match_option%TYPE := null;
1209 l_last_billing_date date := null;
1210 l_consigned_billing_cycle number := null;
1211 l_invoice_close_tolerance number := null;
1212 l_item_inv_asset_flag
1213 mtl_system_items_b.inventory_asset_flag%TYPE := NULL;
1214 l_return_status varchar2(1) := NULL;
1215 l_msg_count number := NULL;
1216 l_msg_data varchar2(2000) := NULL;
1217 -- CONSIGNED FPI END
1218
1219 --<INVCONV R12 START>
1220 l_unit_meas_lookup_code MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE := NULL;
1221 x_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE := NULL;
1222 x_secondary_uom_code MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE := NULL;
1223 x_secondary_quantity PO_LINES_ALL.SECONDARY_QUANTITY%TYPE := NULL;
1224 --<INVCONV R12 END>
1225
1226 --<Complex Work R12 START> Bug 4958300
1227 l_line_value_basis PO_LINE_LOCATIONS_ALL.value_basis%type;
1228 l_line_matching_basis PO_LINE_LOCATIONS_ALL.matching_basis%type;
1229 --<Complex Work R12 END>
1230
1231 BEGIN
1232
1233 l_progress := '000';
1234
1235 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
1236 PO_DEBUG.debug_stmt
1237 (p_log_head => g_module_prefix||'insert_shipment',
1238 p_token => 'invoked',
1239 p_message => 'ship ID: '||x_po_shipment_record.line_location_id||
1240 ' inv_org ID: '||x_inv_org_id||' accrue_on_receipt_flag: '||
1241 x_accrue_on_receipt_flag||' online_report ID: '||
1242 x_online_report_id);
1243 END IF;
1244
1245 --bug #2717053: reorganized the defaulting logic for RFQs/Quotations
1246 --In FPI Timephased, we changed the structure of BPA price breaks so
1247 --that org, loc and qty could all be null. Now, when copying these
1248 --price breaks, we have to make sure we don't copy in org/loc or qty
1249 --values that couldn't be entered that way on the forms themselves.
1250
1251 l_to_ship_to_organization_id := x_po_shipment_record.ship_to_organization_id;
1252 l_to_ship_to_location_id := x_po_shipment_record.ship_to_location_id;
1253 l_quantity := x_po_shipment_record.quantity;
1254
1255 l_progress := '010';
1256
1257 --<INVCONV R12>:added UOM to select below
1258 --<Complex Work R12> bug 4958300: added value basis/matching basis
1259 select item_id, unit_meas_lookup_code,
1260 order_type_lookup_code, matching_basis
1261 into l_item_id, l_unit_meas_lookup_code,
1262 l_line_value_basis, l_line_matching_basis
1263 from po_lines_all
1264 where po_line_id=x_po_shipment_record.po_line_id;
1265
1266 -- CONSIGNED FPI START
1267 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1268 -- initialize local variables for shipment attributes with values
1269 -- passed in from the IN parameters
1270 l_accrue_on_receipt_flag := x_accrue_on_receipt_flag;
1271 l_invoice_close_tolerance := x_po_shipment_record.invoice_close_tolerance;
1272 l_closed_code := x_po_shipment_record.closed_code;
1273 l_closed_reason := x_po_shipment_record.closed_reason;
1274 l_inspection_required_flag := x_po_shipment_record.inspection_required_flag;
1275 l_receipt_required_flag := x_po_shipment_record.receipt_required_flag;
1276 l_match_option := x_po_shipment_record.match_option;
1277 -- CONSIGNED FPI END
1278
1279 IF (x_po_shipment_record.shipment_type IN ('QUOTATION', 'RFQ')) THEN
1280
1281 l_progress := '020';
1282
1283 Begin
1284 SELECT ph.quote_type_lookup_code
1285 INTO l_quote_type_code
1286 FROM po_headers ph
1287 WHERE ph.po_header_id = x_po_shipment_record.po_header_id;
1288 Exception
1289 When others then
1290 l_quote_type_code := NULL;
1291 End;
1292
1293 IF (l_quote_type_code IN ('CATALOG', 'STANDARD')) THEN
1294 --note: we don't do any defaulting for Bid RFQs/Quotes.
1295 --since we can not copy a document INTO a Bid RFQ, we
1296 --dont' have to worry about consistency with Forms entry
1297
1298 IF ((l_to_ship_to_organization_id IS NULL) AND
1299 (l_to_ship_to_location_id IS NOT NULL)) THEN
1300
1301 l_progress := '030';
1302
1303 -- try to infer org if org is null, but loc is not.
1304 -- (you can not specify a loc w/o an org in the forms)
1305 -- first check against the hr_locations table
1306 begin
1307 select inventory_organization_id
1308 into l_to_ship_to_organization_id
1309 from hr_locations_v
1310 where location_id = l_to_ship_to_location_id
1311 and ship_to_site_flag = 'Y';
1312 exception
1313 when no_data_found then
1314 l_to_ship_to_organization_id := NULL;
1315 end;
1316
1317 l_progress := '040';
1318
1319 -- check the org value from hr_locations to ensure
1320 -- that the item is defined in this org
1321 if (l_to_ship_to_organization_id is NOT NULL) then
1322 select count(*) into l_count from
1323 mtl_system_items where
1324 inventory_item_id=l_item_id and
1325 organization_id=l_to_ship_to_organization_id ;
1326 if (l_count=0) then
1327 l_to_ship_to_organization_id:=NULL;
1328 end if;
1329 end if;
1330
1331 l_progress := '050';
1332
1333 -- if there was a null org from hr tables, or if
1334 -- the item was not valid, use the inv_org from
1335 -- financials_system_parameters (x_inv_org_id)
1336 -- if the item is not defined for the FSP org, then
1337 -- leave the org null.
1338 if (l_to_ship_to_organization_id is NULL) then
1339 select count(*) into l_count from
1340 mtl_system_items where
1341 inventory_item_id=l_item_id and
1342 organization_id=x_inv_org_id;
1343 if (l_count <> 0) then
1344 l_to_ship_to_organization_id := x_inv_org_id;
1345 end if;
1346 end if;
1347
1348 END IF; --end of org null/loc not null check
1349
1350 IF (l_quantity IS NULL) THEN
1351 --pb qty can not be NULL in RFQ/Quote form: use zero instead
1352 l_quantity := 0;
1353 END IF; --end of qty check
1354
1355 END IF; --end of l_quote_type_code check
1356
1357 -- <Complex Work R12>: Also skip complex work POs.
1358 ELSIF ((NOT p_is_complex_work_po) AND
1359 (x_po_shipment_record.shipment_type <> 'PRICE BREAK')) THEN
1360 -- shipment is not a BPA, Quote or RFQ price break
1361
1362 l_progress := '060';
1363
1364 IF (l_to_ship_to_organization_id IS NULL) THEN
1365 l_progress := '070';
1366
1367 begin
1368 select vendor_site_id into l_vendor_site_id from
1369 po_headers where
1370 po_header_id=x_po_shipment_record.po_header_id;
1371
1372 select ship_to_location_id into l_to_ship_to_location_id
1373 from po_vendor_sites where
1374 vendor_site_id=l_vendor_site_id;
1375
1376 if (l_to_ship_to_location_id is null) then
1377 select fsp.ship_to_location_id
1378 into l_to_ship_to_location_id
1379 from financials_system_parameters fsp;
1380 end if;
1381 SELECT inventory_organization_id
1382 INTO l_to_ship_to_organization_id
1383 FROM hr_locations_v
1384 WHERE location_id = l_to_ship_to_location_id
1385 AND ship_to_site_flag = 'Y';
1386 exception
1387 when no_data_found then
1388 l_to_ship_to_organization_id:=null;
1389 end;
1390
1391 l_progress := '080';
1392
1393 select count(*) into l_count from
1394 mtl_system_items where
1395 inventory_item_id=l_item_id and
1396 organization_id=l_to_ship_to_organization_id ;
1397
1398 if (l_count=0) then
1399 l_to_ship_to_organization_id:=null;
1400 end if;
1401
1402 if l_to_ship_to_organization_id is null then
1403 return;
1404 end if;
1405
1406 END IF; --end check if org_id is NULL
1407
1408 l_progress := '090';
1409
1410 -- CONSIGNED FPI START
1411 -- Bug Fix for #2697755: COPY PO WITH CONSIGNED SHIPMENT LINE FAILS
1412 IF (x_po_shipment_record.shipment_type = 'STANDARD') THEN
1413
1414 IF(l_to_ship_to_organization_id IS NOT NULL AND
1415 l_item_id IS NOT NULL)
1416 THEN
1417 l_progress := '100';
1418
1419 PO_THIRD_PARTY_STOCK_GRP.Get_Item_Inv_Asset_Flag
1420 (p_api_version => 1.0 ,
1421 p_init_msg_list => NULL ,
1422 x_return_status => l_return_status ,
1423 x_msg_count => l_msg_count ,
1424 x_msg_data => l_msg_data ,
1425 p_organization_id => l_to_ship_to_organization_id,
1426 p_inventory_item_id => l_item_id ,
1427 x_inventory_asset_flag => l_item_inv_asset_flag );
1428 END IF;
1429
1430 IF(l_item_inv_asset_flag = 'Y')
1431 THEN
1432 l_progress := '110';
1433
1434 select vendor_id, vendor_site_id
1435 into l_vendor_id, l_vendor_site_id
1436 from po_headers
1437 where po_header_id = x_po_shipment_record.po_header_id;
1438
1439 l_progress := '120';
1440
1441 PO_THIRD_PARTY_STOCK_GRP.get_asl_attributes
1442 (p_api_version => 1.0 ,
1443 p_init_msg_list => NULL ,
1444 x_return_status => l_return_status ,
1445 x_msg_count => l_msg_count ,
1446 x_msg_data => l_msg_data ,
1447 p_inventory_item_id => l_item_id ,
1448 p_vendor_id => l_vendor_id ,
1449 p_vendor_site_id => l_vendor_site_id ,
1450 p_using_organization_id => l_to_ship_to_organization_id ,
1451 x_consigned_from_supplier_flag => l_consigned_from_supplier_flag,
1452 x_enable_vmi_flag => l_enable_vmi_flag ,
1453 x_last_billing_date => l_last_billing_date ,
1454 x_consigned_billing_cycle => l_consigned_billing_cycle );
1455
1456 IF(l_consigned_from_supplier_flag = 'Y')
1457 THEN
1458 l_consigned_flag := 'Y';
1459 l_accrue_on_receipt_flag := 'N';
1460 l_invoice_close_tolerance := 100;
1461 l_closed_code := 'CLOSED FOR INVOICE';
1462 FND_MESSAGE.SET_NAME('PO', 'PO_SUP_CONS_CLOSED_REASON');
1463 l_closed_reason := FND_MESSAGE.GET;
1464 l_inspection_required_flag := 'N';
1465 l_receipt_required_flag := 'N';
1466 l_match_option := 'P';
1467 END IF;
1468 END IF;
1469 END IF; --end if shipment_type is STANDARD
1470 -- CONSIGNED FPI END
1471
1472 END IF; --end check of shipment_type
1473 --bug #2717053: end of reorganized defaulting logic for RFQs/Quotations
1474
1475 -- Bug 2761415 In 115.35, moved the receipt/invoice close tolerance
1476 -- defaulting logic (Bug 2473335) to PO_COPYDOC_S4.validate_shipment,
1477 -- since it should only apply when copying from RFQ's and quotations.
1478
1479 l_progress := '130';
1480
1481 --<INVCONV R12 START>
1482 -- if item is dual uom control , derive shipment secondary quantity and
1483 -- secondary uom if it is null
1484 x_secondary_unit_of_measure := x_po_shipment_record.secondary_unit_of_measure ;
1485 x_secondary_quantity := x_po_shipment_record.secondary_quantity ;
1486
1487 -- <Complex Work R12>: Default null for secondary fields on complex work po
1488
1489 IF ((NOT p_is_complex_work_po) AND
1490 (x_po_shipment_record.shipment_type IN ('STANDARD','PLANNED'))) THEN
1491 IF x_secondary_quantity IS NULL and l_item_id IS NOT NULL THEN
1492
1493 po_uom_s.get_secondary_uom(l_item_id,
1494 l_to_ship_to_organization_id,
1495 x_secondary_uom_code,
1496 x_secondary_unit_of_measure);
1497
1498 IF x_secondary_unit_of_measure IS NOT NULL THEN
1499 PO_UOM_S.uom_convert ( l_quantity,l_unit_meas_lookup_code,l_item_id,
1500 x_secondary_unit_of_measure ,x_secondary_quantity) ;
1501 ELSE
1502 x_secondary_quantity := null ;
1503 x_secondary_unit_of_measure := null ;
1504 END IF;
1505 END IF;
1506 ELSE
1507 x_secondary_quantity := null ;
1508 x_secondary_unit_of_measure := null ;
1509 END IF;
1510
1511 --<INVCONV R12 END>
1512
1513 INSERT INTO PO_LINE_LOCATIONS (
1514 accrue_on_receipt_flag,
1515 allow_substitute_receipts_flag,
1516 approved_date,
1517 approved_flag,
1518 attribute1,
1519 attribute10,
1520 attribute11,
1521 attribute12,
1522 attribute13,
1523 attribute14,
1524 attribute15,
1525 attribute2,
1526 attribute3,
1527 attribute4,
1528 attribute5,
1529 attribute6,
1530 attribute7,
1531 attribute8,
1532 attribute9,
1533 attribute_category,
1534 calculate_tax_flag,
1535 cancelled_by,
1536 cancel_date,
1537 cancel_flag,
1538 cancel_reason,
1539 closed_by,
1540 closed_code,
1541 closed_date,
1542 closed_flag,
1543 closed_reason,
1544 country_of_origin_code,
1545 created_by,
1546 creation_date,
1547 days_early_receipt_allowed,
1548 days_late_receipt_allowed,
1549 encumbered_date,
1550 encumbered_flag,
1551 encumber_now,
1552 end_date,
1553 enforce_ship_to_location_code,
1554 estimated_tax_amount,
1555 firm_date,
1556 firm_status_lookup_code,
1557 fob_lookup_code,
1558 freight_terms_lookup_code,
1559 from_header_id,
1560 from_line_id,
1561 from_line_location_id,
1562 global_attribute1,
1563 global_attribute10,
1564 global_attribute11,
1565 global_attribute12,
1566 global_attribute13,
1567 global_attribute14,
1568 global_attribute15,
1569 global_attribute16,
1570 global_attribute17,
1571 global_attribute18,
1572 global_attribute19,
1573 global_attribute2,
1574 global_attribute20,
1575 global_attribute3,
1576 global_attribute4,
1577 global_attribute5,
1578 global_attribute6,
1579 global_attribute7,
1580 global_attribute8,
1581 global_attribute9,
1582 global_attribute_category,
1583 government_context,
1584 inspection_required_flag,
1585 invoice_close_tolerance,
1586 last_accept_date,
1587 last_updated_by,
1588 last_update_date,
1589 last_update_login,
1590 lead_time,
1591 lead_time_unit,
1592 line_location_id,
1593 match_option,
1594 need_by_date,
1595 org_id,
1596 po_header_id,
1597 po_line_id,
1598 po_release_id,
1599 price_discount,
1600 price_override,
1601 -- Standard WHO column: program_application_id
1602 -- Standard WHO column: program_id
1603 -- Standard WHO column: program_update_date
1604 promised_date,
1605 qty_rcv_exception_code,
1606 qty_rcv_tolerance,
1607 quantity,
1608 quantity_accepted,
1609 quantity_billed,
1610 quantity_cancelled,
1611 quantity_received,
1612 quantity_rejected,
1613 quantity_shipped,
1614 receipt_days_exception_code,
1615 receipt_required_flag,
1616 receive_close_tolerance,
1617 receiving_routing_id,
1618 -- Standard WHO column: request_id
1619 shipment_num,
1620 shipment_type,
1621 ship_to_location_id,
1622 ship_to_organization_id,
1623 ship_via_lookup_code,
1624 source_shipment_id,
1625 start_date,
1626 taxable_flag,
1627 tax_code_id,
1628 tax_name, --<R12 eTax Integration>
1629 tax_user_override_flag,
1630 terms_id,
1631 -- unencumbered_quantity, -- <Removed Encumbrance FPJ>
1632 unit_meas_lookup_code,
1633 unit_of_measure_class,
1634 --togeorge 10/05/2000
1635 --added note to receiver
1636 note_to_receiver,
1637 -- start of 1548597
1638 secondary_unit_of_measure,
1639 secondary_quantity,
1640 preferred_grade,
1641 secondary_quantity_received,
1642 secondary_quantity_accepted,
1643 secondary_quantity_rejected,
1644 secondary_quantity_cancelled,
1645 -- end of 1548597
1646 consigned_flag, -- CONSIGNED FPI
1647 amount, -- Services FPJ
1648 amount_accepted, -- Services FPJ
1649 amount_billed, -- Services FPJ
1650 amount_cancelled, -- Services FPJ
1651 amount_received, -- Services FPJ
1652 amount_rejected, -- Services FPJ
1653 transaction_flow_header_id, --< Shared Proc FPJ >
1654 manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1655 --<DBI Req Fulfillment 11.5.11 Start >
1656 , shipment_closed_date
1657 , closed_for_receiving_date
1658 , closed_for_invoice_date
1659 --<DBI Req Fulfillment 11.5.11 End >
1660 ,outsourced_assembly --<SHIKYU R12>
1661 ,tax_attribute_update_code --<R12 eTax Integration>
1662 , value_basis -- <Complex Work R12>
1663 , matching_basis -- <Complex Work R12>
1664 , payment_type -- <Complex Work R12>
1665 , description -- <Complex Work R12>
1666 , work_approver_id -- <Complex Work R12>
1667 , original_shipment_id --<eTax Integration R12>
1668 ) VALUES (
1669 l_accrue_on_receipt_flag, -- CONSIGNED FPI
1670 -- x_accrue_on_receipt_flag, -- Bug: 1402128
1671 -- x_po_shipment_record.accrue_on_receipt_flag,
1672 x_po_shipment_record.allow_substitute_receipts_flag,
1673 x_po_shipment_record.approved_date,
1674 x_po_shipment_record.approved_flag,
1675 x_po_shipment_record.attribute1,
1676 x_po_shipment_record.attribute10,
1677 x_po_shipment_record.attribute11,
1678 x_po_shipment_record.attribute12,
1679 x_po_shipment_record.attribute13,
1680 x_po_shipment_record.attribute14,
1681 x_po_shipment_record.attribute15,
1682 x_po_shipment_record.attribute2,
1683 x_po_shipment_record.attribute3,
1684 x_po_shipment_record.attribute4,
1685 x_po_shipment_record.attribute5,
1686 x_po_shipment_record.attribute6,
1687 x_po_shipment_record.attribute7,
1688 x_po_shipment_record.attribute8,
1689 x_po_shipment_record.attribute9,
1690 x_po_shipment_record.attribute_category,
1691 x_po_shipment_record.calculate_tax_flag,
1692 x_po_shipment_record.cancelled_by,
1693 x_po_shipment_record.cancel_date,
1694 x_po_shipment_record.cancel_flag,
1695 x_po_shipment_record.cancel_reason,
1696 x_po_shipment_record.closed_by,
1697 --x_po_shipment_record.closed_code,
1698 l_closed_code, -- CONSIGNED FPI
1699 x_po_shipment_record.closed_date,
1700 x_po_shipment_record.closed_flag,
1701 --x_po_shipment_record.closed_reason,
1702 l_closed_reason, -- CONSIGNED FPI
1703 x_po_shipment_record.country_of_origin_code,
1704 x_po_shipment_record.created_by,
1705 x_po_shipment_record.creation_date,
1706 x_po_shipment_record.days_early_receipt_allowed,
1707 x_po_shipment_record.days_late_receipt_allowed,
1708 x_po_shipment_record.encumbered_date,
1709 x_po_shipment_record.encumbered_flag,
1710 x_po_shipment_record.encumber_now,
1711 x_po_shipment_record.end_date,
1712 x_po_shipment_record.enforce_ship_to_location_code,
1713 x_po_shipment_record.estimated_tax_amount,
1714 x_po_shipment_record.firm_date,
1715 x_po_shipment_record.firm_status_lookup_code,
1716 x_po_shipment_record.fob_lookup_code,
1717 x_po_shipment_record.freight_terms_lookup_code,
1718 x_po_shipment_record.from_header_id,
1719 x_po_shipment_record.from_line_id,
1720 x_po_shipment_record.from_line_location_id,
1721 x_po_shipment_record.global_attribute1,
1722 x_po_shipment_record.global_attribute10,
1723 x_po_shipment_record.global_attribute11,
1724 x_po_shipment_record.global_attribute12,
1725 x_po_shipment_record.global_attribute13,
1726 x_po_shipment_record.global_attribute14,
1727 x_po_shipment_record.global_attribute15,
1728 x_po_shipment_record.global_attribute16,
1729 x_po_shipment_record.global_attribute17,
1730 x_po_shipment_record.global_attribute18,
1731 x_po_shipment_record.global_attribute19,
1732 x_po_shipment_record.global_attribute2,
1733 x_po_shipment_record.global_attribute20,
1734 x_po_shipment_record.global_attribute3,
1735 x_po_shipment_record.global_attribute4,
1736 x_po_shipment_record.global_attribute5,
1737 x_po_shipment_record.global_attribute6,
1738 x_po_shipment_record.global_attribute7,
1739 x_po_shipment_record.global_attribute8,
1740 x_po_shipment_record.global_attribute9,
1741 x_po_shipment_record.global_attribute_category,
1742 x_po_shipment_record.government_context,
1743 --x_po_shipment_record.inspection_required_flag,
1744 l_inspection_required_flag, -- CONSIGNED FPI
1745 --x_po_shipment_record.invoice_close_tolerance, -- Bug 2761415
1746 l_invoice_close_tolerance, -- CONSIGNED FPI
1747 x_po_shipment_record.last_accept_date,
1748 x_po_shipment_record.last_updated_by,
1749 x_po_shipment_record.last_update_date,
1750 x_po_shipment_record.last_update_login,
1751 x_po_shipment_record.lead_time,
1752 x_po_shipment_record.lead_time_unit,
1753 x_po_shipment_record.line_location_id,
1754 --x_po_shipment_record.match_option,
1755 l_match_option, -- CONSIGNED FPI
1756 x_po_shipment_record.need_by_date,
1757 x_po_shipment_record.org_id,
1758 x_po_shipment_record.po_header_id,
1759 x_po_shipment_record.po_line_id,
1760 x_po_shipment_record.po_release_id,
1761 x_po_shipment_record.price_discount,
1762 x_po_shipment_record.price_override,
1763 -- Standard WHO column: x_po_shipment_record.program_application_id
1764 -- Standard WHO column: x_po_shipment_record.program_id
1765 -- Standard WHO column: x_po_shipment_record.program_update_date
1766 x_po_shipment_record.promised_date,
1767 x_po_shipment_record.qty_rcv_exception_code,
1768 x_po_shipment_record.qty_rcv_tolerance,
1769 l_quantity, --bug#2717053
1770 x_po_shipment_record.quantity_accepted,
1771 x_po_shipment_record.quantity_billed,
1772 x_po_shipment_record.quantity_cancelled,
1773 x_po_shipment_record.quantity_received,
1774 x_po_shipment_record.quantity_rejected,
1775 x_po_shipment_record.quantity_shipped,
1776 x_po_shipment_record.receipt_days_exception_code,
1777 --x_po_shipment_record.receipt_required_flag,
1778 l_receipt_required_flag, -- CONSIGNED FPI
1779 x_po_shipment_record.receive_close_tolerance, -- Bug 2761415
1780 x_po_shipment_record.receiving_routing_id,
1781 -- Standard WHO column: x_po_shipment_record.request_id
1782 x_po_shipment_record.shipment_num,
1783 x_po_shipment_record.shipment_type,
1784 l_to_ship_to_location_id, --bug#2717053
1785 l_to_ship_to_organization_id, --bug#2717053
1786 x_po_shipment_record.ship_via_lookup_code,
1787 x_po_shipment_record.source_shipment_id,
1788 x_po_shipment_record.start_date,
1789 x_po_shipment_record.taxable_flag,
1790 x_po_shipment_record.tax_code_id,
1791 x_po_shipment_record.tax_name, --<R12 eTax Integration>
1792 x_po_shipment_record.tax_user_override_flag,
1793 x_po_shipment_record.terms_id,
1794 -- <Removed Encumbrance FPJ>
1795 -- x_po_shipment_record.unencumbered_quantity,
1796 x_po_shipment_record.unit_meas_lookup_code,
1797 x_po_shipment_record.unit_of_measure_class,
1798 --togeorge 10/05/2000
1799 --added note to receiver
1800 x_po_shipment_record.note_to_receiver,
1801 x_secondary_unit_of_measure,
1802 x_secondary_quantity,
1803 x_po_shipment_record.preferred_grade,
1804 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1805 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1806 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1807 decode(x_secondary_unit_of_measure,NULL,NULL,0),
1808 --<INVCONV R12 END>
1809 l_consigned_flag , -- CONSIGNED FPI
1810 x_po_shipment_record.amount, -- Services FPJ (Except for ordered amt all other amts are 0)
1811 0, -- Services FPJ
1812 0, -- Services FPJ
1813 0, -- Services FPJ
1814 0, -- Services FPJ
1815 0, -- Services FPJ
1816 x_po_shipment_record.transaction_flow_header_id, --< Shared Proc FPJ >
1817 x_po_shipment_record.manual_price_change_flag --<MANUAL PRICE OVERRIDE FPJ>
1818 --<DBI Req Fulfillment 11.5.11 Start >
1819 , decode(l_closed_code,'CLOSED',
1820 nvl(x_po_shipment_record.closed_date,sysdate), null) ---shipment closed date
1821 , decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
1822 'CLOSED FOR RECEIVING',sysdate,null) --closed for receiving date
1823 , decode(l_closed_code,'CLOSED',nvl(x_po_shipment_record.closed_date,sysdate),
1824 'CLOSED FOR INVOICE',sysdate,null) ---closed for invoice date
1825 --<DBI Req Fulfillment 11.5.11 End >
1826 , x_po_shipment_record.outsourced_assembly -- <SHIKYU R12>
1827 ,g_tax_attribute_update_code --<R12 eTax Integration>
1828 -- <Complex Work R12 START> bug 4958300: added nvl for basis columns
1829 , nvl(x_po_shipment_record.value_basis, l_line_value_basis)
1830 , nvl(x_po_shipment_record.matching_basis, l_line_matching_basis)
1831 , x_po_shipment_record.payment_type
1832 , x_po_shipment_record.description
1833 , x_po_shipment_record.work_approver_id
1834 -- <Complex Work R12 END>
1835 , NVL2(g_tax_attribute_update_code, p_orig_line_location_id, null) --<eTax Integration R12>
1836 );
1837
1838 x_return_code := 0;
1839
1840 EXCEPTION
1841 WHEN OTHERS THEN
1842 copydoc_sql_error('insert_shipment', l_progress, sqlcode,
1843 x_online_report_id,
1844 x_sequence,
1845 x_line_num, x_po_shipment_record.shipment_num, 0);
1846 x_return_code := -1;
1847 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
1848 PO_DEBUG.debug_exc
1849 (p_log_head => g_module_prefix||'insert_shipment',
1850 p_progress => l_progress);
1851 END IF;
1852 END insert_shipment;
1853
1854
1855
1856 PROCEDURE insert_distribution(
1857 x_po_distribution_record IN po_distributions%ROWTYPE,
1858 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
1859 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
1860 x_line_num IN po_online_report_text.line_num%TYPE,
1861 x_shipment_num IN po_online_report_text.shipment_num%TYPE,
1862 x_return_code OUT NOCOPY NUMBER
1863 ) IS
1864
1865 l_progress VARCHAR2(4);
1866
1867 l_return_status VARCHAR2(1);
1868 l_msg_data VARCHAR2(2000);
1869 l_msg_count NUMBER;
1870
1871 --<GRANTS FPJ START>
1872 l_msg_buf VARCHAR2(2000);
1873 l_gms_po_interface_obj gms_po_interface_type;
1874 l_award_id PO_DISTRIBUTIONS_ALL.award_id%TYPE := NULL;
1875 --<GRANTS FPJ END>
1876
1877 BEGIN
1878
1879 l_progress := '000';
1880 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
1881 PO_DEBUG.debug_stmt
1882 (p_log_head => g_module_prefix||'insert_distribution',
1883 p_token => 'invoked',
1884 p_message => 'dist ID: '||
1885 x_po_distribution_record.po_distribution_id||
1886 ' online_report ID: '||x_online_report_id);
1887 END IF;
1888
1889 --<GRANTS FPJ START>
1890 SAVEPOINT insert_distribution_savepoint;
1891
1892 --If this distribution references an award_id, then
1893 --create new award distribution lines.
1894
1895 IF x_po_distribution_record.award_id IS NOT NULL THEN
1896
1897 l_progress := '010';
1898
1899 l_gms_po_interface_obj := gms_po_interface_type(
1900 gms_type_number(x_po_distribution_record.po_distribution_id),
1901 gms_type_number(x_po_distribution_record.distribution_num),
1902 gms_type_number(x_po_distribution_record.project_id),
1903 gms_type_number(x_po_distribution_record.task_id),
1904 gms_type_number(x_po_distribution_record.award_id),
1905 gms_type_number(NULL));
1906
1907 l_progress := '020';
1908
1909 PO_GMS_INTEGRATION_PVT.maintain_adl (
1910 p_api_version => 1.0,
1911 x_return_status => l_return_status,
1912 x_msg_count => l_msg_count,
1913 x_msg_data => l_msg_data,
1914 p_caller => 'COPYDOC',
1915 x_po_gms_interface_obj => l_gms_po_interface_obj);
1916
1917 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1918 l_progress := '030';
1919 RAISE FND_API.G_EXC_ERROR;
1920 END IF;
1921
1922 l_award_id := l_gms_po_interface_obj.award_set_id_out(1);
1923
1924 END IF;
1925
1926 l_progress := '040';
1927
1928 --<GRANTS FPJ END>
1929
1930 /** Bug 1003635
1931 * bgu, Oct. 06, 1999
1932 * Should not copy encumberance reserve related fields.
1933 */
1934
1935 INSERT INTO PO_DISTRIBUTIONS (
1936 accrual_account_id,
1937 accrued_flag,
1938 accrue_on_receipt_flag,
1939 amount_billed,
1940 attribute1,
1941 attribute10,
1942 attribute11,
1943 attribute12,
1944 attribute13,
1945 attribute14,
1946 attribute15,
1947 attribute2,
1948 attribute3,
1949 attribute4,
1950 attribute5,
1951 attribute6,
1952 attribute7,
1953 attribute8,
1954 attribute9,
1955 attribute_category,
1956 award_id,
1957 -- base_amount_billed, -- June 07, 1999, bgu
1958 bom_resource_id,
1959 budget_account_id,
1960 code_combination_id,
1961 created_by,
1962 creation_date,
1963 deliver_to_location_id,
1964 deliver_to_person_id,
1965 destination_context,
1966 destination_organization_id,
1967 destination_subinventory,
1968 destination_type_code,
1969 distribution_num,
1970 -- encumbered_amount, -- Oct. 06, 1999, bgu
1971 encumbered_flag,
1972 end_item_unit_number,
1973 expenditure_item_date,
1974 expenditure_organization_id,
1975 expenditure_type,
1976 failed_funds_lookup_code,
1977 -- gl_cancelled_date,
1978 gl_closed_date,
1979 gl_encumbered_date,
1980 gl_encumbered_period_name,
1981 government_context,
1982 kanban_card_id,
1983 last_updated_by,
1984 last_update_date,
1985 last_update_login,
1986 line_location_id,
1987 -- <Removed Encumbrance FPJ>
1988 -- mrc_encumbered_amount,
1989 mrc_rate,
1990 mrc_rate_date,
1991 -- <Removed Encumbrance FPJ>
1992 -- mrc_unencumbered_amount,
1993 nonrecoverable_tax,
1994 org_id,
1995 po_distribution_id,
1996 po_header_id,
1997 po_line_id,
1998 po_release_id,
1999 prevent_encumbrance_flag,
2000 -- Standard WHO column: program_application_id
2001 -- Standard WHO column: program_id
2002 -- Standard WHO column: program_update_date
2003 project_accounting_context,
2004 project_id,
2005 quantity_billed,
2006 quantity_cancelled,
2007 quantity_delivered,
2008 quantity_ordered,
2009 rate,
2010 rate_date,
2011 recoverable_tax,
2012 recovery_rate,
2013 -- Standard WHO column: request_id
2014 req_distribution_id,
2015 req_header_reference_num,
2016 req_line_reference_num,
2017 set_of_books_id,
2018 source_distribution_id,
2019 task_id,
2020 tax_recovery_override_flag,
2021 -- <Removed Encumbrance FPJ>
2022 -- unencumbered_amount,
2023 -- unencumbered_quantity,
2024 variance_account_id,
2025 wip_entity_id,
2026 wip_line_id,
2027 wip_operation_seq_num,
2028 wip_repetitive_schedule_id,
2029 wip_resource_seq_num,
2030 --togeorge 10/05/2000
2031 --added oke columns
2032 oke_contract_line_id,
2033 oke_contract_deliverable_id,
2034 amount_ordered, -- Services FPJ
2035 amount_delivered, -- Services FPJ
2036 amount_cancelled, -- Services FPJ
2037 distribution_type, -- <Encumbrance FPJ>
2038 amount_to_encumber, -- Bug 3309589
2039 dest_charge_account_id, --< Shared Proc FPJ >
2040 dest_variance_account_id --< Shared Proc FPJ >
2041 ,tax_attribute_update_code --<R12 eTax Integration>
2042 ) VALUES (
2043 x_po_distribution_record.accrual_account_id,
2044 -- x_po_distribution_record.accrued_flag, 2861250
2045 null,
2046 x_po_distribution_record.accrue_on_receipt_flag,
2047 x_po_distribution_record.amount_billed,
2048 x_po_distribution_record.attribute1,
2049 x_po_distribution_record.attribute10,
2050 x_po_distribution_record.attribute11,
2051 x_po_distribution_record.attribute12,
2052 x_po_distribution_record.attribute13,
2053 x_po_distribution_record.attribute14,
2054 x_po_distribution_record.attribute15,
2055 x_po_distribution_record.attribute2,
2056 x_po_distribution_record.attribute3,
2057 x_po_distribution_record.attribute4,
2058 x_po_distribution_record.attribute5,
2059 x_po_distribution_record.attribute6,
2060 x_po_distribution_record.attribute7,
2061 x_po_distribution_record.attribute8,
2062 x_po_distribution_record.attribute9,
2063 x_po_distribution_record.attribute_category,
2064 l_award_id, --<GRANTS FPJ>
2065 -- x_po_distribution_record.base_amount_billed,
2066 x_po_distribution_record.bom_resource_id,
2067 x_po_distribution_record.budget_account_id,
2068 x_po_distribution_record.code_combination_id,
2069 x_po_distribution_record.created_by,
2070 x_po_distribution_record.creation_date,
2071 x_po_distribution_record.deliver_to_location_id,
2072 x_po_distribution_record.deliver_to_person_id,
2073 x_po_distribution_record.destination_context,
2074 x_po_distribution_record.destination_organization_id,
2075 x_po_distribution_record.destination_subinventory,
2076 x_po_distribution_record.destination_type_code,
2077 x_po_distribution_record.distribution_num,
2078 -- x_po_distribution_record.encumbered_amount,
2079 -- x_po_distribution_record.encumbered_flag,
2080 'N',
2081 x_po_distribution_record.end_item_unit_number,
2082 x_po_distribution_record.expenditure_item_date,
2083 x_po_distribution_record.expenditure_organization_id,
2084 x_po_distribution_record.expenditure_type,
2085 x_po_distribution_record.failed_funds_lookup_code,
2086 -- x_po_distribution_record.gl_cancelled_date,
2087 x_po_distribution_record.gl_closed_date,
2088 x_po_distribution_record.gl_encumbered_date,
2089 x_po_distribution_record.gl_encumbered_period_name,
2090 x_po_distribution_record.government_context,
2091 x_po_distribution_record.kanban_card_id,
2092 x_po_distribution_record.last_updated_by,
2093 x_po_distribution_record.last_update_date,
2094 x_po_distribution_record.last_update_login,
2095 x_po_distribution_record.line_location_id,
2096 -- <Removed Encumbrance FPJ>
2097 -- x_po_distribution_record.mrc_encumbered_amount,
2098 x_po_distribution_record.mrc_rate,
2099 x_po_distribution_record.mrc_rate_date,
2100 -- <Removed Encumbrance FPJ>
2101 -- x_po_distribution_record.mrc_unencumbered_amount,
2102 x_po_distribution_record.nonrecoverable_tax,
2103 x_po_distribution_record.org_id,
2104 x_po_distribution_record.po_distribution_id,
2105 x_po_distribution_record.po_header_id,
2106 x_po_distribution_record.po_line_id,
2107 x_po_distribution_record.po_release_id,
2108 x_po_distribution_record.prevent_encumbrance_flag,
2109 -- Standard WHO column: x_po_distribution_record.program_application_id
2110 -- Standard WHO column: x_po_distribution_record.program_id
2111 -- Standard WHO column: x_po_distribution_record.program_update_date
2112 x_po_distribution_record.project_accounting_context,
2113 x_po_distribution_record.project_id,
2114 x_po_distribution_record.quantity_billed,
2115 x_po_distribution_record.quantity_cancelled,
2116 x_po_distribution_record.quantity_delivered,
2117 x_po_distribution_record.quantity_ordered,
2118 x_po_distribution_record.rate,
2119 x_po_distribution_record.rate_date,
2120 x_po_distribution_record.recoverable_tax,
2121 x_po_distribution_record.recovery_rate,
2122 -- Standard WHO column: x_po_distribution_record.request_id
2123 x_po_distribution_record.req_distribution_id,
2124 x_po_distribution_record.req_header_reference_num,
2125 x_po_distribution_record.req_line_reference_num,
2126 x_po_distribution_record.set_of_books_id,
2127 x_po_distribution_record.source_distribution_id,
2128 x_po_distribution_record.task_id,
2129 x_po_distribution_record.tax_recovery_override_flag,
2130 -- <Removed Encumbrance FPJ>
2131 -- x_po_distribution_record.unencumbered_amount,
2132 -- x_po_distribution_record.unencumbered_quantity,
2133 x_po_distribution_record.variance_account_id,
2134 x_po_distribution_record.wip_entity_id,
2135 x_po_distribution_record.wip_line_id,
2136 x_po_distribution_record.wip_operation_seq_num,
2137 x_po_distribution_record.wip_repetitive_schedule_id,
2138 x_po_distribution_record.wip_resource_seq_num,
2139 --togeorge 10/05/2000
2140 --added oke columns
2141 x_po_distribution_record.oke_contract_line_id,
2142 x_po_distribution_record.oke_contract_deliverable_id,
2143 x_po_distribution_record.amount_ordered, -- Services FPJ
2144 0, -- Services FPJ
2145 0, -- Services FPJ
2146 x_po_distribution_record.distribution_type, -- <Encumbrance FPJ>
2147 x_po_distribution_record.amount_to_encumber, -- Bug 3309589
2148 x_po_distribution_record.dest_charge_account_id, --< Shared Proc FPJ >
2149 x_po_distribution_record.dest_variance_account_id --< Shared Proc FPJ >
2150 ,NVL2(g_tax_attribute_update_code,'CREATE',NULL) --<R12 eTax Integration>
2151 );
2152
2153 x_return_code := 0;
2154
2155 l_progress := '050';
2156
2157
2158 EXCEPTION
2159
2160 --<GRANTS FPJ START>
2161 WHEN FND_API.G_EXC_ERROR THEN
2162 ROLLBACK TO SAVEPOINT insert_distribution_savepoint;
2163 FOR i in 1..FND_MSG_PUB.count_msg LOOP
2164 BEGIN
2165 l_msg_buf := SUBSTRB(FND_MSG_PUB.get(p_msg_index=>i,
2166 p_encoded=>FND_API.G_FALSE),
2167 1, 2000);
2168 online_report(x_online_report_id,
2169 x_sequence,
2170 l_msg_buf,
2171 x_line_num,
2172 x_shipment_num,
2173 x_po_distribution_record.distribution_num);
2174 END;
2175 END LOOP;
2176 x_return_code := -1;
2177 --<GRANTS FPJ END>
2178 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2179 PO_DEBUG.debug_stmt
2180 (p_log_head => g_module_prefix||'insert_distribution',
2181 p_token => l_progress,
2182 p_message => 'FND_API.g_exc_error exception caught.');
2183 END IF;
2184
2185 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2186 PO_DEBUG.debug_exc
2187 (p_log_head => g_module_prefix||'insert_distribution',
2188 p_progress => l_progress);
2189 END IF;
2190
2191 WHEN OTHERS THEN
2192 ROLLBACK TO SAVEPOINT insert_distribution_savepoint; --<GRANTS FPJ>
2193 copydoc_sql_error('insert_distribution', l_progress, sqlcode,
2194 x_online_report_id,
2195 x_sequence,
2196 x_line_num, x_shipment_num, x_po_distribution_record.distribution_num);
2197 x_return_code := -1;
2198 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2199 PO_DEBUG.debug_exc
2200 (p_log_head => g_module_prefix||'insert_distribution',
2201 p_progress => l_progress);
2202 END IF;
2203 END insert_distribution;
2204
2205
2206 PROCEDURE handle_fatal(
2207 x_return_code OUT NOCOPY NUMBER
2208 ) IS
2209 BEGIN
2210
2211 x_return_code := -1;
2212
2213 IF (po_line_cursor%ISOPEN) THEN
2214 CLOSE po_line_cursor;
2215 END IF;
2216 IF (po_shipment_cursor%ISOPEN) THEN
2217 CLOSE po_shipment_cursor;
2218 END IF;
2219 IF (po_distribution_cursor%ISOPEN) THEN
2220 CLOSE po_distribution_cursor;
2221 END IF;
2222
2223 END handle_fatal;
2224
2225 /**************************************************************
2226 ** Fetch header info from FROM PO header
2227 ** Validate header with new info if there's any
2228 ** Insert header with old and new info to the new header
2229 ** Copy attachment if necessary
2230 ***************************************************************/
2231 PROCEDURE process_header(
2232 x_action_code IN VARCHAR2,
2233 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
2234 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
2235 x_po_header_record IN OUT NOCOPY PO_HEADERS%ROWTYPE,
2236 x_from_po_header_id IN po_headers.po_header_id%TYPE,
2237 x_to_segment1 IN po_headers.segment1%TYPE,
2238 x_agent_id IN po_headers.agent_id%TYPE,
2239 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
2240 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
2241 x_copy_attachments IN BOOLEAN,
2242 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2243 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2244 x_return_code OUT NOCOPY NUMBER,
2245 x_copy_terms IN VARCHAR2
2246 ) IS
2247
2248 l_progress VARCHAR2(4);
2249 x_internal_return_code NUMBER := NULL;
2250
2251 /* FPJ CONTERMS START */
2252 l_po_from_document_type po_headers.type_lookup_code%TYPE;
2253 l_contracts_from_document_type VARCHAR2(150); --Change this in 11iX
2254 l_contracts_to_document_type VARCHAR2(150); --Change this in 11iX
2255
2256 l_return_status VARCHAR2(1);
2257 l_msg_count NUMBER;
2258 l_msg_data VARCHAR2(2000);
2259
2260 l_conterms_exist_flag VARCHAR2(1);
2261 l_temp_copy_terms VARCHAR2(1);
2262 l_internal_party_id po_headers_all.org_id%TYPE;
2263 l_internal_contact_id po_headers_all.agent_id%TYPE;
2264 l_external_party_id po_headers_all.vendor_id%TYPE;
2265 l_external_party_site_id po_headers_all.vendor_site_id%TYPE;
2266 l_external_contact_id po_headers_all.vendor_contact_id%TYPE;
2267 l_copy_contracts_attachments VARCHAR2(1) := 'N';
2268 /* FPJ CONTERMS END */
2269
2270 BEGIN
2271
2272 l_progress := '000';
2273 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2274 PO_DEBUG.debug_stmt
2275 (p_log_head => g_module_prefix||'process_header',
2276 p_token => 'invoked',
2277 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
2278 x_to_doc_subtype||' from header ID: '||x_from_po_header_id||
2279 ' to segment1: '||x_to_segment1||' header ID: '||
2280 x_po_header_record.po_header_id||' agent ID: '||x_agent_id||
2281 ' sob ID: '||x_sob_id||' inv_org ID: '||x_inv_org_id||
2282 ' online_report ID: '||x_online_report_id);
2283 END IF;
2284
2285 l_progress := '010';
2286
2287 fetch_header(x_po_header_record,
2288 x_from_po_header_id,
2289 x_online_report_id,
2290 x_sequence,
2291 x_internal_return_code);
2292
2293 -- <FPJ CONTERMS> store the source document document type
2294 l_po_from_document_type := x_po_header_record.type_lookup_code;
2295
2296 --COMMIT; < HTML Agreements R12>
2297
2298 l_progress := '020';
2299
2300 IF (x_internal_return_code = 0) THEN
2301 l_progress := '020';
2302
2303 /** New po_header_id and segment1 are created and stored in
2304 x_po_header_record ****/
2305 po_copydoc_s2.validate_header(x_action_code,
2306 x_to_doc_subtype,
2307 x_to_global_flag, -- Global Agreements (FP-I)
2308 x_po_header_record,
2309 x_to_segment1,
2310 x_agent_id,
2311 x_sob_id,
2312 x_inv_org_id,
2313 x_online_report_id,
2314 x_sequence,
2315 x_internal_return_code);
2316 -- COMMIT; < HTML Agreements R12>
2317 IF (x_internal_return_code = 0) THEN
2318 l_progress := '030';
2319
2320 insert_header(x_po_header_record,
2321 x_online_report_id,
2322 x_sequence,
2323 x_internal_return_code,
2324 x_copy_terms);
2325 -- COMMIT; < HTML Agreements R12>
2326 IF (x_internal_return_code = 0) THEN
2327 l_progress := '040';
2328
2329 /* FPJ CONTERMS START */
2330
2331 -- process user choice, proceed only if choice to copy is made
2332 IF NVL(x_copy_terms, 'N') IN ('L', 'D') THEN
2333
2334 l_progress := '050';
2335
2336 -- Additional check to see if source document has terms attached
2337 -- SQL what: select conterms_exist_flag among other columns
2338 -- SQL why: need to check if source document has terms
2339 -- SQL join: po_header_id
2340 SELECT conterms_exist_flag
2341 ,org_id
2342 ,agent_id
2343 ,vendor_id
2344 ,vendor_site_id
2345 ,vendor_contact_id
2346 INTO l_conterms_exist_flag
2347 ,l_internal_party_id
2348 ,l_internal_contact_id
2349 ,l_external_party_id
2350 ,l_external_party_site_id
2351 ,l_external_contact_id
2352 FROM po_headers
2353 WHERE po_header_id = x_from_po_header_id;
2354
2355 -- contract terms exist and user has chosen to copy
2356 IF (UPPER(l_conterms_exist_flag) = 'Y') THEN
2357
2358 l_progress := '060';
2359
2360 -- decode so that Y/N could be passed to Contracts API
2361 IF (x_copy_terms = 'L') THEN
2362 l_temp_copy_terms := 'N';
2363 ELSIF (x_copy_terms = 'D') THEN
2364 l_temp_copy_terms := 'Y';
2365 END IF;
2366
2367 /* <Bug3365562 Start>. Commented out the following piece of code.
2368 Whether or not to attach Contract Attachments needs to
2369 determined on basis of 'Contract Terms' radio buttons
2370 on Copy Doc form rather than the 'Copy Attachments'
2371 checkbox. 'Copy Attachments' checkbox will only
2372 determine whether PO Attachments are copied or not.
2373 -- check to see if attachments need to be copied
2374 IF (x_copy_attachments) THEN
2375 l_copy_contracts_attachments := 'Y';
2376 ELSE
2377 l_copy_contracts_attachments := 'N';
2378 END IF;*/
2379 -- l_copy_contracts_attachments can be put directly to 'Y' because
2380 -- this part is entered only when x_copy_terms is 'L' or 'D' in both
2381 -- of which cases we need to copy Contract Articles.
2382 l_copy_contracts_attachments := 'Y';
2383 -- <Bug3365562 End>
2384
2385 -- decode source document type for contracts
2386 IF (l_po_from_document_type IN ('CONTRACT', 'BLANKET')) THEN
2387 l_contracts_from_document_type := 'PA_'||l_po_from_document_type;
2388 ELSIF (l_po_from_document_type = 'STANDARD') THEN
2389 l_contracts_from_document_type := 'PO_'||l_po_from_document_type;
2390 END IF;
2391
2392 -- x_po_header_record now contains new po header
2393 -- decode target document type for contracts
2394 IF (x_po_header_record.type_lookup_code IN ( 'CONTRACT', 'BLANKET')) THEN
2395 l_contracts_to_document_type := 'PA_'|| x_po_header_record.type_lookup_code;
2396 ELSIF (x_po_header_record.type_lookup_code = 'STANDARD') THEN
2397 l_contracts_to_document_type := 'PO_'|| x_po_header_record.type_lookup_code;
2398 END IF;
2399
2400 -- call contracts API to copy terms and deliverables
2401 OKC_TERMS_COPY_GRP.copy_doc (
2402 p_api_version => 1.0,
2403 p_init_msg_list => FND_API.G_FALSE,
2404 p_commit => FND_API.G_FALSE,
2405 p_source_doc_type => l_contracts_from_document_type,
2406 p_source_doc_id => x_from_po_header_id,
2407 p_target_doc_type => l_contracts_to_document_type,
2408 p_target_doc_id => x_po_header_record.po_header_id,
2409 p_keep_version => l_temp_copy_terms,
2410 p_article_effective_date => SYSDATE,
2411 -- Bug 3365562. Passing this parameter as 'N' so that
2412 -- deliverable attachments do not get copied.
2413 p_copy_del_attachments_yn => 'N',
2414 p_copy_deliverables => 'Y',
2415 p_copy_doc_attachments => l_copy_contracts_attachments,
2416 p_document_number => x_po_header_record.segment1,
2417 p_internal_party_id => to_char(l_internal_party_id),
2418 p_internal_contact_id => to_char(l_internal_contact_id),
2419 p_external_party_id => to_char(l_external_party_id),
2420 p_external_party_site_id => to_char(l_external_party_site_id),
2421 p_external_contact_id => to_char(l_external_contact_id),
2422 x_return_status => l_return_status,
2423 x_msg_data => l_msg_data,
2424 x_msg_count => l_msg_count
2425 );
2426
2427 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2428 l_progress := '070';
2429
2430 -- terms copying failed , set conterms exist flag on the new doc to N
2431 UPDATE po_headers_all
2432 SET conterms_exist_flag = 'N'
2433 WHERE po_header_id = x_po_header_record.po_header_id;
2434 END IF; -- return status is error
2435
2436 -- COMMIT; < HTML Agreements R12>
2437 END IF; -- conterms exist flag
2438
2439 END IF; -- if chosen to copy
2440
2441 l_progress := '080';
2442
2443 IF (x_copy_attachments) THEN
2444 l_progress := '090';
2445 fnd_attached_documents2_pkg.copy_attachments('PO_HEADERS',
2446 x_from_po_header_id,
2447 '',
2448 '',
2449 '',
2450 '',
2451 'PO_HEADERS',
2452 x_po_header_record.po_header_id,
2453 '',
2454 '',
2455 '',
2456 '',
2457 fnd_global.user_id,
2458 fnd_global.login_id,
2459 '',
2460 '',
2461 '');
2462 -- COMMIT; < HTML Agreements R12>
2463 END IF;
2464 END IF;
2465 END IF;
2466 END IF;
2467
2468 x_return_code := x_internal_return_code;
2469
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 x_return_code := -1;
2473 copydoc_sql_error('process_header', l_progress, sqlcode,
2474 x_online_report_id,
2475 x_sequence,
2476 0, 0, 0);
2477 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2478 PO_DEBUG.debug_exc
2479 (p_log_head => g_module_prefix||'process_header',
2480 p_progress => l_progress);
2481 END IF;
2482 END process_header;
2483
2484 --<Unified Catalog R12: Start>
2485 --------------------------------------------------------------------------------
2486 --Start of Comments
2487 --Name: copy_attributes
2488 --Pre-reqs:
2489 -- None
2490 --Modifies:
2491 -- None
2492 --Locks:
2493 -- None.
2494 --Function:
2495 -- To copy the Attribute Values and TLP rows from a given document to a new
2496 -- document.
2497 --
2498 --Parameters:
2499 --IN:
2500 --p_orig_po_line_id
2501 -- The PO_LINE_ID of the document from which the data has to be copied.
2502 --p_new_po_line_id
2503 -- The PO_LINE_ID of the new document.
2504 --p_line_num
2505 -- The line number in the document which is being ccopied.
2506 --p_online_report_id
2507 -- The key to PO_ONLINE_REPORT_TEXT where error messages will be added in case
2508 -- of error
2509 --OUT:
2510 --x_sequence
2511 -- The sequence number of the error for this document. It will be incremented
2512 -- by 1 inside the procedure copydoc_sql_error()
2513 --End of Comments
2514 --------------------------------------------------------------------------------
2515 PROCEDURE copy_attributes
2516 (
2517 p_orig_po_line_id IN PO_LINES.po_line_id%TYPE
2518 , p_new_po_line_id IN PO_LINES.po_line_id%TYPE
2519 , p_line_num IN PO_LINES.line_num%TYPE
2520 , p_online_report_id IN PO_ONLINE_REPORT_TEXT.online_report_id%TYPE
2521 , x_sequence IN OUT NOCOPY PO_ONLINE_REPORT_TEXT.sequence%TYPE
2522 )
2523 IS
2524 d_mod CONSTANT VARCHAR2(100) := D_copy_attributes;
2525 l_progress VARCHAR2(4);
2526
2527 BEGIN
2528 l_progress := '010';
2529
2530 IF PO_LOG.d_proc THEN
2531 PO_LOG.proc_begin(d_mod,'p_orig_po_line_id',p_orig_po_line_id);
2532 PO_LOG.proc_begin(d_mod,'p_new_po_line_id',p_new_po_line_id);
2533 PO_LOG.proc_begin(d_mod,'p_line_num',p_line_num);
2534 PO_LOG.proc_begin(d_mod,'p_online_report_id',p_online_report_id);
2535 PO_LOG.proc_begin(d_mod,'x_sequence',x_sequence);
2536 END IF;
2537
2538 PO_ATTRIBUTE_VALUES_PVT.copy_attributes
2539 (
2540 p_orig_po_line_id => p_orig_po_line_id
2541 , p_new_po_line_id => p_new_po_line_id
2542 );
2543
2544 IF PO_LOG.d_proc THEN PO_LOG.proc_end(d_mod); END IF;
2545 EXCEPTION
2546 WHEN OTHERS THEN
2547 copydoc_sql_error(x_routine => d_mod,
2548 x_progress => l_progress,
2549 x_sqlcode => SQLCODE,
2550 x_online_report_id => p_online_report_id,
2551 x_sequence => x_sequence,
2552 x_line_num => p_line_num,
2553 x_shipment_num => 0,
2554 x_distribution_num => 0);
2555 IF PO_LOG.d_exc THEN PO_LOG.exc(d_mod,l_progress,'Unhandled exception'); END IF;
2556 RAISE;
2557 END copy_attributes;
2558 --<Unified Catalog R12: End>
2559
2560 PROCEDURE process_line(
2561 x_action_code IN VARCHAR2,
2562 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
2563 x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE,
2564 x_orig_po_line_id IN po_lines.po_line_id%TYPE,
2565 x_wip_install_status IN VARCHAR2,
2566 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
2567 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
2568 x_po_header_id IN po_lines.po_header_id%TYPE,
2569 x_copy_attachments IN BOOLEAN,
2570 x_copy_price IN BOOLEAN,
2571 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
2572 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
2573 x_return_code OUT NOCOPY NUMBER,
2574 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
2575 ) IS
2576
2577 l_progress VARCHAR2(4);
2578 x_internal_return_code NUMBER := NULL;
2579
2580 l_entity_type po_price_differentials.entity_type%TYPE; -- SERVICES FPJ
2581
2582 BEGIN
2583
2584 l_progress := '000';
2585 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2586 PO_DEBUG.debug_stmt
2587 (p_log_head => g_module_prefix||'process_line',
2588 p_token => 'invoked',
2589 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
2590 x_to_doc_subtype||' header ID: '||x_po_header_id||
2591 ' orig_line ID: '||x_orig_po_line_id||' line ID: '||
2592 x_po_line_record.po_line_id||' sob ID: '||x_sob_id||
2593 ' inv_org ID: '||x_inv_org_id||' online_report ID: '||
2594 x_online_report_id);
2595 END IF;
2596
2597 l_progress := '010';
2598
2599 /* Functionality for PA->RFQ Copy : dreddy
2600 new parameter copy_price is added */
2601 po_copydoc_s3.validate_line(x_action_code,
2602 x_to_doc_subtype,
2603 x_po_line_record,
2604 x_orig_po_line_id,
2605 x_wip_install_status,
2606 x_sob_id,
2607 x_inv_org_id,
2608 x_po_header_id,
2609 x_online_report_id,
2610 x_sequence,
2611 x_copy_price,
2612 x_internal_return_code,
2613 p_is_complex_work_po); -- <Complex Work R12>
2614 --COMMIT; < HTML Agreements R12>
2615
2616 IF (x_internal_return_code = 0) THEN
2617 l_progress := '020';
2618
2619 insert_line(x_po_line_record,
2620 x_online_report_id,
2621 x_sequence,
2622 x_internal_return_code);
2623 -- COMMIT; < HTML Agreements R12>
2624
2625 IF (x_internal_return_code = 0) THEN
2626 IF (x_copy_attachments) THEN
2627 l_progress := '030';
2628
2629 fnd_attached_documents2_pkg.copy_attachments('PO_LINES',
2630 x_orig_po_line_id,
2631 '',
2632 '',
2633 '',
2634 '',
2635 'PO_LINES',
2636 x_po_line_record.po_line_id,
2637 '',
2638 '',
2639 '',
2640 '',
2641 fnd_global.user_id,
2642 fnd_global.login_id,
2643 '',
2644 '',
2645 '');
2646 -- COMMIT; < HTML Agreements R12>
2647 END IF;
2648 END IF;
2649
2650 -- Services FPJ Start
2651 -- After line insertion copy the price differentials related to the new line
2652 -- if any price differentials exist.
2653
2654 if x_to_doc_subtype = 'STANDARD' then
2655 l_entity_type := 'PO LINE';
2656 else
2657 l_entity_type := 'BLANKET LINE';
2658 end if;
2659
2660 l_progress := '040';
2661
2662 IF PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials(p_entity_type => l_entity_type,
2663 p_entity_id => x_orig_po_line_id) THEN
2664 l_progress := '050';
2665
2666 PO_PRICE_DIFFERENTIALS_PVT.copy_price_differentials (p_to_entity_id => x_po_line_record.po_line_id,
2667 p_to_entity_type => l_entity_type,
2668 p_from_entity_id => x_orig_po_line_id,
2669 p_from_entity_type => l_entity_type );
2670 -- COMMIT; < HTML Agreements R12>
2671
2672 END IF;
2673
2674 -- Services FPJ End
2675
2676 -- <Unified Catalog R12 Start>
2677 IF x_to_doc_subtype IN ('BLANKET', 'QUOTATION') THEN
2678 copy_attributes(p_orig_po_line_id => x_orig_po_line_id,
2679 p_new_po_line_id => x_po_line_record.po_line_id,
2680 p_line_num => x_po_line_record.line_num,
2681 p_online_report_id => x_online_report_id,
2682 x_sequence => x_sequence);
2683 END IF;
2684 -- <Unified Catalog R12 End>
2685
2686 END IF; -- IF (x_internal_return_code = 0)
2687
2688 x_return_code := x_internal_return_code;
2689
2690 EXCEPTION
2691 WHEN OTHERS THEN
2692 x_return_code := -1;
2693 copydoc_sql_error('process_line', l_progress, sqlcode,
2694 x_online_report_id,
2695 x_sequence,
2696 x_po_line_record.line_num, 0, 0);
2697 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2698 PO_DEBUG.debug_exc
2699 (p_log_head => g_module_prefix||'process_line',
2700 p_progress => l_progress);
2701 END IF;
2702 END process_line;
2703
2704 --< Shared Proc FPJ Start >
2705 --
2706 PROCEDURE process_shipment
2707 (
2708 p_action_code IN VARCHAR2,
2709 p_to_doc_subtype IN VARCHAR2,
2710 p_orig_line_location_id IN NUMBER,
2711 p_po_header_id IN NUMBER,
2712 p_po_line_id IN NUMBER,
2713 p_item_category_id IN NUMBER, --< Shared Proc FPJ >
2714 p_copy_attachments IN BOOLEAN,
2715 p_copy_price IN BOOLEAN,
2716 p_online_report_id IN NUMBER,
2717 p_line_num IN NUMBER,
2718 p_inv_org_id IN NUMBER,
2719 p_item_id IN NUMBER, -- Bug 3433867
2720 x_po_shipment_record IN OUT NOCOPY PO_LINE_LOCATIONS%ROWTYPE,
2721 x_sequence IN OUT NOCOPY NUMBER,
2722 x_return_code OUT NOCOPY NUMBER,
2723 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
2724 ) IS
2725
2726 l_progress VARCHAR2(4);
2727 l_internal_return_code NUMBER := NULL;
2728
2729 -- Bug: 1402128 Declare the variables
2730
2731 l_item_id number;
2732 l_inventory_organization_id number;
2733 l_planned_item_flag varchar2(1);
2734 l_outside_op_flag varchar2(1);
2735 l_outside_op_uom_type varchar2(25);
2736 l_invoice_close_tolerance number;
2737 l_receive_close_tolerance number;
2738 l_receipt_required_flag varchar2(1);
2739 l_stock_enabled_flag varchar2(1);
2740 l_item_status varchar2(1);
2741 l_internal_orderable varchar2(1);
2742 l_purchasing_enabled varchar2(1);
2743 l_inventory_asset_flag varchar2(1);
2744
2745 l_expense_accrual_code varchar2(100);
2746 l_accrue_on_receipt_flag varchar2(1);
2747 l_poll_receipt_required_flag varchar2(1);
2748
2749 --<INVCONV R12 START>
2750 l_secondary_default_ind MTL_SYSTEM_ITEMS.TRACKING_QUANTITY_IND%TYPE ;
2751 l_grade_control_flag MTL_SYSTEM_ITEMS.GRADE_CONTROL_FLAG%TYPE ;
2752 l_secondary_unit_of_measure MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
2753 --<INVCONV R12 END>
2754
2755 BEGIN
2756
2757 l_progress := '000';
2758 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
2759 PO_DEBUG.debug_stmt
2760 (p_log_head => g_module_prefix||'process_shipment',
2761 p_token => 'invoked',
2762 p_message => 'action_code: ' ||p_action_code||' to_doc_subtype: '||
2763 p_to_doc_subtype||' header ID: '||p_po_header_id||
2764 ' line ID: '||p_po_line_id||' orig_ship ID: '||
2765 p_orig_line_location_id||' item_cat ID: '||p_item_category_id
2766 ||' inv_org ID: '||p_inv_org_id||' online_report ID: '||
2767 p_online_report_id);
2768 END IF;
2769
2770 l_progress := '020';
2771
2772 /* Functionality for PA->RFQ Copy : dreddy
2773 new parameter copy_price is added */
2774 po_copydoc_s4.validate_shipment
2775 (p_action_code => p_action_code,
2776 p_to_doc_subtype => p_to_doc_subtype,
2777 x_po_shipment_record => x_po_shipment_record,
2778 p_orig_line_location_id => p_orig_line_location_id,
2779 p_po_header_id => p_po_header_id,
2780 p_po_line_id => p_po_line_id,
2781 p_item_category_id => p_item_category_id, --< Shared Proc FPJ >
2782 p_item_id => p_item_id, -- Bug 3433867
2783 p_online_report_id => p_online_report_id,
2784 x_sequence => x_sequence,
2785 p_line_num => p_line_num,
2786 p_copy_price => p_copy_price,
2787 p_inv_org_id => p_inv_org_id, -- Bug 2761415
2788 x_return_code => l_internal_return_code,
2789 p_is_complex_work_po => p_is_complex_work_po); -- <Complex Work R12>
2790 --COMMIT; < HTML Agreements R12>
2791
2792 l_progress := '030';
2793
2794 /* Bug 1715229 : We need to initialize the accrue flag with whatever we got
2795 * back from the validate shipment procedure because if we do not go into the
2796 * if condition below , a null will be passed to the insert_shipment proc. */
2797
2798 l_accrue_on_receipt_flag := x_po_shipment_record.accrue_on_receipt_flag;
2799
2800 /* Bug: 1402128 get the accrue on receipt flag, since same code is used for
2801 copy of the PO to PO so we are restricting this for quotation only */
2802
2803 IF (p_action_code = 'QUOTATION') Then
2804
2805 l_progress := '040';
2806
2807 /* Get the item_id, accrue on receipt flag, and expense accrual code */
2808
2809 select receipt_required_flag
2810 into l_poll_receipt_required_flag
2811 from po_line_locations
2812 where line_location_id = p_orig_line_location_id;
2813
2814 l_progress := '050';
2815
2816 select expense_accrual_code into l_expense_accrual_code
2817 from po_system_parameters;
2818
2819 l_progress := '060';
2820
2821 select item_id into l_item_id from po_lines
2822 where po_line_id = p_po_line_id;
2823
2824
2825 if l_item_id is not NULL then
2826
2827 l_progress := '070';
2828
2829 -- Get the status of item and discard other values
2830
2831 po_items_sv2.get_item_details(l_item_id,
2832 p_inv_org_id,
2833 l_planned_item_flag,
2834 l_outside_op_flag,
2835 l_outside_op_uom_type,
2836 l_invoice_close_tolerance,
2837 l_receive_close_tolerance,
2838 l_receipt_required_flag,
2839 l_stock_enabled_flag,
2840 l_internal_orderable,
2841 l_purchasing_enabled,
2842 l_inventory_asset_flag,
2843 --<INVCONV R12 START>
2844 l_secondary_default_ind,
2845 l_grade_control_flag,
2846 l_secondary_unit_of_measure ) ;
2847 --<INVCONV R12 END>
2848
2849 IF (l_outside_op_flag = 'Y') THEN
2850 l_item_status := 'O'; -- Outside Processing
2851 ELSE
2852 IF (l_stock_enabled_flag = 'Y') THEN
2853 l_item_status := 'E'; -- Inventory
2854 ELSE
2855 l_item_status := 'D'; -- Expense
2856 END IF;
2857
2858 END IF;
2859
2860 -- Get the Accrue on receipt flag
2861
2862 IF (l_item_status = 'O') THEN
2863 l_accrue_on_receipt_flag := 'Y';
2864
2865 ELSIF (l_item_status = 'E') THEN
2866 l_accrue_on_receipt_flag := 'Y';
2867 ELSE
2868 IF (l_expense_accrual_code = 'PERIOD END') THEN
2869 l_accrue_on_receipt_flag := 'N';
2870 ELSE
2871 l_accrue_on_receipt_flag := l_poll_receipt_required_flag ;
2872 End IF;
2873 END IF;
2874
2875 ELSE
2876 IF (l_expense_accrual_code = 'PERIOD END') THEN
2877 l_accrue_on_receipt_flag := 'N';
2878 ELSE
2879 l_accrue_on_receipt_flag := l_poll_receipt_required_flag ;
2880 End IF;
2881
2882 End if; -- End of item_id condition
2883
2884 END IF; -- End if Action code condition
2885
2886 l_progress := '080';
2887
2888 -- Pass the accrue on receipt flag as a parameter
2889
2890 IF (l_internal_return_code = 0) THEN
2891 l_progress := '090';
2892 insert_shipment(x_po_shipment_record,
2893 p_online_report_id,
2894 x_sequence,
2895 p_line_num,
2896 l_accrue_on_receipt_flag, -- Bug: 1402128
2897 p_inv_org_id, -- Bug 2761415
2898 l_internal_return_code,
2899 p_is_complex_work_po, -- <Complex Work R12>
2900 p_orig_line_location_id); --<eTax Integration R12>
2901
2902 -- End bug fix : 1402128
2903
2904 -- COMMIT; < HTML Agreements R12>
2905
2906 IF (l_internal_return_code = 0) THEN
2907 IF (p_copy_attachments) THEN
2908 l_progress := '100';
2909 fnd_attached_documents2_pkg.copy_attachments('PO_SHIPMENTS',
2910 p_orig_line_location_id,
2911 '',
2912 '',
2913 '',
2914 '',
2915 'PO_SHIPMENTS',
2916 x_po_shipment_record.line_location_id,
2917 '',
2918 '',
2919 '',
2920 '',
2921 fnd_global.user_id,
2922 fnd_global.login_id,
2923 '',
2924 '',
2925 '');
2926 -- COMMIT; < HTML Agreements R12>
2927 END IF;
2928
2929 l_progress := '110';
2930
2931 -- Services FPJ Start
2932 -- After line insertion copy the price differentials related to the new shipment
2933 -- if the shipment has any price differentials
2934
2935 IF PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials(p_entity_type => 'PRICE BREAK',
2936 p_entity_id => p_orig_line_location_id) THEN
2937
2938 l_progress := '120';
2939
2940 PO_PRICE_DIFFERENTIALS_PVT.copy_price_differentials (p_to_entity_id => x_po_shipment_record.line_location_id,
2941 p_to_entity_type => 'PRICE BREAK',
2942 p_from_entity_id => p_orig_line_location_id,
2943 p_from_entity_type => 'PRICE BREAK' );
2944 -- COMMIT; < HTML Agreements R12>
2945
2946 END IF;
2947
2948 -- Services FPJ End
2949
2950 END IF;
2951 END IF;
2952
2953 x_return_code := l_internal_return_code;
2954
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 x_return_code := -1;
2958 copydoc_sql_error('process_shipment', l_progress, sqlcode,
2959 p_online_report_id,
2960 x_sequence,
2961 p_line_num, x_po_shipment_record.shipment_num, 0);
2962 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
2963 PO_DEBUG.debug_exc
2964 (p_log_head => g_module_prefix||'process_shipment',
2965 p_progress => l_progress);
2966 END IF;
2967 END process_shipment;
2968
2969
2970 --< Shared Proc FPJ > Added header, line, shipment records as parameters. Also
2971 -- added generate accounts boolean
2972 --<Encumbrance FPJ: add sob_id to param list>
2973 PROCEDURE process_distribution
2974 (
2975 p_action_code IN VARCHAR2,
2976 p_to_doc_subtype IN VARCHAR2,
2977 p_orig_po_distribution_id IN NUMBER,
2978 p_generate_new_accounts IN BOOLEAN,
2979 p_copy_attachments IN BOOLEAN,
2980 p_online_report_id IN NUMBER,
2981 p_po_header_rec IN PO_HEADERS%ROWTYPE,
2982 p_po_line_rec IN PO_LINES%ROWTYPE,
2983 p_po_shipment_rec IN PO_LINE_LOCATIONS%ROWTYPE,
2984 p_sob_id IN FINANCIALS_SYSTEM_PARAMETERS.set_of_books_id%TYPE,
2985 x_po_distribution_rec IN OUT NOCOPY PO_DISTRIBUTIONS%ROWTYPE,
2986 x_sequence IN OUT NOCOPY NUMBER,
2987 x_return_code OUT NOCOPY NUMBER
2988 )
2989 IS
2990
2991 l_progress VARCHAR2(4);
2992 l_internal_return_code NUMBER := NULL;
2993 l_return_status VARCHAR2(1);
2994
2995 --<ENCUMBRANCE FPJ>: use local vars for this to handle BPA case
2996 l_line_id PO_LINES_ALL.po_line_id%TYPE;
2997 l_line_num PO_LINES_ALL.line_num%TYPE;
2998 l_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE;
2999 l_shipment_num PO_LINE_LOCATIONS_ALL.shipment_num%TYPE;
3000 l_distribution_type PO_DISTRIBUTIONS_ALL.distribution_type%TYPE;
3001
3002 BEGIN
3003
3004 l_progress := '000';
3005
3006 --<ENCUMBRANCE FPJ START> Encumbered BPA dists do not have line/shipment
3007 IF nvl(p_po_header_rec.encumbrance_required_flag, 'N') = 'Y' THEN
3008 l_line_id := NULL;
3009 l_line_num := NULL;
3010 l_line_location_id := NULL;
3011 l_shipment_num := NULL;
3012 l_distribution_type := 'AGREEMENT';
3013
3014 ELSE
3015 l_line_id := p_po_line_rec.po_line_id;
3016 l_line_num := p_po_line_rec.line_num;
3017 l_line_location_id := p_po_shipment_rec.line_location_id;
3018 l_shipment_num := p_po_shipment_rec.shipment_num;
3019 l_distribution_type := p_po_shipment_rec.shipment_type;
3020
3021 END IF;
3022 --<ENCUMBRANCE FPJ END>
3023
3024 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3025 PO_DEBUG.debug_stmt
3026 (p_log_head => g_module_prefix||'process_distribution',
3027 p_token => 'invoked',
3028 p_message => 'action_code: '||p_action_code||' to_doc_subtype: '||
3029 p_to_doc_subtype||' header ID: '||
3030 p_po_header_rec.po_header_id||' line ID: '||
3031 l_line_id||' ship ID: '||
3032 l_line_location_id||' orig_dist ID: '||
3033 p_orig_po_distribution_id||' dist ID: '||
3034 x_po_distribution_rec.po_distribution_id||
3035 ' online_report ID: '||p_online_report_id);
3036 END IF;
3037
3038 IF (p_action_code = 'PO') THEN
3039
3040 --< Shared Proc FPJ Start >
3041 IF p_generate_new_accounts THEN
3042
3043 l_progress := '020';
3044
3045 PO_COPYDOC_S5.generate_accounts
3046 (x_return_status => l_return_status,
3047 p_online_report_id => p_online_report_id,
3048 p_po_header_rec => p_po_header_rec,
3049 p_po_line_rec => p_po_line_rec,
3050 p_po_shipment_rec => p_po_shipment_rec,
3051 x_po_distribution_rec => x_po_distribution_rec,
3052 x_sequence => x_sequence);
3053
3054 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
3055 l_progress := '030';
3056 RAISE FND_API.g_exc_error;
3057 END IF;
3058
3059 END IF;
3060 --< Shared Proc FPJ End >
3061
3062 l_progress := '040';
3063
3064 po_copydoc_s5.validate_distribution(p_action_code,
3065 p_to_doc_subtype,
3066 x_po_distribution_rec,
3067 p_po_header_rec.po_header_id,
3068 l_line_id,
3069 l_line_location_id,
3070 p_online_report_id,
3071 x_sequence,
3072 l_line_num,
3073 l_shipment_num,
3074 p_sob_id,
3075 l_internal_return_code);
3076 -- COMMIT; < HTML Agreements R12>
3077
3078 IF (l_internal_return_code = 0) THEN
3079
3080 l_progress := '050';
3081
3082 /* Bug#1562540: kagarwal
3083 ** Desc: When copying POs we should not be copying the Requisition reference
3084 ** in the target PO Distribution from the distribution of the source PO.
3085 ** Setting the Req reference columns as null in the source distribution record.
3086 */
3087 x_po_distribution_rec.req_distribution_id := NULL;
3088 x_po_distribution_rec.req_header_reference_num := NULL;
3089 x_po_distribution_rec.req_line_reference_num := NULL;
3090
3091 --<ENCUMBRANCE FPJ: add distribution type>
3092 x_po_distribution_rec.distribution_type := l_distribution_type;
3093
3094 insert_distribution(x_po_distribution_rec,
3095 p_online_report_id,
3096 x_sequence,
3097 l_line_num,
3098 l_shipment_num,
3099 l_internal_return_code);
3100 -- COMMIT; < HTML Agreements R12>
3101
3102 IF (l_internal_return_code = 0) THEN
3103 IF (p_copy_attachments) THEN
3104 l_progress := '060';
3105
3106 fnd_attached_documents2_pkg.copy_attachments('PO_DISTRIBUTIONS',
3107 p_orig_po_distribution_id,
3108 '',
3109 '',
3110 '',
3111 '',
3112 'PO_DISTRIBUTIONS',
3113 x_po_distribution_rec.po_distribution_id,
3114 '',
3115 '',
3116 '',
3117 '',
3118 fnd_global.user_id,
3119 fnd_global.login_id,
3120 '',
3121 '',
3122 '');
3123 -- COMMIT; < HTML Agreements R12>
3124 END IF;
3125 END IF;
3126 END IF;
3127 END IF;
3128
3129 x_return_code := l_internal_return_code;
3130
3131 EXCEPTION
3132 --< Shared Proc FPJ Start >
3133 WHEN FND_API.g_exc_error THEN
3134 x_return_code := -1;
3135 IF g_debug_stmt THEN
3136 PO_DEBUG.debug_stmt
3137 (p_log_head => g_module_prefix||'process_distribution',
3138 p_token => l_progress,
3139 p_message => 'FND_API.g_exc_error exception caught.');
3140 END IF;
3141 --< Shared Proc FPJ End >
3142 WHEN OTHERS THEN
3143 x_return_code := -1;
3144 copydoc_sql_error('process_distribution', l_progress, sqlcode,
3145 p_online_report_id,
3146 x_sequence,
3147 l_line_num,
3148 l_shipment_num,
3149 x_po_distribution_rec.distribution_num);
3150 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
3151 PO_DEBUG.debug_exc
3152 (p_log_head => g_module_prefix||'process_distribution',
3153 p_progress => l_progress);
3154 END IF;
3155 END process_distribution;
3156 --
3157 --< Shared Proc FPJ End >
3158
3159 /***************************************************************
3160 Copy header, line, shipment and distribution separately.
3161 for each, process and then copy
3162 ***************************************************************/
3163 --<HTML Agreements R12 Start>
3164 -- Added p_commit parameter so that we can control whether the api should
3165 -- commit and return the control or return without commiting the data.
3166 -- We are defaulting the value to Y so that if the code is called from FORMS
3167 -- we would mantain the existing behavior
3168 --<HTML Agreements R12 End>
3169 PROCEDURE copy_document(
3170 x_action_code IN VARCHAR2,
3171 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
3172 x_to_global_flag IN PO_HEADERS_ALL.global_agreement_flag%TYPE, -- GA
3173 x_copy_attachments IN BOOLEAN,
3174 x_copy_price IN BOOLEAN,
3175 x_from_po_header_id IN po_headers.po_header_id%TYPE,
3176 x_to_po_header_id OUT NOCOPY po_headers.po_header_id%TYPE,
3177 x_online_report_id OUT NOCOPY po_online_report_text.online_report_id%TYPE,
3178 x_to_segment1 IN OUT NOCOPY po_headers.segment1%TYPE,
3179 x_agent_id IN po_headers.agent_id%TYPE,
3180 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
3181 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
3182 x_wip_install_status IN VARCHAR2,
3183 x_return_code OUT NOCOPY NUMBER,
3184 x_copy_terms IN VARCHAR2, -- <FPJ CONTERMS>
3185 p_api_commit IN BOOLEAN, --<HTML Agreements R12>
3186 p_from_doc_type IN VARCHAR2 --<R12 eTax Integration>
3187 ) IS
3188
3189 COPYDOC_FATAL EXCEPTION;
3190
3191 x_po_header_record PO_HEADERS%ROWTYPE;
3192 x_po_line_record po_lines%ROWTYPE;
3193 x_po_shipment_record po_line_locations%ROWTYPE;
3194 x_po_distribution_record po_distributions%ROWTYPE;
3195
3196 x_orig_po_line_id po_lines.po_line_id%TYPE;
3197 x_orig_line_location_id po_line_locations.line_location_id%TYPE;
3198 x_orig_po_distribution_id po_distributions.po_distribution_id%TYPE;
3199
3200 x_line_num po_online_report_text.line_num%TYPE := NULL;
3201 x_shipment_num po_online_report_text.shipment_num%TYPE := NULL;
3202 x_distribution_num po_online_report_text.distribution_num%TYPE := NULL;
3203
3204 x_progress VARCHAR2(4);
3205 x_internal_return_code NUMBER;
3206
3207 x_sequence po_online_report_text.sequence%TYPE := 1;
3208 /* this is used only for BID quotation to calculate quantity at the line
3209 level from shipments. */
3210 x_line_quantity NUMBER;
3211 x_orig_quotation_class_code po_headers.quotation_class_code%TYPE := NULL;
3212
3213 --< Shared Proc FPJ Start >
3214 l_orig_txn_flow_header_id
3215 PO_LINE_LOCATIONS_ALL.transaction_flow_header_id%TYPE;
3216 l_generate_new_accounts BOOLEAN := FALSE;
3217 --< Shared Proc FPJ End >
3218 l_calling_program VARCHAR2(30); --<eTax Integration R12>
3219 l_return_status VARCHAR2(1); --<eTax Integration R12>
3220
3221 l_is_complex_work_po BOOLEAN; -- <Complex Work R12>
3222
3223 BEGIN
3224 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3225 PO_DEBUG.debug_stmt
3226 (p_log_head => g_module_prefix||'copy_document',
3227 p_token => 'invoked',
3228 p_message => 'action_code: ' ||x_action_code||' to_doc_subtype: '||
3229 x_to_doc_subtype||' from header ID: '||x_from_po_header_id||
3230 ' to segment1: '||x_to_segment1||' agent ID: '||x_agent_id||
3231 ' sob ID: '||x_sob_id);
3232 END IF;
3233 -- Standard start of API savepoint
3234 SAVEPOINT COPY_DOCUMENT_PVT;
3235 x_to_po_header_id := NULL;
3236
3237 x_progress := '000';
3238 BEGIN
3239 SELECT po_online_report_text_s.nextval
3240 INTO x_online_report_id
3241 FROM SYS.DUAL;
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 x_online_report_id := NULL;
3245 po_copydoc_s1.COPYDOC_sql_error('copy_documents', x_progress, sqlcode,
3246 x_online_report_id,
3247 x_sequence,
3248 0, 0, 0);
3249 END;
3250
3251 x_progress := '010';
3252
3253 IF (x_action_code = NULL OR x_from_po_header_id = NULL) THEN
3254 RAISE COPYDOC_FATAL;
3255 END IF;
3256
3257 -- <Complex Work R12 Start.
3258 x_progress := '015';
3259
3260 l_is_complex_work_po :=
3261 PO_COMPLEX_WORK_PVT.is_complex_work_po(p_po_header_id => x_from_po_header_id);
3262
3263 -- Bug#5159320 : Added a condition for x_to_doc_subtype=CONTRACT
3264 -- to allow Complex work style CPAs also to be duplicated.
3265 IF (l_is_complex_work_po AND
3266 ( NOT(
3267 (x_action_code = 'PO')
3268 AND
3269 ((x_to_doc_subtype = 'STANDARD') OR (x_to_doc_subtype = 'CONTRACT')))
3270 ))
3271 THEN
3272 RAISE COPYDOC_FATAL;
3273 END IF;
3274
3275 -- <Complex Work R12 End>
3276
3277
3278 x_progress := '020';
3279 -- <R12 eTax Integration Start>
3280 -- Initialize global variable for tax. It will
3281 -- be used for insertion into header, line, shipment and distribution
3282 IF (x_to_doc_subtype IN ('STANDARD', 'PLANNED')) THEN
3283 IF (p_from_doc_type = 'QUOTATION') THEN
3284 g_tax_attribute_update_code := 'CREATE';
3285 l_calling_program :='COPY_QUOTE';
3286 ELSE
3287 g_tax_attribute_update_code := 'COPY_AND_CREATE';
3288 l_calling_program :='COPY_DOCUMENT';
3289 END IF;
3290 END IF;
3291 -- <R12 eTax Integration End>
3292 -- lpo, 06/04/98
3293 -- Note the following conditions for validate_(header|line|shipment|distribution)():
3294 -- Precondition : x_po_(header|line|shipment|distribution)_record contains
3295 -- the record we want to copy FROM.
3296 -- Postcondition: x_po_(header|line|shipment_location|distribution)_record contains
3297 -- the NEW record ready to be inserted.
3298 -- I intend to make most of the complexity reside in the validation phase; thus leaving
3299 -- the fetch and insert phases fairly simple.
3300 process_header(x_action_code,
3301 x_to_doc_subtype,
3302 x_to_global_flag, -- Global Agreements (FP-I)
3303 x_po_header_record,
3304 x_from_po_header_id,
3305 x_to_segment1,
3306 x_agent_id,
3307 x_sob_id,
3308 x_inv_org_id,
3309 x_copy_attachments,
3310 x_online_report_id,
3311 x_sequence,
3312 x_internal_return_code,
3313 x_copy_terms); -- <FPJ CONTERMS>
3314
3315 x_progress := '030';
3316
3317 IF (x_internal_return_code <> 0) THEN
3318 RAISE COPYDOC_FATAL;
3319 END IF;
3320
3321 /**** during processing header, new po_header_id and segment1 are created **/
3322 x_to_po_header_id := x_po_header_record.po_header_id;
3323 IF (x_to_segment1 IS NULL) THEN
3324 x_to_segment1 := x_po_header_record.segment1;
3325 END IF;
3326
3327 x_progress := '040';
3328
3329 --<ENCUMBRANCE FPJ START>
3330 --Encumbrance required flag in the x_po_header_record is set in the
3331 --validate_header procedure during the header processing
3332
3333 IF nvl(x_po_header_record.encumbrance_required_flag, 'N') = 'Y' THEN
3334
3335 --Since there will be a one-one mapping between header and distribution
3336 --for an Encumbered blanket agreement to be copied to another blanket
3337 --agreement, just after the completion of headers processing, we copy
3338 --the distribution from the encumbered blanket agreement to the new
3339 --blanket agreement
3340 x_progress := '042';
3341
3342 BEGIN
3343 SELECT * INTO x_po_distribution_record
3344 FROM PO_DISTRIBUTIONS POD
3345 WHERE POD.PO_HEADER_ID = x_from_po_header_id
3346 AND POD.distribution_type = 'AGREEMENT';
3347
3348 EXCEPTION
3349 WHEN TOO_MANY_ROWS THEN
3350 po_copydoc_s1.COPYDOC_sql_error('copy_documents', x_progress, sqlcode,
3351 x_online_report_id,
3352 x_sequence,
3353 0, 0, 1);
3354 x_internal_return_code := -1;
3355 RAISE COPYDOC_FATAL;
3356 END;
3357
3358 x_progress := '044';
3359 x_orig_po_distribution_id := x_po_distribution_record.po_distribution_id;
3360
3361 process_distribution(
3362 p_action_code => x_action_code
3363 , p_to_doc_subtype => x_to_doc_subtype
3364 , p_orig_po_distribution_id => x_orig_po_distribution_id
3365 , p_generate_new_accounts => FALSE
3366 , p_copy_attachments => x_copy_attachments
3367 , p_online_report_id => x_online_report_id
3368 , p_po_header_rec => x_po_header_record
3369 , p_po_line_rec => NULL
3370 , p_po_shipment_rec => NULL
3371 , p_sob_id => x_sob_id
3372 , x_po_distribution_rec => x_po_distribution_record
3373 , x_sequence => x_sequence
3374 , x_return_code => x_internal_return_code);
3375
3376 x_progress := '046';
3377
3378 END IF; --bug 3338216: changed this from an 'else' to 'end if'
3379 --<ENCUMBRANCE FPJ END>
3380
3381 x_progress := '048';
3382
3383 SELECT quotation_class_code
3384 INTO x_orig_quotation_class_code
3385 FROM po_headers
3386 WHERE po_header_id = x_from_po_header_id;
3387
3388 OPEN po_line_cursor(x_from_po_header_id);
3389
3390 /** for every line in the PO, fetch and store in x_po_line_record,
3391 then process line.
3392 Repeat above steps until all lines have been fetched and processed **/
3393 <<LINES>>
3394 LOOP
3395
3396 FETCH po_line_cursor INTO x_po_line_record;
3397 EXIT LINES WHEN po_line_cursor%NOTFOUND;
3398
3399 x_progress := '050';
3400
3401 x_orig_po_line_id := x_po_line_record.po_line_id;
3402 x_line_num := x_po_line_record.line_num;
3403
3404 /* Functionality for PA->RFQ Copy : dreddy
3405 new parameter copy_price is added */
3406 process_line(x_action_code,
3407 x_to_doc_subtype,
3408 x_po_line_record,
3409 x_orig_po_line_id,
3410 x_wip_install_status,
3411 x_sob_id,
3412 x_inv_org_id,
3413 x_to_po_header_id,
3414 x_copy_attachments,
3415 x_copy_price,
3416 x_online_report_id,
3417 x_sequence,
3418 x_internal_return_code,
3419 l_is_complex_work_po); -- <Complex Work R12>
3420
3421 IF (x_internal_return_code = 0) THEN
3422
3423 OPEN po_shipment_cursor(x_orig_po_line_id);
3424 x_line_quantity := 0; -- initialize for each line
3425
3426 <<SHIPMENTS>>
3427 LOOP
3428
3429 FETCH po_shipment_cursor INTO x_po_shipment_record;
3430 EXIT SHIPMENTS WHEN po_shipment_cursor%NOTFOUND;
3431
3432 /** Bug 940844
3433 * bgu, July 21, 1999
3434 * For blanket agreement, only its shipment of type 'PRICE BREAK'
3435 * should be copied. We should not copy shipment of type 'BLANKET',
3436 * i.e., the shipment of BA's release.
3437 */
3438 IF (x_po_header_record.type_lookup_code='BLANKET'
3439 and (x_po_shipment_record.shipment_type='BLANKET')) then
3440 x_progress := '060';
3441 ELSE
3442 x_progress := '070';
3443
3444 x_orig_line_location_id := x_po_shipment_record.line_location_id;
3445 x_shipment_num := x_po_shipment_record.shipment_num;
3446
3447 --< Shared Proc FPJ Start >
3448 l_orig_txn_flow_header_id :=
3449 x_po_shipment_record.transaction_flow_header_id;
3450
3451 process_shipment(p_action_code => x_action_code,
3452 p_to_doc_subtype => x_to_doc_subtype,
3453 p_orig_line_location_id => x_orig_line_location_id,
3454 p_po_header_id => x_to_po_header_id,
3455 p_po_line_id => x_po_line_record.po_line_id,
3456 p_item_category_id => x_po_line_record.category_id,
3457 p_item_id => x_po_line_record.item_id,--Bug 3433867
3458 p_copy_attachments => x_copy_attachments,
3459 p_copy_price => x_copy_price,
3460 p_online_report_id => x_online_report_id,
3461 p_line_num => x_line_num,
3462 p_inv_org_id => x_inv_org_id,
3463 x_po_shipment_record => x_po_shipment_record,
3464 x_sequence => x_sequence,
3465 x_return_code => x_internal_return_code,
3466 p_is_complex_work_po => l_is_complex_work_po); -- <Complex Work R12>
3467
3468 -- Need to generate new accounts if the txn flow processed is now
3469 -- different than the original txn flow
3470 IF (NVL(l_orig_txn_flow_header_id, -99) <>
3471 NVL(x_po_shipment_record.transaction_flow_header_id, -99))
3472 THEN
3473 l_generate_new_accounts := TRUE;
3474 END IF;
3475 --< Shared Proc FPJ End >
3476
3477 /* only do this for BID quotation */
3478 IF (x_internal_return_code = 0 AND x_orig_quotation_class_code = 'BID') THEN
3479 x_line_quantity := x_line_quantity + nvl(x_po_shipment_record.quantity, 0);
3480 END IF;
3481
3482 IF (x_internal_return_code = 0) THEN
3483 OPEN po_distribution_cursor(x_orig_line_location_id);
3484
3485 <<DISTRIBUTIONS>>
3486 LOOP
3487
3488 FETCH po_distribution_cursor INTO x_po_distribution_record;
3489 EXIT DISTRIBUTIONS WHEN po_distribution_cursor%NOTFOUND;
3490
3491 x_progress := '080';
3492
3493 x_orig_po_distribution_id := x_po_distribution_record.po_distribution_id;
3494 x_distribution_num := x_po_distribution_record.distribution_num;
3495
3496 --< Shared Proc FPJ Start >
3497 process_distribution
3498 (p_action_code => x_action_code,
3499 p_to_doc_subtype => x_to_doc_subtype,
3500 p_orig_po_distribution_id => x_orig_po_distribution_id,
3501 p_generate_new_accounts => l_generate_new_accounts,
3502 p_copy_attachments => x_copy_attachments,
3503 p_online_report_id => x_online_report_id,
3504 p_po_header_rec => x_po_header_record,
3505 p_po_line_rec => x_po_line_record,
3506 p_po_shipment_rec => x_po_shipment_record,
3507 p_sob_id => x_sob_id,
3508 x_po_distribution_rec => x_po_distribution_record,
3509 x_sequence => x_sequence,
3510 x_return_code => x_internal_return_code);
3511 --< Shared Proc FPJ End >
3512
3513 END LOOP DISTRIBUTIONS;
3514 CLOSE po_distribution_cursor;
3515 END IF;
3516 END IF; -- For testing whether the shipment is for a blanket release
3517 END LOOP SHIPMENTS;
3518 CLOSE po_shipment_cursor;
3519
3520 /* got total quantity from all shipments under the line */
3521 IF (x_orig_quotation_class_code = 'BID') THEN
3522 x_progress := '090';
3523
3524 UPDATE PO_LINES
3525 SET quantity = x_line_quantity
3526 WHERE po_header_id = x_to_po_header_id
3527 AND po_line_id = x_po_line_record.po_line_id;
3528
3529 -- COMMIT; < HTML Agreements R12>
3530 END IF;
3531
3532 END IF;
3533
3534 END LOOP LINES;
3535 CLOSE po_line_cursor;
3536
3537 --Call PO Tax API to calculate tax
3538 PO_TAX_INTERFACE_PVT.calculate_tax(
3539 p_po_header_id_tbl => PO_TBL_NUMBER(x_to_po_header_id),
3540 p_po_release_id_tbl => PO_TBL_NUMBER(),
3541 p_calling_program =>l_calling_program,
3542 x_return_status => l_return_status);
3543 --<eTax Integration R12 End>
3544 x_progress := '100';
3545
3546 IF (x_sequence > 1) THEN
3547 --< Shared Proc FPJ > Corrected calculation of return code so that it will
3548 -- be negative if any records were inserted into online report table.
3549 x_return_code := 1 - x_sequence;
3550 ELSE
3551 x_return_code := 0;
3552 --< HTML Agreements R12 Start>
3553 x_progress := '110';
3554 -- We only commit if p_commit is true
3555 IF p_api_commit THEN
3556 COMMIT WORK;
3557 END IF;
3558 --< HTML Agreements R12 End>
3559 END IF;
3560
3561 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3562 PO_DEBUG.debug_stmt
3563 (p_log_head => g_module_prefix||'copy_document',
3564 p_token => 'end',
3565 p_message => 'x_return_code = '||x_return_code||
3566 ' x_sequence = '||x_sequence);
3567 END IF;
3568
3569 EXCEPTION
3570 WHEN COPYDOC_FATAL THEN
3571 ROLLBACK TO SAVEPOINT COPY_DOCUMENT_PVT; --< HTML Agreements R12>
3572 IF g_debug_stmt THEN --< Shared Proc FPJ > Add correct debugging
3573 PO_DEBUG.debug_stmt
3574 (p_log_head => g_module_prefix||'copy_document',
3575 p_token => x_progress,
3576 p_message => 'COPYDOC_FATAL exception caught.');
3577 END IF;
3578 handle_fatal(x_return_code);
3579 RAISE;
3580 WHEN OTHERS THEN
3581 ROLLBACK TO SAVEPOINT COPY_DOCUMENT_PVT; --< HTML Agreements R12>
3582 IF g_debug_unexp THEN --< Shared Proc FPJ > Add correct debugging
3583 PO_DEBUG.debug_exc
3584 (p_log_head => g_module_prefix||'copy_document',
3585 p_progress => x_progress);
3586 END IF;
3587 copydoc_sql_error('copy_document', x_progress, sqlcode,
3588 x_online_report_id,
3589 x_sequence,
3590 x_line_num,
3591 x_shipment_num,
3592 x_distribution_num);
3593 -- COMMIT; < HTML Agreements R12>
3594 handle_fatal(x_return_code);
3595 RAISE;
3596 END copy_document;
3597
3598 -- Bug 2744363 START
3599 /**
3600 * Function: po_is_dropship
3601 * Requires: none
3602 * Modifies: none
3603 * Effects: Checks whether the given PO is drop ship
3604 * Returns: TRUE if any of the shipments in the given PO are drop ship,
3605 * FALSE otherwise.
3606 **/
3607 FUNCTION po_is_dropship (
3608 p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE
3609 ) RETURN BOOLEAN IS
3610
3611 CURSOR l_po_shipment_csr(p_po_header_id PO_HEADERS_ALL.po_header_id%TYPE) IS
3612 SELECT line_location_id
3613 FROM PO_LINE_LOCATIONS
3614 WHERE po_header_id = p_po_header_id
3615 AND SHIPMENT_TYPE NOT IN ('SCHEDULED','BLANKET');
3616
3617 l_line_location_id PO_LINE_LOCATIONS.line_location_id%TYPE;
3618
3619 BEGIN
3620 OPEN l_po_shipment_csr(p_po_header_id);
3621
3622 LOOP
3623 FETCH l_po_shipment_csr INTO l_line_location_id;
3624 EXIT WHEN l_po_shipment_csr%NOTFOUND;
3625
3626 IF (OE_DROP_SHIP_GRP.po_line_location_is_drop_ship(l_line_location_id)
3627 IS NOT NULL) THEN
3628 CLOSE l_po_shipment_csr;
3629 RETURN TRUE;
3630 END IF;
3631 END LOOP;
3632
3633 CLOSE l_po_shipment_csr;
3634 RETURN FALSE;
3635 EXCEPTION
3636 WHEN OTHERS THEN
3637 CLOSE l_po_shipment_csr;
3638 RAISE;
3639 END;
3640 -- Bug 2744363 END
3641
3642 -- <CONFIG_ID FPJ START>
3643
3644 ----------------------------------------------------------------------------
3645 --Start of Comments
3646 --Name: po_has_config_id
3647 --Pre-reqs:
3648 -- None
3649 --Modifies:
3650 -- None
3651 --Locks:
3652 -- None
3653 --Function:
3654 -- Checks whether any lines on the given PO have a config ID.
3655 --Parameters:
3656 --IN:
3657 --p_po_header_id
3658 -- header ID of the PO to check
3659 --Returns:
3660 -- TRUE if any of the lines on the given PO have a config ID,
3661 -- FALSE otherwise.
3662 --Testing:
3663 -- None
3664 --End of Comments
3665 ----------------------------------------------------------------------------
3666
3667 FUNCTION po_has_config_id(
3668 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE
3669 ) RETURN BOOLEAN IS
3670
3671 l_has_config_id NUMBER;
3672
3673 BEGIN
3674
3675 SELECT count(*) INTO l_has_config_id
3676 FROM po_lines
3677 WHERE po_header_id = p_po_header_id AND
3678 supplier_ref_number IS NOT NULL;
3679
3680 IF (l_has_config_id = 0) THEN
3681 RETURN FALSE;
3682 ELSE
3683 RETURN TRUE;
3684 END IF;
3685
3686 EXCEPTION
3687
3688 WHEN OTHERS THEN
3689 PO_MESSAGE_S.sql_error('PO_HAS_CONFIG_ID', '000', sqlcode);
3690 RAISE;
3691
3692 END po_has_config_id;
3693
3694 ----------------------------------------------------------------------------
3695 --Start of Comments
3696 --Name: req_has_config_id
3697 --Pre-reqs:
3698 -- None
3699 --Modifies:
3700 -- None
3701 --Locks:
3702 -- None
3703 --Function:
3704 -- Checks whether any lines on the given requisition have a config ID.
3705 --Parameters:
3706 --IN:
3707 --p_requisition_header_id
3708 -- header ID of the requisition to check
3709 --Returns: TRUE if any of the lines on the given requisition have a config ID,
3710 -- FALSE otherwise.
3711 --Testing:
3712 -- None
3713 --End of Comments
3714 ----------------------------------------------------------------------------
3715
3716 FUNCTION req_has_config_id(
3717 p_requisition_header_id IN PO_REQUISITION_HEADERS_ALL.requisition_header_id%TYPE
3718 ) RETURN BOOLEAN IS
3719
3720 l_has_config_id NUMBER;
3721
3722 BEGIN
3723
3724 SELECT count(*) INTO l_has_config_id
3725 FROM po_requisition_lines
3726 WHERE requisition_header_id = p_requisition_header_id AND
3727 supplier_ref_number IS NOT NULL;
3728
3729 IF (l_has_config_id = 0) THEN
3730 RETURN FALSE;
3731 ELSE
3732 RETURN TRUE;
3733 END IF;
3734
3735 EXCEPTION
3736
3737 WHEN OTHERS THEN
3738 PO_MESSAGE_S.sql_error('REQ_HAS_CONFIG_ID', '000', sqlcode);
3739 RAISE;
3740
3741 END req_has_config_id;
3742
3743 -- <CONFIG_ID FPJ END>
3744 --<HTML Agreements R12 Start>
3745 ------------------------------------------------------------------------
3746 --Start of Comments
3747 --Name: val_params_and_duplicate_doc
3748 --Pre-reqs:
3749 -- None.
3750 --Modifies:
3751 -- None.
3752 --Locks:
3753 -- None Directly.
3754 --Function:
3755 -- It will act as a wrapper to the copy_document procedure. It will get
3756 -- all the required parameters for invoking copy_document procedure and
3757 -- invoke it. This procedure only supports PO/PA copy
3758 --IN:
3759 -- p_po_header_id
3760 -- Document Header Id of The Existing Document to be copied
3761 --p_copy_attachment
3762 -- Flag determining whether attachment can be copied or not
3763 --p_copy_terms
3764 -- Flag determining whether terms can be copied or not
3765 --OUT:
3766 --x_new_po_header_id
3767 -- Document Header Id of The New Document created
3768 --x_errmsg_code
3769 -- Contains the message name of the error to be shown in case of an
3770 -- expected error due to validation failure
3771 --x_message_type
3772 -- MessageType for online report message if any inserted to the table
3773 -- while procedure execution
3774 --x_text_line
3775 -- Message if any inserted to the online_report_text table while procedure
3776 -- execution.
3777 --x_return_status
3778 -- Return Status of API .
3779 --x_exception_msg
3780 -- Message in case of Unhandled Exception.
3781 --IN OUT:
3782 --x_new_segment1
3783 -- In case Numbering is manual user provides a value else if automatic
3784 -- the generated value is returned to the user
3785 --Testing:
3786 -- Refer the Unit Test Plan for 'HTML Agreements R12'
3787 --End of Comments
3788 ----------------------------------------------------------------------------
3789
3790 procedure val_params_and_duplicate_doc( p_po_header_id IN NUMBER
3791 ,p_copy_attachment IN VARCHAR2
3792 ,p_copy_terms IN VARCHAR2
3793 ,x_new_segment1 IN OUT NOCOPY VARCHAR2
3794 ,x_new_po_header_id OUT NOCOPY NUMBER
3795 ,x_errmsg_code OUT NOCOPY VARCHAR2
3796 ,x_message_type OUT NOCOPY VARCHAR2
3797 ,x_text_line OUT NOCOPY VARCHAR2
3798 ,x_return_status OUT NOCOPY VARCHAR2
3799 ,x_exception_msg OUT NOCOPY VARCHAR2)
3800 IS
3801 l_doc_org_id PO_HEADERS_ALL.ORG_ID%type;
3802 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%type;
3803 l_global_agreement_flag PO_HEADERS_ALL.global_agreement_flag%type;
3804 l_inv_org_id FINANCIALS_SYSTEM_PARAMS_ALL.inventory_organization_id%type;
3805 l_sob_id FINANCIALS_SYSTEM_PARAMS_ALL.set_of_books_id%type;
3806 l_return_code NUMBER;
3807 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%type;
3808 d_pos NUMBER;
3809 d_module VARCHAR2(70) := 'po.plsql.PO_COPYDOC_S1.VAL_PARAMS_AND_DUPLICATE_DOC';
3810 d_log_msg VARCHAR2(200);
3811 BEGIN
3812 --Initialise the variables
3813 d_pos := 0;
3814 IF (PO_LOG.d_proc) THEN
3815 PO_LOG.proc_begin(d_module);
3816 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
3817 PO_LOG.proc_begin(d_module, 'p_copy_attachment', p_copy_attachment);
3818 PO_LOG.proc_begin(d_module, 'p_copy_terms', p_copy_terms);
3819 PO_LOG.proc_begin(d_module, 'x_new_segment1', x_new_segment1);
3820 END IF;
3821 d_pos := 10;
3822 x_return_status := FND_API.g_ret_sts_success;
3823 x_errmsg_code := NULL;
3824 l_return_code := NULL;
3825 l_online_report_id := NULL;
3826 x_exception_msg := NULL;
3827
3828 d_pos := 15;
3829 --Get the required header attributes for the PO/PA
3830 SELECT POH.ORG_ID,POH.TYPE_LOOKUP_CODE,
3831 POH.GLOBAL_AGREEMENT_FLAG,
3832 FSP.INVENTORY_ORGANIZATION_ID,
3833 FSP.SET_OF_BOOKS_ID
3834 INTO l_doc_org_id, l_type_lookup_code,
3835 l_global_agreement_flag,
3836 l_inv_org_id, l_sob_id
3837 FROM po_headers_all POH, financials_system_params_all FSP
3838 WHERE po_header_id = p_po_header_id
3839 AND poh.org_id = fsp.org_id;
3840
3841 IF PO_LOG.d_stmt THEN
3842 PO_LOG.stmt(d_module,d_pos,'l_doc_org_id',l_doc_org_id);
3843 PO_LOG.stmt(d_module,d_pos,'l_type_lookup_code',l_type_lookup_code);
3844 PO_LOG.stmt(d_module,d_pos,'l_global_agreement_flag',l_global_agreement_flag);
3845 PO_LOG.stmt(d_module,d_pos,'l_inv_org_id',l_inv_org_id);
3846 PO_LOG.stmt(d_module,d_pos,'l_sob_id',l_sob_id);
3847 END IF;
3848
3849 d_pos := 20;
3850 --Check if the PO Number Provided by the user is unique
3851 IF (x_new_segment1 IS NOT NULL) THEN
3852 d_pos := 25;
3853 IF(NOT PO_CORE_S.Check_Doc_Number_Unique(x_new_segment1,
3854 l_doc_org_id,
3855 l_type_lookup_code)) THEN
3856 x_errmsg_code := 'PO_ALL_ENTER_UNIQUE_VAL';
3857 IF PO_LOG.d_exc THEN
3858 PO_LOG.exc(d_module,d_pos,'Segment1 value not unique');
3859 END IF;
3860 RAISE FND_API.g_exc_error;
3861 END IF;
3862 END IF;
3863 d_pos := 30;
3864 --Check if the PO is a drop ship PO
3865 IF(PO_COPYDOC_S1.po_is_dropship(p_po_header_id)) THEN
3866 d_pos := 35;
3867 x_errmsg_code := 'PO_NOT_SUPPORT_COPY_DROPSHIPPO';
3868 IF PO_LOG.d_exc THEN
3869 PO_LOG.exc(d_module,d_pos,'Duplicate Document not supported for DropShip PO');
3870 END IF;
3871 RAISE FND_API.g_exc_error;
3872 END IF;
3873 d_pos := 40;
3874 --Check if the PO has a config id associated with it
3875 IF(PO_COPYDOC_S1.po_has_config_id(p_po_header_id)) THEN
3876 d_pos := 45;
3877 x_errmsg_code := 'PO_CANNOT_COPY_CONFIG_ID_DOC';
3878 IF PO_LOG.d_exc THEN
3879 PO_LOG.exc(d_module,d_pos,'Duplicate Document not supported for PO with Config ID');
3880 END IF;
3881 RAISE FND_API.g_exc_error;
3882 END IF;
3883 d_pos := 50;
3884 --If validations go thru fine invoke the copy_document procedure
3885 IF PO_LOG.d_event THEN
3886 PO_LOG.event(d_module,d_pos,'Invoking PO_COPYDOC_S1.Copy Document Procedure');
3887 END IF;
3888
3889 copy_document (
3890 x_action_code => 'PO',
3891 x_to_doc_subtype => l_type_lookup_code,
3892 x_to_global_flag => l_global_agreement_flag,
3893 x_copy_attachments => PO_CORE_S.flag_to_boolean(p_copy_attachment),
3894 x_copy_price => PO_CORE_S.flag_to_boolean('N'),
3895 x_from_po_header_id => p_po_header_id,
3896 x_to_po_header_id => x_new_po_header_id,
3897 x_online_report_id => l_online_report_id,
3898 x_to_segment1 => x_new_segment1,
3899 x_agent_id => fnd_global.employee_id,
3900 x_sob_id => l_sob_id,
3901 x_inv_org_id => l_inv_org_id,
3902 x_wip_install_status => PO_CORE_S.get_product_install_status('WIP'),
3903 x_return_code => l_return_code,
3904 x_copy_terms => p_copy_terms,
3905 p_api_commit => FALSE); --Do not Commit
3906
3907 IF PO_LOG.d_event THEN
3908 PO_LOG.event(d_module,d_pos,'PO_COPYDOC_S1.Copy Document Procedure call completed');
3909 END IF;
3910 d_pos := 60;
3911 --If online_report_id is not null get the message
3912 IF (l_online_report_id is NOT NULL) THEN
3913 d_pos := 65;
3914 PO_COPYDOC_S1.ret_and_del_online_report_rec( l_online_report_id
3915 ,x_message_type
3916 ,x_text_line);
3917 IF PO_LOG.d_stmt THEN
3918 PO_LOG.stmt(d_module,d_pos,'l_online_report_id',l_online_report_id);
3919 PO_LOG.stmt(d_module,d_pos,'x_message_type',x_message_type);
3920 PO_LOG.stmt(d_module,d_pos,'x_text_line',x_text_line);
3921 END IF;
3922 END IF;
3923 d_pos := 70;
3924 IF(l_return_code < 0) THEN
3925 d_pos := 75;
3926 RAISE FND_API.g_exc_unexpected_error;
3927 END IF;
3928
3929 IF PO_LOG.d_proc THEN
3930 PO_LOG.proc_end(d_module,'x_new_segment1',x_new_segment1);
3931 PO_LOG.proc_end(d_module,'x_new_po_header_id',x_new_po_header_id);
3932 PO_LOG.proc_end(d_module,'x_errmsg_code',x_errmsg_code);
3933 PO_LOG.proc_end(d_module,'x_message_type',x_message_type);
3934 PO_LOG.proc_end(d_module,'x_text_line',x_text_line);
3935 PO_LOG.proc_end(d_module,'x_return_status',x_return_status);
3936 PO_LOG.proc_end(d_module,'x_exception_msg',x_exception_msg);
3937 PO_LOG.proc_end(d_module);
3938 END IF;
3939 EXCEPTION
3940 WHEN FND_API.g_exc_error THEN
3941 x_return_status := FND_API.g_ret_sts_error;
3942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3943 x_return_status := FND_API.g_ret_sts_unexp_error;
3944 IF(x_text_line is not null) THEN
3945 x_exception_msg := x_text_line;
3946 ELSE
3947 x_exception_msg := 'Unexpected Error in ' || d_module||'-'||d_pos;
3948 END IF;
3949 IF PO_LOG.d_exc THEN
3950 PO_LOG.exc(d_module,d_pos,x_exception_msg);
3951 END IF;
3952 WHEN OTHERS THEN
3953 x_return_status := FND_API.g_ret_sts_unexp_error;
3954 IF (l_online_report_id is NOT NULL) THEN
3955 PO_COPYDOC_S1.ret_and_del_online_report_rec( l_online_report_id
3956 ,x_message_type
3957 ,x_text_line);
3958 END IF;
3959 IF(x_text_line is not null) THEN
3960 x_exception_msg := x_text_line;
3961 ELSE
3962 x_exception_msg := 'Unhandled Exception in ' || d_module||'-'||d_pos;
3963 END IF;
3964 IF PO_LOG.d_exc THEN
3965 PO_LOG.exc(d_module,d_pos,x_exception_msg);
3966 END IF;
3967 END val_params_and_duplicate_doc;
3968 --<HTML Agreements R12 End>
3969 END po_copydoc_s1;