1 PACKAGE BODY po_copydoc_s3 AS
2 /* $Header: POXCPO3B.pls 120.8 2005/12/08 16:51:34 dreddy noship $*/
3
4 -- <INVCONV R12>
5 g_chktype_TRACKING_QTY_IND CONSTANT
6 MTL_SYSTEM_ITEMS_B.TRACKING_QUANTITY_IND%TYPE := 'PS';
7
8 -- Private function prototypes
9
10 PROCEDURE validate_line_type_id(
11 x_line_type_id IN OUT NOCOPY po_lines.line_type_id%TYPE,
12 x_wip_install_status IN VARCHAR2,
13 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
14 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
15 x_line_num IN po_online_report_text.line_num%TYPE,
16 x_return_code OUT NOCOPY NUMBER
17 );
18
19 PROCEDURE validate_trx_reason_code(
20 x_transaction_reason_code IN OUT NOCOPY po_lines.transaction_reason_code%TYPE,
21 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
22 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
23 x_line_num IN po_online_report_text.line_num%TYPE,
24 x_return_code OUT NOCOPY NUMBER
25 );
26 -- End of Private function prototypes
27
28 PROCEDURE validate_line_type_id(
29 x_line_type_id IN OUT NOCOPY po_lines.line_type_id%TYPE,
30 x_wip_install_status IN VARCHAR2,
31 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
32 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
33 x_line_num IN po_online_report_text.line_num%TYPE,
34 x_return_code OUT NOCOPY NUMBER
35 ) IS
36
37 x_progress VARCHAR2(4);
38 x_valid_flag VARCHAR2(2);
39
40 BEGIN
41
42 x_progress := '001';
43 SELECT distinct 'Y'
44 INTO x_valid_flag
45 FROM PO_LINE_TYPES
46 WHERE line_type_id = x_line_type_id
47 AND SYSDATE < nvl(inactive_date, SYSDATE+1)
48 AND ((nvl(outside_operation_flag,'N') = 'Y' AND x_wip_install_status = 'I')
49 OR (nvl(outside_operation_flag, 'N') <> 'Y'));
50
51 x_return_code := 0;
52
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 x_line_type_id := NULL;
56 po_copydoc_s1.online_report(x_online_report_id,
57 x_sequence,
58 'Invalid Line type',
59 x_line_num, 0, 0);
60 x_return_code := -1;
61 WHEN OTHERS THEN
62 x_line_type_id := NULL;
63 po_copydoc_s1.copydoc_sql_error('validate_line_type_id', x_progress, sqlcode,
64 x_online_report_id,
65 x_sequence,
66 x_line_num, 0, 0);
67 x_return_code := -1;
68 END validate_line_type_id;
69
70
71
72 PROCEDURE validate_trx_reason_code(
73 x_transaction_reason_code IN OUT NOCOPY po_lines.transaction_reason_code%TYPE,
74 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
75 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
76 x_line_num IN po_online_report_text.line_num%TYPE,
77 x_return_code OUT NOCOPY NUMBER
78 ) IS
79
80 x_progress VARCHAR2(4);
81 x_valid_flag VARCHAR2(2);
82
83 BEGIN
84
85 IF (x_transaction_reason_code IS NULL) THEN
86 x_return_code := 0;
87 RETURN;
88 END IF;
89
90
91 SELECT distinct 'Y'
92 INTO x_valid_flag
93 FROM PO_LOOKUP_CODES
94 WHERE lookup_type = 'TRANSACTION REASON'
95 AND lookup_code = x_transaction_reason_code
96 AND SYSDATE < nvl(inactive_date, SYSDATE+1);
97
98 x_return_code := 0;
99
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 x_transaction_reason_code := NULL;
103 po_copydoc_s1.online_report(x_online_report_id,
104 x_sequence,
105 'Invalid Transaction reason code',
106 x_line_num, 0, 0);
107 x_return_code := 0;
108 WHEN OTHERS THEN
109 x_transaction_reason_code := NULL;
110 po_copydoc_s1.copydoc_sql_error('validate_trx_reason_code', x_progress, sqlcode,
111 x_online_report_id,
112 x_sequence,
113 x_line_num, 0, 0);
114 x_return_code := -1;
115 END validate_trx_reason_code;
116
117 /*************************************************************
118 ** Initialize date info
119 ** Nullify some attributes which will be inserted from other places
120 ** Validate for correct line_type_id (may not need it ?)
121 ** Validate individual item on line
122 ** Validate transaction reason code
123 ** Get next po_line_id
124 *************************************************************/
125 PROCEDURE validate_line(
126 x_action_code IN VARCHAR2,
127 x_to_doc_subtype IN po_headers.type_lookup_code%TYPE,
128 x_po_line_record IN OUT NOCOPY po_lines%ROWTYPE,
129 x_orig_po_line_id IN po_lines.po_line_id%TYPE,
130 x_wip_install_status IN VARCHAR2,
131 x_sob_id IN financials_system_parameters.set_of_books_id%TYPE,
132 x_inv_org_id IN financials_system_parameters.inventory_organization_id%TYPE,
133 x_po_header_id IN po_lines.po_header_id%TYPE,
134 x_online_report_id IN po_online_report_text.online_report_id%TYPE,
135 x_sequence IN OUT NOCOPY po_online_report_text.sequence%TYPE,
136 x_copy_price IN BOOLEAN,
137 x_return_code OUT NOCOPY NUMBER,
138 p_is_complex_work_po IN BOOLEAN -- <Complex Work R12>
139 ) IS
140
141 COPYDOC_LINE_FAILURE EXCEPTION;
142 x_progress VARCHAR2(4) := NULL;
143 x_internal_return_code NUMBER := NULL;
144 x_quotation_class_code VARCHAR2(10) := NULL;
145 x_orig_po_header_id po_lines.po_header_id%TYPE := NULL;
146 x_qty po_lines.quantity%type := NULL;
147 x_blanket_price po_lines.unit_price%type := NULL;
148 x_order_type_lookup_code po_line_types.order_type_lookup_code%type;
149 -- start of 1548597
150 x_secondary_qty po_lines.secondary_quantity%type := NULL;
151 x_item_number VARCHAR2(240);
152 x_process_org VARCHAR2(1);
153 x_dummy VARCHAR2(240);
154 x_product VARCHAR2(3) := 'GMI';
155 x_opm_installed VARCHAR2(1);
156 x_retvar BOOLEAN;
157 ic_item_mst_rec IC_ITEM_MST%ROWTYPE;
158 ic_item_cpg_rec IC_ITEM_CPG%ROWTYPE;
159 x_order_opm_um ic_item_mst.item_um%type := NULL;
160 -- end of 1548597
161
162 x_quote_type po_headers.type_lookup_code%type;
163 x_quote_sub_type po_headers.quote_type_lookup_code%type;
164 --<INVCONV R12 START>
165 x_secondary_unit_fsp MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
166 x_secondary_uom_fsp MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
167 x_secondary_quantity_fsp po_lines.quantity%type;
168 --<INVCONV R12 END>
169
170 BEGIN
171
172 po_copydoc_s1.copydoc_debug('validate_line()');
173
174 /****** Unchanged Attributes
175 note_to_vendor
176 qty_rcv_tolerance
177 hazard_class_id
178 note_to_vendor
179 over_tolerance_error_flag
180 unordered_flag
181 vendor_product_num
182 taxable_flag
183 tax_code_id
184 type_1099 (what is this?)
185 attribute{1-15}
186 qc_grade (what is this?)
187 base_{uom, qty}
188 secondary_{uom, qty}
189 line_type_id
190 line_num
191 item_{id, revision, description}
192 category_id
193 unit_meas_lookup_code
194 unit_price
195 un_number_id
196 global_attribute{category, _1-20}
197 line_reference_num
198 project_id (?)
199 task_id (?)
200 ******/
201
202 /* store original info */
203 -- bug877084: don't need to override in po_lines
204
205 SELECT po_header_id
206 INTO x_orig_po_header_id
207 FROM po_lines
208 WHERE po_line_id = x_orig_po_line_id;
209
210 /* 1780903 : When a bid quotation is copied to a standard or planned PO
211 we need to copy the quote reference to the PO . For this we copy the
212 from header and from line from the quote to the PO */
213
214 select quote_type_lookup_code,
215 type_lookup_code
216 into x_quote_sub_type,
217 x_quote_type
218 from po_headers
219 where po_header_id = x_orig_po_header_id;
220
221 if x_quote_type = 'QUOTATION' and x_quote_sub_type = 'BID'
222 and x_to_doc_subtype in ('STANDARD','PLANNED') then
223 x_po_line_record.from_header_id := x_orig_po_header_id;
224 x_po_line_record.from_line_id := x_orig_po_line_id;
225 end if;
226
227
228 IF (x_action_code in ('QUOTATION','RFQ')) THEN
229 /* It's the same for Blanket, Planned, or Standard PO */
230 SELECT quotation_class_code
231 INTO x_quotation_class_code
232 FROM po_headers
233 WHERE po_header_id = x_orig_po_header_id;
234
235 IF (x_quotation_class_code = 'CATALOG') THEN
236
237 x_po_line_record.min_order_quantity := NULL;
238 x_po_line_record.max_order_quantity := NULL;
239 x_po_line_record.min_release_amount := NULL;
240 x_po_line_record.price_type_lookup_code := NULL;
241 x_po_line_record.market_price := NULL;
242 x_po_line_record.firm_status_lookup_code := 'N';
243 x_po_line_record.firm_date := NULL;
244 x_po_line_record.contract_num := NULL;
245 x_po_line_record.capital_expense_flag := 'N';
246
247 -- bug3610606
248 -- We no longer set negotiated_by_preparer_flag to 'N' when
249 -- copying from catalog quotation
250 -- x_po_line_record.negotiated_by_preparer_flag := 'N';
251
252 x_po_line_record.quantity_committed := NULL;
253 x_po_line_record.committed_amount := NULL;
254 x_po_line_record.allow_price_override_flag := 'N';
255 x_po_line_record.quantity := NULL;
256 END IF;
257 END IF;
258
259 /* Functionality for PA->RFQ Copy : dreddy
260 line related fields are processed */
261 IF (x_action_code = 'RFQ') THEN
262 -- based on the value of copy_price ,the price from
263 -- the blanket is either copied or left blank.
264 begin
265 SELECT unit_price
266 INTO x_blanket_price
267 FROM po_lines
268 WHERE po_header_id = x_orig_po_header_id
269 AND po_line_id = x_orig_po_line_id;
270 exception
271 when others then
272 x_blanket_price := null;
273 end;
274
275 --Bug# 1567872
276 --togeorge 01/19/2001
277 --Select the order type and if it is 'AMOUNT' copy the price even
278 --if x_copy_price is 'FALSE'. Amount based line should always have a price 1.
279 x_progress := '001';
280 begin
281 SELECT order_type_lookup_code
282 INTO x_order_type_lookup_code
283 FROM po_line_types
284 WHERE line_type_id = x_po_line_record.line_type_id;
285 exception
286 when others then
287 po_copydoc_s1.copydoc_sql_error('validate_line', x_progress, sqlcode,
288 x_online_report_id,
289 x_sequence,
290 x_po_line_record.line_num, 0, 0);
291 x_return_code := -1;
292 end;
293 --
294 if(x_copy_price) then
295 x_po_line_record.unit_price := x_blanket_price;
296 else
297 --Bug# 1567872
298 --togeorge 01/19/2001
299 --Select the order type and if it is 'AMOUNT' copy the price even
300 --if x_copy_price is 'FALSE'. Amount based line should always have a price 1.
301 if x_order_type_lookup_code = 'AMOUNT' then
302 x_po_line_record.unit_price := x_blanket_price; --which would be 1.
303 else
304 x_po_line_record.unit_price := NULL;
305 end if;
306 --x_po_line_record.unit_price := NULL;
307 --
308 end if;
309 END IF;
310
311 -- Bug 2694883. Copy the po line expiration date over from the original.
312
313 -- We now use the expiration date as the assignment end date for std PO's for
314 -- service lines
315 -- so if the to document is std PO we null out the value otherwise copy over.
316
317 IF (x_to_doc_subtype = 'STANDARD') THEN -- SERVICES FPJ
318 x_po_line_record.expiration_date := NULL;
319 END IF;
320
321 -- SERVICES FPJ Start
322 -- Also for service lines we need to null out other contractor specific fields
323 x_po_line_record.contractor_first_name := NULL;
324 x_po_line_record.contractor_last_name := NULL;
325
326 -- As start date is mandatory we need to default it with some value.
327 -- We will be defaulting sysdate.
328 IF (x_to_doc_subtype = 'STANDARD') AND
329 x_po_line_record.start_date is not null THEN
330
331 x_po_line_record.start_date := trunc(sysdate); -- Bug 3266584
332
333 END IF;
334
335 -- SERVICES FPJ End
336
337 x_po_line_record.last_updated_by := fnd_global.user_id;
338 x_po_line_record.last_update_date := SYSDATE;
339 x_po_line_record.last_update_login := fnd_global.login_id;
340 x_po_line_record.created_by := fnd_global.user_id;
341 x_po_line_record.creation_date := SYSDATE;
342
343 -- Standard WHO columns, not inserted
344 x_po_line_record.program_application_id := NULL;
345 x_po_line_record.program_id := NULL;
346 x_po_line_record.program_update_date := NULL;
347 x_po_line_record.request_id := NULL;
348
349 validate_trx_reason_code(x_po_line_record.transaction_reason_code,
350 x_online_report_id,
351 x_sequence,
352 x_po_line_record.line_num,
353 x_internal_return_code);
354 IF (x_internal_return_code < 0) THEN
355 RAISE COPYDOC_LINE_FAILURE;
356 END IF;
357
358 x_po_line_record.closed_by := NULL;
359 x_po_line_record.closed_code := NULL;
360 x_po_line_record.closed_date := NULL;
361 x_po_line_record.closed_flag := 'N';
362 x_po_line_record.closed_reason := NULL;
363
364 x_po_line_record.cancelled_by := NULL;
365 x_po_line_record.cancel_date := NULL;
366 x_po_line_record.cancel_flag := 'N';
367 x_po_line_record.cancel_reason := NULL;
368
369 -- PO DBI FPJ ** Start
370 -- For Blanket, Standard and Planned the negotiated_by_preparer_flag should be
371 -- 'Y' at the time of copying quotation document.
372
373 -- Bug 3602147: x_quote_sub_type should be either STANDARD or CATALOG
374 -- bug3610606: x_quote_sub_type can be 'BID' as well.
375
376 IF x_to_doc_subtype IN ('BLANKET', 'STANDARD', 'PLANNED')
377 AND x_quote_sub_type IN ('STANDARD', 'CATALOG', 'BID') THEN
378 --AND x_quote_sub_type NOT IN ('BLANKET', 'STANDARD', 'PLANNED') THEN
379
380 x_po_line_record.negotiated_by_preparer_flag := 'Y';
381
382 END IF;
383 -- PO DBI FPJ ** End
384
385
386 -- Should be obselete
387 x_po_line_record.user_hold_flag := NULL;
388
389 /* bug 969442: when a po is cancelled the quantity on a line is modified.
390 so when copying from it the original quantity is not copied onto the po_line.
391 To fix this, the sum of the quantities from the shipment lines is copied
392 to the new po instead.and also the note to vendor field is nulled because
393 it is specific to a PO and also for a cancelled po case it contains the
394 reason for cancel. */
395 /* bug 1056086 : but when adding the shipment lines,we should not add the
396 release shipments . also if its a blanket PO copy null into the qty field
397 as quantity does not make sense for a blanket */
398
399 IF (x_to_doc_subtype = 'BLANKET') THEN
400
401 x_qty := NULL;
402 x_secondary_qty := NULL; --Bug 1548597
403
404 ELSIF (x_po_line_record.order_type_lookup_code in ('QUANTITY','AMOUNT'))
405 THEN
406
407 --<Complex Work R12 START>
408 IF (p_is_complex_work_po) THEN
409 --For the Quantity Milestone case, do not sum the line location
410 --quantities.
411 x_qty := x_po_line_record.quantity;
412 x_secondary_qty := NULL;
413 ELSE
414 -- Bug# 3842550 START
415 -- If line has no shipments resulting in null values for the sum of
416 -- quantities and secondary quantities of shipments, set them back to
417 -- their original values.
418 -- Note: The following issue has been resolved by this bug fix.
419 /* If Common Receiving is not installed the above sum of
420 secondary_Quantity will return a null. so we need to override it */
421
422 select nvl(sum(poll.quantity), x_po_line_record.quantity)
423 into x_qty
424 from po_line_locations poll
425 where poll.po_line_id = x_po_line_record.po_line_id
426 and poll.po_release_id is null
427 and poll.payment_type <> 'PREPAYMENT'; -- <Complex Work R12>
428
429 END IF; --If Complex Work PO
430 --<Complex Work R12 END>
431
432 END IF; --If Blanket or if Qty-based PO line
433
434 --<INVCONV R12 START>
435 IF x_po_line_record.quantity <> x_qty THEN
436 X_po_line_record.secondary_quantity := NULL ;
437 X_po_line_record.secondary_unit_of_measure := NULL ;
438 END IF;
439 x_po_line_record.quantity := x_qty;
440
441 -- calculate secondary quantity and UOM based on FSP org
442 -- <Complex Work R12>: Null out secondary qty/uom for complex work POs
443 IF ((NOT p_is_complex_work_po) AND
444 (x_to_doc_subtype in ('STANDARD','PLANNED','BLANKET'))) THEN
445 IF x_po_line_record.item_id is not null THEN
446 PO_UOM_S.get_secondary_uom( x_po_line_record.item_id,
447 x_inv_org_id,
448 X_secondary_uom_fsp,
449 X_secondary_unit_fsp);
450
451 IF X_secondary_unit_fsp IS NOT NULL AND x_to_doc_subtype <> 'BLANKET' THEN
452 PO_UOM_S.uom_convert (x_po_line_record.quantity,x_po_line_record.unit_meas_lookup_code,
453 x_po_line_record.item_id, x_secondary_unit_fsp, x_secondary_quantity_fsp) ;
454 END IF;
455 X_po_line_record.secondary_quantity := x_secondary_quantity_fsp ;
456 X_po_line_record.secondary_unit_of_measure := x_secondary_unit_fsp ;
457 ELSE
458 X_po_line_record.secondary_quantity := NULL ;
459 X_po_line_record.secondary_unit_of_measure := NULL ;
460 END IF;
461 ELSE
462 X_po_line_record.secondary_quantity := NULL ;
463 X_po_line_record.secondary_unit_of_measure := NULL ;
464 END IF;
465
466 --<INVCONV R12 END>
467
468 /* Bug# 1523449 draising
469 While using 'Copy Document' functionality from
470 Quotation to PO below line was nullifying the note_to_vendor field
471 in copied PO form. It shouldn't happen while copying from Quotation
472 to PO.Addded if condition that when x_action_code is 'QUOTATION' then
473 it will not nullify the note_to_vendor field */
474
475 IF (x_action_code <> 'QUOTATION') THEN
476 x_po_line_record.note_to_vendor := NULL;
477 END IF;
478
479 x_po_line_record.po_header_id := x_po_header_id;
480
481 x_progress := '001';
482 BEGIN
483 SELECT po_lines_s.nextval
484 INTO x_po_line_record.po_line_id
485 FROM SYS.DUAL;
486 EXCEPTION
487 WHEN OTHERS THEN
488 x_po_line_record.po_line_id := NULL;
489 po_copydoc_s1.copydoc_sql_error('validate_line', x_progress, sqlcode,
490 x_online_report_id,
491 x_sequence,
492 x_po_line_record.line_num, 0, 0);
493 RAISE COPYDOC_LINE_FAILURE;
494 END;
495
496 -- Global Agreements (FP-I): Do not copy over Cumulative Price field.
497 --
498 IF ( PO_GA_PVT.is_global_agreement( x_po_line_record.po_header_id ) ) THEN
499 x_po_line_record.price_break_lookup_code := NULL;
500 END IF;
501
502 x_return_code := 0;
503 po_copydoc_s1.copydoc_debug('End: validate_line()');
504
505 EXCEPTION
506 WHEN COPYDOC_LINE_FAILURE THEN
507 x_return_code := -1;
508 WHEN OTHERS THEN
509 po_copydoc_s1.copydoc_sql_error('validate_line', x_progress, sqlcode,
510 x_online_report_id,
511 x_sequence,
512 x_po_line_record.line_num, 0, 0);
513 x_return_code := -1;
514 END validate_line;
515
516
517 END po_copydoc_s3;
518
[Home] [Help]