[Home] [Help]
PACKAGE BODY: APPS.PO_VAL_SHIPMENTS
Source
1 PACKAGE BODY PO_VAL_SHIPMENTS AS
2 -- $Header: PO_VAL_SHIPMENTS.plb 120.24.12020000.4 2013/04/03 22:32:45 rarajar ship $
3
4
5 c_ENTITY_TYPE_LINE_LOCATION CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_LINE_LOCATION;
6 c_ENTITY_TYPE_LINE CONSTANT VARCHAR2(30) := PO_VALIDATIONS.C_ENTITY_TYPE_LINE;
7
8 c_FIXED_PRICE CONSTANT VARCHAR2(30) := 'FIXED PRICE';
9 c_RATE CONSTANT VARCHAR2(30) := 'RATE';
10 c_STANDARD CONSTANT VARCHAR2(30) := 'STANDARD';
11 c_PREPAYMENT CONSTANT VARCHAR2(30) := 'PREPAYMENT'; -- <Complex Work R12>
12 c_DELIVERY CONSTANT VARCHAR2(30) := 'DELIVERY'; -- <Complex Work R12>
13
14 -- Constants for column names
15 c_DAYS_EARLY_RECEIPT_ALLOWED CONSTANT VARCHAR2(30) := 'DAYS_EARLY_RECEIPT_ALLOWED';
16 c_DAYS_LATE_RECEIPT_ALLOWED CONSTANT VARCHAR2(30) := 'DAYS_LATE_RECEIPT_ALLOWED';
17 c_RECEIVE_CLOSE_TOLERANCE CONSTANT VARCHAR2(30) := 'RECEIVE_CLOSE_TOLERANCE';
18 c_QTY_RCV_TOLERANCE CONSTANT VARCHAR2(30) := 'QTY_RCV_TOLERANCE';
19 c_INVOICE_CLOSE_TOLERANCE CONSTANT VARCHAR2(30) := 'INVOICE_CLOSE_TOLERANCE';
20 c_SHIP_TO_LOCATION_ID CONSTANT VARCHAR2(30) := 'SHIP_TO_LOCATION_ID';
21 c_SHIP_TO_ORGANIZATION_ID CONSTANT VARCHAR2(30) := 'SHIP_TO_ORGANIZATION_ID';
22 c_PROMISED_DATE CONSTANT VARCHAR2(30) := 'PROMISED_DATE';
23 c_NEED_BY_DATE CONSTANT VARCHAR2(30) := 'NEED_BY_DATE';
24 c_QUANTITY CONSTANT VARCHAR2(30) := 'QUANTITY';
25 c_SECONDARY_QUANTITY CONSTANT VARCHAR2(30) := 'SECONDARY_QUANTITY';
26 c_AMOUNT CONSTANT VARCHAR2(30) := 'AMOUNT';
27 c_SHIPMENT_NUM CONSTANT VARCHAR2(30) := 'SHIPMENT_NUM';
28 --Mod Project
29 c_CONTROL_ACTION CONSTANT VARCHAR2(30) := 'CONTROL_ACTION';
30
31 c_NEW CONSTANT VARCHAR2(30) := 'NEW';
32
33 c_POP_END_DATE CONSTANT VARCHAR2(30) := 'CLM_PERIOD_PERF_END_DATE';
34
35 c_PRICE_OVERRIDE CONSTANT VARCHAR2(30) := 'PRICE_OVERRIDE';
36
37 -- Bug 5385686 : Constnat for the unit field
38 c_UNIT_MEAS_LOOKUP_CODE CONSTANT VARCHAR2(30) := 'UNIT_MEAS_LOOKUP_CODE';
39
40 ---------------------------------------------------------------------------
41 -- Modules for debugging.
42 ---------------------------------------------------------------------------
43
44 -- The module base for this package.
45 D_PACKAGE_BASE CONSTANT VARCHAR2(50) :=
46 PO_LOG.get_package_base('PO_VAL_SHIPMENTS');
47
48 -- The module base for the subprogram.
49 D_days_early_gte_zero CONSTANT VARCHAR2(100) :=
50 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'days_early_gte_zero');
51
52 -- The module base for the subprogram.
53 D_days_late_gte_zero CONSTANT VARCHAR2(100) :=
54 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'days_late_gte_zero');
55
56 -- The module base for the subprogram.
57 D_rcv_close_tol_within_range CONSTANT VARCHAR2(100) :=
58 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'rcv_close_tol_within_range');
59
60 -- The module base for the subprogram.
61 D_over_rcpt_tol_within_range CONSTANT VARCHAR2(100) :=
62 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'over_rcpt_tol_within_range');
63
64 -- The module base for the subprogram.
65 D_planned_item_null_date_check CONSTANT VARCHAR2(100) :=
66 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'planned_item_null_date_check');
67
68 -- The module base for the subprogram.
69 D_match_4way_check CONSTANT VARCHAR2(100) :=
70 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'match_4way_check');
71
72 -- The module base for the subprogram.
73 D_inv_close_tol_range_check CONSTANT VARCHAR2(100) :=
74 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'inv_close_tol_range_check');
75
76 -- The module base for the subprogram.
77 D_need_by_date_open_per_check CONSTANT VARCHAR2(100) :=
78 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'need_by_date_open_period_check');
79
80 -- The module base for the subprogram.
81 D_promise_date_open_per_check CONSTANT VARCHAR2(100) :=
82 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'promise_date_open_period_check');
83
84 -- The module base for the subprogram.
85 D_ship_to_org_null_check CONSTANT VARCHAR2(100) :=
86 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_to_org_null_check');
87
88 -- The module base for the subprogram.
89 D_ship_to_loc_null_check CONSTANT VARCHAR2(100) :=
90 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_to_loc_null_check');
91
92 D_ship_num_gt_zero CONSTANT VARCHAR2(100) :=
93 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_num_gt_zero');
94
95 -- The module base for the subprogram.
96 D_ship_num_unique_check CONSTANT VARCHAR2(100) :=
97 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_num_unique_check');
98
99 -- The module base for the subprogram.
100 D_is_org_in_current_sob_check CONSTANT VARCHAR2(100) :=
101 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'is_org_in_current_sob_check');
102
103 -- The module base for the subprogram.
104 D_quantity_gt_zero CONSTANT VARCHAR2(100) :=
105 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_gt_zero');
106
107 -- The module base for the subprogram.
108 D_ship_sec_quantity_gt_zero CONSTANT VARCHAR2(100) :=
109 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_sec_quantity_gt_zero');
110
111 -- The module base for the subprogram.
112 D_amount_gt_zero CONSTANT VARCHAR2(100) :=
113 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_gt_zero');
114
115 -- <Complex Work R12 Start>: Combined billed and rcvd into exec
116 D_quantity_ge_quantity_exec CONSTANT VARCHAR2(100) :=
117 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'quantity_ge_quantity_exec');
118
119 D_amount_ge_amount_exec CONSTANT VARCHAR2(100) :=
120 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'amount_ge_amount_exec');
121
122 -- <Complex Work R12 End>
123
124 -- The module base for the subprogram.
125 D_ship_qtys_within_deviation CONSTANT VARCHAR2(100) :=
126 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'ship_qtys_within_deviation');
127
128 -- Bug 5385686 : Module base for the subprogram
129 D_unit_of_meas_not_null CONSTANT VARCHAR2(100) :=
130 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'unit_of_meas_not_null');
131
132 --Mod Project
133 D_mod_no_cancel_shp_complt CONSTANT VARCHAR2(100) :=
134 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'mod_no_cancel_shp_complt');
135
136 D_start_date_le_end_date CONSTANT VARCHAR2(100) :=
137 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'start_date_le_end_date');
138
139 D_complex_price_or_gt_zero CONSTANT VARCHAR2(100) :=
140 PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'complex_price_or_gt_zero');
141
142 -------------------------------------------------------------------------
143 -- Check if the days early value is greater than or equal to zero.
144 -------------------------------------------------------------------------
145
146 PROCEDURE days_early_gte_zero(
147 p_line_loc_id_tbl IN PO_TBL_NUMBER
148 , p_days_early_rcpt_allowed_tbl IN PO_TBL_NUMBER
149 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
150 , x_result_type OUT NOCOPY VARCHAR2
151 )
152 IS
153 BEGIN
154
155 PO_VALIDATION_HELPER.greater_or_equal_zero(
156 p_calling_module => D_days_early_gte_zero
157 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
158 , p_value_tbl => p_days_early_rcpt_allowed_tbl
159 , p_entity_id_tbl => p_line_loc_id_tbl
160 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
161 , p_column_name => c_DAYS_EARLY_RECEIPT_ALLOWED
162 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
163 , x_results => x_results
164 , x_result_type => x_result_type
165 );
166
167 END days_early_gte_zero;
168
169
170 -------------------------------------------------------------------------
171 -- Check if the days late value is greater than or equal to zero.
172 -------------------------------------------------------------------------
173 PROCEDURE days_late_gte_zero(
174 p_line_loc_id_tbl IN PO_TBL_NUMBER
175 , p_days_late_rcpt_allowed_tbl IN PO_TBL_NUMBER
176 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
177 , x_result_type OUT NOCOPY VARCHAR2
178 )
179 IS
180 BEGIN
181
182 PO_VALIDATION_HELPER.greater_or_equal_zero(
183 p_calling_module => D_days_late_gte_zero
184 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
185 , p_value_tbl => p_days_late_rcpt_allowed_tbl
186 , p_entity_id_tbl => p_line_loc_id_tbl
187 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
188 , p_column_name => c_DAYS_LATE_RECEIPT_ALLOWED
189 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
190 , x_results => x_results
191 , x_result_type => x_result_type
192 );
193
194 END days_late_gte_zero;
195
196
197 -------------------------------------------------------------------------
198 -- Check if the tolerance value is within 1-100
199 -------------------------------------------------------------------------
200
201 PROCEDURE rcv_close_tol_within_range (
202 p_line_loc_id_tbl IN PO_TBL_NUMBER
203 , p_receive_close_tolerance_tbl IN PO_TBL_NUMBER
204 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
205 , x_result_type OUT NOCOPY VARCHAR2
206 )
207 IS
208 BEGIN
209
210 PO_VALIDATION_HELPER.within_percentage_range(
211 p_calling_module => D_rcv_close_tol_within_range
212 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
213 , p_value_tbl => p_receive_close_tolerance_tbl
214 , p_entity_id_tbl => p_line_loc_id_tbl
215 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
216 , p_column_name => c_RECEIVE_CLOSE_TOLERANCE
217 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
218 , x_results => x_results
219 , x_result_type => x_result_type
220 );
221
222 END rcv_close_tol_within_range;
223
224 -------------------------------------------------------------------------
225 -- Check if the tolerance value is within 1-100
226 -------------------------------------------------------------------------
227
228 PROCEDURE over_rcpt_tol_within_range (
229 p_line_loc_id_tbl IN PO_TBL_NUMBER
230 , p_qty_rcv_tolerance_tbl IN PO_TBL_NUMBER
231 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
232 , x_result_type OUT NOCOPY VARCHAR2
233 )
234 IS
235 BEGIN
236
237 PO_VALIDATION_HELPER.within_percentage_range(
238 p_calling_module => D_over_rcpt_tol_within_range
239 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
240 , p_value_tbl => p_qty_rcv_tolerance_tbl
241 , p_entity_id_tbl => p_line_loc_id_tbl
242 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
243 , p_column_name => c_QTY_RCV_TOLERANCE
244 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_PERCENT
245 , x_results => x_results
246 , x_result_type => x_result_type
247 );
248
249 END over_rcpt_tol_within_range;
250
251 -------------------------------------------------------------------------
252 -- If the shipment is planned, fail if the promised date and need-by date
253 -- are both null.
254 -------------------------------------------------------------------------
255 PROCEDURE planned_item_null_date_check (
256 p_line_loc_id_tbl IN PO_TBL_NUMBER
257 , p_need_by_date_tbl IN PO_TBL_DATE
258 , p_promised_date_tbl IN PO_TBL_DATE
259 , p_item_id_tbl IN PO_TBL_NUMBER
260 , x_result_set_id IN OUT NOCOPY NUMBER
261 , x_result_type OUT NOCOPY VARCHAR2
262 )
263 IS
264 d_mod CONSTANT VARCHAR2(100) := D_planned_item_null_date_check;
265
266 l_line_loc_id_tbl PO_TBL_NUMBER;
267 l_item_id_tbl PO_TBL_NUMBER;
268 l_input_size NUMBER;
269 l_count NUMBER;
270
271 BEGIN
272
273 IF PO_LOG.d_proc THEN
274 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
275 PO_LOG.proc_begin(d_mod,'p_need_by_date_tbl',p_need_by_date_tbl);
276 PO_LOG.proc_begin(d_mod,'p_promised_date_tbl',p_promised_date_tbl);
277 PO_LOG.proc_begin(d_mod,'p_item_id_tbl',p_item_id_tbl);
278 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
279 END IF;
280
281 IF (x_result_set_id IS NULL) THEN
282 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
283 END IF;
284
285 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
286
287 l_input_size := p_line_loc_id_tbl.COUNT;
288
289 l_line_loc_id_tbl := PO_TBL_NUMBER();
290 l_line_loc_id_tbl.extend(l_input_size);
291 l_item_id_tbl := PO_TBL_NUMBER();
292 l_item_id_tbl.extend(l_input_size);
293
294 l_count := 0;
295
296 FOR i IN 1 .. p_line_loc_id_tbl.COUNT LOOP
297 IF (p_need_by_date_tbl(i) IS NULL AND p_promised_date_tbl(i) IS NULL) THEN
298 l_count := l_count + 1;
299 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
300 l_item_id_tbl(l_count) := p_item_id_tbl(i);
301 END IF;
302 END LOOP;
303
304 IF (l_count > 0) THEN
305
306 l_line_loc_id_tbl.trim(l_input_size-l_count);
307 l_item_id_tbl.trim(l_input_size-l_count);
308
309 FORALL i IN 1 .. l_line_loc_id_tbl.COUNT
310 INSERT INTO PO_VALIDATION_RESULTS_GT
311 ( result_set_id
312 , entity_type
313 , entity_id
314 , message_name
315 )
316 SELECT
317 x_result_set_id
318 , c_ENTITY_TYPE_LINE_LOCATION
319 , l_line_loc_id_tbl(i)
320 , PO_MESSAGE_S.PO_PO_PLANNED_ITEM_DATE_REQ
321 FROM
322 FINANCIALS_SYSTEM_PARAMETERS FSP
323 , MTL_SYSTEM_ITEMS MSI
324 WHERE
325 MSI.inventory_item_id = l_item_id_tbl(i)
326 AND MSI.organization_id = FSP.inventory_organization_id
327 AND
328 ( MSI.mrp_planning_code IN (3,4,7,8,9)
329 OR MSI.inventory_planning_code IN (1,2)
330 )
331 ;
332
333 IF (SQL%ROWCOUNT > 0) THEN
334 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
335 END IF;
336
337 END IF;
338
339 IF PO_LOG.d_proc THEN
340 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
341 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
342 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
343 END IF;
344
345 EXCEPTION
346 WHEN OTHERS THEN
347 IF PO_LOG.d_exc THEN
348 PO_LOG.exc(d_mod,0,NULL);
349 END IF;
350 RAISE;
351
352 END planned_item_null_date_check;
353
354 -------------------------------------------------------------------------
355 -- If the line type is 'FIXED PRICE' or 'RATE', match approval level
356 -- cannot be '4WAY'.
357 -- <Complex Work R12>: The match approval level of a payitem cannot be 4WAY
358 -------------------------------------------------------------------------
359 PROCEDURE match_4way_check(
360 p_line_loc_id_tbl IN PO_TBL_NUMBER
361 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
362 , p_receipt_required_flag_tbl IN PO_TBL_VARCHAR1
363 , p_inspection_required_flag_tbl IN PO_TBL_VARCHAR1
364 , p_payment_type_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
365 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
366 , x_result_type OUT NOCOPY VARCHAR2
367 )
368 IS
369 d_mod CONSTANT VARCHAR2(100) := D_match_4way_check;
370
371 l_results_count NUMBER;
372 BEGIN
373
374 IF PO_LOG.d_proc THEN
375 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
376 PO_LOG.proc_begin(d_mod,'p_value_basis_tbl',p_value_basis_tbl);
377 PO_LOG.proc_begin(d_mod,'p_receipt_required_flag_tbl',p_receipt_required_flag_tbl);
378 PO_LOG.proc_begin(d_mod,'p_inspection_required_flag_tbl',p_inspection_required_flag_tbl);
379 PO_LOG.proc_begin(d_mod,'p_payment_type_tbl', p_payment_type_tbl);
380 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
381 END IF;
382
383 IF (x_results IS NULL) THEN
384 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
385 END IF;
386
387 l_results_count := x_results.result_type.COUNT;
388
389 FOR i IN 1 .. p_line_loc_id_tbl.COUNT LOOP
390 IF (((p_value_basis_tbl(i) IN (c_FIXED_PRICE,c_RATE))
391 OR (p_payment_type_tbl(i) IS NOT NULL)) -- <Complex Work R12>
392 AND p_receipt_required_flag_tbl(i) = 'Y'
393 AND p_inspection_required_flag_tbl(i) = 'Y'
394 )
395 THEN
396 x_results.add_result(
397 p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
398 , p_entity_id => p_line_loc_id_tbl(i)
399 , p_column_name => NULL
400 , p_message_name => PO_MESSAGE_S.PO_INVALID_INVOICE_MATCH_FPS
401 );
402 END IF;
403 END LOOP;
404
405 IF (l_results_count < x_results.result_type.COUNT) THEN
406 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
407 ELSE
408 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
409 END IF;
410
411 IF PO_LOG.d_proc THEN
412 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
413 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
414 END IF;
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF PO_LOG.d_exc THEN
419 PO_LOG.exc(d_mod,0,NULL);
420 END IF;
421 RAISE;
422
423 END match_4way_check;
424
425
426 -------------------------------------------------------------------------
427 -- The invoice close tolerance must be between 0 and 100, inclusive.
428 -------------------------------------------------------------------------
429 PROCEDURE inv_close_tol_range_check (
430 p_line_loc_id_tbl IN PO_TBL_NUMBER
431 , p_invoice_close_tolerance_tbl IN PO_TBL_NUMBER
432 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
433 , x_result_type OUT NOCOPY VARCHAR2
434 )
435 IS
436 BEGIN
437
438 PO_VALIDATION_HELPER.within_percentage_range(
439 p_calling_module => D_inv_close_tol_range_check
440 , p_null_allowed_flag => PO_CORE_S.g_parameter_YES
441 , p_value_tbl => p_invoice_close_tolerance_tbl
442 , p_entity_id_tbl => p_line_loc_id_tbl
443 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
444 , p_column_name => c_INVOICE_CLOSE_TOLERANCE
445 , p_message_name => PO_MESSAGE_S.PO_PDOI_INV_CLOSE_TOLERANCE
446 --PBWC Message Change Impact: Removing a token.
447 , x_results => x_results
448 , x_result_type => x_result_type
449 );
450
451 END inv_close_tol_range_check;
452
453
454 -------------------------------------------------------------------------
455 -- If the profile option 'PO: Check open periods' is 'Yes', make sure
456 -- That the need by date is within the open purchasing period.
457 -- Otherwise, add the error 'RCV_ALL_OPEN_PO_PERIOD_HTML'.
458 -------------------------------------------------------------------------
459 PROCEDURE need_by_date_open_period_check(
460 p_line_loc_id_tbl IN PO_TBL_NUMBER
461 , p_line_id_tbl IN PO_TBL_NUMBER
462 , p_need_by_date_tbl IN PO_TBL_DATE
463 , p_org_id_tbl IN PO_TBL_NUMBER
464 , x_result_set_id IN OUT NOCOPY NUMBER
465 , x_result_type OUT NOCOPY VARCHAR2
466 )
467 IS
468 BEGIN
469
470 PO_VALIDATION_HELPER.open_period(
471 p_calling_module => D_need_by_date_open_per_check
472 , p_date_tbl => p_need_by_date_tbl
473 , p_org_id_tbl => p_org_id_tbl
474 , p_entity_id_tbl => p_line_loc_id_tbl
475 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
476 , p_column_name => c_NEED_BY_DATE
477 , p_message_name => PO_MESSAGE_S.RCV_ALL_OPEN_PO_PERIOD_HTML
478 --PBWC Message Change Impact: Adding a token
479 , p_token1_name => PO_MESSAGE_S.c_LINE_NUM_token
480 , p_token1_value => p_line_id_tbl
481 , x_result_set_id => x_result_set_id
482 , x_result_type => x_result_type
483 );
484
485 END need_by_date_open_period_check;
486
487
488 -------------------------------------------------------------------------
489 -- If the profile option 'PO: Check open periods' is 'Yes', make sure
490 -- That the promised date is within the open purchasing period.
491 -- Otherwise, add the error 'RCV_ALL_OPEN_PO_PERIOD_HTML'.
492 -------------------------------------------------------------------------
493 PROCEDURE promise_date_open_period_check(
494 p_line_loc_id_tbl IN PO_TBL_NUMBER
495 , p_line_id_tbl IN PO_TBL_NUMBER
496 , p_promised_date_tbl IN PO_TBL_DATE
497 , p_org_id_tbl IN PO_TBL_NUMBER
498 , x_result_set_id IN OUT NOCOPY NUMBER
499 , x_result_type OUT NOCOPY VARCHAR2
500 )
501 IS
502 BEGIN
503
504 PO_VALIDATION_HELPER.open_period(
505 p_calling_module => D_promise_date_open_per_check
506 , p_date_tbl => p_promised_date_tbl
507 , p_org_id_tbl => p_org_id_tbl
508 , p_entity_id_tbl => p_line_loc_id_tbl
509 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
510 , p_column_name => c_PROMISED_DATE
511 , p_message_name => PO_MESSAGE_S.RCV_ALL_OPEN_PO_PERIOD_HTML
512 --PBWC Message Change Impact: Adding a token
513 , p_token1_name => PO_MESSAGE_S.c_LINE_NUM_token
514 , p_token1_value => p_line_id_tbl
515 , x_result_set_id => x_result_set_id
516 , x_result_type => x_result_type
517 );
518
519 END promise_date_open_period_check;
520
521
522 -------------------------------------------------------------------------
523 -- For Standard POs, verifies that the ship-to-org id is not null.
524 -------------------------------------------------------------------------
525 PROCEDURE ship_to_org_null_check(
526 p_line_loc_id_tbl IN PO_TBL_NUMBER
527 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
528 , p_shipment_type_tbl IN PO_TBL_VARCHAR30
529 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
530 , x_result_type OUT NOCOPY VARCHAR2
531 )
532 IS
533 d_mod CONSTANT VARCHAR2(100) := D_ship_to_org_null_check;
534
535 l_line_loc_id_tbl PO_TBL_NUMBER;
536 l_ship_to_org_id_tbl PO_TBL_NUMBER;
537 l_input_size NUMBER;
538 l_count NUMBER;
539 BEGIN
540
541 IF PO_LOG.d_proc THEN
542 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
543 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
544 PO_LOG.proc_begin(d_mod,'p_shipment_type_tbl',p_shipment_type_tbl);
545 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
546 END IF;
547
548 l_input_size := p_line_loc_id_tbl.COUNT;
549
550 l_line_loc_id_tbl := PO_TBL_NUMBER();
551 l_line_loc_id_tbl.extend(l_input_size);
552 l_ship_to_org_id_tbl := PO_TBL_NUMBER();
553 l_ship_to_org_id_tbl.extend(l_input_size);
554
555 l_count := 0;
556
557 -- <Complex Work R12>: Include PREPAYMENT shipment_type in check
558 FOR i IN 1 .. l_input_size LOOP
559 IF (p_shipment_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT)) THEN
560 l_count := l_count + 1;
561 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
562 l_ship_to_org_id_tbl(l_count) := p_ship_to_org_id_tbl(i);
563 END IF;
564 END LOOP;
565
566 l_line_loc_id_tbl.trim(l_input_size-l_count);
567 l_ship_to_org_id_tbl.trim(l_input_size-l_count);
568
569 PO_VALIDATION_HELPER.not_null(
570 p_calling_module => d_mod
571 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(l_ship_to_org_id_tbl)
572 , p_entity_id_tbl => l_line_loc_id_tbl
573 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
574 , p_column_name => c_SHIP_TO_ORGANIZATION_ID
575 -- ECO# 4708990/4586199: Obsoleting some messages
576 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
577 , x_results => x_results
578 , x_result_type => x_result_type
579 );
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 IF PO_LOG.d_exc THEN
584 PO_LOG.exc(d_mod,0,NULL);
585 END IF;
586 RAISE;
587
588 END ship_to_org_null_check;
589
590 -------------------------------------------------------------------------
591 -- For Standard POs, verifies that the ship-to-org id is not null
592 -------------------------------------------------------------------------
593 PROCEDURE ship_to_loc_null_check(
594 p_line_loc_id_tbl IN PO_TBL_NUMBER
595 , p_ship_to_loc_id_tbl IN PO_TBL_NUMBER
596 , p_shipment_type_tbl IN PO_TBL_VARCHAR30
597 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
598 , x_result_type OUT NOCOPY VARCHAR2
599 )
600 IS
601 d_mod CONSTANT VARCHAR2(100) := D_ship_to_loc_null_check;
602
603 l_line_loc_id_tbl PO_TBL_NUMBER;
604 l_ship_to_loc_id_tbl PO_TBL_NUMBER;
605 l_input_size NUMBER;
606 l_count NUMBER;
607 BEGIN
608
609 IF PO_LOG.d_proc THEN
610 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
611 PO_LOG.proc_begin(d_mod,'p_ship_to_loc_id_tbl',p_ship_to_loc_id_tbl);
612 PO_LOG.proc_begin(d_mod,'p_shipment_type_tbl',p_shipment_type_tbl);
613 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
614 END IF;
615
616 l_input_size := p_line_loc_id_tbl.COUNT;
617
618 l_line_loc_id_tbl := PO_TBL_NUMBER();
619 l_line_loc_id_tbl.extend(l_input_size);
620 l_ship_to_loc_id_tbl := PO_TBL_NUMBER();
621 l_ship_to_loc_id_tbl.extend(l_input_size);
622
623 l_count := 0;
624
625 -- <Complex Work R12>: Include PREPAYMENT shipment_type in check
626 FOR i IN 1 .. l_input_size LOOP
627 IF (p_shipment_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT)) THEN
628 l_count := l_count + 1;
629 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
630 l_ship_to_loc_id_tbl(l_count) := p_ship_to_loc_id_tbl(i);
631 END IF;
632 END LOOP;
633
634 l_line_loc_id_tbl.trim(l_input_size-l_count);
635 l_ship_to_loc_id_tbl.trim(l_input_size-l_count);
636
637 PO_VALIDATION_HELPER.not_null(
638 p_calling_module => d_mod
639 , p_value_tbl => PO_TYPE_CONVERTER.to_po_tbl_varchar4000(l_ship_to_loc_id_tbl)
640 , p_entity_id_tbl => l_line_loc_id_tbl
641 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
642 , p_column_name => c_SHIP_TO_LOCATION_ID
643 -- ECO# 4708990/4586199: Obsoleting some messages
644 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
645 , x_results => x_results
646 , x_result_type => x_result_type
647 );
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 IF PO_LOG.d_exc THEN
652 PO_LOG.exc(d_mod,0,NULL);
653 END IF;
654 RAISE;
655
656 END ship_to_loc_null_check;
657
658 -----------------------------------------------------------------------------
659 -- Checks for null or non-positive shipment numbers.
660 -- Ignores Advance Line Locations, which always have a shipment number
661 -- of zero <Complex Work R12>.
662 -----------------------------------------------------------------------------
663 PROCEDURE ship_num_gt_zero(
664 p_line_loc_id_tbl IN PO_TBL_NUMBER
665 , p_shipment_num_tbl IN PO_TBL_NUMBER
666 , p_payment_type_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
667 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
668 , x_result_type OUT NOCOPY VARCHAR2
669 )
670 IS
671 l_line_loc_id_tbl PO_TBL_NUMBER; --<Complex Work R12>
672 l_shipment_num_tbl PO_TBL_NUMBER; --<Complex Work R12>
673 l_filtered_index NUMBER := 1; --<Complex Work R12>
674 BEGIN
675
676 l_line_loc_id_tbl := PO_TBL_NUMBER(); --<Complex Work R12>
677 l_shipment_num_tbl := PO_TBL_NUMBER(); --<Complex Work R12>
678
679 --<Complex Work R12>
680 --Loop through the existing line locations and exclude
681 --those of payment_type ADVANCE from this check
682 FOR i IN 1..p_line_loc_id_tbl.COUNT LOOP
683 IF nvl(p_payment_type_tbl(i), 'NULL') <> 'ADVANCE' THEN
684 l_line_loc_id_tbl.extend(1);
685 l_shipment_num_tbl.extend(1);
686 l_line_loc_id_tbl(l_filtered_index) := p_line_loc_id_tbl(i);
687 l_shipment_num_tbl(l_filtered_index) := p_shipment_num_tbl(i);
688 l_filtered_index := l_filtered_index + 1;
689 END IF;
690 END LOOP;
691
692 --<Complex Work R12>: only pass in the filtered
693 --list of line locations
694 PO_VALIDATION_HELPER.greater_than_zero(
695 p_calling_module => D_ship_num_gt_zero
696 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
697 , p_value_tbl => l_shipment_num_tbl --<Complex Work R12>
698 , p_entity_id_tbl => l_line_loc_id_tbl --<Complex Work R12>
699 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
700 , p_column_name => c_SHIPMENT_NUM
701 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
702 , x_results => x_results
703 , x_result_type => x_result_type
704 );
705
706 END ship_num_gt_zero;
707
708 ------------------------------------------------------------------------
709 -- Checks that the shipment numbers are unique for a particular line.
710 ------------------------------------------------------------------------
711 -- Assumption:
712 -- All of the unposted shipment data will be passed in
713 -- to this routine in order to get accurate results.
714 PROCEDURE ship_num_unique_check(
715 p_line_loc_id_tbl IN PO_TBL_NUMBER
716 , p_line_id_tbl IN PO_TBL_NUMBER
717 , p_shipment_num_tbl IN PO_TBL_NUMBER
718 , p_shipment_type_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
719 , p_par_draft_id_tbl IN PO_TBL_VARCHAR4000 DEFAULT NULL --<PAR Project>
720 , p_draft_id_tbl IN PO_TBL_NUMBER
721 , x_result_set_id IN OUT NOCOPY NUMBER
722 , x_result_type OUT NOCOPY VARCHAR2
723 )
724 IS
725 BEGIN
726
727 PO_VALIDATION_HELPER.child_num_unique(
728 p_calling_module => D_ship_num_unique_check
729 , p_entity_type => c_entity_type_LINE_LOCATION
730 , p_entity_id_tbl => p_line_loc_id_tbl
731 , p_parent_id_tbl => p_line_id_tbl
732 , p_entity_num_tbl => p_shipment_num_tbl
733 , p_entity_type_tbl => p_shipment_type_tbl -- <Complex Work R12>
734 , p_par_draft_id_tbl => p_par_draft_id_tbl -- <PAR Project>
735 , p_draft_id_tbl => p_draft_id_tbl --<PAR Project>
736 , x_result_set_id => x_result_set_id
737 , x_result_type => x_result_type
738 );
739
740 END ship_num_unique_check;
741
742 -------------------------------------------------------------------------
743 -- Invokes check_inv_org_in_sob to determine if the specified organization
744 -- is in the current set of books.
745 -------------------------------------------------------------------------
746 PROCEDURE is_org_in_current_sob_check (
747 p_line_loc_id_tbl IN PO_TBL_NUMBER
748 --PBWC Message Change Impact: Adding token
749 , p_line_id_tbl IN PO_TBL_NUMBER
750 , p_org_id_tbl IN PO_TBL_NUMBER
751 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
752 , p_consigned_flag_tbl IN PO_TBL_VARCHAR1
753 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
754 , x_result_type OUT NOCOPY VARCHAR2
755 )
756 IS
757 d_mod CONSTANT VARCHAR2(100) := D_is_org_in_current_sob_check;
758
759 l_results_count NUMBER;
760 l_data_key NUMBER;
761
762 l_line_loc_id_tbl PO_TBL_NUMBER;
763 --PBWC Message Change Impact: Adding a token
764 l_line_id_tbl PO_TBL_NUMBER;
765 l_ship_to_org_id_tbl PO_TBL_NUMBER;
766 l_set_of_books_id_tbl PO_TBL_NUMBER;
767
768 l_in_sob BOOLEAN;
769 l_return_status VARCHAR(1);
770 BEGIN
771
772 IF PO_LOG.d_proc THEN
773 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
774 PO_LOG.proc_begin(d_mod,'p_org_id_tbl',p_org_id_tbl);
775 PO_LOG.proc_begin(d_mod,'p_ship_to_org_id_tbl',p_ship_to_org_id_tbl);
776 PO_LOG.proc_begin(d_mod,'p_consigned_flag_tbl',p_consigned_flag_tbl);
777 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
778 END IF;
779
780 IF (x_results IS NULL) THEN
781 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
782 END IF;
783
784 l_results_count := x_results.result_type.COUNT;
785
786 l_data_key := PO_CORE_S.get_session_gt_nextval();
787
788 FORALL i IN 1 .. p_line_loc_id_tbl.COUNT
789 INSERT INTO PO_SESSION_GT SES
790 ( key
791 , num1
792 , num2
793 , num3
794 , num4 --PBWC Message Change Impact: Adding token
795 , char1
796 )
797 VALUES
798 ( l_data_key
799 , p_line_loc_id_tbl(i)
800 , p_org_id_tbl(i)
801 , p_ship_to_org_id_tbl(i)
802 , p_line_id_tbl(i) --PBWC Message Change Impact: Adding token
803 , p_consigned_flag_tbl(i)
804 )
805 ;
806
807 UPDATE PO_SESSION_GT SES
808 SET (num5) = --PBWC Message Change Impact: Adding token
809 ( SELECT
810 FSP.set_of_books_id
811 FROM
812 FINANCIALS_SYSTEM_PARAMS_ALL FSP
813 WHERE
814 FSP.org_id = SES.num2
815 )
816 WHERE
817 SES.key = l_data_key
818 AND SES.char1 = 'Y'
819 RETURNING
820 SES.num1
821 , SES.num3
822 , SES.num4 --PBWC Message Change Impact: Adding token
823 , SES.num5 --PBWC Message Change Impact: Adding token
824 BULK COLLECT INTO
825 l_line_loc_id_tbl
826 , l_ship_to_org_id_tbl
827 , l_line_id_tbl --PBWC Message Change Impact: Adding token
828 , l_set_of_books_id_tbl
829 ;
830
831 FOR i IN 1 .. l_line_loc_id_tbl.COUNT LOOP
832
833 PO_CORE_S.check_inv_org_in_sob(
834 x_return_status => l_return_status
835 , p_inv_org_id => l_ship_to_org_id_tbl(i)
836 , p_sob_id => l_set_of_books_id_tbl(i)
837 , x_in_sob => l_in_sob
838 );
839
840 IF (NOT l_in_sob) THEN
841
842 x_results.add_result(
843 p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
844 , p_entity_id => l_line_loc_id_tbl(i)
845 , p_column_name => c_SHIP_TO_ORGANIZATION_ID
846 , p_column_val => TO_CHAR(l_ship_to_org_id_tbl(i))
847 , p_message_name => PO_MESSAGE_S.PO_CONS_SHIP_TO_ORG_DIFF_SOB
848 --PBWC Message Change Impact: Adding token
849 , p_token1_name => PO_MESSAGE_S.c_LINE_NUM_token
850 , p_token1_value => TO_CHAR(l_line_id_tbl(i))
851 , p_token2_name => PO_MESSAGE_S.c_SCHED_NUM_token
852 , p_token2_value => TO_CHAR(l_line_loc_id_tbl(i))
853 --End PBWC Message Change Impact: Adding token
854 );
855
856 END IF;
857
858 END LOOP;
859
860 IF (l_results_count < x_results.result_type.COUNT) THEN
861 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
862 ELSE
863 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
864 END IF;
865
866 IF PO_LOG.d_proc THEN
867 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
868 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
869 END IF;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 IF PO_LOG.d_exc THEN
874 PO_LOG.exc(d_mod,0,NULL);
875 END IF;
876 RAISE;
877
878 END is_org_in_current_sob_check;
879
880 -----------------------------------------------------------------------------
881 -- Validates that quantity is not null and greater than zero if it is not
882 -- a Rate or Fixed Price line.
883 -----------------------------------------------------------------------------
884 PROCEDURE quantity_gt_zero(
885 p_line_loc_id_tbl IN PO_TBL_NUMBER
886 , p_quantity_tbl IN PO_TBL_NUMBER
887 , p_shipment_type_tbl IN PO_TBL_VARCHAR30
888 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
889 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
890 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
891 , x_result_type OUT NOCOPY VARCHAR2
892 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
893 )
894 IS
895 d_mod CONSTANT VARCHAR2(100) := D_quantity_gt_zero;
896 l_line_loc_id_tbl PO_TBL_NUMBER;
897 l_quantity_tbl PO_TBL_NUMBER;
898 l_input_size NUMBER;
899 l_count NUMBER;
900 --<Bug 15871378>
901 l_is_clm_po VARCHAR2(1) := 'N';
902 l_entity_id NUMBER;
903
904 BEGIN
905
906 IF PO_LOG.d_proc THEN
907 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
908 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
909 PO_LOG.proc_begin(d_mod,'p_shipment_type_tbl',p_shipment_type_tbl);
910 PO_LOG.proc_begin(d_mod,'p_value_basis_tbl',p_value_basis_tbl);
911 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
912 END IF;
913
914 l_input_size := p_line_loc_id_tbl.COUNT;
915
916 l_line_loc_id_tbl := PO_TBL_NUMBER();
917 l_line_loc_id_tbl.extend(l_input_size);
918 l_quantity_tbl := PO_TBL_NUMBER();
919 l_quantity_tbl.extend(l_input_size);
920
921 l_count := 0;
922
923 -- <Complex Work R12>: Include PREPAYMENT shipment_type in check
924 FOR i IN 1 .. l_input_size LOOP
925 IF (p_shipment_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT)
926 AND p_value_basis_tbl(i) NOT IN (c_RATE,c_FIXED_PRICE)
927 )
928 THEN
929 IF (p_value_basis_tbl(i) = 'QUANTITY' OR
930 (p_cost_constraint_tbl(i) IS NULL OR p_cost_constraint_tbl(i) NOT IN ('NSP', 'NC'))) then
931 l_count := l_count + 1;
932 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
933 l_quantity_tbl(l_count) := p_quantity_tbl(i);
934 END IF;
935 END IF;
936 END LOOP;
937
938 l_line_loc_id_tbl.trim(l_input_size-l_count);
939 l_quantity_tbl.trim(l_input_size-l_count);
940
941 --<Bug 15871378 Starts> For clm documents call greater_or_equal_zero else
942 --call greater_than_zero. Allowing zero value for CLM documents.
943
944 IF (p_header_id_tbl.Count > 0)
945 THEN
946 l_entity_id := p_header_id_tbl(1);
947
948 IF PO_LOG.d_stmt THEN
949 PO_LOG.stmt(d_mod,0,'l_entity_id ',l_entity_id);
950 END IF;
951
952 l_is_clm_po := PO_CLM_INTG_GRP.Is_clm_po(p_po_header_id => l_entity_id);
953 END IF;
954
955 IF PO_LOG.d_stmt THEN
956 PO_LOG.stmt(d_mod,10,'l_is_clm_po ',l_is_clm_po);
957 END IF;
958
959 IF l_is_clm_po = 'Y'
960 THEN
961 PO_VALIDATION_HELPER.greater_or_equal_zero(
962 p_calling_module => d_mod
963 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
964 , p_value_tbl => l_quantity_tbl
965 , p_entity_id_tbl => l_line_loc_id_tbl
966 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
967 , p_column_name => c_QUANTITY
968 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
969 , x_results => x_results
970 , x_result_type => x_result_type
971 );
972
973 ELSE
974
975 PO_VALIDATION_HELPER.greater_than_zero(
976 p_calling_module => d_mod
977 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
978 , p_value_tbl => l_quantity_tbl
979 , p_entity_id_tbl => l_line_loc_id_tbl
980 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
981 , p_column_name => c_QUANTITY
982 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
983 , x_results => x_results
984 , x_result_type => x_result_type
985 );
986
987 END IF;
988 --<Bug 15871378 Ends>
989
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF PO_LOG.d_exc THEN
993 PO_LOG.exc(d_mod,0,NULL);
994 END IF;
995 RAISE;
996
997 END quantity_gt_zero;
998
999 -- <Complex Work R12 Start>: Combined quantity_ge_quantity_rcvd and
1000 -- quantity_ge_quantity_billed into quantity_ge_quantity_exec
1001
1002 -----------------------------------------------------------------------------
1003 -- Validates that quantity is greater than or equal to quantity received,
1004 -- quantity billed, and quantity financed.
1005 -- This check is only performed if quantity is being reduced below the
1006 -- current transaction quantity, since over-receiving/billing is allowed.
1007 -----------------------------------------------------------------------------
1008 PROCEDURE quantity_ge_quantity_exec(
1009 p_line_loc_id_tbl IN PO_TBL_NUMBER
1010 , p_quantity_tbl IN PO_TBL_NUMBER
1011 , x_result_set_id IN OUT NOCOPY NUMBER
1012 , x_result_type OUT NOCOPY VARCHAR2
1013 )
1014 IS
1015 d_mod CONSTANT VARCHAR2(100) := D_quantity_ge_quantity_exec;
1016 BEGIN
1017
1018 IF PO_LOG.d_proc THEN
1019 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1020 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_quantity_tbl);
1021 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1022 END IF;
1023
1024 IF (x_result_set_id IS NULL) THEN
1025 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1026 END IF;
1027
1028
1029 FORALL i IN 1 .. p_line_loc_id_tbl.COUNT
1030 INSERT INTO PO_VALIDATION_RESULTS_GT
1031 ( result_set_id
1032 , entity_type
1033 , entity_id
1034 , column_name
1035 , column_val
1036 , message_name
1037 --PBWC Message Change Impact: Adding a token
1038 , token1_name
1039 , token1_value
1040 )
1041 SELECT
1042 x_result_set_id
1043 , c_ENTITY_TYPE_LINE_LOCATION
1044 , p_line_loc_id_tbl(i)
1045 , c_QUANTITY
1046 , TO_CHAR(p_quantity_tbl(i))
1047 , (CASE
1048 WHEN NVL(PLL.quantity_received, 0) >
1049 GREATEST(NVL(quantity_billed, 0), NVL(quantity_financed, 0))
1050 --PBWC Message Change Impact: Make it use the same message
1051 THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_RCVD_NA
1052 ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
1053 END
1054 )
1055 --PBWC Message Change Impact: Adding a token
1056 , (CASE
1057 WHEN NVL(PLL.quantity_received, 0) >
1058 GREATEST(NVL(quantity_billed, 0), NVL(quantity_financed, 0))
1059 THEN PO_MESSAGE_S.c_QTY_RCVD_TOKEN
1060 ELSE PO_MESSAGE_S.c_QTY_BILLED_TOKEN
1061 END
1062 )
1063 , (CASE
1064 WHEN NVL(PLL.quantity_received, 0) >
1065 GREATEST(NVL(quantity_billed, 0), NVL(quantity_financed, 0))
1066 THEN to_char(PLL.quantity_received)
1067 ELSE to_char(quantity_billed)
1068 END
1069 )
1070 --End PBWC Message Change Impact: Adding a token
1071 FROM
1072 PO_LINE_LOCATIONS_ALL PLL
1073 WHERE
1074 PLL.line_location_id = p_line_loc_id_tbl(i)
1075 AND PLL.shipment_type IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
1076 AND p_quantity_tbl(i) IS NOT NULL
1077 -- Quantity is being reduced below the current transaction quantity:
1078 AND p_quantity_tbl(i) < PLL.quantity
1079 AND p_quantity_tbl(i) < GREATEST(NVL(PLL.quantity_received, 0),
1080 NVL(PLL.quantity_billed, 0),
1081 NVL(PLL.quantity_financed, 0))
1082 ;
1083
1084 IF (SQL%ROWCOUNT > 0) THEN
1085 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1086 ELSE
1087 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1088 END IF;
1089
1090 IF PO_LOG.d_proc THEN
1091 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1092 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1093 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1094 END IF;
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 IF PO_LOG.d_exc THEN
1099 PO_LOG.exc(d_mod,0,NULL);
1100 END IF;
1101 RAISE;
1102
1103 END quantity_ge_quantity_exec;
1104
1105 -- <Complex Work R12 End>
1106
1107 -----------------------------------------------------------------------------
1108 -- Validates that amount is not null and greater than zero if the line is
1109 -- Rate or Fixed Price.
1110 -----------------------------------------------------------------------------
1111 PROCEDURE amount_gt_zero(
1112 p_line_loc_id_tbl IN PO_TBL_NUMBER
1113 , p_amount_tbl IN PO_TBL_NUMBER
1114 , p_shipment_type_tbl IN PO_TBL_VARCHAR30
1115 , p_value_basis_tbl IN PO_TBL_VARCHAR30 -- <Complex Work R12>
1116 , p_cost_constraint_tbl IN PO_TBL_VARCHAR30
1117 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1118 , x_result_type OUT NOCOPY VARCHAR2
1119 , p_header_id_tbl IN PO_TBL_NUMBER --<Bug 15871378>
1120 )
1121 IS
1122 d_mod CONSTANT VARCHAR2(100) := D_amount_gt_zero;
1123 l_line_loc_id_tbl PO_TBL_NUMBER;
1124 l_amount_tbl PO_TBL_NUMBER;
1125 l_input_size NUMBER;
1126 l_count NUMBER;
1127
1128 --<Bug 15871378>
1129 l_is_clm_po VARCHAR2(1) := 'N';
1130 l_entity_id NUMBER;
1131
1132 BEGIN
1133
1134 IF PO_LOG.d_proc THEN
1135 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1136 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
1137 PO_LOG.proc_begin(d_mod,'p_shipment_type_tbl',p_shipment_type_tbl);
1138 PO_LOG.proc_begin(d_mod,'p_value_basis_tbl',p_value_basis_tbl);
1139 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1140 END IF;
1141
1142 l_input_size := p_line_loc_id_tbl.COUNT;
1143
1144 l_line_loc_id_tbl := PO_TBL_NUMBER();
1145 l_line_loc_id_tbl.extend(l_input_size);
1146 l_amount_tbl := PO_TBL_NUMBER();
1147 l_amount_tbl.extend(l_input_size);
1148
1149 l_count := 0;
1150
1151 -- <Complex Work R12>: Include shipment_type of PREPAYMENT in check
1152 FOR i IN 1 .. l_input_size LOOP
1153 IF (p_shipment_type_tbl(i) IN (c_STANDARD, c_PREPAYMENT)
1154 AND p_value_basis_tbl(i) IN (c_RATE,c_FIXED_PRICE)
1155 )
1156 THEN
1157 IF (p_value_basis_tbl(i) = 'QUANTITY' OR
1158 (p_cost_constraint_tbl(i) IS NULL OR p_cost_constraint_tbl(i) NOT IN ('NSP', 'NC'))) then
1159 l_count := l_count + 1;
1160 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
1161 l_amount_tbl(l_count) := p_amount_tbl(i);
1162 END IF;
1163 END IF;
1164 END LOOP;
1165
1166 l_line_loc_id_tbl.trim(l_input_size-l_count);
1167 l_amount_tbl.trim(l_input_size-l_count);
1168
1169 --<Bug 15871378 Starts> For clm documents call greater_or_equal_zero else
1170 --call greater_than_zero. Allowing zero value for CLM documents.
1171
1172 IF (p_header_id_tbl.Count > 0)
1173 THEN
1174 l_entity_id := p_header_id_tbl(1);
1175
1176 IF PO_LOG.d_stmt THEN
1177 PO_LOG.stmt(d_mod,0,'l_entity_id ',l_entity_id);
1178 END IF;
1179
1180 l_is_clm_po := PO_CLM_INTG_GRP.Is_clm_po(p_po_header_id => l_entity_id);
1181 END IF;
1182
1183 IF PO_LOG.d_stmt THEN
1184 PO_LOG.stmt(d_mod,10,'l_is_clm_po ',l_is_clm_po);
1185 END IF;
1186
1187 IF l_is_clm_po = 'Y'
1188 THEN
1189 PO_VALIDATION_HELPER.greater_or_equal_zero(
1190 p_calling_module => d_mod
1191 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1192 , p_value_tbl => l_amount_tbl
1193 , p_entity_id_tbl => l_line_loc_id_tbl
1194 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
1195 , p_column_name => c_AMOUNT
1196 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
1197 , x_results => x_results
1198 , x_result_type => x_result_type
1199 );
1200
1201 ELSE
1202
1203 PO_VALIDATION_HELPER.greater_than_zero(
1204 p_calling_module => d_mod
1205 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1206 , p_value_tbl => l_amount_tbl
1207 , p_entity_id_tbl => l_line_loc_id_tbl
1208 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
1209 , p_column_name => c_AMOUNT
1210 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
1211 , x_results => x_results
1212 , x_result_type => x_result_type
1213 );
1214
1215 END IF;
1216 --<Bug 15871378 Ends>
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 IF PO_LOG.d_exc THEN
1221 PO_LOG.exc(d_mod,0,NULL);
1222 END IF;
1223 RAISE;
1224
1225 END amount_gt_zero;
1226
1227 -- <Complex Work R12 Start>: Combined amount_ge_amount_rcvd and
1228 -- amount_ge_amount_billed into amount_ge_amount_exec
1229
1230 -----------------------------------------------------------------------------
1231 -- Validates that amount is greater than or equal to amount received,
1232 -- amount billed, and amount financed.
1233 -- This check is only performed if amount is being reduced below the
1234 -- current transaction amount, since over-receiving/billing is allowed.
1235 -----------------------------------------------------------------------------
1236 PROCEDURE amount_ge_amount_exec(
1237 p_line_loc_id_tbl IN PO_TBL_NUMBER
1238 , p_amount_tbl IN PO_TBL_NUMBER
1239 , x_result_set_id IN OUT NOCOPY NUMBER
1240 , x_result_type OUT NOCOPY VARCHAR2
1241 )
1242 IS
1243 d_mod CONSTANT VARCHAR2(100) := D_amount_ge_amount_exec;
1244 BEGIN
1245
1246 IF PO_LOG.d_proc THEN
1247 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1248 PO_LOG.proc_begin(d_mod,'p_amount_tbl',p_amount_tbl);
1249 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1250 END IF;
1251
1252 IF (x_result_set_id IS NULL) THEN
1253 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1254 END IF;
1255
1256 FORALL i IN 1 .. p_line_loc_id_tbl.COUNT
1257 INSERT INTO PO_VALIDATION_RESULTS_GT
1258 ( result_set_id
1259 , entity_type
1260 , entity_id
1261 , column_name
1262 , column_val
1263 , message_name
1264 --PBWC Message Change Impact: Adding a token
1265 , token1_name
1266 , token1_value
1267 )
1268 SELECT
1269 x_result_set_id
1270 , c_ENTITY_TYPE_LINE_LOCATION
1271 , p_line_loc_id_tbl(i)
1272 , c_AMOUNT
1273 , TO_CHAR(p_amount_tbl(i))
1274 , (CASE
1275 WHEN NVL(PLL.amount_received, 0) >
1276 GREATEST(NVL(amount_billed, 0), NVL(amount_financed, 0))
1277 THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_RCVD_NA
1278 ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
1279 END
1280 )
1281 --PBWC Message Change Impact: Adding a token
1282 , (CASE
1283 WHEN NVL(PLL.amount_received, 0) >
1284 GREATEST(NVL(amount_billed, 0), NVL(amount_financed, 0))
1285 THEN PO_MESSAGE_S.c_AMT_RCVD_TOKEN
1286 ELSE PO_MESSAGE_S.c_AMT_BILLED_TOKEN
1287 END
1288 )
1289 , (CASE
1290 WHEN NVL(PLL.amount_received, 0) >
1291 GREATEST(NVL(amount_billed, 0), NVL(amount_financed, 0))
1292 THEN TO_CHAR(PLL.amount_received)
1293 ELSE TO_CHAR(amount_billed)
1294 END
1295 )
1296 FROM
1297 PO_LINE_LOCATIONS_ALL PLL
1298 WHERE
1299 PLL.line_location_id = p_line_loc_id_tbl(i)
1300 AND PLL.shipment_type IN (c_STANDARD, c_PREPAYMENT) -- <Complex Work R12>
1301 AND p_amount_tbl(i) IS NOT NULL
1302 -- Amount is being reduced below the current transaction amount:
1303 AND p_amount_tbl(i) < PLL.amount
1304 AND p_amount_tbl(i) < GREATEST(NVL(PLL.amount_received, 0),
1305 NVL(PLL.amount_billed, 0),
1306 NVL(PLL.amount_financed, 0))
1307 ;
1308
1309 IF (SQL%ROWCOUNT > 0) THEN
1310 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1311 ELSE
1312 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1313 END IF;
1314
1315 IF PO_LOG.d_proc THEN
1316 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1317 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1318 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1319 END IF;
1320
1321 EXCEPTION
1322 WHEN OTHERS THEN
1323 IF PO_LOG.d_exc THEN
1324 PO_LOG.exc(d_mod,0,NULL);
1325 END IF;
1326 RAISE;
1327
1328 END amount_ge_amount_exec;
1329
1330 -- <Complex Work R12 End>
1331
1332 -----------------------------------------------------------------------------
1333 -- OPM Integration R12
1334 -- Validates that secondary quantity is not null and greater than zero for
1335 -- an opm item.
1336 -----------------------------------------------------------------------------
1337 PROCEDURE ship_sec_quantity_gt_zero(
1338 p_line_loc_id_tbl IN PO_TBL_NUMBER
1339 , p_item_id_tbl IN PO_TBL_NUMBER
1340 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
1341 , p_sec_quantity_tbl IN PO_TBL_NUMBER
1342 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1343 , x_result_type OUT NOCOPY VARCHAR2
1344 )
1345 IS
1346 d_mod CONSTANT VARCHAR2(100) := D_ship_sec_quantity_gt_zero;
1347 BEGIN
1348
1349 IF PO_LOG.d_proc THEN
1350 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1351 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
1352 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1353 END IF;
1354
1355 PO_VALIDATION_HELPER.gt_zero_opm_filter(
1356 p_calling_module => D_ship_sec_quantity_gt_zero
1357 , p_value_tbl => p_sec_quantity_tbl
1358 , p_entity_id_tbl => p_line_loc_id_tbl
1359 , p_item_id_tbl => p_item_id_tbl
1360 , p_inv_org_id_tbl => p_ship_to_org_id_tbl
1361 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
1362 , p_column_name => c_SECONDARY_QUANTITY
1363 , x_results => x_results
1364 , x_result_type => x_result_type
1365 );
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 IF PO_LOG.d_exc THEN
1370 PO_LOG.exc(d_mod,0,NULL);
1371 END IF;
1372 RAISE;
1373
1374 END ship_sec_quantity_gt_zero;
1375
1376 -----------------------------------------------------------------------------
1377 -- OPM Integration R12
1378 -- Validates secondary quantity and the quantity combination is for
1379 -- an opm item
1380 -----------------------------------------------------------------------------
1381 PROCEDURE ship_qtys_within_deviation (
1382 p_line_loc_id_tbl IN PO_TBL_NUMBER
1383 , p_item_id_tbl IN PO_TBL_NUMBER
1384 , p_ship_to_org_id_tbl IN PO_TBL_NUMBER
1385 , p_quantity_tbl IN PO_TBL_NUMBER
1386 , p_primary_uom_tbl IN PO_TBL_VARCHAR30
1387 , p_sec_quantity_tbl IN PO_TBL_NUMBER
1388 , p_secondary_uom_tbl IN PO_TBL_VARCHAR30
1389 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1390 , x_result_type OUT NOCOPY VARCHAR2
1391 )
1392 IS
1393
1394 d_mod CONSTANT VARCHAR2(100) := D_ship_qtys_within_deviation;
1395
1396 BEGIN
1397
1398 IF PO_LOG.d_proc THEN
1399 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1400 PO_LOG.proc_begin(d_mod,'p_quantity_tbl',p_sec_quantity_tbl);
1401 PO_LOG.proc_begin(d_mod,'p_sec_quantity_tbl',p_sec_quantity_tbl);
1402 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1403 END IF;
1404
1405 PO_VALIDATION_HELPER.qtys_within_deviation (
1406 p_calling_module => D_ship_qtys_within_deviation
1407 , p_entity_id_tbl => p_line_loc_id_tbl
1408 , p_item_id_tbl => p_item_id_tbl
1409 , p_inv_org_id_tbl => p_ship_to_org_id_tbl
1410 , p_quantity_tbl => p_quantity_tbl
1411 , p_primary_uom_tbl => p_primary_uom_tbl
1412 , p_sec_quantity_tbl => p_sec_quantity_tbl
1413 , p_secondary_uom_tbl => p_secondary_uom_tbl
1414 , p_column_name => c_QUANTITY
1415 , x_results => x_results
1416 , x_result_type => x_result_type
1417 );
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN
1421 IF PO_LOG.d_exc THEN
1422 PO_LOG.exc(d_mod,0,NULL);
1423 END IF;
1424 RAISE;
1425
1426 END ship_qtys_within_deviation;
1427
1428 -----------------------------------------------------------------------------
1429 -- Bug 5385686
1430 -- Validates that the UOM is not null for
1431 -- a. Quantity based Pay Items (pessimistic check)
1432 -- b. rate based Pay Items with payment type as Rate or Delivery
1433 -----------------------------------------------------------------------------
1434 PROCEDURE unit_of_measure_not_null(
1435 p_line_loc_id_tbl IN PO_TBL_NUMBER
1436 , p_payment_type_tbl IN PO_TBL_VARCHAR30
1437 , p_value_basis_tbl IN PO_TBL_VARCHAR30
1438 , p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30
1439 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1440 , x_result_type OUT NOCOPY VARCHAR2
1441 ) IS
1442
1443 d_mod CONSTANT VARCHAR2(100) := D_unit_of_meas_not_null;
1444
1445 l_results_count NUMBER;
1446 BEGIN
1447
1448 IF PO_LOG.d_proc THEN
1449 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1450 PO_LOG.proc_begin(d_mod,'p_payment_type_tbl',p_payment_type_tbl);
1451 PO_LOG.proc_begin(d_mod,'p_unit_meas_lookup_code_tbl',p_unit_meas_lookup_code_tbl);
1452 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1453 END IF;
1454
1455 IF (x_results IS NULL) THEN
1456 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1457 END IF;
1458
1459 l_results_count := x_results.result_type.COUNT; -- Bug 5532560
1460
1461 FOR i IN 1..p_line_loc_id_tbl.COUNT LOOP
1462 IF ((p_value_basis_tbl(i) = c_QUANTITY OR
1463 (p_value_basis_tbl(i) = c_FIXED_PRICE AND p_payment_type_tbl(i) = c_RATE)) -- Bug 5514671 : Only validate Rate and not delivery
1464 AND p_unit_meas_lookup_code_tbl(i) IS NULL) THEN
1465 x_results.add_result(
1466 p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
1467 , p_entity_id => p_line_loc_id_tbl(i)
1468 , p_column_name => c_UNIT_MEAS_LOOKUP_CODE
1469 , p_column_val => NULL
1470 , p_message_name => PO_MESSAGE_S.PO_ALL_NOT_NULL
1471 );
1472 END IF;
1473 END LOOP;
1474
1475 -- Bug 5532560 START
1476 IF (l_results_count < x_results.result_type.COUNT) THEN
1477 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1478 ELSE
1479 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1480 END IF;
1481
1482 IF PO_LOG.d_proc THEN
1483 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1484 PO_LOG.log(PO_LOG.c_PROC_END,d_mod,NULL,'x_results',x_results);
1485 END IF;
1486 -- Bug 5532560 END
1487 EXCEPTION
1488 WHEN OTHERS THEN
1489 IF PO_LOG.d_exc THEN
1490 PO_LOG.exc(d_mod,0,NULL);
1491 END IF;
1492 RAISE;
1493
1494 END unit_of_measure_not_null;
1495
1496 --Mod Project
1497 PROCEDURE mod_no_cancel_shp_complt(
1498 p_line_loc_id_tbl IN PO_TBL_NUMBER
1499 ,p_control_action_tbl IN PO_TBL_VARCHAR30
1500 ,x_result_set_id IN OUT NOCOPY NUMBER
1501 ,x_result_type OUT NOCOPY VARCHAR2
1502 )
1503 IS
1504 d_mod CONSTANT VARCHAR2(100) := D_mod_no_cancel_shp_complt;
1505 BEGIN
1506
1507 IF PO_LOG.d_proc THEN
1508 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1509 PO_LOG.proc_begin(d_mod,'p_control_action_tbl',p_control_action_tbl);
1510 PO_LOG.proc_begin(d_mod,'x_result_set_id',x_result_set_id);
1511 END IF;
1512 IF (x_result_set_id IS NULL) THEN
1513 x_result_set_id := PO_VALIDATIONS.next_result_set_id();
1514 END IF;
1515
1516 FORALL i IN 1..p_line_loc_id_tbl.COUNT
1517 INSERT INTO po_validation_results_gt
1518 (result_set_id,
1519 entity_type,
1520 entity_id,
1521 message_name,
1522 column_name,
1523 column_val)
1524 SELECT x_result_set_id,
1525 c_entity_type_LINE
1526 ,p_line_loc_id_tbl(i)
1527 ,PO_MESSAGE_S.PO_MOD_CANCEL_INVALID
1528 ,c_CONTROL_ACTION
1529 ,TO_CHAR(p_control_action_tbl(i))
1530 FROM DUAL
1531 WHERE nvl(p_control_action_tbl(i), '*') = 'CANCEL'
1532 AND not exists
1533 (SELECT 'Y'
1534 FROM PO_LINE_LOCATIONS_ALL
1535 WHERE line_location_id = p_line_loc_id_tbl(i)
1536 AND quantity > quantity_received);
1537
1538 IF (SQL%ROWCOUNT > 0) THEN
1539 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1540 ELSE
1541 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1542 END IF;
1543
1544
1545 IF PO_LOG.d_proc THEN
1546 PO_VALIDATIONS.log_validation_results_gt(d_mod,9,x_result_set_id);
1547 PO_LOG.proc_end(d_mod,'x_result_type',x_result_type);
1548 PO_LOG.proc_end(d_mod,'x_result_set_id',x_result_set_id);
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN OTHERS THEN
1553 IF PO_LOG.d_exc THEN
1554 PO_LOG.exc(d_mod,0,NULL);
1555 END IF;
1556 RAISE;
1557 END mod_no_cancel_shp_complt;
1558
1559
1560 PROCEDURE pop_start_date_le_end_date
1561 (
1562 p_line_id_tbl IN PO_TBL_NUMBER
1563 , p_start_date_tbl IN PO_TBL_DATE
1564 , p_expiration_date_tbl IN PO_TBL_DATE
1565 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1566 , x_result_type OUT NOCOPY VARCHAR2
1567 )
1568 IS
1569 l_results_count NUMBER;
1570 BEGIN
1571
1572 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1573 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1574 RETURN;
1575 END IF;
1576
1577 IF (x_results IS NULL) THEN
1578 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1579 END IF;
1580
1581 l_results_count := x_results.result_type.COUNT;
1582
1583 FOR i IN 1 .. p_line_id_tbl.count LOOP
1584
1585 IF p_expiration_date_tbl(i) IS NULL AND p_start_date_tbl(i) IS NOT NULL
1586 OR p_expiration_date_tbl(i) IS NOT NULL AND p_start_date_tbl(i) IS NULL
1587 THEN
1588 x_results.add_result
1589 (
1590 p_entity_type => c_ENTITY_TYPE_LINE
1591 , p_entity_id => p_line_id_tbl(i)
1592 , p_column_name => c_POP_END_DATE
1593 , p_message_name => PO_MESSAGE_S.PO_IDV_POP_END_DT_NULL
1594 );
1595 END IF;
1596 END LOOP;
1597
1598 IF (l_results_count < x_results.result_type.COUNT) THEN
1599 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1600 ELSE
1601 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1602 PO_VALIDATION_HELPER.start_date_le_end_date
1603 (
1604 p_calling_module => D_start_date_le_end_date
1605 , p_start_date_tbl => p_start_date_tbl
1606 , p_end_date_tbl => p_expiration_date_tbl
1607 , p_entity_id_tbl => p_line_id_tbl
1608 , p_entity_type => c_ENTITY_TYPE_LINE
1609 , p_column_name => c_POP_END_DATE
1610 , p_column_val_selector => NULL
1611 , p_message_name => PO_MESSAGE_S.PO_IDV_LINE_POP_DATES
1612 , x_results => x_results
1613 , x_result_type => x_result_type
1614 );
1615 END IF;
1616
1617 EXCEPTION WHEN OTHERS THEN
1618 RAISE;
1619 END pop_start_date_le_end_date;
1620
1621 PROCEDURE pop_date_need_by_date
1622 (
1623 p_line_id_tbl IN PO_TBL_NUMBER
1624 , p_start_date_tbl IN PO_TBL_DATE
1625 , p_end_date_tbl IN PO_TBL_DATE
1626 , p_need_by_date_tbl IN PO_TBL_DATE
1627 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1628 , x_result_type OUT NOCOPY VARCHAR2
1629 )
1630 IS
1631 l_results_count NUMBER;
1632 BEGIN
1633
1634 IF NOT PO_CLM_CLO_UTIL.Isclminstalled THEN
1635 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1636 RETURN;
1637 END IF;
1638
1639 IF (x_results IS NULL) THEN
1640 x_results := PO_VALIDATION_RESULTS_TYPE.new_instance();
1641 END IF;
1642
1643 l_results_count := x_results.result_type.COUNT;
1644
1645 FOR i IN 1 .. p_line_id_tbl.count LOOP
1646
1647 IF (p_start_date_tbl(i) IS NOT NULL AND p_need_by_date_tbl(i) IS NOT NULL)
1648 OR (p_end_date_tbl(i) IS NOT NULL AND p_need_by_date_tbl(i) IS NOT NULL)
1649 THEN
1650 x_results.add_result
1651 (
1652 p_entity_type => c_ENTITY_TYPE_LINE
1653 , p_entity_id => p_line_id_tbl(i)
1654 , p_column_name => c_POP_END_DATE
1655 , p_message_name => PO_MESSAGE_S.PO_IDV_POP_DT_NEED_BY_NULL
1656 );
1657 END IF;
1658 END LOOP;
1659
1660 IF (l_results_count < x_results.result_type.COUNT) THEN
1661 x_result_type := PO_VALIDATIONS.c_result_type_FAILURE;
1662 ELSE
1663 x_result_type := PO_VALIDATIONS.c_result_type_SUCCESS;
1664 END IF;
1665
1666 EXCEPTION WHEN OTHERS THEN
1667 RAISE;
1668 END pop_date_need_by_date;
1669
1670 -------------------------------------------------------------------------
1671 -- For Complex PO's, Check if price override is greater than zero.
1672 -------------------------------------------------------------------------
1673 PROCEDURE complex_price_or_gt_zero(
1674 p_line_loc_id_tbl IN PO_TBL_NUMBER
1675 , p_price_override_tbl IN PO_TBL_NUMBER
1676 , p_value_basis_tbl IN PO_TBL_VARCHAR30
1677 , p_payment_type_tbl IN PO_TBL_VARCHAR30
1678 , x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
1679 , x_result_type OUT NOCOPY VARCHAR2
1680 )
1681 IS
1682 d_mod CONSTANT VARCHAR2(100) := D_complex_price_or_gt_zero;
1683 l_line_loc_id_tbl PO_TBL_NUMBER;
1684 l_price_override_tbl PO_TBL_NUMBER;
1685 l_input_size NUMBER;
1686 l_count NUMBER;
1687 BEGIN
1688 IF PO_LOG.d_proc THEN
1689 PO_LOG.proc_begin(d_mod,'p_line_loc_id_tbl',p_line_loc_id_tbl);
1690 PO_LOG.proc_begin(d_mod,'p_price_override_tbl',p_price_override_tbl);
1691 PO_LOG.proc_begin(d_mod,'p_value_basis_tbl',p_value_basis_tbl);
1692 PO_LOG.proc_begin(d_mod,'p_payment_type_tbl',p_payment_type_tbl);
1693 PO_LOG.log(PO_LOG.c_PROC_BEGIN,d_mod,NULL,'x_results',x_results);
1694 END IF;
1695
1696
1697 l_input_size := p_line_loc_id_tbl.COUNT;
1698
1699 l_line_loc_id_tbl := PO_TBL_NUMBER();
1700 l_line_loc_id_tbl.extend(l_input_size);
1701 l_price_override_tbl := PO_TBL_NUMBER();
1702 l_price_override_tbl.extend(l_input_size);
1703
1704 l_count := 0;
1705
1706 FOR i IN 1 .. l_input_size LOOP
1707 IF ((p_payment_type_tbl(i) IS NOT NULL)
1708 AND (p_value_basis_tbl(i) NOT IN (c_FIXED_PRICE)
1709 OR p_payment_type_tbl(i) in (c_RATE))) THEN
1710 l_count := l_count + 1;
1711 l_line_loc_id_tbl(l_count) := p_line_loc_id_tbl(i);
1712 l_price_override_tbl(l_count) := p_price_override_tbl(i);
1713 END IF;
1714 END LOOP;
1715
1716 l_line_loc_id_tbl.trim(l_input_size-l_count);
1717 l_price_override_tbl.trim(l_input_size-l_count);
1718
1719 PO_VALIDATION_HELPER.greater_than_zero(
1720 p_calling_module => D_complex_price_or_gt_zero
1721 , p_null_allowed_flag => PO_CORE_S.g_parameter_NO
1722 , p_value_tbl => l_price_override_tbl
1723 , p_entity_id_tbl => l_line_loc_id_tbl
1724 , p_entity_type => c_ENTITY_TYPE_LINE_LOCATION
1725 , p_column_name => c_PRICE_OVERRIDE
1726 , p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GT_ZERO
1727 , x_results => x_results
1728 , x_result_type => x_result_type
1729 );
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 IF PO_LOG.d_exc THEN
1733 PO_LOG.exc(d_mod,0,NULL);
1734 END IF;
1735 RAISE;
1736 END complex_price_or_gt_zero;
1737
1738 END PO_VAL_SHIPMENTS;