[Home] [Help]
PACKAGE BODY: APPS.PO_COPY_DOCUMENTS_S
Source
1 PACKAGE BODY po_copy_documents_s AS
2 /* $Header: POXDOCPB.pls 120.10 2007/12/18 07:29:01 grohit ship $ */
3 /* Type declaration for WHO information structure */
4 TYPE who_record_type IS RECORD
5 (user_id number:= 0,
6 login_id number:= 0,
7 resp_id number:= 0);
8
9 /* Type declaration for System Parameters structure */
10 TYPE system_parameters_type IS RECORD
11 (currency_code gl_sets_of_books.currency_code % type,
12 coa_id number,
13 po_encumbrance_flag varchar2(1),
14 req_encumbrance_flag varchar2(1),
15 sob_id number,
16 ship_to_location_id number,
17 bill_to_location_id number,
18 fob_lookup_code financials_system_parameters.fob_lookup_code % type,
19 freight_terms_lookup_code
20 financials_system_parameters.freight_terms_lookup_code % type,
21 terms_id number,
22 default_rate_type po_system_parameters.default_rate_type % type,
23 taxable_flag varchar2(1),
24 receiving_flag varchar2(1),
25 enforce_buyer_name_flag varchar2(1),
26 enforce_buyer_auth_flag varchar2(1),
27 line_type_id number:= null,
28 manual_po_num_type po_system_parameters.manual_po_num_type % type,
29 po_num_code po_system_parameters.user_defined_po_num_code % type,
30 price_type_lookup_code po_system_parameters.price_type_lookup_code % type,
31 invoice_close_tolerance number,
32 receive_close_tolerance number,
33 security_structure_id number,
34 expense_accrual_code po_system_parameters.price_type_lookup_code % type,
35 inventory_organization_id number,
36 rev_sort_ordering number,
37 min_rel_amount number,
38 notify_blanket_flag varchar2(1),
39 budgetary_control_flag varchar2(1),
40 user_defined_req_num_code po_system_parameters.user_defined_req_num_code % type,
41 rfq_required_flag varchar2(1),
42 manual_req_num_type po_system_parameters.manual_req_num_type % type,
43 enforce_full_lot_qty po_system_parameters.enforce_full_lot_quantities % type,
44 disposition_warning_flag varchar2(1),
45 reserve_at_completion_flag varchar2(1),
46 user_defined_rcpt_num_code
47 po_system_parameters.user_defined_receipt_num_code % type,
48 manual_rcpt_num_type po_system_parameters.manual_receipt_num_type % type,
49 use_positions_flag varchar2(1),
50 default_quote_warning_delay number,
51 inspection_required_flag varchar2(1),
52 user_defined_quote_num_code
53 po_system_parameters.user_defined_quote_num_code % type,
54 manual_quote_num_type po_system_parameters.manual_quote_num_type % type,
55 user_defined_rfq_num_code
56 po_system_parameters.user_defined_rfq_num_code % type,
57 manual_rfq_num_type po_system_parameters.manual_rfq_num_type % type,
58 ship_via_lookup_code financials_system_parameters.ship_via_lookup_code % type,
59 qty_rcv_tolerance number,
60 period_name gl_period_statuses.period_name % type);
61
62 /* Global variable declarations */
63
64 who who_record_type;
65 params system_parameters_type;
66 x_progress
67 varchar2(4):= null;
68
69
70
71 /* Private Procedure prototypes */
72 /* replace this with a simple version in future */
73 PROCEDURE get_copy_defaults;
74
75 /* publish copy header, lines, shipments, distributions in the future */
76
77 PROCEDURE copy_header(
78 x_po_header_id IN number,
79 x_new_document_type IN varchar2,
80 x_new_document_subtype IN varchar2,
81 x_new_supplier_id IN number,
82 x_new_supplier_site_id IN number,
83 x_new_supplier_contact_id IN number,
84 x_copy_mode IN varchar2,
85 x_copy_attachments IN varchar2,
86 x_new_document_num IN varchar2,
87 x_new_po_header_id OUT NOCOPY number,
88 x_actual_document_num IN OUT NOCOPY varchar2);
89
90 procedure copy_lines(x_from_po_header_id number,
91 x_to_po_header_id number,
92 x_copy_mode varchar2,
93 x_copy_attachments varchar2,
94 x_new_document_type varchar2,
95 x_new_supplier_id IN number,
96 x_new_supplier_site_id IN number);
97
98 procedure copy_shipments(x_from_po_line_id number,
99 x_to_po_line_id number,
100 x_copy_mode varchar2,
101 x_copy_attachments varchar2,
102 x_new_document_type varchar2,
103 x_new_tax_flag varchar2,
104 x_tax_id ap_tax_codes.tax_id%type);
105
106
107 /*
108 * =========================================================================
109 *
110 * PROCEDURE NAME: copy_header
111 *
112 * ==========================================================================
113 */
114
115 PROCEDURE copy_header(
116 x_po_header_id IN number,
117 x_new_document_type IN varchar2,
118 x_new_document_subtype IN varchar2,
119 x_new_supplier_id IN number,
120 x_new_supplier_site_id IN number,
121 x_new_supplier_contact_id IN number,
122 x_copy_mode IN varchar2,
123 x_copy_attachments IN varchar2,
124 x_new_document_num IN varchar2,
125 x_new_po_header_id OUT NOCOPY number,
126 x_actual_document_num IN OUT NOCOPY varchar2)
127 IS
128
129 x_local_po_header_id number;
130 x_default_quote_warning_delay number;
131
132 -- bug5176308
133 l_unique_id_tbl_name PO_UNIQUE_IDENTIFIER_CONT_ALL.table_name%TYPE;
134
135 BEGIN
136 x_progress:= '001';
137 IF(x_new_document_Num is NULL) THEN
138 /*
139 * new document number not specified specified, get the new number from the
140 * PO UNIQUE ID control tables, even if you are using MANUAL numbering
141 */
142
143 x_progress:= '002';
144
145 -- bug5176308 START
146 -- Use API to get the new document number
147
148 IF (x_new_document_type IN ('STANDARD', 'BLANKET', 'CONTRACT')) THEN
149 l_unique_id_tbl_name := 'PO_HEADERS';
150 ELSIF (x_new_document_type = 'QUOTATION') THEN
151 l_unique_id_tbl_name := 'PO_HEADERS_QUOTE';
152 ELSIF (x_new_document_type = 'RFQ') THEN
153 l_unique_id_tbl_name := 'PO_HEADERS_RFQ';
154 END IF;
155
156 x_actual_document_num :=
157 PO_CORE_SV1.default_po_unique_identifier
158 ( x_table_name => l_unique_id_tbl_name
159 );
160
161 -- bug5176308 END
162
163
164 ELSE
165 /* otherwise, use the document number passed to proc */
166 x_actual_document_num:= x_new_document_num;
167
168 END IF;
169
170 IF(x_new_document_type = 'QUOTATION') THEN
171 /* get the default quote warning delay since it is mandatory for quotes */
172
173 x_progress:= '004';
174 SELECT nvl(default_quote_warning_delay, 0)
175 INTO x_default_quote_warning_delay
176 FROM po_system_parameters;
177 ELSE
178 x_default_quote_warning_delay:= null;
179 END IF;
180
181 x_progress:= '005';
182
183 select po_headers_s.nextval
184 into x_local_po_header_id
185 from dual;
186
187 x_new_po_header_id:= x_local_po_header_id;
188
189 x_progress:= '006';
190
191 insert into po_headers(
192 PO_HEADER_ID
193 ,AGENT_ID
194 ,TYPE_LOOKUP_CODE
195 ,LAST_UPDATE_DATE
196 ,LAST_UPDATED_BY
197 ,SEGMENT1
198 ,SUMMARY_FLAG
199 ,ENABLED_FLAG
200 ,SEGMENT2
201 ,SEGMENT3
202 ,SEGMENT4
203 ,SEGMENT5
204 ,START_DATE_ACTIVE
205 ,END_DATE_ACTIVE
206 ,LAST_UPDATE_LOGIN
207 ,CREATION_DATE
208 ,CREATED_BY
209 ,VENDOR_ID
210 ,VENDOR_SITE_ID
211 ,VENDOR_CONTACT_ID
212 ,SHIP_TO_LOCATION_ID
213 ,BILL_TO_LOCATION_ID
214 ,TERMS_ID
215 ,SHIP_VIA_LOOKUP_CODE
216 ,FOB_LOOKUP_CODE
217 ,FREIGHT_TERMS_LOOKUP_CODE
218 ,STATUS_LOOKUP_CODE
219 ,CURRENCY_CODE
220 ,RATE_TYPE
221 ,RATE_DATE
222 ,RATE
223 ,FROM_HEADER_ID
224 ,FROM_TYPE_LOOKUP_CODE
225 ,START_DATE
226 ,END_DATE
227 ,BLANKET_TOTAL_AMOUNT
228 ,AUTHORIZATION_STATUS
229 ,REVISION_NUM
230 ,REVISED_DATE
231 ,APPROVED_FLAG
232 ,APPROVED_DATE
233 ,NOTE_TO_AUTHORIZER
234 ,NOTE_TO_VENDOR
235 ,NOTE_TO_RECEIVER
236 ,PRINT_COUNT
237 ,PRINTED_DATE
238 ,VENDOR_ORDER_NUM
239 ,CONFIRMING_ORDER_FLAG
240 ,COMMENTS
241 ,REPLY_DATE
242 ,REPLY_METHOD_LOOKUP_CODE
243 ,RFQ_CLOSE_DATE
244 ,QUOTE_TYPE_LOOKUP_CODE
245 ,QUOTE_WARNING_DELAY_UNIT
246 ,QUOTE_WARNING_DELAY
247 ,QUOTE_VENDOR_QUOTE_NUMBER
248 ,ACCEPTANCE_REQUIRED_FLAG
249 ,ACCEPTANCE_DUE_DATE
250 ,USER_HOLD_FLAG
251 ,CANCEL_FLAG
252 ,FIRM_STATUS_LOOKUP_CODE
253 ,FIRM_DATE
254 ,FROZEN_FLAG
255 ,ATTRIBUTE_CATEGORY
256 ,ATTRIBUTE1
257 ,ATTRIBUTE2
258 ,ATTRIBUTE3
259 ,ATTRIBUTE4
260 ,ATTRIBUTE5
261 ,ATTRIBUTE6
262 ,ATTRIBUTE7
263 ,ATTRIBUTE8
264 ,ATTRIBUTE9
265 ,ATTRIBUTE10
266 ,ATTRIBUTE11
267 ,ATTRIBUTE12
268 ,ATTRIBUTE13
269 ,ATTRIBUTE14
270 ,ATTRIBUTE15
271 ,AMOUNT_LIMIT
272 ,APPROVAL_REQUIRED_FLAG
273 ,MIN_RELEASE_AMOUNT
274 ,QUOTATION_CLASS_CODE
275 ,CLOSED_CODE
276 ,GOVERNMENT_CONTEXT
277 ,PROGRAM_APPLICATION_ID
278 ,PROGRAM_ID
279 ,PROGRAM_UPDATE_DATE
280 ,REQUEST_ID
281 ,CLOSED_DATE
282 ,ORG_ID
283 ,DOCUMENT_CREATION_METHOD -- <DBI FPJ>
284 ,STYLE_ID --<R12 STYLES PHASE II >
285 ,CREATED_LANGUAGE --Bug#5401155
286 )
287 SELECT
288 x_local_po_header_id
289 ,AGENT_ID
290 ,x_new_document_type
291 ,sysdate
292 ,who.user_id
293 ,x_actual_document_num
294 ,'N'
295 ,'Y'
296 ,SEGMENT2
297 ,SEGMENT3
298 ,SEGMENT4
299 ,SEGMENT5
300 ,START_DATE_ACTIVE
301 ,END_DATE_ACTIVE
302 ,who.login_id
303 ,sysdate
304 ,who.user_id
305 -- keep original supplier if new supplier is null
306 ,
307 nvl(x_new_supplier_id, VENDOR_ID)
308 --keep original supplier site if new supplier site is null
309 ,nvl(x_new_supplier_site_id, VENDOR_SITE_ID)
310 --only keep original supplier contact if copying to the same supplier site
311 ,nvl(x_new_supplier_contact_id, VENDOR_CONTACT_ID)
312 ,SHIP_TO_LOCATION_ID
313 ,BILL_TO_LOCATION_ID
314 ,TERMS_ID
315 ,SHIP_VIA_LOOKUP_CODE
316 ,FOB_LOOKUP_CODE
317 ,FREIGHT_TERMS_LOOKUP_CODE
318 -- set status lookup to incomplete
319 ,'I'
320 ,CURRENCY_CODE
321 ,RATE_TYPE
322 ,RATE_DATE
323 ,RATE
324 -- set from header id and type to original doc type
325 ,po_header_id
326 ,TYPE_LOOKUP_CODE
327 ,START_DATE
328 ,END_DATE
329 ,BLANKET_TOTAL_AMOUNT
330 ,AUTHORIZATION_STATUS
331 -- reset revision num, revised date, approved flag, approved date
332 ,null
333 ,null
334 ,null
335 ,null
336 ,NOTE_TO_AUTHORIZER
337 ,NOTE_TO_VENDOR
338 ,NOTE_TO_RECEIVER
339 -- reset print count and date
340 ,null
341 ,null
342 ,VENDOR_ORDER_NUM
343 ,CONFIRMING_ORDER_FLAG
344 ,COMMENTS
345 ,REPLY_DATE
346 ,REPLY_METHOD_LOOKUP_CODE
347 ,RFQ_CLOSE_DATE
348 ,x_new_document_subtype
349 ,QUOTE_WARNING_DELAY_UNIT
350 ,x_default_quote_warning_delay
351 ,QUOTE_VENDOR_QUOTE_NUMBER
352 ,ACCEPTANCE_REQUIRED_FLAG
353 ,ACCEPTANCE_DUE_DATE
354 ,USER_HOLD_FLAG
355 -- reset cancel flag
356 ,NULL
357 ,FIRM_STATUS_LOOKUP_CODE
358 ,FIRM_DATE
359 -- reset frozen flag
360 ,Null
361 ,ATTRIBUTE_CATEGORY
362 ,ATTRIBUTE1
363 ,ATTRIBUTE2
364 ,ATTRIBUTE3
365 ,ATTRIBUTE4
366 ,ATTRIBUTE5
367 ,ATTRIBUTE6
368 ,ATTRIBUTE7
369 ,ATTRIBUTE8
370 ,ATTRIBUTE9
371 ,ATTRIBUTE10
372 ,ATTRIBUTE11
373 ,ATTRIBUTE12
374 ,ATTRIBUTE13
375 ,ATTRIBUTE14
376 ,ATTRIBUTE15
377 ,AMOUNT_LIMIT
381 -- reset closed code
378 ,APPROVAL_REQUIRED_FLAG
379 ,MIN_RELEASE_AMOUNT
380 ,QUOTATION_CLASS_CODE
382 ,null
383 ,GOVERNMENT_CONTEXT
384 ,PROGRAM_APPLICATION_ID
385 ,PROGRAM_ID
386 ,PROGRAM_UPDATE_DATE
387 ,REQUEST_ID
388 ,CLOSED_DATE
389 ,ORG_ID
390 -- Bug 3648268. Using lookup code instead of hardcoded value
391 ,'COPY_DOCUMENT' -- <DBI FPJ>
392 ,STYLE_ID --<R12 STYLES PHASE II >
393 ,decode(x_new_document_type, 'QUOTATION', nvl(created_language, PO_ATTRIBUTE_VALUES_PVT.get_base_lang), created_language) --Bug#5401155
394 from po_headers
395 where po_header_id = x_po_header_id;
396
397 IF(x_copy_attachments = 'Y') THEN
398
399 x_progress:= '007';
400
401 --API to copy attachments from requisition line to po line
402 fnd_attached_documents2_pkg.
403 copy_attachments('PO_HEADERS',
404 x_po_header_id,
405 '',
406 '',
407 '',
408 '',
409 'PO_HEADERS',
410 x_local_po_header_id,
411 '',
412 '',
413 '',
414 '',
415 who.user_id,
416 who.login_id,
417 '',
418 '',
419 '');
420 END IF;
421
422 EXCEPTION
423 WHEN OTHERS THEN
424 po_message_s.sql_error('COPY header', x_progress, sqlcode);
425 raise;
426
427 END copy_header;
428
429
430
431 /*
432 * ===========================================================================
433 * = NAME: get copy defaults DESC: replace this with Kim's call in future
434 *
435 * ==========================================================================
436 */
437
438 PROCEDURE get_copy_defaults IS
439 BEGIN
440
441 x_progress:= '000';
442
443 /* Get WHO column values */
444 who.user_id:= nvl(fnd_global.user_id, 0);
445 who.login_id:= nvl(fnd_global.login_id, 0);
446 who.resp_id:= nvl(fnd_global.resp_id, 0);
447
448 x_progress:= '001';
449
450 /* Get system defaults */
451 po_core_s.get_po_parameters(params.currency_code,
452 params.coa_id,
453 params.po_encumbrance_flag,
454 params.req_encumbrance_flag,
455 params.sob_id,
456 params.ship_to_location_id,
457 params.bill_to_location_id,
458 params.fob_lookup_code,
459 params.freight_terms_lookup_code,
460 params.terms_id,
461 params.default_rate_type,
462 params.taxable_flag,
463 params.receiving_flag,
464 params.enforce_buyer_name_flag,
465 params.enforce_buyer_auth_flag,
466 params.line_type_id,
467 params.manual_po_num_type,
468 params.po_num_code,
469 params.price_type_lookup_code,
470 params.invoice_close_tolerance,
471 params.receive_close_tolerance,
472 params.security_structure_id,
473 params.expense_accrual_code,
474 params.inventory_organization_id,
475 params.rev_sort_ordering,
476 params.min_rel_amount,
477 params.notify_blanket_flag,
478 params.budgetary_control_flag,
479 params.user_defined_req_num_code,
480 params.rfq_required_flag,
481 params.manual_req_num_type,
482 params.enforce_full_lot_qty,
483 params.disposition_warning_flag,
484 params.reserve_at_completion_flag,
485 params.user_defined_rcpt_num_code,
486 params.manual_rcpt_num_type,
487 params.use_positions_flag,
488 params.default_quote_warning_delay,
489 params.inspection_required_flag,
490 params.user_defined_quote_num_code,
491 params.manual_quote_num_type,
492 params.user_defined_rfq_num_code,
493 params.manual_rfq_num_type,
494 params.ship_via_lookup_code,
495 params.qty_rcv_tolerance);
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 po_message_s.sql_error('GET COPY DEFAULTS', x_progress, sqlcode);
500 raise;
501
502 END get_copy_defaults;
503
504
505
506 /*
507 * ===========================================================================
508 *
509 * PROCEDURE NAME: copy_lines
510 *
511 * ===========================================================================
512 */
513
514 procedure
515 copy_lines(x_from_po_header_id IN number,
516 x_to_po_header_id IN number,
517 x_copy_mode IN varchar2,
518 x_copy_attachments IN varchar2,
519 x_new_document_type IN varchar2,
520 x_new_supplier_id IN number,
521 x_new_supplier_site_id IN number) IS
522
523 x_from_po_line_id number;
524 x_to_po_line_id number;
525
526 /* Additional tax variables for R11 tax defaulting functionality */
527 x_tax_id ap_tax_codes.tax_id%type;
528 x_allow_tax_code_override_flag gl_tax_option_accounts.allow_tax_code_override_flag%type;
529 x_ship_to_location_id po_headers.ship_to_location_id%type;
533
530 x_ship_to_loc_org_id hr_locations.inventory_organization_id%type;
531 x_item_id mtl_system_items.inventory_item_id%type;
532 x_new_tax_flag varchar2(1):=null;
534 -- Bug#5401155
535 l_po_category_id po_lines.category_id%TYPE;
536 l_ip_category_id po_lines.ip_category_id%TYPE;
537 l_item_description po_lines.item_description%TYPE;
538 l_item_id po_lines.item_id%TYPE;
539 l_org_id po_lines.org_id%TYPE;
540
541 -- Bug#5401155: fetch category_id, description, item_id, org_id - needed to create default attributes
542 CURSOR lines_cursor(x_get_po_header_id number) IS
543 SELECT po_line_id, category_id, item_description, item_id, org_id
544 FROM po_lines pl
545 WHERE pl.po_header_id = x_get_po_header_id
546 ORDER BY pl.po_line_id;
547
548 BEGIN
549
550 x_progress:= '001';
551 --dbms_output.put_line('progress 001');
552
553 OPEN lines_cursor(x_from_po_header_id);
554 --dbms_output.put_line('progress 002' || x_from_po_header_id);
555 x_progress:= '002';
556
557 LOOP
558 --dbms_output.put_line('progress 003');
559
560 FETCH lines_cursor INTO x_from_po_line_id, l_po_category_id, l_item_description, l_item_id, l_org_id;
561 EXIT WHEN lines_cursor % notfound;
562
563 --dbms_output.put_line('progress 005');
564 x_progress:= '004';
565
566 /* get the new line id */
567 select po_lines_s.nextval
568 into x_to_po_line_id
569 from dual;
570
571 -- R11: User-defined tax defaulting enhancements. When copying a document where
572 -- new information is available which may affect the tax on the document,
573 -- re-default the tax here. For example when copying RFQs to Quotations,
574 -- new supplier and supplier site information becomes available. Tax should
575 -- be re-defaulted to take this new information into account.
576 -- If a new tax name is needed this is also passed into the copy_shipments
577 -- procedure and used on the new document shipment.
578
579
580 IF (x_new_supplier_id is not NULL) OR (x_new_supplier_site_id is not NULL) THEN
581
582 /* Bug 1484350 draising
583 Description: x_new_tax_flag is commented coz. this flag is no more in use.
584 the logic is changed so that if there is no tax_code defined for new supplier
585 in the quotation tax code from RFQ will be copied.
586 otherwise if tax code is defined for the new supplier it will consider the
587 default tax_code of new supplier.
588 */
589
590
591 -- x_new_tax_flag := 'Y';
592
593 -- bug: 1534559 Handle the exception when ship_to_loc is not found
594
595 BEGIN
596 SELECT poh.ship_to_location_id
597 INTO x_ship_to_location_id
598 FROM po_headers poh
599 WHERE poh.po_header_id = x_from_po_header_id;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 x_ship_to_location_id := NULL;
603 END;
604
605 -- bug: 1534559 Handle the exception when ship_to_org is not found
606
607 BEGIN
608 SELECT hrl.inventory_organization_id
609 INTO x_ship_to_loc_org_id
610 FROM hr_locations hrl
611 WHERE hrl.location_id = x_ship_to_location_id;
612 EXCEPTION
613 WHEN NO_DATA_FOUND THEN
614 x_ship_to_loc_org_id := NULL;
615 END;
616
617 SELECT pol.item_id
618 INTO x_item_id
619 FROM po_lines pol
620 WHERE pol.po_line_id = x_from_po_line_id;
621
622 END IF;
623
624 x_progress:= '005';
625 --dbms_output.put_line('progress 005');
626
627 -- Bug#5401155
628 -- Get the ip category id: From RFQ you can create only a quote.
629 IF(x_new_document_type = 'QUOTATION') THEN
630 PO_ATTRIBUTE_VALUES_PVT.get_ip_category_id(p_po_category_id => l_po_category_id,
631 x_ip_category_id => l_ip_category_id);
632 END IF;
633
634 /* create the new line */
635 -- <SERVICES FPJ>
636 -- Added order_type_lookup_code, purchase_basis and
637 -- matching_basis as part of denormalization
638 insert into po_lines(
639 PO_LINE_ID
640 ,LAST_UPDATE_DATE
641 ,LAST_UPDATED_BY
642 ,PO_HEADER_ID
643 ,LINE_TYPE_ID
644 ,LINE_NUM
645 ,LAST_UPDATE_LOGIN
646 ,creation_date
647 ,created_by
648 ,ITEM_ID
649 ,ITEM_REVISION
650 ,CATEGORY_ID
651 ,ITEM_DESCRIPTION
652 ,UNIT_MEAS_LOOKUP_CODE
653 ,QUANTITY_COMMITTED
654 ,COMMITTED_AMOUNT
655 ,ALLOW_PRICE_OVERRIDE_FLAG
656 ,NOT_TO_EXCEED_PRICE
657 ,LIST_PRICE_PER_UNIT
658 ,UNIT_PRICE
659 ,QUANTITY
660 ,UN_NUMBER_ID
661 ,HAZARD_CLASS_ID
662 ,NOTE_TO_VENDOR
666 ,MAX_ORDER_QUANTITY
663 ,FROM_HEADER_ID
664 ,FROM_LINE_ID
665 ,MIN_ORDER_QUANTITY
667 ,QTY_RCV_TOLERANCE
668 ,OVER_TOLERANCE_ERROR_FLAG
669 ,MARKET_PRICE
670 ,UNORDERED_FLAG
671 ,CLOSED_FLAG
672 ,USER_HOLD_FLAG
673 ,CANCEL_FLAG
674 ,CANCELLED_BY
675 ,CANCEL_DATE
676 ,CANCEL_REASON
677 ,FIRM_STATUS_LOOKUP_CODE
678 ,FIRM_DATE
679 ,VENDOR_PRODUCT_NUM
680 ,CONTRACT_NUM
681 ,TAXABLE_FLAG
682 ,TAX_CODE_ID
683 ,TYPE_1099
684 ,CAPITAL_EXPENSE_FLAG
685 ,NEGOTIATED_BY_PREPARER_FLAG
686 ,ATTRIBUTE_CATEGORY
687 ,ATTRIBUTE1
688 ,ATTRIBUTE2
689 ,ATTRIBUTE3
690 ,ATTRIBUTE4
691 ,ATTRIBUTE5
692 ,ATTRIBUTE6
693 ,ATTRIBUTE7
694 ,ATTRIBUTE8
695 ,ATTRIBUTE9
696 ,ATTRIBUTE10
697 ,REFERENCE_NUM
698 ,ATTRIBUTE11
699 ,ATTRIBUTE12
700 ,ATTRIBUTE13
701 ,ATTRIBUTE14
702 ,ATTRIBUTE15
703 ,MIN_RELEASE_AMOUNT
704 ,PRICE_TYPE_LOOKUP_CODE
705 ,CLOSED_CODE
706 ,PRICE_BREAK_LOOKUP_CODE
707 ,GOVERNMENT_CONTEXT
708 ,REQUEST_ID
709 ,PROGRAM_APPLICATION_ID
710 ,PROGRAM_ID
711 ,PROGRAM_UPDATE_DATE
712 ,CLOSED_DATE
713 ,CLOSED_REASON
714 ,CLOSED_BY
715 ,TRANSACTION_REASON_CODE
716 /*
717 * project_id and task_id fields are added to the the insert statement which
718 * will enable the Copy Document option in RFQ's form to copy both these
719 * fields
720 */
721 ,project_id
722 ,task_id
723 ,ORG_ID
724 --togeorge 10/05/2000
725 --added oke columns
726 ,oke_contract_header_id
727 ,oke_contract_version_id
728 ,order_type_lookup_code
729 ,purchase_basis
730 ,matching_basis
731 ,ip_category_id --Bug#5401155
732 )
733 select
734 x_to_po_line_id
735 ,sysdate
736 ,who.user_id
737 ,x_to_po_header_id
738 ,LINE_TYPE_ID
739 ,LINE_NUM
740 ,LAST_UPDATE_LOGIN
741 ,sysdate
742 ,who.user_id
743 ,ITEM_ID
744 ,ITEM_REVISION
745 ,CATEGORY_ID
746 ,ITEM_DESCRIPTION
747 ,UNIT_MEAS_LOOKUP_CODE
748 ,QUANTITY_COMMITTED
749 ,COMMITTED_AMOUNT
750 ,ALLOW_PRICE_OVERRIDE_FLAG
751 ,NOT_TO_EXCEED_PRICE
752 ,LIST_PRICE_PER_UNIT
753 ,nvl(UNIT_PRICE,0)
754 ,QUANTITY
755 ,UN_NUMBER_ID
756 ,HAZARD_CLASS_ID
757 ,NOTE_TO_VENDOR
758 ,x_from_po_header_id
759 ,x_from_po_line_id
760 ,MIN_ORDER_QUANTITY
761 ,MAX_ORDER_QUANTITY
762 ,QTY_RCV_TOLERANCE
763 ,OVER_TOLERANCE_ERROR_FLAG
764 ,MARKET_PRICE
765 ,UNORDERED_FLAG
766 ,'N'
767 ,'N'
768 ,'N'
769 ,null
770 ,null
771 ,null
772 ,FIRM_STATUS_LOOKUP_CODE
773 ,FIRM_DATE
774 ,VENDOR_PRODUCT_NUM
775 ,CONTRACT_NUM
776 ,TAXABLE_FLAG
777 ,decode(x_tax_id,null,TAX_CODE_ID,x_tax_id) /* Bug 1484350 draising */
778 -- ,decode (x_new_tax_flag, 'Y', x_tax_id, TAX_CODE_ID)
779 ,TYPE_1099
780 ,CAPITAL_EXPENSE_FLAG
781 ,NEGOTIATED_BY_PREPARER_FLAG
782 ,ATTRIBUTE_CATEGORY
783 ,ATTRIBUTE1
784 ,ATTRIBUTE2
785 ,ATTRIBUTE3
786 ,ATTRIBUTE4
787 ,ATTRIBUTE5
788 ,ATTRIBUTE6
789 ,ATTRIBUTE7
790 ,ATTRIBUTE8
791 ,ATTRIBUTE9
792 ,ATTRIBUTE10
796 ,ATTRIBUTE13
793 ,REFERENCE_NUM
794 ,ATTRIBUTE11
795 ,ATTRIBUTE12
797 ,ATTRIBUTE14
798 ,ATTRIBUTE15
799 ,MIN_RELEASE_AMOUNT
800 ,PRICE_TYPE_LOOKUP_CODE
801 ,null
802 ,PRICE_BREAK_LOOKUP_CODE
803 ,GOVERNMENT_CONTEXT
804 ,null
805 ,null
806 ,null
807 ,null
808 ,null
809 ,null
810 ,null
811 ,TRANSACTION_REASON_CODE
812 ,project_id
813 ,task_id
814 ,ORG_ID
815 --togeorge 10/05/2000
816 --added oke columns
817 ,oke_contract_header_id
818 ,oke_contract_version_id
819 ,order_type_lookup_code
820 ,purchase_basis
821 ,matching_basis
822 ,l_ip_category_id --Bug#5401155
823 from po_lines
824 where po_line_id = x_from_po_line_id;
825
826 --dbms_output.put_line('progress 006');
827 IF(x_copy_attachments = 'Y') THEN
828
829 x_progress:= '006';
830
831 --API to copy attachments from requisition line to po line
832 fnd_attached_documents2_pkg.
833 copy_attachments('PO_LINES',
834 x_from_po_line_id,
835 '',
836 '',
837 '',
838 '',
839 'PO_LINES',
840 x_to_po_line_id,
841 '',
842 '',
843 '',
844 '',
845 who.user_id,
846 who.login_id,
847 '',
848 '',
849 '');
850
851 END IF;
852
853
854 -- Bug#5401155
855 -- Create the default attributes and translations for the new quotation that is created
856 IF(x_new_document_type = 'QUOTATION') THEN
857 PO_ATTRIBUTE_VALUES_PVT.create_default_attributes (
858 p_doc_type => x_new_document_type,
859 p_po_line_id => x_to_po_line_id,
860 p_req_template_name => NULL,
861 p_req_template_line_num => NULL,
862 p_ip_category_id => l_ip_category_id,
863 p_inventory_item_id => l_item_id,
864 p_org_id => l_org_id,
865 p_description => l_item_description
866 );
867 END IF;
868
869 /* shipments, distributions if necessary */
870 IF(x_copy_mode IN('SHIPMENT', 'DISTRIBUTION')) THEN
871
872 x_progress:= '007';
873 --dbms_output.put_line('progress 007');
874
875 copy_shipments(x_from_po_line_id,
876 x_to_po_line_id,
877 x_copy_mode,
878 x_copy_attachments,
879 x_new_document_type,
880 x_new_tax_flag,
881 x_tax_id);
882
883 END IF;
884
885 END LOOP;
886
887 EXCEPTION
888 WHEN OTHERS THEN
889 po_message_s.sql_error('COPY LINES', x_progress, sqlcode);
890 raise;
891
892 END copy_lines;
893
894
895 /*
896 * ===========================================================================
897 *
898 * PROCEDURE NAME: copy_shipments
899 *
900 * ===========================================================================
901 */
902
903 procedure
904 copy_shipments(x_from_po_line_id IN number,
905 x_to_po_line_id IN number,
906 x_copy_mode IN varchar2,
907 x_copy_attachments IN varchar2,
908 x_new_document_type IN varchar2,
909 x_new_tax_flag IN varchar2,
910 x_tax_id IN ap_tax_codes.tax_id%type) IS
911
912 x_from_po_line_location_id number;
913 x_to_po_line_location_id number;
914 x_to_po_header_id number;
915
916 CURSOR shipments_cursor(x_get_po_line_id number) IS
917 SELECT line_location_id
918 FROM po_line_locations pl
919 WHERE pl.po_line_id = x_get_po_line_id
920 ORDER BY pl.line_location_id;
921
922 BEGIN
923
924 x_progress:= '001';
925 --dbms_output.put_line('progress cs 1');
926
927 OPEN shipments_cursor(x_from_po_line_id);
928 --dbms_output.put_line('progress cs 2 ' || x_from_po_line_id);
929
930 x_progress:= '002';
931 LOOP
932 --dbms_output.put_line('progress cs 2');
933 FETCH shipments_cursor INTO x_from_po_line_location_id;
934 EXIT WHEN shipments_cursor % notfound;
935
936 --dbms_output.put_line('progress cs 4');
937 x_progress:= '004';
938
939 /* get the new line loc id, and the original po_header_id */
940 select po_line_locations_s.nextval, pol.po_header_id
941 into x_to_po_line_location_id, x_to_po_header_id
942 from po_lines pol
943 where pol.po_line_id = x_to_po_line_id;
944
945 x_progress:= '005';
946 --dbms_output.put_line('progress cs 5' || x_to_po_header_id);
947
948 /* Bug 1484350 draising
949 Description: decode statement is changed for tax_code_id.
950 the logic is changed so that if there is no tax_code defined for new supplier
954 */
951 in the quotation tax code from RFQ will be copied.
952 otherwise if tax code is defined for the new supplier it will consider the
953 default tax_code of new supplier.
955
956 insert into po_line_locations(
957 LINE_LOCATION_ID
958 ,LAST_UPDATE_DATE
959 ,LAST_UPDATED_BY
960 ,PO_HEADER_ID
961 ,PO_LINE_ID
962 ,LAST_UPDATE_LOGIN
963 ,CREATION_DATE
964 ,CREATED_BY
965 ,QUANTITY
966 ,QUANTITY_RECEIVED
967 ,QUANTITY_ACCEPTED
968 ,QUANTITY_REJECTED
969 ,QUANTITY_BILLED
970 ,QUANTITY_CANCELLED
971 ,UNIT_MEAS_LOOKUP_CODE
972 ,PO_RELEASE_ID
973 ,SHIP_TO_LOCATION_ID
974 ,SHIP_VIA_LOOKUP_CODE
975 ,NEED_BY_DATE
976 ,PROMISED_DATE
977 ,LAST_ACCEPT_DATE
978 ,PRICE_OVERRIDE
979 ,ENCUMBERED_FLAG
980 ,ENCUMBERED_DATE
981 ,UNENCUMBERED_QUANTITY
982 ,FOB_LOOKUP_CODE
983 ,FREIGHT_TERMS_LOOKUP_CODE
984 ,TAXABLE_FLAG
985 ,TAX_CODE_ID
986 ,ESTIMATED_TAX_AMOUNT
987 ,FROM_HEADER_ID
988 ,FROM_LINE_ID
989 ,FROM_LINE_LOCATION_ID
990 ,START_DATE
991 ,END_DATE
992 ,LEAD_TIME
993 ,LEAD_TIME_UNIT
994 ,PRICE_DISCOUNT
995 ,TERMS_ID
996 ,APPROVED_FLAG
997 ,APPROVED_DATE
998 ,CLOSED_FLAG
999 ,CANCEL_FLAG
1000 ,CANCELLED_BY
1001 ,CANCEL_DATE
1002 ,CANCEL_REASON
1003 ,FIRM_STATUS_LOOKUP_CODE
1004 ,FIRM_DATE
1005 ,ATTRIBUTE_CATEGORY
1006 ,ATTRIBUTE1
1007 ,ATTRIBUTE2
1008 ,ATTRIBUTE3
1009 ,ATTRIBUTE4
1010 ,ATTRIBUTE5
1011 ,ATTRIBUTE6
1012 ,ATTRIBUTE7
1013 ,ATTRIBUTE8
1014 ,ATTRIBUTE9
1015 ,ATTRIBUTE10
1016 ,UNIT_OF_MEASURE_CLASS
1017 ,ENCUMBER_NOW
1018 ,ATTRIBUTE11
1019 ,ATTRIBUTE12
1020 ,ATTRIBUTE13
1021 ,ATTRIBUTE14
1022 ,ATTRIBUTE15
1023 ,INSPECTION_REQUIRED_FLAG
1024 ,RECEIPT_REQUIRED_FLAG
1025 ,QTY_RCV_TOLERANCE
1026 ,QTY_RCV_EXCEPTION_CODE
1027 ,ENFORCE_SHIP_TO_LOCATION_CODE
1028 ,ALLOW_SUBSTITUTE_RECEIPTS_FLAG
1029 ,DAYS_EARLY_RECEIPT_ALLOWED
1030 ,DAYS_LATE_RECEIPT_ALLOWED
1031 ,RECEIPT_DAYS_EXCEPTION_CODE
1032 ,INVOICE_CLOSE_TOLERANCE
1033 ,RECEIVE_CLOSE_TOLERANCE
1034 ,SHIP_TO_ORGANIZATION_ID
1035 ,SHIPMENT_NUM
1036 ,SOURCE_SHIPMENT_ID
1037 ,SHIPMENT_TYPE
1038 ,CLOSED_CODE
1039 ,REQUEST_ID
1040 ,PROGRAM_APPLICATION_ID
1041 ,PROGRAM_ID
1042 ,PROGRAM_UPDATE_DATE
1043 ,GOVERNMENT_CONTEXT
1044 ,RECEIVING_ROUTING_ID
1045 ,ACCRUE_ON_RECEIPT_FLAG
1046 ,CLOSED_REASON
1047 ,CLOSED_DATE
1048 ,CLOSED_BY
1049 ,ORG_ID,
1050 --togeorge 10/05/2000
1051 --added note to receiver
1052 note_to_receiver
1053 ,outsourced_assembly /* Bug 6675806. Missing in R12 SHIKYU Transition.*/
1054 )
1055 select
1056 x_to_po_line_location_id
1057 ,LAST_UPDATE_DATE
1058 ,LAST_UPDATED_BY
1059 ,x_to_po_header_id
1060 ,x_to_po_line_id
1061 ,who.login_id
1062 ,sysdate
1063 ,who.user_id
1064 ,QUANTITY
1065 ,0
1066 ,0
1070 ,UNIT_MEAS_LOOKUP_CODE
1067 ,0
1068 ,0
1069 ,0
1071 ,null
1072 ,SHIP_TO_LOCATION_ID
1073 ,SHIP_VIA_LOOKUP_CODE
1074 ,NEED_BY_DATE
1075 ,PROMISED_DATE
1076 ,LAST_ACCEPT_DATE
1077 ,PRICE_OVERRIDE
1078 ,'N'
1079 ,null
1080 ,0
1081 ,FOB_LOOKUP_CODE
1082 ,FREIGHT_TERMS_LOOKUP_CODE
1083 ,TAXABLE_FLAG
1084 -- ,decode(x_new_tax_flag,'Y',x_tax_id,TAX_CODE_ID) /* Bug# 1484350 */
1085 ,decode(x_tax_id,null,TAX_CODE_ID,x_tax_id)
1086 ,ESTIMATED_TAX_AMOUNT
1087 ,PO_HEADER_ID
1088 ,PO_LINE_ID
1089 ,LINE_LOCATION_ID
1090 ,START_DATE
1091 ,END_DATE
1092 ,LEAD_TIME
1093 ,LEAD_TIME_UNIT
1094 ,PRICE_DISCOUNT
1095 ,TERMS_ID
1096 ,'N'
1097 ,null
1098 ,null
1099 ,'N'
1100 ,null
1101 ,null
1102 ,null
1103 ,FIRM_STATUS_LOOKUP_CODE
1104 ,FIRM_DATE
1105 ,ATTRIBUTE_CATEGORY
1106 ,ATTRIBUTE1
1107 ,ATTRIBUTE2
1108 ,ATTRIBUTE3
1109 ,ATTRIBUTE4
1110 ,ATTRIBUTE5
1111 ,ATTRIBUTE6
1112 ,ATTRIBUTE7
1113 ,ATTRIBUTE8
1114 ,ATTRIBUTE9
1115 ,ATTRIBUTE10
1116 ,UNIT_OF_MEASURE_CLASS
1117 ,ENCUMBER_NOW
1118 ,ATTRIBUTE11
1119 ,ATTRIBUTE12
1120 ,ATTRIBUTE13
1121 ,ATTRIBUTE14
1122 ,ATTRIBUTE15
1123 ,INSPECTION_REQUIRED_FLAG
1124 ,RECEIPT_REQUIRED_FLAG
1125 ,QTY_RCV_TOLERANCE
1126 ,QTY_RCV_EXCEPTION_CODE
1127 ,ENFORCE_SHIP_TO_LOCATION_CODE
1128 ,ALLOW_SUBSTITUTE_RECEIPTS_FLAG
1129 ,DAYS_EARLY_RECEIPT_ALLOWED
1130 ,DAYS_LATE_RECEIPT_ALLOWED
1131 ,RECEIPT_DAYS_EXCEPTION_CODE
1132 ,INVOICE_CLOSE_TOLERANCE
1133 ,RECEIVE_CLOSE_TOLERANCE
1134 ,SHIP_TO_ORGANIZATION_ID
1135 ,SHIPMENT_NUM
1136 ,SOURCE_SHIPMENT_ID
1137 ,x_new_document_type
1138 ,'OPEN'
1139 ,null
1140 ,null
1141 ,null
1142 ,null
1143 ,GOVERNMENT_CONTEXT
1144 ,RECEIVING_ROUTING_ID
1145 ,ACCRUE_ON_RECEIPT_FLAG
1146 ,null
1147 ,null
1148 ,null
1149 ,ORG_ID,
1150 --togeorge 10/05/2000
1151 --added note to receiver
1152 note_to_receiver
1153 ,outsourced_assembly /* Bug 6675806. Missing in R12 SHIKYU Transition.*/
1154
1155 from po_line_locations
1156 where line_location_id = x_from_po_line_location_id;
1157
1158 --dbms_output.put_line('progress cs 006');
1159 IF(x_copy_attachments = 'Y') THEN
1160
1161 x_progress:= '006';
1162
1163 --API to copy attachments from requisition line to po line
1164 fnd_attached_documents2_pkg.
1165 copy_attachments('PO_SHIPMENTS',
1166 x_from_po_line_location_id,
1167 '',
1168 '',
1169 '',
1170 '',
1171 'PO_SHIPMENTS',
1172 x_to_po_line_location_id,
1173 '',
1174 '',
1175 '',
1176 '',
1177 who.user_id,
1178 who.login_id,
1179 '',
1180 '',
1181 '');
1182
1183 END IF;
1184
1185 --Distribution copy not yet enabled
1186 -- /* copy distributions if necessary */
1187 -- IF(x_copy_mode IN('SHIPMENT', 'DISTRIBUTION')) THEN
1188 --
1189 -- x_progress:= '007';
1190 --copy_distributions(x_from_po_line_location_id,
1191 --x_to_po_line_location_id)
1192 --
1193 -- END IF;
1194
1195 END LOOP;
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 po_message_s.sql_error('COPY SHIPMENTS', x_progress, sqlcode);
1200 raise;
1201
1202 END copy_shipments;
1203
1204
1205
1206 /*
1207 * ===========================================================================
1208 * = Name: copy documents DESC: ARGS: ALGR:
1209 *
1210 * ==========================================================================
1211 */
1212 PROCEDURE copy_document(x_po_header_id IN number,
1213 x_new_document_type IN varchar2,
1214 x_new_document_subtype IN varchar2,
1215 x_new_supplier_id IN number,
1216 x_new_supplier_site_id IN number,
1217 x_new_supplier_contact_id IN number,
1218 x_copy_mode IN varchar2,
1219 x_copy_attachments IN varchar2,
1220 x_new_document_num in varchar2,
1221 x_new_po_header_id OUT NOCOPY number,
1222 x_actual_document_num IN OUT NOCOPY varchar2) IS
1223
1224 x_to_po_header_id number;
1225 x_to_actual_document_num varchar2(25);
1226
1227 BEGIN
1228
1229
1230 x_progress:= '001';
1231 /* copy the header, with the new supplier site) */
1232
1233 who.user_id:= nvl(fnd_global.user_id, 0);
1234 who.login_id:= nvl(fnd_global.login_id, 0);
1235 who.resp_id:= nvl(fnd_global.resp_id, 0);
1236
1237
1238 copy_header(x_po_header_id,
1239 x_new_document_type,
1240 x_new_document_subtype,
1241 x_new_supplier_id,
1242 x_new_supplier_site_id,
1243 x_new_supplier_contact_id,
1244 x_copy_mode,
1245 x_copy_attachments,
1246 x_new_document_num,
1247 x_to_po_header_id,
1248 x_to_actual_document_num);
1249
1250 /* set the return values for copy_documents */
1251 x_new_po_header_id:= x_to_po_header_id;
1252 x_actual_document_num:= x_to_actual_document_num;
1253
1254 x_progress:= '002';
1255
1256 /* copy lines, shipments, distributions if necessary */
1257 IF(x_copy_mode IN('LINE', 'SHIPMENT', 'DISTRIBUTION')) THEN
1258 x_progress:= '003';
1259 copy_lines(x_po_header_id,
1263 x_new_document_type,
1260 x_to_po_header_id,
1261 x_copy_mode,
1262 x_copy_attachments,
1264 x_new_supplier_id,
1265 x_new_supplier_site_id);
1266 END IF;
1267
1268 /* commit; */
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 po_message_s.sql_error('COPY_DOCUMENTS', x_progress, sqlcode);
1273 raise;
1274 END copy_document;
1275
1276 END po_copy_documents_s;