1 PACKAGE BODY PO_REQ_LINES_SV1 as
2 /* $Header: POXRQL2B.pls 120.3 2006/08/02 02:09:09 pxiao noship $ */
3 /*========================== po_req_lines_sv1 ============================*/
4
5 /*===========================================================================
6
7 PROCEDURE NAME: get_vendor_sourcing_info
8
9 ===========================================================================*/
10 PROCEDURE get_vendor_sourcing_info( x_vendor_id IN NUMBER,
11 x_vendor_site_id IN NUMBER,
12 x_vendor_contact_id IN NUMBER,
13 x_po_header_id IN NUMBER,
14 x_document_type_code IN VARCHAR2,
15 x_buyer_id IN NUMBER,
16 x_vendor_name IN OUT NOCOPY VARCHAR2,
17 x_vendor_location IN OUT NOCOPY VARCHAR2,
18 x_vendor_contact IN OUT NOCOPY VARCHAR2,
19 x_vendor_phone IN OUT NOCOPY VARCHAR2,
20 x_po_num IN OUT NOCOPY VARCHAR2,
21 x_doc_type_disp IN OUT NOCOPY VARCHAR2,
22 x_buyer IN OUT NOCOPY VARCHAR2) IS
23
24 x_progress VARCHAR2(3) := NULL;
25
26 BEGIN
27
28 x_progress := '010';
29
30 po_vendors_sv.get_vendor_details (x_vendor_id,
31 x_vendor_site_id,
32 x_vendor_contact_id,
33 x_vendor_name,
34 x_vendor_location,
35 x_vendor_contact,
36 x_vendor_phone);
37
38 x_progress :='020';
39
40 po_headers_sv3.get_doc_num (x_po_num,
41 x_po_header_id);
42
43 x_progress := '030';
44
45 po_core_s.get_displayed_value ('SOURCE DOCUMENT TYPE',
46 x_document_type_code,
47 x_doc_type_disp);
48
49 x_progress := '040';
50
51 IF (x_buyer_id is not null) THEN
52
53 /* Bug - 1921406 - Changed the sql to use po_buyers_val_v
54 instead of po_buyers_all_v, because po_buyers_all_v has no
55 effectivity dates and hence returns multiple rows. */
56
57 /* Bug 2779096 : added exception handler to the following
58 select so that if the buyer id is not valid and does not
59 exist in po_buyers_val_v we we assign null */
60
61 begin
62 SELECT full_name
63 INTO x_buyer
64 FROM po_buyers_val_v
65 WHERE employee_id = x_buyer_id;
66 exception
67 when others then
68 x_buyer := null;
69 end;
70
71
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 po_message_s.sql_error('get_vendor_sourcing_info', x_progress, sqlcode);
77 raise;
78
79 END get_vendor_sourcing_info;
80
81
82
83 /*===========================================================================
84
85 PROCEDURE NAME: val_src_details()
86
87 ===========================================================================*/
88 PROCEDURE val_src_details (x_src_org_id IN OUT NOCOPY NUMBER,
89 x_src_org IN OUT NOCOPY VARCHAR2,
90 x_src_org_code IN OUT NOCOPY VARCHAR2,
91 x_item_id IN NUMBER,
92 x_item_rev IN VARCHAR2,
93 x_inv_org_id IN NUMBER,
94 x_outside_op_line_type IN VARCHAR2,
95 x_mrp_planned_item IN VARCHAR2,
96 x_src_sub IN OUT NOCOPY VARCHAR2,
97 x_src_type IN OUT NOCOPY VARCHAR2,
98 x_dest_type IN VARCHAR2,
99 x_dest_org_id IN VARCHAR2,
100 x_dest_sub IN VARCHAR2,
101 x_deliver_to_loc_id IN NUMBER,
102 x_val_code IN VARCHAR2,
103 x_sob_id IN OUT NOCOPY NUMBER) IS
104
105 x_progress VARCHAR2(3) := NULL;
106
107 x_stock_enabled_flag mtl_system_items.stock_enabled_flag%type;
108 x_internal_orderable mtl_system_items.internal_order_enabled_flag%type;
109 x_purchasing_enabled_flag mtl_system_items.purchasing_enabled_flag%type;
110 x_outside_operation_flag mtl_system_items.outside_operation_flag%type;
111 x_inventory_asset_flag mtl_system_items.inventory_asset_flag%type;
112
113 X_inventory_org_id NUMBER;
114 X_planned_item_flag VARCHAR2(1);
115 X_outside_op_uom_type mtl_system_items.outside_operation_uom_type%type;
116 X_invoice_close_tolerance mtl_system_items.invoice_close_tolerance%type;
117 X_receive_close_tolerance mtl_system_items.receive_close_tolerance%type;
118 X_receipt_required_flag VARCHAR2(1);
119
120 x_customer_id po_location_associations.customer_id%type;
121 x_address_id po_location_associations.address_id%type;
122 x_site_use_id po_location_associations.site_use_id%type;
123
124 x_error_type VARCHAR2(50);
125
126 --<INVCONV R12 START>
127 x_secondary_default_ind mtl_system_items.secondary_default_ind%TYPE:= NULL;
128 x_grade_control_flag mtl_system_items.grade_control_flag%TYPE:= NULL;
129 x_secondary_unit_of_measure mtl_units_of_measure.unit_of_measure%TYPE:= NULL;
130 x_secondary_unit_of_measure_tl mtl_units_of_measure.unit_of_measure_tl%TYPE:= NULL;
131 --<INVCONV R12 END>
132
133
134 BEGIN
135
136 x_progress := '010';
137
138
139 /* Obtain the source org and source org code
140 ** since this saves us a network round trip from
141 ** client to fetch the code and org.
142 */
143
144 po_orgs_sv.get_org_info (x_src_org_id,
145 x_sob_id,
146 x_src_org_code,
147 x_src_org);
148
149 /*
150 ** Stop processing if the source type
151 ** is null.
152 */
153 /* Ben: 2/6/97 This procedure was checking the item attributes in the
154 DESTINATION ORG, when it should have been in the SOURCE ORG.
155 Changing it: If no source org has been entered by the user then
156 do nothing.
157 */
158
159 IF (x_src_type is null) OR (x_src_org_id is null) THEN
160 x_src_org_id := null;
161 x_src_org := null;
162 x_src_org_code := null;
163 x_src_sub := null;
164
165 return;
166
167 END IF;
168
169
170 /* Determine which set of fields are to
171 ** to be validated, call the corresponding
172 ** validation functions.
173 */
174
175 IF (x_val_code = 'ALL') THEN
176
177 x_progress := '020';
178
179 /* Ben: 2/7/97. We should be getting the attributes for the item in the
180 source org.
181
182 IF (x_dest_org_id is null) THEN
183 x_inventory_org_id := x_inv_org_id;
184
185 ELSE
186 x_inventory_org_id := x_dest_org_id;
187
188 END IF;
189 */
190
191 x_inventory_org_id := x_src_org_id ;
192
193 x_progress := '030';
194
195 --<INVCONV R12 START> since package signature has changed modified this to pass 3 new parameters
196
197 po_items_sv2.get_item_details (X_item_id,
198 X_inventory_org_id,
199 X_planned_item_flag,
200 X_outside_operation_flag,
201 X_outside_op_uom_type,
202 X_invoice_close_tolerance,
203 X_receive_close_tolerance,
204 X_receipt_required_flag,
205 X_stock_enabled_flag,
206 X_internal_orderable,
207 X_purchasing_enabled_flag,
208 X_inventory_asset_flag,
209 --<INVCONV R12 START>
210 X_secondary_default_ind,
211 X_grade_control_flag,
212 X_secondary_unit_of_measure ) ;
213 --<INVCONV R12 END>
214
215
216 /* Obtain the customer information
217 ** for the deliver to location.
218 */
219
220 x_progress := '040';
221
222 po_customers_sv.get_cust_details(x_deliver_to_loc_id,
223 x_customer_id,
224 x_address_id,
225 x_site_use_id,
226 x_src_org_id); -- Bug 5028505
227
228 x_progress := '050';
229
230 IF (po_req_lines_sv.val_src_type (x_src_type,
231 x_item_id,
232 x_internal_orderable,
233 x_stock_enabled_flag,
234 x_purchasing_enabled_flag,
235 x_customer_id,
236 x_outside_op_line_type) = FALSE) THEN
237
238 IF (x_src_type = 'INVENTORY') THEN
239
240 x_src_org_id := null;
241 x_src_org := null;
242 x_src_org_code := null;
243 x_src_sub := null;
244 x_src_type := null;
245
246 return;
247
248 ELSIF (x_src_type = 'VENDOR') THEN
249
250 /*
251 ** copy null into the vendor related
252 ** fields. Since no validation is
253 ** being performed for vendor related
254 ** fields, copying null into the
255 ** source type field should be sufficient.
256 */
257
258 x_src_type := null;
259
260 END IF;
261 END IF;
262
263 END IF;
264
265
266 IF (x_src_type = 'INVENTORY') THEN
267 IF ((x_val_code = 'ALL') OR
268 (x_val_code = 'ORG')) THEN
269
270 x_progress := '060';
271
272 IF (po_orgs_sv2.val_source_org (x_src_org_id,
273 x_dest_org_id,
274 x_dest_type,
275 x_item_id,
276 x_item_rev,
277 x_sob_id,
278 x_error_type) = FALSE) THEN
279
280
281 x_src_org_id := null;
282 x_src_org := null;
283 x_src_org_code := null;
284 x_src_sub := null;
285
286
287 /*
288 ** DEBUG: Need to find a way of displaying
289 ** this message. Setting the message name
290 ** and using fnd_message.warn may be a
291 ** possible solution.
292 */
293
294 IF (x_error_type = 'SRC_DEST_ORG_CONTROL_MISMATCH') THEN
295 fnd_message.set_name ('PO',
296 'PO_RQ_INV_LOOSER_TIGHTER');
297
298 -- begin bug 3279513
299 ELSE --that is, x_error_type = 'INVALID_ORG'
300 fnd_message.set_name ('PO',
301 'PO_RI_INVALID_SOURCE_ORG_ID');
302 -- end bug 3279513
303 END IF;
304
305 return;
306
307 ELSE
308
309 -- Bug 5028505 , Added validation for location when validating the
310 -- Source Organization. We are now Validating that the deliver_to_location
311 -- has customer location association in the Source Organizations
312 -- Operating Unit
313
314 IF (x_deliver_to_loc_id is not null AND
315 x_src_org_id is NOT NULL) THEN
316 IF (po_locations_sv2.val_location (x_deliver_to_loc_id,
317 x_dest_org_id,
318 'N',
319 'N',
320 'Y',
321 x_src_org_id) = FALSE) THEN
322
323 x_src_org_id := null;
324 x_src_org := null;
325 x_src_org_code := null;
326 fnd_message.set_name ('PO','PO_REQ_SRC_REQUIRES_CUST');
327 return;
328
329 END IF;
330 END IF;
331
332 END IF;
333 END IF;
334
335 IF ((x_val_code = 'ALL') OR
336 (x_val_code = 'ORG') OR
337 (x_val_code = 'SUB')) THEN
338
339 /*Bug4256488: call the val_subinventory procedure to perform validations on
340 the source subinventory only when source subinventory is not null*/
341 IF (x_src_sub is not null) THEN
342
343 x_progress := '070';
344
345 IF (po_subinventories_s2.val_subinventory (x_dest_sub,
346 x_dest_org_id,
347 x_src_type,
348 x_src_sub,
349 x_src_org_id,
350 trunc(sysdate),
351 x_item_id,
352 x_dest_type,
353 'SOURCE',
354 x_error_type) = FALSE) THEN
355 x_src_sub := null;
356
357 IF (x_error_type = 'DEST_SUB_EQS_SRC_SUB') THEN
358 fnd_message.set_name ('PO',
359 'PO_RQ_SOURCE_SUB_EQS_DEST_SUB');
360
361 ELSIF (x_error_type = 'INVALID_SUB') THEN
362 fnd_message.set_name ('PO',
363 'PO_RI_INVALID_SRC_SUBINV');
364
365 ELSIF (x_error_type = 'INVALID_EXP_ASSET_SUBS') THEN
366 fnd_message.set_name ('PO',
367 'PO_RQ_INV_EXP_SUB_NA');
368
369 /* Ben: 2/7/97 added the following error message when Order entry
370 reservation is on, but sub is not reservable.
371 */
372 ELSIF (x_error_type = 'PO_RI_SRC_SUB_NOT_RESERVABLE') THEN
373
374 fnd_message.set_name ('PO','PO_RI_SRC_SUB_NOT_RESERVABLE');
375
376 END IF;
377
378 return;
379
380 END IF;
381 END IF; --Bug4256488
382 END IF;
383
384 ELSIF (x_src_type = 'VENDOR') THEN
385 IF (x_val_code = 'ALL') THEN
386
387 /* Any validation for vendor fields should be here */
388
389 null;
390
391 END IF;
392 END IF;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 po_message_s.sql_error('val_src_details', x_progress, sqlcode);
397 raise;
398
399 END val_src_details;
400
401
402
403 /*===========================================================================
404
405 FUNCTION NAME: get_max_line_num
406
407 ===========================================================================*/
408
409 FUNCTION get_max_line_num
410 (X_header_id NUMBER) return number is
411
412 x_max_line_num NUMBER;
413
414 BEGIN
415
416 SELECT nvl(max(line_num), 0)
417 INTO x_max_line_num
418 FROM po_requisition_lines
419 WHERE requisition_header_id = x_header_id;
420
421 return(x_max_line_num);
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 return(0);
426 RAISE;
427
428 END get_max_line_num;
429
430
431 /*===========================================================================
432
433 PROCEDURE NAME: update_modified_by_agent_flag
434
435 ===========================================================================*/
436
437 PROCEDURE update_modified_by_agent_flag(x_req_line_id IN NUMBER,
438 x_agent_id IN NUMBER)
439 IS
440
441 x_progress VARCHAR2(3) := NULL;
442 BEGIN
443
444 x_progress := '010';
445
446 -- <REQINPOOL>: added update of reqs_in_pool_flag and of
447 -- WHO columns.
448 UPDATE po_requisition_lines
449 SET modified_by_agent_flag = 'Y',
450 reqs_in_pool_flag = NULL,
451 purchasing_agent_id = x_agent_id,
452 last_update_date = SYSDATE,
453 last_updated_by = FND_GLOBAL.USER_ID,
454 last_update_login = FND_GLOBAL.LOGIN_ID
455 WHERE requisition_line_id = x_req_line_id;
456
457
458 EXCEPTION
459 WHEN OTHERS THEN
460 --dbms_output.put_line('In exception');
461 po_message_s.sql_error('update_modified_by_agent_flag',
462 x_progress, sqlcode);
463 raise;
464 END update_modified_by_agent_flag;
465
466
467 /*===========================================================================
468
469 PROCEDURE NAME: get_cost_price
470
471 ===========================================================================*/
472
473 PROCEDURE get_cost_price (x_item_id IN NUMBER,
474 x_organization_id IN NUMBER,
475 x_unit_of_measure IN VARCHAR2,
476 x_cost_price IN OUT NOCOPY NUMBER)
477
478 IS
479 x_progress VARCHAR2(3) := NULL;
480 x_primary_cost NUMBER := NULL;
481 x_primary_uom VARCHAR2(25) := NULL;
482
483 BEGIN
484
485 /*
486 ** Make sure that the input parameters
487 ** are being passed in.
488 */
489
490 IF ((x_item_id is null) OR
491 (x_organization_id is null) OR
492 (x_unit_of_measure is null)) THEN
493 return;
494
495 END IF;
496
497 x_progress := '010';
498
499 /*
500 ** Obtain the cost price for the specified
501 ** item and organization. This price is
502 ** in the primary unit of measure.
503 */
504
505 po_items_sv2.get_item_cost (x_item_id,
506 x_organization_id,
507 x_primary_cost);
508
509
510 /*
511 ** If the primary cost is zero there is
512 ** no need to continue with the conversion.
513 */
514
515 IF (x_primary_cost = 0) THEN
516
517 x_cost_price := x_primary_cost;
518 return;
519
520 END IF;
521
522 /*
523 ** Obtain the primary unit of measure
524 ** for the item.
525 */
526
527 x_progress := '020';
528
529 SELECT primary_unit_of_measure
530 INTO x_primary_uom
531 FROM mtl_system_items
532 WHERE inventory_item_id = x_item_id
533 AND organization_id = x_organization_id;
534
535 /*
536 ** If the primary unit of measure is
537 ** the same as the unit of measure
538 ** passed to this procedure then the cost
539 ** does not have to be converted.
540 */
541
542 IF (x_primary_uom = x_unit_of_measure) THEN
543
544 x_cost_price := x_primary_cost;
545 return;
546
547 END IF;
548
549 IF (po_uom_sv2.convert_inv_cost(X_item_id,
550 X_unit_of_measure,
551 X_primary_uom,
552 X_primary_cost,
553 X_cost_price) = TRUE) then
554
555 return;
556
557 ELSE
558
559 x_cost_price := 0;
560
561 END IF;
562
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 --dbms_output.put_line('In exception');
567 po_message_s.sql_error('get_cost_price',
568 x_progress, sqlcode);
569 raise;
570 END get_cost_price;
571
572
573 END po_req_lines_sv1;
574
[Home] [Help]